OGG单表初始化交互式脚本

最新推荐文章于 2024-07-12 16:35:20 发布

cuhuitong3696

最新推荐文章于 2024-07-12 16:35:20 发布

阅读量141

点赞数

文章标签：数据库运维

该脚本早期匆忙编写，当时脚本能力也有限，方法比较笨，redhat下可以运行，unix下可能scn有问题，一直没时间调整
给ogg运维总有表同步引发abend的兄弟参考一下，共同苦逼吧
#传参

clear

echo 'Please first stop replicat process and press RETURN to continue'

read continue

clear

echo 'Please input goldengate user name :'

read username

clear

echo 'Please input goldengate password :'

read password

clear

echo 'Please input exp directory :'

read dir

clear

echo 'Please input exp file name :'

read file

clear

echo 'Please input tables name(hr.employees,hr.departments) :'

read tables

clear

echo 'Please input compress(n/y) :'

read compress

clear

echo 'Please input grants(n/y) :'

read grants

clear

echo 'Please input replicat host IP :'

read host

clear

echo 'Please input replicat operator system user :'

read user

clear

echo 'Please input replicat operator system password :'

read passwd

clear

echo 'Please input replicat host directory :'

read rdir

clear

#传参确认

echo 'check your setup :'

echo ' '

echo 'goldengate user name/password : '$username'/'$password

echo ' '

echo 'exp file : '$dir'/'$file'.dmp'

echo ' '

echo 'exp parameter compress : '$compress

echo ' '

echo 'exp parameter grants : '$grants

echo ' '

echo 'replicat host IP : '$host

echo ' '

echo 'replicat operator system user : '$user

echo ' '

echo 'replicat operator system password : '$passwd

echo ' '

echo 'replicat host directory : '$rdir

echo ' '

echo 'exp tables :'

echo ' '

echo $tables

echo ' '

echo 'Are you ready start this job? if "ok" press RETURN to continue ! if have error press "ctrl + C" :'

read continue

#导出表

cd $dir

sqlplus / as sysdba <

set pagesize 500

set linesize 150

col exp for a150

col scn new_val scn

col exp new_val exp

col filter new_val filter

alter system checkpoint;

select checkpoint_change# scn from v\$database;

select 'nohup exp userid=$username/$password file=$dir/$file.dmp log=$dir/$file.log tables=$tables grants=$grants statistics=none triggers=n compress=$compress flashback_scn=&scn &' as "exp" from dual;

#复制端脚本提示部分

col tmp new_val tmp

select 'echo ''clear'' >$dir/ogg_tables.sh' as "tmp" from dual;

!&tmp

col tmp new_val tmp

select 'echo ''echo ''\''''modify replicat process $rep add : " ,filter ( @GETENV ("TRANSACTION", "CSN") >&scn); "''\'' >>$dir/ogg_tables.sh' as "tmp" from dual;

!&tmp

col tmp new_val tmp

select 'echo ''echo ''\''Press ENTURN to continue\'''''' >>$dir/ogg_tables.sh' as "tmp" from dual;

!&tmp

col tmp new_val tmp

select 'echo ''read continue'' >>$dir/ogg_tables.sh' as "tmp" from dual;

!&tmp

!&exp

quit

EOF

#生成复制端脚本

#复制端脚本删除需要IMP的表

cd $dir

echo 'drop table '$tables' purge;' >drop_tables.txt

vi drop_tables.txt<

:%s/,/ purge;,drop table /g

:wq

EOF

cat drop_tables.txt|tr ',' '\n' >drop_tables.sh

#复制端脚本传参

echo 'clear' >>ogg_tables.sh

echo 'echo '\'Please input goldengate user name:\''' >>ogg_tables.sh

echo 'read username' >>ogg_tables.sh

echo 'clear' >>ogg_tables.sh

echo 'echo '\'Please input goldengate user password:\''' >>ogg_tables.sh

echo 'read password' >>ogg_tables.sh

echo 'clear' >>ogg_tables.sh

echo 'echo '\'Please input fromuser:\''' >>ogg_tables.sh

echo 'read fromuser' >>ogg_tables.sh

echo 'clear' >>ogg_tables.sh

echo 'echo '\'Please input touser:\''' >>ogg_tables.sh

echo 'read touser' >>ogg_tables.sh

echo 'clear' >>ogg_tables.sh

echo 'echo '\'Please input replicat process name :\''' >>ogg_tables.sh

echo 'read rep' >>ogg_tables.sh

echo 'clear' >>ogg_tables.sh

echo 'echo '\'Please input goldengate software directory :\''' >>ogg_tables.sh

echo 'read oggdir' >>ogg_tables.sh

echo 'clear' >>ogg_tables.sh

echo 'cd '$rdir >>ogg_tables.sh

#复制端脚本传参确认

echo 'clear' >>ogg_tables.sh

echo 'echo '\''check your setup :'\'

echo 'echo '\''goldengate user name/password : '\''$username'\''/'\''$password' >>ogg_tables.sh

echo 'echo '\'' '\' >>ogg_tables.sh

echo 'echo '\''imp parameter fromuser : '\''$fromuser' >>ogg_tables.sh

echo 'echo '\'' '\' >>ogg_tables.sh

echo 'echo '\''imp parameter touser : '\''$touser' >>ogg_tables.sh

echo 'echo '\'' '\' >>ogg_tables.sh

echo 'echo '\''replicat process name : '\''$rep' >>ogg_tables.sh

echo 'echo '\'' '\' >>ogg_tables.sh

echo 'echo '\''goldengate sowftware directory : '\''$oggdir' >>ogg_tables.sh

echo 'echo '\'' '\' >>ogg_tables.sh

echo 'echo '\''Are you ready start this job? if "ok" press RETURN to continue ! if have error press "ctrl + C" :'\' >>ogg_tables.sh

echo 'read continue' >>ogg_tables.sh

#复制端脚本IMP要同步的表

echo 'sqlplus / as sysdba <>ogg_tables.sh

cat drop_tables.sh >>ogg_tables.sh

echo 'quit' >>ogg_tables.sh

echo 'EOF' >>ogg_tables.sh

echo 'nohup imp userid=$username/$password file='$rdir'/'$file'.dmp log='$rdir'/'$file'.log fromuser=$fromuser touser=$touser &' >>ogg_tables.sh

echo 'echo '\'' '\' >>ogg_tables.sh

echo 'clear' >>ogg_tables.sh

echo 'echo '\''The job is OK press RETURN to start replicat process "'\''$rep'\''"'\' >>ogg_tables.sh

echo 'read continue' >>ogg_tables.sh

echo 'cd $oggdir' >>ogg_tables.sh

echo './ggsci << EOF' >>ogg_tables.sh

echo 'start $rep' >>ogg_tables.sh

echo 'quit' >>ogg_tables.sh

echo 'EOF' >>ogg_tables.sh

echo 'clear' >>ogg_tables.sh

echo 'echo '\''please wait 10 second'\' >>ogg_tables.sh