advertisements
_____________________________________________________________________________________________________________________
First
step is you have to create an OS level directory and database level directory
for the export import activity. You can ignore this step if you have the
directories available already.
You can verify the directory details in dba_directories data dictionary view.
You can verify the directory details in dba_directories data dictionary view.
D:\>mkdir
export
D:\>cd
export
D:\export>sqlplus
/ as sysdba
SQL>
create directory exp_dir as 'd:\export';
Directory
created.
Grant
privilege to the user going invoke the export the database. Grant export,
import, read & write privileges on the directory.
SQL>
grant exp_full_database to test;
Grant
succeeded.
SQL>
grant imp_full_database to test;
Grant
succeeded.
SQL>
grant read, write on directory exp_dir to test;
Grant
succeeded.
Full Database Export
expdp
directory=exp_dir full=y dumpfile=exp_fulldb.dmp
logfile=exp_fulldb.log
Schema Level Export
expdp
directory=exp_dir schemas=scott,test dumpfile=exp_schemadb.dmp
logfile=exp_schemadb.log
Table Level Export
expdp
directory=exp_dir tables=scott.emp,test.bonus
dumpfile=exp_table.dmp logfile=exp_table.log
Syntax and example for full
database export using data pump.
Compression
parameter is not a mandatory one. But it is advisable to take full database export
using compression option which will reduce the dump file size.
Syntax
expdp
directory=exp_dir full=y compression=all dumpfile=exp_fulldb.dmp
logfile=exp_fulldb.log
Example
D:\>expdp
directory=exp_dir full=y compression=all dumpfile=exp_fulldb.dmp logfi
le=exp_fulldb.log
Export:
Release 11.2.0.1.0 - Production on Mon Feb 25 11:45:50 2013
Copyright
(c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
Username:
/ as sysdba
;;;
Connected
to: Oracle Database 11g Enterprise
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting
"SYS"."SYS_EXPORT_FULL_01":
/******** AS SYSDBA directory=exp_dir full=y compression=all
dumpfile=exp_fulldb.dmp logfile=exp_fulldb.log
Estimate
in progress using BLOCKS method...
Processing
object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total
estimation using BLOCKS method: 127.8 MB
Processing
object type DATABASE_EXPORT/TABLESPACE
Processing
object type DATABASE_EXPORT/PROFILE
Processing
object type DATABASE_EXPORT/SYS_USER/USER
Processing
object type DATABASE_EXPORT/SCHEMA/USER
. .
. . . .
. .
. . . .
. .
. . . .
. .
. . . .
. .
. . . .
. .
. . . .
. .
exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows
. .
exported "TEST"."BONUS" 0 KB 0 rows
Master
table "SYS"."SYS_EXPORT_FULL_01" successfully
loaded/unloaded
******************************************************************************
Dump
file set for SYS.SYS_EXPORT_FULL_01 is:
D:\EXPORT\EXP_FULLDB.DMP
Job
"SYS"."SYS_EXPORT_FULL_01" successfully completed at
11:44:52
Syntax and example for schema level
export using data pump
For
taking the schema level backup you have to use the schemas parameter with the
data pump. You can specify more than one schema by separating each with comma (,).
Syntax
expdp
directory=exp_dir schemas=scott,test compression=all
dumpfile=exp_schemadb.dmp logfile=exp_schemadb.log
Example
D:\>expdp
directory=exp_dir schemas=scott,test compression=all dumpfile=exp_sche
madb.dmp
logfile=exp_schemadb.log
Export:
Release 11.2.0.1.0 - Production on Mon Feb 25 12:27:23 2013
Copyright
(c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
Username:
test
Password:
Connected
to: Oracle Database 11g Enterprise
tion
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting
"TEST"."SYS_EXPORT_SCHEMA_01": test/******** directory=exp_dir schemas
=scott,test
compression=all dumpfile=exp_schemadb.dmp logfile=exp_schemadb.log
Estimate
in progress using BLOCKS method...
Processing
object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total
estimation using BLOCKS method: 384 KB
. .
. . .
. .
. . .
Processing
object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing
object type SCHEMA_EXPORT/TABLE/COMMENT
Processing
object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing
object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. .
exported "SCOTT"."DEPT" 4.976 KB 4 rows
. .
exported "SCOTT"."EMP" 5.664 KB 14 rows
. .
exported "SCOTT"."SALGRADE" 4.898 KB 5 rows
. .
exported "TEST"."DEPT" 4.976 KB 4 rows
. .
exported "TEST"."EMP" 5.593 KB 14 rows
. .
exported "TEST"."SALGRADE" 4.890 KB 5 rows
. .
exported "SCOTT"."BONUS" 0 KB 0 rows
. .
exported "TEST"."BONUS" 0 KB 0 rows
Master
table "TEST"."SYS_EXPORT_SCHEMA_01" successfully
loaded/unloaded
******************************************************************************
Dump
file set for TEST.SYS_EXPORT_SCHEMA_01 is:
D:\EXPORT\EXP_SCHEMADB.DMP
Job
"TEST"."SYS_EXPORT_SCHEMA_01" successfully completed at
12:28:27
Syntax and example for table level
export using data pump
For
table level export you can use the parameter tables and you can
specify the tables from multiple schemas which are separated by comma.
D:\>expdp
directory=exp_dir tables=scott.emp,test.bonus
compression=all dumpfile
=exp_table.dmp
logfile=exp_table.log
Export:
Release 11.2.0.1.0 - Production on Mon Feb 25 12:31:08 2013
Copyright
(c) 1982, 2009, Oracle and/or its affiliates.
All rights reserved.
Username:
test
Password:
Connected
to: Oracle Database 11g Enterprise
tion
With
the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting
"TEST"."SYS_EXPORT_TABLE_01": test/******** directory=exp_dir tables=s
cott.emp,test.bonus
compression=all dumpfile=exp_table.dmp logfile=exp_table.log
Estimate
in progress using BLOCKS method...
Processing
object type TABLE_EXPORT/TABLE/TABLE_DATA
Total
estimation using BLOCKS method: 64 KB
Processing
object type TABLE_EXPORT/TABLE/TABLE
Processing
object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing
object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing
object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing
object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing
object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. .
exported "SCOTT"."EMP" 5.664 KB 14 rows
. .
exported "TEST"."BONUS" 0 KB 0 rows
Master
table "TEST"."SYS_EXPORT_TABLE_01" successfully
loaded/unloaded
******************************************************************************
Dump
file set for TEST.SYS_EXPORT_TABLE_01 is:
D:\EXPORT\EXP_TABLE.DMP
Job
"TEST"."SYS_EXPORT_TABLE_01" successfully completed at
12:31:45
How to recover database from Full
database dump using impdp?
Method 1:
If
you have same kind of file system available on the target system you can
proceed with following steps. If you have different files systems available on
the target system, you have to create the tablespaces before importing the
dump.
Step 1.
Create a
general purpose database using the dbca.
Step 2.
Import the full database dump using table_exists_action=skip option. This
parameter will skip all the objects already exist in the database. So,
whichever schema is available as new in the export dump will get import to the
database.
impdp
directory=exp_dir full=y dumpfile=exp_fulldb.dmp logfile=imp_fulldb.log
table_exists_action=skip
Method 2:
If
you know the important/required application schemas in the source database,
then you can use the schemas parameter during import instead of going for the
full import. This method will not try to import the default system schemas like
sys, system etc. Like in method 1, If
you have different files systems available on the target system, you have to
create the tablespaces before importing the dump.
Step1.
Create a
general purpose database using the dbca.
Step 2.
impdp
directory=exp_dir schemas=app_schema,testschema dumpfile=exp_fulldb.dmp
logfile=imp_schemadb.log
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment