Allan’s Musings

Just another weblog

The Power of Ruby

Posted by Allan Peda on May 7, 2009

The other day I came across Oracle error ORA-01555 during the backup of a database. Thankfully Oracle had some good advice on addressing the issue here and I tossed together a Ruby script to make adjusting the system a bit more automated. Admittedly this could be done in one block of PLSQL, but it was easier to generate the DDL on the fly and print it our prior to running the script. Ruby rocks in this way, being able to do everything perl does, but (honestly I must admit) better, if only because it handles exceptions more neatly. To top all this off, I am not even talking about full application deployment with Rails, although Oracle covers that topic at great length.

# database stuff
require 'dbi'
require 'oci8'

# require 'rubygems'
# require 'optparse'

@verbose = false
def echo( arg )
  if @verbose
    puts arg

   # db handle
   oph = DBI.connect('DBI:OCI8:DB',  'system',  'pass')
   # first fetch prior recorded timestamp from the monitorin DB
   # only need first row
   tuple_snapshot_undo = oph.select_one(
      %Q[ /* tune_undotbs.rb */
            MIN(snap_id) as min_snap_id,
            MAX(snap_id) as max_snap_id
         FROM dba_hist_snapshot
   min_snap_id = tuple_snapshot_undo[0].to_i
   max_snap_id = tuple_snapshot_undo[1].to_i
   # puts "#{min_snap_id}, #{max_snap_id}"
   tune_proc = %Q[
        l_task_name  VARCHAR2(30) := '#{min_snap_id}_#{max_snap_id}_AWR_SNAPSHOT_UNDO';
        l_object_id  NUMBER;
        -- Create an ADDM task.
        DBMS_ADVISOR.create_task (
          advisor_name      => 'Undo Advisor',
          task_name         => l_task_name,
          task_desc         => 'Undo Advisor Task');

        DBMS_ADVISOR.create_object (
          task_name   => l_task_name,
          object_type => 'UNDO_TBS',
          attr1       => NULL, 
          attr2       => NULL, 
          attr3       => NULL, 
          attr4       => 'null',
          attr5       => NULL,
          object_id   => l_object_id);

        -- Set the target object.
        DBMS_ADVISOR.set_task_parameter (
          task_name => l_task_name,
          parameter => 'TARGET_OBJECTS',
          value     => l_object_id);

        -- Set the start and end snapshots.
        DBMS_ADVISOR.set_task_parameter (
          task_name => l_task_name,
          parameter => 'START_SNAPSHOT',
          value     => #{min_snap_id});

        DBMS_ADVISOR.set_task_parameter (
          task_name => l_task_name,
          parameter => 'END_SNAPSHOT',
          value     => #{max_snap_id});

        -- Execute the task.
        DBMS_ADVISOR.execute_task(task_name => l_task_name);
rescue DBI::DatabaseError => e
     STDERR.puts "Error code:    #{e.err}"
     STDERR.puts "Error message: #{e.errstr}"
     oph.disconnect if oph

Sorry, the comment form is closed at this time.

%d bloggers like this: