ORACLE import/export

Attention: there is no ";" at the end of exdp/impdp command

如果是大规模的导入建议关闭归档，或者提前确认空间。

sql>shutdown immediate;

sql>startup nomout;

sql>alter database noarchivelog;

sql>alter database open;

 

备份类型：逻辑备份 /  物理备份(冷、热)

逻辑备份

ORACLE 9i 脚本在哪里运行，文件就产生在哪里。exp对应imp，确保服务器与客户端字符集一致

exp scott/tiger@demo Grants=Y Compress=Y Consistent =Y Indexes = Y Constraints =Y Rows = Y Triggers =Y file= d:\file.dmp tables=(dept,emp)
imp scott/tiger@demo Analyze=Y Commit=Y Constraints=Y Grants=Y Ignore=Y Indexes=Y Rows=Y file= d:\file.dmp tables=(dept,emp)

ORACLE 10g 脚本只产生在服务器上。expdp对应impdp

1. 创建ORACLE Directory

OS：mkdir /tmp/movedata

sys用户：

SQL>grant create any directory to hr;

hr用户：

SQL>create or replace directory  datamove as '/tmp/movedata';

查看：select * from dba_directories

删除：drop directory datamove

授权其他用户使用directory：grant read,write on directory <directory_name> to <username>

2. 导出：

全库：expdp system/<password> DIRECTORY=datamove DUMPFILE=expfull.dmp FULL=y LOGFILE=expfull.log

SCHEMA：expdp system/<password> DIRECTORY=datamove DUMPFILE=schema_exp.dmp SCHEMAS=hr,sh,oe

exclude include表：expdp system/<password> DIRECTORY=datamove DUMPFILE=72_tables_20120309.dmp logfile=tables_20120309.log schemas=nissancms EXCLUDE=<condition>

Attention:1. When you use exclude, you may estimate size first. And the estimate size should be small compare with no exclude.

2. Table in exclude no need owner

Example: exclude the table whose name contains ANALYSE or REPORT

Linux/Unix Platform

expdp system/<password> DIRECTORY=datamove DUMPFILE=72_tables_20120309.dmp logfile=tables_20120309.log schemas=nissancms EXCLUDE=table:\"LIKE \'%ANALYSE%\'\" EXCLUDE=table:\"LIKE \'%REPORT%\'\" 

Windos Platformexpdp system/<password> DIRECTORY=datamove DUMPFILE=72_tables_20120309.dmp logfile=tables_20120309.log schemas=nissancms EXCLUDE=table:\"LIKE '%ANALYSE%'\" EXCLUDE=table:\"LIKE '%REPORT%'\" 

Example: first export table trader, transaction, offline_trans_buffer, and export others

expdp system/syspr0gr3ss DIRECTORY=EXPORT_DIR DUMPFILE=TRADER_20140319.dmp TABLES=THOR.TRADER logfile=TRADER_20140319.log flashback_scn='13642729581'

expdp system/syspr0gr3ss DIRECTORY=EXPORT_DIR DUMPFILE=TRANSACTION_20140319.dmp TABLES=THOR.TRANSACTION logfile=TRANSACTION_20140319.log flashback_scn='13642729581'

expdp system/syspr0gr3ss DIRECTORY=EXPORT_DIR DUMPFILE=OFFLINE_TRANS_BUFFER_20140319.dmp TABLES=THOR.OFFLINE_TRANS_BUFFER logfile=OFFLINE_TRANS_BUFFER_20140319.log flashback_scn='13642729581'

expdp system/syspr0gr3ss directory=EXPORT_DIR_H dumpfile=THOR_20140319%U.dmp SCHEMAS=THOR EXCLUDE=table:\"IN ('TRADER','TRANSACTION','OFFLINE_TRANS_BUFFER')\" logfile=THOR_20140319.log flashback_scn='13642729581' FILESIZE=5G

导出单表，必须注明owner

table：expdp system/<password> DIRECTORY=datamove DUMPFILE=tables_exp.dmp TABLES=owner.employees,owner.jobs,owner.departments

Set the size of dumpfile, export into several files

Example

expdp system/syspr0gr3ss directory=EXPORT_DIR dumpfile=THOR_20140319%U.dmp SCHEMAS=THOR logfile=THOR_20140319.log flashback_scn='13635413306' FILESIZE=5G

1. 运行过程中可以暂停：Ctrl+c
2. 查看：select * from dba_datapump_jobs

在执行过程中，可以按Ctrl+C组合键退出当前交互模式，退出之后，导出操作不会停止，这不同于Oracle以前的EXP。以前的EXP，如果退出交互式模式，就会出错终止导出任务。由于EXPDP是数据库内部定义的任务，已经与客户端无关，退出交互之后会进入export的命令行模式，此时支持status等查看命令：

Export> status                 --查看当前JOB的状态及相关信息

DATA PUMP与传统的EXP/IMP相比它还可以对正在运行的JOB进行停止和启动：

Export> stop_job           --暂停JOB

此时通过status命令查看到这时JOB的状态值是UNDEFINED

Export> start_job           --重启暂停的JOB

Export> kill_job             --取消当前的JOB并释放相关客户会话

Export> continue_client     --通过此命令查看已连接JOB的日志

Export> exit_client          --通过此命令退出export模式

--已退出export模式后再次连接去查看JOB的状态用以下命令： [oracle@LinuxRedHat expdpdump]$ expdp system/mingyue attach;

3. 导入：
全库：impdp system/<password> DIRECTORY=datamove DUMPFILE=expfull.dmp FULL=y LOGFILE=impfull.og
SCHEMA：impdp system/<password> DIRECTORY=datamove DUMPFILE=schema_exp.dmp SCHEMAS=hr,sh,oe
table：impdp system/<password> DIRECTORY=datamove DUMPFILE=tables_exp.dmp TABLES=employees,jobs,departments
4. Import parameter

TABLE_EXISTS_ACTION

        该选项用于指定当表已经存在时导入作业要执行的操作,默认为SKIP

        TABBLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | FRPLACE }

        当设置该选项为SKIP时,导入作业会跳过已存在表处理下一个对象;

        当设置为APPEND时,会追加数据

        当设置为TRUNCATE时,导入作业会截断表,然后为其追加新数据;

        当设置为REPLACE时,导入作业会删除已存在表,重建表并追加数据,

        注意,TRUNCATE选项不适用与簇表和NETWORK_LINK选项

Pasted from: <http://blog.csdn.net/leshami/article/details/5926276>

 
5. 使用导入导出工具复制schema
a. 使用导出工具导出某个用户的schema：
expdp system/<password> DIRECTORY=datamove DUMPFILE=schema_exp.dmp SCHEMAS=hr
b. 创建新的表空间，新用户
创建新的表空间：create tablespace test datafile '/u01/app/oracle/oradata/orcl11g/test01.dbf' size 100M autoextend on next 10M;
创建用户：
create user hr1
  identified by hr1
  default tablespace test
  temporary tablespace TEMP;
c. 导入到另外一个用户，同一个表空间中（程序会自动将数据导入新用户hr1，但是仍旧是原来的表空间example）
impdp system/<system> directory=datamove dumpfile=schema_exp.dmp remap_schema=hr:hr1
d. 导入到另外一个用户，另外一个表空间中
impdp system/<system> directory=datamove dumpfile=schema_exp.dmp remap_schema=hr:hr1 remap_tablespace=example:test
e. 检查：select * from user_segments;
expdp/impdp实验结果：dmp文件总共大小50GB, 产生归档日志大概95GB（950个100m归档日志文件）；
物理备份
冷备份：脱机备份，物理拷贝
1. shutdown数据库；
2. 复制控制文件，数据文件，日志文件；
热备份条件：
1. 将日志模式改成归档
SQL>alter system set log_archive_start=true scope=spfile;
2. 停止数据库
SQL>shutdown immediate;
3. 启动数据库
SQL>startup mount;
4. 启用归档方式
SQL>alter database archivelog;
SQL>alter database open;
5. 验证是否是归档方式
SQL>archive log list;
导出导入报错：
错误信息：
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 operation
报错原因：directory对应的路径，oracle无法找到；
解决方法：重建directory
客户需求：
含LONG表导入新库，数据出现问题。
解决方案：在原表中，将long转换成clob，然后expdp导出，再impdp导入
alter table <table_name> modify (<column name>, clob);
如果确定服务器之间，网络带宽足够，可以使用dblink缩短导入时间：
impdp system/oracle EXCLUDE=TABLE_STATISTICS,INDEX_STATISTICS directory=datamove dumpfile=44_Q_20120702.dmp logfile=44_Q_20120703.log schemas=BMSSAQX8
不导出情况下，分析大小
expdp system/oracle estimate_only=y logfile=estimate.txt schemas=BMSSAQX8
日志文件存放于$ORACLE_HOME/rdbms/admin
如果空间不足，带宽也不够好，可以选择拆分导出
1. 导出表；
2. 导出schema，排除第一次已经导出表
EXPDP / IMPDP PARALLEL parameter only can be used in Enterprise Version.
If the dump file is in Windows compress folder (blue folder) and it is shows blue, the import will be very slow. Before import, covert it to normal.
If the disk space is not enough and the oracle is enterprise, we can use "compression=all" to do export.