Scriptto Collect Data Guard Primary Site Diagnostic Information for Version 10g andabove (Including RAC). (文档ID 1577401.1)
oracle mos 提供了一个不错的dg 检测脚本，基本囊括了所有 主库 dg的检测项：
如下报表：
| TIME |
|---|
| 14-MAR-2014 13:53:18 |
| Database 1 |
|---|
| In the following output the DATABASE_ROLE should be PRIMARY as that is what this script is intended to be run on. PLATFORM_ID should match the PLATFORM_ID of the standby(s) or conform to the supported options in Note: 413484.1 Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration Note: 1085687.1 Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration OPEN_MODE should be READ WRITE. LOG_MODE should be ARCHIVELOG. FLASHBACK can be YES (recommended) or NO. If PROTECTION_LEVEL is different from PROTECTION_MODE then for some reason the mode listed in PROTECTION_MODE experienced a need to downgrade. Once the error condition has been corrected the PROTECTION_LEVEL should match the PROTECTION_MODE after the next log switch. |
| ROLE | NAME | DB_UNIQUE_NAME | PLATFORM_ID | OPEN_MODE | LOG_MODE | FLASHBACK_ON | PROTECTION_MODE | PROTECTION_LEVEL |
|---|---|---|---|---|---|---|---|---|
| PRIMARY | ORA11G | ora11g | 13 | READ WRITE | ARCHIVELOG | NO | MAXIMUM PERFORMANCE | MAXIMUM PERFORMANCE |
| Database 2 |
|---|
| FORCE_LOGGING is not mandatory but is recommended. REMOTE_ARCHIVE should be ENABLE. SUPPLEMENTAL_LOG_DATA_PK and SUPPLEMENTAL_LOG_DATA_UI must be enabled if the standby associated with this primary is a logical standby. During normal operations it is acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or TO STANDBY. DG_BROKER can be ENABLED (recommended) or DISABLED. |
| FORCE_LOGGING | REMOTE_ARCHIVE | SUPPLEMENTAL_LOG_DATA_PK | SUPPLEMENTAL_LOG_DATA_UI | SWITCHOVER_STATUS | DATAGUARD_BROKER |
|---|---|---|---|---|---|
| YES | ENABLED | NO | NO | TO STANDBY | DISABLED |
| Database 3 |
|---|
| The following query gives us information about catpatch. From this we can tell if the catalog version doesn't match the image version it was started with. |
| VERSION | MODIFIED | STATUS |
|---|---|---|
| 11.2.0.1.0 | 15-AUG-2009 00:50:10 | VALID |
| Threads |
|---|
| Check how many threads are enabled and started for this database. If the number of instances below does not match then not all instances are up. |
| THREAD# | INSTANCE | STATUS |
|---|---|---|
| 1 | ora11g | OPEN |
| Instances |
|---|
| The number of instances returned below is the number currently running. If it does not match the number returned in Threads above then not all instances are up. VERSION should match the version from CATPROC above. ARCHIVER can be (STOPPED | STARTED | FAILED). FAILED means that the archiver failed to archive a log last time, but will try again within 5 minutes. LOG_SWITCH_WAIT the ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for. Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online redo log, then the value is NULL. |
| THREAD# | INSTANCE_NAME | HOST_NAME | VERSION | ARCHIVER | LOG_SWITCH_WAIT |
|---|---|---|---|---|---|
| 1 | ora11g | localhost.localdomain | 11.2.0.1.0 | STARTED |
| Log Switches |
|---|
| Check how often logs are switching. Log switches should not regularly be occuring in < 20 mins. Excessive log switching is a performance overhead. Whilst rapid log switching is not in itself a Data Guard issue it can affect Data guard. It may also indicate a problem with log shipping. Use redo log size = 4GB or redo log size >= peak redo rate x 20 minutes. |
| LOG_SWITCHES_UNDER_20_MINS | LOG_SWITCHES_OVER_20_MINS |
|---|---|
| 89 | 119 |
| MINUTES | LOG_SWITCHES |
|---|---|
| <= 5 | 82 |
| 6 TO 10 | 2 |
| 11 TO 15 | 3 |
| 16 TO 20 | 2 |
| 21 TO 25 | 4 |
| 26 TO 30 | 3 |
| 31 TO 35 | 2 |
| 36 TO 40 | 1 |
| >= 41 | 109 |
| Online Redo Logs |
|---|
| Check the number and size of online redo logs on each thread. |
| THREAD# | GROUP# | SEQUENCE# | BYTES | ARCHIV | STATUS |
|---|---|---|---|---|---|
| 1 | 1 | 638 | 52428800 | YES | INACTIVE |
| 1 | 3 | 639 | 52428800 | NO | CURRENT |
2 rows selected.
| Standby Redo Logs |
|---|
| The following query is run to see if standby redo logs have been created in preparation for switchover. The standby redo logs should be the same size as the online redo logs. There should be (( # of online logs per thread + 1) * # of threads) standby redo logs. A value of 0 for the thread# means the log has never been allocated. |
| THREAD# | GROUP# | SEQUENCE# | BYTES | ARCHIV | STATUS |
|---|---|---|---|---|---|
| 0 | 4 | 0 | 52428800 | YES | UNASSIGNED |
| 0 | 5 | 0 | 52428800 | YES | UNASSIGNED |
| 0 | 6 | 0 | 52428800 | YES | UNASSIGNED |
| 0 | 7 | 0 | 52428800 | YES | UNASSIGNED |
4 rows selected.
| Archive Destinations |
|---|
| This query produces a list of defined archive destinations. It shows if they are enabled, what process is servicing that destination, if the destination is local or remote, and if remote what the current mount ID is. |
| THREAD# | DEST_ID | DESTINATION | STATUS | TARGET | SCHEDULE | PROCESS | MID |
|---|---|---|---|---|---|---|---|
| 1 | 1 | /u01/app/ora11/flash_recovery_area | VALID | PRIMARY | ACTIVE | ARCH | 0 |
| 1 | 2 | orcl | VALID | STANDBY | ACTIVE | LGWR | 0 |
| Archive Destination Options |
|---|
| This select will give further detail on the destinations as to what options have been set. Register indicates whether or not the archived redo log is registered in the remote destination control file. |
| THREAD# | DEST_ID | ARCHIVER | TRANSMIT_MODE | AFFIRM | ASYNC_BLOCKS | NET_TIMEOUT | DELAY_MINS | REOPEN | REGIST | BINDING |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | ARCH | SYNCHRONOUS | NO | 0 | 0 | 0 | 300 | YES | OPTIONAL |
| 1 | 2 | LGWR | ASYNCHRONOUS | NO | 61440 | 30 | 0 | 300 | YES | OPTIONAL |
| Archive Destination Errors |
|---|
| The following select will show any errors that occured the last time an attempt to archive to the destination was attempted. If ERROR is blank and status is VALID then the archive completed correctly. |
| THREAD# | DEST_ID | STATUS | ERROR |
|---|---|---|---|
| 1 | 1 | VALID | |
| 1 | 2 | VALID |
| Data Guard Status |
|---|
| The query below will determine if any error conditions have been reached by querying the v$dataguard_status view (view only available in 9.2.0 and above). |
no rows selected
| Managed Standby Status |
|---|
| Query v$managed_standby to see the status of processes involved in the shipping redo on this system. Does not include processes needed to apply redo. |
| INST_ID | THREAD# | PROCESS | PID | STATUS | CLIENT_PROCESS | CLIENT_PID | SEQUENCE# | BLOCK# | ACTIVE_AGENTS | KNOWN_AGENTS |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | ARCH | 9215 | CLOSING | ARCH | 9215 | 637 | 88064 | 0 | 0 |
| 1 | 1 | ARCH | 9217 | CLOSING | ARCH | 9217 | 638 | 81920 | 0 | 0 |
| 1 | 1 | ARCH | 9219 | CLOSING | ARCH | 9219 | 569 | 1 | 0 | 0 |
| 1 | 1 | ARCH | 9221 | CLOSING | ARCH | 9221 | 636 | 94208 | 0 | 0 |
| 1 | 1 | LNS | 9223 | WRITING | LNS | 9223 | 639 | 61750 | 0 | 0 |
| Archived Sequences |
|---|
| The following query will determine the current sequence number and the last sequence archived. If you are remotely archiving using the LGWR process then the archived sequence should be one higher than the current sequence. If remotely archiving using the ARCH process then the archived sequence should be equal to the current sequence. The applied sequence information is updated at log switch time. |
| THREAD# | DEST_ID | Current Sequence | Last Archived |
|---|---|---|---|
| 1 | 1 | 638 | 638 |
| 1 | 2 | 638 | 639 |
| Archive Destination Status |
|---|
| The following select will attempt to gather as much information as possible from the standby. Standby redo logs are not supported with Logical Standby until Version 10.1. The ARCHIVED_SEQUENCE# from a logical standby is the sequence# created by the apply, not the sequence# sent from the primary. |
| DEST_ID | DATABASE_MODE | RECOVERY_MODE | PROTECTION_MODE | STANDBY_LOGFILE_COUNT | STANDBY_LOGFILE_ACTIVE | ARCHIVED_SEQ# |
|---|---|---|---|---|---|---|
| 1 | OPEN | IDLE | MAXIMUM PERFORMANCE | 0 | 0 | 638 |
| 2 | MOUNTED-STANDBY | IDLE | MAXIMUM PERFORMANCE | 4 | 1 | 638 |
| Non Default init Parameters |
|---|
| Non-default init parameters. For a RAC DB Thread# = * means the value is the same for all threads (SID=*) Threads with different values are shown with their individual thread# and values. |
| THREAD# | NAME | VALUE |
|---|---|---|
| * | processes | 150 |
| * | memory_target | 817889280 |
| * | control_files | /u01/app/ora11/oradata/ORA11G/controlfile/o1_mf_907t9hd6_.ctl |
| * | db_block_size | 8192 |
| * | compatible | 11.2.0.0.0 |
| * | log_archive_dest_1 | LOCATION=/u01/app/ora11/flash_recovery_area valid_for=(all_logfiles,all_roles) db_unique_name=ora11g |
| * | log_archive_dest_2 | SERVICE=orcl lgwr async valid_for=(online_logfile,primary_role) db_unique_name=orcl |
| * | fal_client | ora11g |
| * | fal_server | orcl |
| * | log_archive_config | DG_CONFIG=(ora11g,orcl) |
| * | db_create_file_dest | /u01/app/ora11/oradata |
| * | db_recovery_file_dest | /u01/app/ora11/flash_recovery_area |
| * | db_recovery_file_dest_size | 4070572032 |
| * | standby_file_management | AUTO |
| * | undo_tablespace | UNDOTBS1 |
| * | remote_login_passwordfile | EXCLUSIVE |
| * | db_domain | |
| * | dispatchers | (PROTOCOL=TCP) (SERVICE=ora11gXDB) |
| * | audit_file_dest | /u01/app/ora11/admin/ora11g/adump |
| * | audit_trail | DB |
| * | db_name | ora11g |
| * | open_cursors | 300 |
| * | dg_broker_start | TRUE |
| * | diagnostic_dest | /u01/app/ora11 |