Complete Recovery when NON-SYSTEM tablespace is missing and database is closed and restoring datafile to different location


Problem Generation

i. Shutdown the database.
SQL> SHUTDOWN immediate;
ii. Delete USERS tablespace datafile from operating system
[oracle@anar]$ rm -i users01.dbf
iii. Start the database
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 213909652 bytes
Database Buffers 318767104 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/disk5/anardb/users01.dbf’

Solution

i. Make datafile 4 offline
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;
ii. Open the database with datafile 4 oflfine
SQL> ALTER DATABASE OPEN;
iii. Using RMAN again connect to target database
[oracle@anardb scripts]$ rman target /
iv. Restoring and recovering datafile 4 at new location
RMAN> run
{
set newname for datafile 4 to ‘/u01/app/oracle/oradata/disk3/anardb/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘ alter database datafile 4 online’;
}
Advertisements

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: