Recovering a corrupted/lost datafile on Primary database from the 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: srpstb
Standby 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:

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
68

Here we go !!

 

 

COPYRIGHT

© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

 

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.