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

    OS authentication related problem

    523455
      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
          Your instance is not running. You should start it with the STARTUP command.
          • 2. Re: OS authentication related problem
            523455
            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 ...
            • 3. Re: OS authentication related problem
              464759
              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
              • 4. Re: OS authentication related problem
                523455
                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 ....
                • 5. Re: OS authentication related problem
                  orafad
                  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
                    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'
                    • 7. Re: OS authentication related problem
                      523455
                      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>
                      • 8. Re: OS authentication related problem
                        Eduardo Legatti
                        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
                          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?
                          • 10. Re: OS authentication related problem
                            523455
                            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 ...
                            • 11. Re: OS authentication related problem
                              Maran Viswarayar
                              As suggested Mr Eric please check the .profile or .login to verify whether you have
                              ORACLE_HOME,ORACLE_SID set
                              • 12. Re: OS authentication related problem
                                523455
                                Everything is dicussed earlier
                                Pls find more
                                Thanks
                                • 13. Re: OS authentication related problem
                                  Maran Viswarayar
                                  Sorry Mr.Rajesh i know all discussed earlier jus u can check whether these params are in your .profile
                                  • 14. Re: OS authentication related problem
                                    523455
                                    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
                                    1 2 3 Previous Next