BI Publisher – error creating Quartz tables

A very short blog post to break the drought, but I didn’t hit any google results for this error so thought it might be useful to record it.

In BI Publisher 10.1.3.4, trying to install the Scheduler (Quartz) schema, I got this error:

Schema installation failed while creating tables. Schema may already exist. Please remove the existing schema or choose another database and try again.

To me, the error text is a bit unhelpful. Whilst the first statement is correct – “Schema installation failed while creating tables”, it doesn’t tell you the error it encountered, and then it goes on to suggest only one reason for the failure.

The problem in this case was that my user didn’t have privileges to create the tables:

SQL> create table test_table (col1 int);
create table test_table (col1 int)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Granting the CREATE TABLE privilege resolved the problem.

Bug in Clustered Publisher Scheduler – ClusterManager: detected 1 failed or restarted instances

Follow on from setting up Publisher in a clustered environment, I’ve found a nasty little bug in the scheduling element of Publisher, Quartz.

Looking at the oc4j log file /opmn/logs/default_group~home~default_group~1.log I can see OC4J starting up, and then a whole load of repeated messages:

09/03/30 11:28:43 Oracle Containers for J2EE 10g (10.1.3.3.0) initialized
– ClusterManager: detected 1 failed or restarted instances.
– ClusterManager: Scanning for instance “myserver.fqdn.company.net1238408921404″‘s failed in-progress jobs.
– ClusterManager: detected 1 failed or restarted instances.
– ClusterManager: Scanning for instance “myserver.fqdn.company.net1238408921404″‘s failed in-progress jobs.
– ClusterManager: detected 1 failed or restarted instances.
– ClusterManager: Scanning for instance “myserver.fqdn.company.net1238408921404″‘s failed in-progress jobs.
– ClusterManager: detected 1 failed or restarted instances.
– ClusterManager: Scanning for instance “myserver.fqdn.company.net1238408921404″‘s failed in-progress jobs.
– ClusterManager: detected 1 failed or restarted instances.
– ClusterManager: Scanning for instance “myserver.fqdn.company.net1238408921404″‘s failed in-progress jobs.
– ClusterManager: detected 1 failed or restarted instances.
– ClusterManager: Scanning for instance “myserver.fqdn.company.net1238408921404″‘s failed in-progress jobs.
– ClusterManager: detected 1 failed or restarted instances.
– ClusterManager: Scanning for instance “myserver.fqdn.company.net1238408921404″‘s failed in-progress jobs.
– ClusterManager: detected 1 failed or restarted instances.
– ClusterManager: Scanning for instance “myserver.fqdn.company.net1238408921404″‘s failed in-progress jobs.
[… repeated for 38MB worth ]

Metalink to the rescue …. a search for “Search: ClusterManager: Scanning for instance” throws up doc 739623.1 – Repeated Error Appears In Log File – ClusterManager: detected 1 failed or restarted instances which details the problem and references bug # 7264646.

This is a bug in Quartz (the Publisher scheduling tool), which has been fixed in 1.5.2 (the version that’s included with Publisher is 1.5.1).

On my installation quartz was located in /j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/lib

Implenting the fix described on Metalink doc 739623.1 solved the problem.

Which jdbc driver to use

In setting the scheduler in Publisher I discovered a useful difference in jdbc drivers.
Our repository is on Oracle 11g.
According to the manual oracle.jdbc.driver.OracleDriver should be used, but previous installations have used oracle.bi.jdbc.AnaJdbcDriver so I tried this too.

In experimenting with both I found you get more useful feedback from the second one. Here’s the same problem reported by both drivers:

· Exception [TOPLINK-4002] (Oracle TopLink – 11g Release 1 (11.1.1.0.0) (Build 080319)): oracle.toplink.exceptions.DatabaseException Internal Exception: java.sql.SQLException: ORA-28000: the account is locked Error Code: 28000

· Exception [TOPLINK-4021] (Oracle TopLink – 11g Release 1 (11.1.1.0.0) (Build 080319)): oracle.toplink.exceptions.DatabaseException Exception Description: Unable to acquire a connection from driver [oracle.bi.jdbc.AnaJdbcDriver], user [OBIEE_PUBL_SCHED] and URL [jdbc:oracle:thin:@dbserver.company.com:1521:ORACLESID]. Verify that you have set the expected driver class and URL. Check your login, persistence.xml or sessions.xml resource. The jdbc.driver property should be set to a class that is compatible with your database platform Internal Exception: java.sql.SQLException: ORA-28000: the account is locked Error Code: 28000

As you can see with the highlighting that I’ve added the second driver gives you the really useful stuff – which ID and server it’s trying to connect to.

Obviously I can check what’s been configured to trace back which ID and server should be being used – but it’s always useful to get confirmation of what it’s actually doing just to rule out me having been stupid and typed the wrong options 🙂

Clustering Publisher – Scheduler and Report Repository

The Oracle BI Publisher Enterprise Cluster Deployment doc which I just found through Metalink highlighted a couple of points:
– Report repository should be shared
– The scheduler should be configured for a cluster

Report Repository
Through Admin>System Maintenance>Report Repository I changed the path from the default, /xmlp/XMLP to a NFS mount data/shared/xmlp and restarted the xmlpserver application in OAS. On coming back up Publisher complained because all its config files (in xmlp/Admin), had disappeared. I’d not moved any of the contents of /xmlp/XMLP since Report Repository suggested to me that it was just for reports, ergo with no reports yet created there was nothing to move.
So pedantaries aside, I moved the contents of /xmlp/XMLP to my new share, data/shared/xmlp. Publisher was happy after this.

A side effect of config being held in the “Report Repository” path is that when I configured the second BI Publisher server to use this new shared path all of the config I’d done on the first server was applied to the second. I wonder if this is how it’s supposed to work, or there’s going to be server-specific config written to a shared location which will cause problems?

With hindsight, and if the config can be shared like this, then setting up the shared file system first would have been best, and then I’d have only had to configure the one server and the second would have picked it up (for Scheduler changes etc).

Scheduler
I installed the Scheduler schema successfully, and ticked the Enable Clustering under Scheduler Properties. Doing some poking around (google for “Enable Clustering” “Scheduler Properties”) I found this page which documents Quartz (used for scheduling in BI Publisher, some more info here). It states

Enable clustering by setting the “org.quartz.jobStore.isClustered” property to “true”. Each instance in the cluster should use the same copy of the quartz.properties file.

The last sentence of this is reassuring as it describes what I’ve now got with the shared Report Repository folder. Checking data/shared/xmlp/Admin/Scheduler/quartz-config.properties shows that it now includes:

org.quartz.jobStore.isClustered=true

Finding config files in unix

Following my previous work on configuring Publisher, I wanted to note down where the changes were written to.

The -mname syntax of the unix find command comes in handy here:

find /app/oracle/product/obiee -mtime -1

Shows me all files under the specified path which were modified in the last 1 day

and helpfully throws up:

/app/oracle/product/obiee/xmlp/XMLP/Admin/DataSource/datasources.xml

OBIEE Publisher – configuring connection to clustered BI Server

I’m setting up a clustered OBIEE 10.1.3.4 production environment. There are four servers; two BI Server + Cluster Controller + Scheduler and two OAS + Presentation Services + Publisher. Clustering of BI is configured, now I’m setting up the other bits. Today is Publisher.

On publisher instance A connections to the BI Servers directly work fine:
jdbc:oraclebi://serverA.fqdn.company.net:9703/ jdbc:oraclebi://serverB.fqdn.company.net:9703/
both work individually as Connection Strings (with database driver class of oracle.bi.jdbc.AnaJdbcDriver) – verified with “Test Connection” button.
Connections also work when specifying the hostname only (i.e. no FQDN).

In Oracle Business Intelligence Enterprise Edition Deployment Guide p.40 the connection string to use for a cluster is specified:
jdbc:oraclebi://:9706/PrimaryCCS= Cluster Controller Host>;PrimaryCCSPort=9706;SecondaryCCS= Controller Host>;SecondaryCCSPort=9706
This doesn’t work straight out of the box. Both attempts fail with Could not establish connection.
1 – documented suggestion :
jdbc:oraclebi://serverA:9706/PrimaryCCS=serverA;PrimaryCCSPort=9706;SecondaryCCS=serverB;SecondaryCCSPort=9706

2 – adding FQDN to the first instance of the cluster controller host had been suggested by a doc I read :
jdbc:oraclebi://serverA.fqdn.company.net:9706/PrimaryCCS=serverA;PrimaryCCSPort=9706;SecondaryCCS=serverB;SecondaryCCSPort=9706

3 – add FQDN to all hostnames just for good measure : jdbc:oraclebi://serverA.fqdn.company.net:9706/PrimaryCCS=serverA.fqdn.company.net;PrimaryCCSPort=9706;SecondaryCCS=serverB.fqdn.company.net;SecondaryCCSPort=9706

Thought – we’ve proved that BI Server is up and running by specifying them as direct connections above, but we’ve not proved that the Cluster Controller is running. Logging into BI Administrator and using the Cluster Manager proves that all the components are up and running:

Since things weren’t working as expected, I went looking for some log files.
It’s useful to remember that all J2EE/OAS logs for xmlpserver, analytics, etc can be viewed easily through Enterprise Manager. Log in to EM (in my case it’s at http://serverC:7777/em) and then navigate to OC4J home (under the ‘Members’ section) and then click ‘Logs’ in the top right of the page.
In this instance I found the xmlpserver logs under Components – OC4J – home:1 – Application xmlpserver
NB this also gives you the file path to the log if you prefer not to use the web interface each time: [OAS home]/j2ee/home/application-deployments/xmlpserver/home_default_group_1/application.log

There was nothing in the log since startup, so no smoking guns there.

Back to google for a look to see if there’s more information on the syntax for the JDBC connection. Searching for jdbc:oraclebi PrimaryCCS threw up the Oracle Business Intelligence Publisher Administrator’s and Developer’s Guide
From this the connection string can be clearly explained:

<URL>:= <Prefix>: [//<Host>:<Port>/][<Property Name>=<Property Value>;]*

where

<Prefix>: is the string jdbc:oraclebi

<Host>: is the hostname of the analytics server. It can be an IP Address or hostname. The default is localhost.

<Port> is he port number that the server is listening on. The default is 9703.
[…]

<PrimaryCCS> -(For clustered configurations) specifies the primary CCS machine name instead of using the “host” to connect. If this property is specified, the “host” property value is ignored. The jdbc driver will try to connect to the CCS to obtain the load-balanced machine. Default is localhost.

From the syntax in the doco I added LogLevel and LogFilePath to jdbc connection string, but didn’t get any logs produced.
Changed the Publisher logging level to Debug (Admin>System Maintenance>Server Configuration) and through OAS restarted xmlpublisher. Tested clustered connection string again but got no more detailed log. Changed logging level back to Exception.

Resorted to searching Metalink and Metalink 3 (because one support system would be too obvious). Hit straight away in Metalink 3 doc ID 559795.1 “BI Publisher does not accept cluster jdbc connection strings” – a semi colon is missing from the end of the statement!

This now works fine:
jdbc:oraclebi://serverA:9706/PrimaryCCS=serverA;PrimaryCCSPort=9706;SecondaryCCS=serverB;SecondaryCCSPort=9706;
For reference, this also works fine:
jdbc:oraclebi://badgerbadgerbadger:9706/PrimaryCCS=serverA;PrimaryCCSPort=9706;SecondaryCCS=serverB;SecondaryCCSPort=9706;
(i.e. the first hostname is ignored, as stated in the documentation)

This documentation error is listed as bug 7499504

Moral of the lessons is – check Metalink for bugs first!