oracle检查点相关（一）
oracle查看当前的检查点
SQL> select CURRENT_SCN from v$database;
CURRENT_SCN
-----------
2160376
SQL> select CURRENT_SCN,scn_to_timestamp(current_scn) from v$database;
CURRENT_SCN
-----------
SCN_TO_TIMESTAMP(CURRENT_SCN)
---------------------------------------------------------------------------
2160439
27-NOV-17 10.37.28.000000000 PM
scn记录在control file
dump control file
SQL> alter session set events 'immediate trace name controlf level 2';
Session altered.
SQL> select * from v$diag_info;
/u01/app/oracle/diag/rdbms/testdb1/testdb1/trace/testdb1_ora_27659.trc
信息如下：
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)
11/16/2017 20:31:49
DB Name "TESTDB1"
Database flags = 0x40404001 0x00001200
Controlfile Creation Timestamp 11/16/2017 20:31:49
Incmplt recovery scn: 0x0000.00000000
Resetlogs scn: 0x0000.000e2006 Resetlogs Timestamp 11/16/2017 20:31:51
Prior resetlogs scn: 0x0000.00000001 Prior resetlogs Timestamp 08/24/2013 11:37:30
Redo Version: compatible=0xb200400
#Data files = 6, #Online files = 6
Database checkpoint: Thread=1 scn: 0x0000.0020e2c0
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
转成scn 10进制：
SQL> select to_number('0e2006','xxxxxxxxxxxxxxxxxxxxxxxxxxxxx') from dual;
TO_NUMBER('0E2006','XXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
---------------------------------------------------
925702
在v$datafile_head也是有文件头；
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2155200
2 2155200
3 2155200
4 2155200
5 2155200
6 2155200
6 rows selected.
只有发生检查点的时候会写入文件头：
SQL> alter system checkpoint;
System altered.
SQL> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2161353 checkponit SCN
2 2161353
3 2161353
4 2161353
5 2161353
6 2161353
6 rows selected.
RBA就是redo byte address
第一个部分sequence:
SQL> select group#,sequence#,members from v$log;
GROUP# SEQUENCE# MEMBERS
---------- ---------- ----------
1 151 1
2 149 1
3 150 1
SQL> alter system switch logfile;
System altered.
SQL> select group#,sequence#,memberS,STATUS from v$log;
GROUP# SEQUENCE# MEMBERS STATUS
---------- ---------- ---------- --------------------
1 151 1 ACTIVE
2 152 1 CURRENT
3 150 1 INACTIVE
第二部分是文件的第一个块:
RBA=SEQUENCE+BLOCK+OFFSET
SQL> select * from testogg where rownum <2;
ID ID2
---------- --------------------
356 356oggtest1
SQL> update testogg set id2='AAAAAA' where id=356;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- --------------------- ---------------------
MEMBER
--------------------------------------------------------------------------------
IS_RECOVE
---------
3 ONLINE
/u01/app/oracle/oradata/testdb1/redo03.log
NO
2 ONLINE
/u01/app/oracle/oradata/testdb1/redo02.log
NO
GROUP# STATUS TYPE
---------- --------------------- ---------------------
MEMBER
--------------------------------------------------------------------------------
IS_RECOVE
---------
1 ONLINE
/u01/app/oracle/oradata/testdb1/redo01.log
NO
2号组对应：/u01/app/oracle/oradata/testdb1/redo02.log
dump对应的日志文件
SQL> alter system dump logfile '/u01/app/oracle/oradata/testdb1/redo02.log';
System altered.
SQL> select * from v$diag_info;
Default Trace File
/u01/app/oracle/diag/rdbms/testdb1/testdb1/trace/testdb1_ora_30396.trc
修改前的值转化成16进制
SQL> select dump('356oggtest1',16) FROM DUAL;
DUMP('356OGGTEST1',16)
--------------------------------------------------------------------------------
Typ=96 Len=11: 33,35,36,6f,67,67,74,65,73,74,31
在日志文件中找到，相应的日志文件如下:
REDO RECORD - Thread:1 RBA: 0x000098.00001d56.0010 LEN: 0x0228 VLD: 0x05
SCN: 0x0000.002102a5 SUBSCN: 1 11/27/2017 23:17:41
(LWN RBA: 0x000098.00001d56.0010 LEN: 0002 NST: 0001 SCN: 0x0000.002102a5)
CHANGE #1 TYP:0 CLS:25 AFN:3 DBA:0x00c000c0 OBJ:4294967295 SCN:0x0000.0021028e SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0009 sqn: 0x000007ae flg: 0x0012 siz: 168 fbi: 0
uba: 0x00c0020b.01e4.0c pxid: 0x0000.000.00000000
CHANGE #2 TYP:0 CLS:26 AFN:3 DBA:0x00c0020b OBJ:4294967295 SCN:0x0000.0021028d SEQ:3 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 168 spc: 6764 flg: 0x0012 seq: 0x01e4 rec: 0x0c
xid: 0x0005.009.000007ae
ktubl redo: slt: 9 rci: 0 opc: 11.1 [objn: 87501 objd: 87504 tsn: 7]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c0020b.01e4.09
prev ctl max cmt scn: 0x0000.0020fe6d prev tx cmt scn: 0x0000.0020fe78
txn start scn: 0xffff.ffffffff logon user: 85 prev brb: 12585597 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01800083 hdba: 0x01800082
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 5
col 1: [11] 33 35 36 6f 67 67 74 65 73 74 31
CHANGE #3 TYP:2 CLS:1 AFN:6 DBA:0x01800083 OBJ:87504 SCN:0x0000.001af770 SEQ:1 OP:11.5 ENC:0 RBL:0
KTB Redo
op: 0x11 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0005.009.000007ae uba: 0x00c0020b.01e4.0c
Block cleanout record, scn: 0x0000.002102a5 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.001af770
...
itli: 1 flg: 2 scn: 0x0000.001af770
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01800083 hdba: 0x01800082
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 2 ckix: 0
ncol: 2 nnew: 1 size: -5
col 1: [ 6] 41 41 41 41 41 41
CHANGE #4 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:5.20 ENC:0
session number = 202
serial number = 38997
transaction name =
version 186647552
audit sessionid 52438
Client Id =
login username = OGGTEST
找出objectname:
SQL> select object_name from dba_objects where data_object_id ='87504';
OBJECT_NAME
--------------------------------------------------------------------------------
TESTOGG
0x000098.00001d56.0010
000098 = 152
00001d56= 7510
0010=16
