Oracle Controlfile控制文件中记录的信息片段sections

初学Oracle的朋友肯定对Controlfile控制文件中到底记录了何种的信息记录而感到好奇,实际上我们可以通过一个视图v$controlfile_record_section来了解控制文件的信息片段:

 

SQL> select type, record_size, records_total from v$controlfile_record_section;
TYPE                         RECORD_SIZE RECORDS_TOTAL
---------------------------- ----------- -------------
DATABASE                             316             1
CKPT PROGRESS                       8180            35
REDO THREAD                          256            32
REDO LOG                              72           192
DATAFILE                             520          1024
FILENAME                             524          4674
TABLESPACE                            68          1024
TEMPORARY FILENAME                    56          1024
RMAN CONFIGURATION                  1108            50
LOG HISTORY                           56           292
OFFLINE RANGE                        200          1063
ARCHIVED LOG                         584           136
BACKUP SET                            40          1227
BACKUP PIECE                         736          1000
BACKUP DATAFILE                      200          1063
BACKUP REDOLOG                        76           430
DATAFILE COPY                        736          1000
BACKUP CORRUPTION                     44          1115
COPY CORRUPTION                       40          1227
DELETED OBJECT                        20           818
PROXY COPY                           928          1004
BACKUP SPFILE                        124           131
DATABASE INCARNATION                  56           292
FLASHBACK LOG                         84          2048
RECOVERY DESTINATION                 180             1
INSTANCE SPACE RESERVATION            28          1055
REMOVABLE RECOVERY FILES              32          1000
RMAN STATUS                          116           141
THREAD INSTANCE NAME MAPPING          80            32
MTTR                                 100            32
DATAFILE HISTORY                     568            57
STANDBY DATABASE MATRIX              400            31
GUARANTEED RESTORE POINT             212          2048
RESTORE POINT                        212          2083
DATABASE BLOCK CORRUPTION             80          8384
ACM OPERATION                        104            64
FOREIGN ARCHIVED LOG                 604          1002
v$controlfile_record_section 的数据实际来源于X$KCCRS内部表
v$controlfile_record_section 视图的定义 
select inst_id,
decode(indx,
0,
'DATABASE',
1,
'CKPT PROGRESS',
2,
'REDO THREAD',
3,
'REDO LOG',
4,
'DATAFILE',
5,
'FILENAME',
6,
'TABLESPACE',
7,
'TEMPORARY FILENAME',
8,
'RMAN CONFIGURATION',
9,
'LOG HISTORY',
10,
'OFFLINE RANGE',
11,
'ARCHIVED LOG',
12,
'BACKUP SET',
13,
'BACKUP PIECE',
14,
'BACKUP DATAFILE',
15,
'BACKUP REDOLOG',
16,
'DATAFILE COPY',
17,
'BACKUP CORRUPTION',
18,
'COPY CORRUPTION',
19,
'DELETED OBJECT',
20,
'PROXY COPY',
21,
'BACKUP SPFILE',
23,
'DATABASE INCARNATION',
24,
'FLASHBACK LOG',
25,
'RECOVERY DESTINATION',
26,
'INSTANCE SPACE RESERVATION',
27,
'REMOVABLE RECOVERY FILES',
28,
'RMAN STATUS',
29,
'THREAD INSTANCE NAME MAPPING',
30,
'MTTR',
31,
'DATAFILE HISTORY',
32,
'STANDBY DATABASE MATRIX',
33,
'GUARANTEED RESTORE POINT',
34,
'RESTORE POINT',
35,
'DATABASE BLOCK CORRUPTION',
36,
'ACM OPERATION',
37,
'FOREIGN ARCHIVED LOG',
'UNKNOWN'),
rsrsz,
rsnum,
rsnus,
rsiol,
rsilw,
rsrlw
from x$kccrs
where indx not in (22)

 

下表罗列出了controlfile 控制文件可能出现的数据库各类文件的记录,如数据文件记录、文件名记录、表空间记录等:

 

1. Database Information Record: (1 per database)
2. Checkpoint Progress Record: (1 per thread)
3. Redo Thread Record: (1 per thread)
4. Logfile Record: (1 per logfile)
5. Datafile Record: (1 per datafile)
6. Filename Record: (1 per datafile, tempfile or logfile group member)
7. Tablespace Record: (1 per tablespace)
8. Temporary File Record: (1 per tempfile)
9. RMAN Configuration Record: (1 per configuration parameter)
10. Log History Record: (1 per completed logfile)
11. Offline Range Record: (1 per offline range per datafile)
12. Archived Log Record: (1 per archived log)
13. Backup Set Record: (1 per backup set)
14. Backup Piece Record: (1 per backup piece)
15. Backup Datafile Record: (1 per datafile)
16. Backup Redo-log Record: (1 per archived log)
17. Datafile Copy Record: (1 per datafile)
18. Backup Datafile Corruption Record: (1 per corrupt range)
19. Datafile Copy Corruption Record: (1 per corrupt range)
20. Deleted Object Record: (1 per deleted object)
21. Proxy Copy Record: (1 per proxy datafile or archived log)
22. Backup SPFILE Record: (1 per SPFILE)
23. Extended Database Information Record: (1 per database)
24. Flashback Database Log Record: (1 per flashblack log)
25. Recovery Destination Information Record: (1 per database)
26. Instance Recovery Table Record: (1 per instance)
27. Aged File Record: (1 per aged control file record)
28. RMAN Status Record: (1 per RMAN session/operation)
29. Thread Instance Name Mapping Record: (1 per thread)
30. MTTR Record: (1 per thread)
31. Datafile History Record: (1 per dropped datafile)

Raid Level,该如何为Oracle存储选择才好?

Oracle Database中的文件可以分成多种:包括数据文件、联机日志文件、归档日志文件、临时文件及控制文件等。而这些文件在数据库运行时的I/O表现上也存在着巨大的差异:譬如控制文件的IO特点是随机读写多、日志文件和归档日志文件是顺序写、SYSTEM系统表空间总是有大量的随机读写IO、临时表空间和回滚表空间也会有大量随机读写IO。

那么针对以上不同文件的IO特征,我该如何选择合适的Raid Level呢?

著名的Cary Millsap为我们提供了以下图表可作参考:

raid5_color_coded3

以上1代表最佳,5代表最差

在OLTP环境中Raid 10比Raid 5更为合适,因为Raid 5的每次写出都会引发2次读取操作加上一次实际写出操作,而这2次读取操作可能与其他读取发生冲突。同时大多数情况下Raid 5会配置较小的条带化大小(stripe size),如32k或者64k,因而多块读操作可能需要跨越多个磁盘,这就增长了并行读取争用现象出现的概率。

以下为Oracle中常见IO等待事件的属性列表:
Wait Event R/W SIO/AIO Single/Multi Elapsed (with 1000+ waits per hour)
control file parallel write W AIO Multi < 15ms
control file sequential read R SIO Single < 20 ms
db file parallel read R AIO Multi < 20 ms
db file scattered read R SIO Multi < 20 ms
db file sequential read R SIO Single < 20 ms
direct path read R AIO Multi < 20 ms
direct path read temp R AIO Multi < 20 ms
direct path write W AIO Multi < 15 ms
direct path write temp W AIO Multi < 15 ms
log file parallel write W AIO Multi < 15 ms
Exadata Related
cell smart table scan R AIO Multi < 1 ms
cell single block physical read R SIO Single < 1 ms
cell multiblock physical read R SIO Multi < 6 ms

logfile switch causes incremental checkpoint?

不少Oracle的初学者都会因为checkpoint这个知识点而头痛,绝大多数Oracle文档对完全检查点和增量检查点的描述又都略显朦胧;譬如在线日志的切换引起的是完全检查点还是增量检查点这个问题,就有不少的争论。实际上增量检查点与完全检查点有一个显著的区别:完全检查点发生时控制文件和数据文件头中的checkpoint scn都会被更新,而增量检查点发生时只有控制文件中的checkpoint scn更新;
我们可以通过以下演示证明日志切换引发的到底是何种检查点?:

SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1665476
1665476
1665476
1665476
1665476
1665476
6 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697131
1697131
1697131
1697131
1697131
1697131
6 rows selected.
/* 手动执行checkpoint,数据文件头的checkpoint scn立即更新了 */
SQL> alter system flush buffer_cache;
System altered.
SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697131
1697131
1697131
1697131
1697131
1697131
6 rows selected.
/* 单纯flush buffer cache冲刷数据库高速缓存不会更新数据文件头的checkpoint scn */
SQL> alter system set log_checkpoints_to_alert=true;
System altered.
SQL> alter system set log_checkpoint_timeout=20;
System altered.
/* 设置log_checkpoint_timeout为20s,频繁引发增量检查点 */
alert log:
Wed Nov  3 20:24:49 2010
Incremental checkpoint up to RBA [0x3d.dff1.0], current log tail at RBA [0x3d.dff6.0]
Wed Nov  3 20:25:07 2010
Incremental checkpoint up to RBA [0x3d.dff7.0], current log tail at RBA [0x3d.dffc.0]
Wed Nov  3 20:25:25 2010
Incremental checkpoint up to RBA [0x3d.dffd.0], current log tail at RBA [0x3d.e002.0]
Wed Nov  3 20:25:43 2010
Incremental checkpoint up to RBA [0x3d.e003.0], current log tail at RBA [0x3d.e008.0]
Wed Nov  3 20:26:01 2010
Incremental checkpoint up to RBA [0x3d.e009.0], current log tail at RBA [0x3d.e00e.0]
SQL> set time on;
20:26:38 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697131
1697131
1697131
1697131
1697131
1697131
6 rows selected.
/* 可以看到增量检查点并不会引起数据文件头的checkpoint scn 被更新 */
20:26:43 SQL>  alter system set log_checkpoint_timeout=1800;
System altered.
/* 那么日志文件切换就会引起数据文件头的checkpoint scn被更新吗?*/
20:28:10 SQL> alter system switch logfile;
System altered.
20:29:16 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697131
1697131
1697131
1697131
1697131
1697131
6 rows selected.
/* logfile switch 日志文件切换引起的是一种slow慢的完全检查点,它不同于alter system checkpoint(ASC),
ASC要求的脏块写出和控制文件及数据文件头更新时要立即完成的,也就是说当alter system checkpoint语句返回"System altered."
后以上工作都已经完成了;而alter system switch logfile或者自然的日志切换引发的是一种慢的完全检查点,
它在返回"System altered"时不要求写脏块等工作必须已经完成
*/
/* 我们可以用冲刷高速缓存的方式保证脏块写出的工作被督促完成 */
20:33:39 SQL> alter system flush buffer_cache;
System altered.
20:33:45 SQL> select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1697544
1697544
1697544
1697544
1697544
1697544
6 rows selected.
/* 虽然日志切换所引发的slow checkpoint(慢的检查点)并无立即完成的要求,但也并非全无限制;
当某次日志切换由1号日志组切换到2号日志组时,
将引发一个slow checkpoint,之后日志连续切换又要切到1号日志组时要求之前的那个slow checkpoint在切换前必须完成
*/
20:41:35 SQL> set timing on;
20:42:02 SQL>  select * from v$log;
GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1          1         67   52428800          2 YES INACTIVE               1698288 2010-11-03 20:41:19
2          1         68   52428800          2 YES INACTIVE               1698292 2010-11-03 20:41:21
3          1         69   52428800          2 NO  CURRENT                1698302 2010-11-03 20:41:35
Elapsed: 00:00:00.00
20:42:17 SQL> delete tv;
51134 rows deleted.
Elapsed: 00:00:01.68
20:42:34 SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
20:42:36 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:00.01
20:42:40 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:00.01
20:42:43 SQL> alter system switch logfile;
System altered.
Elapsed: 00:00:02.00
20:45:28 SQL>  select checkpoint_change# from v$datafile_header where status='ONLINE';
CHECKPOINT_CHANGE#
------------------
1700686
1700686
1700686
1700686
1700686
1700686
6 rows selected.
Elapsed: 00:00:00.00
alter.log告警日志中的内容:
Wed Nov  3 20:42:40 2010
Beginning log switch checkpoint up to RBA [0x46.2.10], SCN: 1700686
...........................
Wed Nov  3 20:42:45 2010
Thread 1 cannot allocate new log, sequence 72
Checkpoint not complete
....................
Completed checkpoint up to RBA [0x46.2.10], SCN: 1700686
/* 最近一次的日志切换耗费2s,在告警日志中可以看到此次slow checkpoint的相关记录 */

VIEW:X$KCCRS-Controlfile Record Section directory (8.0 – 8.1)

View:   X$KCCRS
[K]ernel [C]ache [C]ontrolfile management
controlfile [R]ecord [S]ection directory
Column      Type           Description
--------    ----           -----------
ADDR        RAW(4)         address of this row/entry in the SGA
INDX        NUMBER         control file record type
The following are the non-circular-reuse record types:
KCCDEDBI     0             DataBase Info record
KCCDECKP     1             Checkpoint progress
KCCDERTH     2             Redo THread record
KCCDELOG     3             LOgFile record
KCCDEDBF     4             DataBase File record
KCCDENAM     5             file NAMe record
KCCDETBS     6     8.x     TaBleSpace record
KCCDERS1     7     8.0     reserved for future use. non-circular re-use
KCCDETFL     7     8.1     Temporary File record
KCCDERS2     8     8.x     reserved for future use. non-circular re-use
KCCDERMC     8     9.x     RMan Configuration record
The following are the circular-Reuse record types:
KCCDELHR     9     8.x     Log History Record
KCCDEORR    10     8.x     Offline Range Record
KCCDEALR    11     8.x     Archived Log Record
KCCDEBSR    12     8.x     Backup Set Record
KCCDEBPR    13     8.x     Backup Piece Record
KCCDEBFR    14     8.x     Backup dataFile Record
KCCDEBLR    15     8.x     Backup redoLog Record
KCCDEDCR    16     8.x     Datafile Copy Record
KCCDEFCR    17     8.x     backup dataFile Corruption Record
KCCDECCR    18     8.x     datafile Copy Corruption Record
KCCDEDLR    19     8.x     DeLeted object Record
KCCDERS3    20     8.0      reserved for future use. circular re-use.
KCCDEPCR    20     8.1     proxy copy record
KCCDERS4    21     8.x     reserved for future use. circular re-use.
KCCDENEN     6     7.3     actual # entry types in control file
KCCDEMEN    10     7.3     max possible # entry types in control file
KCCDEMNR     9     8.x     MiNimum circular-Reuse record type
KCCDEMXR    21     8.x     MaXimum circular-Reuse record type
KCCDEMAX    22     8.x     MAX # record types in current format
INST_ID     NUMBER         oracle instance number
RSLBN       NUMBER         Logical Blk Number (base 1) of section start
RSRSZ       NUMBER         Record SiZe in bytes
RSNUM       NUMBER         NUMber of usable record slots in section
RSNUS       NUMBER         circ-reuse: Number of in-USe slots in section
non-circ-reuse: highest USed slot Number
RSIOL       NUMBER         circ-reuse: Index (base 1) of OLdest (init 0)
RSILW       NUMBER         circ-reuse: Index of Last Written    (init 0)
RSRLW       NUMBER         circ-reuse: Recid of Last Written    (init 0)
non-circ-reuse: incr'd by kccicr()   (init 0)

沪ICP备14014813号

沪公网安备 31010802001379号