Recovering Standby Database when archives are missing at both Production and Standby

Recovering Standby Database when archives are missing at both Production and Standby

1.Determine the Current SCN on Standby database

2.Defer log shipping to Standby Database

3.Create Standby Controlfile on Primary

4.Create the Incremental Backup on Primary using SCN

5.Make the Incremental Backup Accessible at the Standby Database

6.Cancel MRP on Standby database

7.Catalog the Incremental Backup Files at the Standby Database

8.Apply the Incremental Backup to the Standby Database

9.Shutdown the Standby database

10.Remove and replace the current control files with latest

11.Mount the standby and enable MRM mode.

12.Enable log shipping to Standby Database

13.Check for the Sync Status

Determine the Current SCN in Standby database
Find the current SCN in standby database using below query.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
 212189

Defer log shipping to Standby Database Issue the below command on Primary to database to disable shipping of archives to standby database.

SQL> alter system set log_archive_dest_state_2=defer;

System altered.

Create Standby Controlfile on Primary and copy to Standby Database

SQL> alter database create standby controlfile as 'standby.ctl';

Database altered.

Create the Incremental Backup on Primary using SCN

Create the needed incremental backup at the primary database, using BACKUP with the INCREMENTAL FROM SCN clause. In this case, less SCN is used to take backup. i.e. 212100 instead 212189.

-bash-3.2$ rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Tue Sep 3 15:18:59 2013

Copyright (c) 1982, 2007, Oracle.All rights reserved.

connected to target database: HCCDBP (DBID=4238383777)

RMAN> BACKUP DEVICE TYPE DISK INCREMENTAL FROM SCN 212100 DATABASE FORMAT '/hccora/bkup_%U';

Starting backup at 03-SEP-13

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1074 device type=DISK
backup will be obsolete on date 10-SEP-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=00004 name=/hccora/db/apps_st/data/users01.dbf
input datafile file number=00001 name=/hccora/db/apps_st/data/system01.dbf
input datafile file number=00002 name=/hccora/db/apps_st/data/sysaux01.dbf
input datafile file number=00003 name=/hccora/db/apps_st/data/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 03-SEP-13
channel ORA_DISK_1: finished piece 1 at 03-SEP-13
piece handle=/hccora/bkup_01oiub3b_1_1 tag=TAG20130903T151905 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 10-SEP-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 03-SEP-13
channel ORA_DISK_1: finished piece 1 at 03-SEP-13
piece handle=/hccora/bkup_02oiub4e_1_1 tag=TAG20130903T151905 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-SEP-13

RMAN> exit

Recovery Manager complete.

Make the Incremental Backup Accessible at the Standby Database The directory should not contain anything other than the backup pieces.

-bash-3.2$ pwd
/hccora/backup
-bash-3.2$ ls -ltr
total 9636
-rw-r----- 1 hccora dba 9568256 Sep3 15:21 bkup_02oiub4e_1_1
-rw-r----- 1 hccora dba278528 Sep3 15:21 bkup_01oiub3b_1_1

Cancel MRP on Standby database

Use the below command on standby database to bring the standby database out of managed recovery mode.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Catalog the Incremental Backup Files at the Standby Database Register the backup sets in the RMAN repository.

-bash-3.2$ rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Tue Sep 3 15:26:34 2013

Copyright (c) 1982, 2007, Oracle.All rights reserved.

connected to target database: HCCDBP (DBID=4238383777, not open)

RMAN> CATALOG START WITH '/hccora/backup';

using target database control file instead of recovery catalog
searching for all files that match the pattern /hccora/backup

List of Files Unknown to the Database
=====================================
File Name: /hccora/backup/bkup_02oiub4e_1_1
File Name: /hccora/backup/bkup_01oiub3b_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: /hccora/backup/bkup_02oiub4e_1_1
File Name: /hccora/backup/bkup_01oiub3b_1_1

Apply the Incremental Backup to the Standby Database

RMAN>RECOVER DATABASE NOREDO;

Starting recover at 03-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=372 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: /hccora/db/apps_st/data/system01.dbf
destination for restore of datafile 00002: /hccora/db/apps_st/data/sysaux01.dbf
destination for restore of datafile 00003: /hccora/db/apps_st/data/undotbs01.dbf
destination for restore of datafile 00004: /hccora/db/apps_st/data/users01.dbf
channel ORA_DISK_1: reading from backup piece /hccora/backup/bkup_01oiub3b_1_1
channel ORA_DISK_1: piece handle=/hccora/backup/bkup_01oiub3b_1_1 tag=TAG20130903T151905
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 03-SEP-13

RMAN> exit

Shutdown the Standby database

SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

Remove and replace the current control files with latest Remove the old control files. Copy the control file taken in step 3.

-bash-3.2$ grep control $ORACLE_HOME/dbs/init$ORACLE_SID.ora
# of the control files.
# There should be at least two control files, preferably three,
# located on different disks. The control files can dynamically grow,
control_files = /hccora/db/apps_st/data/cntrl01.dbf,/hccora/db/apps_st/data/cntrl02.dbf,/hccora/db/apps_st/data/cntrl03.dbf
# The log writer parameters control the size of the log buffer
# The checkpoint interval and timeout control the frequency of

-bash-3.2$ rm -f /hccora/db/apps_st/data/cntrl01.dbf
-bash-3.2$ rm -f /hccora/db/apps_st/data/cntrl02.dbf
-bash-3.2$ rm -f /hccora/db/apps_st/data/cntrl03.dbf
-bash-3.2$ cp standby.ctl /hccora/db/apps_st/data/cntrl01.dbf
-bash-3.2$ cp standby.ctl /hccora/db/apps_st/data/cntrl02.dbf
-bash-3.2$ cp standby.ctl /hccora/db/apps_st/data/cntrl03.dbf

In this case there are 3 control files in standby database.

Mount the standby and enable MRM mode

-bash-3.2$ sqlplus '/as sysdba'

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Sep 3 14:49:29 2013

Copyright (c) 1982, 2008, Oracle.All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area521936896 bytes
Fixed Size2161272 bytes
Variable Size 218105224 bytes
Database Buffers289406976 bytes
Redo Buffers 12263424 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

Enable log shipping to Standby Database on Primary Connect to primary database and enable log shipping.

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

Check for the Sync Status Run the below sqls to check the status on Primary database.

SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 999999
COL APPLIED_TIME FORMAT A12

SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,LOG_ARCHIVED-LOG_APPLIED LOG_GAP FROM(SELECT NAME DB_NAME FROM v$DATABASE),
(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,'.')-1))))) HOSTNAME FROM v$INSTANCE),
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM v$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),(SELECT MAX(SEQUENCE#) LOG_APPLIED FROM v$ARCHIVED_LOG
WHERE DEST_ID=2 AND APPLIED='YES' ),(SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM v$ARCHIVED_LOG
WHERE DEST_ID=2 AND APPLIED='YES')
/

Run the below sqls to check the status on standby database.

set line 200
col DEST_NAME for a50
col BINDING for a10
select PROCESS,STATUS,GROUP#,THREAD#,SEQUENCE#,BLOCK#,DELAY_MINS from v$managed_standby
/

col MESSAGE for a150
select * from(select message from v$dataguard_status where message like '%Media Recovery Log%' order by message desc) where rownum<=5
/