In the multitenant database, For getting an export dump from the pluggable database, we need a follow a bit different process.
DEMO:
PLUGGABLE DATABASE(PDB) – PRE1
SCHEMA_NAME – DBACLASS ( this schema/user is present in PDB PRE1)
1. Make sure PDB service is registered in the listener.
$ lsnrctl status LISTENER_POC LSNRCTL for Solaris: Version 12.1.0.2.0 - Production on 07-NOV-2018 11:12:26 Copyright (c) 1991, 2014, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1523))) STATUS of the LISTENER ------------------------ Alias LISTENER_POC Version TNSLSNR for Solaris: Version 12.1.0.2.0 - Production Start Date 29-OCT-2018 09:25:38 Uptime 9 days 1 hr. 46 min. 49 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /export/home/oracle/product/12c/dbhome_1/network/admin/listener.ora Listener Log File /export/home/oracle/product/12c/diag/tnslsnr/localhost/listener_poc/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1523))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1523))) Services Summary... Service "POC" has 1 instance(s). Instance "POC", status READY, has 1 handler(s) for this service... Service "POCXDB" has 1 instance(s). Instance "POC", status READY, has 1 handler(s) for this service... Service "pre1" has 1 instance(s). --------------->>>>>>>>>>>>>>>>>>>> This is the PDB SERVICE NAME FOR PRE1 Instance "POC", status READY, has 1 handler(s) for this service... Service "pre2" has 1 instance(s). Instance "POC", status READY, has 1 handler(s) for this service... The command completed successfully
2. Add the entry in tnsnames.ora file for the pdb PRE1.
cat $ORACLE_HOME/network/admin/tnsnames.ora
PRE1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pre1)
)
)
3. Now create a directory for datapump under the PDB.(pre1)
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PRE1 READ WRITE NO
4 PRE2 READ WRITE NO
SQL> alter session set container=PRE1;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PRE1 READ WRITE NO
SQL> create directory PDB_EXPDIR as '/export/home/oracle';
Directory created.
4. Now run the EXPDP:
$ expdp DBACLASS/DBACLASS@pre1 dumpfile=dbaclass.dmp logfile=dbaclass.log directory=PDB_EXPDIR SCHEMAS=DBACLASS schemas=DBACLASS
expdp DBACLASS/DBACLASS@pre1 dumpfile=dbaclass.dmp logfile=dbaclass.log directory=PDB_EXPDIR SCHEMAS=DBACLASS schemas=DBACLASS Export: Release 12.1.0.2.0 - Production on Wed Nov 7 11:18:57 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "DBACLASS"."SYS_EXPORT_SCHEMA_01": DBACLASS/********@pre1 dumpfile=dbaclass.dmp logfile=dbaclass.log directory=PDB_EXPDIR SCHEMAS=DBACLASS schemas=DBACLASS Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 13 MB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER . . exported "DBACLASS"."TEST1" 10.37 MB 90968 rows Master table "DBACLASS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for DBACLASS.SYS_EXPORT_SCHEMA_01 is: /export/home/oracle/dbaclass.dmp Job "DBACLASS"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Nov 7 11:19:55 2018 elapsed 0 00:00:51
We have successfully ran expdp in pluggable database. Similarly for import only we can follow same steps.
Make sure you set the environment for pluggable db , from os level , else it will take the container db and export will fail.
$export ORACLE_PDB_SID=PRE1
Dear Alok,
We are using the tns alias in the expdp command,
$ expdp DBACLASS/DBACLASS@pre1
Yes. the tns names solved the instance problem.
Environment variable ORACLE_PDB_SID is not working on 19.18 DB version.Not able to export tables with out password as SYS user.Tables are in PDB.