This document describes how to do manual database upgrade from 12.1.0.2 to 19.3.0.0
Below are the source and target database, oracle_homes information.
Source:
Oracle Home : /u01/app/oracle/product/12.1.0/dbhome_1
Oracle SID : GOLD
DB Version : 12.1.0.2
CDB : Non-CDB
RAC : No
Target:
Oracle_Home :
Oracle SID : GOLD
DB Version : 19.3.0.0
CDB : Non-CDB
RAC : No
Below are the steps to follow to complete the upgrade.
1. Pre-Upgrade
2. Upgrade
3. Post-Upgrade
1. Pre-Upgrade:
Step 1: Install 19C binaries.
Download the software and stage it in the server. Please click Here to download 19c software.
Copy the software to server and unzip in the directory where we want to the install binaries(ORACLE_HOME).
Run the runInstaller to install 19c binaries.
Select "Set up Software only" option and click on Next
Select "Single Instance database installation and click "Next"
Select Enterprise Edition" and click Next
Click Next to continue
Select dba or oinstall as group and click Next. In my case dba is the primary group.
Click Next to continue.
Click on "Install"
Run the root.sh as root user
Click on OK after complete the script execution.
Step 2 :
Backup the below files.
Take backup of 12C oracle home.
[oracle@server1 u01]$ mkdir backup
[oracle@server1 u01]$ cd backup/
[oracle@server1 backup]$ pwd
/u01/backup
[oracle@server1 backup]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@server1 backup]$
[oracle@server1 12.1.0]$ cd /u01/app/oracle/product
[oracle@server1 product]$ tar -cvf /u01/backup/12.1.0_before_upgrade.tar 12.1.0
[oracle@server1 product]$ cd /u01/backup/
[oracle@server1 backup]$ ls
12.1.0_before_upgrade.tar
[oracle@server1 backup]$
Take backup of tnsnames, listener, sqlnet, spfile and password file.
[oracle@server1 backup]$ cd $ORACLE_HOME/network/admin
[oracle@server1 admin]$ cp tnsnames.ora /u01/backup/
[oracle@server1 admin]$ cp listener.ora /u01/backup/
[oracle@server1 admin]$ cp sqlnet.ora /u01/backup/
[oracle@server1 admin]$ cd $ORACLE_HOME/dbs
[oracle@server1 dbs]$ cp spfileGOLD.ora /u01/backup/
[oracle@server1 dbs]$ cp orapwGOLD /u01/backup/
Step 3:
Create one directory and run preupgrade tool from 19c oracle home. And review the log file.
[oracle@server1 u01]$ mkdir -p /u01/pre-upgrade
[oracle@server1 u01]$ /u01/app/oracle/product/12.1.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /u01/pre-upgrade
==================
PREUPGRADE SUMMARY
==================
/u01/pre-upgrade/preupgrade.log
/u01/pre-upgrade/preupgrade_fixups.sql
/u01/pre-upgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/pre-upgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/pre-upgrade/postupgrade_fixups.sql
Preupgrade complete: 2021-12-09T08:29:47
[oracle@server1 u01]$
[oracle@server1 u01]$ cat /u01/pre-upgrade/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-12-09T08:29:47
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: GOLD
Container Name: GOLD
Container ID: 0
Version: 12.1.0.2.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.1.0.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 18
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] OPTION OFF
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
1. Upgrade Oracle Application Express (APEX) manually before the database
upgrade.
The database contains APEX version 4.2.5.00.08. Upgrade APEX to at least
version 18.2.0.00.12.
Starting with Oracle Database Release 18, APEX is not upgraded
automatically as part of the database upgrade. Refer to My Oracle Support
Note 1088970.1 for information about APEX installation and upgrades.
2. Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes
empty before doing upgrade, unless you have strong business reasons not
to do so. You can use dbms_mview.refresh() to refresh the MVs except
those stale ones to be kept due to business need. If there are any stale
MVs depending on changes in sys.sumdelta$, do not truncate it, because
doing so will cause wrong results after refresh.
There is one or more non-fresh MV in the database or sumdelta$ is not
empty.
Oracle recommends that all materialized views (MV's) are refreshed before
upgrading the database because this will clear the MV logs and the
sumdelta$ table, and make the UPGRADE process faster. If you choose to
not refresh some MVs, the change data for those MV's will be carried
through the UPGRADE process. After UPGRADE, you can refresh the MV's and
MV incremental refresh should work in normal cases.
3. (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
4. Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes
empty before doing upgrade, unless you have strong business reasons not
to do so. You can use dbms_mview.refresh() to refresh the MVs except
those stale ones to be kept due to business need. If there are any stale
MVs depending on changes in sys.sumdelta$, do not truncate it, because
doing so will cause wrong results after refresh.
There is one or more non-fresh MV in the database or sumdelta$ is not
empty.
Oracle recommends that all materialized views (MV's) are refreshed before
upgrading the database because this will clear the MV logs and the
sumdelta$ table, and make the UPGRADE process faster. If you choose to
not refresh some MVs, the change data for those MV's will be carried
through the UPGRADE process. After UPGRADE, you can refresh the MV's and
MV incremental refresh should work in normal cases.
5. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is
recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
6. To help you keep track of your tablespace allocations, the following
AUTOEXTEND tablespaces are expected to successfully EXTEND during the
upgrade process.
Min Size
Tablespace Size For Upgrade
---------- ---------- -----------
SYSAUX 690 MB 797 MB
SYSTEM 790 MB 1136 MB
UNDOTBS1 155 MB 449 MB
Minimum tablespace sizes for upgrade are estimates.
7. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database GOLD
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/pre-upgrade/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
8. (AUTOFIXUP) If you use the -T option for the database upgrade, then run
$ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
to VALIDATE and UPGRADE any user tables affected by changes to
Oracle-Maintained types.
There are user tables dependent on Oracle-Maintained object types.
If the -T option is used to set user tablespaces to READ ONLY during the
upgrade, user tables in those tablespaces, that are dependent on
Oracle-Maintained types, will not be automatically upgraded. If a type is
evolved during the upgrade, any dependent tables need to be re-validated
and upgraded to the latest type version AFTER the database upgrade
completes.
9. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 18 and the target 19 release
ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time
zone file. For more information, refer to "Upgrading the Time Zone File
and Timestamp with Time Zone Data" in the 19 Oracle Database
Globalization Support Guide.
10. To identify directory objects with symbolic links in the path name, run
$ORACLE_HOME/rdbms/admin/utldirsymlink.sql AS SYSDBA after upgrade.
Recreate any directory objects listed, using path names that contain no
symbolic links.
Some directory object path names may currently contain symbolic links.
Starting in Release 18c, symbolic links are not allowed in directory
object path names used with BFILE data types, the UTL_FILE package, or
external tables.
11. (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a database
upgrade, statistics need to be re-gathered as there can now be tables
that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
12. Gather statistics on fixed objects after the upgrade and when there is a
representative workload on the system using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 12.1.0.2
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database GOLD
which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/pre-upgrade/postupgrade_fixups.sql
[oracle@server1 u01]$
Step 4:
Gather dictionary statistics
SQL> set serveroutput on
SQL>
SQL> exec DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
SQL>
Step 5:
Purge Recycle bin.
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
SQL>
Step 6:
Refresh materialized views.
SQL> DECLARE
v_number_of_failures NUMBER(12) := 0;
BEGIN
DBMS_MVIEW.REFRESH_ALL_MVIEWS(v_number_of_failures,'C','', TRUE, FALSE);
END;
/
2 3 4 5 6
PL/SQL procedure successfully completed.
SQL>
Step 7:
Run preupgrade_fixups.sql which is generated as part of pre-upgrade tool.
Connect database as sysdba and run below sql.
SQL> @/u01/pre-upgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2021-12-09 08:29:43
For Source Database: GOLD
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. apex_manual_upgrade NO Manual fixup recommended.
2. mv_refresh YES None.
3. dictionary_stats YES None.
4. mv_refresh YES None.
5. pre_fixed_objects YES None.
6. tablespaces_info NO Informational only.
Further action is optional.
7. rman_recovery_version NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database is not ready
for upgrade. To resolve the outstanding issues, start by reviewing
the preupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
SQL>
Step 8:
Check the archive log destination. Make sure the archive log mode is enabled and we have sufficient space in archive log dest.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
SQL>
SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u02/Data
db_recovery_file_dest_size big integer 4560M
SQL>
SQL> alter system set db_recovery_file_dest_size=20G scope=both;
System altered.
SQL>
2. Upgrade
Downtime starts from here.
Step 1:
stop the listener.
[oracle@server1 u01]$ lsnrctl stop LISTENER
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-DEC-2';021 09:29:36
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.apps.com)(PORT=1521)))
The command completed successfully
[oracle@server1 u01]$
Step 2:
Create flashback restore guarantee point and shutdown the database.
SQL> show user
USER is "SYS"
SQL>
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> create restore point pre_upgrade guarantee flashback database;
Restore point created.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Step 3:
Copy init file and parameter files to 19c oracle home dbs locations. And copy tnsnames,listener,sqlnet.ora files to 19c oracle_home/network/admin location.
[oracle@server1 dbs]$ cp /u01/backup/spfileGOLD.ora /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@server1 dbs]$ cp /u01/backup/init.ora /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@server1 dbs]$ cp /u01/backup/orapwGOLD /u01/app/oracle/product/19.3.0/dbhome_1/dbs/
[oracle@server1 dbs]$ cp /u01/backup/tnsnames.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin
[oracle@server1 dbs]$ cp /u01/backup/listener.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin
[oracle@server1 dbs]$ cp /u01/backup/sqlnet.ora /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/
Step 4 :
Start the database with 19c binaries in upgrade mode. Make sure no environment set to 12c home.
[oracle@server1 ~]$ cat .profile_19c
PATH=$PATH:$HOME/.local/bin:$HOME/bin
#export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1
export ORACLE_SID=GOLD
export PATH=$ORACLE_HOME:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
[oracle@server1 ~]$
[oracle@server1 ~]$ . .profile_19c
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1140849904 bytes
Fixed Size 8895728 bytes
Variable Size 637534208 bytes
Database Buffers 486539264 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE CDB VERSION STATUS
--------- -------------------- --- ----------------- ------------
GOLD READ WRITE NO 19.0.0.0.0 OPEN MIGRATE
SQL>
Step 5:
Run the dbupgrade. It takes care all the steps which required for upgrade.
[oracle@server1 ~]$ cd $ORACLE_HOME/bin
[oracle@server1 bin]$ dbupgrade
Argument list for [/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = 0
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 0
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
catctl.pl VERSION: [19.0.0.0.0]
STATUS: [Production]
BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417]
/u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.3.0/dbhome_1]
/u01/app/oracle/product/19.3.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.3.0/dbhome_1]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.3.0/dbhome_1]
Analyzing file /u01/app/oracle/product/19.3.0/dbhome_1/rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20211209095319]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20211209095319/catupgrd_catcon_16064.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20211209095319/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20211209095319/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = GOLD
DataBase Version = 12.1.0.2.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/GOLD/upgrade20211209095331/catupgrd_catcon_16064.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/GOLD/upgrade20211209095331/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/GOLD/upgrade20211209095331/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/GOLD/upgrade20211209095331]
Parallel SQL Process Count = 4
Components in [GOLD]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [EM MGW ODM RAC WK]
------------------------------------------------------
Phases [0-107] Start Time:[2021_12_09 09:53:41]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [GOLD] Files:1 Time: 107s
*************** Catalog Core SQL ***************
Serial Phase #:1 [GOLD] Files:5 Time: 63s
Restart Phase #:2 [GOLD] Files:1 Time: 1s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [GOLD] Files:19 Time: 36s
Restart Phase #:4 [GOLD] Files:1 Time: 1s
************* Catalog Final Scripts ************
Serial Phase #:5 [GOLD] Files:7 Time: 19s
***************** Catproc Start ****************
Serial Phase #:6 [GOLD] Files:1 Time: 18s
***************** Catproc Types ****************
Serial Phase #:7 [GOLD] Files:2 Time: 17s
Restart Phase #:8 [GOLD] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [GOLD] Files:67 Time: 54s
Restart Phase #:10 [GOLD] Files:1 Time: 2s
************* Catproc Package Specs ************
Serial Phase #:11 [GOLD] Files:1 Time: 86s
Restart Phase #:12 [GOLD] Files:1 Time: 1s
************** Catproc Procedures **************
Parallel Phase #:13 [GOLD] Files:94 Time: 16s
Restart Phase #:14 [GOLD] Files:1 Time: 1s
Parallel Phase #:15 [GOLD] Files:120 Time: 38s
Restart Phase #:16 [GOLD] Files:1 Time: 1s
Serial Phase #:17 [GOLD] Files:22 Time: 5s
Restart Phase #:18 [GOLD] Files:1 Time: 1s
***************** Catproc Views ****************
Parallel Phase #:19 [GOLD] Files:32 Time: 34s
Restart Phase #:20 [GOLD] Files:1 Time: 1s
Serial Phase #:21 [GOLD] Files:3 Time: 15s
Restart Phase #:22 [GOLD] Files:1 Time: 2s
Parallel Phase #:23 [GOLD] Files:25 Time: 220s
Restart Phase #:24 [GOLD] Files:1 Time: 2s
Parallel Phase #:25 [GOLD] Files:12 Time: 117s
Restart Phase #:26 [GOLD] Files:1 Time: 0s
Serial Phase #:27 [GOLD] Files:1 Time: 0s
Serial Phase #:28 [GOLD] Files:3 Time: 8s
Serial Phase #:29 [GOLD] Files:1 Time: 0s
Restart Phase #:30 [GOLD] Files:1 Time: 1s
*************** Catproc CDB Views **************
Serial Phase #:31 [GOLD] Files:1 Time: 2s
Restart Phase #:32 [GOLD] Files:1 Time: 1s
Serial Phase #:34 [GOLD] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [GOLD] Files:293 Time: 40s
Serial Phase #:36 [GOLD] Files:1 Time: 0s
Restart Phase #:37 [GOLD] Files:1 Time: 1s
Serial Phase #:38 [GOLD] Files:6 Time: 10s
Restart Phase #:39 [GOLD] Files:1 Time: 1s
*************** Catproc DataPump ***************
Serial Phase #:40 [GOLD] Files:3 Time: 84s
Restart Phase #:41 [GOLD] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [GOLD] Files:13 Time: 148s
Restart Phase #:43 [GOLD] Files:1 Time: 2s
Parallel Phase #:44 [GOLD] Files:11 Time: 20s
Restart Phase #:45 [GOLD] Files:1 Time: 1s
Parallel Phase #:46 [GOLD] Files:3 Time: 5s
Restart Phase #:47 [GOLD] Files:1 Time: 1s
************* Final Catproc scripts ************
Serial Phase #:48 [GOLD] Files:1 Time: 18s
Restart Phase #:49 [GOLD] Files:1 Time: 1s
************** Final RDBMS scripts *************
Serial Phase #:50 [GOLD] Files:1 Time: 38s
************ Upgrade Component Start ***********
Serial Phase #:51 [GOLD] Files:1 Time: 3s
Restart Phase #:52 [GOLD] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [GOLD] Files:2 Time: 793s
***************** Upgrading XDB ****************
Restart Phase #:54 [GOLD] Files:1 Time: 2s
Serial Phase #:56 [GOLD] Files:3 Time: 23s
Serial Phase #:57 [GOLD] Files:3 Time: 8s
Parallel Phase #:58 [GOLD] Files:10 Time: 7s
Parallel Phase #:59 [GOLD] Files:25 Time: 10s
Serial Phase #:60 [GOLD] Files:4 Time: 12s
Serial Phase #:61 [GOLD] Files:1 Time: 0s
Serial Phase #:62 [GOLD] Files:32 Time: 7s
Serial Phase #:63 [GOLD] Files:1 Time: 0s
Parallel Phase #:64 [GOLD] Files:6 Time: 10s
Serial Phase #:65 [GOLD] Files:2 Time: 30s
Serial Phase #:66 [GOLD] Files:3 Time: 72s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [GOLD] Files:1 Time: 2s
Serial Phase #:69 [GOLD] Files:1 Time: 6s
Parallel Phase #:70 [GOLD] Files:2 Time: 96s
Restart Phase #:71 [GOLD] Files:1 Time: 2s
Parallel Phase #:72 [GOLD] Files:2 Time: 4s
Serial Phase #:73 [GOLD] Files:2 Time: 5s
***************** Upgrading SDO ****************
Restart Phase #:74 [GOLD] Files:1 Time: 1s
Serial Phase #:76 [GOLD] Files:1 Time: 210s
Serial Phase #:77 [GOLD] Files:2 Time: 7s
Restart Phase #:78 [GOLD] Files:1 Time: 1s
Serial Phase #:79 [GOLD] Files:1 Time: 27s
Restart Phase #:80 [GOLD] Files:1 Time: 0s
Parallel Phase #:81 [GOLD] Files:3 Time: 142s
Restart Phase #:82 [GOLD] Files:1 Time: 3s
Serial Phase #:83 [GOLD] Files:1 Time: 10s
Restart Phase #:84 [GOLD] Files:1 Time: 2s
Serial Phase #:85 [GOLD] Files:1 Time: 19s
Restart Phase #:86 [GOLD] Files:1 Time: 2s
Parallel Phase #:87 [GOLD] Files:4 Time: 296s
Restart Phase #:88 [GOLD] Files:1 Time: 1s
Serial Phase #:89 [GOLD] Files:1 Time: 5s
Restart Phase #:90 [GOLD] Files:1 Time: 1s
Serial Phase #:91 [GOLD] Files:2 Time: 15s
Restart Phase #:92 [GOLD] Files:1 Time: 1s
Serial Phase #:93 [GOLD] Files:1 Time: 2s
Restart Phase #:94 [GOLD] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [GOLD] Files:1 Time: 29s
Restart Phase #:96 [GOLD] Files:1 Time: 1s
*********** Final Component scripts ***********
Serial Phase #:97 [GOLD] Files:1 Time: 5s
************* Final Upgrade scripts ************
Serial Phase #:98 [GOLD] Files:1 Time: 422s
******************* Migration ******************
Serial Phase #:99 [GOLD] Files:1 Time: 105s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [GOLD] Files:1 Time: 2s
Serial Phase #:101 [GOLD] Files:1 Time: 0s
Serial Phase #:102 [GOLD] Files:1 Time: 70s
***************** Post Upgrade *****************
Serial Phase #:103 [GOLD] Files:1 Time: 50s
**************** Summary report ****************
Serial Phase #:104 [GOLD] Files:1 Time: 4s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [GOLD] Files:1 Time: 2s
Serial Phase #:106 [GOLD] Files:1 Time: 0s
Serial Phase #:107 [GOLD] Files:1 Time: 36s
------------------------------------------------------
Phases [0-107] End Time:[2021_12_09 10:57:06]
------------------------------------------------------
Grand Total Time: 3807s
LOG FILES: (/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/GOLD/upgrade20211209095331/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19.3.0/dbhome_1/cfgtoollogs/GOLD/upgrade20211209095331/upg_summary.log
Grand Total Upgrade Time: [0d:1h:3m:27s]
[oracle@server1 bin]$
Step 6:
Start the database from 19C ORACLE_HOME
[oracle@server1 bin]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 9 10:59:29 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1140849904 bytes
Fixed Size 8895728 bytes
Variable Size 872415232 bytes
Database Buffers 251658240 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE CDB VERSION STATUS
--------- -------------------- --- ----------------- ------------
GOLD READ WRITE NO 19.0.0.0.0 OPEN
SQL>
3. Post Upgrade steps
Step 1:
compile invalid objects usint utlrp.sql. Make sure no new invalid objects observed after upgrade.
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2021-12-09 11:01:57
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2021-12-09 11:16:15
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from dba_objects where status ='INVALID';
COUNT(*)
----------
0
SQL>
Step 2:
Run postupgrade_fixups.sql
SQL> @/u01/pre-upgrade/postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Package created.
No errors.
Package body created.
PL/SQL procedure successfully completed.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2021-12-09 08:29:47
For Source Database: GOLD
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
8. depend_usr_tables YES None.
9. old_time_zones_exist NO Manual fixup recommended.
10. dir_symlinks YES None.
11. post_dictionary YES None.
12. post_fixed_objects NO Informational only.
Further action is optional.
The fixup scripts have been run and resolved what they can. However,
there are still issues originally identified by the preupgrade that
have not been remedied and are still present in the database.
Depending on the severity of the specific issue, and the nature of
the issue itself, that could mean that your database upgrade is not
fully complete. To resolve the outstanding issues, start by reviewing
the postupgrade_fixups.sql and searching it for the name of
the failed CHECK NAME or Preupgrade Action Number listed above.
There you will find the original corresponding diagnostic message
from the preupgrade which explains in more detail what still needs
to be done.
PL/SQL procedure successfully completed.
Session altered.
SQL>
Step 3:
Upgrade timezone
[oracle@server1 bin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@server1 admin]$ ls -ltr utltz*
-rw-r--r--. 1 oracle dba 8317 Feb 24 2017 utltz_countstats.sql
-rw-r--r--. 1 oracle dba 7423 Feb 24 2017 utltz_countstar.sql
-rw-r--r--. 1 oracle dba 24575 May 29 2017 utltzuv2.sql
-rw-r--r--. 1 oracle dba 26608 May 29 2017 utltzu92.sql
-rw-r--r--. 1 oracle dba 33684 Sep 8 2017 utltz_upg_check.sql
-rw-r--r--. 1 oracle dba 21526 Sep 8 2017 utltz_upg_apply.sql
[oracle@server1 admin]$
[oracle@server1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 9 11:30:44 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
18
SQL>
SQL> @utltz_upg_check.sql
Session altered.
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 19.0.0.0 .
INFO: Database RDBMS DST version is DSTv18 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv32 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update.
INFO: Note that the utltz_upg_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
Session altered.
SQL>
SQL> @utltz_upg_apply.sql
Session altered.
INFO: If an ERROR occurs, the script will EXIT SQL*Plus.
INFO: The database RDBMS DST version will be updated to DSTv32 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1140849904 bytes
Fixed Size 8895728 bytes
Variable Size 956301312 bytes
Database Buffers 167772160 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1140849904 bytes
Fixed Size 8895728 bytes
Variable Size 956301312 bytes
Database Buffers 167772160 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv32 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this SQL*Plus session.
INFO: Do not use it for timezone related selects.
Session altered.
SQL>
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
32
1 row selected.
SQL>
Step 4:
Run utlusts.sql to verify components versions after upgrade.
SQL> @utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool 12-09-2021 11:38:0
Database Name: GOLD
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.3.0.0.0 00:20:36
JServer JAVA Virtual Machine VALID 19.3.0.0.0 00:07:17
Oracle XDK VALID 19.3.0.0.0 00:01:28
Oracle Database Java Packages VALID 19.3.0.0.0 00:00:22
OLAP Analytic Workspace VALID 19.3.0.0.0 00:00:51
Oracle Label Security VALID 19.3.0.0.0 00:00:21
Oracle Database Vault VALID 19.3.0.0.0 00:00:38
Oracle Text VALID 19.3.0.0.0 00:01:08
Oracle Workspace Manager VALID 19.3.0.0.0 00:00:56
Oracle Real Application Clusters OPTION OFF 19.3.0.0.0 00:00:00
Oracle XML Database VALID 19.3.0.0.0 00:02:54
Oracle Multimedia VALID 19.3.0.0.0 00:01:48
Spatial VALID 19.3.0.0.0 00:12:16
Oracle OLAP API VALID 19.3.0.0.0 00:00:26
Datapatch 00:06:50
Final Actions 00:08:45
Post Upgrade 00:00:43
Post Compile 00:14:17
Total Upgrade Time: 01:15:34
Database time zone version is 32. It meets current release needs.
SQL>
Step 5:
We can drop the flashback restore point if we confirm that the upgrade completed successfully.
SQL> col name for a20
SQL> col GUARANTEE_FLASHBACK_DATABASE for a10
SQL> col TIME for a60
SQL> set lines 200
SQL>
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
NAME GUARANTEE_ TIME
-------------------- ---------- ------------------------------------------------------------
PRE_UPGRADE YES 09-DEC-21 09.32.40.000000000 AM
SQL>
SQL> drop restore point PRE_UPGRADE;
Restore point dropped.
SQL>
Step 6:
Check and update the compatible parameter to 19.0.0
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.1.0.2.0
SQL> alter system set compatible='19.0.0' scope=spfile;
System altered.
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1140849904 bytes
Fixed Size 8895728 bytes
Variable Size 956301312 bytes
Database Buffers 167772160 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL> show parameter compatible
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL>
Step 7:
Change the db_home location in listener.ora and start the listener.
[oracle@server1 admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-DEC-2021 11:51:09
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Starting /u01/app/oracle/product/19.3.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/server1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.apps.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server1.apps.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 09-DEC-2021 11:51:10
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.3.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/server1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1.apps.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "GOLD" has 1 instance(s).
Instance "GOLD", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@server1 admin]$
Step 8:
Verify the password file under $ORACLE_HOME/dbs location.
[oracle@server1 admin]$ cd $ORACLE_HOME/dbs
[oracle@server1 dbs]$ ls -ltr orapwGOLD
-rw-r-----. 1 oracle dba 7680 Dec 9 10:59 orapwGOLD
[oracle@server1 dbs]$
Step 9:
Modify /etc/oratab and update 19c oracle home.
[oracle@server1 dbs]$ cat /etc/oratab
#
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by either Database Configuration Assistant while creating
# a database or ASM Configuration Assistant while creating ASM instance.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third field indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
#
#GOLD:/u01/app/oracle/product/12.1.0/dbhome_1:N
GOLD:/u01/app/oracle/product/19.3.0/dbhome_1:N
[oracle@server1 dbs]$
Step 10:
Validate all database links and make sure all are working fine.
Thanks for going through the post............
