轉：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
Successful mount of redo thread 1, with mount id 200680975
Database mounted in Exclusive Mode
Completed: alter database mount
Fri May 13 11:59:32 2011
alter database open
Switching redo format version from 10.2.0.0.0 to 10.2.0.2.0 at change 803371
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.
Variable Size 167775108 bytes
Database Buffers 348127232 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
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
0001200a e28bcb0b 4d594e45 57444200 6a000000 80a20000 00200000 01000300
BBED> sum apply
Check value for File 1, Block 1:
current = 0xf4ba, required = 0xf4ba
修改完所有資料檔案之後，用noresetlog重建控制檔案，日志檔案頭和參數檔案不比對如期而至。
CREATE CONTROLFILE REUSE DATABASE "MYNEWDB" NORESETLOGS NOARCHIVELOG
;ORACLE instance started.
Variable Size 176163716 bytes
Database Buffers 339738624 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "MYNEWDB" NORESETLOGS NOARCHIVELOG
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
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
Block: 1 Offsets: 20 to 511 Dba:0x00000000
0002200a e28bcb0b 4d594e45 57444200 69000000 00200300 00020000 01000200
但由于bbed工具不會再次計算redolog的checksum值，是以修改之後該日志檔案頭也處于了checksum error狀态
Check value for File 0, Block 1:
current = 0x0000, required = 0x0000
ORA-00367: checksum error in log file header
其實Oracle 對checkvalue值的計算隻是各個位元組相對簡單的與或運算，由于手頭沒有現成腳本，于是打算放棄使用該redolog，強制啟用Oracle。
啟用隐含參數_allow_resetlogs_corruption
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> startup force mount
Variable Size 180358020 bytes
Database Buffers 335544320 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00289: suggestion : /ora10g/oracle/product/10.2.0/db_1/dbs/arch1_11_748203362.dbf
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01112: media recovery not started
用reselogs選項打開Oracle。
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
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
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;
再次嘗試打開資料終于成功
ORA-00279: change 823463 generated at 05/13/2011 12:45:09 needed for thread 1
/ora10g/oracle/product/10.2.0/db_1/dbs/arch1_1_751034707.dbf
ORA-00280: change 823463 for thread 1 is in sequence #1
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
背景alert日志顯示，scn已經遞增成功。
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
Advancing SCN to 1073741824 according to _minimum_giga_scn
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
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
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
SMON: enabling tx recovery
*********************************************************************
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
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open resetlogs
可以看到資料庫已經成功降級
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.1.0
本文轉自einyboy部落格園部落格，原文連結：http://www.cnblogs.com/einyboy/p/3200734.html，如需轉載請自行聯系原作者。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)