[数据库恢复]Oracle 数据文件被删除恢复测试

数据库版本：11.2.0.3

场景：

1.数据库开启归档；

2.创建数据文件之后的所有归档日志都在线；

3.数据文件或者表空间没有进行过备份，数据库也没有全库备份数据文件异常丢失；

 

测试步骤：

1：查看是否已开启归档模式：
 

14:25:59 SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled ---表示已开启归档

Archive destination /opt/oracle/oradata/ora/archive

Oldest online log sequence 7

Next log sequence to archive 9

Current log sequence

 

2：创建测试表空间：
 

14:30:29 SQL> create tablespace test_cg datafile '/opt/oracle/oradata/ora/test_a.dbf' size 50m;

Tablespace created.

Elapsed: 00:00:00.71
 

14:31:01 SQL> create table temp_r(account varchar2(10),amount number(2)) tablespace test_cg;

Table created.

Elapsed: 00:00:00.04
 

14:31:22 SQL> insert into temp_r(account,amount) values('er',10);

1 row created.

Elapsed: 00:00:00.01
 

14:31:46 SQL> insert into temp_r(account,amount) values('e',10);

1 row created.

Elapsed: 00:00:00.00
 

14:31:50 SQL> insert into temp_r(account,amount) values('r',10);

1 row created.

Elapsed: 00:00:00.00
 

14:31:52 SQL> insert into temp_r(account,amount) values('re',10);

1 row created.

Elapsed: 00:00:00.00
 

14:31:55 SQL> insert into temp_r(account,amount) values('rr',10);

1 row created.

Elapsed: 00:00:00.00

14:32:00 SQL> commit；

 

3：模拟丢失数据文件：
 

oracle@oracle /opt/oracle/oradata/ora$ ll -lth test*

-rw-r-----. 1 oracle oinstall 51M Mar 10 14:31 test_a.dbf
 

oracle@oracle /opt/oracle/oradata/ora$ rm -rf test*
 

oracle@oracle /opt/oracle/oradata/ora$ ll -lth test*
 

ls: cannot access test*: No such file or directory
 

4：关闭数据库：
 

14:32:41 SQL> alter tablespace test_cg read only;--设置数据文件为只读

Tablespace altered.
 

14:38:02 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.
 

14:39:08 SQL> exit

5：重新启动数据库：
 

14:39:26 SQL> startup

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size 2228904 bytes

Variable Size 1073745240 bytes

Database Buffers 570425344 bytes

Redo Buffers 7118848 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/opt/oracle/oradata/ora/test_a.dbf'—这里启动的时候发现丢失了这个数据文件

 

14:39:37 SQL> select open_mode from v$database;

OPEN_MODE

----------------------------------------

MOUNTED

 

增加回该数据文件：
 

14:41:14 SQL> alter database create datafile 3; 或者alter database create datafile '/opt/oracle/oradata/ora/test_a.dbf'

Database altered.

Elapsed: 00:00:00.37

 

6：介质恢复：
 

14:42:46 SQL> recover datafile 3;

Media recovery complete. --完成介质恢复

14:45:20 SQL> alter database open; --修改数据库到pen下

Database altered.

Elapsed: 00:00:02.40

 

7：查看数据是否丢失：
 

14:45:52 SQL> select count(1) from temp_r;

COUNT(1)

----------

5

14:52:54 SQL> insert into temp_r(account,amount) values('d',10);

insert into temp_r(account,amount) values('d',10)

*

ERROR at line 1:

ORA-00372: file 3 cannot be modified at this time

ORA-01110: data file 3: '/opt/oracle/oradata/ora/test_a.dbf'  --这里无法nsert是由于设置了ead only

 

14:52:59 SQL> alter tablespace test_cg read write;

Tablespace altered.

 

14:54:42 SQL> insert into temp_r(account,amount) values('d',10);

1 row created.

14:54:49 SQL> commit;

 

14:54:52 SQL> select count(1) from temp_r;

COUNT(1)

----------

6

Elapsed: 00:00:00.01