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> 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> !ls -l /home/oracle/appdb01_export.p12 -rw-r--r-- 1 oracle asmadmin 2548 Mar 2 19:48 /home/oracle/appdb01_export.p12 SQL> SQL> BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => '/home/oracle/appdb01.xml'); END; 2 3 4 5 / PL/SQL PROCEDURE successfully completed. SQL>
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> SET SERVEROUTPUT ON DECLARE compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => '/home/oracle/appdb1.xml', pdb_name => 'appdb1') WHEN TRUE THEN 'YES' ELSE 'NO' END; BEGIN DBMS_OUTPUT.PUT_LINE(compatible); END;SQL> 2 3 4 5 6 7 8 9 10 11 12 / NO PL/SQL PROCEDURE successfully completed. SQL> 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> CREATE pluggable DATABASE appdb01 USING '/home/oracle/appdb01.xml' nocopy; Pluggable DATABASE created. SQL> 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>
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> 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>
The next step is to upgrade our new PDB to make it the same version as the container.
SQL>alter session set container=APPDB01; SQL>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: [18.104.22.168.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> ALTER SESSION SET container=appdb01; SESSION altered. SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql ... SQL> SET trimout ON SQL> SET trimspool ON SQL> SET underline "-" SQL> SET verify OFF SQL> SET wrap ON SQL> SET xmloptimizationcheck OFF SQL> SQL> SHOW pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 4 APPDB01 READ WRITE YES SQL> SHOW con_name CON_NAME ------------------------------ APPDB01 SQL> shutdown IMMEDIATE Pluggable DATABASE closed. SQL> startup Pluggable DATABASE opened. SQL>
And you can verify all components in your pluggable database using dba_registry_view:
SQL> col comp_name FOR a50 SQL> col STATUS FOR a20 SQL> SELECT comp_name, version, STATUS FROM dba_registry; COMP_NAME VERSION STATUS -------------------------------------------------- ------------------------------ -------------------- Oracle DATABASE Catalog Views 22.214.171.124.0 VALID Oracle DATABASE Packages AND Types 126.96.36.199.0 VALID JServer JAVA Virtual Machine 188.8.131.52.0 VALID Oracle XDK 184.108.40.206.0 VALID Oracle DATABASE Java Packages 220.127.116.11.0 VALID OLAP Analytic Workspace 18.104.22.168.0 VALID Oracle REAL Application Clusters 22.214.171.124.0 VALID Oracle XML DATABASE 126.96.36.199.0 VALID Oracle Workspace Manager 188.8.131.52.0 VALID Oracle Text 184.108.40.206.0 VALID Oracle Multimedia 220.127.116.11.0 VALID Spatial 18.104.22.168.0 VALID Oracle OLAP API 22.214.171.124.0 VALID Oracle Label Security 126.96.36.199.0 VALID Oracle DATABASE Vault 188.8.131.52.0 VALID 15 ROWS selected. SQL>
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.