Differences

This shows you the differences between two versions of the page.

--- dataguard [2018/12/08 12:49] – created 0.0.0.0
+++ dataguard [2021/11/16 19:09] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Dataguard ======
+  *  [[https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/ha/dataguard/usingsnapshot/usingsnapshot.htm|Using Snapshot Standby from Physical Standby - oracle.com]]
+  *  [[https://docs.oracle.com/cd/B28359_01/server.111/b28282/configbp006.htm#CHDFBGFE|Configuring Oracle Database 11g with Oracle Data Guard (SDU size etc...)]]
+  *  [[http://www.datadisk.co.uk/html_docs/oracle_dg/monitoring.htm|Monitoring a data guard setup - datadisk.co.uk]]
+  *  [[http://emrebaransel.blogspot.co.uk/2013/07/data-guard-queries.html|Some data guard queries - emre baransel]]
+  *  [[http://www.oracle.com/technetwork/articles/sql/11g-dataguard-083323.html|Arup Nanda - some good standby stuff (Active Data Guard, Snapshot Standby, Conversion from Physical to Logical Standby, Rolling Upgrade, Redo Compression...)]]
+  *  [[https://saruamit4.wordpress.com/2014/05/03/recovering-standby-database-using-scn-based-backup/#more-441|Amit Saraswat - Use incremental SCN backup to allow a physical standby to catch up with primary]]
+  *  [[https://www.pythian.com/blog/oracle-database-12c-network-recovery-in-rman/|Pythian - Close a big gap in physical standby using network recovery (12c)]]
+  *  [[http://www.dba-scripts.com/articles/dataguard-standby/recover-standby-over-network-oracle-12c/|dba-scripts.com recover standby over network oracle 12c]]
+==== Convert a Standby to a Primary ====
+[[https://community.oracle.com/mosc/discussion/comment/14183132Converting physical standby to READ /WRITE without using Snapshot Standby]]
+<code>
+    alter database recover managed standby cancel;          -- stops redo apply
+    alter database recover managed standby database finish; -- initiates failover on the target standby, finish applying all received redo data and recovers current standby logs
+    alter database activate standby database;               -- activates standby in case of errors (not all received redo data are applied).
+</code>
-  * [[https://docs.oracle.com/cd/B28359_01/server.111/b28282/configbp006.htm#CHDFBGFE|Configuring Oracle Database 11g with Oracle Data Guard (SDU size etc...)]]
+==== Start up the apply on a physical standby ====
-  * [[http://www.datadisk.co.uk/html_docs/oracle_dg/monitoring.htm|Monitoring a data guard setup - datadisk.co.uk]]
+<code>
-  * [[http://emrebaransel.blogspot.co.uk/2013/07/data-guard-queries.html|Some data guard queries - emre baransel]]
-  * [[http://www.oracle.com/technetwork/articles/sql/11g-dataguard-083323.html|Arup Nanda - some good standby stuff (Active Data Guard, Snapshot Standby, Conversion from Physical to Logical Standby, Rolling Upgrade, Redo Compression...)]]
-  * [[https://saruamit4.wordpress.com/2014/05/03/recovering-standby-database-using-scn-based-backup/#more-441|Amit Saraswat - Use incremental SCN backup to allow a physical standby to catch up with primary]]
-  * [[https://www.pythian.com/blog/oracle-database-12c-network-recovery-in-rman/|Pythian - Close a big gap in physical standby using network recovery (12c)]]
-  * [[http://www.dba-scripts.com/articles/dataguard-standby/recover-standby-over-network-oracle-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;
+</code>
-=====Stop the apply on a physical standby=====
+==== Stop the apply on a physical standby ====
+<code>
  alter database recover managed standby database cancel;
+</code>
-=====Register a missing logfile=====
+==== Register a missing logfile ====
+<code>
  alter database register physical logfile '<fullpath/filename>';
-=====Show apply activity on the standby=====
+</code>
-<code>0@@</code>
+==== Show apply activity on the standby ====
+<code>
+select process
+,      status
+,      client_process
+,      sequence#
+,      block#
+,      active_agents
+,      known_agents
+from   v$managed_standby
+/
+</code>
 shows something like this
-<code>1@@</code>
+<code>
+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
-=====See Dataguard error log messages=====
+rows selected.
+</code>
+==== See Dataguard error log messages ====
 If data guard is not functioning correctly, check the errors in this log.
-<code>2@@</code>
+<code>
+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#
+/
+</code>
 and
-<code>3@@</code>
+<code>
+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
+/
+</code>
-=====Check which logs are missing=====
+==== Check which logs are missing ====
 Assuming dest_2 is the standby
+<code>
  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#);
+</code>
-=====Stop/Start log file shipping=====
+==== Stop/Start log file shipping ====
 On the primary
+<code>
  alter system set log_archive_dest_state_2 = 'defer';
  alter system set log_archive_dest_state_2 = 'enable';
+</code>
-=====Start FAL tracing on the primary=====
+==== Start FAL tracing on the primary ====
+<code>
  alter system set LOG_ARCHIVE_TRACE = 128;
-=====Stop the DataGuard broker=====
+</code>
+==== Stop the DataGuard broker ====
+<code>
  alter system set dg_broker_start=false;
-=====Are we a standby?=====
+</code>
+==== Are we a standby? ====
+<code>
  select database_role
  from   v$database;
-=====Show various details on health of standby database=====
+</code>
-<code>4@@</code>
+==== Show various details on health of standby database ====
-=====See how up-to-date a standby is=====
+<code>
+-- 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;
+</code>
+==== See how up-to-date a standby is ====
 On the primary
-<code>5@@</code>
+<code>
+set numwidth 15
+select max(sequence#) current_seq
+from   v$log;
+</code>
 On the standby
-<code>6@@</code>
+<code>
+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;
+</code>
 or
-<code>7@@</code>
+<code>
-=====See how up-to-date a standby is (another version)=====
+select a.thread#
-<code>8@@</code>
+,      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=====
+</code>
+==== See how up-to-date a standby is (another version) ====
+<code>
+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
+/
+</code>
+==== Show info on all log destinations ====
 On the primary
-<code>9@@</code>
+<code>
+set lines 100
+set numwidth 15
+column ID format 99
+column "SRLs" format 99
+column active format 99
+col type format a4
-=====Display log destinations options=====
+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;
+</code>
+==== Display log destinations options ====
 On the standby
-<code>10@@</code>
+<code>
+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;
+</code>
-=====Show any standby logs=====
+==== Show any standby logs ====
+<code>
  set lines 100
  set pages 999
@@ Line 88: / Line 484: @@
  where  1=1
  and    st.group# = lf.group#
+</code>
-=====Dataguard broker (dgmgrl) setup for management of physical standby databases=====
+==== Dataguard broker (dgmgrl) setup for management of physical standby databases ====
-Assuming primary and standby already exist and have standby redologs setup, force logging etc...<br />
+Assuming primary and standby already exist and have standby redologs setup, force logging etc...\\
-Example: server1 - db_primary, server2 - db_standby<br />
+Example: server1 - db_primary, server2 - db_standby\\
-====Preparation====
+=== Preparation ===
 On both systems...
-<code>11@@</code>
+<code>
-====Create a configuration====
+alter system set dg_broker_start=true;
+</code>
+=== Create a configuration ===
 From server1
-<code>12@@</code>
+<code>
+dgmgrl sys/sys@db_primary
+create configuration dg_config as primary database is db_primary connect identifier is db_primary;
+</code>
 Configuration created, add the physical standby to it
-<code>13@@</code>
+<code>
-====Configuration complete, enable it====
+add database db_standby as connect identifier is db_standby maintained as physical;
-<code>14@@</code>
+</code>
-====Show configuration====
+=== Configuration complete, enable it ===
-<code>15@@</code>
+<code>
-====Show individual database information====
+enable configuration;
-<code>16@@</code>
+</code>
-====Switchover primary to standby and vice versa====
+=== Show configuration ===
+<code>
+show configuration;
+</code>
+=== Show individual database information ===
+<code>
+show database db_primary;
+show database db_standby;
+</code>
+==== Switchover primary to standby and vice versa ====
 From server1
-<code>17@@</code>
+<code>
-====Switch back to the original situation====
+dgmgrl sys/sys@db_primary
+switchover to db_standby;
+</code>
+=== Switch back to the original situation ===
 From server2
-<code>18@@</code>
+<code>
-====Failover====
+dgmgrl sys/sys@db_standby
-The difference between switchover and failover is that you control a switchover, a failover happens bacause the primary db is no longer available<br />
+switchover to db_primary;
+</code>
+==== Switchover without using dgmgrl ====
+From server1, connect to the primary
+<code>
+connect / as sysdba
+alter database commit to switchover to standby;
+</code>
+Now shut it down and restart it as a standby
+<code>
+shutdown immediate;
+startup nomount;
+alter database mount standby database;
+alter database recover managed standby database disconnect from session;
+</code>
+=== From server2, connect to the standby ===
+<code>
+connect / as sysdba
+alter database commit to switchover to primary;
+</code>
+Now shut it down and restart it as a primary
+<code>
+shutdown immediate;
+startup;
+</code>
+==== 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
-<code>19@@</code>
+<code>
-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.<br />
+dgmgrl sys/sys@db_standby
-If flashback was not enabled, the old primary is now useless and will need to be recreated as a standby.<br />
+failover to db_standby;
-If, however flashback was enabled, we can use this to restart the old primary as a standby.<br />
+</code>
+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
-<code>20@@</code>
+<code>
-====If flashback was not enabled, recreate old primary as a standby something like this====
+dgmgrl sys/sys@db_primary
-<code>21@@</code>
+reinstate database db_primary;
-<code>22@@</code>
+</code>
-<code>23@@</code>
+=== If flashback was not enabled, recreate old primary as a standby something like this ===
-<code>24@@</code>
+<code>
-<code>25@@</code>
+sqlplus / as sysdba
+shu abort;
+exit;
+</code>
+<code>
+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
+</code>
+<code>
+echo "*.db_name='db'" > /tmp/initdb_primary.ora
+export ORACLE_SID=db
+sqlplus / as sysdba
+startup nomount pfile='/tmp/initdb_primary.ora';
+exit
+</code>
+<code>
+rman target sys/sys@db_standby auxiliary sys/sys@db_primary
-=====Dataguard broker managed standby (dgmgrl) not applying logs after restart=====
+duplicate target database
-After restarting the Primary and Standby databases manually, the redo logs were not being applied on the standby.<br />
+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;
+</code>
+<code>
+dgmgrl sys/sys@db_standby
+enable database db_primary;
+show configuration;
+</code>
+==== 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:
-<code>26@@</code>
+<code>
+alter database recover managed standby database using current logfile disconnect from session;
+</code>
 It seems this is not good enough, we see a problem in the broker configuration...
- (0) OTMPRODS oracle@ravotm14:/home/oracle] dgmgrl
+<code>
+ (0) OTMPRODS oracle@ravotm14:/home/oracle]] dgmgrl
  DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
+</code>
+<code>
  Copyright (c) 2000, 2009, Oracle. All rights reserved.
+</code>
+<code>
  Welcome to DGMGRL, type "help" for information.
  DGMGRL> connect sys/********@otmprod
  Connected.
  DGMGRL> show configuration
+</code>
+<code>
  Configuration - dg_otmprod
+</code>
+<code>
    Protection Mode: MaxPerformance
    Databases:
      otmprod  - Primary database
      otmprods - **Physical standby database (disabled)**
+</code>
+<code>
  Fast-Start Failover: DISABLED
+</code>
+<code>
  Configuration Status:
  SUCCESS
+</code>
+<code>
  DGMGRL>
+</code>
 We need to re-enable the Standby database in the broker configuration
+<code>
  DGMGRL> enable database OTMPRODS;
  Enabled.
  DGMGRL> show configuration
+</code>
+<code>
  Configuration - dg_otmprod
+</code>
+<code>
   Protection Mode: MaxPerformance
   Databases:
     otmprod  - Primary database
     otmprods - **Physical standby database**
+</code>
+<code>
  Fast-Start Failover: DISABLED
+</code>
+<code>
  Configuration Status:
  SUCCESS
+</code>
 Now check alert log. All is working again.
-=====(Re)Build a physical standby from an active primary database=====
+==== (Re)Build a physical standby from an active primary database ====
 Run from the standby server. Trivial mods to parameterise it soon!
-<code>27@@</code>
+<code>
+  - !/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
-=====Recover a physical standby from an gap using SCN based incremental (catch up) backup=====
+$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
+</code>
+==== 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
-<code>28@@</code>
+<code>
+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%';
+</code>
+==== Other resources ====
+  *  [[http://www.idevelopment.info/data/Oracle/DBA_tips/Data_Guard/DG_5.shtml|Log Gap Detection and Resolution - Jeff Hunter]]
+  *  [[http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php|http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php]]
+  *  [[http://www.orafaq.com/node/2030|Oracle 10g - Manually Create a Physical Standby Database Using Data Guard]]
+  *  [[http://allthingsoracle.com/rolling-forward-a-physical-standby-database-using-the-recover-command/?utm_source=allthingsoracle&utm_medium=pubemail&utm_campaign=allthingsoracle&utm_term=march3-news&utm_content=recoverfromservice|Rolling Forward a Physical Standby Database Using the RECOVER Command]]
-=====Other resources=====
-  * [[http://www.idevelopment.info/data/Oracle/DBA_tips/Data_Guard/DG_5.shtml|Log Gap Detection and Resolution - Jeff Hunter]]
-  * [[http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php|http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php]]
-  * [[http://www.orafaq.com/node/2030|Oracle 10g - Manually Create a Physical Standby Database Using Data Guard]]
-  * [[http://allthingsoracle.com/rolling-forward-a-physical-standby-database-using-the-recover-command/?utm_source=allthingsoracle&utm_medium=pubemail&utm_campaign=allthingsoracle&utm_term=march3-news&utm_content=recoverfromservice|Rolling Forward a Physical Standby Database Using the RECOVER Command]]