sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】详细介绍Oracle controlfile控制文件内部结构

时间:2016-12-20 18:52   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 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

Oracle研究中心

关键词:

Oracle controlfile内部结构

Oracle控制文件中的内容是啥?