Difference between revisions of "Dataguard"

Revision as of 14:43, 15 November 2013

Start up a physical standby

startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect; 

Stop a physical standby

alter database recover managed standby database cancel;

Register a missing logfile

alter database register physical logfile '<fullpath/filename>';

Check which logs are missing

Assuming dest_2 is the primary

select local.thread#
,      local.sequence# from 
       (select thread#
       ,       sequence# 
       from    v$archived_log 
       where dest_id=1) local 
where  local.sequence# not in 
       (select sequence#
       from v$archived_log
       where dest_id=2 and 
       thread# = local.thread#);

Stop/Start log file shipping

On the primary

alter system set log_archive_dest_state_2 = 'defer';
alter system set log_archive_dest_state_2 = 'enable';

Start FAL tracing on the primary

alter system set LOG_ARCHIVE_TRACE = 128;

Stop the DataGuard broker

alter system set dg_broker_start=false;

Are we a standby?

select database_role
from   v$database;

See how up-to-date a standby is

Run this on the primary

set numwidth 15
select max(sequence#) current_seq
from   v$log;

Run this on the standby

set numwidth 15
select max(applied_seq#) last_seq
from   v$archive_dest_status;

select arch.thread# "Thread"
,      arch.sequence# "Last Sequence Received"
,      appl.sequence# "Last Sequence Applied"
,      (arch.sequence# - appl.sequence#) "Difference"
from   (
       select thread#
       ,      sequence#
       from   v$archived_log
       where  1=1
       and    ( thread#,first_time ) in (
                                        select thread#, max(first_time)
                                        from   v$archived_log
                                        group  by thread#
                                        )
       ) arch
       ,
       (
       select thread#
       ,      sequence#
       from   v$log_history
       where  1=1
       and    ( thread#,first_time ) in ( select thread# ,max(first_time)
                                          from   v$log_history
                                          group  by thread#
                                         )
       ) appl
where  1=1
and    arch.thread# = appl.thread#
order  by 1;

Show info on all log destinations

Run this on the primary

set lines 100
set numwidth 15
column ID format 99
column "SRLs" format 99 
column active format 99 
col type format a4

select ds.dest_id id
,      ad.status
,      ds.database_mode db_mode
,      ad.archiver type
,      ds.recovery_mode
,      ds.protection_mode
,      ds.standby_logfile_count "SRLs"
,      ds.standby_logfile_active active
,      ds.archived_seq#
from   v$archive_dest_status   ds
,      v$archive_dest          ad
where  1=1
and    ds.dest_id = ad.dest_id
and    ad.status != 'INACTIVE'
order  by ds.dest_id;

Display log destinations options

Run this on the standby

set numwidth 8
set lines 100
column id format 99

select dest_id id
,      archiver
,      transmit_mode
,      affirm
,      async_blocks  async
,      net_timeout   net_time
,      delay_mins    delay
,      reopen_secs   reopen
,      register
,      binding 
from   v$archive_dest
order  by dest_id;

Show any standby logs

set lines 100
set pages 999
col member format a70
select st.group#
,      st.sequence#
,      ceil(st.bytes/1048576) mb
,      lf.member
from   v$standby_log   st
,      v$logfile       lf
where  1=1
and    st.group# = lf.group#

Other resources

• Log Gap Detection and Resolution - Jeff Hunter
• http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php
• Oracle 10g - Manually Create a Physical Standby Database Using Data Guard