oracle11g database EXPDP, IMPDP use example

EXPDP Backup DATABASE, EXPDP/IMPDP simple test

• Operating system level Create directory

[Email protected] ~]# mkdir-p/home/oracle/db_back/

• Modify the owning user of the directory, the owning group

[Email protected] ~]# chown-r oracle:oinstall/home/oracle/db_back/

• Grant the appropriate permissions to the directory

(the user should have read + Write + Execute permissions on the directory)

[Email protected] ~]# chmod 700/home/oracle/db_back/

• Creating a directory at the Oracle database level

sql> Create directory Ora_back as '/home/oracle/db_back/';

Directory created.

• Grant execution permissions to the appropriate user

Sql> Grant all on the directory ora_back to System;

Grant succeeded.

• Granting users full-Library export permissions

sql> Grant EXPORT full DATABASE to system;

Grant succeeded.

• To view a script that performs a full library export

[Email protected] scripts]$ cat expdp.sh

#!/bin/bash Export Oracle_base=/u01/app/oracle Export Oracle_home= $ORACLE _base/product/10.2.0/db_1 Export ORACLE_SID=ORCL EXPDP system/manager directory=ora_back dumpfile= $ORACLE _sid+ ' Date +%y+%m+%d+%h+%m+%s '. DMP full=y logfile= $ORACLE _ sid+ ' Date +%y+%m+%d+%h+%m+%s '. Log

• The following error actions and error analysis are included

Error Action 1

[Email protected] ~]# chmod 600/home/oracle/db_back/

[Email protected] scripts]$ sh expdp.sh

Export:release 10.2.0.1.0-64bit Production on Tuesday, December, 2014 16:05:46

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to:oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the partitioning, OLAP and Data Mining options

Ora-39002:invalid operation

Ora-39070:unable to open the log file.

Ora-29283:invalid file operation

Ora-06512:at "SYS. Utl_file ", line 475

Ora-29283:invalid file Operatio

Error Analysis:

Oracle user has read + Write access to this directory

--------------------------------------------------------------------------------------------------------------- -------------

Import and export tests:

• Test intent: The Scott user deletes the table emp and then performs an import operation to check whether the table is rebuilt. Whether the rebuilt table is in the moved table space

Sql> Conn Scott/tiger
Connected.
sql> drop table emp Purge;

Table dropped.

• [Email protected] scripts]$ VI expdp.sh content is as follows:

EXPDP system/oracle directory=ora_bak file=071501.dmp file=071502.dmp dumpfile=071501.dmp file=071502.dmp log=0715_ Full.log full=y filesize=300m

Specify the maximum size 300M for the exported file here, just to restore the friend feedback problem

• [Email protected] scripts]$ VI impdp.sh content is as follows:

IMPDP system/oracle directory=ora_bak dumpfile=071501.dmp dumpfile=071502.dmp remap_tablespace=users:test01 logfile= 0715_impdp.log table_exists_action=truncate

• Check whether the table emp is rebuilt, if the rebuilt tablespace is TEST01

Sql> Select Owner,table_name,tablespace_name from all_tables where owner= ' SCOTT ';

OWNER table_name Tablespace_name
------------------------------ ------------------------------ ------------------------------
SCOTT DEPT USERS
SCOTT Salgrade USERS
SCOTT Youyus USERS
SCOTT SALES USERS
SCOTT T USERS
SCOTT mlog$_t USERS
SCOTT EMP TEST01
SCOTT t_new
SCOTT rupd$_t
SCOTT SALES01
SCOTT BONUS USERS
SCOTT TEST TEST01

The result is the same as you expected!!

Ha ha

oracle11g database EXPDP, IMPDP use example