利用oradebug探討控制檔案

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部落格 ” ，連結：http://blog.itpub.net/9932141/viewspace-668520/，如需轉載，請註明出處，否則將追究法律責任。

利用oradebug探討控制檔案

相關文章