Useful SQL / RMAN statements to clone or create a secondary PDB in Oracle DBaaS 12. THIS IS NOT A SCRIPT YOU JUST RUN. THESE ARE JUST USEFUL QUERIES

dbaas-pdb-sql.sql

ALTER SESSION set exclude_seed_cdb_view = false;


ALTER SESSION set "_exclude_seed_cdb_view" = false;


SELECT file_name FROM cdb_data_files
WHERE con_id = 2
UNION
SELECT file_name 
FROM cdb_temp_files
WHERE con_id = 2;


CREATE PLUGGABLE DATABASE pdb2 
ADMIN USER pdb_adm IDENTIFIED BY welcome1
FILE_NAME_CONVERT=(  
'/u02/app/oracle/oradata/ORCL/pdbseed/system01.dbf',  
'/u02/app/oracle/oradata/ORCL/pdb2/system01.dbf',  
'/u02/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf',  
'/u02/app/oracle/oradata/ORCL/pdb2/sysaux01.dbf',  
'/u02/app/oracle/oradata/ORCL/29D627273C13166CE053D64EC40AC9E5/datafile/o1_mf_users_cl48t4c4_.dbf',  
'/u02/app/oracle/oradata/ORCL/pdb2/users01.dbf',  
'/u02/app/oracle/oradata/ORCL/pdbseed/pdbseed_temp012016-01-21_09-59-05-AM.dbf',  
'/u02/app/oracle/oradata/ORCL/pdb2/temp01.dbf');  

Connect with SYSDBA privs for this step:

ALTER PLUGGABLE DATABASE pdb2 open READ WRITE;
SELECT name 
FROM v$services
WHERE pdb = 'PDB2';




/*Note the PDB name identifier has to be supplied in upper-case.*/


ORCL.PDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.101.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb2.gse00000379.oraclecloud.internal)
    )
  )





Drop a Pluggable Database

ALTER PLUGGABLE DATABASE pdb2 close;
DROP PLUGGABLE DATABASE pdb2 including datafiles;



/*All the SQL operations listed below are executed in the container root database as a DBA user with SYSDBA privileges.*/


ALTER SESSION SET CONTAINER = CDB$ROOT;



SELECT tablespace_name, file_name 
FROM cdb_data_files f, dba_pdbs p
WHERE f.con_id = p.pdb_id
AND p.pdb_name = 'PDB1'
UNION
SELECT tablespace_name, file_name 
FROM cdb_temp_files f, dba_pdbs p
WHERE f.con_id = p.pdb_id
AND p.pdb_name = 'PDB1';

/*In Oracle 12.1 cone is read only */

ALTER PLUGGABLE DATABASE "PDB1" CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE "PDB1" OPEN READ ONLY;


CREATE PLUGGABLE DATABASE "NEW_PDB" FROM "PDB1"
STORAGE UNLIMITED TEMPFILE REUSE
FILE_NAME_CONVERT=(
'/u02/app/oracle/oradata/EDDB/PDB1/users01.dbf',
'/u02/app/oracle/oradata/EDDB/NEW_PDB/users01.dbf',
'/u02/app/oracle/oradata/EDDB/PDB1/system01.dbf',
'/u02/app/oracle/oradata/EDDB/NEW_PDB/system01.dbf',
'/u02/app/oracle/oradata/EDDB/PDB1/temp01.dbf',
'/u02/app/oracle/oradata/EDDB/NEW_PDB/temp01dbf',
'/u02/app/oracle/oradata/EDDB/PDB1/sysaux01.dbf',
'/u02/app/oracle/oradata/EDDB/NEW_PDB/temp01.dbf'
);


ALTER PLUGGABLE DATABASE "PDB1" CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE "PDB1" OPEN READ WRITE;
ALTER PLUGGABLE DATABASE "NEW_PDB" CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE "NEW_PDB" OPEN READ WRITE;



set linesize 200
SELECT time || ' ' || name || ' ' || message || ' ' || action
FROM pdb_plug_in_violations
ORDER BY time;



--switch to the new Pluggable (container) Database
ALTER SESSION SET CONTAINER = NEW_PDB;
-- Find the name of the service that has been registered
SELECT NAME FROM v$services;



/* sqlplus system/welcome1//1.2.1.6:1521/pdb5.ggg00000379.oraclecloud.internal*/

/* EXAMPLE TNS ORA ENTRY */

MYCONTAINER.NEW_PDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.101.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = new_pdb.gse00000379.oraclecloud.internal)
    )
  )

ALTER PLUGGABLE DATABASE pdb1 close immediate;


ALTER PLUGGABLE DATABASE pdb1 unplug into 'PDB1.XML'