|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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;
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;