When Several Datafiles Are Missing - Restore From the Standby Database

Not only the local backups can save the datafiles, but also the standby database can be a source to restore the datafiles. There are four major steps that can accomplish this:

• Copy datafiles from the standby database.
• Catalog them.
• Switch them.
• Recover them.

Here is a demonstration below:

1. Stop the primary database.
[oracle@primary01 ~]$ srvctl stop database -d compdb

2. Remove several datafiles to simulate this scenario.
[oracle@primary02 ~]$ asmcmd
ASMCMD> cd +data/primdb/datafile
ASMCMD> ls
EXAMPLE.900.797943711
SYSAUX.898.797943569
SYSTEM.897.797943475
UNDOTBS1.899.797943687
UNDOTBS2.901.797943737
USERS.902.797943753
ASMCMD> rm USERS.902.797943753
ASMCMD> rm EXAMPLE.900.797943711
ASMCMD> ls
SYSAUX.898.797943569
SYSTEM.897.797943475
UNDOTBS1.899.797943687
UNDOTBS2.901.797943737
ASMCMD>

3. Try to startup an instance of the primary cluster database. It will fail as expected.
[oracle@primary01 ~]$ sqlplus / as sysdba
...
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2217992 bytes
Variable Size             792725496 bytes
Database Buffers           41943040 bytes
Redo Buffers                2396160 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DATA/primdb/datafile/users.902.797943753'
The database can only be mounted now.

4. Connect to target and auxiliary database with RMAN.
Please note that, now the target database is the standby database, the auxiliary database is the primary database.
[oracle@standby01 ~]$ rman target / auxiliary sys/password@primdb1
...
connected to target database: COMPDB (DBID=841830157, not open)
connected to auxiliary database: COMPDB (DBID=841830157, not open)
Both databases are mounted, not open.

5. Copy the datafiles from the target (standby) to auxiliary (primary) database.
RMAN> backup as copy datafile 4,5 auxiliary format '+data';

Starting backup at 22-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=82 instance=standb1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/standb/datafile/example.270.799012469
output file name=+DATA/primdb/datafile/example.900.805375051 tag=TAG20130122T111730
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/standb/datafile/users.268.799012479
output file name=+DATA/primdb/datafile/users.902.805375077 tag=TAG20130122T111730
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 22-JAN-13

6. Try to open the primary database again, but failed.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DATA/primdb/datafile/users.902.797943753'
It's reasonable that the database knows nothing about the newly added datafile copies before cataloging and switching them.

7. Connect to the primary database with RMAN.
[oracle@primary01 ~]$ rman target /
...
connected to target database: COMPDB (DBID=841830157, not open)

8. Catalog the new arrivals.
You can catalog the two datafiles explicitly.
RMAN> catalog datafilecopy '+data/primdb/datafile/USERS.902.805375077';

cataloged datafile copy
datafile copy file name=+DATA/primdb/datafile/users.902.805375077 RECID=16 STAMP=805375765

RMAN> catalog datafilecopy '+data/primdb/datafile/EXAMPLE.900.805375051';

cataloged datafile copy
datafile copy file name=+DATA/primdb/datafile/example.900.805375051 RECID=17 STAMP=805375794
Another way to catalog the two datafiles is to use START WITH clause.
RMAN> catalog start with '+data/primdb/datafile';

searching for all files that match the pattern +data/primdb/datafile

List of Files Unknown to the Database
=====================================
File Name: +data/primdb/DATAFILE/EXAMPLE.1120.805383723
File Name: +data/primdb/DATAFILE/USERS.1121.805383737

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +data/primdb/DATAFILE/EXAMPLE.1120.805383723
File Name: +data/primdb/DATAFILE/USERS.1121.805383737

9. Switch to new copies.
You can catalog the two datafiles one by one explicitly.
RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy "+DATA/primdb/datafile/users.902.805375077"

RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy "+DATA/primdb/datafile/example.900.805375051"
Or you can do it together.
RMAN> switch datafile 4,5 to copy;

datafile 4 switched to datafile copy "+DATA/primdb/datafile/users.1121.805383737"
datafile 5 switched to datafile copy "+DATA/primdb/datafile/example.1120.805383723"
If there are too many unmemorable datafiles needed to be switched, I suggest you to copy all the database from the standby and switch database instead.

10. Recover the datafiles.
Although the working datafiles point to the right copies, they can not be used in this moment, they needs to be recovered.
RMAN> recover database;

Starting recover at 22-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 instance=primdb1 device type=DISK

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

Finished recover at 22-JAN-13

11. Alter the primary database open.
RMAN> alter database open;

database opened
Yes, all done.

More related posts that you may be interested in:

• When the SPFILE is Missing
• When the Controlfile is Missing
• When Some of the Archived Logs are Missing
• When Several Datafiles Are Missing - Restore From Local Backups
• When Several Datafiles Are Missing - Restore From the Standby Database
• When Almost Everything of a Database is Missing
• How Will the Database React to Missing Tempfiles