Difference between revisions of "Dataguard"

Revision as of 13:35, 29 July 2016

• Configuring Oracle Database 11g with Oracle Data Guard (SDU size etc...)
• Monitoring a data guard setup - datadisk.co.uk

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 standby

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

On the primary

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

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;

or

select a.thread#
,      b.last_seq
,      a.applied_seq
,      to_char(a.last_app_timestamp,'DD-MON-YYYY HH24:MI:SS') last_app_timestamp
,      b.last_seq-a.applied_seq                               arc_diff 
from   (select thread#
        ,      max(sequence#) applied_seq
        ,      max(next_time) last_app_timestamp 
        from   gv$archived_log 
        where  applied = 'YES' 
        group  by thread#
       ) a
,      (select thread#
        ,      max(sequence#) last_seq 
        from   gv$archived_log 
        group  by thread#
       ) b
where  a.thread# = b.thread#
/

Show info on all log destinations

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

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#

Dataguard broker managed standby (dgmgrl) not applying logs after restart

After restarting the Primary and Standby databases manually, the redo logs were not being applied on the standby.
The command used to start the standby database was:

alter database recover managed standby database using current logfile disconnect from session;

It seems this is not good enough, we see a problem in the broker configuration...

(0) OTMPRODS oracle@ravotm14:/home/oracle] dgmgrl  
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/********@otmprod
Connected.
DGMGRL> show configuration

Configuration - dg_otmprod

  Protection Mode: MaxPerformance
  Databases:
    otmprod  - Primary database
    otmprods - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

We need to re-enable the Standby database in the broker configuration

DGMGRL> enable database OTMPRODS;
Enabled.
DGMGRL> show configuration

Configuration - dg_otmprod

 Protection Mode: MaxPerformance
 Databases:
   otmprod  - Primary database
   otmprods - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Now check alert log. All is working again.

(Re)Build a physical standby

Run from the standby server

#!/usr/bin/ksh
# -----------
# How to call:    ./dataguard_rebuild.ksh OTMPROD              
# -----------
# Set environment Variables
##############################################
export ORACLE_SID=$1
ORAENV_ASK="NO"
. oraenv
ORAENV_ASK="YES"
echo  ' ORACLE_HOME is ==> ' $ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH

$ORACLE_HOME/bin/rman <<EORMAN >>  /oracle/${ORACLE_SID}/admin/change/rebuild_dataguard_${ORACLE_SID}_$(date +%Y%m%d_%H%M).log
connect target sys/********@OTMPROD 
connect auxiliary sys/********@OTMPRODS

DUPLICATE TARGET DATABASE 
    FOR STANDBY 
    FROM ACTIVE DATABASE 
    SPFILE 
        SET db_unique_name='OTMPRODS' 
        set LOG_ARCHIVE_DEST_1='LOCATION=/oracle/arch/OTMPROD/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=OTMPRODS' 
        SET LOG_ARCHIVE_DEST_2='service=OTMPROD LGWR ASYNC valid_for=(ONLINE_LOGFILE,Primary_ROLE) db_unique_name=OTMPROD' 
        SET FAL_SERVER='OTMPROD' 
        SET FAL_CLIENT='OTMPRODS' 
        SET LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = <standby_server>)(PORT = 1522))' 
    NOFILENAMECHECK;

EORMAN

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
• Rolling Forward a Physical Standby Database Using the RECOVER Command