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 - EXATREE ( 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 EXATREE/[email protected] dumpfile=exatree.dmp logfile=exatree.log directory=PDB_EXPDIR SCHEMAS=EXATREE schemas=EXATREE
expdp EXATREE/[email protected] dumpfile=exatree.dmp logfile=exatree.log directory=PDB_EXPDIR SCHEMAS=EXATREE schemas=EXATREE
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 "EXATREE"."SYS_EXPORT_SCHEMA_01": EXATREE/********@pre1 dumpfile=exatree.dmp logfile=exatree.log directory=PDB_EXPDIR SCHEMAS=EXATREE schemas=EXATREE
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 "EXATREE"."TEST1" 10.37 MB 90968 rows
Master table "EXATREE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EXATREE.SYS_EXPORT_SCHEMA_01 is:
/export/home/oracle/exatree.dmp
Job "EXATREE"."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.
EXPDPmultitenantPDB BACKUP & RECOVERYORACLE 12C