Monday, May 4, 2015

Verify Oracle Standby Database Synchronization

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