UPGRADE ORACLE DATABASE FROM ORACLE 12.2.0.1 TO 19C USING DBUA

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:

  1.  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.
  2. 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

 

 

19C RMAN CONCEPTS

rman

19c RMAN CONCEPTS:

RECOVER DATAFILE WITHOUT RMAN BACKUP
RECOVER THROUGH RESETLOGS
BLOCK CHANGE TRACKING
RECOVER LOSS OF ALL CONTROL FILE USING AUTO BACKUP
RECOVER LOSS OF ALL ONLINE REDO LOGS 
POINT IN TIME RECOVERY 
RMAN-06183: datafile or datafile copy xyz.dbf larger than MAXSETSIZE
RMAN Database Restore ASM
DATABASE INCARNATION USING RMAN
Restore Tablespace using RMAN
RESTORE SPFILE USING RMAN
RMAN backup Full Database
RMAN Backup Tablespace
RMAN Backup Particular Datafile
RMAN Backup Spfile
RMAN Backup Current Control file
RMAN Backup Archive log Until Sequence
RMAN Backup Archive log Between Sequence
RMAN Backup Archive log Between SCN
RMAN Backup Archive log Until SCN
RMAN Backup Database Plus Archive log
RMAN Backup Database Includes A Control file
RMAN Backup Archive log and All Delete Input
RMAN Backup Archive log All and Skip Inaccessible
LEVEL 0 and LEVEL 1 Backup And Recovery using RMAN
CROSSCHECK BACKUPS Using RMAN
RESTORE CONTROL FILE USING RMAN
Backup-based Cloning of a database using RMAN
RECOVERY CATALOG DATABASE IN RMAN
RMAN ORA ERRORS
DBVERIFY
END-OF-FILE ERROR
LEVEL 0 INCREMENTAL BACKUP
point-in-time recovery using RMAN in 19c
Recover a loss of all online redo log files

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