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

最新推荐文章于 2023-02-13 19:06:03 发布

原创最新推荐文章于 2023-02-13 19:06:03 发布 · 1.2k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #database #file #数据库 #sql #insert

oracle 专栏收录该内容

150 篇文章

订阅专栏

本文详细介绍了在操作Oracle数据库时，不慎删除数据文件后的恢复步骤，包括创建表空间、用户、表，插入数据，删除数据文件，启动数据库报错，执行介质恢复等关键操作，最终成功恢复数据。

数据文件误删除的恢复方法
不小心在操作系统里把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.至此恢复完成。