9 Replies Latest reply: Dec 5, 2012 4:30 AM by 846231 RSS

    How to write a pl/sql proc that checks remote db?

    846231
      Hi All,

      I have a one critical remote PROD database that I want to check every 10 mins for its connectivity. If connection fails then an email & SMS is sent to me.
      My question is what is the best way to check if the remote database is up and running?

      Can I use sqlplus system/manager@PROD? But sometimes this took so long and hanging. I want the fastest response time?

      How do I write a pl/sql proc check connection? That do something like the ff:

      I created a table for my tnsname.ora entries.
      cursor is c1 select dbname from tnsnames_tbl;
      begin
          connect system/manager@c1.dbname;
          print c1.dbname || 'DB Connection OK';
          exception
             when others;
              print c1.dbname || 'DB Connection Not OK';
          end;
      end;
      Something like this ?

      Thanks,
      Kinz
        • 1. Re: How to write a pl/sql proc that checks remote db?
          ShishirTekadeR
          Step 1:

          Create DB link from dev (Monitoring database) to biz (monitored database):
          Code: [Select all] [Show/ hide]
          create database link Test_remote connect to <monitoring_user> identified by <pwd> using 'tns_string';



          Step 2:

          Check the remote database (biz) availability using the following code:

          Code: [Select all] [Show/ hide]
          set serveroutput on
          declare
          err_msg varchar2(100);
          begin
          execute immediate 'select sysdate from dual@test_remote';
          exception
          when others then
          err_msg := SUBSTR(SQLERRM, 1, 100);
          begin
                    dbms_output.put_line('Error encountered: ' || err_msg);
               end;
          end;


          I hope this does resolve your query.

          ==========================================
          Best Regards
          SHISHIR TEKADE.
          • 2. Re: How to write a pl/sql proc that checks remote db?
            Billy~Verreynne
            Not really feasible doing this at PL/SQL level.

            The reason is that TCP's greatest strength is robustness. TCP will try as hard as possible to succeed, before failing. It was designed to still work over severely damaged and broken communication infrastructure in a nuclear aftermath. A TCP connection can take up to 15 minutes, perhaps even more, before failing. It can be as slow as a turtle - but it wears a hardshell. (unlike UDP that is the opposite)

            So if you want to test TCP connectivity, you need to craft your own custom code to do it, implementing your assumptions about packet latency, jitters and drops, and so on.

            Else you need to use a standard TCP socket, set a timeout, attempt to connect - and hope for the best.

            This approach I demonstrated in {message:id=10111306}.

            If the TCP connection test works, it means the Listener is up. Does not mean that the database itself is up. Which means then using a database link to test. And this in turn can hang because of issues with archive logging being stuck, insufficient idle shared servers, etc.
            • 3. Re: How to write a pl/sql proc that checks remote db?
              846231
              I thank you all (shishir and bill) :)

              Billy, you really explain like a genius, expert, all-knowing, guru, master of all , etc, etc ;)

              Can I copy all the pl/sql codes you have made? or buy it per piece.

              I want the code that sends email and SMS.
              • 4. Re: How to write a pl/sql proc that checks remote db?
                EdStevens
                KinsaKaUy? wrote:
                I thank you all (shishir and bill) :)

                Billy, you really explain like a genius, expert, all-knowing, guru, master of all , etc, etc ;)

                Can I copy all the pl/sql codes you have made? or buy it per piece.

                I want the code that sends email and SMS.
                Why don't you just configure enterprise manager to do this for you?
                • 5. Re: How to write a pl/sql proc that checks remote db?
                  846231
                  Thanks Ed :)
                  Why don't you just configure enterprise manager to do this for you?
                  In my case OEM isn't an option. since we have other instances located at three different customer networks, where we have no permanent connection to (or between). :(
                  • 6. Re: How to write a pl/sql proc that checks remote db?
                    Billy~Verreynne
                    KinsaKaUy? wrote:

                    Billy, you really explain like a genius, expert, all-knowing, guru, master of all , etc, etc ;)
                    Thanks. Nice comment, but one I do not agree with.
                    Can I copy all the pl/sql codes you have made? or buy it per piece.
                    I want the code that sends email and SMS.
                    Code freely shared is to be freely used/abused. :-)
                    • 7. Re: How to write a pl/sql proc that checks remote db?
                      EdStevens
                      KinsaKaUy? wrote:
                      Thanks Ed :)
                      Why don't you just configure enterprise manager to do this for you?
                      In my case OEM isn't an option. since we have other instances located at three different customer networks, where we have no permanent connection to (or between). :(
                      No permanent connection needed. The oem agent, running on the database server, checks system availability and sends an email when it detects non-availability. That email travels over normal email/internet channels.
                      • 8. Re: How to write a pl/sql proc that checks remote db?
                        846231
                        Honestly, I am stressed setting/configuring OEM Grid, and all I need is just a little function :(
                        • 9. Re: How to write a pl/sql proc that checks remote db?
                          846231
                          Code freely shared is to be freely used/abused.
                          Hi bill,

                          Can you share me please your pl/sql code that send email when something is wrong in your database. :)

                          Thanks