Thursday, June 2, 2016

Creating Pluggable Database (PDB) and import data to PDB


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 !!!!!!

No comments:

Post a Comment