LATEST TOPICS

Migrate a Non CDB database to PDB and plug in to a CDB using DBMS_PDB package

Following databases were used for demonstration

Existing CDB : LABCP
Existing PDB: LABP
Existing NON CDB: LABPA

 

Step by Step Migration Tasks

1. Generate XML file from NON CDB to prepare for the PDB creation

  • Make sure NON CDB is in transactional consistent state
  • Start the NON CDB in READ ONLY MODE
SQL> select name,open_mode,DATABASE_ROLE,CDB from v$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
LABPA     READ ONLY            PRIMARY          NO

--- Generate the XML file for PDB creation using DBMS_PDB.DESCRIBE package

SQL> BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => '/app/oracle/ncdb_labpa.xml'); --- This XML file would be used for DB creation
END;
/

PL/SQL procedure successfully completed.

SQL> !ls -lrt /app/oracle/ncdb_labpa.xml
-rw-r--r-- 1 oracle oinstall 3971 Jul 24 22:46 /app/oracle/ncdb_labpa.xml

 

2. Shutdown the NON CDB database

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

 

3. Login to the CDB database root container

SQL> select name,open_mode,DATABASE_ROLE,CDB from v$database;

NAME       OPEN_MODE            DATABASE_ROLE    CDB
---------- -------------------- ---------------- ---
LABCP      READ WRITE           PRIMARY          YES

SQL> select con_id,GUID,name,open_mode from v$pdbs;

CON_ID     GUID                             NAME       OPEN_MODE
---------- -------------------------------- ---------- ----------
2          FE90E33C99253D19E04305E6A8C0272E PDB$SEED   READ ONLY
3          FE91351704094600E04305E6A8C07719 LABP       READ WRITE

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

 

4. Create the new PDB database using the generated XML file

  • This will create the new PDB by copying the datafiles from NON CDB. If DB_CREATE_FILE_DEST is not specified at the CDB level, you need to specify DB_FILE_NAME_CONVERT values along with the CREATE PLUGGABLE DATABASE statement
SQL> CREATE PLUGGABLE DATABASE labpa USING '/app/oracle/ncdb_labpa.xml'
COPY
;

Pluggable database created.

 

5. Cross-check the PDB creation

SQL> select con_id,GUID,name,open_mode from v$pdbs;

CON_ID     GUID                             NAME       OPEN_MODE
---------- -------------------------------- ---------- ----------
2          FE90E33C99253D19E04305E6A8C0272E PDB$SEED   READ ONLY
3          FE91351704094600E04305E6A8C07719 LABP       READ WRITE
----
---- LABPA is the new PDB 
----
4          FEF41694B59D2034E04305E6A8C014B5 LABPA      MOUNTED 

 

6. Run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script from CDB Home. This script must be run before the PDB can be opened for the first time

  • Need to execute the script within the new PDB
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

---- switch to the new pluggable database container
SQL> alter session set container=labpa;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
LABPA

SQL> @?/rdbms/admin/noncdb_to_pdb.sql
-----
----- The script opens the PDB, performs changes, and closes the PDB when the changes are complete. 
----- Will take some time for execution

 

7. Open the new PDB in read/write mode.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

SQL> alter pluggable database labpa open;

Pluggable database altered.

SQL> select con_id,GUID,name,open_mode from v$pdbs;

CON_ID     GUID                             NAME       OPEN_MODE
---------- -------------------------------- ---------- ----------
2          FE90E33C99253D19E04305E6A8C0272E PDB$SEED   READ ONLY
3          FE91351704094600E04305E6A8C07719 LABP       READ WRITE
4          FEF41694B59D2034E04305E6A8C014B5 LABPA      READ WRITE

 

7. Verify the status of data file from new PDB

 

SQL> show con_name

CON_NAME
------------------------------
LABPA

SQL> select tablespace_name,file_name,status from DBA_DATA_FILES;

TABLESPACE FILE_NAME                                                                        STATUS
---------- -------------------------------------------------------------------------------- ---------
USERS      +DATA/LABCP/FEF41694B59D2034E04305E6A8C014B5/DATAFILE/users.293.853801477        AVAILABLE
SYSAUX     +DATA/LABCP/FEF41694B59D2034E04305E6A8C014B5/DATAFILE/sysaux.296.853801439       AVAILABLE
SYSTEM     +DATA/LABCP/FEF41694B59D2034E04305E6A8C014B5/DATAFILE/system.303.853801407       AVAILABLE

 

One Response
  1. vkompally
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed