Due to any reason (given below), If you found that the standby database is lagging behind with the primary database with huge number of archives, in this case we have 2 approaches to bring the standby in synch with primary database as below:
1) Take the Incremental backup from primary & path it on Standby.
2) Rebuild the standby database from active primary database.
1) Take the Incremental backup from primary & path it on Standby
Possible reasons for standby lagging behind the primary.
a) Archive log at primary database location is corrupted.
b) Archive log at primary database location removed accidently.
c) All the archive logs are available at standby location but will take long time to synch with primary database, which is not acceptable by business.
What needs be done in this case?
If you encountered such situation, don’t get panic & follow below steps.
1) Check & Cancel the Managed recovery process (MRP) on standby database.
SQL> Select PROCESS, STATUS, THREAD#, SEQUENCE# from v$managed_standby where process like 'MRP%';
SQL> alter database recover managed standby database cancel;
2) Shutdown the standby database.
SQL> shut immediate
3) Check the last applied archive on to the standby database.
select max(SEQUENCE#) from v$archived_log where APPLIED='YES' and THREAD#='1';
select max(SEQUENCE#) from v$archived_log where APPLIED='YES' and THREAD#='2';
select current_scn from v$database;
4) Take the SCN based Incremental backup from production database.
RMAN> run { allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup as compressed backupset incremental from scn <SCN NUMBER> database format '<PATH>/FOR_STDBY_BKP_%U.bkp';
};
5) Create a new standby controlfile form production.
alter database create standby controlfile as '<ABSOLUTE_PATH>/DEL1_standby.ctl';
OR
RMAN> backup current controlfile for standby format 'Z:\BACKUP\FORSTDBYCTRL.bkp';
6) Transfer/Copy the incremental backup & the new standby control file at the standby site.
7) Startup the standby database in nomount state.
SQL> startup nomount;
8) Find the exact path of the standby control files.
SQL> show parameter control_files.
9) Invoke the RMAN on standby site & restore the new standby control file.
$ rman target=/
RMAN> RMAN> RESTORE STANDBY CONTROLFILE FROM 'C:\TEMP\INCR_BACKUP\FORSTDBYCTRL.BKP';
10) Start-up the database in mount state.
SQL> alter database mount standby database;
11) Register the incremental backup on the newly restored standby control file.
RMAN> catalog start with 'C:\FRA\SATISTD\BACKUPSET';
After this, RMAN will ask for the confirmation to catalog/register the backup. Say YES.
Do you really want to catalog the above files (enter YES or NO)? yes
12) Restore the newly catalog/registered backup & recover the database.
RMAN> recover database;
13) After the completion/end of recovery, it will come out with error/warning, please ignore it.
14) Now start the Managed recovery process onto the standby database.
SQL> alter database recover managed standby database disconnect from session;
15) Open/tail an alert log file on standby database to see the current status/errors.
SQL> show parameter dump
SQL> exit
Cd <Absolute path of the standby alert logfile>
Tail -40f <Name of alertlog file >
16) On primary database check the synch status by running below command.
set scan on
set feed off
set linesize 200
BREAK ON ROW SKIP 1s
column thread format a6;
column "PR - Archived" format a13;
column "STBY - Archived" format a15;
column "STBY - Applied" format a14;
column "Shipping GAP (PR -> STBY)" format a25;
column "Applied GAP (STBY -> STBY)" format a26;
--ACCEPT DEST PROMPT 'Enter the Standby Archive Log Destination : '
SELECT *
FROM (SELECT LPAD (t1, 4, ' ') "Thread",
LPAD (pricre, 9, ' ') "PR - Archived",
LPAD (stdcre, 10, ' ') "STBY - Archived",
LPAD (stdnapp, 9, ' ') "STBY - Applied",
LPAD (pricre - stdcre, 13, ' ')
"Shipping GAP (PR -> STBY)",
LPAD (stdcre - stdnapp, 15, ' ')
"Applied GAP (STBY -> STBY)"
FROM ( SELECT MAX (sequence#) stdcre, thread# t1
FROM v$archived_log
WHERE standby_dest = 'YES'
AND resetlogs_id IN
(SELECT MAX (RESETLOGS_ID)
FROM v$archived_log)
AND thread# IN (1, 2, 3, 4)
GROUP BY thread#) a,
( SELECT MAX (sequence#) stdnapp, thread# t2
FROM v$archived_log
WHERE standby_dest = 'YES'
AND resetlogs_id IN
(SELECT MAX (RESETLOGS_ID)
FROM v$archived_log)
AND thread# IN (1, 2, 3, 4)
AND applied = 'YES'
GROUP BY thread#) b,
( SELECT MAX (sequence#) pricre, thread# t3
FROM v$archived_log
WHERE standby_dest = 'NO'
AND resetlogs_id IN
(SELECT MAX (RESETLOGS_ID)
FROM v$archived_log)
AND thread# IN (1, 2, 3, 4)
GROUP BY thread#) c
WHERE a.t1 = b.t2 AND b.t2 = c.t3 AND c.t3 = a.t1)
ORDER BY 1
/
set feed on
break on off
2) Rebuild the standby database from active primary database.
1) Check & Cancel the Managed recovery process (MRP) on standby database.
SQL> Select PROCESS, STATUS, THREAD#, SEQUENCE# from v$managed_standby where process like 'MRP%';
SQL> alter database recover managed standby database cancel;
2) Shutdown the standby database.
SQL> shut immediate
3) Startup the database in no-mount state.
SQL> startup nomount
4) Delete all the existing archives from STANDBY database so that new archives will be shipped to the standby location & get applied on it.
Source the environment for ASM, invoke ASM command line utility & manually remove the archives.
5) As we already have p-file/SP-file on standby database, we just need to run the below command on standby database.
rman target <Username/Password>@<DB_NAME> auxiliary <Username/Password>@<STDBY_DB_NAME>
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
allocate auxiliary channel ch5 device type disk;
allocate auxiliary channel ch6 device type disk;
allocate auxiliary channel ch7 device type disk;
allocate auxiliary channel ch8 device type disk;
duplicate target database for standby from active database nofilenamecheck dorecover;
}
6) Once this is done. Please mount the standby database & put it in MRP mode.
SQL> alter database recover managed standby database disconnect from session;
7) Make a log switch on primary database (on both the node) & see if those archives are getting
shipped & applied successfully on standby database.
On Node-1: SQL> Alter system switch logfile;
On Node-2: SQL> Alter system switch logfile;
On Any of the Running Node:
set scan on set
feed off
set linesize 200
BREAK ON ROW SKIP 1s column thread format
a6; column "PR - Archived" format a13; column
"STBY - Archived" format a15; column "STBY -
Applied" format a14; column "Shipping GAP (PR -
> STBY)" format a25; column "Applied GAP (STBY
-> STBY)" format a26;
--ACCEPT DEST PROMPT 'Enter the Standby Archive Log Destination : '
SELECT *
FROM (SELECT LPAD (t1, 4, ' ') "Thread",
LPAD (pricre, 9, ' ') "PR - Archived",
LPAD (stdcre, 10, ' ') "STBY - Archived",
LPAD (stdnapp, 9, ' ') "STBY - Applied",
LPAD (pricre - stdcre, 13, ' ')
"Shipping GAP (PR -> STBY)",
LPAD (stdcre - stdnapp, 15, ' ')
"Applied GAP (STBY -> STBY)"
FROM ( SELECT MAX (sequence#) stdcre, thread# t1
FROM v$archived_log
WHERE standby_dest = 'YES'
AND resetlogs_id IN
(SELECT MAX (RESETLOGS_ID)
FROM v$archived_log)
AND thread# IN (1, 2, 3, 4)
GROUP BY thread#) a,
( SELECT MAX (sequence#) stdnapp, thread# t2
FROM v$archived_log
WHERE standby_dest = 'YES'
AND resetlogs_id IN
(SELECT MAX (RESETLOGS_ID)
FROM v$archived_log)
AND thread# IN (1, 2, 3, 4)
AND applied = 'YES'
GROUP BY thread#) b,
( SELECT MAX (sequence#) pricre, thread# t3
FROM v$archived_log
WHERE standby_dest = 'NO'
AND resetlogs_id IN
(SELECT MAX (RESETLOGS_ID)
FROM v$archived_log)
AND thread# IN (1, 2, 3, 4)
GROUP BY thread#) c
WHERE a.t1 = b.t2 AND b.t2 = c.t3 AND c.t3 = a.t1)
ORDER BY 1
/ set feed
on break o
No comments:
Post a Comment