环境说明:
操作系统: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~