unplug / plug PDBS

参考文档

https://docs.oracle.com/database/122/ADMIN/creating-and-removing-pdbs-with-sql-plus.htm#ADMIN13553

-- unplug PDB后， 会产生一个xml文件。 plug的时候，要using这个XML文件。然后file name convert 即可 。

--  -- pdb2 在CDB mynewdb下，将其uplug， 然后plug到Orcl12c CDB下

SYS@mynewdb>ALTER PLUGGABLE DATABASE PDB2 UNPLUG INTO '/home/oracle/PDB2.xml';

Pluggable database altered.

SYS@mynewdb>drop pluggable database PDB2 keep datafiles;

Pluggable database dropped.

SYS@mynewdb>

-- 检查兼容性

-- 先检查是否兼容，,如果不兼容，则在视图PDB_PLUG_IN_VIOLATIONS中查看信息。这里是兼容的

SET SERVEROUTPUT ON
DECLARE
  v_result BOOLEAN;
BEGIN
  v_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => '/home/oracle/PDB2.xml',
                pdb_name       => 'PDB2');

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


SYS@orcl12c>
SET SERVEROUTPUT ON
DECLARE
  v_result BOOLEAN;
BEGIN
  v_result := DBMS_PDB.check_plug_compatibility(
                pdb_descr_file => '/home/oracle/PDB2.xml',
                pdb_name       => 'PDB2');

  IF v_result THEN
    DBMS_OUTPUT.PUT_LINE('compatible');
  ELSE
    DBMS_OUTPUT.PUT_LINE('incompatible');
  END IF;
SYS@orcl12c>  2    3    4    5    6    7    8    9   10   11   12   13  END;
 14  /
compatible

PL/SQL procedure successfully completed.

SYS@orcl12c>

-- plug 到orcl12c cdb库上

CREATE PLUGGABLE DATABASE pdb_plugged USING '/home/oracle/PDB2.xml'
  FILE_NAME_CONVERT=('/u01/app/oracle12/oradata12/pdb2/','/u01/app/oracle12/oradata12/orcl12c/pdg_plugged/');

SYS@orcl12c>
CREATE PLUGGABLE DATABASE pdb_plugged USING '/home/oracle/PDB2.xml'
  2    FILE_NAME_CONVERT=('/u01/app/oracle12/oradata12/pdb2/','/u01/app/oracle12/oradata12/orcl12c/pdg_plugged/');
CREATE PLUGGABLE DATABASE pdb_plugged USING '/home/oracle/PDB2.xml'
*
ERROR at line 1:
ORA-04031: unable to allocate 1048848 bytes of shared memory ("shared
pool","unknown object","PDB Dynamic He","alls-ktimcem")

-- 查看错误，在alert log 中，没有可以参考的信息。和这个提示一样。 看 oerr 错误信息

-- 注意，这个pdb ，unplug之前所在的cdb memory是800， plug到的CDB 的Memory为600M ，可能和这个有关 ？

SYS@orcl12c>!oerr ORA 04031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
//          DBMS_SHARED_POOL package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          initialization parameters SHARED_POOL_RESERVED_SIZE and 
//          SHARED_POOL_SIZE.
//          If the large pool is out of memory, increase the initialization
//          parameter LARGE_POOL_SIZE.  
//          If the error is issued from an Oracle Streams or XStream process, 
//          increase the initialization parameter STREAMS_POOL_SIZE or increase
//          the capture or apply parameter MAX_SGA_SIZE.

SYS@orcl12c>

-- Memory

SYS@orcl12c>show parameter mem

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address	     integer	 0
inmemory_adg_enabled		     boolean	 TRUE
inmemory_clause_default 	     string
inmemory_expressions_usage	     string	 ENABLE
inmemory_force			     string	 DEFAULT
inmemory_max_populate_servers	     integer	 0
inmemory_query			     string	 ENABLE
inmemory_size			     big integer 0
inmemory_trickle_repopulate_servers_ integer	 1
percent
inmemory_virtual_columns	     string	 MANUAL

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
memory_max_target		     big integer 600M
memory_target			     big integer 600M
optimizer_inmemory_aware	     boolean	 TRUE
shared_memory_address		     integer	 0
SYS@orcl12c>

-- 查看PDB 的状态 ，并且打开PDB

SYS@orcl12c>show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB_PLUGGED			  MOUNTED
SYS@orcl12c>



SYS@orcl12c>alter pluggable database pdb_plugged open;

Pluggable database altered.

SYS@orcl12c>show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB_PLUGGED			  READ WRITE NO
SYS@orcl12c>

-- 查看数据文件，原来的CDB上，数据文件还是存在的 。 不过plugged到的PDB上多了temp文件。

-- 补充，这个可能和mynewdb有关，这个CDB库是手工创建的，运行CATCDB.sql出错。待确定

-- 从null，插入到12.2.0 。和之前的刚好相反 。

end