Oracle – tnsping – Message 3513 not found; product=NETWORK; facility=TNS

Short note to record this, as Google drew no hits on it.

Windows XP machine with existing Oracle 11.1 client installation, all working fine.

Installed Oracle 11.2 XE, and started getting these errors:

C:\Windows\System32>tnsping DBNAME

TNS Ping Utility for 32-bit Windows: Version 11.2.0.2.0 - Production on 26-SEP-2011 11:01:11

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\userid\product\11.1.0\client_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Message 3513 not found;  product=NETWORK; facility=TNS
OK (20 msec)

Also got these errors from a previously-functioning ODBC query in Excel when I tried to refresh it:

• [Microsoft][ODBC driver for Oracle][Oracle]
• [Microsoft][ODBC Driver Manager] Driver’s SQLSetConnectAttr failed

Google drew a blank on “Message 3513 not found”, but with a bit of guidance from Ed Stevens’ and Charles Hooper’s blogs I checked my PATH variable and found this:

C:\Windows\System32>set
[...]
ORACLE_HOME=C:\app\userid\product\11.1.0\client_1\
[...]
Path=C:\oraclexe\app\oracle\product\11.2.0\server\bin;C:\app\userid\product\11.1.0\client_1\bin;C:\Python27\;C:\Python27\Scripts;C:\OracleBI\server\Bin;C:\OracleBI\web\bin;C:\OracleBI\web\catalogmanager;C:\Program Files\Java\jdk1.6.0_26\bin;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;[...]
[...]
TNS_ADMIN=C:\app\userid\product\11.1.0\client_1\network\admin
[...]

PATH is evaluated in order, left to right. Note that the 11.2 XE binaries are now listed before the 11.1 client binaries.

So whilst the TNS_NAMES and ORACLE_HOME are still for the 11.1 client, it looks like I’m invoking the 11.2 binaries for tnsping and presumably ODBC driver too.

How to fix

Moving the path of 11.2 XE bin to the end of the PATH variable fixed the problem. Presumably also removing the 11.2 XE path would have worked.

I don’t know if there are going to be other ramifications of changing this path variable around (presumably XE would start hitting 11.1 binaries??), but it fixed my immediate problem both with TNSPing and the ODBC queries.

Screenshot

Oracle XE 11gR2 installation – “OracleXEService should not be installed already”

Oracle XE 11gR2 beta has just been released, some details here and download here. It’s not a great deal of use for sandboxing DWH-specific stuff, given this list of excluded functionality (and this is by no means everything that’s not included):

• Bitmapped index, bitmapped join index, and bitmap plan conversions
• Oracle Partitioning
• Parallel Data Pump Export/Import
• Parallel query/DML
• Parallel Statement Queuing

(source)

However, it’s always interesting to have to hand for trying out other things. And I like playing with new toys 🙂

On running the installer, I hit this problem:

"OracleXEService should not be installed already"

[…]

Checking for Oracle XE Service instance…:
Expected result : OracleXEService should not be installed already.
Actual result: OracleServiceXE found on system.
Check complete: The overall result of this check is failed.

The previous owner of the machine had installed Oracle XE 10, but I’d removed this:

No Oracle XE 10g listed in add/remove programs

Or so I thought:

OracleServiceXE still listed in Services

To remove the service entry, use the sc command:

sc delete OracleServiceXE

For good measure I did the same for OracleJobSchedulerXE.

Delete service entries using sc

After this, installation proceeded as normal.

How to resolve “[nQSError: 12002] Socket communication error at call=: (Number=-1) Unknown”

This error caught me out today. I was building a Linux VM to do some work on, and for the life of me couldn’t get the OBIEE Admin Tool to connect to the BI Server on the VM.

The error I got when trying to define a DSN on the Windows box was:

[nQSError: 12008] Unable to connect to port 9703 on machine 10.3.105.132
[nQSError: 12010] Communication error connecting to remote end point: address = 10.3.105.132; port = 9703.
[nQSError: 12002] Socket communication error at call=: (Number=-1) Unknown

This error means that the ODBC Driver for BI Server can’t communicate with the BI Server on port 9703.
99% of the time this question comes up on the forums it’s because the BI Server isn’t running, or the host is incorrect.

I validated the BI Server was running and listening on port 9703:

[oracle@RNMVM03 setup]$ netstat -a|grep 9703
tcp        0      0 *:9703                      *:*                         LISTEN

And I fired up Presentation Services and OC4J and successfully logged into Answers. So why couldn’t my Windows box connect?

I tried telnetting from my Windows box to the VM on port 9704 – the OC4J port. This worked, as did pinging it. So the network connectivity between the two was there. If I telnetted to port 9703 (BI Server) there was an eventual timeout.

The answer to the problem was that my Linux VM (OEL5.4) was running a firewall which I’d cleverly allowed 9704 on but not 9703. Disabling the firewall fixed the problem.

AdminTool.exe /command

There’s an undocumented feature in AdminTool.exe that you can use the /command switch with a text file containing scripted commands to make changes to an RPD file (or create a new one).

It’s undocumented and UNSUPPORTED so be careful using it.

Some good details in these blog posts, especially Erik’s which has a good list of syntax.

• Venkat
• Erik Eckhardt (translated from Czech, original here)
• @lex
• Kumar Kambam

I’m intrigued to know how the original posters figured out the commands available, if it’s undocumented… 🙂

[update]
Just discovered that CAF uses this functionality in order to Consistency Check the altered RPD that it can create:
CAF uses commandline script:
C:\OracleBI\server\bin\AdminTool /command C:\CAF_Training\Target\Consistency.config

Open C:\CAF_Training\Target\paint.rpd Administrator Administrator
Hide
ConsistencyCheck C:\CAF_Training\Target\ConsistencyCheck.log
Exit

[/update]

CAF troubles

Following the Oracle CAF tutorial here, I got to Cloning Answers Requests section and then got stuck. I’d set up my environment exactly the same as in the tutorial, down to the same paths etc. After firing up the CAF to clone requests from the SampleSales catalog:

I clicked on Next and got the error “Exception occurred when while initializing repository!!!”

By playing around with the passwords and path names I determined that both RPD files existed and that CAF could load them enough to validate the passwords. If the password is incorrect you get the error “The repository C:\CAF_Training\Source\samplesales.rpd cannot be opened”.

Watching what happens in from ProcMon it looks like it checks the target RPD (paint) is present, then connects to Presentation Services web service. In the sawserver log (with enhanced logging) all that’s really shown is the login request comes in and a valid session ID is returned.

In ContentAccelerator.properties I’ve set LOG_FOLDER=C\:\\OracleBI\\web\\catalogmanager\\log but just get a zero-byte CAF_Exception.log file in that directory. The double slashes are deliberate c.f. the CAF usage instructions. The doc also indicates that the logging will be for operations carried out (clone etc), not for the tool as such.

Can anyone suggest how to resolve the “Exception occurred when while initializing repository!!!” error?

Footnote – RTFM, a.k.a set your paths right!

Digging around with the brilliant Process Monitor and ProcessExplorer I noticed that Catalog Manager was running with JDK 1.5:

I’d seen the 1.6 requirement but have it installed so didn’t think too much about it. Clearly 1.5 was taking precedence, as could be seen from my PATH variable:

Even after updating the PATH variable through settings > Control Panel > System Properties > Advanced > Environment Variables, Catalog Manager was still using JDK 1.5. ProcessExplorer can be used to examine the environment variables that a process is using by right-clicking on a process and selecting Properties and then the Environment tab:

A reboot fixed this (at least, CatalogManager now fired up using the correct JDK 1.6 – which I’d have had in the first place if I’d RTFM‘d … ), but I then got a new error in the same place as before (i.e. after clicking on Next in the Content Accelerator Wizard):

D’oh – after the reboot I’d forgotten to restart my OBIEE services.

But — I still have the Exception occurred when while initializing repository!!!” problem 😦

Fixed

Update 9-Sep-09:
Turns out I was almost on the right lines with my comment above about PATHs and java.
There are two downloads for CAF, one when it was released (v1.01) and the current one (v1.02). In the readme for v1.02 java 1.6.0_10 is specified, whereas in v1.01 it only states 1.6.

After I’d upgraded my java installation (from 1.6.0_03 to 1.6.0_16) CAF worked fine.

Querying SQL Server from OBIEE running on Unix

A question that pops up on the OBIEE OTN forum quite often is how to use non-Oracle databases like MS SQL Server when the OBIEE server is running on a non-Windows OS such as Linux.

The answer in a nutshell is that since version 10.1.3.3.1 OBIEE has been bundled with ODBC drivers for unix/linux from a company called DataDirect. See the release notes here for more information and installation instructions (as well as a list of support databases).

The instructions are pretty simple but here’s a step-by-step guide, in this instance for data on SQL Server. The steps vary a bit for other database so check the release notes.

• On Windows define a System DSN for your SQL Server database. Set the default database to the database you are working with
• Build and test your RPD, setting your connection pool to ODBC 3.5 and data source name to the DSN you defined in step (1).
• On your Linux (or unix, but from here on I’ll just write Linux!) box, locate the fully-qualified path to the ODBC driver file, SEmsss23.so. It should be in $OracleBI/odbc/lib (or lib64)
• Backup $OracleBI/setup/odbc.ini
• Copy the entry from the release notes into odbc.ini.

[YourDSNHere]
Driver=/path/to/OracleBI/odbc/lib/SEmsss23.so
Description=DataDirect 5.1 SQL Server Wire Protocol
Address=0.0.0.0,1433
AlternateServers=
AnsiNPW=Yes
ConnectionRetryCount=0
ConnectionRetryDelay=3
Database=yourDB
LoadBalancing=0
LogonID=
Password=
QuoteID=No
ReportCodePageConversionErrors=0

Points to note:

• The header of the entry should correspond to your DSN you used on Windows:
• The Address should be the IP (I’ve not tested with hostname) of the SQL Server, then a comma, then the port (default1433). It is a comma, not a colon!
• Remember to set Database to the database that you’re using
• Full documentation is on the DataDirect website

• For SQL Server, backup $OracleBI/server/Config/DBFeatures.INI and then edit it to change

  [ DATA_SOURCE_FEATURE = SQL_SERVER_70 ]
   [...]
  IDENTIFIER_QUOTE_CHAR = '"';
  [...]

  to

  [ DATA_SOURCE_FEATURE = SQL_SERVER_70 ]
   [...]
  IDENTIFIER_QUOTE_CHAR = '';
  [...]

  (if your Database in the Physical Database data source definition is not SQL Server 7.0/2000 then you’ll need to edit the relevant SQL_SERVER section)

• Copy across your RPD to your linux server and start BI Server up.
• Use the Issue SQL Directly function of Answers to trial the connection (or through nqcmd, or Oracle ODBC client). If you get an error double check your odbc.ini configuration. Also, make your you have “Use Oracle BI Presentation Services Cache” UNTICKED, as it will cache the response to your query so even if everything’s set up correctly after fixing an error you’ll still get an apparent failure!
• If everything works you should see your data returned:

OBIEE Windows PerfMon counters

Yet another way to access the BI Management data discussed here – through Windows’ PerfMon tool.

This will only work for installations where your OBIEE server is running on Windows. You should be able to run PerfMon locally or remotely. Standard practise would be not to run it locally on a Production machine 🙂

To run PerfMon go to Start->Run and enter perfmon, or navigate Start -> Settings -> Control Panel -> Administrative Tools -> Performance

By default a handful of metrics about your local machine are displayed:

Right click and Add Counters:
If you want to monitor a remote installation of OBIEE (on Windows only, remember) then enter the network name eg \\MYREMOTESERVER in ‘Select counters from Computer:’, otherwise set this to ‘Use local computer counters’.

Then click on Performance object dropdown, and you should see a long list of Oracle BI performance objects:
Pick one of these and a list of counters within the object will be listed. You can add all, some or just one of these.

By default the Performance Counters are installed I think, but both NQSServer.exe and sawserver.exe have commandline options for reinstalling them (or uninstalling, if you want to):

NQSServer.exe /installperf
NQSServer.exe /uninstallperf

sawserver.exe /installperf
sawserver.exe /uninstallperf

PerfMon is documented well elsewhere on the web so I won’t say much more other than that you can use it interactively or logging to file. The latter would be very useful for trending of performance data, you could even go full circle and analyse it with Answers 🙂

Final thought is that exposing the data this way is very helpful for Systems Management, as you now have the option of using MOM/SCOM, etc to monitor and alert on your BI servers.

I would imagine some or all of the above functionality is also available through the BI Management Pack for Enterprise Manager, but this is another way to skin the cat.

psservice – Windows command line goodness!

Our main servers are Unix and I’m as happy as a pig in muck at the command line, so when I’m working on Windows (where I’ve got a test OBIEE install) I like to stick with the CLI where possible.

PSService is one of those tools that I instinctively reach for without realising it. Combined with Launchy, it’s even better.

Simply put, you can control windows services from the command line.

So to restart Presentation Services, instead of

Start -> Settings -> Control Panel -> Administrative Tools -> Services, scroll down, find service, right click, select restart,

I do

alt-space -> cmd [tab] psservice restart sawsvc

You can query service statuses, and for multiple services at a time:

C:\Windows\System32>psservice query "Oracle BI"
PsService v2.21 - Service information and configuration utility
Copyright (C) 2001-2006 Mark Russinovich
Sysinternals - www.sysinternals.com

SERVICE_NAME: Oracle BI Cluster Controller
DISPLAY_NAME: Oracle BI Cluster Controller
(null)
TYPE              : 10 WIN32_OWN_PROCESS
STATE             : 1  STOPPED
                    (NOT_STOPPABLE,NOT_PAUSABLE,IGNORES_SHUTDOWN)
WIN32_EXIT_CODE   : 1077 (0x435)
SERVICE_EXIT_CODE : 0  (0x0)
CHECKPOINT        : 0x0
WAIT_HINT         : 0x0

SERVICE_NAME: Oracle BI Scheduler
DISPLAY_NAME: Oracle BI Scheduler
(null)
TYPE              : 10 WIN32_OWN_PROCESS
STATE             : 1  STOPPED
                    (NOT_STOPPABLE,NOT_PAUSABLE,IGNORES_SHUTDOWN)
WIN32_EXIT_CODE   : 1077 (0x435)
SERVICE_EXIT_CODE : 0  (0x0)
CHECKPOINT        : 0x0
WAIT_HINT         : 0x0

SERVICE_NAME: Oracle BI Server
DISPLAY_NAME: Oracle BI Server
(null)
TYPE              : 10 WIN32_OWN_PROCESS
STATE             : 4  RUNNING
                    (STOPPABLE,NOT_PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE   : 0  (0x0)
SERVICE_EXIT_CODE : 0  (0x0)
CHECKPOINT        : 0x0
WAIT_HINT         : 0x0

SERVICE_NAME: sawjavahostsvc
DISPLAY_NAME: Oracle BI Java Host
(null)
TYPE              : 10 WIN32_OWN_PROCESS
STATE             : 4  RUNNING
                    (STOPPABLE,NOT_PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE   : 0  (0x0)
SERVICE_EXIT_CODE : 0  (0x0)
CHECKPOINT        : 0x0
WAIT_HINT         : 0x0

SERVICE_NAME: sawsvc
DISPLAY_NAME: Oracle BI Presentation Server
(null)
TYPE              : 10 WIN32_OWN_PROCESS
STATE             : 4  RUNNING
                    (STOPPABLE,NOT_PAUSABLE,ACCEPTS_SHUTDOWN)
WIN32_EXIT_CODE   : 0  (0x0)
SERVICE_EXIT_CODE : 0  (0x0)
CHECKPOINT        : 0x0
WAIT_HINT         : 0x0

PSService can be run remotely too, so you could use it for remote administration of your servers.

You can get full syntax from psservice -help

Services can be referenced by either their name (sawsvc) or display name (Oracle BI Presentation Server)

BTW if you like this kind of thing and work with Windows rather than Unix, check out Microsoft’s PowerShell – it’s one of the few things I’ve missed since moving back to working primarily with Unix! 😀 (I don’t mean that in a Microsoft-bashing way, just that it’s really satisfying to work with)

[Edit]
You can also use the builtin net command for controlling services, but it’s not as functional as psservice
[/edit]