Multiple database tnsping and login Single sign-on requirements

"Introduction to the Environment"

System environment: Linux + Oracle 11.2.0.4.0 + python 2.7.10

"Background description"

Requirements: Because of the production database involved more, business boast multiple database use. When the business has some impact, the database side if BOMC did not immediately receive the relevant alarm, then if you want to determine which database is a problem, only a lot of databases are checked again. It is obvious that such a hit will be very low and the time is longer. For this problem it is possible that the network problem is too slow to reflect and to test that the database connection is normal.

"Monitoring and optimization process and ideas"

For the problem described above, it is still clearer:

1, whether the database is currently able to connect. Querying database status----connecting multiple databases

2, there is no problem with the database connection, possibly a network problem. ----Use tnsping to determine the network

Based on these two methods, the use of command implementation:

1, multiple database connection databases run SQL statements. -----implemented using Python

2, tnsping Monitor to determine the network. -----Using Oracle's own tnsping

"Test Results and scripts"

Test the script from the test library. Then in the monitoring server process test, the results are as follows:

1,python implements the connection of multiple databases to query the database state information, the final result is as follows:

2,tnsping monitoring network, the final result is as follows:

"Universal Log Processing"

The main implementation features of the script are as follows:

1,python Implementing a connection to multiple databases querying database state information:

Cat > select.py

#-*-Coding:utf-8-*-

Import Xlsxwriter, cx_oracle, sys

Print (' * ' * 50)

Print (' Note:when you add sql_text,the; sign was after the Sql_text ')

Print (' * ' * 50)

Reload (SYS)

Sys.setdefaultencoding (' Utf-8 ')

Database_list = {

' Xxxdb ': ' Xj_exp_data/[email protected]/xxdb ',

Omit part of the IP content

' Xxxdb ': ' Xj_exp_data/[email protected]/xxxdb

}

"," Connecting Database list

Sql=raw_input ("Enter your Sql_text:"); "Prompt for SQL statements

While True:

Line=raw_input ()

If line== '; ':

Break

sql = SQL + "+line"

For D in Database_list:

Print (' * ' * 50)

Print (d)

conn = Cx_oracle.connect (Database_list[d]) #connect database

c = conn.cursor () # Get cursor

x = C.execute (sql) #sql_text "Execute SQL statement"

row = X.fetchall ()

For R in row:

Print R "" For cyclic output results

C.close ()

Conn.close ()

Tnsping Monitoring and judging network:

Cat >tnsping_check.sh

######################################################################

# tnsping_check.sh

# This script is tnsping check

# Author CZT

######################################################################

#!/bin/sh

. ~/.bash_profile

################### #tnsping function######################################

Tns_echo ()

{

If [' Tnsping $ | sed-n ' $p ' | awk ' {print '} ' = OK];then

Nowtime= ' Date +%t '

Avg_time= ' tnsping $i | grep OK | awk ' {print $} ' | Awk-f ' (' {print $} ' | awk ' {sum + = $}; End{print SUM/NR} "

echo "$NowTime: tnsping against $name $i check OK $avg _time seconds"

Else

echo "$NowTime: tnsping against $name $i check ERROR"

Fi

}

################### #database IP list######################################

# #crm_xxqd

Arr_lis_xxqd= ("172.xx.xx.xxx:1521" "172.16.xx.xx:1521" "172.16.xx.xxx:1521" "172.16.xx.xx:1521" "172.16.xx.xx:1521 ")

The contents of the IP list are omitted.

# #other_xxxdb

arr_lis_xxxdb= ("172.xx.xx.xx:1521" "172.xx.xx.xx:1521")

####################### #select Database list########################

Name=$1

#if [!-n $]

#then

#fi

Case $name in

‘‘)

name=$ (Uname-n)

;;

Esac

Case $name in

CRMDB-XXXDB|DZQD)

For i in ${arr_lis_dzqd[*]}

Do

Tns_echo $i

Done

;;

To omit the same part of the same content as the ... ""

Help

Echo ' [usage:] sh tnsping_check.sh yydb 100 '

Echo ' [Instance name] [type name] '

Echo ' Xxxd--crmdb-xxxd

To omit part of the content of repetition.

Echo ' Xxxdb--other-xxx3db '

;;

Esac

"Problem thinking"

1, for the choice of script and the way to be based on the convenience of the needs and the goal of the combination of itself, such as tnsping is the function of the database, so as to better achieve a variety of requirements functions.

2, for a delay standard formulation in tnsping scripts, it is necessary to set the impact of thresholds on business by combining business and actual network conditions;

3, for the database login query status, you can also use other query statements, sometimes need to query the information of multiple databases, you can better realize the demand for bulk query information.

"Summary":

1, the database comes with the function to have a good understanding, will be able to do more with less;

2, for the network delay problem, the establishment of threshold value is the embodiment of the function effect, otherwise it is only data query;

3, for the implementation of the function of the language has a lot of total, choose the correct language implementation, for cross-platform deployment has a lot of help.

Multiple database tnsping and login Single sign-on requirements