Friday, May 6, 2011

Checking Dataguard instances

If you have Oracle databases configured with Dataguard, you may want to be sure everything is going fine and archives are transmitted to and loaded in your standby database. You can do this easily executing this script in your primary instance:

COLUMN ARCHIVE_NAME FORMAT A35
COLUMN TRANSMITTED FORMAT A18
COLUMN STDBY_APPLIED FORMAT A10
COLUMN PRI_ARCH, FORMAT A10
COLUMN STDBY_DEST FORMAT A10

select case when remote.sequence# is null
then 'NOT TRANSMITTED!'
else 'transmitted'
end as TRANSMITTED,
remote.applied as STDBY_APPLIED,
local.sequence# PRI_SEQ#,
local.archived PRI_ARCH,
remote.standby_dest STDBY_DEST,
remote.sequence# STDBY_SEQ#,
current_seq#, status_db
from (
(select *
from (
select v$instance.status as status_db,instance_role,
sequence# current_seq#, (sequence#-11) secuencia
from v$log, v$instance
where v$log.status = 'CURRENT'
), v$archived_log l
where dest_id = 1
and l.sequence# > secuencia) 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# desc;

The meaning of the fields is:

TRANSMITTED if the archive log was transferred to the standby instance
STDBY_APPLIED if the archive log was applied to the standby database
PRI_SEQ# primary's log sequence number
PRI_ARCH if the log was archived
STDBY_SEQ# standby's log sequence number
CURRENT_SEQ# current sequence number
STATUS_DB status of the primary's instance

As you can see in this example, the standby database is up to date and everything is fine:

TRANSMITTED STDBY_APPL PRI_SEQ# PRI STDBY_DEST STDBY_SEQ# CURRENT_SEQ# STATUS_DB
------------------ ---------- ---------- --- ---------- ---------- ------------ ------------
transmitted YES 788 YES YES 788 789 OPEN
transmitted YES 787 YES YES 787 789 OPEN
transmitted YES 786 YES YES 786 789 OPEN
transmitted YES 785 YES YES 785 789 OPEN
transmitted YES 784 YES YES 784 789 OPEN
transmitted YES 783 YES YES 783 789 OPEN
transmitted YES 782 YES YES 782 789 OPEN
transmitted YES 781 YES YES 781 789 OPEN
transmitted YES 780 YES YES 780 789 OPEN
transmitted YES 779 YES YES 779 789 OPEN

10 rows selected.

In this example, all the archive logs have been transferred to the standby database but not applied, in this case because the standby database is open in read-only mode:

TRANSMITTED STDBY_APPL PRI_SEQ# PRI STDBY_DEST STDBY_SEQ# CURRENT_SEQ# STATUS_DB
------------------ ---------- ---------- --- ---------- ---------- ------------ ------------
transmitted NO 387 YES YES 387 388 OPEN
transmitted NO 386 YES YES 386 388 OPEN
transmitted NO 385 YES YES 385 388 OPEN
transmitted NO 384 YES YES 384 388 OPEN
transmitted NO 383 YES YES 383 388 OPEN
transmitted NO 382 YES YES 382 388 OPEN
transmitted NO 381 YES YES 381 388 OPEN
transmitted NO 380 YES YES 380 388 OPEN
transmitted NO 379 YES YES 379 388 OPEN
transmitted NO 378 YES YES 378 388 OPEN

10 rows selected.

This script works as long as your LOG_ARCHIVE_DEST_2 parameter points to the standby database.

And just for the record not everything in this blog is my idea, for example this script was the idea of a fellow DBA. Sometimes I get ideas from the Internet, sometimes from my coworkers, and believe it or not, sometimes I have my own ideas!

No comments:

Post a Comment