51 - [Oracle 19C] Unplugging & Plugging part1

 

 

 

 

Unplugging a PDB is disassociating the PDB from its CDB

Plugging in a PDB is associationg a PDB with a CDB

You can plug a PDB into the same or another CDB

 

1. Close the pdb1

2. Unplug pdb1 to xml

3. Drop the pdb1 but we keep the datafiles

4. Check compatibility

5. Plug the pdb1 using the xml

 

 

 

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT


SQL> alter pluggable database all open;
Pluggable database altered.


SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME 		OPEN_MODE
---------- -------------------- ----------
	 2 PDB$SEED		READ ONLY
	 3 ORCLPDB		READ WRITE
	 4 PDB1 		READ WRITE
	 5 PDB5 		READ WRITE


SQL> alter session set container=pdb5;
Session altered.


SQL> select employee_id, first_name, last_name from hr.employees ;

EMPLOYEE_ID FIRST_NAME		 LAST_NAME
----------- -------------------- -------------------------
	100 Steven		 King
	101 Neena		 Kochhar
	102 Lex 		 De Haan
	103 Alexander		 Hunold
    
...


SQL> alter session set container=cdb$root;
Session altered.

 

 

 

--step 1. To close pdb5

 

SQL> alter pluggable database pdb5 close immediate;
Pluggable database altered.


SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME 		OPEN_MODE
---------- -------------------- ----------
	 2 PDB$SEED		READ ONLY
	 3 ORCLPDB		READ WRITE
	 4 PDB1 		READ WRITE
	 5 PDB5 		MOUNTED

 

 

 

--step 2. To unplug pdb5

 

SQL> alter pluggable database pdb5 unplug into '/u01/app/oracle/oradata/pdb5.xml';
Pluggable database altered.


SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME 		OPEN_MODE
---------- -------------------- ----------
	 2 PDB$SEED		READ ONLY
	 3 ORCLPDB		READ WRITE
	 4 PDB1 		READ WRITE
	 5 PDB5 		MOUNTED

 

 

 

--step 3. To drop pdb5

 

SQL> drop pluggable database pdb5 keep datafiles; --you should keep it.
Pluggable database dropped.


SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME 		OPEN_MODE
---------- -------------------- ----------
	 2 PDB$SEED		READ ONLY
	 3 ORCLPDB		READ WRITE
	 4 PDB1 		READ WRITE

 

 

 

--step 4. To compile PROCEDURE and run PROCEDURE

 

--step 4 -- but this not work in 18c

CREATE OR REPLACE PROCEDURE checking_compatibility
IS
  l_result BOOLEAN;
BEGIN
  l_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => '/u01/app/oracle/oradata/pdb5.xml',
                pdb_name => 'PDB5');

  IF l_result THEN
    DBMS_OUTPUT.PUT_LINE('compatible');
  ELSE
    DBMS_OUTPUT.PUT_LINE('incompatible');
  END IF;
END;

SQL> set serveroutput ON;
SQL> exec checking_compatibility();
compatible

PL/SQL procedure successfully completed.

 

 

 

--step 5. To create a new PDB

 

SQL> create pluggable database pdbtest
using '/u01/app/oracle/oradata/pdb5.xml'
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/ORCL/pdb5/',
                  '/u01/app/oracle/oradata/ORCL/pdbtest/');

Pluggable database created.


SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME 		OPEN_MODE
---------- -------------------- ----------
	 2 PDB$SEED		READ ONLY
	 3 ORCLPDB		READ WRITE
	 4 PDB1 		READ WRITE
	 5 PDBTEST		MOUNTED


SQL> alter session set container=pdbtest;
Session altered.


SQL> show con_name
CON_NAME
------------------------------
PDBTEST


SQL> alter pluggable database open;
Pluggable database altered.


SQL> select employee_id, first_name, last_name from hr.employees ;

EMPLOYEE_ID FIRST_NAME		 LAST_NAME
----------- -------------------- -------------------------
	100 Steven		 King
	101 Neena		 Kochhar
	102 Lex 		 De Haan
	103 Alexander		 Hunold
	104 Bruce		 Ernst