We have a Database A (Central DB) and Database B (Backup DB). Users connect to DB A for normal day to day transactions. Say users connect to DB A from locations IND, LON, AUS, USA. Now say the link from IND to DB A is down for 1Hr. So we move the users and make them connect to DB B and users from IND start working on DB B. Now the problem. After the link is up the changes that was made to DB B should be sent to DB A. We are planning to use Streams for this. The source in this case will be DB B and DB A the target. What i want to know is that is the following possible and how.
(A) Backup to Central Database
- No DDL operations will be performed in backup, but those could be performed in Central Database (but should be avoided)
1. On some particular event, system/database should mark a TAG(logical), so that after that moment of time all the transactions (DML) of the selected set of tables should record and transfered to Staging Area
In this there could be few cases, where some filtering on Databass ROWs is also required (but not a critical requirement)
2. System should have a capability to records/stage these messages in time sequenece. Also system should have a capability to use the added messages on other end (Central Database) for applying those (both in parallel or insequence).
3. APPLY Process: Should have capability to reterive the queued messages at the central system. Few of the transactions required use the DEFAULT apply process (say for master or audit tables) and few transaction should have capability to invoke the USER DFEINED procedure
- Can staging area could also be a FILE or some other messaging product for transfer purpose (say, IBM MESSAGE-Q) ?
- Can we set some session or environment variable for the APPLY process e.g. Set SYSTEM DATETIME as a hard coded value, or set USER to something specific or PORT = , as those variables might be required (??)
- Check how APPLY process call user defined methods and how it passess all the records (may be from more than one table) to that procedure
- How user defined process identify the Transaction, as the data available to this process is nothing but a set of records from various tables and a transaction ID, which binds them together ?
(B)Central Database to Backup
- Some process is required to replicate the data from CENTRAL to BACKUP Database on regular basis, so that BACKUP Database gas alomst all the required. In this case there will be some scenarios where for some tables, NO data is replicated and for some only the filtered data is replicated.