CONVERT AND MIGRATE CDB/PDB TO NON-CDB ORACLE

Every time you would have heard blogs, articles, documents, videos etc .. about converting a NON-CDB to PDB database and this is how our world works. Time move forward but not backwards. But there are some situations where multitenant systems needs to be reverted back to a non-multitenant due to business requirements or application limitations. Let’s see how?

The only easiest method to convert a PDB to NON-CDB is by using transportable tablespaces in same server

SOURCE DATABASE(same server): CONTAINER CDB/PDB on 12.2 (PHYDRA) –> Existing CDB

TARGET DATABASE(same server): NON CONTAINER DATABASE on 12.2 (NCHYDRA) –> New non-CDB to be created

Both source and target should have same character set, same endian format(if different then convert to target endian), same files management(OMF or non-OMF), same database version, patches and binaries installed to further proceed with conversion. The plan is to migrate the user and application data from source to target.

SOURCE:

Things that need to be verified:

Check the datafiles format if it OMF managed or non-OMF. Here it is OMF and hence a new non-CDB database with OMF should be created

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PHYDRA                         MOUNTED
SQL> alter pluggable database PHYDRA open;

Pluggable database altered.

SQL> alter session set container=PHYDRA;

Session altered.
SQL> select name from v$datafile where rownum < 2;

NAME
--------------------------------------------------------------------------------
/apps01/oradata/XHYDRA/datafile/o1_mf_system_k2pmmm1g_.dbf


SQL> show parameter db_create_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /apps01/oradata

Check character set of source container

SQL> col PARAMETER for a20
SQL> col VALUE for a20
SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET';

PARAMETER            VALUE
-------------------- --------------------
NLS_CHARACTERSET     AL32UTF8

Check the database version

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

Check the installed patches and binaries

[oracle@xhydra ~]$ $ORACLE_HOME/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2022, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/12.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/12.2.0/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/opatch2022-03-29_08-27-30AM_1.log

Lsinventory Output file location : /u01/app/oracle/product/12.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2022-03-29_08-27-30AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: xhydra
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 12c                                                  12.2.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

Check the tablespace status in pdb. Here the only user or application tablespace is USERS

SQL> select tablespace_name,status from cdb_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE

Set the user defined tablespace to read only

SQL> alter tablespace users read only;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where status='READ ONLY';

TABLESPACE_NAME                STATUS
------------------------------ ---------
USERS                          READ ONLY

Create a logical directory for datapump export from pdb

SQL> create directory PDB_NO_CDB as '/tmp/pdb_no_cdb';

Directory created.

Create the physical directory and export the pdb user tablespace metadata using combination of full=Y and transportable=always.

Note: Only the datafile metadata will be exported to dump file and not the whole datafile.

[oracle@xhydra ~]$ mkdir -p /tmp/pdb_no_cdb
[oracle@xhydra ~]$ expdp system/password@phydra directory=PDB_NO_CDB dumpfile=pdb_no_cdb_%U.dmp parallel=8 logfile=pdb_no_cdb.log full=y transportable=always

Export: Release 12.2.0.1.0 - Production on Tue Mar 29 09:17:36 2022

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/********@phydra directory=PDB_NO_CDB dumpfile=pdb_no_cdb_%U.dmp parallel=8 logfile=pdb_no_cdb.log full=y transportable=always
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . exported "WMSYS"."WM$WORKSPACES_TABLE$"              12.10 KB       1 rows

Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /tmp/pdb_no_cdb/pdb_no_cdb_01.dmp
  /tmp/pdb_no_cdb/pdb_no_cdb_02.dmp
  /tmp/pdb_no_cdb/pdb_no_cdb_03.dmp
  /tmp/pdb_no_cdb/pdb_no_cdb_04.dmp
  /tmp/pdb_no_cdb/pdb_no_cdb_05.dmp
  /tmp/pdb_no_cdb/pdb_no_cdb_06.dmp
******************************************************************************
Datafiles required for transportable tablespace USERS:
  /apps01/oradata/XHYDRA/D9F26444844F4264E055000000000001/datafile/o1_mf_users_k2pmzsw2_.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Tue Mar 29 09:20:19 2022 elapsed 0 00:02:42

Now copy the datafile from old source pdb location to new target non-CDB location

[oracle@xhydra datafile]$ cp /apps01/oradata/XHYDRA/D9F26444844F4264E055000000000001/datafile/o1_mf_users_k2pmzsw2_.dbf /apps01/oradata/NCHYDRA/datafile/o1_mf_users_k2pmzsw2_.dbf

Trigger IMPDP job with options ==> transport_datafiles=/apps01/oradata/NCHYDRA/datafile/o1_mf_users_k2pmzsw2_.dbf remap_tablespace=USERS:USERS_NEW

Here both source and destination has ‘USERS’ tablespace and so remap_tablespace need to be specified explicitly. In reality, there can be different application tablespaces where this parameter can be removed.

[oracle@xhydra datafile]$ impdp \'/ as sysdba\' directory=PDB_NO_CDB dumpfile=pdb_no_cdb_%U.dmp parallel=8 logfile=imp_pdb_no_cdb.log full=y transport_datafiles=/apps01/oradata/NCHYDRA/datafile/o1_mf_users_k2pmzsw2_.dbf remap_tablespace=USERS:USERS_NEW

Import: Release 12.2.0.1.0 - Production on Tue Mar 29 09:34:56 2022

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=PDB_NO_CDB dumpfile=pdb_no_cdb_%U.dmp parallel=8 logfile=imp_pdb_no_cdb.log full=y transport_datafiles=/apps01/oradata/NCHYDRA/datafile/o1_mf_users_k2pmzsw2_.dbf remap_tablespace=USERS:USERS_NEW
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" completed with 4 error(s) at Tue Mar 29 09:37:03 2022 elapsed 0 00:02:07

Check the newly imported tablespace USERS_NEW from PDB

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
USERS_NEW                      ONLINE <<<<<===========

6 rows selected.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
NCHYDRA   READ WRITE

Now the PDB data is available in non-CDB database.

Reference – Doc ID 2027352.1

Leave a Reply