Database Upgradation Steps For RAC

ANKUSH THAVALI
28 Nov, 2023
0 Comments
4 Mins Read

Step 1: Create a new 19c oracle database home and provide permission for both nodes

mkdir -p /u01/app/oracle/product/19C/dbhome
chown -R oracle:oinstall /u01
chmod -R 775 /u01

Step 2: We download the oracle database 19c software and uploaded zip file into the new 19c home location (one node of RAC)

cd  /u01/app/oracle/product/19C/dbhome

Copy Oracle 19C Software

Unzip it

Step 3: Install oracle 19c software only in one node of RAC environment for upgradation

Cd $ORACLE_HOME
./runInstaller            ------------------------- Install software only 1st node only

Setup only software

Step 4: Check the Invalid Objects if there have any invalid objects compile them before the upgrade.

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 5 16:12:57 2023
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

$ SQL> select count(*) from dba_objects where status='INVALID';
  COUNT(*)
----------
         0

Step 5: Create the required directory where store the upgradation script

mkdir -p /home/oracle/preupgrade	-- 1st Node only

chown -R oracle:oinstall /home/oracle/preupgrade

chmod -R 775 /u01/spool/preupgrade

Step 6: Execute the Pre-upgrade script in 1st node only

/u01/app/oracle/product/12C/dbhome/jdk/bin/java -jar /u01/app/oracle/product/19C/dbhome/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade/

Note: Carefully view the pre-upgrade log and take care of the issues.

Step 7: GATHER DICTIONARY STATS

Before the upgrade process, gather stats. One of the recommendations is to export the stats as well.

sqlplus / as sysdba

SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.

Step 8: Purge Recycle Bin

Before the upgrade process, empty the recycle bin.

 SQL> purge dba_recyclebin;
DBA Recyclebin purged.

Step 9: RUN PREUPGRADE FIXUP SQL

SQL> @/home/oracle/preupgrade/preupgrade_fixups.sql

Step 10: Verify Archive Destination Size

Ensure you have enough free space in db_recovery_file_dest and make changes to the parameter db_recovery_file_dest_size if needed increase size.

SQL> archive log list;
SQL> show parameter db_recovery_file_dest

Step 11: Stop Database Listener and Check the Flashback Status

# srvctl stop LISTENER – run from grid user

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

Enable flashback database using the following procedure 

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=20G;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA’;
SQL> alter database flashback on;

Step 12: Create a restore point before the upgrade issue.

SQL> create restore point PRE_UPGRADE guarantee flashback database;

Step 13: Oracle database upgrade for RAC environment.

Shutdown database of both nodes
copy spfile/create pfile and password file to new 19c oracle home.
COPY [SPFILE/PASSWORD] FILES FROM 12C TO 19C ORACLE HOME.
create pfile and store it in 19c home

sqlplus / as sysdba

create pfile=’ /u01/app/oracle/product/19c/dbhome/dbs/initTESTDB1.ora’ from spfile;

NOTE: In pfile edit one parameter to cluster_database=False

Shutdown database &amp; listener
srvctl stop LISTENER -- from grid user
srvctl stop database -d TESTDB -o immediate -- from oracle user

Step 14: Start database in upgrade from 19c home

export ORACLE_HOME=/u01/app/oracle/product/19C/dbhome

$ which sqlplus
/u01/app/oracle/product/19c/db_1/bin/sqlplus
sqlplus / as sysdba
SQL>startup upgrade

SQL> select status from v$instance;

Status
---------------------------------------
OPEN MIGRATE

Now run dbupgrade from 19c home

cd $ORACLE_HOME/bin

$ ./dbupgrade		-- 1st node only & 2nd node will be down state

Step 15: Post-Upgrade Activities

After the upgrade start the database from 19c home sqlplus / as sysdba

Step 16: Post upgrade run utlrp.sql to compile an invalid object

cd $ORACLE_HOME/rdbms/admin

SQL> @utlrp.sql

Step 17: Run the post-upgrade script

cd /home/oracle/preupgrade
SQL> @postupgrade_fixups.sql

Step 18: Upgrade timezone

SQL> select version from v$timezone_file;

   VERSION
---------------
        26

cd $ORACLE_HOME/rdbms/admin

SQL> @utltz_upg_check.sql
When it executed without any error run the below sql script


SQL> @utltz_upg_apply.sql

Now Run the script utlusts.sql
Now run the script catuppst.sql

SQL> @catuppst.sql

Rerun post upgrade fixup sql to check where exit any error or not

SQL> @postupgrade_fixups.sql


Now check the invalid object 

SQL> select count(*) from dba_objects where status='INVALID';
  COUNT(*)
----------------
         0

Upgradation is completed successfully so delete the restore point that created before upgrade

Set compatible parameter

SQL> show parameter compatible
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0

SQL> alter system set compatible='19.0.0' scope=spfilesid='*';


 SQL>col COMP_ID for a10
col COMP_NAME for a40
col VERSION for a15
	set lines 200 pages 100
	select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;




Now set a new database home for the second node of RAC and startup database for the second node

After completing the migration upgrade oracle home for RAC

Oracle database new home upgradation in oracle RAC infrastructure using the following command

srvctlconfig database -d TESTDB	-- Show the database configuration

su – oracle
cd $ORACLE_HOME/bin

./srvctl upgrade database -d databasename -oraclehome 19c_new_home

./srvctl upgrade database -d TESTDB -oraclehome/u01/app/oracle/product/19C/dbhome

./srvctl status database -d TESTDB			-- Check status of database

./srvctl stop database -d TESTDB -o immediate 	-- stop database of the cluster
./srvctl start database -d TESTDB			        -- Start database of cluster

The database has been successfully upgraded to 19c.


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

NAME      OPEN_MODE            VERSION
--------- -------------------- -----------------
TESTDB    READ WRITE           19.0.0.0.0