Oracle Data Pump examples and some precautions (expdp/impdp)

Recently, the system has to "scale down" because I don't want to say much about it. It is a very troublesome thing because we need to compress and store data.
 
It has nothing to do with the operating system. It mainly refers to Oracle Data.
 
Oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
 
In addition, the system has a RAC cluster with six nodes, and each table is partitioned by month or date.
 
I. Prerequisites:
 
1. Create a directory in the system to generate the dmp file (exported data file) (generally, root permission is required, and this free use of cmd_^ ):
 
Root @ bidb04 # mkdir-p/data/oracle_backup
Root @ bidb04 # chown oracle: oinstall/data/oracle_backup
 
In addition, no matter what user and password you use to log on to the exported and imported database, the database uses (in linux) oracle users to export files, therefore, this directory must grant the write permission to oracle users (in linux.
 
2. Define the directory for generating the dmp file (exported data file) in oracle:
 
Use sqlplus/as sysdba to enter sqlplus:

SQL> grant create any directory to scott; -- this step is not required.
SQL> create or replace directory oracle_backup as '/data/oracle_backup'; -- with the above step, the directory can also be created by the scott user. Of course, this is the case.
SQL> grant read, write on directory oracle_backup to scott; -- grant the directory access permission.
SQL> create or replace directory DATA_PUMP_DIR as '/oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/log/'; -- it seems that I have not created this way, the system has it.
SQL> grant read, write on directory DATA_PUMP_DIR to scott; -- same as above.
SQL> grant EXP_FULL_DATABASE to scott; -- Export Permission
SQL> grant IMP_FULL_DATABASE to scott; -- Import Permission

Ii. Data Pump Export (expdp ):

Expdp scott/********** @ bidb4TABLES = KR. TR_DATA_APP_2A3R_D: P20130101DIRECTORY = ORACLE_BACKUP DUMPFILE = KR. TR_DATA_APP_2A3R_D0101 % u. dmpLOGFILE = DATA_PUMP_DIR: KR. TR_DATA_APP_2A3R_D0101.logCONTENT = data_only CLUSTER = n parallel = 8 FILESIZE = 2g encryption = data_only ENCRYPTION_PASSWORD = password

Note:

#1. scott/********** @ bidb4 (red part) username and password and SID, which are consistent with the sqlplus login.
#2. TABLES (green part) Table Name (Table Name: partition)
#3. DIRECTORY (blue part) DIRECTORY Name of the exported file to be generated -- defined in oracle dba_directories (see the previous document for the definition method)
#4. The exported file name % u parameter generated by DUMPFILE (yellow part) indicates the suffix of each file, which is used together with parallel and filesize.
#5. LOGFILE (purple part) Name of the log file (log directory: log name, which is defined in oracle dba_directories) (For the definition method, see the previous article)
#6. CONTENT (orange part) CONTENT to be included
#7. Whether the CLUSTER (gray part) is used. The default value is Y. N is required here (see the notes below)
#8. PARALLEL (gray part) PARALLEL Processing
#9. FILESIZE (gray part) maximum size of a single file
#10. ENCRYPTION (gray part) ENCRYPTION option 1. Specify the encrypted content (the appendix contains optional description parameters)
#11. ENCRYPTION_PASSWORD (gray part) encryption option 2, specify the encryption key

 

Iii. Data Pump import (impdp ):

Impdp scott/********** @ bidb4 TABLES = KR. TR_DATA_APP_2A3R_D DIRECTORY = ORACLE_BACKUPDUMPFILE = KR. TR_DATA_APP_2A3R_D0101 % u. dmp LOGFILE = DATA_PUMP_DIR: IMP_KR.TR_DATA_APP_2A3R_D0101.log CONTENT = data_onlyCLUSTER = n parallel = 8 FILESIZE = 2G ENCRYPTION_PASSWORD = password

 

Note:

The parameter configuration is basically the same as that of expdp. There are mainly 2nd and 10th differences. In addition, it is best to modify the exported log name slightly, otherwise it will overwrite it.

#2. TABLES (green part) Table Name (as long as the table name is used, no partition is required, because the imported data already contains partition information)
#10. ENCRYPTION (no longer needed)

1 2 Next Page