Downgrade Oracle Database from 19c to 11g by using dbdowngrade (Non CDB) oracledna Oracle Standalone Database July 5, 2025 This 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 999999999999select * 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.datls: cannot access timezlrg_32.dat: No such file or directory export ORACLE_HOME=/u01/app/oracle/product/11.0.0/db_1export 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.50Copyright (c) 2025, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.0.0/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/11.0.0/db_1/oraInst.locOPatch version : 11.2.0.3.50OUI version : 11.2.0.4.0Log 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]yUser Responded with: YAll 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 appliedPatch 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.50Copyright (c) 2025, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.0.0/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/11.0.0/db_1/oraInst.locOPatch version : 11.2.0.3.50OUI version : 11.2.0.4.0Log 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]yUser Responded with: YAll 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.50Copyright (c) 2025, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.0.0/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/11.0.0/db_1/oraInst.locOPatch version : 11.2.0.3.50OUI version : 11.2.0.4.0Log 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.50Copyright (c) 2025, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.0.0/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/11.0.0/db_1/oraInst.locOPatch version : 11.2.0.3.50OUI version : 11.2.0.4.0Log 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]yUser Responded with: YAll 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]yUser Responded with: YBacking 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 appliedPatch 20898997 : applied on Sun Jun 29 13:58:13 IST 2025Patch 20348910 : applied on Sun Jun 29 13:57:26 IST 2025Patch 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 1234col comp_name for a50col version for a15col status for a10SELECT comp_name, version, status FROM dba_registry; COMP_NAME VERSION STATUS————————————————– ————— ———-Oracle Database Catalog Views 19.0.0.0.0 VALIDOracle Database Packages and Types 19.0.0.0.0 VALIDJServer JAVA Virtual Machine 19.0.0.0.0 VALIDOracle XDK 19.0.0.0.0 VALIDOracle Database Java Packages 19.0.0.0.0 VALIDOLAP Analytic Workspace 19.0.0.0.0 VALIDOracle Real Application Clusters 19.0.0.0.0 OPTION OFFOracle Workspace Manager 19.0.0.0.0 VALIDOracle Text 19.0.0.0.0 VALIDOracle XML Database 19.0.0.0.0 VALIDOracle Multimedia 19.0.0.0.0 VALIDSpatial 19.0.0.0.0 VALIDOracle OLAP API 19.0.0.0.0 VALIDOracle Application Express 3.2.1.00.12 VALID 14 rows selected. — SQL> set lines 1234 pages 1234col action_time for a30col action for a14col namespace for a14col version for a10col comments for a60col bundle_series for a14select 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.002-07-25 2:07:15.468709 PM APPLY SERVER 11.2.0.4 Patchset 11.2.0.2.0BOOTSTRAP DATAPATCH 19 RDBMS_19.3.0.0.0DBRU_LINUX.X64_19041702-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 – 19041012272002-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

Upgrade Oracle Database from 11g to 19c by DBUA (Non CDB) oracledna Oracle Standalone Database July 2, 2025 This post is to demonstrate upgrade Oracle Database from 11g (11.2.0.4) to 19c (19.3) by DBUA (Non CDB). 1. Take the RMAN full backup of 11g DB. 2. Take the 11g ORACLE Home backup. 3. Check the hidden parameters in 11g DB. 19c DBUA restricts the carry over of hidden parameters since Oracle recommends not to have hidden parameters other than those suggested by Oracle Support during the upgrade. SQL> SELECT name, description from SYS.V$PARAMETER WHERE name LIKE ‘_%’ ESCAPE ”; no rows selected 4. Ensure DB backup is completed before upgrade. SQL> SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’; no rows selected 5. Check the invalid objects and make them valid. Also, SYS and SYSTEM objects should not be in invalid state. SQL> select count(*) from dba_objects where status=’INVALID’; COUNT(*)———-0 6. Check the status of dba registry components before upgrade. All components should be in valid status. set lines 1234 pages 1234col comp_name for a50col version for a15col status for a10SELECT comp_name, version, status FROM dba_registry; COMP_NAME VERSION STATUS————————————————– ————— ———-OWB 11.2.0.4.0 VALIDOracle Application Express 3.2.1.00.12 VALIDOracle Enterprise Manager 11.2.0.4.0 VALIDOLAP Catalog 11.2.0.4.0 VALIDSpatial 11.2.0.4.0 VALIDOracle Multimedia 11.2.0.4.0 VALIDOracle XML Database 11.2.0.4.0 VALIDOracle Text 11.2.0.4.0 VALIDOracle Expression Filter 11.2.0.4.0 VALIDOracle Rules Manager 11.2.0.4.0 VALIDOracle Workspace Manager 11.2.0.4.0 VALIDOracle Database Catalog Views 11.2.0.4.0 VALIDOracle Database Packages and Types 11.2.0.4.0 VALIDJServer JAVA Virtual Machine 11.2.0.4.0 VALIDOracle XDK 11.2.0.4.0 VALIDOracle Database Java Packages 11.2.0.4.0 VALIDOLAP Analytic Workspace 11.2.0.4.0 VALIDOracle OLAP API 11.2.0.4.0 VALID 18 rows selected. — set lines 1234 pages 1234col action_time for a30col action for a14col namespace for a14col version for a10col comments for a60col bundle_series for a14select 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.002-07-25 02:07:15.468709 PM APPLY SERVER 11.2.0.4 Patchset 11.2.0.2.0 7. Take the compatible parameter value of 11g DB. Min value of COMPATIBLE parameter to upgrade 19.1 is “11.2.0”, make sure you have COMPATIBLE parameter is set to 11.2.0 or greater. SQL> show parameter compatible NAME TYPE VALUE———————————— ———– —————compatible string 11.2.0.4.0 8. Take the time zone value of 11g DB. Time zone should less than or equal to target database time zone version. set linesize 300col property_name for a30col value for a20SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueFROM DATABASE_PROPERTIESWHERE PROPERTY_NAME LIKE ‘DST_%’ORDER BY PROPERTY_NAME; PROPERTY_NAME VALUE—————————— ——————–DST_PRIMARY_TT_VERSION 14DST_SECONDARY_TT_VERSION 0DST_UPGRADE_STATE NONE — SQL> col version for 999999999999999select * from v$timezone_file; FILENAME VERSION——————– —————-timezlrg_14.dat 14 8. Find the duplicate objects in the SYS and SYSTEM schema. Ensure that you do not have duplicate objects in the SYS and SYSTEM schema. Download dbupgdiag.sql from Doc Id 556610.1 to collect DB Upgrade/Migrate Diagnostic Information 9. Upgrade the APEX. If APEX is installed in 11g DB, it is recommended to upgrade APEX before upgrading DB. 10. Disable the Custom Triggers. Disable any custom triggers that would get executed before or after DDL statements. Re-enable after the upgrade. 11. Enable archive mode and Fast Recovery Area (FRA). SQL> select flashback_on from v$database; FLASHBACK_ON——————NO SQL> show parameter recovery NAME TYPE VALUE———————————— ———–db_recovery_file_dest stringdb_recovery_file_dest_size big integer 0recovery_parallelism integer 0 SQL> alter system set db_recovery_file_dest_size=20G; System altered. SQL> alter system set db_recovery_file_dest=’/oem/fra’ scope=both; System altered. SQL> show parameter recovery NAME TYPE VALUE———————————— ———– db_recovery_file_dest string /oem/fradb_recovery_file_dest_size big integer 20Grecovery_parallelism integer 0 Note:= Ensure the log_archive_dest_1 must have sufficient space, at least 4535 MB for archive logs generation. — SQL> select name,open_mode,log_mode from v$database; NAME OPEN_MODE LOG_MODE———- ———- ————PROD READ WRITE NOARCHIVELOG SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down. SQL> startup mount;ORACLE instance started.Total System Global Area 3323752448 bytesFixed Size 2257800 bytesVariable Size 788532344 bytesDatabase Buffers 2516582400 bytesRedo Buffers 16379904 bytesDatabase mounted. SQL> alter database archivelog;Database altered. SQL> alter database open;Database altered. SQL> select name,open_mode,log_mode from v$database; NAME OPEN_MODE LOG_MODE———- ———- ————PROD READ WRITE ARCHIVELOG 12. Check the status of all materialized views (MV), and refresh any materialized views that are not fresh. Use this procedure to query the system to determine if there are any materialized view refreshes still in progress. SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;no rows selected Use below SQL to check COMPILATION_ERROR. SQL> select owner, mview_name, staleness, compile_state from dba_mviews; OWNER MVIEW_NAME STALENESS COMPILE_STATE—————————— —————————— ——————- ———–SYSMAN MGMT_ECM_MD_ALL_TBL_COLUMNS FRESH VALIDSH CAL_MONTH_SALES_MV UNKNOWN VALIDSH FWEEK_PSCAT_SALES_MV UNKNOWN VALID SQL> EXECUTE DBMS_MVIEW.REFRESH(‘SH.CAL_MONTH_SALES_MV’, method => ‘C’);PL/SQL procedure successfully completed. SQL> EXECUTE DBMS_MVIEW.REFRESH(‘SH.FWEEK_PSCAT_SALES_MV’, method => ‘C’);PL/SQL procedure successfully completed. SQL> select owner, mview_name, staleness, compile_state from dba_mviews; OWNER MVIEW_NAME STALENESS COMPILE_STATE—————————— —————————— ——————- ———–SYSMAN MGMT_ECM_MD_ALL_TBL_COLUMNS FRESH VALIDSH FWEEK_PSCAT_SALES_MV FRESH VALIDSH CAL_MONTH_SALES_MV FRESH VALID 13. Disable scheduled database custom jobs and cron jobs. 14. check the definition of the PUBLIC synonym AREA. Before the upgrade, if Oracle Multimedia and/or Oracle Spatial is installed, check the definition of the PUBLICsynonym AREA. It should be defined to be a synonym for OGC_AREA, otherwise, it causes invalid db components uponaupgrading. SQL> set linesize 300SQL> select owner, synonym_name, table_owner, table_name from dba_synonyms where synonym_name =’AREA’; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME—————————— —————————— —————————— ——————————PUBLIC AREA MDSYS OGC_AREA 15. Gather dictionary statistics before upgrade. SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed. 16. Purge the Recycle bin before upgrade. We can purge the recycle bin via DBUA. SQL> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged. 17. Increase the processes parameter value to 300, if it is lower than 300. SQL> show parameter processes; NAME TYPE VALUE———————————— ———– ——————————aq_tm_processes integer 1db_writer_processes integer 1gcs_server_processes integer 0global_txn_processes integer 1job_queue_processes integer 1000log_archive_max_processes integer 4processes integer 150 SQL> alter system set processes=300 scope=spfile;System altered. SQL> shut immediateDatabase closed.Database dismounted.ORACLE instance shut down. SQL> startupORACLE instance started.Total System Global Area 3607691264 bytesFixed Size 2258480 bytesVariable Size 838863312 bytesDatabase Buffers 2751463424 bytesRedo Buffers 15106048 bytesDatabase mounted.Database opened. SQL> show parameter processes;NAME TYPE VALUE———————————— ———– ——————————aq_tm_processes integer 1db_writer_processes integer 1gcs_server_processes integer 0global_txn_processes integer 1job_queue_processes integer 1000log_archive_max_processes integer 4processes integer 300 18. If any ACLs existed, need to take the backup. set linesize 300col acl for a60col host for a14SELECT

Downgrade Oracle Database from 19c (19.3) to 11g (11.2.0.4) manually oracledna Oracle Standalone Database June 29, 2025 This post is to demonstrate downgrade Oracle Database from 19c (19.3) to 11g (11.2.0.4) manually. 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 999999999999select * 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.datls: cannot access timezlrg_32.dat: No such file or directory export ORACLE_HOME=/u01/app/oracle/product/11.0.0/db_1export PATH=/u01/app/oracle/product/11.0.0/db_1/bin === ==Run OPatch Conflict Check[oracle@prod ~]$ /u01/app/oracle/product/11.0.0/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /patch/patches/28125601 Oracle Interim Patch Installer version 11.2.0.3.50Copyright (c) 2025, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/11.0.0/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/11.0.0/db_1/oraInst.locOPatch version : 11.2.0.3.50OUI version : 11.2.0.4.0Log file location : /u01/app/oracle/product/11.0.0/db_1/cfgtoollogs/opatch/opatch2025-06-28_10-12-49AM_1.log Invoking prereq “checkconflictagainstohwithdetail” Prereq “checkConflictAgainstOHWithDetail” passed. OPatch succeeded. — ===Run OPatch System Space Check [oracle@prod ~]$ /u01/app/oracle/product/11.0.0/db_1/OPatch/opatch prereq CheckSystemSpace -phBaseDir /patch/patches/28125601Oracle Interim Patch Installer version 11.2.0.3.50Copyright (c) 2025, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/11.0.0/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/11.0.0/db_1/oraInst.locOPatch version : 11.2.0.3.50OUI version : 11.2.0.4.0Log file location : /u01/app/oracle/product/11.0.0/db_1/cfgtoollogs/opatch/opatch2025-06-28_10-13-28AM_1.log Invoking prereq “checksystemspace” Prereq “checkSystemSpace” passed. OPatch succeeded. — ===Apply the Patch/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@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/28125601Oracle Interim Patch Installer version 11.2.0.3.50Copyright (c) 2025, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.0.0/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/11.0.0/db_1/oraInst.locOPatch version : 11.2.0.3.50OUI version : 11.2.0.4.0Log 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]yUser Responded with: YAll 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 appliedPatch 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.50Copyright (c) 2025, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.0.0/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/11.0.0/db_1/oraInst.locOPatch version : 11.2.0.3.50OUI version : 11.2.0.4.0Log 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]yUser Responded with: YAll 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/20898997Oracle Interim Patch Installer version 11.2.0.3.50Copyright (c) 2025, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.0.0/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/11.0.0/db_1/oraInst.locOPatch version : 11.2.0.3.50OUI version : 11.2.0.4.0Log 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.50Copyright (c) 2025, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.0.0/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/11.0.0/db_1/oraInst.locOPatch version : 11.2.0.3.50OUI version : 11.2.0.4.0Log 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]yUser Responded with: YAll 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]yUser Responded with: YBacking 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 appliedPatch 20898997 : applied on Sun Jun 29 13:58:13 IST 2025Patch 20348910 : applied on Sun Jun 29 13:57:26 IST 2025Patch 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 1234col comp_name for a50col version for a15col status for a10SELECT comp_name, version, status FROM dba_registry; COMP_NAME VERSION STATUS————————————————– ————— ———-Oracle Database Catalog Views 19.0.0.0.0 VALIDOracle Database Packages and Types 19.0.0.0.0 VALIDJServer JAVA Virtual Machine 19.0.0.0.0 VALIDOracle XDK 19.0.0.0.0 VALIDOracle Database Java Packages 19.0.0.0.0 VALIDOracle Real Application Clusters 19.0.0.0.0 OPTION OFFOLAP Analytic Workspace 19.0.0.0.0 VALIDOracle Workspace Manager 19.0.0.0.0 VALIDOracle Text 19.0.0.0.0 VALIDOracle XML Database 19.0.0.0.0 VALIDOracle Multimedia 19.0.0.0.0 VALIDSpatial 19.0.0.0.0 VALIDOracle OLAP API 19.0.0.0.0 VALIDOracle 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

Oracle Database Upgrade from 11g (11.2.0.4) to 19c (19.3) by Autoupgrade oracledna Oracle Standalone Database June 28, 2025 This post is demonstrate the upgrading Standalone Non-CDB Oracle Database  from 11g (11.2.0.4) to 19c (19.3). 1. Take the full RMAN backup of 11g Oracle Database. 2. Take the 11g Home backup. [oracle@prod 11.0.0]$ cd /u01/app/oracle/product/11.0.0[oracle@prod 11.0.0]$ [oracle@prod 11.0.0]$ tar -pcvf db_1_bkp.tar db_1 [oracle@prod 11.0.0]$ lsdb_1 db_1_bkp.tar 3. Ensure DB backup is completed before upgrade. SQL> SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’; no rows selected 4. Check the invalid objects and make them valid. Also, SYS and SYSTEM objects should not be in invalid state. SQL> select count(*) from dba_objects where status=’INVALID’; COUNT(*)———-0 — SQL> SELECT owner,object_name,object_type,status,created FROM dba_objects where status=’INVALID’ and owner=’SYS’; no rows selected — SQL> SELECT owner,object_name,object_type,status,created FROM dba_objects where status=’INVALID’ and owner=’SYSTEM’; no rows selected 5. Check the status of dba registry components before upgrade. All components should be in valid status. set lines 1234 pages 1234col comp_name for a50col version for a15col status for a10SELECT comp_name, version, status FROM dba_registry; COMP_NAME VERSION STATUS————————————————– ————— ———-OWB 11.2.0.4.0 VALIDOracle Application Express 3.2.1.00.12 VALIDOracle Enterprise Manager 11.2.0.4.0 VALIDOLAP Catalog 11.2.0.4.0 VALIDSpatial 11.2.0.4.0 VALIDOracle Multimedia 11.2.0.4.0 VALIDOracle XML Database 11.2.0.4.0 VALIDOracle Text 11.2.0.4.0 VALIDOracle Expression Filter 11.2.0.4.0 VALIDOracle Rules Manager 11.2.0.4.0 VALIDOracle Workspace Manager 11.2.0.4.0 VALIDOracle Database Catalog Views 11.2.0.4.0 VALIDOracle Database Packages and Types 11.2.0.4.0 VALIDJServer JAVA Virtual Machine 11.2.0.4.0 VALIDOracle XDK 11.2.0.4.0 VALIDOracle Database Java Packages 11.2.0.4.0 VALIDOLAP Analytic Workspace 11.2.0.4.0 VALIDOracle OLAP API 11.2.0.4.0 VALID 18 rows selected. 6. Take the compatible parameter value of 11g DB to compare after the DB upgrade. SQL> show parameter compatible NAME TYPE VALUE———————————— ———– ——————————compatible string 11.2.0.4.0 7. Take the time zone value of 11g DB. set linesize 300col property_name for a30col value for a20SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueFROM DATABASE_PROPERTIESWHERE PROPERTY_NAME LIKE ‘DST_%’ORDER BY PROPERTY_NAME; PROPERTY_NAME VALUE—————————— ——————–DST_PRIMARY_TT_VERSION 14DST_SECONDARY_TT_VERSION 0DST_UPGRADE_STATE NONE — col version for 9999999999999SELECT * FROM v$timezone_file; FILENAME VERSION——————– ————–timezlrg_14.dat 14 8. Enable archive mode mode and Fast Recovery Area (FRA). SQL> select flashback_on from v$database; FLASHBACK_ON——————NO — SQL> show parameter recovery NAME TYPE VALUE———————————— ———– ——————————db_recovery_file_dest stringdb_recovery_file_dest_size big integer 0recovery_parallelism integer 0 — SQL> alter system set db_recovery_file_dest_size=20G; System altered. — SQL> alter system set db_recovery_file_dest=’/oem/fra’; System altered. — SQL> show parameter recovery NAME TYPE VALUE———————————— ———– ——————————db_recovery_file_dest string /oem/fradb_recovery_file_dest_size big integer 20Grecovery_parallelism integer 0 — SQL> select name,open_mode,log_mode from v$database; NAME OPEN_MODE LOG_MODE——— ——————– ————PROD READ WRITE ARCHIVELOG 9. Check the status of all materialized views (MV), and refresh any materialized views that are not fresh. SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8; no rows selected 10. Download the most recent version of “Autoupgrade.jar” file, from Doc 2485457.1 or frim https://www.oracle.com/database/upgrades/. In 19c home, take the backup of existing autoupgrade.jar file and place the latest autoupgrade.jar file. cd /u01/app/oracle/product/19.0.0/db_1/rdbms/admin mv autoupgrade.jar autoupgrade.jar_bkp cp /tmp/autoupgrade.jar . 11. Generate a sample config file and edit accordingly. Make sure export the 11g Oracle home before running the “autoupgrade.jar” commands. [oracle@prod ~]$ /u01/app/oracle/product/19.0.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/autoupgrade.jar -create_sample_file config /u01/config.cfg Created sample configuration file /u01/config.cfg — Now, edit the config file upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgradeupg1.sid=produpg1.source_home=/u01/app/oracle/product/11.0.0/db_1upg1.target_home=/u01/app/oracle/product/19.0.0/db_1upg1.start_time=NOWupg1.upgrade_node=produpg1.run_utlrp=yesupg1.timezone_upg=yesupg1.target_version=19 To check the list of checks which autoaupgrade.jar performs use below command. /u01/app/oracle/product/19.0.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/autoupgrade.jar -listchecks 12. Run the upgrade in analyze mode to validate any expected issues with the upgrade. /u01/app/oracle/product/19.0.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/autoupgrade.jar -config /u01/config.cfg -mode analyze [oracle@prod ~]$ /u01/app/oracle/product/19.0.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/autoupgrade.jar -config /u01/config.cfg -mode analyze AutoUpgrade 25.3.250509 launched with default internal optionsProcessing config file …+——————————–+| Starting AutoUpgrade execution |+——————————–+1 Non-CDB(s) will be analyzedType ‘help’ to list console commandsupg> status Config User configuration file [/u01/config.cfg]General logs location [/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto]Mode [ANALYZE]Jobs Summary Total databases in configuration file [1]Total Non-CDB being processed [1]Total Containers being processed [0] Jobs finished successfully [0]Jobs finished/stopped [0]Jobs in progress [1] Progress+—+———————————————————+|Job| Progress|+—+———————————————————+|100|[||||||||||||||||| ] 33 %|+—+———————————————————+ upg> status -job 100Job 100 completed——————- Final Summary ——————–Number of databases [ 1 ] Jobs finished [1]Jobs failed [0] Please check the summary report at:/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log — [oracle@prod ~]$ cat /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log==========================================Autoupgrade Summary Report==========================================[Date] Sat Jun 28 06:06:40 IST 2025[Number of Jobs] 1==========================================[Job ID] 100==========================================[DB Name] prod[Version Before Upgrade] 11.2.0.4.0[Version After Upgrade] 19.3.0.0.0——————————————[Stage Name] PRECHECKS[Status] SUCCESS[Start Time] 2025-06-28 06:06:23[Duration] 0:00:16[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/prod/100/prechecks[Detail] /u01/app/oracle/cfgtoollogs/autoupgrade/prod/100/prechecks/prod_preupgrade.logCheck passed and no manual intervention needed —- We must refer below preupgrade.log for more details. /u01/app/oracle/cfgtoollogs/autoupgrade/prod/100/prechecks/prod_preupgrade.log 13. Run Autoupgrade in Deploy mode. Important: Before running Autoupgrade in Deploy mode, we must validate that there are no invalid objects or components that may affect the process. [oracle@prod ~]$ /u01/app/oracle/product/19.0.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/autoupgrade.jar -config /u01/config.cfg -mode deploy AutoUpgrade 25.3.250509 launched with default internal optionsProcessing config file …+——————————–+| Starting AutoUpgrade execution |+——————————–+1 Non-CDB(s) will be processedType ‘help’ to list console commandsupg> status Config User configuration file [/u01/config.cfg]General logs location [/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto]Mode [DEPLOY]Jobs Summary Total databases in configuration file [1]Total Non-CDB being processed [1]Total Containers being processed [0] Jobs finished successfully [0]Jobs finished/stopped [0]Jobs in progress [1] Progress+—+———————————————————+|Job| Progress|+—+———————————————————+|101|[|||||||||||| ] 23 %|+—+———————————————————+ upg>upg>upg> status -job 101Details Job No 101Oracle SID prodStart Time 25/06/28 06:16:49Elapsed (min): 0End time: N/A Logfiles Logs Base: /u01/app/oracle/cfgtoollogs/autoupgrade/prodJob logs: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/101Stage logs: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/101/prechecksTimeZone: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/tempRemote Dirs: StagesSETUP <1 minGRP <1 minPREUPGRADE <1 minPRECHECKS ~0 min (RUNNING)PREFIXUPSDRAINDBUPGRADEPOSTCHECKSPOSTFIXUPSPOSTUPGRADESYSUPDATES Stage-Progress Per Container +——–+———+|Database|PRECHECKS|+——–+———+| prod| 100% |+——–+———+ — upg> status -job 101Details Job No 101Oracle SID prodStart Time 25/06/28 06:16:49Elapsed (min): 75End time: N/A Logfiles Logs Base: /u01/app/oracle/cfgtoollogs/autoupgrade/prodJob logs: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/101Stage logs: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/101/postfixupsTimeZone: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/tempRemote Dirs: StagesSETUP <1 minGRP <1 minPREUPGRADE <1 minPRECHECKS <1 minPREFIXUPS 6 minDRAIN <1 minDBUPGRADE 49 minDISPATCH <1 minPOSTCHECKS <1 minDISPATCH <1 minPOSTFIXUPS ~18 min (RUNNING)POSTUPGRADESYSUPDATES Stage-Progress Per Container +——–+———-+|Database|POSTFIXUPS|+——–+———-+| prod| 100% |+——–+———-+ — upg> status -job 101Details Job No 101Oracle SID prodStart Time 25/06/28 06:16:49Elapsed (min): 72End time: N/A Logfiles Logs Base: /u01/app/oracle/cfgtoollogs/autoupgrade/prodJob logs: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/101Stage logs: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/101/postfixupsTimeZone: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/tempRemote Dirs: StagesSETUP <1 minGRP <1 minPREUPGRADE <1 minPRECHECKS <1 minPREFIXUPS 6 minDRAIN <1 minDBUPGRADE 49 minDISPATCH <1 minPOSTCHECKS <1 minDISPATCH <1 minPOSTFIXUPS ~16 min (RUNNING)POSTUPGRADESYSUPDATES Stage-Progress Per Container +——–+———-+|Database|POSTFIXUPS|+——–+———-+| prod| 80 % |+——–+———-+ upg> lsj+—-+——-+———-+———+——-+———-+——-+—————-+|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|+—-+——-+———-+———+——-+———-+——-+—————-+| 101| prod|POSTFIXUPS|EXECUTING|RUNNING| 06:16:49|27s ago|Executing fixups|+—-+——-+———-+———+——-+———-+——-+—————-+ 14. Post upgrade, check the DB version. [oracle@prod ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/db_1[oracle@prod ~]$ export ORACLE_SID=prod[oracle@prod ~]$ export PATH=/u01/app/oracle/product/19.0.0/db_1/bin [oracle@prod ~]$ sqlplus

Downgrade Standalone Oracle database (Non-CDB) from 19c (19.3) to 12c (12.2.0.1) manually using dbdowngrade utility oracledna Oracle Standalone Database June 24, 2025 This 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 1234col NAME for a24SELECT * FROM CDB_DV_STATUS; NAME STATUS CON_ID———————— ————– ———-DV_APP_PROTECTION NOT CONFIGURED 0DV_CONFIGURE_STATUS FALSE 0DV_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 999999999999select * 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.datls: cannot access timezlrg_32.dat: No such file or directory export ORACLE_HOME=/u01/app/oracle/product/12c/db_1export PATH=/u01/app/oracle/product/12c/db_1/binexport 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.6Copyright (c) 2025, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/12c/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/12c/db_1/oraInst.locOPatch version : 12.2.0.1.6OUI version : 12.2.0.1.4Log 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.6Copyright (c) 2025, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/12c/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/12c/db_1/oraInst.locOPatch version : 12.2.0.1.6OUI version : 12.2.0.1.4Log 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.6Copyright (c) 2025, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/12c/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/12c/db_1/oraInst.locOPatch version : 12.2.0.1.6OUI version : 12.2.0.1.4Log 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]yUser Responded with: YAll 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.6Copyright (c) 2025, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/12c/db_1Central Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/12c/db_1/oraInst.locOPatch version : 12.2.0.1.6OUI version : 12.2.0.1.4Log 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]yUser Responded with: YAll 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 appliedPatch 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 1234col comp_name for a50col version for a15col status for a10SELECT comp_name, version, status FROM dba_registry; COMP_NAME VERSION STATUS————————————————– ————— ———-Oracle Database Catalog Views 19.0.0.0.0 VALIDOracle Database Packages and Types 19.0.0.0.0 VALIDJServer JAVA Virtual Machine 19.0.0.0.0 VALIDOracle XDK 19.0.0.0.0 VALIDOracle Database Java Packages 19.0.0.0.0 VALIDOLAP Analytic Workspace 19.0.0.0.0 VALIDOracle Real Application Clusters 19.0.0.0.0 OPTION OFFOracle XML Database 19.0.0.0.0 VALIDOracle Workspace Manager 19.0.0.0.0 VALIDOracle Text 19.0.0.0.0 VALIDOracle Multimedia 19.0.0.0.0 VALIDSpatial 19.0.0.0.0 VALIDOracle OLAP API 19.0.0.0.0 VALIDOracle Label Security 19.0.0.0.0 VALIDOracle Database Vault 19.0.0.0.0 VALID 15 rows selected. — set linesize 300col status for a10col ACTION_TIME for a35select 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 1234col owner for a15select owner,count(*) from dba_objects where status=’INVALID’ group by owner order by owner; OWNER COUNT(*)————— ———-HR                4 — SQL> set lines 1234 pages 1234col owner for a18col object_name for a30col object_type for a18col status for a10select 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 INVALIDHR SECURE_EMPLOYEES TRIGGER 11-06-25 INVALIDHR ADD_JOB_HISTORY PROCEDURE 11-06-25 INVALIDHR UPDATE_JOB_HISTORY TRIGGER 11-06-25 INVALID — To recompile the invalid objects run the utlrp.sql @?/rdbms/admin/utlrp.sql SQL> select count(*)

Downgrade Standalone DB (Non-CDB) from 19c (19.27) to 12c (12.2.0.1) Using Flashback Database oracledna Oracle Standalone Database June 14, 2025 This post is to demonstrate the downgrade standalone Oracle Database (Non-CDB) from 19c (19.27) to 12c (12.2.0.1) Using Flashback Database. In this scenario compatible parameter was not updated after DB upgrade from 12c to 19c. 1. Check the GRP created before upgrading 12c DB to 19c. set linesize 300col name format a30col time format a32col scn for 999999999999999999col storage_size for 999999999999999SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size FROM v$restore_point; NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE—————————— ——————- ——————————– ——————— — —————-AUTOUPGRADE_9212_PROD122010 1551009 14-06-25 8:40:55.000000000 AM 2 YES 1048576000 2. Flashback to the GRP using the upgraded 19c home. 2.1 Shutdown the 19c DB. SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down. 2.2 Startup the 19c DB in mount state.Ensure 19c Home and Environment variable is set. SQL> startup mountORACLE instance started.Total System Global Area 2063597432 bytesFixed Size 8941432 bytesVariable Size 520093696 bytesDatabase Buffers 1526726656 bytesRedo Buffers 7835648 bytesDatabase mounted. 2.3 Flashback to the GRP. SQL> flashback database to restore point AUTOUPGRADE_9212_PROD122010;Flashback complete. 2.4 Shutdown the DB SQL> shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down. 3. Open the database using 12c home. Set the environment variable to point to the 12c (12.2.0.1) Oracle Home (12.2.0.1). export ORACLE_HOME=/u01/app/oracle/product/12c/db_1export ORACLE_SID=prodexport PATH=/u01/app/oracle/product/12c/db_1/bin [oracle@prod ~]$ echo $ORACLE_HOME/u01/app/oracle/product/12c/db_1 [oracle@prod ~]$ echo $ORACLE_SIDprod [oracle@prod ~]$ sqlplus -VSQL*Plus: Release 12.2.0.1.0 Production [oracle@prod ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Sat Jun 14 16:35:31 2025Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to an idle instance. SQL> startup mount;ORACLE instance started.Total System Global Area 2063597568 bytesFixed Size 8622528 bytesVariable Size 587206208 bytesDatabase Buffers 1459617792 bytesRedo Buffers 8151040 bytesDatabase mounted. SQL> alter database open resetlogs;Database altered. SQL> select name,version,status from v$database, v$instance;NAME VERSION STATUS———- —————– ———-PROD 12.2.0.1.0 OPEN 4. Post downgrade check the status of components of dba_registry. set lines 1234 pages 1234col comp_name for a50col version for a15col status for a10SELECT comp_name, version, status FROM dba_registry; COMP_NAME VERSION STATUS————————————————– ————— ———-Oracle Database Catalog Views 12.2.0.1.0 VALIDOracle Database Packages and Types 12.2.0.1.0 VALIDJServer JAVA Virtual Machine 12.2.0.1.0 VALIDOracle XDK 12.2.0.1.0 VALIDOracle Database Java Packages 12.2.0.1.0 VALIDOLAP Analytic Workspace 12.2.0.1.0 VALIDOracle Real Application Clusters 12.2.0.1.0 OPTION OFFOracle XML Database 12.2.0.1.0 VALIDOracle Workspace Manager 12.2.0.1.0 VALIDOracle Text 12.2.0.1.0 VALIDOracle Multimedia 12.2.0.1.0 VALIDSpatial 12.2.0.1.0 VALIDOracle OLAP API 12.2.0.1.0 VALIDOracle Label Security 12.2.0.1.0 VALIDOracle Database Vault 12.2.0.1.0 VALID 15 rows selected. 5. Check the timezone version whether it is reverted or not. SQL> SELECT version FROM v$timezone_file;VERSION————–26 6. Take the RMAN backup. 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. Popular Posts Downgrade Standalone DB (Non-CDB) from 19c (19.27) to 12c (12.2.0.1) Using Flashback Database Upgrading Standalone Oracle Database (Non-CDB) from 12c (12.2.0.1) to 19c (19.27) by Autoupgrade Creating multitenant physical standby database in oracle 19c Recover lost datafile of primary database by using a copy of datafile of physical standby database OCI 2025 Certification Learning Paths and Exams Categories ASM (2) DataGuard (2) News (1) Oracle Standalone Database (3) Patching (1)

Upgrading Standalone Oracle Database (Non-CDB) from 12c (12.2.0.1) to 19c (19.27) by Autoupgrade oracledna Oracle Standalone Database June 14, 2025 1. Take the full RMAN backup of 12c DB. 2. Take the 12.2.0.1 Home backup. [oracle@prod 12c]$ cd /u01/app/oracle/product/12c [oracle@prod 12c]$ tar -pcvf db_1_bkp.tar db_1 [oracle@prod 12c]$ ls -lrttotal 11235676drwxr-xr-x 74 oracle oinstall 4096 Jun 14 06:53 db_1-rw-r–r– 1 oracle oinstall 11505326080 Jun 14 06:55 db_1_bkp.tar 3. Ensure DB backup is completed before upgrade. SQL> SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;no rows selected 4. Check the invalid objects and make them valid. Also, SYS and SYSTEM objects should not be in invalid state. SQL> select count(*) from dba_objects where status=’INVALID’; COUNT(*)———-0 5. Check the status of dba registry components before upgrade. All components should be in valid status. set linesize 300col comp_name for a50col version for a15col status for a10SELECT comp_name, version, status FROM dba_registry; COMP_NAME VERSION STATUS————————————————– ————— ———-Oracle Database Catalog Views 12.2.0.1.0 VALIDOracle Database Packages and Types 12.2.0.1.0 VALIDJServer JAVA Virtual Machine 12.2.0.1.0 VALIDOracle XDK 12.2.0.1.0 VALIDOracle Database Java Packages 12.2.0.1.0 VALIDOLAP Analytic Workspace 12.2.0.1.0 VALIDOracle Real Application Clusters 12.2.0.1.0 OPTION OFFOracle XML Database 12.2.0.1.0 VALIDOracle Workspace Manager 12.2.0.1.0 VALIDOracle Text 12.2.0.1.0 VALIDOracle Multimedia 12.2.0.1.0 VALIDSpatial 12.2.0.1.0 VALIDOracle OLAP API 12.2.0.1.0 VALIDOracle Label Security 12.2.0.1.0 VALIDOracle Database Vault 12.2.0.1.0 VALID 15 rows selected. 6. Take the compatible parameter value of 12c DB to compare after the DB upgrade. SQL> show parameter compatible NAME TYPE VALUE———————————— ———– ——————————compatible string 12.2.0noncdb_compatible boolean FALSE 7. Take the time zone value of 12c DB. set linesize 300col property_name for a30col value for a20SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueFROM DATABASE_PROPERTIESWHERE PROPERTY_NAME LIKE ‘DST_%’ORDER BY PROPERTY_NAME; PROPERTY_NAME VALUE—————————— ——————–DST_PRIMARY_TT_VERSION 26DST_SECONDARY_TT_VERSION 0DST_UPGRADE_STATE NONE — col version for 9999999999999SELECT version FROM v$timezone_file; VERSION————–26 8. Enable archive mode mode and Fast Recovery Area (FRA). SQL> select flashback_on from v$database;FLASHBACK_ON————————————————————————NO SQL> show parameter recoveryNAME TYPE VALUE———————————— ———– ——————————db_recovery_file_dest stringdb_recovery_file_dest_size big integer 0recovery_parallelism integer 0remote_recovery_file_dest string SQL> alter system set db_recovery_file_dest_size=20G;System altered. SQL> alter system set db_recovery_file_dest=’/u01/app/oracle/fra’;System altered.   SQL> show parameter recoveryNAME TYPE VALUE———————————— ———– ——————————db_recovery_file_dest string /u01/app/oracle/fradb_recovery_file_dest_size big integer 20Grecovery_parallelism integer 0remote_recovery_file_dest string — SQL> select name,open_mode,log_mode from v$database; NAME OPEN_MODE LOG_MODE——— ——————– ————PROD READ WRITE NOARCHIVELOG SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down. SQL> startup mount;ORACLE instance started.Total System Global Area 2063597568 bytesFixed Size 8622528 bytesVariable Size 587206208 bytesDatabase Buffers 1459617792 bytesRedo Buffers 8151040 bytesDatabase mounted. SQL>alter database archivelog;Database altered. SQL> alter database open;Database altered. SQL> select name,open_mode,log_mode from v$database;NAME OPEN_MODE LOG_MODE——— ——————– ————PROD READ WRITE ARCHIVELOG 9. Check the status of all materialized views (MV), and refresh any materialized views that are not fresh. SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;no rows selected 10. Download the most recent version of “Autoupgrade.jar” file, from Doc 2485457.1 In 19c home, take the backup of existing autoupgrade.jar file and place the latest autoupgrade.jar file. cd /u01/app/oracle/product/19.0.0/db_1/rdbms/admin mv autoupgrade.jar autoupgrade.jar_bkp cp /tmp/autoupgrade.jar . 11. Generate a sample config file and edit accordingly. Make sure export the 12c Oracle home before running the “autoupgrade.jar” commands. export ORACLE_HOME=/u01/app/oracle/product/12c/db_1 /u01/app/oracle/product/19.0.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/autoupgrade.jar -create_sample_file config /u01/config.cfg [oracle@prod ~]$ /u01/app/oracle/product/19.0.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/autoupgrade.jar -create_sample_file config /u01/config.cfgCreated sample configuration file /u01/config.cfg — Now, edit the config file upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgradeupg1.sid=produpg1.source_home=/u01/app/oracle/product/12c/db_1upg1.target_home=/u01/app/oracle/product/19.0.0/db_1upg1.start_time=NOWupg1.upgrade_node=produpg1.run_utlrp=yesupg1.timezone_upg=yesupg1.target_version=19 To check the list of checks which autoaupgrade.jar performs use below command./u01/app/oracle/product/19.0.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/autoupgrade.jar -listchecks 12. Run the upgrade in analyze mode to validate any expected issues with the upgrade. /u01/app/oracle/product/19.0.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/autoupgrade.jar -config /u01/config.cfg -mode analyze   [oracle@prod u01]$ /u01/app/oracle/product/19.0.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/autoupgrade.jar -config /u01/config.cfg -mode analyzeAutoUpgrade 25.3.250509 launched with default internal optionsProcessing config file …+——————————–+| Starting AutoUpgrade execution |+——————————–+1 Non-CDB(s) will be analyzedType ‘help’ to list console commandsupg> status Config User configuration file [/u01/config.cfg]General logs location [/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto]Mode [ANALYZE]Jobs Summary Total databases in configuration file [1]Total Non-CDB being processed [1]Total Containers being processed [0] Jobs finished successfully [0]Jobs finished/stopped [0]Jobs in progress [1] Progress+—+———————————————————+|Job| Progress|+—+———————————————————+|102|[||||||||||||||||| ] 33 %|+—+———————————————————+ upg>upg> Job 102 completed——————- Final Summary ——————–Number of databases [ 1 ] Jobs finished [1]Jobs failed [0] Please check the summary report at:/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.html/u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log — [oracle@prod ~]$ cat /u01/app/oracle/cfgtoollogs/autoupgrade/cfgtoollogs/upgrade/auto/status/status.log==========================================Autoupgrade Summary Report==========================================[Date] Sat Jun 14 08:33:16 IST 2025[Number of Jobs] 1==========================================[Job ID] 102==========================================[DB Name] prod[Version Before Upgrade] 12.2.0.1.0[Version After Upgrade] 19.27.0.0.0——————————————[Stage Name] PRECHECKS[Status] SUCCESS[Start Time] 2025-06-14 08:32:48[Duration] 0:00:26[Log Directory] /u01/app/oracle/cfgtoollogs/autoupgrade/prod/102/prechecks[Detail] /u01/app/oracle/cfgtoollogs/autoupgrade/prod/102/prechecks/prod_preupgrade.logCheck passed and no manual intervention needed——————————————[oracle@prod ~]$ Note:The output files list the status of the analysis, and any manual intervention that is needed before an upgrade can take place. 13. Run Autoupgrade in Deploy mode. Important:Before running Autoupgrade in Deploy mode, we must validate that there are no invalid objects or components that may affect the process. /u01/app/oracle/product/19.0.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/autoupgrade.jar -config /u01/config.cfg -mode deploy [oracle@prod ~]$ /u01/app/oracle/product/19.0.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/autoupgrade.jar -config /u01/config.cfg -mode deployAutoUpgrade 25.3.250509 launched with default internal optionsProcessing config file …+——————————–+| Starting AutoUpgrade execution |+——————————–+1 Non-CDB(s) will be processedType ‘help’ to list console commandsupg>   upg> lsj+—-+——-+———+———+——-+———-+——-+—————————-+|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|+—-+——-+———+———+——-+———-+——-+—————————-+| 103| prod|PRECHECKS|EXECUTING|RUNNING| 08:40:54|20s ago|Loading database information|+—-+——-+———+———+——-+———-+——-+—————————-+Total jobs 1upg>   upg> status -job 103Details Job No 103Oracle SID prodStart Time 25/06/14 08:40:54Elapsed (min): 0End time: N/A Logfiles Logs Base: /u01/app/oracle/cfgtoollogs/autoupgrade/prodJob logs: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/103Stage logs: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/103/prefixupsTimeZone: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/tempRemote Dirs: StagesSETUP <1 minGRP <1 minPREUPGRADE <1 minPRECHECKS <1 minPREFIXUPS ~0 min (RUNNING)DRAINDBUPGRADEPOSTCHECKSPOSTFIXUPSPOSTUPGRADESYSUPDATES Stage-Progress Per Container +——–+———+|Database|PREFIXUPS|+——–+———+| prod| 0 % |+——–+———+upg>   upg> status -job 103Details Job No 103Oracle SID prodStart Time 25/06/14 08:40:54Elapsed (min): 12End time: N/A Logfiles Logs Base: /u01/app/oracle/cfgtoollogs/autoupgrade/prodJob logs: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/103Stage logs: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/103/dbupgradeTimeZone: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/tempRemote Dirs: StagesSETUP <1 minGRP <1 minPREUPGRADE <1 minPRECHECKS <1 minPREFIXUPS 3 minDRAIN <1 minDBUPGRADE ~7 min (RUNNING)POSTCHECKSPOSTFIXUPSPOSTUPGRADESYSUPDATES Stage-Progress Per Container +——–+———+|Database|DBUPGRADE|+——–+———+| prod| 14 % |+——–+———+upg>   upg> status -job 103Details Job No 103Oracle SID prodStart Time 25/06/14 08:40:54Elapsed (min): 48End time: N/A Logfiles Logs Base: /u01/app/oracle/cfgtoollogs/autoupgrade/prodJob logs: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/103Stage logs: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/103/postfixupsTimeZone: /u01/app/oracle/cfgtoollogs/autoupgrade/prod/tempRemote Dirs: StagesSETUP <1 minGRP <1 minPREUPGRADE <1 minPRECHECKS <1 minPREFIXUPS 3 minDRAIN <1 minDBUPGRADE 42 minDISPATCH <1 minPOSTCHECKS <1 minDISPATCH <1 minPOSTFIXUPS ~0 min (RUNNING)POSTUPGRADESYSUPDATES Stage-Progress Per Container +——–+———-+|Database|POSTFIXUPS|+——–+———-+| prod| 0 % |+——–+———-+upg>   StagesSETUP <1 minGRP <1 minPREUPGRADE <1 minPRECHECKS <1 minPREFIXUPS 3 minDRAIN <1 minDBUPGRADE 42 minDISPATCH <1 minPOSTCHECKS <1 minDISPATCH <1 minPOSTFIXUPS 12 minPOSTUPGRADE ~1 min (RUNNING)SYSUPDATES Stage-Progress Per

Recover lost datafile of primary database by using a copy of datafile of physical standby database oracledna Oracle Core Database April 21, 2025 This post is to demonstrate how to recover lost (at OS level) datafile of primary database using a copy of datafile of physical standby database in 19c (19.3.0.0.0). Datafile 7 of primary database was lost  and need to restore and recover the datafile 7 from physical standby database. Below errors found in alert log of primary database. Errors in file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_mz00_3779.trc: ORA-01110: data file 7: ‘/u01/app/oracle/oradata/PROD/users01.dbf’ ORA-01565: error in identifying file ‘/u01/app/oracle/oradata/PROD/users01.dbf’ ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 Checker run found 1 new persistent data failures 1. This is important step. 1.a). Before replacing the lost datafile with the copy of Standby datafile, please make sure to have the necessary archive log files which are needed for the recovery of the lost datafile. 1.b). Ensure to check that source (Standby DB) datafile is corruption free. Run DBV and RMAN validate to check for corruption. On Standby Server, verify whether the source datafile is corruption free not by dbv utility, dbv must return with zero corrupted pages. dbv file= /u01/app/oracle/oradata/STDBY/users01.dbf blocksize=8192 logfile=/home/oracle/dbv_stdby.log [oracle@stdby ~]$ dbv file= /u01/app/oracle/oradata/STDBY/users01.dbf blocksize=8192 logfile=/home/oracle/dbv_stdby.log DBVERIFY: Release 19.0.0.0.0 – Production on Mon Apr 21 10:14:24 2025 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. [oracle@stdby ~]$ cat dbv_stdby.log DBVERIFY: Release 19.0.0.0.0 – Production on Mon Apr 21 10:14:24 2025 Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved. DBVERIFY – Verification starting : FILE = /u01/app/oracle/oradata/STDBY/users01.dbf DBVERIFY – Verification complete Total Pages Examined         : 640 Total Pages Processed (Data) : 60 Total Pages Failing   (Data) : 0 Total Pages Processed (Index): 15 Total Pages Failing   (Index): 0 Total Pages Processed (Other): 464 Total Pages Processed (Seg)  : 0 Total Pages Failing   (Seg)  : 0 Total Pages Empty            : 101 Total Pages Marked Corrupt   : 0 Total Pages Influx           : 0 Total Pages Encrypted        : 0 Highest block SCN            : 1252497 (0.1252497) Also, validate the datafile of Standby DB with RMAN. RMAN> backup validate check logical datafile 7; Starting backup at 21-APR-2025 10:17:14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=69 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/u01/app/oracle/oradata/STDBY/users01.dbf channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 List of Datafiles ================= File Status Marked Corrupt Empty Blocks Blocks Examined High SCN —- —— ————– ———— ————— ———- 7    OK     0              101          641             1252497   File Name: /u01/app/oracle/oradata/STDBY/users01.dbf   Block Type Blocks Failing Blocks Processed   ———- ————– —————-   Data       0              60   Index      0              15   Other      0              464 Finished backup at 21-APR-2025 10:17:17 And, check the DB block corruption, the below view must return zero rows SQL> select * from v$database_block_corruption; no rows selected 2. Take the backup of physical standby database datafile RMAN> backup datafile 7 format ‘/tmp/users_df7_stdby.bkp’; Starting backup at 21-APR-2025 10:20:30 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=70 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/u01/app/oracle/oradata/STDBY/users01.dbf channel ORA_DISK_1: starting piece 1 at 21-APR-2025 10:20:31 channel ORA_DISK_1: finished piece 1 at 21-APR-2025 10:20:32 piece handle=/tmp/users_df7_stdby.bkp tag=TAG20250421T102031 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 21-APR-2025 10:20:32 Starting Control File and SPFILE Autobackup at 21-APR-2025 10:20:32 piece handle=/u01/app/oracle/product/19.0.0/db_1/dbs/c-608515774-20250421-01 comment=NONE Finished Control File and SPFILE Autobackup at 21-APR-2025 10:20:33 3. Transfer the backup pieces to primary database server [oracle@stdby tmp]$ scp users_df7_stdby.bkp oracle@XXXXX:/tmp/ The authenticity of host ‘XXX.XXX.XXX.XXX (XXX.XXX.XXX.XXX)’ can’t be established. ECDSA key fingerprint is SHA256:ePPILxuIgt3vNdG38wHx5foswlo2au4IRCVAtPy6qFo. ECDSA key fingerprint is MD5:a6:3c:4f:58:f2:b7:b7:7d:19:4f:14:bf:cb:89:bc:d2. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added ‘XXX.XXX.XXX.XXX’ (ECDSA) to the list of known hosts. oracle@XXX.XXX.XXX.XXX’s password: users_df7_stdby.bkp                                                                                    100% 2448KB  40.6MB/s   00:00 4. In primary DB Server, catalog the backuppiece and check the availability for use. RMAN> catalog backuppiece ‘/tmp/users_df7_stdby.bkp’; using target database control file instead of recovery catalog channel default: cataloged backup piece backup piece handle=/tmp/users_df7_stdby.bkp RECID=5 STAMP=1199010274 RMAN> list backuppiece ‘/tmp/users_df7_stdby.bkp’; List of Backup Pieces BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name ——- ——- — — ———– ———– ———- 5       5       1   1   AVAILABLE   DISK        /tmp/users_df7_stdby.bkp RMAN> list backup of datafile 7; List of Backup Sets =================== BS Key  Type LV Size       Device Type Elapsed Time Completion Time ——- —- — ———- ———– ———— ——————– 3       Full    2.38M      DISK        00:00:00     20-APR-2025 17:14:30         BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20250420T171430         Piece Name: /tmp/users_df7_prim.bkp   List of Datafiles in backup set 3   File LV Type Ckp SCN    Ckp Time             Abs Fuz SCN Sparse Name   —- — —- ———- ——————– ———– —— —-   7       Full 2287979    20-APR-2025 17:14:30              NO    /u01/app/oracle/oradata/PROD/users01.dbf BS Key  Type LV Size       Device Type Elapsed Time Completion Time ——- —- — ———- ———– ———— ——————– 5       Full    2.38M      DISK        00:00:00     21-APR-2025 10:20:31         BP Key: 5   Status: AVAILABLE  Compressed: NO