oracle中 SCN号总结 下篇

  1. 云栖社区>
  2. 博客>
  3. 正文

oracle中 SCN号总结 下篇

长烟慢慢 2012-09-24 09:24:08 浏览497
展开阅读全文

重新创建的可以自动进行完全恢复(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{
2> allocate channel c1 type disk;
3> restore database;
4> }

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#
---------- ------------------ -------------
1 2156662355
2 2156662349
3 2156662322
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'
ORA-27041: unable to open file
如:
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
Specify log: {=suggested | filename | AUTO | CANCEL}
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

网友评论

登录后评论
0/500
评论
长烟慢慢
+ 关注