Follow the below steps to verify Oracle Standby Database Synchronization that was prepared using Oracle DataGuard Technology.
Firstly, count the existing archived logs in the standby database
select count(*) from v$archived_log;
Then archive the current log on the primary database:
alter system switch logfile current;
Then count the number of archived logs on the standby database again and you will see it has increased by one.
Level of synchronization
To see if there is a problem with the archiving destinations (such as that destionation that transmits logs), query 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 as,
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 transmittedThe 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#;
To check if standby database performing managed recovery or not
select process, status from v$managed_standby;If there is a MRP or a MRP0 process, the database is performing managed recovery.
If recovery is not running then execute the below statement to start managed recovery,
alter database recovery managed standby database using current logfile disconnect from session;
Monitoring the recovery process
select process, status, thread#, sequence#, block#, blocks from v$managed_standby;
My previous articles on Dataguard and Manual Standby configurations,
Views Pertinent to DataGuard Configurations
http://samiora.blogspot.ae/2010_12_01_archive.html
Non-Dataguard Standby for RAC primary using RMAN
http://samiora.blogspot.ae/2012/11/oracle-manual-single-instance-standby.html
Create 11g Clone or Standby Database using RMAN
http://samiora.blogspot.ae/2012/11/oracle-11g-standby-or-clone-database.html
Physical Standby database using Dataguard
http://samiora.blogspot.ae/2010/10/physical-standb-database-using.html
Alternative for standby/dataguard - Manual
http://samiora.blogspot.ae/2009/02/alternative-for-standbydataguard-manual.html
For any queries on Oracle DataGuard Technology and its Setup, Please don't hesitate to email me on samiora@gmail.com
