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.

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.

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>

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

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.

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.

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.

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.

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>

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>

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.

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>

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>

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>

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

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.

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>

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

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

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#

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.

 

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.