I was playing with an old Perl script on my Oracle 10g enterprise edition database server,  running on Redhat Enterprise Linux Advanced Server 4 (RHEL AS4). As the script was originally written for a different database, I had to modify the DBI->connect portion of the script. The script was written to produce a custom report on a schema, therefore, I needed a schema to play with. HR schema was available on my lab server, but with a password that I no longer know. I did the password reset as below

[oracle@racnode1 ~]$sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Jul 7 01:47:07 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> alter user hr identified by pa$$wd;
User altered.
SQL>

So far so good! I decided to test the HR account before I updated the DBI Connect handle

[oracle@'racnode1'~]$sqlplus hr/pa$$wd
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Jul 7 20:44:23 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR: ORA-01017: invalid username/password; logon denied
Enter user-name:

I was surprised that the HR user is not able to connect with the right password. I immediately connected as sysdba and started looking at the HR user privileges:

–Find roles granted to an Oracle database user
select * from dba_role_privs where grantee = ‘HR’;
–Find privileges granted to an Oracle database user
select * from dba_sys_privs where grantee = ‘HR’;
–HR user role in my database RESOURCE
select * from dba_sys_privs where grantee = ‘RESOURCE’;
–Query to check Oracle database user account status
select username, account_status, expiry_date from dba_users where username = ‘HR’;

HR user had the create session privilege and the account is not locked. I just couldn’t fathom, why I am not able to connect? After tapping my fingers on the table, sembling a nervous tick, I tried to connect as HR user from my sysdba session:

SQL> show user
USER is “SYS”
SQL> connect hr/pa$$wd
Connected.
SQL> show user
USER is “HR”
SQL>

And that works! But it does not explain why it wouldn’t work from the command prompt. This time I tried some thing different:

[oracle@'racnode1'~]$sqlplus hr/pa$$wd as sysoper
SQL*Plus: Release 10.2.0.1.0 – Production on Wed Jul 8 03:02:09 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL>

Even that seems to work. Upon a closer look, I realized the Sql*plus just authenticated the OS user as SYSOPER:

SQL> show user
USER is “PUBLIC” <——shows that the connected user is not HR
SQL> exit

Same logic applies to SYSDBA authentication:

[oracle@'racnode1'~]$sqlplus hr/pa$$wd as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Wed Jul 8 03:07:04 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> show user
USER is “SYS”
SQL>

Then it all occurred to me, the culprit is $$ part of my password…….I take it back, the culprit is me, I did not treat the special characters specially. In Unix $$ is a shell perameter, $$ give the process id of my shell. This time I showed some ’special treatment’ to the specail characters:

[oracle@'racnode1'~]$sqlplus hr/’pa$$wd’
SQL*Plus: Release 10.2.0.1.0 – Production on Wed Jul 8 03:32:13 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> show user
USER is “HR”
SQL>

Voila…..I finally figured it out! I was happy to have resolved it, but kinda felt stupid that I spent four hours!

Here is a simple way to check if the DBI and Oracle DBD Perl modules are installed:

If you know the module name, at the command prompt:

> perl -e ‘use DBI’
> perl -e ‘use DBD::Oracle’

If the above return no error messages and return the command prompt you, the modules exist. If you see a message,  some thing like: “Can’t locate DBD/Oraccle.pm in @INC (@INC contains:……..lot of paths here……) at -e line 1. BEGIN failed–compilation aborted at -e line 1.” Then you missing those modules and you need to install them.

If you do not know the module name, copy paste the below at command prompt:

> perl -MFile::Find=find -MFile::Spec::Functions -Tlwe ‘find { wanted => sub { print canonpath $_ if /\.pm\z/ }, no_chdir => 1 }, @INC’ | grep DBI | more

Note: Manipulate the blue highlighted portion above to find other modules

Install The DBI Module

To install missing Perl modules (DBI first and then DBD::Oracle), you would need the root access. You can download the modules directly from CPAN, unzip, untar, read the README file in the module directory very carefully and then do the below (The below example shows how to install DBI module, you can extend that to install DBD::Oracle module):

>perl Makefile.PL

>make

>make test

>make install

(I am not posting the output of any of the above commands, if you read the output, you will know what it means and what you need to do)

Another way to install is, using the CAPN shell. At the command prompt, type the below:

> cpan

cpan> install DBI

The above command will install the latest version of DBI, sit back and watch the magic!

(Note: If you are using the cpan for the first time, it will prompt you to setup the CPAN environment. On most installations your can get away by hitting return key for all options/questions, it will assume defaults. Make sure to select proper continent, country and list of sites to download the Perl modules from. Now you will see the cpan prompt like below)

Before You Install DBD::Oracle Module

Apart from installing the DBI module before you install the DBD::Oracle, you must make sure that you are able to connect to the database. I would certainly test the connectivity with Sql*Plus, with a working schema/password from the OS user (root in my case). My root user does not have the Oracle specific environment variables set, so I had to set the following appropriately:

ORACLE_HOME, ORACLE_SID, TWO_TASK, PATH, LD_LIBRARY_PATH and most importantly ORACLE_USERID. ORACLE_USERID should be set to a correct username and password pair, that is active and unlocked in the database.

Example:

> export ORACLE_USERID=scott/tiger

Do make sure you have the latest version of Test::Builder module. While installing the DBD::Oracle module, if you see a bunch of error messages with “Test returned status 9 (wstat 2304, 0×900)” in them; it means your Perl install is missing the latest version of Test::Builder module. This is some thing I witnessed while installing the latest (version 1.23) DBD::Oracle module. My existing Test::Builder module was the culprit and the error messages vanished after I upgraded it.

Install the DBD::Oracle Module

The below will install the Oracle DBD module.

cpan> install DBD::Oracle

You can always download the module from CPAN and manually install as described in the Install the DBI Module section

Sample Script To Test Your Installation

#!/usr/bin/perl
#Sample script to test Perl connectivity to Oracle Database
#http://www.Oracledbahelp.com
use strict;
use DBI;
my $dbh = DBI->connect( ‘dbi:Oracle:orcl’, ‘hr’, ‘tiger’, { RaiseError => 1, AutoCommit => 0 } );
my $query = qq{ SELECT sysdate FROM DUAL };
my $outpt = $dbh->prepare( $query );
$outpt->execute();
while ( my($systemdate) = $outpt->fetchrow_array) {
print $systemdate, “\n”;
}
$dbh->disconnect();

Other day, while googling about a memory issue, I ended up on this page. It describes how to force discard some memory resident objects by writing 1, 2 or 3 into drop_cache file in the /proc pseudo filesystem. Then I read the part where the author described forcing dentry, inode and pagecache drop could lead to some serious consequences. Here is an excerpt:

There are huge consequences to dumping these caches and running sync. If you are writing data at the time you run these commands, you’ll actually be dumping the data out of the filesystem cache before it reaches the disk, which could lead to very bad things.

I wonder what consequences the author is talking about? The notion that the data being written at the time of running the command will be dropped is not correct. Dirty objects can not be dropped by directing the kernel with drop_cache (as in writing 1/2/3 into that file ). The only way to force them out of memory is with the sync command, which may have consequences, but not the one described above. It may lead to more I/O by the processes that were using the dropped objects and more CPU cycles to build those objects, but ’sync’ will not drop data. Here is an excerpt from the sync command documentation:

sync writes any data buffered in memory out to disk.  This can include (but is not limited to) modified superblocks, modified inodes, and delayed reads and writes.  This must be implemented by the kernel; The `sync’ program does nothing but exercise the `sync’ system

It is important to use drop_cache tweak/hack only in a proper context. Just using it to relinquish some memory does not make sense, it might mask the actual reason for the OOM kill or what ever memory issue you are dealing with. Best way to zero in on the root cause is by gathering the process list, slabinfo, meminfo and buddyinfo (memory fragmentation) snapshot before, during and after the issue. If you are dealing with an after the fact situation like an OOM kill, try to reproduce it, and use a background process to capture afore mentioned stats every 5 or 10 seconds.

It is very important to know what hardware architecture you are dealing with, for instance x86 32bit vs 64bit. The way Linux manages memory zones in 32bit architectures is very different from 64bit architectures.  Knowing the Linux version is also extremely important, you have better controls in 2.6.x vs 2.4.x or previous versions. Here is another excerpt from the article that prompted me to write this:

When I checked the sysstat data with sar, I found that the server was only using about 2-3GB out of 4GB of physical memory at the time when OOM killer was started. At this point, I was utterly perplexed.

It almost seems like this guy was dealing with a 32bit machine and the OS killed his processes due to low memory exhaustion; explains why there is free memory around oom kill. It may be the case where his processes are seeking a set of physically contiguos memory pages. How he came to the conclusion that the OS was caching too much, just puzzles me!

Mount Points:

Naming Convention                  /pm

Where:  p= string constant

m=two digit number

Examples: /u01, /u02, /u03 etc

Mount point names must be simple and should not include and hardware specific information. This helps with changing disk system when needed.

At least 4 mount points to fully comply with OFA. 1 for oracle binaries and the remaining 3 are for oracle database files.

Directory and File Naming Conventions:

OS User Home directory should be:    /pm/h/u

Where: h=Standard directory name (generally app)

u=directory owner

Example: /u01/app/oracle is the home directory of the oracle user

ORACLE_BASE: Root of the Oracle directory structure, Home of the Oracle installation owner. Generally /u01/app/oracle

Set this as an environment variable for clarity

ORACLE_HOME: All oracle binaries and library files are to be located under this directory.

Examples: $ORACLE_HOME/bin Container for Oracle binaries

$ORACLE_HOME/network Container for Oracle network services files

$ORACE_HOME       =          $ORACLE_BASE/product/v/type_[n]

Where: v= version of the software

type= type of installation db|client|companion

n=counter for multiple installation of  the same product

Oracle Inventory Directory (OraInventory)

Conventional location is $ORACLE_BASE/oraInventory

Stores the inventory list of all oracle products installed on the box. This path is specified only during the installation of the first oracle product. Future installs will/can use this inventory directory.

Administrative Files

Configurations files, core dump files, trace files, export files and other related log files are the administrative files of an Oracle database. Each of the former mentioned group of files need to be stored in a different folder.

OFA recommended location: $ORACLE_BASE/admin/d/a

Where: d=refers to a specific database

a=subdirectory for each specific type of administrative files

Example: For a database orcl

Database Files:

OFA Recommendation for Control Files :         /pm/q/d/control[n].ctl

Where: q=indicator such as “oradata”, to indicate that the contents are Oracle related data

d=Database=DB_NAME (initialization parameter)

n=two digit number

OFA Recommendation for Redolog Files:         /pm/q/d/redo[n].log

Where: q=indicator such as “oradata”, to indicate that the contents are Oracle related data

d=Database=DB_NAME (initialization parameter)

n=two digit number

OFA Recommendation for Data Files:   /pm/q/d/t[n].dbf

Where: q= indicator such as “oradata”, to indicate that the contents are Oracle related data

d=Database=DB_NAME (initialization parameter)

Tablespace name should be of format t[n] and not more than eight character in length

t= Descriptive name of the tablespace

n=two digit number

Few things to bear in mind

Always place your database files over a logical volume that spreads over several disks or use RAID.

SAME:  Stripe and mirror every thing

For all oracle mount points that you use to store data files, make sure to run:

Chown oracle:oinstall <mount point>

Chmod 755  <mount point>

My Dell inspiron keeps dropping wireless connection
Intel(R) Pro/wireless 2200BG network adapter drops wireless connection  my wireless connection drops often

read on…..

One of laptops and 4 year old Dell Inspiron 600 has a problem, the wireless connection drops every 30 to 40 minutes. Only way to get the connection back is to disable-and-enable the network connection. Soon this became quite a nuisance having to do all that navigation to network connections, select the connection, disable and then wait for it be disabled …..then enable! It felt like it took for ever to do this. Tried to install latest and greatest drivers, no luck! Tried reinstalling Windows XP and still no luck! I realized that I hate the disable-enable thing not the computer so wanted to find a way to bounce the network connection with very little effort, like a single click!

Hmm…single click doesn’t sound that bad right? I am convinced but there is one problem, I am no windows genius, actually I am windows dummy. Found the windows equivalent of ifconfig, the ipconfig. Wrote a batch script to do ipconifg/release and then ipconfig/renew. It did not work, the ipconfig/release goes fine but the ipconfig/renew fails consistently. Further googling revealed a fancy little Microsoft tool, DevCon, a command-line utility that acts as an alternative to Device Manager. Downloaded it from http://support.microsoft.com/kb/311272 and here is what I did:

1. Copy the devcon.exe file to my c:\windows\system32 folder
2. Open control panel -> Network Connections -> Right Click on the wireless network connection -> Properties -> Click on the “Configure” under the General tab -> Select the “General tab” on the top. Here you can see Device type, Manufacturer and location. Location is really what tells us what bus the device is connected to.
   Mine shows: PCI bus 2, device 3, function 0
   That tells me the device is attached to PCI Bus 2
   

   Wireless Adapter Configuration Info

3. Now lets find the device_id of our device (my wireless adapter in this case). Open the command prompt (Start -> run -> open: cmd). At the command prompt, type the following command:>devcon find pci\*The output looks like the below
   

   Devices on my PCI Bus

   A quick look into the output shows me the instance ID of my wireless adapter

   PCI\VEN_8086&DEV_4220&SUBSYS_27218086&REV_05\4&39A852   02&0&18F0: Intel(R) PRO/Wireless 2200BG Network Connection

   That long string is the instance ID, the device ID part is highlighted part only. Similarly on your machine look for the value that reads some thing like “DEV_XXX” or some thing similar embedded between the “PCI\VEN_XXXX” and “&SUBSYS.” Adapt this logic when dealing with non PCI devices. Step 2 shows what kind of bus you are dealing with.

4. To try the restart, type the following command at your command prompt:> devcon restart *DEV_4220*The output should look like something below. If not, you haven’t done one of the above steps right.
   

   PCI Device Restart With Devcon

5. Now that our restart is gone fine, here is a little batch script:
   @echo off
   echo Restarting your Wireless adapter
   devcon restart *DEV_4220*
   echo Done restarting your wireless adapter
   echo it should be back with an ip in few secs
   I used a notepad to write the above lines and saved it as “wb.bat” (short form of wirelessbounce.bat)
6. You can save that file on your desktop to double-click every time you need to bounce your wireless adapter or make a shortcut and put it in your quick launch area (area right next to your start menu). In my case, I badly needed the single click restart/bounce. So I prefer the quick launch (the black arrow point to the shortcut on my cmputer)
   

   Quick Launch Shortcut

The whole idea of this article is to use this cool little hack to get around the malfunctioning hardware (or Windows or the drivers or what ever), you can get creative and use it in many more occasions. Just be careful, it is powerful tool!