General
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Restore Until Cancel - continue

Tor-Arne Nostdal
Trusted Contributor

Oracle Restore Until Cancel - continue

Hi,
First my scenario:
Next week I need to have duplicated my DB fast. Instead of stopping my DB - making an Offline backup - then restore this, we plan to run a continous restore to a second copy of the database.
So we take an Online backup and start applying redo-logs.
Plan is to continue apply redo-logs as long as needed...

Question:
*********
If I run restore of redo-logs "Until cancel"
I suppose this would be up to the point where I copied the control file (or what) ???
Then I would like to continue applying even more redo logs ...
Is this possible ?
Do I need to get newer copy of the control files as well ?
Could I then continue as long as I haven't started the database ?
I'm trying to become President of the state I'm in...
7 REPLIES
Steven E. Protter
Exalted Contributor

Re: Oracle Restore Until Cancel - continue

I wouldn't do that.

I'd want a cold backup prior to doing this process.

You can get a good cold backup using OnlineJFS snapshots disk to disk in about 60 seconds, if you have OnlineJFS. Otherwise I'd shutdown, copy all relavent files to disk before doing this work.

Your process seems like it might work, it probably will, but it make me nervous.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Tor-Arne Nostdal
Trusted Contributor

Re: Oracle Restore Until Cancel - continue

No problem SEP.
I will not put my head on the bench ;-)
This is just to speed up the creation of the second copy.

Next friday I will stop Prod.DB and make an offl. backup. This offl.backup will be my "failover scenario" - in creation the second DB-copy

If I need to restore the offl.backup I will loose **many** hours.

So while the production DB run as normal - and creating new redo logs - I copy these to the second system - and want to continously restore them into this new DB.

If I succed - the copy will be finalized almost at the same time as I shutdown the prod.system to make the offline backup...
I'm trying to become President of the state I'm in...
Volker Borowski
Honored Contributor

Re: Oracle Restore Until Cancel - continue

Hello,

"I suppose this would be up to the point where I copied the control file (or what) ???"
-> No, you create/copy the controlfile right at the beginning.

On source do
- create a backup controlfile, or in case you like to rename the db to another SID, to a backup controlfile to trace to get a CREATE CONTROLFILE Statement.
- Set all datafiles to backup mode
- Transfer the files
- Startup nomount the target DB.
- Put the controlfile in place, either by using the binary copy or creating a new one if SID-change is wanted.
- If CREATEed controlfile, DB is alread mounted. If COPYed controlfile, mount DB
- Do "recover database using backup contolfile until cancel".
- cancel at convenient time
- alter database open resetlogs

DO NOT touch the controlfile again, your "backup controlfile" will become your current one on target db.

- If temporary tablespaces are in use, recreate tempfiles.

Good luck,
Volker
Tor-Arne Nostdal
Trusted Contributor

Re: Oracle Restore Until Cancel - continue

Hi Volker,
I have already started testing. I follow "standard procedure" as you've described. This we have done before. What we never have tried before is:
- continue restoring redo-logs, after the point in time where control file was created.

So far it seems fine, I have been able to bypass this point in time.
I ran into a problem where a datafile was added.
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 401: '/oracle/AAA/sapdata25/btabd_48/btabd.data48'

This was solved with an:
SQL> alter database
2 create datafile '/oracle/BBB/920_64/dbs/UNNAMED00401'
3 as '/oracle/BBB/sapdata25/btabd_48/btabd.data48';

Then I could continue with restore until cancel. (AAA=Original SID, BBB=New SID)
(Just make sure there is space available).

Once when I had to create a datafile. I could not start "until cancel", but had to use "until time". After I had processed up to this time specification, I could again start a continous run "until cancel".

Next possible obstacle I forsee is:
What will happen when I reach the point where a new backup is made (online/offline)?

If this doesn't give me problem I think I'm close to succeed.

/Tor-Arne
I'm trying to become President of the state I'm in...
Volker Borowski
Honored Contributor

Re: Oracle Restore Until Cancel - continue

Hey,
a SAP database :-)

Well the problem is, that you created new datafiles in source while the recovery already was in process. As far as I see, you have managed the workaround to create the file on the fly - well done.

A new backup on the source db will not affect you at all. Just make sure, that you do not cancel the recovery while the tablespaces are still in backup-mode.
Be sure to finish the backup (check brbackup logfile on the log seq# at the end of the backup) and include at least this seq#.

What will really hit you while you are in recovery is
- change the compatible parameter on source
- apply a patchset or bugfixes at source
- loose a logfile

Check the source for possible V$TEMPFILE entries.

Procedure sounds positive to me.
Volker
Tor-Arne Nostdal
Trusted Contributor

Re: Oracle Restore Until Cancel - continue

Hi again Volker.
You got some extra points also since I tought I was "cheap" last time. You explained how the "backup control file" was used - and I didnt know exactly. I understand it now and see why/how I was able to "restore into the future"...
I started with an online backup from last thursday and restored this into a new SID. Then I have applied redo-logs on the database which I have copied from the source system as soon as they have been created.
I now have a second database which is a couple of hours behind the source.

We have been doing system copy before, but then normally from an Offl.Backup, or from an online with a few redo-logs (and never when there have been runned tablespace extending on the source after online backup).

We do System Copy like this when creating test/project systems and have good procedures for renaming the systems and changing all interfaces so that we dont interfere with the actual production db.

This time the copy will be used to split out a part of our company. We will change the copy into a separate (intermediate) production system. Since it is going to be a producttive system we must be able to run the new system creation, name conversion, adaption, scrambling etc. during a weekend.

Therefore we stressed a faster way of creating the system and still have the most recent data available.

This exercise have learnt us a lot and we also see other possibilities by using same kind of approach.

Example: discussing standby/failover DB which is ~4 hours behind and could be restored very fast).
Purposes like:
- someone run a systemjob who delete vital info. They dont want to restore the whole system, but startup a copy and extract data for transfer back.
- faster creation of test/project systems (refreshment)

Time is money - Disk is cheap

Thanks for contributions

/Tor-Arne
I'm trying to become President of the state I'm in...
Tor-Arne Nostdal
Trusted Contributor

Re: Oracle Restore Until Cancel - continue

The procedure described above have been fine tuned and used several yrs. now in our organisation.

Oracle and HP-UX is just great!!!

First weekend of December this year, we moved 6 production SAP systems in one go from Denmark to Belgium during one weekend. The largest system has a db > 1Tb and produces quite an amount of redologs.

How we did it:
- initial setup of oracle+sap in BE
- deleted db in BE
- restore of online consistent backup (brought on tape from DK to BE)
- continous copy process of redologs from DK to BE (with sum check)
- continous applying redologs until cancel
- shutdown in DK
- applying last redologs
- resetlogs and startup

This is the very short version, for making a system copy.

Our project was more complex, as we also changed hostnames, changed IP-addresses, outsorced floorspace.

This required extra precautions, especially related to SAP profiles and interfaces.

We also went from PA Risc/Itanium heterogenous environment (IA64 only on app.servers) to Itanium Homogenous environment, from separate db-hosts to several db's on same host, using PRM and much more.

/Tor-Arne
I'm trying to become President of the state I'm in...