Linux/Unix shell scripts run SQL statements across servers and Instances

Linux/Unix shell scripts Execute SQL statements across servers and instances during database O & M, it is inevitable that you need to query databases across multiple servers and multiple Oracle instances. For example, you need to check the value of the open_cursor parameter in all current production environment databases to determine whether to increase the value. The database server to be viewed has multiple instances and multiple database servers. In this way, dozens of databases and hundreds of database servers are queried one by one, that means we have to get tired. It is like a method to write a shell script to patrol all servers and all instances on the server. 1. Use ssh-keygen to generate a key for fast Login

[Python] to automatically execute scripts across servers, you must implement password-free automatic login before you can jump between multiple servers. Therefore, you must first generate a logon key. Three steps are required to survive the logon key. Create a key on the local machine, copy the public key to the remote host, and append the public key to the authorized_keys of the remote host. An operation example is as follows: oracle @ linux1: ~> Mkdir ~ /. Ssh # first create the. ssh directory locally and grant the permission to oracle @ linux1: ~> Chmod 700 ~ /. Ssh oracle @ linux1: ~> Ssh-keygen-t rsa # Use ssh-keygen to generate a key pair, or Generating public/private rsa key pair using dsa. enter file in which to save the key (/users/oracle /. ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in/users/oracle /. ssh/id_rsa. your public key has been saved in/users/oracle /. ssh/id_rsa.pub. the key fingerprint is: 01: c8: 48: 01: f2: 3d: a7: B4: cd: 4a: 9c: 10: 2d: ba: ef: 4e oracle @ linux1 oracle @ linux2: ~> Mkdir ~ /. Ssh # create a. ssh directory on the remote server and grant the permission to oracle @ linux2: ~> Chmod 700 ~ /. Ssh oracle @ linux1: ~> Scp ~ /. Ssh/id_rsa.pub 172.161.196 :~ /. Ssh # copy The public key to The remote server, that is, The machine linux2 The authenticity of host' 172. 168.1.196 (172.161.196) 'Can't be established. RSA key fingerprint is 08: 3d: 69: 80: 85: 1d: ce: 57: 32: e0: 72: e0: 38: 66: 0c: 36. are you sure you want to continue connecting (yes/no )? Yes Warning: Permanently added '192. 168.1.196 '(RSA) to the list of known hosts. Password: id_rsa.pub 172 100% 0.4KB/s oracle @ linux2: ~> Cat ~ /. Ssh/id_rsa.pub> ~ /. Ssh/authorized_keys # append the public key to the authorized_keys oracle @ linux1: ~> Ssh 172.161.196 date # verify whether the password Thu Aug 22 10:50:47 HKT 2013 is required

 

2. Round-Robin of multiple servers and instances using tnsnames

[Python] # The following is a shell script for Round Robin through tnsnames, that is, you only need to obtain all instances under/etc/oratab from a remote server # Note: #. make sure that the local environment has a tnsnames connection string to all remote servers. If no connection exists, the system will receive an error # B. all users and passwords of the database to be inspected and have corresponding permissions # c. if the SQL statement to be executed is complicated, we can write the SQL statement to a separate file and then call oracle @ linux1: ~> More query_multi_inst_tns.sh # scripts + # Script Name: query_multi_inst_tns.sh | # Desc: This script login to different remote host where define in known_host file, | # after that look through oratab and execute SQL for each instance | # in local machine by tnsnames. | # Req: Configure a secure shell by ssh-keygen to all remote host | # Add Oracle Net string to local tnsnames. ora for each remote instance | # Author: Robinson | # Blog: http://blog.csdn.net/robinson_0612 | # Pipeline + #! /Bin/bash # ------------------------------------------ # Set environment vairable and define variable # ------------------------------------------ if [-f ~ /. Bash_profile]; then .~ /. Bash_profile fi ORATAB =/etc/oratab RHOST = ~ /. Ssh/known_hosts LOGFILE =/users/robin/dba_scripts/custom/log/query_multi_inst.log # login # take a loop in each hostname # login {for host in 'cat $ RHOST | awk '{ print $1} ''do echo "******************************* * ***** "echo" Current host is $ host. "echo "************************************" echo "" # --------------------------- # take a loop in ORATAB file # ------------------------- for db in 'ssh $ host cat $ ORATAB | egrep ': N |: y' | grep-v \ * | grep-v \ # | cut-d ":"-f1 'do echo "----------------------------------" echo "Current database is $ db. "echo" ---------------------------------- "$ ORACLE_HOME/bin/sqlplus-S usr/passwd @ $ db <EOF col name format a30 col value format a20 select name, value from v \ $ parameter where name = 'open _ cursors '; exit EOF done} | tee-a $ {LOGFILE} 2> & 1 exit

 

3. patrol all instances directly in the remote server environment

[Python] # The following is a shell script that executes SQL statements and performs round-robin in the remote host environment. This method can be used without or without knowing the database user and password # Note: #. this method uses User Authentication Based on the operating system. Ensure that this method is supported # B. if the SQL statement to be executed is complex, the nested shell script is also complex. If the file is written as a. SQL file, each server must have a copy of oracle @ linux1: ~> More query_multi_inst_notns.sh #! /Bin/bash # scripts + # Script Name: query_multi_inst_notns.sh | # Desc: This script login to different remote host where define in known_host file, | # after that look through oratab and execute SQL in each remote instance. | # Req: Configure a secure shell by ssh-keygen to all remote host | # Author: Robinson | # Blog: http://blog.csdn.net/robinson_0612 | # Environment + # ------------------------------------------ # Set environment vairable and define variable # ------------------------------------------ if [-f ~ /. Bash_profile]; then .~ /. Bash_profile fi ORATAB =/etc/oratab RHOST = ~ /. Ssh/known_hosts LOGFILE =/users/robin/dba_scripts/custom/log/query_multi_inst_notns.log # login # take a loop in each hostname # login {for host in 'cat $ RHOST | awk '{ print $1} ''do echo "******************************* * ***** "echo" Current host is $ host. "echo "************************************" echo "" # --------------------------- # take a loop in ORATAB file # ------------------------- for db in 'ssh $ host cat $ ORATAB | egrep ': N |: y' | grep-v \ * | grep-v \ # | cut-d ":"-f1 'do echo "----------------------------------" echo "Current database is $ db. "echo" ---------------------------------- "home = 'ssh $ host cat $ ORATAB | egrep ': N |: y' | grep-v \ * | grep-v \ # | grep $ db | cut-d ": "-f2 'ssh $ host" export ORACLE_SID = $ db export ORACLE_HOME =$ {home }$ {ORACLE_HOME}/bin/sqlplus-S/as sysdba <EOF col name format a30 col value format a20 select name, value from v \ "\ $" parameter where name = 'open _ cursors '; exit EOF "done} | tee-a $ {LOGFILE} 2> & 1 exit

 

4. Test

[Python] # Use tnsnames to test oracle @ linux1: ~>. /Query_multi_inst_tns.sh ************************************ Current host is 172.162.196. * ********************************** -------------------------------- Current database is US001. parameter name value ------------------------------ ---------------------- open_cursors 300 -------------------------------- Current database is US002 .------------------------ ------------ Name value ---------------------------- ---------------------- open_cursors 300 concurrent Current database is US003. # This is the case without tnsnames handle ERROR: ORA-12154: TNS: cocould not resolve the connect identifier specified # round-robin test oracle @ linux1: ~>. /Query_multi_inst_notns.sh ************************************ Current host is 172.161.196. * ********************************** -------------------------------- Current database is US001. specify name value when ------------------ open_cursors 300 seconds Current database is US002. specify name value when -------------------- open_cursors 300 seconds Current database is US005. # When the instance on the remote server is not started, choose select name, value from v $ parameter where name = 'open _ cursors '* ERROR at line 1: ORA-01034: ORACLE not available