Restore a schema from full expdp backup

Import schema from full db expdp backup:

In some situations you might want to restore a single schema from entire EXPDP backup. In this example I want to explain how to import a single schema from full DB expdp backup.





Lets backup the full database using the EXPDP:

F:\>expdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8

Export: Release 11.2.0.4.0 - Production on Tue May 17 14:55:21 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_EXPORT_SCHEMA_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb.log full=y compression=all parallel=8

.
.
.
.
.
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
. . exported "SCOTT"."DEPT"                             4.976 KB       4 rows
. . exported "SCOTT"."EMP"                              5.625 KB      14 rows
. . exported "SCOTT"."SALGRADE"                         4.890 KB       5 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
.
.
.
.
.
Master table "ATOORPU"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATOORPU.SYS_EXPORT_FULL_01 is:
  C:\DPUMP\FULLDB_01.DMP
  C:\DPUMP\FULLDB_02.DMP
  C:\DPUMP\FULLDB_03.DMP
  C:\DPUMP\FULLDB_04.DMP
  C:\DPUMP\FULLDB_05.DMP
  C:\DPUMP\FULLDB_06.DMP
  C:\DPUMP\FULLDB_07.DMP
  C:\DPUMP\FULLDB_08.DMP
Job "ATOORPU"."SYS_EXPORT_FULL_01" successfully completed at Tue May 17 14:27:13 2016 elapsed 0 00:05:36

Restore schema into a temp SCOTT1 schema:


Now that I have completed my full DB backup. I want to restore just SCOTT schema from full backup into a temp schema called SCOTT1. For this I will need to pass two parameters
1. schemas = Schema name you want to import
2. remap_schema= This is to remap that schema to a temp SOCTT1 schema


F:\>impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log schemas=SCOTT remap_schema=SCOTT:SCOTT1

Import: Release 11.2.0.4.0 - Production on Tue May 17 14:57:21 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_SCHEMA_01":  atoorpu/******** directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log schemas=SCOTT remap_schema=SCOTT:SCOTT1
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "SCOTT1"."DEPT"                             4.976 KB       4 rows
. . imported "SCOTT1"."EMP"                              5.625 KB      14 rows
. . imported "SCOTT1"."SALGRADE"                         4.890 KB       5 rows
. . imported "SCOTT1"."BONUS"                                0 KB       0 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue May 17 14:57:34 2016 elapsed 0 00:00:08


Restore SCOTT into SCOTT schema and replace exiting tables :

If you want to restore it with same schema SCOTT and replace existing tables use this


impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log schemas=SCOTT ABLE_EXISTS_ACTION=REPLACE


If you want to restore it with same schema SCOTT but append (add) rows to existing tables use this
impdp atoorpu directory=dpump dumpfile=fulldb_%U.dmp logfile=fulldb_imp.log schemas=SCOTT ABLE_EXISTS_ACTION=APPPEND