1 2 Previous Next 21 Replies Latest reply: Sep 11, 2013 4:12 AM by 817846 RSS

    OS Authentication is not working for Oracle 10g database

    817846

      We are facing an surprising problem in oracle 10g database. Previously we are able to connect our Oracle 10g database using os authentication with "sqlplus / as sysdba" command.

       

      Last wednesday in our linux server maximum number of processes have overflowed and we need to increase the soft limit of our linux server. After that without restarting database every applications [OID 10g] are working fine.

       

      But, we are not able to connect with system using OS authentication. It is showing following.

       

      $ export ORACLE_HOME=/a01/OID

      $ export ORACLE_SID=OID

      $ export PATH=$PATH:/a01/OID/bin

      $ sqlplus / as sysdba

      SQL*Plus: Release 10.1.0.5.0 - Production on Tue Sep 10 06:45:08 2013

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

      Connected to an idle instance.

      SQL>

       

      Whereas I can connect with instance after providing @OID [SID]

      $ sqlplus sys@OID as sysdba

      SQL*Plus: Release 10.1.0.5.0 - Production on Tue Sep 10 06:47:07 2013

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

      Enter password:

      Connected to:

      Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production

      With the Partitioning, OLAP and Data Mining options

      SQL>

       

       

      What am I missing. How can I connect with system with "sqlplus / as sysdba" command?

        • 1. Re: OS Authentication is not working for Oracle 10g database
          sybrand_b

          You are missing

           

          - reading documentation

          - using Google

          - searching this forum

           

          for an answer to this FAQ.

           

          Did you check your OS account is in the dba group?

           

          Why should you be pointed to this?

           

          -----------

          Sybrand Bakker

          Senior Oracle DBA

          • 2. Re: OS Authentication is not working for Oracle 10g database
            Hemant K Chitale

            You'd need to be logged in to the OS (Unix or Linux)  as a member of the OS's "dba" group  (whichever group is identified in $ORACLE_HOME/rdbms/lib/config.c)

             

            Typically, on most installs, it is the login "oracle" in the OS group "dba".

             

            Was there a recent change to any Oracle configuration files ?

             

            Hemant K Chitale


            • 3. Re: OS Authentication is not working for Oracle 10g database
              saurabh

              As you were previsously able to connect to the database using OS authentication. And it can be seen that OS authentication is still enable on you database server. The basic problem would be that you have not exported the sid,home and path variable properly. check the same.

              • 4. Re: OS Authentication is not working for Oracle 10g database
                817846

                The user is already in DBA group.

                $ id oaprod

                uid=503(oaprod) gid=5002(dba) groups=5002(dba)

                 

                See the output of config.c file.

                $ cat /a01/OID/rdbms/lib/config.c

                /*  SS_DBA_GRP defines the UNIX group ID for adminstrative access.  */

                /*  Refer to the Installation and User's Guide for further information.  */

                #define SS_DBA_GRP "dba"

                #define SS_OPER_GRP "dba"

                char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP};

                 

                Also previously "sqlplus / as sysdba" was working previously. So, what is the problem? Why I need to give @OID [OID=SID name] to connect to the database now?

                • 5. Re: OS Authentication is not working for Oracle 10g database
                  817846

                  Also please see my first post that I have set PATH, ORACLE_HOME, ORACLE_SID. previously also I used to use same commands.

                   

                  I have some experience in DBA and also working with a team of experienced DBA group. If this is too trivial problem like env variable setting would have not posted this post.

                  • 6. Re: OS Authentication is not working for Oracle 10g database
                    Hemant K Chitale

                    >Also previously "sqlplus / as sysdba" was working previously. So, what is the problem?

                    Something has changed.  Or  you have the wrong ORACLE_HOME or ORACLE_SID.

                    I did ask "Was there a recent change to any Oracle configuration files ?"

                     

                    Hemant K Chitale

                     


                    • 7. Re: OS Authentication is not working for Oracle 10g database
                      Mustafa KALAYCI

                      I am agree with saurabh, your OS authentication works fine, when you run sqlplus / as sysdba command, you connected to an instance it says: "connected to an idle instance". so the problem is your environment variables. you should give wrong value either ORACLE_SID or ORACLE_HOME (there might be 2 different home on your server).

                      check your TNSNAMES.ORA file and look for OID alias and in its description, what is the name of database ? (or service_name)

                      check if there is more then one dbhome

                      • 8. Re: OS Authentication is not working for Oracle 10g database
                        817846

                        Hemant, there is no changes has been done in the mentioned file. I can only remember last wednesday's downtime because of soft limit processes exhausted.

                         

                        the same env variable we used to set for connecting. see tnsnames.ora file content below.

                        OID =

                          (DESCRIPTION =

                            (ADDRESS = (PROTOCOL = TCP)(HOST = LONDAN.cloud.com)(PORT = 1521))

                            (CONNECT_DATA =

                              (SERVER = DEDICATED)

                              (SERVICE_NAME = OID.cloud.com)

                            )

                          )

                         

                         

                        EXTPROC_CONNECTION_DATA =

                          (DESCRIPTION =

                            (ADDRESS_LIST =

                              (ADDRESS = (PROTOCOL = TCP)(HOST = LONDAN.cloud.com)(PORT = 1521))

                            )

                            (CONNECT_DATA =

                              (SERVICE_NAME = PLSExtProc)

                            )

                          )

                         

                        I am setting env like following.

                        $ export ORACLE_HOME=/a01/OID

                        $ export ORACLE_SID=OID

                        $ export PATH=$PATH:/a01/OID/bin

                        $ sqlplus / as sysdba

                        • 9. Re: OS Authentication is not working for Oracle 10g database
                          Mustafa KALAYCI

                          is there any other dbhome ? also run sqlplus sys@OID as sysdba after that run "select name from v$database" check dbname.

                          • 10. Re: OS Authentication is not working for Oracle 10g database
                            JohnWatson

                            You can get this effect if you run SQL*Plus from the wrong home, even if all the environment variables are correct. Can you set your path like this,

                             

                            export PATH=/a01/OID/bin:$PATH

                             

                            to make sure that you get the correct SQL*Plus ?

                            • 11. Re: OS Authentication is not working for Oracle 10g database
                              817846

                              John, I tried to set path.

                              $ export ORACLE_HOME=/a01/OID

                              $ export ORACLE_SID=OID

                              $ export PATH=/a01/OID/bin:$PATH

                              $ which sqlplus

                              /a01/OID/bin/sqlplus

                               

                              Still the same issue is coming.

                               

                               

                              Hi Mustafa,

                              after connecting with sys. we can see the correct oracle home.

                              SQL> select name from v$database;

                               

                               

                              NAME

                              ---------

                              OID

                               

                              Is there something to do with OS process? Last wednesday our server are exhausted with many processes and we have increased softlink number in our server. After that we hv not restarted the DB or linux server.

                              • 12. Re: OS Authentication is not working for Oracle 10g database
                                jgarry

                                In the previous posts, you appended the oracle home to the PATH.  In the last one, you prepended the oracle home to the path.  What happens when you echo $PATH?  Do you have more than one oracle home?  Perhaps the instance that is running got started with the wrong one.

                                 

                                What does ipcs tell you?  Are there any shared memory areas you don't recognize?  Anything odd in /tmp or ls -rl $ORACLE_HOME/dbs?  ls -l $ORACLE_HOME/bin/sqlplus $ORACLE_HOME/bin/oracle

                                 

                                What does lsnrctl services tell you?  Perhaps you have more hosts than you realize.  I'm kind of wondering where that extra LONDAN goes.  When you do get logged in, show parameters global, db_name and instance.

                                 

                                Maybe there really is a need to reboot linux.

                                 

                                How are you getting to be oaprod?  Are there other environment variables being set like TNS_ADMIN or TWO_TASK?

                                • 13. Re: OS Authentication is not working for Oracle 10g database
                                  sb92075

                                  817846 wrote:

                                   

                                  Also please see my first post that I have set PATH, ORACLE_HOME, ORACLE_SID. previously also I used to use same commands.

                                   

                                  I have some experience in DBA and also working with a team of experienced DBA group. If this is too trivial problem like env variable setting would have not posted this post.

                                   

                                   

                                  post the results of the OS commands below

                                   

                                  hostname

                                  id

                                  uname -a

                                  ps -ef | grep -i pmon | grep -v grep

                                   

                                  COPY the results from above then  PASTE  all back here

                                  • 14. Re: OS Authentication is not working for Oracle 10g database
                                    817846

                                    sb92075 thank you for reply. Please see the command outputs.

                                    $ hostname

                                    LONDAN

                                    $ id

                                    uid=503(oaprod) gid=5002(dba) groups=5002(dba)

                                    $ uname -a

                                    Linux LONDAN 2.6.18-274.el5 #1 SMP Fri Jul 8 17:36:59 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

                                    $ ps -ef | grep -i pmon | grep -v grep

                                    oaprod    3810     1  0 May11 ?        00:02:24 ora_pmon_OID

                                     

                                    jgarry thank u also. please see the response.

                                    $ ls -rl $ORACLE_HOME/dbs

                                    total 2848

                                    -rw-rw---- 1 oaprod dba    3584 May  8 14:33 spfileOID.ora

                                    -rw-r----- 1 oaprod dba 2867200 Sep 10 10:17 snapcf_OID.f

                                    -rwSr----- 1 oaprod dba    1536 May  5 06:17 orapwOID

                                    -rw-rw---- 1 oaprod dba      24 May 11 17:24 lkOID

                                    -rw------- 1 oaprod dba    8385 May  5 05:18 init.ora

                                    -rw------- 1 oaprod dba   12920 May  5 05:18 initdw.ora

                                    -rw-rw---- 1 oaprod dba    1544 May 11 17:24 hc_OID.dat

                                     

                                     

                                    ok. previously I set TNS_ADMIN. Now I set TWO_TASK, while connecting now am getting different error "insufficient privilege". Please check following.

                                     

                                    $ export TWO_TASK=OID

                                    $sqlplus / as sysdba

                                     

                                     

                                    SQL*Plus: Release 10.1.0.5.0 - Production on Wed Sep 11 05:22:28 2013

                                     

                                     

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

                                     

                                     

                                    ERROR:

                                    ORA-01031: insufficient privileges

                                     

                                    But, now while connecting with "sys", I don't need to supply "@OID" it's connecting directly with "sqlplus sys as sysdba" command.

                                    1 2 Previous Next