在归档模式下删除非系统文件的恢复

众所周知，我们的核心生产数据库通常都是在归档模式下执行的，更不用说还配置DG环境的了。开启归档，并保证全部归档不丢失，就能保证我们对数据库所做的不论什么改动不会丢失，归档日志可谓是恢复的根本，假设丢失归档，那么即使RMAN功能再强大，也无法对丢失的数据进行恢复。所以我们通常配置的RMAN策略就是全备+归档+控制文件自己主动备份。这里的归档不是指数据库创建以来生成的归档(那量也太大了)，而是当进行RMAN非一致性备份时新产生的那部分归档日志，用来保证数据库能够前推到一致性状态，这样才干顺利open数据库。下面的測试仅仅是想说明归档日志对恢复数据的重要性，并没实用到RMAN来进行恢复。

測试一：归档日志健全未丢失

--连接到Oracle，确保是执行在归档模式下

[oracle@ora10g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on 8 13:46:53 2014

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> archive log list

Database log mode Archive Mode --归档模式

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 172

Next log sequence to archive 174

Current log sequence 174

SQL> set lin 130 pages 130

SQL> col name for a45

SQL> select file#,name from v$datafile;

FILE# NAME

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

1 /u01/app/oracle/oradata/ora10g/system01.dbf

2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf

3 /u01/app/oracle/oradata/ora10g/sysaux01.dbf

4 /u01/app/oracle/oradata/ora10g/users01.dbf

5 /u01/app/oracle/oradata/ora10g/example01.dbf

--创建測试表空间、用户、表

SQL> create tablespace zlm_test datafile '/u01/app/oracle/oradata/ora10g/zlm01.dbf' size 50m;

Tablespace created.

SQL> create user zlm identified by zlm default tablespace zlm_test;

User created.

SQL> grant connect,resource to zlm; --赋权限

Grant succeeded.

SQL> select file#,name from v$datafile;

FILE# NAME

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

1 /u01/app/oracle/oradata/ora10g/system01.dbf

2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf

3 /u01/app/oracle/oradata/ora10g/sysaux01.dbf

4 /u01/app/oracle/oradata/ora10g/users01.dbf

5 /u01/app/oracle/oradata/ora10g/example01.dbf

6 /u01/app/oracle/oradata/ora10g/zlm01.dbf --新增了6号文件作为測试表存放的物理介质

6 rows selected.

SQL> create table zlm.test1 as select rownum as id,object_name from dba_objects where rownum<=5;

Table created.

SQL> col object_name for a15

SQL> select * from zlm.test1;

ID OBJECT_NAME

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

1 ICOL$

2 I_USER1

3 CON$

4 UNDO$

5 C_COBJ#

--查看当前online日志文件状态

SQL> select group#,status,archived from v$log;

GROUP# STATUS ARC

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

1 INACTIVE YES

2 INACTIVE YES

3 CURRENT NO --当前日志组为3，未归档

--归档当前日志（多次）

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

这里进行了3次归档当前日志文件的操作，目的是使online日志被刷新，强制其归档，写到归档日志中去，由于我们要測试的是归档，否则恢复文件时，会自己主动去online日志中查找，即便是非归档模式，仅仅要online日志还未被刷新，依然是能够恢复的

SQL> select group#,status,archived from v$log;

GROUP# STATUS ARC

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

1 INACTIVE YES

2 INACTIVE YES

3 CURRENT NO --尽管看起来和刚才上一步一致，但此时事实上已经把第3组online日志刷新掉了

--保险起见，再归档一次（可选）

SQL> alter system archive log current;

System altered.

SQL> select group#,status,archived from v$log;

GROUP# STATUS ARC

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

1 CURRENT NO

2 INACTIVE YES

3 ACTIVE YES --如今新一轮的第3组的日志也已经归档了

--一致性关闭数据库，在OS级别删除測试文件datafile 6

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

[oracle@ora10g ~]$ cd $ORACLE_BASE/oradata/ora10g

[oracle@ora10g ora10g]$ ll -lrth

total 1.7G

-rw-r----- 1 oracle oinstall 51M Sep 5 10:13 test02.dbf

-rw-r----- 1 oracle oinstall 301M Sep 5 10:13 test01.dbf

-rw-r----- 1 oracle oinstall 201M Sep 16 16:56 temp01.dbf

-rw-r----- 1 oracle oinstall 51M Sep 18 13:49 redo02.log

-rw-r----- 1 oracle oinstall 51M Sep 18 13:51 redo03.log

-rw-r----- 1 oracle oinstall 51M Sep 18 13:51 zlm01.dbf

-rw-r----- 1 oracle oinstall 31M Sep 18 13:51 users01.dbf

-rw-r----- 1 oracle oinstall 166M Sep 18 13:51 undotbs01.dbf

-rw-r----- 1 oracle oinstall 561M Sep 18 13:51 system01.dbf

-rw-r----- 1 oracle oinstall 271M Sep 18 13:51 sysaux01.dbf

-rw-r----- 1 oracle oinstall 51M Sep 18 13:51 redo01.log

-rw-r----- 1 oracle oinstall 101M Sep 18 13:51 example01.dbf

-rw-r----- 1 oracle oinstall 7.2M Sep 18 13:52 control03.ctl

-rw-r----- 1 oracle oinstall 7.2M Sep 18 13:52 control02.ctl

-rw-r----- 1 oracle oinstall 7.2M Sep 18 13:52 control01.ctl

[oracle@ora10g ora10g]$ rm -f zlm01.dbf

[oracle@ora10g ora10g]$ exit

exit

--重新启动数据库

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 88082000 bytes

Database Buffers 192937984 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'

能够看到，此时是无法open数据库的，由于数据库文件物理上已经不存在，而在控制文件里是有记录的，这里提示的是“cannot identify/lock data file 6”，而当假设不过物理上存在，数据文件头中的信息与控制文件里记录的数据文件头信息不一致时，会提示xxx文件须要恢复

--手动创建一个datafile 6

SQL> alter database create datafile 6;

Database altered.

注意，此时不过创建了一个不一致的datafile 6而已，也能够通过RMAN的restore datafile 6;命令来实现，作用是一样的

--恢复datafile 6

SQL> recover datafile 6;

ORA-00279: change 983806 generated at 09/18/2014 13:47:22 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_174_%u_.arc

ORA-00280: change 983806 for thread 1 is in sequence #174

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto --此处输入auto，让数据库自己主动匹配，去寻找须要的日志去恢复数据库

ORA-00279: change 983923 generated at 09/18/2014 13:49:44 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_175_%u_.arc

ORA-00280: change 983923 for thread 1 is in sequence #175

ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_174_b1nwmrpv_.arc' no longer needed

for this recovery

Log applied.

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> select * from zlm.test1;

ID OBJECT_NAME

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

1 ICOL$

2 I_USER1

3 CON$

4 UNDO$

5 C_COBJ#

虽然online日志没有了，但因为归档日志从头至尾都没有删除过，非常快地数据库完毕了介质恢复，顺利地open了，測试表数据也未丢失

測试二：更改表内容后的归档日志所有丢失

--删除測试表中第5条记录并提交

SQL> delete from zlm.test1 where id=5;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from zlm.test1;

ID OBJECT_NAME

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

1 ICOL$

2 I_USER1

3 CON$

4 UNDO$

SQL> select group#,status,archived from v$log;

GROUP# STATUS ARC

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

1 CURRENT NO

2 INACTIVE YES

3 INACTIVE YES

--相同的，切3次归档，把online日志刷到归档去

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> select group#,status,archived from v$log;

GROUP# STATUS ARC

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

1 CURRENT NO --此处online日志已经被刷新

2 ACTIVE YES

3 ACTIVE YES

--关闭数据库，在os级别删除datafile 6以及新增的归档日志文件

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

[oracle@ora10g ~]$ cd $ORACLE_BASE/oradata/ora10g

[oracle@ora10g ora10g]$ rm -f zlm01.dbf

[oracle@ora10g ora10g]$ cd $ORACLE_BASE/flash_recovery_area/ORA10G/archivelog/2014_09_18

[oracle@ora10g 2014_09_18]$ ll -lrth

total 9.5M

-rw-r----- 1 oracle oinstall 2.4M Sep 18 10:10 o1_mf_1_172_b1nhskdd_.arc

-rw-r----- 1 oracle oinstall 469K Sep 18 10:14 o1_mf_1_173_b1nj0wxp_.arc

-rw-r----- 1 oracle oinstall 6.1M Sep 18 13:49 o1_mf_1_174_b1nwmrpv_.arc

-rw-r----- 1 oracle oinstall 1.0K Sep 18 13:49 o1_mf_1_175_b1nwmzo4_.arc

-rw-r----- 1 oracle oinstall 2.5K Sep 18 13:49 o1_mf_1_176_b1nwn43r_.arc

-rw-r----- 1 oracle oinstall 37K Sep 18 13:51 o1_mf_1_177_b1nwpwxb_.arc

-rw-r----- 1 oracle oinstall 477K Sep 18 14:01 o1_mf_1_178_b1nx9ry9_.arc

-rw-r----- 1 oracle oinstall 1.0K Sep 18 14:01 o1_mf_1_179_b1nx9y1k_.arc

-rw-r----- 1 oracle oinstall 7.0K Sep 18 14:01 o1_mf_1_180_b1nxb6q1_.arc

这里14:01生成的3个归档日志，是我在删除測试表数据库后归档current online日志生成的

--为了方便恢复，移走这3个归档日志（未真正删除）

[oracle@ora10g 2014_09_18]$ mv *178* ../

[oracle@ora10g 2014_09_18]$ mv *179* ../

[oracle@ora10g 2014_09_18]$ mv *180* ../

[oracle@ora10g 2014_09_18]$ ll -lrth

total 9.0M

-rw-r----- 1 oracle oinstall 2.4M Sep 18 10:10 o1_mf_1_172_b1nhskdd_.arc

-rw-r----- 1 oracle oinstall 469K Sep 18 10:14 o1_mf_1_173_b1nj0wxp_.arc

-rw-r----- 1 oracle oinstall 6.1M Sep 18 13:49 o1_mf_1_174_b1nwmrpv_.arc

-rw-r----- 1 oracle oinstall 1.0K Sep 18 13:49 o1_mf_1_175_b1nwmzo4_.arc

-rw-r----- 1 oracle oinstall 2.5K Sep 18 13:49 o1_mf_1_176_b1nwn43r_.arc

-rw-r----- 1 oracle oinstall 37K Sep 18 13:51 o1_mf_1_177_b1nwpwxb_.arc

[oracle@ora10g 2014_09_18]$ exit

exit

--启动数据库

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 88082000 bytes

Database Buffers 192937984 bytes

Redo Buffers 2973696 bytes

Database mounted.

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

ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'

--再次创建数据文件datafile 6

SQL> alter database create datafile 6;

Database altered.

--对数据文件datafile 6进行介质恢复

SQL> recover datafile 6;

ORA-00279: change 983806 generated at 09/18/2014 13:47:22 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_174_%u_.arc

ORA-00280: change 983806 for thread 1 is in sequence #174

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 983923 generated at 09/18/2014 13:49:44 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_175_%u_.arc

ORA-00280: change 983923 for thread 1 is in sequence #175

ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_174_b1nwmrpv_.arc' no longer needed

for this recovery

ORA-00279: change 983927 generated at 09/18/2014 13:49:51 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_176_%u_.arc

ORA-00280: change 983927 for thread 1 is in sequence #176

ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_175_b1nwmzo4_.arc' no longer needed

for this recovery

ORA-00279: change 983931 generated at 09/18/2014 13:49:56 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_177_%u_.arc

ORA-00280: change 983931 for thread 1 is in sequence #177

ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_176_b1nwn43r_.arc' no longer needed

for this recovery

ORA-00279: change 983974 generated at 09/18/2014 13:51:24 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_178_%u_.arc

ORA-00280: change 983974 for thread 1 is in sequence #178

ORA-00278: log file '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_177_b1nwpwxb_.arc' no longer needed

for this recovery

ORA-00308: cannot open archived log '/u01/app/oracle/flash_recovery_area/ORA10G/archivelog/2014_09_18/o1_mf_1_178_b1nx9ry9_.arc'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

当运行auto后，第一个建议的归档位置是174，然后到175、176、177，都没有问题，一直到178，提示文件无法找到，因为178、179、180这3个归档日志被移走了，模拟被删除的情况，数据库无法自己主动获取到这3个归档日志，也就无法把datafile 6前推到数据库正常关闭前的一致性状态，这个时候想要恢复，就仅仅能通过BBED工具来改动数据文件头信息来实现了，数据库自身以无法完毕这个任务，假设这个数据文件对整个数据库而言并非很重要，那么能够先offline该文件，然后一致性打开数据库，当然，这个数据文件里的数据也就丢失了

--使datafile 6 offline

SQL> alter database datafile 6 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> select file#,name,status from v$datafile;

FILE# NAME STATUS

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

1 /u01/app/oracle/oradata/ora10g/system01.dbf SYSTEM

2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf ONLINE

3 /u01/app/oracle/oradata/ora10g/sysaux01.dbf ONLINE

4 /u01/app/oracle/oradata/ora10g/users01.dbf ONLINE

5 /u01/app/oracle/oradata/ora10g/example01.dbf ONLINE

6 /u01/app/oracle/oradata/ora10g/zlm01.dbf OFFLINE

6 rows selected.

SQL> select * from zlm.test1;

select * from zlm.test1

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: '/u01/app/oracle/oradata/ora10g/zlm01.dbf'

尽管打开了数据库，但測试数据表还是丢失了，丢失了归档，又没有备份过归档，那么丢数据库是在所难免得了，重新证明了归档对数据恢复的重要性，因为刚才并未真正地删除归档，仅仅是使了一个trick，那么就当我们之前对归档做了个手动备份，如今来恢复丢失的归档（mv回原归档路径）

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !

cd[oracle@ora10g ~]$ cd $ORACLE_BASE/flash_recovery_area/ORA10G/archivelog

[oracle@ora10g archivelog]$ ll -lrth

total 516K

drwxr-x--- 2 oracle oinstall 4.0K Sep 12 10:33 2014_09_12

drwxr-x--- 2 oracle oinstall 4.0K Sep 15 17:19 2014_09_15

drwxr-x--- 2 oracle oinstall 4.0K Sep 17 12:30 2014_09_16

drwxr-x--- 2 oracle oinstall 4.0K Sep 18 10:15 2014_09_17

-rw-r----- 1 oracle oinstall 477K Sep 18 14:01 o1_mf_1_178_b1nx9ry9_.arc

-rw-r----- 1 oracle oinstall 1.0K Sep 18 14:01 o1_mf_1_179_b1nx9y1k_.arc

-rw-r----- 1 oracle oinstall 7.0K Sep 18 14:01 o1_mf_1_180_b1nxb6q1_.arc

drwxr-x--- 2 oracle oinstall 4.0K Sep 18 14:25 2014_09_18

[oracle@ora10g archivelog]$ mv *.arc ./2014_09_18

[oracle@ora10g archivelog]$ cd 2014_09_18