Friday, August 26, 2011

Shrinking the results file to generate graphs using Excel

Here is an example of running swingbench using the clarbench client, and redirecting the results to the file
results_400users_aix_run2_phase1.txt and the error log to swing.err

bash-3.2$./charbench -c swingconfig.xml -a -v users,tpm,tps,resp > results_400users_aix_run2_phase1.txt 2> swing.err

This run was run for a period of 24 hours which generated a result file with 86279 lines
-rw-r--r--    1 oracle   oinstall    2523482 Aug 21 13:58 results_400users_aix_run2_phase1.txt

Author  :        Dominic Giles
Version :        2.4.0.764

Results will be written to results.xml.

Time            Users   TPM     TPS     Response
1:58:42 PM      0       0       0       0
1:58:43 PM      0       0       0       0
1:58:44 PM      0       0       0       0
1:58:45 PM      0       0       0       0
..........    .    .    .    .
..........    .    .    .    .
..........    .    .    .    .
..........    .    .    .    .
1:58:37 PM      398     37324   443     518
1:58:38 PM      398     37297   783     518
1:58:39 PM      398     37519   849     518
1:58:40 PM      398     37622   644     518
1:58:41 PM      398     37740   748     518
1:58:42 PM      398     37806   501     518
1:58:43 PM      -1      37787   781     518

Completed Run.


The problem I found with was that when I tried to create open the results with Excel spreadsheeti, Excel did not
like it as it had far more number of lines than what it would like.

So one way to get around this is to shrink the file. You see that there is a line entry for every second, and
this could definitely be reduced ie. we use 1 data line for every 10 lines.

This can be done with a simple perl script. What this scripts does is read the source file and transfers every
10th line to the shrunk file 


#!/usr/bin/perl
#
# Usage: ./shrink_results.pl <source_file> <shrunk_file>
#


$source_file=$ARGV[0];
$destin_file=$ARGV[1];

if ( ! open RESULT, "<$source_file") {
        usage();
        die "The result file does not exist!";
        exit (1);
        }

open SHRUNK, ">$destin_file" or die "Cannot open file the save the shrunk result
s.\n";

sub usage {
        print STDERR "Usage: ./shrink_results.pl result_file shrunk_file\n";
}

my $lnum = 1;
my $multi = 1;
while ($line = <RESULT>) {
        chomp($line);
        if ($lnum == 10*$multi) {
                print SHRUNK "$line\n";
                $multi++;
        }
        $lnum++;
}

close SHRUNK;
close RESULT;


bash-3.2$ ./shrink_results.pl results_400users_aix_run2_phase1.txt results_400users_aix_run2_phase1_shrunk.txt

This created a shrunk file with only 8627 lines:
bash-3.2$ ls -l results_400users_aix_run2_phase1*.txt
-rw-r--r--    1 oracle   oinstall    2523482 Aug 21 13:58 results_400users_aix_run2_phase1.txt
-rw-r--r--    1 oracle   oinstall     252333 Aug 23 22:25 results_400users_aix_run2_phase1_shrunk.txt

Excel liked the new shrunk file, and the graphs were created.

Thursday, August 25, 2011

ORA-01261 and ORA-01262 while using dbca to create a database

I was using dbca to create a database on my server with Oracle ASM as the storage for the database files.

My ASM diskgroup was called DATA, so while using the dbca GUI to create the database I entered "DATA" as below for the database area.



I then clicked the Next button on the screen, and moved to the next screen. I then entered some information on the next couple of screens and on clicking Finish, then creation of the database started.

This is when I saw the ORA-01261 and the ORA-01262 error messages as shown below.

 At this point click on the Abort button and start over again. This time when you are asked for the storage type for the database files, enter "+DATA" as shown below.

Things worked fine after there.

Tuesday, August 16, 2011

What are the Oracle backgroud processes

To find out the information on the Oracle backgroud processes there are a couple of ways.

One would be to look use the OS command to find information on the Oracle process/database instance.
"testasm" is the instance name in this case.



isvp14_ora> ps -ef | grep _testasm
  oracle  9568370        1   0 15:05:04      -  0:02 ora_smon_testasm
  oracle 19071098        1   0 15:05:04      -  0:01 oracle+ASM_asmb_testasm (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
  oracle 19333214        1   0 15:05:03      -  0:08 ora_ckpt_testasm
  oracle 19529820        1   0 15:05:03      -  0:15 ora_lgwr_testasm
  oracle 19595362        1   0 15:05:03      -  0:40 ora_dbw0_testasm
  oracle 19660900        1   0 15:05:03      -  0:01 ora_mman_testasm
  oracle 19726434        1   0 15:05:03      -  0:33 ora_dia0_testasm
  oracle 19857512        1   0 15:05:03      -  0:00 ora_psp0_testasm
  oracle 19923050        1   0 15:05:03      -  0:00 ora_gen0_testasm
  oracle 20316278        1   0 15:05:04      -  0:00 ora_s000_testasm
  oracle 20381826        1   0 15:05:03      -  0:22 ora_vktm_testasm
  oracle 20578446        1   0 15:05:03      -  0:04 ora_pmon_testasm
  oracle 20775082        1   0 15:05:04      -  0:00 ora_d000_testasm
  oracle 25296902        1   0 15:05:14      -  0:06 ora_cjq0_testasm
  oracle 25559076        1   0 15:05:23      -  0:00 ora_q001_testasm
  oracle 25821214        1   0 15:05:13      -  0:00 ora_qmnc_testasm
  oracle 25886744        1   0 15:05:04      -  0:00 ora_mark_testasm
  oracle 25952282        1   0 15:05:04      -  0:06 ora_mmon_testasm
  oracle 26017820        1   0 15:05:04      -  0:00 ora_asmb_testasm
  oracle 26083358        1   0 15:05:04      -  0:00 ora_rbal_testasm
  oracle 26214454        1   0 15:05:04      -  0:14 ora_mmnl_testasm
  oracle 26280002        1   0 15:05:03      -  0:00 ora_diag_testasm
  oracle 26345516        1   0 15:10:14      -  0:00 ora_smco_testasm
  oracle 26411078        1   0 15:05:03      -  0:00 ora_dbrm_testasm
  oracle 26476590        1   0 15:05:04      -  0:00 ora_reco_testasm
  oracle 35258456        1   0 09:31:30      -  0:00 ora_w000_testasm
  oracle 41156862        1   0 15:05:23      -  0:00 ora_q000_testasm
isvp14_ora>


or we could use the information in the v$process table for more accurate information.



SQL> select program from v$process where BACKGROUND=1;

PROGRAM
------------------------------------------------
oracle@isvp14_ora.storage.tucson.com (PMON)
oracle@isvp14_ora.storage.tucson.com (VKTM)
oracle@isvp14_ora.storage.tucson.com (GEN0)
oracle@isvp14_ora.storage.tucson.com (DIAG)
oracle@isvp14_ora.storage.tucson.com (DBRM)
oracle@isvp14_ora.storage.tucson.com (PSP0)
oracle@isvp14_ora.storage.tucson.com (DIA0)
oracle@isvp14_ora.storage.tucson.com (MMAN)
oracle@isvp14_ora.storage.tucson.com (DBW0)
oracle@isvp14_ora.storage.tucson.com (LGWR)
oracle@isvp14_ora.storage.tucson.com (CKPT)

PROGRAM
------------------------------------------------
oracle@isvp14_ora.storage.tucson.com (SMON)
oracle@isvp14_ora.storage.tucson.com (RECO)
oracle@isvp14_ora.storage.tucson.com (RBAL)
oracle@isvp14_ora.storage.tucson.com (ASMB)
oracle@isvp14_ora.storage.tucson.com (MMON)
oracle@isvp14_ora.storage.tucson.com (MMNL)
oracle@isvp14_ora.storage.tucson.com (MARK)
oracle@isvp14_ora.storage.tucson.com (W000)
oracle@isvp14_ora.storage.tucson.com (Q000)
oracle@isvp14_ora.storage.tucson.com (QMNC)
oracle@isvp14_ora.storage.tucson.com (SMCO)

PROGRAM
------------------------------------------------
oracle@isvp14_ora.storage.tucson.com (CJQ0)
oracle@isvp14_ora.storage.tucson.com (Q001)

24 rows selected.


SQL>