‘COMPATIBLE’: as promised, a version upgrade procedure.

As I had promised earlier in my previous post, here is a short step-by-step description of upgrade procedure. I omit overall pre-upgrade checking , my goal is to show — when we need to manually change ‘COMPATIBLE’ parameter.

Requirements:

• — 10g DB
• — 11g Oracle home
• — keg of a beer (just kidding 🙂 )

I’ve got both Oracle homes at one PC.

Run pre-upgrade script utlu112i.sql from 11g Oracle home and analyze output. Here is mine:

SQL> @c:\temp\utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 02-02-2012 13:01:40
.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL10G
--> version: 10.2.0.4.0
--> compatible: 10.2.0.3.0
--> blocksize: 8192
--> platform: Microsoft Windows x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 682 MB
.... AUTOEXTEND additional space required: 192 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 462 MB
.... AUTOEXTEND additional space required: 387 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 348 MB
.... AUTOEXTEND additional space required: 78 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 41 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> user_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
--> core_dump_dest 11.1 DEPRECATED replaced by
"diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 11.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.4.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Database contains schemas with stale optimizer statistics.
.... Refer to the Upgrade Guide for instructions to update
.... schema statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... OLAPSYS
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the Upgrade Guide for instructions to configure Network ACLs.
WARNING:--> recycle bin in use.
.... Your recycle bin turned on.
.... It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command: PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
.
PL/SQL procedure successfully completed.

As you can see I need to modify pfile and purge recycle bin, but be careful — my DB is for testing purposes and to avoid all possible errors follow Oracle docs!

Shutdown and startup DB with UPGRADE option:

SQL> startup upgrade;

Then run DBUA from 11g Oracle home!

I won’t post screenshots of Database Upgrade Assistant (dbua), it is very simple, follow on-screen instructions and select required DB 😉

At the end of DBUA execution I’ve got the following:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select NAME,VALUE from v$parameter where NAME in ('db_name','compatible');
NAME                 VALUE
-------------------- --------------------
compatible           10.2.0.3.0
db_name              orcl10g

And only now I must set ‘COMPATIBLE’ to 11.2.0.1.0 (I’ve created pfile and manually set):

Connected to an idle instance.
SQL> startup mount pfile='INITorcl10g.ORA';
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size 2175168 bytes
Variable Size 436211520 bytes
Database Buffers 738197504 bytes
Redo Buffers 9269248 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL> create spfile from pfile;
File created.
SQL> select NAME,VALUE from v$parameter where NAME in ('db_name','compatible');
NAME                 VALUE
-------------------- --------------------
compatible           11.2.0.1.0
db_name              orcl10g

And the last test, if database would be available with ‘COMPATIBLE’ immediatly set back to ‘10.2.0.3.0’:

SQL> startup pfile='INITorcl10g.ORA';
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size 2175168 bytes
Variable Size 436211520 bytes
Database Buffers 738197504 bytes
Redo Buffers 9269248 bytes
ORA-00201: control file version 11.2.0.1.0 incompatible with ORACLE version 10.2.0.3.0
ORA-00202: control file: 'C:\TOOLS\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL10G\CONTROL01.CTL'

That’s it, enjoy compatibility and don’t let anybody confuse you!