Monday, October 31, 2011

RECOVERING A STANDBY DATABASE FROM A MISSING ARCHIVES LOG


Applying an incremental backup for missing archive log in to a physical standby
(OR)
ROLLING FORWARD A STANDBY DATABASE USING RMAN INCREMENTAL BACKUP
My setup

1) 2 node primary RAC database on 11202
2) 2 node physical standby RAC database on 11202
3) Running on ASM


We found that couple of archive log are delete before apply it in the standby database

Primary DB SCN Number

SQL> select current_scn from gv$database;

CURRENT_SCN
-----------
   13395160
   13395160

Standby DB SCN Number

SQL> select current_scn from gv$database;

CURRENT_SCN
-----------
    9642901
    9642901

SQL> select min (checkpoint_change#) from v$datafile_header;


ERROR MESSAGE

In our DG Environment, it has been reported as a error message

wyclorau003[BHU_2]>dgmgrl
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration;
Configuration - DG_BHU
  Protection Mode: MaxAvailability
  Databases:
    BHU_B - Primary database
      Error: ORA-16810: multiple errors or warnings detected for the database
    BHU_A - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR

DGMGRL> show database 'BHU_A';

Database - BHU_A

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   13 days 31 minutes 26 seconds
  Apply Lag:       13 days 31 minutes 16 seconds
  Real Time Query: OFF
  Instance(s):
    BHU_1
    BHU_2 (apply instance)
Database Status:
SUCCESS
DGMGRL> show database 'BHU_B';
Database - BHU_B
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    BHU_1
    BHU_2
  Database Error(s):
    ORA-16783: cannot resolve gap for database BHU_A
  Database Warning(s):
    ORA-16629: database reports a different protection level from the protection mode
Database Status:
ERROR

STEP #1 STOP THE RECOVERY PROCESS (APPLY OFF)

$ dgmgrl     -----à On standby server
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> edit database 'BHU_A' set state='APPLY-OFF';
Succeeded.
DGMGRL> show database 'BHU_A';
Database - BHU_A
  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   13 days 31 minutes 26 seconds
  Apply Lag:       13 days 31 minutes 16 seconds
  Real Time Query: OFF
  Instance(s):
    BHU_1
    BHU_2 (apply instance)

Database Status:
SUCCESS

IF NO BROKER

Stop the managed recovery process (MRP)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

STEP#2 PERFORM A INCREMENTAL BACKUP FROM PRIMARY DB

$ rman target /               -à primary DB

RMAN> run
2> {
3> allocate channel ch1 type disk;
4> allocate channel ch2 type disk;
5> backup incremental from scn 9642000 database format '/oracle/BHU/standby_%d_%t_%c_%p';
6> }

using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=95 instance=BHU_1 device type=DISK
allocated channel: ch2
channel ch2: SID=120 instance=BHU_1 device type=DISK
Starting backup at 31-OCT-11
backup will be obsolete on date 07-NOV-11
archived logs will not be kept or backed up
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00004 name=+BHU_DATA1/BHU_b/datafile/pindb.257.762433489
input datafile file number=00001 name=+BHU_DATA1/BHU_b/datafile/system.260.762433549
channel ch1: starting piece 1 at 31-OCT-11
channel ch2: starting full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00002 name=+BHU_DATA1/BHU_b/datafile/sysaux.261.762433557
input datafile file number=00005 name=+BHU_DATA1/BHU_b/datafile/undo_2.259.762433541
input datafile file number=00003 name=+BHU_DATA1/BHU_b/datafile/undo.258.762433535
channel ch2: starting piece 1 at 31-OCT-11
channel ch1: finished piece 1 at 31-OCT-11
piece handle=/oracle/BHU/standby_BHU_765989757_1_1 tag=TAG20111031T145557 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:16
channel ch2: finished piece 1 at 31-OCT-11
piece handle=/oracle/BHU/standby_BHU_765989758_1_1 tag=TAG20111031T145557 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:25
backup will be obsolete on date 07-NOV-11
archived logs will not be kept or backed up
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 31-OCT-11
channel ch1: finished piece 1 at 31-OCT-11
piece handle=/oracle/BHU/standby_BHU_765989783_1_1 tag=TAG20111031T145557 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-OCT-11
released channel: ch1
released channel: ch2
RMAN> exit


STEP#3 RECOVER STANDBY DB

A)    Move the rman backup piece to the standby server
B)     Catalog backup piece on the standby server

$ rman target /   -à STANDBY SERVER
Recovery Manager: Release 11.2.0.2.0 - Production on Mon Oct 31 15:05:10 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: BHU (DBID=19835845, not open)

RMAN> catalog start with '/oracle/BHU/saptrace/bkp';

using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/BHU/bkp
List of Files Unknown to the Database
=====================================
File Name: /oracle/BHU/bkp/standby_BHU_765989757_1_1
File Name: /oracle/BHU/bkp/standby_BHU_765989758_1_1
File Name: /oracle/BHU/bkp/standby_BHU_765989783_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: /oracle/BHU/bkp/standby_BHU_765989757_1_1
File Name: /oracle/BHU/bkp/standby_BHU_765989758_1_1
File Name: /oracle/BHU/bkp/standby_BHU_765989783_1_1

C)    Recover the standby database using the backup piece which has taken from the PRIMARY DB
-- RUN FROM STANDBY SERVER
RMAN> run
2> {
3> allocate channel ch1 type disk;
4> recover database noredo;
5> }

allocated channel: ch1
channel ch1: SID=2729 instance=BHU_2 device type=DISK
Starting recover at 31-OCT-11
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +BHU_DATA1/BHU_a/datafile/system.265.762888379
destination for restore of datafile 00004: +BHU_DATA1/BHU_a/datafile/pindb.270.762888453
channel ch1: reading from backup piece /oracle/BHU/bkp/standby_BHU_765989757_1_1
channel ch1: piece handle=/oracle/BHU/saptrace/bkp/standby_BHU_765989757_1_1 tag=TAG20111031T145557
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting incremental datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: +BHU_DATA1/BHU_a/datafile/sysaux.268.762888411
destination for restore of datafile 00003: +BHU_DATA1/BHU_a/datafile/undo.269.762888431
destination for restore of datafile 00005: +BHU_DATA1/BHU_a/datafile/undo_2.264.762888353
channel ch1: reading from backup piece /oracle/BHU/bkp/standby_BHU_765989758_1_1
channel ch1: piece handle=/oracle/BHU/saptrace/bkp/standby_BHU_765989758_1_1 tag=TAG20111031T145557
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
Finished recover at 31-OCT-11
released channel: ch1

STEP#4 Create standby control from primary DB

SQL> alter database create standby controlfile as '/oracle/BHU/standby.ctl';
Database altered.

STEP#5 Restore the Control file in the STANDBY DB

-- Below steps are in the standby server
A) STOP THE STANDBY DATBASE( Both instances)

$ srvctl stop database -d BHU_a

B) START ONLY ONE INSTANCE OF THE STANDBY DATBASE

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 31 15:10:18 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.4431E+10 bytes
Fixed Size                  2240272 bytes
Variable Size            3892314352 bytes
Database Buffers         1.0503E+10 bytes
Redo Buffers               34148352 bytes

C) IDENTIFY THE CONTROL FILE LOCATION

Note: you can remove the existing control file or we can allow the system to over write the control file which is placed in the ASM. I am over writing the controlfiles
SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +BHU_DATA1/BHU_a/controlfile/current.258.762889151,
+BHU_ARCH/BHU_a/controlfile/current.332.762889151,
+BHU_RECO/BHU_a/controlfile/current.285.762889151

D) I have the copied standby control file from primary DB

$ ls
standby.ctl
$ pwd
/oracle/BHU/bkp

E)     RESTORING CONTROL FILE TO STANDBY DATABASE

RMAN> restore controlfile from '/oracle/BHU/bkp/standby.ctl';

Starting restore at 31-OCT-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=660 instance=BHU_2 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+BHU_DATA1/BHU_a/controlfile/current.258.762889151
output file name=+BHU_ARCH/BHU_a/controlfile/current.332.762889151
output file name=+BHU_RECO/BHU_a/controlfile/current.285.762889151
Finished restore at 31-OCT-11
RMAN> exit

RMAN> alter database mount;
Database mount

STEP#6 CHANGING CONTROLFILE CONFIGURATION

A) Controlfile is restored from PRIMARY DB the datafile locations in STANDBY controlfile will be same as PRIMARY database, so catalog datafiles in STANDBY will do the necessary rename operations.
B) Perform the below step  in STANDBY for each diskgroup where the datafile directory structure between primary and standby are different.

$ rman target /    à STANDBY DB
Recovery Manager: Release 11.2.0.2.0 -Production on Mon Oct 31 17:27:18 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: BHU (DBID=19835845, not open)

RMAN> CATALOG START WITH '+BHU_DATA1/BHU_A/DATAFILE/';

Starting implicit crosscheck backup at 31-OCT-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
Crosschecked 7 objects
Finished implicit crosscheck backup at 31-OCT-11
Starting implicit crosscheck copy at 31-OCT-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 31-OCT-11
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern +BHU_DATA1/BHU_A/DATAFILE/
List of Files Unknown to the Database
=====================================
File Name: +BHU_data1/BHU_A/DATAFILE/UNDO_2.264.762888353
File Name: +BHU_data1/BHU_A/DATAFILE/SYSTEM.265.762888379
File Name: +BHU_data1/BHU_A/DATAFILE/SYSAUX.268.762888411
File Name: +BHU_data1/BHU_A/DATAFILE/UNDO.269.762888431
File Name: +BHU_data1/BHU_A/DATAFILE/INDB.270.762888453
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +BHU_data1/BHU_A/DATAFILE/UNDO_2.264.762888353
File Name: +BHU_data1/BHU_A/DATAFILE/SYSTEM.265.762888379
File Name: +BHU_data1/BHU_A/DATAFILE/SYSAUX.268.762888411
File Name: +BHU_data1/BHU_A/DATAFILE/UNDO.269.762888431
File Name: +BHU_data1/BHU_A/DATAFILE/INDB.270.762888453

STEP#7 Check for New Datafile added in primary DB

Any datafiles have been added to Primary AFTER SCN 13395160 they will also have to be restored to the standby host and catalog as shown above before doing the switch.
 To determine if any files have been added to Primary since the standby current SCN
-- IN STANBDY DB
SQL> SELECT FILE#, NAME FROM V$DATAFILE WHERE CREATION_CHANGE# > 13395160;

no rows selected

NOTE: if any files available, we have to copy it from primary to the standby database using RMAN

copy the datafile from the primary database.

run
{
allocate channel a1 type disk;
backup datafile 10 format '/oracle/BHU_A/bkp_ctl_file/datafile_%d_%U_%p_%c';
}

Transfer the rman backup datafile to standby database location then catalog the backup piece in the standby database.

run
{
allocate channel a1 type disk;
Restore datafile 10;
}

STEP#8 Switch the datafile USING RMAN
This process will change the datafile details of the standby database in the controlfile

RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+BHU_DATA1/BHU_a/datafile/system.265.762888379"
datafile 2 switched to datafile copy "+BHU_DATA1/BHU_a/datafile/sysaux.268.762888411"
datafile 3 switched to datafile copy "+BHU_DATA1/BHU_a/datafile/undo.269.762888431"
datafile 4 switched to datafile copy "+BHU_DATA1/BHU_a/datafile/pindb.270.762888453"
datafile 5 switched to datafile copy "+BHU_DATA1/BHU_a/datafile/undo_2.264.762888353"

STEP#9 Clear the standby log files

On standby database, clear all standby redo log groups:

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 100;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 110;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 200;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 210;
....

STEP#10 START THE RECOVEY PROCESS ON STANDBY

$ dgmgrl     -----à On standby server
DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys
Password:
Connected.
DGMGRL> edit database 'BHU_A' set state='APPLY-ON';
Succeeded.

IF NO BROKER
 On the STANDBY database, start the MRP on apply instance
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;


FINAL RESULT

DGMGRL> show configuration

Configuration - DG_BHU

  Protection Mode: MaxAvailability
  Databases:
    BHU_B - Primary database
    BHU_A - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database 'BHU_A';

Database - BHU_A

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    BHU_1
    BHU_2 (apply instance)

Database Status:
SUCCESS

DGMGRL>

No comments:

Post a Comment