SVGulhane's IT Blog: Upgrade Oracle Database to Latest Oracle Database 18c

Upgrade Types

Database Upgrade Assistant (DBUA) – Interactive GUI method.

Command-Line Method for Upgrading Oracle Database – Manual Method

Oracle Data Pump – Export/Import Method

IMPORTANT POINTS TO REMEMBER

DBUA runs the Pre-Upgrade Information Tool as part of the prerequisite checks it performs before starting the upgrade. However, to reduce downtime, Oracle strongly recommends that you run the Pre-Upgrade Information Tool manually.

If the DB Is too large and DB need to be upgraded to same server, then DBUA tool is the best option to select.

Make sure there are sufficient space for the tablespaces to grow during upgrade, else upgrade will get failed in between. Like in cluster DB Diskgroup need to have sufficient free space.

If you stop upgrade, either you need to restore the DB to start again with DBUA or without restoring DB you need to upgrade DB using Manual (Command Line) upgrade procedure.

Need to Use Same OS user of Previous Database Version

Method 1 – DBUA

After running DBUA from the 18c binary, which was already installed.

ORACLE_HOME/bin

./dbua

Under Step 1 of 10

Select the database need to upgrade to higher version

Under Step 2 of 10

Select the PDB’s going to upgrade

Under Step 3 of 10

Pre-requisite check Result, this will be either Manual or Auto fixable and having severity as Warning or Error.

It may includes , apply patch to source server, OS or DB parameter changes, PGA/SGA memory suggestions, and etc.

Under Step 4 of 10

Select Upgrade Options such as

Enable Parallel Upgrade
Recompile Invalid Objects During Post Upgrade
Upgrade Timezone Data

Select User tablespace to Read Only during Upgrade

You can specify the SQL script to be executed before or after upgrade

Under Step 5 of 10

Select Recovery Option

1)Flashback and Guaranteed Restore Point

2)Use RMAN backup

Under Step 6 of 10

Select the existing listener or you can create new Listener.

Under Step 7 of 10

Configure Enterprise manager or you can register in existing OMS

Method 2 – Manual Upgrade

(Local or Remote Cloning / Plug-out, Plug-In)

Run the Pre-Upgrade Information Tool on the PDB.

[db1221@whf00ccs ~]$ $ORACLE_HOME/jdk/bin/java -jar /scratch/db/db1800/product/18.0.0/dbhome_1/rdbms/admin/preupgrade.jar dir /tmp –c PDBTESTCLONE

==================

PREUPGRADE SUMMARY

==================

  /tmp/preupgrade.log

  /tmp/preupgrade_fixups.sql

  /tmp/postupgrade_fixups.sql

Here in this script

$ORACLE_HOME/jdk/bin/java is the Source Oracle Java Home
/scratch/db/db1800/product/18.0.0/dbhome_1/rdbms/admin/preupgrade.jar

is the target ORACLE HOME preupgrade.jar file, it will decide on which version db going to upgrade

Run preupgrade_fixups.sql on your source database.

For example:

CONNECT / AS SYSDBA

SQL> ALTER SESSION SET CONTAINER= PDBTESTCLONE ;

SQL> @/tmp/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script

Version: 18.0.0.0.0 Build: 1

Generated on:            2018-09-06 08:01:37

For Source Database:     OBCL12R2

Source Database Version: 12.2.0.1.0

For Upgrade to Version: 18.0.0.0.0

Executing in container: PDBTESTCLONE

Preup                             Preupgrade

Action                            Issue Is

Number Preupgrade Check Name     Remedied    Further DBA Action

—— ———————— ———- ——————————–
parameter_obsolete        NO Manual fixup recommended.
invalid_objects_exist       NO      Manual fixup recommended.
hidden_params NO Informational only. Further action is optional.
dictionary_stats YES None.
mv_refresh NO       Informational only. Further action is optional.
tablespaces_info NO      Informational only. Further action is optional.
parameter_deprecated NO      Informational only. Further action is optional.
cycle_number NO Informational only. Further action is optional

YOU HAVE CHOICE TO EITHER CLONE THE PDB OR UNPLUG AND PLUG THE PDB.

In Local clone method if the DB is too large and there are no much free space to duplicate the DB then you can go DBUA or Unplug and Plug method in same server. It will work on the existing DB Data files.

Local clone is suitable for smaller database where you can have the copy of the source in case of any issue.

UPGRADE USING CLONE PROCESS

The source PDB must be open in read-only mode.
The target database must have a database link to the source database.
The user in the source database that the database link connects to must have the create pluggable database privilege.

Steps to create the Remote Clone PDB

Create a common user in the source and give required permission.

(In Source)

create user c##dblinkuser identified by oracle123;

grant create session,create pluggable database to c##dblinkuser container=ALL;

Create a db link from target container database to source container database.

(In Target)

create database link pdblink connect to c##dblinkuser identified by oracle123 using ‘ofss2311694.in.oracle.com:1521/pdbtest1’;

Source PDB must be in read-only mode

alter pluggable database PDBTEST1 close immediate;

alter pluggable database PDBTEST1 open read only;

In target, create the directory for the PDB’s datafiles and create pluggable database from source pdb using db link. Execute following command to create the PDB

create pluggable database PDBTESTCLONE from PDBTEST1@pdblink file_name_convert=(‘/scratch/db1212dat/CDBTEST/PDBTEST1′,’/scratch/db1212dat/CDBTEST/PDBTESTCLONE’);

Upgrading the Earlier Release PDB to the Later Release

In target, open the clone DB in Upgrade mode, which is now in mounted mode

SQL> show pdbs

CON_ID CON_NAME                   OPEN MODE RESTRICTED

———- —————————— ———- ———-

2 PDB$SEED                      READ ONLY   NO

3 DLY124WLYCLN                  READ WRITE NO

4 PDBTESTCLONE                  MOUNTED     YES

SQL> alter pluggable database PDBTESTCLONE open upgrade;

SQL> show pdbs

CON_ID CON_NAME                    OPEN MODE RESTRICTED

———- —————————— ———- ———-

2 PDB$SEED                      READ ONLY   NO

3 DLY124WLYCLN                  READ WRITE NO

4 PDBTESTCLONE                  MIGRATE     YES

Upgrade the PDB Using Upgrade Utility Command

Method 1 : Parallel Upgrade Utility command (catctl.pl)

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d \

$ORACLE_HOME/rdbms/admin -c ‘PDBTESTCLONE’ -l $ORACLE_BASE catupgrd.sql

Method 2 : Shell utility (dbupgrade)

/scratch/db/db1800/product/18.0.0/dbhome_1/bin/dbupgrade

Post-upgrade Fixup and Invalid object compilation

recompile the INVALID objects in the database:

SQL> ALTER SESSION SET CONTAINER=PDBTESTCLONE;

SQL> @/tmp/postupgrade_fixups.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

COUNT(*)

———-

0

SQL> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

COUNT(*)

———-

59

SQL> conn / as sysdba

SQL> alter pluggable database PDBTESTCLONE close immediate;

SQL> alter pluggable database PDBTESTCLONE open;

SQL> show pdbs

CON_ID CON_NAME                    OPEN MODE RESTRICTED

———- —————————— ———- ———-

2 PDB$SEED                      READ ONLY    NO

3 DLY124WLYCLN                 READ WRITE   NO

4 PDBTESTCLONE                 READ WRITE   YES

SQL> select con_id, type, message, status

from PDB_PLUG_IN_VIOLATIONS

where status<>’RESOLVED’

order by time;

CONT_ID TYPE   MESSAGE                                                                      STATUS

——- ——   ———————————————————————–    ——–

10      ERROR SQL patch ID/UID 24923080/21874550 (): Installed in the PDB but not in the CDB.   PENDING

[db18c@blrgxr6116 OPatch]$ ./datapatch –verbose

………

……….

Current state of SQL patches:

Patch 24923080 ():

Installed in COMPR_PDB only

………

……….

Patch 24923080 rollback (pdb COMPR_PDB): SUCCESS

logfile: /scratch/db18c/app/db12c/cfgtoollogs/sqlpatch/24923080/21874550/24923080_rollback_CDB122R_COMPR_PDB_2018Sep26_15_22_46.log (no errors)

NOTE before running datapatch make sure:

Copy the Patch folder from the source Oracle Home (OH) to the target Oracle Home (OH) same sub-directories.

From source folder /u01/app/oracle/product/12.2.0.1/dbhome_1/sqlpatch to target OH.

[oracle@whfoda3011 sqlpatch]$ ls -lrt

total 228

-rwxr-x— 1 oracle oinstall    999 Apr 1 2016 sqlpatch.bat

-rwxr-x— 1 oracle oinstall   2732 Apr 1 2016 sqlpatch

-rw-r–r– 1 oracle oinstall   4208 Jul 2 2016 sqlpatch.pl

-rw-r–r– 1 oracle oinstall   3516 Aug 16 2016 sqlpatch_bootstrap_driver.sql

-rw-r–r– 1 oracle oinstall 14532 Aug 26 2016 sqlpatch_bootstrap.sql

drwxr-xr-x 7 oracle oinstall   4096 Jan 26 2017 lib

-rw-r—– 1 oracle oinstall 170922 Apr 10 10:40 sqlpatch.pm

drwxr-xr-x 3 oracle oinstall   4096 Apr 18 14:48 24923080

SQL> conn / as sysdba

SQL> alter pluggable database PDBTESTCLONE close immediate;

SQL> alter pluggable database PDBTESTCLONE open;

SQL> show pdbs

CON_ID CON_NAME                    OPEN MODE RESTRICTED

———- —————————— ———- ———-

2 PDB$SEED                      READ ONLY    NO

3 DLY124WLYCLN                 READ WRITE   NO

4 PDBTESTCLONE                 READ WRITE   NO

SVGulhane's IT Blog

Monday, 8 October 2018

Upgrade Oracle Database to Latest Oracle Database 18c