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
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.
You can set the value according to the version