DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Need advice on a particular setup.

  1. #1
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    Need advice on a particular setup.

    Hi,

    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

    Assumption:
    - No DDL operations will be performed in backup, but those could be performed in Central Database (but should be avoided)

    CAPTURE Process

    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


    Queries:
    - 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.
    How can we achieve this.

    Regards

    PS: It seems that not much of Streams fan out there, so i'm not expecting an answer to this one too. But i'm hoping that maybe some one out of the blue might have used it and give me some suggestion
    Last edited by adewri; 05-23-2003 at 05:06 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Amar

    I do not have experience on oracle streams..But what you are aksing for i Think its very much possible..

    Capture –
    Data is captured from the redo logs and packaged into Logical Change Records or LCRs.
    The LCRs are then moved into the Staging environment.
    Data and events may be changed or formatted by a predefined set of rules
    before they are packaged into an LCR.

    Staging –
    LCRs are stored in the Staging environment until a subscriber picks them up to be
    used or consumed. The subscriber may be another staging environment
    or a user application.

    Consumption – During Consumption, LCRs are picked up and applied to a database. Consumption allows the LCR to be modified before it is applied to the database

    These three points should answer all your questions..

    However you should try setting up a test environment and share the knowledge with us

    http://www.oracle-base.com/Articles/9i/Streams9i.asp
    http://www.dbasupport.com/oracle/ora...pture2_1.shtml

    regards
    Hrishy

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Hi Hrishy,

    Thanks for the input. I have read the manual once and have understood the concept partially and very well know what capturing, staging and applying is and am going to read it two more times during the weekend (There goes my weekend). The question is how ?

    How do i filter the LCR's before putting them in a queue. Ok i can use the rules for that. But the examples given in the manual is way too simple for the set up that i have in mind. Its like say a Booking Module user is updating three tables and a Port module updates only one of those three tables. I want to filter out only the changes made by Booking module and not by Port module.

    Lets take another example. Its very much possible that during that 1Hr down time the other users from USA were inserting or updating the Booking tables. Now when the link is restored and the changes are being pushed from DB B to DB A, its possible that the unique booking number already exists. So how do i do a dequeue that transforms these changes and create new booking number if it already exists or update the existing booking number values based on the which ever is latest.

    The problem here is how do i know what value is comming from the queue so that i can apply a procedure to achieve the above.

    These are few of the known problems that will come. But there may be many other unknown which i'll have to tackle when i begin the setup.

    Any ideas on this ?? I have done all the setups given in the manual. Those are way too simple. But there is no doc on the metalink and google on complex setups or on making complex transformation rules. Could not find any good document on the packages as well. Except for those in the PLSQL packages manuals.

    Thanks
    Last edited by adewri; 05-23-2003 at 05:55 AM.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    Cant you use multimaster replication on the different sites and when once site fails, a failover method to connect to the other remote site and use conflict resolution methods.

  5. #5
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    No i do not want to use multi master replication as the backup databases need not have all the data.

    Its just a backup db where user can make entries while the link is down. So it need not have all the data the central db is having. only some mandatory data... just for making entries...

    So the whole idea is not to keep the user waiting while the link is down... just give them a db to make the entries... and the push those changes, after customizing them to the central DB.

    Where as with replication you do not have control over the data. With streams you can change and manipulate the data.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Amar

    I do not have a 9i database with me..so was just wundering wheather you can simulate the scenario on a small database and let us know what problems you encountered and share it here :-)

    regards
    Hrishy

  7. #7
    Join Date
    May 2003
    Location
    Kathmandu, Nepal
    Posts
    16
    I think this would be an ideal case for single master-updateable snapshot replication setup. DB A would be a master site and DB B a updateable snapshot site. Whenever DB A is down DB B takes over and records all the transactions. When DB A is up refresh DB B against DB A which pushes all the changes be available to DB A. With this setup a dedicated network is not required. It also takes care of duplcate records.
    Raj Kishore
    Oracle DBA

  8. #8
    Join Date
    May 2001
    Posts
    736
    No this not his requirment.Still the Snapshot will have the Master data which he doesn't want.

  9. #9
    Join Date
    May 2003
    Location
    Kathmandu, Nepal
    Posts
    16
    I find that the most suitable solution for his requirement. The crucial part of his requirement is that the duplcates should be eliminated even when the main DB is down. I do not see any other way to handle duplcates but single master - updateable snapshot setup.
    Raj Kishore
    Oracle DBA

  10. #10
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by Raj Kishore
    I find that the most suitable solution for his requirement. The crucial part of his requirement is that the duplcates should be eliminated even when the main DB is down. I do not see any other way to handle duplcates but single master - updateable snapshot setup.
    I think you have not read the entire thread. The Main DB is not down, people from other locations are working on it and the records are not duplicate only the primary key is duplicate and the other column values are important.

    Streams gives you the control over the data by allowing you to manipulate the data thats being replicated using your own custom based procedure.

    And Hrishy i'm half half way through it. I'll post a document in How-To forums as soon as i make things running. Right now im able to split the rows coming from the Source database and insert them into two different tables (i.e. the primary key into one table and the other column values into other table). Need to work out other possibilities.

    Cheers!
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width