RE: vmstat output to db

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Thu, 29 Jul 2004 10:09:05 -0400
Message-ID: <4001DEAF7DF9BD498B58B45051FBEA650146C57F@25exch1.vicorpower.vicr.com>

Had a requirement to read SAR data & store that. The following extproc = C code did it for us from either SQL or a dbms_job.

char *sar_data(char *option)
{ FILE *p =3D NULL;

   char cmd[30];
   char rply[100];
   char bfr[100];
   char *r =3D NULL;

   sprintf(cmd, "/usr/bin/sar %s\n", option);    p =3D popen(cmd, "r");
   if(!p) return(NULL);
   while(fgets(bfr, sizeof(bfr), p))
   { if(bfr[0] =3D=3D '0' || bfr[0] =3D=3D '1' || bfr[0] =3D=3D '2')

      {  strcpy(rply, bfr);
      }

   }
   pclose(p);
   r =3D &rply[0];
   return(r);
}

Copy and modify at your pleasure.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
From: David [mailto:thump_at_cosmiccooler.org] Sent: Wednesday, July 28, 2004 6:12 PM
To: oracle-l_at_freelists.org
Subject: vmstat output to db

I'm startign to go bald tryign to figure out how to get this to work. = Any
insight, suggestions or solution is greatly appreciated. I'm simply trying to run vmstat on an interval, send the output to a = file
and then load the contents of that file into the database to be reported against:
#!/usr/bin/ksh

# First, we must set the environment . . . . export ORAENV_ASK=3DNO
ORACLE_SID=3DSDTEST01
export ORACLE_SID
. oraenv SDTEST01
export ORACLE_HOME

SERVER_NAME=3D`uname -a|awk '{print $2}'` typeset -u SERVER_NAME
export SERVER_NAME

# sample every 15 minutes (900 seconds) . . . . SAMPLE_TIME=3D900 while true
do

   vmstat ${SAMPLE_TIME} > /tmp/msg$$

# Note that Solaris does not have a wait CPU column cat /tmp/msg$$|sed 1,3d | awk '{ printf("%s %s %s %s %s %s %s\n", $1, = $8,
$9, $12, $20, $21, $22) }' | while read RUNQUE PAGE_IN PAGE_OUT SR USER_CPU SYSTEM_CPU IDLE_CPU
   do

      $ORACLE_HOME/bin/sqlplus -s / <<EOF set echo on
set feedback on
set verify on
spool vmstat.log

      insert into perfstat.stats$vmstat
                           values (
                             SYSDATE,
                             $SAMPLE_TIME,
                             '$SERVER_NAME',
                             $RUNQUE,
                             $PAGE_IN,
                             $PAGE_OUT,
                             $SR,
                             $USER_CPU,
                             $SYSTEM_CPU,
                             $IDLE_CPU,
                             0
                                  );
spool off
      EXIT

EOF
   done
done
rm /tmp/msg$$

The output to /tmp/msg(pid) gets created fine and I can do the insert fine, but no rows get loaded. "Funny" thing is I had this working at = one
point, but it stopped inserting after 24 rows and ever since then it seeems to not insert at all...

Cheers
--=20
..
David

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Jul 29 2004 - 09:06:25 CDT