- ANKUSH THAVALI
- 28 Nov, 2023
- 0 Comments
- 4 Mins Read
Database Upgradation Steps For RAC
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 userSQL> 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 & 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 sysdbaStep 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