Genesys on an Oracle DB Cluster

[ed]

Does someone have any experience with the genesys DB on a cluster?
If the oracle cluster switches, we have to restart the dbserver.

thanks ed

[Vic]

? why in the world do you have to do that?

What version of DBServer on what OS are you using? The least you can do is use failover protocol built into ORACLE to switch over to the backup DB like this:

Failover Configuration in Tnsnames.ora on Net8 Client

PROD.WORLD =
(DESCRIPTION_LIST =
(FAILOVER = TRUE)
(LOAD_BALANCE = FALSE)
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = arkum)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = ARK1)
(SERVER = DEDICATED)
)
)
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = diamond)(PORT = 1523))
(CONNECT_DATA =
(SERVICE_NAME = DIA3)
(SERVER = DEDICATED)
)
)
)

This would allow your DB Server to connect to backup DB when it attempts to connect to primary DB. Needless to say, if your DB goes down while it is running, it will drop a connection, and if there is no backup defined for this particular dbserver, next time it attempt to reconnect it will smoothly connect to your backup without Genesys even knowing it.

It is not really pretty but it works

[Gui75]

Hi

And how would you do to configure JDBC connection used by ETL to access ODS database?

[borkokrz]

[quote author=Gui75 link=topic=552.msg16082#msg16082 date=1232465013]
Hi

And how would you do to configure JDBC connection used by ETL to access ODS database?
[/quote]

that way:

jdbcurl=jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST={host1})(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST={host2})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DATAMART)))

but if you are using Brio Broadcast Server or Hyperion Performance Suite this won't help

[Gui75]

Hi,

Thanks borkokrz,
Can you also tell me how to handle this connection from Brio Broadcast server?

Regards,

[borkokrz]

[quote author=Gui75 link=topic=552.msg16544#msg16544 date=1234179947]
Can you also tell me how to handle this connection from Brio Broadcast server?
Regards,
[/quote]

Hi,

For BCS 6.6 I'm not sure because I wasn't able to test it. AFAIK BCS 6.6MR3 is working fine on Windows 2000 with Oracle 8i (so without RAC functions).I think it should also work with Oracle Client 9i (with RAC) on W2K platform - RAC load-ballance and failover will be handled by Oracle Client. First time when I've played with Oracle RAC W2K was already out of official support and I've tried to run BCS on W2003 and I'm sure that BCS 6.6MR3 (6.6.3.15) is not working on the Windows 2003 platform - every scheduled job finish with error. I've found some post over the internet that version 6.6.4.50 should work but I wasn't able to download this version because BCS was also then already out of official support.
Now I've administer installation of Hyperion Performance Suite 8.3 running on W2003 with connection to Oracle 9.2 with RAC. To be able to handle RAC you have to provide:
- correct tnsnames in Oracle client version 9.2.0.8 or higher
- correct jdbc url in Name Server section of HPU ConfigFile utility for example:
jdbc:brio:oracle : / / host1:1521;alternateServers=(host2:1521);serviceName=datamart (remove spaces)
- update v8_jdbc table - set JDBC_URL in all rows as shown above.

Best Regards.

[CTIgem]

Hi,

You do this in etl properties?

jdbcurl=jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST={host1})(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST={host2})(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DATAMART)))

[CTIgem]

How can you make ETL to read its configuration options from etl.properties file instead of ETL DAP?
Do I need to uncomment ETL_Proxy lines?

Thanks.

[CTIgem]

I'm getting below error after modifying etl.properties file.
Any ideas, please?
Thanks

ETL_Trans_Only*:* 10:53:00.839 Main.Options Options: {CfgAppName=ETL_Proxy, dropTransferredTables=, jdbcurl=jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=off)(FAILOVER=on)(ADDRESS_LIST=(ADDRESS=(protocol=tcp)(host=serverA)(port=1521))(ADDRESS=(protocol=tcp)(host=serverB)(port=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=SIDNAME))), user=xx, CfgUserPassword=xxxxx, CfgUser=default, pass=*****, CfgAppPassword=xxxx}
ETL_Trans_Only*:* 10:53:00.855 Main TRANSFORM started
ETL_Trans_Only*:* 10:53:24.294 Main Unhandled exception happened. Exiting ...
ETL_Trans_Only*:* Stack Trace:
ETL_Trans_Only*:* common.TransformError: Error during upgrade
ETL_Trans_Only*:* at ETL.UpgradeOfficer.approve(Unknown Source)
ETL_Trans_Only*:* at ETL.Transformer.start(Unknown Source)
ETL_Trans_Only*:* at ETL.Transformer.main(Unknown Source)
ETL_Trans_Only*:* Caused by: java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
ETL_Trans_Only*:* at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
ETL_Trans_Only*:* at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
ETL_Trans_Only*:* at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)
ETL_Trans_Only*:* at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)
ETL_Trans_Only*:* at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
ETL_Trans_Only*:* at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
ETL_Trans_Only*:* at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
ETL_Trans_Only*:* at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
ETL_Trans_Only*:* at java.sql.DriverManager.getConnection(Unknown Source)
ETL_Trans_Only*:* at java.sql.DriverManager.getConnection(Unknown Source)
ETL_Trans_Only*:* at DataMart.ConnectObject.createConnection(Unknown Source)
ETL_Trans_Only*:* at DataMart.ConnectObject.createConnection(Unknown Source)
ETL_Trans_Only*:* ... 3 more
ETL_Trans_Only*:* Caused by:
ETL_Trans_Only*:* SQLState: null
ETL_Trans_Only*:* Vendor:  17002

[catanirex]

Hi,

ETL Service/Runtime does not support Oracle loadbalance/cluster... Been there-tried it :-(

You need to use the normal Oracle jdbc syntax, and point to one of the oracle SIDs

[CTIgem]

Thanks!

[borkokrz]

Not Not true. It is working fine in my 4 CCA installations since 3 years for now. ETL version 7.1.000.14. All what Genesys requires is the transparent and stable connection on JDBC layer. Consult with your OracleDBA on that. Just specify correct jdbc-url string in etl.properties and Options\jdbc section of DAP connected to ETL.
Look at attached logs.

ETL_Trans_Only*:* 10:12:01.544 Main.Options            Options: {deadlockThreshold=3600, setFi
rstDayOfWeek=2, data-flow-alarm=, updateStatsForLevel0Views=, dropTransferredTables=, CfgUserPa
sswordEncrypted=CDF4EC815DB4C7D4, data-flow-timeout=20, communication-alarm=, dba_user=genesys,
dba_pass=*****, passEncrypted=7D972C50D79810BF, data-flow-check-interval=5, CfgAppName=ETL_Pro
xy, jdbcurl=jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=ON)(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP
)(HOST=host1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))(CONNECT_DATA=(SERVICE_
NAME=DATAMART))), chunksAtOnce=16, CfgUser=default, loglevel=WAKE:3,START_WRITE_MON:3,DROP:9,WR
ITE:3,INFO:9,PURGE:9,WARNING:8,ERROR:9,_ALL_:0, updateStatsForTablesAndIndexes=, user=genesys}
ETL_Trans_Only*:* 10:12:01.696 Main.Options            Checksums: 5052d804, 433f63fc
ETL_Trans_Only*:* 10:12:01.696 Main                    TRANSFORM started
ETL_Trans_Only*:* 10:12:04.893 Write.Heartbeat          Pulse: 2009/03/20, CET, 3600, 4.1M
ETL_Trans_Only*:* 10:12:06.770 LFolder                  views OK in LayoutFolder[l:IDOO_1,f:1]
ETL_Trans_Only*:* 10:12:06.881 LFolder                  views OK in LayoutFolder[l:IDOO_2,f:2]
ETL_Trans_Only*:* 10:12:06.972 LFolder                  views OK in LayoutFolder[l:IDOO_4,f:4]
ETL_Trans_Only*:* 10:12:07.061 LFolder                  views OK in LayoutFolder[l:IDOO_3,f:3]
ETL_Trans_Only*:* 10:12:07.156 LFolder                  views OK in LayoutFolder[l:IDOO_21,f:22

[CTIgem]

I see that it does work with RAC. This is good to know.
Will have to talk to DBA.

thanks.

[eugene]

Guys, this works fine for ETL Runtime, but about during the configuration of ETL Assistant to point ODS to ETL?  Would I need to get the DBA to shut down one of the node in order to connect properly?  Right now i keep getting a ORA-12505 upon trying to login into ETL Assitant yet i can tnsping the connection description with no issue?  Anyone aware of a workaround?

Eugene

[borkokrz]

In tnsnames I have 3 entries, one with load-balancing and failover to SERVICE_NAME=datamart on 2 hosts, one direct to SID=datamart1 on host1 and third one also direct to SID=datamart2 on host2. In ETL Assistant I always use direct connections. Oracle RAC mechanism will do the job with replicatation everything you do on one of the cluster hosts.