Home » RDBMS Server » Server Administration » Issue with logging in sql with /as sysdba (Oracle 10g release 2, AIX 5.3)
Issue with logging in sql with /as sysdba [message #522923] Wed, 14 September 2011 08:33 Go to next message
raj9999
Messages: 49
Registered: June 2011
Member
I have a small query.

I am able to login into sqlplus by "sqlplus "/as sysdba" from my oracle user(owner of oracle). But when I connect with any other user (which are part of DBA Group) i get "connected to Idle Instance". whereas the database is up & running with no errors in alert log.
I checked the ORACLE_SID in both the session's and both are same.

Can anyone help?
Re: Issue with logging in sql with /as sysdba [message #522928 is a reply to message #522923] Wed, 14 September 2011 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 22781
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

you mis-report reality

post results of following commands using COPY & PASTE

id
env | sort
ls -ltr $ORACLE_HOME/dbs
ps -ef | grep -i pmon
Re: Issue with logging in sql with /as sysdba [message #522929 is a reply to message #522923] Wed, 14 September 2011 08:41 Go to previous messageGo to next message
cookiemonster
Messages: 10960
Registered: September 2008
Location: Rainy Manchester
Senior Member
IS ORACLE_HOME the same?
Re: Issue with logging in sql with /as sysdba [message #523701 is a reply to message #522928] Tue, 20 September 2011 00:37 Go to previous messageGo to next message
raj9999
Messages: 49
Registered: June 2011
Member
Below is the output:


For Oracle User

bash-3.00$ id
uid=100(ora10g) gid=100(dba) groups=101(oinstall)
bash-3.00$
bash-3.00$ env | sort
AUTHSTATE=compat
G_BROKEN_FILENAMES=1
HOME=/oracle/ora102
LANG=en_US
LC__FASTMSG=true
LD_LIBRARY_PATH=/oracle/DB/lib
LOCPATH=/usr/lib/nls/loc
LOGIN=ora10g
LOGNAME=ora10g
MAIL=/usr/mail/ora10g
MAILMSG=[YOU HAVE NEW MAIL]
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat
ODMDIR=/etc/objrepos
OLDPWD=/oracle/ora102
ORACLE_BASE=/oracle
ORACLE_HOME=/oracle/DB
ORACLE_SID=NEWDB
PAGER=/usr/xpg4/bin/more
PATH=
PWD=/oracle/DB/dbs
SHELL=/usr/bin/ksh
SHLVL=1
SSH_TTY=/dev/pts/9
TERM=xterm
TZ=IST+5:30:00CDT
USER=ora10g
_=/usr/bin/env
my_tty=/dev/pts/9
bash-3.00$
bash-3.00$
bash-3.00$
bash-3.00$ ls -ltr $ORACLE_HOME/dbs
total 272
-rw-r-----    1 ora10g   dba            8385 Sep 11 1998  init.ora
-rw-r--r--    1 ora10g   dba           12920 May 03 2001  initdw.ora
-rw-rw----    1 ora10g   dba              24 Apr 21 2010  lkTESTTSTDB
-rw-r-----    1 ora10g   dba            1536 Apr 21 2010  orapwTESTTSTDB_old
-rw-rw----    1 ora10g   dba            1544 Apr 29 2010  hc_TESTTSTDB.dat_old
-rw-r--r--    1 ora10g   dba             986 Apr 29 2010  initTESTTSTDB.ora
-rw-r-----    1 ora10g   dba            2048 Apr 29 2010  orapwTESTTSTDB
-rw-rw----    1 ora10g   dba              24 May 05 2010  lkTESTBPD
-rw-rw----    1 ora10g   dba              24 May 05 2010  lkTESTIAB
-rw-r--r--    1 ora10g   dba            5107 May 05 2010  initTESTIAB.ora
-rw-rw----    1 ora10g   dba              24 May 05 2010  lkTESTUAQ
-rw-rw----    1 ora10g   dba            1544 May 06 2010  hc_TESTTSTDB.dat
-rw-rw----    1 ora10g   dba              24 May 29 2010  lkTESTUAT
-rw-rw-r--    1 ora10g   dba            5071 Mar 10 2011  initTESTBPD.ora
-rw-rw----    1 ora10g   dba            1544 Mar 10 2011  hc_TESTIAB.dat
-rw-rw----    1 ora10g   dba            1544 Jul 14 15:35 hc_TESTBPD.dat
-rw-r--r--    1 ora10g   dba            5071 Jul 16 12:18 initTESTUAT.ora
-rw-rw----    1 ora10g   dba              24 Aug 08 17:23 lkNEWDB
-rwxrwxrwx    1 ora10g   dba             702 Sep 08 19:07 initNEWDB.ora
-rw-r-----    1 ora10g   dba            2560 Sep 10 15:12 spfileNEWDB.ora
-rw-r--r--    1 ora10g   dba            2494 Sep 30 18:11 initTESTUAQ.ora
-rw-r-----    1 ora10g   dba            2048 Oct 03 14:34 orapwTESTUAT
-rw-r-----    1 ora10g   dba            2048 Oct 05 10:31 orapwNEWDB
-rw-rw----    1 ora10g   dba            1544 Oct 05 12:38 hc_NEWDB.dat
-rw-rw----    1 ora10g   dba            1544 Oct 06 10:30 hc_TESTUAQ.dat
-rw-rw----    1 ora10g   dba            1544 Oct 06 10:30 hc_TESTUAT.dat
bash-3.00$ ps -ef | grep -i pmon
  ora10g 3109054       1   0   Sep 30      -  1:13 ora_pmon_TESTUAT
  ora10g 6615056       1   0   Sep 30      -  1:35 ora_pmon_TESTUAQ
  ora10g 6979646       1   0 11:18:59      -  0:10 ora_pmon_NEWDB
  ora10g 8163376 8294548   0 10:49:58  pts/9  0:00 grep -i pmon








For App user


[NEWDB]:/X >id
uid=206(appuser1) gid=100(dba) groups=1(staff)
[NEWDB]:/X >
[NEWDB]:/X >env | sort
AUTHSTATE=compat
CLIENT_CONFIG_FILE=
CSIS_VERSION=70
CTS_ALLOED_SCHM=SBA,ODA,CCA,CAA
CTS_DB=TESTBPD
CTS_DB_IP=172.30.1.217
CTS_DB_PORT=2521
CTS_IMAGE_STORE_PATH=/Y2/testuser/ncr/CTS/BCP_
CTS_JAVA_HOME=/usr/java5_64/bin
CTS_ROOT=/Y2/testuser/ncr/CTS/
CTS_USER_NAME=CUSTOM
DB_NAME=V6
DB_TYPE=O
ECS_BULK_FILE_DIR=/Y2/TESTadm
EPC_DISABLED=TRUE
FALLBACK_CLIENT_NODE=
FCLIENT_APP_TIME_OUT=20
FCLIENT_BACKUP_DIR=/wd/dev/netcast
FCLIENT_DAEMON_CUSTOM_SCRIPT=sendMesgToOpCon.com
FCLIENT_SLEEP=40
FLN_ABBR=IN
FLN_NAME=base
G_BROKEN_FILENAMES=1
HOME=/X
HOST_CONNECT_STR=@TESTUAT
HOST_ID=000BBA82D600
HOST_MACHINE_TYPE=AIX
HOST_NAME=fintnd01
JAVA_HOME=/usr/java14
KIND_OF_NET=T
LANG=
LC_ALL=
LC__FASTMSG=true
LD_LIBRARY_PATH=
LIBPATH=
DB/lib32:/lib
LIST_OF_NODES=
LOCPATH=/usr/lib/nls/loc
LOGIN=appuser1
LOGNAME=appuser1
MAIL=/usr/mail/appuser1
MAILMSG=[YOU HAVE NEW MAIL]
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat
NLS_LANG=
NODE_ID=0
NODE_NAME=000BBA82D600
NODE_TYPE=C
ODMDIR=/etc/objrepos
OLDPWD=/X
ORACLE_HOME=/oracle/DB
ORACLE_SID=NEWDB
PAGER=/usr/xpg4/bin/more
PATH=
PLATFORM=AIX
PS1=[$ORACLE_SID]:$PWD >
PWD=/X
RTGS_HOME=
SAF_REPORT_PATH=
SHELL=/bin/ksh
SHLIB_PATH==/usr/vacpp/bin:/Y/test/application/basic/custom/INT_exe:/oracle/DB/lib32:/usr/java14/bin:/lib
/oracle/DB/lib32:/usr/java14/bin:/lib
USER=appuser1
_=/bin/env
my_tty=/dev/pts/13


[NEWDB]:/X >ls -ltr $ORACLE_HOME/dbs
total 272
-rw-r-----    1 ora10g   dba            8385 Sep 11 1998  init.ora
-rw-r--r--    1 ora10g   dba           12920 May  3 2001  initdw.ora
-rw-rw----    1 ora10g   dba              24 Apr 21 2010  lkTESTTSTDB
-rw-r-----    1 ora10g   dba            1536 Apr 21 2010  orapwTESTTSTDB_old
-rw-rw----    1 ora10g   dba            1544 Apr 29 2010  hc_TESTTSTDB.dat_old
-rw-r--r--    1 ora10g   dba             986 Apr 29 2010  initTESTTSTDB.ora
-rw-r-----    1 ora10g   dba            2048 Apr 29 2010  orapwTESTTSTDB
-rw-rw----    1 ora10g   dba              24 May  5 2010  lkTESTBPD
-rw-rw----    1 ora10g   dba              24 May  5 2010  lkTESTIAB
-rw-r--r--    1 ora10g   dba            5107 May  5 2010  initTESTIAB.ora
-rw-rw----    1 ora10g   dba              24 May  5 2010  lkTESTUAQ
-rw-rw----    1 ora10g   dba            1544 May  6 2010  hc_TESTTSTDB.dat
-rw-rw----    1 ora10g   dba              24 May 29 2010  lkTESTUAT
-rw-rw-r--    1 ora10g   dba            5071 Mar 10 2011  initTESTBPD.ora
-rw-rw----    1 ora10g   dba            1544 Mar 10 2011  hc_TESTIAB.dat
-rw-rw----    1 ora10g   dba            1544 Jul 14 15:35 hc_TESTBPD.dat
-rw-r--r--    1 ora10g   dba            5071 Jul 16 12:18 initTESTUAT.ora
-rw-rw----    1 ora10g   dba              24 Aug  8 17:23 lkNEWDB
-rwxrwxrwx    1 ora10g   dba             702 Sep  8 19:07 initNEWDB.ora
-rw-r-----    1 ora10g   dba            2560 Sep 10 15:12 spfileNEWDB.ora
-rw-r--r--    1 ora10g   dba            2494 Sep 30 18:11 initTESTUAQ.ora
-rw-r-----    1 ora10g   dba            2048 Oct  3 14:34 orapwTESTUAT
-rw-r-----    1 ora10g   dba            2048 Oct  5 10:31 orapwNEWDB
-rw-rw----    1 ora10g   dba            1544 Oct  5 12:38 hc_NEWDB.dat
-rw-rw----    1 ora10g   dba            1544 Oct  6 10:30 hc_TESTUAQ.dat
-rw-rw----    1 ora10g   dba            1544 Oct  6 10:30 hc_TESTUAT.dat


[NEWDB]:/X >ps -ef | grep -i pmon
  ora10g 3109054       1   0   Sep 30      -  1:13 ora_pmon_TESTUAT
 appuser1 3678282 8790252   0 10:54:19 pts/13  0:00 grep -i pmon
  ora10g 6615056       1   0   Sep 30      -  1:35 ora_pmon_TESTUAQ
  ora10g 6979646       1   0 11:18:59      -  0:10 ora_pmon_NEWDB





[Edit: some path removed at OP's request]

[Updated on: Tue, 11 October 2011 06:36] by Moderator

Report message to a moderator

Re: Issue with logging in sql with /as sysdba [message #523702 is a reply to message #523701] Tue, 20 September 2011 00:42 Go to previous messageGo to next message
raj9999
Messages: 49
Registered: June 2011
Member
Also,


[b]with ora10g[/b]

bash-3.00$ id
uid=100(ora10g) gid=100(dba) groups=101(oinstall)
bash-3.00$
bash-3.00$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 6 11:04:50 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


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

SQL>  select open_mode, name from v$database;

OPEN_MODE  NAME
---------- ---------
READ WRITE NEWDB




[b]With App User[/b]

[NEWDB]:/X >id
uid=206(appuser1) gid=100(dba) groups=1(staff)
[NEWDB]:/X >sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 6 11:05:27 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> select open_mode, name from v$database;
select open_mode, name from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available





[Edit: some path removed at OP's request]

[Updated on: Tue, 11 October 2011 02:28] by Moderator

Report message to a moderator

Re: Issue with logging in sql with /as sysdba [message #523707 is a reply to message #523702] Tue, 20 September 2011 01:36 Go to previous messageGo to next message
John Watson
Messages: 4553
Registered: January 2010
Location: Global Village
Senior Member
Are you certain that the OSDBA group is dba, and not oinstall? You can check by looking at your $ORACLE_HOME/rdbms/lib/config.s file, check the group listed under .L12

[Updated on: Tue, 20 September 2011 01:37]

Report message to a moderator

Re: Issue with logging in sql with /as sysdba [message #523708 is a reply to message #523702] Tue, 20 September 2011 01:40 Go to previous messageGo to next message
John Watson
Messages: 4553
Registered: January 2010
Location: Global Village
Senior Member
Also, your LD_LIBRARY_PATH may be pointing to the wrong place.
Re: Issue with logging in sql with /as sysdba [message #523719 is a reply to message #523708] Tue, 20 September 2011 02:27 Go to previous messageGo to next message
raj9999
Messages: 49
Registered: June 2011
Member
Hi John,



This is what i found in config.s file in $ORACLE_HOME/rdbms/lib/ path.


# End   csect   ss_dba_grp{RW}

        .csect  H.12.NO_SYMBOL{RO}, 3
        .string "dba"
# End   csect   H.12.NO_SYMBOL{RO}

        .csect  H.14.NO_SYMBOL{RO}, 3
        .string "dba"
# End   csect   H.14.NO_SYMBOL{RO}
        .llong  0x00000000


Also, LD_LIBRARY_PATH is same for id's



[TESTUAT]:/X >id
uid=206(appuser1) gid=100(dba) groups=1(staff)
[TESTUAT]:/X >echo $LD_LIBRARY_PATH
/oracle/DB/lib




bash-3.00$ echo $LD_LIBRARY_PATH
/oracle/DB/lib
bash-3.00$
bash-3.00$
bash-3.00$ id
uid=100(ora10g) gid=100(dba) groups=101(oinstall)





[Edit: some path removed at OP's request]

[Updated on: Tue, 11 October 2011 02:29] by Moderator

Report message to a moderator

Re: Issue with logging in sql with /as sysdba [message #523721 is a reply to message #523719] Tue, 20 September 2011 02:35 Go to previous messageGo to next message
John Watson
Messages: 4553
Registered: January 2010
Location: Global Village
Senior Member
You are not telling the truth: your earlier post gives a very different LD_LIBRARY_PATH. I also see that your PATH is pointing to different places. It is all abit if a mess, isn't it?
Re: Issue with logging in sql with /as sysdba [message #523735 is a reply to message #523721] Tue, 20 September 2011 03:44 Go to previous messageGo to next message
raj9999
Messages: 49
Registered: June 2011
Member
Yes you are right, LD_LIBRARY_PATH in the previous post for app user was different but i set it same as
ora10g user nd then tried login but it didn't worked.

Also, PATH in app user is set by the application owner. Can i set it same as ora10g user?
as this is UAT, it can be done.

Kindly help to resolve.
Re: Issue with logging in sql with /as sysdba [message #525603 is a reply to message #523735] Tue, 04 October 2011 02:02 Go to previous messageGo to next message
raj9999
Messages: 49
Registered: June 2011
Member
I got the solution.

giving permission 4751 on "oracle" file in bin folder solved the problem. Smile

Thanks.
Re: Issue with logging in sql with /as sysdba [message #525614 is a reply to message #525603] Tue, 04 October 2011 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback, it will be helpful for future readers.

Regards
Michel
Re: Issue with logging in sql with /as sysdba [message #525658 is a reply to message #523702] Tue, 04 October 2011 08:17 Go to previous messageGo to next message
joy_division
Messages: 4512
Registered: February 2005
Location: East Coast USA
Senior Member
sactom wrote on Tue, 20 September 2011 01:42


SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 6 11:04:50 2011


I see you are from the future. Can you tell me how Apple stock did on 10/5/2011 after the announcement of the new iPhone?
Re: Issue with logging in sql with /as sysdba [message #525659 is a reply to message #525658] Tue, 04 October 2011 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Re: Issue with logging in sql with /as sysdba [message #525731 is a reply to message #525658] Tue, 04 October 2011 23:20 Go to previous message
raj9999
Messages: 49
Registered: June 2011
Member
joy_division wrote on Tue, 04 October 2011 18:47
sactom wrote on Tue, 20 September 2011 01:42


SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 6 11:04:50 2011


I see you are from the future. Can you tell me how Apple stock did on 10/5/2011 after the announcement of the new iPhone?


Joy,
It is a UAT server on which the application team do the testing. They have changed the date for HALF YEAR END mock run.
Previous Topic: Is it possible to write Trigers on Data Dictionary Tables & Views?
Next Topic: drop table = truncate table
Goto Forum:
  


Current Time: Tue Sep 16 17:13:18 CDT 2014

Total time taken to generate the page: 0.13171 seconds