重建控制文件时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.
/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部分:
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19438.trc
last-recid= 0, old-recno = 0, last-recno = 0)
name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x0 dup: 1
archive links: fwrd: 2 back: 3 prev scn: 0x0000.000ea466
name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0xa dup: 1
archive links: fwrd: 0 back: 1 prev scn: 0x0000.000ea474
name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x0 dup: 1
archive links: fwrd: 1 back: 0 prev scn: 0x0000.00000000
从上面的记录我们可以看到重建的控文件能够从当前的日志文件获得正确的scn及时间点等信息.同样地,控制
文件也能够从数据文件中获得详细的检查点信息:
data file records
(size = 520, compat size = 520, section max = 100, section in-use = 6,
(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
creation checkpointed at scn: 0x0000.00000874 08/13/2009 23:00:57
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
checkpoint cnt:47 scn: 0x0000.000ea4db 05/02/2013 11:44:07
creation checkpointed at scn: 0x0000.000b7982 08/13/2009 23:56:54
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
checkpoint cnt:118 scn: 0x0000.000ea4db 05/02/2013 11:44:07
creation checkpointed at scn: 0x0000.00004743 08/13/2009 23:01:06
....
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
checkpoint cnt:43 scn: 0x0000.000ea4db 05/02/2013 11:44:07
creation checkpointed at scn: 0x0000.000bf3fe 04/25/2013 14:05:52
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
checkpoint cnt:11 scn: 0x0000.000ea96d 05/02/2013 12:00:47
creation checkpointed at scn: 0x0000.000e9b4f 05/02/2013 08:43:22
从上面的信息可以知道由于数据库是异常关闭的,所以数据文件的stop scn:为无穷大:
stop scn: 0xffff.ffffffff,接下来对数据库执行恢复,当恢复完成后再对控制文件进行转储:
sql> recover database;
media recovery complete.
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19450.trc
来观察此跟踪文件中的数据文件信息:
creation size=0 block size=8192 status=0x2 head=9 tail=9 dup=1
checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16
stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
data file #2:
creation size=0 block size=8192 status=0x2 head=8 tail=8 dup=1
....
data file #3:
creation size=0 block size=8192 status=0x2 head=7 tail=7 dup=1
checkpoint cnt:48 scn: 0x0000.000efd7d 05/02/2013 12:43:16
data file #4:
creation size=0 block size=8192 status=0x2 head=6 tail=6 dup=1
checkpoint cnt:119 scn: 0x0000.000efd7d 05/02/2013 12:43:16
data file #5:
creation size=0 block size=8192 status=0x2 head=5 tail=5 dup=1
checkpoint cnt:44 scn: 0x0000.000efd7d 05/02/2013 12:43:16
data file #6:
creation size=0 block size=8192 status=0x2 head=4 tail=4 dup=1
checkpoint cnt:12 scn: 0x0000.000efd7d 05/02/2013 12:43:16
经过恢复之后,数据文件达到了一致状态,checkpoint scn(0x0000.000efd7d)和stop scn(0x0000.000efd7d)
达到了一致,此时数据库就完成了恢复,数据库可以顺利启动:
sql> alter system archive log all;
system altered.
sql> alter database open;
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方式来重建控制文件:
模拟数据库故障
以resetlogs来重建控制文件
sql> startup nomount
sql> create controlfile reuse database "jingyong" resetlogs archivelog
此时对控制文件进行一次转储
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19598.trc
观察转储的跟踪文件中的log file record的信息:
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
siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x2 flg: 0xb dup: 1
从上面的信息可以看到此时控制文件中的日志信息都是空的,oracle认为resetlogs方式下,当前的日志文件
已经损坏,那么就意味着oracle可能会丢失提交成功的数据,恢复将是一次不完全的介质恢复.
此时的数据文件信息如下:
checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11
stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
checkpoint cnt:51 scn: 0x0000.000efd80 05/02/2013 12:53:11
checkpoint cnt:122 scn: 0x0000.000efd80 05/02/2013 12:53:11
checkpoint cnt:47 scn: 0x0000.000efd80 05/02/2013 12:53:11
checkpoint cnt:15 scn: 0x0000.000efd80 05/02/2013 12:53:11
从上面的信息可以知道由于数据库是异常关闭的,所以数据文件的stop scn:为无穷大:
stop scn: 0xffff.ffffffff
不完全恢复最终要求数据库通过resetlogs方式打开,resetlogs将会强制清空或重建联机重做日志文件.
此时执行恢复必须使用backup controlfile选项,否则将会报错:
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;
ora-00279: change 1003572 generated at 05/02/2013 13:20:06 needed for thread 1
2/o1_mf_1_19_%u_.arc
ora-00280: change 1003572 for thread 1 is in sequence #19
/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
log applied.
sql> alter database open resetlogs;
![【MySQL数据库】数据库索引事务1.索引2.事务[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)