实验步骤
控制文件是一个二进制文件，为了查看其内容，我们可以通过oracle命令转储出来进行查看（以下命令来自oracle 19c）：
SQL> alter session set events 'immediate trace name controlf level 8';
Session altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_13639.trc
解读 19c trace
trc 文件头简介
文件头对文件来源做出了简介：包括文件信息、数据库信息、DB版本号、系统信息、实例信息和进程信息
# 文件名
Trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_13639.trc
# 数据库信息
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Build label: RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME: /u01/app/oracle/product/19.3.0/dbhome_1
# 系统信息
System name: Linux
Node name: node1
Release: 3.10.0-957.el7.x86_64
Version: #1 SMP Thu Oct 4 20:48:51 UTC 2018
Machine: x86_64
# 实例信息
Instance name: orcl
Redo thread mounted by this instance: 1
# 进程信息
Oracle process number: 35
Unix process pid: 13639, image: oracle@node1 (TNS V1-V3)
会话信息
# 会话信息，包括时间、SID、serial#、用户名、客户端、客户端驱动等信息
*** 2020-01-24T10:31:06.667767-05:00
*** SESSION ID:(237.49972) 2020-01-24T10:31:06.667836-05:00
*** CLIENT ID:() 2020-01-24T10:31:06.667850-05:00
*** SERVICE NAME:(SYS$USERS) 2020-01-24T10:31:06.667859-05:00
*** MODULE NAME:(sqlplus@node1 (TNS V1-V3)) 2020-01-24T10:31:06.667868-05:00
*** ACTION NAME:() 2020-01-24T10:31:06.667877-05:00
*** CLIENT DRIVER:(SQL*PLUS) 2020-01-24T10:31:06.667885-05:00
控制文件头
# dump 控制文件，序号为 543
DUMP OF CONTROL FILES, Seq # 543 = 0x21f
# 控制文件头
V10 STYLE FILE HEADER:
# 兼容版本号
Compatibility Vsn = 318767104=0x13000000
# 数据库的dbid、数据库名
Db ID=1558045014=0x5cdde156, Db Name='ORCL'
Activation ID=0=0x0
# 控制文件序号、文件大小共 1142 个文件块
Control Seq=543=0x21f, File size=1142=0x476
# 文件号为0、块大小为16384，即16k块大小、文件类型为控制文件
File Number=0, Blksiz=16384, File Type=1 CONTROL
查询 V$CONTROLFILE_RECORD_SECTION 整理可知
SQL> SELECT TYPE,
1 RECORD_SIZE,
2 RECORDS_TOTAL,
3 RECORD_SIZE*RECORDS_TOTAL,
4 ceil(record_size*records_total/((8*1024)-24))
5 FROM v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL SIZE BLOCK# BLOCKS
---------------------------- ----------- ------------- ------- ------- ---------
TITLE 1 1 1 1
DATABASE 316 1 316 2 1
CKPT PROGRESS 8180 11 89980 3 12
REDO THREAD 256 8 2048 15 1
REDO LOG 72 16 1152 16 1
DATAFILE 520 1024 532480 17 66
FILENAME 524 4146 2172504 83 266
TABLESPACE 180 1024 184320 349 23
TEMPORARY FILENAME 56 1024 57344 372 8
RMAN CONFIGURATION 1108 50 55400 380 7
LOG HISTORY 56 292 16352 387 3
OFFLINE RANGE 200 1063 212600 390 27
ARCHIVED LOG 584 28 16352 417 3
BACKUP SET 96 1022 98112 420 13
BACKUP PIECE 780 1006 784680 433 97
BACKUP DATAFILE 200 1063 212600 530 27
BACKUP REDOLOG 76 215 16340 557 3
DATAFILE COPY 736 1000 736000 560 91
BACKUP CORRUPTION 44 1115 49060 651 7
COPY CORRUPTION 40 1227 49080 658 7
DELETED OBJECT 20 818 16360 665 3
PROXY COPY 928 1004 931712 668 115
BACKUP SPFILE 124 131 16244 783 2
DATABASE INCARNATION 56 292 16352 785 3
FLASHBACK LOG 84 2048 172032 788 22
RECOVERY DESTINATION 180 1 180 810 1
INSTANCE SPACE RESERVATION 28 1055 29540 811 4
REMOVABLE RECOVERY FILES 32 1000 32000 815 4
RMAN STATUS 116 141 16356 819 3
THREAD INSTANCE NAME MAPPING 80 8 640 822 1
MTTR 100 8 800 823 1
DATAFILE HISTORY 568 57 32376 824 4
STANDBY DATABASE MATRIX 400 128 51200 828 7
GUARANTEED RESTORE POINT 256 2048 524288 835 65
RESTORE POINT 256 2108 539648 900 67
DATABASE BLOCK CORRUPTION 80 8384 670720 967 83
ACM OPERATION 104 64 6656 1050 1
FOREIGN ARCHIVED LOG 604 1002 605208 1051 75
PDB RECORD 780 10 7800 1126 1
AUXILIARY DATAFILE COPY 584 128 74752 1127 10
MULTI INSTANCE REDO APPLY 556 1 556 1137 1
PDBINC RECORD 144 113 16272 1139 2
TABLESPACE KEY HISTORY 108 151 16308 1141 2
数据库条目信息
其中包括数据库文件及日志文件数量、检查点等信息：
***************************************************************************
DATABASE ENTRY
***************************************************************************
# 如下对应此下的信息如每条记录的大小,记录个数;已使用记录数
# select * from v$controlfile_record_section where type='DATABASE';
# TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
# -------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
# DATABASE 316 1 1 0 0 0 0
# size指出数据库项的字节数，本例中为 316 byte;
# max指出最大的块
# in-use当前正在使用的块
(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)
# 创建时间
01/21/2020 08:31:18
# 数据库名称
DB Name "ORCL"
Database flags = 0x00404000 0x00001000 0x00000080
# 控制文件创建时间
Controlfile Creation Timestamp 01/21/2020 08:31:19
# 数据库是否进行过不完全回复
Incmplt recovery scn: 0x0000000000000000
# 控制文件启用 resetlog 的时间及 scn
Resetlogs scn: 0x0000000000000001 Resetlogs Timestamp 01/21/2020 08:31:18
# 启用 Resetlogs 之前的 scn 值和时间
Prior resetlogs scn: 0x0000000000000000 Prior resetlogs Timestamp 01/01/1988 00:00:00
# 重做日志文件的版本是13.0.0.0
Redo Version: compatible=0x13000000
# 数据文件总数以及 online 文件数
#Data files = 5, #Online files = 5
# 当前启用了一个线程，当然，也仅仅打开了这一个线程，数据库的检查点 scn，即 v$database.checkpoint_change#
Database checkpoint: Thread=1 scn: 0x000000000008e5e1
# 说明有头尾的概念
Threads: #Enabled=1, #Open=1, Head=1, Tail=1
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
# 说明最多一个日志组有３个成员，经测试可以添加3个以上的成员到日志组中
# Oracle 目前还不支持数据文件的多路复用，因此，Max data members 的值为 1，这个条目是为以后的版本升级而预留的。
Max log members = 3, Max data members = 1
# 凡是开始 scn 小于 Force scn 的重做都被归档。强制 scn 是所有重做都被归档的 scn。后者为当前日志文件的first_change#
Arch list: Head=0, Tail=0, Force scn: 0x0000000000077407scn: 0x0000000000000000
Activation ID: 1558025302
SCN compatibility 3
Auto-rollover enabled
# current 当前日志的 first_change# scn
Controlfile Checkpointed at scn: 0x000000000008f4d0 01/24/2020 10:30:43
thread:0 rba:(0x0.0.0)
# 开启的 thread
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
检查点计数条目
***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
# 如下对应此下的信息如每条记录的大小,记录个数;已使用记录数
# select * from v$controlfile_record_section where type='CKPT PROGRESS';
# TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
# -------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
# CKPT PROGRESS 8180 11 0 0 0 0 0
(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
THREAD #1 - status:0x2 flags:0x0 dirty:29
# 这个就是自 low cache rba 写入到 on disk rba，即实例恢复前滚的 rba 地址段
# on disk rba是磁盘上的最高的重做值，在进行实力恢复应用重做的时候必须达到这个值。
low cache rba:(0xe.22d68.0) on disk rba:(0xe.22d90.0)
# on disk scn是on disk rba 处的 scn 值
on disk scn: 0x000000000008f4d4 01/24/2020 10:30:43
# 启用 Resetlogs 时的 scn 值和时戳，和数据库项中的相应条目是一致的
resetlogs scn: 0x0000000000000001 01/21/2020 08:31:18
# 心跳机制
heartbeat: 1030490376 mount id: 1558287699
# 如下 7 个线程同上
THREAD #2 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #3 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #4 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #5 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #6 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #7 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
THREAD #8 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000000000000000 01/01/1988 00:00:00
resetlogs scn: 0x0000000000000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
补充数据库条目
***************************************************************************
EXTENDED DATABASE ENTRY
***************************************************************************
(size = 900, compat size = 900, section max = 1, section in-use = 1,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 390, numrecs = 1)
# 主要是控制文件自动备份，下次备份的序列
Control AutoBackup date(dd/mm/yyyy)=21/ 1/2020
Next AutoBackup sequence= 0
Database recovery target inc#:1, Last open inc#:1
flg:0x0, flag:0x2000
Change tracking state=0, file index=0, checkpoint count=0scn: 0x0000000000000000
# Oracle 闪回功能
Flashback log count=0, block count=0
Desired flashback log size=0 blocks
Oldest guarantee restore point=0
Highest thread enable/disable scn: 0x0000000000000001
Number of Open thread with finite next SCN in last log: 0
Number of half-enabled redo threads: 0
Sum of absolute file numbers for files currently being moved online: 0
Minimum flashback scn: 0x0000000000000000
Cross-endian dictionary SCN scn: 0x0000000000000000
Old recovery timestamp: 01/01/1988 00:00:00
New recovery timestamp: 01/01/1988 00:00:00
Head filename record number for pre-created datafiles=0
Tail filename record number for pre-created datafiles=0
重做线程条目
***************************************************************************
REDO THREAD RECORDS
***************************************************************************
# 如下对应此下的信息如每条记录的大小,记录个数;已使用记录数
# select * from v$controlfile_record_section where type='REDO THREAD';
# TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
# -------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
# REDO THREAD 256 8 1 0 0 0 0
(size = 256, compat size = 256, section max = 8, section in-use = 1,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 9, numrecs = 8)
THREAD #1 - status:0x40f thread links forward:0 back:0
#logs:3 first:1 last:3 current:2 last used seq#:0xe
# 启用线程 1 时的 scn 值
enabled at scn: 0x0000000000000001 01/21/2020 08:31:24
# 禁用线程 1 时的 scn 值，因为该线程目前处于打开状态，所以该值为无穷大
disabled at scn: 0x0000000000000000 01/01/1988 00:00:00
# 打开线程的时戳，当前线程以及被哪个实例打开
opened at 01/24/2020 09:24:40 by instance orcl
Checkpointed at scn: 0x000000000008e5e1 01/24/2020 09:24:40
thread:1 rba:(0xe.20ac0.10)
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
# 对应select max(sequence#) from v$log_history;
log history: 13
restore point keep sequence: 0
日志文件条目
***************************************************************************
LOG FILE RECORDS
***************************************************************************
# 如下对应此下的信息如每条记录的大小,记录个数;已使用记录数
# select * from v$controlfile_record_section where type='REDO LOG';
# TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID CON_ID
# -------------- ----------- ------------- ------------ ----------- ---------- ---------- ----------
# REDO LOG 72 16 3 0 0 3 0
(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 #1: /oradata/ORCL/redo01a.log
# 这里有个forward、backward，把日志组链接起来
Thread 1 redo log links: forward: 2 backward: 0
# nab是日志文件中LGWR可用的下一个数据块的地址，如果这个值是无穷大，说明这个日志文件是当前（LGWR正在写入）的日志文件
siz: 0x32000 seq: 0x0000000d hws: 0x4 bsz: 512 nab: 0x28c37 flg: 0x0 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000000000077407
# 对应 v$log.first_change# 和 last_change#
# 日志发生切换时会分配一个低scn和一个高scn
# 低 scn 就是上一个日志的高 scn 的值，而高scn则设置为无穷大，直到切换到下一个日志文件时，再将当前日志的高scn设置为下一个日志文件的低scn，将下一个日志文件的高scn设置为无穷大。
Low scn: 0x0000000000081b9c 01/21/2020 11:23:59
Next scn: 0x0000000000087599 01/21/2020 11:28:36
LOG FILE #2:
name #2: /oradata/ORCL/redo02a.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x32000 seq: 0x0000000e hws: 0x7 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000000000081b9c
Low scn: 0x0000000000087599 01/21/2020 11:28:36
# 对于当前(current)日志文件，其最终scn不可知，所以next scn被设置为无穷大
Next scn: 0xffffffffffffffff 01/01/1988 00:00:00
LOG FILE #3:
name #3: /oradata/ORCL/redo03a.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x32000 seq: 0x0000000c hws: 0x4 bsz: 512 nab: 0x293dd flg: 0x0 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x000000000006af2c
Low scn: 0x0000000000077407 01/21/2020 11:21:29
Next scn: 0x0000000000081b9c 01/21/2020 11:23:59
- 还没有人评论，欢迎说说您的想法！