oraclednaThis post is to demonstrate to downgrade Standalone Oracle Database (Non-CDB) from 19c (19.3) to 12c (12.2.0.1) manually using dbdowngrade utility.
1. Check the Compatibility Matrix to downgrade the database.
2. Disable the Oracle Database Vault.
If Oracle Database enabled, need to disable the Oracle Database Vault before downgrading. Use CDB_DV_STATUS to check the status of Oracle Database Vault.
set lines 1234 pages 1234
col NAME for a24
SELECT * FROM CDB_DV_STATUS;
NAME STATUS CON_ID
———————— ————– ———-
DV_APP_PROTECTION NOT CONFIGURED 0
DV_CONFIGURE_STATUS FALSE 0
DV_ENABLE_STATUS FALSE 0
If the output is TRUE, then Oracle Database Vault is enabled, so we must disable it.
3. 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
Here Unified Auditing is not Enabled. If enabled, perform the below steps.
3.a Get the unified audit records
SQL> SELECT COUNT(*) FROM UNIFIED_AUDIT_TRAIL;
COUNT(*)
———-
53
3.b Backup existing data to backup table.
SQL> CREATE TABLE UA_DATA_BKP AS (SELECT * FROM UNIFIED_AUDIT_TRAIL);
Table created.
3.c 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);
PL/SQL procedure successfully completed.
4. 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 12c home. If not exists, need to apply patch 28125601 (p28125601_122010_Linux-x86-64).
[oracle@prod ~]$ cd /u01/app/oracle/product/12c/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/12c/db_1
export PATH=/u01/app/oracle/product/12c/db_1/bin
export PATH=$ORACLE_HOME/OPatch:$PATH
—
==Run OPatch Conflict Check
[oracle@prod OPatch]$ /u01/app/oracle/product/12c/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /patch/patches/28125601
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2025, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/12c/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12c/db_1/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12c/db_1/cfgtoollogs/opatch/opatch2025-06-24_05-45-03AM_1.log
Invoking prereq “checkconflictagainstohwithdetail”
Prereq “checkConflictAgainstOHWithDetail” passed.
OPatch succeeded.
—
===Run OPatch System Space Check
[oracle@prod OPatch]$ /u01/app/oracle/product/12c/db_1/OPatch/opatch prereq CheckSystemSpace -phBaseDir /patch/patches/28125601
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2025, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u01/app/oracle/product/12c/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12c/db_1/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12c/db_1/cfgtoollogs/opatch/opatch2025-06-24_05-47-03AM_1.log
Invoking prereq “checksystemspace”
Prereq “checkSystemSpace” passed.
OPatch succeeded.
—
==Analyze for Patch Conflict Detection and Resolution
[oracle@prod OPatch]$ /u01/app/oracle/product/12c/db_1/OPatch/opatch apply /patch/patches/28125601 -analyze
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2025, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12c/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12c/db_1/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12c/db_1/cfgtoollogs/opatch/opatch2025-06-24_05-48-19AM_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.
You are calling OPatch with -ocmrf option while this OPatch is generic, not being bundled with OCM. The -ocmrf option is being deprecated. Please remove it while calling OPatch.
Backing up files…
Applying interim patch ‘28125601’ to OH ‘/u01/app/oracle/product/12c/db_1’
Users request no RAC file generation. Do not create MP files.
Skip patching component oracle.oracore.rsf, 12.2.0.1.0 and its actions.
The actions are reported here, but are not performed.
ApplySession skipping inventory update.
Users request no RAC file generation. Do not create MP files.
Patch 28125601 successfully applied.
Log file location: /u01/app/oracle/product/12c/db_1/cfgtoollogs/opatch/opatch2025-06-24_05-48-19AM_1.log
OPatch succeeded.
—
===Apply the Patch
[oracle@prod OPatch]$ /u01/app/oracle/product/12c/db_1/OPatch/opatch apply -oh /u01/app/oracle/product/12c/db_1 -local /patch/patches/28125601
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2025, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/12c/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12c/db_1/oraInst.loc
OPatch version : 12.2.0.1.6
OUI version : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12c/db_1/cfgtoollogs/opatch/opatch2025-06-24_05-52-26AM_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/12c/db_1’
Patching component oracle.oracore.rsf, 12.2.0.1.0…
Patch 28125601 successfully applied.
Log file location: /u01/app/oracle/product/12c/db_1/cfgtoollogs/opatch/opatch2025-06-24_05-52-26AM_1.log
OPatch succeeded.
—
Now, check the timezone file available or not at OS level.
[oracle@prod ~]$ cd /u01/app/oracle/product/12c/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]$ ./opatch lsinventory | grep applied
Patch 28125601 : applied on Tue Jun 24 05:52:33 IST 2025
5. 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 XML Database 19.0.0.0.0 VALID
Oracle Workspace Manager 19.0.0.0.0 VALID
Oracle Text 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 Label Security 19.0.0.0.0 VALID
Oracle Database Vault 19.0.0.0.0 VALID
15 rows selected.
—
set linesize 300
col status for a10
col ACTION_TIME for a35
select PATCH_ID,PATCH_TYPE,ACTION,ACTION_TIME,SOURCE_VERSION,TARGET_VERSION,STATUS,description from dba_registry_sqlpatch order by ACTION_TIME;
PATCH_ID PATCH_TYPE ACTION ACTION_TIME SOURCE_VERSION TARGET_VERSION STATUS DESCRIPTION
———- ———- ————— ———————————– ————— ————— ———- —————————————————-
29517242 RU APPLY 22-06-25 4:01:09.881240 PM 19.1.0.0.0 19.3.0.0.0 SUCCESS Database Release Update : 19.3.0.0.190416 (29517242)
6. 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.
7. Check the invalid objects in database.
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———-
4
—
SQL> set lines 1234 pages 1234
col owner for a15
select owner,count(*) from dba_objects where status=’INVALID’ group by owner order by owner;
OWNER COUNT(*)
————— ———-
HR 4
—
SQL> set lines 1234 pages 1234
col owner for a18
col object_name for a30
col object_type for a18
col status for a10
select owner,object_name,object_type,created,status from dba_objects where status = ‘INVALID’;
OWNER OBJECT_NAME OBJECT_TYPE CREATED STATUS
—————— —————————— —————— ——– ———-
HR SECURE_DML PROCEDURE 11-06-25 INVALID
HR SECURE_EMPLOYEES TRIGGER 11-06-25 INVALID
HR ADD_JOB_HISTORY PROCEDURE 11-06-25 INVALID
HR UPDATE_JOB_HISTORY TRIGGER 11-06-25 INVALID
—
To recompile the invalid objects run the utlrp.sql
@?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———-
0
8. Start the Oracle database downgrade from 19c to 12c.
8.a Shutdown the 19c database and start up 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.
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME HOST_NAME STARTUP_TIME
———- ————— ———- —————— ————— —————- ——————–
PROD prod READ WRITE PRIMARY prod prod 24-JUN-2025 06:11
8.b spool downgrade_19cto12c.log
SQL> spool downgrade_19cto12c.log
SQL> set timing on;
SQL> @?/rdbms/admin/catdwgrd.sql
SQL> spool off;
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
8.c Set the environment to 12c home
[oracle@prod ~]$ export ORACLE_HOME=/u01/app/oracle/product/12c/db_1
[oracle@prod ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12c/db_1
[oracle@prod ~]$ sqlplus -V
SQL*Plus: Release 12.2.0.1.0 Production
8.d Check the oratab file and update the with 12c ORACLE_HOME path.
[oracle@prod ~]$ cat /etc/oratab
#prod:/u01/app/oracle/product/19.0.0/db_1:N
prod:/u01/app/oracle/product/12c/db_1:N
8.d From 19c home to 12c home, restore the necessary configuraton files such as parameter files, password filed and tns files sucha as listener.ora, tnsnames.ora and sqlner.ora.
8.e start the database from 12c home.
[oracle@prod ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 24 06:43:20 2025
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 3053453312 bytes
Fixed Size 8625368 bytes
Variable Size 754975528 bytes
Database Buffers 2281701376 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME HOST_NAME STARTUP_TIME
———- ————— ———- —————— ————— —————- ——————–
PROD prod READ WRITE PRIMARY prod prod 24-JUN-2025 06:45
BANNER CON_ID
——————————————————————————– ———-
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production 0
PL/SQL Release 12.2.0.1.0 – Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 – Production 0
NLSRTL Version 12.2.0.1.0 – Production 0
SQL> spool reload12c.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 3053453312 bytes
Fixed Size 8625368 bytes
Variable Size 754975528 bytes
Database Buffers 2281701376 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
9. Post downgrade from 19c to 12c, perform the post checks.
9.a Check the invalid objects and compile the, if any.
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———-
403
@?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———-
0
1 row selected.
—
9.b Check the status of dba_registry, all the components should be in valid state.
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 12.2.0.1.0 VALID
Oracle Database Packages and Types 12.2.0.1.0 VALID
JServer JAVA Virtual Machine 12.2.0.1.0 VALID
Oracle XDK 12.2.0.1.0 VALID
Oracle Database Java Packages 12.2.0.1.0 VALID
OLAP Analytic Workspace 12.2.0.1.0 VALID
Oracle Real Application Clusters 12.2.0.1.0 OPTION OFF
Oracle XML Database 12.2.0.1.0 VALID
Oracle Workspace Manager 12.2.0.1.0 VALID
Oracle Text 12.2.0.1.0 VALID
Oracle Multimedia 12.2.0.1.0 VALID
Spatial 12.2.0.1.0 VALID
Oracle OLAP API 12.2.0.1.0 VALID
Oracle Label Security 12.2.0.1.0 VALID
Oracle Database Vault 12.2.0.1.0 VALID
15 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
—————————— ————– ————– ———- ————————————————————
BOOTSTRAP DATAPATCH 12.2.0.1 RDBMS_12.2.0.1.0_LINUX.X64_170125
BOOTSTRAP DATAPATCH 19 RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
22-06-25 04:01:02.687432 PM RU_APPLY SERVER 19.0.0.0.0 Patch applied on 19.3.0.0.0: Release_Update – 190410122720
22-06-25 04:02:20.658080 PM UPGRADE SERVER 19.0.0.0.0 Upgraded from 12.2.0.1.0 to 19.3.0.0.0
24-06-25 06:22:31.804728 AM DOWNGRADE
24-06-25 07:20:06.711158 AM RELOAD SERVER 12.2.0.1.0 Reloaded after downgrade from 12.2.0.1.0
6 rows selected.
9.c Check the timezone version.
SQL> col version for 999999999999
select * from V$TIMEZONE_FILE;
FILENAME VERSION CON_ID
——————– ————- ———-
timezlrg_32.dat 32 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.
