Advertisements
Skip to content
May 3, 2014 / Shivananda Rao P

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, 2013, 2014. 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.

Advertisements

5 Comments

Leave a Comment
  1. mani / May 22 2014 9:30 pm

    hi can we do the same, if system datafile currupted on primary database.

  2. Shivananda Rao P / May 24 2014 5:40 am

    Hi Mani,

    Yes, you can get a backup copy of system datafile from standby database and have it restored and recovered on the primary database. Standby database is a mirror copy of the primary database. So, if any datafile (including SYSTEM and SYSAUX) is corrupted on primary, you can get it replaced from it’s standby database, provided the datafile on the standby is not corrupted.

    Regards,
    Shivananda

  3. Mani / May 28 2014 6:36 pm

    Hi Shiva, thanks a lot buddy………..

  4. lakshmi / Jul 21 2014 1:49 pm

    Is it safe to back up before we do any changes to primary ? Also, do you have blogs for rac ?

  5. Shivananda Rao P / Jul 21 2014 2:03 pm

    Hi Lakshmi,

    Yes, it’s the best and suggested to take a backup of the primary database before you do any DB level changes to it. But in this case, since the datafile on the primary database is already corrupted and you take a backup of this database, then this doesn’t make sense as you are backing up a corrupted database.

    Yes, I do have the articles for RAC under the “DATABASE” category. I’m in the process of writing more articles on RAC. Hope I finish it off soon 🙂

    Regards,
    Shivananda

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: