Tag Archives: covert noncdb to pdb

Convert Non-CDB to PDB with DBMS_PDB

Convert 12.2 Non-CDB (NONCDB12C) as PDB (NONCDB12C) into CDB (CDB1)

Contents
___________________________________________________________________________________________________

1. Overview
2. Environment
3. Shutdown NON-CDB (NONCDB12C)
4. Open Non-CDB in read only (this is for consistency)
5. Run DBMS_PDB.DESCRIBE to create an XML file
6. Shutdown the Non-CDB
7. Connect to Target
8. Check whether Non-CDB (NONCDB12C) can be plugged into CDB(CDB1)
9. Create target directory for datafiles
10. Plug-in Non-CDB (NONCDB12C) as PDB (NONCDB12C) into CDB1
11. Verify newly created PDB NONCDB12C on CDB1
12. Run noncdb_to_pdb.sql script on new PDB (NONCDB12C)
13. Verify table PDB_PLUG_IN_VIOLATIONS
___________________________________________________________________________________________________


1. Overview

There are 4 methods to move Non-CDB into a Pluggable Database (PDB) in a Container Database

- Create PDB by cloning a Non-CDB
- Use DBMS_PDB package
- Use Oracle Data Pump export/import
- Use GoldenGate Replication

Note: If status is UNUSABLE state it must be dropped before a PDB with the same name as the unusable PDB can be created. YOu can view the status from CBD_PDBS OR DBA_PDBS views


2. Environment

Platform		: Linux x86_64
Server Name		: RAC1.RAJASEKHAR.COM, IP: 192.168.2.101
DB Version		: Oracle 12.2.0.1, File system: Normal
Source NON-CDB   	: NONCDB12C
Target CDB 		: CDB1
Target PDB  	        : NONCDB12C
Oracle Home Path        : /u01/app/oracle/product/12.2.0.1


3. Shutdown NON-CDB (NONCDB12C)

SQL> select name, open_mode, cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
NONCDB12  READ WRITE           NO <-----

SQL> create user raj identified by raj default tablespace users temporary tablespace temp;

User created.

SQL> grant connect,resource to raj;

Grant succeeded.

SQL> alter user raj quota unlimited on users;

User altered.

SQL> conn raj/raj;
Connected.
SQL> show user
USER is "RAJ"
SQL>
SQL> create table sugi(Name varchar2(10), Role varchar2(10));

Table created.

SQL> insert into sugi values ('&a','&b');
Enter value for a: RAJ
Enter value for b: DBA
old   1: insert into sugi values ('&a','&b')
new   1: insert into sugi values ('RAJ','DBA')

1 row created.

SQL> /
Enter value for a: SUGI
Enter value for b: DBA
old   1: insert into sugi values ('&a','&b')
new   1: insert into sugi values ('SUGI','DBA')

1 row created.

SQL> /
Enter value for a: TEJU
Enter value for b: DBA
old   1: insert into sugi values ('&a','&b')
new   1: insert into sugi values ('TEJU','DBA')

1 row created.

SQL> /
Enter value for a: VIJAY
Enter value for b: DBA
old   1: insert into sugi values ('&a','&b')
new   1: insert into sugi values ('VIJAY','DBA')

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select * from sugi; <-- please check output once task completed.

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA

SQL> 
SQL> CONN / AS SYSDBA
Connected.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/oradata/NONCDB12C/system01.dbf
/u02/oradata/NONCDB12C/sysaux01.dbf
/u02/oradata/NONCDB12C/undotbs01.dbf
/u02/oradata/NONCDB12C/users01.dbf

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


4. Open Non-CDB in read only (this is for consistency)

SQL> STARTUP MOUNT EXCLUSIVE;
ORACLE instance started.

Total System Global Area 1660944384 bytes
Fixed Size                  8621376 bytes
Variable Size            1056965312 bytes
Database Buffers          587202560 bytes
Redo Buffers                8155136 bytes
Database mounted.
SQL>
SQL> alter database open read only;

Database altered.

SQL> select name, open_mode, cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
NONCDB12  READ ONLY            NO

SQL>


5. Run DBMS_PDB.DESCRIBE to create an XML file

The DBMS_PDB. It describe procedure creates the XML file that contains all necessary information regarding the nonCDB Database

SQL> BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => '/u02/oradata/NONCDB12C/noncdb12c.xml');
END;
/ 

PL/SQL procedure successfully completed.

SQL>


[oracle@rac1 ~]$ cat /u02/oradata/NONCDB12C/noncdb12c.xml

Please click noncdb12c.xml    <------

[oracle@rac1 ~]$


6. Shutdown the Non-CDB

SQL> select name, open_mode, cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
NONCDB12  READ ONLY            NO

SQL>
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@rac1 ~]$

On Target
===========


7. Connect to Target

Connect to the target Container Database (CDB1)

SQL> select name, open_mode, cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDB1      READ WRITE           YES  <-----

SQL>


8. Check whether non-cdb (NONCDB12C) can be plugged into CDB(CDB1)

SQL> SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/u02/oradata/NONCDB12C/noncdb12c.xml',
pdb_name => 'NONCDB12')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

YES  

PL/SQL procedure successfully completed.

SQL>

--  Incase output is NO, then check PDB_PLUG_IN_VIOLATIONS view

SQL> col cause for a10
col name for a10
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NONCDB12C';

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------
NONCDB12C  Non-CDB to WARNING   PDB plugged in is a non-CDB,        PENDING
            PDB                 requires noncdb_to_pdb.sql be run.

NONCDB12C  Parameter  WARNING   CDB parameter memory_target         PENDING
                                mismatch: Previous 1584M Current 0


SQL>


9. Create target directory for datafiles

SQL> !mkdir -p /u01/app/oracle/oradata/CDB1/NONCDB12C

SQL>


10. Plug-in Non-CDB (NONCDB12C) as PDB (NONCDB12C) into CDB1

The NOCOPY option keeps the original location of the datafiles as the database is Plugged-In
The SOURCE_FILE_NAME_CONVERT option allows us to specify a new location of the datafiles

I am plugging the database in to a CDB on the same server with COPY clause and hence using  FILE_NAME_CONVERT.

SQL> CREATE PLUGGABLE DATABASE NONCDB12C USING '/u02/oradata/NONCDB12C/noncdb12c.xml'
COPY
file_name_convert=('/u02/oradata/NONCDB12C','/u01/app/oracle/oradata/CDB1/NONCDB12C'); 

Pluggable database created.

SQL>


11. Verify newly created PDB NONCDB12C on CDB1

SQL> select name, open_mode, cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDB1      READ WRITE           YES

SQL>
SQL> col pdb_name for a30
SQL> SELECT pdb_name , status from cdb_pdbs;

PDB_NAME                       STATUS
------------------------------ ----------
PDB1                           NORMAL
PDB$SEED                       NORMAL
NONCDB12C                      NEW <--------

SQL>

--- OR ---

SQL> select pdb_name, status from dba_pdbs;

PDB_NAME                       STATUS
------------------------------ ----------
PDB1                           NORMAL
PDB$SEED                       NORMAL
NONCDB12C                      NEW

SQL>
SQL> col name for a30
SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------------------------------------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
NONCDB12C                      MOUNTED <-----

SQL>

--check that datafiles copied to new location 

SQL> !ls -ltr /u01/app/oracle/oradata/CDB1/NONCDB12C*
total 1397856
-rw-r-----. 1 oracle dba  33562624 Jul 22 21:39 temp01.dbf
-rw-r-----. 1 oracle dba   5251072 Jul 22 21:39 users01.dbf
-rw-r-----. 1 oracle dba  73408512 Jul 22 21:39 undotbs01.dbf
-rw-r-----. 1 oracle dba 503324672 Jul 22 21:39 sysaux01.dbf
-rw-r-----. 1 oracle dba 849354752 Jul 22 21:39 system01.dbf

SQL>


12. Run noncdb_to_pdb.sql script on new PDB (NONCDB12C)

You have to convert the plugged non-CDB to a proper PDB by deleting unnecessary metadata from PDB SYSTEM tablespace.
For this purpose, you execute the $ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
script when connected to the PDB. 

SQL> select name, open_mode, cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDB1      READ WRITE           YES

SQL> ALTER SESSION SET CONTAINER=NONCDB12C;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
NONCDB12C
SQL>
SQL> show con_id

CON_ID
------------------------------
4
SQL>


SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;
SQL> SET SERVEROUTPUT ON
SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL> SET VERIFY OFF
SQL>
SQL> -- save settings
SQL> STORE SET ncdb2pdb.settings.sql REPLACE
Wrote file ncdb2pdb.settings.sql
SQL>
SQL> SET TIME ON
21:58:13 SQL> SET TIMING ON
21:58:13 SQL>
21:58:13 SQL> WHENEVER SQLERROR EXIT;
21:58:13 SQL>
...
...
...
22:22:53 SQL> -- restore old settings
22:22:53 SQL> START ncdb2pdb.settings.sql
22:22:53 SQL> set appinfo OFF
22:22:54 SQL> set appinfo "SQL*Plus"
22:22:54 SQL> set arraysize 15
22:22:54 SQL> set autocommit OFF
22:22:54 SQL> set autoprint OFF
22:22:54 SQL> set autorecovery OFF
22:22:54 SQL> set autotrace OFF
22:22:54 SQL> set blockterminator "."
22:22:54 SQL> set cmdsep OFF
22:22:54 SQL> set colinvisible OFF
22:22:54 SQL> set colsep " "
22:22:54 SQL> set compatibility NATIVE
22:22:54 SQL> set concat "."
22:22:54 SQL> set copycommit 0
22:22:54 SQL> set copytypecheck ON
22:22:54 SQL> set define "&"
22:22:54 SQL> set describe DEPTH 1 LINENUM OFF INDENT ON
22:22:54 SQL> set echo ON
22:22:54 SQL> set editfile "afiedt.buf"
22:22:54 SQL> set embedded OFF
22:22:54 SQL> set escape OFF
22:22:54 SQL> set escchar OFF
22:22:54 SQL> set exitcommit ON
22:22:54 SQL> set feedback ON
22:22:54 SQL> set flagger OFF
22:22:54 SQL> set flush ON
22:22:54 SQL> set fullcolname OFF
22:22:54 SQL> set heading ON
22:22:54 SQL> set headsep "|"
22:22:54 SQL> set linesize 80
22:22:54 SQL> set lobprefetch 0
22:22:54 SQL> set logsource ""
22:22:54 SQL> set long 80
22:22:54 SQL> set longchunksize 80
22:22:54 SQL> set markup HTML OFF HEAD "
.......................................
..... Intentionally left black here.. trimmed output
.......................................
SQL> set termout ON 22:22:54 SQL> set time OFF SQL> set timing OFF 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>

Click here for complete output noncdb_to_pdb_output  <------


13. Verify table PDB_PLUG_IN_VIOLATIONS

Check out the table PDB_PLUG_IN_VIOLATIONS in order to see if there are error related to the plug in process, but read first the note "OPTION WARNING Database option mismatch: PDB installed version NULL" in PDB_PLUG_IN_VIOLATIONS (Doc ID 2020172.1)"

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? CDB1
The Oracle base has been set to /u01/app/oracle
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jul 22 22:41:00 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> col cause for a10
col name for a10
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NONCDB12C';SQL> SQL> SQL>

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------
NONCDB12C  Parameter  WARNING   CDB parameter memory_target         RESOLVED
                                mismatch: Previous 1584M Current 0

NONCDB12C  Non-CDB to ERROR     PDB plugged in is a non-CDB,        PENDING
            PDB                 requires noncdb_to_pdb.sql be run.


SQL>
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 NONCDB12C                      MOUNTED
SQL> 
SQL> alter session set container=NONCDB12C;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
NONCDB12C

SQL>
SQL> alter database open;

Database altered.

SQL>
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 NONCDB12C                      READ WRITE NO
SQL>

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sun Jul 22 22:58:51 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
SQL> show con_name

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

SQL> select name, open_mode, cdb from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDB1      READ WRITE           YES

SQL> col pdb_name for a30
SQL> SELECT pdb_name , status from cdb_pdbs;

PDB_NAME                       STATUS
------------------------------ ----------
PDB1                           NORMAL
PDB$SEED                       NORMAL
NONCDB12C                      NORMAL <----

SQL>

SQL> col cause for a10
col name for a10
col message for a35 word_wrapped
select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='NONCDB12C';SQL> SQL> SQL>

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------
NONCDB12C  Parameter  WARNING   CDB parameter memory_target         RESOLVED
                                mismatch: Previous 1584M Current 0

NONCDB12C  Non-CDB to ERROR     PDB plugged in is a non-CDB,        RESOLVED
            PDB                 requires noncdb_to_pdb.sql be run.


SQL>
SQL>  alter session set container=NONCDB12C;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
NONCDB12C
SQL>
SQL> select * from raj.sugi;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala