PDB1KTNS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.88)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1k) ) ) SQL> create database link pdb1k_link connect to system identified by system using 'PDB1KTNS';Validate the link by querying a view on the remote PDB
SQL> select name from v$pdbs@pdb1k_link; NAME ------------------------------ PDB1KIf OMF is used nothing else is needed and PDB could be cloned. However in this case a data files of the remotely cloned PDBs are stored separately. To achieve that set the db_create_dest parameter to desired location with scope set to memory.
SQL> alter system set db_create_file_dest='/opt/app/oracle/oradata/remoteclones' scope=memory; SQL> show parameter db_create NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /opt/app/oracle/oradata/remoteclones
Put the source PDB into read only mode. Refer oracle doc for full list of pre-reqs. Create the PDB, the new PDB is named PDB1KRMT.
SQL> create pluggable database pdb1krmt from pdb1k@pdb1k_link; SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ONEPDB READ WRITE NO 4 TWOPDB READ WRITE NO 5 PDB1KRMT MOUNTEDFinally open the PDB
SQL> alter pluggable database pdb1krmt open; SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ONEPDB READ WRITE NO 4 TWOPDB READ WRITE NO 5 PDB1KRMT READ WRITE NOVerify the PDB data files are created in the intended location
SQL> select name from v$datafile; NAME ------------------------------------------------------------------------------------------------------------------------ /opt/app/oracle/oradata/CGCDB/datafile/o1_mf_undotbs1_cvchzywd_.dbf /opt/app/oracle/oradata/remoteclones/CGCDB/3B5F12ED0B6F4762E0536300A8C0A85F/datafile/o1_mf_system_cwfq6dd5_.dbf /opt/app/oracle/oradata/remoteclones/CGCDB/3B5F12ED0B6F4762E0536300A8C0A85F/datafile/o1_mf_sysaux_cwfq6ddt_.dbf /opt/app/oracle/oradata/remoteclones/CGCDB/3B5F12ED0B6F4762E0536300A8C0A85F/datafile/o1_mf_pdb1ktbs_cwfq6ddv_.dbfUsing the USER_TABLESPACES clause available on 12.1.0.2 it is possible to clone the new PDB only with a subset of tablespaces. Assume that original PDB has 3 application specific tablespaces.
SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE TEMP ONLINE APP1 ONLINE APP2 ONLINE APP3 ONLINEOnly 2 of them are wanted in the newly cloned PDB. It is possible to include just these two tablespaces in the user_tablespaces clause excluding all other tablespaces.
create pluggable database pdb1krmt from pdb1k@pdb1k_link USER_TABLESPACES=('APP1','APP3');Tablespace name exists but status will be offline with data file missing as well.
SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE TEMP ONLINE APP1 ONLINE APP2 OFFLINE APP3 ONLINE SQL> select tablespace_name,status,file_name from dba_data_files; TABLESPACE_NAME STATUS FILE_NAME ------------------------------ --------- ---------------------------------------------------------------------------------------------------- SYSTEM AVAILABLE /opt/app/oracle/oradata/CGCDB/3BED1E0F3E63672CE0536300A8C0356F/datafile/o1_mf_system_cx0bpkwo_.dbf SYSAUX AVAILABLE /opt/app/oracle/oradata/CGCDB/3BED1E0F3E63672CE0536300A8C0356F/datafile/o1_mf_sysaux_cx0bpkwy_.dbf APP1 AVAILABLE /opt/app/oracle/oradata/CGCDB/3BED1E0F3E63672CE0536300A8C0356F/datafile/o1_mf_app1_cx0bpkwz_.dbf APP3 AVAILABLE /opt/app/oracle/oradata/CGCDB/3BED1E0F3E63672CE0536300A8C0356F/datafile/o1_mf_app3_cx0bpkx1_.dbf APP2 AVAILABLE /opt/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00122Offline tablespace could be dropped to clean up the new PDB
SQL> drop tablespace app2 including contents and datafiles cascade constraints;Same could be done when plugging non-CDB as PDBs as well.
SQL> create pluggable database stdpdb from std12c1@std_link USER_TABLESPACES=('APP1','APP3');Run the post cloning steps and verify the tablespace list
SQL> select tablespace_name,status from dba_tablespaces order by 2,1; TABLESPACE_NAME STATUS ------------------------------ --------- APP2 OFFLINE TOOLS OFFLINE UNDOTBS1 OFFLINE USERS OFFLINE APP1 ONLINE APP3 ONLINE SYSAUX ONLINE SYSTEM ONLINE TEMP ONLINE SQL> select tablespace_name,status,file_name from dba_data_files; TABLESPACE_NAME STATUS FILE_NAME ------------------------------ --------- ---------------------------------------------------------------------------------------------------- SYSTEM AVAILABLE /opt/app/oracle/oradata/CGCDB/3BEC00E8AA7862ECE0536300A8C0B5F7/datafile/o1_mf_system_cx060v17_.dbf SYSAUX AVAILABLE /opt/app/oracle/oradata/CGCDB/3BEC00E8AA7862ECE0536300A8C0B5F7/datafile/o1_mf_sysaux_cx060v18_.dbf USERS AVAILABLE /opt/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00114 TOOLS AVAILABLE /opt/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00115 APP1 AVAILABLE /opt/app/oracle/oradata/CGCDB/3BEC00E8AA7862ECE0536300A8C0B5F7/datafile/o1_mf_app1_cx060v1b_.dbf APP2 AVAILABLE /opt/app/oracle/product/12.1.0/dbhome_2/dbs/MISSING00117 APP3 AVAILABLE /opt/app/oracle/oradata/CGCDB/3BEC00E8AA7862ECE0536300A8C0B5F7/datafile/o1_mf_app3_cx060v1c_.dbfUndo tablespace within the PDB cannot be removed (2067414.1).
SQL> drop tablespace UNDOTBS1 including contents and datafiles cascade constraints; drop tablespace UNDOTBS1 including contents and datafiles cascade constraints * ERROR at line 1: ORA-30013: undo tablespace 'UNDOTBS1' is currently in useOffline undo tablespace in this case is the undo tablespace on non-CDB. This is because the CDB undo tablespace name and cloned non-CDB tablespace name is the same and undo is not local to PDB but common to entire CDB. It was not possible to get rid of the undotbs1 offline status even after switching the default undo tablespace of the CDB to a different undo tablespace.
This clause does not apply to the SYSTEM, SYSAUX, or TEMP tablespaces.
Related Posts
Move a PDB Between Servers
Plugging a SE2 non-CDB as an EE PDB Using File Copying and Remote Link