Additional Blogs by SAP
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
0 Kudos

When working in SAP Global Support in the database team, problems during establishing a database connection reported by our customers are very common. This does not mean that they are always easy to solve, the level of 'nastiness' is greatly varying. Sometimes small things cause big trouble here.


For many people, connecting to the Oracle database is a rather simple thing. Just specify "/ as sysdba" and it’s done. BUT: Troubleshooting in case these rather basic mechanisms do not work heavily depends on a thorough understanding of the inner workings of Oracle (and SAP when it comes to the connect initiated via the DBSL or JDBC). This blog series will try to shed some light on these everyday tasks.

Please note that I have done the test case on Oracle 10 / Linux x86_64, so there could be differences when it comes to system calls and outputs in case you try this on a different platform. This article does not apply by any means to MS Windows (perhaps, I will do separate one for windows in the future).

This blog post will focus on the frequently used command 'sqlplus / as sysdba', future posts will spotlight different commands.</p><p align="left">Introduction - Look behind the curtain</p><p align="left">Imagine this:<br />Both the oracle instance and the listener are stopped. How can it be possible that this command works? What actually starts the Oracle Server process? First of all, we use a tool like strace or truss to have a closer look at the system calls that this sqlplus command performs:</p><p align="left">...<br />clone(child_stack=0, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x2a97712b90) = 19555<br />...</p><p align="left">The clone command is a standard function that creates child processes. You could compare it to fork but there are lots of differences that can be looked up in the man page for more, detailed information.<br />The result of the clone command is the following: An Oracle Server process (you could also call it shadow process).</p><p align="left">nwtest:nw2adm 52> ps -ef | grep 19555<br />oranw2 19555 19554 0 15:30 ? 00:00:00 oracleNW2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))</p><p align="left"><br />This is very important information! So in this case, the Oracle Server process is started by sqlplus. </p><p align="left"><br />The ps output holds the key to further understanding of this scenario. The protocol that is used for the communication between the client and the server is BEQ. We will not go into further details here, basically, BEQ means that client - server communication does not rely on the listener but on pipes. So we now know why we do not need a listener here. Apart from this, BEQ is also a prerequisite for this process spawning mechanism to work. Please note that BEQ can only be used in case the client and the server are on the same host.</p><p align="left"><br />When it comes to SAP environments, you should also pay attention to the fact that Oracle processes are always running as ora<sid>, even when the startsap script is usually executed as <sid>adm. How can this be?<br />This can be explained by examining the permissions of the Oracle executable:</p><p align="left">nwtest:nw2adm 71> cd $ORACLE_HOME/bin<br />-rwsr-s--x 1 oranw2 dba 101539517 2007-09-09 07:54 oracle</p><p align="left">The s Bit is set for both the user and the group.</p><p align="left">Authentication</p>

"/ as sysdba" means that the user calling sqlplus has to be a member of the dba group for a successful authentication.
It is very important to know that authentication in this particular case is done on client side by sqlplus. SQLPLUS will always try to fetch the information concerning the group assignments from the nscd daemon. I have seen situations where a hanging nscd daemon causes the whole login procedure to simply "hang". Only in case the nscd daemon is not available (stopped), SQLPLUS will read /etc/passwd.
For those really interested in the internals, this is the corresponding strace/truss output:

...
connect(7, {sa_family=AF_FILE, path="/var/run/.nscd_socket"}, 110) = 0

poll({fd=7, events=POLLOUT, 1, 5000) = 1
writev(7, [{"\2\0\0\0\v\0\0\0\7\0\0\0", 12}, {"passwd\0", 7}], 2) = 19
...

How does SQLPLUS actually knows which group is the right one? This information is "hard coded" into the Oracle executables during relink and derived from the information that can be found in $ORACLE_HOME/rdms/lib/config.c. This file looks like the following:

nwtest:nw2adm 62> more config.c

/* SS_DBA_GRP defines the UNIX group ID for administrative access. /<br />/ Refer to the Installation and User's Guide for further information. */

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "oper"

char ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};</p><p align="left">Please be aware that any changes to in this file only take effect as soon as the Oracle binaries have been relinked.</p>

Additional Information

<p align="left">For anybody that wants to go into further detail here, I can recommend the following the following sources:</p>

SAP Notes

<p align="left"><br />480266 - Problems with the SYSDBA/SYSOPER/INTERNAL connect<br />562863 - FAQ: Logon mechanisms<br />700548 - FAQ: Oracle authorizations</p>

Metalink

*

18089.1 - UNIX: Connect AS SYSBDA / SYSOPER Privilege on Oracle RDBMS
50507.1 - SYSDBA and SYSOPER Privileges in Oracle