Resync the standby database using an incremental RMAN backup from the primary database.
Advantages:
- This does not require a full rebuild of the standby database and can be a lot more efficient.
- Time saving.
Scenario:
Primary Database – pdb
Standby Database – sdb
One of the archivelog files is missing and there is no backup of that archivelog. Primary database and Standby database are not in SYNC. There is an archival gap.
Resyncing the standby data using RMAN incremental backup taken from the primary database.
On Primary Database:
- connect as sys user and check the archive log list
SQL> conn sys/oracle@sdb as sysdba
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
————–
sdb
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol1/pdb/arch
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
Issue alter system switch logfile on primary database
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol1/pdb/arch
Oldest online log sequence 24
Next log sequence to archive 26
Current log sequence 26
ON Standby database:
SQL> conn sys/oracle@sdb as sysdba
Connected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
—————-
sdb
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol2/sdb/arch
Oldest online log sequence 24
Next log sequence to archive 0
Current log sequence 26
ON Primary Database:
- Create a user, connect to that user, create table and insert records in it.
SQL> conn veera/veera@pdb
Connected.
SQL> select * from tab;
no rows selected
SQL> create table emp (empid number(4), emp_name varchar2(12));
Table created.
SQL> insert into emp values(&empid,’&emp_name’);
Enter value for empid: 1111
Enter value for emp_name: veera
old 1: insert into emp values(&empid,’&emp_name’)
new 1: insert into emp values(1111,’veera’)
1 row created.
SQL> /
Enter value for empid: 1309
Enter value for emp_name: suresh
old 1: insert into emp values(&empid,’&emp_name’)
new 1: insert into emp values(1309,’suresh’)
1 row created.
SQL> commit;
Commit complete.
SQL> select * from emp;
EMPID EMP_NAME
———- ————
1111 veera
1309 suresh
You can see that we connected to a user “veera”, created a table “emp” and inserted rows in to it.
ON Standby Database:
- connect to the standby database and check whether the table has been replicated or not.
SQL> conn sys/oracle@sdb as sysdba
Connected.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol2/sdb/arch
Oldest online log sequence 25
Next log sequence to archive 0
Current log sequence 27
SQL> select * from veera.emp;
EMPID EMP_NAME
———- ————
1111 veera
1309 suresh
From the above we can see that the data has been replicated to the standby database from the primary database.
- Now cancel the media recovery in Standby database.
alter database recover managed standby database cancel;
On Primary Database:
Insert in to the same table two more rows.
SQL> insert into emp values(&empid,’&emp_name’);
Enter value for empid: 1234
Enter value for emp_name: mannoj
old 1: insert into emp values(&empid,’&emp_name’)
new 1: insert into emp values(1234,’mannoj’)
1 row created.
SQL> insert into emp values(&empid,’&emp_name’);
Enter value for empid: 9876
Enter value for emp_name: thiyagu
old 1: insert into emp values(&empid,’&emp_name’)
new 1: insert into emp values(9876,’thiyagu’)
1 row created.
SQL> commit;
Commit complete.
SQL> select * from veera.emp;
EMPID EMP_NAME
———- ————
1111 veera
1309 suresh
1234 mannoj
9876 thiyagu
- Now connect as a sys user and switch the logfile.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol1/pdb/arch
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
- Now remove this archivelog file physically from the production database.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol1/pdb/arch
Oldest online log sequence 26
Next log sequence to archive 28
Current log sequence 28
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol1/pdb/arch
Oldest online log sequence 27
Next log sequence to archive 29
Current log sequence 29
ON Standby Database:
The archivelog file with sequence# 27 will be moved to standby database. Since the media recovery process has been canceled the archivelog will not be applied to the standby database.
Now remove that archivelog with sequence# 27 physically from standby server also.
Once after deleting the archivelog file from both production and standby database servers, enable the media recovery process in standby database.
alter database recover managed standby database disconnect from session;
SQL> archive log list
Database log mode Archive Mo
Automatic archival Enabled
Archive destination /vol2/sdb/arch
Oldest online log sequence 26
Next log sequence to archive 0
Current log sequence 28
SQL> select * from veera.emp;
EMPID EMP_NAME
———- ————
1111 veera
1309 suresh
1309 suresh
SQL> archive log list
Database log mode Archive Mo
Automatic archival Enabled
Archive destination /vol2/sdb/arch
Oldest online log sequence 26
Next log sequence to archive 0
Current log sequence 28
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> archive log list
Database log mode Archive Mo
Automatic archival Enabled
Archive destination /vol2/sdb/arch
Oldest online log sequence 27
Next log sequence to archive 0
Current log sequence 29
SQL> archive log list
Database log mode Archive Mo
Automatic archival Enabled
Archive destination /vol2/sdb/arch
Oldest online log sequence 27
Next log sequence to archive 0
Current log sequence 29
SQL> select sequence#,archived,applied from v$archived_log;
SEQUENCE# ARC APPLIED
———- — ———
2 YES YES
3 YES YES
4 YES YES
5 YES YES
6 YES YES
7 YES YES
8 YES YES
9 YES YES
10 YES YES
11 YES YES
12 YES YES
SEQUENCE# ARC APPLIED
———- — ———
13 YES YES
14 YES YES
15 YES YES
16 YES YES
17 YES YES
18 YES YES
19 YES YES
20 YES YES
21 YES YES
22 YES YES
23 YES YES
SEQUENCE# ARC APPLIED
———- — ———
24 YES YES
25 YES YES
26 YES YES
27 YES NO
28 YES NO
27 rows selected.
SQL> select * from veera.emp;
EMPID EMP_NAME
———- ————
1111 veera
1309 suresh
From the above we can clearly see that there is an archival gap sequence# 27 has not been applied to the standby database. But the archivelogs are moving to the standby database location but not applying.
ON Primary Database:
SQL> insert into veera.emp values(&empid,’&emp_name’);
Enter value for empid: 2345
Enter value for emp_name: vivek
old 1: insert into veera.emp values(&empid,’&emp_name’)
new 1: insert into veera.emp values(2345,’vivek’)
1 row created.
SQL> insert into veera.emp values(&empid,’&emp_name’);
Enter value for empid: 3456
Enter value for emp_name: shek
old 1: insert into veera.emp values(&empid,’&emp_name’)
new 1: insert into veera.emp values(3456,’shek’)
1 row created.
SQL> commit;
Commit complete.
SQL> select * from veera.emp;
EMPID EMP_NAME
———- ————
1111 veera
1309 suresh
1234 mannoj
9876 thiyagu
2345 vivek
3456 shek
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> select * from veera.emp;
EMPID EMP_NAME
———- ————
1111 veera
1309 suresh
1234 mannoj
9876 thiyagu
2345 vivek
3456 shek
6 rows selected.
On Standby Database:
SQL> select * from veera.emp;
EMPID EMP_NAME
———- ————
1111 veera
1309 suresh
SQL> select sequence#,archived,applied from v$archived_log;
SEQUENCE# ARC APPLIED
———- — ———
2 YES YES
3 YES YES
4 YES YES
5 YES YES
6 YES YES
7 YES YES
8 YES YES
9 YES YES
10 YES YES
11 YES YES
12 YES YES
SEQUENCE# ARC APPLIED
———- — ———
13 YES YES
14 YES YES
15 YES YES
16 YES YES
17 YES YES
18 YES YES
19 YES YES
20 YES YES
21 YES YES
22 YES YES
23 YES YES
SEQUENCE# ARC APPLIED
———- — ———
24 YES YES
25 YES YES
26 YES YES
27 YES NO
28 YES NO
29 YES NO
28 rows selected.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol2/sdb/arch
Oldest online log sequence 28
Next log sequence to archive 0
Current log sequence 30
Overall Scenario:
Archivelog with sequence# 27 is missing from primary and also from standby database. Due to this there is an archival gap between primary database and standby database. The synchronization between primary database and standby database has been lost.
We have to recover / resync the standby database with the primary database.
The steps to resync the standby database using an incremental RMAN backup from the primary database are as follows.
- Get the current scn of the standby database.,
SQL> select current_scn from v$database;
CURRENT_SCN
———–
1053647
- Connect to the primary database as the RMAN target and create an incremental backup from the current SCN (for a standby lagging far behind the primary):
primary:/vol1 />rman catalog rman/rman@pdb target sys/oracle@pdb
Recovery Manager: Release 11.2.0.3.0 – Production on Wed Mar 13 15:54:00 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PDB (DBID=3232692579)
connected to recovery catalog database
RMAN> backup incremental from scn 1053647 database;
Starting backup at 13-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
backup will be obsolete on date 20-MAR-13
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/VOL1/PDB/SYSTEM01.DBF
input datafile file number=00002 name=/VOL1/PDB/SYSAUX01.DBF
input datafile file number=00005 name=/VOL1/PDB/TBSRMAN.DBF
input datafile file number=00003 name=/VOL1/PDB/UNDOTBS01.DBF
input datafile file number=00004 name=/VOL1/PDB/USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 13-MAR-13
channel ORA_DISK_1: finished piece 1 at 13-MAR-13
piece handle=/RMAN/DBF_BKUP/DBF_09O4EBAE_1_1 tag=TAG20130313T155700 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
using channel ORA_DISK_1
backup will be obsolete on date 20-MAR-13
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 13-MAR-13
channel ORA_DISK_1: finished piece 1 at 13-MAR-13
piece handle=/RMAN/DBF_BKUP/DBF_0AO4EBBJ_1_1 tag=TAG20130313T155700 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 13-MAR-13
RMAN>
- Transfer all the backup pieces created on the primary to the standby.
- Create a standby controlfile on the primary and ship to the standby.
SQL> alter database create standby controlfile as ‘/oracle/CONTROL01.CTL’;
Database altered.
Now on Standby database:
- Recreate the standby controlfile on the standby:
- shutdown abort the database.,
SQL> shut abort
ORACLE instance shut down.
- Start the database in nomount.,
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1686925312 bytes
Fixed Size 2255824 bytes
Variable Size 989856816 bytes
Database Buffers 687865856 bytes
Redo Buffers 6946816 bytes
SQL>
- connect to the rman prompt and connect to the target database (sdb).,
secondary:/vol2 />rman target sys/oracle@sdb
Recovery Manager: Release 11.2.0.3.0 – Production on Wed Mar 13 16:20:45 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PDB (not mounted)
- restore the controlfile using the below command.,
restore controlfile from ‘/oracle/CONTROL01.CTL’;
RMAN> restore controlfile from ‘/oracle/CONTROL01.CTL’;
Starting restore at 13-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/VOL2/SDB/STDCONTROL01.CTL
Finished restore at 13-MAR-13
RMAN>
NOTE:
Rename standby data/temp files and redo if different from the primary using ALTER DATABASE RENAME FILE
- Mount the standby database.,
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
- Catalog backup pieces if the location on the standby is different from the primary.
connect to rman as target as standby database (stb).,
secondary:/vol2 />rman target sys/oracle@sdb
Recovery Manager: Release 11.2.0.3.0 – Production on Wed Mar 13 16:01:03 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PDB (DBID=3232692579)
RMAN> catalog start with ‘/rman/rman_sdb’;
searching for all files that match the pattern /rman/rman_sdb
List of Files Unknown to the Database
=====================================
File Name: /rman/rman_sdb/DBF_0BO4GTHJ_1_1
File Name: /rman/rman_sdb/DBF_0CO4GTIO_1_1
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: /rman/rman_sdb/DBF_0BO4GTHJ_1_1
File Name: /rman/rman_sdb/DBF_0CO4GTIO_1_1
- Apply incremental backup to the standby.,
RMAN> recover database noredo;
Starting recover at 13-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=195 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /VOL2/SDB/SYSTEM01.DBF
destination for restore of datafile 00002: /VOL2/SDB/SYSAUX01.DBF
destination for restore of datafile 00003: /VOL2/SDB/UNDOTBS01.DBF
destination for restore of datafile 00004: /VOL2/SDB/USERS01.DBF
destination for restore of datafile 00005: /VOL2/SDB/TBSRMAN.DBF
channel ORA_DISK_1: reading from backup piece /RMAN/DBF_BKUP/DBF_09O4EBAE_1_1
channel ORA_DISK_1: piece handle=/RMAN/DBF_BKUP/DBF_09O4EBAE_1_1 tag=TAG20130313T155700
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished recover at 13-MAR-13
RMAN>
————————————————————————————————————————
You can see the Alert log in which the recovery process is clearly mentioned.,
———————————————————————————————————————–
ARC0: STARTING ARCH PROCESSES COMPLETE
Lost write protection disabled
ARC2: Becoming the active heartbeat ARCH
Thu Mar 14 15:26:28 2013
Completed: alter database mount
Thu Mar 14 15:26:34 2013
Using STANDBY_ARCHIVE_DEST parameter default value as /vol2/sdb/arch
Thu Mar 14 15:27:36 2013
Incremental restore complete of datafile 4 /VOL2/SDB/USERS01.DBF
checkpoint is 1084371
last deallocation scn is 3
Incremental restore complete of datafile 3 /VOL2/SDB/UNDOTBS01.DBF
checkpoint is 1084371
last deallocation scn is 1025328
Incremental restore complete of datafile 5 /VOL2/SDB/TBSRMAN.DBF
checkpoint is 1084371
last deallocation scn is 994065
Incremental restore complete of datafile 2 /VOL2/SDB/SYSAUX01.DBF
checkpoint is 1084371
last deallocation scn is 1005551
Incremental restore complete of datafile 1 /VOL2/SDB/SYSTEM01.DBF
checkpoint is 1084371
last deallocation scn is 1004787
Thu Mar 14 15:28:03 2013
alter database recover managed standby database disconnect from session
Thu Mar 14 15:28:03 2013
MRP0 started with pid=28, OS id=3188
started logmerger process
Thu Mar 14 15:28:08 2013
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived…
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 32
Completed: alter database recover managed standby database disconnect from session
Thu Mar 14 15:28:56 2013
RFS[1]: Assigned to RFS process 1416
RFS[1]: Opened log for thread 1 sequence 32 dbid -1062274717 branch 809697765
Archived Log entry 1 added for thread 1 sequence 32 rlc 809697765 ID 0xc0af5d63 dest 2:
Thu Mar 14 15:29:00 2013
Media Recovery Log /VOL2/SDB/ARCH/ARC0000000032_0809697765.0001
Media Recovery Waiting for thread 1 sequence 33
Thu Mar 14 15:29:16 2013
—————————————————————————————————-
- Start the media recovery process in standby database
alter database recover managed standby database disconnect from session;
SQL> alter database recover managed standby database disconnect from session;
- Check the archive log list on primary and standby databases.,
ON Primary :
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol1/pdb/arch
Oldest online log sequence 31
Next log sequence to archive 33
Current log sequence 33
ON Standby :
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol2/sdb/arch
Oldest online log sequence 31
Next log sequence to archive 0
Current log sequence 33
- Cancel the recovery mode in Standby Database and open the database for read only access. Start the media recovery process.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select sequence#,archived,applied from v$archived_log;
SEQUENCE# ARC APPLIED
———- — ———
32 YES YES
ON Primary Database:
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol1/pdb/arch
Oldest online log sequence 31
Next log sequence to archive 33
Current log sequence 33
ON Standby Database:
SQL> select sequence#,archived,applied from v$archived_log;
SEQUENCE# ARC APPLIED
———- — ———
32 YES YES
33 YES NO
SQL> select sequence#,archived,applied from v$archived_log;
SEQUENCE# ARC APPLIED
———- — ———
32 YES YES
33 YES NO
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select sequence#,archived,applied from v$archived_log;
SEQUENCE# ARC APPLIED
———- — ———
32 YES YES
33 YES IN-MEMORY
SQL> select sequence#,archived,applied from v$archived_log;
SEQUENCE# ARC APPLIED
———- — ———
32 YES YES
33 YES YES
- Now finally check the synchronization of primary and standby databases.
ON Primary Database:
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol1/pdb/arch
Oldest online log sequence 32
Next log sequence to archive 34
Current log sequence 34
ON Standby Database:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vol2/sdb/arch
Oldest online log sequence 32
Next log sequence to archive 0
Current log sequence 34
From the above check, we can clearly see that both primary and standby databases are in sync.
Loading ...
Total Users : 1816843
Thank you, Very nice Article.. keep it up
Thank you, it saved my time.