如何查看Control File中保存的内容

 

Control File是二进制文件，用普通的方法很难知道其中到底保存了什么内容，但是Oracle却提供了一个SQL*PLUS命令来将Control File的内容dump到文本文件中。

方法如下： 以SYSDBA身份登入SQL*PLUS

sys@JILL>oradebug setmypid
Statement processed.

sys@JILL>oradebug dump controlf 3（bt啊。。。control +f....)
Statement processed.
sys@JILL>

sys@JILL>select type from V$controlfile_record_section; 可以提前看看controlfile里面到底藏了哪些信息 呵呵！
可见我的数据库中在controlfile中主要记录了34中信息，很多都是rman产生的，没有使用catalog，所以很多信息都放在了controlfile中。

TYPE
----------------------------
DATABASE
CKPT PROGRESS
REDO THREAD
REDO LOG
DATAFILE
FILENAME
TABLESPACE
TEMPORARY FILENAME
RMAN CONFIGURATION
LOG HISTORY
OFFLINE RANGE
ARCHIVED LOG
BACKUP SET
BACKUP PIECE
BACKUP DATAFILE
BACKUP REDOLOG
DATAFILE COPY
BACKUP CORRUPTION
COPY CORRUPTION
DELETED OBJECT
PROXY COPY
BACKUP SPFILE
DATABASE INCARNATION
FLASHBACK LOG
RECOVERY DESTINATION
INSTANCE SPACE RESERVATION
REMOVABLE RECOVERY FILES
RMAN STATUS
THREAD INSTANCE NAME MAPPING
MTTR
DATAFILE HISTORY
STANDBY DATABASE MATRIX
GUARANTEED RESTORE POINT
RESTORE POINT

34 rows selected.

将把control file dump到USER_DUMP_DEST初始化参数指定的目录下。

sys@JILL>show parameter USER_DUMP_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /export/home/oracle/product/10
                                               .2/rdbms/log

其中3为dump level。 level的解释如下：

1 ：only the file header

2 ：just the file header, the database info record, and checkpoint progress records

3 ：all record types, but just the earliest and latest records for circular reuse record types

4 ：as above, but includes the 4 most recent records for circular reuse record types

5+ ：as above, but the number of circular reuse records included doubles with each level

http://blog.csdn.net/lunar2000/archive/2004/11/30/198942.aspx

http://blog.csdn.net/lunar2000/archive/2004/11/30/198956.aspx

对controlfile很详细的解读

=====我的controlfile 导出如下，搞懂controfile会让你对整个数据库的结构有非常清晰的了解。===========

Dump file /export/home/oracle/product/10.2/rdbms/log/ora10g_ora_1388.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /export/home/oracle/product/10.2
System name:    SunOS
Node name:    james.jill.com
Release:    5.10
Version:    Generic_127128-11
Machine:    i86pc
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 1388, image: oracle@james.jill.com (TNS V1-V3)

*** 2008-11-15 15:46:41.690
*** SERVICE NAME:(SYS$USERS) 2008-11-15 15:46:41.689
*** SESSION ID:(32.15) 2008-11-15 15:46:41.689
DUMP OF CONTROL FILES, Seq # 2217 = 0x8a9
V10 STYLE FILE HEADER:
    Compatibility Vsn = 169869312=0xa200000
    Db ID=2268329493=0x8733f615, Db Name='JILL'
    Activation ID=0=0x0
    Control Seq=2217=0x8a9, File size=418=0x1a2
    File Number=0, Blksiz=16384, File Type=1 CONTROL

友情提示：看这些16进制（OX打头的）的信息，可以使用windows自带的计算器的科学模式，快速的进行16进制和10进制的转换。


***************************************************************************
1 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)
10/09/2008 23:17:41
DB Name "JILL"
Database flags = 0x10404001 0x00001000
Controlfile Creation Timestamp 10/09/2008 23:17:42 数据库创建时间
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.00099829 Resetlogs Timestamp 11/02/2008 00:58:37 最近的一次resetlog
Prior resetlogs scn: 0x0000.00065ea8 Prior resetlogs Timestamp 10/26/2008 21:25:52
Redo Version: compatible=0xa200000
#Data files = 8, #Online files = 8
Database checkpoint: Thread=1 scn: 0x0000.000ae5a4 导出时的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 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000
Max log members = 5, Max data members = 1
Arch list: Head=1, Tail=1, Force scn: 0x0000.000a986dscn: 0x0000.000a9870
Activation ID: 2270298222
Controlfile Checkpointed at scn: 0x0000.000afdbf 11/15/2008 15:21:55
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 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000




***************************************************************************
2 CHECKPOINT PROGRESS RECORDS
***************************************************************************
(size = 8180, compat size = 8180, section max = 4, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 2, numrecs = 4)
THREAD #1 - status:0x2 flags:0x0 dirty:2
low cache rba:(0x4.26d80.0) on disk rba:(0x4.26d86.0)
on disk scn: 0x0000.000affb6 11/15/2008 15:45:25
resetlogs scn: 0x0000.00099829 11/02/2008 00:58:37
heartbeat: 670840101 mount id: 2271478102
Flashback log tail log# 12 thread# 1 seq 50 block 123 byte 0




***************************************************************************
3 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 = 130, numrecs = 1)
Control AutoBackup date(dd/mm/yyyy)= 9/11/2008
Next AutoBackup sequence= 3
Database recovery target inc#:4, Last open inc#:4
flg:0x0, flag:0x1
Change tracking state=0, file index=0, checkpoint count=0
Flashback log count=18, block count=9276
Oldest guarantee restore point=0
Highest thread enable/disable scn: 0x0000.00000000
Number of Open thread with finite next SCN in last log: 0




***************************************************************************
4 REDO THREAD RECORDS
***************************************************************************
(size = 256, compat size = 256, section max = 1, section in-use = 1,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 5, numrecs = 1)
THREAD #1 - status:0xbf thread links forward:0 back:0
#logs:3 first:1 last:3 current:1 last used seq#:0x4
enabled at scn: 0x0000.00099829 11/02/2008 00:58:37
disabled at scn: 0x0000.00000000 01/01/1988 00:00:00
opened at 11/15/2008 13:15:14 by instance ora10g
Checkpointed at scn: 0x0000.000ae5a4 11/15/2008 13:15:14
thread:1 rba:(0x4.1a723.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
log history: 30




***************************************************************************
5 LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max = 5, section in-use = 5,
last-recid= 12, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 6, numrecs = 5)
LOG FILE #1:
(name #1) /export/home/oradata/redo_log/redo01.log
Thread 1 redo log links: forward: 2 backward: 0
siz: 0x32000 seq: 0x00000004 hws: 0xa bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000a837e
Low scn: 0x0000.000a9870 11/09/2008 23:21:24
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #2:
(name #2) /export/home/oradata/redo_log/redo02.log
Thread 1 redo log links: forward: 3 backward: 1
siz: 0x32000 seq: 0x00000002 hws: 0x6 bsz: 512 nab: 0x454 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00099829
Low scn: 0x0000.000a8183 11/05/2008 21:00:58
Next scn: 0x0000.000a837e 11/05/2008 21:16:45
LOG FILE #3:
(name #3) /export/home/oradata/redo_log/redo03.log
Thread 1 redo log links: forward: 0 backward: 2
siz: 0x32000 seq: 0x00000003 hws: 0x8 bsz: 512 nab: 0xa3d5 flg: 0x1 dup: 1
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000a8183
Low scn: 0x0000.000a837e 11/05/2008 21:16:45
Next scn: 0x0000.000a9870 11/09/2008 23:21:24
LOG FILE #4: <deleted>
Thread 0 redo log links: forward: 0 backward: 0
siz: 0x0 seq: 0x00000000 hws: 0x0 bsz: 0 nab: 0x0 flg: 0x3 dup: 0
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00
LOG FILE #5: <deleted>
Thread 0 redo log links: forward: 0 backward: 0
siz: 0x0 seq: 0x00000000 hws: 0x0 bsz: 0 nab: 0x0 flg: 0x3 dup: 0
Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
Low scn: 0x0000.00000000 01/01/1988 00:00:00
Next scn: 0x0000.00000000 01/01/1988 00:00:00

 

From:http://hi.baidu.com/dba_james/blog/item/133c47d8f91924ee38012f66.html