Clone Remote PDB using db link from one CDB to another CDB oracledna Oracle Standalone Database August 21, 2026 This post demonstrates how to clone the remote pluggable database from one CDB to another CDB using db link. Here, we are cloning the pluggable database, PRODPDB1 from the root container database, PROD to DEV in the root container database, DEV. Environment:==========Source CDB and PDB: PROD and PRODPDB1Target CDB and PDB: DEV and DEVPDB2Source and Target DB Version: 19.27OS Version: Oracle Linux 9.4 1. Create a clone user in source pluggable database PRODPDB1 of CDB PROD.   This user is used for db link creation from target container database, DEV.   SQL> alter session set container=PRODPDB1;Session altered. SQL> create user cloneuser identified by cloneuser;User created. 2. Grant create session and create pluggable database privileges to cloneuser of PRODPDB1 of CDB PROD. SQL> show con_name CON_NAME——————————PRODPDB1 SQL> grant create session, create pluggable database to cloneuser;Grant succeeded. SQL> select * from dba_sys_privs where grantee = ‘CLONEUSER’; GRANTEE PRIVILEGE ADM COM INH——— —————————————- — — —CLONEUSER CREATE PLUGGABLE DATABASE NO NO NOCLONEUSER CREATE SESSION NO NO NO 3. Create a test table and insert data to validate the data integrity after cloning PRODPDB1 from PROD to DEV. SQL> show con_name CON_NAME——————————PRODPDB1   SQL> create table PDBCLONETEST (c1 number);Table created.   SQL> insert into PDBCLONETEST values(1);1 row created. SQL> commit;Commit complete. SQL> select * from PDBCLONETEST;C1———-1 4. Find the data file locations of pluggable database PRODPDB1 of CDB PROD. The datafile location path needs to be included in the file_name_convert parameter in the cloning script. SQL> alter session set container=PRODPDB1;Session altered. SQL> select name from v$datafile;NAME——————————————————————————–/u01/app/oracle/oradata/PROD/prodpdb1/system01.dbf/u01/app/oracle/oradata/PROD/prodpdb1/sysaux01.dbf/u01/app/oracle/oradata/PROD/prodpdb1/undotbs01.dbf/u01/app/oracle/oradata/PROD/prodpdb1/users01.dbf 5. Close the PRODPDB1 in PROD. SQL> select name,CDB from v$database;NAME CDB——— —PROD YES   SQL> alter pluggable database PRODPDB1 close;Pluggable database altered.   SQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED———- —————————— ———- ———-2 PDB$SEED READ ONLY NO3 PRODPDB1 MOUNTED 6. Open the PRODPDB1 at PROD in read only mode. PRODPDB1 should be in READ ONLY mode for cloning SQL> alter pluggable database PRODPDB1 open read only;Pluggable database altered.   SQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED———- —————————— ———- ———-2 PDB$SEED READ ONLY NO3 PRODPDB1 READ ONLY NO 7. Login to Target Container Database, DEV. SQL> show con_nameCON_NAME——————————CDB$ROOT   SQL> select name,CDB from v$database;NAME CDB——— —DEV YES 8. Add the tnsentry of PRODPDB1 in tnsnames.ora of target server (DEV). PRODPDB1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.166)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = prodpdb1)))   [oracle@prod admin]$ tnsping PRODPDB1TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 21-AUG-2026 13:14:27Copyright (c) 1997, 2025, Oracle. All rights reserved.Used parameter files:/u01/app/oracle/product/19.30/db/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.166)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prodpdb1)))OK (0 msec) 9. Create the database link in target database(DEV) to clone the PRODPDB1 from PROD to DEV database. SQL> select name,cdb from v$database;NAME CDB———- —DEV YES SQL> create database link clone_prodpdb1 connect to cloneuser identified by cloneuser using ‘prodpdb1’;Database link created. Check the connectvity from DEB database to pluggable database PRODPDB1 of PROD CDB. SQL> select * from dual@clone_prodpdb1;D–X   [oracle@prod ~]$ sqlplus cloneuser/cloneuser@prodpdb1SQL*Plus: Release 19.0.0.0.0 – Production on Fri Aug 21 15:40:52 2026Version 19.27.0.0.0Copyright (c) 1982, 2024, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – ProductionVersion 19.27.0.0.0   SQL> show con_nameCON_NAME——————————PRODPDB1 10. Run CREATE PLUGGABLE DATABASE command for cloning PRODPDB1. The new clone pluggable database DEVPDB2 will becreated in the DEV. SQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED———- —————————— ———- ———-2 PDB$SEED READ ONLY NO3 DEVPDB1 READ WRITE NO   SQL> create pluggable database devpdb2 from prodpdb1@clone_prodpdb1 file_name_convert=(‘/u01/app/oracle/oradata/PROD/prodpdb1/’,’/u01/app/oracle/oradata/DEV/devpdb2′);Pluggable database created. SQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED———- —————————— ———- ———-2 PDB$SEED READ ONLY NO3 DEVPDB1 READ WRITE NO4 DEVPDB2 MOUNTED   SQL> alter pluggable database DEVPDB2 open;Pluggable database altered. 11. Check the data integrity. Connect to newly cloned DEVPDB2. SQL> alter session set container=DEVPDB2;Session altered. SQL> show con_nameCON_NAME——————————DEVPDB2   SQL> select * from PDBCLONETEST;C1———-1 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 Categories ASM (2) DataGuard (11) News (1) OEM (1) Oracle Standalone Database (21) Patching (1) RAC (6) Uncategorized (1) WebLogic (2)

Patching Oracle Database 19c oracledna Oracle Standalone Database August 13, 2026 This post demonstrates the patching of the Oracle Database from 19.27 to 19.30. Environment:============DB Version: 19.27OS Version: OEL 9.4DB Type: Multitenant (CDB with two PDBs)Patching RU: 19.30 1. Take the backup of database.   It is recommended to take the full backup of Database. 2. Take the backup of ORACLE HOME and inventory. [oracle@prod ~]$ cd /u01/app/oracle/product/19c   [oracle@prod 19c]$ tar -pcvf /miss/oracle/db_home_bkp.tar db     [oracle@prod ~]$ cat /etc/oraInst.locinventory_loc=/u01/app/oraInventoryinst_group=oinstall [oracle@prod ~]$ cd /u01/app/tar -pcvf /miss/oracle/OraInventory_Backup.tar oraInventory 3. Upgrade the OPatch to latest version cd $ORACLE_HOME mv OPatch OPatch_old_date unzip p6880880_190000_Linux-x86-64 cd OPatch [oracle@prod OPatch]$ ./opatch versionOPatch Version: 12.2.0.1.52OPatch succeeded. 4. Check Applied list of patches before patching. [oracle@prod ~]$ cd $ORACLE_HOME/OPatch [oracle@prod OPatch]$ ./opatch lspatches37642901;Database Release Update : 19.27.0.0.250415 (37642901)29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)OPatch succeeded. 5. Check Patch Conflict cd $ORACLE_HOME/OPatch [oracle@prod OPatch]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /miss/oracle/19.30/38632161Oracle Interim Patch Installer version 12.2.0.1.52Copyright (c) 2026, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/19c/dbCentral Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/19c/db/oraInst.locOPatch version : 12.2.0.1.52OUI version : 12.2.0.7.0Log file location : /u01/app/oracle/product/19c/db/cfgtoollogs/opatch/opatch2026-08-12_04-56-16AM_1.log Invoking prereq “checkconflictagainstohwithdetail” Prereq “checkConflictAgainstOHWithDetail” passed. OPatch succeeded. 6. Check System Space check [oracle@prod OPatch]$ $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseDir /miss/oracle/19.30/38632161Oracle Interim Patch Installer version 12.2.0.1.52Copyright (c) 2026, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/19c/dbCentral Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/19c/db/oraInst.locOPatch version : 12.2.0.1.52OUI version : 12.2.0.7.0Log file location : /u01/app/oracle/product/19c/db/cfgtoollogs/opatch/opatch2026-08-12_04-58-54AM_1.log Invoking prereq “checksystemspace” Prereq “checkSystemSpace” passed. OPatch succeeded. 7. Check the consistency of inventory information for database home [oracle@prod OPatch]$ /u01/app/oracle/product/19c/db/OPatch/opatch lsinventory -detail -oh /u01/app/oracle/product/19c/dbOracle Interim Patch Installer version 12.2.0.1.52Copyright (c) 2026, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/19c/dbCentral Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/19c/db/oraInst.locOPatch version : 12.2.0.1.52OUI version : 12.2.0.7.0Log file location : /u01/app/oracle/product/19c/db/cfgtoollogs/opatch/opatch2026-08-12_05-05-23AM_1.log…….Patch 29585399 : applied on Thu Apr 18 12:51:33 IST 2019Unique Patch ID: 22840393Patch description: “OCW RELEASE UPDATE 19.3.0.0.0 (29585399)”Created on 9 Apr 2019, 19:12:47 hrs PST8PDTBugs fixed:27222128, 27572040, 27604329, 27760043, 27877830, 28302580, 2847067328621543, 28642469, 28699321, 28710663, 28755846, 28772816, 2878532128800508, 28808652, 28815557, 28847541, 28847572, 28870496, 2887104028874416, 28877252, 28881191, 28881848, 28888083, 28911140, 2892525028925460, 28935956, 28940472, 28942694, 28951332, 28963036, 2896877928980448, 28995287, 29003207, 29003617, 29016294, 29018680, 2902487629026154, 29027933, 29047127, 29052850, 29058476, 29111631, 2911245529117337, 29123444, 29125708, 29125786, 29129476, 29131772, 2913245629139727, 29146157, 29147849, 29149170, 29152603, 29152752, 2915463129154636, 29154829, 29159216, 29159661, 29160462, 29161923, 2916954029169739, 29170717, 29173618, 29181568, 29182920, 29183298, 2918609129191827, 29201143, 29201695, 29209545, 29210577, 29210610, 2921062429210683, 29213641, 29219627, 29224294, 29225861, 29229839, 2923593429242906, 29243749, 29244495, 29244766, 29244968, 29248723, 2924958329251564, 29255616, 29260224, 29261695, 29271019, 29273360, 2928209029282666, 29285453, 29285621, 29290235, 29292232, 29293806, 2929475329299830, 29307090, 29307109, 29311336, 29329675, 29330791, 2933929929357821, 29360467, 29360775, 29367971, 29368725, 29379299, 2937938129380527, 29381000, 29382296, 29391301, 29393649, 29402110, 2941193129413360, 29457319, 29465047, 3Files Touched:clustdb.sql –> ORACLE_HOME/srvm/admin/clustdb.sqlPatch Location in Inventory:/u01/app/oracle/product/19c/db/inventory/oneoffs/29585399Patch Location in Storage area:/u01/app/oracle/product/19c/db/.patch_storage/29585399_Apr_9_2019_19_12_47 OPatch succeeded. 8. Stop the listener and database. [oracle@prod ~]$ lsnrctl stop LISTENER_PRODLSNRCTL for Linux: Version 19.0.0.0.0 – Production on 12-AUG-2026 05:08:24Copyright (c) 1991, 2025, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prod)(PORT=1521)))The command completed successfully SQL> shut immediateDatabase closed.Database dismounted.ORACLE instance shut down. 9. Apply the RU patch. [oracle@prod ~]$ $ORACLE_HOME/OPatch/opatch apply -oh /u01/app/oracle/product/19c/db -local /miss/oracle/19.30/38632161Oracle Interim Patch Installer version 12.2.0.1.52Copyright (c) 2026, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/19c/dbCentral Inventory : /u01/app/oraInventoryfrom : /u01/app/oracle/product/19c/db/oraInst.locOPatch version : 12.2.0.1.52OUI version : 12.2.0.7.0Log file location : /u01/app/oracle/product/19c/db/cfgtoollogs/opatch/opatch2026-08-12_05-10-41AM_1.log Verifying environment and performing prerequisite checks…OPatch continues with these patches: 38632161 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/19c/db’) Is the local system ready for patching? [y|n]yUser Responded with: YBacking up files…Applying interim patch ‘38632161’ to OH ‘/u01/app/oracle/product/19c/db’ApplySession: Optional component(s) [ oracle.network.gsm, 19.0.0.0.0 ] , [ oracle.assistants.asm, 19.0.0.0.0 ] , [ oracle.crypto.rsf, 19.0.0.0.0 ] , [ oracle.datamining, 19.0.0.0.0 ] , [ oracle.oraolap.mgmt, 19.0.0.0.0 ] , [ oracle.pg4appc, 19.0.0.0.0 ] , [ oracle.pg4mq, 19.0.0.0.0 ] , [ oracle.precomp.companion, 19.0.0.0.0 ] , [ oracle.rdbms.ic, 19.0.0.0.0 ] , [ oracle.rdbms.tg4db2, 19.0.0.0.0 ] , [ oracle.sdo.companion, 19.0.0.0.0 ] , [ oracle.tfa, 19.0.0.0.0 ] , [ oracle.rdbms.tg4msql, 19.0.0.0.0 ] , [ oracle.ons.eons.bwcompat, 19.0.0.0.0 ] , [ oracle.options.olap.api, 19.0.0.0.0 ] , [ oracle.rdbms.tg4sybs, 19.0.0.0.0 ] , [ oracle.net.cman, 19.0.0.0.0 ] , [ oracle.options.olap, 19.0.0.0.0 ] , [ oracle.network.cman, 19.0.0.0.0 ] , [ oracle.pg4mq, 19.0.0.0.0 ] , [ oracle.oraolap.mgmt, 19.0.0.0.0 ] , [ oracle.java.sqlj.sqljruntime, 19.0.0.0.0 ] , [ oracle.datamining, 19.0.0.0.0 ] , [ oracle.xdk.companion, 19.0.0.0.0 ] , [ oracle.rdbms.tg4tera, 19.0.0.0.0 ] , [ oracle.ldap.ztk, 19.0.0.0.0 ] , [ oracle.ons.cclient, 19.0.0.0.0 ] , [ oracle.oid.client, 19.0.0.0.0 ] , [ oracle.rdbms.tg4ifmx, 19.0.0.0.0 ] , [ oracle.jdk, 1.8.0.191.0 ] , [ oracle.jdk, 1.8.0.391.11 ] not present in the Oracle Home or a higher version is found. Patching component oracle.rdbms.rsf, 19.0.0.0.0… Patching component oracle.rdbms, 19.0.0.0.0… Patching component oracle.rdbms.util, 19.0.0.0.0… Patching component oracle.assistants.acf, 19.0.0.0.0… Patching component oracle.assistants.deconfig, 19.0.0.0.0… Patching component oracle.assistants.server, 19.0.0.0.0… Patching component oracle.blaslapack, 19.0.0.0.0… Patching component oracle.buildtools.rsf, 19.0.0.0.0… Patching component oracle.ctx, 19.0.0.0.0… Patching component oracle.dbdev, 19.0.0.0.0… Patching component oracle.dbjava.ic, 19.0.0.0.0… Patching component oracle.dbjava.jdbc, 19.0.0.0.0… Patching component oracle.dbjava.ucp, 19.0.0.0.0… Patching component oracle.duma, 19.0.0.0.0… Patching component oracle.javavm.client, 19.0.0.0.0… Patching component oracle.ldap.client, 19.0.0.0.0… Patching component oracle.ldap.owm, 19.0.0.0.0… Patching component oracle.ldap.rsf, 19.0.0.0.0… Patching component oracle.ldap.security.osdt, 19.0.0.0.0… Patching component oracle.marvel, 19.0.0.0.0… Patching component oracle.network.rsf, 19.0.0.0.0… Patching component oracle.odbc.ic, 19.0.0.0.0… Patching component oracle.ons, 19.0.0.0.0… Patching component oracle.ons.ic, 19.0.0.0.0… Patching component oracle.oracore.rsf, 19.0.0.0.0… Patching component oracle.perlint, 5.28.1.0.0… Patching component oracle.precomp.common.core, 19.0.0.0.0… Patching component oracle.precomp.rsf, 19.0.0.0.0… Patching component oracle.rdbms.crs, 19.0.0.0.0… Patching component oracle.rdbms.dbscripts, 19.0.0.0.0… Patching component oracle.rdbms.deconfig, 19.0.0.0.0… Patching component oracle.rdbms.install.common, 19.0.0.0.0… Patching component oracle.rdbms.oci, 19.0.0.0.0… Patching component oracle.rdbms.rsf.ic, 19.0.0.0.0… Patching component oracle.rdbms.scheduler, 19.0.0.0.0… Patching component oracle.rhp.db, 19.0.0.0.0… Patching component oracle.rsf, 19.0.0.0.0… Patching component oracle.sdo, 19.0.0.0.0… Patching component oracle.sdo.locator.jrf, 19.0.0.0.0… Patching component oracle.sqlj.sqljruntime, 19.0.0.0.0… Patching component oracle.sqlplus, 19.0.0.0.0… Patching component oracle.sqlplus.ic, 19.0.0.0.0… Patching component oracle.tfa.db, 19.0.0.0.0… Patching component oracle.wwg.plsql, 19.0.0.0.0… Patching component oracle.xdk.rsf, 19.0.0.0.0… Patching component oracle.xdk.parser.java, 19.0.0.0.0… Patching component oracle.rdbms.lbac, 19.0.0.0.0… Patching component oracle.ldap.ssl, 19.0.0.0.0… Patching component oracle.network.aso, 19.0.0.0.0… Patching component oracle.xdk, 19.0.0.0.0… Patching component oracle.nlsrtl.rsf.core, 19.0.0.0.0… Patching component oracle.odbc, 19.0.0.0.0… Patching component oracle.rdbms.rat, 19.0.0.0.0… Patching component oracle.rdbms.dv, 19.0.0.0.0… Patching component oracle.oraolap.dbscripts, 19.0.0.0.0… Patching component oracle.rdbms.hs_common, 19.0.0.0.0… Patching component oracle.ldap.rsf.ic, 19.0.0.0.0… Patching component oracle.oraolap, 19.0.0.0.0… Patching component oracle.install.deinstalltool, 19.0.0.0.0… Patching component oracle.mgw.common, 19.0.0.0.0… Patching component

Installing Oracle Client 19c on Oracle Linux 9 oracledna Oracle Standalone Database August 8, 2026 This post demonstrates installing Oracle Client 19c on Oracle Linux 9. Oracle Client 19c is the connectivity layer that allows applications to interact with Oracle Database 19c (and earlier versions), providing drivers, libraries, and utilities for SQL execution, data loading, and secure communication. It comes in multiple installation types, including the lightweight Instant Client, and supports cross-platform environments. To install the Oracle Client 19c on Oracle Linux 9, we must use a Release Update (RU) 19.19 or higher (such as 19.27) because the base 19.3 version does not natively support Oracle Linux 9. Environment:============Oracle Linux: 9.4Client: 19.27 Note: LINUX.X64_193000_client_home.zip is an image of and Administrator type installation. 1. Install oracle-database-preinstall-19c package.   As a root user,install Oracle preinstallation RPM for Linux release to configure operating system.   The oracle-database-preinstall-19c package is an RPM provided by Oracle for Linux systems that automatically configures the operating system to meet Oracle Database 19c installation requirements. It sets up users, groups, kernel parameters, and system limits so we don’t have to manually adjust them before installing the client/database. [root@prod ~]# dnf install -y oracle-database-preinstall-19cLast metadata expiration check: 1 day, 11:17:50 ago on Thursday 06 August 2026 03:27:43 PM.Dependencies resolved.=====================================================================================================================================================Package Architecture Version Repository Size=====================================================================================================================================================Installing:oracle-database-preinstall-19c x86_64 1.0-3.el9 ol9_appstream 35 kInstalling dependencies:libnsl x86_64 2.34-100.0.1.el9 ol9_baseos_latest 69 k Transaction Summary=====================================================================================================================================================Install 2 Packages Total download size: 104 kInstalled size: 181 kDownloading Packages:(1/2): oracle-database-preinstall-19c-1.0-3.el9.x86_64.rpm 35 kB/s | 35 kB 00:01(2/2): libnsl-2.34-100.0.1.el9.x86_64.rpm 21 kB/s | 69 kB 00:03—————————————————————————————————————————————————–Total 32 kB/s | 104 kB 00:03Running transaction checkTransaction check succeeded.Running transaction testTransaction test succeeded.Running transactionPreparing : 1/1Installing : libnsl-2.34-100.0.1.el9.x86_64 1/2Running scriptlet: oracle-database-preinstall-19c-1.0-3.el9.x86_64 2/2Installing : oracle-database-preinstall-19c-1.0-3.el9.x86_64 2/2Running scriptlet: oracle-database-preinstall-19c-1.0-3.el9.x86_64 2/2Verifying : libnsl-2.34-100.0.1.el9.x86_64 1/2Verifying : oracle-database-preinstall-19c-1.0-3.el9.x86_64 2/2 Installed:libnsl-2.34-100.0.1.el9.x86_64 oracle-database-preinstall-19c-1.0-3.el9.x86_64 Complete! 2. Create directory for client home.   As a root user, create below directories and provide permissions.   mkdir -p /u01/app/oracle/product/19c/client chown -R oracle:oinstall /u01 chmod -R 775 /u01 3. Copy the client and RU patch binaries and unzip. Copy the Client, RU patch binaries and unzip at respective locations. Unzip the 19c Client bimaries zip file [oracle@prod ~]$ cd /u01/app/oracle/product/19c/client [oracle@prod client]$ unzip LINUX.X64_193000_client_home.zip — Unzip the 19.27 RU patch zip file. [oracle@prod ~]$ cd /miss/oracle/19.27/ [oracle@prod 19.27]$ unzip p37642901_190000_Linux-x86-64.zip [oracle@prod 19.27]$ ls -lrttotal 2233028drwxr-xr-x. 4 oracle oinstall 4096 Apr 15 2025 37642901-rw-rw-r–. 1 oracle oinstall 2282356 Apr 22 2025 PatchSearch.xml-rw-r–r–. 1 oracle oinstall 2284324411 May 2 2025 p37642901_190000_Linux-x86-64.zip 4. Upgrade the OPatch to latest version. [oracle@prod ~]$ cd /u01/app/oracle/product/19c/client [oracle@prod client]$ mv OPatch OPatch_old [oracle@prod client]$ unzip p6880880_190000_Linux-x86-64.zip [oracle@prod client]$ cd OPatch [oracle@prod OPatch]$ ./opatch versionOPatch Version: 12.2.0.1.52OPatch succeeded. 5. Run the runInstaller with applyRU to patch and then install the client. [oracle@prod ~]$ export CV_ASSUME_DISTID=OEL7 [oracle@prod ~]$ cd /u01/app/oracle/product/19c/client [oracle@prod client]$ ./runInstaller -applyRU /miss/oracle/19.27/37642901Preparing the home to patch…Applying the patch /miss/oracle/19.27/37642901…Successfully applied the patch.The log can be found at: /u01/app/oraInventory/logs/InstallActions2026-08-08_04-30-46AM/installerPatchActions_2026-08-08_04-30-46AM.logLaunching Oracle Database Client Setup Wizard..       The response file for this session can be found at:/u01/app/oracle/product/19c/client/install/response/client_2026-08-08_04-30-46AM.rsp You can find the log of this install session at:/u01/app/oraInventory/logs/InstallActions2026-08-08_04-30-46AM/installActions2026-08-08_04-30-46AM.log — Check the version of client [oracle@prod ~]$ export ORACLE_HOME=/u01/app/oracle/product/19c/client[oracle@prod ~]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@prod ~]$ sqlplus /nologSQL*Plus: Release 19.0.0.0.0 – Production on Sat Aug 8 04:40:10 2026Version 19.27.0.0.0Copyright (c) 1982, 2024, Oracle. All rights reserved. [oracle@prod ~]$ The response file for this session can be found at:/u01/app/oracle/product/19c/client/install/response/client_2026-08-08_04-30-46AM.rsp You can find the log of this install session at:/u01/app/oraInventory/logs/InstallActions2026-08-08_04-30-46AM/installActions2026-08-08_04-30-46AM.log — Check the version of client [oracle@prod ~]$ export ORACLE_HOME=/u01/app/oracle/product/19c/client[oracle@prod ~]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@prod ~]$ sqlplus /nologSQL*Plus: Release 19.0.0.0.0 – Production on Sat Aug 8 04:40:10 2026Version 19.27.0.0.0Copyright (c) 1982, 2024, Oracle. All rights reserved. [oracle@prod ~]$ sqlplus -VSQL*Plus: Release 19.0.0.0.0 – ProductionVersion 19.27.0.0.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. Popular Posts Categories ASM (2) DataGuard (11) News (1) OEM (1) Oracle Standalone Database (19) Patching (1) RAC (6) Uncategorized (1)

Upgrade Time Zone to Specific Version in Oracle Standalone Database (Non-CDB) oracledna Oracle Standalone Database August 6, 2026 This post is to demonstrate upgrade Time Zone to Specific Version in Oracle Standalone Database (Non-CDB). Starting with Oracle 19c RU 19.18, Oracle automatically bundles all available DST patches directly into the Release Update. They are automatically deployed as .dat files into $ORACLE_HOME/oracore/zoneinfo directory during the RU patching. The utltz_upg_check.sql will always detect the highest available version by default and upgrade DST version to it. To avoid this automatic selection and upgrade to specific timezone version, need to the patch 35117839. From 19.22 RU, no need to apply the patch just setting below event helps. alter session set events ‘35117839 trace name context forever, level 38’; The files are already sitting in ORACLE Home, do not need to download or apply a separate timezone patch. Only need to update the database to recognize them. In Dataguard environment, when we upgrade the timezone version on the primary database, redo logs will contain timezone metadata based on the new version and will propagate to the standby database. Make sure, the standby database must also have the same timezone file version installed in its $ORACLE_HOME/oracore/zoneinfo/. Environment:==========DB Version: 19.27OS Version: OEL 9.4DB Type: Standalone (NOn-CDB)Timezone Version: 32Target Time Zone version: 42 1. Check the current time zone version.   SQL> select * from v$timezone_file; FILENAME VERSION CON_ID——————– ———- ———-timezlrg_32.dat 32 0   SQL> select tz_version from registry$database; TZ_VERSION———-32 2. Before performing the time zone file upgrade, create a restore point.   SQL> create restore point before_tz_upg guarantee flashback database; Restore point created. 3. Set events 35117839 to override upgrading to latest available time zone. This Oracle command forces the database session to override the default automatic Daylight Saving Time (DST) version detection. Setting the event to level 34 instructs the time zone upgrade scripts (utltz_upg_check.sql) to explicitly target and upgrade the database to DST version 34 instead of picking the highest available version. SQL> alter session set events ‘35117839 trace name context forever, level 42’; Session altered. 4. Run the utltz_countstats.sql It’s used during a DBMS_DST timezone upgrade to gather statistics about how many rows in database are affected by timezone changes. No database restart required SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstats.sql Session altered. .Amount of TSTZ data using num_rows stats info in DBA_TABLES..For SYS tables first …Note: empty tables are not listed.Stat date – Owner.TableName.ColumnName – num_rows17/04/2019 – SYS.AQ$_ALERT_QT_S.CREATION_TIME – 317/04/2019 – SYS.AQ$_ALERT_QT_S.DELETION_TIME – 317/04/2019 – SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME – 317/04/2019 – SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME – 317/04/2019 – SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME – 317/04/2019 – SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME – 317/04/2019 – SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME – 117/04/2019 – SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME – 117/04/2019 – SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME – 117/04/2019 – SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME – 117/04/2019 – SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME – 117/04/2019 – SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME – 117/04/2019 – SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME – 117/04/2019 – SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME – 117/04/2019 – SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME – 117/04/2019 – SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME – 317/04/2019 – SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME – 317/04/2019 – SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME – 317/04/2019 – SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME – 117/04/2019 – SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME – 117/04/2019 – SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME – 117/04/2019 – SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME – 117/04/2019 – SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME – 117/04/2019 – SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME – 117/04/2019 – SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME – 117/04/2019 – SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME – 117/04/2019 – SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME – 117/04/2019 – SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME – 417/04/2019 – SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME – 417/04/2019 – SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME – 417/04/2019 – SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME – 117/04/2019 – SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME – 117/04/2019 – SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME – 117/04/2019 – SYS.KET$_AUTOTASK_STATUS.MW_START_TIME – 117/04/2019 – SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME – 117/04/2019 – SYS.KET$_CLIENT_CONFIG.FIELD_2 – 717/04/2019 – SYS.KET$_CLIENT_CONFIG.LAST_CHANGE – 706/08/2026 – SYS.OPTSTAT_HIST_CONTROL$.SPARE6 – 4506/08/2026 – SYS.OPTSTAT_HIST_CONTROL$.SVAL2 – 4506/08/2026 – SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP – 8417/04/2019 – SYS.OPTSTAT_USER_PREFS$.CHGTIME – 7217/04/2019 – SYS.RADM_FPTM$.TSWTZ_COL – 117/04/2019 – SYS.REG$.NTFN_GROUPING_START_TIME – 217/04/2019 – SYS.REG$.REG_TIME – 217/04/2019 – SYS.SCHEDULER$_EVENT_LOG.LOG_DATE – 2917/04/2019 – SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP – 1106/08/2026 – SYS.SCHEDULER$_JOB.END_DATE – 2306/08/2026 – SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME – 2306/08/2026 – SYS.SCHEDULER$_JOB.LAST_END_DATE – 2306/08/2026 – SYS.SCHEDULER$_JOB.LAST_START_DATE – 2306/08/2026 – SYS.SCHEDULER$_JOB.NEXT_RUN_DATE – 2306/08/2026 – SYS.SCHEDULER$_JOB.START_DATE – 2317/04/2019 – SYS.SCHEDULER$_SCHEDULE.END_DATE – 417/04/2019 – SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE – 417/04/2019 – SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE – 917/04/2019 – SYS.SCHEDULER$_WINDOW.END_DATE – 917/04/2019 – SYS.SCHEDULER$_WINDOW.LAST_START_DATE – 917/04/2019 – SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME – 917/04/2019 – SYS.SCHEDULER$_WINDOW.NEXT_START_DATE – 917/04/2019 – SYS.SCHEDULER$_WINDOW.START_DATE – 917/04/2019 – SYS.WRI$_ALERT_HISTORY.CREATION_TIME – 117/04/2019 – SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED – 117/04/2019 – SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME – 2351817/04/2019 – SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 – 2351817/04/2019 – SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME – 173317/04/2019 – SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 – 173317/04/2019 – SYS.WRI$_OPTSTAT_OPR.END_TIME – 7417/04/2019 – SYS.WRI$_OPTSTAT_OPR.SPARE6 – 7417/04/2019 – SYS.WRI$_OPTSTAT_OPR.START_TIME – 7417/04/2019 – SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME – 517917/04/2019 – SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 – 517917/04/2019 – SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME – 517917/04/2019 – SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME – 193217/04/2019 – SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 – 193217/04/2019 – SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ – 117/04/2019 – SYS.XS$PRIN.END_DATE – 1517/04/2019 – SYS.XS$PRIN.START_DATE – 15Total numrows of SYS TSTZ columns is : 70725There are in total 169 SYS TSTZ columns..For non-SYS tables …Note: empty tables are not listed.Stat date – Owner.Tablename.Columnname – num_rows17/04/2019 – GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME – 117/04/2019 – GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME – 117/04/2019 – GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME – 117/04/2019 – WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME – 117/04/2019 – WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME – 117/04/2019 – WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME – 117/04/2019 – WMSYS.WM$WORKSPACES_TABLE$.CREATETIME – 117/04/2019 – WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE – 1Total numrows of non-SYS TSTZ columns is : 8There are in total 20 non-SYS TSTZ columns.Total Minutes elapsed : 0 Session altered. 5. Run the utltz_countstar.sql It scans database for tables containing TIMESTAMP WITH TIME ZONE columns. It runs a COUNT(*) across those tables to estimate how many rows will be affected by a timezone upgrade. Database restart not required. SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstar.sql Session altered. .Estimating amount of TSTZ data using COUNT(*).This might take some time ….For SYS tables first …Note: empty tables are not listed.Owner.TableName.ColumnName – COUNT(*) of that columnSYS.AQ$_ALERT_QT_S.CREATION_TIME – 4SYS.AQ$_ALERT_QT_S.DELETION_TIME – 4SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME – 4SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME – 3SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME – 3SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME – 3SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME – 1SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME – 1SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME – 1SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME – 1SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME – 1SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME – 1SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME – 1SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME – 1SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME – 1SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME – 3SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME – 3SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME – 3SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME – 1SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME – 1SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME – 1SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME – 1SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME – 1SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME – 1SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME – 1SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME – 1SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME – 1SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME – 4SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME – 4SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME – 4SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ – 1SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME – 1SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME – 1SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME – 1SYS.KET$_AUTOTASK_STATUS.MW_START_TIME – 1SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME – 1SYS.KET$_CLIENT_CONFIG.FIELD_2 – 7SYS.KET$_CLIENT_CONFIG.LAST_CHANGE – 7SYS.OPTSTAT_HIST_CONTROL$.SPARE6 – 45SYS.OPTSTAT_HIST_CONTROL$.SVAL2 – 45SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP – 176SYS.OPTSTAT_USER_PREFS$.CHGTIME – 72SYS.RADM_FPTM$.TSWTZ_COL – 1SYS.REG$.NTFN_GROUPING_START_TIME – 2SYS.REG$.REG_TIME – 2SYS.SCHEDULER$_EVENT_LOG.LOG_DATE – 8SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP – 11SYS.SCHEDULER$_JOB.END_DATE – 23SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME – 23SYS.SCHEDULER$_JOB.LAST_END_DATE – 23SYS.SCHEDULER$_JOB.LAST_START_DATE – 23SYS.SCHEDULER$_JOB.NEXT_RUN_DATE – 23SYS.SCHEDULER$_JOB.START_DATE – 23SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE – 8SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE – 8SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE – 8SYS.SCHEDULER$_SCHEDULE.END_DATE – 4SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE – 4SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE – 9SYS.SCHEDULER$_WINDOW.END_DATE – 9SYS.SCHEDULER$_WINDOW.LAST_START_DATE – 9SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME – 9SYS.SCHEDULER$_WINDOW.NEXT_START_DATE – 9SYS.SCHEDULER$_WINDOW.START_DATE – 9SYS.WRI$_ALERT_HISTORY.CREATION_TIME – 36SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED – 36SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME – 3320SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 – 3320SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME – 273SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 – 273SYS.WRI$_OPTSTAT_OPR.END_TIME – 32SYS.WRI$_OPTSTAT_OPR.SPARE6 – 32SYS.WRI$_OPTSTAT_OPR.START_TIME

Applying Higher RU to a Single Oracle Database in a Shared Oracle Home oracledna Oracle Standalone Database August 3, 2026 This post explains how to apply a higher Release Update (RU) to a single Oracle database when multiple databases share the same Oracle home using Out-of-place patching. Environment:==========DB Type: CDB with one PDB for each databaseDB Home and Version of shared Home: /u01/app/oracle/product/19c/db, 19.27Databases: PROD (PRODPDB1), DEV (DEVPDB1)Applying RU to DB and version: DEV, 19.30New RU Home: /u01/app/oracle/product/19.30/dbOS: OEL 9.4 1. Check Current Patch Level for DEV DB. SET LINESIZE 200SET PAGESIZE 50COLUMN DB_NAME FORMAT A15COLUMN BANNER_FULL FORMAT A100 SELECT (SELECT name FROM v$database) AS DB_NAME,(SELECT banner_full FROM v$version WHERE banner LIKE ‘Oracle%’) AS BANNER_FULL FROM dual; DB_NAME BANNER_FULL————— —————————————————————————————————-DEV Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – ProductionVersion 19.27.0.0.0   —   SET LINESIZE 200SET PAGESIZE 50 COLUMN PATCH_ID FORMAT 99999999999COLUMN PATCH_UID FORMAT 99999999999COLUMN DESCRIPTION FORMAT A54COLUMN STATUS FORMAT A10COLUMN ACTION FORMAT A10COLUMN ACTION_TIME FORMAT A30 SELECT patch_id, patch_uid, description, status, action, action_timeFROM dba_registry_sqlpatchORDER BY action_time DESC; PATCH_ID PATCH_UID DESCRIPTION STATUS ACTION ACTION_TIME———— ———— —————————————————— ———- ———- ——————————37642901 27123174 Database Release Update : 19.27.0.0.250415 (37642901) SUCCESS APPLY 03-08-26 2:35:34.833297 PM   2. Create new Oracle Home for 19.30 and install 19.3 with 19.30 RDBMS binaries.   – Create directories for new ORACLE HOME– Upgrade tge OPatch with latest version– Copy and unzip the 19.3 binaries– Unzip the 19.30 RU patch at patch location [oracle@prod ~]$ mkdir -p /u01/app/oracle/product/19.30/db   [oracle@prod ~]$ export CV_ASSUME_DISTID=OEL7.8   [oracle@prod db]$ export ORACLE_HOME=/u01/app/oracle/product/19.30/db   [oracle@prod ~]$ export TNS_ADMIN=$ORACLE_HOME/network/admin   [oracle@prod ~]$ export PATH=$ORACLE_HOME/bin:$PATH   [oracle@prod ~]$ cd /u01/app/oracle/product/19.30/db   [oracle@prod db]$ ./runInstaller -applyRU /miss/oracle/19.30/38632161Preparing the home to patch…Applying the patch /miss/oracle/19.30/38632161…Successfully applied the patch.The log can be found at: /u01/app/oraInventory/logs/InstallActions2026-08-03_03-21-31PM/installerPatchActions_2026-08-03_03-21-31PM.logLaunching Oracle Database Setup Wizard… The response file for this session can be found at:/u01/app/oracle/product/19.30/db/install/response/db_2026-08-03_03-21-31PM.rsp You can find the log of this install session at:/u01/app/oraInventory/logs/InstallActions2026-08-03_03-21-31PM/installActions2026-08-03_03-21-31PM.log — Check the version. [oracle@prod ~]$ sqlplus -VSQL*Plus: Release 19.0.0.0.0 – ProductionVersion 19.30.0.0.0 —   Shut down the DEV DB. SQL> shut immediateDatabase closed.Database dismounted.ORACLE instance shut down. 3. Copy the network configuration files, password and init files to new RU home. [oracle@prod dbs]$ cp orapwdev /u01/app/oracle/product/19.30/db/dbs [oracle@prod dbs]$ cp initdev.ora /u01/app/oracle/product/19.30/db/dbs[oracle@prod dbs]$ cp spfiledev.ora /u01/app/oracle/product/19.30/db/dbs [oracle@prod admin]$ cp listener.ora /u01/app/oracle/product/19.30/db/network/admin/[oracle@prod admin]$ cp tnsnames.ora /u01/app/oracle/product/19.30/db/network/admin/[oracle@prod admin]$ cp sqlnet.ora /u01/app/oracle/product/19.30/db/network/admin/ — [oracle@prod ~]$ cd /u01/app/oracle/product/19.30/db/network/admin [oracle@prod admin]$ cat listener.ora LISTENER_DEV =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.168)(PORT = 1522))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522)))) — [oracle@prod admin]$ cat tnsnames.ora DEV =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.168)(PORT = 1522))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = dev))) LISTENER_DEV =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.168)(PORT = 1522)) — [oracle@prod admin]$ lsnrctl start LISTENER_DEV LSNRCTL for Linux: Version 19.0.0.0.0 – Production on 03-AUG-2026 16:08:48 Copyright (c) 1991, 2025, Oracle. All rights reserved. Starting /u01/app/oracle/product/19.30/db/bin/tnslsnr: please wait… TNSLSNR for Linux: Version 19.0.0.0.0 – ProductionSystem parameter file is /u01/app/oracle/product/19.30/db/network/admin/listener.oraLog messages written to /u01/app/oracle/diag/tnslsnr/prod/listener_dev/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.168)(PORT=1522)))Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.168)(PORT=1522)))STATUS of the LISTENER————————Alias LISTENER_DEVVersion TNSLSNR for Linux: Version 19.0.0.0.0 – ProductionStart Date 03-AUG-2026 16:08:48Uptime 0 days 0 hr. 0 min. 0 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/19.30/db/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/prod/listener_dev/alert/log.xmlListening Endpoints Summary…(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.168)(PORT=1522)))(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))The listener supports no servicesThe command completed successfully [oracle@prod admin]$ ps -ef | grep tnsroot 6 2 0 14:12 ? 00:00:00 [netns]oracle 64040 1 0 16:07 ? 00:00:00 /u01/app/oracle/product/19c/db/bin/tnslsnr LISTENER_PROD -inheritoracle 64667 1 0 16:08 ? 00:00:00 /u01/app/oracle/product/19.30/db/bin/tnslsnr LISTENER_DEV -inherit   4. Start the DEV db from RU 19.30 HOME. [oracle@prod ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 – Production on Mon Aug 3 16:10:19 2026Version 19.30.0.0.0Copyright (c) 1982, 2025, Oracle. All rights reserved.Connected to an idle instance.   SQL> startupORACLE instance started.Total System Global Area 1610611992 bytesFixed Size 9178392 bytesVariable Size 385875968 bytesDatabase Buffers 1207959552 bytesRedo Buffers 7598080 bytesDatabase mounted.Database opened.   SQL> @dbNAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME HOST_NAME STARTUP_TIME———- ————— ———- —————— ————— —————- ——————–DEV dev READ WRITE PRIMARY dev prod 03-AUG-2026 16:10   SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED———- —————————— ———- ———-2 PDB$SEED READ ONLY NO3 DEVPDB1 MOUNTED   SQL> alter pluggable database all open;Pluggable database altered.   SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED———- —————————— ———- ———-2 PDB$SEED READ ONLY NO3 DEVPDB1 READ WRITE NO   SQL> alter system register;System altered.   [oracle@prod ~]$ lsnrctl status LISTENER_DEVLSNRCTL for Linux: Version 19.0.0.0.0 – Production on 03-AUG-2026 16:13:40Copyright (c) 1991, 2025, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.168)(PORT=1522)))STATUS of the LISTENER————————Alias LISTENER_DEVVersion TNSLSNR for Linux: Version 19.0.0.0.0 – ProductionStart Date 03-AUG-2026 16:08:48Uptime 0 days 0 hr. 4 min. 51 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/19.30/db/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/prod/listener_dev/alert/log.xmlListening Endpoints Summary…(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.168)(PORT=1522)))(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))Services Summary…Service “58221a8a25756705e063a838a8c0da41” has 1 instance(s).Instance “dev”, status READY, has 1 handler(s) for this service…Service “86b637b62fdf7a65e053f706e80a27ca” has 1 instance(s).Instance “dev”, status READY, has 1 handler(s) for this service…Service “dev” has 1 instance(s).Instance “dev”, status READY, has 1 handler(s) for this service…Service “devXDB” has 1 instance(s).Instance “dev”, status READY, has 1 handler(s) for this service…Service “devpdb1” has 1 instance(s).Instance “dev”, status READY, has 1 handler(s) for this service…The command completed successfully 5. Run datapatch [oracle@prod ~]$ cd $ORACLE_HOME/OPatch [oracle@prod OPatch]$ ./datapatch -verboseSQL Patching tool version 19.30.0.0.0 Production on Mon Aug 3 16:18:16 2026Copyright (c) 2012, 2026, Oracle. All rights reserved. Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_68544_2026_08_03_16_18_16/sqlpatch_invocation.log Connecting to database…OKGathering database info…done Note: Datapatch will only apply or rollback SQL fixes for PDBsthat are in an open state, no patches will be applied to closed PDBs.Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation(Doc ID 1585822.1) Bootstrapping registry and package to current versions…doneDetermining current state…done Current state of interim SQL patches:No interim patches found Current state of release update SQL patches:Binary registry:19.30.0.0.0 Release_Update 260126024251: InstalledPDB CDB$ROOT:Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 03-08-26 2:35:34.833297 PMPDB DEVPDB1:Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 03-08-26 2:50:40.153831 PMPDB PDB$SEED:Applied 19.27.0.0.0 Release_Update 250406131139 successfully on 03-08-26 2:50:40.153831 PM Adding patches to installation queue and performing prereq checks…doneInstallation queue:For the following PDBs: CDB$ROOT PDB$SEED DEVPDB1No interim patches need to be rolled backPatch 38632161 (Database Release Update : 19.30.0.0.260120(REL-JAN260130) (38632161)):Apply from 19.27.0.0.0 Release_Update 250406131139 to 19.30.0.0.0

ORA-30012: undo tablespace ‘UNDOTBS1’ does not exist or of wrong type oracledna Oracle Standalone Database August 1, 2026 This post is to demonstrate how to overcome ORA-30012 error in pluggable database. ORA-30012: undo tablespace ‘UNDOTBS1’ does not exist or of wrong type. Environment=============CDB : PRODPDB: PRODPDB1Undo tablespace of PDB: UNDOTBS1DB Version: 19.27 1. Change the UNDO_MANAGEMENT to MANUAL in CDB$ROOT.   SQL> show con_nameCON_NAME——————————CDB$ROOT   SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL SCOPE=SPFILE;System altered.   SQL> SHUTDOWN IMMEDIATE;Database closed.Database dismounted.ORACLE instance shut down.   SQL> STARTUP;ORACLE instance started.Total System Global Area 2365584784 bytesFixed Size 9180560 bytesVariable Size 520093696 bytesDatabase Buffers 1828716544 bytesRedo Buffers 7593984 bytesDatabase mounted.Database opened. 2. Open the PDB DB PRODPDB1.   SQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED———- —————————— ———- ———-2 PDB$SEED READ ONLY NO3 PRODPDB1 MOUNTED   SQL> ALTER PLUGGABLE DATABASE PRODPDB1 OPEN;Pluggable database altered. SQL> show parameter undo;NAME TYPE VALUE———————————— ———– ——————————temp_undo_enabled boolean FALSEundo_management string MANUALundo_retention integer 900undo_tablespace string UNDOTBS1 SQL> ALTER SESSION SET CONTAINER = PRODPDB1;Session altered. TABLESPACE_NAME AUT MAX_TS_SIZE MAX_TS_PCT_USED CURR_TS_SIZE USED_TS_SIZE TS_PCT_USED FREE_TS_SIZE TS_PCT_FREE—————————— — ———– ————— ———— ———— ———– ———— ———–SYSTEM YES 32767.98 1.45 480 474.13 98.78 5.88 1SYSAUX YES 32767.98 1.36 470 444.06 94.48 25.94 6TEMP YES 32767.98 .6 201 198 98.51 3 1NEW_UNDO_TBS NO 500 .45 500 2.25 .45 497.75 100USERS YES 32767.98 0 5 1 20 4 80 3. Create undo tablespace UNDOTBS1 in PDB DB PRODPDB1. SQL> create undo tablespace UNDOTBS1 datafile ‘/u01/app/oracle/oradata/PROD/prodpdb1/undotbs1_02.dbf’ size 500m;Tablespace created. 4. Change the UNDO_MANAGEMENT to AUTO and bounce the CDB$ROOT container. SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;Session altered. SQL> ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;System altered.   SQL> SHUTDOWN IMMEDIATE;Database closed.Database dismounted.ORACLE instance shut down. SQL> STARTUP;ORACLE instance started.Total System Global Area 2365584784 bytesFixed Size 9180560 bytesVariable Size 520093696 bytesDatabase Buffers 1828716544 bytesRedo Buffers 7593984 bytesDatabase mounted.Database opened. 5. Open the PDB DB. SQL> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED———- —————————— ———- ———-2 PDB$SEED READ ONLY NO3 PRODPDB1 MOUNTED SQL> alter pluggable database PRODPDB1 open;Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED———- —————————— ———- ———-2 PDB$SEED READ ONLY NO3 PRODPDB1 READ WRITE NO   SQL> ALTER SESSION SET CONTAINER = PRODPDB1;Session altered. SQL> show con_nameCON_NAME——————————PRODPDB1 Now, able to open the pluggable database. 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 Categories ASM (2) DataGuard (11) News (1) OEM (1) Oracle Standalone Database (16) Patching (1) RAC (6) Uncategorized (1)

Switch UNDO tablespace in Standalone Oracle Multitenant Pluggable Database   The aim of this post is to provide an example of switching the undo tablespace in a standalone Oracle multitenant pluggable database. We may experience the situation where current UNDO tablespace in the PDB is oversized and underutilized, and cannot shrink it directly to reclaim unused space back to storage. To address this issue, a new undo tablespace must be created, followed by switching to newly created undo tablespace and dropping the old undo tablespace. Note: We can only switch UNDO tablespace in a PDB if the CDB is in local undo mode. Environment============CDB : PRODPDB: PRODPDB1Old Undo tablespace of PDB: UNDOTBS1New Undo tablespace of PDB: NEW_UNDO_TBSDB Version: 19.27 1. The undo must be in local_mode. SQL> show con_nameCON_NAME——————————CDB$ROOT   SQL> col PROPERTY_NAME for a20col PROPERTY_VALUE for a20select property_name, property_value from database_properties where property_name = ‘LOCAL_UNDO_ENABLED’; PROPERTY_NAME PROPERTY_VALUE——————– ——————–LOCAL_UNDO_ENABLED TRUE   SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED———- —————————— ———- ———-2 PDB$SEED READ ONLY NO3 PRODPDB1 READ WRITE NO 2. Create the New UNDO Tablespace in PDB. SQL> alter session set container = prodpdb1;Session altered. SQL> show con_nameCON_NAME——————————PRODPDB1 SQL> show parameter undoNAME TYPE VALUE———————————— ———– ——————————temp_undo_enabled boolean FALSEundo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1   SQL> create undo tablespace NEW_UNDO_TBS datafile ‘/u01/app/oracle/oradata/PROD/prodpdb1/new_undo_tbs01.dbf’ size 500m;Tablespace created. 3. Switch the undo tablespace to newly created in PDB. Before switching UNDO tablespaces in a PDB, you should always check that no active rollback/undo transactions are running. If you switch while transactions are still using the old UNDO, you’ll hit errors or risk corruption. SQL> select s.sid, s.serial#, t.addr, t.ubafil, t.ubablk, t.used_ublk, t.used_urec from v$transaction t, v$session s where t.ses_addr = s.saddr;no rows selected SQL> alter system set undo_tablespace = NEW_UNDO_TBS scope=both;System altered. SQL> show parameter undoNAME TYPE VALUE———————————— ———– ——————————temp_undo_enabled boolean FALSEundo_management string AUTOundo_retention integer 900undo_tablespace string NEW_UNDO_TBS 4. Drop the old undo tablespace from PDB. SQL> drop tablespace undotbs1 including contents and datafiles;Tablespace dropped. New UNDO tablespace is in place for undo transactions. 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 Categories

Upgrade Time Zone in Oracle Standalone Multitenant Database 19.18 and later oracledna Oracle Standalone Database July 30, 2026 This post is to demonstrate upgrade Time Zone in Oracle Standalone Multitenant Database 19.18 and later. Importing (impdp) into a database that has a lower timezone file version than the source is not supported. We may experience impdp failure with below ORA errors. ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 43 into a target database with TSTZ version 32.ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 34 into a target database with TSTZ version 32. To perform a successful impdp operation, the timezone needs to be upgraded to an equal or higher version than the source database’s version. Starting with Oracle 19c RU 19.18, Oracle automatically bundles all available DST patches directly into the Release Update. They are automatically deployed as .dat files into $ORACLE_HOME/oracore/zoneinfo directory during the RU patching. The files are already sitting in ORACLE Home, do not need to download or apply a separate timezone patch. Only need to update the database to recognize them. In Dataguard environment, when we upgrade the timezone version on the primary database, redo logs will contain timezone metadata based on the new version and will propagate to the standby database. Make sure, the standby database must also have the same timezone file version installed in its $ORACLE_HOME/oracore/zoneinfo/. Note: The utltz_upg_check.sql will always detect the highest available version by default and upgrade DST version to it.The main limitation is that there’s no possibility of downgrade or reverting the time zone. After upgrading to a specified DST version, not possible to revert to an earlier version. This post applicable to the environment having DB version 19.18 or higher .   Environment:============DB Version: 19.27OS Version: OEL 9.4DB Type: Standalone CDB with one PDBTimezone Version: 32Target Time Zone version: 44 1. Check the current time zone version.   SQL> select * from v$timezone_file; FILENAME VERSION CON_ID——————– ———- ———-timezlrg_32.dat 32 0   SQL> select tz_version from registry$database; TZ_VERSION———-32 2. Before performing the time zone file upgrade, create a restore point.   SQL> create restore point before_tz_upg guarantee flashback database; Restore point created. 3. Run the utltz_countstats.sql It’s used during a DBMS_DST timezone upgrade to gather statistics about how many rows in database are affected by timezone changes. No database restart required SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstats.sql Session altered. .Amount of TSTZ data using num_rows stats info in DBA_TABLES..For SYS tables first …Note: empty tables are not listed.Stat date – Owner.TableName.ColumnName – num_rows25/07/2026 – SYS.AQ$_ALERT_QT_S.CREATION_TIME – 425/07/2026 – SYS.AQ$_ALERT_QT_S.DELETION_TIME – 425/07/2026 – SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME – 417/04/2019 – SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME – 317/04/2019 – SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME – 317/04/2019 – SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME – 317/04/2019 – SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME – 117/04/2019 – SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME – 117/04/2019 – SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME – 117/04/2019 – SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME – 117/04/2019 – SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME – 117/04/2019 – SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME – 125/07/2026 – SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME – 125/07/2026 – SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME – 125/07/2026 – SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME – 125/07/2026 – SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME – 325/07/2026 – SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME – 325/07/2026 – SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME – 317/04/2019 – SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME – 117/04/2019 – SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME – 117/04/2019 – SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME – 125/07/2026 – SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME – 125/07/2026 – SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME – 125/07/2026 – SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME – 125/07/2026 – SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME – 125/07/2026 – SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME – 125/07/2026 – SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME – 125/07/2026 – SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME – 425/07/2026 – SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME – 425/07/2026 – SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME – 425/07/2026 – SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ – 125/07/2026 – SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME – 125/07/2026 – SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME – 125/07/2026 – SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME – 125/07/2026 – SYS.KET$_AUTOTASK_STATUS.MW_START_TIME – 125/07/2026 – SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME – 125/07/2026 – SYS.KET$_CLIENT_CONFIG.FIELD_2 – 725/07/2026 – SYS.KET$_CLIENT_CONFIG.LAST_CHANGE – 725/07/2026 – SYS.KET$_CLIENT_TASKS.CURR_WIN_START – 325/07/2026 – SYS.KET$_CLIENT_TASKS.LG_DATE – 325/07/2026 – SYS.KET$_CLIENT_TASKS.LT_DATE – 325/07/2026 – SYS.OPTSTAT_HIST_CONTROL$.SPARE6 – 4525/07/2026 – SYS.OPTSTAT_HIST_CONTROL$.SVAL2 – 4525/07/2026 – SYS.OPTSTAT_SNAPSHOT$.TIMESTAMP – 251225/07/2026 – SYS.OPTSTAT_USER_PREFS$.CHGTIME – 7225/07/2026 – SYS.RADM_FPTM$.TSWTZ_COL – 125/07/2026 – SYS.REG$.NTFN_GROUPING_START_TIME – 225/07/2026 – SYS.REG$.REG_TIME – 225/07/2026 – SYS.SCHEDULER$_EVENT_LOG.LOG_DATE – 2725/07/2026 – SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP – 1125/07/2026 – SYS.SCHEDULER$_JOB.END_DATE – 2325/07/2026 – SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME – 2325/07/2026 – SYS.SCHEDULER$_JOB.LAST_END_DATE – 2325/07/2026 – SYS.SCHEDULER$_JOB.LAST_START_DATE – 2325/07/2026 – SYS.SCHEDULER$_JOB.NEXT_RUN_DATE – 2325/07/2026 – SYS.SCHEDULER$_JOB.START_DATE – 2325/07/2026 – SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE – 1425/07/2026 – SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE – 1425/07/2026 – SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE – 1425/07/2026 – SYS.SCHEDULER$_SCHEDULE.END_DATE – 425/07/2026 – SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE – 425/07/2026 – SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE – 925/07/2026 – SYS.SCHEDULER$_WINDOW.END_DATE – 925/07/2026 – SYS.SCHEDULER$_WINDOW.LAST_START_DATE – 925/07/2026 – SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME – 925/07/2026 – SYS.SCHEDULER$_WINDOW.NEXT_START_DATE – 925/07/2026 – SYS.SCHEDULER$_WINDOW.START_DATE – 925/07/2026 – SYS.WRI$_ALERT_HISTORY.CREATION_TIME – 4925/07/2026 – SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED – 4925/07/2026 – SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SAVTIME – 615925/07/2026 – SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SPARE6 – 615925/07/2026 – SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME – 49425/07/2026 – SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 – 49425/07/2026 – SYS.WRI$_OPTSTAT_OPR.END_TIME – 3025/07/2026 – SYS.WRI$_OPTSTAT_OPR.SPARE6 – 3025/07/2026 – SYS.WRI$_OPTSTAT_OPR.START_TIME – 3025/07/2026 – SYS.WRI$_OPTSTAT_OPR_TASKS.END_TIME – 111325/07/2026 – SYS.WRI$_OPTSTAT_OPR_TASKS.SPARE6 – 111325/07/2026 – SYS.WRI$_OPTSTAT_OPR_TASKS.START_TIME – 111325/07/2026 – SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME – 53225/07/2026 – SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 – 53225/07/2026 – SYS.WRM$_DATABASE_INSTANCE.STARTUP_TIME_TZ – 725/07/2026 – SYS.WRM$_PDB_INSTANCE.OPEN_TIME_TZ – 525/07/2026 – SYS.WRM$_PDB_INSTANCE.STARTUP_TIME_TZ – 525/07/2026 – SYS.WRM$_PDB_IN_SNAP.OPEN_TIME_TZ – 1425/07/2026 – SYS.WRM$_SNAPSHOT.BEGIN_INTERVAL_TIME_TZ – 525/07/2026 – SYS.WRM$_SNAPSHOT.END_INTERVAL_TIME_TZ – 517/04/2019 – SYS.XS$PRIN.END_DATE – 1517/04/2019 – SYS.XS$PRIN.START_DATE – 15Total numrows of SYS TSTZ columns is : 21016There are in total 169 SYS TSTZ columns..For non-SYS tables …Note: empty tables are not listed.Stat date – Owner.Tablename.Columnname – num_rows25/07/2026 – GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.CREATION_TIME – 125/07/2026 – GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.DELETION_TIME – 125/07/2026 – GSMADMIN_INTERNAL.AQ$_CHANGE_LOG_QUEUE_TABLE_S.MODIFICATION_TIME –117/04/2019 – WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.CREATION_TIME – 117/04/2019 – WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.DELETION_TIME – 117/04/2019 – WMSYS.AQ$_WM$EVENT_QUEUE_TABLE_S.MODIFICATION_TIME – 125/07/2026 – WMSYS.WM$WORKSPACES_TABLE$.CREATETIME – 125/07/2026 – WMSYS.WM$WORKSPACES_TABLE$.LAST_CHANGE – 1Total numrows of non-SYS TSTZ columns is : 8There are in total 20 non-SYS TSTZ columns.Total Minutes elapsed : 0 Session altered. 4. Run the utltz_countstar.sql It scans database for tables containing TIMESTAMP WITH TIME ZONE columns. It runs a COUNT(*) across those tables to estimate how many rows will be affected by a timezone upgrade. Database restart not required. SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstar.sql Session altered. .Estimating amount of TSTZ data using COUNT(*).This might take some time ….For SYS tables first …Note: empty tables are not listed.Owner.TableName.ColumnName – COUNT(*) of that columnSYS.AQ$_ALERT_QT_S.CREATION_TIME – 4SYS.AQ$_ALERT_QT_S.DELETION_TIME – 4SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME – 4SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME – 3SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME – 3SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME – 3SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME – 1SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME – 1SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME – 1SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.CREATION_TIME – 1SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.DELETION_TIME – 1SYS.AQ$_ORA$PREPLUGIN_BACKUP_QTB_S.MODIFICATION_TIME – 1SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.CREATION_TIME – 1SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.DELETION_TIME – 1SYS.AQ$_PDB_MON_EVENT_QTABLE$_S.MODIFICATION_TIME – 1SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME – 3SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME – 3SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME – 3SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME – 1SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME – 1SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME – 1SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME – 1SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME – 1SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME – 1SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME – 1SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME – 1SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME – 1SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.CREATION_TIME – 4SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.DELETION_TIME – 4SYS.AQ$_SYS$SERVICE_METRICS_TAB_S.MODIFICATION_TIME – 4SYS.ATSK$_SCHEDULE_CONTROL.MRCT_TASK_TIME_TZ – 1SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME – 1SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME – 1SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME – 1SYS.KET$_AUTOTASK_STATUS.MW_START_TIME –

Upgrade Oracle Database 19c non cdb to 26ai using Autoupgrade oracledna Oracle Standalone Database March 30, 2026 This post demonstrated the upgrading Oracle Database 19c non cdb to 26ai using Autoupgrade on the same server. Environment:==========Source Database : 19c (19.27), ProdSource ORACLE HOME (19c) : /u01/app/oracle/product/19.0.0/db_1Target Database : 23.26.1.0.0 (26ai)Target ORACLE HOME (26ai) : /u01/app/oracle/product/26ai/db, empty CDB (PROD26ai) without PDB has been created. Ensure to perform pre checks before upgrade and post checks after upgrade. Caution: Make sure to include parameter “upg1.target_pdb_copy_option” as this parameter copy datafiles of source non cdb to target pdb. So, rollback is possible. Because, he compatible will be the newly upgraded non cdb to pdb will be the compatible value to CDB. Downgrade is not possible. Before upgrade, RMAN full backup is mandatory. 1. Generate a sample config file and edit accordingly.   Make sure export the 19c Oracle home before running the “autoupgrade.jar” commands.   [oracle@prod ~]$ /u01/app/oracle/product/26ai/db/jdk/bin/java -jar /u01/app/oracle/product/26ai/db/rdbms/admin/autoupgrade.jar -create_sample_file config /miss/autoup_19cto26ai/config.cfgCreated sample configuration file /miss/autoup_19cto26ai/config.cfg global.global_log_dir=/u01/app/oracle/cfgtoollogs/upg1.sid=produpg1.source_home=/u01/app/oracle/product/19.0.0/db_1upg1.target_home=/u01/app/oracle/product/26ai/dbupg1.restoration=YESupg1.timezone_upg=YESupg1.target_cdb=prod26aiupg1.start_time=NOWupg1.target_pdb_name=prpdb1upg1.target_pdb_copy_option=file_name_convert=(‘/u01/app/oracle/oradata/PROD’, ‘/u01/app/oracle/oradata/PROD26AI/prpdb1’)upg1.run_utlrp=YES 2. Run the upgrade in analyze mode to validate any issues with the upgrade. [oracle@prod ~]$ /u01/app/oracle/product/26ai/db/jdk/bin/java -jar /u01/app/oracle/product/26ai/db/rdbms/admin/autoupgrade.jar -config /miss/autoup_19cto26ai/config.cfg -mode analyze AutoUpgrade 26.2.260205 launched with default internal optionsProcessing config file …+——————————–+| Starting AutoUpgrade execution |+——————————–+1 Non-CDB(s) will be analyzedType ‘help’ to list console commandsupg> lsj+—-+——-+———+———+——-+———-+——-+—————————-+|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|+—-+——-+———+———+——-+———-+——-+—————————-+| 100| prod|PRECHECKS|EXECUTING|RUNNING| 06:14:28|25s ago|Loading database information|+—-+——-+———+———+——-+———-+——-+—————————-+Total jobs 1 upg> lsj+—-+——-+———+———+——-+———-+——-+—————-+|Job#|DB_NAME| STAGE|OPERATION| STATUS|START_TIME|UPDATED| MESSAGE|+—-+——-+———+———+——-+———-+——-+—————-+| 100| prod|PRECHECKS|EXECUTING|RUNNING| 06:14:28| 4s ago|Executing Checks|+—-+——-+———+———+——-+———-+——-+—————-+Total jobs 1 upg> status -job 100Details Job No 100Oracle SID prodStart Time 26/03/30 06:14:28Elapsed (min): 1End time: N/A Logfiles Logs Base: /miss/autoup_19cto26ai/prodJob logs: /miss/autoup_19cto26ai/prod/100Stage logs: /miss/autoup_19cto26ai/prod/100/prechecksTimeZone: /miss/autoup_19cto26ai/prod/tempRemote Dirs: StagesSETUP <1 minPRECHECKS ~1 min (RUNNING) Stage-Progress Per Container +——–+———+|Database|PRECHECKS|+——–+———+| prod| 98 % |+——–+———+ upg> status -job 100Details Job No 100Oracle SID prodStart Time 26/03/30 06:14:28Elapsed (min): 2End time: N/A Logfiles Logs Base: /miss/autoup_19cto26ai/prodJob logs: /miss/autoup_19cto26ai/prod/100Stage logs: /miss/autoup_19cto26ai/prod/100/prechecksTimeZone: /miss/autoup_19cto26ai/prod/tempRemote Dirs: StagesSETUP <1 minPRECHECKS ~2 min (RUNNING) Stage-Progress Per Container +——–+———+|Database|PRECHECKS|+——–+———+| prod| 99 % |+——–+———+ upg> Job 100 completed——————- Final Summary ——————–Number of databases [ 1 ] Jobs finished [1]Jobs failed [0] Please check the summary report at:/u01/app/oracle/cfgtoollogs/cfgtoollogs/upgrade/auto/status/status.html/u01/app/oracle/cfgtoollogs/cfgtoollogs/upgrade/auto/status/status.log — [oracle@prod prechecks]$ cat /u01/app/oracle/cfgtoollogs/cfgtoollogs/upgrade/auto/status/status.log==========================================Autoupgrade Summary Report==========================================[Date] Mon Mar 30 06:17:17 IST 2026[Number of Jobs] 1==========================================[Job ID] 100==========================================[DB Name] prod[Version Before Upgrade] 19.3.0.0.0[Version After Upgrade] 23.26.1.0.0——————————————[Stage Name] PRECHECKS[Status] SUCCESS[Start Time] 2026-03-30 06:14:28[Duration] 0:02:49[Log Directory] /miss/autoup_19cto26ai/prod/100/prechecks[Detail] /miss/autoup_19cto26ai/prod/100/prechecks/prod_preupgrade.logCheck passed and no manual intervention needed 3. Run Autoupgrade in Deploy mode. /u01/app/oracle/product/26ai/db/jdk/bin/java -jar /u01/app/oracle/product/26ai/db/rdbms/admin/autoupgrade.jar -config /miss/autoup_19cto26ai/config.cfg -mode deploy upg> status -job 108Details Job No 108Oracle SID prodStart Time 26/03/30 12:17:30Elapsed (min): 51End time: N/A Logfiles Logs Base: /miss/autoup_19cto26ai/prodJob logs: /miss/autoup_19cto26ai/prod/108Stage logs: /miss/autoup_19cto26ai/prod/108/postfixupsTimeZone: /miss/autoup_19cto26ai/prod/tempRemote Dirs: StagesSETUP <1 minGRP <1 minPREUPGRADE <1 minPRECHECKS <1 minPREFIXUPS <1 minDRAIN 1 minDBUPGRADE 36 minDISPATCH 9 minNONCDBTOPDB <1 minPOSTCHECKS <1 minPOSTFIXUPS ~2 min (RUNNING)POSTUPGRADESYSUPDATES Stage-Progress Per Container +——–+———-+|Database|POSTFIXUPS|+——–+———-+| PRPDB1| 100% |+——–+———-+ upg> status -job 108Job 108 completed——————- Final Summary ——————–Number of databases [ 1 ] Jobs finished [1]Jobs failed [0]Jobs restored [0]Jobs pending [0] Please check the summary report at:/u01/app/oracle/cfgtoollogs/cfgtoollogs/upgrade/auto/status/status.html/u01/app/oracle/cfgtoollogs/cfgtoollogs/upgrade/auto/status/status.log ==== [oracle@prod ~]$ cat /u01/app/oracle/cfgtoollogs/cfgtoollogs/upgrade/auto/status/status.log==========================================Autoupgrade Summary Report==========================================[Date] Mon Mar 30 13:08:37 IST 2026[Number of Jobs] 1==========================================[Job ID] 108==========================================[DB Name] prod[Version Before Upgrade] 19.27.0.0.0[Version After Upgrade] 23.26.1.0.0——————————————[Stage Name] GRP[Status] SUCCESS[Start Time] 2026-03-30 12:17:31[Duration] 0:00:00[Detail] Please drop the following GRPs after Autoupgrade completes:AUTOUPGRADE_9212_PROD1927000——————————————[Stage Name] PREUPGRADE[Status] SUCCESS[Start Time] 2026-03-30 12:17:31[Duration] 0:00:00[Log Directory] /miss/autoup_19cto26ai/prod/108/preupgrade——————————————[Stage Name] PRECHECKS[Status] SUCCESS[Start Time] 2026-03-30 12:17:31[Duration] 0:00:17[Log Directory] /miss/autoup_19cto26ai/prod/108/prechecks[Detail] /miss/autoup_19cto26ai/prod/108/prechecks/prod_preupgrade.logCheck passed and no manual intervention needed——————————————[Stage Name] PREFIXUPS[Status] SUCCESS[Start Time] 2026-03-30 12:17:49[Duration] 0:00:57[Log Directory] /miss/autoup_19cto26ai/prod/108/prefixups[Detail] /miss/autoup_19cto26ai/prod/108/prefixups/prefixups.html——————————————[Stage Name] DRAIN[Status] SUCCESS[Start Time] 2026-03-30 12:18:46[Duration] 0:01:09[Log Directory] /miss/autoup_19cto26ai/prod/108/drain——————————————[Stage Name] DBUPGRADE[Status] SUCCESS[Start Time] 2026-03-30 12:19:56[Duration] 0:36:19[Log Directory] /miss/autoup_19cto26ai/prod/108/dbupgrade——————————————[Stage Name] NONCDBTOPDB[Status] SUCCESS[Start Time] 2026-03-30 13:05:38[Duration] 0:00:00[Log Directory] /miss/autoup_19cto26ai/prod/108/noncdbtopdb——————————————[Stage Name] POSTCHECKS[Status] SUCCESS[Start Time] 2026-03-30 13:05:39[Duration] 0:00:01[Log Directory] /miss/autoup_19cto26ai/prod/108/postchecks[Detail] /miss/autoup_19cto26ai/prod/108/postchecks/prod_postupgrade.logCheck passed and no manual intervention needed——————————————[Stage Name] POSTFIXUPS[Status] SUCCESS[Start Time] 2026-03-30 13:05:40[Duration] 0:02:55[Log Directory] /miss/autoup_19cto26ai/prod/108/postfixups[Detail] /miss/autoup_19cto26ai/prod/108/postfixups/postfixups.html——————————————[Stage Name] POSTUPGRADE[Status] SUCCESS[Start Time] 2026-03-30 13:08:36[Duration] 0:00:00[Log Directory] /miss/autoup_19cto26ai/prod/108/postupgrade——————————————[Stage Name] SYSUPDATES[Status] SUCCESS[Start Time] 2026-03-30 13:08:37[Duration] 0:00:00[Log Directory] /miss/autoup_19cto26ai/prod/108/sysupdates——————————————Summary:/miss/autoup_19cto26ai/prod/108/dbupgrade/upg_summary.log 4. Check whether the non-cdb to migrated as pdb and upgraded or not. SQL> @db NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE INSTANCE_NAME HOST_NAME STARTUP_TIME———- ————— ———- —————— ————— —————- ——————–PROD26AI prod26ai READ WRITE PRIMARY prod26ai prod 30-MAR-2026 12:13 CON_ID CON_NAME OPEN MODE RESTRICTED———- —————————— ———- ———-3 PRPDB1 READ WRITE NO BANNER_FULL——————————————————————————–Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 – ProductionVersion 23.26.1.0.0 — SQL> show con_name CON_NAME——————————PRPDB1 SQL> show parameter compatible NAME TYPE VALUE———————————— ———– ——————————compatible string 23.6.0noncdb_compatible boolean FALSE The 19c non-cdb database is to migrated as pdb and upgraded by Autoupgrade. 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 Upgrade Oracle Database 19c non cdb to 26ai using Autoupgrade Perform out-of-place (OOP) patching using Autoupgrade in Oracle Database Reverting an Oracle Database upgrade using Flashback Database and AutoUpgrade Upgrade Oracle Database from 11g to 19c by Manual method (Non CDB & Non-RAC) Roll Forward Physical Standby Using Service in Oracle Database 18.1 and later (non cdb & non-RAC) Categories ASM (2) DataGuard (11) News (1) Oracle Standalone Database (12) Patching (1) RAC (3)