Dataguard

• Configuring Oracle Database 11g with Oracle Data Guard (SDU size etc...)
• Monitoring a data guard setup - datadisk.co.uk
• Some data guard queries - emre baransel
• Arup Nanda - some good standby stuff (Active Data Guard, Snapshot Standby, Conversion from Physical to Logical Standby, Rolling Upgrade, Redo Compression...)
• Amit Saraswat - Use incremental SCN backup to allow a physical standby to catch up with primary
• Pythian - Close a big gap in physical standby using network recovery (12c)
• dba-scripts.com recover standby over network oracle 12c

Start up the apply on a physical standby

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

Stop the apply on a physical standby

alter database recover managed standby database cancel;

Register a missing logfile

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

Show apply activity on the standby

select process
,      status
,      client_process
,      sequence#
,      block#
,      active_agents
,      known_agents
from   v$managed_standby
/

shows something like this

PROCESS   STATUS       CLIENT_P  SEQUENCE#     BLOCK# ACTIVE_AGENTS KNOWN_AGENTS
--------- ------------ -------- ---------- ---------- ------------- ------------
ARCH      CLOSING      ARCH         321071      45056             0            0
ARCH      CONNECTED    ARCH              0          0             0            0
ARCH      CLOSING      ARCH         197886       6144             0            0
ARCH      CLOSING      ARCH         197887      10240             0            0
MRP0      APPLYING_LOG N/A          197888       2063            17           17
RFS       IDLE         UNKNOWN           0          0             0            0
RFS       IDLE         LGWR         197888       2063             0            0
RFS       IDLE         ARCH              0          0             0            0
RFS       RECEIVING    LGWR         321072      34506             0            0
RFS       IDLE         UNKNOWN           0          0             0            0
RFS       IDLE         UNKNOWN           0          0             0            0

11 rows selected.

See Dataguard error log messages

If data guard is not functioning correctly, check the errors in this log.

select gvi.thread#
,      timestamp
,      message
from   gv$dataguard_status gvds
,      gv$instance         gvi
where  gvds.inst_id = gvi.inst_id
and    severity     in ('Error','Fatal')
order  by timestamp
,      thread#
/

and

select thread#
,      dest_id
,      gvad.status
,      error
,      fail_sequence
from   gv$archive_dest gvad
,      gv$instance     gvi
where  gvad.inst_id = gvi.inst_id
and    destination  is not null
order  by thread#
,      dest_id
/

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;

Show various details on health of standby database

-- This script is to be run on the Standby of a Data Guard Physical Standby Site

set echo off
set feedback off
column timecol new_value tstamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hhmi') timecol from sys.dual;
column output new_value dbname
select value || '_' output from v$parameter where name = 'db_name';

-- Output the results to this file

spool dg_Standby_diag_&&dbname&&tstamp
set lines 132
set pagesize 500
set numformat 999999999999999
set trim on
set trims on

-- Get the current Date

set feedback on
select systimestamp from dual;

-- Standby Site Details
set heading off
set feedback off
select 'Standby Site Details' from dual;
select '********************' from dual;
set heading on
set feedback on

col db_unique_name format a15
col flashb_on format a10

select DB_UNIQUE_NAME,DATABASE_ROLE DB_ROLE,FORCE_LOGGING F_LOG,FLASHBACK_ON FLASHB_ON,LOG_MODE,OPEN_MODE,
       GUARD_STATUS GUARD,PROTECTION_MODE PROT_MODE
from v$database;

-- Current SCN - this value on the primary and standby sites where real time apply is in place should be nearly the same

select DB_UNIQUE_NAME,SWITCHOVER_STATUS,CURRENT_SCN from v$database;

-- Incarnation Information
--

set heading off
set feedback off
select 'Incarnation Destination Configuration' from dual;
select '*************************************' from dual;
set heading on
set feedback on

select INCARNATION# INC#, RESETLOGS_CHANGE# RS_CHANGE#, RESETLOGS_TIME, PRIOR_RESETLOGS_CHANGE# PRIOR_RS_CHANGE#, STATUS,FLASHBACK_DATABASE_ALLOWED FB_OK from v$database_incarnation;


set heading off
set feedback off
select 'Archive Destination Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on
-- Current Archive Locations
--

column host_name format a30 tru
column version format a10 tru
select INSTANCE_NAME,HOST_NAME,VERSION,ARCHIVER from v$instance;

column destination format a35 wrap
column process format a7
column archiver format a8
column dest_id format 99999999

select DEST_ID,DESTINATION,STATUS,TARGET,ARCHIVER,PROCESS,REGISTER,TRANSMIT_MODE
from v$archive_dest
where DESTINATION IS NOT NULL;

column name format a22
column value format a100
select NAME,VALUE from v$parameter where NAME like 'log_archive_dest%' and upper(VALUE) like 'SERVICE%';

set heading off
set feedback off
select 'Archive Destination Errors' from dual;
select '**************************' from dual;
set heading on
set feedback on

column error format a55 tru
select DEST_ID,STATUS,ERROR from v$archive_dest
where DESTINATION IS NOT NULL;

column message format a80
select MESSAGE, TIMESTAMP
from v$dataguard_status
where SEVERITY in ('Error','Fatal')
order by TIMESTAMP;

-- Redo Log configuration
-- The size of the standby redo logs must match exactly the size on the online redo logs

set heading off
set feedback off
select 'Data Guard Redo Log Configuration' from dual;
select '*********************************' from dual;
set heading on
set feedback on

select GROUP# STANDBY_GROUP#,THREAD#,SEQUENCE#,BYTES,USED,ARCHIVED,STATUS from v$standby_log order by GROUP#,THREAD#;

select GROUP# ONLINE_GROUP#,THREAD#,SEQUENCE#,BYTES,ARCHIVED,STATUS from v$log order by GROUP#,THREAD#;

-- Data Guard Parameters
--
set heading off
set feedback off
select 'Data Guard Related Parameters' from dual;
select '*****************************' from dual;
set heading on
set feedback on

column name format a30
column value format a100
select NAME,VALUE from v$parameter where NAME IN ('db_unique_name','cluster_database','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2','fal_client','fal_server','log_archive_config','log_archive_trace','log_archive_max_processes','archive_lag_target','remote_login_password_file','redo_transport_user') order by name;

-- Managed Recovery State

set heading off
set feedback off
select 'Data Guard Apply Status' from dual;
select '***********************' from dual;
set heading on
set feedback on

select systimestamp from dual;

column client_pid format a10
select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby  order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

exec DBMS_LOCK.SLEEP(10);

select systimestamp from dual;

select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby  order by CLIENT_PROCESS,THREAD#,SEQUENCE#;

exec DBMS_LOCK.SLEEP(10);

select systimestamp from dual;

select PROCESS,STATUS,CLIENT_PROCESS,CLIENT_PID,THREAD#,SEQUENCE#,BLOCK#,ACTIVE_AGENTS,KNOWN_AGENTS
from v$managed_standby  order by CLIENT_PROCESS,THREAD#,SEQUENCE#;


set heading off
set feedback off
select 'Data Guard Apply Lag' from dual;
select '********************' from dual;
set heading on
set feedback on

column name format a12
column lag_time format a20
column datum_time format a20
column time_computed format a20
SELECT NAME, VALUE LAG_TIME, DATUM_TIME, TIME_COMPUTED
from V$DATAGUARD_STATS where name like 'apply lag';

-- If there is a lag remove the comment for the select below
--SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag'  AND COUNT > 0;

set heading off
set feedback off
select 'Data Guard Gap Problems' from dual;
select '***********************' from dual;
set heading on
set feedback on

select * from v$archive_gap;

set heading off
set feedback off
select 'Data Guard Errors in the Last Hour' from dual;
select '**********************************' from dual;
set heading on
set feedback on

select TIMESTAMP,SEVERITY,ERROR_CODE,MESSAGE from v$dataguard_status where timestamp > systimestamp-1/24;

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#
/

See how up-to-date a standby is (another version)

PRIMARY_SQL>
select thread#
,      max(sequence#) "Last Primary Seq Generated"
from   v$archived_log val
,      v$database     vdb
where  val.resetlogs_change# = vdb.resetlogs_change#
group  by thread#
order  by 1
/

STANDBY_SQL>
select thread#
,      max(sequence#) "Last Standby Seq Received"
from   v$archived_log val
,      v$database     vdb
where  val.resetlogs_change# = vdb.resetlogs_change#
group  by thread#
order  by 1
/

STANDBY_SQL>
select thread#
,      max(sequence#) "Last Standby Seq Applied"
from   v$archived_log val
,      v$database     vdb
where  val.resetlogs_change# = vdb.resetlogs_change#
and    val.applied           in ('YES', 'IN-MEMORY')
group  by thread#
order  by 1
/

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 (dgmgrl) setup for management of physical standby databases

Assuming primary and standby already exist and have standby redologs setup, force logging etc...
Example: server1 - db_primary, server2 - db_standby

Preparation

On both systems...

alter system set dg_broker_start=true;

Create a configuration

From server1

dgmgrl sys/sys@db_primary

create configuration dg_config as primary database is db_primary connect identifier is db_primary;

Configuration created, add the physical standby to it

add database db_standby as connect identifier is db_standby maintained as physical;

Configuration complete, enable it

enable configuration;

Show configuration

show configuration;

Show individual database information

show database db_primary;
show database db_standby;

Switchover primary to standby and vice versa

From server1

dgmgrl sys/sys@db_primary
switchover to db_standby;

Switch back to the original situation

From server2

dgmgrl sys/sys@db_standby
switchover to db_primary;

Failover

The difference between switchover and failover is that you control a switchover, a failover happens bacause the primary db is no longer available
From server2

dgmgrl sys/sys@db_standby
failover to db_standby;

Backup this database immediately as it is now the only database and has no standby. This can be done with RMAN while it is running.
If flashback was not enabled, the old primary is now useless and will need to be recreated as a standby.
If, however flashback was enabled, we can use this to restart the old primary as a standby.
From server1

dgmgrl sys/sys@db_primary
reinstate database db_primary;

If flashback was not enabled, recreate old primary as a standby something like this

sqlplus / as sysdba
shu abort;
exit;

rm $ORACLE_HOME/dbs/*<SID>.ora
rm -Rf /oracle/<SID>/oradata*/*
rm -Rf /oracle/<SID>/fra/*
rm -Rf /oracle/<SID>/admin/*
mkdir -p /oracle/<SID>/oradata1/ /oracle/<SID>/oradata2/ /oracle/<SID>/oradata3/
mkdir -p /oracle/<SID>/fra/
mkdir -p /oracle/<SID>/admin/adump

echo "*.db_name='db'" > /tmp/initdb_primary.ora
export ORACLE_SID=db
sqlplus / as sysdba
startup nomount pfile='/tmp/initdb_primary.ora';
exit

rman target sys/sys@db_standby auxiliary sys/sys@db_primary

duplicate target database
for standby
from active database
dorecover
spfile
    set db_unique_name='DB_PRIMARY' comment 'Standby'
    set db_file_name_convert='/old/dbfile/dir1/','/new/dbfile/dir1/','/old/dbfile/dir2/','/new/dbfile/dir2/'
    set log_file_name_convert='/old/logfile/dir1/','/new/logfile/dir1/','/old/logfile/dir2/','/new/logfile/dir2/'
    nofilenamecheck;

dgmgrl sys/sys@db_standby
enable database db_primary;
show configuration;

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 from an active primary database

Run from the standby server. Trivial mods to parameterise it soon!

#!/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

Recover a physical standby from an gap using SCN based incremental (catch up) backup

Essential steps using nfs mounted filesystem (/shared_backups) shared between both primary and secondary nodes to avoid scp copies

Primary
SQL> alter system set log_archive_dest_state_2=defer scope=both;

Standby
SQL> select min(checkpoint_change#) lowest_scn from v$datafile_header order by 1;

Primary
RMAN> backup incremental from scn <lowest_scn> database format '/shared_backups/catchup_backup_%U';

Standby
SQL> alter database recover managed standby database cancel;

Standby
RMAN> catalog start with '/shared_backups';
RMAN> recover database noredo;

Primary
SQL> backup current controlfile for standby format '/shared_backups/catchup_control.ctl';

Standby
RMAN> report schema;
RMAN> shutdown immediate;
RMAN> startup nomount;
RMAN> restore standby controlfile from '/shared_backups/catchup_control.ctl'
SQL> alter database mount;
RMAN> report schema;
RMAN> run {
set newname for datafile 1 to '+DATA/<standbySID>/data1/system.dbf';
set newname for datafile 2 to '+DATA/<standbySID>/data1/sysaux.dbf';
set newname for datafile 3 to '+DATA/<standbySID>/data2/users.dbf';
set newname for datafile 4 to '+DATA/<standbySID>/data3/undotbs.dbf';
switch datafile all;
}
RMAN> run {
set newname for tempfile 1 to '+DATA/<standbySID>/data2/temptbs.dbf';
switch tempfile all;
}
RMAN> report schema;

Primary
SQL> alter system set log_archive_dest_state_2=enable scope=both;

Standby
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select inst_id, process, thread#, sequence#, blocks, status from gv$managed_standby where process like '%MRP%';

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