Oracle database can be upgraded directly to the new release from the following releases:
- 11.2.0.4
- 12.1.0.2
- 12.2.0.1
- 18
If your current Oracle Database is a release earlier than 11.2.0.4, then you cannot directly upgrade your Oracle Database to the latest release. In this case, you are required to upgrade to an intermediate release before upgrading to Oracle Database 19c.
There are many methods available for upgrading database to 19c . A few of them are mentioned below:-
- Database Upgrade Assistant(DBUA)
- Manual Upgrade
- Full Transportable Export Import
- Transportable Tablespace
- Datapump Export Import
Here I will be demonstrating how to upgrade database from 12c to 19c manually.
You can find the documentation of upgrading the database from 12c to 19c using DBUA here.
- Environment
- Step 1
Before starting the upgrade process it is recommended to take a full backup of the Database.
[oracle@gg1 ~]$ . oraenv
ORACLE_SID = [orclcdb] ? orcl12c
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@gg1 ~]$ sqlplus / as sysdba
SQL> select banner from v$version;
BANNER
——————————————————————————–
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
——— ——————–
ORCL12C READ WRITE
[oracle@gg1 ~]$ rman target /
Recovery Manager: Release 12.2.0.1.0 – Production on Wed Jun 24 21:50:08 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL12C (DBID=872557593)
RMAN>run {
allocate channel c1 type disk;
allocate channel c2 type disk;
backup AS COMPRESSED BACKUPSET full database tag DB_FULL format ‘/u01/BACKUP/%d_%T_%s_%p_FULL’ ;
sql ‘alter system archive log current’;
RMAN> backup archivelog all format ‘/u01/BACKUP/arch_%d_%u_%s’;
backup current controlfile format ‘/u01/BACKUP/%d_%T_%s_%p_CONTROL’;
release channel c1;
release channel c2;
}
- Step 2
Pre-requisites for Upgrade:-
1.Install Oracle Binary 19c on a different home other than 12c home. in our case the 19c db home is
/u01/app/oracle/product/19.0.0/dbhome_1
The installation of 19c can be found here.
2. Check the number of invalid objects.
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———-
0
If invalid objects exist, try to validate them by running utlrp.sql from the 12c
SQL>@?/rdbms/admin/utlrp.sql
3. Purge Recyclebin.
SQL> PURGE DBA_RECYCLEBIN;
4.Stop the running 12c listener
lsnrctl stop LISTENER
5. Copy the existing network related files such as tnsnames.ora, listener.ora sqlnet.ora into the 19c $ORACLE_HOME/network/admin
directory.
[oracle@gg1 u01]$ cd $ORACLE_HOME/network/admin
[oracle@gg1 admin]$ cp listener.ora tnsnames.ora sqlnet.ora /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/
6.Copy the password file into 19c $ORACLE_HOME/dbs directory.
[oracle@gg1 admin]$ cd $ORACLE_HOME/dbs
[oracle@gg1 dbs]$ cp orapworcl12c /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
- Step 3
Running the Pre-Upgrade Information Tool
To check your system and database to see if it is ready for upgrade, we use the Pre-Upgrade Information Tool (preupgrade.jar)
Syntax :-
$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home
/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]
FILE|TERMINAL denotes Script output location. Use FILE to direct script output to a file.
Use TERMINAL to direct output to the terminal
TEXT|XML denotes the output file type.
DIR denotes the directory where the output of the command would be saved.
[oracle@gg1 dbs]$ /u01/app/oracle/product/12.2.0.1/jdk/bin/java -jar
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT DIR /u01/
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2020-06-24T22:35:08
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL12C
Container Name: orcl12c
Container ID: 0
Version: 12.2.0.1.0
DB Patch Level: No Patch Bundle applied
Compatible: 12.2.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone File: 26
Database log mode: ARCHIVELOG
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. (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.2.0.1
Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================
2. 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 480 MB 500 MB
SYSTEM 810 MB 920 MB
TEMP 32 MB 150 MB
UNDOTBS1 65 MB 439 MB
Minimum tablespace sizes for upgrade are estimates.
3. Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least
4618 MB of archived logs. Check alert log during the upgrade that there
is no write error to the destination due to lack of disk space.
Archiving cannot proceed if the archive log destination is full during
upgrade.
Archive Log Destination:
Parameter : LOG_ARCHIVE_DEST_1
Destination : /u01/app/oracle/product/12.2.0.1/dbs/arch
The database has archiving enabled. The upgrade process will need free
disk space in the archive log destination(s) to generate archived logs to.
4. 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 ORCL12C
which are identified above as BEFORE UPGRADE “(AUTOFIXUP)” can be resolved by
executing the following
SQL>@/u01/preupgrade_fixups.sql
=============
AFTER UPGRADE
=============
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
5. Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 26 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.
6. 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.
7. (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.
8. 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.2.0.1
Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database ORCL12C
which are identified above as AFTER UPGRADE “(AUTOFIXUP)” can be resolved by
executing the following
SQL>@/u01/postupgrade_fixups.sql
==================
PREUPGRADE SUMMARY
==================
/u01/preupgrade.log
/u01/preupgrade_fixups.sql
/u01/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/postupgrade_fixups.sql
Preupgrade complete: 2020-06-24T22:35:08
[oracle@gg1 dbs]$
- Step 4
Check the tablespace information reported in preupgrade log, and make sure the autoextenstion is enabled for those
tablespace.
SQL> select tablespace_name,autoextensible from dba_data_Files;
TABLESPACE_NAME AUT
————————– —
SYSTEM YES
SYSAUX YES
UNDOTBS1 YES
USERS YES
- Step 5
Gather DICTIONARY STATS
Oracle recommends that you gather dictionary statistics both before and after upgrading the database,
because Data Dictionary tables are modified and created during the upgrade.
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
- Step 6
Run preupgrade_fixups.sql
[oracle@gg1 ~]$export ORACLE_SID=orcl12c
[oracle@gg1 ~]$ sqlplus / as sysdba
SQL> @/u01/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: 2020-06-24 22:34:50
For Source Database: ORCL12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
—— ———————— ———- ——————————–
1. dictionary_stats YES None.
2. tablespaces_info NO Informational only.
Further action is optional.
3. min_archive_dest_size NO Informational only.
Further action is optional.
4. 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.
- Step 7
Create a guaranteed restore point.
Though we took a full backup before starting our activity ,we would create a restore point at this point , so that in case our
upgrade fails we can easily rollback to this point.
To enable restore point ,we must set db_recovery_file_dest location.
SQL> alter system set db_recovery_file_dest_size=20G scope=both;
SQL> alter system set db_recovery_file_dest=’/u01/archive_log’ scope=both;
SQL> create restore point before_upgrade guarantee flashback database;
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
NAME GUARANTEE_ TIME
——————– ———- ————————————————————
BEFORE_UPGRADE YES 24-JUN-20 11.06.17.000000000 PM
Copy the spfile into 19c $ORACLE_HOME/dbs Directory
[oracle@gg1 dbs]$ cp spfileorcl12c.ora /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
- Step 8
Shutdown the Database and
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
- Step 9
start upgrade process from 19c
[oracle@gg1 dbhome_1]$ export ORACLE_SID=orcl12c
[oracle@gg1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@gg1 dbhome_1]$ export PATH=$ORACLE_HOME/bin:$PATH
SQL> startup upgrade;——> This is used for Non CDB
ORACLE instance started.
Total System Global Area 931133920 bytes
Fixed Size 8903136 bytes
Variable Size 226492416 bytes
Database Buffers 692060160 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.
SQL>
———————–
SQL> startup upgrade;
SQL> alter pluggable database all open upgrade;
For CDB database use the above commands
————————-
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE CDB VERSION STATUS
——— ——————– — —————– ————
ORCL12C READ WRITE NO 19.0.0.0.0 OPEN MIGRATE
- Step 10
Running the DBUPGRADE utility.
[oracle@gg1 dbs]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/bin/
[oracle@gg1 bin]$ ./dbupgrade
Argument list for [/u01/app/oracle/product/19.0.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.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1/]
/u01/app/oracle/product/19.0.0/dbhome_1//bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1/]
catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1/]
Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1//rdbms/admin/catupgrd.sql
Log file directory = [/tmp/cfgtoollogs/upgrade20200624234212]
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20200624234212/catupgrd_catcon_33615.lst]
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200624234212/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200624234212/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 1
Database Name = orcl12c
DataBase Version = 12.2.0.1.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/orcl12c/upgrade20200624234226/catupgrd_catcon_33615.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/orcl12c/upgrade20200624234226/catupgrd*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/orcl12c/upgrade20200624234226/catupgrd_*.lst] files for spool files, if any
Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/orcl12c/upgrade20200624234226]
Parallel SQL Process Count = 4
Components in [orcl12c]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX EM MGW ODM RAC WK]
——————————————————
Phases [0-107] Start Time:[2020_06_24 23:42:39]
——————————————————
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcl12c] Files:1 Time: 147s
*************** Catalog Core SQL ***************
Serial Phase #:1 [orcl12c] Files:5 Time: 180s
Restart Phase #:2 [orcl12c] Files:1 Time: 5s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [orcl12c] Files:19 Time: 87s
Restart Phase #:4 [orcl12c] Files:1 Time: 3s
************* Catalog Final Scripts ************
Serial Phase #:5 [orcl12c] Files:7 Time: 66s
***************** Catproc Start ****************
Serial Phase #:6 [orcl12c] Files:1 0 Time: 52s
***************** Catproc Types ****************
Serial Phase #:7 [orcl12c] Files:2 Time: 57s
Restart Phase #:8 [orcl12c] Files:1 Time: 3s
**************** Catproc Tables ****************
Parallel Phase #:9 [orcl12c] Files:67 Time: 140s
Restart Phase #:10 [orcl12c] Files:1 Time: 2s
************* Catproc Package Specs ************
Serial Phase #:11 [orcl12c] Files:1 Time: 332s
Restart Phase #:12 [orcl12c] Files:1 Time: 5s
************** Catproc Procedures **************
Parallel Phase #:13 [orcl12c] Files:94 Time: 32s
Restart Phase #:14 [orcl12c] Files:1 Time: 2s
Parallel Phase #:15 [orcl12c] Files:120 Time: 69s
Restart Phase #:16 [orcl12c] Files:1 Time: 4s
Serial Phase #:17 [orcl12c] Files:22 Time: 11s
Restart Phase #:18 [orcl12c] Files:1 Time: 3s
***************** Catproc Views ****************
Parallel Phase #:19 [orcl12c] Files:32 ^[^[ Time: 98s
Restart Phase #:20 [orcl12c] Files:1 Time: 5s
Serial Phase #:21 [orcl12c] Files:3 Time: 34s
Restart Phase #:22 [orcl12c] Files:1 Time: 2s
Parallel Phase #:23 [orcl12c] Files:25 ^[[C Time: 485s
Restart Phase #:24 [orcl12c] Files:1 Time: 4s
Parallel Phase #:25 [orcl12c] Files:12 Time: 252s
Restart Phase #:26 [orcl12c] Files:1 Time: 1s
Serial Phase #:27 [orcl12c] Files:1 Time: 0s
Serial Phase #:28 [orcl12c] Files:3 Time: 14s
Serial Phase #:29 [orcl12c] Files:1 Time: 0s
Restart Phase #:30 [orcl12c] Files:1 Time: 2s
*************** Catproc CDB Views **************
Serial Phase #:31 [orcl12c] Files:1 Time: 3s
Restart Phase #:32 [orcl12c] Files:1 Time: 2s
Serial Phase #:34 [orcl12c] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [orcl12c] Files:293 Time: 101s
Serial Phase #:36 [orcl12c] Files:1 Time: 0s
Restart Phase #:37 [orcl12c] Files:1 Time: 1s
Serial Phase #:38 [orcl12c] Files:6 Time: 22s
Restart Phase #:39 [orcl12c] Files:1 Time: 2s
*************** Catproc DataPump ***************
Serial Phase #:40 [orcl12c] Files:3 Time: 148s
Restart Phase #:41 [orcl12c] Files:1 Time: 4s
****************** Catproc SQL *****************
Parallel Phase #:42 [orcl12c] Files:13 ^[ Time: 266s
Restart Phase #:43 [orcl12c] Files:1 Time: 4s
Parallel Phase #:44 [orcl12c] Files:11 Time: 34s
Restart Phase #:45 [orcl12c] Files:1 Time: 2s
Parallel Phase #:46 [orcl12c] Files:3 Time: 6s
Restart Phase #:47 [orcl12c] Files:1 Time: 1s
************* Final Catproc scripts ************
Serial Phase #:48 [orcl12c] Files:1 Time: 19s
Restart Phase #:49 [orcl12c] Files:1 Time: 1s
************** Final RDBMS scripts *************
Serial Phase #:50 [orcl12c] Files:1 Time: 8s
************ Upgrade Component Start ***********
Serial Phase #:51 [orcl12c] Files:1 Time: 3s
Restart Phase #:52 [orcl12c] Files:1 Time: 1s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [orcl12c] Files:2 Time: 814s
***************** Upgrading XDB ****************
Restart Phase #:54 [orcl12c] Files:1 Time: 4s
Serial Phase #:56 [orcl12c] Files:3 Time: 17s
Serial Phase #:57 [orcl12c] Files:3 Time: 11s
Parallel Phase #:58 [orcl12c] Files:10 Time: 17s
Parallel Phase #:59 [orcl12c] Files:25 Time: 17s
Serial Phase #:60 [orcl12c] Files:4 Time: 21s
Serial Phase #:61 [orcl12c] Files:1 Time: 0s
Serial Phase #:62 [orcl12c] Files:32 Time: 17s
Serial Phase #:63 [orcl12c] Files:1 Time: 0s
Parallel Phase #:64 [orcl12c] Files:6 Time: 13s
Serial Phase #:65 [orcl12c] Files:2 Time: 53s
Serial Phase #:66 [orcl12c] Files:3 Time: 72s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [orcl12c] Files:1 Time: 4s
Serial Phase #:69 [orcl12c] Files:1 Time: 5s
Parallel Phase #:70 [orcl12c] Files:2 Time: 113s
Restart Phase #:71 [orcl12c] Files:1 Time: 4s
Parallel Phase #:72 [orcl12c] Files:2 Time: 5s
Serial Phase #:73 [orcl12c] Files:2 Time: 6s
***************** Upgrading SDO ****************
Restart Phase #:74 [orcl12c] Files:1 Time: 2s
Serial Phase #:76 [orcl12c] Files:1 Time: 137s
Serial Phase #:77 [orcl12c] Files:2 Time: 7s
Restart Phase #:78 [orcl12c] Files:1 Time: 2s
Serial Phase #:79 [orcl12c] Files:1 Time: 111s
Restart Phase #:80 [orcl12c] Files:1 Time: 4s
Parallel Phase #:81 [orcl12c] Files:3 Time: 228s
Restart Phase #:82 [orcl12c] Files:1 Time: 4s
Serial Phase #:83 [orcl12c] Files:1 Time: 15s
Restart Phase #:84 [orcl12c] Files:1 Time: 1s
Serial Phase #:85 [orcl12c] Files:1 Time: 27s
Restart Phase #:86 [orcl12c] Files:1 Time: 3s
Parallel Phase #:87 [orcl12c] Files:4 Time: 355s
Restart Phase #:88 [orcl12c] Files:1 Time: 4s
Serial Phase #:89 [orcl12c] Files:1 Time: 5s
Restart Phase #:90 [orcl12c] Files:1 Time: 1s
Serial Phase #:91 [orcl12c] Files:2 Time: 35s
Restart Phase #:92 [orcl12c] Files:1 Time: 2s
Serial Phase #:93 [orcl12c] Files:1 Time: 2s
Restart Phase #:94 [orcl12c] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [orcl12c] Files:1 Time: 36s
Restart Phase #:96 [orcl12c] Files:1 Time: 3s
*********** Final Component scripts ***********
Serial Phase #:97 [orcl12c] Files:1 Time: 6s
************* Final Upgrade scripts ************
Serial Phase #:98 [orcl12c] Files:1 Time: 629s
******************* Migration ******************
Serial Phase #:99 [orcl12c] Files:1 Time: 3s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [orcl12c] Files:1 Time: 2s
Serial Phase #:101 [orcl12c] Files:1 Time: 0s
Serial Phase #:102 [orcl12c] Files:1 Time: 71s
***************** Post Upgrade *****************
Serial Phase #:103 [orcl12c] Files:1 Time: 51s
**************** Summary report ****************
Serial Phase #:104 [orcl12c] Files:1 Time: 4s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [orcl12c] Files:1 Time: 3s
Serial Phase #:106 [orcl12c] Files:1 Time: 0s
Serial Phase #:107 [orcl12c] Files:1 Time: 28s
——————————————————
Phases [0-107] End Time:[2020_06_25 01:17:39]
——————————————————
Grand Total Time: 5703s
LOG FILES: (/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/orcl12c/upgrade20200624234226/catupgrd*.log)
Upgrade Summary Report Located in:
/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/orcl12c/upgrade20200624234226/upg_summary.log
Grand Total Upgrade Time: [0d:1h:35m:3s]
- Step 11
Start the Database
[oracle@gg1 dbhome_1]$ export ORACLE_SID=orcl12c
[oracle@gg1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@gg1 dbhome_1]$ export PATH=$ORACLE_HOME/bin:$PATH
SQL> startup
Total System Global Area 1560277408 bytes
Fixed Size 8896928 bytes
Variable Size 1174405120 bytes
Database Buffers 369098752 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select name,open_mode,cdb,version,status from v$database,v$instance;
NAME OPEN_MODE CDB VERSION STATUS
——— ——————– — —————– ————
ORCL12C READ WRITE NO 19.0.0.0.0 OPEN
Verify all the database components:-
SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
COMP_ID COMP_NAME VERSION STATUS
———- —————————————- ————— ——————————————–
CATALOG Oracle Database Catalog Views 19.0.0.0.0 UPGRADED
CATPROC Oracle Database Packages and Types 19.0.0.0.0 UPGRADED
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 UPGRADED
XML Oracle XDK 19.0.0.0.0 UPGRADED
CATJAVA Oracle Database Java Packages 19.0.0.0.0 UPGRADED
APS OLAP Analytic Workspace 19.0.0.0.0 UPGRADED
RAC Oracle Real Application Clusters 19.0.0.0.0 UPGRADED
XDB Oracle XML Database 19.0.0.0.0 UPGRADED
OWM Oracle Workspace Manager 19.0.0.0.0 UPGRADED
CONTEXT Oracle Text 19.0.0.0.0 UPGRADED
ORDIM Oracle Multimedia 19.0.0.0.0 UPGRADED
SDO Spatial 19.0.0.0.0 UPGRADED
XOQ Oracle OLAP API 19.0.0.0.0 UPGRADED
OLS Oracle Label Security 19.0.0.0.0 UPGRADED
DV Oracle Database Vault 19.0.0.0.0 UPGRADED
- Step 12
Run postupgrade_fixups.sql
SQL>@/u01/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: 2020-06-24 22:35:07
For Source Database: ORCL12C
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
—— ———————— ———- ——————————–
5. old_time_zones_exist NO Manual fixup recommended.
6. dir_symlinks YES None.
7. post_dictionary YES None.
8. 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 13
Upgrade Timezone file as reported in Post upgrade fixup script.
To upgrade the timezone, run the following scripts.
SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/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 DSTv26 .
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> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/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 931133920 bytes
Fixed Size 8903136 bytes
Variable Size 452984832 bytes
Database Buffers 465567744 bytes
Redo Buffers 3678208 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 931133920 bytes
Fixed Size 8903136 bytes
Variable Size 452984832 bytes
Database Buffers 465567744 bytes
Redo Buffers 3678208 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_S”
Number of failures: 0
Table list: “GSMADMIN_INTERNAL”.”AQ$_CHANGE_LOG_QUEUE_TABLE_L”
Number of failures: 0
Table list: “MDSYS”.”SDO_DIAG_MESSAGES_TABLE”
Number of failures: 0
Table list: “DVSYS”.”SIMULATION_LOG$”
Number of failures: 0
Table list: “DVSYS”.”AUDIT_TRAIL$”
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> SELECT version FROM v$timezone_file;
VERSION
———-
32
1 row selected.
- Step 14
Run utlusts.sql to verify the database components upgraded version and status.
SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool 06-25-2020 09:45:1
Database Name: ORCL12C
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server UPGRADED 19.3.0.0.0 00:45:15
JServer JAVA Virtual Machine UPGRADED 19.3.0.0.0 00:04:10
Oracle XDK UPGRADED 19.3.0.0.0 00:02:55
Oracle Database Java Packages UPGRADED 19.3.0.0.0 00:00:32
OLAP Analytic Workspace UPGRADED 19.3.0.0.0 00:00:50
Oracle Label Security UPGRADED 19.3.0.0.0 00:00:33
Oracle Database Vault UPGRADED 19.3.0.0.0 00:01:10
Oracle Text UPGRADED 19.3.0.0.0 00:01:37
Oracle Workspace Manager UPGRADED 19.3.0.0.0 00:01:36
Oracle Real Application Clusters UPGRADED 19.3.0.0.0 00:00:01
Oracle XML Database UPGRADED 19.3.0.0.0 00:03:52
Oracle Multimedia UPGRADED 19.3.0.0.0 00:02:07
Spatial UPGRADED 19.3.0.0.0 00:15:33
Oracle OLAP API UPGRADED 19.3.0.0.0 00:00:33
Datapatch 00:10:16
Final Actions 00:10:32
Post Upgrade 00:00:45
Total Upgrade Time: 01:32:57
Database time zone version is 32. It meets current release needs.
- Step 15
Check for invalid objects
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———-
2270
Validate invalid objects by running utlrp.sql
SQL>@?/rdbms/admin/utlrp.sql
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
———-
0
- Step 16
Set the compatible parameter to 19c and restart the database.
NAME TYPE VALUE
———————————— ———– ——————————
compatible string 12.2.0
noncdb_compatible boolean FALSE
SQL> alter system set compatible=’19.0.0′ scope=spfile;
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 931133920 bytes
Fixed Size 8903136 bytes
Variable Size 494927872 bytes
Database Buffers 423624704 bytes
Redo Buffers 3678208 bytes
ORA-38880: Cannot advance compatibility from 12.2.0.0.0 to 19.0.0.0.0 due to
guaranteed restore points
Here , we encountered an error while starting the DB. Because the restore point we created earlier before the upgrade was
done on compatible=12.2.0. Hence oracle wont let us to change the compatibility setting unless we remove it.
To fix it.
SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> create pfile from spfile;
SQL> exit
Change the compatible parameter value from ‘19.0.0’ to ‘12.2.0’ and start the database using this pfile.
SQL> startup pfile=’initorcl12c.ora’;
ORACLE instance started.
Total System Global Area 931133920 bytes
Fixed Size 8903136 bytes
Variable Size 494927872 bytes
Database Buffers 423624704 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.
Now drop the guaranteed restore point we created before the upgrade. Remember if we drop the restore point at this point
we wont able to rollback the database before the upgrade.
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
NAME GUA TIME
————————- — ———————————————
BEFORE_UPGRADE YES 24-JUN-20 11.06.17.000000000 PM
SQL> drop restore point before_upgrade;
Now change the compatible parameter again.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 931133920 bytes
Fixed Size 8903136 bytes
Variable Size 494927872 bytes
Database Buffers 423624704 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.
SQL> alter system set compatible=’19.0.0′ scope=spfile;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 931133920 bytes
Fixed Size 8903136 bytes
Variable Size 494927872 bytes
Database Buffers 423624704 bytes
Redo Buffers 3678208 bytes
Database mounted.
Database opened.
SQL> show parameter compatible
NAME TYPE VALUE
———————————— ———– ——————————
compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL>
- Step 17
Start Listener from 19c home.
- Step 18
Modify oratab with 19c entry
#orcl12c:/u01/app/oracle/product/12.2.0.1:N
orcl12c:/u01/app/oracle/product/19.0.0/dbhome_1:N
- Step 19
Backup the Database once entire activity is complete. The Backup script is provided on step 1.
This concludes our database upgrade activity from 12c to 19c
Leave a Reply