C:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on 星期二 7月 20 16:33:30 2010

SQL> conn / as sysdba

已连接。

SQL> oradebug setmypid

已处理的语句

SQL> oradebug unlimit

已处理的语句

SQL> oradebug dump controlf 1;

已处理的语句

SQL> oradebug tracefile_name;

c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4532.trc

这里说一下常用的的转储level:

level 1.仅仅转储控制文件头(file header)

level 2.仅仅包括控制文件头(file header),the database info record,and checkpoint progress records

level 3.All record types,but just the earliest and latest records for circular reuse record types

level 4.As above,but includs the 4 most recent records for circular reuse record types

level为1时跟踪文件的内容

Dump file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_4532.trc

Tue Jul 20 16:34:06 2010

ORACLE V10.2.0.4.0 - Production vsnsta=0

vsnsql=14 vsnxtr=3

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Windows XP Version V5.1 Service Pack 3

CPU : 2 - type 586, 2 Physical Cores

Process Affinity : 0x00000000

Memory (Avail/Total): Ph:357M/2047M, Ph+PgF:1658M/3941M, VA:1254M/2047M

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 17

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

*** 2010-07-20 16:34:06.968

*** ACTION NAME:() 2010-07-20 16:34:06.953

*** MODULE NAME:(sqlplus.exe) 2010-07-20 16:34:06.953

*** SERVICE NAME:(SYS$USERS) 2010-07-20 16:34:06.953

*** SESSION ID:(152.2918) 2010-07-20 16:34:06.953

DUMP OF CONTROL FILES, Seq # 1548 = 0x60c

V10 STYLE. FILE HEADER:

Compatibility Vsn = 169870080=0xa200300

Db ID=1250753805=0x4a8cfd0d, Db Name='ORCL'

Activation ID=0=0x0

Control Seq=1548=0x60c, File size=430=0x1ae

File Number=0, Blksiz=16384, File Type=1 CONTROL

*** END OF DUMP ***

转储控制文件，Db ID表示数据库的DBID，一般用RMAN时可显示出DBID，做恢复时可用到；

Control Seq是控制文件的序列号，表明控制文件的更新次数，可以看作是控制的版本号，后面是用16进制表示值；

File size表示文件的大小，单位为块，则控制文件的大小表示为430*16KB=6880KB，这与实际的物理文件的大小一致；

Blksiz则表示文件块的大小，应该是控制文件本身块的大小，当我查询数据库块时为8K，与这里不一样：

SQL> show parameter db_block_size;

NAME TYPE VALUE

-------------------- --------------- -----------

db_block_size integer 8192

转储level2的控制文件

SQL> oradebug setmypid

已处理的语句

SQL> oradebug dump controlf 2

已处理的语句

SQL> oradebug tracefile_name

c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_5752.trc

level为2时转储文件的内容

Dump file c:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_5752.trc

Tue Jul 20 17:00:07 2010

ORACLE V10.2.0.4.0 - Production vsnsta=0

vsnsql=14 vsnxtr=3

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Windows XP Version V5.1 Service Pack 3

CPU : 2 - type 586, 2 Physical Cores

Process Affinity : 0x00000000

Memory (Avail/Total): Ph:515M/2047M, Ph+PgF:1674M/3941M, VA:1255M/2047M

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 17

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

*** 2010-07-20 17:00:07.437

*** ACTION NAME:() 2010-07-20 17:00:07.421

*** MODULE NAME:(sqlplus.exe) 2010-07-20 17:00:07.421

*** SERVICE NAME:(SYS$USERS) 2010-07-20 17:00:07.421

*** SESSION ID:(152.2976) 2010-07-20 17:00:07.421

DUMP OF CONTROL FILES, Seq # 1550 = 0x60e

V10 STYLE. FILE HEADER:

Compatibility Vsn = 169870080=0xa200300

Db ID=1250753805=0x4a8cfd0d, Db Name='ORCL'

Activation ID=0=0x0

Control Seq=1550=0x60e, File size=430=0x1ae

File Number=0, Blksiz=16384, File Type=1 CONTROL

***************************************************************************

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)

<这里blkno表示数据库项信息所在block号>

06/29/2010 17:51:09

DB Name "ORCL"

Database flags = 0x00404001 0x00001000

Controlfile Creation Timestamp 06/29/2010 17:51:10

<这里表示控制文件创建的时间戳>

Incmplt recovery scn: 0x0000.00000000

<这里表示不完全恢复时候的SCN>

Resetlogs scn: 0x0000.00089c75 Resetlogs Timestamp 06/29/2010 17:51:13

<这里表示启用Resetlogs时候的SCN的值以及那个时候的时间戳>

Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp 03/14/2008 18:46:22

<这里表示在启用Resetlogs之前的SCN的值以及那个时候的时间戳>

Redo Version: compatible=0xa200300

<这里与上面Compatibility Vsn一致>

#Data files = 9, #Online files = 9

<表示这里有9个数据文件，并且这9个数据文件都是连机状态>

Database checkpoint: Thread=1 scn: 0x0000.0014cb31

<这里表示了一个完全检查点的SCN>

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

<这里表示了每个日志文件组中最大日志成员个数为3；Oracle中数据文件的多路复用，这里表示为1>

Arch list: Head=3, Tail=3, Force scn: 0x0000.0013a55cscn: 0x0000.0014cb30

<这里表示一个归档列表，需要注意的是凡是SCN的值小于我们Force SCN的redo log都被归档了>

Activation ID: 1250761741

Controlfile Checkpointed at scn: 0x0000.00152f98 07/20/2010 16:35:42

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000

***************************************************************************

CHECKPOINT PROGRESS RECORDS

***************************************************************************

<这里表示"检查点的记录进度"，这一项从Oracle8开始引入在控制文件当中。这里主要是记录了我们增量检查点的状态，因为我们都知道自从 checkpoint queue引入了Oracle后，每一个脏数据块都会被移动到检查点队列，按照Low RBA的顺序来排列，那么在写出的时候也是按照Low RBA的顺序写出，对块一级所做的任何修改并不影响这个块在checkpoint queue中的low rba位置顺序。CKPT进程，按照每3秒的heartbeat来更新我们的控制文件，更新的就是这一块区域。每3秒都是把最低的RBA，也就是我们 low rba的信息写入我们的控制文件中的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:198

low cache rba:(0x1e.e6c3.0) on disk rba:(0x1e.eb06.0)

<表示实例恢复时候的起点和终点，应该是low cache rba->on disk rba>

on disk scn: 0x0000.00153422 07/20/2010 17:00:04

<这里表示on disk rba所处的SCN值>

resetlogs scn: 0x0000.00089c75 06/29/2010 17:51:13

heartbeat: 724838762 mount id: 1252624356

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

<启用Resetlogs时的scn值和时间戳，和数据库项中的相应条目应该是一致的。>

heartbeat: 0 mount id: 0

<这里还记录了每N秒记录的心跳，目的就是为了减少数据库崩溃的时候恢复所用的时间,其实心跳机制是包含在CKPT timeout action里面的，每3秒来更新一下我们的checkpoint, 同时需要注意的是，心跳并非在open在才更新，mount状态下，Oracle也是同样可以检测的>

THREAD #3 - 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

THREAD #4 - 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

THREAD #5 - 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

THREAD #6 - 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

THREAD #7 - 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

THREAD #8 - 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

<这里总共有8个线程>

***************************************************************************

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 = 137, numrecs = 1)

Control AutoBackup date(dd/mm/yyyy)=29/ 6/2010

Next AutoBackup sequence= 0

Database recovery target inc#:2, Last open inc#:2

flg:0x0, flag:0x0

Change tracking state=0, file index=0, checkpoint count=0

Flashback log count=0, block count=0

Oldest guarantee restore point=0

Highest thread enable/disable scn: 0x0000.00000000

Number of Open thread with finite next SCN in last log: 0

Number of half-enabled redo threads: 0

*** END OF DUMP ***

ITPub博客

利用oradebug探讨控制文件

最新文章