Difference between revisions of "Dataguard"

Revision as of 15:26, 10 November 2012

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

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;

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#