Scripts to Check Oracle Database for Log-Based Capture

``` Run this script to identify possible issues with source database replication. This script was designed to run on Oracle Database 11gR2 and higher. On a lower version the query for partitioned tables will fail.

Please share the output of this script with your HVR representative.

This script must be run in SQL Plus by a user with DBA privileges.

*********************** HVR Software ***********************/

-- Set sqlplus environment set serveroutput on set long 10000 set pagesize 2000 set linesize 180 set maxdata 10000 set arraysize 1 set trimspool on set timing on alter session set nls_date_format='dd-mon-yyyy' ;

set define on

accept filename prompt 'Output file name (default hvr_check_[today].out): ' default 'hvr_check_&_DATE..out'

spool &filename

prompt Database, instance and version information prompt

select inst_id , instance_name , host_name , version from gv$instance order by inst_id ;

prompt prompt prompt Identify whether at least minimal supplemental logging is enabled prompt If not, "alter database add supplemental log data ;" must be run prompt

select name , database_role , supplemental_log_data_min min , supplemental_log_data_pk pk , supplemental_log_data_ui ui , supplemental_log_data_fk fk , supplemental_log_data_all "ALL" from v$database ;

prompt prompt prompt Identify instance settings prompt

col value format a15

select inst_id , name , value from gv$parameter where name in ('db_block_checksum','filesystemio_options') order by inst_id , name ;

prompt prompt prompt Information about redo logs and redo log generation prompt

select l.group# , l.thread# , l.sequence# , l.bytes/1024/1024 size_mb , l.status , lf.member from v$log l , v$logfile lf where l.group# = lf.group# order by l.group# ;

prompt prompt prompt Information about redo log generation in the last 30 days prompt

break on the_window on fr_dy on to_dy

with hours as ( select rownum - 1 hour from dual connect by level <= 24 ) , days as ( select rownum - 1 day from dual connect by level < 30 + 1 --30 days ) , log_details as ( select thread# , sequence# , first_time , next_time , (next_time - first_time) * 24 * 3600 seconds_span , blocks * block_size / 1024 / 1024 mb from v$archived_log where first_time > sysdate - (select max(day) + 1 from days) ) , start_point as ( select trunc(sysdate,'hh24') start_time from dual ) , hourly_details as ( select s.start_time - d.day - h.hour/24 end_window , s.start_time - d.day - (h.hour + 1)/24 begin_window , l.thread# , l.sequence# , l.mb , case nvl(seconds_span,0) when 0 then -1 else mb / seconds_span end as mbps , case sign(s.start_time - d.day - h.hour/24 - l.first_time) when 1 then 'before' else 'on_or_after' end as first_time_ref_end , case sign(s.start_time - d.day - (h.hour + 1)/24 - l.first_time) when 1 then 'before' else 'on_or_after' end as first_time_ref_begin , l.first_time , case sign(s.start_time - d.day - h.hour/24 - l.next_time) when -1 then 'after' else 'on_or_before' end as next_time_ref_end , case sign(s.start_time - d.day - (h.hour + 1)/24 - l.next_time) when -1 then 'after' else 'on_or_before' end as next_time_ref_begin , l.next_time from hours h , days d , start_point s , log_details l ) , aggregate_hourly_details as ( select hd.begin_window , hd.end_window , trunc(hd.begin_window) + (trunc(to_number(to_char(hd.begin_window,'hh24'))/4)) / 6 as begin_four_hour_window , trunc(hd.begin_window) + (1 + trunc(to_number(to_char(hd.begin_window,'hh24'))/4)) / 6 as end_four_hour_window , hd.thread# , hd.sequence# , hd.mb , hd.mbps , case hd.first_time_ref_begin when 'before' then case hd.next_time_ref_begin when 'on_or_before' then 0 else case hd.next_time_ref_end when 'after' then 3600 * hd.mbps else 3600 * (hd.next_time - hd.begin_window) * hd.mbps end end else case hd.first_time_ref_end when 'before' then case hd.next_time_ref_end when 'on_or_before' then hd.mb else 3600 * (hd.end_window - hd.first_time) * hd.mbps end else 0 end end as mb_contribution , hd.first_time , hd.next_time from hourly_details hd ) select '1) daily' as the_window , to_char(ahd.begin_window,'dd-MON') as fr_dy , to_char(min(ahd.begin_window),'hh24:mi') as fr_hr , to_char(max(ahd.end_window),'dd-MON') as to_dy , to_char(max(ahd.end_window),'hh24:mi') as to_hr , ahd.thread# , round((sum(ahd.mb_contribution)),2) as mb_redo , round((sum(ahd.mb_contribution) / 1024),2) as gb_redo , to_char(100 * round(sum(ahd.mb_contribution) / max(sum(ahd.mb_contribution)) over (partition by ahd.thread#),4),'990d00') as "%_MAX" , min(ahd.begin_window) as order_by from aggregate_hourly_details ahd group by ahd.thread# , to_char(ahd.begin_window,'dd-MON') union all select '2) 4-hour increments' as the_window , to_char(greatest(min(ahd.begin_window),ahd.begin_four_hour_window),'dd-MON') as fr_dy , to_char(greatest(min(ahd.begin_window),ahd.begin_four_hour_window),'hh24:mi') as fr_hr , to_char(least(max(ahd.end_window),ahd.end_four_hour_window),'dd-MON') as to_dy , to_char(least(max(ahd.end_window),ahd.end_four_hour_window),'hh24:mi') as to_hr , ahd.thread# , round((sum(ahd.mb_contribution)),2) as mb_redo , round((sum(ahd.mb_contribution) / 1024),2) as gb_redo , to_char(100 * round(sum(ahd.mb_contribution) / max(sum(ahd.mb_contribution)) over (partition by ahd.thread#),4),'990d00') as "%_MAX" , (ahd.begin_four_hour_window) as order_by from aggregate_hourly_details ahd group by ahd.thread# , ahd.begin_four_hour_window , ahd.end_four_hour_window union all select '3) hourly (7 days)' , to_char(ahd.begin_window,'dd-MON') as fr_dy , to_char(ahd.begin_window,'hh24:mi') as fr_hr , to_char(ahd.end_window,'dd-MON') as to_dy , to_char(ahd.end_window,'hh24:mi') as to_hr , ahd.thread# , round((sum(ahd.mb_contribution)),2) as mb_redo , round((sum(ahd.mb_contribution) / 1024),2) as gb_redo , to_char(100 * round(sum(ahd.mb_contribution) / max(sum(ahd.mb_contribution)) over (partition by ahd.thread#),4),'990d00') as "%_MAX" , ahd.begin_window as order_by from aggregate_hourly_details ahd where ahd.begin_window >= trunc(sysdate) - 7 group by ahd.thread# , ahd.begin_window , ahd.end_window order by the_window , order_by , thread# /

prompt prompt prompt Transaction information prompt Find out how many open transactions there are and how long they are open (oldest transaction first) prompt

col transaction_duration format a45

with transaction_details as ( select inst_id , ses_addr , sysdate - start_date as diff from gv$transaction ) select s.username , to_char(trunc(t.diff)) || ' days, ' || to_char(trunc(mod(t.diff * 24,24))) || ' hours, ' || to_char(trunc(mod(t.diff * 24 * 60,24))) || ' minutes, ' || to_char(trunc(mod(t.diff * 24 * 60 * 60,60))) || ' seconds' as transaction_duration , s.program , s.terminal , s.status , s.sid , s.serial# from gv$session s , transaction_details t where s.inst_id = t.inst_id and s.saddr = t.ses_addr order by t.diff desc /

prompt prompt prompt Schema information prompt Find out what data types are in use in tables not owned by a number of Oracle's default users prompt

col owner format a30 col data_type format a50 break on owner

select case grouping_id(owner, data_type) when 1 then owner || ' TOTAL' when 3 then null else owner end owner , case grouping_id(owner, data_type) when 2 then 'AGG ' || data_type when 3 then 'GRAND TOTAL' else data_type end data_type , count(1) col_count from dba_tab_columns where owner not in ( 'ANONYMOUS' , 'APEX_PUBLIC_USER' , 'APPQOSSYS' , 'AUDSYS' , 'BI' , 'CTXSYS' , 'DBSNMP' , 'DIP' , 'DVSYS' , 'EXFSYS' , 'FLOWS_FILES' , 'GSMADMIN_INTERNAL' , 'HR' , 'IX' , 'LBACSYS' , 'MDDATA' , 'MDSYS' , 'MGMT_VIEW' , 'OE' , 'OJVMSYS' , 'OLAPSYS' , 'ORACLE_OCM' , 'ORDDATA' , 'ORDPLUGINS' , 'ORDSYS' , 'OUTLN' , 'OWBSYS' , 'OWBSYS_AUDIT' , 'PM' , 'SCOTT' , 'SH' , 'SI_INFORMTN_SCHEMA' , 'SPATIAL_CSW_ADMIN_USR' , 'SPATIAL_WFS_ADMIN_USR' , 'SYS' , 'SYSMAN' , 'SYSTEM' , 'WMSYS' , 'XDB' , 'XS$NULL' ) and owner not like 'APEX_______' escape '' and (owner, table_name) not in ( select owner, view_name from dba_views ) group by cube (owner, data_type) order by owner , data_type ;

prompt prompt prompt Find out if there are partitioned tables prompt

col interval_p format a12 col nested format a6

select owner , partitioning_type , subpartitioning_type , case nvl(interval,'$') when '$' then null else 'YES' end as interval_p , is_nested as nested , count(1) part_tables_count from dba_part_tables where owner not in ( 'ANONYMOUS' , 'APEX_PUBLIC_USER' , 'APPQOSSYS' , 'AUDSYS' , 'BI' , 'CTXSYS' , 'DBSNMP' , 'DIP' , 'DVSYS' , 'EXFSYS' , 'FLOWS_FILES' , 'GSMADMIN_INTERNAL' , 'HR' , 'IX' , 'LBACSYS' , 'MDDATA' , 'MDSYS' , 'MGMT_VIEW' , 'OE' , 'OJVMSYS' , 'OLAPSYS' , 'ORACLE_OCM' , 'ORDDATA' , 'ORDPLUGINS' , 'ORDSYS' , 'OUTLN' , 'OWBSYS' , 'OWBSYS_AUDIT' , 'PM' , 'SCOTT' , 'SH' , 'SI_INFORMTN_SCHEMA' , 'SPATIAL_CSW_ADMIN_USR' , 'SPATIAL_WFS_ADMIN_USR' , 'SYS' , 'SYSMAN' , 'SYSTEM' , 'WMSYS' , 'XDB' , 'XS$NULL' ) and owner not like 'APEX_______' escape '' group by owner , partitioning_type , subpartitioning_type , case nvl(interval,'$') when '$' then null else 'YES' end , is_nested order by owner , partitioning_type , subpartitioning_type , case nvl(interval,'$') when '$' then null else 'YES' end , is_nested ;

prompt prompt prompt Database sequences by owner and increment value prompt

select sequence_owner owner , increment_by , count(1) seq_count from dba_sequences where sequence_owner not in ( 'ANONYMOUS' , 'APEX_PUBLIC_USER' , 'APPQOSSYS' , 'AUDSYS' , 'BI' , 'CTXSYS' , 'DBSNMP' , 'DIP' , 'DVSYS' , 'EXFSYS' , 'FLOWS_FILES' , 'GSMADMIN_INTERNAL' , 'HR' , 'IX' , 'LBACSYS' , 'MDDATA' , 'MDSYS' , 'MGMT_VIEW' , 'OE' , 'OJVMSYS' , 'OLAPSYS' , 'ORACLE_OCM' , 'ORDDATA' , 'ORDPLUGINS' , 'ORDSYS' , 'OUTLN' , 'OWBSYS' , 'OWBSYS_AUDIT' , 'PM' , 'SCOTT' , 'SH' , 'SI_INFORMTN_SCHEMA' , 'SPATIAL_CSW_ADMIN_USR' , 'SPATIAL_WFS_ADMIN_USR' , 'SYS' , 'SYSMAN' , 'SYSTEM' , 'WMSYS' , 'XDB' , 'XS$NULL' ) and sequence_owner not like 'APEX_______' escape '' group by sequence_owner , increment_by order by sequence_owner , increment_by ;

break on tablespace_name

prompt prompt prompt Find out which users have segments in encrypted tablespaces (if any) prompt

select e.tablespace_name , e.owner , count(distinct e.segment_name) segment_count from dba_extents e , dba_tablespaces t where e.tablespace_name = t.tablespace_name and t.encrypted = 'YES' and e.owner not in ( 'ANONYMOUS' , 'APEX_PUBLIC_USER' , 'APPQOSSYS' , 'AUDSYS' , 'BI' , 'CTXSYS' , 'DBSNMP' , 'DIP' , 'DVSYS' , 'EXFSYS' , 'FLOWS_FILES' , 'GSMADMIN_INTERNAL' , 'HR' , 'IX' , 'LBACSYS' , 'MDDATA' , 'MDSYS' , 'MGMT_VIEW' , 'OE' , 'OJVMSYS' , 'OLAPSYS' , 'ORACLE_OCM' , 'ORDDATA' , 'ORDPLUGINS' , 'ORDSYS' , 'OUTLN' , 'OWBSYS' , 'OWBSYS_AUDIT' , 'PM' , 'SCOTT' , 'SH' , 'SI_INFORMTN_SCHEMA' , 'SPATIAL_CSW_ADMIN_USR' , 'SPATIAL_WFS_ADMIN_USR' , 'SYS' , 'SYSMAN' , 'SYSTEM' , 'WMSYS' , 'XDB' , 'XS$NULL' ) and e.owner not like 'APEX_______' escape '' group by e.tablespace_name , e.owner order by e.tablespace_name , e.owner ;

prompt prompt prompt Data volume information prompt

break on owner

select owner , segment_type , segment_subtype , round(sum(bytes)/1024/1024,2) mb , round(sum(bytes)/1024/1024/1024,2) gb from dba_segments where owner not in ( 'ANONYMOUS' , 'APEX_PUBLIC_USER' , 'APPQOSSYS' , 'AUDSYS' , 'BI' , 'CTXSYS' , 'DBSNMP' , 'DIP' , 'DVSYS' , 'EXFSYS' , 'FLOWS_FILES' , 'GSMADMIN_INTERNAL' , 'HR' , 'IX' , 'LBACSYS' , 'MDDATA' , 'MDSYS' , 'MGMT_VIEW' , 'OE' , 'OJVMSYS' , 'OLAPSYS' , 'ORACLE_OCM' , 'ORDDATA' , 'ORDPLUGINS' , 'ORDSYS' , 'OUTLN' , 'OWBSYS' , 'OWBSYS_AUDIT' , 'PM' , 'SCOTT' , 'SH' , 'SI_INFORMTN_SCHEMA' , 'SPATIAL_CSW_ADMIN_USR' , 'SPATIAL_WFS_ADMIN_USR' , 'SYS' , 'SYSMAN' , 'SYSTEM' , 'WMSYS' , 'XDB' , 'XS$NULL' ) and owner not like 'APEX_______' escape '' group by rollup( owner , segment_type , segment_subtype ) order by owner , segment_type , segment_subtype /

prompt prompt prompt Trigger information prompt

col triggering_event format a40

select owner , trigger_type , triggering_event , status , count(1) num_triggers from dba_triggers where owner not in ( 'ANONYMOUS' , 'APEX_PUBLIC_USER' , 'APPQOSSYS' , 'AUDSYS' , 'BI' , 'CTXSYS' , 'DBSNMP' , 'DIP' , 'DVSYS' , 'EXFSYS' , 'FLOWS_FILES' , 'GSMADMIN_INTERNAL' , 'HR' , 'IX' , 'LBACSYS' , 'MDDATA' , 'MDSYS' , 'MGMT_VIEW' , 'OE' , 'OJVMSYS' , 'OLAPSYS' , 'ORACLE_OCM' , 'ORDDATA' , 'ORDPLUGINS' , 'ORDSYS' , 'OUTLN' , 'OWBSYS' , 'OWBSYS_AUDIT' , 'PM' , 'SCOTT' , 'SH' , 'SI_INFORMTN_SCHEMA' , 'SPATIAL_CSW_ADMIN_USR' , 'SPATIAL_WFS_ADMIN_USR' , 'SYS' , 'SYSMAN' , 'SYSTEM' , 'WMSYS' , 'XDB' , 'XS$NULL' ) and owner not like 'APEX_______' escape '' group by owner , trigger_type , triggering_event , status order by owner , trigger_type , triggering_event , status ;

prompt prompt prompt Constraint information prompt

select owner , constraint_type , delete_rule , deferrable , deferred , count(1) num_constraints from dba_constraints where owner not in ( 'ANONYMOUS' , 'APEX_PUBLIC_USER' , 'APPQOSSYS' , 'AUDSYS' , 'BI' , 'CTXSYS' , 'DBSNMP' , 'DIP' , 'DVSYS' , 'EXFSYS' , 'FLOWS_FILES' , 'GSMADMIN_INTERNAL' , 'HR' , 'IX' , 'LBACSYS' , 'MDDATA' , 'MDSYS' , 'MGMT_VIEW' , 'OE' , 'OJVMSYS' , 'OLAPSYS' , 'ORACLE_OCM' , 'ORDDATA' , 'ORDPLUGINS' , 'ORDSYS' , 'OUTLN' , 'OWBSYS' , 'OWBSYS_AUDIT' , 'PM' , 'SCOTT' , 'SH' , 'SI_INFORMTN_SCHEMA' , 'SPATIAL_CSW_ADMIN_USR' , 'SPATIAL_WFS_ADMIN_USR' , 'SYS' , 'SYSMAN' , 'SYSTEM' , 'WMSYS' , 'XDB' , 'XS$NULL' ) and owner not like 'APEX_______' escape '' group by owner , constraint_type , delete_rule , deferrable , deferred order by owner , constraint_type , delete_rule , deferrable , deferred ;

prompt prompt prompt Tables with no primary or unique keys or unique indexes prompt

select t.owner , count(1) num_tables from dba_tables t where (t.owner, t.table_name) not in ( select c.owner, c.table_name from dba_constraints c where c.constraint_type in ('P','U') ) and (t.owner, t.table_name) not in ( select i.table_owner, i.table_name from dba_indexes i where i.uniqueness = 'UNIQUE' ) and t.owner not in ( 'ANONYMOUS' , 'APEX_PUBLIC_USER' , 'APPQOSSYS' , 'AUDSYS' , 'BI' , 'CTXSYS' , 'DBSNMP' , 'DIP' , 'DVSYS' , 'EXFSYS' , 'FLOWS_FILES' , 'GSMADMIN_INTERNAL' , 'HR' , 'IX' , 'LBACSYS' , 'MDDATA' , 'MDSYS' , 'MGMT_VIEW' , 'OE' , 'OJVMSYS' , 'OLAPSYS' , 'ORACLE_OCM' , 'ORDDATA' , 'ORDPLUGINS' , 'ORDSYS' , 'OUTLN' , 'OWBSYS' , 'OWBSYS_AUDIT' , 'PM' , 'SCOTT' , 'SH' , 'SI_INFORMTN_SCHEMA' , 'SPATIAL_CSW_ADMIN_USR' , 'SPATIAL_WFS_ADMIN_USR' , 'SYS' , 'SYSMAN' , 'SYSTEM' , 'WMSYS' , 'XDB' , 'XS$NULL' ) and t.owner not like 'APEX_______' escape '' and t.table_name not like 'HVR_%' escape '' group by t.owner order by t.owner ;

prompt prompt prompt Tables with encrypted columns prompt

select owner , table_name , column_name from dba_encrypted_columns where owner not in ( 'ANONYMOUS' , 'APEX_PUBLIC_USER' , 'APPQOSSYS' , 'AUDSYS' , 'BI' , 'CTXSYS' , 'DBSNMP' , 'DIP' , 'DVSYS' , 'EXFSYS' , 'FLOWS_FILES' , 'GSMADMIN_INTERNAL' , 'HR' , 'IX' , 'LBACSYS' , 'MDDATA' , 'MDSYS' , 'MGMT_VIEW' , 'OE' , 'OJVMSYS' , 'OLAPSYS' , 'ORACLE_OCM' , 'ORDDATA' , 'ORDPLUGINS' , 'ORDSYS' , 'OUTLN' , 'OWBSYS' , 'OWBSYS_AUDIT' , 'PM' , 'SCOTT' , 'SH' , 'SI_INFORMTN_SCHEMA' , 'SPATIAL_CSW_ADMIN_USR' , 'SPATIAL_WFS_ADMIN_USR' , 'SYS' , 'SYSMAN' , 'SYSTEM' , 'WMSYS' , 'XDB' , 'XS$NULL' ) and owner not like 'APEX_______' escape '' order by owner , table_name , column_name ;

prompt prompt prompt ** please provide script output &filename to your HVR representative ** prompt

spool off

clear buffer set define off ```