Home » RDBMS Server » Server Administration » Maintain Logical Transaction Integrity in a High Availability ODS
Maintain Logical Transaction Integrity in a High Availability ODS [message #248953] Mon, 02 July 2007 18:35 Go to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
We're beginning the design of an Operational Data Store using DataStage to load data from various source systems into a 10gR2 database, with a Business Object reporting interface. The ODS requires high availability, so we will be performing ETL during the working day whilst users are running reports.

We can't work out how we can gurantee logical transaction integrity.

If a transaction in the source system inserts rows into two or more tables as part of a logical transaction then we want to make sure that all of those rows become visible in the ODS at the same time.

Datastage works best in a single table-to-table (or file-to-table) transformation. So if you have a separate job for each table, logical transaction integrity will not be preserved. ie. Rows loaded into the source system in 1 transaction are loaded into the ODS in 2 or more transactions - one for each table.

Does anyone have any creative solutions to this? Ideas that have occurred to me:
  • DBMS_FLASHBACK package to freeze users view of data whilst the ETL is active. Problem is that I cannot work out how to get Business Objects to issue the procedure calls; it just runs queries - not procedures.
  • Standby database of some type. Load one database whilst users query another. Unsure as to how to go about migrating sessions between databases without killing them. Unsure whether this is even possible.
  • Create Datastage jobs that transform many tables at once. I am concerned that this solution is not extensible, and that I'll end up with one job transforming dozens of tables.
  • Obtain iterfaces from the source systems that preserve transactional integrity by including many record types in a single file. This would make for insanely comlicated DataStage jobs - DS is not especially good at different record types in data files.

None of these sound viable to me (except possibly standby database if there is a neat way of switching databases). Can anyone think of any other Oracle-based solutions?

Ross Leishman
Re: Maintain Logical Transaction Integrity in a High Availability ODS [message #249215 is a reply to message #248953] Tue, 03 July 2007 11:40 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
WOULD YOU USE THIS FOR REPORTING ?
Re: Maintain Logical Transaction Integrity in a High Availability ODS [message #249276 is a reply to message #249215] Tue, 03 July 2007 21:22 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Not sure I understand your question....

The system is used for reporting. The problem is that we want the reports to have a consistent view of the data whilst the ETL is running.

I may have selected the wrong forum to post this in, but I this one would be best bet to find out what sort of 2-database approach could be applied. I will move it to Server Admin tomorrow if I still get no interest.

Ross Leishman

[RL: Used my moderator privs for evil and moved this to Server Admin to reach a broader audience]

[Updated on: Wed, 04 July 2007 21:57]

Report message to a moderator

Re: Maintain Logical Transaction Integrity in a High Availability ODS [message #249759 is a reply to message #249276] Thu, 05 July 2007 15:11 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Mostly for reporting LOGICAL STANDBY DATABASE USED.
Re: Maintain Logical Transaction Integrity in a High Availability ODS [message #249823 is a reply to message #249759] Fri, 06 July 2007 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Standby database, Advanced Repication, Streams, etc. are based upon DATABASE transactions and not LOGICAL (or application) ones.
I don't know any Oracle tool that can handle this case.
I think only application knows what is logical transaction and so only application team can develop a module to handle logical transaction replication.

Regards
Michel
Re: Maintain Logical Transaction Integrity in a High Availability ODS [message #250754 is a reply to message #249823] Wed, 11 July 2007 03:24 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The data extract from the source system will use Oracle CDC, so we can guarantee that the source files will retain transaction integrity (ie. if the app committed transactions in 2 or more files, either all transactions will be present in a set of extract files or none of them will.

So now we have a set of (say) 10 files that have logical transaction integrity. The problem is:
How to load those 10 files in a single transaction?

Clearly I can do this with EOTs and SQL, but the ETL will be written in DataStage, not SQL; so that's no help.

Say we use one database for ETL, and a Logical Standby Database for reporting. We could load the data file-by-file using DataStage into the primary database, but then the refresh of the Logical Standby Database would also be file by file because DataStage performed a commit after each file.

Logical Standby Database would only be a solution if we could re-synch ALL changes since the last synch in a single commit.

Ross Leishman
Previous Topic: 32 bit oracle
Next Topic: How ro migrate scheduled jobs ?
Goto Forum:
  


Current Time: Wed Dec 07 22:26:24 CST 2016

Total time taken to generate the page: 0.09471 seconds