误删除数据文件，如果没有备份，在不关库的情况下，是可以恢复的。如：rm /u01/app/oracle/oradata/PROD1/test.dbf
-
创建测试表空间
点击(此处)折叠或打开
-
SQL> create tablespace test02 datafile '/u01/app/oracle/oradata/PROD1/test02.dbf' size 10M autoextend on maxsize 2G;
-
- Tablespace created.
-
SQL> create tablespace test02 datafile '/u01/app/oracle/oradata/PROD1/test02.dbf' size 10M autoextend on maxsize 2G;
-
在测试表空间上创建测试表
点击(此处)折叠或打开
-
SQL> create table t tablespace test02 as select * from dba_objects;
-
- Table created.
-
SQL> create table t tablespace test02 as select * from dba_objects;
-
删除数据文件
点击(此处)折叠或打开
- SQL> !rm /u01/app/oracle/oradata/PROD1/test02.dbf
-
再次建表，验证数据文件已经丢失
点击(此处)折叠或打开
-
SQL> create table t1 tablespace test02 as select * from dba_objects;
-
create table t1 tablespace test02 as select * from dba_objects
-
*
-
ERROR at line 1:
-
ORA-01565: error in identifying file '/u01/app/oracle/oradata/PROD1/test02.dbf'
-
ORA-27037: unable to obtain file status
-
Linux-x86_64 Error: 2: No such file or directory
- Additional information: 3
-
SQL> create table t1 tablespace test02 as select * from dba_objects;
-
切记，此时不能关闭数据库 -
查看DBWR进程的pid
点击(此处)折叠或打开
-
[oracle@host01 PROD1]$ ps -ef|grep dbw|grep -v grep
- oracle 19784 1 0 21:59 ? 00:00:00 ora_dbw0_PROD1
-
[oracle@host01 PROD1]$ ps -ef|grep dbw|grep -v grep
-
找到句柄
点击(此处)折叠或打开
-
[oracle@host01 PROD1]$ ls -rtl /proc/19784/fd
-
total 0
-
lrwx------ 1 oracle oinstall 64 Apr 5 22:15 9 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkPROD1
-
lrwx------ 1 oracle oinstall 64 Apr 5 22:15 8 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_PROD1.dat
-
lr-x------ 1 oracle oinstall 64 Apr 5 22:15 7 -> /dev/zero
-
lr-x------ 1 oracle oinstall 64 Apr 5 22:15 6 -> /proc/19784/fd
-
lr-x------ 1 oracle oinstall 64 Apr 5 22:15 5 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
-
lr-x------ 1 oracle oinstall 64 Apr 5 22:15 4 -> /dev/null
-
lr-x------ 1 oracle oinstall 64 Apr 5 22:15 3 -> /dev/null
- lrwx------ 1 oracle oinstall 64 Apr 5 22:15 265 -> /u01/app/oracle/oradata/PROD1/test02.dbf (deleted)
-
[oracle@host01 PROD1]$ ls -rtl /proc/19784/fd
-
将找到的句柄拷贝回来
点击(此处)折叠或打开
- [oracle@host01 PROD1]$ cp /proc//19784/fd/265 /u01/app/oracle/oradata/PROD1/test02.dbf
-
查看数据文件的状态
点击(此处)折叠或打开
-
SQL> select file#,status,name from v$datafile;
-
-
FILE# STATUS NAME
-
---------- ------- --------------------------------------------------
-
1 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
-
2 ONLINE /u01/app/oracle/oradata/PROD1/sysaux01.dbf
-
3 ONLINE /u01/app/oracle/oradata/PROD1/undotbs01.dbf
-
4 ONLINE /u01/app/oracle/oradata/PROD1/users01.dbf
-
5 ONLINE /u01/app/oracle/oradata/PROD1/example01.dbf
-
6 ONLINE /u01/app/oracle/oradata/PROD1/test01.dbf
-
7 ONLINE /u01/app/oracle/oradata/PROD1/test02.dbf
-
- 7 rows selected.
-
SQL> select file#,status,name from v$datafile;
-
将数据文件offline
点击(此处)折叠或打开
-
SQL> alter database datafile 6 offline;
-
- Database altered.
-
SQL> select file#,status,name from v$datafile;
FILE# STATUS NAME
---------- ------- --------------------------------------------------
1 SYSTEM /u01/app/oracle/oradata/PROD1/system01.dbf
2 ONLINE /u01/app/oracle/oradata/PROD1/sysaux01.dbf
3 ONLINE /u01/app/oracle/oradata/PROD1/undotbs01.dbf
4 ONLINE /u01/app/oracle/oradata/PROD1/users01.dbf
5 ONLINE /u01/app/oracle/oradata/PROD1/example01.dbf
6 RECOVER /u01/app/oracle/oradata/PROD1/test01.dbf
7 ONLINE /u01/app/oracle/oradata/PROD1/test02.dbf
-
SQL> alter database datafile 6 offline;
-
恢复数据文件
点击(此处)折叠或打开
-
SQL> recover datafile 6;
- Media recovery complete.
-
SQL> recover datafile 6;
-
将数据文件online
点击(此处)折叠或打开
-
SQL> alter database datafile 6 online;
-
- Database altered.
-
SQL> alter database datafile 6 online;
-
验证
点击(此处)折叠或打开
-
SQL> select count(*) from t;
-
-
COUNT(*)
-
----------
- 86955
-
SQL> select count(*) from t;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31405405/viewspace-2136673/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
利用热备恢复表空间
下一篇：
基于时间的不完全恢复
请登录后发表评论
登录
全部评论
