RMAN - restoring tablespaces/datafiles

• Restore a datafile in "Recovery" status
• Restore a single tablespace
• Fix these errors: RMAN-00571,RMAN-00569,RMAN-00571,RMAN-03002,ORA-00283,RMAN-11003,ORA-00283,ORA-01111,ORA-01110,ORA-01157,ORA-01111,ORA-01110

I added a few files to a tablespace in trouble. Months later I went to restore the database on another server for a refresh. I didn't remember to call out the newer datafiles in the recovery script I wrote for that refresh.

The database restored, but users soon reported odd errors in the applications hitting it.

Oops.

I'd already done a resetlogs, so I was starting to get a sinking feeling.

Happily I was running Oracle 10.2.x... so I could restore through the resetlogs.

Here's what I did:

1. Identify which tablespaces and datafiles were offline: In OEM (emctl start dbconsole will give you the URL) I navigated to Administration > datafiles and looked for any datafiles that did not have a status of ONLINE
2. Once I identified those I jotted down the TABLESPACE they were a part of and the MISSING000## file name Oracle had assigned it.
3. I renamed the datafiles via SQLPlus from MISSING000## to its legit name
4. I ran a (base) restore and subsequent recover of the datafile

It looked something like the following

Note that:

• My backup files were already cataloged
• The tablespace is ryan_boyer_tablespace
• The missing datafile is datafile #25
• The missing datafile should be called "ryanboyer_indx03.dbf"
• Oracle is calling the missing file MISSING00025
• The datafile should reside in /u03/oradata/ryanboyer/...

First take the tablespace offline (from RMAN execute like so: sql 'sql command'; ): sql 'alter tablespace ryan_boyer_tablespace offline immediate'; Now try a recovery/restore in a run block (also executed from RMAN): run { set newname for datafile 25 to '/u03/oradata/ryanboyer/ryan_boyer_indx03.dbf'; restore datafile 25; recover datafile 25; }

In my case the run block failed with the following: starting media recovery media recovery failed RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 01/14/2011 20:42:30 ORA-00283: recovery session canceled due to errors RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed datafile 25 ORA-00283: recovery session canceled due to errors ORA-01111: name for data file 25 is unknown - rename to correct file ORA-01110: data file 25: '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00025' ORA-01157: cannot identify/lock data file 25 - see DBWR trace file ORA-01111: name for data file 25 is unknown - rename to correct file ORA-01110: data file 25: '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00025'

I entered SQLPLUS to get the MISSING0025 datafile renamed to what it should be, ryanboyer_indx03.dbf ALTER TABLESPACE ryan_boyer_tablespace rename datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00025' to '/u03/oradata/ryanboyer/ryanboyer_indx03.dbf';

I was greeted with Tablespace altered.. Phew.

Now I went back to RMAN and re-ran my restore/recovery: run { set newname for datafile 25 to '/u03/oradata/ryanboyer/ryanboyer_indx03.dbf'; restore datafile 25; recover datafile 25; }

I was greeted with output similar to the following: eady on disk as file /u04/oradata/flash_recovery_area/ryanboyer/archivelog/ryanboyer_1_397_740318237.arc archive log thread 1 sequence 398 is already on disk as file /u04/oradata/flash_recovery_area/ryanboyer/archivelog/ryanboyer_1_398_74038237.arc... archive log filename=/u04/restore/boyer_1_52484_608657515.arc thread=1 sequence=5484 archive log filename=/u04/restore/boyer_1_52485_608657515.arc thread=1 sequence=5485 archive log filename=/u04/restore/boyer_1_52486_608657515.arc thread=1 sequence=5486 archive log filename=/u04/restore/boyer_1_52487_608657515.arc thread=1 sequence=5487 archive log filename=/u04/restore/boyer_1_52488_608657515.arc thread=1 sequence=5488 archive log filename=/u04/restore/boyer_1_52494_608657515.arc thread=1 sequence=5494 archive log filename=/u04/oradata/flash_recovery_area/ryanboyer/archivelog/ryanboyer_1_1_74038237.arc thread=1 sequence=1 media recovery complete, elapsed time: 00:00:05 Finished recover at 14-JAN-2011 20:50:28

Double *Phew*.

Now I bring the tablespace back online (shown also from RMAN): sql 'alter database datafile 25 online';

Honestly though I was greeted with errors indicating that datafiles that were also a part of that tablespace needed to be restored prior to the tablespace successfully being brought online. Only after repeating the above steps for each datafile in the affected tablespace was I able to bring the tablespace online.

Please comment if you find any of this helpful.