oracle自带的数据备份还原工具,使用Oracle自带工具实现数据库备份与迁移(四)

才疏学浅，欢迎批评指正

文章目录

1.使用Oracle的数据泵(expdp/impdp)实现数据库的备份与迁移实战总结

2.使用Oracle的数据泵(expdp/impdp)结合DB_LINK实现数据库的备份与迁移实战总结

3.关于数据泵与DBlink的其他总结

关于数据泵的相关语法可见

https://blog.csdn.net/xinxindiandeng9789/article/details/88198204

1.使用Oracle的数据泵(expdp/impdp)实现数据库的备份与迁移实战总结

场景： 需要将源库中的数据迁移到目标库中，事前已将源库的库表结构(包括表的约束、表的外键等)和一些小表导入目标库中，几张大表需要使用数据泵

1.1从源库中迁出数据(在源库的数据库服务器上操作或者远程操作)

给sourceUSER用户申请使用expdp的权限(exp_full_database)

grant exp_full_database to sourceUSER;

给sourceUSER用户授予exp_full_database权限

grant exp_full_database to 用户名;

创建导出文件目录

create or replace directory expdp_dir as‘H:\datadmp’;

创建目录expdp_dir

create or replace directory 目录名 as‘文件所在位置’;

grant read, write on directory expdp_dir to sourceUSER;

授予 sourceUSER用户对 expdp_dir目录的读写权限

grant read, write on directory 目录名 to 用户名;

将数据从源库sourceDB中迁出

expdp sourceUSER/sourecePASS tables=sourceUSER.table1 directory=expdp_dir dumpfile=expdpdata content=ALL JOB_NAME= expdptable1 logfile = table1expdp.log

将sourceUSER用户的table1表导出(包括表结构、表约束、外键)，数据文件为expdpdata，数据文件存放在expdp_dir 下，作业名指定为expdptable1，日志文件为table1expdp.log

expdp 用户名/密码 tables=用户名.表名 directory=目录名 dumpfile=导出文件名称 content=ALL JOB_NAME= 作业名 logfile = 日志文件

1.2将导出的数据文件存入外部存储设备，至此完成了数据库的备份，若需要迁库，则继续1.3

1.3将数据导入目标库中(在目标库的数据库服务器上操作或者远程操作)

给targetUSER用户申请使用impdp的权限(imp_full_database)

grant imp_full_database to targetUSER;

给targetUSER用户授予imp_full_database 权限

grant imp_full_database to 用户名;

创建导入文件目录，并放入将先前导出的数据文件

create or replace directory impdp_dir as‘H:\datadmp’;

**创建目录impdp_dir **

create or replace directory 目录名 as‘文件所在位置’;

grant read, write on directory impdp_dir to targetUSER;

授予 targetUSER用户对 impdp_dir 目录的读写权限

grant read, write on directory 目录名 to 用户名;

禁用约束与外键(最好在plsql之类的软件中执行)

形成禁用约束的sql语句

set heading off

select ‘alter table ‘||table_name||’ disable constraint ‘||constraint_name||’;’ from user_constraints;

执行禁用约束的sql语句

alter table table_name disable constraint constraint_name;

形成禁用外键的sql语句

set heading off

select ‘alter trigger ‘||trigger_name||’ disable;’ from user_triggers;

执行禁用外键的sql语句

alter trigger trigger_name disable;

将数据导入targetDB中

impdp targetUSER/targetPASS directory = impdp_dir dumpfile=expdpdata tables=sourceUSER.table1 remap_tablespace=sourceTableSpace:targetTableSpace remap_schema =sourceUSER:targetUSER table_exists_action = truncate content = DATA_ONLY JOB_NAME= impdptable1 logfile = impdplog.log

导入tables_dir 路径下的 expdpdata数据文件中用户sourceUSER下的table1，同时将表空间由原来的sourceTableSpace替换为targetTableSpace ，用户由sourceUSER替换为targetUSER，如果table1在目标数据库中存在，执行截断操作，并且只导入表中数据，作业名指定为impdptable1，日志文件为impdplog.log

impdp 目标库用户名/目标库用户密码 directory =路径名 dumpfile=导入文件名 tables=源库用户名.表名 remap_tablespace=源库表空间:目标库表空间 remap_schema =源库用户名:目标库用户名 table_exists_action = truncate content = DATA_ONLY JOB_NAME= 作业名 logfile = 日志文件

启用约束与外键(最好在plsql之类的软件中执行)

形成启用约束的sql语句

set heading off

select ‘alter table ‘||table_name||’ enable novalidate constraint ‘||constraint_name||’;’ from user_constraints;

执行启用约束的sql语句

alter table table_name enable novalidate constraint constraint_name;

形成启用外键的sql语句

set heading off

select ‘alter trigger ‘||trigger_name||’ enable;’ from user_triggers;

执行启用外键的sql语句

alter trigger trigger_name enable;

至此完成使用数据库的迁库

2.使用Oracle的数据泵(expdp/impdp)结合DB_LINK实现数据库的备份与迁移实战总结

场景： 需要将源库中的数据迁移到目标库中，事前已将源库的库表结构(包括表的约束、表的外键等)和一些小表导入目标库中，几张大表需要使用数据泵，但无法直接操作数据所在服务器，但有一台机器可以访问源库与目标库，同时在该机器上可以安装数据库

2.1给源库和目标库用户授予使用数据泵的权限

给sourceUSER用户申请使用expdp的权限(exp_full_database)

grant exp_full_database to sourceUSER;

给sourceUSER用户授予exp_full_database权限

grant exp_full_database to 用户名;

给targetUSER用户申请使用impdp的权限(imp_full_database)

grant imp_full_database to targetUSER;

给targetUSER用户授予imp_full_database 权限

grant imp_full_database to 用户名;

禁用目标库上的约束、外键(见1.3)

2.2从源库中将数据导出至中间库所在机器，在移至目标库中(在中间库的机器上执行)

给MiddleUSER授予创建DBlink的权限，并创建链接sourceDB的link

grant create public database link to MiddleUSER

给MiddleUSER授予创建db_link的权限

grant create public database link to 数据库用户名

create public database link link2source connect to sourceUSER identified by soucePASS by using ‘10.196.83.53/sourceDB’

在用户MiddleUSER下， 使用用户sourceUSER ，创建名为link2source的远程连接，连接到sourceDB上

create public database link 链接名 connect to 源数据库用户名 identified by 源数据库用户对应的密码 by using ‘源数据库地址/SID’

从源库导出数据

expdp MiddleUSER/MiddlePASS network_link = link2source tables = sourceUSER .table1 dumpfile = expdpdata content=ALL JOB_NAME= expdptable1 logfile = table1expdp.log

利用link2source， 将sourceUSER用户的table1表导出(包括表结构、表约束、外键)，数据文件为expdpdata，作业名指定为expdptable1，日志文件为table1expdp.log

expdp 中间库用户名/密码 network_link= 连接名 tables=用户名.表名 directory=目录名 dumpfile=导出文件名称 content=ALL JOB_NAME= 作业名 logfile = 日志文件

2.3在中间库所在机器，将数据导入目标库中(在中间库的机器上执行)

MiddleUSER创建链接targetDB的link

create public database link link2target connect to targetUSER identified by targetPASS by using ‘10.196.83.55/targetDB’

在用户MiddleUSER下， 使用用户targetUSER ，创建名为link2source的远程连接，连接到targetDB上

create public database link 链接名 connect to 目标数据库用户名 identified by 目标数据库用户对应的密码 by using ‘目标数据库地址/SID’

将数据导入目标库中

impdp MiddleUSER/MiddlePASS network_link = link2source dumpfile=expdpdata tables= sourceUSER.table1 remap_tablespace = sourceTableSpace: targetTableSpace remap_schema =sourceUSER:targetUSER table_exists_action = truncate content = DATA_ONLY JOB_NAME= impdptable1 logfile = impdplog.log

利用link2target，将 expdpdata数据文件中用户sourceUSER下的table1，同时将表空间由原来的sourceTableSpace替换为targetTableSpace ，用户由sourceUSER替换为targetUSER，如果table1在目标数据库中存在，执行截断操作，并且只导入表中数据，作业名指定为impdptable1，日志文件为impdplog.log

impdp 中间库用户名/中间库用户密码 network_link = 链接名 dumpfile=导入文件名 tables=源库用户名.表名 remap_tablespace=源库表空间:目标库表空间 remap_schema =源库用户名:目标库用户名 table_exists_action = truncate content = DATA_ONLY JOB_NAME= 作业名 logfile = 日志文件

启用目标库上的约束、外键(见1.3)

2.4放弃创建的dblink (在中间库的机器上执行)

放弃使用到的dblink

drop public database link link2source ；

drop public database link link2target；

放弃link2source，link2target

drop public database link 连接名

3.关于数据泵与DBlink的其他总结

3.1关于数据泵

数据泵在命令行下直接使用(退出sqlplus)

使用数据泵时可以不关闭约束，但要注意表的导入顺序，不然会报错

在windows系统下dumpfile参数后的文件名可以不加dmp后缀，但在linux下需要加上dmp后缀，否则 可能 会报错

查看数据泵任务

select * from dba_datapump_jobs;

中断数据泵任务( 在命令行下按)

ctrl+c

恢复数据泵任务(命令行下输入 )

expdp ATTACH=expdptable1

impdp ATTACH=impdptable1

继续expdptable1或impdptable1作业

expdp/ impdp ATTACH=作业名

3.2关于dblink

public database link 与 database link的区别

创建的DBlink是否共所有用户使用(加上public 则为所有用户)

查看所有连接

select * from dba_db_links