Recovery from loss of datafiles on primary database (using standby datafiles)

You can recover from loss of datafiles on primary site by restoring and recovering the datafiles from backup or using the files on standby database. In this example I will go through recovery using standby datafiles.

I have two databases up and running on 11.2.0.3.0 version, orcl (standby) and orclstb (primary). Also, there is recovery catalog rctdb.

[oracle@edvm3pc ~]$ rman target / catalog=rctadmin/rctadmin@rctdb

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Aug 24 17:46:11 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1351425709)
connected to recovery catalog database

RMAN>
RMAN> report schema;

Report of database schema for database with db_unique_name ORCLSTB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    720      SYSTEM               YES     /u01/app/oracle/oradata/orclstb/system01.dbf
2    630      SYSAUX               NO      /u01/app/oracle/oradata/orclstb/sysaux01.dbf
3    105      UNDOTBS1             YES     /u01/app/oracle/oradata/orclstb/undotbs01.dbf
4    5        USERS                NO      /u01/app/oracle/oradata/orclstb/users01.dbf
5    345      EXAMPLE              NO      /home/oracle/example01.dbf
6    10       DEMO01               NO      /u01/app/oracle/oradata/demo01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/orclstb/temp01.dbf

RMAN>

I am interested in datafile 6, tablespace name DEMO01.
Lets delete the datafile 6, simulating loss of the datafile.

[oracle@edvm3pc ~]$ rm /u01/app/oracle/oradata/demo01.dbf
[oracle@edvm3pc ~]$

Now, lets test if I can insert row into a table which is stored in the DEMO01 tablespace.
I received the following error.

SQL> insert into t values (1);
insert into t values (1)
            *
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/demo01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

To fix this issue I have two options – restore and recover the datafile from a backup or backup a copy of the datafile from the standby database to the primary database and switch to the datafile copy. I’ll go with the second option.

STANDBY site:
Login as target on the standby database and as auxiliary on the primary database.

Make a datafile copy of the missing datafile to the auxiliary destination (in this case primary site).

[oracle@edvm2pc ~]$ rman target / auxiliary=sys/oracle_4U@primarydb

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Aug 24 17:59:52 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1351425709, not open)
connected to auxiliary database: ORCL (DBID=1351425709)

RMAN>

RMAN> backup as copy datafile 6 auxiliary format '/u01/app/oracle/oradata/orclstb/demo01.dbf';

Starting backup at 24-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/demo01.dbf
output file name=/u01/app/oracle/oradata/orclstb/demo01.dbf tag=TAG20130824T180043
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 24-AUG-13

RMAN>

PRIMARY site steps:
1. Change the datafile status to offline.
2. Catalog the datafile copy from the standby database.
3. Switch the datafile to use the new copy (updates the controlfile).
4. Recover the datafile.
5. Change the datafile status to online.

[oracle@edvm3pc ~]$
[oracle@edvm3pc ~]$ rman target / catalog=rctadmin/rctadmin@rctdb

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Aug 24 18:05:05 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1351425709)
connected to recovery catalog database

RMAN>

RMAN> sql 'alter database datafile 6 offline';

sql statement: alter database datafile 6 offline

RMAN> catalog datafilecopy '/u01/app/oracle/oradata/orclstb/demo01.dbf';

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/orclstb/demo01.dbf RECID=4 STAMP=824321134
RMAN-08591: WARNING: invalid archived log deletion policy

RMAN>

RMAN> run {
2> set newname for datafile 6 to '/u01/app/oracle/oradata/orclstb/demo01.dbf';
3> switch datafile 6;
4> }

executing command: SET NEWNAME

datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=824321134 file name=/u01/app/oracle/oradata/orclstb/demo01.dbf
starting full resync of recovery catalog
full resync complete

RMAN> recover datafile 6;

Starting recover at 24-AUG-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 24-AUG-13

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN>

Lets now test to see if I can succeed in inserting row in the table T.

[oracle@edvm3pc ~]$
[oracle@edvm3pc ~]$ sqlplus demo_user/demo_user

SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 24 18:08:15 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> insert into t values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> col name format a50
SQL> select status,name from v$datafile where file# = 6;

STATUS  NAME
------- --------------------------------------------------
ONLINE  /u01/app/oracle/oradata/orclstb/demo01.dbf

SQL>

Leave a Reply