Upgrade Options In Oracle 12c Multitenant Database

Yenugula Venkata RaviKumar (Oracle ACE & Oracle Certified Master), Nassyam Basha (Oracle ACE Director, Oracle Certified Master & Author) & KM Krishna Kumar (Oracle Certified Professional)

Introduction

Oracle has introduced multitenant architecture in Oracle Database 12c which has Container databases (CDB) and Pluggable databases (PDB). User data will be stored at PDB and, the CDB will have information about its mapped pluggable database. Users associated with Container database are called Common users who have access to all pluggable databases and Users created in pluggable databases are called Local user and these users have access to only that particular pluggable database (PDB).

Currently two versions of multitenant databases are available: Oracle 12c (12.1.0.1.0) and Oracle 12c (12.1.0.2.0). In the future, non multitenant database will be deprecated. So it is recommended to move databases to a Multitenant architecture model. Moving databases as a Pluggable database in Container Database has advantages related Memory, Background Processes and Disaster Recovery Environments. Multitenant database can be upgraded to higher version in two ways:

1. CDB with its associated PDB can be upgraded to higher version. All PDBs will be upgraded to higher version along with CDB
2. Only particular PDB say HRMS in the above diagram can be upgraded to higher version. PDB will be detached from CDB and attached to higher version CDB. Existing CDB will not get affected and other PDBs can operate as normal.

Before proceeding to upgrade methods, let us see the new upgrade features available with Oracle 12c.

1. Oracle 12c Upgrade New Features

• From Oracle 12c, upgrade process can be executed using parallel process. Till Oracle 11gR2 upgrade will be executed by single process and sometimes it may take more time to complete the upgrade. Also resources may not be utilized properly. In Oracle 12c this drawback has been rectified. We can specify the number of parallel process to perform upgrade. It is purely based on number of CPU available on the server. Using this parallelism upgrade can be completed in lesser time with optimized utilization of resources.

• In Oracle 12c upgrade process is executed in multiple phases. We can execute Catupgrd.sql to do the database upgrade, but this script internally calls many sql files to complete the upgrade. These sql scripts are responsible for oracle server and other db components upgrade. Some of the sql scripts are dependent on each other, some are not. The upgrade activity will first collect all the SQL files and then splits them into multiple phases based on their dependency on other sql files. Due to this segregation in case any phase is failed during upgrade, upgrade process can be repeated from that particular phase. It means we don’t need to re-execute the whole upgrade process.

• Pre-upgrade script in Oracle 12c is enhanced. There are two scripts namely Preupgrd.sql and Utluppkg.sql which are doing Pre-upgrade checks at the source database. During execution both the sql files should be placed at same location. The Preupgrd.sql calls Utluppkg.sql which has the sql procedures to validate source database and recommend necessary changes required for upgrade. Also Preupgrade script creates fix up scripts which will help to achieve the required tasks before and after upgrade.

• In Oracle 12c Database, upgrade parallelism is achieved through perl script catctl.pl. It is available at $ORACLE_HOME/rdbms/admin directory. We should pass catupgrd.sql as an argument to this script. Along with that the number of parallel process required to be executed needs to be specified. It will create parallel process and start executing each phase.

• Upgrade creates logs by default in format of “catupgrd(n).log”. Each parallel process creates its own log file. So the logs will be generated from catupgrd(n-1) to catupgrd0.log, where n is the number of process used to execute sql scripts. For example, in case 4 parallel processes are used for upgrade, then the generated logs are: catupgrd3.log, catupgrd2.log and catupgrd1.log and catupgrd0.log. These logs are created for CDB and every PDB involved in upgrade.

In Oracle 12c we have perl script catcon.pl which will get the sql script as input and executes it at CDB and its associated PDBs. Pre-upgrade and Post-upgrade scripts during upgrade will be given to catcon.pl to execute at CDB and PDBs.

2. Upgrade checklist

Before the database upgrade the generic upgrade checklist should be performed at CDB and PDB level.

1. Cleanup unwanted objects (Empty recycle bin, Remove duplicate objects).
2. Ensure all db components and DB objects are valid at CDB and PDB level.
3. Collect dictionary statistics.
4. Install 12.1.0.2.0 (or) the available higher version at new location and check the integrity of binaries by relinking it.
5. Install latest PSU patch at 12.1.0.2.0 oracle home binary level.

3. CDB and its PDB upgrade (or) Whole CDB upgrade

3.1 Pre-upgrade script execution

Pre-upgrade checks have to be executed in Container database and its connected pluggable database. As a best practice the Preupgrade scripts (preupgrd.sql and utluppkg.sql) can be copied to source oracle home or some other location.

At the source database to execute scripts at CDB and its PDB, invoke catcon.pl

cd $ORACLE_HOME/rdbms/admin (or) Copied location of Preupgrade scripts

$ORACLE_HOME/perl/bin/perl catcon.pl -d <directory location of preupgrade scripts> -l <log location of preupgrade script> -b <preupgrade_log_base_name> preupgrd.sql

Pre-upgrade script output will be generated for CDB and every PDB. Review the output and perform the recommended changes before proceeding to upgrade. Recommendations might be on sys/system invalid objects, Invalid db components, Data dictionary statistics collection.

Once pre-upgrade check list is completed for CDB and all PDBs, Database is ready for upgrade.

Database can be upgraded either through DBUA or Manual method. DBUA is recommended by oracle which will perform the entire upgrade tasks automatically. But it can be used only when source and target oracle home are in the same server and both oracle homes are owned by same user. Though DBUA performs Pre-upgrade checks during upgrade, it is recommended to manually execute it before proceeding to upgrade.

3.2 Database Upgrade Assistant (DBUA)

DBUA has to be invoked from target oracle home. It will be available at $ORACLE_HOME/bin directory. It reads”/etc/oratab” file to know the list of oracle databases running on that server. When we choose database for upgrade,

it will connect to database as sysdba user and execute the Preupgrade checks.

DBUA has option to fix the Preupgrade output warnings automatically. In case the warnings are manually fixed, it also has the option to re-execute Preupgrade step to verify the same. Once Preupgrade tasks are completed

we can start the upgrade. DBUA collects some information from user like number of parallel process required for upgrade, whether change of storage is required in higher version. Upgraded database can move from

file system to ASM storage.

DBUA will upgrade CDB first and then Seed database followed by pluggable databases. We don’t have control to skip any PDB during this upgrade.

In background DBUA splits the upgrade activity into multiple phases. Each phase will have sql scripts. Based on the dependency, sql scripts will be divided into phases.

Details about each phase will be saved at OracleServer.log. At the end of upgrade “Summary report” will be shown which will have the upgrade results.

3.3 Manual Upgrade

Manual upgrade will be taken up when:

• Source and target oracle homes are in different server
• Source and target oracle home are owned by different users.
• DB upgrade has to be executed using Database backup. Source database backup will be restored at target server and upgrade will be done on restored database.
• In case DBUA is failed and we cannot re-run DBUA again on failed upgrade.
• We would like to have more control on upgrade process

Target oracle home preparation:

Install Oracle 12c (12.1.0.2.0) (OR) available higher version at target server. Select the software only installation option and create password file for CDB which is going to be upgraded in this oracle home.

Copy source database datafiles and pfile to target oracle home in case source and target servers are different and start the database in upgrade mode. If we have source database backup, restore and recover it at target server and open the database in upgrade mode.

Invoke catupgrd.sql through perl utility catctl.pl to achieve parallelism.

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -l <log location for upgrade> catupgrd.sql

Catupgrd.sql will be executed first in CDB, then PDB seed and followed by all PDBs. At each database upgrade will be executed in multiple phases. Logs by default will be stored at location from where catctl.pl is invoked. We should not exclude any PDB in this method.

We can see the status of each phase in detail at OracleServer.log. It has details about phase number, method of execution (serial or parallel), sql scripts executed in that phase and time spent for that phase. Restart phase will reconnect to database. Due to this reconnect, dependencies between phases are achieved and upgrade can start from any required phase. For example: In case phase 9 has failed during upgrade, then re-execution can start from 9th phase.

OracleServer.log will be like below with Files and Time Information:

Serial Phase #: 0 Files: 1 Time: 369s
Serial Phase #: 1 Files: 3 Time: 80s
Restart Phase #: 2 Files: 1 Time: 9s
Parallel Phase #: 3 Files: 18 Time: 24s
Restart Phase #: 4 Files: 1 Time: 1s
Serial Phase #: 5 Files: 5 Time: 34s
Serial Phase #: 6 Files: 1 Time: 21s
Serial Phase #: 7 Files: 2 Time: 19s
Restart Phase #:8 Files: 1 Time: 1s
Parallel Phase #: 9 Files: 61 Time: 111s
Restart Phase #:10 Files: 1 Time: 1s
Serial Phase #:11 Files: 1 Time: 23s
Restart Phase #:12 Files: 1 Time: 0s
Parallel Phase #:13 Files: 199 Time: 91s
Restart Phase #:14 Files: 1 Time: 1s

catupgrade scripts execution will be stored in logs: catupgrd(n-1) to catupgrd0.log where ‘n’ denotes number of parallel process involved in this upgrade.

OracleServer.log and catupgrd(n) logs will be generated for CDB and every PDB

Execute post upgrade script catuppst.sql on CDB and all PDBs using catcon.pl script

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catcon.pl -d <directory location of upgrade scripts> -l <log location of postupgrade script> -b <post upgrade_log_base_name> catuppst.sql

Verify dba_registry to know the DB component status.

4. Pluggable Database (PDB) Upgrade

Instead of upgrading whole Container Database (CDB) and all Pluggable Databases (PDBs), we may have requirement to upgrade only particular PDBs. In that case we don’t need to execute pre-upgrade or upgrade scripts to CDB and other PDBs.

Here the whole upgrade process will work on only selected PDBs. Other PDB will function normally without any downtime.

4.1 Pre-upgrade script execution

Execute pre-upgrade only to that particular Pluggable Database (PDB) which are chosen for upgrade.

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catcon.pl –c <pdb name> -d <directory location of preupgrade scripts> -l <log location of preupgrade script> -b <preupgrade_log_base_name> preupgrd.sql

(OR)

You can connect to particular PDB using sqlplus and execute Preupgrd.sql

SQL> alter system set container =<PDB Name>

SQL> @?/rdbms/admin/preupgrd.sql

Once Pre-upgrade checklists are performed, Pluggable Database (PDB) is read for upgrade. Since only PDB is getting upgraded, we need to have CDB at target location which is at higher version.

PDB will be detached from source CDB and it will be attached with higher version of CDB. Once Pluggable Database (PDB) is attached it has to be open in upgrade mode and then upgrade scripts will be executed on that Pluggable Database (PDB).

4.2 Detach Pluggable Database (PDB) from source oracle home

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> connect / as sysdba
SQL> alter session set container=CDB$ROOT;
SQL> alter pluggable database <PDB_Name> close;
SQL> alter pluggable database <PDB_Name> unplug into '/stage/<PDB_Name>.xml';
SQL> exit;

4.3 Attach Pluggable Database (PDB) to target oracle home

In case target location is a different server then copy below files to target server.

1. PDB database files
2. /stage/<PDB_Name>.xml
3. postupgrade_fixups.sql generated by Preupgrade scripts

Connect to higher version Container Database (CDB)

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog

SQL> connect / as sysdba

SQL> alter session set container=CDB$ROOT;

SQL> create pluggable database <PDB_Name> using '/stage/<PDB_Name>.xml' file_name_convert=('<Old Location>', '<New location>');

Note: file_name_convert is required when directory structure is different between source and target server

4.4 Upgrade Steps

Open the Pluggable Database (PDB) in upgrade mode

SQL> alter pluggable database <pdb name> open upgrade;

SQL> exit;

Execute upgrade scripts

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl –c <pdb name> -l <log location for upgrade> catupgrd.sql

-c option is mandatory to specify the PDB name

To upgrade multiple Pluggable Database (PDB) at the same time, detach those PDBs from container database and attach it to higher version of container database and execute upgrade scripts through catclt.pl command

$ORACLE_HOME/perl/bin/perl catctl.pl -c "<PDB_Name1>, <PDB_Name2>" -l <log location for upgrade> catupgrd.sql

Run post upgrade script catuppst.sql as follows:

$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -c <PDB_Name> -b catuppst -d '''.''' catuppst.sql

Run catcon.pl to invoke utlrp.sql to recompile any remaining stored PL/SQL and Java code.

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

Open the Pluggable Database (pdb) in normal mode.

4.5 Post upgrade fix-up scripts

Followed by a startup and upgraded Pluggable Database (PDB) must now be opened for recompilation

SQL> startup pfile=<location of pfile>;

SQL> alter pluggable database <PDB_Name> open;

Execute the postupgrade_fixups.sql:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d <post upgrade script location> -l <Log location> -c <PDB_Name> -b postupgrade_fixups postupgrade_fixups.sql

(OR)

SQL> alter session set container=<PDB_Name>;

SQL> @<Post_upgrade_script_location>/postupgrade_fixups.sql

Summary:

This article has explored options available to upgrade Oracle Multitenant databases directly to higher version. We had discussed about Container Database (CDB) upgrade and individual Pluggable Database (PDB) upgrade.

About the Authors

Nassyam Basha @"CKPT"

Nassyam Basha is a Database Administrator, Oracle ACE Director. He holds a Master's Degree in Computer Applications from the University of Madras. He started learning with dBase,FoxPro, and has participated in several projects with FoxPro before he started working with Oracle database technologies in 2006. He is an Oracle 11g Certified Master, Exadata Implementation Specialist and has solid experience in Oracle technologies including Data Guard, RMAN, RAC, and Exadata. He actively participates in Oracle-related forums such as OTN and also contributes to many Oracle-Lists. He maintains an Oracle technology-related blog, and he co-authored the book "Oracle Data Guard 11gR2 administration beginners guide" for PACKT publications. He is a frequent contributor to OTN in many languages and is a moderator on the Oracle Community Platform. He is speaker @AIOUG, OTN , IOUG, SANGAM and he is co-founder of oraworld team( www.oraworld-team.com ). Learn more from his profile at @"CKPT"

YV Ravi Kumar @"LaserSoft"

YV RaviKumar is an Oracle ACE and Oracle Certified Master (OCM) with 17 years of experience in Banking, Financial Services and Insurance (BFSI) vertical and played various roles like Senior Database Architect and Production DBA. He is also OCP in Oracle 8i, 9i, 10g,11g &12c and Certified in Golden Gate, RAC, Performance Tuning & Oracle Exadata. He continuously motivates many DBAs and helps the Oracle Community by publishing his tips/ideas/suggestions/solutions in his blog. He has written 35 OTN articles on Oracle Exadata, Oracle RAC and Oracle Golden Gate for OTN-Spanish, OTN-Portuguese and OTN-English and 17 articles for TOAD World, 2 Articles for UKOUG, 3 Articles for OTech Magazine and 2 Articles for Redgate. He is a member of the All India Oracle User Group (AIOUG) and a frequent Oracle speaker in @OTN, AIOUG, Sangam and IOUG. He designed, architected and implemented Core Banking System (CBS) Database for Central Banks of two countries - India & Mahe, Seychelles. He is a Co-Founder of OraWorld (www.oraworld.com). Learn more from his profile at @"LaserSoft"

KM Krishna Kumar

KM Krishna Kumar is a Database Administrator. He holds master's degree in Business administration from Anna university. He started working with oracle technologies from 2004 as DBA and worked with oracle corporation for 5+ years. He had involved in Beta testings of Database upgrade and written knowledge articles. He is an Oracle 11g and 12c Certified professional and having good knowledge in Oracle technologies such as Installation, patching, DB upgrade using varios methods,Data Guard, RMAN, RAC. He has delivered presentations to customers through various channels. He actively participates in Oracle-related forums such as OTN communities.