René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Verify standby environment

Physical standby database

Count the existing archived logs n the standby database: 
select count(*) from v$archived_log;
Then archive the current log on the primary database: 
alter system archive log current;
Then, count the archived logs on the standby database again. It should have been increased by one.

Level of synchronization

To see if there is a problem with the archiving destinations (such as that destionation that transmits logs), quere v$archive_dest_status. 
select archived_thread#, archived_seq#, applied_thread#, applied_seq# from v$archive_dest_status;

Show received logs

On a physical standby database, the received archived redo logs can be displayed like this: 
select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log;

Show applied archived redo log

select thread#, sequence#, first_change#, next_change# from v$log_history;

Show messages

Use v$dataguard_status to display messages. dest_id refers to what is configured with log_archive_dest_n. 
select message from v$dataguard_status where dest_id = 2;
The query was executed on the primary database, and it shows that the destination 2 cannot deliver its logs. 
ARCH: Error 12535 Creating archive log file to 'to_standby'
ARCH: Error 12535 Creating archive log file to 'to_standby'
ARCH: Error 12535 Creating archive log file to 'to_standby'
Broken network connections: If the network connection is broken, the standby database writes RFS: Possible network disconnect with primary database. The primary database writes: Network asynch I/O wait error 3114 log 3 service 'to_standby'.

Archived logs that are not transmitted

The following query assumes that the archives are locally stored on destination 1 and sent to the remote server on destionation 2. 
select
  substr(local.name,1,50)  "Archive Name",
  case when remote.sequence# is null then 'NOT TRANSMITTED' 
                                     else 'transmitted'
  end,
  local.sequence#,
  local.thread#
from
  (select * from v$archived_log where dest_id = 1) local 
                                    left join 
  (select * from v$archived_log where dest_id = 2) remote
  on local.sequence# = remote.sequence# and
     local.thread#   = remote.thread#
  order by local.sequence#;

Is standby database performing managed recovery

select process, status from v$managed_standby;
If there is a MRP or a MRP0 process, the database is performing managed recovery.

Monitoring the recovery process

select process, status, thread#, sequence#, block#, blocks from v$managed_standby;