oraclednaThis post is to demonstrate downgrade Oracle Database from 19c (19.3) to 11g (11.2.0.4) by using dbdowngrade (Non CDB).
1. Check the Compatibility Matrix to downgrade the database.
2. Disable the Oracle Database Vault.
If Oracle Database Vault is enabled, need to disable the Oracle Database Vault before downgrading. Use CDB_DV_STATUS to check the status of Oracle Database Vault.
SQL> SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;
PARAMETER VALUE CON_ID
———————— ——————– ———-
Oracle Database Vault FALSE 0
If the output is TRUE, then Oracle Database Vault is enabled, so we must disable it.
3. If your database uses Oracle Label Security, and you are downgrading to release 11.2, then run the Oracle Label Security (OLS) preprocess downgrade olspredowngrade.sql script in the new Oracle Database 12c Oracle home.
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Oracle Label Security’;
VALUE
—————————————————————-
FALSE
If Oracle Label Security enabled, run the olspredowngrade.sql script:
SQL> @ORACLE_HOME/rdbms/admin/olspredowngrade.sql
Caution:
Run olspredowngrade.sql before you downgrade from Oracle Database 12c to database release 11.2 for databases that use Oracle Label Security and Oracle Database Vault.
4. If Unified Auditing is enabled, take the backup and purge the unified audit trail.
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = ‘Unified Auditing’;
VALUE
——————–
FALSE
If enabled, perform the below steps.
4.a Get the unified audit records
SQL> SELECT COUNT(*) FROM UNIFIED_AUDIT_TRAIL;
4.b Get the unified audit records
SQL> SELECT COUNT(*) FROM UNIFIED_AUDIT_TRAIL;
4.c Backup existing data to backup table.
SQL> CREATE TABLE UA_DATA_BKP AS (SELECT * FROM UNIFIED_AUDIT_TRAIL);
4.d Clean up the audit trail.
SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, use_last_arch_timestamp => FALSE);
5. Before downgrade, check the the version of time zone file.
SQL> col version for 999999999999
select * from V$TIMEZONE_FILE;
FILENAME VERSION CON_ID
——————– ————- ———-
timezlrg_32.dat 32 0
This version of time zone file should exist in Oracle 11g home. If not exists, need to apply patch 28125601 (p28125601_112040_Linux-x86-64).
[oracle@prod db_1]$ cd /u01/app/oracle/product/11.0.0/db_1/oracore/zoneinfo/
[oracle@prod zoneinfo]$ ls -lrt timezlrg_32.dat
ls: cannot access timezlrg_32.dat: No such file or directory
export ORACLE_HOME=/u01/app/oracle/product/11.0.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
===
[oracle@prod ~]$ /u01/app/oracle/product/11.0.0/db_1/OPatch/opatch apply -oh /u01/app/oracle/product/11.0.0/db_1 -local /patch/patches/28125601
Oracle Interim Patch Installer version 11.2.0.3.50
Copyright (c) 2025, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.0.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.0.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.50
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.0.0/db_1/cfgtoollogs/opatch/opatch2025-06-28_10-16-14AM_1.log
Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 28125601
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files…
Applying interim patch ‘28125601’ to OH ‘/u01/app/oracle/product/11.0.0/db_1’
ApplySession: Optional component(s) [ oracle.oracore.rsf.core, 11.2.0.4.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.oracore.rsf, 11.2.0.4.0…
Patch 28125601 successfully applied.
Log file location: /u01/app/oracle/product/11.0.0/db_1/cfgtoollogs/opatch/opatch2025-06-28_10-16-14AM_1.log
OPatch succeeded.
—
[oracle@prod ~]$ cd /u01/app/oracle/product/11.0.0/db_1/oracore/zoneinfo/
[oracle@prod zoneinfo]$ ls -lrt timezlrg_32.dat
-rw-r–r– 1 oracle oinstall 786909 Jul 19 2018 timezlrg_32.dat
[oracle@prod OPatch]$ cd /u01/app/oracle/product/11.0.0/db_1/OPatch
[oracle@prod OPatch]$ ./opatch lsinventory | grep applied
Patch 28125601 : applied on Sat Jun 28 10:16:25 IST 2025
6. Apply the Patches on 11g home – 20348910 and 20898997
[oracle@prod ~]$ /u01/app/oracle/product/11.0.0/db_1/OPatch/opatch apply -oh /u01/app/oracle/product/11.0.0/db_1 -local /patch/patches/20348910
Oracle Interim Patch Installer version 11.2.0.3.50
Copyright (c) 2025, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.0.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.0.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.50
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.0.0/db_1/cfgtoollogs/opatch/opatch2025-06-29_13-57-16PM_1.log
Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 20348910
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files…
Applying interim patch ‘20348910’ to OH ‘/u01/app/oracle/product/11.0.0/db_1’
Patching component oracle.rdbms.dbscripts, 11.2.0.4.0…
Patch 20348910 successfully applied.
Log file location: /u01/app/oracle/product/11.0.0/db_1/cfgtoollogs/opatch/opatch2025-06-29_13-57-16PM_1.log
OPatch succeeded.
—–
[oracle@prod ~]$ /u01/app/oracle/product/11.0.0/db_1/OPatch/opatch apply -oh /u01/app/oracle/product/11.0.0/db_1 -local /patch/patches/20898997
Oracle Interim Patch Installer version 11.2.0.3.50
Copyright (c) 2025, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.0.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.0.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.50
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.0.0/db_1/cfgtoollogs/opatch/opatch2025-06-29_13-57-53PM_1.log
Verifying environment and performing prerequisite checks…
——————————————————————————–
Start OOP by Prereq process.
Launch OOP…
Oracle Interim Patch Installer version 11.2.0.3.50
Copyright (c) 2025, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.0.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.0.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.50
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.0.0/db_1/cfgtoollogs/opatch/opatch2025-06-29_13-57-59PM_1.log
Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 20898997
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/11.0.0/db_1’)
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files…
Applying interim patch ‘20898997’ to OH ‘/u01/app/oracle/product/11.0.0/db_1’
Patching component oracle.rdbms.rsf, 11.2.0.4.0…
Patching component oracle.rdbms, 11.2.0.4.0…
Patch 20898997 successfully applied.
Log file location: /u01/app/oracle/product/11.0.0/db_1/cfgtoollogs/opatch/opatch2025-06-29_13-57-59PM_1.log
OPatch succeeded.
—
[oracle@prod ~]$ cd /u01/app/oracle/product/11.0.0/db_1/OPatch
[oracle@prod OPatch]$ ./opatch lsinventory | grep applied
Patch 20898997 : applied on Sun Jun 29 13:58:13 IST 2025
Patch 20348910 : applied on Sun Jun 29 13:57:26 IST 2025
Patch 28125601 : applied on Sun Jun 29 12:48:49 IST 2025
7. Before downgrade, check the status of dba_registry components.
set lines 1234 pages 1234
col comp_name for a50
col version for a15
col status for a10
SELECT comp_name, version, status FROM dba_registry;
COMP_NAME VERSION STATUS
————————————————– ————— ———-
Oracle Database Catalog Views 19.0.0.0.0 VALID
Oracle Database Packages and Types 19.0.0.0.0 VALID
JServer JAVA Virtual Machine 19.0.0.0.0 VALID
Oracle XDK 19.0.0.0.0 VALID
Oracle Database Java Packages 19.0.0.0.0 VALID
OLAP Analytic Workspace 19.0.0.0.0 VALID
Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
Oracle Workspace Manager 19.0.0.0.0 VALID
Oracle Text 19.0.0.0.0 VALID
Oracle XML Database 19.0.0.0.0 VALID
Oracle Multimedia 19.0.0.0.0 VALID
Spatial 19.0.0.0.0 VALID
Oracle OLAP API 19.0.0.0.0 VALID
Oracle Application Express 3.2.1.00.12 VALID
14 rows selected.
—
SQL> set lines 1234 pages 1234
col action_time for a30
col action for a14
col namespace for a14
col version for a10
col comments for a60
col bundle_series for a14
select action_time,action,namespace,version,comments from dba_registry_history;
ACTION_TIME ACTION NAMESPACE VERSION COMMENTS
—————————— ————– ————– ———- ————————————————————
24-08-13 12:03:45.119862 PM APPLY SERVER 11.2.0.4 Patchset 11.2.0.2.0
02-07-25 2:07:15.468709 PM APPLY SERVER 11.2.0.4 Patchset 11.2.0.2.0
BOOTSTRAP DATAPATCH 19 RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
02-07-25 3:25:55.137723 PM RU_APPLY SERVER 19.0.0.0.0 Patch applied on 19.3.0.0.0: Release_Update – 190410122720
02-07-25 3:27:50.404590 PM UPGRADE SERVER 19.0.0.0.0 Upgraded from 11.2.0.4.0 to 19.3.0.0.0
8. If Oracle Enterprise Manager configured in database, then the drop the Enterprise Manager user.
SQL> select username,account_status from dba_users where username=’SYSMAN’;
no rows selected
In this scenario, OEM is not configured. If OEM configured, drop the SYSMAN user by below drop command.
DROP USER sysman CASCADE;
Note: After we drop the Enterprise Manager user, we can find that MGMT* synonyms are invalid. You must reconfigure Oracle Enterprise Manager to use any Oracle Enterprise Manager controls in the downgraded database.
9. Check the invalid objects in database.
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———-
0
10. Start the downgrade from 19c to 11g.
10.a Shutdown the 19c database and startup in downgrade mode.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup downgrade
ORACLE instance started.
Total System Global Area 3053449816 bytes
Fixed Size 8901208 bytes
Variable Size 654311424 bytes
Database Buffers 2382364672 bytes
Redo Buffers 7872512 bytes
Database mounted.
Database opened.
10.b Downgrade the 19c Database.
cd $ORACLE_HOME/bin
[oracle@prod bin]$ ls -l dbdowngrade
-rw-r–r– 1 oracle oinstall 8336 Feb 9 2018 dbdowngrade
[oracle@prod bin]$ chmod u+x dbdowngrade
[oracle@prod bin]$ ls -l dbdowngrade
-rwxr–r– 1 oracle oinstall 8336 Feb 9 2018 dbdowngrade
./dbdowngrade
[oracle@prod bin]$ ./dbdowngrade
Downgrading noncdb
SPOOL /u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/downgrade/catdwgrd.log
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
10.c Set the environment to 11g home
export ORACLE_HOME=/u01/app/oracle/product/11.0.0/db_1
export PATH=/u01/app/oracle/product/11.0.0/db_1/bin
export ORACLE_SID=prod
[oracle@prod ~]$ sqlplus -V
SQL*Plus: Release 11.2.0.4.0 Production
10.d Check the oratab file and update the with 11g ORACLE_HOME path.
[oracle@prod ~]$ cat /etc/oratab
#prod:/u01/app/oracle/product/19.0.0/db_1:N
prod:/u01/app/oracle/product/11.0.0/db_1:N
10.e From 19c home to 11g home, restore the necessary configuraton files such as parameter files, password filed and tns files sucha as listener.ora, tnsnames.ora and sqlner.ora.
10.f start the database in upgrade mode from 11g home.
[oracle@prod ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 5 16:37:20 2025
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1987563520 bytes
Fixed Size 2254584 bytes
Variable Size 520096008 bytes
Database Buffers 1459617792 bytes
Redo Buffers 5595136 bytes
Database mounted.
Database opened.
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
PL/SQL Release 11.2.0.4.0 – Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 – Production
NLSRTL Version 11.2.0.4.0 – Production
SQL> spool reload11g.log
SQL> @?/rdbms/admin/catrelod.sql
SQL> spool off
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 3607691264 bytes
Fixed Size 2258480 bytes
Variable Size 855640528 bytes
Database Buffers 2734686208 bytes
Redo Buffers 15106048 bytes
Database mounted.
Database opened.
11. Post downgrade from 19c to 11g, perform the post checks.
11.a Check the invalid objects and compile the, if any.
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———-
0
—
11.b Check the status of dba registry components before upgrade. All components should be in valid status.
set lines 1234 pages 1234
col comp_name for a50
col version for a15
col status for a10
SELECT comp_name, version, status FROM dba_registry;
COMP_NAME VERSION STATUS
————————————————– ————— ———-
Oracle Application Express 3.2.1.00.12 VALID
Oracle OLAP API 11.2.0.4.0 VALID
Spatial 11.2.0.4.0 VALID
Oracle Multimedia 11.2.0.4.0 VALID
Oracle XML Database 11.2.0.4.0 VALID
Oracle Text 11.2.0.4.0 VALID
Oracle Workspace Manager 11.2.0.4.0 VALID
Oracle Database Catalog Views 11.2.0.4.0 VALID
Oracle Database Packages and Types 11.2.0.4.0 VALID
JServer JAVA Virtual Machine 11.2.0.4.0 VALID
Oracle XDK 11.2.0.4.0 VALID
Oracle Database Java Packages 11.2.0.4.0 VALID
OLAP Analytic Workspace 11.2.0.4.0 VALID
13 rows selected.
—
set lines 1234 pages 1234
col action_time for a30
col action for a14
col namespace for a14
col version for a10
col comments for a60
col bundle_series for a14
select action_time,action,namespace,version,comments from dba_registry_history;
ACTION_TIME ACTION NAMESPACE VERSION COMMENTS
—————————— ————– ————– ———- ————————————————————
24-08-13 12:03:45.119862 PM APPLY SERVER 11.2.0.4 Patchset 11.2.0.2.0
02-07-25 02:07:15.468709 PM APPLY SERVER 11.2.0.4 Patchset 11.2.0.2.0
BOOTSTRAP DATAPATCH 19 RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
02-07-25 03:25:55.137723 PM RU_APPLY SERVER 19.0.0.0.0 Patch applied on 19.3.0.0.0: Release_Update – 190410122720
02-07-25 03:27:50.404590 PM UPGRADE SERVER 19.0.0.0.0 Upgraded from 11.2.0.4.0 to 19.3.0.0.0
05-07-25 04:31:13.126352 PM DOWNGRADE
05-07-25 05:09:07.379930 PM RELOAD SERVER 11.2.0.4.0 Reloaded after downgrade from 11.2.0
—
11.c Post downgrade, check the time zone value of 11g DB.
SQL> set linesize 300
col property_name for a30
col value for a20
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
—————————— ——————–
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
col version for 9999999999999
SELECT * FROM v$timezone_file;
FILENAME VERSION
——————– ————–
timezlrg_32.dat 32
12. Post downgrade, check the compatible parameter value.
SQL> show parameter compatible
NAME TYPE VALUE
———————————— ———– ——————————
compatible string 11.2.0.4.0
Disclaimer:
Please note the above information is only for educational purpose and practised in personal test database only. Always test in test database before implementing in production database. The pre-requisites and ways of implementing may vary from one environment to another. Hence, not providing guarantee that it will work in your environment.
