# S405DG.def: Collects Data Guard Information # $Id: S405DG.def,v 1.7 2008/02/25 17:32:56 mschenke Exp $ # ARCS: $Header: /home/cvs/repository/RDA_4/src/scripting/lib/modules/S405DG.def,v 1.7 2008/02/25 17:32:56 mschenke Exp $ # # Change History # 20080225 MSC Change the copyright notice. =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 $TOC = '%TOC%' var $TOP = '[[#Top][Back to top]]' toc '1:Data Guard' # Load the common macros run DBinfo() # 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 the DG module, gathering basic database information' report basic var $TTL = '---+!! Basic Database Information' var @TTL = ('',\ '---+ Database Information',\ '---+ RAC Configuration') if match($ORACLE_VERSION,'10|11') {var $HDR[1] = '| *DB Id*|*DB Unique Name*| *Activation#*|*DB Role*|\ *Switchover Status*|' set $sql {SELECT '| ' || " dbid || '|' || " db_unique_name || ' | ' || " activation# || '|' || " database_role || ' | ' || " switchover_status || ' |' " FROM v$database; } var $rol = field('\|',4,grepSql($sql,'.','f')) } else {var $HDR[1] = '| *DB Id*| *Activation#*|*DB Role*|*Switchover Status*|' set $sql {SELECT '| ' || " dbid || '| ' || " activation# || '|' || " database_role || ' | ' || " switchover_status || ' |' " FROM v$database; } var $rol = field('\|',3,grepSql($sql,'.','f')) } var ($HDR[2],$col2) = getSqlColumns('RDA','','V$PARAMETER') call clearSqlColumns('RDA') append $sql {PROMPT ___Macro_separator(2)___ "SELECT :1 " FROM v$parameter " WHERE name = 'cluster_database'; } call separator(1) call writeSql(bindSql($sql,$col2),2) call separator(0,'Basic Database Information') =head2 primary - Primary Database Information Provides the primary database information. =cut if match($rol,'PRIMARY') {debug ' Inside the 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') var ($HDR[1],$col1) = getSqlColumns('RDA','','V$ARCHIVE_DEST') call clearSqlColumns('RDA') var ($HDR[2],$col2) = getSqlColumns('RDA','','V$ARCHIVE_DEST_STATUS') call clearSqlColumns('RDA') var ($HDR[4],$col4) = getSqlColumns('RDA','','V$DATAGUARD_STATUS') var $col4 = replace($col4,'\bmessage\b',"REPLACE(message,CHR(10),'%BR%')",true) call clearSqlColumns('RDA') var ($HDR[5],$col5) = getSqlColumns('RDA','','V$STANDBY_LOG') call clearSqlColumns('RDA') var ($HDR[6],$col6) = getSqlColumns('RDA','','V$ARCHIVED_LOG') call clearSqlColumns('RDA') set $sql {SELECT :1 " FROM v$archive_dest; "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; } } append $sql {PROMPT ___Macro_separator(4)___ "SELECT :3 " FROM v$dataguard_status; "PROMPT ___Macro_separator(5)___ "SELECT :4 " FROM v$standby_log; "PROMPT ___Macro_separator(6)___ "SELECT :5 " FROM v$archived_log; } call separator(1) call writeSql(bindSql($sql,$col1,$col2,$col4,$col5,$col6),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 the DG module, gathering physical standby database information' report physical var $TTL = '---+!! Physical Standby Information' var @TTL = ('',\ '---+ Data Guard Features Enabled',\ '---+ Archive Dest Information',\ '---+ Archive Dest Status',\ '---+ Redo Receive Status',\ '---+ Redo Apply Status',\ '---+ Standby Redo Log information',\ '---+ Data Guard Status',\ '---+ Archive Gap Information',\ '---+ Archival History') 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; } } var ($HDR[2],$col2) = getSqlColumns('RDA','','V$ARCHIVE_DEST') call clearSqlColumns('RDA') var ($HDR[3],$col3) = getSqlColumns('RDA','','V$ARCHIVE_DEST_STATUS') call clearSqlColumns('RDA') var ($HDR[4],$col4) = getSqlColumns('RDA','','V$MANAGED_STANDBY') call clearSqlColumns('RDA') var $HDR[5] = $HDR[4] var ($HDR[6],$col6) = getSqlColumns('RDA','','V$STANDBY_LOG') call clearSqlColumns('RDA') var ($HDR[7],$col7) = getSqlColumns('RDA','','V$DATAGUARD_STATUS') var $col7 = replace($col7,'\bmessage\b',"REPLACE(message,CHR(10),'%BR%')",true) call clearSqlColumns('RDA') var ($HDR[8],$col8) = getSqlColumns('RDA','','V$ARCHIVE_GAP') call clearSqlColumns('RDA') var ($HDR[9],$col9) = getSqlColumns('RDA','','V$ARCHIVED_LOG') call clearSqlColumns('RDA') append $sql {PROMPT ___Macro_separator(2)___ "SELECT :1 " FROM v$archive_dest; "PROMPT ___Macro_separator(3)___ "SELECT :2 " FROM v$archive_dest_status; "PROMPT ___Macro_separator(4)___ "SELECT :3 " FROM v$managed_standby " WHERE process IN ('RFS','ARCH'); "PROMPT ___Macro_separator(5)___ "SELECT :4 " FROM v$managed_standby " WHERE process LIKE 'MRP%'; "PROMPT ___Macro_separator(6)___ "SELECT :5 " FROM v$standby_log; "PROMPT ___Macro_separator(7)___ "SELECT :6 " FROM v$dataguard_status; "PROMPT ___Macro_separator(8)___ "SELECT :7 " FROM v$archive_gap; "PROMPT ___Macro_separator(9)___ "SELECT :8 " FROM v$archived_log; } call separator(1) call writeSql(bindSql($sql,$col2,$col3,$col4,$col4,$col6,$col7,$col8,$col9),2) 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 the DG module, gathering logical standby database information \ (can take time) ' report logical var $TTL = '---+!! Logical Standby Information' var @TTL = ('',\ '---+ Data Guard Features Enabled',\ '---+ 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',\ 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; } } var ($HDR[2],$col2) = getSqlColumns('RDA','','V$ARCHIVE_DEST') call clearSqlColumns('RDA') var ($HDR[3],$col3) = getSqlColumns('RDA','','V$ARCHIVE_DEST_STATUS') call clearSqlColumns('RDA') var ($HDR[4],$col4) = getSqlColumns('RDA','','V$MANAGED_STANDBY') call clearSqlColumns('RDA') var ($HDR[6],$col6) = getSqlColumns('RDA','','DBA_LOGSTDBY_PARAMETERS') call clearSqlColumns('RDA') var ($HDR[8],$col8) = getSqlColumns('RDA','','V$LOGSTDBY_STATS') call clearSqlColumns('RDA') var $HDR[9] = '|*Process*|*Server ID*|*SQL Text*|' var ($HDR[10],$col10) = getSqlColumns('RDA','','V$DATAGUARD_STATUS') var $col10 = replace($col10,'\bmessage\b',"REPLACE(message,CHR(10),'%BR%')",\ true) call clearSqlColumns('RDA') var ($HDR[11],$col11) = getSqlColumns('RDA','','DBA_LOGSTDBY_LOG') call clearSqlColumns('RDA') append $sql {PROMPT ___Macro_separator(2)___ "SELECT :1 " FROM v$archive_dest; "PROMPT ___Macro_separator(3)___ "SELECT :2 " FROM v$archive_dest_status; "PROMPT ___Macro_separator(4)___ "SELECT :3 " FROM v$managed_standby " WHERE process IN ('RFS','ARCH'); } if match($ORACLE_VERSION,'10|11') {var ($HDR[5],$col5) = getSqlColumns('RDA','','V$STANDBY_LOG') call clearSqlColumns('RDA') append $sql {PROMPT ___Macro_separator(5)___ "SELECT :4 " FROM v$standby_log; } } append $sql {PROMPT ___Macro_separator(6)___ "SELECT :5 " FROM dba_logstdby_parameters; } if match($ORACLE_VERSION,'10|11') {var ($HDR[7],$col7) = getSqlColumns('RDA','','V$LOGSTDBY_PROCESS') call clearSqlColumns('RDA') append $sql {PROMPT ___Macro_separator(12)___ "PROMPT ---+ Redo Apply status "PROMPT ___Macro_separator(7)___ "SELECT :6 " FROM v$logstdby_process; "EXECUTE DBMS_LOCK.SLEEP(30); "PROMPT ___Macro_separator(7)___ "SELECT :6 " FROM v$logstdby_process; "EXECUTE DBMS_LOCK.SLEEP(30); "PROMPT ___Macro_separator(7)___ "SELECT :6 " FROM v$logstdby_process; } } else {var ($HDR[7],$col7) = getSqlColumns('RDA','','V$LOGSTDBY') call clearSqlColumns('RDA') append $sql {PROMPT ___Macro_separator(12)___ "PROMPT ---+ Redo Apply status PROMPT ___Macro_separator(7)___ "SELECT :6 " FROM v$logstdby; "EXECUTE DBMS_LOCK.SLEEP(30); "PROMPT ___Macro_separator(7)___ "SELECT :6 " FROM v$logstdby; "EXECUTE DBMS_LOCK.SLEEP(30); "PROMPT ___Macro_separator(7)___ "SELECT :6 " FROM v$logstdby; } } append $sql {PROMPT ___Macro_separator(12)___ "PROMPT ---+ Logical Statistics "PROMPT ___Macro_separator(8)___ "SELECT :7 " FROM v$logstdby_stats; "EXECUTE DBMS_LOCK.SLEEP(30); "PROMPT ___Macro_separator(8)___ "SELECT :7 " FROM v$logstdby_stats; "EXECUTE DBMS_LOCK.SLEEP(30); "PROMPT ___Macro_separator(8)___ "SELECT :7 " FROM v$logstdby_stats; "PROMPT ___Macro_separator(9)___ "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; "PROMPT ___Macro_separator(10)___ "SELECT :8 " FROM v$dataguard_status; "PROMPT ___Macro_separator(11)___ "SELECT :9 " FROM dba_logstdby_log; } call separator(1) call writeSql(bindSql($sql,$col2,$col3,$col4,$col5,$col6,$col7,$col8,\ $col10,$col11),2) 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 the 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') {# Determine the password if getSetting('SQL_PASSWORD_DG_BROKER_USER') var ($pwd) = unpack('u',last) else var $pwd = askPassword("Enter 'sys' 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 sys/',$pwd,';' write 'SHOW CONFIGURATION VERBOSE;' write 'exit;' close report config debug ' Inside the 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 sys/',$pwd,';' loop $lin (@tbl) write $str,trim(field('-',0,$lin)),';' write 'exit;' close report site debug ' Inside the 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 See also L, L, L. =begin credits =over 10 =item RDA 4.7: Rajendra Ghatge, Somnath Gund, Brandie Smith. =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002-2008, 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