Convert NON CDB to CDB database Oracle 12c

Convert NON CDB to CDB database

Note 1:-We can’t convert NON CDB to CDB directly. Create a CDB database and plug NON CDB database as PDB to new CDB database.

Testdb1è NON CDB database

TESTCDB è CDB database.

PDB1 è Cloning of NON CDB database (We can use the same name of NON CDB database. But I used different name)

1)Mount NON-CDB db as read only.

SQL> startup  mount;

ORACLE instance started.

Total System Global Area  772472832 bytes

Fixed Size                  2855328 bytes

Variable Size             310382176 bytes

Database Buffers          452984832 bytes

Redo Buffers                6250496 bytes

Database mounted.

SQL>  ALTER DATABASE OPEN READ ONLY;

Database altered.

2)Create db description file

SQL> BEGIN

  2  DBMS_PDB.DESCRIBE(

  3  pdb_descr_file => '/home/oracle/ora12c.xml');

  4  END;

  5  /

PL/SQL procedure successfully completed.

SQL> ! ls -lrth /home/oracle/ora12c.xml

-rw-r--r-- 1 oracle dba 4.4K Apr 25 14:13 /home/oracle/ora12c.xml

3)Create new CDB database(TESTCDB)

 [oracle@rac1 dbs]$ ps -ef | grep pmon

oracle    3016     1  0 11:01 ?        00:00:01 asm_pmon_+ASM1

oracle    8346     1  0 11:14 ?        00:00:01 mdb_pmon_-MGMTDB

oracle   16164     1  0 14:03 ?        00:00:00 ora_pmon_testdb1

oracle   19226     1  0 14:55 ?        00:00:00 ora_pmon_TESTCDB

oracle   19410 12423  0 14:57 pts/0    00:00:00 grep pmon

4)Shutdown NON-CDB database.

5)Connect to new CDB database and clone the NON CDB db as PDB in new CDB db.

[oracle@rac1 dbs]$ . oraenv

ORACLE_SID = [testdb1] ? TESTCDB

[oracle@rac1 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.1.0 Production on Mon Apr 25 15:03:16 2016

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics

and Real Application Testing options

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME      OPEN_MODE

--------- --------------------

TESTCDB   READ WRITE

SQL> CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/ora12c.xml' COPY FILE_NAME_CONVERT = ('+DATA/PDB1/DATAFILE/', '+DATA/PDB1/DATAFILE/');

CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/ora12c.xml' COPY FILE_NAME_CONVERT = ('+DATA/PDB1/DATAFILE/', '+DATA/PDB1/DATAFILE/')

*

ERROR at line 1:

ORA-65005: missing or invalid file name pattern for file -

+DATA/TESTDB/DATAFILE/system.270.908822475

SQL>  CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/ora12c.xml' COPY FILE_NAME_CONVERT = ('+DATA/TESTDB/DATAFILE/', '+DATA/PDB1/DATAFILE/');

 CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/ora12c.xml' COPY FILE_NAME_CONVERT = ('+DATA/TESTDB/DATAFILE/', '+DATA/PDB1/DATAFILE/')

*

ERROR at line 1:

ORA-65005: missing or invalid file name pattern for file -

+DATA/TESTDB/DATAFILE/sysaux.269.908822371

SQL>CREATE PLUGGABLE DATABASE PDB1 USING

 '/home/oracle/ora12c.xml' COPY FILE_NAME_CONVERT = ('+DATA', '+DATA');

Pluggable database created.

SQL> SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;

NAME                           OPEN_MODE

------------------------------ ----------

PDB$SEED                       READ ONLY

PDB1                           MOUNTED

6)Connect to new PDB and execute the script.This will take time.So,Better execute in the backend.

SQL> ALTER SESSION SET CONTAINER=PDB1;

Session altered.

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>

SQL> WHENEVER SQLERROR EXIT;

SQL>

SQL> DOC

DOC>#######################################################################

DOC>#######################################################################

DOC>   The following statement will cause an "ORA-01403: no data found"

DOC>   error if we're not in a PDB.

DOC>   This script is intended to be run right after plugin of a PDB,

DOC>   while inside the PDB.

DOC>#######################################################################

DOC>#######################################################################

"""SKIPPED THE BIG OUTPUT""

SQL>

SQL> -- leave the PDB in the same state it was when we started

SQL> BEGIN

  2    execute immediate '&open_sql &restricted_state';

  3  EXCEPTION

  4    WHEN OTHERS THEN

  5    BEGIN

  6      IF (sqlcode <> -900) THEN

  7        RAISE;

  8      END IF;

  9    END;

 10  END;

 11  /

PL/SQL procedure successfully completed.

SQL>

SQL> WHENEVER SQLERROR CONTINUE;

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>

SQL>

SQL>

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         3 PDB1                           MOUNTED

7)Connect to CDB and open PDB

SQL> conn / as sysdba

Connected.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           MOUNTED

SQL> alter pluggable database PDB1 open;

Warning: PDB altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PDB1                           READ WRITE YES

SQL> show parameter ENABLE_PLUGGABLE_DATABASE

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

enable_pluggable_database            boolean     TRUE

Note:-The old NON-CDB database need to delete manually