SYSDBA connections not working (eg for standby database)

[dtseiler]

The latest release of check_oracle_health did not work for SYSDBA connections because the code was setting the username to "sysdba" when it needs to be null.

I have a patch to correct this as well as use the ora_session_mode keywords instead of the hex constant for easier readability and in case those constants change.

--- check_oracle_health 2013-03-21 14:49:52.000000000 -0500
+++ check_oracle_health_dts     2013-03-25 09:05:47.000000000 -0500
@@ -4739,6 +4739,7 @@

 use strict;
 use Net::Ping;
+use DBD::Oracle qw(:ora_session_modes);

 our @ISA = qw(DBD::Oracle::Server::Connection);

@@ -4803,15 +4804,17 @@
       alarm($self->{timeout} - 1); # 1 second before the global unknown timeout
       my $dsn = sprintf "DBI:Oracle:%s", $self->{connect};
       my $connecthash = { RaiseError => 0, AutoCommit => $self->{commit}, PrintError => 0 };
+      my $username = $self->{username};
       if ($self->{username} eq "sys" || $self->{username} eq "sysdba") {
         $connecthash = { RaiseError => 0, AutoCommit => $self->{commit}, PrintError => 0,
-              #ora_session_mode => DBD::Oracle::ORA_SYSDBA
-              ora_session_mode => 0x0002  };
+              ora_session_mode => ORA_SYSDBA   };
         $dsn = sprintf "DBI:Oracle:";
+        $username = '';
       }
+       $self->debug("Connecting to " . $dsn);
       if ($self->{handle} = DBI->connect(
           $dsn,
-          $self->{username},
+          $username,
           $self->{password},
           $connecthash)) {
         $self->{handle}->do(q{

[dtseiler]

I accidentally left an extra debug line in there.

[dtseiler]

Also, FYI, I do get this output when I try to run on standby, but only in verbose mode. The check itself returns cleanly.

Wed Apr 24 15:00:19 2013: fetchrow_array: select dbms_utility.port_string,sys_context('userenv', 'session_user'),i.thread#,i.parallel, i.instance_name, d.name FROM dual, v$instance i, v$database d Wed Apr 24 15:00:19 2013: args: $VAR1 = [];

bumm Can't call method "execute" on an undefined value at /usr/local/nagios/libexec/check_oracle_health_dts line 5004. DBD::Oracle::Server::Connection::Dbi bumm ORA-00904: "DBMS_UTILITY"."PORT_STRING": invalid identifier (DBD ERROR: error possibly near <> indicator at char 8 in ' select <>dbms_utility.port_string,sys_context('userenv', 'session_user'),i.thread#,i.parallel, i.instance_name, d.name FROM dual, v$instance i, v$database d ') DBD::Oracle::Server::Connection::Dbi

[dtseiler]

You could use v$database.platform_name on standby to get the OS platform, but that's only available in 10g or higher. The output is somewhat different so you'd need to handle the if/else statements that check the values.

[dtseiler]

Or you could draw the hard line and and say future versions of check_oracle_health only work for 10g or higher (at least for standby support). I think at this point in time, cutting off 8i/9i support in future versions isn't a horrible idea.