The following solution can be useful when there is a gap of some archive log at standby which was physically removed/delted 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 cluase
----Take Controlfile backup for standby
--- Copy the backup peace to standby and catalog it
--- Recover the Databaes 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
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: Stadnby :
========================
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
No comments:
Post a Comment