aaaa

最新推荐文章于 2021-04-03 23:44:06 发布

转载最新推荐文章于 2021-04-03 23:44:06 发布 · 69 阅读

认识Oracle控制文件(10g)

一，dump控制文件

dump控制文件常用两种方式：

1. 通过event可以dump出控制文件的内容。
SQL> alter session set events 'immediate trace name controlf level 12' ;

2. 通过oradebug 导出控制文件的内容。

C:\>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 5月 17 11:18:13 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining Scoring Engine options

SQL> oradebug setmypid ;
已处理的语句
SQL> oradebug dump controlf 12;
已处理的语句
SQL>

二，控制文件的内容

以下是从测试数据库dump出来的控制文件中的内容。我们详细来看一看。

http://space.itpub.net/24355950/viewspace-670129

控制文件中的内容包括：

1. DATABASE ENTRY    数据库入口
2. CHECKPOINT PROGRESS RECORDS     检查点部分
3. EXTENDED DATABASE ENTRY
4. REDO THREAD RECORDS      redo检查点信息
5. LOG FILE RECORDS      redo log file信息
6. DATA FILE RECORDS
7. TEMP FILE RECORDS
8. TABLESPACE RECORDS
9. RMAN CONFIGURATION RECORDS      RMAN配置记录
10. FLASHBACK LOGFILE RECORDS       闪回日志记录
11. THREAD INSTANCE MAPPING RECORDS
12. MTTR RECORDS               Mean-Time-To-Recover (MTTR)
13. STANDBY DATABASE MAP RECORDS
14. RESTORE POINT RECORDS
15. LOG FILE HISTORY RECORDS
16. OFFLINE RANGE RECORDS    offline范围记录
17. ARCHIVED LOG RECORDS     归档日志记录
18. BACKUP SET RECORDS       备份集记录
19. BACKUP PIECE RECORDS     备份片记录
20. BACKUP DATAFILE RECORDS 备份数据文件记录
21. BACKUP LOG RECORDS          备份日志记录
22. DATAFILE COPY RECORDS       数据文件拷贝记录
23. BACKUP DATAFILE CORRUPTION RECORDS    备份数据文件损坏记录
24. DATAFILE COPY CORRUPTION RECORDS      数据文件拷贝损坏记录
25. DELETION RECORDS            删除记录
26. PROXY COPY RECORDS
27. INCARNATION RECORDS
28. RMAN STATUS RECORDS        RMAN状态记录
29. DATAFILE HISTORY RECORDS      数据文件历史记录
30. NORMAL RESTORE POINT RECORDS      正常还原点记录

具体例子：

Dump file d:\oracle\product\10.2.0\admin\test01\udump\test01_ora_3108.trc
Tue May 17 11:09:57 2011
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining Scoring Engine options
Windows Server 2003 Version V5.2 Service Pack 1
CPU : 2 - type 586, 1 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1068M/2037M, Ph+PgF:2274M/3889M, VA:1268M/2047M
Instance name: test01

Redo thread mounted by this instance: 1

Oracle process number: 21

Windows thread id: 3108, image: ORACLE.EXE (SHAD)

*** 2011-05-17 11:09:57.343
*** SERVICE NAME:(SYS$USERS) 2011-05-17 11:09:57.312
*** SESSION ID:(143.641) 2011-05-17 11:09:57.312
DUMP OF CONTROL FILES, Seq # 924 = 0x39c
V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3556425887=0xd3fac09f, Db Name='TEST01'
Activation ID=0=0x0
Control Seq=924=0x39c, File size=450=0x1c2
File Number=0, Blksiz=16384, File Type=1 CONTROL

-- 以上内容说明dbid及dbname, Control Seq=924表示控制文件的序列号，表示
控制文件的更新次数，后面是对应的十六进制，File size=450 表示控制文件的
大小，是block数量，这里的block_size=16K, 所以控制文件大小是7200K, 和查
询到的控制文件大小一致。

***************************************************************************
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)
-- size表示这个section的大小. extent=1表示没有扩展. blkno=1表示控制文件块号。
04/28/2011 11:29:06
DB Name "TEST01"
Database flags = 0x00404001 0x00001000
Controlfile Creation Timestamp 04/28/2011 11:29:07
-- 控制文件创建时间
Incmplt recovery scn: 0x0000.00000000
-- 不完全恢复scn
Resetlogs scn: 0x0000.000dc98f Resetlogs Timestamp 04/27/2011 12:51:15
Prior resetlogs scn: 0x0000.000d18da Prior resetlogs Timestamp 04/26/2011 13:35:12
Redo Version: compatible=0xa200100
#Data files = 4, #Online files = 4
Database checkpoint: Thread=1 scn: 0x0000.00111793
-- 数据库检查点SCN, 0x0000.00111793是十六进制,转化为10进制就是1120147,
    与 select checkpoint_change# from v$database; 查询结果一致.
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
Max log members = 3, Max data members = 1
Arch list: Head=3, Tail=3, Force scn: 0x0000.000fecccscn: 0x0000.001115f2
Activation ID: 3557025609
Controlfile Checkpointed at scn: 0x0000.001118f8 05/17/2011 07:43:57
thread:0 rba:(0x0.0.0)
enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
...

***************************************************************************
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:0x2 flags:0x0 dirty:51
low cache rba:(0xa.36aa.0) on disk rba:(0xa.379e.0)
-- low cache rba表示恢复的起点， on disk rba表示恢复时的终点。
on disk scn: 0x0000.00113a4d 05/17/2011 11:09:37
resetlogs scn: 0x0000.000dc98f 04/27/2011 12:51:15
heartbeat: 751355397 mount id: 3558756934
-- heartbeat : ckpt每隔3秒会更新控制文件中的检查点部分
THREAD #2 - 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: 0 mount id: 0
.....

***************************************************************************
EXTENDED DATABASE ENTRY
***************************************************************************
(size = 276, compat size = 276, section max = 1, section in-use = 1,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 147, numrecs = 1)
Control AutoBackup date(dd/mm/yyyy)=28/ 4/2011
Next AutoBackup sequence= 0
Database recovery target inc#:1, Last open inc#:1
flg:0x0, flag:0x0
Change tracking state=0, file index=0, checkpoint count=0
-- Block change tracking 设置为disable, 如果是enable, 那么应该不是0 .
Flashback log count=0, block count=0
Oldest guarantee restore point=0

***************************************************************************
REDO THREAD RECORDS redo检查点信息
***************************************************************************
(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:0xf thread links forward:0 back:0
#logs:3 first:1 last:3 current:3 last used seq#:0xa
enabled at scn: 0x0000.000dc98f 04/27/2011 12:51:15
disabled at scn: 0x0000.00000000 01/01/1988 00:00:00
opened at 05/17/2011 07:38:56 by instance test01
Checkpointed at scn: 0x0000.00111793 05/17/2011 07:38:56
thread:1 rba:(0xa.23a.10)
--- rba:(0xa.23a.10) 表示日志序列.数据块号.偏移量
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
....
00000000 00000000 00000000 00000000 00000000 00000000
log history: 6