Upgrade/Downgrade 10gR2 <=> 11gR2

After I tested upgrade Oracle Database 10gR2 to 11gR2 by command-line. I just curious to downgrade Oracle Database 11gR2 to 10gR2. One thing, If we need to downgrade Database, don't change "compatible" parameter.

10G ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
11G ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

Step: Upgrade Oracle Database 10.2.0.5 to 11.2.0.2

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl

- Check "compatible" parameter

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.5.0

- Check Oracle Version

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

- Check by pre-upgrade script

SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 12-29-2010 17:29:14
Script Version: 11.2.0.2.0 Build: 001.
**********************************************************************
Database:
**********************************************************************
--> name: ORCL
--> version: 10.2.0.5.0
--> compatible: 10.2.0.5.0
--> blocksize: 8192
--> platform: Linux x86 64-bit
--> timezone file: V4.
.
.
.
--> 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
--> EM Repository [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
.
.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'

Trace Events:
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.
**********************************************************************

- Gather stats and truncate AUD$ table

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

SQL> truncate table SYS.AUD$ drop storage;

- Start to upgrade by "catupgrd.sql" script

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

$ cp /u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
$ cp /u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
$ /u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlplus /nolog

SQL> connect / as sysdba
Connected to an idle instance.
SQL>> startup upgrade;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 730714112 bytes
Fixed Size 2230080 bytes
Variable Size 205523136 bytes
Database Buffers 515899392 bytes
Redo Buffers 7061504 bytes
Database mounted.
Database opened.

SQL> spool upgrade.log
SQL> set echo on
SQL> set termout on
SQL> @?/rdbms/admin/catupgrd.sql
.
.
.
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> Rem *********************************************************************
SQL> Rem END catupgrd.sql
SQL> Rem *********************************************************************

After run "catupgrd.sql", Instance should shutdown
- Step to run "catuppst.sql" script(This is post upgrade script: only necessary when upgrading from ≥ 10.1)

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 730714112 bytes
Fixed Size 2230080 bytes
Variable Size 360712384 bytes
Database Buffers 360710144 bytes
Redo Buffers 7061504 bytes
Database mounted.
Database opened.

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.5.0

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> @?/rdbms/admin/catuppst.sql

- Generate fixed object stats

SQL> exec dbms_stats.gather_fixed_objects_stats;

PL/SQL procedure successfully completed.

- Recompile

SQL> @?/rdbms/admin/utlrp.sql

- Run post-Upgrade script

SQL> @?/rdbms/admin/utlu112s.sql

- Compare invalid objects scripts

SQL> @?/rdbms/admin/utluiobj.sql

- Adjust time zone data

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 939495424 bytes
Fixed Size 2232088 bytes
Variable Size 251658472 bytes
Database Buffers 679477248 bytes
Redo Buffers 6127616 bytes
Database mounted.
Database opened.

SQL> exec dbms_dst.begin_upgrade(new_version => 11);

PL/SQL procedure successfully completed.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 939495424 bytes
Fixed Size 2232088 bytes
Variable Size 251658472 bytes
Database Buffers 679477248 bytes
Redo Buffers 6127616 bytes
Database mounted.
Database opened.

SQL> declare num_of_failures number;
begin
dbms_dst.upgrade_database(num_of_failures);
dbms_output.put_line(num_of_failures);
dbms_dst.end_upgrade(num_of_failures);
dbms_output.put_line(num_of_failures);
end;
/

- Check Oracle database after Upgrade

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.5.0

Need to Downgrade !!! Don't change "compatible" parameter

Step: Downgrade Oracle Database 11.2.0.2 to 10.2.0.5

- Check Oracle version and "compatible" parameter

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.5.0

-- Make sure "compatible" parameter is not changed.
- Start Oracle database for downgrade (11gR2 Home)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP DOWNGRADE
ORACLE instance started.

Total System Global Area 939495424 bytes
Fixed Size 2232088 bytes
Variable Size 251658472 bytes
Database Buffers 679477248 bytes
Redo Buffers 6127616 bytes
Database mounted.
Database opened.

- If use EM, then drop EM user

SQL> DROP USER sysman CASCADE;

User dropped.

- Run "catdwgrd.sql" script to downgrade

SQL> SPOOL downgrade.log
SQL> @?/rdbms/admin/catdwgrd.sql
.
.
.
SQL> Rem ***********************************************************************
SQL> Rem END catdwgrd.sql
SQL> Rem ***********************************************************************

- Shutdown database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

- Start Oracle database from 10gR2 Home and use "sqlplus" from 10gR2

$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
$ /u01/app/oracle/product/10.2.0/db_1/bin/sqlplus /nolog

SQL> connect / as sysdba
Connected to an idle instance.

SQL> STARTUP UPGRADE
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 2099136 bytes
Variable Size 192940096 bytes
Database Buffers 532676608 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

- Run "catrelod.sql" script reloads the appropriate version of all of the database components in the downgraded database

SQL> SPOOL reload.log

SQL> @?/rdbms/admin/catrelod.sql
.
.
.
COMP_NAME STATUS VERSION
----------------------------------- ----------- ----------
Oracle Database Packages and Types VALID 10.2.0.5.0
Oracle Database Catalog Views VALID 10.2.0.5.0
JServer JAVA Virtual Machine VALID 10.2.0.5.0
Oracle XDK VALID 10.2.0.5.0
Oracle Database Java Packages VALID 10.2.0.5.0
Oracle Text VALID 10.2.0.5.0
Oracle XML Database VALID 10.2.0.5.0
Oracle Workspace Manager VALID 10.2.0.5.0
Oracle Data Mining VALID 10.2.0.5.0
OLAP Analytic Workspace VALID 10.2.0.5.0
OLAP Catalog VALID 10.2.0.5.0
Oracle OLAP API VALID 10.2.0.5.0
Oracle interMedia VALID 10.2.0.5.0
.
.
.

SQL> SPOOL OFF

- Shutdown & Startup database and then recompile

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 734003200 bytes
Fixed Size 2099136 bytes
Variable Size 247466048 bytes
Database Buffers 478150656 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.

SQL> @?/rdbms/admin/utlrp.sql

- Check Oracle version

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 10.2.0.5.0

Now, my database was downgraded to 10.2.0.5, we can read more "Downgrading Database"