1. IT知识观首页

oracle数据泵定时导出到目标库定时导入「建议收藏」

云烟 • • 未分类 • 阅读 222

oracle数据泵定时导出到目标库定时导入「建议收藏」最近碰到客户需要实现定时每天导出某张表最近一天的数据，同时定时在目标库进行导入。通过expdp的query参数根据指定的值导出数据可以两种写法一

欢迎大家来到IT世界,在知识的湖畔探索吧!

最近碰到客户需要实现定时每天导出某张表最近一天的数据，同时定时在目标库进行导入。实现步骤如下在此记录一下。

源库查看表大小

select owner,segment_name,tablespace_name,sum(bytes)/1024/1024/1024 gb from dba_segments where segment_name ='表名称' and owner='用户名称' group by owner,segment_name,tablespace_name;

欢迎大家来到IT世界,在知识的湖畔探索吧!

查看表对应的索引大小

欢迎大家来到IT世界,在知识的湖畔探索吧!select owner,segment_name,tablespace_name,sum(bytes)/1024/1024/1024 gb from dba_segments where segment_name in(select index_name from dba_indexes where table_name='表名称' and owner='用户')  group by owner,segment_name,tablespace_name;

模拟数据表（此处仅作记录自己测试用）

create table test1(
    id number primary key,
    name varchar(16),
    create_time timestamp
);
插入不同的日期，然后模拟按日期导出数据。
insert into test1(id, name, create_time) values(1, 'a', to_date('2022-12-13 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));
insert into test1(id, name, create_time) values(2, 'a', to_date('2022-12-12 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));
insert into test1(id, name, create_time) values(3, 'a', to_date('2022-12-11 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));
insert into test1(id, name, create_time) values(4, 'a', to_date('2022-12-10 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));
insert into test1(id, name, create_time) values(5, 'a', to_date('2022-12-09 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));
insert into test1(id, name, create_time) values(6, 'a', to_date('2022-12-08 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));
insert into test1(id, name, create_time) values(7, 'a', to_date('2022-12-07 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));
insert into test1(id, name, create_time) values(8, 'a', to_date('2022-12-06 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));
insert into test1(id, name, create_time) values(9, 'a', to_date('2022-12-05 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));
insert into test1(id, name, create_time) values(10, 'a', to_date('2022-12-04 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));
insert into test1(id, name, create_time) values(11, 'a', to_date('2022-12-03 16:47:21', 'yyyy-MM-dd HH24:mi:ss'));

按日期导出指定的表的数据

通过expdp的query参数根据指定的值导出数据可以两种写法一个是通过par参数文件一个是直接命令需要注意字符的转义。

  • 通过参数文件
欢迎大家来到IT世界,在知识的湖畔探索吧! cat expdp_query.par 

dumpfile=test.dmp 
 logfile=test1.log 
 directory=dmp1 
 tables=LUQX.TEST1
 QUERY=LUQX.TEST1:"WHERE create_time> sysdate -6"   导出LUQX下的test1表中最近6天的数据

expdp system/oracle parfile=expdp_query.par
  • 通过命令直接导出（注意字符的转义）
expdp system/oracle directory=dmp1 dumpfile=exp2.dmp logfile=exp.log tables=luqx.test1 query=\"WHERE create_time\> sysdate -6\"

Export: Release 11.2.0.4.0 - Production on Tue Dec 13 10:15:46 2022

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** directory=dmp1 dumpfile=exp2.dmp logfile=exp.log tables=luqx.test1 query="WHERE create_time> sysdate -7" 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "LUQX"."TEST1"                              5.945 KB       8 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /dmp1/exp2.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Dec 13 10:16:08 2022 elapsed 0 00:00:21

通过上面的测试说明可以通过query参数实现导出数据表的部分数据。编写自动导出及自动导入脚本如下。

源库导出脚本

oracle创建目录及授权部分省略。

autoexpdp.sh
#!/bin/bash
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export PATH=$ORACLE_HOME/OPatch:$PATH
export NLS_LANG=japanese_japan.AL32UTF8
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
today=`date +"%Y%m%d"`
expdp \'/ as sysdba\'  directory=EXPDIR dumpfile=test_"$today".dmp logfile=test_exp_"$today".log tables=test.test query=\"WHERE create_dte\>sysdate -1\"
#create status file
sleep 10
echo "test exp ok">/data2/expdir/test_exp_ok_"$today"
sleep 10

echo "auto upload to server"
ftp -v -n >>/home/oracle/scripts/ftplog/ftpup_full."$today".log <<EOF
open 1.1.1.1
user oracle *****
binary
cd /oradata/expdir
lcd /data2/expdir
prompt
mput test_"$today".dmp
mput test_exp_ok_"$today"
close
bye
EOF

sleep 60
#auto delete old backupfiles
echo "Delete 1 days before data files..."
cd /data2/expdir
date
echo "below file to delete"
find ./ -mtime +1 -name 'test*' -type f -exec ls -l {} \;
find ./ -mtime +1 -name 'test_exp_ok*' -type f -exec ls -l {} \;
find ./ -mtime +1 -name 'test*.dmp' -type f -exec rm -rf {} \;
find ./ -mtime +1 -name 'test_exp*.log' -type f -exec rm -rf {} \;
find ./ -mtime +1 -name 'test_exp_ok*' -type f -exec rm -rf {} \;
echo "delete before 1days success"

目标数据库导入脚本

#!/bin/bash
export today=`date +"%Y%m%d"`
export ORACLE_SID=oradb
export NLS_LANG=japanese_japan.AL32UTF8
echo "Starting imp data..."
echo "Importing USER data"
status_file=/oradata/expdir/test_exp_ok_"$today"
cd /oradata/expdir
if [ ! -f "$status_file" ];then
    exit
 else
sleep 10
echo "--------------continue import data---------------------------"
impdp \'/ as sysdba \' tables=test.test directory=expdir dumpfile=test_"$today".dmp logfile=test_IMP_"$today".log table_exists_action=replace;
fi

echo "Delete 1 days before data files..."
cd /oradata/expdir/
echo "below file to delete"
find ./ -mtime +1 -name 'test*' -type f -exec ls -l {} \;
find ./ -mtime +1 -name 'test_exp_ok*' -type f -exec ls -l {} \;
find ./ -mtime +1 -name 'test*.dmp' -type f -exec rm -rf {} \;
find ./ -mtime +1 -name 'test_IMP*.log' -type f -exec rm -rf {} \;
find ./ -mtime +1 -name 'test_exp_ok*' -type f -exec rm -rf {} \;
echo "delete before 1days success"

设置定时任务

08 08 * * * /bin/sh /home/oracle/scripts/autoexp.sh>/dev/null 2>&1  源库设置
20 08 * * * /bin/sh /home/oracle/scripts/autoimpsh >/dev/null 2>&1  目标设置
根据导出测试和导出测试的时间合理设置定时任务时间

一键查看索引状态并生成rebuild脚本

set line2222 pages1000
SELECT owner index_owner, index_name, index_type,'N/A' partition_name,status,table_name,tablespace_name,
  'alter index '||owner||'.'||index_name||' rebuild;' rebuild_index
  FROM dba_indexes
WHERE status = 'UNUSABLE'
UNION ALL
SELECT a.index_owner,a.index_name,b.index_type,a.partition_name,a.status,b.table_name,a.tablespace_name,
'alter index '||a.index_owner||'.'||a.index_name||' rebuild partition '||a.partition_name||' ;' rebuild_index
  FROM dba_ind_partitions a, dba_indexes b
WHERE a.index_name = b.index_name
   AND a.index_owner = b.owner
   AND a.status = 'UNUSABLE'
UNION ALL
SELECT owner index_owner,a.index_name,b.index_type,'N/A' partition_name,a.status,b.table_name,NULL,
'alter index '||a.index_owner||'.'||a.index_name||' rebuild subpartition '||a.subpartition_name||';' rebuild_index
  FROM dba_ind_subpartitions a, dba_indexes b
WHERE a.index_name = b.index_name
   AND a.index_owner = b.owner
   AND a.status = 'UNUSABLE';

模拟手动失效索引

SYS@oradb>alter index TEST2_IDX_ID unusable;

Index altered.

SYS@oradb>select index_name,status from user_indexes where table_name='TEST2';

INDEX_NAME		       STATUS
------------------------------ --------
TEST2_IDX_ID		       UNUSABLE
TEST2_IDX_NAME		       VALID

执行上面的查看索引状态的脚本
SYS@oradb>l
  1  SELECT owner index_owner, index_name, index_type,'N/A' partition_name,status,table_name,tablespace_name,
  2    'alter index '||owner||'.'||index_name||' rebuild;' rebuild_index
  3    FROM dba_indexes
  4  WHERE status = 'UNUSABLE'
  5  UNION ALL
  6  SELECT a.index_owner,a.index_name,b.index_type,a.partition_name,a.status,b.table_name,a.tablespace_name,
  7  'alter index '||a.index_owner||'.'||a.index_name||' rebuild partition '||a.partition_name||' ;' rebuild_index
  8    FROM dba_ind_partitions a, dba_indexes b
  9  WHERE a.index_name = b.index_name
 10	AND a.index_owner = b.owner
 11	AND a.status = 'UNUSABLE'
 12  UNION ALL
 13  SELECT owner index_owner,a.index_name,b.index_type,'N/A' partition_name,a.status,b.table_name,NULL,
 14  'alter index '||a.index_owner||'.'||a.index_name||' rebuild subpartition '||a.subpartition_name||';' rebuild_index
 15    FROM dba_ind_subpartitions a, dba_indexes b
 16  WHERE a.index_name = b.index_name
 17	AND a.index_owner = b.owner
 18*	AND a.status = 'UNUSABLE'
SYS@oradb>/

INDEX_OWNE INDEX_NAME	   INDEX_TYPE PARTITION_NAME  STATUS   TABLE_NAME		      TABLESPACE_NAME	   REBUILD_INDEX
---------- --------------- ---------- --------------- -------- ------------------------------ -------------------- ----------------------------------------
SYS	   TEST2_IDX_ID    NORMAL     N/A	      UNUSABLE TEST2			      SYSTEM		   alter index SYS.TEST2_IDX_ID rebuild;

SYS@oradb>alter index SYS.TEST2_IDX_ID rebuild;

Index altered.

SYS@oradb>select index_name,status from user_indexes where table_name='TEST2';

INDEX_NAME	STATUS
--------------- --------
TEST2_IDX_ID	VALID
TEST2_IDX_NAME	VALID

免责声明：本站所有文章内容,图片，视频等均是来源于用户投稿和互联网及文摘转载整编而成，不代表本站观点，不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益，请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权，请联系删除，如若转载，请注明出处：https://itzsg.com/17954.html

赞 (0)
云烟云烟
0

相关推荐

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

联系我们YX

mu99908888

在线咨询： 微信交谈

邮件：itzsgw@126.com

工作时间：时刻准备着!

关注微信