This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Sep 11, 2013 2:12 AM by 817846 RSS

OS Authentication is not working for Oracle 10g database

817846 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    >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 Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Journeyer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points