Recovery after physical deletion of Oracle data files

Do the system management is so, inevitably will mistakenly delete files, one day if an Oracle data file deleted, then how to recover it? (Here the database is open and not closed)

1. Create a test table space

2. Create a test user

3. Inserting test data

4. Delete a data file

5. Recovering a database file

1. Create a test table space
   

Sql> select name from V$datafile; NAME--------------------------------------------------------------------------------/opt/oracle/oradata/member /system01.dbf/opt/oracle/oradata/member/sysaux01.dbf/opt/oracle/oradata/member/undotbs01.dbf/opt/oracle/ oradata/member/users01.dbfsql> Create tablespace Test datafile '/opt/oracle/oradata/member/test01.dbf ' size 10m; Tablespace created. Sql> select name from V$datafile; NAME--------------------------------------------------------------------------------/opt/oracle/oradata/member /system01.dbf/opt/oracle/oradata/member/sysaux01.dbf/opt/oracle/oradata/member/undotbs01.dbf/opt/oracle/ Oradata/member/users01.dbf/opt/oracle/oradata/member/test01.dbf

2. Create a Test account

Sql> create user test identified by test default tablespace test; Sql> Grant Connect,resource to test;

3. Inserting test data

Sql> conn test/testsql> CREATE TABLE t1 (id int); sql> INSERT INTO T1 values (1);        Sql> select * from T1; ID----------1

4. Delete data files

[[email protected] ~]$ rm -f /opt/oracle/oradata/member/test01.dbf[[email  Protected] ~]$ sqlplus test/testsql> create table t2 as select  * from t1;create table t2 as select * from t1                                   *error at line 1 :ora-01116: error in opening database file 5ora-01110: data file  5:  '/opt/oracle/oradata/member/test01.dbf ' ora-27041: unable to open filelinux-x86_64  error: 2: no such file or directoryadditional information: 3sql > select table_name,tablespace_name from user_tables; Table_name        &nbsP;            tablespace_ NAME------------------------------ ------------------------------t1                               test

The data file has been deleted, what should I do? Don't restart the database at this time, otherwise the data will be lost.

5. Data File Recovery

[[email protected] ~]$ ps-ef |grep dbw0oracle 3309 1 0 12:07?  00:00:00 ora_dbw0_memberoracle 6217 5105 0 15:29 pts/0 00:00:00 grep dbw0# Find the ORA_DBW0_SID process number 3309[[email protected] ~]$ cd/proc/3309/fd# above 3309 is the process number and then executes Ls-al view File link

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/7A/10/wKiom1ah23qx99khAAKZNXM6Oh8053.jpg "title=" 12.jpg "alt=" Wkiom1ah23qx99khaakznxm6oh8053.jpg "/>

You can see that file 27 is the deleted file.

[email protected] fd]$ CP 27/opt/oracle/oradata/member/test01.dbf

View Test table Space status

Sql> Select Name,status from v$datafile;name status-------------------------------------------- ---/opt/oracle/oradata/member/system01.dbf system/opt/oracle/oradata/member/sysaux01.dbf ONLINE/opt/oracle/ ORADATA/MEMBER/UNDOTBS01.DBF online/opt/oracle/oradata/member/users01.dbf online/opt/oracle/oradata/member/ TEST01.DBF ONLINE

Downline test01 Table File

sql> ALTER DATABASE datafile '/OPT/ORACLE/ORADATA/MEMBER/TEST01.DBF ' offline; sql> recover datafile '/opt/oracle/oradata/member/test01.dbf '; Media recovery complete. sql> ALTER DATABASE datafile '/OPT/ORACLE/ORADATA/MEMBER/TEST01.DBF ' online;database altered.

#以上就成功恢复了, if it appears

sql> recover datafile '/opt/oracle/oradata/member/test01.dbf '; ora-00283:recovery session canceled due to Errorsora-01110:data file 5: '/opt/oracle/oradata/member/test01.dbf ' ora-01157:cannot identify/lock data file 5-see DBW R Trace Fileora-01110:data file 5: '/OPT/ORACLE/ORADATA/MEMBER/TEST01.DBF '

It is possible that the permissions of the/opt/oracle/oradata/member/test01.dbf file are causing the problem, under the root user

[[email protected] ~]# chown-r oracle.oinstall/opt/oracle/oradata/member/test01.dbf recover datafile '/opt/oracle/ ORADATA/MEMBER/TEST01.DBF '

After the experiment is complete, delete the test user and the test table space

sql> drop user test cascade; sql> drop tablespace test including CONTENTS and datafiles;

This article is from "Maple Night" blog, please be sure to keep this source http://fengwan.blog.51cto.com/508652/1737664

Recovery after physical deletion of Oracle data files