重建控制文件时resetlogs与noresetlogs的使用情况

重建控制文件时resetlogs与noresetlogs的使用情况

控制文件中记录着数据库的数据文件,日志文件,备份数据等信息,更为重要的,控制文件中还记录了数据库的检查点

和scn信息,这些信息在数据恢复的过程中将起到关键性作用.

一个正常运行的数据库,通常控制文件都存在多份镜像,这些镜像的内容是完全相同的,oracle缺省就创建多份控制

文件更说明了控制文件的重要:

SQL> select name from v$controlfile;

NAME

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

/u01/app/oracle/product/11.2.0/oradata/jingyong/control01.ctl

/u01/app/oracle/product/11.2.0/oradata/jingyong/control02.ctl

可以通过如下一条命令将控制文件的创建语句备份到跟踪文件中:

SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_18818.trc

SQL> host sz /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_18818.trc

rz

Starting zmodem transfer.  Press Ctrl+C to cancel.

  100%       8 KB    8 KB/s 00:00:01       0 Errors

此跟踪文件中会记录控制文件的创建脚本,脚本包含两个主要的段落,其中一段如下所示:

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "JINGYONG" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',

  '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',

  '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',

  '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',

  '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',

  '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'

CHARACTER SET ZHS16GBK

;

当数据库处于nomount状态下时,可以通过运行这段脚本创建控制文件,控制文件会自动创建到参数文件中

记录控制文件的位置(原来的控制文件在创建过程会被覆盖).这里需要理解的一个主要选项是:

noresetlogs/resetlogs.在跟踪文件中包含如下注释,详细解释了这两个选项的含义:

-- Below are two sets of SQL statements, each of which creates a new

-- control file and uses it to open the database. The first set opens

-- the database with the NORESETLOGS option and should be used only if

-- the current versions of all online logs are available. The second

-- set opens the database with the RESETLOGS option and should be used

-- if online logs are unavailable.

-- The appropriate set of statements can be copied from the trace into

-- a script. file, edited as necessary, and executed when there is a

-- need to re-create the control file.

当数据库当前的redo log都可用时,可以通过noresetlogs参数重建控制文件,此时oracle能够从日志文件中

读取redo信息,记录到控制文件中,由于redo中记录的信息足以重演所有提交成功的事务,所以最终能够实现

完全恢复,成功打开数据库,这时的数据库就如同进行了一次断电之后的实例恢复,数据没有损失,重做日志

可以继续向前写入:

下面测试来看一下以noresetlogs重建控制文件进行数据库恢复的过程

先在数据库正常运行状态下对控制文件执行一次转储:

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19350.trc

这个转储文件中将包含数据库的检查点,redo thread信息,数据文件等信息,看一下

log file records内容:

***************************************************************************

LOG FILE RECORDS

***************************************************************************

 (size = 72, compat size = 72, section max = 16, section in-use = 3,

  last-recid= 3, old-recno = 0, last-recno = 0)

 (extent = 1, blkno = 10, numrecs = 16)

LOG FILE #1:

  name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log

 Thread 1 redo log links: forward: 2 backward: 0

 siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x1 dup: 1

 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ea466

 Low scn: 0x0000.000ea474 05/02/2013 11:40:58

 Next scn: 0x0000.000ea4db 05/02/2013 11:44:07

LOG FILE #2:

  name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log

 Thread 1 redo log links: forward: 3 backward: 1

 siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1

 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ea474

 Low scn: 0x0000.000ea4db 05/02/2013 11:44:07

 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

LOG FILE #3:

  name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log

 Thread 1 redo log links: forward: 0 backward: 2

 siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x1 dup: 1

 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000e8ed8

 Low scn: 0x0000.000ea466 05/02/2013 11:40:52

 Next scn: 0x0000.000ea474 05/02/2013 11:40:58

从记录信息中我们可以看到redo02.log文件的next scn:0xffff.ffffffff,所以redo02.log文件是当前的

日志文件,我们可以从v$log视图中查看当前的重做日志组

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

    GROUP# STATUS

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

         1 INACTIVE

         2 CURRENT

         3 INACTIVE

接下来通过shutdown abort模拟一次数据库故障:

SQL> shutdown abort;

ORACLE instance shut down.

启动数据库到nomount状态,再来使用noresetlogs参数来重建控制文件:

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  238530560 bytes

Fixed Size                  1335724 bytes

Variable Size             150998612 bytes

Database Buffers           83886080 bytes

Redo Buffers                2310144 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "JINGYONG" NORESETLOGS  ARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log'  SIZE 50M BLOCKSIZE 512,

  9    GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log'  SIZE 50M BLOCKSIZE 512,

 10    GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log'  SIZE 50M BLOCKSIZE 512

 11  -- STANDBY LOGFILE

 12  DATAFILE

 13    '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',

 14    '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',

 15    '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',

 16    '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',

 17    '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',

 18    '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'

 19  CHARACTER SET ZHS16GBK

 20  ;

Control file created.

此时再来对控制文件进行一次转储,检查log file records部分:

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19438.trc

***************************************************************************

LOG FILE RECORDS

***************************************************************************

 (size = 72, compat size = 72, section max = 16, section in-use = 3,

  last-recid= 0, old-recno = 0, last-recno = 0)

 (extent = 1, blkno = 10, numrecs = 16)

LOG FILE #1:

  name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log

 Thread 1 redo log links: forward: 2 backward: 0

 siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x0 dup: 1

 Archive links: fwrd: 2 back: 3 Prev scn: 0x0000.000ea466

 Low scn: 0x0000.000ea474 05/02/2013 11:40:58

 Next scn: 0x0000.000ea4db 05/02/2013 11:44:07

LOG FILE #2:

  name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log

 Thread 1 redo log links: forward: 3 backward: 1

 siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0xa dup: 1

 Archive links: fwrd: 0 back: 1 Prev scn: 0x0000.000ea474

 Low scn: 0x0000.000ea4db 05/02/2013 11:44:07

 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

LOG FILE #3:

  name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log

 Thread 1 redo log links: forward: 0 backward: 2

 siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x0 dup: 1

 Archive links: fwrd: 1 back: 0 Prev scn: 0x0000.00000000

 Low scn: 0x0000.000ea466 05/02/2013 11:40:52

 Next scn: 0x0000.000ea474 05/02/2013 11:40:58

从上面的记录我们可以看到重建的控文件能够从当前的日志文件获得正确的SCN及时间点等信息.同样地,控制

文件也能够从数据文件中获得详细的检查点信息:

***************************************************************************

DATA FILE RECORDS

***************************************************************************

 (size = 520, compat size = 520, section max = 100, section in-use = 6,

  last-recid= 0, old-recno = 0, last-recno = 0)

 (extent = 1, blkno = 11, numrecs = 100)

DATA FILE #1:

  name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf

creation size=0 block size=8192 status=0x12 head=9 tail=9 dup=1

 tablespace 0, index=1 krfil=1 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:119 scn: 0x0000.000ea4db 05/02/2013 11:44:07

 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28

 Creation Checkpointed at scn:  0x0000.00000007 08/13/2009 23:00:53

 thread:0 rba:(0x0.0.0)

 .....

 DATA FILE #2:

  name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf

creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1

 tablespace 1, index=2 krfil=2 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:119 scn: 0x0000.000ea4db 05/02/2013 11:44:07

 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28

 Creation Checkpointed at scn:  0x0000.00000874 08/13/2009 23:00:57

 thread:0 rba:(0x0.0.0)

 .....

 DATA FILE #3:

  name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf

creation size=0 block size=8192 status=0x12 head=7 tail=7 dup=1

 tablespace 2, index=3 krfil=3 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:47 scn: 0x0000.000ea4db 05/02/2013 11:44:07

 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28

 Creation Checkpointed at scn:  0x0000.000b7982 08/13/2009 23:56:54

 thread:0 rba:(0x0.0.0)

 .....

 DATA FILE #4:

  name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf

creation size=0 block size=8192 status=0x12 head=6 tail=6 dup=1

 tablespace 4, index=4 krfil=4 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:118 scn: 0x0000.000ea4db 05/02/2013 11:44:07

 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28

 Creation Checkpointed at scn:  0x0000.00004743 08/13/2009 23:01:06

 thread:0 rba:(0x0.0.0)

 ....

 DATA FILE #5:

  name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf

creation size=0 block size=8192 status=0x12 head=5 tail=5 dup=1

 tablespace 6, index=5 krfil=5 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:43 scn: 0x0000.000ea4db 05/02/2013 11:44:07

 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28

 Creation Checkpointed at scn:  0x0000.000bf3fe 04/25/2013 14:05:52

 thread:0 rba:(0x0.0.0)

 ....

 DATA FILE #6:

  name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf

creation size=0 block size=8192 status=0x12 head=4 tail=4 dup=1

 tablespace 7, index=6 krfil=6 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:11 scn: 0x0000.000ea96d 05/02/2013 12:00:47

 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28

 Creation Checkpointed at scn:  0x0000.000e9b4f 05/02/2013 08:43:22

 thread:0 rba:(0x0.0.0)

 .....

从上面的信息可以知道由于数据库是异常关闭的,所以数据文件的Stop scn:为无穷大:

Stop scn: 0xffff.ffffffff,接下来对数据库执行恢复,当恢复完成后再对控制文件进行转储:

SQL> recover database;

Media recovery complete.

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19450.trc

来观察此跟踪文件中的数据文件信息:

***************************************************************************

DATA FILE RECORDS

***************************************************************************

 (size = 520, compat size = 520, section max = 100, section in-use = 6,

  last-recid= 0, old-recno = 0, last-recno = 0)

 (extent = 1, blkno = 11, numrecs = 100)

DATA FILE #1:

  name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf

creation size=0 block size=8192 status=0x2 head=9 tail=9 dup=1

 tablespace 0, index=1 krfil=1 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16

 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16

 Creation Checkpointed at scn:  0x0000.00000007 08/13/2009 23:00:53

 thread:0 rba:(0x0.0.0)

 ....

DATA FILE #2:

  name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf

creation size=0 block size=8192 status=0x2 head=8 tail=8 dup=1

 tablespace 1, index=2 krfil=2 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16

 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16

 Creation Checkpointed at scn:  0x0000.00000874 08/13/2009 23:00:57

 thread:0 rba:(0x0.0.0)

....

DATA FILE #3:

  name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf

creation size=0 block size=8192 status=0x2 head=7 tail=7 dup=1

 tablespace 2, index=3 krfil=3 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:48 scn: 0x0000.000efd7d 05/02/2013 12:43:16

 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16

 Creation Checkpointed at scn:  0x0000.000b7982 08/13/2009 23:56:54

 thread:0 rba:(0x0.0.0)

 ....

DATA FILE #4:

  name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf

creation size=0 block size=8192 status=0x2 head=6 tail=6 dup=1

 tablespace 4, index=4 krfil=4 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:119 scn: 0x0000.000efd7d 05/02/2013 12:43:16

 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16

 Creation Checkpointed at scn:  0x0000.00004743 08/13/2009 23:01:06

 thread:0 rba:(0x0.0.0)

 ....

DATA FILE #5:

  name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf

creation size=0 block size=8192 status=0x2 head=5 tail=5 dup=1

 tablespace 6, index=5 krfil=5 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:44 scn: 0x0000.000efd7d 05/02/2013 12:43:16

 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16

 Creation Checkpointed at scn:  0x0000.000bf3fe 04/25/2013 14:05:52

 thread:0 rba:(0x0.0.0)

 ....

DATA FILE #6:

  name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf

creation size=0 block size=8192 status=0x2 head=4 tail=4 dup=1

 tablespace 7, index=6 krfil=6 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:12 scn: 0x0000.000efd7d 05/02/2013 12:43:16

 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16

 Creation Checkpointed at scn:  0x0000.000e9b4f 05/02/2013 08:43:22

 thread:0 rba:(0x0.0.0)

 ....

经过恢复之后,数据文件达到了一致状态,checkpoint scn(0x0000.000efd7d)和Stop scn(0x0000.000efd7d)

达到了一致,此时数据库就完成了恢复,数据库可以顺利启动:

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf'

  2       SIZE 30M  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 40M;

Tablespace altered.

现在我们来实验使用resetlogs方式来重建控制文件:

模拟数据库故障

SQL> shutdown abort;

ORACLE instance shut down.

以resetlogs来重建控制文件

SQL> startup nomount

ORACLE instance started.

Total System Global Area  238530560 bytes

Fixed Size                  1335724 bytes

Variable Size             150998612 bytes

Database Buffers           83886080 bytes

Redo Buffers                2310144 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "JINGYONG" RESETLOGS  ARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log'  SIZE 50M BLOCKSIZE 512,

  9    GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log'  SIZE 50M BLOCKSIZE 512,

 10    GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log'  SIZE 50M BLOCKSIZE 512

 11  -- STANDBY LOGFILE

 12  DATAFILE

 13    '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',

 14    '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',

 15    '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',

 16    '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',

 17    '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',

 18    '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'

 19  CHARACTER SET ZHS16GBK

 20  ;

Control file created.

此时对控制文件进行一次转储

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19598.trc

观察转储的跟踪文件中的log file record的信息:

***************************************************************************

LOG FILE RECORDS

***************************************************************************

 (size = 72, compat size = 72, section max = 16, section in-use = 3,

  last-recid= 0, old-recno = 0, last-recno = 0)

 (extent = 1, blkno = 10, numrecs = 16)

LOG FILE #1:

  name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log

 Thread 1 redo log links: forward: 2 backward: 0

 siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1

 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000

 Low scn: 0x0000.00000000 01/01/1988 00:00:00

 Next scn: 0x0000.00000000 01/01/1988 00:00:00

LOG FILE #2:

  name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log

 Thread 1 redo log links: forward: 3 backward: 1

 siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1

 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000

 Low scn: 0x0000.00000000 01/01/1988 00:00:00

 Next scn: 0x0000.00000000 01/01/1988 00:00:00

LOG FILE #3:

  name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log

 Thread 1 redo log links: forward: 0 backward: 2

 siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x2 flg: 0xb dup: 1

 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000

 Low scn: 0x0000.00000000 01/01/1988 00:00:00

 Next scn: 0x0000.00000000 01/01/1988 00:00:00

从上面的信息可以看到此时控制文件中的日志信息都是空的,oracle认为resetlogs方式下,当前的日志文件

已经损坏,那么就意味着oracle可能会丢失提交成功的数据,恢复将是一次不完全的介质恢复.

此时的数据文件信息如下:

***************************************************************************

DATA FILE RECORDS

***************************************************************************

 (size = 520, compat size = 520, section max = 100, section in-use = 6,

  last-recid= 0, old-recno = 0, last-recno = 0)

 (extent = 1, blkno = 11, numrecs = 100)

DATA FILE #1:

  name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf

creation size=0 block size=8192 status=0x12 head=9 tail=9 dup=1

 tablespace 0, index=1 krfil=1 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11

 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12

 Creation Checkpointed at scn:  0x0000.00000007 08/13/2009 23:00:53

 thread:0 rba:(0x0.0.0)

 ....

DATA FILE #2:

  name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf

creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1

 tablespace 1, index=2 krfil=2 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11

 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12

 Creation Checkpointed at scn:  0x0000.00000874 08/13/2009 23:00:57

 thread:0 rba:(0x0.0.0)

 ....

DATA FILE #3:

  name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf

creation size=0 block size=8192 status=0x12 head=7 tail=7 dup=1

 tablespace 2, index=3 krfil=3 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:51 scn: 0x0000.000efd80 05/02/2013 12:53:11

 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12

 Creation Checkpointed at scn:  0x0000.000b7982 08/13/2009 23:56:54

 thread:0 rba:(0x0.0.0)

 ....

DATA FILE #4:

  name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf

creation size=0 block size=8192 status=0x12 head=6 tail=6 dup=1

 tablespace 4, index=4 krfil=4 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:122 scn: 0x0000.000efd80 05/02/2013 12:53:11

 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12

 Creation Checkpointed at scn:  0x0000.00004743 08/13/2009 23:01:06

 thread:0 rba:(0x0.0.0)

 ....

DATA FILE #5:

  name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf

creation size=0 block size=8192 status=0x12 head=5 tail=5 dup=1

 tablespace 6, index=5 krfil=5 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:47 scn: 0x0000.000efd80 05/02/2013 12:53:11

 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12

 Creation Checkpointed at scn:  0x0000.000bf3fe 04/25/2013 14:05:52

 thread:0 rba:(0x0.0.0)

 ....

DATA FILE #6:

  name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf

creation size=0 block size=8192 status=0x12 head=4 tail=4 dup=1

 tablespace 7, index=6 krfil=6 prev_file=0

 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

 Checkpoint cnt:15 scn: 0x0000.000efd80 05/02/2013 12:53:11

 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12

 Creation Checkpointed at scn:  0x0000.000e9b4f 05/02/2013 08:43:22

 thread:0 rba:(0x0.0.0)

 ....

 从上面的信息可以知道由于数据库是异常关闭的,所以数据文件的Stop scn:为无穷大:

Stop scn: 0xffff.ffffffff

不完全恢复最终要求数据库通过resetlogs方式打开,resetlogs将会强制清空或重建联机重做日志文件.

此时执行恢复必须使用backup controlfile选项,否则将会报错:

SQL> recover database;

ORA-00283: recovery session canceled due to errors

ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;

ORA-00279: change 982400 generated at 05/02/2013 12:53:11 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/11.2.0/flash_recovery_area/JINGYONG/archivelog/2013_05_0

2/o1_mf_1_18_%u_.arc

ORA-00280: change 982400 for thread 1 is in sequence #18

Specify log: {=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

如果在线日志操坏,那么恢复到最后,oracle提示的最后一个归档日志将是不存在的(如果在线日志没有

损坏,则可以指定在线日志文件执行恢复),此时可以输入cancel取消恢复,然后可以强制打开数据库:

alter database open resetlogs;

SQL> recover database using backup controlfile;

ORA-00279: change 1003572 generated at 05/02/2013 13:20:06 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/11.2.0/flash_recovery_area/JINGYONG/archivelog/2013_05_0

2/o1_mf_1_19_%u_.arc

ORA-00280: change 1003572 for thread 1 is in sequence #19

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf'

  2       SIZE 30M  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 40M;

Tablespace altered.

时间： 2024-08-08 12:51:17

重建控制文件时resetlogs与noresetlogs的使用情况的相关文章

oracle重建控制文件丢失数据文件导致悲剧

数据库最初故障 Thu Sep 25 09:27:26 2014 MMON started with pid=15, OS id=1968 starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... starting up 1 shared server(s) ... ORACLE_BASE from environment = F:＼oracle Thu Sep 25 09

[20121105]重建控制文件少一个数据文件的情况.txt

[20121105]重建控制文件少一个数据文件的情况.txt 我的测试数据库经常做各种测试,resetlogs很多次,产生很多incarnation.今天想通过重建控制文件来清除这些信息. 我的控制文件的脚本是以前建立的,少包括一个数据文件.自己就拿这个做一个测试看看. 建立控制文件少包括'/u01/app/oracle11g/oradata/test/test01.dbf' STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "TEST"

重建控制文件，并且不干净的关闭数据库测试

重建控制文件,并且不干净的关闭数据库测试: 数据库SHUTDOWN ABORT,删除CONTROLFILE SQL> startup nomount;ORACLE instance started. Total System Global Area  419430400 bytesFixed Size                  2021248 bytesVariable Size             171968640 bytesDatabase Buffers          2

所有控制文件损坏的恢复--noresetlogs方式

       所有控制文件损坏,或者人为的删除了所有的控制文件,通过控制文件的复制已经不能解决问题,这个时候需要重新建立控制文件.同时注意,alter database backup control file to trace可以产生一个控制文件的文本备份. 1)先备份控制文件             SQL> alter database backup controlfile to 'f:\lib\control.ctl' reuse;数据库已更改.2)生成跟踪文件. SQL> alter

ORACLE控制文件的重建

oracle|控制 数据库系统运行一段时间后有很多参数需要调整,有些参数可在$ORACLE_HOME/dbs/initXXX.ora文件中调整,而有些参数必须要在ORACLE的控制文件中调整.如ORACLE的缺省数据文件个数为30个,在系统需要扩表空间而数据文件个数不够时就需要进行调整. 一种方法是将所有数据全倒出来,重建库,再将数据倒进出.这种方法较麻烦. 二种方法是只重建控制文件,因为数据文件个数的限制参数存放在控制文件中. 以下是一种较好的重建控制文件的方法: 1. 备份数据库中的数据,以

所有控制文件损坏的恢复--resetlogs方式

        此方式和 所有控制文件损坏的恢复--noresetlogs方式恢复时的前五个步骤是一样的. 1)先备份控制文件            SQL> alter database backup controlfile to 'f:\lib\control.ctl' reuse;数据库已更改.2)生成跟踪文件. SQL> alter database backup controlfile to trace;数据库已更改.SQL> @f:\sql\gettrace.sql---一个

Oracle 控制文件(CONTROLFILE)

--============================= -- Oracle 控制文件(CONTROLFILE) --=============================   一.Oracle 控制文件         为二进制文件,初始化大小由CREATE DATABASE指定,可以使用RMAN备份         记录了当前数据库的结构信息,同时也包含数据文件及日志文件的信息以及相关的状态,归档信息等等         在参数文件中描述其位置,个数等等.通常采用分散放开,多路复用

控制文件中MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS等参数的说明

CONTENTS -------- 1. What are the maximun number of datafiles ? MAXDATAFILES 2. What are the maximun number of redolog groups ? MAXLOGFILES 3. What are the maximun number of members for a redolog group ? MAXLOGMEMBERS 4. What are the maximun number o

如何创建RAC集群控制文件

在下面的一些情况下,可能需要重建控制文件: o 所有控制文件都已损坏或丢失o 没有针对控制文件的备份或者备份已损坏 下面是针对RAC环境下重建控制文件的具体过程, 包括两个例子.一个是以noresetlogs模式来重建控制文件,一个是以resetlogs模式来重建控制文件.如果redo logs都存在而且没有被损坏,那么可以采用noresetlogs. 使用resetlogs会将所有redo log清空而且重置log sequence为1. 在RAC上重建控制文件与单实例有一些小区别: 在重建控