Remote connection to oracle as sysdba

Been really busy lately at work, so it's my first post in a while.

Having a different OS and oracle flavor than my peers have caused me quite a bit of fuss in the past. Particularly, logging in as normal user vs. sysdba, running sqlplus as the oracle user vs. a normal user (in the dba group) and local in process sqlplus vs. network sql*net. (BTW, the solution for running in process as normal user involved fixing the permissions of the oracle executable messed up by the package manage, conary. It needs to be chmod'd 6751)

A code change in our application forced me to revisit the issue with connecting as sysdba over sql*net. We had some JDBC code that was updating two schemas. Originally it was running twice as two different users and someone changed it to run once as sysdba. I will not go into why, and this change has been reverted eventually for different reasons, but I had to get it working and I couldn't figure out why sys on other Oracle's is able to connect and on mine it can't.

I found the following kludgy workaround:
1. Make sure oracle is using an EXCLUSIVE password file:

> show parameter password;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

2. Grant some user SYSDBA:

> GRANT SYSDBA TO someuser;

3. Check it:

> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
SOMEUSER                       TRUE  FALSE

4. Now export TWO_TASK in the environment and see that you can connect over sql*net

$ export TWO_TASK=LOCAL_XE
$ sqlplus someuser/password AS SYSDBA

Hurray! If you can connect with TWO_TASK set, jdbc using the thin client should work as well.

I also needed to set the permissions on the log dir because I got some:

ORA-29283: invalid file operation                               
ORA-06512: at "SYS.UTL_FILE", line 33                                                                                                 
ORA-06512: at "SYS.UTL_FILE", line 460

Sources:
http://it.toolbox.com/wiki/index.php/Remote_login_as_Sysdba_to_Oracle_database_server#Operating-System-Based_Authentication

http://www.oracleutilities.com/OSUtil/orapwd.html

Oracle VMware Support

An interesting read about the current status of Oracle support for VMware.

http://chucksblog.emc.com/chucks_blog/2009/05/why-oracle-doesnt-like-vmware.html
http://oraclestorageguy.typepad.com/oraclestorageguy/2009/04/what-the-oracle-vmware-support-statement-really-meansand-why.html
http://virtualgeek.typepad.com/virtual_geek/2009/05/oracle-on-vmware-a-manifesto.html
http://oraclestorageguy.typepad.com/oraclestorageguy/2009/05/response-to-comments-from-alessandro-perilli-blog.html
http://www.virtualization.info/2009/05/emc-attacks-oracle-on-its-vmware.html

More information about running Oracle in VMware:
http://vmware.com/partners/virtualize_oracle_landscape.html
The community: http://communities.vmware.com/community/vmtn/general/oracle
Perfomance Analysis guide: http://communities.vmware.com/docs/DOC-3930

Oracle Listener Configuration Revisited

Configuring the oracle 10.2 listener correctly might not be as easy as it seems.
First off, Oracle has the ability to automagically configure everything without any configuration file. The defaults should usually suffice, but if you want to make some changes it can get a bit confusing.

When the oracle service starts (and more importantly, the listener) it says to where it is logging and where it is taking the listener parameters from (for example $ORACLE_HOME/network/admin/listener.ora). Now, if for any reason Oracle can't find or read this file it will happily continue with it's automatic configuration but will do so silently, while still printing the configuration file name!

This is where it stumped me. I made some changes to that file (changed tracing level) but it didn't make any effect. Later I found the reason was that the file was owned by root, while the listener was running as oracle and had no read permission to the file. It took a while to figure that out.

Anyway, back to the problems I was trying to solve. Namely, the occasional hangs at oracle startup/shutdown. I found out that oracle (again, actually the listener) always tries to resolve the machine hostname (probably from gethostname(), dunno) and connect to the result (in netstat I saw connections to ports 199 and 1521). If there is a bogus resolution (put something unreachable in /etc/hosts), the listener process will hang for the machine's defined connect timeout (usually 2 or 5 minutes).
It seems this happens regardless of what you put down in the (HOST = x.x.x) in the listener.ora file. I tried putting down 0.0.0.0, localhost and 127.0.0.1, but in all cases it still tried resolving the hostname.

The lesson here, boys and girls, is that you must make sure that your hostname always resolves to something reachable. E.g. in /etc/hosts you should have:

127.0.0.1 myhost

or

0.0.0.0 myhost

Fair enough. But there are a few additional things that can be done. First, the old oracle init script (very old, probably taken from RedHat 9) calls lsnrctl before calling dbstart and dbshut.
I noticed that these scripts already handle the lsnrctl, so I removed the additional calls from the init script. The hang is gone! Actually the hang is still happening, but since the oracle scripts start lsnrctl in the background, it doesn't block the oracle init script anymore.

Also a warning message in the listener.log after the timeout expires drew my suspicion to the ONS ("subscription to the node down event is still pending"). I found a couple of solutions online, one is to add SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name} = OFF. That took care of the warning message and some of the connections (the one to 1521 is still there) but it still hangs.

Finally I also commented out the cfgHostname call in the init script since all that crap is not really needed. It's easier to set the HOST in listener.ora to something constant such as 0.0.0.0 or localhost than changing the file and moving directories around everytime the hostname/ip changes.