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


Problem Generation

i. Delete USERS tablespace datafile from operating system
[oracle@anardb]$ rm -i users01.dbf
ii. Connect as user sh/sh and create a table in users tablespace
SQL> CREATE TABLE sales_test
TABLESPACE users
AS
SELECT *
FROM sales
WHERE ROWNUM < 10;
ERROR at line 3:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/disk3/anardb/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
Solution

i. As DBA user make datafile users01.dbf off line
SQL> SELECT file#, name FROM v$datafile;
SQL> ALTER DATABASE DATAFILE 4 OFFLINE;
ii. Restoring and recovering datafile 4 at new location
RMAN> run
{
set newname for datafile 4 to ‘/u01/app/oracle/oradata/disk5/anardb/users01.dbf’;
restore datafile 4;
switch datafile 4;
recover datafile 4;
sql ‘ alter database datafile 4 online’;
}
iii. Connect as user sh/sh and create a table in users tablespace
SQL> CREATE TABLE sales_test
TABLESPACE users
AS
SELECT *
FROM sales
WHERE ROWNUM < 10;
Table created.
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: