oracle update from 12c to 19c steps

 

Upgrade Oracle 19c step by step guide

Oracle Upgrade from 12.2 to 19c

oracle update from 12c to 19c steps

Purpose of the document is to upgrade 12.2, which is running on physical server to upgrade into 19c version.

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment.  

For more detail you can check the Oracle Doc:

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

 

# Minimum version, which can directly upgraded.

11.2.0.4 ==>> 19c

12.1.0.2 ==>> 19c

12.2.0.1 ==>> 19c

18c      ==>> 19c

 

Source Database		Intermediate upgrade path		Target database
12.1.0.1	-->	12.1.0.2/12.2.0.1	-->	19c
11.2.0.1/11.2.0.2/11.2.0.3	-->	11.2.0.4	-->	19c
11.1.0.6/11.1.0.7	-->	11.2.0.4	-->	19c
10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5	-->	11.2.0.4/12.1.0.2	-->	19c
10.1.0.5	-->	11.2.0.4/12.1.0.2	-->	19c
9.2.0.8 or earlier	-->	11.2.0.4	-->	19c

 

# Pre-Upgrade tasks:

I am using the GUI for Installation. 

NOTE: If you don't use GRID, Then ignore following steps and start directelly with the steps "Install  DB Just Oracle Software" 

Just Installing GRID Software.

Create new Oracle_Home directory und unzip the GI files as follow.

unzip LINUX.X64_193000_grid_home.zip -d /oracle19c/product/19.3.0.0/grid

Importent NOTE: Make sure that you install latest OPATCH

Download lest OPatch from "https://updates.oracle.com/download/6880880.html"

Latest OPatch copy files.

p6880880_190000_Linux-x86-64.zip

cd $GRID_HOME

mv OPatch OPatch_old

cd $DOWNLOAD_PATCH/OPatch

cp -r OPatch $GRID_HOME

$ORACLE_HOME/OPatch/opatch version

$ORACLE_HOME/OPatch/opatch lspatches

 

Using MobaXterm for DISPLAY:

./gridSetup.sh -applyRU /JAN_PATCH_19c_2022/33567274/33509923

choose following Options

-> Upgrade Oracle Grid Infrastructure

-> No need EM

-> No need Automatically run configuration script

-> Ignore swap size

-> execute rootupgrade.sh

-> No need to overwrite

-> n

-> n

-> Finish Close

#Copy spfile+ASM.ora from 12c ASM_HOME to 19c ASM_HOME

# STOP ASM

srvctl stop asm -f

cp spfile+ASM.ora $ORACLE_HOME/dbs

srvctl modify asm -p '$ORACLE_HOME/dbs/spfile+ASM.ora'

srvctl start asm

srvctl config asm -a

Install  DB Just Oracle Software.

unzip LINUX.X64_193000_db_home.zip -d /oracle19c/product/19.3.0.0/db

Importent NOTE: Make sure that you install latest OPATCH

cd $ORACLE_HOME

mv OPatch OPatch_old

cd $DOWNLOAD_PATCH/OPatch

cp -r OPatch $ORACLE_HOME

$ORACLE_HOME/OPatch/opatch version

$ORACLE_HOME/OPatch/opatch lspatches

export DISPLAY=xx.xx.xx.xx:0.0

unset ORACLE_BASE

unset ORACLE_HOME

unset ORACLE_SID

./runInstaller -applyRU /JAN_PATCH_19c_2022/33567274/33509923

=>> Set Up Software Only

=>> Single Instance database installation

=>> Enterprise Edition

=>> keep the same Oracle Base

=>> chose the group you have.

=>> Keep as it is Uncheck "Automatically run configuration script"

=>> Click on Install

Execute the root script as root user.

/oracle19c/product/19.3.0.0/db/root.sh

Performing root user operation.

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /oracle19c/product/19.3.0.0/db

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The contents of "dbhome" have not changed. No need to overwrite.

The contents of "oraenv" have not changed. No need to overwrite.

The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Oracle Trace File Analyzer (TFA - Standalone Mode) is available at :

    /oracle19c/product/19.3.0.0/db/bin/tfactl

Note :

1. tfactl will use TFA Service if that service is running and user has been granted access

2. tfactl will configure TFA Standalone Mode only if user has no access to TFA Service or TFA is not installed

Backup your database before you start update

# Backup ==>> RMAN backup

 

# TNS Files

12c_Home:

cd $ORACLE_HOME/network/admin/

cp -p listener.ora sqlnet.ora tnsnames.ora /users/oracle/update19c/bkpOraFiles

 

cd $ORACLE_HOME/dbs

cp -p spfile* orapw* /users/oracle/update19c/bkpOraFiles

 

# Check for any invalid Objects

select count(*) from dba_objects where status='INVALID';

 

# Run preupgrade scrips

From Oracle 12c home:

mkdir -p /users/oracle/update19c/preupgrade

$ORACLE_HOME/jdk/bin/java -jar /oracle19c/product/19.3.0.0/db/rdbms/admin/preupgrade.jar FILE DIR /users/oracle/update19c/preupgrade

 

# View Preupgrade log files and fix the requirements

 

# verify tablespacesize for upgrade

 

# Check the current time zone version 

COL PROPERTY_NAME FOR A30
COL PROPERTY_VALUE FOR A25

select * from v$timezone_file;

select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name ='DST_PRIMARY_TT_VERSION';

 

# Gather DICTIONARY STATS. This will reduce the total time of upgrade.

SET ECHO ON;
SET SERVEROUTPUT ON;

exec dbms_stats.gather_fixed_objects_stats;

exec dbms_stats.gather_schema_stats ('SYSTEM');

exec dbms_stats.gather_schema_stats ('SYS');

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

# Purge Recyclebin

PURGE DBA_RECYCLEBIN;

SQL> select count(*) from DBA_RECYCLEBIN;
  COUNT(*)

----------

         0

# Wait until all views completed refreshed.

SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

 

declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;

/

# Upgrade APEX: it’s recommended to upgrade it prior to upgrading the database to 19c following doc ID 1088970.1. 

select COMP_ID, VERSION, STATUS from DBA_REGISTRY where COMP_ID='APEX';

# Run preupgrade_fixups.sql

 

From Oracle 12c home::

@/users/oracle/update19c/preupgrade/preupgrade_fixups.sql

 

# Verify archive log dest size

SQL> archive log list

df -hl /fast_recovery_area

 

# Stop LISTENER

ps -ef | grep tns

lsnrctl stop LISTENER

 

# create Flashback Guaranteed Restore Point <<== optional steps

NO need to enable Flashback Database from 11.2.0.1 onwards

Database MUST be in Archive Log mode

MUST NOT change the compatible parameter to higher version

 

SQL> select flashback_on from v$database;
 
FLASHBACK_ON
------------------

NO                <<== keep it es it is.

 

select name,open_mode,log_mode from v$database;

show parameter compatible  <<== no need to change keep

                                it as it is 12.2.0

show parameter recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /fast_recovery_area 

 

# Starting to create restore point

select * from V$restore_point;

create restore point pre_upgrade guarantee flashback database;

 

# verify restore point is created

col name for a20
col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190

select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;

 

# UPGRADE TASK

select name, open_mode from v$database;

shutdown immediate;

 

# Copy init and password files from 12c to 19c dbs home

# Copy following files from Oracle12c home to the Oracle19c home

cd $ORACLE_HOME/dbs

cp spfileDBNAME.ora orapwDBNAME /oracle19c/product/19.3.0.0/db/dbs

# Copy following files from Oracle12c home to the Oracle19c home

cd $ORACLE_HOME/network/admin/

cp tnsnames.ora sqlnet.ora /oracle19c/product/19.3.0.0/db/network/admin/

 

# Startup DB in Upgrade mode from 19c home

From Oracle 19c home:

sqlplus / as sysdba

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 4.0534E+10 bytes

Fixed Size                  8906984 bytes

Variable Size            6174015488 bytes

Database Buffers         3.4226E+10 bytes

Redo Buffers              125308928 bytes

Database mounted.

Database opened.

SQL> select name,open_mode,cdb,version,status from v$database,v$instance;

NAME      OPEN_MODE            CDB VERSION           STATUS

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

TSTDB     READ WRITE           NO  19.0.0.0.0        OPEN MIGRATE

# Regulair upgrade command

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql

 

OR

cd $ORACLE_HOME/bin/

ls -al dbupgrade

nohup ./dbupgrade &

jobs -l

disown

ps -ef | grep -i catctl.pl

 

# Monitore upgrades Logs

ll $ORACLE_HOME/cfgtoollogs/upgrade*

cd $ORACLE_HOME/bin

more nohup.out

 

# Starup DB from 19c home

From Oracle 12c home:

srvctl remove database -d tstdb

From Oracle 19c home:

srvctl add database -d [db_unique_name] -dbname [DB_NAME] -o $ORACLE_HOME -p '$ORACLE_HOME/dbs/spfileDBNAME.ora'

srvctl config database -d DBNAME

sqlplus / as sysdba
startup;

NOTE: You may need to change the follwoing parameters:

show parameter diagnostic_dest;

show parameter audit_file_dest;

alter system set audit_file_dest='NEW_LOCATION' scope=spfile;

alter system set diagnostic_dest='NEW_LOCATION' scope=spfile;

shutdown immediate;

startup;

SQL> select name,open_mode,cdb,version,status from v$database,v$instance;

NAME      OPEN_MODE            CDB VERSION           STATUS

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

TSTDB     READ WRITE           NO  19.0.0.0.0        OPEN

 
col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999

select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

 

# POST-UPGRADE TASKS WHEN DBUA USING  <<== I am not using those steps 

# Run utlrp.sql

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql 

OR

cd $ORACLE_HOME/rdbms/admin
nohup sqlplus "/ as sysdba" @utlrp.sql > /users/oracle/update19c/utlrp.out 2>&1 &
 
select count(*) from dba_objects where status='INVALID';
select count(*) from dba_objects where status='INVALID' and owner in ('SYS','SYSTEM');

@ORACLE_HOME/rdbms/admin/utlrp.sql
 
select count(*) from dba_objects where status='INVALID';

 

# Run postupgrade_fixups.sql  <<== I am using following steps 

@/users/oracle/update19c/preupgrade/postupgrade_fixups.sql

 

# Upgrade Timezone

For 19c, time zone scripts are included in the binary.

cd /$ORACLE_HOME/rdbms/admin/

ls -lthr utltz*

 

SELECT version FROM v$timezone_file;

@?/rdbms/admin/utltz_countstar.sql

@?/rdbms/admin/utltz_upg_check.sql

@?/rdbms/admin/utltz_upg_apply.sql

 

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        32

SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              32          0

# Run utlusts.sql

Note: utluNNNs.sql is replaced by utlusts.sql in 19c version

Note: In 19c Earlier version utluNNNs.sql is replaced by utlusts.sql

*** Run utlusts.sql as many times as you want, at any time after the upgrade is completed.

*** utlusts.sql reads the view called dba_registry_log and displays the upgrade results for the       database components.

 

@$ORACLE_HOME/admin/utlusts.sql TEXT

 

# Caution: If you use manual upgrade, and you do not run catuppst.sql, then later can your database suffers performance issue.

 

@$ORACLE_HOME/rdbms/admin/catuppst.sql

 

# Re-Run postupgrade_fixups.sql

@/users/oracle/update19c/preupgrade/postupgrade_fixups.sql

exec DBMS_STATS.GATHER_DICTIONARY_STATS (estimate_percent => 100, degree => 8);

exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(NULL);

exec DBMS_STATS.GATHER_SYSTEM_STATS;

# Verify again INVALID OBJECTS

select count(*) from dba_objects where status='INVALID';

 

# Drop Restore point

col name for a20

col GUARANTEE_FLASHBACK_DATABASE for a10
col TIME for a60
set lines 190
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
 
ls -al /fast_recovery_area/TSTDB01/flashback
 
drop restore point PRE_UPGRADE;
 
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
 

ls -al /fast_recovery_area/TSTDB01/flashback

 

#  Set COMPATIBALE parameter value to 19.0.0 on DB

show parameter COMPATIBLE
ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;
shutdown immediate;

startup;

show parameter COMPATIBLE;

# From GRID 19c home on ASM: 

 

 sqlplus / as sysasm

 col COMPATIBILITY form a10

 col DATABASE_COMPATIBILITY form a10

 col NAME form a20

select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

alter diskgroup DATA01 set attribute 'compatible.rdbms'='19.0.0.0.0';

alter diskgroup DATA01 set attribute 'compatible.rdbms'='19.0.0';

Restart DB and ASM:

crsctl stop res -all

crsctl start res -all

# Verify DBA_REGISTRY

col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
set lines 180
set pages 999
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;

# Add TNS Entries in 19c TNS home

cd $ORACLE_HOME/network/admin
cat listener.ora
cat tnsnames.ora
cat sqlnet.ora
 

lsnrctl start LISTENER

# Password File – orapw*

show parameter password

 

# Edit oratab File

 

# Back Up the Database

#You can Install new Oracle Patch. Use following Linkf for the steps:

January Patch 19c 2022

 Need to Fix the BUG:

OPATCHAUTO-72050: System instance creation failed.
OPATCHAUTO-72050: Failed while retrieving system information.

OPATCHAUTO-72050: Please check log file for more details.

# You will see folloing error in the logfile:

Failed to retrieve instance list for database TSTDB

PRCR-1055 : Cluster membership check failed for node

SOLUTION:

crsctl modify resource "ora.tstdb.db"  -attr "HOSTING_MEMBERS=" -unsupported

crsctl stop has

crsctl start has

Let me know if something missing !

Your comments are most valuable and it would help me to come up with better posts.