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
No comments:
Post a Comment