3 Replies Latest reply: Apr 10, 2012 12:43 PM by clcarter RSS

    connecting to database as sysman

    PaulS
      1. foraydb3 ( dbTier, Concurrent Manager, Admin)
      2. forayap3 ( form Tier, java )

      I can able to login from * foraydb3* to database

      [oracle@foraydb3 ~]$ sqlplus /nolog

      SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 10 07:42:09 2012

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

      SQL> conn / as sysdba
      Connected.
      SQL> select name from v$database;

      NAME
      ====
      FIN



      but when I tried to login from * forayap3 * following error occurs.


      [oracle@forayap3 ~]$ sqlplus /nolog

      SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 10 07:43:00 2012

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

      SQL> conn / as sysdba
      Connected to an idle instance.
      SQL> select name from v$database;
      select name from v$database
      *
      ERROR at line 1:
      ORA-01034: ORACLE not available
      Process ID: 0
      Session ID: 0 Serial number: 0


      It must be some type of environment setup issue but not sure where to look for.
        • 1. Re: connecting to database as sysman
          clcarter
          A frequent cause for the "ORA-01034 ... not available" is an incorrect ORACLE_SID. Find the right SID by looking at the pmon process ...
          $ ps -ef |egrep pmon
          ...
          oracle <PID> 1 ... <datetime> ... ora_pmon_SOMETHING
          $ export ORACLE_SID=SOMETHING
          $ sqlplus /nolog
          connect system
          ... password ...
          Connected.
          select name from v$database;
          ...
          SOMETHING
          ...
          That should work, as long as you have the correct ORACLE_HOME environment setting for the running instance.

          Edited by: clcarter on Apr 10, 2012 12:41 PM
          add _HOME note                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
          • 2. Re: connecting to database as sysman
            PaulS
            Yes your pointer worked when I did

            SQL> connect system/password@fin
            Connected.
            SQL> select name from v$database;

            NAME
            ---------
            FIN


            but when I did this it had following error.

            SQL> connect system/password@fin as sysdba;
            ERROR:
            ORA-01031: insufficient privileges


            Is this because I'm trying to login to database from "application node" instead "database node"

            which I usually connect with "conn / as sysdba" to shutdown the instance?



            regards
            • 3. Re: connecting to database as sysman
              clcarter
              Back to your successful 'system' user connection, take a look at which database users have rights to allow a sysdba connection, pretty sure the system user will not be in that list:
              select * from v$pwfile_users;
              USERNAME                       SYSDB SYSOP SYSAS
              ------------------------------ ----- ----- -----
              SYS                            TRUE  TRUE  FALSE
              ...
              -- also check the passwordfile setting
              show parameter remote_login
              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- ------------------------------
              remote_login_passwordfile            string      EXCLUSIVE
              trying to login to database from "application node" instead "database node"
              Yes, if you're not on the database server the connection must use the 'connect <user>@<tnsname>' syntax. But to shut down (or startup) an instance, generally its best to do that on the database host.