expdp&impdp备份恢复常用命令
-
备份前准备
创建备份用户
create user backup identified by backup#2018 ;
授予导入导出角色
grant connect,resource to backup;
grant exp_full_database,imp_full_database to backup;
创建逻辑目录
create or replace directory oradmp as '/oradmp';
授予备份用户逻辑目录读写权限
grant read,write on directory oradmp to backup;
-
备份数据库--EXPDP
备份全库
expdp backup/backup#2018 directory=oradmp dumpfile=`date +%Y%m%d`_full.dmp logfile=`date +%Y%m%d`_full.log full=y
备份单个用户下的数据
expdp backup/backup#2018 directory=oradmp dumpfile=`date +%Y%m%d`_scott.dmp logfile=`date +%Y%m%d`_scott.log schemas=scott
备份多个用户下的数据
expdp backup/backup#2018 directory=oradmp dumpfile=`date +%Y%m%d`_scott.dmp logfile=`date +%Y%m%d`_scott.log schemas=scott_1,scott_2
备份单个表
expdp backup/backup#2018 directory=oradmp dumpfile=`date +%Y%m%d`_scott.dmp logfile=`date +%Y%m%d`_scott.log tables=scott.dept
备份多个表
expdp backup/backup#2018 directory=oradmp dumpfile=`date +%Y%m%d`_scott.dmp logfile=`date +%Y%m%d`_scott.log tables=scott.dept,scott.emp
备份A开头的表
expdp backup/backup#2018 directory=oradmp dumpfile=`date +%Y%m%d`_scott.dmp logfile=`date +%Y%m%d`_scott.log tables=scott.A%
备份某表前10行数据
expdp backup/backup#2018 directory=oradmp dumpfile=`date +%Y%m%d`_scott.dmp logfile=`date +%Y%m%d`_scott.log tables=scott.dept query=scott.dept:"where rownum<5"
备份时排除单表
expdp backup/backup#2018 directory=oradmp schemas=scott dumpfile=`date +%Y%m%d`_scott.dmp logfile=`date +%Y%m%d`_scott.log exclude=table:\"in\(\'DEPT\'\)\"
备份时排除多表
expdp backup/backup#2018 directory=oradmp schemas=scott dumpfile=`date +%Y%m%d`_scott.dmp logfile=`date +%Y%m%d`_scott.log exclude=table:\"in\(\'DEPT\',\'EMP\'\)\"
备份时排除包含20的表
expdp backup/backup#2018 directory=oradmp schemas=scott dumpfile=`date +%Y%m%d`_scott.dmp logfile=`date +%Y%m%d`_scott.log exclude=table:\"like \'\%20\%\'\"
备份用户的数据表结构
expdp backup/backup#2018 directory=oradmp dumpfile=`date +%Y%m%d`_scott.dmp schemas=scott content=metadata_only
备份用户的数据，不导出表结构
expdp backup/backup#2018 directory=oradmp dumpfile=`date +%Y%m%d`_scott.dmp schemas=scott content=data_only
多进程并行备份（加快备份速度） 注意并行数量一般要少于CPU的数量，并行数量决定了备份文件的分片数量，比如并行数量为8 就会产生8个分片集文件
expdp backup/backup#2018 compress=all full=y directory=oradmp dumpfile=`date +%Y%m%d`_full_%U.dmp logfile=`date +%Y%m%d`_full_%U.log parallel=8
备份排除统计信息
expdp backup/backup#2018 compress=all full=y dumpfile=`date +%Y%m%d`_full_%U.dmp logfile=`date +%Y%m%d`_full.log exclude=table_statistics
集群环境下限制备份文件分片集只在一个节点下
expdp backup/backup#2018 compress=all full=y directory=oradmp dumpfile=`date +%Y%m%d`_full.dmp logfile=`date +%Y%m%d`_full.log cluster=n
备份时只导出DB_LINK
expdp backup/backup#2018 compress=all full=y directory=oradmp dumpfile=`date +%Y%m%d`_full.dmp logfile=`date +%Y%m%d`_full.log include=db_link;
-
恢复数据库
全库恢复
impdp backup/backup#2018 directory=oradmp dumpfile=20180517_full.dmp logfile=`date +%Y%m%d`_impdp.log full=y
恢复单个用户数据
impdp backup/backup#2018 directory=oradmp dumpfile=20180517_scott.dmp logfile=`date +%Y%m%d`_impdp.log schemas=scott
恢复多个用户数据
impdp backup/backup#2018 directory=oradmp dumpfile=20180517_scott.dmp logfile=`date +%Y%m%d`_impdp.log schemas=scott_1, scott_2
恢复时排除用户
impdp backup/backup#2018 directory=oradmp dumpfile=20180517_scott.dmp logfile=`date +%Y%m%d`_impdp.log full=y exclude=schema:\"in\(\'SCOTT\'\)\"
恢复单个表
impdp backup/backup#2018 directory=oradmp dumpfile=20180517_scott.dmp logfile=`date +%Y%m%d`_impdp.log tables=scott.dept
恢复多个表
impdp backup/backup#2018 directory=oradmp dumpfile=20180517_scott.dmp logfile=`date +%Y%m%d`_impdp.log tables=scott.dept,scott.emp
恢复时排除表
impdp backup/backup#2018 directory=oradmp dumpfile=20180517_scott.dmp logfile=`date +%Y%m%d`_impdp.log full=y exclude=table:\"in\(\'DEPT\'\)\"
恢复时排除统计信息
impdp backup/backup#2018 directory=oradmp dumpfile=20180517_scott.dmp logfile=`date +%Y%m%d`_impdp.log full=y exclude=table_statistics
恢复时重命名表空间
impdp backup/backup#2018 directory=oradmp dumpfile=20180517_full.dmp logfile=`date +%Y%m%d`_impdp.log full=y remap_tablespace=EXAMPLE:EXAMPLE_B
恢复时重命名用户名
impdp backup/backup#2018 directory=oradmp dumpfile=20180517_full.dmp logfile=`date +%Y%m%d`_impdp.log schemas=hr remap_schema=hr:other_hr
恢复时重命名表名 （自动创建表other_hr）
impdp backup/backup#2018 directory=oradmp dumpfile=20180517_full.dmp logfile=`date +%Y%m%d`_impdp.log tables=scott.emp remap_table=scott.emp:scott.emp1
-
监控备份恢复进度
查看整体进度
select sid,serial#,start_time,opname,target,sofar,totalwork,round(sofar/totalwork,2) process from v$session_longops where sofar<>totalwork
查看对象创建进度
select count(*) from dba_objects where owner like 'scott';
创建数据导入进度
select sum(bytes)/1024/1024/1024,owner,tablespace_name from dba_segments where owner like 'scott' group by owner,tablespace_name
查看磁盘IO速率
iostat -dmx 1
查看表空间使用情况
select c.host_name HOSTNAME,
b.tablespace_name TABLESPACE_NAME,
a.total TOTAL_SIZE,
a.total - b.free USED_SIZE,
b.free FREE_SIZE,
to_char(round(100 - (b.free/a.total) * 100),'fm9999999990.00') USED_PERCENT
from (select tablespace_name, round(sum(bytes/1024/1024)) total
from dba_data_files
where tablespace_name not in ('UNDOTBS1', 'UNDOTBS2')
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/1024/1024)) free
from dba_free_space
where tablespace_name like 'USERS'
group by tablespace_name) b,
(select host_name from v$instance) c
where a.tablespace_name = b.tablespace_name
