How to Recover Standby Database from a Missing Archive Log by applying the Incremental backup from Production Database


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.

 

  1. Get the current scn of the standby database.,

SQL> select current_scn from v$database;

CURRENT_SCN

———–

1053647

 

  1. 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>

 

  1. Transfer all the backup pieces created on the primary to the standby.

 

  1. 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:

 

  1. 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;

  1. 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.

You may also like...

2 Responses

  1. Amy says:

    Thank you, Very nice Article.. keep it up

  2. Lam Nguyen says:

    Thank you, it saved my time.

Leave a Reply

Your email address will not be published. Required fields are marked *