Creating PDB and Import Data
[oracle@localhost ~]$ ps -ef | grep pmon
oracle 1756 1 0 23:16 ? 00:00:00 ora_pmon_cdb1
oracle 2538 1 0 23:19 ? 00:00:00 ora_pmon_cdb2
oracle 2970 2945 0 23:37 pts/1 00:00:00 grep pmon
[oracle@localhost ~]$ cd /u01/app/oracle/oradata/cdb2
[oracle@localhost cdb2]$ ls -lrth
[oracle@localhost cdb2]$ mkdir pdb3
[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [oracle] ? cdb2
The Oracle base has been set to /u01/app/oracle
[oracle@localhost cdb2]$ sqlplus /nolog
SQL> connect sys/oracle@192.168.56.101:1521/cdb2 as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE pdb3
ADMIN USER pdb3_admin
IDENTIFIED BY pdb3 ROLES=(CONNECT)
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb2/pdbseed', '/u01/app/oracle/oradata/cdb2/pdb3');
Pluggable database created.
(OR) - > If it is OMF format check the below script
SQL> connect sys/oracle@192.168.56.101:1521/cdb3 as sysdba
Connected.
SQL> CREATE PLUGGABLE DATABASE pdb3
ADMIN USER pdb3_admin
IDENTIFIED BY pdb3 ROLES=(CONNECT)
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB3/datafile', '/u01/app/oracle/oradata/CDB3/pdb3/datafile/');
2 3 4 CREATE PLUGGABLE DATABASE pdb3
*
ERROR at line 1:
ORA-01276: Cannot add file
/u01/app/oracle/oradata/CDB3/pdb3/datafile//o1_mf_system_b7chl7yq_.dbf. File
has an Oracle Managed Files file name.
SQL> CREATE PLUGGABLE DATABASE pdb3
2 ADMIN USER pdb3_admin
3 IDENTIFIED BY pdb3 ROLES=(CONNECT)
4 FILE_NAME_CONVERT=('/u01/app/oracle/oradata/CDB3/datafile/pdbseed_temp01.dbf',
'/u01/app/oracle/oradata/CDB3/pdb3/datafile/temp01.dbf',
5 '/u01/app/oracle/oradata/CDB3/datafile/o1_mf_system_b7chl7yq_.dbf',
'/u01/app/oracle/oradata/CDB3/pdb3/datafile/system01.dbf',
6 '/u01/app/oracle/oradata/CDB3/datafile/o1_mf_sysaux_b7chl7xy_.dbf',
'/u01/app/oracle/oradata/CDB3/pdb3/datafile/sysaux01.dbf');
Pluggable database created.
SQL> !
[oracle@localhost datafile]$ cd /u01/app/oracle/oradata/CDB3/pdb3/datafile/
[oracle@localhost datafile]$ ls -lrth
total 841M
-rw-r-----. 1 oracle oinstall 21M Nov 26 14:12 temp01.dbf
-rw-r-----. 1 oracle oinstall 251M Nov 26 14:12 system01.dbf
-rw-r-----. 1 oracle oinstall 591M Nov 26 14:12 sysaux01.dbf
[oracle@localhost datafile]$
Check the open mode of PDB3 in CDB2:
====================================
SQL> select CON_ID, NAME, OPEN_MODE from v$pdbs;
CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 PDB3 MOUNTED
SQL> alter pluggable database pdb3 open;
Pluggable database altered.
SQL> exit
Note: Add the entries in tnsnames.ora for the database ‘PDB3’
[oracle@localhost cdb2]$ cd /u01/app/oracle/product/12.1.0/dbhome_1/network/admin
[oracle@localhost admin]$ ls -lrth
[oracle@localhost admin]$ vi tnsnames.ora
[oracle@localhost admin]$ exit
SQL> !lsnrctl services
Connect to PDB3 under the pdb3_admin user
=========================================
SQL> connect pdb3_admin/pdb3@pdb3
Connected.
List the datafiles created
===========================
SQL> !ls /u01/app/oracle/oradata/cdb2/pdb3
pdbseed_temp01.dbf sysaux01.dbf system01.dbf
Before importing data into pdb3 & Create the directory on the filesystem.
=========================================================================
SQL> !mkdir /u01/app/oracle/oradata/cdb2/pdb3/dump
SQL> !ls /u01/app/oracle/oradata/cdb2/pdb3/dump
SQL> exit
Create a directory in noncdb
=============================
[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [cdb2] ? noncdb
[oracle@localhost cdb2]$ sqlplus /nolog
SQL> connect sys/oracle as sysdba
SQL> startup;
SQL> exit
[oracle@localhost cdb2]$ sqlplus /nolog
SQL> connect system/oracle@192.168.56.101:1521/noncdb
Connected.
SQL> create directory dpdump_pdb1 as '/u01/app/oracle/oradata/cdb2/pdb3/dump';
Directory created.
SQL> grant read, write on directory dpdump_pdb1 to PUBLIC;
Grant succeeded.
SQL> connect sys/oracle@192.168.56.101:1521/pdb3 as sysdba
Connected.
SQL> create directory dpdump_pdb1 as '/u01/app/oracle/oradata/cdb2/pdb3/dump';
Directory created.
SQL> grant read, write on directory dpdump_pdb1 to PUBLIC;
Grant succeeded.
SQL> create user SOE identified by soe;
User created.
SQL> grant dba to SOE;
Grant succeeded.
SQL> GRANT EXECUTE on DBMS_LOCK to PUBLIC;
Grant succeeded.
SQL> connect system/oracle@192.168.56.101:1521/noncdb
Connected.
SQL> create user SOE identified by soe;
User created.
SQL> grant dba to SOE;
Grant succeeded.
SQL> connect sys/oracle@192.168.56.101:1521/noncdb as sysdba
Connected.
SQL> GRANT EXECUTE on DBMS_LOCK to PUBLIC;
Grant succeeded.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/noncdb/system01.dbf
/u01/app/oracle/oradata/noncdb/sysaux01.dbf
/u01/app/oracle/oradata/noncdb/undotbs01.dbf
/u01/app/oracle/oradata/noncdb/users01.dbf
/u01/app/oracle/oradata/noncdb/example01.dbf
/u01/app/oracle/oradata/noncdb/sh_001.dbf
/u01/app/oracle/oradata/noncdb/sh_index_001.dbf
/u01/app/oracle/oradata/noncdb/ilmtbs1.dbf
/u01/app/oracle/oradata/noncdb/lowcoststore1.dbf
9 rows selected.
SQL> alter tablespace example read only;
Tablespace altered.
SQL> exit
[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [noncdb] ? noncdb
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost cdb2]$ expdp system/oracle DIRECTORY= dpdump_pdb1 DUMPFILE=tts_mydump TRANSPORT_TABLESPACES=EXAMPLE TRANSPORT_FULL_CHECK=YES
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory= DUMPFILE=tts_mydump TRANSPORT_TABLESPACES=EXAMPLE TRANSPORT_FULL_CHECK=YES
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/oradata/cdb2/pdb3/dump/tts_mydump.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
/u01/app/oracle/oradata/noncdb/example01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Wed Nov 12 04:21:44 2014 elapsed 0 00:01:37
Note: Before importing the tablespace, copy the datafile from noncbd to pdb3 directory
[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [noncdb] ? cdb2
[oracle@localhost cdb2]$ cp /u01/app/oracle/oradata/noncdb/example01.dbf /u01/app/oracle/oradata/cdb2/pdb3/example01.dbf
[oracle@localhost cdb2]$ sqlplus /nolog
SQL> connect sys/oracle@192.168.56.101:1521/pdb3 as sysdba
Connected.
SQL> create user hr identified by oracle;
User created.
SQL> grant dba to hr;
Grant succeeded.
SQL> GRANT EXECUTE on DBMS_LOCK to PUBLIC;
Grant succeeded.
SQL> grant connect,resource to hr;
Grant succeeded.
SQL> create user pm identified by oracle;
SQL> create user oe identified by oracle;
SQL> create user ix identified by oracle;
SQL> exit
[oracle@localhost cdb2]$ . oraenv
ORACLE_SID = [cdb2] ? cdb2
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost cdb2]$ impdp system/oracle@PDB3 DIRECTORY=dpdump_pdb1 DUMPFILE=tts_mydump TRANSPORT_DATAFILES='/u01/app/oracle/oradata/cdb2/pdb3/example01.dbf'
Import: Release 12.1.0.1.0 - Production on Wed Nov 12 04:31:49 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source timezone version is +00:00 and target timezone version is -07:00.
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@PDB3 DIRECTORY=dpdump_pdb1 DUMPFILE=tts_mydump TRANSPORT_DATAFILES=/u01/app/oracle/oradata/cdb2/pdb3/example01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 33 error(s) at Wed Nov 12 04:32:55 2014 elapsed 0 00:01:04
[oracle@localhost cdb2]$ sqlplus /nolog
SQL> connect sys/oracle@192.168.56.101:1521/pdb3 as sysdba
Connected.
SQL> connect hr/oracle@pdb3
Connected.
SQL> select table_name from user_tables ;
TABLE_NAME
--------------------------------------------------------------------------------
JOB_HISTORY
EMPLOYEES
DEPARTMENTS
LOCATIONS
REGIONS
JOBS
COUNTRIES
7 rows selected.
SQL> connect sys/oracle@192.168.56.101:1521/noncdb as sysdba
Connected.
SQL> connect hr/hr@noncdb
Connected.
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
COUNTRIES
JOB_HISTORY
EMPLOYEES
JOBS
DEPARTMENTS
LOCATIONS
REGIONS
EMP_TEMP
8 rows selected.
SQL> connect sys/oracle@192.168.56.101:1521/noncdb as sysdba
Connected.
SQL> alter tablespace example read write;
Tablespace altered.
Cheers !!!!!!
