Soumya's Database Blog : How to upgrade Oracle Database 12c to 19c Manually

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

Hostname	gg1.soumya.com
DB SID	ORCL12C
Source of DB Version	12.2.0.1
CDB	Single Instance, Non CDB
Source DB Home	/u01/app/oracle/product/12.2.0.1
Target DB Version	19.3.0
Target DB Home	/u01/app/oracle/product/19.0.0/dbhome_1

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

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(*)

----------

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.

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

----------

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(*)

----------

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

If you like this post, then please share it with others.

Please subscribe on email for every updates on mail.

Disclaimer:- The use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Reference:

Oracle 19c – Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 19c (Doc ID 2539778.1)

https://docs.oracle.com/en/database/oracle/oracle-database/19/index.html

Soumya's Database Blog

How to upgrade Oracle Database 12c to 19c Manually

No comments:

Post a Comment