52 - [Oracle 19C] Unplugging & Plugging part2

 

 

 

 

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 PDBTEST		READ WRITE
     
     
SQL> alter session set container=pdbtest;
Session altered.


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

EMPLOYEE_ID FIRST_NAME		 LAST_NAME
----------- -------------------- -------------------------
	100 Steven		 King
	101 Neena		 Kochhar
	102 Lex 		 De Haan
	103 Alexander		 Hunold
	104 Bruce		 Ernst
	105 David		 Austin
	106 Valli		 Pataballa

7 rows selected.


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

 

 

 

--step 1. 

 

SQL> alter pluggable database pdbtest 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 PDBTEST		MOUNTED

 

 

 

--step 2.

 

SQL> alter pluggable database pdbtest unplug into '/u01/app/oracle/oradata/pdbtest.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 PDBTEST		MOUNTED

 

 

 

--step 3. Main point is "keep datafiles".

 

SQL> drop pluggable database pdbtest 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.

 

create or replace NONEDITIONABLE PROCEDURE checking_compatibility
(descr_file IN VARCHAR2, name_pdb IN VARCHAR2)
IS
  l_result BOOLEAN;
BEGIN
  l_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => descr_file,
                pdb_name => name_pdb);

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



SQL> exec checking_compatibility('/u01/app/oracle/oradata/pdbtest.xml','pdbtest');
compatible

PL/SQL procedure successfully completed.


# In the event of an error caused by incorrectly entering the file name.
SQL> exec checking_compatibility('/u01/app/oracle/oradata/pdbtest1.xml','pdbtest');
BEGIN checking_compatibility('/u01/app/oracle/oradata/pdbtest1.xml','pdbtest'); END;

*
ERROR at line 1:
ORA-65026: XML metadata file error : LPX-00202: could not open
"/u01/app/oracle/oradata/pdbtest1.xml" (error 200)
ORA-06512: at "SYS.DBMS_PDB", line 32
ORA-06512: at "SYS.CHECKING_COMPATIBILITY", line 6
ORA-06512: at line 1

 

 

 

--step 5.

 

create pluggable database pdbtest1
using '/u01/app/oracle/oradata/pdbtest.xml'
NOCOPY TEMPFILE REUSE;
--FILE_NAME_CONVERT=('/u01/app/oracle/oradata/ORCL/pdb5/',
--                   '/u01/app/oracle/oradata/ORCL/pdbtest/')



SQL> create pluggable database pdbtest1
using '/u01/app/oracle/oradata/pdbtest.xml'
NOCOPY TEMPFILE REUSE;

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 PDBTEST1		MOUNTED

 

 

 

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


SQL> alter pluggable database open;
Pluggable database altered.


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

EMPLOYEE_ID FIRST_NAME		 LAST_NAME
----------- -------------------- -------------------------
	100 Steven		 King
	101 Neena		 Kochhar
	102 Lex 		 De Haan
	103 Alexander		 Hunold
	104 Bruce		 Ernst
	105 David		 Austin
	106 Valli		 Pataballa

7 rows selected.


SQL> select con_id,file#, name from V$DATAFILE;

    CON_ID	FILE# NAME
---------- ---------- --------------------------------------------------
	 5	   34 /u01/app/oracle/oradata/ORCL/pdbtest/system01.dbf
	 5	   35 /u01/app/oracle/oradata/ORCL/pdbtest/sysaux01.dbf
	 5	   36 /u01/app/oracle/oradata/ORCL/pdbtest/undotbs01.dbf
	 5	   37 /u01/app/oracle/oradata/ORCL/pdbtest/users01.dbf