關於System Change Number（SCN）的常用操作

--檢視一下系統當前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')

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

                         87

--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的資料塊寫到資料檔案