Moving Oracle database to the cloud. 12.2 standalone to 19C RAC PDB.

I see more and more Oracle databases are moving to the public cloud or to a hybrid cloud solution. Depending on the platform, size and used options it could be a different path but the general approach boils down to three main options – Oracle RMAN backup and restore, Oracle Data Guard or Oracle Data Pump with or without transportable tablespaces. Here I want to share our approach for migration from 12.2 standalone database to 19c RAC container as PDB in the Oracle Cloud Infrastructure (OCI). 

Here are the initial conditions and requirements. We had multiple Linux 86×64 12.2 Enterprise edition standalone databases on a file system based storage moving  to Oracle Cloud Extreme Performance 19c with RAC on ASM.

Considering size for the databases and the endian format (little) the most viable option was Oracle DataGuard (DG) . The main question was whether we upgrade our database and convert to a pluggable database (PDB) on-prem and move it to the cloud after or do the migration, upgrade and conversion in one shot using the same downtime window. We’ve chosen the latter.

Here is the high level diagram:

And here is general workflow:

The source database was analyzed by the Oracle preupgrade.jar tool to verify if it was ready to be upgraded to 19c. A few issues were fixed in advance and some reported problems were ignored. There was no universal solution for everybody to tell which warning should be ignored and which one should be taken into consideration and fixed. 

The next step was to prepare the database to use TDE on the target platform. An Oracle encryption wallet and master key was created for the original database. The basic steps are following (all paths and values are arbitrary):

SQL> administer KEY management CREATE keystore '/u01/app/oracle/product/12.2.0/dbhome_1/network/admin' IDENTIFIED BY #SYS_PASSWORD#;
 
keystore altered.
 
SQL> administer KEY management SET keystore OPEN IDENTIFIED BY #SYS_PASSWORD#;
 
keystore altered.
 
SQL> administer KEY management SET KEY IDENTIFIED BY #SYS_PASSWORD# WITH backup;
 
keystore altered.
 
SQL> administer KEY management CREATE auto_login keystore FROM keystore '/u01/app/oracle/product/12.2.0/dbhome_1/network/admin' IDENTIFIED BY #SYS_PASSWORD#;
 
keystore altered.
 
SQL> administer KEY management SET keystore close IDENTIFIED BY #SYS_PASSWORD#;
 
keystore altered.

You can verify the status of your wallet in the v$encryption_wallet view and make sure it is shown as “open”.

After that we created the target 19c container RAC database on DBCS using ASM as storage (you remember you have two options for DBCS). Having the database created we were able to use the first node as a staging area for our DR. We cloned the database software from on-prem to that node and created a dummy database with the same database name but with a different unique name.

[oracle@oracloud01 ~]$./clone.pl ORACLE_HOME=/u01/app/oracle/staging/12.2.0/dbhome_1 ORACLE_HOME_NAME=OraHome12201_dbhome_1 ORACLE_BASE=/u01/app/oracle 
...
 
[oracle@oracloud01 ~]$ dbca -createDatabase -silent -createAsContainerDatabase false -templateName General_Purpose.dbc -gdbName nsppwcb -storageType ASM -diskGroupName +DATAC1 -recoveryAreaDestination +RECOC1 -recoveryAreaSize 10240 -initParams db_name=appdb,db_unique_name=appdbstg,sga_target=2G,sga_max_size=2G
Enter SYS user password:
 
Enter SYSTEM user password:

Having set it up and copied the wallet from source to the staging node we were ready to set up a Data Guard standby where the cloud based standby would be using the cloned home and be encrypted using the master key we created on the source. 

I used the Oracle Zero Downtime Migration tool to establish the replication and prepare the staging. It saved time, effort and avoided human mistakes providing a unified, consistent approach to all the migration. We spent some time troubleshooting different  issues during implementation and a dry run for the first database but it paid off later.  We used the parameter “-pauseafter ZDM_CONFIGURE_DG_SRC” to wait before actual cutover. As you could see the Oracle ZDM can be useful even if it cannot cover the complete migration path. 

Before doing cutover we also moved the standby datafiles to be inside the ASM under ” <Disk group>/<CDB unique name>/<Future PDB GUID>>” using rman backup as copy. I might write another short blog with all the details on how to do that.

We were keeping the Data Guard replication until the cutover time when the real downtime for production started. On the scheduled time we resumed the ZDM job to complete the database switchover and make our staging database primary. The command was simple:

./zdmcli resume job -jobid 3
 
and in the output for "query job" command we got list of completed actions:
 
ZDM_DISCOVER_SRC .............. COMPLETED
ZDM_COPYFILES ................. COMPLETED
ZDM_PREPARE_TGT ............... COMPLETED
ZDM_SETUP_TDE_TGT ............. COMPLETED
ZDM_CLONE_TGT ................. COMPLETED
ZDM_FINALIZE_TGT .............. COMPLETED
ZDM_CONFIGURE_DG_SRC .......... COMPLETED
ZDM_SWITCHOVER_SRC ............ COMPLETED
ZDM_SWITCHOVER_TGT ............ COMPLETED
ZDM_MANIFEST_TO_CLOUD ......... COMPLETED
ZDM_NONCDBTOPDB_PRECHECK ...... COMPLETED
ZDM_NONCDBTOPDB_CONVERSION .... COMPLETED
ZDM_POSTUSERACTIONS ........... COMPLETED
ZDM_POSTUSERACTIONS_TGT ....... COMPLETED
ZDM_CLEANUP_SRC ............... COMPLETED
ZDM_CLEANUP_TGT ............... COMPLETED
[zdmuser@vlxpr1008 ~]$

After the successful switchover we started the staging database in read only mode, exported the encryption key and description file to be plugged to the CDB.

SQL&gt; administer KEY management export encryption KEYS WITH secret "my_secret" TO '/home/oracle/appdb01_export.p12' force keystore IDENTIFIED BY #SYS_PASSWORD;
 
keystore altered.
 
SQL&gt; !ls -l /home/oracle/appdb01_export.p12
-rw-r--r-- 1 oracle asmadmin 2548 Mar  2 19:48 /home/oracle/appdb01_export.p12
 
SQL&gt;
 
 
SQL&gt; BEGIN
  DBMS_PDB.DESCRIBE(
    pdb_descr_file =&gt; '/home/oracle/appdb01.xml');
END;  2    3    4
  5  /
 
PL/SQL PROCEDURE successfully completed.
 
SQL&gt;

It is recommended to verify the target PDB for any violations if it will be plugged into the target CDB using “DBMS_PDB.CHECK_PLUG_COMPATIBILITY” package and the exported xml file.

SQL&gt; SET SERVEROUTPUT ON
DECLARE
  compatible CONSTANT VARCHAR2(3) :=
    CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
           pdb_descr_file =&gt; '/home/oracle/appdb1.xml',
           pdb_name       =&gt; 'appdb1')
    WHEN TRUE THEN 'YES'
    ELSE 'NO'
END;
BEGIN
  DBMS_OUTPUT.PUT_LINE(compatible);
END;SQL&gt;   2    3    4    5    6    7    8    9   10   11
 12  /
NO
 
PL/SQL PROCEDURE successfully completed.
 
SQL&gt; SELECT line,message,STATUS FROM pdb_plug_in_violations WHERE name='APPDB1' ORDER BY TIME,line;

Some violations like version mismatch and the fact the database is not yet container PDB could be ignored. Also keep in mind that some violations are “ERROR” type and should be fixed sooner or later but some are just “WARNING” and might not have any impact.

After that we shut down our staging database and plugged it into the target CDB with “nocopy” option effectively using already encrypted data files and saving time during the cutover downtime. 

SQL&gt; CREATE pluggable DATABASE appdb01 USING '/home/oracle/appdb01.xml' nocopy;
 
Pluggable DATABASE created.
 
SQL&gt; SHOW pdbs
 
    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 CDB01_PDB1			  READ WRITE NO
	 4 APPDB01			  MOUNTED
SQL&gt;

Our database was plugged in to the target container CDB and was ready for upgrade. Before doing the upgrade I imported the master encryption key we used on the source and staging.

SQL&gt; administer KEY management import encryption KEYS WITH secret "my_secret" FROM '/home/oracle/appdb01_export.p12' force keystore IDENTIFIED BY #SYS_PASSWORD WITH backup;
 
keystore altered.
 
SQL&gt;

The next step is to upgrade our new PDB to make it the same version as the container.

SQL&gt;alter session set container=APPDB01;
 
SQL&gt;startup upgrade;
 
 
 
[oracle@oracloud01 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@oracloud01 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -c APPDB01 catupgrd.sql
 
Argument list for [catctl.pl]
For Oracle internal use only A = 0
Run in                       c = APPDB01
Do not run in                C = 0
Input Directory              d = 0
Echo OFF                     e = 1
Simulate                     E = 0
Forced cleanup               F = 0
Log Id                       i = 0
Child Process                I = 0
Log Dir                      l = 0
Priority List Name           L = 0
Upgrade Mode active          M = 0
SQL Process Count            n = 0
SQL PDB Process Count        N = 0
Open Mode Normal             o = 0
Start Phase                  p = 0
End Phase                    P = 0
Reverse Order                r = 0
AutoUpgrade Resume           R = 0
Script                       s = 0
Serial Run                   S = 0
RO User Tablespaces          T = 0
Display Phases               y = 0
Debug catcon.pm              z = 0
Debug catctl.pl              Z = 0
 
catctl.pl VERSION: [19.0.0.0.0]
           STATUS: [Production]
...

After all successful upgrade steps (including fixup.sql script if it is required) our database was almost ready and required only to be converted into PDB.

SQL&gt; ALTER SESSION SET container=appdb01;
 
SESSION altered.
 
SQL&gt; @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
...
SQL&gt; SET trimout ON
SQL&gt; SET trimspool ON
SQL&gt; SET underline "-"
SQL&gt; SET verify OFF
SQL&gt; SET wrap ON
SQL&gt; SET xmloptimizationcheck OFF
SQL&gt;
SQL&gt; SHOW pdbs
 
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 APPDB01                        READ WRITE YES
SQL&gt; SHOW con_name
 
CON_NAME
------------------------------
APPDB01
SQL&gt; shutdown IMMEDIATE
Pluggable DATABASE closed.
SQL&gt; startup
Pluggable DATABASE opened.
SQL&gt;

And you can verify all components in your pluggable database using dba_registry_view:

SQL&gt; col comp_name FOR a50
SQL&gt; col STATUS FOR a20
SQL&gt; SELECT comp_name, version, STATUS FROM dba_registry;
 
COMP_NAME                                          VERSION                        STATUS
-------------------------------------------------- ------------------------------ --------------------
Oracle DATABASE Catalog Views                      19.0.0.0.0                     VALID
Oracle DATABASE Packages AND Types                 19.0.0.0.0                     VALID
JServer JAVA Virtual Machine                       19.0.0.0.0                     VALID
Oracle XDK                                         19.0.0.0.0                     VALID
Oracle DATABASE Java Packages                      19.0.0.0.0                     VALID
OLAP Analytic Workspace                            19.0.0.0.0                     VALID
Oracle REAL Application Clusters                   19.0.0.0.0                     VALID
Oracle XML DATABASE                                19.0.0.0.0                     VALID
Oracle Workspace Manager                           19.0.0.0.0                     VALID
Oracle Text                                        19.0.0.0.0                     VALID
Oracle Multimedia                                  19.0.0.0.0                     VALID
Spatial                                            19.0.0.0.0                     VALID
Oracle OLAP API                                    19.0.0.0.0                     VALID
Oracle Label Security                              19.0.0.0.0                     VALID
Oracle DATABASE Vault                              19.0.0.0.0                     VALID
 
15 ROWS selected.
 
SQL&gt;

The result is a fully migrated and upgraded database after 1 hour 30 min cutover time. Of course you still need to create services, complete acceptance and verification tests, adjust UNDO tablespaces  and do some application or company specific actions but the migration is done. The staging home and leftovers from the database could be removed if they are not going to be used for the next migration to the same container.

I didn’t put all the small details or issues we encountered and should solve during our migrations – it would be too long and totally unreadable. Hopefully I will be able to create a webinar or discuss on one of the virtual events about different pitfalls and unexpected issues you can expect during migration.

This is only one case when you have a 12.2 source and 19c target in the Oracle cloud but for the last several months we did different migrations involving other source and target versions and platforms. Let us know if you need our help and we will be happy to do that. 

2 thoughts on “Moving Oracle database to the cloud. 12.2 standalone to 19C RAC PDB.”

Leave a Reply

Your email address will not be published. Required fields are marked *