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;