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

ou’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=)(PORT=1521))(ADDRESS=(PROTOCOL=TCPS)(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=)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=)))" -repos_user SYSMAN -repos_pwd $ ./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. https://www.aemcorp.com/managedservices/blog/upgrading-from-oracle-12.2-to-19c-with-a-container/pluggable-configuration http://www.br8dba.com/upgrade-oracle-database-manually-from-12-2-0-1-to-19c/#17 https://oracle-blog.com/upgrade-oracle-database-manually-from-12c-to-19c/