RESOLVE REDO GAPS DATA GUARD

RESOLVE REDO GAPS DATA GUARD:
=============================== 

---> Document Legend:
--=> *****************************************
--=> *****<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>****
--=> -------------> PERSONEL <----------------
--=> *****<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>****
--=> *****************************************
NOTE1: Oracle DBA
       Madhu
       kmadhuapps2@gmail.com
       9703588226
NOTE2: Application administrator:
       Madhu
       kmadhuapps2@gmail.com
       9703588226
NOTE3: RAC DBA.
       Madhu
       kmadhuapps2@gmail.com
       9703588226
---> *****************************************
---> *****<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>****
---> ----------> REDO GAP NOTES <-------------
---> ------> Notes taken on Jan 17 2014<-----
---> *****<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>****
---> *****************************************
--=> Notes taken on Jan 17 2014

---> ******************************************************
---> ***********<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>***********
---> --------------> GENERAL CHECKING  <-------------------
---> ***********<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>***********
---> ******************************************************
---> Force the Primary to send redo data to the Standby
--=> Database Primary: SQL>
alter system switch logfile;
alter system switch logfile;
alter system checkpoint;
alter system archive log current;
---> Verify database open state
--=> Database Primary: SQL>
set lines 200
set pages 99
set trimspool on
set feedback on
set heading on

COL HOST_NAME     FOR A15 HEADING HOST|NAME
COL INSTANCE_NAME FOR A8 HEADING INST|NAME
COL INSTSTATUS    FOR A10 HEADING INST|STATUS
COL INST_ROLE     FOR A18 HEADING INST|ROLE
COL ACTIVE_STATE  FOR A10 HEADING DB ACTIVE|STATE
COL DBNAME        FOR A8 HEADING DB|NAME
COL DATABASE_ROLE FOR A16  HEADING 'DATABASE|ROLE'
COL DGB           FOR A8 HEADING DATAGUARD|BROKER
COL FS_STATUS     FOR A8 HEADING FS|FAILOVER|STATUS
COL FS_HOST       FOR A8 HEADING FS|FAILOVER|OBSERVER|HOST
COL CURRENT_SEQ1          FOR A15 HEADING CURRENT|APPLIED|ARCH|SEQUENCE
COL DB_UNIQUE_NAME FOR A15
COL PRIMARY_DB_UNIQUE_NAME FOR A15

SELECT B.HOST_NAME,
      B.INSTANCE_NAME,
       B.INSTANCE_ROLE INST_ROLE,
      decode(b.status,'STARTED','Started in NoMount',b.status) inststatus,
       A.NAME DBNAME,
       A.DB_UNIQUE_NAME,
       A.DATABASE_ROLE,
      B.DATABASE_STATUS DBSTATUS,
      A.DATAGUARD_BROKER DGB,
       A.FS_FAILOVER_STATUS FS_STATUS,
       A.FS_FAILOVER_OBSERVER_HOST FS_HOST,
       SEQ1.CURRENT_SEQ1
from v$database a,
     v$instance b,
     (SELECT TO_CHAR(MAX(SEQUENCE#)) CURRENT_SEQ1 FROM GV$ARCHIVED_LOG WHERE APPLIED='YES') SEQ1;

---> Make sure the primary database is open and the database role is PRIMARY
--=> Database Primary: SQL>

                                                                                                                       FS       CURRENT
                                                                                                              FS       FAILOVER APPLIED
HOST            INST   INST               INST    DB                       DATABASE                  DATAGUAR FAILOVER OBSERVER ARCH
NAME            NAME   ROLE               STATUS  NAME     DB_UNIQUE_NAME  ROLE         DBSTATUS     BROKER   STATUS   HOST     SEQUENCE
--------------- ------ ------------------ ------- -------- --------------- ------------ ------------ -------- -------- -------- --------
osb-prd-ofs-001 ACME   PRIMARY_INSTANCE   OPEN    ACME     ACME_pri        PRIMARY      ACTIVE       ENABLED  DISABLED          82801

---> Make sure the standby database is mounted, the database role is "PHYSICAL STANDBY", and the SEQUENCE is the same.
--=> Database Standby: SQL>
                                                                                                                        FS       CURRENT
                                                                                                               FS       FAILOVER APPLIED
HOST          INST   INST               INST       DB                       DATABASE                  DATAGUAR FAILOVER OBSERVER ARCH
NAME          NAME   ROLE               STATUS     NAME     DB_UNIQUE_NAME  ROLE             DBSTATUS BROKER   STATUS   HOST     SEQUENCE
------------- ------ ------------------ ---------- -------- --------------- ---------------- -------- -------- -------- -------- --------
drc-ofs-001   ACME   PRIMARY_INSTANCE   MOUNTED    ACME     ACME_drc        PHYSICAL STANDBY ACTIVE   ENABLED  DISABLED          82801


---> Verify connectivity between Primary and Standby
Ping the standby server from the primary server
ping the primary server from the standby server

--=> LOG Onto the Primary database from the standby server as sysdba.
sqlplus sys/rdbms_dba@ACME_pri as sysdba

--=> LOG Onto the Standby database from the primary server as sysdba.
sqlplus sys/rdbms_dba@ACME_drc as sysdba

---> Get last seq sent to Standby database
SQL> --=> Database Primary: SQL>
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;

   THREAD# Last Primary Seq Generated
---------- --------------------------
         1                      80466

---> Get last seq received by Standby database
SQL> --=> Database Standby: SQL>
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;

   THREAD# Last Standby Seq Received
---------- -------------------------
         1                     80466

---> Get last seq applied by Standby database
SQL> --=> Database Standby: SQL>
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 applied='YES'
group by thread# order by 1;

   THREAD# Last Standby Seq Applied
---------- ------------------------
         1                    78056
---> ******************************************************
---> ***********<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>***********
---> -----------> RESOLVING A REDO LOG GAP <---------------
---> ***********<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>***********
---> ******************************************************

---> URL's for data guard gap resolution:
--=> http://arup.blogspot.com/2009/12/resolving-gaps-in-data-guard-apply.html
--=> http://oracle-dba-quickanswers.blogspot.com/2012/03/data-guard-gap-detection-and-resolution.html
--=> http://chandu208.blogspot.com/2011/07/data-guard-gap-detection-and-resolution.html
--=> http://forums.databasejournal.com/showthread.php?42574-Recover-Standby-Database2
--=> http://www.idevelopment.info/data/Oracle/DBA_tips/Data_Guard/DG_5.shtml
--=> http://www.dba-oracle.com/t_detect_redo_log_gaps.htm

---> Procedures for RESOLVING A REDO LOG GAP and/or Re-syncing Primary and Standby
--=> There are 5 procedures for RESOLVING A REDO LOG GAP and/or Re-syncing Primary and Standby.
--=>
--=> Procedure #1 (Re-syncing Primary and Standby)
--=> Bounce the standby database and see if managed recovery process starts applying the redo log files.
--=>
--=> Procedure #2 (RESOLVING A REDO LOG GAP and/or Re-syncing Primary and Standby)
--=> If the archived log files are available on the Primary then:
--=> 1) Copie them over to the standby,
--=> 2) Registere the log files
--=> 3) Let the Managed Recovery Process (MRP) apply the log file.
--=>
--=> Procedure #3 (RESOLVING A REDO LOG GAP and/or Re-syncing Primary and Standby)
--=> If the archived log files are NOT available on the Primary then:
--=> Use backed up data files from Primary to recover the data files on Standby.
--=>
--=> Procedure #4 (RESOLVING A REDO LOG GAP and/or Re-syncing Primary and Standby)
--=> Apply an incremental "backup from SCN" of Primary database.
--=>
--=> Procedure #5 (RESOLVING A REDO LOG GAP and/or Re-syncing Primary and Standby)
--=> Re-create the standby from a backup of the Primary database.
--=> NOTE: Re-creating a new standby database is beyond the scope of this document.
--=>       SEE document: Instructions_for_building_dg_standby_database.txt

---> **************************************************************
---> Procedure #1 (Re-syncing Primary and Standby)
---> <Restart archive shipping and applying>
---> NOTE: If this Procedure is not succesfull then try Procedure #2
---> **************************************************************

--=> DATABASE Primary: SQL>
alter system set log_archive_dest_state_2  = 'defer' scope=both;

SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL NAME FOR A30
COL VALUE FOR A15
COL DESCRIPTION FOR A60
select NAME, VALUE, DESCRIPTION from v$parameter where name = 'log_archive_dest_state_2';

--=> DATABASE Standby: SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate;

--=> DATABASE Standby: SQL>
Startup nomount;
alter database mount standby database;
alter database recover managed standby database using current logfile disconnect from session;

--=> DATABASE Primary: SQL>
alter system set log_archive_dest_state_2 = 'enable' scope=both;

SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL NAME FOR A30
COL VALUE FOR A15
COL DESCRIPTION FOR A60
select NAME, VALUE, DESCRIPTION from v$parameter where name = 'log_archive_dest_state_2';

---> Force the Primary to send redo data to the Standby
--=> Database Primary: SQL>
alter system switch logfile;
alter system switch logfile;
alter system checkpoint;
alter system archive log current;

---> Check the MRP status
--=> NOTE1: After a minute or so has passed, the Primary database (the ARCH1 process) will wake up and attempt
--=>        to ping the physical standby database.
--=>        Once it is determined that the physical standby database is mounted (and the Standby server TNS Listener is up),
--=>        it will start to push archived redo logs to the standby database.
--=> NOTE2: If MRPO has a status = APPLYING_LOG then the Gap is being closed
--=> NOTE3: There is a Redo Data gap if process MRPO has a status = WAIT_FOR_GAP
--=> NOTE4: If there is no MRP0 process then Managed Recovery is not in Real Time Apply
--=> NOTE5: Verify the Gap is getting smaller over time
--=>
--=> Valid statuses are:
--=>     UNUSED - No active process
--=>     ALLOCATED - Process is active but not currently connected to a Primary database
--=>     CONNECTED - Network connection established to a Primary database
--=>     ATTACHED - Process is actively attached and communicating to a Primary database
--=>     IDLE - Process is not performing any activities
--=>     ERROR - Process has failed
--=>     OPENING - Process is opening the archived redo log
--=>     CLOSING - Process has completed archival and is closing the archived redo log
--=>     WRITING - Process is actively writing redo data to the archived redo log
--=>     RECEIVING - Process is receiving network communication
--=>     ANNOUNCING - Process is announcing the existence of a potential dependent archived redo log
--=>     REGISTERING - Process is registering the existence of a completed dependent archived redo log
--=>     WAIT_FOR_LOG - Process is waiting for the archived redo log to be completed
--=>     WAIT_FOR_GAP - Process is waiting for the archive gap to be resolved
--=>     APPLYING_LOG - Process is actively applying the archived redo log to the standby database
--=>
--=> DATABASE Standby SQL>
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL PROCESS FOR A38
select decode(process,'RFS','RFS-Remote file server',
                      'MRP0','MRP0-Detached recovery server process',
                      'MR(fg)','MR(fg)-Foreground recovery session',
                                    'ARCH','ARCH-Archiver process',
                      'FGRD','FGRD',
                      'LGWR','LGWR',
                      'RFS(FAL)','RFS(FAL)',
                      'RFS(NEXP)','RFS(NEXP)',
                      'LNS','LNS-Network server process',
                                    process)process,
       STATUS,
          client_process,
       thread#,
          SEQUENCE#
from v$managed_standby;

--=> DATABASE Standby: SQL>
--=1> Error <1=--
--=> NOTE: There is no MRP0 process therefore MRP is not running Real Time Apply.
PROCESS                                DEST STATUS          CLIENT_P       THREAD#     SEQUENCE#
-------------------------------------- -------------------- -------- ------------- -------------
ARCH-Archiver process                  CLOSING              ARCH                 1         81762
ARCH-Archiver process                  CONNECTED            ARCH                 0             0
ARCH-Archiver process                  CLOSING              ARCH                 1         81767
ARCH-Archiver process                  CLOSING              ARCH                 1         81763
ARCH-Archiver process                  CLOSING              ARCH                 1         81759
ARCH-Archiver process                  CLOSING              ARCH                 1         81764
ARCH-Archiver process                  CLOSING              ARCH                 1         81760
ARCH-Archiver process                  CLOSING              ARCH                 1         81765
RFS-Remote file server                 IDLE                 ARCH                 1         36602
RFS-Remote file server                 UNKNOWN              ARCH                 1         0

--=1> No Error <1=--
PROCESS                                DEST STATUS          CLIENT_P       THREAD#     SEQUENCE#
-------------------------------------- -------------------- -------- ------------- -------------
ARCH-Archiver process                  CLOSING              ARCH                 1         81762
ARCH-Archiver process                  CONNECTED            ARCH                 0             0
ARCH-Archiver process                  CLOSING              ARCH                 1         81767
ARCH-Archiver process                  CLOSING              ARCH                 1         81763
ARCH-Archiver process                  CLOSING              ARCH                 1         81759
ARCH-Archiver process                  CLOSING              ARCH                 1         81764
ARCH-Archiver process                  CLOSING              ARCH                 1         81760
ARCH-Archiver process                  CLOSING              ARCH                 1         81765
ARCH-Archiver process                  CLOSING              ARCH                 1         81761
ARCH-Archiver process                  CLOSING              ARCH                 1         81766
MRP0-Detached recovery server process  APPLYING_LOG         N/A                  1         81768
---> Verify the Gap is getting smaller over time
--=> DATABASE Standby SQL>
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL SEQ1 FOR A60 heading 'REDO LOG GAPS'
SELECT DECODE (
              (SELECT ltrim(to_char(COUNT(*),'999,999,999')) from v$archive_gap),'0',
              (select 'NO GAPS FOUND' FROM dual),
              (select 'LOW_SEQ: '||LOW_SEQUENCE#||' <> '||'HIGH_SEQ: '||HIGH_SEQUENCE#  SEQ1 from v$archive_gap)) SEQ1
FROM DUAL;

REDO LOG GAPS
------------------------------------------------------------
LOW_SEQ: 68404 <> HIGH_SEQ: 81410

---> Check #1 to see if the Primary and Standby are in sync
--=> DATABASE Standby: SQL>
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL LINE1 HEADING REDO_DATA_TRANSFER_INFORMATION FOR A60
SELECT ltrim(to_char(COUNT(*),'999,999,999'))||' TOTAL_LOGS' LINE1 FROM GV$ARCHIVED_LOG
UNION ALL
SELECT ltrim(to_char(COUNT(*),'999,999,999'))||' LOGS_APPLIED' FROM GV$ARCHIVED_LOG
WHERE APPLIED='YES'
UNION ALL
SELECT ltrim(to_char(COUNT(*),'999,999,999'))||' LOGS_IN_MEMORY' FROM GV$ARCHIVED_LOG
WHERE APPLIED='IN-MEMORY'
UNION ALL
SELECT ltrim(to_char(COUNT(*),'999,999,999'))||' LOGS_NOT_APPLIED' FROM GV$ARCHIVED_LOG
WHERE APPLIED='NO'
UNION ALL
SELECT '=================' FROM DUAL
UNION ALL
SELECT DECODE (
             (SELECT ltrim(to_char(COUNT(*),'999,999,999')) FROM GV$ARCHIVED_LOG WHERE APPLIED='NO'),'0',
             (SELECT TO_CHAR(MAX(SEQUENCE#))||' MAX ARCH LOG SEQUENCE APPLIED' FROM GV$ARCHIVED_LOG WHERE APPLIED='YES'),
              (SELECT TO_CHAR(MAX(SEQUENCE#))||' MAX ARCH LOG SEQ NOT APPLIED' FROM GV$ARCHIVED_LOG WHERE APPLIED='NO')) sql1
FROM DUAL
UNION ALL
SELECT DECODE (
             (SELECT ltrim(to_char(COUNT(*),'999,999,999')) FROM GV$ARCHIVED_LOG WHERE APPLIED='NO'),'0',
             ('ALL ARCH LOG SEQUENCES APPLIED'),
              (SELECT TO_CHAR(MIN(SEQUENCE#))||' MIN ARCH LOG SEQ NOT APPLIED' FROM GV$ARCHIVED_LOG WHERE APPLIED='NO')) sql1
FROM DUAL;
--=> DATABASE Standby: SQL>
--=1> No Error <1=--
REDO_DATA_TRANSFER_INFORMATION
------------------------------------------------------------
922 TOTAL_LOGS
920 LOGS_APPLIED
2 LOGS_IN_MEMORY
0 LOGS_NOT_APPLIED
=================
81892 MAX ARCH LOG SEQUENCE APPLIED
ALL ARCH LOG SEQUENCES APPLIED

--=> DATABASE Standby: SQL>
--=1> Error <1=--
REDO_DATA_TRANSFER_INFORMATION
------------------------------------------------------------
18713 TOTAL_LOGS
9863 LOGS_APPLIED
2 LOGS_IN_MEMORY
8848 LOGS_NOT_APPLIED
=================
81768 MAX_ARCH_LOG_SEQ_NOT_PPLIED
71903 MIN_ARCH_LOG_SEQ_NOT_PPLIED
---> Check #2 to see if Primary and standby databases are in sync
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL STANDBY_DEST FOR A7 HEADING STANDBY|DEST
COL "LAST SEQUENCE GENERATED" HEADING "LAST|SEQUENCE|GENERATED|IN PRIMARY"
COL "PRIME THREAD" HEADING "PRIME|THREAD"
COL "PRIME/STANDBY DIFFERENCE" HEADING "PRIME/STANDBY|DIFFERENCE"
COL "STANDBY THREAD" HEADING "STANDBY|THREAD"
COL "LAST SEQUENCE RECEIVED" HEADING "LAST|SEQUENCE|RECEIVED|BY STANDBY"
COL "LAST SEQUENCE APPLIED" FOR A15 HEADING "LAST|SEQUENCE|APPLIED|BY STANDBY"
COL "STANDBY ARCH/APPL DIFFERENCE" HEADING "STANDBY|ARCH/APPL|DIFFERENCE"
COL "SEP" FOR A3 HEADING "-->|-->"
COL COMPLETION_TIME FOR A25
select prime.STANDBY_DEST,
       prime."last sequence generated",
       prime."thread" "prime thread",
      (prime."last sequence generated" - arch.sequence#) "prime/standby difference",
      '-->' "SEP",
      arch.STANDBY_DEST,
      arch.thread# "standby thread",
      arch.sequence# "last sequence received",
      '-->' "sep",
      appl.sequence# "last sequence applied",
     (arch.sequence# - appl.sequence#) "standby arch/appl difference",
     to_char(arch.completion_time,'mm/dd/yyyy hh:mm:ss am') completion_time
from
(select distinct
        a.sequence# "last sequence generated",
        a.thread# "thread",
        a.STANDBY_DEST
        from v$archived_log a
        where (a.thread#,a.first_time ) in
              (select aa.thread#,
                  max(aa.first_time)
               from v$archived_log aa
               group by aa.thread#)) prime,
(select distinct
        c.thread#,
        c.STANDBY_DEST,
        c.sequence#,
        c.completion_time
        from v$archived_log c
        where (c.thread#,c.first_time ) in
              (select cc.thread#,
                  max(cc.first_time)
             from v$archived_log cc
             group by thread#)) arch,
(SELECT DECODE(
             (SELECT ltrim(to_char(COUNT(*),'999,999,999')) FROM GV$ARCHIVED_LOG WHERE APPLIED='NO'),'0',
             (SELECT TO_CHAR(MAX(SEQUENCE#)) FROM GV$ARCHIVED_LOG WHERE (APPLIED='YES' OR APPLIED='IN-MEMORY')),
                      (SELECT TO_CHAR(MAX(SEQUENCE#)) FROM GV$ARCHIVED_LOG WHERE APPLIED='NO')) sequence#
        FROM DUAL) APPL
where arch.thread# = 1
and arch.thread#(+)=prime."thread"
and   prime.standby_dest = arch.standby_dest
order by 10,1;

--=> NOTE: The databases are in sync when the "Last Sequence Applied by Standby" is the same on the Primary and Standby result sets
--=>       and the "Standby AECH/APPL Difference" is 0 in Primary and Standby result sets.

--=> DATABASE Primary: SQL>
              LAST                                                       LAST     LAST
          SEQUENCE                                                   SEQUENCE     SEQUENCE           STANDBY
STANDBY  GENERATED      PRIME PRIME/STANDBY --> STANDBY    STANDBY   RECEIVED --> APPLIED          ARCH/APPL
DEST    IN PRIMARY     THREAD    DIFFERENCE --> DEST        THREAD BY STANDBY --> BY STANDBY      DIFFERENCE COMPLETION_TIME
------- ---------- ---------- ------------- --- ------- ---------- ---------- --- --------------- ---------- -------------------------
NO           81893          1             0 --> NO               1      81893 --> 81893                    0 01/20/2014 09:01:10 pm
YES          81893          1             0 --> YES              1      81893 --> 81893                    0 01/20/2014 09:01:09 pm

--=> DATABASE Standby: SQL>
              LAST                                                       LAST     LAST
          SEQUENCE                                                   SEQUENCE     SEQUENCE           STANDBY
STANDBY  GENERATED      PRIME PRIME/STANDBY --> STANDBY    STANDBY   RECEIVED --> APPLIED          ARCH/APPL
DEST    IN PRIMARY     THREAD    DIFFERENCE --> DEST        THREAD BY STANDBY --> BY STANDBY      DIFFERENCE COMPLETION_TIME
------- ---------- ---------- ------------- --- ------- ---------- ---------- --- --------------- ---------- -------------------------
NO           81893          1             0 --> NO               1      81893 --> 81893                    0 01/20/2014 09:01:13 pm

---> Verify datafiles do not need recovery
--=> NOTE: X$KCVFH contains information gathered directly from data file headers.
--=>       This is more accurate than information gathered from the control file.
--=>       The control file will be updated using data in the data file headers.

---> Select distinct list of data file header SCN's
--=> NOTE: The first number is the highest seq number in the redo log files.
--=>       The other numbers are the distinct set of SCN numbers in data file headers.
--=>       There should only be 1 OR 2 numbers. (One set for read only data files and possibly one set of read/write data files)
--=>       If there are more than 2 numbers then some of the data file headers are not current and those datafiles need recovery.

SET ECHO ON FEEDBACK ON  PAGESIZE 100 LINESIZE 200 TRIMSPOOL ON NUMWIDTH 13
COL CNT HEADING 'DATA FILES'
COL DTE FOR A25 HEADING 'DATE AND TIME'
break on report
compute sum of cnt on report
select to_char(sysdate,'mm-dd-yyyy hh:mi AM') DTE,
       FHTHR Thread,
          FHRBA_SEQ Sequence,
          count(*) cnt
from X$KCVFH
group by FHTHR, FHRBA_SEQ
order by FHTHR, FHRBA_SEQ;
--=> DATABASE Standby: SQL>
--=1> Errors <1=--
       THREAD      SEQUENCE    DATA FILES
------------- ------------- -------------
            0             0            51
            1         81168           162
            1         81169           235
            1         81170           243
            1         81171           374
            1         81172           246
            1         81173           312
            1         81174           252
            1         81175           123
            1         81176           369
            1         81177           184
            1         81178            61
            1         81653            46
                            -------------
sum                                  2654

--=> DATABASE Standby: SQL>
--=1> No Error <1=--

DATE and TIME                    THREAD      SEQUENCE    DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:49 PM                   1         81894          2654
                                                      -------------
sum                                                            2654
---> **************************************************************
---> Procedure #2 (RESOLVING A REDO LOG GAP and/or re-syncing Primary and Standby)
---> <Copy missing archive redo log files from primary to stndby and re-start MRP>
---> NOTE: If this Procedure is not successful then try Procedure #3
---> **************************************************************

---> List redo log files that have not been applied on the Standby
--=> If there are files that are "NOT ON DISK" on Primary or the
--=> Standby server then go to Procedure #3.

SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL FILE_NAME FOR A60
COL ARCHIVED FOR A15 HEADING 'ARCHIVE LOG|RECEIVED FROM|PRIMARY DB'
COL APPLIED FOR A15 HEADING 'APPLIED|ON|STANDBY'
COL DELETED FOR A15 HEADING 'DELETED|FROM|STANDBY'
COL STATUS FOR A20
SELECT NVL(NAME,'FILE NOT ON DISK') FILE_NAME,
       CREATOR,
          SEQUENCE#,
          ARCHIVED,
          APPLIED,
          DELETED,
          decode(STATUS,      
          'A','Available',
       'D','Deleted',
          'U', 'Unavailable',
          'X','Expired', STATUS) STATUS
from v$archived_log
where APPLIED = 'NO'
and sequence# >=68404
  and DEST_ID = 2
order by sequence#,RESETLOGS_ID;

--=> DATABASE Primary: SQL>
                                                                     ARCHIVE LOG     APPLIED         DELETED
                                                                     RECEIVED FROM   ON              FROM
FILE_NAME                                          CREATOR SEQUENCE# PRIMARY DB      STANDBY         STANDBY   STATUS
-------------------------------------------------- ------- --------- --------------- --------------- --------- -------
/oracle/arc/arch1/ACME/ACME_1_81858_769561756.log  ARCH    81858     YES             NO              YES       DELETED
/oracle/arc/arch1/ACME/ACME_1_81859_769561756.log  ARCH    81859     YES             NO              YES       DELETED
/oracle/arc/arch1/ACME/ACME_1_81860_769561756.log  ARCH    81860     YES             NO              NO        Available
/oracle/arc/arch2/ACME/ACME_1_81861_769561756.log  ARCH    81861     YES             NO              NO        Available
--=> DATABASE Standby SQL>
--=> There should be zero or one row returned.
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
SELECT NVL(NAME,'FILE NOT ON DISK') FILE_NAME,
      CREATOR,
       SEQUENCE#,
       ARCHIVED,
       APPLIED,
       DELETED,
       DECODE(STATUS,         
       'A','AVAILABLE',
      'D','DELETED',
       'U', 'UNAVAILABLE',
       'X','EXPIRED', STATUS) STATUS
FROM   V$ARCHIVED_LOG
WHERE APPLIED   = 'NO'
  AND SEQUENCE# >=68404
  AND STATUS    != 'A'
ORDER BY SEQUENCE#,RESETLOGS_ID;

--=> DATABASE Standby: SQL>
--=1> No Error <1=--
no rows selected

--=> DATABASE Standby: SQL>
--=1> Error <1=--
                                                                         ARCHIVE LOG     APPLIED    DELETED
                                                                         RECEIVED FROM   ON         FROM
FILE_NAME                                          CREATOR     SEQUENCE# PRIMARY DB      STANDBY    STANDBY    STATUS
-------------------------------------------------- ------- ------------- --------------- ---------- ---------- ---------
FILE NOT ON DISK                                   ARCH            81858 YES             NO         YES        DELETED
FILE NOT ON DISK                                   ARCH            81859 YES             NO         YES        DELETED
/oracle/arc/arch1/ACME/ACME_1_81860_769561756.log  ARCH            81860 YES             NO         NO         Available
/oracle/arc/arch2/ACME/ACME_1_81861_769561756.log  ARCH            81861 YES             NO         NO         Available
...
500 rows selected

--=> Copy the above redo log files on the Primary to the physical Standby database and register them in the control file.
--=> NOTE1: I copied the file names from the above Primary Database select statement output.
            Then I used a note++ macro to add:
                  nice -20 scp ...oracle@osb-drc-ofs-001:/oracle/archlogs/ACME/;
--=> NOTE2: The nice -20 alows other processes to consume CPU amd RAM before this SCP process.
            That will reduce the impact on server performance if there is a shortage of CPU or RAM.

--=> SERVER Primary: OS>
nice -20 scp /oracle/archlogs/ACME/ACME_1_68311_769561756.log  oracle@osb-drc-ofs-001:/oracle/archlogs/ACME

---> Register the archive redo log files on the Standby control file
--=> DATABASE Standby SQL>
ALTER DATABASE REGISTER LOGFILE '/oracle/archlogs/ACME';

-- Update the RMAN repository and database control file with information about archive redo log files
--=> SERVER Standby  RMAN>
catalog start with '/oracle/archlogs/ACME';

-- Recover thet database using the archive redo log files.
--=> SERVER Standby RMAN>
RECOVER DATABASE;

---> Put the physical standby database into automatic recovery managed mode.
--=> DATABASE Primary: SQL>
alter system set log_archive_dest_state_2  = 'defer' scope=both;

SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL NAME FOR A30
COL VALUE FOR A15
COL DESCRIPTION FOR A60
select NAME, VALUE, DESCRIPTION from v$parameter where name = 'log_archive_dest_state_2';

--=> DATABASE Standby: SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate;

--=> DATABASE Standby: SQL>
Startup nomount;
alter database mount standby database;
alter database recover managed standby database using current logfile disconnect from session;

--=> DATABASE Primary: SQL>
alter system set log_archive_dest_state_2 = 'enable' scope=both;

SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL NAME FOR A30
COL VALUE FOR A15
COL DESCRIPTION FOR A60
select NAME, VALUE, DESCRIPTION from v$parameter where name = 'log_archive_dest_state_2';

--=> DATABASE Primary: SQL>
alter system switch logfile;
alter system switch logfile;

alter system checkpoint;
alter system archive log current;

---> Verify the physical standby database is automatic recovery managed mode and applying redo log data.
--=> DATABASE Standby: SQL>
--=> NOTE1: After a minute or so has passed, the Primary database (the ARCH1 process) will wake up and attempt
--=>        to ping the physical standby database.
--=>        Once it is determined that the physical standby database is mounted (and the Standby server TNS Listener is up),
--=>        it will start to push archived redo logs to the standby database.
--=> NOTE2: If MRPO has a status = APPLYING_LOG then the Gap is being closed
--=> NOTE3: There is a Redo Data gap if process MRPO has a status = WAIT_FOR_GAP
--=> NOTE4: If there is not MRP0 then Managed Recovery is not in Real Time Apply
--=> NOTE5: Verify the Gap is getting smaller over time
--=>
--=> Valid statuses are:
--=> STATUS   VARCHAR2(12) Current process status:
--=>     UNUSED - No active process
--=>     ALLOCATED - Process is active but not currently connected to a Primary database
--=>     CONNECTED - Network connection established to a Primary database
--=>     ATTACHED - Process is actively attached and communicating to a Primary database
--=>     IDLE - Process is not performing any activities
--=>     ERROR - Process has failed
--=>     OPENING - Process is opening the archived redo log
--=>     CLOSING - Process has completed archival and is closing the archived redo log
--=>     WRITING - Process is actively writing redo data to the archived redo log
--=>     RECEIVING - Process is receiving network communication
--=>     ANNOUNCING - Process is announcing the existence of a potential dependent archived redo log
--=>     REGISTERING - Process is registering the existence of a completed dependent archived redo log
--=>     WAIT_FOR_LOG - Process is waiting for the archived redo log to be completed
--=>     WAIT_FOR_GAP - Process is waiting for the archive gap to be resolved
--=>     APPLYING_LOG - Process is actively applying the archived redo log to the standby database
--=>
--=> DATABASE Standby SQL>
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL PROCESS FOR A38
select decode(process,'RFS','RFS-Remote file server',
                      'MRP0','MRP0-Detached recovery server process',
                      'MR(fg)','MR(fg)-Foreground recovery session',
                       'ARCH','ARCH-Archiver process',
                      'FGRD','FGRD',
                      'LGWR','LGWR',
                      'RFS(FAL)','RFS(FAL)',
                      'RFS(NEXP)','RFS(NEXP)',
                      'LNS','LNS-Network server process',
                       process) process,
       STATUS,
       THREAD#,
       SEQUENCE#
from v$managed_standby;

---> NOTE: We do not want the MRPO status = WAIT_FOR_GAP or ERROR
PROCESS                                STATUS             THREAD# SEQUENCE#
-------------------------------------- --------------- ---------- ---------
ARCH-Archiver process                  CLOSING                  1     68433
ARCH-Archiver process                  CONNECTED                0         0
RFS-Remote file server                 IDLE                     0
MRP0-Detached recovery server process  APPLYING_LOG             1     68434
RFS-Remote file server                 IDLE                     1

---> Verify the Gap is getting smaller over time
--=> DATABASE Standby SQL>
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL SEQ1 FOR A60 heading 'REDO LOG GAPS'
SELECT DECODE (
              (SELECT ltrim(to_char(COUNT(*),'999,999,999')) from v$archive_gap),'0',
              (select 'NO GAPS FOUND' FROM dual),
              (select 'LOW_SEQ: '||LOW_SEQUENCE#||' <> '||'HIGH_SEQ: '||HIGH_SEQUENCE#  SEQ1 from v$archive_gap)) SEQ1
FROM DUAL;

REDO LOG GAPS
------------------------------------------------------------
LOW_SEQ: 68404 <> HIGH_SEQ: 81410
---> Check to see if the Primary and Standby are in sync
--=>list # of redo logs that have not been applied on the standby
--=> DATABASE Primary: SQL>
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL LINE1 HEADING REDO_DATA_TRANSFER_INFORMATION FOR A60
SELECT ltrim(to_char(COUNT(*),'999,999,999'))||' TOTAL_LOGS' LINE1 FROM GV$ARCHIVED_LOG
UNION ALL
SELECT ltrim(to_char(COUNT(*),'999,999,999'))||' LOGS_APPLIED' FROM GV$ARCHIVED_LOG WHERE APPLIED='YES'
UNION ALL
SELECT ltrim(to_char(COUNT(*),'999,999,999'))||' LOGS_IN_MEMORY' FROM GV$ARCHIVED_LOG WHERE APPLIED='IN-MEMORY'
UNION ALL
SELECT ltrim(to_char(COUNT(*),'999,999,999'))||' LOGS_NOT_APPLIED' FROM GV$ARCHIVED_LOG WHERE APPLIED='NO'
UNION ALL
SELECT '=================' FROM DUAL
UNION ALL
SELECT DECODE (
              (SELECT ltrim(to_char(COUNT(*),'999,999,999')) FROM GV$ARCHIVED_LOG WHERE APPLIED='NO'),'0',
              (SELECT TO_CHAR(MAX(SEQUENCE#))||' MAX ARCH LOG SEQ APPLIED' FROM GV$ARCHIVED_LOG WHERE APPLIED='YES'),
              (SELECT TO_CHAR(MAX(SEQUENCE#))||' MAX ARCH LOG SEQ NOT APPLIED' FROM GV$ARCHIVED_LOG WHERE APPLIED='NO')) sql1
FROM DUAL
UNION ALL
SELECT DECODE (
             (SELECT ltrim(to_char(COUNT(*),'999,999,999')) FROM GV$ARCHIVED_LOG WHERE APPLIED='NO'),'0',
             ('ALL ARCH LOG SEQ APPLIED'),
              (SELECT TO_CHAR(MIN(SEQUENCE#))||' MIN ARCH LOG SEQ NOT APPLIED' FROM GV$ARCHIVED_LOG WHERE APPLIED='NO')) sql1
FROM DUAL;

--=> DATABASE Standby: SQL>
--=1> No Error <1=--
REDO_DATA_TRANSFER_INFORMATION
------------------------------------------------------------
922 TOTAL_LOGS
920 LOGS_APPLIED
2 LOGS_IN_MEMORY
0 LOGS_NOT_APPLIED
=================
81892 MAX ARCH LOG SEQ APPLIED
ALL ARCH LOG SEQ APPLIED

--=> DATABASE Standby: SQL>
--=1> Error <1=--
REDO_DATA_TRANSFER_INFORMATION
------------------------------------------------------------
18713 TOTAL_LOGS
9863 LOGS_APPLIED
2 LOGS_IN_MEMORY
8848 LOGS_NOT_APPLIED
=================
81768 MAX_ARCH_LOG_SEQ_NOT_PPLIED
71903 MIN_ARCH_LOG_SEQ_NOT_PPLIED
---> Check to see if Primary and standby databses are in sync
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL STANDBY_DEST FOR A7 HEADING STANDBY|DEST
COL "LAST SEQUENCE GENERATED" HEADING "LAST|SEQUENCE|GENERATED|IN PRIMARY"
COL "PRIME THREAD" HEADING "PRIME|THREAD"
COL "PRIME/STANDBY DIFFERENCE" HEADING "PRIME/STANDBY|DIFFERENCE"
COL "STANDBY THREAD" HEADING "STANDBY|THREAD"
COL "LAST SEQUENCE RECEIVED"  HEADING "LAST|SEQUENCE|RECEIVED|BY STANDBY"
COL "LAST SEQUENCE APPLIED"   FOR A15 HEADING "LAST|SEQUENCE|APPLIED|BY STANDBY"
COL "STANDBY ARCH/APPL DIFFERENCE" HEADING "STANDBY|ARCH/APPL|DIFFERENCE"
COL 'SEP' FOR A3 HEADING "-->|-->"
COL completion_time for a25
SELECT PRIME.STANDBY_DEST,
       PRIME."LAST SEQUENCE GENERATED",
       PRIME."THREAD" "PRIME THREAD",
      (PRIME."LAST SEQUENCE GENERATED" - ARCH.SEQUENCE#) "PRIME/STANDBY DIFFERENCE",
      '-->' "SEP",
      ARCH.STANDBY_DEST,
      ARCH.THREAD# "STANDBY THREAD",
      ARCH.SEQUENCE# "LAST SEQUENCE RECEIVED",
      '-->' "SEP",
      APPL.SEQUENCE# "LAST SEQUENCE APPLIED",
     (ARCH.SEQUENCE# - APPL.SEQUENCE#) "STANDBY ARCH/APPL DIFFERENCE",
     TO_CHAR(ARCH.COMPLETION_TIME,'MM/DD/YYYY HH:MM:SS AM') COMPLETION_TIME
FROM
(SELECT DISTINCT
        A.SEQUENCE# "LAST SEQUENCE GENERATED",
        A.THREAD# "THREAD",
        A.STANDBY_DEST
        FROM V$ARCHIVED_LOG A
        WHERE (A.THREAD#,A.FIRST_TIME ) IN
              (SELECT AA.THREAD#,
                  MAX(AA.FIRST_TIME)
               FROM V$ARCHIVED_LOG AA
             GROUP BY AA.THREAD#)) PRIME,
(SELECT DISTINCT
        C.THREAD#,
        C.STANDBY_DEST,
        C.SEQUENCE#,
        C.COMPLETION_TIME
        FROM V$ARCHIVED_LOG C
        WHERE (C.THREAD#,C.FIRST_TIME ) IN
              (SELECT CC.THREAD#,
                MAX(CC.FIRST_TIME)
             FROM V$ARCHIVED_LOG CC
             GROUP BY THREAD#)) ARCH,
(SELECT DECODE(
             (SELECT LTRIM(TO_CHAR(COUNT(*),'999,999,999')) FROM GV$ARCHIVED_LOG WHERE APPLIED='NO'),'0',
             (SELECT TO_CHAR(MAX(SEQUENCE#)) FROM GV$ARCHIVED_LOG WHERE (APPLIED='YES' OR APPLIED='IN-MEMORY')),
                      (SELECT TO_CHAR(MAX(SEQUENCE#)) FROM GV$ARCHIVED_LOG WHERE APPLIED='NO')) SEQUENCE#
        FROM DUAL) APPL
WHERE ARCH.THREAD# = 1
AND ARCH.THREAD#(+)=PRIME."THREAD"
AND   PRIME.STANDBY_DEST = ARCH.STANDBY_DEST
ORDER BY 10,1;

--=> NOTE: The database are in sync when the "Last Sequence Applied by Standby" is the same on the Primary and Standby result sets
--=>       and the "STANDBY ARCH/APPL DIFFERENCE" is 0 on Primary and Standby result sets.

--=> DATABASE Primary: SQL>
--=1> No Error <1=--

              LAST                                                       LAST     LAST
          SEQUENCE                                                   SEQUENCE     SEQUENCE               STANDBY
STANDBY  GENERATED      PRIME PRIME/STANDBY --> STANDBY    STANDBY   RECEIVED --> APPLIED                ARCH/APPL
DEST    IN PRIMARY     THREAD    DIFFERENCE --> DEST        THREAD BY STANDBY --> BY STANDBY  DIFFERENCE COMPLETION_TIME
------- ---------- ---------- ------------- --- ------- ---------- ---------- --- ----------- ---------- ----------------------
NO           81893          1             0 --> NO               1      81893 --> 81893                0 01/20/2014 09:01:10 pm
YES          81893          1             0 --> YES              1      81893 --> 81893                0 01/20/2014 09:01:09 pm

--=> DATABASE Standby: SQL>
--=1> No Error <1=--

              LAST                                                       LAST     LAST
          SEQUENCE                                                   SEQUENCE     SEQUENCE     STANDBY
STANDBY  GENERATED      PRIME PRIME/STANDBY --> STANDBY    STANDBY   RECEIVED --> APPLIED      ARCH/APPL
DEST    IN PRIMARY     THREAD    DIFFERENCE --> DEST        THREAD BY STANDBY --> BY STANDBY  DIFFERENCE COMPLETION_TIME
------- ---------- ---------- ------------- --- ------- ---------- ---------- --- ----------- ---------- ----------------------
NO           81893          1             0 --> NO               1      81893 --> 81893                0 01/20/2014 09:01:13 pm

---> Verify datafiles do not need recovery
--=> NOTE1: X$KCVFH contains information gathered directly from data file headers.
--=>        This is more accurate than information gathered from the control file.
--=>        The control file will be updated using data in the data file headers.
--=> NOTE2: The first number is the highest SEQ number in the redo log files.
--=>        The other numbers are the distinct set of SCN numbers in data file headers.
--=> NOTE3: Run the bellow select several times. The number of SEQUENCE numbers returned should be
--=>        changing as the MRP applied the redo data.
--=>        When the database is fully recovered there should only be 1 OR 2 SEQUENCE numbers.
--=>        One set for read only data files and possibly one set of read/write data files. Once the
--=>        SEQUENCE numbers stop changiing; if there are more than 2 SEQUENCE numbers then some of the data file
--=>        headers are not current and those datafiles need recovery. Use Procedure #3 to recover that database.

SET ECHO ON FEEDBACK ON  PAGESIZE 100 LINESIZE 200 TRIMSPOOL ON NUMWIDTH 13
COL CNT HEADING 'DATA FILES'
COL DTE FOR A25 HEADING 'DATE AND TIME'
BREAK ON REPORT
COMPUTE SUM OF CNT ON REPORT
SELECT TO_CHAR(SYSDATE,'MM-DD-YYYY HH:MI AM') DTE,
       FHTHR THREAD,
          FHRBA_SEQ SEQUENCE,
          COUNT(*) CNT
FROM X$KCVFH
GROUP BY FHTHR, FHRBA_SEQ
ORDER BY FHTHR, FHRBA_SEQ;
--=> DATABASE Standby: SQL>
--=1> Error <1=--
DATE and TIME                    THREAD      SEQUENCE    DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:49 PM                   0         0              2000
01-20-2014 09:49 PM                   1         78057           380
01-20-2014 09:49 PM                   1         81168            90
01-20-2014 09:49 PM                   1         81173           130
01-20-2014 09:49 PM                   1         81178            54
                                                      -------------
sum                                                            2654

--=> DATABASE Standby: SQL>
--=1> No Error <1=--
DATE and TIME                    THREAD      SEQUENCE    DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:58 PM                   1         81894          2654
                                                      -------------
sum                                                            2654
---> **************************************************************
---> Procedure #3 (RESOLVING A REDO LOG GAP and/or Re-syncing Primary and Standby)
---> <Use backed up data files from Primary to recover the data files on Standby>
---> NOTE: If this Procedure is not successful then try Procedure #4
---> **************************************************************

---> Identify datafiles that need recovery
--=> NOTE1: X$KCVFH contains information gathered directly from data file headers.
--=>        This is more accurate than information gathered from the control file.
--=>        The control file will be updated using data in the data file headers.
--=> NOTE2: The first number is the highest SEQ number in the redo log files.
--=>        The other numbers are the distinct set of SCN numbers in data file headers.
--=> NOTE3: Run the bellow select several times. The number of SEQUENCE numbers returned should be
--=>        changing as the MRP applied the redo data.
--=>        When the database is fully recovered there should only be 1 OR 2 SEQUENCE numbers.
--=>        One set for read only data files and possibly one set of read/write data files. Once the
--=>        SEQUENCE numbers stop changiing; if there are more than 2 SEQUENCE numbers then some of the data file
--=>        headers are not current and those datafiles need recovery.

SET ECHO ON FEEDBACK ON  PAGESIZE 100 LINESIZE 200 TRIMSPOOL ON NUMWIDTH 13
COL CNT HEADING 'DATA FILES'
COL DTE FOR A25 HEADING 'DATE AND TIME'
break on report
compute sum of cnt on report
select to_char(sysdate,'mm-dd-yyyy hh:mi AM') DTE,
       FHTHR Thread,
          FHRBA_SEQ Sequence,
          count(*) cnt
from X$KCVFH
group by FHTHR, FHRBA_SEQ
order by FHTHR, FHRBA_SEQ;

--=> DATABASE Standby: SQL>
--=1> Error <1=--
DATE and TIME                    THREAD      SEQUENCE    DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:49 PM                   0         0              2000
01-20-2014 09:49 PM                   1         78057           380
01-20-2014 09:49 PM                   1         81168            90
01-20-2014 09:49 PM                   1         81173           130
01-20-2014 09:49 PM                   1         81178            54
                                                      -------------
sum                                                            2654

--=> DATABASE Standby: SQL>
--=1> No Error <1=--
DATE and TIME                    THREAD      SEQUENCE    DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:58 PM                   1         81894          2654
                                                      -------------
sum                                                            2654
---> Repair damaged or in-consistent data files on the Standby database
--=> To repair the damaged data files we need to complete these steps:
1) RMAN> backup datafile from the Primary database.
   Syntax Example: backup datafile 297 format '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_297.ctl';
2) SERVER> copy the backup files from the Primary server to the Standby server.
   Syntax Example: scp *file_297.ctl oracle@osb-drc-ofs-001:/obackup/ACME/incremental_1/bk2
3) RMAN> catalog the backup files in the Standby database control file.
   Syntax Example: catalog backuppiece '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_297.ctl';
4) RMAN> Repair the damaged Standby data files.
   Syntax Example: restore datafile 297;

---> Preparation
--=> Run these commands on the Primary database before running the below dynamic SQL statements.
--=> DATABASE Primary RMAN>
sql 'alter system checkpoint';
sql 'alter system archive log current';
CONFIGURE CONTROLFILE AUTOBACKUP OFF;

---> Backup data files
--=> The below DQL dynamically produces RMAN statements to backup datafiles that need recovery.
--=> Run this DQL on the standby database. Run the RMAN "backup datafile" commands on the Primary database.
--=> NOTE:    x$kcvfh table column definitions:
              hxfil=DATAFILE NUMBER
              fhsta=DATAFILE HEADER STATUS
              fhscn=DATAFILE HEADER SCN
              fhrba_Seq=DATAFILE HEADER SEQUENCE

SET ECHO ON FEEDBACK ON  PAGESIZE 100 LINESIZE 200 TRIMSPOOL ON NUMWIDTH 13
COL SEQ1 FOR A120
SELECT 'BACKUP DATAFILE '||HXFIL||' FORMAT '||''''||'/OBACKUP/ACME/INCREMENTAL_1/BK2/INCREMENTAL_ACME_DATA_FILE_'||HXFIL||'.BAK'||''''||';' SEQ1
FROM X$KCVFH
WHERE FHSTA = 0
AND FHSCN = 0
AND FHRBA_SEQ = 0
ORDER BY HXFIL;

SEQ1
------------------------------------------------------------------------------------------------
backup datafile 195 format '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_195.bak';
backup datafile 196 format '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_196.bak';
backup datafile 197 format '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_197.bak';
---> SCP backed up data files
--=> The below select dynamically produces scp commands to copy the backed up files from the Primary server to the standby server.
--=> Run this DQL on the Standby database. Run the SCP commands from the Primary server.
select 'scp *file_'||hxfil||'.bak '||' oracle@osb-drc-ofs-001:/obackup/ACME/incremental_1/bk2' SEQ1
from x$kcvfh
where fhsta = 0
and fhscn = 0
and fhrba_Seq = 0
order by hxfil;

SEQ1
--------------------------------------------------------------------------
scp *file_3088.bak  oracle@osb-drc-ofs-001:/obackup/ACME/incremental_1/bk2
scp *file_3089.bak  oracle@osb-drc-ofs-001:/obackup/ACME/incremental_1/bk2

---> Catalog and Restore backed up data files
--=> The below select dynamically produces:
--=>     RMAN catalog commands to update the Standby database control file with datafile backup file location and name.
--=>     RMAN restore datafile commands to restore damaged datafiles on the Standby database.
--=> Run this DQL on the primary server. Run the RMAN Catalog and Restore statements on the Standby database.
--=> NOTE: The chr(10) command produces an ascii carriage return code
select 'catalog backuppiece '||''''||'/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_'||hxfil||'.bak'||''''||';'||chr(10)||
'restore datafile '||hxfil||';' SEQ1
from x$kcvfh
where fhsta != 0
and fhscn != 0
and fhrba_Seq != 0
and rownum < 10
order by hxfil;
SEQ1
------------------------------------------------------------------------------------------------------------------------
catalog backuppiece '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_1.bak';
restore datafile 1;

catalog backuppiece '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_2.bak';
restore datafile 2;

---> Recover the database to set the SCN in all datafile headers consistently with the same SCN.
--=> Run these commands on the Standby database after running the above restore datafile statements.
RECOVER DATABASE NOREDO;
---> Verify datafiles need recovery
--=> NOTE1: X$KCVFH contains information gathered directly from data file headers.
--=>        This is more accurate than information gathered from the control file.
--=>        The control file will be updated using data in the data file headers.
--=> NOTE2: The first number is the highest SEQ number in the redo log files.
--=>        The other numbers are the distinct set of SCN numbers in data file headers.
--=> NOTE3: Run the bellow select several times. The number of SEQUENCE numbers returned should be
--=>        changing as the MRP applied the redo data.
--=>        When the database is fully recovered there should only be 1 OR 2 SEQUENCE numbers.
--=>        One set for read only data files and possibly one set of read/write data files. Once the
--=>        SEQUENCE numbers stop changiing; if there are more than 2 SEQUENCE numbers then some of the data file
--=>        headers are not current and those datafiles need recovery.

SET ECHO ON FEEDBACK ON  PAGESIZE 100 LINESIZE 200 TRIMSPOOL ON NUMWIDTH 13
COL CNT HEADING 'DATA FILES'
COL DTE FOR A25 HEADING 'DATE AND TIME'
break on report
compute sum of cnt on report
select to_char(sysdate,'mm-dd-yyyy hh:mi AM') DTE,
       FHTHR Thread,
          FHRBA_SEQ Sequence,
          count(*) cnt
from X$KCVFH
group by FHTHR, FHRBA_SEQ
order by FHTHR, FHRBA_SEQ;

--=> DATABASE Standby: SQL>
--=1> Error <1=--
DATE and TIME                    THREAD      SEQUENCE    DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:49 PM                   0         0              2000
01-20-2014 09:49 PM                   1         78057           380
01-20-2014 09:49 PM                   1         81168            90
01-20-2014 09:49 PM                   1         81173           130
01-20-2014 09:49 PM                   1         81178            54
                                                      -------------
sum                                                            2654

--=> DATABASE Standby: SQL>
--=1> No Error <1=--
DATE and TIME                    THREAD      SEQUENCE    DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:58 PM                   1         81894          2654
                                                      -------------
sum                                                            2654

---> Open the database in Managed Standby recovery mode
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

---> *********************** <---
---> Post Procedure cleanup
---> *********************** <---

--=> Remove backup files used to restore and recover the standby database.
--=> DATABASE Standby: SQL>
cd /obackup/ACME/incremental_1/bk2/
rm *
cd /obackup/ACME/incremental_1/
rm *
cd /obackup/ACME/
rm -r ./incremental_1/bk2/

---> Remove files from RMAN
--=> Crosscheck deleted files and update the control file.
--=> DATABASE Standby: RMAN>
CROSSCHECK COPY;
CROSSCHECK backup of controlfile;
crosscheck backup;
crosscheck archivelog all;

---> Delete information about expired files from the database control file.
delete noprompt expired copy;
delete noprompt expired backupset;
delete noprompt expired backup;
delete noprompt obsolete;
delete noprompt expired archivelog all;

--=> DATABASE Primary: SQL>
cd /obackup/ACME/incremental_1/bk2/
rm *
cd /obackup/ACME/incremental_1/
rm *
cd /obackup/ACME/
rm -r ./incremental_1/bk2/

---> Remove files from RMAN
--=> Crosscheck deleted files and update the control file.
--=> DATABASE Primary: RMAN>
CROSSCHECK COPY;
CROSSCHECK backup of controlfile;
crosscheck backup;
crosscheck archivelog all;

---> Delete information about expired files from the database control file.
delete noprompt expired copy;
delete noprompt expired backupset;
delete noprompt expired backup;
delete noprompt obsolete;
delete noprompt expired archivelog all;
---> **************************************************************
---> Procedure #4 (RESOLVING A REDO LOG GAP and/or Re-syncing Primary and Standby)
---> <Eliminate the redo gap using an incremental backup from SCN>
---> NOTE: If this Procedure is not successful then try Procedure #5
---> **************************************************************

--=> DATABASE Primary: SQL>
alter system set log_archive_dest_state_2  = 'defer' scope=both;
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL NAME FOR A30
COL VALUE FOR A15
COL DESCRIPTION FOR A60
select NAME, VALUE, DESCRIPTION from v$parameter where name = 'log_archive_dest_state_2';

alter system switch logfile;
alter system switch logfile;

alter system checkpoint;
alter system archive log current;

--=> DATABASE Standby: SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
shutdown immediate;
Startup nomount;
alter database mount standby database;

--=> DATABASE Standby: SQL>
col CURRENT_SCN for 999,999,999,999,999
SELECT CURRENT_SCN FROM V$DATABASE;
         CURRENT_SCN
--------------------
      71,682,124,766

---> Server Primary: RMAN>
--=> NOTE: Make whatever changes are needed and then copy and paste this code into
--=>       a shell script and run the script from the OS.

--=> ************************* <=--
--=> Beginning of shell script <=--
--=> ************************* <=--

#!/bin/ksh
# ACME_hot_backup.sh
# Declare ORACLE environment variables
export ORACLE_SID=ACME
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=/app/oracle/product/11.2.0
export PATH=$PATH:${ORACLE_HOME}/bin
DATE_TIME="$(date +%m_%d_%Y_%H_%M_%S_%P)"
echo $DATE_TIME
export NLS_DATE_FORMAT='DD_MON_YYYY_HH24_MI_SS'

echo start_of_ACME_incremental_Backup | mail -s 2063108547@txt.att.net

#Perform the RMAN incremental HOT backup
rman nocatalog target / <<eof1
spool trace to /obackup/ACME/incremental_1/ACME_incremental_hot_backup_L1_$DATE_TIME.trc
spool log   to /obackup/ACME/incremental_1/ACME_incremental_hot_backup_L2_$DATE_TIME.log
set echo on

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/obackup/ACME/online/incremental_1/incremental_HOT_controlfile_snapshot.ctl';
SHOW SNAPSHOT CONTROLFILE NAME;

CONFIGURE CONTROLFILE AUTOBACKUP ON;
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/obackup/ACME/incremental_1/incremental_HOT_controlfile_autobackup_%F';

CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE COMPRESSION ALGORITHM 'BZIP2';
#NOTE: 'BZIP2' will compress more than 'BASIC' but will use more CPU. 'BASIC' is the default copression algorithm.

run {
allocate channel d1 type disk maxpiecesize=1g;
allocate channel d2 type disk maxpiecesize=1g;
allocate channel d3 type disk maxpiecesize=1g;
crosscheck backup;
crosscheck archivelog all;
delete noprompt expired backup;
delete noprompt expired archivelog all;
backup spfile format '/obackup/ACME/incremental_1/incremental_HOT_spfile_DB%d_Time%T_BSet%s_BPiece%p_ID%U' tag 'incr_spfile_bkp';
sql 'alter system checkpoint';
BACKUP INCREMENTAL FROM SCN 71360 DATABASE FORMAT '/obackup/ACME/incremental_1/ForStandby_backup_DB%d_Time%T_BSet%s_BPiece%p_ID%U' tag 'incr_db_bkp';
sql 'alter system checkpoint';
sql 'alter system archive log current';
copy current controlfile to '/obackup/ACME/incremental_1/incremental_HOT_control_file_current_copy.ctl' tag 'incr_ctrl_file_current_copy';
copy current controlfile for standby to '/obackup/ACME/incremental_1/incremental_HOT_control_file_for_standby.ctl' tag 'incr_ctrl_file_for_standby_copy';
backup format '/obackup/ACME/incremental_1/incremental_HOT_arclog_DB%d_Time%T_BSet%s_BPiece%p_ID%U' archivelog all tag 'incr_archive_log_file_bkp';
release channel d1;
release channel d2;
release channel d3;
}
eof1

#--=> Backup the control file to trace and report on RMAN backups
sqlplus /nolog <<eof2
connect / as sysdba
spool /obackup/ACME/incremental_1/incremental_ACME_hot_backup_report_L3_$DATE_TIME.log

select to_char(sysdate, 'mm-dd-yy hh:mi AM') start_date from dual;
alter database backup controlfile to trace as '/obackup/ACME/incremental_1/incremental_HOT_control_file_ACME_DDL.ctl';

SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
set underline '='
COL SESSION_STAMP               HEADING 'SESSION   |ID STAMP  '
COL STAMP                       HEADING 'UNIQUE     |OPERATION  |ID STAMP   '
COL OPERATION          FOR A29
COL OBJECT_TYPE        FOR A14  HEADING OBJECT|TYPE
COL OUTPUT_DEVICE_TYPE FOR A6   HEADING OUTPUT|DEVICE|TYPE
COL STATUS             FOR A28
COL OPTIMIZED          FOR A3   HEADING O|P|T|I|M|I|Z|E|D
COL LINE               FOR A3   HEADING '-->'
COL MBYTES_PROCESSED   FOR 999,999,999 HEADING MBYTES|PROCESSED
COL START_TIME         FOR A32
COL END_TIME           FOR A32

COL SORTING NEW_VALUE SORTING123 NOPRINT
BREAK ON SESSION_STAMP SKIP 1

select SESSION_STAMP,
       STAMP,
       decode (OPERATION,'CONTROL FILE AND SPFILE AUTOBACK','CONTROL AND SPFILE AUTOBACK',OPERATION) OPERATION,
       OBJECT_TYPE,
       OUTPUT_DEVICE_TYPE,
       STATUS,
       OPTIMIZED,
       MBYTES_PROCESSED,
       to_char(START_TIME, 'day mm-dd-yyyy hh:mi:ss AM') START_TIME,
       '-->' LINE,
       to_char(end_time, 'day mm-dd-yyyy hh:mi:ss AM') END_TIME,
       END_TIME SORTING
from v\$rman_status
where end_time is not null
order by sorting;

COL OPERATION          FOR A15
COL OBJECT_TYPE        FOR A9 HEADING OBJECT|TYPE
COL STATUS             FOR A10
COL MBYTES_PROCESSED   FOR 9,999,999,999 HEADING MBYTES|PROCESSED
select SESSION_STAMP,
       STAMP,
       OPERATION,
       OBJECT_TYPE,
       OUTPUT_DEVICE_TYPE,
       STATUS,
       OPTIMIZED,
       MBYTES_PROCESSED,
       to_char(START_TIME, 'day mm-dd-yyyy hh:mi:ss AM') START_TIME,
       '-->' LINE,
       to_char(end_time, 'day mm-dd-yyyy hh:mi:ss AM') END_TIME,
       END_TIME SORTING
from v\$rman_status
where end_time is not null
  and end_time > sysdate -7
  and object_type like 'DB%'
order by sorting;

select to_char(sysdate, 'mm-dd-yy hh:mi AM') backup_end_date from dual;
spool off;
eof2

echo end_of_ACME_incremental_Backup | mail -s 2063108547@txt.att.net

--=> ******************* <=--
--=> End of shell script <=--
--=> ******************* <=--
---> Copy the backup files to the standby server
--=> SERVER Primary: OS>
nice -20 scp -r /obackup/ACME/online/incremental_1/ForStandby_v* oracle@osb-drc-ofs-001:/obackup/ACME/online/incremental_1
nice -20 scp -r /obackup/ACME/online/incremental_1/ForStandby_0* oracle@osb-drc-ofs-001:/obackup/ACME/online/incremental_1

ps -elf|grep pmon
 F S      UID   PID  PPID   C PRI NI     ADDR     SZ    WCHAN    STIME TTY         TIME CMD
 0 S   oracle  7438 16945   0  40 20        ? 6383985        ?   Jun 22 ?           3:46 ora_pmon_ACME

NOTE: ACME pmon has a NICE priority of 20
ps -elf|grep scp
 F S      UID   PID  PPID   C PRI NI     ADDR     SZ    WCHAN    STIME TTY         TIME CMD
 0 S   oracle 12720  5028   0  40 20        ?    577        ? 12:08:56 pts/1       0:01 scp -r /obackup/ACME/online/incr
 0 S   oracle 12644  2474   0  40 20        ?    576        ? 12:08:35 pts/3       0:01 scp -r /obackup/ACME/online/incr

ps -elf|grep scp
 0 S   oracle 12720  5028   0  52 24        ?    577        ? 12:08:56 pts/1       0:02 scp -r /obackup/ACME/online/incr
 0 S   oracle 12644  2474   0  52 24        ?    576        ? 12:08:35 pts/3       0:02 scp -r /obackup/ACME/online/incr
NOTE: Items with a priority of 20 will consume CPU resources in preferance to items with a higher nice level. If there is not enough
      CPU available then items with a priority 20 will get CPU  and items with a priority of 24 will have to wait.

---> Update the database control file with information about the backup files
--=> DATABASE Standby: SQL>
select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
--=> NOTE: Should be mounted

---> RMAN Standby: RMAN>
---> Update the control file with incremental backup file information
rman target /
catalog start with '/oracle/archlogs/ACME';
--=> NOTE: Redo log files, text files etc. cannot be cataloged

--=> RMAN Standby: RMAN>
--=> Execute the REPORT SCHEMA statement to ensure that the standby database site
--=> is automatically registered and that the file names at the standby site are displayed:
REPORT SCHEMA;

---> Create a new control file for standby
--=> RMAN PRIMARY:  RMAN>
COPY CURRENT CONTROLFILE FOR STANDBY TO '/obackup/ACME/incremental/incremental_ACME_control_file_for_standby.ctl';
--=> NOTE: This syntax has worked as well: BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/obackup/ACME/incremental/ForStandbyCTRL.bck';

---> Copy the control file for standby to the standby server
--=> SERVER Primary: OS>
nice -20 scp /obackup/ACME/incremental/incremental_ACME_control_file_for_standby.ctl oracle@osb-drc-ofs-001:/obackup/ACME/incremental/

---> Restore the control file
---> Database Standby: SQL:>
alter database recover managed standby database cancel;
shutdown immediate;
startup nomount;

---> RMAN Standby: RMAN>
restore standby controlfile from '/obackup/ACME/incremental/incremental_ACME_control_file_for_standby.ctl';

--=> DATABASE Standby: SQL>
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL NAME FOR A10
COL DB_UNIQUE_NAME FOR A15
COL CONTROLFILE_TYPE FOR A14 HEADING 'CONTROL|FILE TYPE'
COL CURRENT_SCN FOR 999,999,999,999,999
SELECT NAME, DB_UNIQUE_NAME, DATABASE_ROLE,CONTROLFILE_TYPE, CURRENT_SCN FROM V$DATABASE;

                                            CONTROL
NAME       DB_UNIQUE_NAME  DATABASE_ROLE    FILE TYPE               CURRENT_SCN
---------- --------------- ---------------- -------------- --------------------
ACME       ACME_drc        PHYSICAL STANDBY STANDBY              71,694,700,214

---> Recover the database
---> RMAN Standby: RMAN>
RECOVER DATABASE NOREDO;
--=> If the below error is not returned then go to: ---> Open the database in Managed Standby recovery mode

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/22/2013 05:25:42
RMAN-06094: datafile 162 must be restored

---> Database Primary: SQL:>
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL TABLESPACE_NAME FOR A30
COL FILE_NAME FOR A70
select TABLESPACE_NAME,
       FILE_ID,
          FILE_NAME
from dba_data_files where FILE_ID = 162;

TABLESPACE_NAME                   FILE_ID FILE_NAME
------------------------------ ---------- ----------------------------------------------------------------------
ASSET_SYNC_DATA                       162 /oracle/data1/ACME/ASSET_SYNC_DATA01.dbf
---> Verify datafiles DO NOT need recovery
--=> NOTE1: X$KCVFH contains information gathered directly from data file headers.
--=>        This is more accurate than information gathered from the control file.
--=>        The control file will be updated using data in the data file headers.
--=> NOTE2: The first number is the highest SEQ number in the redo log files.
--=>        The other numbers are the distinct set of SCN numbers in data file headers.
--=> NOTE3: Run the bellow select several times. The number of SEQUENCE numbers returned should be
--=>        changing as the MRP applied the redo data.
--=>        When the database is fully recovered there should only be 1 OR 2 SEQUENCE numbers.
--=>        One set for read only data files and possibly one set of read/write data files. Once the
--=>        SEQUENCE numbers stop changing; if there are more than 2 SEQUENCE numbers then some of the data file
--=>        headers are not current and those datafiles need recovery.

SET ECHO ON FEEDBACK ON  PAGESIZE 100 LINESIZE 200 TRIMSPOOL ON NUMWIDTH 13
COL CNT HEADING 'DATA FILES'
COL DTE FOR A25 HEADING 'DATE AND TIME'
break on report
compute sum of cnt on report
select to_char(sysdate,'mm-dd-yyyy hh:mi AM') DTE,
       FHTHR Thread,
          FHRBA_SEQ Sequence,
          count(*) cnt
from X$KCVFH
group by FHTHR, FHRBA_SEQ
order by FHTHR, FHRBA_SEQ;

--=> DATABASE Standby: SQL>
--=1> Error <1=--
DATE and TIME                    THREAD      SEQUENCE    DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:49 PM                   0         0              2000
01-20-2014 09:49 PM                   1         78057           380
01-20-2014 09:49 PM                   1         81168            90
01-20-2014 09:49 PM                   1         81173           130
01-20-2014 09:49 PM                   1         81178            54
                                                      -------------
sum                                                            2654

--=> DATABASE Standby: SQL>
--=1> No Error <1=--
DATE and TIME                    THREAD      SEQUENCE    DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:58 PM                   1         81894          2654
                                                      -------------
sum                                                            2654

---> Repair damaged data files on the Standby database
--=> To repair the damaged data files we need to complete these steps:
--=>    1) RMAN> backup datafile from the Primary database.
--=>       Syntax Example: backup datafile 297 format '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_297.ctl';
--=>    2) SERVER> copy the backup files from the Primary server to the Standby server.
--=>       Syntax Example: scp *file_297.ctl oracle@osb-drc-ofs-001:/obackup/ACME/incremental_1/bk2
--=>    3) RMAN> catalog the backup files in the Standby database control file.
--=>       Syntax Example: catalog backuppiece '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_297.ctl';
--=>    4) RMAN> Repair the damaged Standby data files.
--=>       Syntax Example: restore datafile 297;

---> Preparation
--=> Run these commands on the Primary database before running the below dynamic SQL statements.
--=> DATABASE Primary RMAN>
sql 'alter system checkpoint';
sql 'alter system archive log current';
CONFIGURE CONTROLFILE AUTOBACKUP OFF;

---> Backup data files
--=> NOTE:    x$kcvfh table column definitions:
              hxfil=DATAFILE NUMBER
              fhsta=DATAFILE HEADER STATUS
              fhscn=DATAFILE HEADER SCN
              fhrba_Seq=DATAFILE HEADER SEQUENCE

--=> The below DQL dynamically produces RMAN statements to backup datafiles that need recovery.
--=> Run this DQL on the standby database. Run the RMAN "backup datafile" commands on the Primary database.
--=> Change file names and paths as needed.
SET ECHO ON FEEDBACK ON PAGESIZE 100 LINESIZE 200 TRIMSPOOL ON NUMWIDTH 13
COL SEQ1 FOR A120
select 'backup datafile '||hxfil||' format '||''''||'/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_'||hxfil||'.bak'||''''||';' SEQ1
from x$kcvfh
where fhsta = 0
and fhscn = 0
and fhrba_Seq = 0
order by hxfil;

SEQ1
------------------------------------------------------------------------------------------------------------------------
backup datafile 195 format '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_195.bak';
backup datafile 196 format '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_196.bak';
...
30 rows returned

---> SCP backed up data files
--=> The below select dynamically produces scp commands to copy the backed up files from the Primary server to the standby server.
--=> Run this DQL on the Standby database. Run the SCP commands from the Primry server.
--=> Change file names and paths as needed.
select 'scp *file_'||hxfil||'.bak '||' oracle@osb-drc-ofs-001:/obackup/ACME/incremental_1/bk2' SEQ1
from x$kcvfh
where fhsta = 0
and fhscn = 0
and fhrba_Seq = 0
order by hxfil;

SEQ1
------------------------------------------------------------------------------------------------------------------------
scp *file_195.bak  oracle@osb-drc-ofs-001:/obackup/ACME/incremental_1/bk2
scp *file_196.bak  oracle@osb-drc-ofs-001:/obackup/ACME/incremental_1/bk2
...
30 rows returned
---> Catalog and Restore backed up data files
--=> The below select dynamically produces:
--=>     RMAN catalog commands to update the Standby database control file with datafile backup file location and name.
--=>     RMAN restore datafile commands to restore damaged datafiles on the Standby database.
--=> Run this DQL on the primary server. Run the RMAN Catalog and Restore statements on the Standby database.
--=> NOTE: The chr(10) command produces an ascii carriage return code
--=> Change file names and paths as needed.
select 'catalog backuppiece '||''''||'/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_'||hxfil||'.bak'||''''||';'||chr(10)||
'restore datafile '||hxfil||';' SEQ1
from x$kcvfh
where fhsta != 0
and fhscn != 0
and fhrba_Seq != 0
and rownum < 10
order by hxfil;
SEQ1
-----------------------------------------------------------------------------------------
catalog backuppiece '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_195.bak';
restore datafile 195;

catalog backuppiece '/obackup/ACME/incremental_1/bk2/incremental_ACME_data_file_196.bak';
restore datafile 196;
...
30 rows returned

---> Recover the database to set the SCN in all datafile headers consistently with the same SCN.
--=> Run these commands on the Standby database after running the above restore datafile statements.
RECOVER DATABASE NOREDO;

---> Open the database in Managed Standby recovery mode
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
---> Check the MRP status
--=> DATABASE Standby: SQL>
--=> NOTE1: After a minute or so has passed, the primary database (the ARCH1 process) will wake up and attempt
--=>        to ping the physical standby database.
--=>        Once it is determined that the physical standby database is mounted (and the Standby server TNS Listener is up),
--=>        it will start to push archived redo logs to the standby database.
--=> NOTE2: If MRPO has a status = APPLYING_LOG then the Gap is being closed
--=> NOTE3: There is a Redo Data gap if process MRPO has a status = WAIT_FOR_GAP
--=> NOTE4: If there is not MRP0 then Managed Recovery is not in Real Time Apply
--=> NOTE5: Verify the Gap is getting smaller over time
--=>
--=> Valid statuses are:
--=> STATUS   VARCHAR2(12) Current process status:
--=>          UNUSED - No active process
--=>          ALLOCATED - Process is active but not currently connected to a primary database
--=>          CONNECTED - Network connection established to a primary database
--=>          ATTACHED - Process is actively attached and communicating to a primary database
--=>          IDLE - Process is not performing any activities
--=>          ERROR - Process has failed
--=>          OPENING - Process is opening the archived redo log
--=>          CLOSING - Process has completed archival and is closing the archived redo log
--=>          WRITING - Process is actively writing redo data to the archived redo log
--=>          RECEIVING - Process is receiving network communication
--=>          ANNOUNCING - Process is announcing the existence of a potential dependent archived redo log
--=>          REGISTERING - Process is registering the existence of a completed dependent archived redo log
--=>          WAIT_FOR_LOG - Process is waiting for the archived redo log to be completed
--=>          WAIT_FOR_GAP - Process is waiting for the archive gap to be resolved
--=>          APPLYING_LOG - Process is actively applying the archived redo log to the standby database
--=>
--=> DATABASE Standby SQL>
--=> DATABASE Standby SQL>
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL PROCESS FOR A38
select decode(process,'RFS','RFS-Remote file server',
                      'MRP0','MRP0-Detached recovery server process',
                      'MR(fg)','MR(fg)-Foreground recovery session',
                       'ARCH','ARCH-Archiver process',
                      'FGRD','FGRD',
                      'LGWR','LGWR',
                      'RFS(FAL)','RFS(FAL)',
                      'RFS(NEXP)','RFS(NEXP)',
                      'LNS','LNS-Network server process',
                       process) process,
              STATUS,
              client_process,
              thread#,
              SEQUENCE#
from v$managed_standby;
--=> DATABASE Standby: SQL>
--=1> Error <1=--
--=> NOTE: There is no MRP0 process therefore MRP is not running Real Time Apply.
PROCESS                                DEST STATUS          CLIENT_P       THREAD#     SEQUENCE#
-------------------------------------- -------------------- -------- ------------- -------------
ARCH-Archiver process                  CLOSING              ARCH                 1         81762
ARCH-Archiver process                  CONNECTED            ARCH                 0             0
ARCH-Archiver process                  CLOSING              ARCH                 1         81767
ARCH-Archiver process                  CLOSING              ARCH                 1         81763
ARCH-Archiver process                  CLOSING              ARCH                 1         81759
ARCH-Archiver process                  CLOSING              ARCH                 1         81764
ARCH-Archiver process                  CLOSING              ARCH                 1         81760
ARCH-Archiver process                  CLOSING              ARCH                 1         81765
RFS-Remote file server                 IDLE                 ARCH                 1         36602
RFS-Remote file server                 UNKNOWN              ARCH                 1         0

--=1> No Error <1=--
PROCESS                                DEST STATUS          CLIENT_P       THREAD#     SEQUENCE#
-------------------------------------- -------------------- -------- ------------- -------------
ARCH-Archiver process                  CLOSING              ARCH                 1         81762
ARCH-Archiver process                  CONNECTED            ARCH                 0             0
ARCH-Archiver process                  CLOSING              ARCH                 1         81767
ARCH-Archiver process                  CLOSING              ARCH                 1         81763
ARCH-Archiver process                  CLOSING              ARCH                 1         81759
ARCH-Archiver process                  CLOSING              ARCH                 1         81764
ARCH-Archiver process                  CLOSING              ARCH                 1         81760
ARCH-Archiver process                  CLOSING              ARCH                 1         81765
ARCH-Archiver process                  CLOSING              ARCH                 1         81761
ARCH-Archiver process                  CLOSING              ARCH                 1         81766
MRP0-Detached recovery server process  APPLYING_LOG         N/A                  1         81768

---> Verify the Gap is getting smaller over time
--=> DATABASE Standby SQL>
SET LINES 250 PAGES 99 TRIMSPOOL ON FEEDBACK ON HEADING ON
COL SEQ1 FOR A60 heading 'REDO LOG GAPS'
SELECT DECODE (
             (SELECT ltrim(to_char(COUNT(*),'999,999,999')) from v$archive_gap),'0',
             (select 'NO GAPS FOUND' FROM dual),
              (select 'LOW_SEQ: '||LOW_SEQUENCE#||' <> '||'HIGH_SEQ: '||HIGH_SEQUENCE#  SEQ1 from v$archive_gap)) SEQ1
FROM DUAL;

REDO LOG GAPS
------------------------------------------------------------
LOW_SEQ: 68404 <> HIGH_SEQ: 81410
---> Verify datafiles do not need recovery
--=> NOTE: X$KCVFH contains information gathered directly from data file headers. This is more accurate than
--=>       information in the control file. The control file will be updated using data in the data file headers.

---> Select distinct list of data file header SCN's
--=> NOTE:    The first number is the highest SEQ number in data files. The other numbers are the distinct set of SCN
              numbers in data file headers. There be 2 numbers (One for read only data files & one for read/write data files).
              If there are more than 2 numbers then some data file headers are not current and need recovery.

SET ECHO ON FEEDBACK ON PAGESIZE 100 LINESIZE 200 TRIMSPOOL ON NUMWIDTH 13
COL CNT HEADING 'DATA FILES'
COL DTE FOR A25 HEADING 'DATE AND TIME'
break on report
compute sum of cnt on report
select to_char(sysdate,'mm-dd-yyyy hh:mi AM') DTE,
       FHTHR Thread,
       FHRBA_SEQ Sequence,
       count(*) cnt
from X$KCVFH
group by FHTHR, FHRBA_SEQ
order by FHTHR, FHRBA_SEQ;

--=> The sequence categories should reduce over time as the datafile headers become consistent.
--=> DATABASE Standby: SQL>
--=1> Error <1=--
DATE and TIME                    THREAD      SEQUENCE      DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:28 PM                   0             0           232
01-20-2014 09:28 PM                   1         78057           105
01-20-2014 09:28 PM                   1         81168           158
01-20-2014 09:28 PM                   1         81169           227
01-20-2014 09:28 PM                   1         81170           242
01-20-2014 09:28 PM                   1         81171           358
01-20-2014 09:28 PM                   1         81172           242
01-20-2014 09:28 PM                   1         81173           300
01-20-2014 09:28 PM                   1         81174           240
01-20-2014 09:28 PM                   1         81175           122
01-20-2014 09:28 PM                   1         81176           360
01-20-2014 09:28 PM                   1         81177           182
01-20-2014 09:28 PM                   1         81178            57
                                                      -------------
sum                                                            2654

--=> DATABASE Standby: SQL>
--=2> Error <2=--
DATE and TIME                    THREAD      SEQUENCE    DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:42 PM                   0         0              2000
01-20-2014 09:42 PM                   1         78057           380
01-20-2014 09:42 PM                   1         81168            90
01-20-2014 09:42 PM                   1         81169            90
01-20-2014 09:42 PM                   1         81173           130
01-20-2014 09:42 PM                   1         81178            54
                                                      -------------
sum                                                            2654


--=> DATABASE Standby: SQL>
--=3> Error <3=--
DATE and TIME                    THREAD      SEQUENCE    DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:49 PM                   0         0              2000
01-20-2014 09:49 PM                   1         78057           380
01-20-2014 09:49 PM                   1         81168            90
01-20-2014 09:49 PM                   1         81173           130
01-20-2014 09:49 PM                   1         81178            54
                                                      -------------
sum                                                            2654

--=> DATABASE Standby: SQL>
--=1> No Error <1=--
DATE and TIME                    THREAD      SEQUENCE    DATA FILES
------------------------- ------------- ------------- -------------
01-20-2014 09:52 PM                   1         81894          2654
                                                      -------------
sum                                                            2654


---> *********************** <---
---> Post Procedure cleanup
---> *********************** <---

---> Remove files from disk
--=> DATABASE Standby: SQL>
cd /obackup/ACME/incremental_1/bk2/
rm *
cd /obackup/ACME/incremental_1/
rm *
cd /obackup/ACME/
rm -r ./incremental_1/bk2/

---> Remove files from RMAN
--=> Crosscheck deleted files and update the control file.
--=> DATABASE Standby: RMAN>
CROSSCHECK COPY;
CROSSCHECK backup of controlfile;
crosscheck backup;
crosscheck archivelog all;

---> Delete information about expired files from the database control file.
delete noprompt expired copy;
delete noprompt expired backupset;
delete noprompt expired backup;
delete noprompt obsolete;
delete noprompt expired archivelog all;

--=> DATABASE Primary: SQL>
cd /obackup/ACME/incremental_1/bk2/
rm *
cd /obackup/ACME/incremental_1/
rm *
cd /obackup/ACME/
rm -r ./incremental_1/bk2/

---> Remove files from RMAN
--=> Crosscheck deleted files and update the control file.
--=> DATABASE Primary: RMAN>
CROSSCHECK COPY;
CROSSCHECK backup of controlfile;
crosscheck backup;
crosscheck archivelog all;

---> Delete information about expired files from the database control file.
delete noprompt expired copy;
delete noprompt expired backupset;
delete noprompt expired backup;
delete noprompt obsolete;
delete noprompt expired archivelog all;
---> **************************************************************
---> Procedure #5 (RESOLVING A REDO LOG GAP and/or Re-syncing Primary and Standby)
---> <Re-build the standby using a backup of the Primary database>
---> NOTE: Completly re-building a standby database is beond the scope of this document. See document ??????
---> **************************************************************



---> ******** <---
---> APPENDIX <---
---> ******** <---
------------------------------------------------
Check to see if the database needs recovery
------------------------------------------------
---> Verify datafiles do not need recovery
--=> NOTE: X$KCVFH contains information gathered directly from data file headers.
--=>       This is more accurate than information gathered from the control file.
--=>       The control file will be updated using data in the data file headers.

--=> X$KCVFH table definition:
The table X$KCVFH is a fixed table in Oracle. It stores the file headers along with their statuses. The name is derived from:
K  – Kernel layer
C  – Cache layer
V  – recoVery component
FH - File Header

---> FHSTA column definition:
FHSTA  = File Header Status
Status = 0: DB is consistent. No more recovery required.
Status = 1: DB needs more recovery. Apply the archive logs in serial order until you find the status change to 0.
Status = 4: DB is in a FUZZY state. This indicates the backup was not entirely successful.
Status = 8192: ?
Status = 8196: ?

--=> DATABASE Primary: SQL>
--=> Select read only tablespaces
select fhrba_seq, count(*) cnt
from x$kcvfh
where fhtnm in (select tablespace_name from dba_tablespaces where status ='READ ONLY')
group by fhrba_seq;

 FHRBA_SEQ        CNT
---------- ----------
       396        234  ---> These are all read only tablepaces

---> Select distinct list of data file header SCN's
--=> NOTE: The first number is the highest SEQ number in the redo log files.
           The other numbers are the distinct set of SCN numbers in data file headers.
              There should only be 2 numbers. (One set for read only data files and one set of read/write data files)
              If there are more than 2 numbers then some of the data file headers are not current and those datafiles need recovery.

COL FILES_WITH_SCN FOR 999,999 HEADING NUMBER|FILES|WITH|SCN
COL FILE_SCN_NUMBER FOR 9999999 HEADING FILE|SCN|NUMBER
select fhrba_seq file_scn_number, count(*) Files_with_SCN
from x$kcvfh
group by fhrba_seq
order by 1;

--=> DATABASE Standby: SQL>
           NUMBER
    FILE    FILES
     SCN     WITH
  NUMBER      SCN
-------- --------
     396      234 ---> These are read only tablepaces
   63245    2,680 ---> These are read/write tablepaces

--=> DATABASE Standby: SQL>
--=> Select count of data file recovery statuses
set lines 100
COL STATUS FOR 999,999
COL STATUS_COUNT FOR 999,999
COL NUMBER_OF_FILES HEADING NUMBER|OF|FILES
BREAK ON REPORT
COMPUTE SUM OF NUMBER_OF_FILES ON REPORT

select  count(fhsta) number_of_files,
        fhsta STATUS,
        decode(fhsta,0,'No recovery needed',8192, 'Status unknown',8196, 'Status unknown','Needs recovery')  recovery_status
from x$kcvfh
group by fhsta
order by 2;

    number
        of          RECOVERY
     files   STATUS STATUS
---------- -------- --------------------
      2913        0 No recovery needed
         1    8,192 Status unknown
----------
      2914

NOTE: If COMPATIBLE is set to 10.0.0.0 or higher X$KCVFH.FHSTA column will show 8196
      for the system datafile. The FHSTA column for system datafile will have a
      value of 4. The value of 8196 for the fhsta (status) column for the first system
      tablespace datafile is normal.

prompt show parameter COMPATIBLE









Posted by MADHU APPS at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)