In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Sunday, December 16, 2018

Convert normal database(NON-CDB) to PDB database Demo

Here is a demo to convert normal NONCDB database to container database as pluggable database (PDB) in oracle 12c.

I have converted normal non-ASM filesystem database to PDB database, same steps will be applicable to ASM filesystem databases.

DEMO:-

NORMAL database name:- NONCDB
Container database name:- CDBRAC

I have created Container database with the name CDBRAC and normal database name with the name NONCDB using dbca.

1. Open the non-cdb database in read only mode:

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

NAME   CDB OPEN_MODE
--------- --- --------------------
NONCDB   NO  READ ONLY
2. Check the compatibility of PDB on ( NONCDB)
Describe the non-DBC using the DBMS_PDB.DESCRIBE procedure. This procedure creates an XML file

SQL> BEGIN 
  2  DBMS_PDB.DESCRIBE(pdb_descr_file=> '/home/oracle/NonCDB_to_CDB.xml');
  3  END;
  4  /

PL/SQL procedure successfully completed.

3. shutdown the NON-CDB database ( NONCDB)

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

4. Now connect to the container database, where it need to be plugged.

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

NAME   CDB OPEN_MODE
--------- --- --------------------
CDBRAC   YES READ WRITE

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2  compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file=> '/home/oracle/NonCDB_to_CDB.xml')
  3  WHEN TRUE THEN 'YES'
  4  ELSE 'NO'
  5  END;
  6  BEGIN
  7  DBMS_OUTPUT.PUT_LINE(compatible);
  8  END;
  9  /
YES

PL/SQL procedure successfully completed.
5. Check the violations:

SQL>  select name, cause, type, message , status from pdb_plug_in_violations where name='NONCDB';

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

NONCDB        OPTION WARNING
Database option RAC mismatch: PDB installed version NULL. CDB installed version 12.1.0.2.0.
PENDING

NONCDB        Parameter WARNING
CDB parameter sga_target mismatch: Previous 452M Current 0
PENDING

NONCDB        Parameter WARNING
CDB parameter pga_aggregate_target mismatch: Previous 150M Current 0
PENDING


6. Create pluggable database


SQL> create pluggable database NONCDB using '/home/oracle/NonCDB_to_CDB.xml' NOCOPY;
create pluggable database NONCDB using '/home/oracle/NonCDB_to_CDB.xml' NOCOPY
*
ERROR at line 1:
ORA-27038: created file already exists
ORA-01119: error in creating database file '/u01/app/oracle/oradata/noncdb/temp01.dbf'

-- Tempfile already exists, so to avoid above temp error use tempfile REUSE Clause 
SQL> CREATE PLUGGABLE DATABASE NONCDB using '/home/oracle/NonCDB_to_CDB.xml' NOCOPY tempfile reuse;

Pluggable database created.

7 . Run the noncdb_to_pdb.sql script
SQL> ALTER SESSION SET CONTAINER=NONCDB;

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

8. Open the PDB:

SQL> SELECT NAME, OPEN_MODE FROM V$pdbs;

NAME        OPEN_MODE
------------------------------ ----------
PDB$SEED        READ ONLY
PDB1        MOUNTED
PDB2        MOUNTED
NONCDB        MOUNTED

SQL> alter session set container=NONCDB;

Session altered.

SQL> ALTER PLUGGABLE DATABASE OPEN;

SQL> show con_name

CON_NAME
------------------------------
NONCDB
SQL> show con_id

CON_ID
------------------------------
5
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/CDBRAC/DATAFILE/undotbs1.280.994898349
/u01/app/oracle/oradata/noncdb/system01.dbf
/u01/app/oracle/oradata/noncdb/sysaux01.dbf
/u01/app/oracle/oradata/noncdb/users01.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/noncdb/temp01.dbf
SQL> alter session set container=cdb$root;

SQL> SELECT NAME, OPEN_MODE FROM V$pdbs;

NAME        OPEN_MODE
------------------------------ ----------
PDB$SEED        READ ONLY
PDB1        MOUNTED
PDB2        MOUNTED
NONCDB        READ WRITE

Source and Reference:- http://dbaclass.com/article/convert-non-cdb-database-to-pdb-database-in-oracle-12c/

No comments:

free counters
 
Share/Bookmark