Amit's Oracle DBA Blog: 2021

Server	DB vesrion
	12c,18c-à19c

AutoUpgrade Tool (Doc ID 2485457.1)

In this demonstration, I have upgraded Oracle Database from 12.2.0.1 to 19.0.0.0 which is running on Oracle Linux 7.5

There were two methods till 18c for upgrade,

1.DBUA

2. Manual – Using preupgrade.jar

· However, in Oracle Database 19c there’s new method of upgrading the database is introduced. That is using Auto Upgrade utility. Database can directly be upgraded to 19c from below releases 11.2.0.3, 11.2.0.4, 12.1.0.1, 12.1.0.2, 12.2.0.1 and 18

· Oracle has released a new tool “Auto-Upgrade” to automate the process of upgrading Oracle Database Management System. This tool is much needed now, as the upgrade process is “lengthy” procedure ,and having such automation capability will shorten the period of upgrading any organization’s Large Oracle database infrastructure.The tool will let you upgrade from 11g (11.2.0.4) as the “source” minimum version to either 12cR2, 18c , 19c releases as “target” version.

aPre-requisite checks:

Check Java version under Oracle Home, it should be version 8. Java 8 is available in Oracle database homes for Oracle 12cR2 release and beyond. If your Oracle release is 11g (11.2.0.4) either install java 8 to your Oracle Home or use the “target” release java Oracle Home directory.

To check which java release you have , execute the following command:

$ORACLE_HOME/jdk/bin/java –version

$ /applications/oracle/12.2.0.1/jdk/bin/java -version

java version "1.8.0"

Java(TM) SE Runtime Environment (build pap6480sr3-20160428_01(SR3))

IBM J9 VM (build 2.8, JRE 1.8.0 AIX ppc64-64 Compressed References 20160427_301573 (JIT enabled, AOT enabled)

J9VM - R28_Java8_SR3_20160427_1620_B301573

JIT - tr.r14.java.green_20160329_114288

GC - R28_Java8_SR3_20160427_1620_B301573_CMPRSS

J9CL - 20160427_301573)

JCL - 20160421_01 based on Oracle jdk8u91-b14

$ /applications/oracle/18.4.0.1/jdk/bin/java -version

java version "1.8.0_171"

Java(TM) SE Runtime Environment (build 8.0.5.17 - pap6480sr5fp17-20180627_01(SR5 FP17))

IBM J9 VM (build 2.9, JRE 1.8.0 AIX ppc64-64-Bit Compressed References 20180626_390413 (JIT enabled, AOT enabled)

OpenJ9 - 5cdc604

OMR - a24bc01

IBM - 21870d6)

JCL - 20180619_01 based on Oracle jdk8u171-b11

1. 1) Take full backup of your database with controlfile and spfile. So that if anything goes wrong during upgrade, you can always restore your database.

2. 2) Make sure the backup is completed by querying v$backup view.

3. 3) Pre-Upgrade actions.

Recompile invalid objects.

Check invalid objects from DBA_OBJECTS using below query.

SQL> SELECT count(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';

Recompile invalids using utlrp.sql

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

Gather Dictionary Statistics.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

Purge DBA Recyclebin.

Check if DBA_RECYCLEBIN has any objects in it.

SQL> SELECT COUNT(*) FROM DBA_RECYCLEBIN;

Purge DBA_RECYCLEBIN using below query.

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

4. 4) Create sample Auto-Upgrade config file.

First of all, let us make one directory called upgrade_to_19c which will hold all the config files, log files during this upgrade.

/applications/oracle/12.2.0.1/jdk/bin/java -jar /applications/oracle/19.3.0.1/rdbms/admin/autoupgrade.jar -version

build.version 20190207

build.date 2019/02/07 12:35:56

build.label RDBMS_PT.AUTOUPGRADE_LINUX.X64_190205.1800

First of all, let us make one directory called upgrade_to_19c which will hold all the config files, log files during this upgrade.

$ mkdir mkdir upgrade_to_19c

You have mail in /usr/spool/mail/oracle

$ cd upgrade_to_19c

$ pwd

/ORACLE/TESTDB2/base/upgrade_to_19c

Run autoupgrade.jar to create sample file in current directory.

$ /applications/oracle/12.2.0.1/jdk/bin/java -jar /applications/oracle/19.3.0.1/rdbms/admin/autoupgrade.jar -create_sample_file config

Created sample configuration file /ORACLE/TESTDB2/base/upgrade_to_19c/sample_config.cfg

$ ls -lrt

total 8

drwxr-xr-x 2 oracle dba 256 Mar 30 11:05 AUTOUPGRADE

-rw-r--r-- 1 oracle dba 1943 Mar 30 11:12 sample_config.cfg

$ cat sample_config.cfg

# $Header: rdbms/src/server/upgrade/autoupgrade/src/main/resources/autoupgrade/templates/sample_config_unix.properties /st_rdbms_pt-autoupgrade/7 2018/07/11 09:38:27 frealvar Exp $

# DESCRIPTION

# This is a template for config file to be used with autoupgrade tool.

# NOTES

# <other useful comments, qualifications, etc.>

# MODIFIED (MM/DD/YY)

# frealvar 07/10/18 - code refactor due to AUPG-250

# frealvar 04/30/18 - AUPG-189 optionally run utlrp and timezone upgrades

# fvallin 04/06/17 - Creation

#Global configurations

#Autoupgrade's global directory, non-job logs generated,

#temp files created and other autoupgrade files will be

#send here

global.autoupg_log_dir=/default/current/location

# sample config file

# Database number 1

upg1.dbname=employee

upg1.start_time=NOW

upg1.source_home=/u01/app/oracle/product/11.2.0/dbhome_1

upg1.target_home=/u01/app/oracle/product/19.1.0/dbhome_1

upg1.sid=emp

upg1.log_dir=/scratch/auto

upg1.upgrade_node=node1

upg1.target_version=19.1

#upg1.run_utlrp=yes

#upg1.timezone_upg=yes

# Database number 2

#upg2.dbname=database name

#upg2.start_time=dd/mm/yyyy hh:mm:ss | now

#upg2.source_home=Path to source version of Oracle database

#upg2.target_home=Path to target version of Oracle database

#upg2.sid=Value of Oracle SID

#upg2.log_dir=Path to log dir

#upg2.upgrade_node=Node name

#upg2.target_version= 12.2.0.1

#upg2.run_utlrp=[yes|no]

#upg2.timezone_upg=[yes|no]

# You can have as many databases as desired

#upgN.dbname=database name

#upgN.start_time=dd/mm/yyyy hh:mm:ss/now

#upgN.source_home=Path to source version of Oracle database

#upgN.target_home=Path to target version of Oracle database

#upgN.sid=Value of Oracle SID

#upgN.log_dir=Path to log dir

#upgN.upgrade_node= Node name

#upgN.target_version= 18.1

#upgN.run_utlrp=[yes|no]

#upgN.timezone_upg=[yes|no]

Copy config file to sample_config.cfg and edit it to make necessary changes.

Delete unwanted lines from my_config.cfg file and make necessary changes as below.

$ pwd

/ORACLE/TESTDB2/base/upgrade_to_19c

$ ls -lrt

total 8

-rw-r--r-- 1 oracle dba 1943 Mar 12 13:12 sample_config.cfg

$ pwd

/ORACLE/TESTDB2/base/upgrade_to_19c

$ cp sample_config.cfg my_config.txt

$ ls -lrt

total 16

-rw-r--r-- 1 oracle dba 1943 Mar 12 13:12 sample_config.cfg

-rw-r--r-- 1 oracle dba 1943 Mar 12 18:02 my_config.txt

$ cat my_upgrade.cfg

# Global logging directory pertains to all jobs

global.autoupg_log_dir=/ORACLE/TESTDB2/base/upgrade_to_19c/GLOBAL_LOGS

# Database 1

upg1.dbname=UPGRADE1 # Database Name (Required)

upg1.source_home=/applications/oracle/12.2.0.1 # Source Home (Required)

upg1.target_home=/applications/oracle/19.3.0.1 # Target home (Required)

upg1.sid=UPGRADE1 # Oracle Sid (Required)

#upg1.start_time=09/06/2019 17:30:00 # Start time of the operation (Required)

upg1.start_time=NOW

upg1.log_dir=/ORACLE/TESTDB2/base/upgrade_to_19c/UPGRADE_UPGRADE1_LOGS_NEW

upg1.upgrade_node=localhost # Upgrade node that operation will run on (Required)

upg1.run_utlrp=yes # yes(default) to run utlrp as part of upgrade, no to skip it (Optional)

upg1.timezone_upg=yes # yes(default) to upgrade timezone if needed, no to skip it (Optional)

upg1.target_version=19 # Oracle Home Target version number (Required)

upg1.restoration=no # For GRP

# Database 2

upg2.dbname=BUGPATCH # Database Name (Required)

upg2.source_home=/applications/oracle/18.4.0.1 # Source Home (Required)

upg2.target_home=/applications/oracle/19.3.0.1 # Target home (Required)

upg2.sid=BUGPATCH # Oracle Sid (Required)

#upg2.start_time=09/06/2019 17:30:00 # Start time of the operation (Required)

upg2.start_time=NOW

upg2.log_dir=/ORACLE/TESTDB2/base/upgrade_to_19c/UPGRADE_BUGPATCH_LOGS_NEW

upg2.upgrade_node=localhost # Upgrade node that operation will run on (Required)

upg2.run_utlrp=yes # yes(default) to run utlrp as part of upgrade, no to skip it (Optional)

upg2.timezone_upg=yes # yes(default) to upgrade timezone if needed, no to skip it (Optional)

upg2.target_version=19 # Oracle Home Target version number (Required)

upg2.restoration=no # For GRP

1. The Four Modes of AutoUpgrade processing described in Oracle Docs (ANALYZE, FIXUP, DEPLOY & UPGRADE).

· ANALYZE : This mode perform pre-checks on source database to see if it is ready for upgrade. It will only ANALYZE the source database and will to perform any changes.

· FIXUPS : This mode performs the pre-checks on source database, also it will attempt to fix up the items that must be corrected before it can be upgraded.

· DEPLOY : This mode will perform actual upgrade of the source database. If needed it can also perform fixups on database.

· UPGRADE : Upgrade mode enables you to upgrade the target Oracle Home in cases where you do not have access to source Oracle Home.

You can run auto upgrade directly in DEPLOY mode without running ANALYZE and FIXUPS mode. But if you do not want to take chances to see unforseen abortion of upgrade, you better run it in ANALYZE and FIXUPS mode before running it in DEPLOY mode.

2. Running AutoUpgrade.jar

$ pwd

/ORACLE/TESTDB2/base/upgrade_to_19c

$ /applications/oracle/12.2.0.1/jdk/bin/java -jar /ORACLE/TESTDB2/base/upgrade_to_19c/AUTOUPGRADE/autoupgrade.jar -config my_upgrade.cfg -mode ANALYZE

AutoUpgrade tool launched with default options

Processing config file ...

+--------------------------------+

| Starting AutoUpgrade execution |

+--------------------------------+

2 databases will be analyzed

Type 'help' to list console commands

upg> tasks

+--+--------------------+-------------+

|ID| NAME| Job#|

$ cp my_upgrade.cfg /ORACLE/TESTDB2/base/upgrade_to_19c/UPGRADE_UPGRADE1_LOGS_NEW/UPGRADE1/105/prechecks

$ cp my_upgrade.cfg /ORACLE/TESTDB2/base/upgrade_to_19c/UPGRADE_BUGPATCH_LOGS_NEW/BUGPATCH/104/prechecks

$ /applications/oracle/12.2.0.1/jdk/bin/java -jar /ORACLE/TESTDB2/base/upgrade_to_19c/AUTOUPGRADE/autoupgrade.jar -config my_upgrade.cfg -mode fixups

AutoUpgrade tool launched with default options

Processing config file ...

+--------------------------------+

| Starting AutoUpgrade execution |

+--------------------------------+

2 databases will be processed

Type 'help' to list console commands

upg> status

---------------- Config -------------------

User configuration file [/ORACLE/TESTDB2/base/upgrade_to_19c/my_upgrade.cfg]

General logs location [/ORACLE/TESTDB2/base/upgrade_to_19c/GLOBAL_LOGS/cfgtoollogs/upgrade/auto]

$ /applications/oracle/12.2.0.1/jdk/bin/java -jar /ORACLE/TESTDB2/base/upgrade_to_19c/AUTOUPGRADE/autoupgrade.jar -config my_upgrade.cfg -mode DEPLOY

AutoUpgrade tool launched with default options

Processing config file ...

+--------------------------------+

| Starting AutoUpgrade execution |

+--------------------------------+

2 databases will be processed

Type 'help' to list console commands

SQL> show parameter spfile;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /applications/oracle/19.3.0.1/

dbs/spfileUPGRADE1.ora

SQL>

SQL> SELECT VERSION FROM V$TIMEZONE_FILE;

VERSION

----------

SQL>

SQL> SELECT DBMS_DST.get_latest_timezone_version FROM DUAL;

GET_LATEST_TIMEZONE_VERSION

---------------------------

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

$ $

$ . oraenv

ORACLE_SID = [UPGRADE1] ? BUGPATCH

The Oracle base remains unchanged with value /applications/oracle

$ sqlplus "/ as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 30 15:21:12 2020

Version 19.3.0.0.0

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

----------

SQL> SELECT DBMS_DST.get_latest_timezone_version FROM DUAL;

GET_LATEST_TIMEZONE_VERSION

---------------------------

SQL>

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

SQL> SELECT NAME,HOST_NAME,VERSION,OPEN_MODE FROM V$DATABASE,V$INSTANCE;

NAME HOST_NAME

--------- ----------------------------------------------------------------

VERSION OPEN_MODE

----------------- --------------------

BUGPATCH ffm04aixhatest02-mng

19.0.0.0.0 READ WRITE

SQL>

SQL> show parameter compatible;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

compatible string 18.0.0

noncdb_compatible boolean FALSE

SQL>

SQL> show parameter name;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

cdb_cluster_name string

cell_offloadgroup_name string

db_file_name_convert string

db_name string BUGPATCH

db_unique_name string BUGPATCH

global_names boolean FALSE

instance_name string BUGPATCH

lock_name_space string

log_file_name_convert string

pdb_file_name_convert string

processor_group_name string

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

service_names string BUGPATCH.media-saturn.com

SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> SET LINESIZE 400

SET PAGESIZE 100

COLUMN action_time FORMAT A20

COLUMN action FORMAT A10

COLUMN status FORMAT A10

COLUMN description FORMAT A60

SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,

action,

status,

description,

patch_id

FROM sys.dba_registry_sqlpatch

ORDER by action_time;SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7

ACTION_TIME ACTION STATUS DESCRIPTION PATCH_ID

-------------------- ---------- ---------- ------------------------------------------------------------ ----------

30-MAR-2020 14:46:36 APPLY SUCCESS Database Release Update : 19.3.0.0.190416 (29517242) 29517242

SQL> column comp_name format a30

column version format a20

column status format a15

column owner format a30

column object_name format a30

column object_type format a15

column long_name format a75

column role format a40

column act_time format a24

column action format a15

column comments format a20

set pagesize 500

set linesize 150

set trimspool on

set serveroutput on

set echo onSQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

SQL>

SQL> SELECT SUBSTR(comp_name, 1, 30) comp_name, SUBSTR(version, 1, 20) version, status

FROM dba_registry ORDER BY comp_name; 2

COMP_NAME VERSION STATUS

------------------------------ -------------------- ---------------

JServer JAVA Virtual Machine 19.0.0.0.0 VALID

OLAP Analytic Workspace 19.0.0.0.0 VALID

Oracle Database Catalog Views 19.0.0.0.0 VALID

Oracle Database Java Packages 19.0.0.0.0 VALID

Oracle Database Packages and T 19.0.0.0.0 VALID

Oracle Database Vault 19.0.0.0.0 VALID

Oracle Label Security 19.0.0.0.0 VALID

Oracle Multimedia 19.0.0.0.0 VALID

Oracle OLAP API 19.0.0.0.0 VALID

Oracle Real Application Cluste 19.0.0.0.0 OPTION OFF

Oracle Text 19.0.0.0.0 VALID

Oracle Workspace Manager 19.0.0.0.0 VALID

Oracle XDK 19.0.0.0.0 VALID

Oracle XML Database 19.0.0.0.0 VALID

Spatial 19.0.0.0.0 VALID

15 rows selected.

SQL> SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') act_time, action, version, id, comments FROM dba_registry_history ORDER BY action_time DESC;

6 rows selected.

SQL>

Change the compatibility:

Amit's Oracle DBA Blog

Disclaimer

Friday, 24 December 2021

Upgrade to Oracle Database 19c from 12c using autoupgrade.jar

How to recovery PDB when PDB database is dropped in Oracle

Labels