SCN不一致的情况下如何开库（1）

模拟实验环境：

1 完整的rman备份，mv最后的归档日志

[oracle@fyl 20140831_0]$ pwd/oracle/rman_fyl/20140831_0[oracle@fyl 20140831_0]$ ls -ltrtotal 964928-rw-r----- 1 oracle dba 959627264 Aug 31 15:45 fyl_1_60_1sphbau5.dbf-rw-r----- 1 oracle dba  10125312 Aug 31 15:45 fyl_1_61_1tphbb0r.dbf-rw-r----- 1 oracle dba   3780608 Aug 31 15:45 fyl_1_62_1uphbb13.arc-rw-r----- 1 oracle dba   1522176 Aug 31 15:45 fyl_1_63_1vphbb15.arc-rw-r----- 1 oracle dba      2560 Aug 31 15:45 fyl_1_64_20phbb16.arc-rw-r----- 1 oracle dba   1945600 Aug 31 15:45 fyl_1_65_21phbb17.arc-rw-r----- 1 oracle dba  10092544 Aug 31 15:45 fyl_1_66.ctl[oracle@fyl 20140831_0]$ mv fyl_1_65_21phbb17.arc fyl_1_65_21phbb17.arc1

2 使用此备份restore、recover

[oracle@fyl ~]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Fri Sep 26 09:51:09 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.connected to target database (not started)RMAN> startup nomountOracle instance startedTotal System Global Area     418484224 bytesFixed Size                     1336932 bytesVariable Size                360712604 bytesDatabase Buffers              50331648 bytesRedo Buffers                   6103040 bytesRMAN> restore controlfile from '/oracle/rman_fyl/20140831_0/fyl_1_66.ctl';Starting restore at 26-SEP-14using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=24 device type=DISKchannel ORA_DISK_1: restoring control filechannel ORA_DISK_1: restore complete, elapsed time: 00:00:04output file name=+ORADATA/fyl/controlfile/current.256.845725969Finished restore at 26-SEP-14RMAN> startup mount;database is already starteddatabase mountedreleased channel: ORA_DISK_1RMAN> run{2> restore database ;3> recover database ;4> }Starting restore at 26-SEP-14allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=24 device type=DISKchannel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to +ORADATA/fyl/datafile/system.260.845725985channel ORA_DISK_1: restoring datafile 00002 to +ORADATA/fyl/datafile/sysaux.261.845726017channel ORA_DISK_1: restoring datafile 00003 to +ORADATA/fyl/datafile/undotbs1.262.845726043channel ORA_DISK_1: restoring datafile 00004 to +ORADATA/fyl/datafile/users.264.845726073channel ORA_DISK_1: reading from backup piece /oracle/rman_fyl/20140831_0/fyl_1_60_1sphbau5.dbfchannel ORA_DISK_1: piece handle=/oracle/rman_fyl/20140831_0/fyl_1_60_1sphbau5.dbf tag=TAG20140831T154405channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:01:25Finished restore at 26-SEP-14Starting recover at 26-SEP-14using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=4channel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=5channel ORA_DISK_1: reading from backup piece /oracle/rman_fyl/20140831_0/fyl_1_65_21phbb17.arcchannel ORA_DISK_1: ORA-19870: error while restoring backup piece /oracle/rman_fyl/20140831_0/fyl_1_65_21phbb17.arcORA-19505: failed to identify file "/oracle/rman_fyl/20140831_0/fyl_1_65_21phbb17.arc"ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3failover to previous backupRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 09/26/2014 09:54:27RMAN-20506: no backup of archived log foundRMAN-06053: unable to perform media recovery because of missing logRMAN-06025: no backup of archived log for thread 1 with sequence 5 and starting SCN of 650640 found to restoreRMAN-06025: no backup of archived log for thread 1 with sequence 4 and starting SCN of 649065 found to restore

3 此时查询数据库SCN（此时数据库SCN不一致）

SYS@ fyl>select CHECKPOINT_CHANGE# from v$database;CHECKPOINT_CHANGE#------------------            650664SYS@ fyl>select CHECKPOINT_CHANGE# from v$datafile;CHECKPOINT_CHANGE#------------------            650664            650664            650664            650664SYS@ fyl>select CHECKPOINT_CHANGE# from v$datafile_header;CHECKPOINT_CHANGE#------------------            650498            650498            650498            650498

解决办法
1 使用_allow_resetlogs_corruption参数（此方法慎用，可能后续出现一系列ORA600）

SYS@ fyl>alter system set "_allow_resetlogs_corruption"=true scope=spfile;System altered.SYS@ fyl>shutdown immediateORA-01109: database not openDatabase dismounted.ORACLE instance shut down.SYS@ fyl>startup mount;ORACLE instance started.Total System Global Area  418484224 bytesFixed Size                  1336932 bytesVariable Size             360712604 bytesDatabase Buffers           50331648 bytesRedo Buffers                6103040 bytesDatabase mounted.          SYS@ fyl>select CHECKPOINT_CHANGE# from v$datafile_header;CHECKPOINT_CHANGE#------------------            650498            650498            650498            650498SYS@ fyl>select CHECKPOINT_CHANGE# from v$database;CHECKPOINT_CHANGE#------------------            650664SYS@ fyl>alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-03113: end-of-file on communication channelProcess ID: 3841Session ID: 1 Serial number: 5查看alert日志发现RESETLOGS is being done without consistancy checks. This may resultin a corrupted database. The database should be recreated.Oracle告诉我们，强制resetlogs跳过了一致性检查，可能导致数据库损坏，数据库应当重建Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0  Mem# 0: +ORADATA/fyl/onlinelog/group_1.257.845725973Block recovery completed at rba 1.64.16, scn 0.650686Errors in file /u01/app/oracle/diag/rdbms/fyl/fyl/trace/fyl_smon_3792.trc:ORA-01595: error freeing extent (11) of rollback segment (2))ORA-00600: internal error code, arguments: [4194], [], [], [], [], [], [], [], [], [], [], []Fri Sep 26 10:23:22 2014ORA-600 [4194]错误的官方解释是："Undo Record Number Mismatch While Adding Undo Record"，当数据库通过REDO恢复来增加UNDO记录时，发现UNDO记录的号码不匹配，也就是出现了不一致。此时sqlplus登录数据库[oracle@fyl ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 26 10:45:31 2014Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected.SYS@ fyl>startupORA-01012: not logged on查看alert日志Fri Sep 26 10:45:52 2014ORA-1092 : opitsk aborting processSYS@ fyl>shutdown abortORACLE instance shut down.SYS@ fyl>create pfile='/home/oracle/init.ora' from spfile;File created.在init.ora文件中修改参数undo_tablespace='SYSTEM'undo_management='MANUAL'使用此init.ora重启数据库，查看alert日志发现Fri Sep 26 11:02:11 2014Errors in file /u01/app/oracle/diag/rdbms/fyl/fyl/trace/fyl_m000_1525.trc:ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'新建undo表空间SYS@ fyl>drop tablespace undotbs1;Tablespace dropped.SYS@ fyl>create undo tablespace undotbs1  2  datafile '+ORADATA' size 100m;修改init.ora文件，使用此init启动数据*.undo_management='auto'*.undo_tablespace='undotbs1'生成spfileSYS@ fyl>create spfile='+ORADATA' from pfile='/home/oracle/init.ora';File created.查找ASM文件最新的spfile文件，并修改数据库pfile文件initfyl.ora，重启即打开完成ASMCMD> lsspfile.266.847053067spfile.267.859288189ASMCMD> [oracle@fyl dbs]$ vi initfyl.oraspfile='+ORADATA/FYL/PARAMETERFILE/spfile.267.859288189'

2 用_minimum_giga_scn推进SCN

在init.ora文件添加_allow_resetlogs_corruption=TRUE_minimum_giga_scn=1                       ------把SCN向前推进1G=1024*1024*1024       11.2.0.4后参数失效undo_tablespace='SYSTEM'undo_management='MANUAL'SYS@ fyl>startup pfile='/home/oracle/init.ora';ORACLE instance started.Total System Global Area  418484224 bytesFixed Size                  1336932 bytesVariable Size             360712604 bytesDatabase Buffers           50331648 bytesRedo Buffers                6103040 bytesDatabase mounted.Database opened.查看alert日志Completed crash recovery at Thread 1: logseq 2, block 67, scn 1073782170 9 data blocks read, 9 data blocks written, 32 redo k-bytes readCurrent SCN is not changed: _minimum_giga_scn (scn 1073741824) is too smallFri Sep 26 12:21:15 2014SYS@ fyl>select CHECKPOINT_CHANGE# from v$datafile_header;CHECKPOINT_CHANGE#------------------        1073782634        1073782634        1073782634        1073782634SCN从650664推进到1073782634！近似等于2的30次方

3 使用10015事件

在init.ora文件添加_allow_resetlogs_corruption=TRUE_allow_error_simulation=TRUEundo_management='MANUAL'SYS@ fyl>startup mount pfile='/home/oracle/init.ora';ORACLE instance started.SYS@ fyl>select CHECKPOINT_CHANGE# from v$datafile_header;CHECKPOINT_CHANGE#------------------            650498            650498            650498            650498SYS@ fyl>alter session set events '10015 trace name adjust_scn level 1'; Session altered.SYS@ fyl>alter database open resetlogs;Database altered.SYS@ fyl>select CHECKPOINT_CHANGE# from v$datafile_header;CHECKPOINT_CHANGE#------------------        1073742289        1073742289        1073742289        1073742289查看alert日志Debugging event used to advance scn to 1073741824      即level1=1G=1024*1024*1024在数据库open状态一种是在open的状态下：用alter session set events 'IMMEDIATE trace name adjust_scn level n';其中n的运算如下：根据alertlog中的报错：ORA-00600: internal error code, arguments: [2662], [0], [43556042], [261], [2396789971], [4194729], [], []这边，我们把2662后的参数[2662],[a],[b],[c],[d],[e]…[a] Current SCN WRAP[b] Current SCN BASE[c] dependent SCN WRAP[d] dependent SCN BASE[e] Where present this is the DBA where the dependent SCN came from.其中scn可以用十六进制表示0Xffff.ffffffff。为了方便，oracle把前面的4位表示scn wrap，后面的8位表示scn base。scn最低值是0X0000.00000000，最高值是0Xffff.ffffffff。高位是scn wrap，低位是scn base。根据报错，我们需要把scn增进到dependent SCN WRAP为261。而我们增进的level n，n是表示1g（即1024×1024×1024），也就是说，调整是以g为单位进行的。而高位的scn wrap的一个1，即0X0001.00000000=0X000100000000（去掉便于分隔高低位的点）=100000000000000000000000000000000=2^32（即2乘以10的32次方）=4×2^30（4乘以2的30次方）=4×（1024×1024×1024）=4g。因此我们要增加到的scn，根据level n，n表示g，调整的level为4×261。即1044，再比这个数字大一些，我们可以设置成1045，1047都可以。

4 oradebug推进SCN方法（见下篇~《oradebug 推进SCN》）
http://blog.csdn.net/u013820054/article/details/39899111