记一次数据库参数compatible降级[转]

转：http://dbzone.iteye.com/blog/1042455

众所周知，Oracle参数compatible 主要用于启用Oracle针对某一版本的新特性。但此参数设置时，只能往上调，设置好之后不能往下降。 

引用

You can advance the compatibility level of your database. If you do advance the compatibility of your database with the COMPATIBLE initialization parameter, there is no way to start the database using a lower compatibility level setting, except by doing a point-in-time recovery to a time before the compatibility was advanced. 

The default value for the COMPATIBLE parameter is the release number of the most recent major release.

Note:For Oracle Database 10g Release 2 (10.2), the default value of the COMPATIBLE parameter is 10.2.0. The minimum value is 9.2.0. If you create an Oracle Database using the default value, you can immediately use all the new features in this release, and you can never downgrade the database.

比如可以将参数compatible从10.2.0.1.0设置成10.2.0.2.0，重启数据库后生效。 

引用

SQL> alter system set compatible="10.2.0.2.0" scope=spfile; 

System altered.

重启数据库后alert日志会有如下显示，从alert日志中可以明显的看出，compatible升级之后，Oracle会修改控制文件和redolog 

引用

Fri May 13 11:59:11 2011 
alter database mount 
Fri May 13 11:59:15 2011 
ALERT: Compatibility of the database is changed from 10.2.0.0.0 to 10.2.0.2.0. 
Setting recovery target incarnation to 1 
Fri May 13 11:59:15 2011 
Successful mount of redo thread 1, with mount id 200680975 
Fri May 13 11:59:15 2011 
Database mounted in Exclusive Mode 
Completed: alter database mount 
Fri May 13 11:59:32 2011 
alter database open 
Fri May 13 11:59:32 2011 
Switching redo format version from 10.2.0.0.0 to 10.2.0.2.0 at change 803371 
Fri May 13 11:59:32 2011 
Thread 1 opened at log sequence 10 
  Current log# 1 seq# 10 mem# 0: /oradata/mynewdb/redo01.log 
Successful open of redo thread 1

其实，Oracle还会修改数据文件头，从数据文件头的dump信息中可以看出存在compatible信息 

引用

aux_file is NOT DEFINED 
V10 STYLE FILE HEADER: 
        Compatibility Vsn = 169869568=0xa200100 
        Db ID=2596133541=0x9abddaa5, Db Name='XE' 
        Activation ID=0=0x0 
        Control Seq=198940=0x3091c, File size=142080=0x22b00 
        File Number=1, Blksiz=8192, File Type=3 DATA

经过以上分析，要实现手工实现compatible降级，必须修改3个地方，即控制文件，数据文件头，redolog。 
修改步骤如下，摸索着艰难前进，注意以下操作，除非特殊情况下，严禁在生产库操作： 
1、在参数文件中将compatible重设为10.2.0.1.0，重启数据后出现参数文件和控制文件不匹配 

引用

SQL> startup force 
ORACLE instance started. 

Total System Global Area  524288000 bytes 
Fixed Size                  1262716 bytes 
Variable Size             159386500 bytes 
Database Buffers          356515840 bytes 
Redo Buffers                7122944 bytes 
ORA-00201: control file version 10.2.0.2.0 incompatible with ORACLE version 
10.2.0.1.0 
ORA-00202: control file: '/oradata/mynewdb/control01.ctl'

于是尝试修改控制文件，由于控制文件compatible设置是从参数文件获取的，可以通过重建控制文件的方法，将compatible从10.2.0.2.0降为10.2.0.1.0。 
重建控制文件分为noresetlogs和resetlogs两种。 
如果采用noresetlogs方法重建时，需要扫描redolog文件头，由于redolog文件头compatible为10.2.0.2.0，和参数文件compatible版本号不一致。由于控制文件采用resetlogs选项重建时，并不会扫描redolog头，于是我们采用resetlogs选项重建控制文件。 
但是无论采用noresetlogs或者resetlogs选项重建控制文件，重建时都会进行数据文件头匹配，由于数据文件头compatible为10.2.0.2.0 
所以重建控制文件时会出现以下错误： 

引用

SQL> STARTUP NOMOUNT 
CREATE CONTROLFILE REUSE DATABASE "MYNEWDB" RESETLOGS  NOARCHIVELOG 
    MAXLOGFILES 5 
    MAXLOGMEMBERS 5 
    MAXDATAFILES 100 
    MAXINSTANCES 1 
    MAXLOGHISTORY 292 
LOGFILE 
  GROUP 1 '/oradata/mynewdb/redo01.log'  SIZE 100M, 
  GROUP 2 '/oradata/mynewdb/redo02.log'  SIZE 100M, 
  GROUP 3 '/oradata/mynewdb/redo03.log'  SIZE 100M 
-- STANDBY LOGFILE 
DATAFILE 
  '/oradata/mynewdb/system01.dbf', 
  '/oradata/mynewdb/undotbs01.dbf', 
  '/oradata/mynewdb/sysaux01.dbf' 
CHARACTER SET ZHS16GBK 
; 
--ORACLE instance started. 

Total System Global Area  524288000 bytes 
Fixed Size                  1262716 bytes 
Variable Size             167775108 bytes 
Database Buffers          348127232 bytes 
Redo Buffers                7122944 bytes 
SQL>  CREATE CONTROLFILE REUSE DATABASE "MYNEWDB" RESETLOGS  NOARCHIVELOG 
* 
ERROR at line 1: 
ORA-01503: CREATE CONTROLFILE failed 
ORA-01130: database file version 10.2.0.2.0 incompatible with ORACLE version 
10.2.0.1.0 
ORA-01110: data file 1: '/oradata/mynewdb/system01.dbf'

至此，我们只能通过修改数据文件头的compatible来达到降级的目的了 

引用

BBED> find 0x0002200a 
File: /oradata/mynewdb/system01.dbf (1) 
Block: 1                Offsets:   24 to  535           Dba:0x00400001 
------------------------------------------------------------------------ 
0002200a e28bcb0b 4d594e45 57444200 6a000000 80a20000 00200000 01000300 

BBED> modify 0x0001200a 
File: /oradata/mynewdb/system01.dbf (1) 
Block: 1                Offsets:   24 to  535           Dba:0x00400001 
------------------------------------------------------------------------ 
0001200a e28bcb0b 4d594e45 57444200 6a000000 80a20000 00200000 01000300 

BBED> sum apply 
Check value for File 1, Block 1: 
current = 0xf4ba, required = 0xf4ba

修改完所有数据文件之后，用noresetlog重建控制文件，日志文件头和参数文件不匹配如期而至。 

引用

SQL> STARTUP NOMOUNT 
CREATE CONTROLFILE REUSE DATABASE "MYNEWDB" NORESETLOGS  NOARCHIVELOG 
    MAXLOGFILES 5 
    MAXLOGMEMBERS 5 
    MAXDATAFILES 100 
    MAXINSTANCES 1 
    MAXLOGHISTORY 292 
LOGFILE 
  GROUP 1 '/oradata/mynewdb/redo01.log'  SIZE 100M, 
  GROUP 2 '/oradata/mynewdb/redo02.log'  SIZE 100M, 
  GROUP 3 '/oradata/mynewdb/redo03.log'  SIZE 100M 
-- STANDBY LOGFILE 
DATAFILE 
  '/oradata/mynewdb/system01.dbf', 
  '/oradata/mynewdb/undotbs01.dbf', 
  '/oradata/mynewdb/sysaux01.dbf' 
CHARACTER SET ZHS16GBK 
;ORACLE instance started. 

Total System Global Area  524288000 bytes 
Fixed Size                  1262716 bytes 
Variable Size             176163716 bytes 
Database Buffers          339738624 bytes 
Redo Buffers                7122944 bytes 
SQL>  CREATE CONTROLFILE REUSE DATABASE "MYNEWDB" NORESETLOGS  NOARCHIVELOG 
* 
ERROR at line 1: 
ORA-01503: CREATE CONTROLFILE failed 
ORA-00331: log version 0.0.0.0.0 incompatible with ORACLE version 10.2.0.1.0 
ORA-01517: log member: '/oradata/mynewdb/redo01.log'

于是我们采用reselogs选项重建控制文件，终于成功 

引用

SQL> CREATE CONTROLFILE REUSE DATABASE "MYNEWDB" RESETLOGS  NOARCHIVELOG 
  2      MAXLOGFILES 5 
  3      MAXLOGMEMBERS 5 
  4      MAXDATAFILES 100 
  5      MAXINSTANCES 1 
  6      MAXLOGHISTORY 292 
  7  LOGFILE 
  8    GROUP 1 '/oradata/mynewdb/redo01.log'  SIZE 100M, 
  9    GROUP 2 '/oradata/mynewdb/redo02.log'  SIZE 100M, 
10    GROUP 3 '/oradata/mynewdb/redo03.log'  SIZE 100M 
11  -- STANDBY LOGFILE 
12  DATAFILE 
13    '/oradata/mynewdb/system01.dbf', 
14    '/oradata/mynewdb/undotbs01.dbf', 
15    '/oradata/mynewdb/sysaux01.dbf' 
16  CHARACTER SET ZHS16GBK 
17  ; 

Control file created.

重建好控制文件后，在open resetlogs时提示需要recover，redolog文件头的版本号依然为10.2.0.2.0，于是recover出现了问题 

引用

SQL> alter database open resetlogs; 
alter database open resetlogs 
* 
ERROR at line 1: 
ORA-01113: file 1 needs media recovery 
ORA-01110: data file 1: '/oradata/mynewdb/system01.dbf' 


SQL> recover database using backup controlfile; 
ORA-00279: change 823455 generated at 05/13/2011 12:05:45 needed for thread 1 
ORA-00289: suggestion : 
/ora10g/oracle/product/10.2.0/db_1/dbs/arch1_11_748203362.dbf 
ORA-00280: change 823455 for thread 1 is in sequence #11 


Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 
/oradata/mynewdb/redo01.log 
ORA-00331: log version 10.2.0.2.0 incompatible with ORACLE version 10.2.0.1.0 
ORA-00334: archived log: '/oradata/mynewdb/redo01.log'

出现这个错误之后，其实修复也很简单，只要再次通过bbed修复redolog文件头即可。 

引用

BBED> dump offset 0 
File: /oradata/mynewdb/redo01.log (0) 
Block: 1                Offsets:    0 to  511           Dba:0x00000000 
------------------------------------------------------------------------ 
01220000 01000000 0a000000 00809db0 00000000 0002200a e28bcb0b 4d594e45 

BBED> find 0x0002200a 
File: /oradata/mynewdb/redo01.log (0) 
Block: 1                Offsets:   20 to  511           Dba:0x00000000 
------------------------------------------------------------------------ 
0002200a e28bcb0b 4d594e45 57444200 69000000 00200300 00020000 01000200

但由于bbed工具不会再次计算redolog的checksum值，所以修改之后该日志文件头也处于了checksum error状态 

引用

BBED> sum apply 
Check value for File 0, Block 1: 
current = 0x0000, required = 0x0000 

SQL> recover database using backup controlfile; 
ORA-00279: change 823455 generated at 05/13/2011 12:05:45 needed for thread 1 
ORA-00289: suggestion : 
/ora10g/oracle/product/10.2.0/db_1/dbs/arch1_11_748203362.dbf 
ORA-00280: change 823455 for thread 1 is in sequence #11 


Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 
/oradata/mynewdb/redo01.log 
ORA-00367: checksum error in log file header 
ORA-00334: archived log: '/oradata/mynewdb/redo01.log'

其实Oracle 对checkvalue值的计算只是各个字节相对简单的与或运算，由于手头没有现成脚本，于是打算放弃使用该redolog，强制启用Oracle。 
启用隐含参数_allow_resetlogs_corruption 

引用

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; 

System altered. 

SQL> startup force mount 
ORACLE instance started. 

Total System Global Area  524288000 bytes 
Fixed Size                  1262716 bytes 
Variable Size             180358020 bytes 
Database Buffers          335544320 bytes 
Redo Buffers                7122944 bytes 
Database mounted. 
SQL> recover database using backup controlfile until cancel; 
ORA-00279: change 823455 generated at 05/13/2011 12:05:45 needed for thread 1 
ORA-00289: suggestion : /ora10g/oracle/product/10.2.0/db_1/dbs/arch1_11_748203362.dbf 
ORA-00280: change 823455 for thread 1 is in sequence #11 


Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 
cancel 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below 
ORA-01194: file 1 needs more recovery to be consistent 
ORA-01110: data file 1: '/oradata/mynewdb/system01.dbf' 


ORA-01112: media recovery not started

用reselogs选项打开Oracle。 

引用

SQL> alter database open resetlogs; 
alter database open resetlogs 
* 
ERROR at line 1: 
ORA-01092: ORACLE instance terminated. Disconnection forced

实例异常终止，打开后台alert日志一看，2662错误如期而至 

引用

Fri May 13 12:29:00 2011 
Errors in file /ora10g/oracle/product/10.2.0/db_1/rdbms/log/mynewdb_ora_22033.trc: 
ORA-00600: internal error code, arguments: [2662], [0], [823461], [0], [823504], [4194313], [], [] 
Fri May 13 12:29:01 2011 
Errors in file /ora10g/oracle/product/10.2.0/db_1/rdbms/log/mynewdb_ora_22033.trc: 
ORA-00600: internal error code, arguments: [2662], [0], [823461], [0], [823504], [4194313], [], [] 
Fri May 13 12:29:01 2011 
Error 600 happened during db open, shutting down database 
USER: terminating instance due to error 600 
Instance terminated by USER, pid = 22033 
ORA-1092 signalled during: alter database open resetlogs..

ORA-600 [2662]主要是由于Oracle内部block的scn大于当前打开数据库的scn而引起的，主要有5个参数： 

引用

ERROR:              
  ORA-600 [2662] [a] [b] [c] [d] [e] 
VERSIONS: 
  versions 6.0 to 10.1 
DESCRIPTION: 
  A data block SCN is ahead of the current SCN. 
  The ORA-600 [2662] occurs when an SCN is compared to the dependent SCN 
  stored in a UGA variable. 
  If the SCN is less than the dependent SCN then we signal the ORA-600 [2662] 
  internal error. 
ARGUMENTS: 
  Arg [a]  Current SCN WRAP 
  Arg [b]  Current SCN BASE 
  Arg [c]  dependent SCN WRAP 
  Arg [d]  dependent SCN BASE 
  Arg [e]  Where present this is the DBA where the dependent SCN came from.

一般情况下，产生ora-600 [2662],可以通过设置event 10015事件，手工递增scn，即数据库open时的scn。 
event 10015事件设置需要针对每个场景计算出level 1，通过2662几个参数根据一定的规则可以计算出我们需要的level。 
计算规则如下： 
Arg [c]*4得出一个数值，假设为V_Wrap 
如果Arg [d]=0，则V_Wrap值为需要的level 
Arg [d] < 1073741824，V_Wrap+1为需要的level 
Arg [d] < 2147483648，V_Wrap+2为需要的level 
Arg [d] < 3221225472，V_Wrap+3为需要的level 
本案例中[c]=0，所以level为0*4+1=1，即在数据mount状态下，设置如下参数即可 

引用

SQL> ALTER SESSION SET EVENTS '10015 TRACE NAME ADJUST_SCN LEVEL 1'; 

Session altered.

但Oracle并不识别此event的设置，即并不递增scn值，在打开时，数据库依然报错 

引用

Fri May 13 12:45:09 2011 
SMON: enabling cache recovery 
Fri May 13 12:45:09 2011 
Errors in file /ora10g/oracle/product/10.2.0/db_1/rdbms/log/mynewdb_ora_25610.trc: 
ORA-00600: internal error code, arguments: [2662], [0], [823467], [0], [823504], [4194313], [], [] 
Fri May 13 12:45:09 2011 
Errors in file /ora10g/oracle/product/10.2.0/db_1/rdbms/log/mynewdb_ora_25610.trc: 
ORA-00600: internal error code, arguments: [2662], [0], [823467], [0], [823504], [4194313], [], [] 
Fri May 13 12:45:09 2011

也是尝试采用隐含参数_minimum_giga_scn，其取值方法和10015 event相同 

引用

SQL> alter system set "_minimum_giga_scn"=1 scope=spfile; 

System altered.

再次尝试打开数据终于成功 

引用

SQL> recover database using backup controlfile until cancel; 
ORA-00279: change 823463 generated at 05/13/2011 12:45:09 needed for thread 1 
ORA-00289: suggestion : 
/ora10g/oracle/product/10.2.0/db_1/dbs/arch1_1_751034707.dbf 
ORA-00280: change 823463 for thread 1 is in sequence #1 


Specify log: {<RET>=suggested | filename | AUTO | CANCEL} 
cancel 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below 
ORA-01194: file 1 needs more recovery to be consistent 
ORA-01110: data file 1: '/oradata/mynewdb/system01.dbf' 


ORA-01112: media recovery not started 

SQL>  alter database open resetlogs; 

Database altered.

后台alert日志显示，scn已经递增成功。 

引用

Fri May 13 12:47:37 2011 
alter database open resetlogs 
Fri May 13 12:47:37 2011 
RESETLOGS is being done without consistancy checks. This may result 
in a corrupted database. The database should be recreated. 
RESETLOGS after incomplete recovery UNTIL CHANGE 823463 
Resetting resetlogs activation ID 200680610 (0xbf624a2) 
Online log /oradata/mynewdb/redo01.log: Thread 1 Group 1 was previously cleared 
Online log /oradata/mynewdb/redo02.log: Thread 1 Group 2 was previously cleared 
Fri May 13 12:47:39 2011 
Setting recovery target incarnation to 6 
Fri May 13 12:47:39 2011 

引用

Advancing SCN to 1073741824 according to _minimum_giga_scn

Fri May 13 12:47:39 2011 
Assigning activation ID 200679734 (0xbf62136) 
Thread 1 opened at log sequence 1 
  Current log# 3 seq# 1 mem# 0: /oradata/mynewdb/redo03.log 
Successful open of redo thread 1 
Fri May 13 12:47:39 2011 
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set 
Fri May 13 12:47:39 2011 
SMON: enabling cache recovery 
Fri May 13 12:47:39 2011 
Successfully onlined Undo Tablespace 1. 
Dictionary check beginning 
Tablespace 'TEMPTS1' #3 found in data dictionary, 
but not in the controlfile. Adding to controlfile. 
Dictionary check complete 
Fri May 13 12:47:39 2011 
SMON: enabling tx recovery 
Fri May 13 12:47:39 2011 
********************************************************************* 
WARNING: The following temporary tablespaces contain no files. 
         This condition can occur when a backup controlfile has 
         been restored.  It may be necessary to add files to these 
         tablespaces.  That can be done using the SQL statement: 

         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE 

         Alternatively, if these temporary tablespaces are no longer 
         needed, then they can be dropped. 
           Empty temporary tablespace: TEMPTS1 
********************************************************************* 
Database Characterset is ZHS16GBK 
replication_dependency_tracking turned off (no async multimaster replication found) 
Starting background process QMNC 
QMNC started with pid=16, OS id=401 
Fri May 13 12:47:39 2011 
LOGSTDBY: Validating controlfile with logical metadata 
Fri May 13 12:47:39 2011 
LOGSTDBY: Validation complete 
Completed:  alter database open resetlogs

可以看到数据库已经成功降级 

引用

SQL> show parameter compatible 

NAME                                 TYPE        VALUE 
------------------------------------ ----------- ------------------------------ 
compatible                           string      10.2.0.1.0