Tech Monger

TNSPING checks database server reachability with the help of details present inside tnsnames.ora against given data source name. If you have tnsping command line utility installed on your machine and able to check tnsping status from command line then you can execute same command from python scripts.

---

Recommended Reading and Prerequisites

---

Python TNSPING Example

In below example we will invoke tnsping command from python script using inbuilt subprocess module. Note that this only checks connectivity between your machine and database server. To make an actual connection you should use cx_Oracle and connect to oracle database from python.

py_tnsping.py

def tnsping(data_source_name):
    # Creating Command String.  Example : "tnsping data-source-name"
    command_string = "tnsping {d}".format(d=data_source_name)

    # Converts tnsping command list
    command = command_string.split()

    # Trigger Command
    process = Popen(command, stdout=PIPE, stderr=PIPE)

    # Stores stdout and stderr of command execution
    stdout, stderr = process.communicate()

    # Stores tnsping exit code.
    # Code will be 0 if tnsping succeeds.
    status_code = process.returncode
    
    return status_code,stdout, stderr

if __name__ == "__main__":
    # Command line input for data source name to be checked
    data_source_name = sys.argv[1]
    
    status_code, stdout, stderr = tnsping(data_source_name)
    
    # Print TNSPING Command Execution Output
    print stdout  

Example Execution

$ python py_tnsping.py my_data_source_name

TNS Ping Utility for Linux: Version 10.1.0.1.0 

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = example.com) (PORT = 1521))) (CONNECT_DATA = (SID = my_db_name)))

OK (80 msec)

Customization

If you want to take action based on whether tnsping succeeds or fails then you can use status code of the command. If status code returned is 0 then it indicates successful tnsping. Non zero status code would indicate failed tnsping.

if status_code == 0:
    # TNSPING Succeeded - DO SOME WORK
else:
    # TNSPING Failed - DO SOME OTHER WORK

You can also take the action based on specific status code. Refer oracle tnsping documentation to see all exit codes.

---

Conclusion