Two databases in one PHD
Robert J.M. Edis
robert.edis@creatcomp.com
Fri, 25 Feb 2000 14:58:11 -0500
Thanks Mark, that worked!
I can now do:
ACCESS table IN DB1 and
ACCESS table IN DB2
successfully.
Blue
-----Original Message-----
From: Fry, Mark
To: 'Robert J.M. Edis'
Sent: 2/25/00 4:42 AM
Subject: RE: Two databases in one PHD
Hi Robert (or is it Blue?)
LD_LIBRARY_PATH is one of the pointers to the ORACLE shared libraries.
SHLIB_PATH is the other. I notice that neither variable includes a path
to
$ORACLE_HOME/lib. Maybe you could try adding LD_LIBRARY_PATH (pointing
to
$ORACLE_HOME/lib) and tag $ORACLE_HOME/lib onto the end of your existing
SHLIB_PATH. I don't think this is your problem, though.
Looking again at your tnsnames.ora file, you could try
sqlplus $VORA1/$VORA2@DB1.world
I think the tns listener is actually looking for the tns name on the
connect
string, not the SID. That's why it can't resolve DB1 - your tns name is
DB1.world.
My guess is that simply changing your dictionary to say:
Database DB1 type ORACLE open ORACLE@$VORA1@DB1.world/$VORA2 owner xxx
Database DB2 type ORACLE open ORACLE@$VORA1@DB2.world/$VORA2 owner xxx
Will fix your problem. Either that or change tnsnames.ora so that the
tns
names are DB1 and DB2.
If I'm right, you can buy me a beer next time you're in the UK ;-)
Best regards,
Mark Fry
Cognos Limited
email: Mark.Fry@Cognos.com
-----Original Message-----
From: Robert J.M. Edis [mailto:robert.edis@creatcomp.com]
Sent: 24 February 2000 16:09
To: 'Fry, Mark '
Subject: RE: Two databases in one PHD
G'day Mark
Here is what I have for the environment varaibles you listed. Only the
first one is not set - what's it for?:
$ echo $LD_LIBRARY_PATH
$ echo $ORACLE_HOME
/usr/oracle/product/7.3.4
$ echo $ORACLE_SID
DB1
$ echo $SHLIB_PATH
/usr/cognos/ph733d3/lib
$ echo $PATH
/usr/oracle/product/7.3.4/bin:/home/lantan/v63oln:/home/lantan/v63oln/qu
iz:/
binn
$ echo $VORA1
FREDDY
$ echo $VORA2
MERCURY
(Actual values for SID, userid and password changed to protect the
guilty.
;)
I'll try the GLOBAL thing in the tnsnames.ora file but I suspect that
for
some reason niether PH or SqlPlus can find/read it. I have no problem
connecting to the default RDBMS with the connect string
ORACLE@userid/password, i.e. the SID declared in ORACLE_SID.
My UNIX version won't accept 'sqlplus $VORA1/$VORA2@DB1' as it bulks on
the
'@' character, even if entered as \@. The 'connect' syntax once at the
sqlplus prompt also reject anything after the password in the unix
version
but is quite happy with it in the sqlplus 3.3 version on the PC!
Regards,
Blue
--Original Message-----
From: Fry, Mark
To: 'Robert J.M. Edis'; 'powerh-l@list.swau.edu'
Sent: 2/24/00 4:20 AM
Subject: RE: Two databases in one PHD
Hi Robert,
What happens if you get to a unix shell and enter
sqlplus $VORA1/$VORA2@DB1
or
sqlplus $VORA1/$VORA2@DB2
Does sqlplus connect OK, or do you get a similar message from TNS?
The syntax you have used in your dictionary looks fine to me.
Is your Oracle TNS listener daemon started?
Are all your environment variables set correctly? (below is an example
of
c-shell syntax I use for Oracle 8 - the same variables are used for
Oracle 7
too)
setenv LD_LIBRARY_PATH /isv/oracle/8.0.3/lib
setenv ORACLE_HOME /isv/oracle/8.0.3
setenv ORACLE_SID ORASOL80
setenv SHLIB_PATH $ORACLE_HOME/lib:$SHLIB_PATH
setenv PATH $ORACLE_HOME/bin:$PATH
What about your $VORA1 and $VORA2 environment variables? Have you
checked
that they contain your username and password?
Here are the entries from tnsnames.ora that I use [hostnames have been
changed to protect the innocent :)]:
ORASOL80 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = <hostname of a Cognos ORACLE server>)
(PORT = 1521)
)
(CONNECT_DATA = (SID = ORASOL80)(GLOBAL_NAME=ORASOL80)
)
)
ORASOL80.world =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = <same hostname as above>)
(PORT = 1521)
)
(CONNECT_DATA = (SID = ORASOL80)(GLOBAL_NAME=ORASOL80.world)
)
)
The only real difference I can see is that I have a GLOBAL_NAME set and
you
do not. Might be worth setting it to see if it makes a difference.
Lots of questions, but no definite solutions (so far).
Regards,
Mark Fry
Cognos Limited
email: Mark.Fry@Cognos.com
-----Original Message-----
From: Robert J.M. Edis [mailto:robert.edis@creatcomp.com]
Sent: 23 February 2000 16:49
To: 'powerh-l@list.swau.edu'
Subject: Two databases in one PHD
G'day all
Environment: HP/UX 10.20, PH 7.33.D3, Oracle 7.3.4
I am trying to put two database contections in the same PHD. Both
RDBMS's
have the same structure, including table and columns names but a for
different organisations.
My environment variables ORACLE_SID, ORACLE_TERM, ORACLE_HOME and
TNS_ADMIN
are set correctly. The PATH points to .../oracle.7.3.4/bin etc.
My PHD declarations are:
Database DB1 type ORACLE open ORACLE@$VORA1@DB1/$VORA2 owner xxx
Database DB2 type ORACLE open ORACLE@$VORA1@DB2/$VORA2 owner xxx
The tnsnames.ora entries are:
DB1.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 4.56.211.59)
(Port = 1521)
)
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 4.56.211.59)
(Port = 1526)
)
)
(CONNECT_DATA = (SID = DB1)
))
DB2.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 4.56.211.59)
(Port = 1521)
)
(ADDRESS =
(COMMUNITY = tcp.world)
(PROTOCOL = TCP)
(Host = 4.56.211.59)
(Port = 1526)
)
)
(CONNECT_DATA = (SID = DB2)
))
Problem:
> acc table1 in db1
*W* SQL START DBE error (ORACLE@$VORA1@DB1/$VORA2)
DMS-E-GENERAL, A general exception has occurred during operation 'attach
database'.
ORA-12154: TNS:could not resolve service name
*E* The file TABLE1 IN DB1 wasn't found in the data dictionary.
> acc table1 in db2
*W* SQL START DBE error (ORACLE@$VORA1@DB2/$VORA2)
DMS-E-GENERAL, A general exception has occurred during operation 'attach
database'.
ORA-12154: TNS:could not resolve service name
*E* The file TABLE1 IN DB2 wasn't found in the data dictionary.
>
I looked at the knowledgebase at the Cognos support site and that is how
I
derived the logic above (document: KB57519CS). The document has some
typograhpical errors but I worked around those.
Anyone have an idea as to why this is not working?
Blue
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
= =
Subscribe: "subscribe powerh-l" in message body to
majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to
majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
This list is closed, thus to post to the list, you must be a subscriber.