This discussion is archived
3 Replies Latest reply: Apr 10, 2012 10:43 AM by clcarter RSS

connecting to database as sysman

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

Legend

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