Roll-Forward a Standby Database using RMAN Incremental backup

-
The following solution can be useful when there is a gap of some archive log at standby which was physically removed/deleted at production end,  
And it was not applied to standby database.


Solution Out line:

Take the scn of last log missing from Standby, Take RMAN incremental backup using SCN clause from production, and apply on standby. refer Oracle Support doc...
  
  Findout Which archive Log is missing and MRP Process waiting for which Archivelog
  Findout the Current SCN of Standby Database
  Cancel the Recovery mode of Standby DB
  TAKE RMAN incremental Backup in Production Database using FROM SCN clause
  Take Controlfile backup for standby
  Copy the backup peace to standby and catalog it
  Recover the Database with NOREDO
  Switch database to copy or Change datafile names
  Start the recovery mode of Standby DB

Ref: 836986.1 and 1531031.1


Steps to perform for Rolling forward a standby database using RMAN incremental backup when primary and standby are in ASM filesystem (Doc ID 836986.1)

Primary: PRODDB
Standby: STDBYDB

1) Find Gap of Primary and Standby :

SQL-PRODDB> select a.dest_id,a.thread#, applied "Last Applied Seq", b.notappld "Current Seq", b.notappld-applied "Difference" from (select dest_id,thread#, max(sequence#) applied from gv$archived_log where applied='YES' and RESETLOGS_TIME=(select RESETLOGS_TIME from v$database) group by dest_id,thread#) A, (select thread#,max(sequence#) notappld from gv$archived_log where RESETLOGS_TIME=(select RESETLOGS_TIME from v$database) and dest_id=1 group by thread#) B where a.thread# = b.thread# order by 1,2;

   DEST_ID    THREAD# Last Applied Seq Current Seq Difference
---------- ---------- ---------------- ----------- ----------
         2          1           656650      657073        423
         2          2            45754       47002       1248
         2          3            45530       45953        423
         2          4            44694       45116        422


SQL-STDBYDB> select NAME,VALUE,UNIT,TIME_COMPUTED from v$dataguard_stats;

NAME                      VALUE                UNIT                           TIME_COMPUTED

 
------------------------- -------------------- ------------------------------ -------------------------

 
transport lag             +00 06:03:14         day(2) to second(0) interval   08/15/2019 23:36:50
apply lag                 +00 06:03:14         day(2) to second(0) interval   08/15/2019 23:36:50
apply finish time                                  day(2) to second(3) interval   08/15/2019 23:36:50
estimated startup time    16                   second                         08/15/2019 23:36:50

Found some of the archive logs not available on ASM disk and crosscheck of archivelog 

archived log file name=+DG_ARCH/PRODDB/1_657012_740066546.dbf RECID=375919 STAMP=1016399390 validation failed for archived log
archived log file name=+DG_ARCH/PRODDB/1_657013_740066546.dbf RECID=375931 STAMP=1016399421 validation failed for archived log
archived log file name=+DG_ARCH/PRODDB/1_657014_740066546.dbf RECID=375945 STAMP=1016399451 validation failed for archived log
archived log file name=+DG_ARCH/PRODDB/1_657015_740066546.dbf RECID=375957 STAMP=1016399481 validation failed for archived log
archived log file name=+DG_ARCH/PRODDB/1_657016_740066546.dbf RECID=375965 STAMP=1016399511 validation failed for archived log
archived log file name=+DG_ARCH/PRODDB/1_657017_740066546.dbf RECID=375977 STAMP=1016399541 validation failed for archived log
archived log file name=+DG_ARCH/PRODDB/1_657018_740066546.dbf RECID=375989 STAMP=1016399568 validation failed for archived log

archived log file name=+DG_ARCH/PRODDB/1_657019_740066546.dbf RECID=376001 STAMP=1016399595 validation failed for archived log



Gap: Standby out of sync for 6+ hrs 


Check Current SCN in Standby and Primary
------------------------------------------
Primary: PRODDB
-------------------
SQL> select inst_id,name,database_role,TO_CHAR(current_scn),DB_UNIQUE_NAME from gv$database order by inst_id;

   INST_ID NAME      DATABASE_ROLE    TO_CHAR(CURRENT_SCN)                     DB_UNIQUE_NAME
---------- --------- ---------------- ---------------------------------------- ------------------------------
         1 BAPP      PRIMARY          15657600439478                           PRODDB
         2 BAPP      PRIMARY          15657600439478                           PRODDB
         3 BAPP      PRIMARY          15657600439478                           PRODDB
         4 BAPP      PRIMARY          15657600439478                           PRODDB

SQL>

Standby: STDBYDB 
-------------------

SQL> select inst_id,name,database_role,TO_CHAR(current_scn),DB_UNIQUE_NAME from gv$database order by inst_id;

   INST_ID NAME      DATABASE_ROLE    TO_CHAR(CURRENT_SCN)                     DB_UNIQUE_NAME
---------- --------- ---------------- ---------------------------------------- ------------------------------
         1 BAPP      PHYSICAL STANDBY 15657557120593                           STDBYDB
         2 BAPP      PHYSICAL STANDBY 15657557120593                           STDBYDB
         3 BAPP      PHYSICAL STANDBY 15657557120593                           STDBYDB
         4 BAPP      PHYSICAL STANDBY 15657557120593                           STDBYDB

SQL>

Check Datafile SCN:
-------------------

SQL> select to_char(min(checkpoint_change#)) from v$datafile_header;  
                                                                          
TO_CHAR(MIN(CHECKPOINT_CHANGE#))
----------------------------------------
15657557120594

Take SCN which ever is lesser


Primary: PRODDB
-------------------
Find if there are any files added to primary database after current scn of standby database. 

SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 15657557120593;

no rows selected

No files added, so Doc ID 836986.1 can be taken as reference 
if files added Doc ID 1531031.1 can be taken as reference


Primary: PRODDB
------------------
Take an incremental backup of the PRIMARY database:


RMAN> BACKUP INCREMENTAL FROM SCN 15657557120593 DATABASE FORMAT '/dbbackup01/PRODDB/ForStandby_%U' tag 'FORSTANDBY';

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/dbbackup01/PRODDB/ForStandbyCTRL.bck';



Standby:STDBYDB
-------------------

Stop All Instances of standby database except recovery instance

SQL> alter database recover managed standby database cancel;

 


$ srvctl stop instance -db STDBYDB -instance "STDBYDB1"
$ srvctl stop instance -db STDBYDB -instance "STDBYDB2"
$ srvctl stop instance -db STDBYDB -instance "STDBYDB3"

Copy backup files to  standby location

cd /dbbackup01/STDBYDB/
scp oracle@uiiompd63:/dbbackup01/PRODDB/ForStandby* .



Catalog backup files
RMAN> CATALOG START WITH '/dbbackup01/STDBYDB/ForStandby_'; 


Recover the STANDBY database with the cataloged incremental backup:

RMAN> RECOVER DATABASE NOREDO;


RMAN> RECOVER DATABASE NOREDO;

Starting recover at 16-AUG-19

:
:
:
channel ORA_DISK_4: restore complete, elapsed time: 00:00:25
channel ORA_DISK_4: starting incremental datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
destination for restore of datafile 00018: +DG_ORA4/STDBYDB/bap_data_l_03.dbf
channel ORA_DISK_4: reading from backup piece /dbbackup01/STDBYDB/ForStandby_k1u99t8k_1_1
channel ORA_DISK_4: piece handle=/dbbackup01/STDBYDB/ForStandby_k1u99t8k_1_1 tag=FORSTANDBY
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: piece handle=/dbbackup01/STDBYDB/ForStandby_k0u99t7r_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_3: piece handle=/dbbackup01/STDBYDB/ForStandby_jtu99t19_1_1 tag=FORSTANDBY
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:57
channel ORA_DISK_2: piece handle=/dbbackup01/STDBYDB/ForStandby_jru99t19_1_1 tag=FORSTANDBY
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:02:38

Finished recover at 16-AUG-19




Capture datafile information in STANDBY and Primary database.
------------------------------------------------------------

Standby: STDBYDB
-------------------
spool datafile_names_stabdby.txt
set lines 200
col name format a60
select file#, name from v$datafile order by file# ;
spool off

     FILE# NAME
---------- ------------------------------------------------------------
         1 +DG_ORA3/STDBYDB/system01.dbf
         2 +DG_ORA3/STDBYDB/sysaux01.dbf
         3 +DG_ORA3/STDBYDB/undotbs01.dbf
         4 +DG_ORA3/STDBYDB/users01.dbf
         5 +DG_ORA3/STDBYDB/bap_data_s_01.dbf
         6 +DG_ORA3/STDBYDB/bap_data_l_01.dbf
         7 +DG_ORA3/STDBYDB/bap_data_m_01.dbf
         8 +DG_ORA3/STDBYDB/bap_idx_m_01.dbf
         9 +DG_ORA3/STDBYDB/bap_idx_l_01.dbf
        10 +DG_ORA4/STDBYDB/bap_data_s_01.dbf
        11 +DG_ORA3/STDBYDB/bap_idx_l_02.dbf

     FILE# NAME
---------- ------------------------------------------------------------
        12 +DG_ORA4/STDBYDB/bap_idx_l_03.dbf
        13 +DG_ORA3/STDBYDB/bap_idx_l_04.dbf
        14 +DG_ORA4/STDBYDB/bap_data_l_02.dbf
        15 +DG_ORA4/STDBYDB/bap_idx_l_05.dbf
        16 +DG_ORA4/STDBYDBbap_idx_m_02.dbf  
        17 +DG_ORA4/STDBYDBbap_data_m_02.dbf 
        18 +DG_ORA4/STDBYDB/bap_data_l_03.dbf
        19 +DG_ORA3/STDBYDB/undotbs02.dbf
        20 +DG_ORA3/STDBYDB/undotbs03.dbf
        21 +DG_ORA3/STDBYDB/undotbs04.dbf
        22 +DG_ORA4/STDBYDB/bap_data_l_04.dbf

     FILE# NAME
---------- ------------------------------------------------------------
        23 +DG_ORA4/STDBYDB/bap_data_l_05.dbf




Primary: PRODDB
------------------
spool datafile_names_Primary.txt
set lines 200
col name format a60
select file#, name from v$datafile order by file# ;
spool off



Standby: STDBYDB
-------------------
Connect to STANDBY database and restore the standby control file:
----------------------------------------------------------------------
RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT; 
RMAN> RESTORE STANDBY CONTROLFILE FROM '/dbbackup01/PRODDB/ForStandbyCTRL.bck'; 

Starting restore at 16-AUG-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=130 instance=STDBYDB4 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DG_ORA3/STDBYDB/CONTROLFILE/control01.ctl

 

output file name=+DG_ORA4/STDBYDB/CONTROLFILE/control02.ctl
Finished restore at 16-AUG-19


Shut down the STANDBY database and startup mount:
-------------------------
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;


---> Catalog datafiles in STANDBY if location/name of datafiles is different

RMAN> CATALOG START WITH '+DATA/mystd/datafile/'; 
SQL>SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 3162298
If the above query returns with 0 zero rows, you can switch the datafiles. This will rename the datafiles to its correct name at the standby site:

RMAN> SWITCH DATABASE TO COPY; 


*****************************

Chance of error:
==================
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of switch to copy command at 09/04/2019 16:03:22
RMAN-06571: datafile 8 does not have recoverable copy

follow below procedure Ref: Doc ID 1339439.1

Check Missing datafiles:
RMAN> report schema;

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------

 

1    0        SYSTEM               ***     +DG_ORA1/ndceivr1/datafile/system.282.965043423
2    0        SYSAUX               ***     +DG_ORA1/ndceivr1/datafile/sysaux.283.965043427
3    0        UNDOTBS1             ***     +DG_ORA1/ndceivr1/datafile/undotbs1.284.965043431
4    0        UNDOTBS2             ***     +DG_ORA1/ndceivr1/datafile/undotbs2.286.965043437
5    0        UNDOTBS3             ***     +DG_ORA1/ndceivr1/datafile/undotbs3.287.965043439
6    0        UNDOTBS4             ***     +DG_ORA1/ndceivr1/datafile/undotbs4.288.965043439
7    0        USERS                ***     +DG_ORA1/ndceivr1/datafile/users.289.965043441
8    30720    CALL_REC_STAGE       ***     +DG_ORA1/ndceivr1/call_rec_stage_data_001.dbf



Rename Datafile with below commands

run
{
switch datafile '+DG_ORA1/ndceivr1/datafile/system.282.965043423' to datafilecopy '+DG_ORA1/ndceivr1/datafile/system.278.966808907';
}
run{
switch datafile '+DG_ORA1/ndceivr1/datafile/sysaux.283.965043427'  to datafilecopy '+DG_ORA1/ndceivr1/datafile/sysaux.277.966808907';
}
run{
switch datafile '+DG_ORA1/ndceivr1/datafile/undotbs1.284.965043431'  to datafilecopy '+DG_ORA1/ndceivr1/datafile/undotbs1.279.966808907';
}
run{
switch datafile '+DG_ORA1/ndceivr1/datafile/undotbs2.286.965043437' to datafilecopy  '+DG_ORA1/ndceivr1/datafile/undotbs2.280.966808945';
}
run
{
switch datafile '+DG_ORA1/ndceivr1/datafile/undotbs3.287.965043439' to datafilecopy '+DG_ORA1/ndceivr1/datafile/undotbs3.281.966808945';
}
run{
switch datafile '+DG_ORA1/ndceivr1/datafile/undotbs4.288.965043439' to datafilecopy '+DG_ORA1/ndceivr1/datafile/undotbs4.282.966808947';
}
run{
switch datafile '+DG_ORA1/ndceivr1/datafile/users.289.965043441' to datafilecopy '+DG_ORA1/ndceivr1/datafile/users.283.966808955';
}
run{
switch datafile '+DG_ORA1/ndceivr1/datafile/sysaux.340.1011615095' to datafilecopy '+DG_ORA1/ndceivr1/datafile/sysaux.331.1011629663';
}




***************************************************************************************

SQL> select name,database_role,to_char(current_scn) from v$database;

NAME      DATABASE_ROLE    TO_CHAR(CURRENT_SCN)
--------- ---------------- ----------------------------------------
PRODDB      PHYSICAL STANDBY 15657600439478



SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;




Status: Primary:
============
SQL> select a.dest_id,a.thread#, applied "Last Applied Seq", b.notappld "Current Seq", b.notappld-applied "Difference" from
(select dest_id,thread#, max(sequence#) applied from gv$archived_log where applied='YES' and RESETLOGS_TIME=(select RESETLOGS_TIME from v$database) group by dest_id,thread#) A, (select thread#,max(sequence#) notappld from gv$archived_log where RESETLOGS_TIME=(select RESETLOGS_TIME from v$database) and dest_id=1 group by thread#) B where a.thread# = b.thread# order by 1,2;

   DEST_ID    THREAD# Last Applied Seq Current Seq Difference
---------- ---------- ---------------- ----------- ----------
         2          1           657121      657121          0
         2          2            47050       47051          1
         2          3            46002       46002          0
         2          4            45164       45164          0

SQL> SQL> SQL>


Status: Standby :
========================
select NAME,VALUE,UNIT,TIME_COMPUTED from v$dataguard_stats;

NAME                      VALUE                UNIT                           TIME_COMPUTED
------------------------- -------------------- ------------------------------ -------------------------
transport lag             +00 00:00:00         day(2) to second(0) interval   08/16/2019 03:28:51
apply lag                 +00 00:00:00           day(2) to second(0) interval   08/16/2019 03:28:51
apply finish time    +00 00:00:00.000     day(2) to second(3) interval   08/16/2019 03:28:51
estimated startup time    14                        second                                  08/16/2019 03:28:51



Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more