Database Software

In LISTENER.ORA file:
for PLSExtProc set PROGRAM=extproc
for MyNameSID set PROGRAM=hsodbc

Don't forget to restart listener after modification of LISTENER.ORA file.

Also look at forum thread
981851
maybe it will be helpfull for you.

Regards,
Oleg

Hi.

I made those changes to the LISTENER.ORA file and then stopped and started the listener using "lsnrctl stop" and "lsnrctl start" but i still get the same errors.

Please post here
1) contents of init.ora file from ORACLEHOME\hs\admin directory (and name of this file)
2) contents of listener.ora, tnsnames.ora, sqlnet.ora files from ORACLEHOME\network\admin directory
3) command which you used for creating database link

filename: initAlexGeneratedDataSite1.ora
Contents: HS_FDS_CONNECT_INFO = AlexGeneratedDataSite1

My System DSN is also called: AlexGeneratedDataSite1 # listener.ora Network Configuration File: H:\app\administrator\product\11.1.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = SDMFLAGSHIP.SDM.com)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = AlexGeneratedDataSite1)
(ORACLE_HOME = H:\app\administrator\product\11.1.0\db_1)
(PROGRAM = hsodbc)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = H:\app\administrator\product\11.1.0\db_1)
(PROGRAM = extproc)
)
)


# tnsnames.ora Network Configuration File: H:\app\administrator\product\11.1.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ETLTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SDMFLAGSHIP.SDM.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ETLTest)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SDMFLAGSHIP.SDM.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

# sqlnet.ora Network Configuration File: H:\app\administrator\product\11.1.0\db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
i didn't creatre a database link.

Following [here |http://download.oracle.com/docs/cd/E11882_01/owb.112/e10582/gateways_connection.htm#CACCIBCD] after making the odbc settings, i then try "Creating an ODBC Module" where i enter the details using "Host:Port:Service" when i try the "test connection" button it gives me the error.

Is creation of a database link another step i need to do?

What parameters did you enter for host/port/service?


Add additional TNS description into TNSNAMES.ORA file:
CACHEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SDMFLAGSHIP.SDM.com)(PORT = 1521))
(CONNECT_DATA = (SID=AlexGeneratedDataSite1))
(HS = OK)
)

Then try to create database link with command (if Cache database is case sensitive to username and password you need to enclose values for username and password indo doublequotes)
CREATE DATABASE LINK dbl_cachedb connect to <Cache DB username> identified by <Cache DB user password> using 'CACHEDB';

Then try to select:
select * from all_tables@dbl_cachedb;

host is the ip address of the Cache db i want to connect to. port is the cache db port number and service is AlexGeneratedDataSite1
>
> I presume the host and port here should the same as the Cache db's host and port number.

> I tried the command, shown below where username and password were replaced with real values:


SQL> CREATE DATABASE LINK dbl_cachedb connect to USERNAME identified by PASSWORD usi
ng 'CACHEDB';

Database link created.

SQL> select * from all_tables@dbl_cachedb;
select * from all_tables@dbl_cachedb
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

You are wrong. You should
- use for host address of server with Oracle database (SDMFLAGSHIP.SDM.com)
- use for port value 1521
- service is correct

No, host=SDMFLAGSHIP.SDM.com, port=1521

I hope you modify tnsnames.ora on the server side (server with Oracle database)?

Regards,
Oleg

ok.

I dropped the database link, changed the tnsnames.ora file to have:
CACHEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SDMFLAGSHIP.SDM.com)(PORT = 1521))
(CONNECT_DATA = (SID=AlexGeneratedDataSite1))
(HS = OK)
)

recreated the link and it still gives me the error:
SQL> select * from all_tables@dbl_cachedb;
select * from all_tables@dbl_cachedb
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified

in design centre, creatring module i changed host to localhost and port to 1521, service name to AlexGeneratedDataSite1
i still get:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from OWB_56

From reading the desciption of how to enter detail for the module:
Host:Port:Service
If you selected Host:Port:Service, then provide the following connection details:
* User Name/Password: You can provide a dummy user name and password as you are not connecting to an Oracle database.
* Host: Machine on which the database is hosted.
* Port: SQL port number of the database.
* Service Name: The SID_NAME that you specify in the listener.ora file.

i was under the impression that it was the Cachedb details i needed to enter here, but your saying that these should be the oracle db settings?

I am running owb on the server which is running the oracle db.

Hi, This error indicate that you modify tnsnames.ora file in wrong location.

Please describe your environment in detail as much as possible:
1) what is the hostname of server with target Oracle database
2) how did you connect to OWB repository with OWB Design Center (which parameter you entered for host name)
3) what is the hostname of server with Cache DB
4) what is the hostname of computer where you run OWB client (OWB Design Center)

Regards,
Oleg

SDMflagship localhost i am using the ipaddress of the machine, not sure if its name is registered, could add it to the hosts file if required i am running this on the server, so also SDMflagship

The machine was restarted so i have to start the database again.

In Oracle Enterprise web interface it asks me for "Host Credentials" and "Database Credentials" what usernames are these supposed to be? i have tried variants of SYS,sys,SYSTEM and system but i keep getting:
RemoteOperationException: ERROR: Invalid username and/or password

I have a very little experience with OEM, so I can't help you with this product.
But on MS Windows to start Oracle database it is enough to start corresponding Windows service,
Oracle database instance service has name OracleService<SIDNAME>.

The OracleServiceORCL is in the started state.

When i tried to log into to warehouse builder design centre it gives me an error saying "API5022: Cannot connect to the specified account. Verify connection information"

I want to reset the user password to see if thats the issue.

When i launch the web interface it says "Enterprise Manager is not able to connect to the database instance. " and it shows the orcl database as being down.

restarting the OracleServiceORCL service did not help.

Open commandline window, execute commands:
set ORACLE_SID=ORCL
sqlplus / as sysdba

If sqlplus reports "Connected to an idle instance", execute startup commands

Also check database alert log for any error

Running that i get:

H:\app\administrator\product\11.1.0\db_1\BIN>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Nov 17 09:42:53 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

It seems that database instance is up and running.
Check DB instance status with SQL (from sqlplus session)
select status from v$instance

Also check status of Oracle listener (with command lsnrctl status executed from Windows commandline, or check status of corresponding Windows service - service with name Oracle<Home_name>TNSListener)

SQL> select status from v$instance;*
STATUS*
OPEN*
SQL>*

> C:\Documents and Settings\administrator.SDM>lsnrctl status*

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 17-NOV-2009 11:35:22*

Copyright (c) 1991, 2007, Oracle. All rights reserved.*

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))*
TNS-12541: TNS:no listener*
TNS-12560: TNS:protocol adapter error*
TNS-00511: No listener*
+32-bit Windows Error: 2: No such file or directory+
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SDMFLAGSHIP.SDM.com)(PORT=1521)))*
TNS-12541: TNS:no listener*
TNS-12560: TNS:protocol adapter error*
TNS-00511: No listener*
+32-bit Windows Error: 61: Unknown error+

OracleOraDb11g_home1TNSListener is not running.

so i started the OracleOraDb11g_home1TNSListener service.

than ran:
C:\Documents and Settings\administrator.SDM>lsnrctl status*

LSNRCTL for 32-bit Windows: Version 11.1.0.6.0 - Production on 17-NOV-2009 11:38:56*

Copyright (c) 1991, 2007, Oracle. All rights reserved.*

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))*
STATUS of the LISTENER*

Alias LISTENER*
Version TNSLSNR for 32-bit Windows: Version 11.1.0.6.0 - Production*
Start Date 17-NOV-2009 11:38:31*
Uptime 0 days 0 hr. 0 min. 25 sec*
Trace Level off*
Security ON: Local OS Authentication*
SNMP OFF*
Listener Parameter File H:\app\administrator\product\11.1.0\db_1\network\admin\listener.ora*
Listener Log File h:\app\administrator\diag\tnslsnr\SDMFLAGSHIP\listener\alert\log.xml*
Listening Endpoints Summary...*
+(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))+
+(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SDMFLAGSHIP.SDM.com)(PORT=1521)))+
Services Summary...*
Service "AlexGeneratedDataSite1" has 1 instance(s).*
Instance "AlexGeneratedDataSite1", status UNKNOWN, has 1 handler(s) for this service...*
Service "PLSExtProc" has 1 instance(s).*
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...*
The command completed successfully*


Now that the listener is running i can login to design centre.

I then changeed in LISTENER.ORA string PROGRAM=hsodbc to PROGRAM=dg4odbc.

Stopped and started lsnctrl

Tried to create location as before, using test connection it give me the following error:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:*
Cache ODBC State : S1C00 Native Code 469*
H:\app\administrator\product\11.1.0\db_1\bin\dg4odbc.exe*
Driver not capable*
ORA-02063: preceding 4 lines from OWB_61*

This looks like a different problem i need to look into.

it seems error returned from Cache DB.
I have never worked with Cache DB so I can't tell what a reason for this error.
Maybe it would be better to ask this question on [Heterogeneous Connectivity forum|http://forums.oracle.com/forums/forum.jspa?forumID=63]

Regards,
Oleg

I figured that was the case alright.

Looking into that now.

Thanks for the help.