alter session set events 'immediate trace name controlf level <n>';
如:
alter session set events 'immediate trace name controlf level 1';
# 通过以下命令查看dump trace文件的位置
select * from v$diag_info where NAME='Default Trace File';
level : level 1: Generic File Header Level 2: Level 1 + database information + database entry + check point progress records + Extended database entry level 3 or Higher< 9: level 2 + reuse record section level 10: Memory dump of all the control file logical blocks
Control file: file header
alter session set events 'immediate trace name controlf level 1';
select * from v$diag_info where NAME='Default Trace File';
dump trace:
DUMP OF CONTROL FILES, Seq # 28874 = 0x70ca
V10 STYLE FILE HEADER:
Compatibility Vsn = 186647552=0xb200400 # 数据库版本 11.2.0.4
Db ID=1493347395=0x5902ac43, Db Name='ORCL'
Activation ID=0=0x0
Control Seq=28874=0x70ca, File size=614=0x266 # 控制文件大小
File Number=0, Blksiz=16384, File Type=1 CONTROL
# Seq
SQL> select CONTROLFILE_SEQUENCE# from v$database;
CONTROLFILE_SEQUENCE#
---------------------
28875
Control file: DATABASE ENTRY
alter session set events 'immediate trace name controlf level 2';
select * from v$diag_info where NAME='Default Trace File';
dump trace:
***************************************************************************
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)
02/26/2018 17:12:45
DB Name "ORCL"
Database flags = 0x00404001 0x00001200
Controlfile Creation Timestamp 02/26/2018 17:12:46
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.002d8db8 Resetlogs Timestamp 02/26/2018 17:24:23
Prior resetlogs scn: 0x0000.002d3346 Prior resetlogs Timestamp 02/26/2018 14:33:26
Redo Version: compatible=0xb200400
#Data files = 7, #Online files = 7
Database checkpoint: Thread=1 scn: 0x0000.00370ab3
Threads: #Enabled=1, #Open=1, Head=1, Tail=1
enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000
...
#Database flags : 当前数据库的状态
KCCDIMRE 0x00000001 whether media recovery enabled(that is: ARCHIVELOG mode) #
KCCDICKD 0x00000002 if dictionary must be checked with control file
KCCDIRLR 0x00000004 DB OPEN RESETLOGS required
KCCDIJNK 0x00000008 (junk value from beta)
KCCDIMRC 0x00000010 was/is last mounted READ_COMPATIBLE
KCCDICNV 0x00000020 controlfile was just created by convert from v6
KCCDIIRA 0x00000040 Incomplete Recovery Allowed when resetting logs
KCCDICCF 0x00000100 Controlfile was created with CREATE CONTROLFILE
KCCDIINV 0x00000200 Invalid control file or database; still creating
KCCDISBD 0x00000400 StandBy Database; control file for hot standby
KCCDIORL 0x00000800 Opened ResetLogs; set until dictionary check
KCCDICFC 0x00001000 valid ControlFile Checkpoint in backup cf
KCCDISSN 0x00002000 SnapShot controlfile fileName pointer valid
KCCDIUCD 0x00004000 lazy file header Update Checkpoint cycle Done #
KCCDICLO 0x00008000 clone database
KCCDINDL 0x00010000 standby database No Data Loss
KCCDISPK 0x00020000 Supplemental log primary keys
KCCDISUI 0x00040000 Supplemental log unique indexes
KCCDISFK 0x00080000 Supplemental log foreign keys
KCCDIGDA 0x00100000 Database guard all
KCCDIGDS 0x00200000 Database guard standby data
KCCDIIMR 0x00400000 Group Membership Recovery is supported #
KCCDIEAR 0x00800000 End-of-redo Archival Received
KCCDISTR 0x01000000 Standby Terminal Recovery
KCCDILSB 0x02000000 Logical StandBy database
Database flags = 0x00404001 = KCCDIIMR + KCCDIUCD + KCCDIMRE
# 一般控制文件的检查点要比数据库的检查点大一些
SQL> select CONTROLFILE_CHANGE#, CHECKPOINT_CHANGE# from v$database;
CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------------------- ------------------
3616799 3607219
Control file: PROGRESS RECORDS
***************************************************************************
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:30
low cache rba:(0x36.42a6.0) on disk rba:(0x36.42d3.0) # 实例恢复时,启动数据库从low cache rba,到on disk rba结束
on disk scn: 0x0000.00372e55 03/20/2018 15:46:19
resetlogs scn: 0x0000.002d8db8 02/26/2018 17:24:23
heartbeat: 971215409 mount id: 1498920122
Control file: EXTENDED DATABASE ENTRY
***************************************************************************
EXTENDED DATABASE ENTRY
***************************************************************************
(size = 900, compat size = 900, section max = 1, section in-use = 1,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 150, numrecs = 1)
Control AutoBackup date(dd/mm/yyyy)=26/ 2/2018
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=0scn: 0x0000.00000000
Flashback log count=0, block count=0
Desired flashback log size=0 blocks
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
Sum of absolute file numbers for files currently being moved online: 0
low cache rba:(0x36.42a6.0) on disk rba:(0x36.42d3.0) # 实例恢复时,启动数据库从low cache rba,到on disk rba结束。low cache rba:(0x36.4da4.0) on disk rba:(0x36.4dbd.0) # 从(0x36.4da4.0) 开始恢复到(0x36.4dbd.0)结束。如何把5号文件的文件头offset=1的值a2变为c2(写出详细操作步骤,切不能用BBED修改)(给出详细操作步骤)