关于System Change Number（SCN）的常用操作-CSDN博客

--查看一下系统当前SCN

sys@TESTDB11>select current_scn from v$database;

CURRENT_SCN

-----------

2543498

--查看一下更新语句的操作的SCN,确定它应该比系统当前SCN小

-- 26caed从对应的redo record中获取

sys@TESTDB11>select to_number('26caed', 'xxxxxxxxx') from dual;

TO_NUMBER('26CAED','XXXXXXXXX')

-------------------------------

2542317

--RBA: redo byte address (日志序号.块号.行号 redo记录的RBA)

--经常做16进制转换，可以考虑使用替换变量，到前面确定一下当前的日志序号是否为87

sys@TESTDB11>select to_number('&a', 'xxxxxxxxx') from dual;

Enter value for a: 57

old 1: select to_number('&a', 'xxxxxxxxx') from dual

new 1: select to_number('57', 'xxxxxxxxx') from dual

TO_NUMBER('57','XXXXXXXXX')

---------------------------

--AFN（Absolute File Number）绝对文件编号. 查看CHANGE #3, AFN:3, 确定3号文件是undo表空间中的文件

sys@TESTDB11>select file#, name from v$datafile;

FILE# NAME

---------- --------------------------------------------------

1 /u01/app/oracle/oradata/TestDB11/system01.dbf

2 /u01/app/oracle/oradata/TestDB11/sysaux01.dbf

3 /u01/app/oracle/oradata/TestDB11/undotbs01.dbf

4 /u01/app/oracle/oradata/TestDB11/users01.dbf

5 /u01/app/oracle/oradata/TestDB11/example01.dbf

sys@TESTDB11>select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

------------------------------ --------------------------------------------------

USERS /u01/app/oracle/oradata/TestDB11/users01.dbf

UNDOTBS1 /u01/app/oracle/oradata/TestDB11/undotbs01.dbf

SYSAUX /u01/app/oracle/oradata/TestDB11/sysaux01.dbf

SYSTEM /u01/app/oracle/oradata/TestDB11/system01.dbf

EXAMPLE /u01/app/oracle/oradata/TestDB11/example01.dbf

--查看系统默认的undo表空间（确定正是UNDOTBS1)

sys@TESTDB11>show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

--DBA (Data Block Address CHANGE #3 DBA:0x00c00301)

sys@TESTDB11>select to_number('c00301', 'xxxxxxxxxx') from dual;

TO_NUMBER('C00301','XXXXXXXXXX')

--------------------------------

12583681

--确定这个DBA地址对应于哪个文件哪个块

sys@TESTDB11>select dbms_utility.data_block_address_file(12583681) file#,

2 dbms_utility.data_block_address_block(12583681) block# from dual;

FILE# BLOCK#

---------- ----------

3 769

--查看修改之前工资1600对应的16进制值, 与redo日志中CHANGE #3内容对比,可以确定CHANGE #3记录的是修改之前的值

sys@TESTDB11>select dump(1600, 16) from dual;

DUMP(1600,16)

------------------

Typ=2 Len=2: c2,11

--识别修改的那行记录

scott@TESTDB11>select rowid from emp where empno = 7499;

ROWID

------------------

AAASb2AAEAAAACXAAB

--把rowid转换为文件.块.行与redo中记录的信息进行对比

scott@TESTDB11>select dbms_rowid.rowid_relative_fno('AAASb2AAEAAAACXAAB') file#,

2 dbms_rowid.rowid_block_number('AAASb2AAEAAAACXAAB') block#,

3 dbms_rowid.rowid_row_number('AAASb2AAEAAAACXAAB') row# from dual;

FILE# BLOCK# ROW#

---------- ---------- ----------

4 151 1

--查看CHANGE #1中的 AFN:4 DBA:0x01000097, 可以确定CHANGE记录的是对数据文件中的改变

sys@TESTDB11>select to_number('1000097', 'xxxxxxxxxxx') from dual;

TO_NUMBER('1000097','XXXXXXXXXXX')

----------------------------------

16777367

sys@TESTDB11>select dbms_utility.data_block_address_file(16777367) file#,

2 dbms_utility.data_block_address_block(16777367) block# from dual;

FILE# BLOCK#

---------- ----------

4 151

--先操作的数据先恢复，后操作的数据后恢复

--发生检查点事件，也会产生SCN（假设为1000)，要要求将data buffer cache中 scn < 1000的数据块写到数据文件

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1151253/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1151253/