Resolving Gaps in Data Guard Apply Using Incremental RMAN Backup:-
On the primary:
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
ORCL PRIMARY UNRESOLVABLE GAP
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2076457
On the standby:
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1998045
Solution:-
1. [Standby] Stop the managed standby apply process:
SQL> alter database recover managed standby database cancel;
Database altered.
2. [Standby] Shutdown the standby database;
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck;
RMAN> backup incremental from scn 1998045 database;
Starting backup at 15-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
backup will be obsolete on date 22-SEP-14
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=00006 name=/u01/MONEY.DBF
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 15-SEP-14
channel ORA_DISK_1: finished piece 1 at 15-SEP-14
piece handle=/u01/neeraj/29pij5mb_1_1 tag=TAG20140915T181954 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
using channel ORA_DISK_1
backup will be obsolete on date 22-SEP-14
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 15-SEP-14
channel ORA_DISK_1: finished piece 1 at 15-SEP-14
piece handle=/u01/neeraj/2apij5p1_1_1 tag=TAG20140915T181954 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-SEP-14
4. [Primary] On the primary, create a new standby controlfile:
SQL> alter database create standby controlfile as '/u01/neeraj/stdb_cont.ctl';
Database altered.
5. [Primary] Copy these files to standby host:
[oracle@localhost ~]$ cd /u01/neeraj/
[oracle@localhost neeraj]$ ls
29pij5mb_1_1 2apij5p1_1_1 stdb_cont.ctl
[oracle@localhost neeraj]$
[oracle@localhost neeraj]$
[oracle@localhost neeraj]$ scp * oracle@192.168.204.129:/u01/tspr
Address 192.168.204.129 maps to localhost.localdomain, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!
oracle@192.168.204.129's password:
29pij5mb_1_1 100% 59MB 19.7MB/s 00:03
2apij5p1_1_1 100% 11MB 11.1MB/s 00:00
stdb_cont.ctl 100% 11MB 11.1MB/s 00:01
[oracle@localhost neeraj]$
6. [Standby] Bring up the instance in nomount mode:
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1341312 bytes
Variable Size 201328768 bytes
Database Buffers 864026624 bytes
Redo Buffers 4636672 bytes
7. [Standby] Check the location of the controlfile:
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/stdb/control01.ctl --- "rename or remove(control01_old.ctl)"
8. [Standby] Replace the controlfile with the one you just created in primary. and rename the existing file which is already exist on the standby.
$ cp /u01/tspr/stdb_cont.ctl /u01/app/oracle/oradata/stdb/control01.ctl
9.[Standby] Mount the standby database:
SQL> alter database mount standby database;
Database altered.
10 .[Standby] RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:
RMAN> catalog start with '/u01/tspr';
searching for all files that match the pattern /u01/tspr
List of Files Unknown to the Database
=====================================
File Name: /u01/tspr/29pij5mb_1_1
File Name: /u01/tspr/2apij5p1_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: /u01/tspr/29pij5mb_1_1
File Name: /u01/tspr/2apij5p1_1_1
11 .Recover these files:
RMAN> recover database;
Starting recover at 15-SEP-14
using channel ORA_DISK_1
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: /u01/app/oracle/oradata/stdb/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/stdb/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/stdb/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/stdb/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/stdb/example01.dbf
destination for restore of datafile 00006: /u01/MONEY.DBF
channel ORA_DISK_1: reading from backup piece /u01/tspr/29pij5mb_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/15/2014 18:37:55
ORA-19870: error while restoring backup piece /u01/tspr/29pij5mb_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 6
if you will get this error then follow the step. and database must be mounted;
NOTE:- ORA-19573: CANNOT OBTAIN EXCLUSIVE ENQUEUE FOR DATAFILE
SQL> alter database recover managed standby database cancel;
Database altered.
Then run again recovery command:
RMAN> recover database;
Starting recover at 15-SEP-14
using channel ORA_DISK_1
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: /u01/app/oracle/oradata/stdb/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/stdb/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/stdb/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/stdb/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/stdb/example01.dbf
destination for restore of datafile 00006: /u01/MONEY.DBF
channel ORA_DISK_1: reading from backup piece /u01/tspr/29pij5mb_1_1
channel ORA_DISK_1: piece handle=/u01/tspr/29pij5mb_1_1 tag=TAG20140915T181954
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
starting media recovery
archived log for thread 1 with sequence 373 is already on disk as file /u01/app/oracle/flash_recovery_area/STDB/orcl_373_1_850048081.arc
archived log file name=/u01/app/oracle/flash_recovery_area/STDB/orcl_373_1_850048081.arc thread=1 sequence=373
unable to find archived log
archived log thread=1 sequence=374
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/15/2014 18:50:43
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 374 and starting SCN of 2078522
Note:- After some time, the recovery fails with the message. This happens because we have come to the last of the archived logs.
12 .At this point exit RMAN and start managed recovery process:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2082113
On the primary database:
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
Please share your suggestion and comment on this.
all the best.... :)
On the primary:
SQL> select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
ORCL PRIMARY UNRESOLVABLE GAP
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2076457
On the standby:
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1998045
Solution:-
1. [Standby] Stop the managed standby apply process:
SQL> alter database recover managed standby database cancel;
Database altered.
2. [Standby] Shutdown the standby database;
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL>
3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck;
RMAN> backup incremental from scn 1998045 database;
Starting backup at 15-SEP-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
backup will be obsolete on date 22-SEP-14
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=00006 name=/u01/MONEY.DBF
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 15-SEP-14
channel ORA_DISK_1: finished piece 1 at 15-SEP-14
piece handle=/u01/neeraj/29pij5mb_1_1 tag=TAG20140915T181954 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
using channel ORA_DISK_1
backup will be obsolete on date 22-SEP-14
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 15-SEP-14
channel ORA_DISK_1: finished piece 1 at 15-SEP-14
piece handle=/u01/neeraj/2apij5p1_1_1 tag=TAG20140915T181954 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 15-SEP-14
4. [Primary] On the primary, create a new standby controlfile:
SQL> alter database create standby controlfile as '/u01/neeraj/stdb_cont.ctl';
Database altered.
5. [Primary] Copy these files to standby host:
[oracle@localhost ~]$ cd /u01/neeraj/
[oracle@localhost neeraj]$ ls
29pij5mb_1_1 2apij5p1_1_1 stdb_cont.ctl
[oracle@localhost neeraj]$
[oracle@localhost neeraj]$
[oracle@localhost neeraj]$ scp * oracle@192.168.204.129:/u01/tspr
Address 192.168.204.129 maps to localhost.localdomain, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!
oracle@192.168.204.129's password:
29pij5mb_1_1 100% 59MB 19.7MB/s 00:03
2apij5p1_1_1 100% 11MB 11.1MB/s 00:00
stdb_cont.ctl 100% 11MB 11.1MB/s 00:01
[oracle@localhost neeraj]$
6. [Standby] Bring up the instance in nomount mode:
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1071333376 bytes
Fixed Size 1341312 bytes
Variable Size 201328768 bytes
Database Buffers 864026624 bytes
Redo Buffers 4636672 bytes
7. [Standby] Check the location of the controlfile:
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oracle/oradata/stdb/control01.ctl --- "rename or remove(control01_old.ctl)"
8. [Standby] Replace the controlfile with the one you just created in primary. and rename the existing file which is already exist on the standby.
$ cp /u01/tspr/stdb_cont.ctl /u01/app/oracle/oradata/stdb/control01.ctl
9.[Standby] Mount the standby database:
SQL> alter database mount standby database;
Database altered.
10 .[Standby] RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:
RMAN> catalog start with '/u01/tspr';
searching for all files that match the pattern /u01/tspr
List of Files Unknown to the Database
=====================================
File Name: /u01/tspr/29pij5mb_1_1
File Name: /u01/tspr/2apij5p1_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: /u01/tspr/29pij5mb_1_1
File Name: /u01/tspr/2apij5p1_1_1
11 .Recover these files:
RMAN> recover database;
Starting recover at 15-SEP-14
using channel ORA_DISK_1
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: /u01/app/oracle/oradata/stdb/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/stdb/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/stdb/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/stdb/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/stdb/example01.dbf
destination for restore of datafile 00006: /u01/MONEY.DBF
channel ORA_DISK_1: reading from backup piece /u01/tspr/29pij5mb_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/15/2014 18:37:55
ORA-19870: error while restoring backup piece /u01/tspr/29pij5mb_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 6
if you will get this error then follow the step. and database must be mounted;
NOTE:- ORA-19573: CANNOT OBTAIN EXCLUSIVE ENQUEUE FOR DATAFILE
SQL> alter database recover managed standby database cancel;
Database altered.
Then run again recovery command:
RMAN> recover database;
Starting recover at 15-SEP-14
using channel ORA_DISK_1
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: /u01/app/oracle/oradata/stdb/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/stdb/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/stdb/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/stdb/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/stdb/example01.dbf
destination for restore of datafile 00006: /u01/MONEY.DBF
channel ORA_DISK_1: reading from backup piece /u01/tspr/29pij5mb_1_1
channel ORA_DISK_1: piece handle=/u01/tspr/29pij5mb_1_1 tag=TAG20140915T181954
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
starting media recovery
archived log for thread 1 with sequence 373 is already on disk as file /u01/app/oracle/flash_recovery_area/STDB/orcl_373_1_850048081.arc
archived log file name=/u01/app/oracle/flash_recovery_area/STDB/orcl_373_1_850048081.arc thread=1 sequence=373
unable to find archived log
archived log thread=1 sequence=374
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/15/2014 18:50:43
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 374 and starting SCN of 2078522
Note:- After some time, the recovery fails with the message. This happens because we have come to the last of the archived logs.
12 .At this point exit RMAN and start managed recovery process:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2082113
On the primary database:
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
Please share your suggestion and comment on this.
all the best.... :)
