9 Replies Latest reply: Dec 3, 2011 7:10 AM by Stephen Phillips RSS

    Can't Login with SID

    Stephen Phillips
      I have seen several threads about this issue, but so far I can't figure it out. I don't know if my problem is different from the other people or if I am just missing something in the other threads.

      I can login as SYSDBA if I set the ORACLE_SID variable, but if I try to specify it, with "/@FN91DMO", it won't work!.

      Here's some commands to show you what is going on:
      $echo $ORACLE_SID
      FN91TRN
      $ll $ORACLE_HOME/dbs/orapwd$ORACLE_SID
      -rw-r-----. 1 oracle dba 1536 Dec  1 21:05 /oracle/home/dbhome_1/dbs/orapwdFN91TRN
      $grep -i REMOTE_LOGIN_PASSWORDFILE /oracle/control/FN91TRN/initFN91TRN.ora 
      remote_login_passwordfile=exclusive
      $sqlplus / as sysdba
      
      SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 1 21:24:28 2011
      
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      
      
      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      
      SQL> show parameter REMOTE_LOGIN_PASSWORDFILE
      
      NAME                         TYPE      VALUE
      ------------------------------------ ----------- ------------------------------
      remote_login_passwordfile          string      EXCLUSIVE
      SQL> connect /@FN91TRN as sysdba
      ERROR:
      ORA-01031: insufficient privileges
      
      
      Warning: You are no longer connected to ORACLE.
      SQL> 
      I don't understand why I get the insufficient privileges.

      I would be fine with just using the ORACLE_SID variable, but I need the @FN91TRN syntax for RMAN:
      [oracle@lxdb02 ~]$ rman TARGET /@FN91DMO NOCATALOG AUXILIARY /@FN91TRN
      
      Recovery Manager: Release 11.2.0.3.0 - Production on Fri Dec 2 10:28:06 2011
      
      Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
      
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-00554: initialization of internal recovery manager package failed
      RMAN-04005: error from target database: 
      ORA-01031: insufficient privileges
      Can anyone give me any tips to check toward fixing this? -- Thanks
        • 1. Re: Can't Login with SID
          sb92075
          UNIX: Checklist for Resolving Connect AS SYSDBA Issues NOTE: 69642.1.
          • 2. Re: Can't Login with SID
            Lakmal Rajapakse
            You have to give the userid and password:

            sqlplus sys/<password>@FN91TRN as sysdba

            Unless you have remote OS authentication switched on.

            Edited by: Lakmal Rajapakse on 02-Dec-2011 10:33
            • 3. Re: Can't Login with SID
              Stephen Phillips
              I skimmed down through UNIX: Checklist for Resolving Connect AS SYSDBA Issues [ID 69642.1], and I didn't see anything that I missed. Most of it was talking about not being able to connect at all. The difference is that I can connect if I don't supply a SID on the connect command. I only get the error if I specify the SID on the connect command.


              I was hoping to connect using OS Authentication. That is way I didn't supply a password. My use is the owner of the Oracle Home and is in the dba group. But, the same thing happens if I use a password:
              $echo $ORACLE_SID
              FN91TRN
              $sqlplus /nolog
              
              SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 2 12:56:51 2011
              
              Copyright (c) 1982, 2011, Oracle.  All rights reserved.
              
              SQL> connect sys/test as sysdba
              Connected.
              SQL> connect sys/test@FN91TRN as sysdba
              ERROR:
              ORA-01031: insufficient privileges
              
              
              Warning: You are no longer connected to ORACLE.
              Now, I did see several mentions of the sqlnet.ora. I don't have one configured because I was hoping to use the defaults. Is this my problem, or can I use the defaults? I tried creating a sqlnet.ora, but it didn't seem to help.
              $ls $TNS_ADMIN/sqlnet.ora
              ls: cannot access /sqlnet.ora: No such file or directory
              $ls $ORACLE_HOME/network/admin/sqlnet.ora
              ls: cannot access /oracle/home/dbhome_1/network/admin/sqlnet.ora: No such file or directory
              $ls $HOME/sqlnet.ora
              ls: cannot access /home/oracle/sqlnet.ora: No such file or directory
              • 4. Re: Can't Login with SID
                sb92075
                Stephen Phillips wrote:

                I was hoping to connect using OS Authentication.
                OS Authentication & SQL*Net access are mutually EXCLUSIVE
                • 5. Re: Can't Login with SID
                  Lakmal Rajapakse
                  please run the following and send the ouput:

                  tnsping FN91TRN
                  lsnrctl status <LISTENER_NAME>
                  • 6. Re: Can't Login with SID
                    714150
                    Hi,

                    Please follow below steps

                    bash-3.00$ sqlplus / as sysdba

                    SQL*Plus: Release 11.1.0.7.0 - Production on Sat Dec 3 10:37:47 2011

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


                    Connected to:
                    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options

                    SQL>
                    SQL>
                    SQL>
                    SQL> conn sys@OPEX as sysdba
                    Enter password:
                    ERROR:
                    ORA-01031: insufficient privileges


                    Warning: You are no longer connected to ORACLE.
                    SQL>
                    SQL>
                    SQL> conn scott@OPEX
                    Enter password:
                    ERROR:
                    ORA-28000: the account is locked


                    SQL> conn / as sysdba
                    Connected.
                    SQL>
                    SQL>
                    SQL>
                    SQL> show parameter remote_login

                    NAME TYPE VALUE
                    ------------------------------------ ----------- ------------------------------
                    remote_login_passwordfile string EXCLUSIVE
                    SQL>
                    SQL>select * from v$pwfile_users;

                    no rows selected

                    SQL> exit
                    Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    bash-3.00$
                    bash-3.00$
                    bash-3.00$ orapwd
                    Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>

                    where
                    file - name of password file (required),
                    password - password for SYS, will be prompted if not specified at command line,
                    entries - maximum number of distinct DBA (optional),
                    force - whether to overwrite existing file (optional),
                    ignorecase - passwords are case-insensitive (optional),
                    nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).

                    There must be no spaces around the equal-to (=) character.
                    bash-3.00$
                    bash-3.00$
                    bash-3.00$ orapwd file=orapwOPEX password=sys entries=10
                    bash-3.00$
                    bash-3.00$
                    bash-3.00$ sqlplus / as sysdba

                    SQL*Plus: Release 11.1.0.7.0 - Production on Sat Dec 3 10:44:50 2011

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


                    Connected to:
                    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options

                    SQL>
                    SQL>
                    SQL> select * from v$pwfile_users;

                    USERNAME SYSDB SYSOP SYSAS
                    ------------------------------ ----- ----- -----
                    SYS TRUE TRUE FALSE

                    SQL>
                    SQL>
                    SQL>
                    SQL> conn sys@OPEX as sysdba
                    Enter password:
                    Connected.
                    SQL>
                    SQL>

                    Regards,
                    Nitin

                    Edited by: Nitin J on Dec 2, 2011 9:27 PM
                    • 7. Re: Can't Login with SID
                      738992
                      I noticed the name of your password file is orapwdFN91TRN, it should be orapwFN91TRN. Also,since this is 11g, your password might also be case sensitive (check the value of the SEC_CASE_SENSITIVE_LOGON parameter currently).

                      Make the necessary changes and try again, thanks.
                      • 8. Re: Can't Login with SID
                        903459
                        you must make sure your tnsname and password is correct, and login as sys/xxx@xxx as sysdba
                        • 9. Re: Can't Login with SID
                          Stephen Phillips
                          Ok, so here are the tnsping and lsnrctl outputs:
                          $lsnrctl status
                          
                          LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 03-DEC-2011 04:51:43
                          
                          Copyright (c) 1991, 2011, Oracle.  All rights reserved.
                          
                          Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
                          STATUS of the LISTENER
                          ------------------------
                          Alias                     LISTENER
                          Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
                          Start Date                01-DEC-2011 21:17:20
                          Uptime                    1 days 7 hr. 34 min. 23 sec
                          Trace Level               off
                          Security                  ON: Local OS Authentication
                          SNMP                      OFF
                          Listener Parameter File   /oracle/home/dbhome_1/network/admin/listener.ora
                          Listener Log File         /oracle/home/dbhome_1/log/diag/tnslsnr/lxdb02/listener/alert/log.xml
                          Listening Endpoints Summary...
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
                            (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lxdb02.pugetsound.edu)(PORT=1521)))
                          Services Summary...
                          Service "FN91CFG" has 1 instance(s).
                            Instance "FN91CFG", status READY, has 1 handler(s) for this service...
                          Service "FN91CNV" has 1 instance(s).
                            Instance "FN91CNV", status READY, has 1 handler(s) for this service...
                          Service "FN91DMO" has 1 instance(s).
                            Instance "FN91DMO", status READY, has 1 handler(s) for this service...
                          Service "FN91TRN" has 1 instance(s).
                            Instance "FN91TRN", status READY, has 1 handler(s) for this service...
                          The command completed successfully
                          $tnsping FN91TRN
                          
                          TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 03-DEC-2011 04:52:11
                          
                          Copyright (c) 1997, 2011, Oracle.  All rights reserved.
                          
                          Used parameter files:
                          
                          
                          Used TNSNAMES adapter to resolve the alias
                          Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <removed>)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FN91TRN)))
                          OK (0 msec)
                          $
                          Here are the parameters:
                          $sqlplus / as sysdba
                          
                          SQL*Plus: Release 11.2.0.3.0 Production on Sat Dec 3 04:54:30 2011
                          
                          Copyright (c) 1982, 2011, Oracle.  All rights reserved.
                          
                          
                          Connected to:
                          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                          With the Partitioning, OLAP, Data Mining and Real Application Testing options
                          
                          SQL> show parameter SEC_CASE_SENSITIVE_LOGON;
                          
                          NAME                         TYPE      VALUE
                          ------------------------------------ ----------- ------------------------------
                          sec_case_sensitive_logon          boolean      TRUE
                          SQL> show parameter remote_login_passwordfile;
                          
                          NAME                         TYPE      VALUE
                          ------------------------------------ ----------- ------------------------------
                          remote_login_passwordfile          string      EXCLUSIVE
                          SQL> select * from v$pwfile_users;
                          
                          no rows selected
                          That was my problem !!! I had an extra "d" in the password file. I had orapwdFN91TRN instead of orapwFN91TRN.

                          I recreated it like this:
                          SQL> shutdown immediate;
                          Database closed.
                          Database dismounted.
                          ORACLE instance shut down.
                          SQL> exit
                          Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                          With the Partitioning, OLAP, Data Mining and Real Application Testing options
                          $rm /oracle/home/dbhome_1/dbs/orapwdFN91TRN 
                          $orapwd file=/oracle/home/dbhome_1/dbs/orapwFN91TRN password=test entries=2
                          After I started the database, I could connect with the SID:
                          SQL> connect sys/test@FN91TRN as sysdba
                          Connected.
                          SQL>  show parameter remote_login_passwordfile;
                          
                          NAME                         TYPE      VALUE
                          ------------------------------------ ----------- ------------------------------
                          remote_login_passwordfile          string      EXCLUSIVE
                          SQL> select * from v$pwfile_users;
                          
                          USERNAME                 SYSDB SYSOP SYSAS
                          ------------------------------ ----- ----- -----
                          SYS                      TRUE  TRUE  FALSE
                          Thank you for all of the help. It is very much appreciated. I read through those instructions, but I just didn't catch the name difference. Thank you for catching it for me.