oracle数据文件被误操作删除了恢复方法

柔情只为你懂 2022-06-11 00:19 1123阅读 0赞

数据文件误删除的恢复方法  
不小心在操作系统里把oracle的某一个数据文件删除了，下面构造这样的场景并恢复  
1.创建一个表空间  
SQL> create  tablespace db01  
  2  datafile '/opt/oracle/db/oradata/oradb/db01.dbf' size 10M;  
Tablespace created.  
1.创建一个用户db01  
create user db01 identified by db01 default tablespace db01;  
SQL> create user db01 identified by db01 default tablespace db01;  
User created.  
2.以创建的用户登入并创建一张表test插入两条数据  
create table test (id varchar(5),name varchar(10));  
SQL> grant connect,resource to db01;  
Grant succeeded.  
SQL> conn db01/db01  
Connected.      
SQL> create table test (id varchar(5),name varchar(10));  
Table created.  
SQL>  
3.插入两条数据到test中  
insert into test values (1,'jack');  
insert into test values (2,'peck');  
commit;  
SQL> select \* from test;  
ID         NAME  
\---------- --------------------  
1          jack  
2          peck  
4.手动把db01.dbf这个数据文件删掉  
这时alert日志会报错  
Errors in file /opt/oracle/db/diag/rdbms/oradb/oradb/trace/oradb\_m000\_6495.trc:  
ORA-01116: error in opening database file 7  
ORA-01110: data file 7: '/opt/oracle/db/oradata/oradb/db01.dbf'  
ORA-27041: unable to open file  
Linux Error: 2: No such file or directory  
Additional information: 3  
Mon May 28 21:54:41 2012  
Linux Error: 2: No such file or directory  
Additional information: 3  
Errors in file  
数据库做检查点报错数据库会宕掉  
/opt/oracle/db/diag/rdbms/oradb/oradb/trace/oradb\_ckpt\_4755.trc:  
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode  
ORA-01116: error in opening database file 7  
ORA-01110: data file 7: '/opt/oracle/db/oradata/oradb/db01.dbf'  
ORA-27041: unable to open file  
Linux Error: 2: No such file or directory  
Additional information: 3

5.此时启动数据库会报错  
SQL> startup  
ORACLE instance started.  
Total System Global Area  238530560 bytes  
Fixed Size                  1335724 bytes  
Variable Size             146804308 bytes  
Database Buffers           88080384 bytes  
Redo Buffers                2310144 bytes  
Database mounted.  
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file  
ORA-01110: data file 7: '/opt/oracle/db/oradata/oradb/db01.dbf'  
数据库启动到mount状态  
6.执行alter database create datafile创建数据文件  
SQL> alter database create datafile'/opt/oracle/db/oradata/oradb/db01.dbf';  
Database altered.  
7.把数据库启动到open状态  
SQL> alter database open；  
alter database open  
\*  
ERROR at line 1:  
ORA-01113: file 7 needs media recovery  
ORA-01110: data file 7: '/opt/oracle/db/oradata/oradb/db01.dbf'  
提示需要介质恢复  
执行recover操作  
SQL> recover datafile '/opt/oracle/db/oradata/oradb/db01.dbf';  
Media recovery complete.  
SQL> alter database open;

Database altered.  
数据库启动成功

8.连接到db01用户查询test数据  
SQL> conn db01/db01  
Connected.  
SQL> select \* from test  
  2  /  
ID         NAME  
\---------- --------------------  
1          jack  
2          peck  
9.至此恢复完成。