RECOVERING CORRUPTED OR LOST DATAFILE ON PRIMARY DATABASE FROM STANDBY DATABASE
In this article, I’m demonstrating on a scenario where the one of the datafile of the primary database is corrupted and how it can be restored back from it’s standby database. The following steps can also be followed for scenarios where the datafile of the primary database is accidentally deleted from the File system or ASM diskgroup.
Primary database: srpstbStandby database: srprim
Primary database server: ora1-2
Standby database server: ora1-1
Primary database:
From the below outcome, it can be noted that datafile 9 of tablespace MYTS is corruped on the primary database.
SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------ ------------- ------------- -----------
OPEN srpstb PRIMARY READ WRITE
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ----------
9 128 1 0 CORRUPT
9 138 1 0 CORRUPT
SQL> select file_id,file_name,tablespace_name from dba_data_files where file_id=9;
FILE_ID FILE_NAME TABLESPACE_NAME
------- -------------------------------------------- ------------
9 +DATA_NEW/srpstb/datafile/myts.273.833748265 MYTS
Make sure that the standby database is in sync with the primary database.
Primary database:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
59
Standby database:
SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------ ------------- ---------------- -------------------------
OPEN srprim PHYSICAL STANDBY READ ONLY WITH APPLY
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
59
It could be noticed that the standby database is in sync with the primary with the latest archive sequence that is being applied is 59.
Consider taking a backup of the datafile 9 from the standby database. Here, I’ve taken a image copy backup.
[oracle@ora1-1 ~]$ rman target sys/oracle@srprim
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Apr 30 19:11:04 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rightsreserved.
connected to target database: SRPRIM (DBID=216679430)
RMAN> backup as copy datafile 9 format '/u02/bkp/MYTS_09.dbf';
Starting backup at 30-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=+DATA/srprim/datafile/myts.291.839878711
output file name=/u02/bkp/MYTS_09.dbf tag=TAG20140430T191204 RECID=22 STAMP=846270726
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-APR-14
Copy the above backup piece to the primary database server from the standby DB server.
[oracle@ora1-1 ~]$
[oracle@ora1-1 ~]$ scp /u02/bkp/MYTS_09.dbf oracle@ora1-2:/u02/bkp/MYTS_09.dbf
oracle@ora1-2's password:
MYTS_09.dbf 100% 10MB 10.0MB/s 00:00
[oracle@ora1-1 ~]$
Connect to the primary database and get the corrupted datafile offline.
Primary Database:
[oracle@ora1-2 ~]$ sqlplus sys/oracle@srpstb as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 30 19:13:39 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> alter database datafile 9 offline;
Database altered.
Catalog the above copied backup copy with the primary database so that the controlfile of the primary gets updated with this backup copy.
[oracle@ora1-2 ~]$ rman target sys/oracle@srpstb
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Apr 30 19:15:02 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SRPRIM (DBID=216679430)
RMAN> catalog datafilecopy '/u02/bkp/MYTS_09.dbf';
using target database control file instead of recovery catalog
cataloged datafile copy
datafile copy file name=/u02/bkp/MYTS_09.dbf RECID=14 STAMP=846271256
Restore and recover the datafile from the backup.
RMAN> restore datafile 9;
Starting restore at 30-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
channel ORA_DISK_1: restoring datafile 00009
input datafile copy RECID=14 STAMP=846271256 file name=/u02/bkp/MYTS_09.dbf
destination for restore of datafile 00009: +DATA_NEW/srpstb/datafile/myts.273.833748265
channel ORA_DISK_1: copied datafile copy of datafile 00009
output file name=+DATA_NEW/srpstb/datafile/myts.273.833748265 RECID=0 STAMP=0
Finished restore at 30-APR-14
RMAN> recover datafile 9;
Starting recover at 30-APR-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-APR-14
Once the recovery process is done, bring back the datafile 9 online .
RMAN> sql 'alter database datafile 9 online';
sql statement: alter database datafile 9 online
Now, validate this datafile and crosscheck if the corruption exists.
RMAN> validate check logical datafile 9;
Starting validate at 30-APR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=+DATA_NEW/srpstb/datafile/myts.273.833748265
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 1 1281 2835804
File Name: +DATA_NEW/srpstb/datafile/myts.273.833748265
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 1279
Finished validate at 30-APR-14
RMAN> exit
Recovery Manager complete.
It can be seen that there are no blocks being marked as corrupted.
Connect to the Primary and standby database verify if the standby is in sync with the primary
Primary database:
[oracle@ora1-2 ~]$
[oracle@ora1-2 ~]$
[oracle@ora1-2 ~]$ sqlplus sys/oracle@srpstb as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 30 19:33:06 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select * from v$database_block_corruption;
no rows selected
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
68
Standby database:
From the below outcome, it can be noted that datafile 9 of tablespace MYTS is corruped on the primary database.
SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------ ------------- ------------- -----------
OPEN srpstb PRIMARY READ WRITE
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ----------
9 128 1 0 CORRUPT
9 138 1 0 CORRUPT
SQL> select file_id,file_name,tablespace_name from dba_data_files where file_id=9;
FILE_ID FILE_NAME TABLESPACE_NAME
------- -------------------------------------------- ------------
9 +DATA_NEW/srpstb/datafile/myts.273.833748265 MYTS
Make sure that the standby database is in sync with the primary database.
Primary database:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
59
Standby database:
SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------ ------------- ---------------- -------------------------
OPEN srprim PHYSICAL STANDBY READ ONLY WITH APPLY
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
59
It could be noticed that the standby database is in sync with the primary with the latest archive sequence that is being applied is 59.
Consider taking a backup of the datafile 9 from the standby database. Here, I’ve taken a image copy backup.
[oracle@ora1-1 ~]$ rman target sys/oracle@srprim
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Apr 30 19:11:04 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rightsreserved.
connected to target database: SRPRIM (DBID=216679430)
RMAN> backup as copy datafile 9 format '/u02/bkp/MYTS_09.dbf';
Starting backup at 30-APR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=+DATA/srprim/datafile/myts.291.839878711
output file name=/u02/bkp/MYTS_09.dbf tag=TAG20140430T191204 RECID=22 STAMP=846270726
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 30-APR-14
Copy the above backup piece to the primary database server from the standby DB server.
[oracle@ora1-1 ~]$
[oracle@ora1-1 ~]$ scp /u02/bkp/MYTS_09.dbf oracle@ora1-2:/u02/bkp/MYTS_09.dbf
oracle@ora1-2's password:
MYTS_09.dbf 100% 10MB 10.0MB/s 00:00
[oracle@ora1-1 ~]$
Connect to the primary database and get the corrupted datafile offline.
Primary Database:
[oracle@ora1-2 ~]$ sqlplus sys/oracle@srpstb as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 30 19:13:39 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> alter database datafile 9 offline;
Database altered.
Catalog the above copied backup copy with the primary database so that the controlfile of the primary gets updated with this backup copy.
[oracle@ora1-2 ~]$ rman target sys/oracle@srpstb
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Apr 30 19:15:02 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SRPRIM (DBID=216679430)
RMAN> catalog datafilecopy '/u02/bkp/MYTS_09.dbf';
using target database control file instead of recovery catalog
cataloged datafile copy
datafile copy file name=/u02/bkp/MYTS_09.dbf RECID=14 STAMP=846271256
Restore and recover the datafile from the backup.
RMAN> restore datafile 9;
Starting restore at 30-APR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
channel ORA_DISK_1: restoring datafile 00009
input datafile copy RECID=14 STAMP=846271256 file name=/u02/bkp/MYTS_09.dbf
destination for restore of datafile 00009: +DATA_NEW/srpstb/datafile/myts.273.833748265
channel ORA_DISK_1: copied datafile copy of datafile 00009
output file name=+DATA_NEW/srpstb/datafile/myts.273.833748265 RECID=0 STAMP=0
Finished restore at 30-APR-14
RMAN> recover datafile 9;
Starting recover at 30-APR-14
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 30-APR-14
Once the recovery process is done, bring back the datafile 9 online .
RMAN> sql 'alter database datafile 9 online';
sql statement: alter database datafile 9 online
Now, validate this datafile and crosscheck if the corruption exists.
RMAN> validate check logical datafile 9;
Starting validate at 30-APR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00009 name=+DATA_NEW/srpstb/datafile/myts.273.833748265
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 1 1281 2835804
File Name: +DATA_NEW/srpstb/datafile/myts.273.833748265
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 1279
Finished validate at 30-APR-14
RMAN> exit
Recovery Manager complete.
It can be seen that there are no blocks being marked as corrupted.
Connect to the Primary and standby database verify if the standby is in sync with the primary
Primary database:
[oracle@ora1-2 ~]$
[oracle@ora1-2 ~]$
[oracle@ora1-2 ~]$ sqlplus sys/oracle@srpstb as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 30 19:33:06 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select * from v$database_block_corruption;
no rows selected
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
68
Standby database:
Comments
Post a Comment