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