BlueSkyTelematicsInc
asked on
Oracle Appl Server -Create JDBC datasource error - Test connection failed
Hi - I am trying to create a JDBC connection in Oracle Application Server 10.1.3.0
After failing in creating a new connection, I tried the "Test" button for the default jdbc datasource created at install and it failed as well. Here is the "Deployment Descriptor File"
Can you see or think of any reason why this connection would fail?
(Username and pswd are correct)
Setup Logs Help Logout
Cluster Topology > Application Server: as1013.bluesky1.blueskytel ematics.ne t > OC4J: home > Application: default >
View Data Sources Deployment Descriptor
<?xml version = '1.0' encoding = 'UTF-8'?>
<data-sources xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocat ion="http://xmlns.oracle.com/oracleas/schema/data-sources-10_1.xsd" schema-major-version="10" schema-minor-version="1">
<managed-data-source login-timeout="5" connection-pool-name="Exam ple Connection Pool" jndi-name="jdbc/OracleDS" name="OracleDS"/>
<connection-pool name="Example Connection Pool">
<connection-factory factory-class="oracle.jdbc .pool.Orac leDataSour ce" user="*******" password="********" url="jdbc:oracle:thin:@//b luesky1.bl ueskytelem atics.net: 1522/oracl e.regress. rdbms.dev. us.oracle. com" commit-record-table-name=" "/>
</connection-pool>
</data-sources>
When attempting "Create New" via MapViewer console; this is the error I get;
Error
Data source cannot be created : [MapperConfig] cannot add map data source.
Here are the settings I input;
Name: mvdemo
* Based on: **JDBC URL---- not selected; J2EE DSTNS name
Host: bluesky1.blueskytelematics .net
Port: 1522
Sid: orcl
User: *****
Password: ******
# Mappers: 3
Max Connections: 100
Maximum number of DB connections. 0 means no limit.
After failing in creating a new connection, I tried the "Test" button for the default jdbc datasource created at install and it failed as well. Here is the "Deployment Descriptor File"
Can you see or think of any reason why this connection would fail?
(Username and pswd are correct)
Setup Logs Help Logout
Cluster Topology > Application Server: as1013.bluesky1.blueskytel
View Data Sources Deployment Descriptor
<?xml version = '1.0' encoding = 'UTF-8'?>
<data-sources xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocat
<managed-data-source login-timeout="5" connection-pool-name="Exam
<connection-pool name="Example Connection Pool">
<connection-factory factory-class="oracle.jdbc
</connection-pool>
</data-sources>
When attempting "Create New" via MapViewer console; this is the error I get;
Error
Data source cannot be created : [MapperConfig] cannot add map data source.
Here are the settings I input;
Name: mvdemo
* Based on: **JDBC URL---- not selected; J2EE DSTNS name
Host: bluesky1.blueskytelematics
Port: 1522
Sid: orcl
User: *****
Password: ******
# Mappers: 3
Max Connections: 100
Maximum number of DB connections. 0 means no limit.
ASKER
DB is 11g; listener is up for all other purposes, so I don't think it is that, but I coud be wrong. SID and ORACLE_SID ARE orcl (default) . All other consoles etc are wroking, but still the above jbdc error. I was wondering from which jdbc instance I should be running ( there are multiple) Port 1522 is correct and workng for the listener as per em console, not sure why it didn't use 1521, maybe its in use for something else? How can I check in linux?
The two commands you requested yielded;
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 18-NOV-2007 15:02:10
Uptime 17 days 11 hr. 54 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11 .1.0/db_1/ network/ad min/listen er.ora
Listener Log File /u01/app/oracle/product/11 .1.0/db_1/ log/diag/t nslsnr/blu esky1/list ener/alert /log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO TOCOL=ipc) (KEY=EXTPR OC1522)))
(DESCRIPTION=(ADDRESS=(PRO TOCOL=tcp) (HOST=blue sky1.blues kytelemati cs.net)(PO RT=1522)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
and..........
LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 06-DEC-2007 02:57:53
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PRO TOCOL=IPC) (KEY=EXTPR OC1522)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:9681 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: bluesky1.blueskytelematics .net, pid: 3635>
(ADDRESS=(PROTOCOL=tcp)(HO ST=bluesky 1.blueskyt elematics. net)(PORT= 42834))
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:9681 refused:0 state:ready
LOCAL SERVER
The command completed successfully
The two commands you requested yielded;
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 18-NOV-2007 15:02:10
Uptime 17 days 11 hr. 54 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11
Listener Log File /u01/app/oracle/product/11
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
and..........
LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 06-DEC-2007 02:57:53
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PRO
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:9681 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: bluesky1.blueskytelematics
(ADDRESS=(PROTOCOL=tcp)(HO
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:9681 refused:0 state:ready
LOCAL SERVER
The command completed successfully
may be it is too early to use 11g.
Can you try connection to 10g?
Can you try connection to 10g?
to check your ports in Linux - use 'netstat -ltnp' - will show all the TCP ports and binding processes
To verify if your listener is correct - can you 'tnsping' to the DB from the AS?
Can you SQLPlus to the DB from the AS? If not you could turn on tracing in the sqlnet.ora file - or you could 'snoop' the interface for <much> more detail on the connection - or the lack thereof....
To verify if your listener is correct - can you 'tnsping' to the DB from the AS?
Can you SQLPlus to the DB from the AS? If not you could turn on tracing in the sqlnet.ora file - or you could 'snoop' the interface for <much> more detail on the connection - or the lack thereof....
ASKER
I am not sure what you mean by SQLplus to the DB from AS, but what I did manage to do in AS was create a jdbc connection pool and a jdbc datasource; both in the application "mapviewer" in the only oc4j instance I have up. I thought that would fix the problem, BUT when I am inside mapviewer console, not only can I not see the connection, but I can't create one either, even though I am using the same settings as I did in ASconsole, which worked??
Why is the mapviewer console not seeing the AS jdbc source, and how is it different? Are the following possible places to look at?
1. tnsnames.ora - do I need to define the datasource "mvdemo" in this file?
2. listener.ora - same question as above
3. java_home varaible?- do AS and mapviewer absolutely need to be looking at the same java home?
4. oracle_home variable - any impact possible?
5. Which port, the listener port (1522) or the AS port (7780) or another ( am using 1522 which works for the AS jdbc datasource "mvedemo"
Where should I look next given the variance between mapviewer and AS? Are there any patches for mapviewer 10131 that address any jdbc bugs?
Why is the mapviewer console not seeing the AS jdbc source, and how is it different? Are the following possible places to look at?
1. tnsnames.ora - do I need to define the datasource "mvdemo" in this file?
2. listener.ora - same question as above
3. java_home varaible?- do AS and mapviewer absolutely need to be looking at the same java home?
4. oracle_home variable - any impact possible?
5. Which port, the listener port (1522) or the AS port (7780) or another ( am using 1522 which works for the AS jdbc datasource "mvedemo"
Where should I look next given the variance between mapviewer and AS? Are there any patches for mapviewer 10131 that address any jdbc bugs?
Your comments brought up a question or two as well as suggestions:
Are the DB and the AS on the same machine?
If so - Definitely check to make sure your ENV variables are different for each instance; using one ORACLE_HOME (or JAVA_HOME for that matter) will confuse issues and cause you all sorts of headaches. Your instances should be mapped out thus: ORACLE_BASE/ORACLE_HOME<fo r DB>
ORACLE_BASE/ORACLE_HOME<fo r AS>.
Check your tnsnames.ora - there should be one for the AS and one for the DB, each located in the relative path /ORACLE_HOME/network/admin . If you have your ENV var's mixed, this could very easily be the cause of confusion.
Looks like you listener is starting up so that seems ok.
Please correct as needed, but as I understand your description, the mapviewer, i.e. the 'mvdemo' is the application you're trying to get working on the AS side, right? IF so - then they should both (AS & mapviwewer) be looking at the same JAVA_HOME of the app server.
I don't understand your description of separate consoles for the app server and the application, unless you mean as you drill down on the application "mapviewer" with the starting point of the Enterprise Manager console.
What I meant by SQLplus is to - from the command line in the AS environment - type in 'sqlplus / as sysdba' or any other valid connect string and see whether you can get into SqlPlus on the DB side from the AS side using your AS env var's.
Are the DB and the AS on the same machine?
If so - Definitely check to make sure your ENV variables are different for each instance; using one ORACLE_HOME (or JAVA_HOME for that matter) will confuse issues and cause you all sorts of headaches. Your instances should be mapped out thus: ORACLE_BASE/ORACLE_HOME<fo
ORACLE_BASE/ORACLE_HOME<fo
Check your tnsnames.ora - there should be one for the AS and one for the DB, each located in the relative path /ORACLE_HOME/network/admin
Looks like you listener is starting up so that seems ok.
Please correct as needed, but as I understand your description, the mapviewer, i.e. the 'mvdemo' is the application you're trying to get working on the AS side, right? IF so - then they should both (AS & mapviwewer) be looking at the same JAVA_HOME of the app server.
I don't understand your description of separate consoles for the app server and the application, unless you mean as you drill down on the application "mapviewer" with the starting point of the Enterprise Manager console.
What I meant by SQLplus is to - from the command line in the AS environment - type in 'sqlplus / as sysdba' or any other valid connect string and see whether you can get into SqlPlus on the DB side from the AS side using your AS env var's.
ASKER
>>>>>>>>>>>>>>>>>>>>>>>>>>
Your comments brought up a question or two as well as suggestions:
Are the DB and the AS on the same machine?
>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>Yes, they are both on a remote hosted server that I connect to via putty, however, since the consoles are all up, I now use IE on a vista machine to access the consoles for non Linux cmds.
Here is the env (on RHEL5).........
[root@bluesky1 conf]# env
HOSTNAME=bluesky1.blueskyt
TERM=xterm
SHELL=/bin/bash
HISTSIZE=1000
KDE_NO_IPV6=1
SSH_CLIENT=99.242.219.138 52133 22
OLDPWD=/u01/app/appserver/
SSH_TTY=/dev/pts/1
USER=root
LS_COLORS=no=00:fi=00:di=0
ORACLE_SID=orcl
KDEDIR=/usr
MAIL=/var/spool/mail/root
PATH=/u01/app/appserver/10
INPUTRC=/etc/inputrc
PWD=/u01/app/appserver/101
JAVA_HOME=/u01/app/appserv
SID=orcl
LANG=en_US.UTF-8
KDE_IS_PRELINKED=1
SSH_ASKPASS=/usr/libexec/o
SHLVL=1
HOME=/root
LOGNAME=root
CVS_RSH=ssh
SSH_CONNECTION=99.242.219.
LESSOPEN=|/usr/bin/lesspip
DISPLAY=localhost:11.0
ORACLE_HOME=/u01/app/oracl
G_BROKEN_FILENAMES=1
_=/bin/env
and the TNS file;
[root@bluesky1 admin]# cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = bluesky1.blueskytelematics
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bluesky1.blueskytelematics
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
>>>>>>>>>>>>>>>>>>>>>>>>>>
Your instances should be mapped out thus: ORACLE_BASE/ORACLE_HOME<fo
ORACLE_BASE/ORACLE_HOME<fo
>>>>>>>>>>>>>>>>>>>>>>>>>>
I don't see a second tnsnames file for the AppServer.......(Oracle Application Server 10.1.3.0), other than the one shown as tnsnames.ora.sample below..
[root@bluesky1 db_1]# locate tnsnames.ora
/u01/app/appserver/10130/n
/u01/app/oracle/product/11
/u01/app/oracle/product/11
/u01/app/oracle/product/11
/u01/app/oracle/product/11
/u01/app/oracle/product/11
This is where AppServer is installed.....
/u01/app/appserver/10130..
[root@bluesky1 10130]# dir
ant bin datadirect install javavm ldap OPatch ord precomp rules sysman xds
Apache cfgtoollogs deconfig install.platform jdbc lib opatches oui rdbms slax toplink xqs
assistants chgip diagnostics inventory jdk mds opmn owm relnotes sqlj uix
backup_restore clone dsa j2ee jlib network oracore perl root.sh sqlplus webservices
BC4J config iaspt javacache jre nls oraInst.loc plsql root.sh.old srvm xdk
[root@bluesky1 admin]# pwd
/u01/app/appserver/10130/n
[root@bluesky1 admin]# dir
samples shrept.lst >>>(no tnsnames file here; do I need one?)
>>>>>>>>>>>>>>>>>>>>>>>>>>
The evolution was thus;
Installed 11g DB, then Oracle AppServer on top of it (v 10.1.3.0). Started OC4J from AppServer console
Next Installed MapViewer .ear file, and through the AppServer console, deployed it to the OC4J instance, in which it is curently the only app other than "default".
MapViewer and AppServer have two slightly different URL's for their respective consoles,
AppServer= hostname:7780/em
Mapviewer= hostname:7780/mapviewer/fa
I had assumed creating the jdbc via the appserver console would cause it to be visible in the mapviewer app (jdbc datasource name=mvdemo)
However, even though the Test Connections succeed from AS console, they do not appear in the MapViewer console?? Do I need to re-start or redeploy the MapViewer applicaiton?
I am confused for sure!
Somethings have jumped out at me while enjoying my morning java - ;-p
BTW - thanks for the detail - it helps!
Your search PATH has a mixed environment which proves to me what I suspected & alluded to previously; you'll need to correct this situation before any more troubleshooting as it could be your SOLE problem.
Below is an example of how we separate the environments when multiple ORACLE_HOMES exist on the same box.
Oracle user's profile: (Note that the variable ORACLE_HOME isn't set here)
export PATH=.:/usr/bin:/usr/ucb:/ usr/ccs/bi n:/etc:/us r/local/bi n:/usr/bin :\
/etc:/usr/ccs/bin
export SQLPATH=$ORACLE_HOME/rdbms /admin
export TNS_ADMIN=$ORACLE_HOME/net work/admin
export BOX=`uname -n`
PS1='[ $BOX $PWD $ORACLE_SID ] '
export EDITOR=vi
VISUAL=vi
FCEDIT=vi
export CLASSPATH=$ORACLE_HOME/jdb c/lib/clas ses12.zip
export CLASSPATH=$CLASSPATH:$ORAC LE_HOME/jd bc/lib/nls _charset12 .zip
#
. .switch
Then we have the .switch script which presents the different ORACLE_HOME options (still in /home/oracle):
echo "ORACLE_SID= [$NEWSID] - Please enter 'dev8' or 'dev10': "
read NEWSID
case "$NEWSID" in
dev8) . .817
;;
dev10) . .10
;;
esac
...which switches you to the different ENV variables being exported (.10 file shown below):
export FLASH=/flash_recovery_area /BETA10
export ORACLE_SID=beta10
export ORACLE_BASE=/ofs/app/oracl e
export ORACLE_HOME=$ORACLE_BASE/p roduct/10. 2.0/db_1
export DBA=$ORACLE_BASE/admin/$OR ACLE_SID
export TNS_ADMIN=$ORACLE_HOME/net work/admin
export SQLPATH=$ORACLE_HOME/rdbms /admin
export CLASSPATH=$ORACLE_HOME/jdb c/lib/clas ses12.zip: \
$ORACLE_HOME/jdbc/lib/nls_ charset12. zip
export LD_LIBRARY_PATH=$ORACLE_HO ME/lib:$OR ACLE_HOME/ ctx/lib:/u sr/lib:\
$ORACLE_HOME/bin:/usr/dt/l ib:/usr/op enwin/lib: /usr/ucbli b\
$ORACLE_HOME/jdbc/lib
export PS1='[ $BOX $PWD $ORACLE_SID ] '
echo "Welcome to [$ORACLE_SID] ORACLE10g"
ENV=.ora10rc
And then you have your rc files where your aliases and - in our case we reset some of the env vars for non-login interactive shells that are spawned. This was an important issue/solution for me when I was dealing with earlier versions of OAS (.ora10rc shown below):
export ORACLE_SID=beta10
export ORACLE_HOME=$ORACLE_BASE/p roduct/10. 2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/net work/admin
alias os="cd /home/oracle/scripts"
alias dbrun="ps -ef|grep ora_|grep -v grep|cut -c57-62|sort|uniq"
alias bdump="cd /ofs/app/oracle/admin/$ORA CLE_SID/bd ump"
alias flash="cd /flash_recovery_area/`echo $ORACLE_SID | tr [a-z] [A-Z]`"
alias nt="cd /usr/openv/netbackup"
alias oh="cd $ORACLE_HOME"
alias ob="cd $ORACLE_BASE"
alias switch=". /home/oracle/.switch"
alias ll="ls -lart"
alias top="/usr/local/bin/top"
I stress this is one of the most important prerequisites to getting your OAS/RDBMS to work together on the same box - the environments must be absolutely separate or you'll always have some issues that you can't troubleshoot!
tnsnames.ora - yup, you should have on in your OAS ORACLE_HOME/network/admin which further leads me to believe that when the OAS was installed, it was done starting out with the RDBMS ENV settings.
If my above suggestions for separating the two env's doesn't provide a solution right away, I would suggest uninstalling the OAS, ensuring the separate profiles are configured and reinstall the OAS.
It sounds to me like you're really close so don't lose hope! It is way confusing and much simpler to put each product on its own box - if you've got the hardware. good luck...
BTW - thanks for the detail - it helps!
Your search PATH has a mixed environment which proves to me what I suspected & alluded to previously; you'll need to correct this situation before any more troubleshooting as it could be your SOLE problem.
Below is an example of how we separate the environments when multiple ORACLE_HOMES exist on the same box.
Oracle user's profile: (Note that the variable ORACLE_HOME isn't set here)
export PATH=.:/usr/bin:/usr/ucb:/
/etc:/usr/ccs/bin
export SQLPATH=$ORACLE_HOME/rdbms
export TNS_ADMIN=$ORACLE_HOME/net
export BOX=`uname -n`
PS1='[ $BOX $PWD $ORACLE_SID ] '
export EDITOR=vi
VISUAL=vi
FCEDIT=vi
export CLASSPATH=$ORACLE_HOME/jdb
export CLASSPATH=$CLASSPATH:$ORAC
#
. .switch
Then we have the .switch script which presents the different ORACLE_HOME options (still in /home/oracle):
echo "ORACLE_SID= [$NEWSID] - Please enter 'dev8' or 'dev10': "
read NEWSID
case "$NEWSID" in
dev8) . .817
;;
dev10) . .10
;;
esac
...which switches you to the different ENV variables being exported (.10 file shown below):
export FLASH=/flash_recovery_area
export ORACLE_SID=beta10
export ORACLE_BASE=/ofs/app/oracl
export ORACLE_HOME=$ORACLE_BASE/p
export DBA=$ORACLE_BASE/admin/$OR
export TNS_ADMIN=$ORACLE_HOME/net
export SQLPATH=$ORACLE_HOME/rdbms
export CLASSPATH=$ORACLE_HOME/jdb
$ORACLE_HOME/jdbc/lib/nls_
export LD_LIBRARY_PATH=$ORACLE_HO
$ORACLE_HOME/bin:/usr/dt/l
$ORACLE_HOME/jdbc/lib
export PS1='[ $BOX $PWD $ORACLE_SID ] '
echo "Welcome to [$ORACLE_SID] ORACLE10g"
ENV=.ora10rc
And then you have your rc files where your aliases and - in our case we reset some of the env vars for non-login interactive shells that are spawned. This was an important issue/solution for me when I was dealing with earlier versions of OAS (.ora10rc shown below):
export ORACLE_SID=beta10
export ORACLE_HOME=$ORACLE_BASE/p
export TNS_ADMIN=$ORACLE_HOME/net
alias os="cd /home/oracle/scripts"
alias dbrun="ps -ef|grep ora_|grep -v grep|cut -c57-62|sort|uniq"
alias bdump="cd /ofs/app/oracle/admin/$ORA
alias flash="cd /flash_recovery_area/`echo
alias nt="cd /usr/openv/netbackup"
alias oh="cd $ORACLE_HOME"
alias ob="cd $ORACLE_BASE"
alias switch=". /home/oracle/.switch"
alias ll="ls -lart"
alias top="/usr/local/bin/top"
I stress this is one of the most important prerequisites to getting your OAS/RDBMS to work together on the same box - the environments must be absolutely separate or you'll always have some issues that you can't troubleshoot!
tnsnames.ora - yup, you should have on in your OAS ORACLE_HOME/network/admin which further leads me to believe that when the OAS was installed, it was done starting out with the RDBMS ENV settings.
If my above suggestions for separating the two env's doesn't provide a solution right away, I would suggest uninstalling the OAS, ensuring the separate profiles are configured and reinstall the OAS.
It sounds to me like you're really close so don't lose hope! It is way confusing and much simpler to put each product on its own box - if you've got the hardware. good luck...
Re-reading my post there are some details I missed (it's saturday!)
The PATH statement started in the original profile should be appended in one of the later files, e.g. the .10 or .ora10rc thus (after you set the ORACLE_HOME):
PATH=$ORACLE_HOME/bin:$ORA CLE_HOME/o pmn/bin:$O RACLE_HOME /dcm/bin:$ PATH
And the CLASSPATH can't really be finished until the later file when the ORACLE_HOME is declared.
sorry for the confusion.
The PATH statement started in the original profile should be appended in one of the later files, e.g. the .10 or .ora10rc thus (after you set the ORACLE_HOME):
PATH=$ORACLE_HOME/bin:$ORA
And the CLASSPATH can't really be finished until the later file when the ORACLE_HOME is declared.
sorry for the confusion.
ASKER
OK....lots o java this morn for sure, this is a bit beyond my understanding, but I am going to give it a shot....
I will need your help. Let's start with the following;
>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>> >>>
Below is an example of how we separate the environments when multiple ORACLE_HOMES exist on the same box.
Oracle user's profile: (Note that the variable ORACLE_HOME isn't set here)
DO I LOGIN AS ORACLE USER TO DO THIS? I HAVE THE STD MODS MADE IN THE .bash_profile to set env variables for the oracle user as follows------
xxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxx
[oracle@bluesky1 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
#Inserted as per Oracle requirements
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=bluesky1.b lueskytele matics.net ; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/oracl e; export ORACLE_BASE
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_HOME=$ORACLE_BASE/p roduct/11. 1.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PAT H; export PATH
LD_LIBRARY_PATH=$ORACLE_HO ME/lib:/li b:/usr/lib :/usr/dt/l ib:/usr/op enwin/lib: /usr/ccs/l ib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE :$ORACLE_H OME/jlib:$ ORACLE_HOM E/rdbms/jl ib; export CLASSPATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
[oracle@bluesky1 ~]$
xxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxx
export PATH=.:/usr/bin:/usr/ucb:/ usr/ccs/bi n:/etc:/us r/local/bi n:/usr/bin :\
/etc:/usr/ccs/bin
IS THIS TO BE MY OS root user path? Am I reading correctly- you start with = . : /usr (dot,colon,slash)
xxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxx
export SQLPATH=$ORACLE_HOME/rdbms /admin
SQLPATH is currently not set anywhere I can see, is this a std?
xxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxx
export TNS_ADMIN=$ORACLE_HOME/net work/admin
EASY ENOUGH- IS THIS TO BE A PERMANENT SETTING, SHOULD DECLARE TNS_ADMIN session only, or write to the .bash_profile files?
xxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxx
export BOX=`uname -n`
PS1='[ $BOX $PWD $ORACLE_SID ] '
I AM ASSIGNING A NAME TO THE BOX?(ex: cirrus)--what is the significance of PS1=[$BOX $PWD $ORACLE_SID] ? use "orcl" for SID value, or the dev8 and dev10 values you metioned below?
xxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx x
export EDITOR=vi
VISUAL=vi
FCEDIT=vi
vim is what I am using- any difference?
xxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx x
export CLASSPATH=$ORACLE_HOME/jdb c/lib/clas ses12.zip
export CLASSPATH=$CLASSPATH:$ORAC LE_HOME/jd bc/lib/nls _charset12 .zip
THIS IS DIFFERENT FROM THE oracle users .bash_profile setting of CLASSPATH above - and we are assigning it multiple values- how does that work? (pardon my lack of knowledge - new to this)
Will the multiple settings conflict against the bash_profile?
xxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xxxxxxxxxx xx
#
. .switch
>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>> >.>>>>>>>> >>>>>>>>>> >
As we make these changes what are our risks and should we shut-down OracleDB or OAS?
It is a lab-only server as I am sure is obvious at this point, so shut-down is OK if needed.
I will need your help. Let's start with the following;
>>>>>>>>>>>>>>>>>>>>>>>>>>
Below is an example of how we separate the environments when multiple ORACLE_HOMES exist on the same box.
Oracle user's profile: (Note that the variable ORACLE_HOME isn't set here)
DO I LOGIN AS ORACLE USER TO DO THIS? I HAVE THE STD MODS MADE IN THE .bash_profile to set env variables for the oracle user as follows------
xxxxxxxxxxxxxxxxxxxxxxxxxx
[oracle@bluesky1 ~]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
#Inserted as per Oracle requirements
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=bluesky1.b
ORACLE_BASE=/u01/app/oracl
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_HOME=$ORACLE_BASE/p
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PAT
LD_LIBRARY_PATH=$ORACLE_HO
CLASSPATH=$ORACLE_HOME/JRE
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
[oracle@bluesky1 ~]$
xxxxxxxxxxxxxxxxxxxxxxxxxx
export PATH=.:/usr/bin:/usr/ucb:/
/etc:/usr/ccs/bin
IS THIS TO BE MY OS root user path? Am I reading correctly- you start with = . : /usr (dot,colon,slash)
xxxxxxxxxxxxxxxxxxxxxxxxxx
export SQLPATH=$ORACLE_HOME/rdbms
SQLPATH is currently not set anywhere I can see, is this a std?
xxxxxxxxxxxxxxxxxxxxxxxxxx
export TNS_ADMIN=$ORACLE_HOME/net
EASY ENOUGH- IS THIS TO BE A PERMANENT SETTING, SHOULD DECLARE TNS_ADMIN session only, or write to the .bash_profile files?
xxxxxxxxxxxxxxxxxxxxxxxxxx
export BOX=`uname -n`
PS1='[ $BOX $PWD $ORACLE_SID ] '
I AM ASSIGNING A NAME TO THE BOX?(ex: cirrus)--what is the significance of PS1=[$BOX $PWD $ORACLE_SID] ? use "orcl" for SID value, or the dev8 and dev10 values you metioned below?
xxxxxxxxxxxxxxxxxxxxxxxxxx
export EDITOR=vi
VISUAL=vi
FCEDIT=vi
vim is what I am using- any difference?
xxxxxxxxxxxxxxxxxxxxxxxxxx
export CLASSPATH=$ORACLE_HOME/jdb
export CLASSPATH=$CLASSPATH:$ORAC
THIS IS DIFFERENT FROM THE oracle users .bash_profile setting of CLASSPATH above - and we are assigning it multiple values- how does that work? (pardon my lack of knowledge - new to this)
Will the multiple settings conflict against the bash_profile?
xxxxxxxxxxxxxxxxxxxxxxxxxx
#
. .switch
>>>>>>>>>>>>>>>>>>>>>>>>>>
As we make these changes what are our risks and should we shut-down OracleDB or OAS?
It is a lab-only server as I am sure is obvious at this point, so shut-down is OK if needed.
ASKER
The following has been done......
>>>>>>>>>>>>>>>>>>>>
Modified /home/oracle/.bash_profile - Commented out previous settings for ORACLE_HOME, PATH, AND CLASSPATH....(Should I also comment out the LD_LIBRARY_PATH or any others you see???,,,)
[root@bluesky1 oracle]# cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
#PATH=$PATH:$HOME/bin
#export PATH
#Inserted as per Oracle requirements
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=bluesky1.b lueskytele matics.net ; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/oracl e; export ORACLE_BASE
ORACLE_SID=orcl; export ORACLE_SID
#ORACLE_HOME=$ORACLE_BASE/ product/11 .1.0/db_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
#PATH=/usr/sbin:$PATH; export PATH
#PATH=$ORACLE_HOME/bin:$PA TH; export PATH
LD_LIBRARY_PATH=$ORACLE_HO ME/lib:/li b:/usr/lib :/usr/dt/l ib:/usr/op enwin/lib: /usr/ccs/l ib; export LD_LIBRARY_PATH
#CLASSPATH=$ORACLE_HOME/JR E:$ORACLE_ HOME/jlib: $ORACLE_HO ME/rdbms/j lib; export CLASSPATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
# The following inserted to facilitate multiple $ORACLE_HOME instances
export PATH=.:/usr/bin:/usr/ucb:/ usr/ccs/bi n:/usr/loc al/bin:/us r/bin:\/et c:/usr/ccs /bin
export SQLPATH=$ORACLE_HOME/rdbms /admin
export TNS_ADMIN=$ORACLE_HOME/net work/admin
export BOX='cirrus'
PS1='[$BOX $PWD $ORACLE_SID]'
export EDITOR=vim
VISUAL=vim
FCEDIT=vim
export CLASSPATH=$ORACLE_HOME/jdb c/lib/clas ses12.zip
export CLASSPATH=$CLASSPATH:$ORAC LE_HOME/jd bc/lib/nls _charset12 .zip
#
..switch
# End of insertions for multiple ORACLE_HOMES
# Previous PATH and ORACLE_HOME declarations in this file are# commented out above this section
>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>
Next, create the .switch file......
[root@bluesky1 oracle]# cat .switch
# File created to manage multiple Oracle Homes.
#Further modifications also made in /home/oracle/.bash_profile
#December 08, 2007 PM
echo "ORACLE_SID=[$NEWSID]
read NEWSID
case "$NEWSID" in
dev8)..817
..
"
dev10)..10
..
"
esac
[root@bluesky1 oracle]#
>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>
NOw.....where do I put the .10 and ora10rc? Please be specific about file names and directories, as I am not fully understanding exactly what we are doing, (although reading it through a few times , it is starting to sink in). Here are the instructions you gave me above....where do I put this?
...which switches you to the different ENV variables being exported (.10 file shown below):
export FLASH=/flash_recovery_area /BETA10
export ORACLE_SID=beta10
export ORACLE_BASE=/ofs/app/oracl e
export ORACLE_HOME=$ORACLE_BASE/p roduct/10. 2.0/db_1
export DBA=$ORACLE_BASE/admin/$OR ACLE_SID
export TNS_ADMIN=$ORACLE_HOME/net work/admin
export SQLPATH=$ORACLE_HOME/rdbms /admin
export CLASSPATH=$ORACLE_HOME/jdb c/lib/clas ses12.zip: \
$ORACLE_HOME/jdbc/lib/nls_ charset12. zip
export LD_LIBRARY_PATH=$ORACLE_HO ME/lib:$OR ACLE_HOME/ ctx/lib:/u sr/lib:\
$ORACLE_HOME/bin:/usr/dt/l ib:/usr/op enwin/lib: /usr/ucbli b\
$ORACLE_HOME/jdbc/lib
export PS1='[ $BOX $PWD $ORACLE_SID ] '
echo "Welcome to [$ORACLE_SID] ORACLE10g"
ENV=.ora10rc
And then you have your rc files where your aliases and - in our case we reset some of the env vars for non-login interactive shells that are spawned. This was an important issue/solution for me when I was dealing with earlier versions of OAS (.ora10rc shown below):
export ORACLE_SID=beta10
export ORACLE_HOME=$ORACLE_BASE/p roduct/10. 2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/net work/admin
alias os="cd /home/oracle/scripts"
alias dbrun="ps -ef|grep ora_|grep -v grep|cut -c57-62|sort|uniq"
alias bdump="cd /ofs/app/oracle/admin/$ORA CLE_SID/bd ump"
alias flash="cd /flash_recovery_area/`echo $ORACLE_SID | tr [a-z] [A-Z]`"
alias nt="cd /usr/openv/netbackup"
alias oh="cd $ORACLE_HOME"
alias ob="cd $ORACLE_BASE"
alias switch=". /home/oracle/.switch"
alias ll="ls -lart"
alias top="/usr/local/bin/top"
>>>>>>>>>>>>>>>>>>>>
Modified /home/oracle/.bash_profile
[root@bluesky1 oracle]# cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
#PATH=$PATH:$HOME/bin
#export PATH
#Inserted as per Oracle requirements
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=bluesky1.b
ORACLE_BASE=/u01/app/oracl
ORACLE_SID=orcl; export ORACLE_SID
#ORACLE_HOME=$ORACLE_BASE/
ORACLE_TERM=xterm; export ORACLE_TERM
#PATH=/usr/sbin:$PATH; export PATH
#PATH=$ORACLE_HOME/bin:$PA
LD_LIBRARY_PATH=$ORACLE_HO
#CLASSPATH=$ORACLE_HOME/JR
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
# The following inserted to facilitate multiple $ORACLE_HOME instances
export PATH=.:/usr/bin:/usr/ucb:/
export SQLPATH=$ORACLE_HOME/rdbms
export TNS_ADMIN=$ORACLE_HOME/net
export BOX='cirrus'
PS1='[$BOX $PWD $ORACLE_SID]'
export EDITOR=vim
VISUAL=vim
FCEDIT=vim
export CLASSPATH=$ORACLE_HOME/jdb
export CLASSPATH=$CLASSPATH:$ORAC
#
..switch
# End of insertions for multiple ORACLE_HOMES
# Previous PATH and ORACLE_HOME declarations in this file are# commented out above this section
>>>>>>>>>>>>>>>>>>>>>>>>>>
Next, create the .switch file......
[root@bluesky1 oracle]# cat .switch
# File created to manage multiple Oracle Homes.
#Further modifications also made in /home/oracle/.bash_profile
#December 08, 2007 PM
echo "ORACLE_SID=[$NEWSID]
read NEWSID
case "$NEWSID" in
dev8)..817
..
"
dev10)..10
..
"
esac
[root@bluesky1 oracle]#
>>>>>>>>>>>>>>>>>>>>>>>>>>
NOw.....where do I put the .10 and ora10rc? Please be specific about file names and directories, as I am not fully understanding exactly what we are doing, (although reading it through a few times , it is starting to sink in). Here are the instructions you gave me above....where do I put this?
...which switches you to the different ENV variables being exported (.10 file shown below):
export FLASH=/flash_recovery_area
export ORACLE_SID=beta10
export ORACLE_BASE=/ofs/app/oracl
export ORACLE_HOME=$ORACLE_BASE/p
export DBA=$ORACLE_BASE/admin/$OR
export TNS_ADMIN=$ORACLE_HOME/net
export SQLPATH=$ORACLE_HOME/rdbms
export CLASSPATH=$ORACLE_HOME/jdb
$ORACLE_HOME/jdbc/lib/nls_
export LD_LIBRARY_PATH=$ORACLE_HO
$ORACLE_HOME/bin:/usr/dt/l
$ORACLE_HOME/jdbc/lib
export PS1='[ $BOX $PWD $ORACLE_SID ] '
echo "Welcome to [$ORACLE_SID] ORACLE10g"
ENV=.ora10rc
And then you have your rc files where your aliases and - in our case we reset some of the env vars for non-login interactive shells that are spawned. This was an important issue/solution for me when I was dealing with earlier versions of OAS (.ora10rc shown below):
export ORACLE_SID=beta10
export ORACLE_HOME=$ORACLE_BASE/p
export TNS_ADMIN=$ORACLE_HOME/net
alias os="cd /home/oracle/scripts"
alias dbrun="ps -ef|grep ora_|grep -v grep|cut -c57-62|sort|uniq"
alias bdump="cd /ofs/app/oracle/admin/$ORA
alias flash="cd /flash_recovery_area/`echo
alias nt="cd /usr/openv/netbackup"
alias oh="cd $ORACLE_HOME"
alias ob="cd $ORACLE_BASE"
alias switch=". /home/oracle/.switch"
alias ll="ls -lart"
alias top="/usr/local/bin/top"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Still unclear with respect to the .10 and .ora10rc files, how to create them, and where to put them. However, I have been away a bit and am not sure when I will get back at it to force feed it to my brain. So in the meantime, I would like to THANK YOU very much and accept this, as it is my own inexperience holding me back, certainly not your lack of effort or patience. So THANKS MAN!!
Maybe in a week or so, I'll drop you a note to let you know how it went.
Maybe in a week or so, I'll drop you a note to let you know how it went.
ASKER
Very thorough, and very expanatory to a beginner. Excellent help !!
If it is 10g then there is a famous bug in the listener.ora.
I will say you what to do.
Please investigate the host name, SID name, port for Listener
and protocol for the Listener:
C:>lsnrctl status
C:>lsnrctl services
Normally port 1521 is used (your info points to 1522)