Downgrade the Oracle Database
In Oracle You have option to downgrade the Oracle database. Suppose you upgraded the Oracle database from 11g to 12c database now you want to downgrade.
but if you change the compatibility parameter of your oracle database from 11.2.0 to 12.1.0 then you cannot able to downgrade. only method is logical backup expdp or impdp.
If your database compatibility is same lower than 12.1.0 then you can follows the downgrade steps of the database.
Note:
1. If you are downgrading to Oracle Database 11g Release 2 (11.2.0.2),(11.2.0.3) or (11.2.0.4), then the COMPATIBLE initialization parameter must be set to 11.2.0 or lower.
2. Downgrading to release 11.2.0.1 is not supported.
3. Downgrading to Oracle Database 11g Release 1 (11.1.0.7), then the COMPATIBLE initialization parameter must be set to 11.1.0, its supported.
Following are the steps in three phases
Pre-Downgrade Steps
DOWNGRADE Steps
POSTDOWNGRADE STEPS
Following are the Pre-Downgrade Steps
1. Take a full backup of Oracle Database before downgrading.
Pre-Downgrade Steps for Oracle Database
2. If you are downgrade the CDB or any PDB in oracle 12.1.0.2, you need to apply PSU4 patch. (MOS Note 756671.1)
3. If you are downgrading to Oracle 11.1, took backup of the password of database link.
Note: its better to take the database link in all cases
-- Export database link
expdp system/manager FULL=Y directory=dpump_dir dumpfile=saved_dblinks.dmp INCLUDE=DB_LINK;
— Import database link
impdp system/manager directory=dpump_dir dumpfile=saved_dblinks.dmp;
4. Disable the Oracle Database vault service if its enabled.
Disable the Oracle Database vault
--Connect with DB owner
sqlplus dv_owner
-- Disable the database vault
EXEC DBMS_MACADM.DISABLE_DV;
--restart the database
shutdown immediate
startup
Enable the database vault
CONNECT dv_owner
EXEC DBMS_MACADM.ENABLE_DV;
–Label security must be enabled of database vault.
SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Label Security’;
–Enable label secuirty
EXEC LBACSYS.CONFIGURE_OLS;
EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;
— Restart the database
shutdown immediate;
startup
5. If your database uses Oracle Label Security
If downgraded to 11.2 or older then run the downgrade script
-- Check its label security is enabled
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
-- if yes then run the following script in case of downgrade to 11.2 or older version.
@ORACLE_HOME/rdbms/admin/olspredowngrade.sql
Note: if you are downgrading release 12.1.0.2 to release 12.1.0.1. then donot do anything
6. If you already enabled unified auditing.
--backup the unified table
CREATE TABLE UA_DATA AS (SELECT * FROM V$UNIFIED_AUDIT_TRAIL);
–clean the table
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);
7. Check the timezone
-- Check the timezone of both the databases. if it matched then fine otherwise you need to copy the time zone from upper to lower version.
select * from V$TIMEZONE_FILE;
-- Copy the time zone files from the current Oracle home to the Orache home to which you are downgrading.
%ORACLE_HOME%\oracore\zoneinfo\timezlrg_18.dat
%ORACLE_HOME%\oracore\zoneinfo\timezone_18.dat
DROP USER sysman CASCADE;
Following are the Downgrading a CDB or Non-CDB Oracle Database Steps
Suppose downgrade the oracle 12c latest version to 12.1.0.1
1. Log in to the system with new oracle home.
2. Go to the directory ORACLE_HOME/rdbms/admin
cd %ORACLE_HOME%\rdbms\admin
sqlplus / as sysdba
3. Start the instance in downgrade mode.
-- For Non CDB
startup downgrade pfile=pfile_name
-- For CDB
startup downgrade pfile=pfile_name
alter pluggable database all open downgrade;
4. Run catdwgrd.sql script for downgrade for CDB or Non CDB database.
-- For Non CDB
SPOOL downgrade.log
SQL> @catdwgrd.sql
spool off
— For a CDB:
ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -d $ORACLE_HOME/rdbms/admin -e -b catdwgrd -l -r catdwgrd.sql
In CDB example catcon.pl is run with catdwgrd.
-d parameter tells catcon.pl where to find catdwgrd.
-l parameter specifies the output directory for log files
-r parameter to run the two scripts together.
5. Shutdown the database after executing catdwgrd.sql script
Shutdown immediate
6. Change the environment variable to old oracle home which you downgraded the database.
ORACLE_HOME
PATH
7. In case of windows system you need to drop the exisiting service and create new from downgraded home of database
-- Stop the ORCL database service
NET STOP OracleServiceORCL
-- Drop the ORCL service
ORADIM -DELETE -SID ORCL
-- Create new ORCL Service
ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS -STARTMODE MANUAL -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
8. Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges.
-- For Non CDB
CONNECT / AS SYSDBA
STARTUP UPGRADE
–For CDB
connect / as sysdba
startup database mount;
alter database open upgrade;
alter pluggable database all open upgrade;
9. Go to ORACLE_HOME/rdbms/admin and connect with database
--For a non-CDB:
SPOOL reload.log
@catrelod.sql
SPOOL off
— For CDB
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b catrelod -d ”’.”’ catrelod.sql
10. If you downgrading to Oracle Database 11g Release 1 (11.1.0.7) then you run the @apxrelod.sql script.
@apxrelod.sql
Note: Script used for reload Oracle Application Express.
11. Shut down and restart the instance for normal operation:
SHUTDOWN IMMEDIATE
STARTUP
12. IF Oracle Label Security is configured then copy the script olstrig.sql from upper version oracle home to new downgraded oracle home and run it.
@olstrig.sql
Note: Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies.
13. Run the utlrp.sql script
-- For a non-CDB:
@utlrp.sql;
— For CDB
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d ”’.”’ utlrp.sql
POSTDOWNGRADE STEPS
1. Recreate the listener from downgraded Oracle home.
Note: Copy paste the network folder from old home to downgrade home.
NETCA
2. If enabled then Re-enabling Oracle Database Vault after Downgrading Oracle Database.
Enable the database vault
CONNECT dv_owner
EXEC DBMS_MACADM.ENABLE_DV;
--Label security must be enabled of database vault.
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
--Enable label secuirty
EXEC LBACSYS.CONFIGURE_OLS;
EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;
-- Restart the database
shutdown immediate;
startup
3. If configured then Restoring Oracle Enterprise Manager after Downgrading Oracle Database.
ORACLE_HOME/bin/emca -restore db
Smart way of Technology 