詳解Oracle升高或降低參數Compatible

今天在看oracle 10g的文檔，注意到Irreversible Compatibility這個說法。

Starting with Oracle Database 10g, the COMPATIBLE initialization parameter becomes irreversible; that is, it cannot be set to a value that is less than a previous value. Therefore, the compatibility of the database can go only forward and never backward.
For example, suppose that you set COMPATIBLE to 10.0.0 and start up the database, then shut down the database to restart with COMPATIBLE set to 9.2.0. During startup, you get an error indicating that the compatible setting cannot be reversed.
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 compatibility was advanced.
Any changes since, are lost.

Consequently, the ALTER DATABASE RESET COMPATIBILITY command is now obsolete.

從10g開始COMPATIBLE 不能由高降低，難道9I可以？將compatiable從9.2.0.5改為9.2.0.1？我測試後果然可以。

以前我一直以為COMPATIBLE 在所有版本(包括patch set)都不能修改為更小的值，以為每次修改compatiable都會去修改所有的datafile header.

先看10g得，改低compatible後，資料庫在mount的時候提是錯誤，參數設定與控制檔案不一致。

SQL> startup
ORACLE instance started.

Total System Global Area 272629760 bytes
Fixed Size 2035592 bytes
Variable Size 201330808 bytes
Database Buffers 62914560 bytes
Redo Buffers 6348800 bytes
ORA-00201: control file version 10.2.0.3.0 incompatible with ORACLE version 10.2.0.1.0
ORA-00202: control file: '/oracle/DDS/data03/lewu/data/cntrl_lewu_1.dbf'

測試將compatiable從9.2.0.5修改為9.2.0.0.0，資料庫重起成功。看日誌.

Starting ORACLE instance (normal)
Starting up ORACLE RDBMS Version: 9.2.0.5.0.
System parameters with non-default values:
compatible = 9.2.0.5

ARCH: STARTING ARCH PROCESSES
ARCH: STARTING ARCH PROCESSES COMPLETE
ALTER DATABASE MOUNT
Completed: ALTER DATABASE MOUNT
Wed Aug 27 20:53:23 2008
ALTER DATABASE OPEN
Completed: ALTER DATABASE OPEN

ALTER DATABASE CLOSE NORMAL
Completed: ALTER DATABASE CLOSE NORMAL

Starting ORACLE instance (normal)
Starting up ORACLE RDBMS Version: 9.2.0.5.0.
System parameters with non-default values:

compatible = 9.2.0.0

ARCH: STARTING ARCH PROCESSES
ARCH: STARTING ARCH PROCESSES COMPLETE
ALTER DATABASE MOUNT
Completed: ALTER DATABASE MOUNT
ALTER DATABASE OPEN
Completed: ALTER DATABASE OPEN

再看看能不能從9.2.0.5改為8.1.7.4，結果失敗。資料庫能夠mount，但無法啟動。看不來不能夠跨大版本。

SQL> startup
ORACLE instance started.

Total System Global Area 689409008 bytes
Fixed Size 732144 bytes
Variable Size 352321536 bytes
Database Buffers 335544320 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-00402: database changes by release 9.2.0.0.0 cannot be used by release 8.1.7.0.0
ORA-00405: compatibility type "Locally Managed SYSTEM tablespace"

oracle@:/export/home/oracle/products/9205/dbs > oerr ora 00402
00402, 00000, "database changes by release %s cannot be used by release %s"
// *Cause: Changes have been made to the database that require a newer
// software release or that violate the compatibility parameters.
// *Action: Use a version of the software that can understand the changes or
// relax the compatibility requirements in the init file.

結論糾正以前的一個誤區:compatible在9i的時候能降能升。10g開始不行了。

 

轉貼: http://yumianfeilong.com/html/2008/09/25/229.html