时间戳

首页 > 解决方案 > 循环 sql 以匹配 bash ksh (AIX) oracle 数据库中的数据

bash - 循环 sql 以匹配 bash ksh (AIX) oracle 数据库中的数据

问题描述

我想通过 shell 检查 sqlplus 命令的两个输出是否匹配。因此，我循环直到捕获该输出的变量相等，然后结束循环，然后是下一组指令。

下面提到的循环都没有按预期运行。

我希望实时获取 和 的数据，testSeq然后expectedSeq进行比较以检查数据，如果它们相等，则退出并继续下一步。 

testSeq=`sqlplus -S user/xxxxxxxxx@${primary} << EOF
set heading off feedback off pagesize 0 verify off echo off numwidth 15 
select max(ARCHIVED_SEQ#) from v\\$ARCHIVE_DEST_STATUS;
EOF`

expectedSeq=`sqlplus -S user/xxxxxxxxx@${DR}<< EOF
set heading off feedback off pagesize 0 verify off echo off numwidth 15 
select max(APPLIED_SEQ#) from V\\$ARCHIVE_DEST_STATUS;
EOF`
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

while (true)
    do
    while do;

    testSeq=`sqlplus -S user/xxxxxxxxx@${primary} << EOF
    set heading off feedback off pagesize 0 verify off echo off numwidth 15 
    select max(ARCHIVED_SEQ#) from v\\$ARCHIVE_DEST_STATUS;
    EOF`

    expectedSeq=`sqlplus -S user/xxxxxxxxx@${DR}<< EOF
    set heading off feedback off pagesize 0 verify off echo off numwidth 15 
    select max(APPLIED_SEQ#) from V\\$ARCHIVE_DEST_STATUS;
    EOF`

       if [[ "$testSeq" != "$expectedSeq" ]] 
       then
       echo "$DR sync is in Progress." | mailx -s "$DR Refresh update" xxxxx@mail.com
       else
       sleep 20
    echo "$DR is in sync with ${PRIMARY} and ready to be switched to Snapshot DR" | mailx -s "$DR Refresh update" xxxxx@mail.com
    done

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    while [[ "$testSeq" != "$expectedSeq" ]]; do
    echo "$DR sync is in Progress." | mailx -s "$DR Refresh update" xxxxx@mail.com

    sleep 20

    done
    echo "$DR is in sync with ${PRIMARY} and ready to be switched to Snapshot DR" | mailx -s "$DR Refresh update" xxxxx@mail.com


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

until [ "$expectedSeq" -eq "$testSeq" ]; do
echo "$DR sync is in Progress." | mailx -s "$DR Refresh update" xxxxx@mail.com
sleep 5 
if [ $? -eq 0 ]; then
break
fi
done
echo "$DR is in sync with ${PRIMARY} and ready to be switched to Snapshot DR" | mailx -s "$DR Refresh update" xxxxx@mail.com

感谢所有的帮助。提前致谢

标签： bashoracleoracle11gkshdatabase-administration

解决方案

这应该适用于 bash，我没有测试 ksh：

expectedSeq=`sqlplus -S user/xxxxxxxxx@${DR} <<EOF
set heading off feedback off pagesize 0 verify off echo off numwidth 15 
select max(APPLIED_SEQ#) from V\\$ARCHIVE_DEST_STATUS;
EOF`
testSeq=

while [[ $testSeq != $expectedSeq ]]; do
    if [[ -n testSeq ]]; then
        echo "$DR sync is in Progress."
        sleep 20
    fi
    testSeq=`sqlplus -S user/xxxxxxxxx@${primary} <<EOF
set heading off feedback off pagesize 0 verify off echo off numwidth 15 
select max(ARCHIVED_SEQ#) from V\\$ARCHIVE_DEST_STATUS;
EOF`
    # todo: handle failures
done
echo "$DR is in sync with ${primary} and ready to be switched to Snapshot DR"

可以在循环外定义预期输出，前提是表格在此过程中不发生变化。您应该为查询失败的情况做好准备，通过在输出中查找错误等。

推荐阅读