Oracle 10g数据库闪回之后怎么样noresetlogs打开数据库(一)

很多时候数据库resetlogs打开之后会引起诸多不便，比如在Oracle 10g下，闪回数据库之后必须要resetlogs打开，那怎么样才能避免数据库resetlogs打开呢？
以下步骤仅用于测试，在生产环境下慎用。
（1）将数据库启动在mount状态，打开强制模式的闪回点dd，记录控制文件的checkpoint和checkpoint count
SQL> startup mount
ORACLE instance started.

Total System Global Area 1069547520 bytes
Fixed Size                  2101704 bytes
Variable Size             276827704 bytes
Database Buffers          784334848 bytes
Redo Buffers                6283264 bytes
Database mounted.

SQL>  create restore point dd guarantee flashback database;

Restore point created.

SQL> select to_char(checkpoint_change#) from v$datafile;

TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
10999733115905
10999733115905
10999733115905
10999733115905


SQL> select file#,to_char(checkpoint_change#),CHECKPOINT_COUNT from v$datafile_header;

     FILE# TO_CHAR(CHECKPOINT_CHANGE#)              CHECKPOINT_COUNT
---------- ---------------------------------------- ----------------
         1 10999733115905                                         70
         2 10999733115905                                         70
         3 10999733115905                                         70
         4 10999733115905                                         69

（2）在mount状态下备份控制文件和日志文件
[ora10g@xe2 lank]$ cp redo01.log redo01.log.bak     
[ora10g@xe2 lank]$ cp redo02.log redo02.log.bak     
[ora10g@xe2 lank]$ cp redo03.log redo03.log.bak         
[ora10g@xe2 lank]$ cp control01.ctl control01.ctl.bak

（3）打开数据库之后切换几个归档
SQL> alter database open;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.


（4）将数据库闪回至闪回点dd，可以发现数据文件的checkpoint和控制文件checkpoint均已闪回至闪回点状态，但是checkpoint count没有，这需要用bbed手动修改
SQL> flashback database to restore point dd;

Flashback complete.

SQL> select to_char(checkpoint_change#) from v$datafile_header;

TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
10999733115905
10999733115905
10999733115905
10999733115905

SQL>  select file#,to_char(checkpoint_change#),CHECKPOINT_COUNT from v$datafile_header;

     FILE# TO_CHAR(CHECKPOINT_CHANGE#)              CHECKPOINT_COUNT
---------- ---------------------------------------- ----------------
         1 10999733115905                                         74
         2 10999733115905                                         74
         3 10999733115905                                         74
         4 10999733115905                                         73


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

     FILE# FUZ
---------- ---
         1 NO
         2 NO
         3 NO
         4 NO
        
SQL> select to_char(checkpoint_change#) from v$datafile;

TO_CHAR(CHECKPOINT_CHANGE#)
----------------------------------------
10999733115905
10999733115905
10999733115905
10999733115905

（5）将之前备份的控制文件和日志文件还原
[ora10g@xe2 lank]$ cp control01.ctl.bak control01.ctl
[ora10g@xe2 lank]$ cp control01.ctl.bak control02.ctl
[ora10g@xe2 lank]$ cp control01.ctl.bak control03.ctl
[ora10g@xe2 lank]$ cp redo01.log.bak redo01.log
[ora10g@xe2 lank]$ cp redo02.log.bak redo02.log
[ora10g@xe2 lank]$ cp redo03.log.bak redo03.log

（6）mount数据库时alert日志会提示闪回日志比控制文件状态新，这主要是因为控制文件来自之前备份的控制文件。
SQL> startup mount
ORACLE instance started.

Total System Global Area 1069547520 bytes
Fixed Size                  2101704 bytes
Variable Size             276827704 bytes
Database Buffers          784334848 bytes
Redo Buffers                6283264 bytes
Database mounted.

Thu Oct 13 10:59:38 CST 2011
Errors in file /app/admin/lank/bdump/lank_rvwr_9155.trc:
ORA-38739: Flashback log file is more recent than control file.
ORA-38701: Flashback database log 1 seq 1 thread 1: "/app/flash_recovery_area/LANK/flashback/o1_mf_79dnqb8m_.flb"

如果直接打开数据库会有如下提示：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
其解决办法是将闪回点删除
SQL> drop restore point dd;

Restore point dropped.

再次尝试将数据库打开，提示数据文件比控制文件新，但是之前看到控制文件和数据文件的checkpoint已经处于一致状态，且fuzzy为NO
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/lank/db/lank/system01.dbf'
ORA-01207: file is more recent than control file - old control file

（7）进一步比较控制文件和数据文件的区别，这里主要比较checkpoint count和control seq
dump控制文件
SQL>  alter session set events 'immediate trace name controlf level 1';

Session altered.


DUMP OF CONTROL FILES, Seq # 2440 = 0x988
V10 STYLE FILE HEADER:
        Compatibility Vsn = 169870592=0xa200500
        Db ID=1193074783=0x471ce05f, Db Name='LANK'
        Activation ID=0=0x0
        Control Seq=2440=0x988, File size=430=0x1ae
        File Number=0, Blksiz=16384, File Type=1 CONTROL
       
dump 数据文件
SQL> alter session set events 'immediate trace name file_hdrs level 10';
DATA FILE #1:
  (name #4) /lank/db/lank/system01.dbf
creation size=38400 block size=8192 status=0xe head=4 tail=4 dup=1
tablespace 0, index=1 krfil=1 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:70 scn: 0x0a01.132f5c01 10/13/2011 10:53:56
Stop scn: 0x0a01.132f5c01 10/13/2011 10:53:56
Creation Checkpointed at scn:  0x0000.00000007 09/20/2011 15:54:10


V10 STYLE FILE HEADER:
        Compatibility Vsn = 169870592=0xa200500
        Db ID=1193074783=0x471ce05f, Db Name='LANK'
        Activation ID=0=0x0
        Control Seq=2464=0x9a0, File size=51200=0xc800
        File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000007 09/20/2011 15:54:10
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x2d901f29 scn: 0x0a01.132f57a3 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2d70c21f scn: 0x0000.00000001 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 10/13/2011 10:57:46
status:0x2000 root dba:0x00400179 chkpt cnt: 74 ctl cnt:73
begin-hot-backup file size: 0
Checkpointed at scn:  0x0a01.132f5c01 10/13/2011 10:53:56

发现控制文件和数据文件头的checkpoint count和control seq有区别，也就意味着数据库做闪回时，并没有闪回checkpoint count和control seq。
于是按照以下原则更改数据文件头的值：
a、数据文件头的control seq小于控制文件的control seq
b、数据文件头的chkpt cnt等于控制文件的chkpt cnt
经过查找需要修改的值在数据文件头的位置为：
   ub4 kccfhcsq                             @40 ==>control seq
   ub4 kcvfhcpc                             @140 ==>chkpt cnt                                                                   
   ub4 kcvfhccc                             @148 ==>ctl cnt
找到位置之后将所有文件用bbed修改即可 
   BBED> dump offset 40
   BBED> modify 0x8009
   BBED> dump offset 140
   BBED> modify 0x46
   BBED> dump offset 148
   BBED> modify 0x45
   BBED> sum apply
  
修改完毕之后，数据库就可以正常打开了。鼓掌！！！