Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Thursday, April 19, 2007

Getting "ORA-01034" and "ORA-27123" errors when connecting to Oracle 9i database with another unix user other than "oracle" user (database owner)

The problem is when you try to connect to the oracle locally by using another user other than oracle (database owner) we get "ORA-01034" and "ORA-27123" errors. When you use "TWO_TASK" environment variable, it is OK, since it is going through network I mean over the listener.
But locally we are not able to connect to the database whithout using "TWO_TASK".

There may be others who have seen the same error, so I am writing the solution in my case here.

user1@host1:/data3/oracle/user1 > echo $TWO_TASK
user1@host1:/data3/oracle/user1 > sqlplus db_user1

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Apr 18 23:37:00 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27123: unable to attach to shared memory segment
IBM AIX RISC System/6000 Error: 13: Permission denied


Enter user-name: ^C

ORACLE_SID and ORACLE_HOME are checked and they are what they should be for this database so what is the problem then ?

Solution :

After searching for the solution for some time in Internet,
I finally found the following a solution that solved my problem.

oracle@host1:/data3/oracle > cd $ORACLE_HOME/bin
oracle@host1:/data3/oracle/bin > ls -l oracle
-rwxr-xrwx   1 oracle   dba        74053740 Sep 04 2003  oracle

When we look at the file permissions of "oracle" executable it looks strange
because it normally should be like "-rwsr-s--x", so we are going to make it so

First of all we close the database
This is very important that you should close the database before doing that

oracle@host1:/data3/oracle > sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 19 00:17:08 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> alter system checkpoint;

System altered.

SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 -
64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Then we change the file permissions of "oracle" executable

oracle@host1:/data3/oracle/bin > chmod 6751 oracle
oracle@host1:/data3/oracle/bin > ls -l oracle
-rwsr-s--x   1 oracle   dba        74053740 Sep 04 2003  oracle

Then we open the database

oracle@host1:/data3/oracle/bin > sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 19 00:18:10 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  622298928 bytes
Fixed Size                   743216 bytes
Variable Size             486539264 bytes
Database Buffers          134217728 bytes
Redo Buffers                 798720 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 -
64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

We switch to the user where the connection problem occurs

oracle@host1:/data3/oracle/bin > su - user1
user1's Password:
user1@host1:/data3/oracle/user1 > sqlplus db_user1

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 19 00:18:46 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL>

Finally the problem is solved.

4 comments:

Gary said...

Thank you for posting this. I just had the same issue and now it's resolved.

Mike said...

Yes - thank you! This post got me unstuck as well.

jishar said...

Thank you..its worked for me

Ural Ural said...

Thank you for your comments.
Regards,
Ural