Experiment idea: first in the database for bys. update a row in Table a, find the FILE # BLOCK # corresponding to the row, and then DUMP the FILE # BLOCK #. view the SCN information using BBEDDUMP this FILE # BLOCK # To view the SCN Information 1. in the database. update a row in Table a to record the ROWID of the row. 19: 55: 13BYS @ bys3select * from
Experiment idea: first in the database for bys. update a row in Table a, find the FILE # BLOCK # corresponding to this row, and then DUMP this FILE # BLOCK #. view the SCN information. Use bbed dump this FILE # BLOCK # To view the SCN Information 1. in the database. update a row in Table a to record the ROWID of the row. 19:55:13 BYS @ bys3select * from
Tutorial ideas:
Update the row of table bys. a in the database to find the FILE # BLOCK # corresponding to this row #
Then DUMP the FILE # BLOCK # To view the SCN information.
Use bbed dump this FILE # BLOCK # To view the SCN Information
1. Update the row in Table bys. a in the database to record the ROWID of the row. 19:55:13 BYS @ bys3> select * from;
B
----------
55
7
3
At 19:55:15 BYS @ bys3> update a set B = 0 where B = 7;
1 row updated.
19:55:38 BYS @ bys3> commit;
Commit complete.
19:55:40 BYS @ bys3> select rowid, B from a; --- COMMIT is 19:55:40
ROWID B
----------------------------
AAAFOzAAEAAAACSAAA 55
AAAFOzAAEAAAACZAAA 0
AAAFOzAAEAAAACbAAA 3
19:57:15 BYS @ bys3> col object_name for a10
20:01:04 BYS @ bys3> select. rowid,. object_id,. file_id,. block_id,. row_id, B. object_name, B from (select rowid, dbms_rowid.rowid_object (rowid) object_id, partition (rowid) file_id, dbms_rowid.rowid_block_number (rowid) block_id, partition (rowid) row_id, B from bys. a) a, dba_objects B where. object_id = B. object_id; --- if you use this statement, you only need to modify the table name marked in red and the field name.
ROWID OBJECT_ID FILE_ID BLOCK_ID ROW_ID OBJECT_NAM B
------------------------------------------------------------------------------
AAAFOzAAEAAAACbAAA 21427 4 155 0 A 3
Aaafozaaeaaaaczaaaaa 21427 4 153 0 A 0
AAAFOzAAEAAAACSAAA 21427 4 146 0 A 55
The above statement can be used to query the update a set B = 0 where B = 7; modify the data block to file #4, block #153.
######################################## ###########################
2. use DUMP file #4, block #153 data block to view the relevant information of this block-mainly SCN20: 01: 36 BYS @ bys3> alter system dump datafile 4 block 153 at the time of modification;
System altered.
There are three methods to view the TRACE file location:
Select value from v $ diag_info where name like 'default % ';
SYS user execution: oradebug setmypid; oradebug tracefile_name
Or use ls-alt in the user_dump_dest directory to find the most recently generated file.
20:05:32 BYS @ bys3> col name for a10
20:05:40 BYS @ bys3> col value for a50
20:05:49 BYS @ bys3> select * from v $ diag_info where name like 'default % ';
INST_ID NAME VALUE
----------------------------------------------------------------------
1 Default Tr/u01/app/oracle/product/11.2.0/dbhome_1/log/diag/r
Ace File dbms/bys3/bys3/trace/bys3_ora_17341.trc
When the DUMP information is changed, the SCN is: scn: 0x0000. 00197b75. For details, see the following TRACE file.
To SCN:
20:19:17 BYS @ bys3> select to_number ('197b75', 'xxxxxxxxxxx') from dual;
TO_NUMBER ('197b75', 'xxxxxxxxxxx ')
-------------------------------
1670005
20:19:38 BYS @ bys3> select scn_to_timestamp (1670005) from dual; -- converts SCN to time to verify that this SCN is consistent with the time when the UPDATE statement is submitted.
SCN_TO_TIMESTAMP (1670005)
---------------------------------------------------------------------------
29-NOV-13 07.55.39.000000000 PM
View the details in bys3_ora_17341.trc as follows:
Start dump data blocks tsn: 4 file #: 4 minblk 153 maxblk 153
Block dump from cache:
Dump of buffer cache at level 4 for tsn = 4 rdbas = 16777369
BH (0x22bfad14) file #: 4 rdba: 0x01000099 (4/153) class: 1 ba: 0x22b9e000
Set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0, 0
Dbwrid: 0 obj: 21427 objn: 21427 tsn: 4 afn: 4 hint: f
Hash: [0x22bfba7c, 0x2879bdd8] lru: [0x22bfaea4, 0x22bfac10]
Ckptq: [NULL] fileq: [NULL] objq: [0x22bfaebc, 0x25040784] objaq: [0x22bfbba8, 0x22bfb9f0]
St: XCURRENT md: NULL fpin: 'kdswh01: kdstgr 'tch: 6
Flags: block_written_once redo_since_read
LRBA: [0x0. 0.0] LSCN: [0x0.0] HSCN: [0xffff. ffffffff] HSUB: [1]
BH (0x22bfb9f8) file #: 4 rdba: 0x01000099 (4/153) class: 1 ba: 0x22bbc000
Set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0, 0
Dbwrid: 0 obj: 21427 objn: 21427 tsn: 4 afn: 4 hint: f
Hash: [0x2879bdd8, 0x22bfad98] lru: [0x22bfacec, 0x217e4bb8]
Lru-flags: hot_buffer
Ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
St: CR md: NULL fpin: 'kdswh01: kdstgr 'tch: 0
Cr: [scn: 0x0. 197b72], [xid: 0x0. 0.0], [uba: 0x0. 0.0], [cls: 0x0. 197b72], [sfl: 0x0], [lc: 0x0. 14da6c]
Flags: only_sequential_access
Block dump from disk:
Buffer tsn: 4 rdba: 0x01000099 (4/153)
Scn: 0x0000. 00197b75 seq: 0x02 flg: 0x06 tail: 0x7b750602
Frmt: 0x02 chkval: 0xe52c type: 0x06 = trans data
Hex dump of block: st = 0, typ_found = 1
Dump of memory from 0xB6C41600 to 0xB6C43600
######################################## ################## 3
3. Use BBED to view the SCN information in file #4, block #153 data blocks [oracle @ bys3 ~] $ Cat par. bbd
Blocksize = 8192
Listfiledomainbbedfile.txt
Mode = edit
[Oracle @ bys3 ~] $ Cat bbedfile.txt
1/u01/oradata/bys3/system01.dbf 524288000
2/u01/oradata/bys3/sysaux01.dbf 340787200
3/u01/oradata/bys3/undotbs01.dbf 209715200
4/u01/oradata/bys3/user01.dbf 52428800
[Oracle @ bys3 ~] $ Bbed parfile = par. bbd
Password:
BBED: Release 2.0.0.0.0-Limited Production on Fri Nov 29 20:22:10 2013
Copyright (c) 1982,201 1, Oracle and/or its affiliates. All rights reserved.
*************!!! For Oracle Internal Use only !!! ***************
BBED> set file 4 block 153
FILE #4
BLOCK #153.
BBED> map
File:/u01/oradata/bys3/user01.dbf (4)
Block: 153 Dba: 0x01000099
------------------------------------------------------------
KTB Data Block (Table/Cluster)
Struct kcbh, 20 bytes @ 0
Struct ktbbh, 72 bytes @ 20
Struct kdbh, 14 bytes @ 100
Struct kdbt [1], 4 bytes @ 114
Sb2 kdbr [660] @ 118
Ub1 freespace [719] @ 1438
Ub1 rowdata [6031] @ 2157
Ub4 tailchk @ 8188
BBED> print kcbh
Struct kcbh, 20 bytes @ 0
Ub1 type_kcbh @ 0 0x06
Ub1 frmt_kcbh @ 1 0xa2
Ub1 sparejavaskcbh @ 2 0x00
Ub1 spare2_kcbh @ 3 0x00
Ub4 rdba_kcbh @ 4 0x01000099
Ub4 bas_kcbh @ 8 0x00197b75 --- it is better to see that it is scn wrap and scn base.
Ub2 wrp_kcbh @ 12 0x0000
Ub1 seq_kcbh @ 14 0x02
Ub1 flg_kcbh @ 15 0x06 (KCBHFDLC, KCBHFCKV)
Ub2 chkval_kcbh @ 16 0xe52c
Ub2 spare3_kcbh @ 18 0x0000
The following two lines correspond to the SCN wrap of scn high 16-2byte and scn base low 32-4 byte see: http://blog.csdn.net/q947817003/article/details/11590983
Ub4 bas_kcbh @ 8 0x00197b75
Ub2 wrp_kcbh @ 12 0x0000
Scn: SCN = (SCN_WRP * 4294967296) + SCN_BAS
Therefore, the SCN on this data block is 0x0000. 00197b75 -- corresponding to the decimal SCN: 1670005 time: 29-NOV-13 07.55.39.000000000 PM
The value of this SCN is consistent with the SCN obtained by using DUMP file #4, block #153 data block information in the database. Here, the conversion of SCN: 0x0000. 00197b75 to the decimal SCN and to the time can be found in the conversion at DUMP in the previous step.
