dataifle误删除的模拟recover 之二

测试archived log不连续的情况下的恢复
（不能恢复到当前点)
热备份tablespace users.

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#    BYTES    MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
      1 1    11 52428800   1 YES INACTIVE        2339363 24-MAY-13
      2 1    13 52428800   1 NO CURRENT        2340471 24-MAY-13
      3 1    12 52428800   1 YES INACTIVE        2340041 24-MAY-13

SQL> insert into scott.tt select dbms_flashback.get_system_change_number from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.

.....

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#    BYTES    MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
      1 1    20 52428800   1 YES ACTIVE        2341862 24-MAY-13
      2 1    19 52428800   1 YES ACTIVE        2341853 24-MAY-13
      3 1    21 52428800   1 NO CURRENT        2341869 24-MAY-13


SQL> select sequence#,first_change#,next_change# from v$archived_log where deleted='NO';

SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
0        0     0
0        0     0
1   631403   2.8147E+14
7 2333319      2339240
8 2339240      2339279
9 2339279      2339337
10 2339337      2339363
11 2339363      2340041
12 2340041      2340471
13 2340471      2341801
14 2341801      2341806

SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ------------- ------------
15 2341806      2341819
16 2341819      2341824
17 2341824      2341840
18 2341840      2341853
19 2341853      2341862
20 2341862      2341869

17 rows selected.



SQL> select * from scott.tt;

       SEQ
----------
   2341789
   2341794
   2341801
   2341812
   2341819
   2341835
   2341846
   2341853
   2341862

9 rows selected.

-- delete sequence#=18的archived log file
   scott.tt seq=2341840打后的应该不能recovery了.


SQL> select sequence#,name from v$archived_log where sequence#=18;

SEQUENCE# NAME
---------- -----------------------------------------------------------------------------------------------------------------------------------
18 /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_18_8t0qm071_.arc



SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size     1218992 bytes
Variable Size    83887696 bytes
Database Buffers   197132288 bytes
Redo Buffers     2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/home/oracle/oracle/product/10.2.0/db_1/oradata/petervm/users01.dbf'


SQL> host cp /home/oracle/backup/users01.dbf /home/oracle/oracle/product/10.2.0/db_1/oradata/petervm

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/home/oracle/oracle/product/10.2.0/db_1/oradata/petervm/users01.dbf'


SQL> recover datafile 4;
ORA-00279: change 2340665 generated at 05/24/2013 23:34:39 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_13_%u_.arc
ORA-00280: change 2340665 for thread 1 is in sequence #13


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

ORA-00279: change 2341801 generated at 05/24/2013 23:45:26 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_14_%u_.arc
ORA-00280: change 2341801 for thread 1 is in sequence #14
ORA-00278: log file '/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_13_8t0qj6ly_.arc' no longer needed for this recovery


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

ORA-00279: change 2341806 generated at 05/24/2013 23:45:36 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_15_%u_.arc
ORA-00280: change 2341806 for thread 1 is in sequence #15
ORA-00278: log file '/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_14_8t0qjjxo_.arc' no longer needed for this recovery


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

ORA-00279: change 2341819 generated at 05/24/2013 23:45:50 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_16_%u_.arc
ORA-00280: change 2341819 for thread 1 is in sequence #16
ORA-00278: log file '/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_15_8t0qjy69_.arc' no longer needed for this recovery


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

ORA-00279: change 2341824 generated at 05/24/2013 23:46:00 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_17_%u_.arc
ORA-00280: change 2341824 for thread 1 is in sequence #17
ORA-00278: log file '/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_16_8t0qk90q_.arc' no longer needed for this recovery


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

ORA-00279: change 2341840 generated at 05/24/2013 23:46:41 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_18_%u_.arc
ORA-00280: change 2341840 for thread 1 is in sequence #18
ORA-00278: log file '/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_17_8t0qlkq1_.arc' no longer needed for this recovery


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

ORA-00308: cannot open archived log '/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_24/o1_mf_1_18_8t0qm071_.arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3


SQL> select file#,checkpoint_change# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
1       2342256
2       2342256
3       2342256
4       2341840
5       2342256
6       2342256

6 rows selected.

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

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
1       2342256
2       2342256
3       2342256
4       2342256
5       2342256
6       2342256

6 rows selected.

SQL> show parameter _allow

NAME      TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption      boolean TRUE

SQL> recover database until cancel;
ORA-00279: change 2341840 generated at 05/24/2013 23:46:41 needed for thread 1
ORA-00289: suggestion : /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/PETERVM/archivelog/2013_05_25/o1_mf_1_18_%u_.arc
ORA-00280: change 2341840 for thread 1 is in sequence #18


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/home/oracle/oracle/product/10.2.0/db_1/oradata/petervm/system01.dbf'


ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced

--原因不得而知，查alter.log,如下：
ARC2: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
ARC2 started with pid=18, OS id=5832
Sat May 25 00:12:33 2013
Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/petervm/udump/petervm_ora_5734.trc:
ORA-00600: internal error code, arguments: [2662], [0], [2341846], [0], [2342253], [4194313], [], []
Sat May 25 00:12:36 2013
Errors in file /home/oracle/oracle/product/10.2.0/db_1/admin/petervm/udump/petervm_ora_5734.trc:
ORA-00600: internal error code, arguments: [2662], [0], [2341846], [0], [2342253], [4194313], [], []
Sat May 25 00:12:36 2013
Error 600 happened during db open, shutting down database
USER: terminating instance due to error 600
Instance terminated by USER, pid = 5734

oracle>sqlplus / as sysdba

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@petervm 2013_05_24]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 25 00:20:53 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size     1218992 bytes
Variable Size    83887696 bytes
Database Buffers   197132288 bytes
Redo Buffers     2973696 bytes
Database mounted.
Database opened.

--验证
SQL> select * from scott.tt;

       SEQ
----------
   2341789
   2341794
   2341801
   2341812
   2341819
   2341835

6 rows selected.

-- resetlogs 会重置归档日志及联机日志的sequence
SQL> select sequence#,first_change#,next_change#,deleted from v$archived_log;

SEQUENCE# FIRST_CHANGE# NEXT_CHANGE# DEL
---------- ------------- ------------ ---
16 2341819      2341824 NO
17 2341824      2341840 NO
18 2341840      2341853 NO
19 2341853      2341862 NO
20 2341862      2341869 NO
21 2341869      2342257 NO
1 2341841      2361846 NO

SQL> select sequence#,first_change# from v$log;

SEQUENCE# FIRST_CHANGE#
---------- -------------
2 2361846
0        0
1 2341841