Recently, I joined two project teams, FALABELLA and FM, to support Oracle DB. Because the project is in the development stage, there are always some database replication work. I compared several methods, it seems that the use of EXPDP/IMPDP is not bad, it can smoothly meet the needs of developers.
Requirement: migrate data at the user/schema level.
Version: Oracle Enterprise 11g R2
In general, there are several steps:
1. view the DUMP directory of the original and target databases.
- SQL>Select*FromDba_directories;
- Sys subdir/opt/oracle/app/oracle/product/11.2.0/dbhome_1/demo/Schema/Order_entry/2002/Sep
- SYS SS_OE_XMLDIR/opt/oracle/app/oracle/product/11.2.0/dbhome_1/demo/Schema/Order_entry/
- SYS LOG_FILE_DIR/opt/oracle/app/oracle/product/11.2.0/dbhome_1/demo/Schema/Log/
- SYS DATA_FILE_DIR/opt/oracle/app/oracle/product/11.2.0/dbhome_1/demo/Schema/Sales_history/
- Sys xmldir/ade/B/2125410156/oracle/rdbms/xml
- SYS MEDIA_DIR/opt/oracle/app/oracle/product/11.2.0/dbhome_1/demo/Schema/Product_media/
- SYS DATA_PUMP_DIR/opt/oracle/app/oracle/admin/oracle/dpdump/
- SYS ORACLE_OCM_CONFIG_DIR/opt/oracle/app/oracle/product/11.2.0/dbhome_1/Cr/state
- 8RowsSelected.
AboveDATA_PUMP_DIRIt is the DUMP directory that can be used directly. The exported and imported files are placed in the above path. If not, you need to manually create and grant read/write permissions.
- SQL> CONN sys/AsSysdba
- SQL>CREATEDIRECTORY DATA_PUMP_DIRAS '/Opt/oracle/app/oracle/admin/oracle/dpdump /';
- SQL>GRANT READ, WRITEONDIRECTORYTOTEST;
2. Compare the username and tablespace of the original and target Databases
If the target database does not contain the username and tablespace to be imported, create username and tablespace in the target database.
- SQL>SelectUsername, default_tablespaceFromDba_users;-- View the users in the original database and the corresponding default tablespace
- SQL>CreateTablespace test_tbs datafile'/Opt/oracle/oradata/test/test_tbs01.dbf' Size1024 m autoextendOn;
- SQL>Create UserTest identifiedByTestDefaultTablespace test_tbs;
3. Export the script
- Expdp system/oracle @ orcl DIRECTORY = DATA_PUMP_DIR DUMPFILE = FALABELLA_20111014.DMP SCHEMAS = FALABELLA_CL_CATA, FALABELLA_CL_CATB, export logfile = export status = 10 parallel = 4 CONTENT =ALL
4. Import script
- -- Import data from one schema to another schema
- Impdp system/oracle @ test directory = dump_dir DUMPFILE = ATG_12OCT11.dmp LOGFILE = export SCHEMAS = QA2_ATGPUB_MF, export REMAP_SCHEMA = QA2_ATGPUB_MF: QA2_ATGPUB_MF, export: Export CONTENT =ALLPARALLEL = 4 STATUS = 10 TABLE_EXISTS_ACTION =REPLACE
- -- Schema does not need to be renamed.
