使用文件句柄方式恢复误删除的Oracle数据文件

环境说明：
操作系统：Linux
数据库版本：11.2.0.4

begin~

• 创建表空间test_tb

SQL> create tablespace test_tb datafile '/u01/app/oracle/oradata/PROD/test.dbf' size 50M;

Tablespace created.

• 创建用户test

SQL> create user test identified by oracle default tablespace test_tb;

User created.

• 给用户test授权

SQL> grant connect,resource,dba to test;

Grant succeeded.

• 创建表t1

SQL> conn test/oracle
Connected.

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
     82347

• 查看数据库归档模式

SQL> archive log list
Database log mode          No Archive Mode
Automatic archival         Disabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     65
Current log sequence           67

• 将数据库启动到mount状态

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size          2252824 bytes
Variable Size         788533224 bytes
Database Buffers      452984832 bytes
Redo Buffers            8892416 bytes
Database mounted.

• 开启归档

SQL> alter database archivelog;

Database altered.

• 打开数据库

SQL> alter database open;

Database altered.

• 验证是否开启归档

SQL> archive log list
Database log mode          Archive Mode
Automatic archival         Enabled
Archive destination        USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     65
Next log sequence to archive   67
Current log sequence           67

• 模拟误删除数据文件test.dbf

[oracle@vastdata1 ~]$ rm -f /u01/app/oracle/oradata/PROD/test.dbf

• 再次向t1表插入数据失败

SQL> insert into t1 select * from dba_objects;
insert into t1 select * from dba_objects
            *
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/test.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

• 此时数据库没有关闭，使用句柄恢复即可

[oracle@vastdata1 ~]$ ps -ef |grep -v grep |grep dbw0
oracle    37300      1  0 17:45 ?        00:00:00 ora_dbw0_PROD
[oracle@vastdata1 ~]$ cd /proc/37300/fd
[oracle@vastdata1 fd]$ ll
total 0
lr-x------ 1 oracle oinstall 64 May 26 17:53 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 May 26 17:53 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 May 26 17:53 10 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkPROD
lr-x------ 1 oracle oinstall 64 May 26 17:53 11 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 May 26 17:53 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 May 26 17:53 256 -> /u01/app/oracle/oradata/PROD/control01.ctl
lrwx------ 1 oracle oinstall 64 May 26 17:53 257 -> /u01/app/oracle/fast_recovery_area/PROD/control02.ctl
lrwx------ 1 oracle oinstall 64 May 26 17:53 258 -> /u01/app/oracle/oradata/PROD/system01.dbf
lrwx------ 1 oracle oinstall 64 May 26 17:53 259 -> /u01/app/oracle/oradata/PROD/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 May 26 17:53 260 -> /u01/app/oracle/oradata/PROD/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 May 26 17:53 261 -> /u01/app/oracle/oradata/PROD/users01.dbf
lrwx------ 1 oracle oinstall 64 May 26 17:53 262 -> /u01/app/oracle/oradata/PROD/test.dbf (deleted)
lrwx------ 1 oracle oinstall 64 May 26 17:53 263 -> /u01/app/oracle/oradata/PROD/temp01.dbf
lr-x------ 1 oracle oinstall 64 May 26 17:53 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 May 26 17:53 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 May 26 17:53 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 May 26 17:53 6 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 May 26 17:53 7 -> /proc/37300/fd
lr-x------ 1 oracle oinstall 64 May 26 17:53 8 -> /dev/zero
lrwx------ 1 oracle oinstall 64 May 26 17:53 9 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_PROD.dat

• 使用cp命令将删除的文件恢复到原来额位置

[oracle@vastdata1 fd]$ cp 262 /u01/app/oracle/oradata/PROD/test.dbf

• 数据库一直是open的，SCN不断的变化，将test.dbf文件offline

SQL> alter database datafile '/u01/app/oracle/oradata/PROD/test.dbf' offline;

Database altered.

• 使用cp恢复出来的数据文件和数据库当前的信息不一致，需要recover

SQL> recover datafile '/u01/app/oracle/oradata/PROD/test.dbf' 
Media recovery complete.

• 在recover完成后将test.dbf文件online

SQL> alter database datafile '/u01/app/oracle/oradata/PROD/test.dbf' online;

Database altered.

• 重启数据库

SQL> conn / as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size          2252824 bytes
Variable Size         788533224 bytes
Database Buffers      452984832 bytes
Redo Buffers            8892416 bytes
Database mounted.
Database opened.

• 测试

SQL> conn test/oracle
Connected.
SQL> select count(*) from t1;

  COUNT(*)
----------
     82347

注意：数据库是在归档模式下，数据库、操作系统没有重启，如果关闭数据库或操作系统这种方式是不适合的。

end~