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
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