天萃荷净
Oracle研究中心学习笔记:Oracle控制文件Controlfile深入学习,介绍controlfile中究竟包含哪些内容。
本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: About Controlfile Structure
controlfile就类似oracle的大脑,其重要程度就不言而喻了,在数据库恢复中,我们经常遇到关于controlfile的问题。
但是网上似乎没有关于controlfile的详细文章。今天花点时间来大概描述下,由于controlfile中的内容过多,所以对照起来是相当麻烦的,所以我这里仅仅是列出我认为相对比较重要的内容。
那么controlfile中究竟包含哪些内容? 你可以通过试图去查询或通过dump 去观察,我这里直接查试图,如下:
SQL> SELECT * FROM v$version WHERE rownum < 3;
BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
SQL> SELECT * FROM
V$CONTROLFILE_RECORD_SECTION;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE 316 1 1 0 0 0
CKPT PROGRESS 8180 11 0 0 0 0
REDO THREAD 256 8 1 0 0 0
REDO LOG 72 16 16 0 0 2
DATAFILE 428 100 8 0 0 6
FILENAME 524 2298 8 0 0 0
TABLESPACE 68 100 7 0 0 1
TEMPORARY FILENAME 56 100 0 0 0 0
RMAN CONFIGURATION 1108 50 0 0 0 0
LOG HISTORY 56 292 2 1 2 2
OFFLINE RANGE 200 163 0 0 0 0
ARCHIVED LOG 584 308 2 1 2 2
BACKUP SET 40 409 0 0 0 0
BACKUP PIECE 736 200 0 0 0 0
BACKUP DATAFILE 116 282 0 0 0 0
BACKUP REDOLOG 76 215 0 0 0 0
DATAFILE COPY 660 223 0 0 0 0
BACKUP CORRUPTION 44 371 0 0 0 0
COPY CORRUPTION 40 409 0 0 0 0
DELETED OBJECT 20 818 0 0 0 0
PROXY COPY 852 249 0 0 0 0
BACKUP SPFILE 36 454 0 0 0 0
DATABASE INCARNATION 56 292 2 1 2 2
FLASHBACK LOG 84 2048 0 0 0 0
RECOVERY DESTINATION 180 1 0 0 0 0
INSTANCE SPACE RESERVATION 28 1055 1 0 0 0
REMOVABLE RECOVERY FILES 32 1000 0 0 0 0
RMAN STATUS 116 141 0 0 0 0
THREAD INSTANCE NAME MAPPING 80 8 8 0 0 0
MTTR 100 8 1 0 0 0
DATAFILE HISTORY 568 57 0 0 0 0
STANDBY DATABASE MATRIX 400 10 10 0 0 0
GUARANTEED RESTORE POINT 212 2048 0 0 0 0
RESTORE POINT 212 2083 0 0 0 0
34 ROWS selected.
毫无疑问,前面的几项对我们来讲是最为重要的,下面我通过bbed来dump controlfile(当然你可以直接dd+od).
+++++controlfile header block
BBED> d /v count 100
File: /home/ora10g/oradata/roger/control01.ctl (1)
Block: 0 Offsets: 0 to 99 Dba:0x00400000
-------------------------------------------------------
00c20000 0000c0ff 00000000 00000000 l ............
a4fb0000 00400000 c2010000 7d7c7b7a l ...@....}|{z
a0810000 00000000 00000000 00000000 l ..............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 l ....
<16 bytes per line>
这里的7d7c7b7a 是表示magic number。不同平台是不一样的。
这里的controlfile header block,其实有点类似datafile header block,其block号为0. 其他信息不需要关注。
下面来看block 1的内容,类似你dump controlfile看到的STYLE FILE HEADER信息:
BBED> d /v count 365
File: /home/ora10g/oradata/roger/control01.ctl (1)
Block: 1 Offsets: 0 to 364 Dba:0x00400001
-------------------------------------------------------
15c20000 01000000 00000000 00000104 l ..............
6bb20000 00000000 0005200a 294d0a93 l k....... .)M..
524f4745 52000000 bc210000 c2010000 l ROGER.......
00400000 00000100 00000000 00000000 l .@..............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
09e69195 2cbafc2f 34a95c00 00000000 l ..,狐/4‐.....
90bafc2f 00000000 00000000 00000000 l .狐/............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 08000000 08000000 l ................
08000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00 l .............
<16 bytes per line>
offset 从0~361结束。 这部分内容是controlfile header 内容,如下进行详细描述:
offset 25~29,0005200a 表示Compatibility Vsn,即使版本号,转换后即为10.2.0.5.0.
offset 29~32,294d0a93 表示DB id,转换后为2466925865
offset 32~37,524f4745 52 表示db name。我这里是ROGER.
offset 41~42,bc21 表示Control Seq number,由于我这里是x86平台,字节序是反的,所以应该是21bc,转换后是8636.
offset 44~45,c201 表示file size,即使controlfile大小,转换后为450.
offset 49~50,0040 表示blocksize
offset 55 ,01 表示file type,01表示controlfile,关于该值的属性描述如下:
KCCTYPCF 1 /* Control File */
KCCTYPRL 2 /* Redo Log file */
KCCTYPDF 3 /* Vanilla Db File */
KCCTYPBC 4 /* Backup Controlfile */
KCCTYPBP 5 /* Backup Piece */
KCCTYPTF 6 /* Temporary db File */
我们先来看下DATABASE ENTRY信息(对应controlfile dump的database entry信息):
BBED> set file 1 block 17
FILE# 1
BLOCK# 17
BBED> d /v count 200
File: /home/ora10g/oradata/roger/control01.ctl (1)
Block: 17 Offsets: 0 to 199 Dba:0x00400011
-------------------------------------------------------
15c20000 11000000 bd210000 ffff0104 l ............
f9ad0000 00000000 00000000 2bbafc2f l ..........+狐/
524f4745 52000000 00000000 79014000 l ROGER.......y.@.
01404000 00000000 00000000 d4a75c00 l .@@.........鸳\.
00000000 8ebafc2f f1a65c00 00000000 l .....狐/瘭\.....
e8b8fc2f 00000000 0005200a 06000000 l 韪...... .....
06000000 01000000 70b95c00 00000000 l ........p筡.....
01000000 00000000 02000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 l ........
........
<16 bytes per line>
这里针对上面关键性的offset进行描述:
offset 29~32, 2bbafc2f 表示controlfile create timestamp.
offset 17~20, 524f4745 52 这部分内容描述DB NAME
offset 49~52, 01404000 表示database flags.
offset 61~64, d4a75c00 表示Resetlogs scn值.
offset 69~72, 8ebafc2f 表示Resetlogs Timestamp.
offset 73~76, f1a65c00 表示Prior resetlogs scn.
offset 81~84, e8b8fc2f 表示Prior resetlogs Timestamp.
offset 89~92, 0005200a 表示Redo Version compatible,转换后为10.2.0.5.0
offset 93~96, 06000000 表示datafile 数量.
offset 97~100, 06000000 表示datafile online的数量.
offset 101~104,01000000 表示thread number。为1表示只有1个线程,即单实例.
offset 105~108,70b95c00 表示database checkpoint scn.
offset 113~116,01000000 表示处于enable状态的thread编号.
后面还有一些其他内容,不描述,不是重点。 这部分内容里面我们关注的地方也就是resetlogs scn和time以及daOracleoracleplus.nettabase checkpoint scn.
下面继续dump,来看下controlfile中另外一个重要的地方:CHECKPOINT PROGRESS RECORDS
BBED> set file 1 block 3
FILE# 1
BLOCK# 3
BBED> d /v count 16384
File: /home/ora10g/oradata/roger/control01.ctl (1)
Block: 3 Offsets: 0 to 16383 Dba:0x00400003
-------------------------------------------------------
15c20000 03000000 00000000 00000104 l ..............
d5d80000 01000000 00000000 00000000 l 肇..............
ffffffff ffffffff ffff0000 01000000 l ................
392a0000 00000000 5bb95c00 00000000 l 9*......[筡.....
f0b4fd2f 8ebafc2f d4a75c00 00000000 l 鸫.狐/鸳\.....
9092fd2f 09e69195 00000000 00000000 l ............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 01000000 l ................
01000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
............
这是controlfile的第3个block,这部分信息是CHECKPOINT PROGRESS RECORDS。
offset 21, 01 表示thread status ,1表示线程关闭,2表示线程处于open状态.
offset 33~42,即0xffffffff.ffffffff.ffff 表示low cache rba
offset 43~52,即0000 01000000 392a0000 表示on disk rba转换后为0×1.2a39.0
offset 57~60,即5bb95c00 表示on disk scn 转换后为6076763
offset 73~76,即d4a75c00 表示resetlogs scn 转换后为6072276
offset 81~84,即9092fd2f 表示hearbeat值,ckpt进程每3s会递增该值,这个无实际意义
offset 85~88,即09e69195 表示mount id,转换后为2509366793,这个也无什么实际意义
我这里是单机环境,如果你是rac环境,那么你会看到thread 2的信息。
下面我们接着来看redo thread records:
dd if=/home/ora10g/oradata/roger/control01.ctl bs=16384 count=450 |od -xv > control1.txt
搜索control1.txt,字符串为2a56,定位到redo thread records信息在block 19中,如下:
BBED> set file 1 block 19
FILE# 1
BLOCK# 19
BBED> d /v count 500
File: /home/ora10g/oradata/roger/control01.ctl (1)
Block: 19 Offsets: 0 to 499 Dba:0x00400013
-------------------------------------------------------
15c20000 13000000 b9210000 ffff0104 l ............
7b890000 0e000000 70b95c00 00000000 l {.......p筡.....
f5b4fd2f 01000000 01000000 562a0000 l 醮........V*..
10000000 02000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 02000200 l ................
03000200 01000000 d4a75c00 00000000 l ........鸳\.....
8ebafc2f 00000000 00000000 00000000 l .狐/............
02000000 726f6765 72000000 00000000 l ....roger.......
00000000 8fbafc2f 00000000 00000000 l .....狐/........
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 l ....
<16 bytes per line>
这里仍然只描述关键的部分内容:
offset 21 ,0e 表示redo thread status。
offset 25~28,70b95c00 表示checkpoint scn。
offset 41~50,共10个offset,01000000 562a0000 1000 表示thread rba,转换后即为0×1.2a56.10
offset 201~204,共计4个offset,d4a75c00 表示thread enable scn.转换后为6072276
最后来看下data file records。
同理,定位到data file record记录在第23个block中,如下:
BBED> set file 1 block 23
FILE# 1
BLOCK# 23
BBED> d /v count 3000
File: /home/ora10g/oradata/roger/control01.ctl (1)
Block: 23 Offsets: 0 to 2999 Dba:0x00400017
-------------------------------------------------------
15c20000 17000000 b9210000 ffff0104 l ............
95680000 00000000 00200000 0e000000 l .h....... ......
08000000 00000000 c43eb32a 00000000 l ............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
70b95c00 00000000 f5b4fd2f 86040000 l p筡.....醮....
70b95c00 00000000 f5b4fd2f 00000000 l p筡.....醮....
00000000 08000800 01000000 00000000 l ................
00000000 00000000 d3a75c00 00000000 l ........缨\.....
d4a75c00 00000000 8ebafc2f 01000000 l 鸳\......狐/....
01000000 02000000 00000000 02000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 01000000 01000000 l ................
00000000 00000000 ffff0000 01000000 l ................
00000000 00200000 0e000000 0def5900 l ..... .......颵.
00000000 6e8ad62f 00000000 00000000 l ....n.........
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 70b95c00 l ............p筡.
00000000 f5b4fd2f 33000000 70b95c00 l ....醮3...p筡.
00000000 f5b4fd2f 00000000 00000000 l ....醮........
07000700 01000000 00000000 00000000 l ................
00000000 d3a75c00 00000000 d4a75c00 l ....缨\.....鸳\.
00000000 8ebafc2f 01000000 01000000 l .....狐/........
02000000 00000000 02000000 00000000 l ................
........
00000000 ffff0000 01000000 00000000 l ................
00200000 0e000000 a4160000 00000000 l . ............
c93eb32a 00000000 00000000 00000000 l ............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 70b95c00 00000000 l ........p筡.....
f5b4fd2f 86040000 70b95c00 00000000 l 醮....p筡.....
f5b4fd2f 00000000 00000000 06000600 l 醮............
01000000 00000000 00000000 00000000 l ................
d3a75c00 00000000 d4a75c00 00000000 l 缨\.....鸳\.....
8ebafc2f 01000000 01000000 02000000 l .狐/............
00000000 02000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 02000000 l ................
03000000 03000000 00000000 00000000 l ................
ffff0000 01000000 00000000 00200000 l ............. ..
0e000000 b0220000 00000000 cb3eb32a l ................
..........
00000000 00000000 00000000 00000000 l ................
00000000 70b95c00 00000000 f5b4fd2f l ....p筡.....醮
85040000 70b95c00 00000000 f5b4fd2f l ....p筡.....醮
00000000 00000000 05000500 01000000 l ................
00000000 00000000 00000000 d3a75c00 l ............缨\.
00000000 d4a75c00 00000000 8ebafc2f l ....鸳\......狐/
01000000 01000000 02000000 00000000 l ................
02000000 00000000 00000000 00000000 l ................
.........
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00200000 0e000000 88955500 l ..... ........U.
00000000 b985ac2f 00000000 00000000 l ............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 70b95c00 l ............p筡.
00000000 f5b4fd2f 44000000 70b95c00 l ....醮D...p筡.
00000000 f5b4fd2f 00000000 00000000 l ....醮........
04000400 01000000 00000000 00000000 l ................
00000000 d3a75c00 00000000 d4a75c00 l ....缨\.....鸳\.
00000000 8ebafc2f 01000000 01000000 l .....狐/........
02000000 00000000 02000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
05000000 05000000 06000000 00000000 l ................
00000000 ffff0000 01000000 00000000 l ................
........
00000000 00000000 00000000 00000000 l ................
00000000 00000000 l ........
<16 bytes per line>
BBED>
offset 17, 08 表示name #号,即datafile名编号,通常第一个就是我们的system.
offset 29,0e 表示datafile status。 针对该属性,有如下几种:
KCCFEFDB 0×0001 /* File read-only, plugged from foreign DB */
KCCFEONL 0×0002 /* File is ONLine */
KCCFERDE 0×0004 /* ReaDing is Enabled */
KCCFECGE 0×0008 /* ChanGing is Enabled */
KCCFEMRR 0×0010 /* Media Recovery Required */
KCCFEGEM 0×0020 /* Generate End hot backup Marker at next open */
KCCFECKD 0×0040 /* File record generated by ChecK Dictionary */
KCCFESOR 0×0080 /* Save Offline scn Range at next checkpoint */
KCCFERMF 0×0100 /* Renamed Missing File */
KCCFEGOI 0×0200 /* Generate Off-line Immediate marker */
KCCFECUV 0×0400 /* Checkpoint by instance where UnVerified */
KCCFEDRP 0×0800 /* Offline to be DRoPped */
KCCFEWCC 0×1000 /* Was at Clean Chkpt: clean up to ctrlfile chkpt */
KCCFEODC 0×2000 /* Online at Dictionary Check if read/only tblspc */
KCCFEDBR 0×4000 /* Entry created by DBMS_BACKUP_RESTORE */
KCCFETRO 0×8000 /* Transition Read Only */
offset 193~196,70b95c00 表示checkpoint scn。转换后为6076784
offset 205~206,4086 表示checkpoint count值,即使检查点次数. 转换后为1158.
offset 209~212,70b95c00 表示stop scn. 转换后为6076784。 可见正常停库,stop scn是等于datafile checkpoint scn的。
offset 225~234,00000000 0800 表示Creation Checkpoint scn。
offset 249~252,d3a75c00 表示offline scn. 转换后为6072275.
offset 257~260,d4a75c00 表示offline checkpoint scn. 转换后为6072276
offset 273~282, 01000000 02000000 0000 表示 thread rba. 转换后为0×1.2.0 (这里的offset对应不一定准确)
后面的内容就是我们的file 2,file 3 …..。
controlfile中还存在做其他的内容,非常之多,但是其他内容通常来讲我们需要怎么去关注,而上面的这部分内容
对我们来讲非常重要,特别是针对数据库的恢复。
最后简单总结一下:
1. 针对上述结构的描述,可能某些offset描述不一定准确,仅供大家参考.
2. 当你参考上述描述对controlfile进行修改时,注意先copy备份一下,你可以用bbed或直接UE等工具去编辑。
另外说明一点的是,通过编辑controlfile的方式去操作,并不推荐,针对这样的情况,通常都可以通过
重建controlfile来实现。
3. 上述结构的描述,我们重点关注scn信息以及rba信息,如system checkpoint,datafile checkpoint scn,stop scn,redo scn等等.
4. 由于我这里环境是10.2.0.5.0(linux X86),可能不一定适用所有版本或平台,这个需要注意,另外也希望大家能够进行补充和完善.
上述内容,可能存在错误,如发现,请跟帖指出,我好及时更正,谢谢!
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之【学习笔记】详细介绍Oracle controlfile控制文件内部结构
本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1417.html