| REDO Logs Applied Remotely [message #237171] |
Sat, 12 May 2007 18:36  |
802519083
Messages: 3
Registered: May 2007
|
Junior Member |
|
|
I really need some experienced opinion on something that is being proposed for my current project. I would appreciate any help that can be given.
Background:
I work for a company that is about to configure a managed service for two clients. Client A owns the data, client B will be running the managed service, i.e. a call centre, for them.
Unfortunately, client A already has another firm (client C) running a form of managed service for them and that contract is about to end.
So Client A = Owner
Client B = New Managed Service
Client C = Current Managed Service
Client C stores the data on an Oracle (9.2) database, Client B is going to store the data on a SQL Server with a CRM application schema (please don't be put off by this bit!)  .
Solution: In simple terms, we will be lifting the data from the Oracle database, using PL/SQL to perform some cleansing and transformation to get it to fit the new schema. So far so good.
Unfortunately, there is going to be a phased approach to this where Client C will continue to work on the data for two weeks after we have taken the first cut of some of the data (contact name and address data mainly) to make it visible Client B on their database.
The problem is that the data stored by both Client B and Client C, and the networks that they reside on are highly secure and their security boffins say that they cannot allow each other access across their own firewalls. Nor will client C allow the extract jobs to run on their production database.
(client C and client B are not quite at war with each other but they may as well be!  ) Client A did not write any exit requirements into the contract for client C so everything they give up is with reluctance.
Luckily Client B does have a licence to own an Oracle database so we intend to do a cold physical back up of Client C's database and physically bring the tape to Client B's site and re-create Client C's oracle database behind Client B's firewall and perform the extract of the contact data there.
For the following two weeks we want to take copies of the archived logs at Client C's site and move them physically again to Client B's site and apply them to keep the databases in synch (in effect a physical standby even though the primary database will have no reference/knowledge of this second database)
For the two week period we intend to use DBMS_LOGMNR to find changes to the contact data so that we can extract just those changes and update Client B's SQL Server database.
At the end of the two week period, a final log will be taken, couriered to Client B where the logminer will allow us to extract final updates to the contact data before we apply the final log to the database and run the PL/SQL procedures that will extract all the remaining data tables relating to the contacts (i.e assets, payments, orders etc)
Thank you for reading so far, now for the opinion I need!
Assuming that the server that we create the physical standby copy on is identical to the primary database, will there be a facility to apply those archived log files to the standby DB and to use logminer even though there is neither a logical or physical connection between the two? All we will have is the log files on transportable media. I guess if this was all behind one firewall it would be fairly straightforward.
I am not a DBA, I'm just a developer who has read about this a little, but if anyone can confirm that our approach will work it will be of great use.
I know it's not ideal and if we could get access across the firewalls we would do. We have to fit a solution around the constraints we face.
Can it work?
Your help appreciated guys.
Chloe
[Updated on: Sat, 12 May 2007 20:18] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: REDO Logs Applied Remotely on an alternative site [message #237239 is a reply to message #237176] |
Sun, 13 May 2007 11:59  |
802519083
Messages: 3
Registered: May 2007
|
Junior Member |
|
|
Imagine this scenario.
Company A purchase company B with the intention that they will rationalise their IT estate within 12 months.
Company A take a full back up of their Oracle database every 14 days and their log files are archived off, not just into operating system files, but to removable media.
Just before the latest backup is taken, the data centre (center) of Company A suffers fire damage (or explosion damage) sufficient that the server that the Oracle database runs on is rendered completely unrecoverable. The standby databases in the same data centre are also destroyed.
All that is left is a fireproof safe that contains the last backup from 14 days ago and a number of DVDs that contain the archived log files.
The management are told that the data centre won't be up and running again for at least a fortnight.
One of them says, "Hey, couldn't we use the data centre of Company B that we've just bought?"
A DBA is called in and asked if it is possible to build a server in the new data centre and to load the back up followed by all the log files.
Wouldn't you expect that this could be met by Oracle's recovery processes?
This in effect is the scenario that we want to employ - rebuild a copy of the original database and apply all its logfiles on a remote site.
Thoughts?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
