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

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

4 thoughts on “12c: PLUG IN 12c NON-CDB AS PDB”

raj says:

September 6, 2015 at 5:02 am

Dear,

Its a good article, My question is , if i wish to plug this to a cdb on another server, then

Do i need to copy the physical files to that server. Because here here we are doing it in the same server, so the COPY command, FILE_NAME,CONVERT command copied the files to target . But in case of target cdb on a different server, what we need to do?

1. Anju Garg says:
  
  September 7, 2015 at 6:28 am
  
  Hi Raj,
  
  In the initial release of Oracle Database 12c Release 1 (12.1.0.1) remote cloning of PDBs was listed as a feature, but it didn’t work. The 12.1.0.2 patch has fixed that, but also added the ability to create a PDB as a clone of a remote non-CDB database. This provides an alternative to the initial migration path from non-CDB to PDB.
  
  Pls refer to following link:
  https://oracle-base.com/articles/12c/multitenant-clone-remote-pdb-or-non-cdb-12cr1#cloning-remote-non-cdb
  
  regards
  Anju
  
  1. Raj says:
    
    September 25, 2016 at 7:56 am
    
    Thanks mam ,
    
    I checked the link . It is using db link to copy the files . But for big database it will take a lot of time to copy files using db link. Any alternative to copy the files using ftp and …
    
    1. Anju Garg says:
      
      September 27, 2016 at 12:04 am
      
      Hi Raj,
      
      You can use transportable database feature to migrate a 12c non-CDB to a pluggable database.
      Please refer to following link which demonstrates use of full transportable export/import to migrate an 11.2.0.3 database to a pluggable database:
      http://oracleinaction.com/12c-transportable-database/
      
      Same procedure can be employed for non-cdb as well.
      
      Hope it helps
      
      regards
      Anju

ORACLE IN ACTION

Let's do it simply…

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

Like this:

Related

4 thoughts on “12c: PLUG IN 12c NON-CDB AS PDB”

Leave a Reply to Anju Garg Cancel reply

Follow ORACLE IN ACTION

Share this:

Like this:

Related

4 thoughts on “12c: PLUG IN 12c NON-CDB AS PDB”

Leave a Reply to Anju Garg Cancel reply

Follow ORACLE IN ACTION