shell: Shell: how to list all db links in oracle DB to generate a flat file (生成dblink列表文件)

如果数据库里有上百个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

#
# file : dl.sh
# author: weejar (anbob.com)
# desc: tend to collect all DB links
# call: sh dl.sh
# date: 2015-5-5
# hp-ux , aix have tested.
# version: 0.2
# 0.1 to tnsping ip
# 0.2 to add isvalid flag
. ~/.profile
# temp file of db_link list
FILE_DBLINK=dl`hostname`_`date +%Y%m%d`.txt
# the result file
FILE_DBLINK1=2.txt
sqlplus -s / as sysdba << ! |sed  '/^$/d'  > $FILE_DBLINK
set timing off time off
set feed off
set lines 200  pages 1000
col owner for a20
col db_link for a40
col HOST for a20
col created for a10
set heading off
SELECT owner,
		 db_link,
		 username,
		 CASE
			 WHEN INSTR (UPPER (HOST), 'DESCRIPTION') > 1
			 THEN
					REGEXP_SUBSTR (
						 UPPER (
							 REGEXP_SUBSTR (
								 UPPER (HOST),
								 'HOST[^=]*=[^0-9]*[0-9]+.[0-9]+.[0-9]+.[0-9]+')),
						 '[0-9]+.[0-9]+.[0-9]+.[0-9]+')
				 ||':'|| REGEXP_SUBSTR (
						 UPPER (
							 REGEXP_SUBSTR (UPPER (HOST), 'PORT[^=]*=[^0-9]*[0-9]+')),
						 '[0-9]+')
			 ELSE
				  host
		 END as host
		 ,TO_CHAR (created, 'yyyymmdd') created
  FROM dba_db_links;
!
while read line
do
# 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...>"
# linux
# 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>" ",>"`
# HP unix
	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 [ -z "$IPS>" ]; then
		IPS=N/A
	fi
	if [ 1 -eq "$ISOK>" ]; then
	 # awk '{if ($1 ~ /^all/) print $0, "anotherthing>"; else print $0}'
	 # linux
	 # sed -i "/$line/ s/$/t valid/>" $FILE_DBLINK
	 ISVALID=YES
	else
	  ISVALID=N/A
	fi
	echo "$line t $IPS t $ISVALID>" >>$FILE_DBLINK1
done < $FILE_DBLINK
echo "RESULT FILE NAME: $FILE_DBLINK1>"
echo "================== if had errors to contact weejar@gmail.com  ========================>"
echo "done.>"
 

原文:http://www.anbob.com/archives/2565.html