本人在AIX系统源端oracle数据库中,部署了一套ogg同步软件,同步软件开启了ddl复制功能。由于ddl自动添加附加日志功能参数DDLOPTIONS ADDTRANDATA有时不能正确起作用,所以打算自己写一个脚本,在每日凌晨0点0,1,2,3分钟对新增的表自动添加附加日志,基本配置如下:
crontab配置如下:
$
$ crontab -l
0,1,2,3 0 * * * sh /oracle/ogg/oggsh/oggall.sh
oggall.sh脚本:
$ cat oggall.sh
/oracle/ogg/oggsh/oggtask.sh
/oracle/ogg/ggsci paramfile /oracle/ogg/oggsh/addtrandata.sh > /oracle/ogg/oggsh/add.log
$
oggtask.sh脚本:
$ cat oggtask.sh
echo "dblogin userid goldengate,password goldengate
add trandata TD.IDS_GPS_`date +%Y%m%d`" > /oracle/ogg/oggsh/addtrandata.sh
在配置完成后,后续通过查看日志,发现日志add.log中没有数据,查看crontab日志信息,发现报错如下:
From daemon Tue Jul 2 09:57:00 2019
Received: (from root@localhost)
by lxbusdbs1 (AIX7.1/8.14.4/8.14.4) id x621v0nQ19334018
for oracle; Tue, 2 Jul 2019 09:57:00 +0800
Date: Tue, 2 Jul 2019 09:57:00 +0800
From: daemon
Message-Id: <201907020157.x621v0nQ19334018@lxbusdbs1>
To: oracle
Subject: Output from cron job sh /oracle/ogg/oggsh/oggall.sh, oracle@lxbusdbs1, exit status 255
Cron Environment:
SHELL = /usr/bin/sh
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/usr/bin/X11:/sbin:/usr/java6/jre/bin:/usr/java6/bin
CRONDIR=/var/spool/cron/crontabs
ATDIR=/var/spool/cron/atjobs
LOGNAME=oracle
HOME=/home/oracle
Your "cron" job executed on lxbusdbs1 on Tue Jul 2 09:56:00 CST 2019
sh /oracle/ogg/oggsh/oggall.sh
produced the following output:
exec(): 0509-036 Cannot load program /oracle/ogg/ggsci because of the following errors:
0509-150 Dependent module libgglog.a could not be loaded.
0509-022 Cannot load module libgglog.a.
0509-026 System error: A file or directory in the path name does not exist.
*****************************************************************
cron: The previous message is the standard output
and standard error of one of the cron commands.
原来crontab默认是不带环境变量的,cron命令默认shell是/usr/bin/bsh,如果要在cron启动的脚本中使用ksh,就必须在脚本中的第一行添加“#/usr/bin/ksh”的声明。如果cron进程启动的shell脚本要用登陆时的环境变量,就必须在cron启动的shell添加环境变量。
修改oggall.sh
$ cat oggall.sh
. ~/.profile
/oracle/ogg/oggsh/oggtask.sh
/oracle/ogg/ggsci paramfile /oracle/ogg/oggsh/addtrandata.sh > /oracle/ogg/oggsh/add.log
$
修改完成后,脚本测试执行成功,表附加日志添加成功:
$ cat add.log
Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005_FBO
AIX 6, ppc, 64bit (optimized), Oracle 11g on Dec 14 2017 02:48:55
Operating system character set identified as US-ASCII.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
GGSCI (lxbusdbs1) 1> dblogin userid goldengate,password goldengate
Successfully logged into database.
GGSCI (lxbusdbs1 as goldengate@LXBUSDB1) 2> add trandata TD.IDS_GPS_20190702
2019-07-02 10:01:03 INFO OGG-15131 Logging of supplemental redo log data is already enabled for table TD.IDS_GPS_20190702.
2019-07-02 10:01:04 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table TD.IDS_GPS_20190702.
2019-07-02 10:01:04 WARNING OGG-01988 Could not find schematrandata function in source database: failed to find function.