Multitenant - Create PDB from NON CDB database using DBLINK

Multitenant - Create PDB from NON CDB database using DBLINK

LOCAL database  name : SMRCDB

Remote database name : NONCDB

• Provide grant to local user system is common user in both CDB and non CDB database. Also create separate user and provide privilege

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO SYSTEM;

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
         7 PLM3                           READ WRITE NO

• Create database link NONCDB  

SQL> CREATE DATABASE LINK NONCDB  CONNECT TO system IDENTIFIED BY system USING 'SMRNONCDB';

Database link created.

• Check the status

SQL> SELECT SYSDATE FROM DUAL@NONCDB;

SYSDATE
---------
10-AUG-20

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES@NONCDB ORDER BY 1;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRNONCDB/sysaux01.dbf
/u01/app/oracle/oradata/SMRNONCDB/system01.dbf
/u01/app/oracle/oradata/SMRNONCDB/undotbs01.dbf
/u01/app/oracle/oradata/SMRNONCDB/users01.dbf

• It will through error as NON CDB should be open in read only mode.

SQL> CREATE PLUGGABLE DATABASE PLM4 FROM oradb@NONCDB;

CREATE PLUGGABLE DATABASE PLM4 FROM oradb@NONCDB

*

ERROR at line 1:

ORA-17628: Oracle error 65011 returned by remote Oracle server

ORA-65011: Pluggable database  does not exist.

• Open NON CDB in read only mode

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;

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

NAME      OPEN_MODE            DATABASE_ROLE

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

SMRNONCD  READ ONLY            PRIMARY

• Create PDB using DB link

SQL> CREATE PLUGGABLE DATABASE PLM4 FROM NON$CDB@NONCDB;

Pluggable database created.

SQL> ALTER SESSION SET CONTAINER=PLM4;

Session altered.

• Execute noncdb_to_pdb.sql command

SQL> set timing on

SQL> @/u01/app/oracle/product/12.2.0/dbhome_1//rdbms/admin/noncdb_to_pdb.sql

• Open PDB and check the status 

SQL> ALTER PLUGGABLE DATABASE PLM4 OPEN;

Elapsed: 00:00:00.13

SQL> SELECT PDB_NAME, STATUS FROM DBA_PDBS;

PDB_NAME

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

STATUS

----------

PLM4

NORMAL

1 row selected.

Elapsed: 00:00:00.02

SQL> col PDB_NAME for a40

SQL> /

PDB_NAME                                 STATUS

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

PLM4                                     NORMAL

1 row selected.

Elapsed: 00:00:00.00

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         6 PLM4                           READ WRITE YES

SQL> SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N' ORDER BY 1;

USERNAME

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

INFODBA

1 row selected.