Monday, November 7, 2011

Setting up R on Red Hat Linux

Thought of giving R programing a shot to create graph, so I downloaded the source code and compiled it on my Red Hat Linux server.


[root@isvx7 ~]# uname -a
Linux isvx7.storage.tucson.ibm.com 2.6.18-274.3.1.el5 #1 SMP Fri Aug 26 18:49:02 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux


So as user root I created a directory called r_prog. Under directory I created a directory called
source. I then cd'ed on to the source directory and downloaded the source code for R.


[root@isvx7 source]# wget http://cran.r-project.org/src/base/R-2/R-2.14.0.tar.gz

I next untared to .gz file


[root@isvx7 source]# tar -xvf R-2.14.0.tar.gz

Next I cd onto the directory R-2.14.0 and ran "configure"


[root@isvx7 source]# ./configure

.....
.....
....

R is now configured for x86_64-unknown-linux-gnu

  Source directory:          .
  Installation directory:    /usr/local

  C compiler:                gcc -std=gnu99  -g -O2
  Fortran 77 compiler:       gfortran  -g -O2

  C++ compiler:              g++  -g -O2
  Fortran 90/95 compiler:    gfortran -g -O2
  Obj-C compiler:

  Interfaces supported:      X11
  External libraries:        readline
  Additional capabilities:   PNG, JPEG, TIFF, NLS, cairo
  Options enabled:           shared BLAS, R profiling, Java

  Recommended packages:      yes

configure: WARNING: you cannot build PDF versions of the R manuals
configure: WARNING: you cannot build PDF versions of all the help pages
[root@isvx7 R-2.14.0]#

I then ran the "make" and "make check" command as shown below.

[root@isvx7 R-2.14.0]# make
......
......
......


[root@isvx7 R-2.14.0]# make check
make[1]: Entering directory `/root/r_prog/source/R-2.14.0/tests'
make[2]: Entering directory `/root/r_prog/source/R-2.14.0/tests'
make[3]: Entering directory `/root/r_prog/source/R-2.14.0/tests/Examples'
Testing examples for package âbaseâ
  comparing âbase-Ex.Routâ to âbase-Ex.Rout.prevâ ...
5654c5654
< 54
---
> 14
Testing examples for package âtoolsâ
  comparing âtools-Ex.Routâ to âtools-Ex.Rout.saveâ ... OK
Testing examples for package âutilsâ
  comparing âutils-Ex.Routâ to âutils-Ex.Rout.prevâ ... OK
Testing examples for package âgrDevicesâ
  comparing âgrDevices-Ex.Routâ to âgrDevices-Ex.Rout.saveâ ... OK
Testing examples for package âgraphicsâ
  comparing âgraphics-Ex.Routâ to âgraphics-Ex.Rout.saveâ ... OK
Testing examples for package âstatsâ
  comparing âstats-Ex.Routâ to âstats-Ex.Rout.saveâ ... OK
Testing examples for package âdatasetsâ
  comparing âdatasets-Ex.Routâ to âdatasets-Ex.Rout.saveâ ... OK
Testing examples for package âmethodsâ
  comparing âmethods-Ex.Routâ to âmethods-Ex.Rout.prevâ ... OK
..............................................................
..............................................................
..............................................................
..............................................................
running code in 'reg-plot-latin1.R' ... OK
  comparing 'reg-plot-latin1.ps' to './reg-plot-latin1.ps.save' ... OK
make[3]: Leaving directory `/root/r_prog/source/R-2.14.0/tests'
make[2]: Leaving directory `/root/r_prog/source/R-2.14.0/tests'
make[2]: Entering directory `/root/r_prog/source/R-2.14.0/tests'
running tests of Internet and socket functions
  expect some differences
make[3]: Entering directory `/root/r_prog/source/R-2.14.0/tests'
running code in 'internet.R' ... OK
  comparing 'internet.Rout' to './internet.Rout.save' ...17c17
< [1] 3373
---
> [1] 3383
 OK
make[3]: Leaving directory `/root/r_prog/source/R-2.14.0/tests'
make[2]: Leaving directory `/root/r_prog/source/R-2.14.0/tests'
make[1]: Leaving directory `/root/r_prog/source/R-2.14.0/tests'
[root@isvx7 R-2.14.0]#

If you get a whole bunch of OK's then R is ready to go.


[root@isvx7 R-2.14.0]# ls
bin            COPYING  libtool      Makefrag.cc     ONEWS         tests
ChangeLog      doc      m4           Makefrag.cc_lo  OONEWS        tools
config.log     etc      Makeconf     Makefrag.cxx    po            VERSION
config.site    include  Makeconf.in  Makefrag.m      README
config.status  INSTALL  Makefile     modules         share
configure      lib      Makefile.fw  NEWS            src
configure.ac   library  Makefile.in  NEWS.pdf        SVN-REVISION
[root@isvx7 R-2.14.0]#

Now go into the bin directory and run R.


[root@isvx7 R-2.14.0]# cd bin
[root@isvx7 bin]# ls
BATCH    config   javareconf     pager   Rdconv      Rprof    Stangle
build    exec     libtool        R       Rdiff       Rscript  Sweave
check    f77_f2c  LINK           Rcmd    REMOVE      rtags
COMPILE  INSTALL  mkinstalldirs  Rd2dvi  Rplots.pdf  SHLIB
[root@isvx7 bin]# ./R

R version 2.14.0 (2011-10-31)
Copyright (C) 2011 The R Foundation for Statistical Computing
ISBN 3-900051-07-0
Platform: x86_64-unknown-linux-gnu (64-bit)

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

  Natural language support but running in an English locale

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.

[Previously saved workspace restored]

>

At this point I tried the example provided in for the following link:
http://www.harding.edu/fmccown/r/


>
> cars <- c(1, 3, 6, 4, 9)
> plot(cars)
> q()
Save workspace image? [y/n/c]: y
[root@isvx7 bin]#
This created a graph called Rplots.pdf under the current directory.


Thursday, October 20, 2011

Protocol used by RMAN to communicate with the Oracle server

I just wanted to see what happens from a server process point of view when I run "rman" from a
the command. So from one terminal I ran "rman" as follows
bash-3.2$ rman target /
When I did that I observed that there were three new processes
that were started on the server. The rman process is the obvious one, but then other two processes which were the child processes of the rman process.
>   oracle 32309274 30998642   0 22:01:57  pts/0  0:00 rman target /
55a57
>   oracle 34275468 32309274   0 22:01:57      -  0:00 oracletestasm (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
58a61
>   oracle 37421080 32309274   0 22:01:58      -  0:00 oracletestasm (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
isvp14_ora>
Which got me curious about the PROTOCOL=beq. Reading throught the docs I found out the following abot the BEQ Protocol

  • The BEQ Protocol Adapter, is both a communications mechanism and a process spawning mechanism.
  •  If a service name is not specified, either directly by the user on the command line or the login screen, or indirectly through an environment variable, then the BEQ Protocol Adapter will be used.
  • When the BEQ Protocol is used a dedicated server will always be used, and the multi-threaded server will never be used.
  •  This dedicated server is started automatically by the BEQ Protocol Adapter, which waits for the server process to start and attach to an existing SGA. If the startup of the server process is successful, the BEQ Protocol Adapter then provides inter-process communication via UNIX pipes.
  •  An important feature of the BEQ Protocol Adapter is that no network Listener is required for its operation, since the adapter is linked into the client tools and directly starts its own server process with no outside interaction.
  •  Another thing to note is that the BEQ Protocol Adapter is always installed, and always linked in to all client tools.

Wednesday, October 12, 2011

Oracle ASM and data rebalancing between the volumes

In this post I want to show how data is distributed evenly between all the volumes of an Oracle ASM diskgroup eg. If we have 2 x 100 GB volumes in our disk group, and 100 GB of data. Then the data is distributed evenly between each of the volumes ie. the first volume will have 50GB and the second volume will have 50GB. Now if I add a 3rd volume to the disk group, then the data will be re-balanced between the three volumes ie. each volume will have 33.33GB of the data.

The initial distribution of data between the volumes, and the later re-balancing of the data is done based on the current data capacity, and not on the I/O pattern. This means that if we do have a table that is heavily accessed, we could potentially have hot-spots and degradation in performance due to the limitations of HDD disks.

Here is the informtion I got just after creating the Oracle ASM diskgroup +DATA using hdisk21, hdisk22, hdisk23, and hdisk24 on an AIX system. Each volume in this case is 300 GB


SQL> select name, path, free_mb, total_mb from v$asm_disk;

NAME
------------------------------
PATH
--------------------------------------------------------------------------------
   FREE_MB   TOTAL_MB
---------- ----------

DATA_0000
/dev/rhdisk21

NAME
------------------------------
PATH
--------------------------------------------------------------------------------
   FREE_MB   TOTAL_MB
---------- ----------
    307180     307200

DATA_0001
/dev/rhdisk22
    307185     307200

DATA_0002

NAME
------------------------------
PATH
--------------------------------------------------------------------------------
   FREE_MB   TOTAL_MB
---------- ----------
/dev/rhdisk23
    307184     307200

DATA_0003
/dev/rhdisk24
    307185     307200


4 rows selected.

SQL>

After creating the database, schema, tables, and filling it with data, here is what I have. As you see the data is evenly distributed between all the volumes of the Oracle ASM diskgroup.


SQL> select name,path, free_mb, total_mb from v$asm_disk;

NAME
------------------------------
PATH
--------------------------------------------------------------------------------
   FREE_MB   TOTAL_MB
---------- ----------

DATA_0000
/dev/rhdisk21

NAME
------------------------------
PATH
--------------------------------------------------------------------------------
   FREE_MB   TOTAL_MB
---------- ----------
    161103     307200

DATA_0001
/dev/rhdisk22
    161111     307200

DATA_0002

NAME
------------------------------
PATH
--------------------------------------------------------------------------------
   FREE_MB   TOTAL_MB
---------- ----------
/dev/rhdisk23
    161113     307200

DATA_0003
/dev/rhdisk24
    161108     307200

4 rows selected.

SQL>

Tuesday, October 11, 2011

Increase the redo log file size on a live system

While running a workload on my test system I noticed in the AWR report that in the Top 5 Timed Foreground Events "log file switch (checkpoint incomplete)" was on the top. It was taking around 48.43% DB Time which is rather high.

To resolve the issue it is recommended to increase the size of the redo logs and/or add more redo log groups.

It is not possible to increase the size of an existing red log file, so on a live system where the workload was running I did the following to increase the size of the redo logs:

I added 3 new log files each of size 250M

SQL> alter database add logfile group 4 size 250M;

Database altered.

SQL> alter database add logfile group 5 size 250M;

Database altered.

SQL> alter database add logfile group 6 size 250M;

Database altered.

SQL>
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 ACTIVE
         2 INACTIVE
         3 ACTIVE
         4 CURRENT
         5 UNUSED
         6 UNUSED

6 rows selected.

SQL>
SQL> select l.bytes/1024/1024 "Meg",g.member from v$log l,v$logfile g where l.group# = g.group#;

       Meg
----------
MEMBER
--------------------------------------------------------------------------------
        50
+DATA/testasm/onlinelog/group_3.263.764177393

        50
+DATA/testasm/onlinelog/group_2.262.764177393

        50
+DATA/testasm/onlinelog/group_1.261.764177393


       Meg
----------
MEMBER
--------------------------------------------------------------------------------
       250
+DATA/testasm/onlinelog/group_4.276.764264429

       250
+DATA/testasm/onlinelog/group_5.277.764264441

       250
+DATA/testasm/onlinelog/group_6.278.764264449


6 rows selected.


The log files need to be inactive to drop then, so dropped group 1 and 3 first.

SQL> alter database drop logfile group 1;

Database altered.


SQL> alter database drop logfile group 3;

Database altered.

SQL>
SQL> alter system checkpoint;

System altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         2 NO  INACTIVE
         4 NO  INACTIVE
         5 NO  ACTIVE
         6 NO  CURRENT

In then dropped log file 2:

SQL> alter database drop logfile group 2;

Database altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         4 NO  INACTIVE
         5 NO  ACTIVE
         6 NO  CURRENT

SQL>
SQL> alter system switch logfile;

System altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         4 NO  ACTIVE
         5 NO  CURRENT
         6 NO  ACTIVE


SQL>

There we go, I now have 3 redo log each of 250M

SQL> select l.bytes/1024/1024 "Meg",g.member from v$log l,v$logfile g where l.group# = g.group#;

       Meg
----------
MEMBER
--------------------------------------------------------------------------------
       250
+DATA/testasm/onlinelog/group_4.276.764264429

       250
+DATA/testasm/onlinelog/group_5.277.764264441

       250
+DATA/testasm/onlinelog/group_6.278.764264449

Monday, October 10, 2011

Increasing the Maximum number of PROCESSES allowed per user in AIX 7.1

One thing to remember while running Oracle benchmark clients is to increase the maximum number of processes the user(oracle in this case) can handle on the database server(AIX) side. You can use "ulimit -a" to check the current "max user processes"

To increase the "max user processes" on an AIX server, run smitty -> select System Environments -> select  Change / Show Characteristics of Operating System -> Maximum number of PROCESSES allowed per user       [600]  ->  press Enter
 



isvp14_ora> smitty
Login  as user oracle and run "ulimit -a". You will see that the maximum user processes has increased to 600.

Wednesday, September 28, 2011

Oracle ASM: ORA-01078: failure in processing system parameters and ORA-29701: unable to connect to Cluster Synchronization Service

Recently we had a power outage in our lab which caused all the Linux servers running Oracle database, switches, storage to go down. When the power was restored the servers, storages, and switches were brought up cleanly.

I noticed that Oracle ASM instance was down, so I tried to manually start it and got the ORA-01078 and ORA-29701 error messages.



sh-3.2$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 28 15:20:59 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
SQL>



-bash-3.2$ . ./.bash_profile_grid
-bash-3.2$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/grid
-bash-3.2$ which crsctl
/u01/app/oracle/product/11.2.0/grid/bin/crsctl

Tried to use crsctl to start all the Oracle resources

-bash-3.2$ crsctl start resource -all
CRS-5702: Resource 'ora.LISTENER.lsnr' is already running on 'isvx7'
CRS-2672: Attempting to start 'ora.cssd' on 'isvx7'
CRS-2679: Attempting to clean 'ora.diskmon' on 'isvx7'
CRS-2681: Clean of 'ora.diskmon' on 'isvx7' succeeded
CRS-2672: Attempting to start 'ora.diskmon' on 'isvx7'
CRS-2676: Start of 'ora.diskmon' on 'isvx7' succeeded
CRS-2676: Start of 'ora.cssd' on 'isvx7' succeeded
CRS-2672: Attempting to start 'ora.asm' on 'isvx7'
CRS-2676: Start of 'ora.asm' on 'isvx7' succeeded
CRS-2672: Attempting to start 'ora.DATA.dg' on 'isvx7'
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA"
CRS-2674: Start of 'ora.DATA.dg' on 'isvx7' failed
CRS-2679: Attempting to clean 'ora.DATA.dg' on 'isvx7'
CRS-2681: Clean of 'ora.DATA.dg' on 'isvx7' succeeded
CRS-4000: Command Start failed, or completed with errors.
-bash-3.2$

On the Linux server running Oracle

[root@isvx7 ~]# multipath -d -l
mulipath.conf line 111, invalid keyword: prio
mpath144 (36005076802828000c000000000000050) dm-3 IBM,2145
[size=300G][features=0][hwhandler=0][rw]
\_ round-robin 0 [prio=0][active]
 \_ 5:0:3:3 sdaf 65:240 [active][undef]
 \_ 6:0:3:3 sdag 66:0   [active][undef]
 \_ 5:0:0:3 sdh  8:112  [active][undef]
 \_ 6:0:0:3 sdi  8:128  [active][undef]
 \_ 6:0:1:3 sdp  8:240  [active][undef]
 \_ 5:0:1:3 sdq  65:0   [active][undef]
 \_ 5:0:2:3 sdx  65:112 [active][undef]
 \_ 6:0:2:3 sdy  65:128 [active][undef]
mpath143 (36005076802828000c000000000000053) dm-2 IBM,2145
[size=300G][features=0][hwhandler=0][rw]
\_ round-robin 0 [prio=0][active]
 \_ 5:0:3:2 sdad 65:208 [active][undef]
 \_ 6:0:3:2 sdae 65:224 [active][undef]
 \_ 5:0:0:2 sdf  8:80   [active][undef]
 \_ 6:0:0:2 sdg  8:96   [active][undef]
 \_ 5:0:1:2 sdn  8:208  [active][undef]
 \_ 6:0:1:2 sdo  8:224  [active][undef]
 \_ 5:0:2:2 sdv  65:80  [active][undef]
 \_ 6:0:2:2 sdw  65:96  [active][undef]
mpath142 (36005076802828000c000000000000052) dm-1 IBM,2145
[size=300G][features=0][hwhandler=0][rw]
\_ round-robin 0 [prio=0][active]
 \_ 5:0:3:1 sdab 65:176 [active][undef]
 \_ 6:0:3:1 sdac 65:192 [active][undef]
 \_ 5:0:0:1 sdc  8:32   [active][undef]
 \_ 6:0:0:1 sde  8:64   [active][undef]
 \_ 6:0:1:1 sdl  8:176  [active][undef]
 \_ 5:0:1:1 sdm  8:192  [active][undef]
 \_ 5:0:2:1 sdt  65:48  [active][undef]
 \_ 6:0:2:1 sdu  65:64  [active][undef]
mpath141 (36005076802828000c000000000000051) dm-0 IBM,2145
[size=300G][features=0][hwhandler=0][rw]
\_ round-robin 0 [prio=0][active]
 \_ 5:0:3:0 sdaa 65:160 [active][undef]
 \_ 5:0:0:0 sdb  8:16   [active][undef]
 \_ 6:0:0:0 sdd  8:48   [active][undef]
 \_ 6:0:1:0 sdj  8:144  [active][undef]
 \_ 5:0:1:0 sdk  8:160  [active][undef]
 \_ 6:0:2:0 sdr  65:16  [active][undef]
 \_ 5:0:2:0 sds  65:32  [active][undef]
 \_ 6:0:3:0 sdz  65:144 [active][undef]

When I looked at the owner and group of the disks, I noticed that the they had changed to owner root and group disk. No wonder Oracle is not seeing them.

[root@isvx7 ~]# ls -l /dev/mapper/mpath141
brw-rw---- 1 root disk 253, 0 Sep 28 09:57 /dev/mapper/mpath141
[root@isvx7 ~]# ls -l /dev/mapper/mpath142
brw-rw---- 1 root disk 253, 1 Sep 28 09:57 /dev/mapper/mpath142
[root@isvx7 ~]# ls -l /dev/mapper/mpath143
brw-rw---- 1 root disk 253, 2 Sep 28 09:57 /dev/mapper/mpath143
[root@isvx7 ~]# ls -l /dev/mapper/mpath144
brw-rw---- 1 root disk 253, 3 Sep 28 09:57 /dev/mapper/mpath144
Changed the owner and group of the volumes to oracle and dba

[root@isvx7 ~]# chown -R oracle:dba /dev/mapper/mpath141
[root@isvx7 ~]# chown -R oracle:dba /dev/mapper/mpath142
[root@isvx7 ~]# chown -R oracle:dba /dev/mapper/mpath143
[root@isvx7 ~]# chown -R oracle:dba /dev/mapper/mpath144
[root@isvx7 ~]# ls -l /dev/mapper/mpath143
brw-rw---- 1 oracle dba 253, 2 Sep 28 09:57 /dev/mapper/mpath143
[root@isvx7 ~]#

When I brought up asmca. I saw that the my +DATA diskgroup could now be seen, but was not mounted. I mounted the diskgroup, and everything worked fine after that.



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>

Thursday, July 21, 2011

ORA-00018: maximum number of sessions exceeded

Again, while running my charbench client against the swingbench Order Entry schema I got the following error message:

ORA-00018: maximum number of sessions exceeded

On googling I found an excellent thread which explains how connections, sessions, and processes are related in Oracle.
http://asktom.oracle.com/pls/asktom/f?p=100:11:413703333046318::::P11_QUESTION_ID:5671284058977

Here is what I did to find out my current value of session, and then increase its value.




SQL> show parameter sessions;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
sessions integer 400
shared_server_sessions integer
SQL> alter system set sessions=800 scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 3540881408 bytes
Fixed Size 2211864 bytes
Variable Size 1644171240 bytes
Database Buffers 1879048192 bytes
Redo Buffers 15450112 bytes
Database mounted.
Database opened.
SQL> show parameter sessions;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size integer 0
java_soft_sessionspace_limit integer 0
license_max_sessions integer 0
license_sessions_warning integer 0
sessions integer 800
shared_server_sessions integer
SQL>

Wednesday, July 20, 2011

ORA-12516, TNS:listener could not find available handler with matching protocol stack

My charbench client was running fine with 100 users against an Oracle 11g R2 database created using swingbench, but when I tried to run the client with 200 users I got the following error message:


Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12516, TNS:listener could not find available handler with matching protocol stack


On checking to see the vaule of the process for the database I noticed that it was set to 150.


SQL> show parameter processes;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 150



SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/testasm/spfiletestasm.or
a
SQL> alter system set PROCESSES=250 scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 3540881408 bytes
Fixed Size 2211864 bytes
Variable Size 1644171240 bytes
Database Buffers 1879048192 bytes
Redo Buffers 15450112 bytes
Database mounted.
Database opened.
SQL> show parameter processes;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 250
SQL>


The charbench client proceeded file with 200 users after the above change to the PROCESSES parameter.

Thursday, July 14, 2011

ORA-09817: Write to audit file failed.

While trying to connect to our 11gR2 Oracle database this morning I got the
ORA-09817: Write to audit file failed. error message.


bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 14 10:29:19 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

ERROR:
ORA-09817: Write to audit file failed.
IBM AIX RISC System/6000 Error: 28: No space left on device
Additional information: 12
ORA-09945: Unable to initialize the audit trail file
IBM AIX RISC System/6000 Error: 28: No space left on device


Enter user-name:



On doing a "df -m" at I noticed that the / had run out of space. It's on / that I had our ORACLE_HOME, so I seemed that that might have caused the problem.


$ df -m
Filesystem MB blocks Free %Used Iused %Iused Mounted on
/dev/hd4 25600.00 0.00 100% 70464 95% /


I increased the size of / from 25G to 50G by doing the following:



isvp14_ora> chfs -a size=50G /
Filesystem size changed to 104857600
isvp14_ora> df -m
Filesystem MB blocks Free %Used Iused %Iused Mounted on
/dev/hd4 51200.00 25596.05 51% 70465 2% /


Now, I tried to connect to the database again, and everything worked fine.



bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 14 10:34:32 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>

Tuesday, June 28, 2011

Adding a datafile to a tablespace on ASM

I  had created a database using the ASM diskgroup +DATA for the Oracle datafiles. Next, I wanted to add another datafile to the USERS tablespace.

The commands below show how I went about it.



SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/testasm/datafile/system.256.755000669
+DATA/testasm/datafile/sysaux.257.755000671
+DATA/testasm/datafile/undotbs1.258.755000671
+DATA/testasm/datafile/users.259.755000671

SQL> alter tablespace users add datafile '+DATA/testasm/datafile/users.260.755000671' size 4M;
alter tablespace users add datafile '+DATA/testasm/datafile/users.260.755000671' size 4M
*
ERROR at line 1:
ORA-01276: Cannot add file +DATA/testasm/datafile/users.260.755000671.  File
has an Oracle Managed Files file name.


SQL> alter tablespace users add datafile '+DATA' size 100M;

Tablespace altered.


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/testasm/datafile/system.256.755000669
+DATA/testasm/datafile/sysaux.257.755000671
+DATA/testasm/datafile/undotbs1.258.755000671
+DATA/testasm/datafile/users.259.755000671
+DATA/testasm/datafile/users.266.755002021

SQL>

Friday, June 17, 2011

Enterprise Manager: Hostname should not contain underscore _ in it.

While using dbca to create a database with Enterprise manager configured. The configuration of the Enterprise Manager failed with the following error message:
"Invalid Hostname isvp14_ora. Hostname should not contain an underscore _ in it."

 Well, RFC 952 and RFC 1123 explain the rules for choosing a hostname.




Anyroad, here is the workaround:
On the shell prompt set the ORACLE_HOSTNAME variable to your machines IP address, and then re-configure.

bash-3.2$ export ORACLE_HOSTNAME=<ip_address>

Tuesday, June 14, 2011

I/O stat for Veritas VxVM volumes

I had created a Veritas disk group(dg3) which was made up of 3 volumes (SSD, SAS, and Nearline SAS drives).

I was running the Oracle TPC-C workload, and wanted to measure the iostat information for the diskgroup. Running iostat -x was not particularly useful as it gave me an output as follows:


[root@isvx7 smarttier]# iostat -x 3
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util

..................
....................
....................
VxVM5000 0.00 0.00 0.00 0.00 0.00 0.00 5.77 0.00 0.35 0.35 0.00
VxVM23000 0.00 0.00 0.20 1830.51 3.13 14261.30 7.79 0.36 0.20 0.14 26.46
VxVM23001 0.00 0.00 192.38 1488.29 5490.32 34298.45 23.67 0.45 0.27 0.15 25.38
VxVM23002 0.00 0.00 1.91 117.70 67.23 7716.99 65.08 0.07 0.56 0.24 2.90
VxVM6000 0.00 0.00 9.70 845.52 9146.15 11965.72 24.69 0.18 0.21 0.17 14.28
.....................
.....................
.....................

I then found that using vmstat is much more useful, and here is what I got when I ran the vmstat command:

[root@isvx7 report]# vxstat -i 3 -g dg3
OPERATIONS BLOCKS AVG TIME(ms)
TYP NAME READ WRITE READ WRITE READ WRITE

Tue 14 Jun 2011 11:19:19 AM MST
vol vol_nlsas 0 0 0 0 0.00 0.00
vol vol_sas 4 888 2056 108967 0.75 0.71
vol vol_ssd 49 29066 784 308527 1.55 0.17

Tue 14 Jun 2011 11:19:22 AM MST
vol vol_nlsas 0 0 0 0 0.00 0.00
vol vol_sas 0 2 0 64 0.00 0.00
vol vol_ssd 107 28129 1712 286129 0.50 0.17

Tue 14 Jun 2011 11:19:25 AM MST
vol vol_nlsas 0 0 0 0 0.00 0.00
vol vol_sas 4 881 2056 108455 1.25 0.66
vol vol_ssd 169 26647 2704 291859 0.50 0.18

Thursday, June 9, 2011

ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace USERS

While running the Hammerora workload against the database that I had created. I noticed that though the workload was running, the "Transaction Counter" was still showing 0.

I looked into the alert log of the database instance, and notice lots of ORA-1654 being generated.


ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS
ORA-1654: unable to extend index TPCC.ORDERS_I1 by 8192 in tablespace               USERS
ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS
ORA-1654: unable to extend index TPCC.ORDERS_I1 by 8192 in tablespace               USERS
ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS
ORA-1654: unable to extend index TPCC.ORDERS_I1 by 8192 in tablespace               USERS
ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS
ORA-1654: unable to extend index TPCC.ORDERS_I1 by 8192 in tablespace               USERS
ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS
ORA-1654: unable to extend index TPCC.ORDERS_I1 by 8192 in tablespace               USERS
ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS
ORA-1654: unable to extend index TPCC.ORDERS_I1 by 8192 in tablespace               USERS
ORA-1654: unable to extend index TPCC.ORDERS_I1 by 128 in tablespace               USERS

To fix the issue I first found the datafile names and there sizes for the USERS tablespace.

SQL> select file_name,bytes/1024/1024 "Megs" from dba_data_files where tablespace_name=
  2  'USERS';

 SQL> select file_name,bytes/1024/1024 "Megs" from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
      Megs
----------
/oracle_mvfs/testorcl/users01.dbf
          32735

/oracle_mvfs/testorcl/users02.dbf
          30720

/oracle_mvfs/testorcl/users03.dbf
          30720
..........
..........
..........

Next, I increased the size of the datafiles as follows.


SQL> alter database datafile '/oracle_mvfs/testorcl/users20.dbf' resize 32735M;

Database altered.

SQL> alter database datafile '/oracle_mvfs/testorcl/users19.dbf' resize 32735M;

Database altered.

Voila, we were back on track.







ORA-24550: signal received: [si_signo=11] [si_errno=0] [si_code=1] [si_int=-1066996032]

While trying to run the tpc-c workload using Hammerora tool I got the following ORA-24550 error message and the client GUI crashed.

The exact error message was:

ORA-24550: signal received: [si_signo=11] [si_errno=0] [si_code=1] [si_int=-1066996032] [si_ptr=0x2b8bc066eec0] [si_addr=0xef000a72bd]
kpedbg_dmp_stack()+314<-kpeDbgCrash()+166<-kpeDbgSignalHandler()+158<-skgesig_sigactionHandler()+217<-__restore_rt()<-DisplayText()+621

After trying out a few things, I tried to connect to the database(testorcl) using the hammer ora users(tpcc) account and got the error message ORA-12541: TNS:no listener


[oracle@isvx7 ~]$ sqlplus tpcc/xxxx@testorcl

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 9 11:23:08 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-12541: TNS:no listener

I started the listener using lsnrctl, and then started the Hammerora client againg to generate the TPC-C workload, this time things worked fine.



Wednesday, June 8, 2011

vxlicinst ERROR V-21-1-19 Duplicate License key detected

I was working on Symantec SmartTier and multi-volumed file systems when I got the message that my license has expired and that I need a new one.



[root@isvx7 ~]# fsppadm query /oracle_mvfs
UX:vxfs fsppadm: ERROR: V-3-26550: The command failed with message - à<
UX:vxfs fsppadm: ERROR: V-3-26550: The command failed with message - /sbin/vxlictest -n "VERITAS File System" -sup -f "Quality of Storage Service"
UX:vxfs fsppadm: ERROR: V-3-26540: Valid license is not found


I got a new license, and tried to add it when I got the message "Duplicate License key detected"


[root@isvx7 ~]# cd /sbin
[root@isvx7 sbin]# ls vxlic*
vxlicinst  vxlicrep  vxlictest
[root@isvx7 sbin]# /sbin/vxlicinst



Symantec License Manager vxlicinst utility version 3.02.51.010
Copyright (C) 1996-2010 Symantec Corporation. All rights reserved.

Enter your license key : MMMM-NNNN-OOOO-PPPP-QQQQ-RRRR-SSSS-T12

vxlicinst ERROR V-21-1-19 Duplicate License key detected
[root@isvx7 sbin]#




The workaround for this is move all the files under /etc/vx/license/lic to some other directory, and then try to apply the license again.



[root@isvx7 sbin]# cd /etc/vx/licenses/lic
[root@isvx7 lic]# ls
AAAA-BBB-CCCC-DDDD-EEEE-FFFF-GGGG-HHHH-III.vxlic
JJJJ-KKKK-LLLL-MMMM-NNNN-OOOO-PPPP-QQQQ-RRR.vxlic
SSSS-TTTT-UUUU-VVVV-WWWW-XXXX-YYYY-ZZZZ-AAA.vxlic
BBBB-CCCC-DDDD-EEEE-FFFF-GGGG-HHHH-III.vxlic
[root@isvx7 lic]# mv *.* /root/vxlicenses


[root@isvx7 ~]# /sbin/vxlicinst

Symantec License Manager vxlicinst utility version 3.02.51.010
Copyright (C) 1996-2010 Symantec Corporation. All rights reserved.

Enter your license key : MMMM-NNNN-OOOO-PPPP-QQQQ-RRRR-SSSS-T12

Number of days left for Demo = 364

License key successfully installed for VERITAS Storage Foundation Enterprise HA
License key successfully installed for VERITAS Volume Manager
License key successfully installed for VERITAS Cluster Server
License key successfully installed for VERITAS File System
[root@isvx7 ~]#

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

I wanted to turn archiving for my database. To turn on archive log mode the database must be mounted, but not open. If the database is already open, first shutdown the database using "shutdown" or "shutdown immediate". If you use "shutdown abort" to shutdown the database, then while running the SQL> alter database archivelog; you will get the ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

The easiest way to get around this would be to start the database again using startup, and the shutting it again using "shutdown" or "shutdown immediate". This will do the trick.


-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 8 12:06:23 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     132361
Current log sequence           132363
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance


SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.6991E+10 bytes
Fixed Size                  2213976 bytes
Variable Size            1.7985E+10 bytes
Database Buffers         8858370048 bytes
Redo Buffers              145174528 bytes
Database mounted.
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


SQL> shutdown;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.6991E+10 bytes
Fixed Size                  2213976 bytes
Variable Size            1.7985E+10 bytes
Database Buffers         8858370048 bytes
Redo Buffers              145174528 bytes
Database mounted.
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode


SQL> shutdown;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 2.6991E+10 bytes
Fixed Size                  2213976 bytes
Variable Size            1.7985E+10 bytes
Database Buffers         8858370048 bytes
Redo Buffers              145174528 bytes
Database mounted.
Database opened.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.6991E+10 bytes
Fixed Size                  2213976 bytes
Variable Size            1.7985E+10 bytes
Database Buffers         8858370048 bytes
Redo Buffers              145174528 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL>
SQL>  archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch
Oldest online log sequence     132362
Next log sequence to archive   132364
Current log sequence           132364
SQL>

Friday, May 13, 2011

mount: 0506-324 Cannot mount /dev/oradata_lv on /oradata: There is a request to a device or address that does not exist.

I have a two node cluster sharing a Storwize V7000 between then. Volume groups "oradata_vg" and "oralogs_vg" were created on the shared LUNs.

I then mounted file systesms /ordata and /oralogs on the volume groups. I was also able to manualy switchover the file system between the two nodes of the cluster.

But now when I tried to mount /oradata on the first node I got the "mount: 0506-324" error message.


isvp14_ora> mount /oradata
mount: 0506-324 Cannot mount /dev/oradata_lv on /oradata: There is a request to a device or address that does not exist.

On doing an lspv on the node I noticed that the volume group was not active. I did an varyonvg on that volume, but that failed.

isvp14_ora> lspv
hdisk0          00f62a6c98758859                    rootvg          active
hdisk1          00f62a6ca2279d3e                    swap            active
hdisk2          00f62a6cdf8874f5                    None
hdisk3          00f62a6cdf8875ff                    None
hdisk4          00f62a6ce57d00d4                    oradata_vg
hdisk5          00f62a6bdf88762c                    None
hdisk6          00f62a6bdf887797                    oralogs_vg
hdisk7          00f62a6bdf887802                    None

I noticed that the volume group was active on the second node, so I did a varoffvg of the volume groups. I then did a varyonvg of the volume groups on the first node. I was then able to mount the file systems


isvp15_ora> lspv
hdisk0          00f62a6c98758859                    rootvg          active
hdisk1          00f62a6ca2279d3e                    swap            active
hdisk2          00f62a6cdf8874f5                    None
hdisk3          00f62a6cdf8875ff                    None
hdisk4          00f62a6ce57d00d4                    oradata_vg      active
hdisk5          00f62a6bdf88762c                    None
hdisk6          00f62a6bdf887797                    oralogs_vg      active
hdisk7          00f62a6bdf887802                    None
isvp15_ora> varyoffvg oradata_vg
isvp15_ora> varyoffvg oralogs_vg

isvp14_ora> varyonvg oradata_vg
isvp14_ora> varyonvg oralogs_vg
isvp14_ora> mount /oradata

Tuesday, May 10, 2011

Manually starting and stopping Oracle 11gR2 from the command line, and a shell script

A colleague of mine asked me how to start Oracle database from the command like without getting into SQL.

To start and stop the database, testora is the name of our database

bash-3.2$ srvctl start database -d testora
bash-3.2$ srvctl stop database -d testora

To shutdown the ASM instance

bash-3.2$ srvctl stop asm -f

Start the ASM instance and the diskgroups:

bash-3.2$ srvctl start asm

Script to startup Oracle(startup.ksh)
#!/usr/bin/ksh
SQLDBA="sqlplus /nolog"
$SQLDBA <<EOF
connect / as sysdba
startup
quit
EOF

Script to shutdown Oracle(shutdown.ksh)
#!/usr/bin/ksh
SQLDBA="sqlplus /nolog"
$SQLDBA <<EOF
connect / as sysdba
shutdown abort
quit
EOF 

Script to startup Oracle ASM(startup_asm.ksh)
#!/usr/bin/ksh
export ORACLE_SID=+ASM
SQLDBA="sqlplus /nolog"

$SQLDBA <<EOF
connect / as sysdba
connect sys/test123 as sysasm
startup
quit
EOF
 
Script to shutdown Oracle ASM(shutdown_asm.ksh)
#!/usr/bin/ksh
ORACLE_SID="+ASM"
export ORACLE_SID
SQLDBA="sqlplus / as sysdba"

$SQLDBA <<EOF
connect sys as sysdba
connect sys/test123 as sysasm
shutdown abort
quit
EOF

Thursday, April 28, 2011

Got wget, bash, unzip for AIX 7.1?

Well I was looking around for all these utilities for a while untill i came across this page:
ftp://public.dhe.ibm.com/aix/freeSoftware/aixtoolbox/README.txt


      isvp14_ora> ftp ftp.software.ibm.com
      Name> ftp
      Password> your e-mail address
          ftp> cd aix/freeSoftware/aixtoolbox/RPMS/ppc/wget
          ftp> binary
          ftp> get wget-1.9.1-1.aix5.1.ppc.rpm
          ftp> quit
      isvp14_ora> rpm -hUv wget-1.9.1-1.aix5.1.ppc.rpm
      isvp14_ora> wget -r -nd ftp://ftp.software.ibm.com/aix/freeSoftware/aixtoolbox/ezinstall/ppc

You will now have the following files in the directory that you created:


isvp14_ora> ls
getapp-dev.sh       getgnome.base.sh    getkde3.all.sh
Xsession.kde        getbase.sh          getkde2.all.sh      getkde3.base.sh
Xsession.kde2       getdesktop.base.sh  getkde2.base.sh     getkde3.opt.sh
getgnome.apps.sh    getkde2.opt.sh     

isvp14_ora> chmod +x get*.sh

Run the script getbase.sh this will create a directory called base, ftp the rpm's into it.


isvp14_ora>
isvp14_ora> cd base
isvp14_ora> ls
bash-3.2-1.aix5.2.ppc.rpm          rpm-3.0.5-52.aix5.3.ppc.rpm
bzip2-1.0.5-3.aix5.3.ppc.rpm       rpm-build-3.0.5-52.aix5.3.ppc.rpm
gettext-0.10.40-8.aix5.2.ppc.rpm   rpm-devel-3.0.5-52.aix5.3.ppc.rpm
gzip-1.2.4a-10.aix5.2.ppc.rpm      tar-1.14-2.aix5.1.ppc.rpm
info-4.6-1.aix5.1.ppc.rpm          unzip-5.51-1.aix5.1.ppc.rpm
patch-2.5.4-4.aix4.3.ppc.rpm      
popt-1.7-2.aix5.1.ppc.rpm

isvp14_ora>

Install the rpms that you need:

isvp14_ora> rpm -hUv unzip-5.51-1.aix5.1.ppc.rpm
isvp14_ora> rpm -hUv zip-2.3-3.aix4.3.ppc.rpm
isvp14_ora> rpm -hUv bash-3.2-1.aix5.2.ppc.rpm

There we go, we now have bash with AIX 7.1
isvp14_ora> bash
bash-3.2#

Monday, March 28, 2011

Handy ORACLE commands

Some useful commands(source: http://www.vttoth.com/oracle.htm , http://www.orafaq.com/wiki/Oracle_database_FAQ, and the internet ;-)
Oracle version: select * from v$version;
To start a session as sysdba: sqlplus sys@tnsname as sysdba;
To start a sysdba session: sqlplus /as sysdba
To generate an AWR report: SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Show current database: SELECT * FROM global_name;
Show current database: SELECT name FROM v$database;
Show spfile location: show parameter spfile;
Check database block size: select value from v$parameter where name = 'db_block_size';
Database size: select sum(bytes)/1024/1024 "Meg" from v$datafile;
Redo log size: select l.bytes/1024/1024 "Meg",g.member from v$log l,v$logfile g where l.group# = g.group#;
Check whether or not the database is in archivelog mode: select log_mode from v$database;
Temp file size total: select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;
List datafiles: select name from v$datafile;
List control files: select name from v$controlfile;
List redo log files: select member from v$logfile;
Tablespaces to which the datafiles belong: select file_name, tablespace_name from dba_data_files;
Tablespace status, and it's block size: select tablespace_name, block_size, status from dba_tablespaces;
Table info: select owner, table_name, tablespace_name, blocks,NUM_ROWS from all_tables where table_name='your_table_name';
To list all tables in current schema: SELECT table_name FROM user_tables;
or, all tables current user has access to: SELECT table_name FROM all_tables;
To list all schemas: SELECT username FROM all_users ORDER BY username;
To turn pause on: SET PAUSE ON;
To list top n rows of a table in order: SELECT * FROM (SELECT * FROM t ORDER BY c) WHERE ROWNUM <=
n;
Use database: CONNECT schema/password@tnsname;
Show who I am: SHOW USER;
Describe table: DESC tablename;
Set display rows: SET PAGESIZE 66;
Read field constraints: SELECT constraint_name,search_condition FROM
user_constraints WHERE table_name='tablename';
Who owns a table and what tablespace it is in: select owner, table_name, tablespace_name from dba_tables where table_name='CUSTOMERS';
Copy table from foreign host to here: COPY FROM user@tnsname CREATE tablename
USING SELECT * FROM tablename;
Start SQLPLUS without login: SQLPLUS /NOLOG
Change a user's password: ALTER USER user IDENTIFIED BY password;
Unlock an account ALTER USER user ACCOUNT UNLOCK;
ASM ALLOCATION UNIT SIZE select name, allocation_unit_size from v$asm_diskgroup;
ASM Disk Group Size SELECT name, type, total_mb, free_mb,usable_file_mb FROM V$ASM_DISKGROUP;

Cleaning Oracle ASM disk headers


I wanted to create a new ASM diskgroup using asmca, so I started asmca as user oracle.

When asmca was up i could not see the disks that were available as CANDIDATES




The Header Status on some of the  disks were FORMER and MEMBER. From the Oracle docs
Per-instance status of the disk as seen by discovery:
  • UNKNOWN - Automatic Storage Management disk header has not been read
  • CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
  • INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version
  • PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.
  • MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option.
  • FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.
  • CONFLICT - Automatic Storage Management disk was not mounted due to a conflict
  • FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.


    It's good to clean the ASM disk headers if the disks have been used before, and you want to start fresh. I did the following to clean the disk headers on three of the disks that I planned to use for the diskgroup.



    isvp18> dd if=/dev/zero bs=8k count=1000 of=/dev/rhdisk12
    1000+0 records in.
    1000+0 records out.
    isvp18> dd if=/dev/zero bs=8k count=1000 of=/dev/rhdisk11
    1000+0 records in.
    1000+0 records out.
    isvp18> dd if=/dev/zero bs=8k count=1000 of=/dev/rhdisk13
    1000+0 records in.
    1000+0 records out.




After that my disk were available to asmca as candidates.

 


Wednesday, March 23, 2011

Preparing the Storwize V7000 storage volumes to install Oracle ASM

I wanted to create a single standalone Oracle database on ASM. The storage attached to my AIX host is Storwize V7000.

I had created three volumes on the V7000, and had mounted files systems on them. Now, I want to re-use those volumes on have ASM on them. and re-create the database.

Here is how things looked when I began:


isvp17> lspv
hdisk0          00f65d51a5aa3cf1                    rootvg          active
hdisk1          00f65d51bfba4e2e                    test1           active
hdisk2          00f65d5108ff9043                    test2           active
hdisk3          00f65d5108ffefb6                    test3           active
hdisk4          00f65d51c465f8eb                    metro           active
hdisk5          00f65d51cdf2c48c                    swap            active
hdisk6          00f65d514519e449                    gm_test1        active
hdisk7          00f65d51451a1693                    gm_test2        active
hdisk8          00f65d51451a4067                    gm_test3        active
isvp17>
isvp17> df -m
Filesystem    MB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4       10240.00   2556.56   76%    52314     9% /
/dev/hd2       16896.00  14396.23   15%    44361     2% /usr
/dev/hd9var     5120.00   4813.32    6%     5785     1% /var
/dev/hd3        3584.00   2527.33   30%     1786     1% /tmp
/dev/hd1       15360.00   1894.61   88%     5963     2% /home
/dev/hd11admin    256.00    255.61    1%        5     1% /admin
/proc                 -         -    -         -     -  /proc
/dev/hd10opt    5120.00   4935.14    4%     7015     1% /opt
/dev/livedump    256.00    255.64    1%        4     1% /var/adm/ras/livedump
/dev/lv00       9440.00   4976.29   48%       83     1% /home/test
vanhalen:/vanhalen/tools    512.00    486.43    5%      541     1% /testlab/tools
nimble:/71aix  30208.00  25331.73   17%     1334     1% /mnt
/dev/lv04       9440.00   4477.18   53%       31     1% /gm_oradata
/dev/lv05       9440.00   8986.10    5%       22     1% /gm_oralog
/dev/lv06       9440.00   9128.91    4%       20     1% /gm_oraarch
isvp17>


I unmounted the file systems /gm_oradata, /gm_oralog. and /gm_oraarch which were using the three V7000 volumes. Next, I tried to run the chdev command to clear the disks.


isvp17> chdev -l hdisk6 -a pv=clear
Method error (/etc/methods/chgdisk):
        0514-062 Cannot perform the requested function because the
                 specified device is busy.
     pv

isvp17> varyoffvg gm_test1
isvp17> varyoffvg gm_test2
isvp17> varyoffvg gm_test3
isvp17> lspv
hdisk0          00f65d51a5aa3cf1                    rootvg          active
hdisk1          00f65d51bfba4e2e                    test1           active
hdisk2          00f65d5108ff9043                    test2           active
hdisk3          00f65d5108ffefb6                    test3           active
hdisk4          00f65d51c465f8eb                    metro           active
hdisk5          00f65d51cdf2c48c                    swap            active
hdisk6          00f65d514519e449                    gm_test1
hdisk7          00f65d51451a1693                    gm_test2
hdisk8          00f65d51451a4067                    gm_test3
isvp17>
isvp17> chdev -l hdisk6 -a pv=clear
hdisk6 changed
isvp17> chdev -l hdisk7 -a pv=clear
hdisk7 changed
isvp17> chdev -l hdisk8 -a pv=clear
hdisk8 changed
isvp17> lspv
hdisk0          00f65d51a5aa3cf1                    rootvg          active
hdisk1          00f65d51bfba4e2e                    test1           active
hdisk2          00f65d5108ff9043                    test2           active
hdisk3          00f65d5108ffefb6                    test3           active
hdisk4          00f65d51c465f8eb                    metro           active
hdisk5          00f65d51cdf2c48c                    swap            active
hdisk6          none                                None
hdisk7          none                                None
hdisk8          none                                None
isvp17>

isvp17> ls -l /dev/rhdisk6
crw-------    1 root     system       18,  6 Mar 21 10:47 /dev/rhdisk6
isvp17> ls -l /dev/rhdisk7
crw-------    1 root     system       18,  8 Mar 21 10:48 /dev/rhdisk7
isvp17> ls -l /dev/rhdisk8
crw-------    1 root     system       18,  7 Mar 21 10:48 /dev/rhdisk8
isvp17>


Changing the ownership of the disk to oracle and group to dba


isvp17> chown oracle:dba /dev/rhdisk6
isvp17> chown oracle:dba /dev/rhdisk7
isvp17> chown oracle:dba /dev/rhdisk8
isvp17> ls -l /dev/rhdisk6
crw-------    1 oracle   dba          18,  6 Mar 21 10:47 /dev/rhdisk6
isvp17> ls -l /dev/rhdisk7
crw-------    1 oracle   dba          18,  8 Mar 21 10:48 /dev/rhdisk7
isvp17> ls -l /dev/rhdisk8
crw-------    1 oracle   dba          18,  7 Mar 21 10:48 /dev/rhdisk8
isvp17>


The volumes/disks are now ready for creation of an ASM diskgroup.