Query 1: protection_level should match the protection_mode after the next log switch
select name,database_role role,log_mode, protection_mode,protection_level from v$database;
NAME ROLE LOG_MODE PROTECTION_MODE PROTECTION_LEVEL
TEST PRIMARY ARCHIVELOG MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
1 row selected.
Query 2: 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 value is NULL
select instance_name,host_name,version,archiver,log_switch_wait from v$instance;
INSTANCE_NAME HOST_NAME VERSION ARCHIVE LOG_SWITCH_
TEST flex-suntdb 9.2.0.5.0 STARTED
1 row selected.
Query 3: Query give us information about catpatch.
select version, modified, status from dba_registry where comp_id = 'CATPROC';
VERSION MODIFIED STATUS
9.2.0.5.0 19-NOV-2004 10:12:27 VALID
1 row selected.
Query 4: Force logging is not mandatory but is recommended. Supplemental logging must be enabled if thestandby associated with this primary is a logical standby. During normal operations it is acceptable for SWITCHOVER_STATUS to be SESSIONS ACTIVE or TO STANDBY.
select force_logging,remote_archive,supplemental_log_data_pk,supplemental_log_data_ui, switchover_status,dataguard_broker from v$database;
FORCE_LOGGING REMOTE_ARCHIVE SUP SUP SWITCHOVER_STATUS DATAGUARD_BROKER
NO ENABLED NO NO SESSIONS ACTIVE DISABLED
1 row selected.
Query 5: This query produces a list of all 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.
select dest_id "ID",destination,status,target,schedule,process,mountid mid from v$archive_dest order by dest_id;
ID DESTINATION STATUS TARGET SCHEDULE PROCESS MID
1 /applprod/archprod VALID PRIMARY ACTIVE ARCH 0
2 STANDBY VALID STANDBY ACTIVE ARCH 0
........
........
10 rows selected.
Query 6: 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.
select dest_id "ID",archiver,transmit_mode,affirm,async_blocks async, net_timeout net_time,delay_mins delay,reopen_secs reopen, register,binding from v$archive_dest order by dest_id;
ID ARCHIVER TRANSMIT_MOD AFF ASYNC NET_TIME DELAY REOPEN REG BINDING
1 ARCH SYNCHRONOUS NO 0 0 0 300 YES MANDATORY
2 ARCH SYNCHRONOUS NO 0 0 0 300 YES OPTIONAL
...
...
10 rows selected.
Query 7: 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.
select dest_id,status,error from v$archive_dest;
DEST_ID STATUS ERROR
1 VALID
2 VALID
3 INACTIVE
.........
...........
10 rows selected.
Query 8: 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):
select message, timestamp from v$dataguard_status where severity in ('Error','Fatal') order by timestamp;
no rows selected
Query 9: 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.
select ads.dest_id,max(sequence#) "Current Sequence", max(log_sequence) "Last Archived" from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads where ad.dest_id=al.dest_id and al.dest_id=ads.dest_id group by ads.dest_id;
DEST_ID Current Sequence Last Archived
1 233 233
2 233 233
2 rows selected.
Query 10: The following select will attempt to gather as much information as possible from the standby. SRLs are not supported with Logical Standby until Version 10.1.
select dest_id id,database_mode db_mode,recovery_mode, protection_mode,standby_logfile_count "SRLs", standby_logfile_active ACTIVE, archived_seq# from v$archive_dest_status;
ID DB_MODE RECOVER PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#
1 OPEN IDLE MAXIMUM PERFORMANCE 0 0 233
2 MOUNTED-STANDBY IDLE MAXIMUM PERFORMANCE 0 0 233
...
...
10 rows selected.
Query 11: 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.
select process,status,client_process,sequence# from v$managed_standby;
PROCESS STATUS CLIENT_P SEQUENCE#
ARCH CLOSING ARCH 233
ARCH CLOSING ARCH 232
2 rows selected.
Query 12: The following query is run on the primary to see if SRL's have been created in preparation for switchover.
select group#,sequence#,bytes from v$standby_log;
no rows selected
Query 13: The above SRL's should match in number and in size with the ORL's returned below:
select group#,thread#,sequence#,bytes,archived,status from v$log;
