浅析控制文件的创建
当我们用不同方式创建控制文件的时候，可能会对控制文件中的信息产生不同的影响，下面分别介绍以noresetlogs方式、resetlogs方式创建控制文件，以查看控制文件中
当我们用不同方式创建控制文件的时候，可能会对控制文件中的信息产生不同的影响，下面分别介绍以noresetlogs方式、resetlogs方式创建控制文件，以查看控制文件中信息的变化。
一：以noresetlogs方式创建控制文件
1 重建控制文件语句如下
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/oracle/app/db1/dbs/log1CRM.dbf',
'/oracle/CRM2/CRM/redo01b.log'
) SIZE 200M,
GROUP 2 (
'/oracle/app/db1/dbs/log2CRM.dbf',
'/oracle/CRM2/CRM/redo02b.log'
) SIZE 50M,
GROUP 3 (
'/oracle/CRM2/CRM/redo03.log',
'/oracle/CRM2/CRM/redo03b.log'
) SIZE 200M,
GROUP 4 (
'/oracle/CRM2/CRM/redo04.log',
'/oracle/CRM2/CRM/redo04b.log'
) SIZE 200M,
GROUP 5 (
'/oracle/CRM2/CRM/redo05.log',
'/oracle/CRM2/CRM/redo05b.log'
) SIZE 200M,
GROUP 6 (
'/oracle/CRM2/CRM/redo06.log',
'/oracle/CRM2/CRM/redo06b.log'
) SIZE 200M
DATAFILE
'/oracle/test/system1.dbf',
'/oracle/test/zxb.dbf',
'/oracle/test/sysaux01.dbf',
'/oracle/test/users01.dbf',
'/oracle/test/zxa.dbf',
'/oracle/test/test1.dbf',
'/oracle/test/zxc.dbf',
'/oracle/test/undotbs1.dbf',
'/oracle/test/zxbig.dbf'
CHARACTER SET ZHS16GBK
;
2转储 数据文件头部信息如下：
V10 STYLE FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3601019238=0xd6a33166, Db
Activation ID=0=0x0
Control Seq=9739=0x260b, File size=640=0x280
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS rel_fn:4
Creation at scn: 0x0000.000027b9 10/22/2005 21:45:00
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x2fac7053 scn: 0x0000.802c8c23 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2fac6f51 scn: 0x0000.802c3dfd prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 12/05/2012 05:28:04
status:0x0 root dba:0x00000000 chkpt cnt: 1183 ctl cnt:1182
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.8031da6a（2150750826） 11/22/2012 19:25:50
thread:1 rba:(0x1e.c8f.10)
3 转储新控制文件信息如下：
----------------------------------------------------------------------------------------------------
DATABASE ENTRY
----------------------------------------------------------------------------------------------------
(size = 316, compat size = 316, section max = 1, section in-use = 1,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 1, numrecs = 1)
12/05/2012 05:47:51
DB Name "CRM"
Database flags = 0x00400103 0x00001000
Controlfile Creation Timestamp 12/05/2012 05:47:52
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.802c8c23 Resetlogs Timestamp 11/20/2012 07:01:39
Prior resetlogs scn: 0x0000.802c3dfd Prior resetlogs Timestamp 11/20/2012 06:57:21
Redo Version: compatible=0xa200100
#Data files = 9, #Online files = 9
Database checkpoint: Thread=1 scn: 0x0000.a5aaadfd
Threads: #Enabled=1, #Open=0, Head=0, Tail=0
--------------------------------------------------------------------------------
CHECKPOINT PROGRESS RECORDS
-------------------------------------------------------------------------------
(size = 8180, compat size = 8180, section max = 11, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 2, numrecs = 11)
THREAD #1 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00
heartbeat: 801186341 mount id: 3609678535
-------------------------------------------------------------------------------------
DATA FILE #4:
(name #18) /oracle/test/users01.dbf
creation size=0 block size=8192 status=0x12 head=18 tail=18 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:1183 scn: 0x0000.a5aaadfd 01/01/1988 00:00:00
Stop scn: 0x0000.a5aaadfd （2779426301）12/05/2012 05:47:52
Creation Checkpointed at scn: 0x0000.000027b9 10/22/2005 21:45:00
thread:0 rba:(0x0.0.0)
4 转储当前联机日志其最后一个记录如下
REDO RECORD - Thread:1 RBA: 0x000040.0000e490.0160 LEN: 0x0064 VLD: 0x02
SCN: 0x0000.a5aaadfb SUBSCN: 1 12/05/2012 04:52:27
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:23.1
Block Written - afn: 1 rdba: 0x0040edc8 BFT:(1024,4255176) non-BFT:(1,60872)
scn: 0x0000.a5aaadf9 seq: 0x07 flg:0x04
Block Written - afn: 1 rdba: 0x0040006a BFT:(1024,4194410) non-BFT:(1,106)
scn: 0x0000.a5aaadfa seq: 0x01 flg:0x06
Block Written - afn: 1 rdba: 0x00400009 BFT:(1024,4194313) non-BFT:(1,9)
scn: 0x0000.a5aaadfa seq: 0x01 flg:0x04
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 29255Kb in 41.53s => 0.69 Mb/sec
Total physical reads: 29255Kb
Longest record: 10Kb, moves: 0/92501 (0%)
Change moves: 41327/182885 (22%), moved: 10Mb
Longest LWN: 1537Kb, moves: 7/364 (1%), moved: 6Mb
Last redo scn: 0x0000.a5aaadfb (2779426299)
5总结：
Noresetlogs方式创建控制文件
数据文件头部情况
控制文件中记录数据文件信息
检查点计数值
chkpt cnt: 1183
检查点计数值
Checkpoint cnt:1183
检查点scn值
0x8031da6a
检查点scn值
scn: 0xa5aaadfd
Redo 块地址
rba:(0x1e.c8f.10)
Stop scn
Stop scn: 0xa5aaadfd
Database checkpoint: Thread=1 scn: 0x0000.a5aaadfd
Checkpoint cnt:1183 scn: 0x0000.a5aaadfd 01/01/1988 00:00:00
Stop scn: 0x0000.a5aaadfd （2779426301）12/05/2012 05:47:52
1 控制文件中记录数据文件检查点计数值信息来自于数据文件头部。
2 控制文件中记录数据文件检查点 Scn以及stop scn 值来自于当前日志文件。
3 数据库检查点scn值也来自于当前日志文件。
4 重建控制文件后数据文件头部的rba地址决定了应用归档的开始（0x1e转换为10进制为30）
6 介质恢复过程：（注意红色部分）
执行Recover database后按提示输入auto，香港服务器，网站空间，从踪过程可看到恢复的应用归档和联机日志的过程。
PARSING IN CURSOR #1 len=34 dep=0 uid=0 oct=35 lid=0 tim=1322909211242292 hv=1214106442 ad='72e77150'
ALTER DATABASE RECOVER database 真正后台恢复语句
END OF STMT
PARSE #1:c=1000,e=1470,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1322909211242288
Recovery target incarnation = 1, activation ID = 0
Influx buffer limit = 12870 (50% x 25740)
Successfully allocated 3 recovery slaves
Using 367 overflow buffers per recovery slave
Start recovery at thread 1 ckpt scn 2150750826 logseq 30 block 3215
*** 2012-12-05 06:10:32.542
Media Recovery add redo thread 1
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_30_799830099.dbf （从30号归档开始应用）
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_31_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_32_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_33_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_34_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_35_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_36_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_37_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_38_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_39_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_40_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_41_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_42_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_43_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_44_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_45_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_46_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_47_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_48_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_49_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_50_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_51_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_52_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_53_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_54_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_55_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_56_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /oracle/archive/1_57_799830099.dbf
=====================
ALTER DATABASE RECOVER CONTINUE DEFAULT
END OF STMT
Media Recovery Log /oracle/archive/1_58_799830099.dbf
*** 2012-12-05 06:12:59.378
Recovery of Online Redo Log: Thread 1 Group 5 Seq 59 Reading mem 0
*** 2012-12-05 06:12:59.906
Recovery of Online Redo Log: Thread 1 Group 6 Seq 60 Reading mem 0
*** 2012-12-05 06:13:00.147
Recovery of Online Redo Log: Thread 1 Group 1 Seq 61 Reading mem 0
*** 2012-12-05 06:13:00.330
Recovery of Online Redo Log: Thread 1 Group 2 Seq 62 Reading mem 0
*** 2012-12-05 06:13:00.538
Recovery of Online Redo Log: Thread 1 Group 3 Seq 63 Reading mem 0
*** 2012-12-05 06:13:03.523
Recovery of Online Redo Log: Thread 1 Group 4 Seq 64 Reading mem 0
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 110780Kb in 152.42s => 0.71 Mb/sec
Total physical reads: 110780Kb
Longest record: 23Kb, moves: 0/292927 (0%)
Change moves: 113261/562625 (20%), moved: 45Mb
Longest LWN: 2004Kb, moves: 23/4514 (0%), moved: 15Mb
Last redo scn: 0x0000.a5aaadfb (2779426299)
----------------------------------------------
*** 2012-12-05 06:13:04.986
Media Recovery drop redo thread 1
File 1 (stop scn 2779426300) completed recovery at checkpoint scn 2779426300
File 2 (stop scn 2779426300) completed recovery at checkpoint scn 2779426300
File 3 (stop scn 2779426300) completed recovery at checkpoint scn 2779426300
File 4 (stop scn 2779426300) completed recovery at checkpoint scn 2779426300
File 5 (stop scn 2779426300) completed recovery at checkpoint scn 2779426300
File 6 (stop scn 2779426300) completed recovery at checkpoint scn 2779426300
File 7 (stop scn 2779426300) completed recovery at checkpoint scn 2779426300
File 8 (stop scn 2779426300) completed recovery at checkpoint scn 2779426300
File 9 (stop scn 2779426300) completed recovery at checkpoint scn 2779426300
-------------------------------------介质恢复到此处完成-----------------------------------------
二 resetlogs 方式创建控制文件
1 resetlogs方式创建控制文件语句如下：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CRM" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/oracle/app/db1/dbs/log1CRM.dbf',
'/oracle/CRM2/CRM/redo01b.log'
) SIZE 200M,
GROUP 2 (
'/oracle/app/db1/dbs/log2CRM.dbf',
'/oracle/CRM2/CRM/redo02b.log'
) SIZE 50M,
GROUP 3 (
'/oracle/CRM2/CRM/redo03.log',
'/oracle/CRM2/CRM/redo03b.log'
) SIZE 200M,
GROUP 4 (
'/oracle/CRM2/CRM/redo04.log',
'/oracle/CRM2/CRM/redo04b.log'
) SIZE 200M,
GROUP 5 (
'/oracle/CRM2/CRM/redo05.log',
'/oracle/CRM2/CRM/redo05b.log'
) SIZE 200M,
GROUP 6 (
'/oracle/CRM2/CRM/redo06.log',
'/oracle/CRM2/CRM/redo06b.log'
) SIZE 200M
DATAFILE
'/oracle/test/system1.dbf',
'/oracle/test/zxb.dbf',
'/oracle/test/sysaux01.dbf',
'/oracle/test/users01.dbf',
'/oracle/test/zxa.dbf',
'/oracle/test/test1.dbf',
'/oracle/test/zxc.dbf',
'/oracle/test/undotbs1.dbf',
'/oracle/test/zxbig.dbf'
CHARACTER SET ZHS16GBK
;
2 转储数据文件头部信息如下：
V10 STYLE FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3601019238=0xd6a33166, Db
Activation ID=0=0x0
Control Seq=11044=0x2b24, File size=640=0x280
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS rel_fn:4
Creation at scn: 0x0000.000027b9 10/22/2005 21:45:00
Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0
reset logs count:0x2fc8ca2a scn: 0x0000.a5ac8ab7 reset logs terminal rcv data:0x0 scn: 0x0000.00000000
prev reset logs count:0x2fc60dc2 scn: 0x0000.a5abe48a prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
recovered at 12/12/2012 01:00:31
status:0x0 root dba:0x00000000 chkpt cnt: 1285 ctl cnt:1284
begin-hot-backup file size: 0
Checkpointed at scn: 0x0000.a5ad6cd4 12/11/2012 23:00:27
thread:1 rba:(0xc.a2df.10)
3 转储控制文件信息如下： ----------------------------------------------------------------------------------------------
DATABASE ENTRY
-----------------------------------------------------------------------------------------------
(size = 316, compat size = 316, section max = 1, section in-use = 1,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 1, numrecs = 1)
12/12/2012 01:20:14
DB Name "CRM"
Database flags = 0x00400147 0x00001000
Controlfile Creation Timestamp 12/12/2012 01:20:14
Incmplt recovery scn: 0x0000.a5ad6cd4
Resetlogs scn: 0x0000.a5ac8ab7 Resetlogs Timestamp 12/10/2012 19:08:26
Prior resetlogs scn: 0x0000.a5abe48a Prior resetlogs Timestamp 12/08/2012 17:20:02
Redo Version: compatible=0xa200100
#Data files = 9, #Online files = 9
Database checkpoint: Thread=0 scn: 0x0000.00000000
Threads: #Enabled=1, #Open=0, Head=0, Tail=0
---------------------------------------------------------------------------------------------------
CHECKPOINT PROGRESS RECORDS
---------------------------------------------------------------------------------------------------
(size = 8180, compat size = 8180, section max = 11, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 2, numrecs = 11)
THREAD #1 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00
heartbeat: 801780265 mount id: 3610283405
---------------------------------------------------------------------------------------------------
DATA FILE #4:
----------------------------------------------------------------------------------------------------
(name #18) /oracle/test/users01.dbf
creation size=0 block size=8192 status=0x12 head=18 tail=18 dup=1
tablespace 4, index=4 krfil=4 prev_file=0
unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
Checkpoint cnt:1285 scn: 0x0000.a5ad6cd4 12/11/2012 23:00:27
Stop scn: 0xffff.ffffffff 12/12/2012 01:20:15
Creation Checkpointed at scn: 0x0000.000027b9 10/22/2005 21:45:00
thread:0 rba:(0x0.0.0)