What is upgradation?
Upgradation is the process of replacing an older version/software with a new version of the product. It transforms the existing Oracle Database Environment into a new Environment. Upgradation can be done within the similar OS or with a different platform.
NOTE: It is important that we should apply the latest PSU or proactive bundle patch, RU, or RUR patch before starting an upgrade.
Why do we need to upgrade?
Upgrading a database generally fixes the bugs, internal errors and other technical issues which can be identified and resolved as soon as possible thereby it reduces the downtime.
You can perform a direct upgrade to the new release from the following releases:
• 11.2.0.4
• 12.1.0.1 and 12.1.0.2
• 12.2.0.1
For example: Here I am converting my db from 12.2.0 (base version) to a 19.0.0 (higher version)
Types of Upgrade:
We can upgrade our DB in any one of the following methods,
1)DBUA (Database Upgrade Assistant)
2)Manual Method
3)Data Pump
4)Transportable Tablespaces
DATABASE UPGRADE ASSISTANT (DBUA) :
DBUA is a GUI method that checks all pre-requisties themselves.it checks for all the pre-requisties such as configuring the listener, recompiling the invaid objects, upgrading the time zone, running the preupgrade_fixups.sql,postupgrade_fixups.sql, configuring the enterprise manager (EM) database express, creating a Guaranteed Restore Point and all the things by itself.DBUA only performs the upgradation process if it satisfies all the above-mentioned things.
In the manual method, we should perform all the pre-requisties things manually.
Overview of steps to Upgrade a Database:
PRE-UPGRADE STEPS:
- Create a new directory called backup, Take a backup of listener.ora,tnsnames.ora,spfile and password files ,then move all the files to the newly created directory called backup.
- Create a new directory called upgrade,Run the preupgrade.jar file which is in the location
(/u01/app/oracle/product/12.2.0.1/db_1/jdk/bin/java -jar /u02/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar),Move the summary of the preupgrade.script to the upgrade directory.
3.Three files named preupgrade.log,preupgrade_fixups.sql,postupgrade_fixups.sql files will be created after running the preupgrade.script.
4.We should execute the below scripts,
Before upgrade – @/u01/preupgrade/preupgrade_fixups.sql
After upgrade – @/u01/preupgrade/postupgrade_fixups.sql
5.Then check the preupgrade.log whether it is completed without any errors.
6. Verify the tablespace sizes for upgrade. (enable the AUTO EXTENT ON and set the maximum size for tablespaces)
7. Gather the dictionary statistics query to get the statistical data of fixed objects. we should gather dictionary statistics before and after the upgrade to maintain better DB performance.Because dictionary tables are modified and created during the upgrade.
8.Purge the recyclebin
9.Refresh all the materialised views before upgrade.
10.Run preupgrade_fixups.sql (/u01/upgrade/preupgrade_fixups.sql) before the upgrade.
11. You should enable the archive log list.
12.Create the flashback restore point before performing upgrade,it is important suppose if dbupgrade fails you can recover the database from this restore point.
13.Set the db_recovery_file_dest_size=10GB
UPGRADE TASK:
After we have completed all our pre-upgrade steps, Now it is time to upgrade our DB using dbua(GUI Method), All the steps will be taken by this.
Once you run the dbua command ,a log will be generated in the location (/u01/app/oracle/cfgtoollogs/dbua/upgrade2020-04-23_10-46-32PM) we can check this log file if any error occurs)
14.We should choose the database which is to be upgraded for 19c.
15. Next choose to Create a Guranteed Restore Point or RMAN backup in case of failure of upgrade. Double-check our restore point
16. Configure a new listener or upgrade the existing listener which is running up from 12.2.20.1 home to target 19c home.
17.Check if all the pre-requisites that we mentioned are correctly mentioned in the GUI.
POST UPGRADE STEPS:
18.Put an entry of current database in the vi /etc/oratab file.
19. Verify the timezone version of the upgraded database. (it should be changed to 32)
20. Then check for any invalid objects in the database after the upgrade.
21.We have to verify the DBA_REGISTRY view,it displays the information about the components loaded in the database.
22. Now we can drop the restore point that we have created already before the upgrade.
23.Update the compatible parameter value, it will be set in the base database version we have to update the upgraded db version in the compatible parameter value.
24. Finally check the listener.ora and tnsnames.ora files it should be updated to an upgraded db version.
Step:1 create a new directory for backup and for the upgrade.
[oracle@orcldbs u01]$ mkdir backup
[oracle@orcldbs u01]$ ls
app backup oradata
[oracle@orcldbs u01]$ mkdir preupgrade
[oracle@orcldbs u01]$ ls
app backup oradata preupgrade
Step:2 Take backup of listener.ora,tnsnames.ora.spfile,password file.
Step:3 Run the pre-upgrade jar script,a log(preupgrade.log will be generated after running the script and send it to the /u01/preupgrade)
[oracle@orcldbs db_1]$ /u02/app/oracle/product/12.2.0.1/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /u01/preupgrade
==================
PREUPGRADE SUMMARY
==================
/u01/preupgrade/preupgrade.log
/u01/preupgrade/preupgrade_fixups.sql
/u01/preupgrade/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/preupgrade/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/preupgrade/postupgrade_fixups.sql
Preupgrade complete: 2021-01-30T01:54:48
Step:4 Log in to the database and run the preupgrade_fixups.sql
[oracle@orcldbs db_1]$ !sq
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 30 01:55:09 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @/u01/preupgrade/preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build
Generated on: 2021-01-30 01:54:46
For Source Database: GERMANY
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. dictionary_stats YES None.
2. pre_fixed_objects YES None.
3. tablespaces_info NO Informational only.
Further action is optional.
4. rman_recovery_version NO Informational only.
Further action is optional.
PL/SQL procedure successfully completed.
Step:5 Gather the dictionary statistics it should be gathered after performing the upgrade.
SQL> SET ECHO ON;
SET SERVEROUTPUT ON;
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
Step:6 Purge the recycle bin.it is mainly used to release the storage space used,since we have enabled the GRP we can purge the recycle bin.
PURGE DBA_RECYCLEBIN;
SQL>DBA_RECYCLEBIN purged.
Step:7 Refreshing the Materialised views update all its indexes.
SQL> declare
list_failures integer(3) :=0;
begin
DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE);
end;
PL/SQL procedure successfully completed.
Step:8 Enable the archive log mode it is mandatory to perform an db upgrade.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/12.2.0.1/db_1/dbs/arch
Oldest online log sequence 1
Current log sequence 2
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2432696320 bytes
Fixed Size 8623592 bytes
Variable Size 654314008 bytes
Database Buffers 1761607680 bytes
Redo Buffers 8151040 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/12.2.0.1/db_1/dbs/arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> show parameter rec
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle string
control_file_record_keep_time integer 7
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
db_recycle_cache_size big integer 0
db_unrecoverable_scn_tracking boolean TRUE
ldap_directory_access string NONE
ldap_directory_sysauth string no
recovery_parallelism integer 0
recyclebin string on
remote_recovery_file_dest string
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
GERMANY MOUNTED ARCHIVELOG
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 0
SQL> alter database open;
Database altered.
Step:9 set the db_recovery_fil_dest_size to 10 gb for recovery purpose.
SQL> alter system set db_recovery_file_dest_size=10G;
System altered.
Step:10 create the GRP (Guarantee Restore Point) ,suppose if any failure occurs during upgradation we can easily go to the before upgrade of db (ie looks like as if our db before upgradation)it is used for recovery purpose.
SQL> create restore point pre_upgrade guarantee flashback database;
Restore point created.
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;
NAME TIME GUA
PRE_UPGRADE 30-JAN-21 02.26.08.000000000 AM YES
Step:11 We have done all the things to be done before the upgrade. Now it’s time to run the DBUA a GUI Utility to perform the upgrade.
11.1 you have to choose the database which you need to upgrade, here I am choosing Germany db for upgrade to 19.0.0 which is in version 12.2.0 and click next.
11.2 second window shows the Pre Upgrade Recommendations and Post Upgrade Recommendations and then click next.
11.3 in this screen click the following checkboxes,
Enable Parallel Upgrade
Recompile Invalid Objects During Post Upgrade
Upgrade Timezone Data
After that you have to browse the locations of preupgrade_fixups.sql,postupgrade_fixups.sql
11.4 In this screen you can specify any of the recovery option for the database in case of upgrade failure.
Here I am using Flashback and Guaranteed Restore Point.
There are methods are also available such that RMAN backup , use latest available full RMAN backup. if you have your own backup and restore strategy you can also use that.
11.5 Select the 12c listener, it should be up and running.
11.6 select the Configure Enterprise Manager (EM) database express check box and click next.
11.7 Finally this window shows the Database Upgrade summary you can verify all your selections that you have choose above are correctly mentioned and proceed further.
11.8 After that Oracle upgrade process starts.
11.9 when the upgradation process is over it give an upgrade results which db is upgraded from which version to which version. You can check here for information.
Step:12 Put an entry of the current database in the vi /etc/oratab file.
[oracle@orcldbs ~]$ cat /etc/oratab | grep -i germany
germany:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@orcldbs ~]$[oracle@orcldbs admin]$ export ORACLE_SID=germany
[oracle@orcldbs admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jan 30 04:09:40 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT name from v$database;
NAME
----------------
GERMANY
Step:13 check the timezone version for the upgraded db (GERMANY) it has changed from 26(12c) to 32(19c).
SQL> SELECT version FROM v$timezone_file;
VERSION
----------------
32
Step:14 Check for any invalid objects in upgraded database.
select count(1) from dba_objects where status='INVALID';
COUNT(1)
------------------
0
Step:15 Verify the dba_registry, it should be verified before and after the upgrade because it display information about the components loaded in the database.
SQL> 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;
COMP_ID COMP_NAME VERSION STATUS
---------- ---------------------------------------- --------------- --------------------------------------------
CATALOG Oracle Database Catalog Views 19.0.0.0.0 VALID
CATPROC Oracle Database Packages and Type 19.0.0.0.0 VALID
JAVAVM JServer JAVA Virtual Machine 19.0.0.0.0 VALID
XML Oracle XDK 19.0.0.0.0 VALID
CATJAVA Oracle Database Java Packages 19.0.0.0.0 VALID
APS OLAP Analytic Workspace 19.0.0.0.0 VALID
RAC Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF
XDB Oracle XML Database 19.0.0.0.0 VALID
OWM Oracle Workspace Manager 19.0.0.0.0 VALID
CONTEXT Oracle Text 19.0.0.0.0 VALID
ORDIM Oracle Multimedia 19.0.0.0.0 VALID
SDO Spatial 19.0.0.0.0 VALID
XOQ Oracle OLAP API 19.0.0.0.0 VALID
OLS Oracle Label Security 19.0.0.0.0 VALID
DV Oracle Database Vault 19.0.0.0.0 VALID
15 rows selected.
Step:16 we have upgraded our db to a higher version, hence we can drop the guaranteed restore point.
SQL> drop restore point pre_upgrade;
Restore point dropped.
Step:17 check the compatible parameter it will be in 12.2.0 compatible value we have to set the compatible value to 19.0.0
SQL> show parameter COMPATIBLE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 12.2.0
noncdb_compatible boolean FALSE
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;
System altered.
SQL> show parameter COMPATIBLE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 19.0.0
noncdb_compatible boolean FALSE
SQL>
Step:18 Verify whether the listener and tns files are changed to the upgraded version.(19.0.0)
[oracle@orcldbs ~]$ ps -ef | grep tns
root 15 2 0 Jan29 ? 00:00:00 [netns]
oracle 2165 1 0 02:35 ? 00:00:00 /u02/app/oracle/product/12.2.0.1/db_1/bin/tnslsnr LISTENER -inherit
oracle 6055 1 0 02:49 ? 00:00:00 /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle 25603 28937 0 04:16 pts/0 00:00:00 grep --color=auto tns
[oracle@orcldbs ~]$
[oracle@orcldbs ~]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 30-JAN-2021 04:17:17
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.26)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 30-JAN-2021 02:49:58
Uptime 0 days 1 hr. 27 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/orcldbs/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.26)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=orcldbs.localdomain)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/trichy/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "oraprod" has 2 instance(s).
Instance "oraprod", status UNKNOWN, has 1 handler(s) for this service...
Instance "oraprod", status READY, has 1 handler(s) for this service...
Service "oraprodXDB" has 1 instance(s).
Instance "oraprod", status READY, has 1 handler(s) for this service...
Service "germany" has 1 instance(s).
Instance "germany", status READY, has 1 handler(s) for this service...
Service "germanyXDB" has 1 instance(s).
Instance "germany", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@orcldbs ~]$
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
