AEM Blue

Oracle database upgrades can seem like a daunting task if you are like most DBAs who only upgrade every few years.

You’ve come to the right place to learn the steps and potential pitfalls.

What if you have a stand-alone (non-Oracle RAC) database and want to switch from a non-container database to a container/pluggable configuration? I know this sounds like a lot more work, adding to an already stressful situation. However, please know it only requires a few extra steps and results in improved resource utilization.

Starting with Oracle 19c, you can have up to three pluggable databases per container. This means you can take advantage of multitenant-type improvements in memory and processor sharing within a single container without having to fork over extra license fees. If you have more than one instance per database server, it becomes a no-brainer.

This blog post provides step-by-step instructions for upgrading from Oracle 12.2 to 19c and converting from a non-container to a container/pluggable configuration. I’ll even throw in the steps for upgrading an OEM repository to this configuration at no extra charge.

Upgrading from Oracle 12.2 to 19c

First, download the latest Oracle 19c release and associated quarterly patches. In my case, I decided to try out using the rpm method for installing the Oracle software.

Let’s assume you are running Red Hat Linux with 64bit OS. To do this, two rpm files are required: oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm and oracle-database-ee-19c-1.0-1.x86_64.rpm.

Please note that this will install the software under the /opt mount point so make sure you have at least 6.5 Gb of free space. (I would go with 20 Gb if you don’t like to worry about running out of disk space any time soon.)

1. To start, you’ll want to install the prerequisites as root:

# curl -o oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/ getPackage/oracle-database-preinstall-19c-1.0-1.el7.x86_64.rpm
# yum -y localinstall oracle-databasepreinstall-19c-1.0-1.el7.x86_64.rpm

2. Download the .rpm from Oracle and install the Oracle software, as root:

# yum -y localinstall oracle-database-ee-19c-1.0-1.x86_64.rpm

3. Go ahead and apply the latest OJVM and Oracle Quarterly patch prior to creating any databases. This will save you a couple steps since you won’t have any databases created that require applying post patch steps. (Working smarter, not harder.)

Next, you need to create and configure a database. There is a new way of doing this which is very simple and does not require X Windows to be running. This is more of a silent mode option. Before you do this, here are a couple steps I would take to customize your database creation.

4. Edit the /etc/init.d/oracledb_ORCLCDB-19c file to specify the name of the container/pluggable it will create automatically for you. Please see below for an example with the database being called MAINCDB and a pluggable called MAINPDB1:

# Setting the required environment variables
export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1

export ORACLE_VERSION=19c
export ORACLE_SID=MAINCDB
export TEMPLATE_NAME=General_Purpose.dbc
export CHARSET=AL32UTF8
export PDB_NAME=MAINPDB1
export LISTENER_NAME=LISTENER
export NUMBER_OF_PDBS=1
export CREATE_AS_CDB=true

5. (Optional but please read): Another edit I would highly recommend is to change the oracledb_ORCLCDB-19c further down by adding the last argument (in bold). In one environment I did not have to make this change and the database created with no issue. For another environment, when I tried to create the database it errored out almost immediately with [DBT-50000] and [DBT-50001] messages. Below is the fix:

$SU -s /bin/bash $ORACLE_OWNER -c "$DBCA -silent -createDatabase -gdbName $ORACLE_SID -templateName $TEMPLATE_NAME -characterSet $CHARSET -createAsContainerDatabase $CREATE_AS_CDB -numberOfPDBs $NUMBER_OF_PDBS -pdbName $PDB_NAME -createListener $LISTENER_NAME:$LISTENER_PORT -datafileDestination $ORACLE_DATA_LOCATION -sid $ORACLE_SID -autoGeneratePasswords -emConfiguration DBEXPRESS -emExpressPort $EM_EXPRESS_PORT -J-Doracle.assistants.dbca.validate.ConfigurationParams=false"

6. You will now need to create a config file which matches the name of the container. If you want to edit the default location of the database datafiles, you would need to change the location within the new .conf file created below. To setup the configuration for the new container database, perform the below commands:

# cd /etc/sysconfig
# cp oracledb_ORCLCDB-19c.conf oracledb_MAINCDB-19c.conf

7. You are ready to create the new container/pluggable. Please ensure the Oracle 12.2 listener is down prior to running this command. Otherwise, the database creation will error due to port 1521 being in use. Below includes all the commands required for ensuring the database listener for 12.2 (test database) is in the proper state along with the command to create the database (in bold). You will see a progress percentage periodically show up. It will take a few minutes to run so be patient:

$ su – oracle
$. oraenv
test
$ lsnrctl stop
$ cd /opt/oracle/product/19c/dbhome_1/network/admin
$ mv listener.ora listener.ora.bkup
$ su -
# /etc/init.d/oracledb_ORCLCDB-19c configure
# exit
$ lsnrctl stop
$ cp listener.ora.bkup listener.ora
$ lsnrctl start

8. Run a pre-check on the 12.2 database. Make sure your ORACLE_HOME (12.2) /ORACLE_SID are pointed to the appropriate locations for the database you want to upgrade. If so, run the below command:

$ . oraenv
test

$ $ORACLE_HOME/jdk/bin/java -jar /opt/oracle/product/19c/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT

You will be provided an output of anything that may be an issue. In addition, scripts for both pre-upgrade and post-upgrade will be produced. You will need to run the “pre” script now and record the location for the “post” script so you can run that after.

If your database happens to be on a VM, now is a great time to do a snapshot/backup, just in case.

Once the “pre” script has been run, you’ll need to capture your local listener value for later use and shut down the database from Oracle 12.2 for the last time ever (hopefully).

$. oraenv
test
$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Thu May 28 10:16:30 2020

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter local_listener

SQL> shutdown immediate;
SQL> exit;

9. (Optional but please read): I had issues with performing the upgrade following the official Oracle instructions by running the command from $ORACLE_HOME/rdbms/admin. To get around this issue, I copied files (temporarily) from this directory to $ORACLE_HOME. Below are the steps to ensure you don’t hit the issue I faced (simple work around) which you can remove these files after you are done with all of the upgrades. No .sql/.plb file should exist under $ORACLE_HOME by default so you won’t break anything as long as you are under the 19c $ORACLE_HOME when you remove the files when you are done:

$ cd /opt/oracle/product/19c/dbhome_1/rdbms/admin
$ cp *.sql ../..
$ cp *.plb ../..

10. You’ll want to copy the orapwd and parameter files from the 12.2 $ORACLE_HOME/dbs and $ORACLE_HOME/network/admin to the 19c equivalent. To do so, perform the following:

$ cp $ORACLE_HOME/dbs/spfile* /opt/oracle/product/19c/dbhome_1/dbs
$ cp $ORACLE_HOME/dbs/orapw* /opt/oracle/product/19c/dbhome_1/dbs

11. You are now ready for the upgrade. To do this manually, shutdown your database (test) from the 12.2 location (see step 8) and startup with 19c as seen below:

$. oraenv
MAINCDB
$ export ORACLE_SID=test
$ cd /opt/oracle/product/19c/dbhome_1/rdbms/admin
$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 29 10:16:30 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> startup upgrade;
SQL> exit;

Run the upgrade command from the Unix prompt as oracle. This will do all the heavy lifting and take a while to run so feel free to just check on it periodically as you work on other activities:

$ dbupgrade -d /opt/oracle/product/19c/dbhome_1

12. Once the upgrade completes, you’ll want to edit the /etc/oratab to reflect the new ORACLE_HOME so that . oraenv command works as expected. See below:

$ vi /etc/oratab
test:/opt/oracle/product/19c/dbhome_1:N
MAINCDB:/opt/oracle/product/19c/dbhome_1:N

13. You’ll now want to start up the database and run the post upgrade commands. Please note the sql (below) was generated during the pre-check. Below are the commands to run:

$. oraenv
test
$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 29 10:16:30 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> startup;
SQL> exit

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

$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 27 07:35:31 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> @/u01/oracle/cfgtoollogs/test/preupgrade/postupgrade_fixups.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql

14. You most likely need to adjust your time zone for 19c. Below are the four commands to complete this task:

SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstats.sql
SQL> @$ORACLE_HOME/rdbms/admin/utltz_countstar.sql
SQL> @$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
SQL> @?/rdbms/admin/utltz_upg_apply.sql

Congratulations, you have now officially upgraded to 19c!

15. (Optional but please read) If you have Transparent Data Encryption (TDE) in place on the stand-alone, you will need to perform the following steps:

Update the $ORACLE_HOME/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =
  (METHOD = FILE)
  (METHOD_DATA =
   (DIRECTORY = /u01/oracle/admin/$ORACLE_SID/wallet)
  )
 )

Create the wallet directory and set the environmental variables:

$ export ORACLE_SID=MAINCDB
$ export ORACLE_UNQNAME=MAINCDB
$ cd /u01/oracle/admin
$ mkdir MAINCDB
$ cd MAINCDB
$ mkdir wallet

In sqlplus as sysdba:

SQL> administer key management create keystore '/u01/oracle/admin/MAINCDB/wallet' identified by password123; 
SQL> administer key management set keystore open identified by password123 container=all;
SQL> administer key management set key identified by password123 with backup container=all;
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/oracle/admin/MAINCDB/wallet' IDENTIFIED by password123;
SQL> exit

$ export ORACLE_SID=test
$ export ORACLE_UNQNAME=test

This example assumes the wallet already exists for the test database and is using the same password (password123) as what was previously set for MAINCDB. In sqlplus:

SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET password123 TO '/u01/oracle/admin/test/wallet/test_tdekey.exp' IDENTIFIED BY password123;

Converting the stand-alone 19c database into a pluggable database

16. Convert the stand-alone 19c database into a pluggable database with the below steps:

$ export ORACLE_SID=test
$ export ORACLE_UNQNAME=test

$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 29 10:16:30 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> startup mount;
SQL> alter database open read only;
SQL> BEGIN
 DBMS_PDB.DESCRIBE(
 pdb_descr_file => '/u01/oracle/test.xml');
END;
/

SQL> shutdown immediate;
SQL> exit

 

$ . oraenv
MAINCDB
$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 29 10:16:30 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> set serveroutput on

SQL> DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/u01/oracle/test.xml', pdb_name => 'test') WHEN TRUE THEN 'YES' ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('Is the future PDB compatible? ==> ' || compatible);
END;
/

It should show the pluggable is ready to be converted. Now, it is time to convert test into a PDB.

SQL> create pluggable database test using '/u01/oracle/test.xml' nocopy tempfile reuse;
SQL> alter session set container=test;
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

The script runs for a while so just be patient.

17. To complete the TDE setup, import the encryption keys you previously exported:

SQL> alter session set container=test;
administer key management import encryption keys with secret password123 from '/u01/oracle/admin/test/wallet/test_tdekey.exp' force keystore identified by password123 with backup;

For any services previously associated with the stand alone database, start these up to avoid experiencing the dreaded TNS-12514 error.

SQL> exec dbms_service.start_service('test.world');
SQL> shutdown
SQL> startup
SQL> exit

$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 29 10:16:30 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle. All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> ALTER PLUGGABLE DATABASE test SAVE STATE;

The above command will cause the pluggable to come up whenever the container starts up automatically. Be sure your local_listener parameter value is set to what test was previously set to with Oracle 12.2 (see step 8) to avoid connection issues. Below is just an example.

SQL> ALTER PLUGGABLE DATABASE test SAVE STATE;
SQL> alter system set local_listener=
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC))(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname>)(PORT=1521))(ADDRESS=(PROTOCOL=TCPS)(HOST=<host>)(PORT=1522)));

Upgrading the OEM repository and converting it into a pluggable

18. (Optional but please read) This is the moment you’ve been waiting for. If you are upgrading the OEM repository and converting it into a pluggable, please follow the below steps:

From the OMS Server:

$ cd $ORACLE_HOME/bin
$ ./emctl stop oms -all
$ ./emctl start oms -admin_only

$ ./emctl config oms -store_repos_details -repos_conndesc "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=<db hostname>)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=<service name>)))" -repos_user SYSMAN -repos_pwd <password>

$ ./emctl stop oms -all
$ ./emctl start oms

If you did everything correctly, the OMS should come up with no issue and you are done.

This concludes the steps for upgrading to Oracle 19c, converting to a container/pluggable configuration, and updating the OEM repository to reflect this new configuration if required.

As you can probably tell, there are a few gotchas along the way which have been highlighted. Regardless, it’s a repeatable process which I would encourage everyone to look at so you can be on the latest, supported software version.

AEM can help your organization implement technologies such as upgrading to Oracle 19c. Please reach out to learn more about our service offerings.

RECOMMENDED BLOG POSTS

Installing Oracle Access Management 12.2.1.4

Oracle Access Management (OAM) is Oracle’s solution for user management. The software is part of the Fusion Middleware Infrastructure family and can be integrated with both Oracle and non-Oracle software. OAM provides an enterprise-level platform that delivers user authentication and single sign-on (SSO) capabilities in a simple web-based console. Access Manager SSO allows for entities to access multiple applications after authentication and reduces the need for multiple logins. 

5 Lessons for Finding the Right Test Automation Software

This is the second blog post in a two-part series examining test automation software. This blog post focuses on lessons learned for finding the right software product for your organization. We recommend you also read our first post, which is dedicated to understanding the process for moving from manual to automated testing.

5 Keys to Successful Test Automation

This blog post is the first in a two-part series on website testing automation that can help your organization better understand how to maximize the effectiveness of your tests and find the right tools to meet your needs. Below we offer insights that can help your organization improve its testing automation process. Our follow-on blog post will help your organization understand the different software tools available to begin automating your tests.