We have already upgraded Oracle Database11gR2 to 12c, the whole process is still relatively smooth, although some of the previous version of the upgrade is not the same, but the overall difficulty is not too large, because of too many different features, greatly extended the upgrade time.

But for Oracle 12c back to the previous version, there will be some restrictions, only downgrade to the version and patch level used before the upgrade.

  • If we are upgrading directly from 10.2.0.5 , 11.1.0.7 or 11.2.0.2 and above. Although it is possible to upgrade 10.2.0.5 directly, downgrade does not apply to 10.2.0.5 .
  • If any patches are applied to the source database that is running from the upgraded home directory, you need to roll back before you start the downgrade process.

Firstly, start the inspection before the downgrade operation
Ensure that all components are in a normal state before upgrading without invalid components.

Check the state of each component

SQL> select substr (comp_id, 1,15) comp_id, substr (comp_name, 1,30) comp_name, substr (version, 1,10) version, status from dba_registry;

COMP_ID COMP_NAME VERSION STATUS
---------- ------------------------------ ---------- --------
DV Oracle Database Vault 12.1.0.1.0 VALID
APEX Oracle Application Express 4.2.0.00.2 VALID
OLS Oracle Label Security 12.1.0.1.0 VALID
SDO Spatial 12.1.0.1.0 VALID
ORDIM Oracle Multimedia 12.1.0.1.0 VALID
CONTEXT Oracle Text 12.1.0.1.0 VALID
OWM Oracle Workspace Manager 12.1.0.1.0 VALID
XDB Oracle XML Database 12.1.0.1.0 VALID
CATALOG Oracle Database Catalog Views 12.1.0.1.0 VALID
CATPROC Oracle Database Packages and T 12.1.0.1.0 VALID
JAVAVM JServer JAVA Virtual Machine 12.1.0.1.0 VALID XML Oracle XDK 12.1.0.1.0 VALID
CATJAVA Oracle Database Java Packages 12.1.0.1.0 VALID
APS OLAP Analytic Workspace 12.1.0.1.0 VALID
XOQ Oracle OLAP API 12.1.0.1.0 VALID
RAC Oracle Real Application Cluste 12.1.0.1.0 OPTION OFF
16 rows selected.

Check invalid components

SQL> select owner, count (object_name) "Invalid object count" from dba_objects where status! = 'VALID' and owner in ('SYS', 'SYSTEM') group by owner;

no rows selected

Second, began to implement downgrade operations

  • Shut down the database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
  • Start the database to downgrade mode
SQL> startup downgrade;
ORACLE instance started.

Total System Global Area 5027385344 bytes
Fixed Size 2298736 bytes
Variable Size 1040190608 bytes
Database Buffers 3976200192 bytes
Redo Buffers 8695808 bytes
Database mounted.
Database opened.
  • Run the downgrade script
SQL> spool downgrade_12c_to_11g.log
SQL> @ $ORACLE_HOME/rdbms/admin/catdwgrd.sql
.........
PL / SQL procedure successfully completed.
SQL>
SQL> - lrg 7259350: objects created in this script need their flags that will not fit
SQL> - in a ub2 cleared
SQL> update obj $ mig set flags = bitand (flags, 65535) where flags> 65535;

222 rows updated.

SQL>
SQL> / *********************************************** ****************************** /
SQL> / * Step 9 - Swap bootstrap $ mig with bootstrap $
SQL> * /
SQL> / *********************************************** ****************************** /
SQL> / * According to JKLEIN, performing 3 count (*) will ensure there are
SQL> no dirty itl's present in bootstrap $. * /
SQL> select count (*) from bootstrap $;

COUNT (*)
----------
60

1 row selected.

SQL> select count (*) from bootstrap $;

COUNT (*)
----------
60

1 row selected.

SQL> select count (*) from bootstrap $;

COUNT (*)
----------
60

1 row selected.

SQL> select count (*) from bootstrap $ mig;

COUNT (*)
----------
60

1 row selected.

SQL> select count (*) from bootstrap $ mig;

COUNT (*)
----------
60

1 row selected.

SQL> select count (*) from bootstrap $ mig;

COUNT (*)
----------
60

1 row selected.

SQL>
SQL> WHENEVER SQLERROR CONTINUE
SQL>
SQL> begin
2
3 - Now we can do the swap.
4 declare
5 procedure swap_bootstrap (replacement_tbl_name IN VARCHAR2)
6 is language c library DBMS_DDL_INTERNAL_LIB
7 name "swap_bootstrap"
8 with context
9 parameters (context, replacement_tbl_name String,
10 replacement_tbl_name LENGTH ub2,
11 replacement_tbl_name INDICATOR sb2);
12 begin
13 swap_bootstrap ('BOOTSTRAP $ MIG');
14 end;
15
16 - We've completed the swap.
17 - Remove the BOOTSTRAP_DOWNGRADE_ERROR entry in props $.
18 delete from props $ where name = 'BOOTSTRAP_DOWNGRADE_ERROR';
19 commit;
20 end;
twenty one /

PL / SQL procedure successfully completed.

SQL>
SQL> Rem *********************************************** ************************
SQL> Rem END catdwgrd.sql
SQL> Rem *********************************************** ************************
SQL>
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Now modify /etc/oratab for Oracle 11gR2 HOME directory

[root@host ~] # vi /etc/oratab
orcl: /u01/oracle/product/11.2.0.4/dbhome_1

Additionally, modify the system environment variables

[root @ host oracle] # vi ~/.bash_profile
[oracle @ host ~] $ echo $ ORACLE_HOME
/u01/oracle/product/11.2.0.4/dbhome_1

After that start to upgrade mode, execute the catrelod script from the downgraded version $ORACLE_HOME/rdbms/admin directory.

[oracle @ host~] $ sqlplus / as sysdba

SQL * Plus: Release 11.2.0.4.0 Production on Tue Dec 23 18:13:17 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 5027385344 bytes
Fixed Size 2261888 bytes
Variable Size 1191185536 bytes
Database Buffers 3825205248 bytes
Redo Buffers 8732672 bytes
Database mounted.
Database opened.

SQL> $ORACLE_HOME/rdbms/admin/catrelod.sql

Now check the registry components.

SQL>SELECT comp_name, status, substr (version, 1,10) as version from dba_server_registry order by modified
COMP_NAME                           STATUS                                       VERSION  
----------------------------------- ---------------------------                  -----------------
Oracle Workspace Manager            VALID                                        11.2.0.4.0  
OLAP Analytic Workspace             VALID                                        11.2.0.4.0  
Spatial                             INVALID                                      11.2.0.4.0  
Oracle Multimedia                   VALID                                        11.2.0.4.0  
Oracle XML Database                 VALID                                        11.2.0.4.0  
Oracle Text                         VALID                                        11.2.0.4.0  
Oracle OLAP API                     VALID                                        11.2.0.4.0  
Oracle Database Catalog Views       INVALID                                      11.2.0.4.0  
Oracle Database Packages and Types  VALID                                        11.2.0.4.0  
JServer JAVA Virtual Machine        VALID                                        11.2.0.4.0  
Oracle XDK                          VALID                                        11.2.0.4.0  
Oracle Database Java Packages       VALID                                        11.2.0.4.0  
Oracle Application Express          INVALID                                      3.2.1.00.1  
OWB                                 VALID                                        11.2.0.4.0  

Now execute utlrp.sql script Recompile all existing PL/SQL modules in the INVALID state

SQL> @ $ ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> Rem
SQL> Rem $ Header: utlrp.sql 24-jul-2003.10: 06: 51 gviswana Exp $
SQL> Rem
SQL> Rem utlrp.sql
SQL> Rem
SQL> Rem Copyright (c) 1998, 2003, Oracle Corporation.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlrp.sql - Recompile invalid objects
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem When run as one of the last steps during upgrade or downgrade,
SQL> Rem this script will validate all remaining invalid objects. It will
SQL> Rem also run a component validation procedure for each component in
SQL> Rem the database. See the README notes for your current release and
SQL> Rem the Oracle Database Upgrade book for more information about
SQL> Rem using utlrp.sql
SQL> Rem
SQL> Rem Although invalid objects are automatically re-validated when used,
SQL> Rem it is useful to run this script after an upgrade or downgrade and
SQL> Rem after applying a patch. This minimizes latencies caused by
SQL> Rem on-demand recompilation
SQL> Rem script after upgrades, downgrades and patches.
SQL> Rem
SQL> Rem NOTES
SQL> Rem * This script must be run using SQL * PLUS.
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem * There should be no other DDL on the database while running the
SQL> Rem script. Not following this recommendation may lead to deadlocks.

<<<<Output Truncated>>>>

... Setting DBMS Registry 18:09:01
... Setting DBMS Registry Complete 18:09:01
... Exiting validate 18:09:01

PL / SQL procedure successfully completed.

SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem =============================================== ============================
SQL> Rem END utlrp.sql
SQL> Rem =============================================== ============================

After executing the above script before making a check, Now there is no invalid objects

SQL> set line 300
SQL>SELECT comp_name, status, substr (version, 1,10) as version from dba_server_registry order by modified

COMP_NAME                           STATUS                                       VERSION  
----------------------------------- ---------------------------                  -----------------
Oracle Workspace Manager            VALID                                        11.2.0.4.0  
OLAP Analytic Workspace             VALID                                        11.2.0.4.0  
Spatial                             INVALID                                      11.2.0.4.0  
Oracle Multimedia                   VALID                                        11.2.0.4.0  
Oracle XML Database                 VALID                                        11.2.0.4.0  
Oracle Text                         VALID                                        11.2.0.4.0  
Oracle OLAP API                     VALID                                        11.2.0.4.0  
Oracle Database Catalog Views       VALID                                        11.2.0.4.0  
Oracle Database Packages and Types  VALID                                        11.2.0.4.0  
JServer JAVA Virtual Machine        VALID                                        11.2.0.4.0  
Oracle XDK                          VALID                                        11.2.0.4.0  
Oracle Database Java Packages       VALID                                        11.2.0.4.0  
Oracle Application Express          VALID                                        3.2.1.00.1  
OWB                                 VALID                                        11.2.0.4.0  

14 rows selected.

Lastly, Restart the database.

[oracle@host~] $ sqlplus / as sysdba
SQL * Plus: Release 11.2.0.4.0 Production on Sun Dec 21 22:13:32 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 5027385344 bytes
Fixed Size 2261888 bytes
Variable Size 1107299456 bytes
Database Buffers 3909091328 bytes
Redo Buffers 8732672 bytes
Database mounted.
Database open.

SQL>

Stay Tuned for Downgrade the Oracle RAC Database.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Comments

  1. saurabhjsshukla

    I believe you downgraded the database when its compatible parameter was still pointing to 11.2.0.4 after upgrade to 12.1.0.2, what if Compatible initialization parameter is set to 12.1.0.2 after upgrade, what are options to downgrade a 1.5TB size of database.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.