| Issue with logging in sql with /as sysdba [message #522923] |
Wed, 14 September 2011 08:33  |
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 #523701 is a reply to message #522928] |
Tue, 20 September 2011 00:37  |
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 |
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 #523719 is a reply to message #523708] |
Tue, 20 September 2011 02:27 |
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 #523735 is a reply to message #523721] |
Tue, 20 September 2011 03:44 |
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 #525658 is a reply to message #523702] |
Tue, 04 October 2011 08:17 |
joy_division
Messages: 4963
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 #525731 is a reply to message #525658] |
Tue, 04 October 2011 23:20 |
raj9999
Messages: 49
Registered: June 2011
|
Member |
|
|
joy_division wrote on Tue, 04 October 2011 18:47sactom 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
