12c: PLUG IN 12c NON-CDB AS PDB

In this post, I will demonstrate the conversion of a 12.1.0.1 non-CDB  to a PDB. We will plug in  12.1.0.1 non-cdb named ncdb12c into a CDB called CDB2

Current scenario:

Source 12.1.0.1 non-cdb  : ncdb12c
Target CDB                               : CDB2
Target PDB                              : ncdb12c

To get data from a Non-CDB to a PDB you have to do a convert of a Non-CDB to PDB. The conversion process is similar to unplug and plug of a pdb.

Overview:

- Log into ncdb12c as sys
- Get the database in a consistent state by shutting it down cleanly.
- Open the database in read only mode
- Run DBMS_PDB.DESCRIBE to create an XML file  describing the database.
- Shut down ncdb12c
- Connect to target CDB (CDB2)
- Check whether non-cdb (NCDB12c) can be plugged into CDB(CDB2)
- Plug-in Non-CDB (NCDB12c) as PDB(NCDB12c) into target CDB(CDB2).
- Access the PDB and run the noncdb_to_pdb.sql script.
- Open the new PDB in read/write mode.

Implementation:

- Log into ncdb12c as sys and check that it is a non CDB

NCDB12C>sho parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      ncdb12c

NCDB12C>select name, cdb from v$database;

NAME      CDB
--------- ---
NCDB12C   NO

NCDB12C>select instance_name from v$instance;

INSTANCE_NAME
----------------
NCDB12C

-- Find out names of datafiles for NCDB12c (needed for FILE_NAME_CONVERT)

NCDB12C>select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ncdb12c/system01.dbf
/u01/app/oracle/oradata/ncdb12c/sysaux01.dbf
/u01/app/oracle/oradata/ncdb12c/undotbs01.dbf
/u01/app/oracle/oradata/ncdb12c/users01.dbf

To convert it to be a PDB we first need to get the database in a consistent state and then run DBMS_PDB.DESCRIBE to create an XML file to describe the database.

- Shutdown the database cleanly and open it in read only mode

SQL> shutdown immediate;
startup mount;
alter database open read only;

exec dbms_pdb.describe (PDB_DESCR_FILE=>'/u01/app/oracle/oradata/ncdb12c/ncdb12c.xml');

ho ls -l /u01/app/oracle/oradata/ncdb12c/ncdb12c.xml

-rw-r--r-- 1 oracle asmadmin 3994 Jul 24 16:15 /u01/app/oracle/oradata/ncdb12c/ncdb12c.xml

- Shutdown the source non-CDB (NCDB12c)

SQL> shutdown immediate;
exit

Now we can plug NONCDB into a existing CDB database cdb2

– connect to target CDB (CDB2)

SQL>conn sys/oracle@cdb2 as sysdba

select name, CDB from v$database;

NAME       CDB
---------- ---
CDB2       YES

– Check whether non-cdb (NCDB12c) can be plugged into CDB(CDB2)

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

NO

- If the scripts returns 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='NCDB12C';

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

NCDB12C    Parameter  WARNING   CDB parameter sga_target mismatch:  PENDING
Previous 394264576 Current
1895825408

NCDB12C    Parameter  WARNING   CDB parameter pga_aggregate_target  PENDING
mismatch: Previous 131072000
Current 629145600

There are WARNING’s only.  We can continue.
– Make the target directory for datafiles and create pluggable database

SQL> ho mkdir -p /u02/app/oracle/oradata/cdb2/ncdb12c

Plug-in Non-CDB (NCDB12c) as PDB (NCDB12c):

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 ncdb12c USING '/u01/app/oracle/oradata/ncdb12c/ncdb12c.xml'
COPY
file_name_convert=('/u01/app/oracle/oradata/ncdb12c','/u02/app/oracle/oradata/cdb2/ncdb12c');

Pluggable database created.

– check that datafiles for the plugagble database ncdb12c have been created in the location specified in FILE_NAME_CONVERT

SQL> ho ls /u02/app/oracle/oradata/cdb2/ncdb12c/*
/u02/app/oracle/oradata/cdb2/ncdb12c/sysaux01.dbf
/u02/app/oracle/oradata/cdb2/ncdb12c/system01.dbf
/u02/app/oracle/oradata/cdb2/ncdb12c/temp01.dbf
/u02/app/oracle/oradata/cdb2/ncdb12c/users01.dbf

– check that newly created PDB NCDB12c is in mounted state

SQL>  select name, open_mode from v$pdbs;

NAME       OPEN_MODE
---------- ----------
PDB$SEED   READ ONLY
PDB1       MOUNTED
NCDB12C    MOUNTED

– check that status of newly created PDB NCDB12c is NEW

SQL> col pdb_name for a15
select pdb_name, status from dba_pdbs where pdb_name = 'NCDB12C';

PDB_NAME        STATUS
--------------- -------------
NCDB12C         NEW

–Run noncdb_to_pdb.sql script, complete the following steps:

- Access the newly creatd PDB.

SQL> alter session set container=ncdb12c;

sho con_name

CON_NAME
------------------------------
NCDB12C

- Run the noncdb_to_pdb.sql script:

NCDB12c>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

– connect to CDB and verify that warnings for sga_target and pga_aggregate_target mismatch have been resolved

SQL>conn sys/oracle@cdb2 as sysdba

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='NCDB12C';

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------
NCDB12C    Parameter  WARNING   CDB parameter sga_target mismatch:  RESOLVED
Previous 394264576 Current
1895825408

NCDB12C    Parameter  WARNING   CDB parameter pga_aggregate_target  RESOLVED
mismatch: Previous 131072000
Current 629145600

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

You must open the new PDB in read/write mode for Oracle Database to complete the integration of the new PDB into the CDB. An error is returned if you attempt to open the PDB in read-only mode. After the PDB is opened in read/write mode, its status is NORMAL.

– Open the new PDB in read/write mode.

SQL>shu immediate;
alter pluggable database ncdb12c open;

– check that the status of NCDB12c changes to OPEN now

SQL>select pdb_name, status from dba_pdbs;

PDB_NAME        STATUS
--------------- -------------
PDB1            NORMAL
PDB$SEED        NORMAL
NCDB12C         NORMAL

– verify that warning for requires noncdb_to_pdb.sql be run has also been resolved

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='NCDB12C';SQL> SQL> SQL>

NAME       CAUSE      TYPE      MESSAGE                             STATUS
---------- ---------- --------- ----------------------------------- ---------
NCDB12C    Parameter  WARNING   CDB parameter sga_target mismatch:  RESOLVED
Previous 394264576 Current
1895825408

NCDB12C    Parameter  WARNING   CDB parameter pga_aggregate_target  RESOLVED
mismatch: Previous 131072000
Current 629145600

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

If an error is returned during PDB creation, then the PDB being created might be in an UNUSABLE state. You can check a PDB’s state by querying the CDB_PDBS or DBA_PDBS view, and you can learn more about PDB creation errors by checking the alert log. An unusable PDB can only be dropped, and it must be dropped before a PDB with the same name as the unusable PDB can be created.

References:

Oracle documentation

————————————————————————————————

Related Links:

Home

Oracle 12c Index

Clone An Existing PDB As Non-SYS User

 

——————————————————————————————————————

Your comments and suggestions are welcome!