exp和imp,expdp和impdp 这4个工具属于逻辑备份。
exp常用选项
ROWS=N 能够导出整个数据库的结构,不导出数据,只导出表结构
一定要设置buffer,exp最好>64000,imp的buffer最好>100000
查看帮助,exp help=y 显示中文设置 export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
EXP:有3种主要的方式(完全、用户、表)
1、完全:执行完全导出,必须具有特殊的权限
exp system/system buffer=64000 file=/home/oracle/full.dmp full=y
2、用户模式:这样用户scott的所有对象被输出到文件中
exp system/system file=/home/oracle/scott.dmp owner=scott
3、表模式:这样用户scott的表emp就被导出
exp scott/scott file=/home/oracle/scott.dmp table=(emp)
实验:
exp scott/tiger file=scott.dmp owner=scott ---导出scott用户下的所有数据,包含表和视图,同义词,触发器等
exp scott/tiger file=scott.dmp tables=DEPT,EMP ---导出scott用户下的两张表,DEPT 和 EMP表
exp scott/tiger file=scott.dmp tables=DEPT,EMP ROWS=N ---导出表,只导出表结构,不导出内容
exp scott/tiger file=scott.dmp tables=(DEPT,EMP) ---带括号的Windows下使用的,在linux下带括号会报错
exp sys/oracle buffer=64000 file=full.dmp full=y
*.dmp是可以用文本文件打开,看到一些基本的信息的。
imp
导入表
imp scott/tiger file=scott.dmp tables=DEPT,EMP
imp scott/tiger file=scott.dmp tables=DEPT,EMP ignore=y ---如果表存在,会报错,加ignore=y,意思是oracle不执行CREATE TABLE语句,直接将数据插入到表中,如果插入的记录违反了约束条件,比如主键约束,则出错的记录不会插入,但合法的记录会添加到表中。
imp system/oracle file=scott.dmp tables=DEPT,EMP fromuser=scott touser=DEBC --scott用户下的两张表导到DEBC用户下
导入用户
imp system/oracle file=zz_jw.dmp fromuser=ZZ_JW touser=BEDC ---将用户ZZ_JW下的所有数据导入用户BEDC下
全库导入(导入数据库)
全库导出(不建议全库导出,建议只导出业务用户的数据)
1、去掉统计信息的导出; 加参数 statistics=none ---统计信息等导完了,再统计一遍就有了,不需要导出
2、去掉系统默认用户、只导出业务用户的数据,需要验证系统用户里边是否含有业务数据表(看建表时间)
3、select * from dba_users a where a.username like '%SYS%' order by a.created desc --查询用户,按照创建时间倒序排序
4、查看系统失效的对象,业务表空间
确定需要导出的用户在哪些表空间,及其初始化时占用的表空间大小
确定需要导出的用户中有哪些无效的对象,及总共需要导出的对象数量(后续校验用)
5、增加buffer参数,direct=y (设buffer、设direct=y直接路径导出,都是增加导出的速度)
全库导入(不建议全库导入,建议只导入业务用户的数据)
1、创建相关的表空间、创建用户
执行imp system/oracle file=expfull.dmp full=y log=impfull.log show=y (show=y 显示创建命令信息,执行以下,看日志,在前几行就可以确定需要创建的表空间和用户,然后手动创建)
2、比如需要导入lhr和qiji两个用户的数据
imp system/oracle file=expfull.dmp fromuser=lhr touser=lhr log=impfull.log buffer=50000000 feeback=100000 ignore=y (50000000B,/1024/1024≈50M)
imp system/oracle file=expfull.dmp fromuser=qiji touser=qiji log=impfull.log buffer=50000000 feeback=100000 ignore=y (50000000B,/1024/1024≈50M)
3、导入完成后,需要校验相关的业务数据
imp导入常见问题处理
1、数据库对象已经存在 ----建议加参数ignore=y,同时,导入数据前应该彻底删除目标数据下的表,序列,函数/过程,触发器等
2、权限不够 ---用sys或者system用户来导入,或赋系统权限imp_full_database
3、imp和exp使用的字符集不同 ---查看更改NLS_LANG相关信息
4、imp和exp版本不能往上兼容 ---可以从低版本导入高版本,但不能从高版本导入低版本
从dmp文件可以获取哪些内容
1、导出的版本、时间、导出的用户(strings zz_jw.dmp |head -10)
[oracle@rac6n2 ~]$ strings zz_jw.dmp |head -10
TEXPORT:V11.02.00 ====》版本号
DZZ_JW ====》使用zz_jw用户导出的
RUSERS ====》基于用户模式导出,RTABLES是基于表模式导出,RENTIRE是基于全库模式导出
8192
Mon Nov 9 20:16:49 2020zz_jw.dmp ====》生产的时间和文件地址
#G#G
#G#G
+00:00
BYTE
UNUSED
2、获取dmp文件中的表信息strings zz_jw.dmp |grep "CREATE TABLE"|awk '{print $3}'|sed 's/"//g'
[oracle@rac6n2 ~]$ strings zz_jw.dmp |grep "CREATE TABLE"|awk '{print $3}'|sed 's/"//g'|awk '{ if (FNR==1) print "tables="$1 ; else print ","$1}'
tables=A
,ACT_EVT_LOG
,ACT_GE_BYTEARRAY
,ACT_GE_PROPERTY
,ACT_HI_ACTINST
,ACT_HI_ATTACHMENT
,ACT_HI_COMMENT
,ACT_HI_DETAIL
,ACT_HI_IDENTITYLINK
,ACT_HI_PROCINST
,ACT_HI_TASKINST
3、如何查看dmp文件的字符集 imp \'/ as sysdba\' table=xxx.xx file=zz_jw.dmp
[oracle@rac6n2 ~]$ imp \'/ as sysdba\' tables=xxx.xx file=zz_jw.dmp
Import: Release 11.2.0.3.0 - Production on Tue Nov 10 00:10:12 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Tes
Export file created by EXPORT:V11.02.00 via conventional path
Warning: the objects were exported by ZZ_JW, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion) ====> 当前数据库的字符集
export client uses ZHS16GBK character set (possible charset conversion) ====> dmp文件中的字符集
IMP-00029: cannot qualify table name by owner (xxx.xx), use FROMUSER parameter
IMP-00000: Import terminated unsuccessfully
4、如果将US7ASCII字符集的dmp文件导入到ZHS16GBK字符集的数据库中,那么还需要根据文件修改第4行的第3-4个字节,22#视频<01:04:09>位置
5、oracle 的 exp工具有一个query参数,可以指定一个where条件来有条件的导出记录,可以配合parfile参数一起使用,用法如下。
exp scott/tiger file=xxx.dmp tables=emp query=\"where job=\'SALEMAN\' and sal\<1600\" statistics=none ---这样可以
exp \'/ as sysdba\' file=xxx.dmp parfile=/tmp/scottfile.par log=/tmp/exp001.log ----也可以把条件写入scottfile.par文件 用parfile参数
6、延时段创建,创建空表时不会立即分配段,只有在第一行插入时才会分配段。参数deferred_segment_creation默认是true的。详见22#<01:10:40>
7、在11gr2中,采用exp导出数据时,不能导出空表,(expdp是可以的)所以要先判断数据库中有没有空表,当有空表时,先设置参数deferred_segment_creation为false,然后使用ALLOCATE EXTENT为每一个空表分配段,最后再导出所需要的数据。
8、用此语句查出用户下的空表,在执行结果给空表分配段。 SELECT 'ALTER TABLE'||D.OWNER||'.'||D.TABLE_NAME||'ALLOCATE EXTENT;' EXEC_SQL FROM DBA_TABLES D WHERE D.SEGMENT_CREATED='NO' AND D.OWNER IN ('LHR','ABC') 注意修改用户名
expdp\impdp
1、帮助 expdp help=y
2、为数据泵指定文件位置时,需要用到目录对象(数据泵数据文件导出导入只能放在目录对象里)
查询数据库目录对象 select * from dba_directories;查到的任意一个目录对象都可以用来指定文件位置。常用的是DATA_PUMP_DIR
可以用sys用户也可以用lhr用户创建数据库目录对象,不管用什么用户创建的,数据库目录对象只属于sys用户
create directory d2 as '/home/oracle';
grant all on directory d2 to public;
3、expdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=jw_zz.dmp FULL=y ESTIMATE_ONLY=y ----ESTIMATE_ONLY=y只看导出后文件的大小,不执行导出操作
4、exp导出的文件不能用impdp导入
5、按照用户导出,导入
expdp scott/tiger DIRECTORY=D2 dumpfile=SCOTT_expdp.dmp schemas=scott ---用scott或者system、sys用户都可以
impdp scott/tiger DIRECTORY=D2 dumpfile=SCOTT_expdp.dmp schemas=scott
6、按照表名进行导出
expdp system/oracle DIRECTORY=D2 dumpfile=SCOTT_expdp_emp.dmp TABLES=SCOTT.emp,QIJI.A ---注意用system用户时,导出表,表的写法
7、导出表,加查询条件
expdp system/oracle DIRECTORY=D2 dumpfile=SCOTT_expdp_emp.dmp TABLES=SCOTT.emp,QIJI.A query='WHERE deptno=20'
8、按表空间导出,导入
expdp system/oracle DIRECTORY=D2 dumpfile=expdp_tablespace.dmp TABLESPACES=BEDC_DATA
impdp system/oracle DIRECTORY=D2 dumpfile=expdp_tablespace.dmp TABLESPACES=BEDC_DATA
9、导出整个数据库,导入+
expdp system/oracle DIRECTORY=D2 full=y ESTIMATE_ONLY=y -------预估导出的文件大小
expdp system/oracle DIRECTORY=D2 dumpfile=expdp_full.dmp full=y
impdp system/oracle DIRECTORY=D2 dumpfile=expdp_full.dmp full=y
10、导入表,但是改用户,将scott用户下的表,导入到zz_jw下
impdp system/oracle DIRECTORY=D2 dumpfile=SCOTT_expdp.dmp TABLES=SCOTT.emp REMAP_SCHEMA=SCOTT:QIJI
如何彻底停止expdp进程?
使用expdp导出时,不小心按了crtl+C,有输入exit命令,或者网络中断等异常情况,导致expdp进程不存在,但oracle数据库的会话仍存在,dmp文件也一直在增长,这种情况需要彻底停止expdp进程。
1、检查expdp进程是否存在 ps -ef|grep expdp,若存在,则用kill -9 process 杀掉expdp进程
2、检查会话,把表drop掉
SYS@hx1>SELECT * FROM DBA_DATAPUMP_SESSIONS; ------检查会话
OWNER_NAME JOB_NAME INST_ID SADDR SESSION_TYPE
------------------------------ ------------------------------ ---------- ---------------- --------------
SYSTEM SYS_EXPORT_FULL_01 1 000000008C3A8A08 MASTER
SYSTEM SYS_EXPORT_FULL_01 1 000000008C45DB50 WORKER
SYS@hx1> DROP TABLE SYSTEM.SYS_EXPORT_FULL_01 PURGE; -----把表drop掉
导出过滤
导出过滤query、include、exclude
1、之前学的query
2、exclude、include
expdp scott/tiger directory= dumpfile= logfile= schemas=scott exclude=TABLE:\"not like \'b$%\'\"; -----不导出以b$开头的表;
导出job
expdp system/oracle directory=D2 dumpfile=a.dmp full=y include=JBO
impdp system/oracle directory=D2 dumpfile=a.dmp full=y include=JBO SQLFILE=expddl.sql ----看job的创建语句
导出dblink
expdp system/oracle directory=D2 dumpfile=a.dmp full=y include=DBLINK
impdp system/oracle directory=D2 dumpfile=a.dmp full=y include=DBLINK SQLFILE=expddlee.sql ----看job的创建语句
:
实用总结1:
如何查看dmp文件的ddl语句,就是说在导入之前,看看需要创建哪些表空间、创建哪些用户,imp和impdp不同。执行下面的命令,不会真正执行导入操作,只会看ddl语句
imp system/oracle file=expfull.dmp full=y log=impfull.log show=y
impdp \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR DUMPFILE=lhr1202.dmp logfile=imp_test.log sqlfile=xxx.sql
实用总结2:
1、exp和imp导出导入更多的模式是用户模式
导出:exp system/oracle file=expfull.dmp owner=scott
导入:imp system/oracle file=expfull.dmp fromuser=lhr touser=lhr log=impfull.log buffer=50000000 feeback=100000 ignore=y
2、全库导出更多用来获取dmp文件中的表空间和用户
imp system/oracle file=expfull.dmp full=y log=impfull.log show=y
3、表模式更多用于通过dmp文件查看导出导入数据库的字符集
imp \'/ as sysdba\' file=zz_jw.dmp table=xxx.xx