Multitenant- Creating PDB Using the DBMS_PDB Package from Non-CDB

• Multitenant-  Creating PDB Using the DBMS_PDB Package from  Non-CDB

SRV3 - CDB

SRV4 - NONCDB

• Shutdown Non CDB database and open database in read only mode

SQL> select name,open_mode,database_role,CDB from V$database;

NAME      OPEN_MODE            DATABASE_ROLE    CDB
--------- -------------------- ---------------- ---
SMRNONCD  READ WRITE           PRIMARY          NO

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

Total System Global Area 1795162112 bytes
Fixed Size                  8621760 bytes
Variable Size             620757312 bytes
Database Buffers         1157627904 bytes
Redo Buffers                8155136 bytes
Database mounted.
SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

• Execute DBMS_PD Package command below

SQL> exec DBMS_PDB.DESCRIBE( PDB_DESCR_FILE => '/home/oracle/SMRNONCDB.xml');

PL/SQL procedure successfully completed.

• Check existing datafile and scp to target CDB database.

SQL> SET PAGESIZE 20
SQL> SET LINESIZE 200
SQL> SELECT 'scp -p ' || NAME || ' root@srv3:/u01/app/oracle/oradata/SMRNONCDB/' as command FROM V$DATAFILE ORDER BY 1;

SQL> exit

oracle@srv4:~> scp -p /u01/app/oracle/oradata/SMRNONCDB/system01.dbf oracle@srv3:/u01/app/oracle/oradata/SMRNONCDB/
Password:
system01.dbf                                                                                                                          100%  800MB  47.1MB/s   00:17
oracle@srv4:~> scp -p /u01/app/oracle/oradata/SMRNONCDB/undotbs01.dbf oracle@srv3:/u01/app/oracle/oradata/SMRNONCDB/
Password:
undotbs01.dbf                                                                                                                         100%   65MB  65.0MB/s   00:01
oracle@srv4:~> scp -p /u01/app/oracle/oradata/SMRNONCDB/users01.dbf oracle@srv3:/u01/app/oracle/oradata/SMRNONCDB/
Password:
users01.dbf 

oracle@srv4:~> scp -p /u01/app/oracle/oradata/SMRNONCDB/sysaux01.dbf oracle@srv3:/u01/app/oracle/oradata/SMRNONCDB/
Password:
sysaux01.dbf                                                                                                                          100%  460MB  92.0MB/s   00:05

• Target CDB database Server SRV3.example.com

(Check the database compatibility )

SQL> begin
IF DBMS_PDB.CHECK_PLUG_COMPATIBILITY( PDB_DESCR_FILE => '/home/oracle/SMRNONCDB.xml', PDB_NAME => 'PLM5')
THEN
DBMS_OUTPUT.PUT_LINE('COMPATIBLE');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT COMPATIBLE');
END IF;
end;
/  2    3    4    5    6    7    8    9
COMPATIBLE

PL/SQL procedure successfully completed.

• Provide permission to SMRNONCDB.xml if already have then kindly ignore.

srv3:/home/oracle # chown oracle:oinstall /home/oracle/SMRNONCDB.xml

Create Pluggable database 

oracle@srv3:~> sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Aug 10 20:17:58 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> CREATE PLUGGABLE DATABASE PLM5 USING '/home/oracle/SMRNONCDB.xml';

Pluggable database created.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PLM1                           READ WRITE NO
         4 PDB2                           MOUNTED
         5 PLM2                           READ WRITE NO
         6 PLM4                           READ WRITE NO
         7 PLM3                           READ WRITE NO
         8 PLM5                           MOUNTED
SQL>

SQL> ALTER SESSION SET CONTAINER=PLM5;

Session altered.

• Execute conversion script noncdb_to_pdb.sql

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

• Open and check the status

SQL> ALTER PLUGGABLE DATABASE PLM5 OPEN;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         8 PLM5                           READ WRITE NO