转储控制文件:
方法一:
alter session set events 'immediate trace name controlf level 8';
level表示级别
--level1 仅包含文件头信息
--level2 包括文件头,数据库信息记录,检查点进程记录
--level3 所有记录类型,针对循环重用的记录类型仅保留最早及最近的记录
--level4 如上,包含4条最新的可重用类型的记录
--level5+ 如上,包含的可重用类型记录为2*level条,注意超过10的和10一样
查找转储文件:
SQL> col value for a75
SQL> select value from v$diag_info wherename='Default Trace File';
也可以从show parameter user_dest_dump查看路径,到相应目录下查找
方法二:
使用oradebug
SQL> oradebug setmypid
已处理的语句
SQL> oradebug dump controlf 3;
已处理的语句
SQL> oradebug tracefile_name;
d:\app\lenovo\diag\rdbms\orcl\orcl\trace\orcl_ora_9708.trc
SQL>
内容解析:
关于数据文件:
***************************************************************************
DATA FILE RECORDS
***************************************************************************
(size = 520, compat size = 520, section max =100, section in-use = 10,
last-recid= 132, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 11, numrecs = 100)
DATA FILE #6:
name #10: D:\APP\LENOVO\ORADATA\ORCL\ZBCXY.DBF
creation size=1280 block size=8192status=0xe head=10 tail=10 dup=1
tablespace 4, index=4 krfil=6 prev_file=4
unrecoverable scn: 0x0000.00000000 01/01/198800:00:00
Checkpoint cnt:143 scn: 0x0000.003883bb11/04/2013 15:46:32
Stopscn: 0xffff.ffffffff 11/03/2013 23:18:06
Creation Checkpointed at scn: 0x0000.0023c33d 10/08/2013 16:10:25
thread:1 rba:(0x42.15a4.10)
enabled threads: 01000000 0000000000000000 00000000 00000000 00000000
参数解释:
检查点计数: Checkpoint cnt:143 为数据文件头和控制文件头第一次检查,如果通过则数据库进行二次检查,第二次检查数据文件头的开始scn和控制文件中记录的该文件的结束scn是否一致
检查点SCN: scn: 0x0000.002ac8ee 08/11/2006 09:48:29
数据文件Stop scn: 0xffff.ffffffff 11/03/2013 23:18:06
关于重做日志文件:
***************************************************************************
LOG FILE RECORDS
***************************************************************************
(size = 72, compat size = 72, section max =16, section in-use = 3,
last-recid= 6, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
name #3: D:\APP\LENOVO\ORADATA\ORCL\REDO01.LOG
name #15: D:\APP\LENOVO\ORADATA\ORCL\REDO01B.LOG
Thread 1 redo log links: forward: 2 backward:0
siz:0x19000 seq: 0x00000088 hws: 0x2 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn:0x0000.003b74d2
Low scn: 0x0000.003be631 11/08/2013 09:54:22
Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #2:
name #2: D:\APP\LENOVO\ORADATA\ORCL\REDO02.LOG
name #16: D:\APP\LENOVO\ORADATA\ORCL\REDO02B.LOG
Thread 1 redo log links: forward: 3 backward:1
siz:0x19000 seq: 0x00000086 hws: 0x4 bsz: 512 nab: 0x13c55 flg: 0x0 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn:0x0000.003a9356
Low scn: 0x0000.003aee75 11/07/2013 08:45:14
Next scn: 0x0000.003b74d2 11/07/2013 22:01:11
LOG FILE #3:
name #1: D:\APP\LENOVO\ORADATA\ORCL\REDO03.LOG
name #17: D:\APP\LENOVO\ORADATA\ORCL\REDO03B.LOG
Thread 1 redo log links: forward: 0 backward:2
siz:0x19000 seq: 0x00000087 hws: 0x4 bsz: 512 nab: 0x115b6 flg: 0x0 dup: 2
Archive links: fwrd: 0 back: 0 Prev scn:0x0000.003aee75
Low scn: 0x0000.003b74d2 11/07/2013 22:01:11
Next scn: 0x0000.003be631 11/08/2013 09:54:22
对于当前(current)日志文件,其最终scn不可知,所以next scn被设置为无穷大
可以通过查询视图v$log看是否当前重做日志文件为第一组
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- -------------------- ---------- -------- ---------------- ------------- ----------------------- -----------
1 1 136 52428800 512 2 NO CURRENT 3925553 2013/11/8 9281474976710
2 1 134 52428800 512 2 NO INACTIVE 3862133 2013/11/7 8 3896530 2013/11/7 2
3 1 135 52428800 512 2 NO INACTIVE 3896530 2013/11/7 2 3925553 2013/11/8 9
关于控制文件的其他内容以后再加以补充