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

客官°小女子只卖身不卖艺 2022-08-12 20:59 188阅读 0赞

模拟实验环境：

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

[oracle@fyl 20140831_0]$ pwd
    /oracle/rman_fyl/20140831_0
    [oracle@fyl 20140831_0]$ ls -ltr
    total 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 2014
    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
    connected to target database (not started)
    
    RMAN> startup nomount
    Oracle instance started
    Total System Global Area     418484224 bytes
    Fixed Size                     1336932 bytes
    Variable Size                360712604 bytes
    Database Buffers              50331648 bytes
    Redo Buffers                   6103040 bytes
    
    RMAN> restore controlfile from '/oracle/rman_fyl/20140831_0/fyl_1_66.ctl';
    Starting restore at 26-SEP-14
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=24 device type=DISK
    channel ORA_DISK_1: restoring control file
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
    output file name=+ORADATA/fyl/controlfile/current.256.845725969
    Finished restore at 26-SEP-14
    
    RMAN> startup mount;
    database is already started
    database mounted
    released channel: ORA_DISK_1
    
    RMAN> run{
    2> restore database ;
    3> recover database ;
    4> }
    
    Starting restore at 26-SEP-14
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=24 device type=DISK
    
    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_DISK_1: restoring datafile 00001 to +ORADATA/fyl/datafile/system.260.845725985
    channel ORA_DISK_1: restoring datafile 00002 to +ORADATA/fyl/datafile/sysaux.261.845726017
    channel ORA_DISK_1: restoring datafile 00003 to +ORADATA/fyl/datafile/undotbs1.262.845726043
    channel ORA_DISK_1: restoring datafile 00004 to +ORADATA/fyl/datafile/users.264.845726073
    channel ORA_DISK_1: reading from backup piece /oracle/rman_fyl/20140831_0/fyl_1_60_1sphbau5.dbf
    channel ORA_DISK_1: piece handle=/oracle/rman_fyl/20140831_0/fyl_1_60_1sphbau5.dbf tag=TAG20140831T154405
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
    Finished restore at 26-SEP-14
    Starting recover at 26-SEP-14
    using channel ORA_DISK_1
    
    starting media recovery
    channel ORA_DISK_1: starting archived log restore to default destination
    channel ORA_DISK_1: restoring archived log
    archived log thread=1 sequence=4
    channel ORA_DISK_1: restoring archived log
    archived log thread=1 sequence=5
    channel ORA_DISK_1: reading from backup piece /oracle/rman_fyl/20140831_0/fyl_1_65_21phbb17.arc
    channel ORA_DISK_1: ORA-19870: error while restoring backup piece /oracle/rman_fyl/20140831_0/fyl_1_65_21phbb17.arc
    ORA-19505: failed to identify file "/oracle/rman_fyl/20140831_0/fyl_1_65_21phbb17.arc"
    ORA-27037: unable to obtain file status
    Linux Error: 2: No such file or directory
    Additional information: 3
    
    failover to previous backup
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 09/26/2014 09:54:27
    RMAN-20506: no backup of archived log found
    RMAN-06053: unable to perform media recovery because of missing log
    RMAN-06025: no backup of archived log for thread 1 with sequence 5 and starting SCN of 650640 found to restore
    RMAN-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#
    ------------------
                650664
    SYS@ fyl>select CHECKPOINT_CHANGE# from v$datafile;
    CHECKPOINT_CHANGE#
    ------------------
                650664
                650664
                650664
                650664
    SYS@ 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 immediate
    ORA-01109: database not open
    Database dismounted.
    ORACLE instance shut down.
    SYS@ fyl>startup mount;
    ORACLE instance started.
    
    Total System Global Area  418484224 bytes
    Fixed Size                  1336932 bytes
    Variable Size             360712604 bytes
    Database Buffers           50331648 bytes
    Redo Buffers                6103040 bytes
    Database mounted.
              
    SYS@ fyl>select CHECKPOINT_CHANGE# from v$datafile_header;
    CHECKPOINT_CHANGE#
    ------------------
                650498
                650498
                650498
                650498
    
    SYS@ fyl>select CHECKPOINT_CHANGE# from v$database;
    CHECKPOINT_CHANGE#
    ------------------
                650664
    
    SYS@ fyl>alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel
    Process ID: 3841
    Session ID: 1 Serial number: 5
    
    查看alert日志发现
    RESETLOGS is being done without consistancy checks. This may result
    in 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.845725973
    Block recovery completed at rba 1.64.16, scn 0.650686
    Errors 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 2014
    
    ORA-600 [4194]错误的官方解释是："Undo Record Number Mismatch While Adding Undo Record"，当数据库通过REDO恢复来增加UNDO记录时，发现UNDO记录的号码不匹配，也就是出现了不一致。
    
    此时sqlplus登录数据库
    [oracle@fyl ~]$ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 26 10:45:31 2014
    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    Connected.
    SYS@ fyl>startup
    ORA-01012: not logged on
    
    查看alert日志
    Fri Sep 26 10:45:52 2014
    ORA-1092 : opitsk aborting process
    
    SYS@ fyl>shutdown abort
    ORACLE 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 2014
    Errors 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'
    
    生成spfile
    SYS@ fyl>create spfile='+ORADATA' from pfile='/home/oracle/init.ora';
    File created.
    查找ASM文件最新的spfile文件，并修改数据库pfile文件initfyl.ora，重启即打开完成
    ASMCMD> ls
    spfile.266.847053067
    spfile.267.859288189
    ASMCMD> 
    [oracle@fyl dbs]$ vi initfyl.ora
    spfile='+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 bytes
    Fixed Size                  1336932 bytes
    Variable Size             360712604 bytes
    Database Buffers           50331648 bytes
    Redo Buffers                6103040 bytes
    Database 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 read
    Current SCN is not changed: _minimum_giga_scn (scn 1073741824) is too small
    Fri Sep 26 12:21:15 2014
    
    SYS@ fyl>select CHECKPOINT_CHANGE# from v$datafile_header;
    CHECKPOINT_CHANGE#
    ------------------
            1073782634
            1073782634
            1073782634
            1073782634
    SCN从650664推进到1073782634！近似等于2的30次方

3 使用10015事件

在init.ora文件添加
    _allow_resetlogs_corruption=TRUE
    _allow_error_simulation=TRUE
    undo_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
                650498
    SYS@ 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][http_blog.csdn.net_u013820054_article_details_39899111]

[http_blog.csdn.net_u013820054_article_details_39899111]: http://blog.csdn.net/u013820054/article/details/39899111