Home » RDBMS Server » Backup & Recovery » REDO Logs Applied Remotely
REDO Logs Applied Remotely [message #237171] Sat, 12 May 2007 18:36 Go to next message
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!) Razz .

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! Laughing) 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 [message #237173 is a reply to message #237171] Sat, 12 May 2007 18:49 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>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
As a general rule, you can not do what you state above.
If when you restore from the cold back, Oracle will just cleanly open and you won't be given the opportunity to apply the redo log files.
I am fairly certain you might be able to get creative & "trick" Oracle, but IMO that is one VERY risky operation.
At the very least you should practice, practice, practice before ever doing it for real.
BTW, in round numbers how big is this DB?
Re: REDO Logs Applied Remotely [message #237175 is a reply to message #237173] Sat, 12 May 2007 20:13 Go to previous messageGo to next message
802519083
Messages: 3
Registered: May 2007
Junior Member
Hoping to get some ideas of the size in the next week. You might think we'd need to know this to size the new database. We know how many data records we'll need, but there is such an overhead in the application that sits above Oracle (i.e complete copies of table rows made to show changes made for audit purposes), the size of current service bears near enough no relation to new database size.

We've just been throwing ideas around up to now as to what might be possible, but the discussions start for real with current service DBA's in this coming week. As of yet, our project hasn't had access to an Oracle DBA on the new service team (Client B). The current service (Client C) DBAs may be very knowledgeable but I am worried that if they can avoid doing too much they might just say that everything we suggest is not possible. That's why I wanted unbiased opinions from people on this site.

I suppose we could negotiate that we take the backup initially at Client C and bring that across the two firewalls in some media to create the DB instance locally on Client B's site to do our initial work on (that won't affect their production machine - depending how long it takes to do the backup I suppose!).

We could then run the log mining queries and the updated extracts on their production machine through the following two week period before we get hold of another back up at the end of that time.

We can physically move text files output from these PL/SQL jobs if necessary instead of the log files.

Quote:
If when you restore from the cold back, Oracle will just cleanly open and you won't be given the opportunity to apply the redo log files.


The plan was that the log files would come over each day for the next two weeks - not at the same time as the back up. I guess that the archive log files coming separately is going to be more dificult still then Sad



Re: REDO Logs Applied Remotely [message #237176 is a reply to message #237171] Sat, 12 May 2007 20:55 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
What can be done is to take a hotback in preparation of creating a standby database.
With a standby database it can be opened READONLY for data transfer & periodically apply the redo logfiles.
Re: REDO Logs Applied Remotely on an alternative site [message #237239 is a reply to message #237176] Sun, 13 May 2007 11:59 Go to previous message
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?
Previous Topic: backup from 3 machine to 1 tape
Next Topic: Back Database
Goto Forum:
  


Current Time: Sun Dec 04 23:05:29 CST 2016

Total time taken to generate the page: 0.06834 seconds