oracle中 SCN号總結 下篇

重新建立的可以自動進行完全恢複（By biti）

示例

例子背景：

oracle 8i

windows

采用rman做熱備，在備份期間，做不少事務，同時做alter system checkpoint.

RMAN> run {

2> allocate channel c1 type disk;

3> backup database filesperset 3 format 'e:/full_%p_%t.bak';

4> }

(這裡需要一提的是，在這個備份角本裡面我們加了filesperset 3。這樣将整個資料庫分成兩個備份集。這樣還原出來的資料檔案其checkpoint_change#将不一樣。否則由于資料庫資料檔案不多，都将包含在一個備份集中，這樣即使在備份中做insert操作和alter system checkpoint也不會産生不同的checkpoint_change#。因為rman備份是将一個備份集中的檔案同時備份的。

而checkpoint_change#是存放在資料檔案頭部的，這樣備份這些資料檔案的頭部的時間将是很快的。)

然後

RMAN> run{

3> restore database;

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

2156662354

SQL> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#

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

1 2156662355

2 2156662354

3 2156662322

4 2156662354

5 2156662354

6 2156662354

SQL> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE# LAST_CHANGE#

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

2 2156662349

4 2156662342

5 2156662349

6 2156662342

從這裡可以看出，顯然是需要做media recovery的。正常情況下，還需要做instance recovery.當然由于沒有線上日志，是以隻能做resetlogs。

1.有歸檔日志存

若有歸檔日志在，則隻需要做一個recover database until cancel;

然後即可alter database open resetlogs;

SQL> recover database until cancel (using backup controlfile);

ORA-00279: change 2156621770 generated at 10/07/2005 14:30:06 needed for thread 1

ORA-00289: suggestion : D:ORACLE8IRDBMSARC00738.001

ORA-00280: change 2156621770 for thread 1 is in sequence #738

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 2156621779 generated at 10/07/2005 14:30:51 needed for thread

1

ORA-00289: suggestion : D:ORACLE8IRDBMSARC00739.001

ORA-00280: change 2156621779 for thread 1 is in sequence #739

ORA-00278: log file 'D:ORACLE8IRDBMSARC00738.001' no longer needed for this recovery

ORA-00308: cannot open archived log 'D:ORACLE8IRDBMSARC00739.001'

ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) 系統找不到指定的檔案。

SQL> alter database open resetlogs;

Database altered.

2.無歸檔日志

如果沒有歸檔日志，由于restore出來是沒有線上日志的。

如果v$datafile_header中checkpoint_change#是相同的，此時由于控制檔案中checkpoint_change#比資料檔案頭中要高，是以資料庫還是需要做media recovery。

此時重建控制檔案還是一樣的，因為重建控制檔案後，在控制檔案中checkpoint_change#為0，與檔案頭的checkpoint_change#還是不一樣，還需要media recovery.且由于控制檔案中checkpoint_change#比檔案頭中要高，是以做recover時還需要加上using backup controlfile.

注意，這時由于沒有線上日志，是以重建控制檔案需要将noresetlogs改成RESETLOGS才可以建立成功，否則會報以下錯誤:

ORA-01565: error in identifying file 'D:ORACLE8IORADATAORA8IREDO01.LOG'

如：

CREATE CONTROLFILE REUSE DATABASE "ORA8I" RESETLOGS ARCHIVELOG

MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDATAFILES 254

MAXINSTANCES 1

MAXLOGHISTORY 226

LOGFILE

GROUP 1 'D:ORACLE8IORADATAORA8IREDO01.LOG' SIZE 1M,

GROUP 2 'D:ORACLE8IORADATAORA8IREDO02.LOG' SIZE 1M,

GROUP 3 'D:ORACLE8IORADATAORA8IREDO03.LOG' SIZE 1M

DATAFILE

'D:ORACLE8IORADATAORA8ISYSTEM01.DBF',

'D:ORACLE8IORADATAORA8IRBS01.DBF',

'D:ORACLE8IORADATAORA8IUSERS01.DBF',

'D:ORACLE8IORADATAORA8ITEMP01.DBF',

'D:ORACLE8IORADATAORA8ITOOLS01.DBF',

'D:ORACLE8IORADATAORA8IINDX01.DBF'

CHARACTER SET ZHS16GBK

;

此時scn号資訊如下:

SQL> select CHECKPOINT_CHANGE#,CONTROLFILE_CHANGE# from v$database;

CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#

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

0 0

此時由于沒有歸檔日志和線上日志，無法做recovery。

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 2156662342 generated at 10/07/2005 17:06:27 needed for thread 1

ORA-00289: suggestion : D:ORACLE8IRDBMSARC00749.001

ORA-00280: change 2156662342 for thread 1 is in sequence #749

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: 'D:ORACLE8IORADATAORA8ISYSTEM01.DBF'

ORA-01112: media recovery not started

是以也就無法做alter database open Resetlogs了。

此時可以加上_allow_resetlogs_corruption隐含參數，然後就可以alter database open resetlogs将資料庫打開了。

當然如果v$datafile_header中checkpoint_change#是不相同的，那麼此時就沒有什麼常歸有效的辦法能将資料庫打開了。

如果相差不多，加上隐含參數_allow_resetlogs_corruption，然後alter database open resetlogs還是有可能可以打開的。這個參數oracle是不建議加的，且加上這個參數也隻是有可能可以打開。這個參數是以最oldest的scn将資料庫打開，是以最好system資料檔案的scn号是最oldest的，否則容易産生大量的600号錯誤。

補充：

SCN <-> TIMESTAMP conversion features are available only from Oracle 10g.  In 10g, there are two built-in functions to give the timestamp and SCN mapping details--Timestamp_to_scn() and Scn_to_timestamp().

10g中有兩個内建的函數用來在scn和timestamp中轉換：--Timestamp_to_scn()

and Scn_to_timestamp().

For example: In 10g, you will issue code like,

SQL> select current_scn from v$database;

CURRENT_SCN

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

1086382

SQL> Select scn_to_timestamp(1086382) from dual;

SCN_TO_TIMESTAMP(1086382)

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

15-DEC-05 09.39.49.000000000 PM

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

However, in earlier releases, while there is a system object - SYS.SMON_SCN_TIME that will provide the SCN to TIME mapping information.   There is no conversion facility provided.  

在10g之前的版本，一個系統對象表 SYS.SMON_SCN_TIME

提供了在scn和timestamp的映射關系，沒有提供轉換工具。

SYS.SMON_SCN_TIME will have a maximum of 1440 rows and each record will be for a 5 minute period.  Oracle maintains this information for maximum of 5 days after which the records will be recycled.

This means that data is stored 12 times per hour * 24 hours * 5 days=1440 rows.  

SCN value is stored internally as :

i. SCN_wrap

ii. SCN_base

我覺得可以跟時鐘的分針秒針差不多哈哈。。形象的比喻。。

Whenever the SCN is incremented, the BASE component is incremented first unil it reaches it maximum.  Once the BASE reaches the maximum value allowed, it is initialized to zero again after incrementing the WRAP

by 1.

Using this logic, we can calculate the timestamp of the SCN as follows:

(SCN_WRP * 4294967296) + SCN_BAS should give us the SCN in the number format

To get the time/date for an SCN value in 9i, use the following example:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- (a) Get the current SCN base.

SQL>select max(scn_bas) SCN_BASE from smon_scn_time;

1603342197

--(b) Get the complete SCN and the timestamp.

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YY HH:MI:SS';

SQL> select time_dp TIMESTAMP, scn_wrp*4294967296+SCN_bas SCN from smon_scn_time where

scn_bas='1603342197';

TIMESTAMP                 SCN

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

28-JUL-06 05:31:08        8252235517813