ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 每日同步环境SHELL脚本

每日同步环境SHELL脚本

原创 Linux操作系统 作者:zhanglei_itput 时间:2009-06-24 15:49:13 0 删除 编辑

    最近在写同步环境数据的脚本,写完了与大家分享一下:
    由于我们的线上环境中有lob字段,而两个库的表空间名称又不一样,所以在drop对象以后,imp时会有问题,因为lob字段的storage参数中写死了表空间的名称,所以我考虑用truncate table的方法来同步数据,不动表结构。

一、 创建syn.sh脚本
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2/db
export PATH=/usr/bin:/etc/:/usr/sbin:/usr/ucb:/usr/local/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:/usr/bin/X11:/sbin:/oracle/soft/patch/OPatch:$
PATH
export NLS_LANG=American_America.ZHS16GBK

--1.禁用外键约束和触发器
######## disable foreign&trigger constraint ##########
sqlplus
ecc_view/ecc@devdb1  @/expdata/syn_script/disable_constraint1.sql
sqlplus
ecc_view/ecc@devdb1  @/expdata/syn_script/disable_constraintc.sql

--2.truncate table
########syn tables##########
sqlplus
ecc_view/ecc@devdb1  @/expdata/syn_script/trunc1_exe1.sql
sqlplus
ecc_view/ecc@devdb1  @/expdata/syn_script/trunc1_exec.sql

--3.imp 数据,ignore=y
########imp data##########
imp
ecc_view/ecc@devdb1  file=/expdata/exp-tmp/ecc_view1.dmp parfile=/expdata/syn_script/table1.par log=/expdata/exp-tmp/ecc_view1.log ignore=y grants=n buffer=9999999 

--4.重新编译存储过程
########recompile procedure##########
sqlplus
ecc_view/ecc@devdb1  @/expdata/syn_script/execompile1.sql
sqlplus
ecc_view/ecc@devdb1  @/expdata/syn_script/execompilec.sql

--5.启用外键约束和触发器
######## enable foreign&trigger constraint ##########
sqlplus
ecc_view/ecc@devdb1  @/expdata/syn_script/enable_constraint1.sql
sqlplus
ecc_view/ecc@devdb1  @/expdata/syn_script/enable_constraintc.sql

二、附加脚本:
1.disable_constraint1.sql

set linesize 1024
set pagesize 1024
set feedback off
set termout off
set heading off
spool /expdata/syn_script/disable_constraintc.sql

select ' alter table '||a.table_name||' disable constraint '||constraint_name||';' from user_constraints a where a.constraint_type = 'R';
select ' ALTER TRIGGER '||a.trigger_name||' DISABLE ;' from user_triggers a ;
select 'exit' from dual;
spool off
exit


2.trunc1_exe1.sql

set linesize 1024
set pagesize 1024
set feedback off
set termout off
set heading off
spool /expdata/syn_script/trunc1_exec.sql

select 'truncate table cpfbos.'||table_name||';' from user_tables where table_name ;
select 'exit' from dual;
spool off
exit

3.execompile1.sql

set linesize 1024
set pagesize 1024
set feedback off
set termout off
set heading off
spool /expdata/syn_script/execompilec.sql

select 'ALTER '||A.OBJECT_TYPE||' '||A.OBJECT_NAME||' COMPILE ;' from user_objects a where A.status <> 'VALID' and a.object_type = 'PROCEDURE' ORDER BY A.OBJECT_TYPE DESC; 
select 'exit' from dual;
spool off
exit

4.enable_constraint1.sql

set linesize 1024
set pagesize 1024
set feedback off
set termout off
set heading off
spool /expdata/syn_script/enable_constraintc.sql

select ' alter table '||a.table_name||' enable constraint '||constraint_name||';' from user_constraints a where a.constraint_type = 'R';
select ' ALTER TRIGGER '||a.trigger_name||' ENABLE ;' from user_triggers a;
select 'exit' from dual;
spool off
exit

5.导出线上sequence脚本

select
'create sequence schema_name.'|| SEQUENCE_NAME || ' minvalue '||MIN_VALUE||' maxvalue '||MAX_VALUE||' start with '||LAST_NUMBER||' increment by '||INCREMENT_BY||' cache '||CACHE_SIZE||' ;'
from dba_sequences where SEQUENCE_OWNER='&your_schema_name';


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9252210/viewspace-607455/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-10

  • 博文量
    400
  • 访问量
    1108739