如果数据库里有上百个DATABASE LINK, 而且同时要管理几十套这样的数据库,在日后改数据库用户密码时就要格外注意是否有DB LINK在使用,否则只改了LOCAL DB 的用户密码,没有级连修改REMOTE DB 的Database Link 密码,造成访问DB LINK时出错,现在去统计所有DB Link 是件很费时间的事。
自己整理了个简单的SHELL 去收集LOCAL 的所有DB LINKS,功能是如果DB LINK创建使用的是简单方式(没有配置TNSNAMES.ORA)直接取IP:PORT, 或如果使用TNSNAME Alias Name调用TNSPING 转换成IP, 同时还会判断tnsping ip port 里否通?
— I hope it’s useful
# author: weejar (anbob.com) |
# desc: tend to collect all DB links |
# hp-ux , aix have tested. |
# 0.2 to add isvalid flag |
# temp file of db_link list |
FILE_DBLINK=dl`hostname`_`date +%Y%m%d`.txt |
sqlplus -s / as sysdba << ! |sed '/^$/d' > $FILE_DBLINK |
WHEN INSTR (UPPER (HOST), 'DESCRIPTION') > 1 |
'HOST[^=]*=[^0-9]*[0-9]+.[0-9]+.[0-9]+.[0-9]+')), |
'[0-9]+.[0-9]+.[0-9]+.[0-9]+') |
REGEXP_SUBSTR (UPPER (HOST), 'PORT[^=]*=[^0-9]*[0-9]+')), |
,TO_CHAR (created, 'yyyymmdd') created |
# TNS=`echo "$line>"|awk '$4 !~ /:/ && NF==5 {print $4}'` |
TNS=`echo "$line>"|awk 'NF==5 {print $4}'` |
echo "to convert tnsnames alias $TNS to IP...>" |
# IPS=`tnsping $TNS|grep "DESCRIPT>"|grep -o '[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}'|tr "\n>" ",>"` |
TNSTR=`tnsping $TNS|tail -n 2` |
IPS=`echo $TNSTR|grep "DESCRIPT>"|sed -e 's/.*HOST *= *//' -e 's/ *).*//'|tr "\n>" ",>"` |
ISOK=`echo $TNSTR|tail -n 1|grep "OK>"|wc -l` |
if [ 1 -eq "$ISOK>" ]; then |
# awk '{if ($1 ~ /^all/) print $0, "anotherthing>"; else print $0}' |
# sed -i "/$line/ s/$/t valid/>" $FILE_DBLINK |
echo "$line t $IPS t $ISVALID>" >>$FILE_DBLINK1 |
echo "RESULT FILE NAME: $FILE_DBLINK1>" |
echo "================== if had errors to contact weejar@gmail.com ========================>" |
原文:http://www.anbob.com/archives/2565.html
0 Comments