Restoration of accidentally deleted Oracle data files

Environment: the database is in Open state, then rm-rf users. dbf (delete users tablespace), and finally retrieve the users. dbf file.

When replying, the reply method has little to do with the database version (because it is a physical file), but has a certain relationship with the operating system, such as Red hat Linux and Solaris Linux.

 

The following is an example:

Delete users tablespace.

1. First, go to Sqlplus and execute the SQL statement:

Select name from v $ datafile;
NAME
--------------------------------------------------------------------------------
/U01/app/oracle/oradata/prod/disk4/system01.dbf
/U01/app/oracle/oradata/prod/disk2/undotbs01.dbf
/U01/app/oracle/oradata/prod/disk5/sysaux01.dbf
/U01/app/oracle/oradata/prod/disk5/bigtbs01.dbf
/U01/app/oracle/oradata/prod/disk5/indx01.dbf
/U01/app/oracle/oradata/prod/disk2/users01.dbf
/U01/app/oracle/oradata/prod/disk3/oltp01.dbf
 
7 rows selected.
 
SQL>! Rm/u01/app/oracle/oradata/prod/disk2/users01.dbf tried today, delete a data file in Linux, and then reply.

 

2. At this time, the users tablespace has been deleted and we cannot create a table in the users tablespace.

You cannot create a table named abcd123 in the users tablespace.


SQL> create table abcd123 tablespace users as select * from v $ instance;
Create table abcd123 tablespace users as select * from v $ instance
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/prod/disk2/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

3. Confirm the dbwr process PID

Through the command, we can see the dbwr PID of the current database instance. Because I have two instances on the same host, I have two instances.

One is prod and the other is oms.


$ Ps-ef | grep dbw0 | grep-v grep
Oracle 11872 1 0? 00:00:00 ora_dbw0_prod
Oracle 11951 1 0? 00:00:01 ora_dbw0_oms

4. Because dbwr opens the handles of all data files. Therefore, you can enter the proc directory for search, in the format:

Cd/prod/PID/fd

Here, fd is the file descriptor.


[Oracle @ prod fd] $ cd/proc/11872/fd
[Oracle @ prod fd] $ ls-l
Total 30
Lr-x ------ 1 oracle dba 64 May 14 0->/dev/null
Lr-x ------ 1 oracle dba 64 May 14 1->/dev/null
Lrwx ------ 1 oracle dba 64 May 14 10->/u01/app/oracle/product/10.2.0/db_1/rdbms/audit/ora_11859.aud
Lr-x ------ 1 oracle dba 64 May 14 11->/dev/zero
Lr-x ------ 1 oracle dba 64 May 14 12->/dev/zero
Lr-x ------ 1 oracle dba 64 May 14>/u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus. msb
Lrwx ------ 1 oracle dba 64 May 14 14->/u01/app/oracle/product/10.2.0/db_1/dbs/hc_prod.dat
Lrwx ------ 1 oracle dba 64 May 14 15->/u01/app/oracle/product/10.2.0/db_1/dbs/lkPROD
Lrwx ------ 1 oracle dba 64 May 14 16->/u01/app/oracle/oradata/prod/disk1/control01.ctl
Lrwx ------ 1 oracle dba 64 May 14 17->/u01/app/oracle/oradata/prod/disk2/control02.ctl
Lrwx ------ 1 oracle dba 64 May 14 18->/u01/app/oracle/oradata/prod/disk3/control03.ctl
Lrwx ------ 1 oracle dba 64 May 14 19->/u01/app/oracle/oradata/prod/disk4/system01.dbf
Lr-x ------ 1 oracle dba 64 May 14 2->/dev/null
Lrwx ------ 1 oracle dba 64 May 14 20->/u01/app/oracle/oradata/prod/disk2/undotbs01.dbf
Lrwx ------ 1 oracle dba 64 May 14 21->/u01/app/oracle/oradata/prod/disk5/sysaux01.dbf
Lrwx ------ 1 oracle dba 64 May 14 22->/u01/app/oracle/oradata/prod/disk5/bigtbs01.dbf
Lrwx ------ 1 oracle dba 64 May 14->/u01/app/oracle/oradata/prod/disk5/indx01.dbf
Lrwx ------ 1 oracle dba 64 May 14->/u01/app/oracle/oradata/prod/disk2/users01.dbf (deleted)
Lrwx ------ 1 oracle dba 64 May 14 25->/u01/app/oracle/oradata/prod/disk3/oltp01.dbf
Lrwx ------ 1 oracle dba 64 May 14 26->/u01/app/oracle/oradata/prod/disk1/temp01.dbf
Lrwx ------ 1 oracle dba 64 May 14 27->/u01/app/oracle/oradata/prod/disk3/lmtemp201.dbf
Lrwx ------ 1 oracle dba 64 May 14 28->/u01/app/oracle/oradata/prod/disk4/lmtemp201.dbf
Lr-x ------ 1 oracle dba 64 May 14 29->/u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus. msb
Lr-x ------ 1 oracle dba 64 May 14 3->/dev/null
Lr-x ------ 1 oracle dba 64 May 14 4->/dev/null
L-wx ------ 1 oracle dba 64 May 14 5->/u01/app/oracle/admin/udump/prod_ora_11859.trc
L-wx ------ 1 oracle dba 64 May 14 6->/u01/app/oracle/admin/bdump/alert_prod.log
Lrwx ------ 1 oracle dba 64 May 14 7->/u01/app/oracle/product/10.2.0/db_1/dbs/hc_prod.dat
L-wx ------ 1 oracle dba 64 May 14 8->/u01/app/oracle/admin/bdump/alert_prod.log
Lrwx ------ 1 oracle dba 64 May 14 9->/u01/app/oracle/product/10.2.0/db_1/dbs/lkinstprod (deleted)
[Oracle @ prod fd] $

 

We can clearly see the words/u01/app/oracle/oradata/prod/disk2/users01.dbf (deleted), indicating that the file has been deleted.

5. Restore

Run the cp command to copy the statement handle back to the original position.

Cp 24/u01/app/oracle/oradata/prod/disk2/users01.dbf

Here, 24 is the number given by it.

 

6. Go to recovery

Run the following SQL command:

SQL> alter database datafile 6 offline;
 
Database altered.
 
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;
 
Database altered.
 
SQL>

 

 

Now, the reply is complete!

 

7. Test

We try again to create a table in the users tablespace.

SQL> create table abcd123 tablespace users as select * from v $ instance;
 
Table created.
 
SQL> select * from abcd123;
 
INSTANCE_NUMBER INSTANCE_NAME
-------------------------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T status par thread # ARCHIVE LOG_SWITCH_WAIT
-------------------------------------------------------------------------
Logins shu DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
------------------------------------------------------------
1 prod
Prod
10.2.0.1.0 16-APR-12 open no 1 STARTED
Allowed no active PRIMARY_INSTANCE NORMAL NO
 
SQL>
 

We can find that the establishment is successful, indicating that the reply is successful,