# S405DG.def: Collects Data Guard Information # $Id: S405DG.def,v 1.19 2009/01/25 13:50:14 mschenke Exp $ # ARCS: $Header: /u02/webapps/workbench/cvs/RDA_4/src/scripting/lib/modules/S405DG.def,v 1.19 2009/01/25 13:50:14 mschenke Exp $ # # Change History # 20090125 MSC Make the debug messages more consistent. =head1 NAME S405DG - Collects Data Guard Information =head1 DESCRIPTION This module collects Data Guard-related information (for Oracle 9i Release 2 or later). The following reports can be generated and are regrouped under C. =cut if !getSetting('DATABASE_INSTALLED') return echo tput('bold'),'Processing DG module ...',tput('off') # Initialization var $AGE = trim(getSetting('DG_AGE')) var $TOC = '%TOC%' var $TOP = '[[#Top][Back to top]]' toc '1:Data Guard' # Load the common macros run DBinfo() # Validate the number of days for archived history if !match($AGE,'^\d+$') var $AGE = 7 # Obtain the database version var $ORACLE_VERSION = get_db_version() if !match($ORACLE_VERSION,'^(92|10|11)') {report not_applicable write 'The Data Guard module can only be executed on 9i Release 2 \ or higher.' toc '2:[[',getFile(),'][rda_report][Not Applicable]]' return } =head2 basic - Basic Database Information Provides the basic information related to Data Guard. =cut debug ' Inside DG module, gathering basic database information' report basic var $TTL = '---+!! Basic Database Information' var @TTL = ('',\ '---+ Database Information',\ '---+ Instance Information',\ '---+ RAC Configuration',\ '---+ Instance Recovery Information') if match($ORACLE_VERSION,'^(10|11)') {var $HDR[1] = '| *DB ID*|*DB Unique Name*| *Activation#*|*DB Role*|\ *Switchover Status*| *Platform ID*|*Platform Name *|\ *Controlfile Type* |*Log Mode* |' set $sql {SELECT '| ' || " dbid || '|' || " db_unique_name || ' | ' || " activation# || '|' || " database_role || ' | ' || " switchover_status || ' | ' || " platform_id || '|' || " platform_name || ' |' || " controlfile_type || ' |' || " log_mode || ' |' " FROM v$database; } var $rol = field('\|',4,grepSql($sql,'.','f')) } else {var $HDR[1] = '| *DB ID*| *Activation#*|*DB Role*|*Switchover Status*|\ *Controlfile Type* |*Log Mode* |' set $sql {SELECT '| ' || " dbid || '| ' || " activation# || '|' || " database_role || ' | ' || " switchover_status || ' |' || " controlfile_type || ' |' || " log_mode || ' |' " FROM v$database; } var $rol = field('\|',3,grepSql($sql,'.','f')) } var $HDR[2] = '|*Instance Name* |*Host Name* |*Version* |*Archiver* \ |*Log Switch Wait* |*Status* |' append $sql {PROMPT ___Macro_separator(2)___ "SELECT '|' || " instance_name || ' |' || " host_name || ' |' || " version || ' |' || " archiver || ' |' || " log_switch_wait || ' |' || " status || ' |' " FROM gv$instance; } var ($HDR[3],$col3) = getSqlColumns('RDA','','V$PARAMETER') call clearSqlColumns('RDA') if $col3 {append $sql {PROMPT ___Macro_separator(3)___ "SELECT :1 " FROM v$parameter " WHERE name = 'cluster_database'; } } var ($HDR[4],$col4) = getSqlColumns('RDA','','GV$INSTANCE_RECOVERY') call clearSqlColumns('RDA') if $col4 {append $sql {PROMPT ___Macro_separator(4)___ "SELECT :2 " FROM gv$instance_recovery; } } call separator(1) call writeSql(bindSql($sql,$col3,$col4),2) call separator(0,'Basic Database Information') =head2 primary - Primary Database Information Provides the primary database information. =cut if match($rol,'PRIMARY') {debug ' Inside DG module, gathering primary database information' report primary var $TTL = '---+!! Primary Database Information' var @TTL = ('',\ '---+ Archive Dest Information',\ '---+ Archive Dest Status',\ '---+ Data Guard Features Enabled',\ '---+ Data Guard Status',\ '---+ Standby Redo Logs',\ '---+ Archival History',\ '---+ Archival History Count') var ($HDR[1],$col1) = getSqlColumns('RDA','','V$ARCHIVE_DEST') call clearSqlColumns('RDA') if $col1 {set $sql {SELECT :1 " FROM v$archive_dest; } } else {set $sql {PROMPT ___Macro_separator(1)___ } } var ($HDR[2],$col2) = getSqlColumns('RDA','','V$ARCHIVE_DEST_STATUS') call clearSqlColumns('RDA') if $col2 {append $sql {PROMPT ___Macro_separator(2)___ "SELECT :2 " FROM v$archive_dest_status; } } if match($ORACLE_VERSION,'^(10|11)') {var $HDR[3] = '|*Flashback*|*Force Logging*|*Supplemental Log Data PK*|\ *Supplemental Log Data UI*|*Remote Archive *|\ *Protection Mode*|*Protection Level*|*Data Guard Broker*|' append $sql {PROMPT ___Macro_separator(3)___ "SELECT '| ' || " flashback_on || ' |' || " force_logging || ' |' || " supplemental_log_data_pk || ' |' || " supplemental_log_data_ui || ' |' || " remote_archive || ' |' || " protection_mode || ' |' || " protection_level || ' |' || " dataguard_broker || ' |' " FROM v$database; } } else {var $HDR[3] = '|*Force Logging*|*Supplemental Log Data PK*|\ *Supplemental Log Data UI*|*Remote Archive*|\ *Protection Mode*|*Protection Level*|*Data Guard Broker*|' append $sql {PROMPT ___Macro_separator(3)___ "SELECT '| ' || " force_logging || ' |' || " supplemental_log_data_pk || ' |' || " supplemental_log_data_ui || ' |' || " remote_archive || ' |' || " protection_mode || ' |' || " protection_level || ' |' || " dataguard_broker || ' |' " FROM v$database; } } call setSqlColumns('RDA','message',"REPLACE(message,CHR(10),'%BR%')") call setSqlColumns('RDA','severity',"DECODE(UPPER(severity),\ 'ERROR','%RED%ERROR%ENDCOLOR%',\ 'FATAL','%RED%FATAL%ENDCOLOR%',\ UPPER(severity))") var ($HDR[4],$col4) = getSqlColumns('RDA','','V$DATAGUARD_STATUS') call clearSqlColumns('RDA') if $col4 {append $sql {PROMPT ___Macro_separator(4)___ "SELECT :3 " FROM v$dataguard_status; } } var ($HDR[5],$col5) = getSqlColumns('RDA','','V$STANDBY_LOG') call clearSqlColumns('RDA') if $col5 {append $sql {PROMPT ___Macro_separator(5)___ "SELECT :4 " FROM v$standby_log; } } var ($HDR[6],$col6) = getSqlColumns('RDA','','V$ARCHIVED_LOG') if $col6 {append $sql {PROMPT ___Macro_separator(6)___ "SELECT :5 " FROM v$archived_log " WHERE completion_time >= SYSDATE - :6; } } call clearSqlColumns('RDA') var $HDR[7] = '| *Count*|' append $sql {PROMPT ___Macro_separator(7)___ "SELECT '| ' || " COUNT(1) || '|' " FROM v$archived_log; } if match($ORACLE_VERSION,'^11') {var $TTL[8] = '---+ X$LOGBUF_READHIST Table Information' var ($HDR[8],$col8) = getSqlColumns('RDA','','X$LOGBUF_READHIST') call clearSqlColumns('RDA') if $col8 {append $sql {PROMPT ___Macro_separator(8)___ "SELECT :7 " FROM x$logbuf_readhist; } } } call separator(1) call writeSql(bindSql($sql,$col1,$col2,$col4,$col5,$col6,$AGE,$col8),2) call separator(0,'Primary Database Information') } =head2 physical - Physical Standby Information Provides the physical standby database information. =cut if match($rol,'PHYSICAL STANDBY') {debug ' Inside DG module, gathering physical standby database information' if match($ORACLE_VERSION,'^(10|11)') {set $sql {SELECT '| ' || " open_mode " FROM v$database; } var $mod = field('\|',1,grepSql($sql,'.','f')) } report physical var $TTL = '---+!! Physical Standby Information' var @TTL = ('',\ '---+ Data Guard Features Enabled',\ '---+ Database Services Information from GV$SERVICES',\ '---+ Database Services Information from DBA_SERVICES',\ '---+ Archive Dest Information',\ '---+ Archive Dest Status',\ '---+ Redo Receive Status',\ '---+ Redo Apply Status',\ '---+ Standby Redo Log information',\ '---+ Data Guard Status',\ '---+ Archive Gap Information',\ '---+ Archival History',\ '---+ Archival History Count',\ '---+ Last Sequence Applied and Received') if match($ORACLE_VERSION,'^(10|11)') {var $HDR[1] = '|*Flashback*|*Force Logging*|*Supplemental Log Data PK*|\ *Supplemental Log Data UI*|*Remote Archive *|\ *Protection Mode*|*Protection Level*|*Data Guard Broker*|' set $sql {SELECT '| ' || " flashback_on || ' |' || " force_logging || ' |' || " supplemental_log_data_pk || ' |' || " supplemental_log_data_ui || ' |' || " remote_archive || ' |' || " protection_mode || ' |' || " protection_level || ' |' || " dataguard_broker || ' |' " FROM v$database; } } else {var $HDR[1] = '|*Force Logging*|*Supplemental Log Data PK*|\ *Supplemental Log Data UI*|*Remote Archive*|\ *Protection Mode*|*Protection Level*|*Data Guard Broker*|' set $sql {SELECT '| ' || " force_logging || ' |' || " supplemental_log_data_pk || ' |' || " supplemental_log_data_ui || ' |' || " remote_archive || ' |' || " protection_mode || ' |' || " protection_level || ' |' || " dataguard_broker || ' |' " FROM v$database; } } if match($ORACLE_VERSION,'^(10|11)') {if match($mod,'MOUNTED') {var ($HDR[2],$col2) = getSqlColumns('RDA','','GV$SERVICES') call clearSqlColumns('RDA') if $col2 {append $sql {PROMPT ___Macro_separator(2)___ "SELECT :1 " FROM gv$services; } } } elsif match($mod,'READ ONLY') {var ($HDR[3],$col3) = getSqlColumns('RDA','','DBA_SERVICES') call clearSqlColumns('RDA') if $col3 {append $sql {PROMPT ___Macro_separator(3)___ "SELECT :2 " FROM dba_services; } } } } var ($HDR[4],$col4) = getSqlColumns('RDA','','V$ARCHIVE_DEST') call clearSqlColumns('RDA') if $col4 {append $sql {PROMPT ___Macro_separator(4)___ "SELECT :3 " FROM v$archive_dest; } } var ($HDR[5],$col5) = getSqlColumns('RDA','','V$ARCHIVE_DEST_STATUS') call clearSqlColumns('RDA') if $col5 {append $sql {PROMPT ___Macro_separator(5)___ "SELECT :4 " FROM v$archive_dest_status; } } var ($HDR[6],$col6) = getSqlColumns('RDA','','V$MANAGED_STANDBY') var $HDR[7] = $HDR[6] call clearSqlColumns('RDA') if $col6 {append $sql {PROMPT ___Macro_separator(6)___ "SELECT :5 " FROM v$managed_standby " WHERE process IN ('RFS','ARCH'); "PROMPT ___Macro_separator(7)___ "SELECT :5 " FROM v$managed_standby " WHERE process LIKE 'MRP%'; } } var ($HDR[8],$col8) = getSqlColumns('RDA','','V$STANDBY_LOG') call clearSqlColumns('RDA') if $col8 {append $sql {PROMPT ___Macro_separator(8)___ "SELECT :6 " FROM v$standby_log; } } call setSqlColumns('RDA','message',"REPLACE(message,CHR(10),'%BR%')") call setSqlColumns('RDA','severity',"DECODE(UPPER(severity),\ 'ERROR','%RED%ERROR%ENDCOLOR%',\ 'FATAL','%RED%FATAL%ENDCOLOR%',\ UPPER(severity))") var ($HDR[9],$col9) = getSqlColumns('RDA','','V$DATAGUARD_STATUS') call clearSqlColumns('RDA') if $col9 {append $sql {PROMPT ___Macro_separator(9)___ "SELECT :7 " FROM v$dataguard_status; } } var ($HDR[10],$col10) = getSqlColumns('RDA','','V$ARCHIVE_GAP') call clearSqlColumns('RDA') if $col10 {append $sql {PROMPT ___Macro_separator(10)___ "SELECT :8 " FROM v$archive_gap; } } var ($HDR[11],$col11) = getSqlColumns('RDA','','V$ARCHIVED_LOG') call clearSqlColumns('RDA') if $col11 {append $sql {PROMPT ___Macro_separator(11)___ "SELECT :9 " FROM v$archived_log " WHERE completion_time >= SYSDATE - :10; } } var $HDR[12] = '| *Count*|' var $HDR[13] = '| *Last Sequence Received*| *Last Sequence Applied*|' append $sql {PROMPT ___Macro_separator(12)___ "SELECT '| ' || " COUNT(1) || '|' " FROM v$archived_log; "PROMPT ___Macro_separator(13)___ "SELECT '| ' || " max(al.sequence#) || '| ' || " max(lh.sequence#) || '|' " FROM v$archived_log al, v$log_history lh; } if match($ORACLE_VERSION,'^(102|11)') {var $TTL[14] = '---+ Wait Time For Events' var $HDR[14] = '|*Event* | *Total Waits*| *Time Waited (seconds)*| \ *Average Wait (Milliseconds)*|*Time*|' var $TTL[15] = '---+ Event Histogram Information' var $HDR[15] = '|*Time* |*Event* | *Wait Time (Milliseconds)*| *Wait Count*|' var $TTL[16] = '---+ Recovery Progress' var $HDR[16] = '|*Start Time* |*Type* |*Item* | *Units*| *So Far*| *Total*|\ *Time Stamp* |' append $sql {PROMPT ___Macro_separator(14)___ "SELECT '|' || " event || ' | ' || " total_waits || '| ' || " ROUND(time_waited/100) || '| ' || " average_wait*10 || '|' || " TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM v$system_event " WHERE time_waited > 100 " AND event NOT LIKE 'rdbms ipc %' " AND event NOT LIKE '%timer%' " AND LOWER(event) NOT LIKE '%idle%' " AND LOWER(event) not like 'sql%net%' " AND event NOT LIKE 'ges%' " ORDER BY time_waited; "PROMPT ___Macro_separator(15)___ "SELECT '|' || " TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " event || ' | ' || " wait_time_milli || '| ' || " wait_count || '|' " FROM v$event_histogram " WHERE wait_count > 0; "PROMPT ___Macro_separator(16)___ "SELECT '|' || " TO_CHAR(start_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " type || ' |' || " item || ' | ' || " units || '| ' || " sofar || '| ' || " total || '|' || " TO_CHAR(timestamp,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM v$recovery_progress; } } call separator(1) call writeSql(bindSql($sql,$col2,$col3,$col4,$col5,$col6,$col8,$col9,$col10,\ $col11,$AGE),5) call separator(0,'Physical Standby Information') } =head2 logical - Logical Standby Information Provides the logical standby database information. =cut if match($rol,'LOGICAL STANDBY') {debug ' Inside DG module, gathering logical standby database information \ (can take time) ' if match($ORACLE_VERSION,'^(10|11)') {set $sql {SELECT '| ' || " open_mode " FROM v$database; } var $mod = field('\|',1,grepSql($sql,'.','f')) } report logical var $TTL = '---+!! Logical Standby Information' var @TTL = ('',\ '---+ Data Guard Features Enabled',\ '---+ Database Services Information from GV$SERVICES',\ '---+ Database Services Information from DBA_SERVICES',\ '---+ Archive Dest Information',\ '---+ Archive Dest Status',\ '---+ Redo Receive Status',\ '---+ Standby Redo Log information',\ '---+ Logical Standby Parameters',\ code(concat('---++ Redo Apply status at ',\ gmtime('%Y%m%d_%H%M%S'))),\ code(concat('---++ Logical Statistics at ',\ gmtime('%Y%m%d_%H%M%S'))),\ '---+ SQL Applied',\ '---+ Data Guard Status',\ '---+ Archival History',\ '---+ Logical Standby Apply Activity History',\ '---+ Logical Standby Progress Times',\ '---+ Logical Standby Event Information',\ '---+ Logical Standby Parameters',\ '---+ LogMiner Session Information',\ '---+ LogMiner Dictionary Information',\ '---+ LogMiner Dictionary Status',\ '---+ LogMiner Persistent Sessions',\ '---+ LogMiner Key Tables',\ undef,\ undef,\ undef) if match($ORACLE_VERSION,'^(10|11)') {var $HDR[1] = '|*Flashback*|*Force Logging*|*Supplemental Log Data PK*|\ *Supplemental Log Data UI*|*Remote Archive *|\ *Protection Mode*|*Protection Level*|*Data Guard Broker*|' set $sql {SELECT '| ' || " flashback_on || ' |' || " force_logging || ' |' || " supplemental_log_data_pk || ' |' || " supplemental_log_data_ui || ' |' || " remote_archive || ' |' || " protection_mode || ' |' || " protection_level || ' |' || " dataguard_broker || ' |' " FROM v$database; } } else {var $HDR[1] = '|*Force Logging*|*Supplemental Log Data PK*|\ *Supplemental Log Data UI*|*Remote Archive*|\ *Protection Mode*|*Protection Level*|*Data Guard Broker*|' set $sql {SELECT '| ' || " force_logging || ' |' || " supplemental_log_data_pk || ' |' || " supplemental_log_data_ui || ' |' || " remote_archive || ' |' || " protection_mode || ' |' || " protection_level || ' |' || " dataguard_broker || ' |' " FROM v$database; } } if match($ORACLE_VERSION,'^(10|11)') {if match($mod,'MOUNTED') {var ($HDR[2],$col2) = getSqlColumns('RDA','','GV$SERVICES') call clearSqlColumns('RDA') if $col2 {append $sql {PROMPT ___Macro_separator(2)___ "SELECT :1 " FROM gv$services; } } } elsif match($mod,'READ ONLY|READ WRITE') {var ($HDR[3],$col3) = getSqlColumns('RDA','','DBA_SERVICES') call clearSqlColumns('RDA') if $col3 {append $sql {PROMPT ___Macro_separator(3)___ "SELECT :2 " FROM dba_services; } } } } var ($HDR[4],$col4) = getSqlColumns('RDA','','V$ARCHIVE_DEST') call clearSqlColumns('RDA') if $col4 {append $sql {PROMPT ___Macro_separator(4)___ "SELECT :3 " FROM v$archive_dest; } } var ($HDR[5],$col5) = getSqlColumns('RDA','','V$ARCHIVE_DEST_STATUS') call clearSqlColumns('RDA') if $col5 {append $sql {PROMPT ___Macro_separator(5)___ "SELECT :4 " FROM v$archive_dest_status; } } var ($HDR[6],$col6) = getSqlColumns('RDA','','V$MANAGED_STANDBY') call clearSqlColumns('RDA') if $col6 {append $sql {PROMPT ___Macro_separator(6)___ "SELECT :5 " FROM v$managed_standby " WHERE process IN ('RFS','ARCH'); } } if match($ORACLE_VERSION,'^(10|11)') {var ($HDR[7],$col7) = getSqlColumns('RDA','','V$STANDBY_LOG') call clearSqlColumns('RDA') if $col7 {append $sql {PROMPT ___Macro_separator(7)___ "SELECT :6 " FROM v$standby_log; } } } var ($HDR[8],$col8) = getSqlColumns('RDA','','DBA_LOGSTDBY_PARAMETERS') call clearSqlColumns('RDA') if $col8 {append $sql {PROMPT ___Macro_separator(8)___ "SELECT :7 " FROM dba_logstdby_parameters; } } if match($ORACLE_VERSION,'^(10|11)') {var ($HDR[9],$col9) = getSqlColumns('RDA','','V$LOGSTDBY_PROCESS') call clearSqlColumns('RDA') if $col9 {append $sql {PROMPT ___Macro_separator(25)___ "PROMPT ---+ Redo Apply status "PROMPT ___Macro_separator(9)___ "SELECT :8 " FROM v$logstdby_process; "EXECUTE DBMS_LOCK.SLEEP(30); "PROMPT ___Macro_separator(9)___ "SELECT :8 " FROM v$logstdby_process; "EXECUTE DBMS_LOCK.SLEEP(30); "PROMPT ___Macro_separator(9)___ "SELECT :8 " FROM v$logstdby_process; } } } else {var ($HDR[9],$col9) = getSqlColumns('RDA','','V$LOGSTDBY') call clearSqlColumns('RDA') if $col9 {append $sql {PROMPT ___Macro_separator(25)___ "PROMPT ---+ Redo Apply status "PROMPT ___Macro_separator(9)___ "SELECT :8 " FROM v$logstdby; "EXECUTE DBMS_LOCK.SLEEP(30); "PROMPT ___Macro_separator(9)___ "SELECT :8 " FROM v$logstdby; "EXECUTE DBMS_LOCK.SLEEP(30); "PROMPT ___Macro_separator(9)___ "SELECT :8 " FROM v$logstdby; } } } var ($HDR[10],$col10) = getSqlColumns('RDA','','V$LOGSTDBY_STATS') call clearSqlColumns('RDA') if $col10 {append $sql {PROMPT ___Macro_separator(25)___ "PROMPT ---+ Logical Statistics "PROMPT ___Macro_separator(10)___ "SELECT :9 " FROM v$logstdby_stats; "EXECUTE DBMS_LOCK.SLEEP(30); "PROMPT ___Macro_separator(10)___ "SELECT :9 " FROM v$logstdby_stats; "EXECUTE DBMS_LOCK.SLEEP(30); "PROMPT ___Macro_separator(10)___ "SELECT :9 " FROM v$logstdby_stats; } } var $HDR[11] = '|*Process*|*Server ID*|*SQL Text*|' append $sql {PROMPT ___Macro_separator(11)___ "SELECT '| ' || " s.process || ' |' || " sas.server_id || ' |' || " sa.sql_text || ' |' " FROM v$sqlarea sa,v$session s,v$streams_apply_server sas " WHERE s.sid = sas.sid " AND sa.address = s.sql_address; } call setSqlColumns('RDA','message',"REPLACE(message,CHR(10),'%BR%')") call setSqlColumns('RDA','severity',"DECODE(UPPER(severity),\ 'ERROR','%RED%ERROR%ENDCOLOR%',\ 'FATAL','%RED%FATAL%ENDCOLOR%',\ UPPER(severity))") var ($HDR[12],$col12) = getSqlColumns('RDA','','V$DATAGUARD_STATUS') call clearSqlColumns('RDA') if $col12 {append $sql {PROMPT ___Macro_separator(12)___ "SELECT :10 " FROM v$dataguard_status; } } var ($HDR[13],$col13) = getSqlColumns('RDA','','DBA_LOGSTDBY_LOG') call clearSqlColumns('RDA') if $col13 {append $sql {PROMPT ___Macro_separator(13)___ "SELECT :11 " FROM dba_logstdby_log; } } var $HDR[14] = '|*Finished* | *Newest SCN*| *Applied SCN*| *Read SCN*|' var $HDR[15] = '|*Newest Time* |*Applied Time* |*Read Time* |' var $HDR[16] = '|*Time* | *Commit SCN*| *Current SCN*|*Event* |*Status* |' var $HDR[17] = '|*Name* |*Value* |*Type* |' append $sql {PROMPT ___Macro_separator(14)___ "SELECT '|' || " (CASE WHEN newest_scn = applied_scn THEN 'Done' " WHEN newest_scn <= applied_scn + 9 THEN 'Done?' " WHEN newest_scn > (SELECT MAX(next_change#) " FROM dba_logstdby_log) THEN 'Near done' " WHEN (SELECT COUNT(*) " FROM dba_logstdby_log " WHERE (next_change#, thread#) NOT IN " (SELECT first_change#, " thread# " FROM dba_logstdby_log)) > 1 THEN 'Gap' " WHEN newest_scn > applied_scn THEN 'Not Done' " ELSE '---' end) || ' | ' || " newest_scn || '| ' || " applied_scn || '| ' || " read_scn || '|' " FROM dba_logstdby_progress; "PROMPT ___Macro_separator(15)___ "SELECT '|' || " TO_CHAR(newest_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(applied_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " TO_CHAR(read_time,'DD-Mon-YYYY HH24:MI:SS') || ' |' " FROM dba_logstdby_progress; "PROMPT ___Macro_separator(16)___ "SET LONGC 10000 "SET LONG 10000 "SET LINES 15000 "SELECT '|' || " TO_CHAR(event_time, 'DD-Mon-YYYY HH24:MI:SS') || ' | ' || " commit_scn || '| '|| " current_scn || '|' || " REPLACE(event,CHR(10),'%BR%') || ' |' || " REPLACE(status,CHR(10),'%BR%') || ' |' " FROM dba_logstdby_events " ORDER BY event_time, commit_scn, current_scn; "PROMPT ___Macro_separator(17)___ "SELECT '|' || " name || ' |' || " value || ' |' || " type || ' |' " FROM system.logstdby$parameters " ORDER BY type, name; } var ($HDR[18],$col18) = getSqlColumns('RDA','','SYSTEM.LOGMNR_SESSION$') call clearSqlColumns('RDA') if $col18 {append $sql {PROMPT ___Macro_separator(18)___ "SELECT :12 " FROM system.logmnr_session$; } } var ($HDR[19],$col19) = getSqlColumns('RDA','','SYSTEM.LOGMNR_DICTIONARY$') call clearSqlColumns('RDA') if $col19 {append $sql {PROMPT ___Macro_separator(19)___ "SELECT :13 " FROM system.logmnr_dictionary$; } } var ($HDR[20],$col20) = getSqlColumns('RDA','','SYSTEM.LOGMNR_DICTSTATE$') call clearSqlColumns('RDA') if $col20 {append $sql {PROMPT ___Macro_separator(20)___ "SELECT :14 " FROM system.logmnr_dictstate$; } } var ($HDR[21],$col21) = getSqlColumns('RDA','','V$LOGMNR_SESSION') call clearSqlColumns('RDA') if $col21 {append $sql {PROMPT ___Macro_separator(21)___ "SELECT :15 " FROM v$logmnr_session; } } var $HDR[22] = '|*LogMiner UID* | *Object Number*| *Objv Number*|*Owner* |\ *Name* |' append $sql {PROMPT ___Macro_separator(22)___ "SELECT '|' || " o.logmnr_uid || ' | ' || " o.obj# || '| ' || " o.objv# || '|' || " u.name || ' |' || " o.name || ' |' " FROM system.logmnr_obj$ o, " system.logmnr_user$ u " WHERE o.logmnr_uid = u.logmnr_uid " AND o.owner# = u.user# " AND o.name in ('JOB$','JOBSEQ','SEQ$','AUD$','FGA_LOG$','IND$','COL$', " 'LOGSTDBY$PARAMETER') " ORDER BY u.name; } if match($ORACLE_VERSION,'^(102|11)') {var $TTL[23] = '---+ Event Histogram Information' var $HDR[23] = '|*Time* |*Event* | *Wait Time (Milliseconds)*| *Wait Count*|' append $sql {PROMPT ___Macro_separator(23)___ "SELECT '|' || " TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS') || ' |' || " event || ' | ' || " wait_time_milli || '| ' || " wait_count || '|' " FROM v$event_histogram " WHERE wait_count > 0; } } if match($ORACLE_VERSION,'^102') {var $TTL[24] = '---+ Unsupported Columns' var $HDR[24] = '|*Owner* |*Table Name* |*Column Name*|*Data Type* |' append $sql {PROMPT ___Macro_separator(24)___ "SELECT '|' || " owner || ' |' || " table_name || ' |' || " column_name || ' |' || " data_type || ' |' " FROM (SELECT col.owner owner, " col.table_name table_name, " col.column_name column_name, " col.data_type data_type " FROM dba_tab_cols col, " dba_tables tbl " WHERE col.owner NOT IN (SELECT owner " FROM dba_logstdby_skip " WHERE statement_opt = 'INTERNAL SCHEMA') " AND col.data_type NOT IN ('BINARY_DOUBLE','BINARY_FLOAT','FLOAT', " 'BLOB','CLOB','CHAR','DATE','LONG', " 'LONG RAW','NCHAR','NCLOB','NUMBER', " 'NVARCHAR2','RAW','VARCHAR','VARCHAR2') " AND col.data_type NOT LIKE 'TIMESTAMP%' " AND col.data_type NOT LIKE 'INTERVAL%' " AND col.owner = tbl.owner " AND col.table_name = tbl.table_name " AND tbl.secondary != 'Y' " UNION " SELECT owner, " table_name, " column_name, " data_type " FROM dba_logstdby_unsupported) " ORDER BY owner,table_name,column_name; } } elsif match($ORACLE_VERSION,'^11') {var $TTL[24] = '---+ Unsupported Columns' var $HDR[24] = '|*Owner* |*Table Name* |*Column Name*|*Data Type* |' append $sql {PROMPT ___Macro_separator(24)___ "SELECT '|' || " owner || ' |' || " table_name || ' |' || " column_name || ' |' || " data_type || ' |' " FROM dba_logstdby_unsupported " ORDER BY owner,table_name,column_name; } } call separator(1) call writeSql(bindSql($sql,$col2,$col3,$col4,$col5,$col6,$col7,$col8,$col9,\ $col10,$col12,$col13,$col18,$col19,$col20,$col21),5) call separator(0,'Logical Standby Information') } =head2 Data Guard Broker Information Provides the Data Guard Broker information. =head2 config - Data Guard Broker Configuration Displays a detailed summary about the broker configuration. =cut debug ' Inside DG module, gathering Data Guard Broker information' pretoc '2:Data Guard Broker Information' set $sql {SELECT '| ' || " dataguard_broker " FROM v$database; } if grepSql($sql,'ENABLED','f') {var $usr = getSetting('DG_BROKER_USER','sys') # Determine the user password if compare('eq',$usr,'/') var ($usr,$pwd) = ('','') elsif getSetting('SQL_PASSWORD_DG_BROKER_USER') var ($pwd) = unpack('u',last) else var $pwd = askPassword(concat("Enter '",$usr,"' password for dgmgrl:")) # Execute the command var $out = getTemp('dg','.txt') var $cmd = concat(catCommand(getSetting('ORACLE_HOME'),'bin','dgmgrl'),' >',\ quote($out,'x')) output P,$cmd write 'CONNECT ',$usr,'/',$pwd,';' write 'SHOW CONFIGURATION VERBOSE;' write 'exit;' close report config debug ' Inside DG module, gathering Data Guard broker configuration' prefix {write '---+!! Data Guard Configuration Information' write '---## Using in dgmgrl: SHOW CONFIGURATION VERBOSE' } call writeFile($out) if hasOutput(true) {write $TOP toc '3:[[',getFile(),'][rda_report][Data Guard Broker Configuration]]' } # Collect database list var @tbl = grepFile($out,'\w+\s+\-.*database$','i') call unlinkTemp('dg') =head2 site - Data Guard Site Information Displays a detailed summary of the available databases if the Data Guard broker is enabled. =cut if @tbl {var $out = getTemp('dg','.txt') var $str = cond(match($ORACLE_VERSION,'^(10|11)'),'SHOW DATABASE VERBOSE ',\ 'SHOW SITE VERBOSE ') output P,$cmd write 'CONNECT ',$usr,'/',$pwd,';' loop $lin (@tbl) write $str,trim(field('-',0,$lin)),';' write 'exit;' close report site debug ' Inside DG module, gathering Data Guard site information' prefix {write '---+!! Data Guard Site Information' write '---## Using in dgmgrl: ',$str } call writeFile($out) if hasOutput(true) {write $TOP toc '3:[[',getFile(),'][rda_report][Data Guard Site Information]]' } call unlinkTemp('dg') } } =head1 SEE ALSO L, L, L =begin credits =over 10 =item RDA 4.7: Rajendra Ghatge, Somnath Gund, Brandie Smith. =item RDA 4.14: Frank Kobylanski, Philominraj Periyanayagam. =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002-2009, Oracle. All rights reserved. =head1 TRADEMARK NOTICE Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. =cut