Introduction
Oracle Databases on Oracle Cloud are created using the Multitenant architecture. All databases are encrypted using Transparent Data Encryption (TDE) by default. All databases on Exadata, Bare Metal machines, and RAC on Virtual Machines use ASM storage management. For Single Instance databases on Virtual Machine DB systems you have the choice between Logical Volume Manager (LVM) and Grid Infrastructure using ASM for storage management. Usually you’d go with Grid Infrastructure to benefit from it’s high availability capabilities. There is no additional cost when you choose ASM for your database storage management.
In this blog post we will see how to migrate a non-encrypted, non-CDB database with datafiles stored on a local file system to the Oracle Cloud and meet it’s standards and requirements: Multitenant, Encryption, and using ASM.
The Environment
Source database: not encrypted non-CDB using local file system.
Target database: Encrypted CDB using ASM.
- Create the XML file that describes the non-CDB
- Copy the XML file along with the databases’ datafiles to the target host
- Plug in the non-CDB as PDB into the target CDB
- Upgrade if the target CDB is at a higher release version
- Run the noncdb_to_pdb.sql script to clean up the PDB
- Check the PDB using the PDB_PLUG_IN_VIOLATIONS view
- Encrypt the PDBs’ tablespaces
Prerequisites:
- All database options installed on the source non-CDB must be available in the target CDB, otherwise you’d need to remove those option from the non-CDB first.
- For version 12.1, the target CDB must have the same character set as the source non-CDB.
Step 1
Shut down the source non-CDB and start it in read-only mode:
SQL> select cdb from v$database;
CDB
---
NO
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only
ORACLE instance started.
Total System Global Area 4865392640 bytes
Fixed Size 8630568 bytes
Variable Size 1006636760 bytes
Database Buffers 3841982464 bytes
Redo Buffers 8142848 bytes
Database mounted.
Database opened.
Create the XML file that describes the non-CDB:
SQL> SET SERVEROUTPUT ON;
SQL> exec DBMS_PDB.DESCRIBE(pdb_descr_file => '/home/oracle/db12201.xml');
PL/SQL procedure successfully completed.
Step 2
On the source non-CDB, check the datafiles location and shut down the non-CDB database:
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
Copy the XML file along with the datafiles to the target host:
scp -p /home/oracle/db12201.xml oracle@<target_ip>:/home/oracle/db12201.xml
scp -p /u01/app/oracle/oradata/ORCL/*.dbf oracle@<target_ip>:/u01/app/oracle/oradata/ORCL/
In case the datafiles’ path (here /u01/app/oracle/oradata/ORCL/) doesn’t exist on the target, or you already have database datafiles there you don’t want to override, copy the datafiles to another location on the target host, e.g. /tmp and change the paths in the XML files manually.
vi db12201.xml
-- look for "<path>" and edit
<path>/u01/app/oracle/oradata/ORCL/users01.dbf</path>
-- to
<path>/tmp/users01.dbf</path>
-- repeat for all datafiles
Step 3
On the target CDB, check the compatibility of the non-CDB to be plugged in as PDB:
SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => '/home/oracle/db12201.xml', pdb_name => 'NEWPDB')
WHEN TRUE THEN 'YES' ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
NO
PL/SQL procedure successfully completed.
If you get a “NO” like above, don’t worry. In most cases it will give you a “NO”. Indeed I can’t remember if I ever got a “YES”. We just need to check what’s wrong by querying the PDB_PLUG_IN_VIOLATIONS view and correct the issues.
Connect to the target CDB and create a new PDB using the XML file describing the non-CDB database.
SQL> create pluggable database pdbnew using '/home/oracle/db12201.xml' copy;
-- if you are not using OMF, add:
-- file_name_convert = ('/u01/app/oracle/oradata/ORCL/', '+DATA/CDB12201_fra2gs/PDBNEW/');
Pluggable database created.
SQL> alter session set container=pdbnew;
Session altered.
SQL> select name from v$datafile;
NAME
----------------------------------------------------------------------------------------------------
+DATA/CDB12201_FRA2GS/BE790D11A2143A33E0533700000A9D8A/DATAFILE/system.280.1068293317
+DATA/CDB12201_FRA2GS/BE790D11A2143A33E0533700000A9D8A/DATAFILE/sysaux.278.1068293317
+DATA/CDB12201_FRA2GS/BE790D11A2143A33E0533700000A9D8A/DATAFILE/undotbs1.279.1068293317
+DATA/CDB12201_FRA2GS/BE790D11A2143A33E0533700000A9D8A/DATAFILE/users.277.1068293317
Step 4
In case your target CDB is at a higher release level (e.g. source is 18c and target is 19c), then now it’s the time to upgrade the newly created PDB. Use AutoUpgrade or execute the following commands:
SQL> alter Pluggable database pdbnew open upgrade;
$ORACLE_HOME/bin/dbupgrade -c "PDBNEW" -l /tmp
Step 5
The plugged in non-CDB (the new PDB) still contains some objects in the data dictionary that does not belong to a PDB and hence need to be removed. This is done by executing the noncdb_to_pdb.sql script from within the PDB:
The script also automatically runs the utl_recomp.recomp_parallel procedure, which could take a while to complete.
SQL> alter session set container=pdbnew;
Session altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDBNEW MOUNTED
SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
Step 6
Open and check the PDB. As we got a “NO” is step 3, it will be no surprise that our PDB will open with errors in restricted mode:
SQL> alter pluggable database pdbnew open;
Warning: PDB altered with errors.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDBNEW READ WRITE YES
SQL> set lines 300
SQL> col type for a15
SQL> col cause for a30
SQL> col message for a170
SQL> select type, cause, message
from PDB_PLUG_IN_VIOLATIONS
where name='PDBNEW' and status != 'RESOLVED';
TYPE CAUSE MESSAGE
--------------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ERROR SQL Patch SQL patch ID/UID 31668898/23868204 (OJVM RELEASE UPDATE 12.2.0.1.201020): Installed in the PDB but not in the CDB.
ERROR SQL Patch SQL patch ID/UID 31465389/23823236 (RMAN RECOVER FAILS FOR PDB$SEED DATAFILES RMAN-06163 RMAN-06166): Installed in the CDB but not in the PDB.
ERROR SQL Patch SQL patch ID/UID 31668898/23868204 (OJVM RELEASE UPDATE 12.2.0.1.201020): Installed in the CDB but not in the PDB.
ERROR SQL Patch SQL patch ID/UID 31772700/23767883 (MERGE REQUEST ON TOP OF DATABASE OCT 2020 RU 12.2.0.1.201020 FOR BUGS 31359592): Installed in the CDB but not in the PDB.
WARNING is encrypted tablespace? Tablespace SYSTEM is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.
WARNING is encrypted tablespace? Tablespace SYSAUX is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.
WARNING is encrypted tablespace? Tablespace USERS is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.
Ok, we have 4 errors regarding missing patches and 3 warnings regarding non-encrypted tablespaces.
The patching related errors indicates having patches in the CDB, but not in the PDB, so we need to run datapatch for the PDB. This would also be the case when the CDB is at a higher patch level than the original non-CDB.
$ORACLE_HOME/OPatch/datapatch -verbose -pdbs PDBNEW
...
Patch 31772700 apply (pdb PDBNEW): SUCCESS
logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/31772700/23767883/31772700_apply_CDB12201_PDBNEW_2021Mar27_12_36_42.log (no errors)
SQL Patching tool complete on Sat Mar 27 12:36:50 2021
-- check
SQL> alter pluggable database pdbnew close immediate;
Pluggable database altered.
SQL> alter pluggable database pdbnew open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDBNEW READ WRITE NO
SQL> select type, cause, message
from PDB_PLUG_IN_VIOLATIONS
where name='PDBNEW' and status != 'RESOLVED';
TYPE CAUSE MESSAGE
--------------- ------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WARNING is encrypted tablespace? Tablespace SYSTEM is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.
WARNING is encrypted tablespace? Tablespace SYSAUX is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.
WARNING is encrypted tablespace? Tablespace USERS is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.
No errors regarding patches anymore.
Step 7
Encrypt the USERS tablespace. Encrypting the SYSTEM and SYSAUX tablespaces is usually not needed as they should not contain any application data. But let’s do it anyway to demonstrate getting rid of the above warnings:
SQL> administer key management set key force keystore identified by <TDE_Wallet_Password> with backup;
keystore altered.
SQL> alter tablespace USERS encryption online encrypt;
Tablespace altered.
SQL> alter tablespace SYSTEM encryption online encrypt;
Tablespace altered.
SQL> alter tablespace SYSAUX encryption online encrypt;
Tablespace altered.
Last check:
SQL> alter pluggable database pdbnew close immediate;
Pluggable database altered.
SQL> alter pluggable database pdbnew open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDBNEW READ WRITE NO
SQL> select type, cause, message
from PDB_PLUG_IN_VIOLATIONS
where name='PDBNEW' and status != 'RESOLVED';
no rows selected
That’s it!
Fallback
If something went wrong throughout the migration process and you want to keep your original non-CDB, you just need to start it again. No changes were made to the original non-CDB.
In case you migrated successfully and started to use the target PDB for production, but want to fallback afterward, you’ll need to use a logical migration method (Data Pump export and import or Oracle GoldenGate). There is no PDB to non-CDB conversion.
Downtime
The downtime required for this procedure is mainly the time needed to copy the datafiles from the source to the target host (step 2) and copying the files from the local files system to ASM while creating the PDB (step 3).
If you have the requirement for a minimal downtime migration, you’ll need to create a Data Guard environment with the standby on the target host in the same ASM disk group and then create the PDB from it using the NOCOPY option.
Conclusion
Non-CDB is already deprecated since a while and Multitenant is the only supported architecture starting with 21c. Even if you are running non encrypted non-CDBs using the local file system for your databases’ datafile, it’s quite easy and straight forward to move to an encrypted PDB using ASM. At the same time you are also able to move to higher patch level or to upgrade your database to new major release, 19c of course.
Further Reading
- 10 Blogs Series about Oracle Database Migration
- Part 7/10: Migration Methods using Multitenant Architecture
- Is the Multitenant Option mandatory in the Oracle Cloud?
- How to create PDBs with different Character Sets in the same CDB
- A Simple Approach – Creating PDBs in a Data Guard environment with TDE enabled
Database Heartbeat 