1 2 3 Previous Next 30 Replies Latest reply on Dec 28, 2006 12:14 PM by orafad

OS authentication related problem

523455 Dec 27, 2006 10:10 AM

Hi Friends

I am connecting to my database server (Oracle 10g R2) using oracle (UNIX) id. This user belongs to DBA group on UNIX and is the database administrator. We also have a System Administrator user thru which the database has been started.

My problem is that when I log with oracle (database admin) user id and try to connect to database thru SQLPLUS, i get the following message:
SQL> connected to an idle instance.

My ORACLE_SID is set properly .... So what can be the problem ... Any help shall be greatly appreciated ... Thanks ....

1. Re: OS authentication related problem

464759 Dec 27, 2006 10:12 AM (in response to 523455)

Your instance is not running. You should start it with the STARTUP command.
Like Show 0 Likes(0)

Actions
2. Re: OS authentication related problem

523455 Dec 27, 2006 10:20 AM (in response to 464759)

Hi Pierre,

The database is up and running. When we connect thru the Sys Admin user and login to SQLPLUS we get connected to the database. But when connecting thru the oracle user id we get the above message ... Thanks ...
Like Show 0 Likes(0)

Actions
3. Re: OS authentication related problem

464759 Dec 27, 2006 10:34 AM (in response to 523455)

Are you sure you did not get any Oracle error message ?
Is there any error message in the database alert.log ?
Is ORACLE_SID uppercase ?
Is DB_NAME defined in the initialization file ?

Message was edited by:
Pierre Forstmann
Like Show 0 Likes(0)

Actions
4. Re: OS authentication related problem

523455 Dec 27, 2006 10:41 AM (in response to 464759)

Hi Pierre,

Thanks for ur reply ...

init.ora contents:
*.db_name='BP3'

Please find below the result of two login attempts (one using system Admin and other using Database Admin)

Login using System Admin ID:
DSS-BIWPRD:bp3adm 17% id
uid=206(bp3adm) gid=101(sapsys)
DSS-BIWPRD:bp3adm 18% sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Dec 27 16:09:15 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options

SQL>

Connecting using Database Admin ID:
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64
bit Production
With the Partitioning and Data Mining options
DSS-BIWPRD:bp3adm 19% su - orabp3
Password:
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
DSS-BIWPRD:orabp3 1% id
uid=205(orabp3) gid=201(dba)
DSS-BIWPRD:orabp3 2% sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Dec 27 16:10:16 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL>

Kindly clarify ....
Thanks ....
Like Show 0 Likes(0)

Actions

5. Re: OS authentication related problem

orafad Dec 27, 2006 10:52 AM (in response to 523455)

This can be a case of not setting env variables properly (below, no instance called test1 is started, within current oracle home):

hostname:[]: > export ORACLE_SID=test1

hostname:[test1]: > sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Dec 27 11:47:20 2006
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

SQL> exit
Disconnected
hostname:[test1]: > export ORACLE_SID=geoprod

hostname:[geoprod]: > sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Dec 27 11:47:33 2006

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


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
JServer Release 9.2.0.7.0 - Production

Message was edited by:
orafad

6. Re: OS authentication related problem

464759 Dec 27, 2006 10:55 AM (in response to 523455)
If you are sure that you have the same value of ORACLE_SID for both shell sessions,
can you run following Unix trace command to check what SQL*Plus is doing at system call level:

strace -fo /tmp/sqlplus.strace sqlplus '/ as sysdba'
Like Show 0 Likes(0)

Actions
7. Re: OS authentication related problem

523455 Dec 27, 2006 10:55 AM (in response to orafad)

Hi Orafad,

Please find below the continuation from last posted message:

Database Admin logged in:

SQL> !echo $ORACLE_SID
BP3

SQL> exit
Disconnected
DSS-BIWPRD:orabp3 3% su - bp3adm
Password:
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
You have new mail.
DSS-BIWPRD:bp3adm 1% sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Dec 27 16:24:01 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

System Admin logged in:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> !echo $ORACLE_SID
BP3

SQL>
Like Show 0 Likes(0)

Actions

8. Re: OS authentication related problem

Eduardo Legatti Dec 27, 2006 11:05 AM (in response to 523455)

Hi,

I agree with Fredrik. I did the same test on Oracle 10g/Linux.

oracle@icaro:~> echo $ORACLE_SID

BDRPS

oracle@icaro:~> sqlplus / as sysdba



SQL*Plus: Release 10.1.0.2.0 - Production on Wed Dec 27 08:58:44 2006



Copyright (c) 1982, 2004, Oracle.  All rights reserved.





Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options



SYS@BDRPS> select instance_name from v$instance;

INSTANCE_NAME

----------------

BDRPS



SYS@BDRPS> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

oracle@icaro:~>



oracle@icaro:~> export ORACLE_SID=TEST



oracle@icaro:~> echo $ORACLE_SID

TEST

oracle@icaro:~> sqlplus / as sysdba



SQL*Plus: Release 10.1.0.2.0 - Production on Wed Dec 27 08:59:14 2006



Copyright (c) 1982, 2004, Oracle.  All rights reserved.



Connected to an idle instance.

Cheers

9. Re: OS authentication related problem

530897 Dec 27, 2006 11:31 AM (in response to 523455)

DSS-BIWPRD:bp3adm 19% su - orabp3
Password:
When you performed the su - operation above, you are loading the environment variables for the orabd3 user. Does the oradb3 user have the Oracle Home and Oracle Sid set correctly?
Like Show 0 Likes(0)

Actions
10. Re: OS authentication related problem

523455 Dec 27, 2006 12:12 PM (in response to 530897)

Hi Eric,

Please find below the details :

DSS-BIWPRD:bp3adm 11% echo $ORACLE_SID
BP3
DSS-BIWPRD:bp3adm 12% echo $ORACLE_HOME
/oracle/BP3/102_64
DSS-BIWPRD:bp3adm 13% su - orabp3
Password:
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
DSS-BIWPRD:orabp3 1% echo $ORACLE_SID
BP3
DSS-BIWPRD:orabp3 2% echo $ORACLE_HOME
/oracle/BP3/102_64
DSS-BIWPRD:orabp3 3%

Thanks ...
Like Show 0 Likes(0)

Actions
11. Re: OS authentication related problem

Maran Viswarayar Dec 27, 2006 12:14 PM (in response to 530897)

As suggested Mr Eric please check the .profile or .login to verify whether you have
ORACLE_HOME,ORACLE_SID set
Like Show 0 Likes(0)

Actions
12. Re: OS authentication related problem

523455 Dec 27, 2006 12:34 PM (in response to Maran Viswarayar)

Everything is dicussed earlier
Pls find more
Thanks
Like Show 0 Likes(0)

Actions
13. Re: OS authentication related problem

Maran Viswarayar Dec 27, 2006 12:43 PM (in response to 523455)

Sorry Mr.Rajesh i know all discussed earlier jus u can check whether these params are in your .profile
Like Show 0 Likes(0)

Actions
14. Re: OS authentication related problem

523455 Dec 27, 2006 12:48 PM (in response to Maran Viswarayar)

We have got this here so can this through some light on the issue:--------------

SQL> select username,password from dba_users;

USERNAME PASSWORD
------------------------------ ------------------------------
OPS$ORABP3 EXTERNAL
OPS$BP3ADM EXTERNAL
Thanks for your support
Like Show 0 Likes(0)

Actions

1 2 3 Previous Next

Go to original post