Archive for

creating a pluggable database from an existing non-CDB Using the DBMS_PDB package

There are three ways of creating a pluggable database from an existing non-CDB:
• Using the DBMS_PDB package to generate metadata and then creating the pluggable database
with CREATE PLUGGABLE DATABASE SQL statement
• Data Pump (using the transportable tablespace feature)
• GoldenGate replication

Note :

When using the DBMS_PDB package to convert a non-CDB to a pluggable database, the non-CDB must be Oracle12c or higher.

Take care of your Non CDB and CDB chraceter set before pluggable database

In Oracle Database 12c, all pluggable databases (PDBs) in a container database (CDB) must have
* the same Database character set (NLS_CHARACTERSET) or the NLS_CHARACTERSET need to be a (Plug-in compatible) binary subset of the CDB NLS_CHARACTERSET
* the same National character set (NLS_NCHAR_CHARACTERSET)  as the CDB’s root container
in order to be able to plug in.

If PDB NLS_CHARACTERSET is a (Plug-in compatible) binary subset of the CDB’s NLS_CHARACTERSET the NLS_CHARACTERSET of the PDB is automatically changed to the NLS_CHARACTERSET of the CDB at the first open.

 

more detail support id : 12c Multitenant Container Databases (CDB) and Pluggable Databases (PDB) Character set restrictions / ORA-65116/65119: incompatible database/national character set ( Character set mismatch: PDB character set CDB character set ) (Doc ID 1968706.1)

Step 1:  Non CDB in read only mode

SQL> select name from v$database;

NAME
———
ORA12C

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 6710886400 bytes
Fixed Size 5298856 bytes
Variable Size 2835350872 bytes
Database Buffers 3858759680 bytes
Redo Buffers 11476992 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

Step 2: run the DBMS_PDB package to create an XML file that describes the structure of the non-CDB database

SQL> BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => ‘/u01/app/12c/ncdb.xml’);
END;
/ 2 3 4

PL/SQL procedure successfully completed.

Step 3: shutdown the non cdb database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

 

Step 4: connect to cdb container

SQL> show user con_id con_name
USER is “SYS”

CON_ID
——————————
1

CON_NAME
——————————
CDB$ROOT

Step 5: Now, you can optionally check to see if the non-CDB is compatible with the CDB in which it will be plugged.
When you run this code provide the directory and name of the XML file that was created previously

SQL> SET SERVEROUTPUT ON
DECLARE
hold_var boolean;
begin
hold_var := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=>’/u01/app/12c/ncdb.xml’);
if hold_var then
dbms_output.put_line(‘YES’);
else
dbms_output.put_line(‘NO’);
end if;
end;SQL> 2 3 4 5 6 7 8 9 10
11 /
YES

PL/SQL procedure successfully completed.

If there are no compatibility issues, a YES is displayed by the prior code; a NO is displayed if the pluggable database
is not compatible. You can query the contents of the PDB_PLUG_IN_VIOLATIONS view for details on why a pluggable
database is not compatible with a CDB.

Step 6: create pluggable database

SQL> CREATE PLUGGABLE DATABASE ora12c
USING ‘/u01/app/12c/ncdb.xml’
2 3 COPY
4 FILE_NAME_CONVERT = (‘/premia/app/12c/oracle/oradata/ora12c/’,
5 ‘/premia/app/12c/oracle/oradata/CDB/ora12c/’,’/premia/app/12c/oracle/product/dbhome_1/dbs/’,’/premia/app/12c/oracle/oradata/CDB/ora12c/’);

Pluggable database created.

Step 7:

SQL> select con_id, name from v$containers;

CON_ID NAME
———- ——————————
1 CDB$ROOT
2 PDB$SEED
3 HRPDB
4 ORA12C

Step 8:  Run the below script

SQL> alter session set container=ora12c;

Session altered.

SQL> show user con_id con_name user
USER is “SYS”

CON_ID
——————————
4

CON_NAME
——————————
ORA12C
USER is “SYS”

SQL> @/premia/app/12c/oracle/product/dbhome_1/rdbms/admin/noncdb_to_pdb.sql

Step 9:

SQL> select con_id,name,open_mode from v$containers;

CON_ID NAME OPEN_MODE
———- —————————— ———-
4 ORA12C MOUNTED

SQL> alter pluggable database ORA12C open;

Pluggable database altered.

Step 10:

SQL> conn test/test@ora12c
Connected.
SQL> select * from emp;

EMPNO
———-
1
2
3
4

4 rows selected.