Backups using storage system mirroring/splitting

Hi there,

This message is sort-of a continuation of a thread from summer 2003, from which the most important message was <WdhKa.1167$p8.43445_at_newsfeeds.bigpond.com>, retrievable with Google using http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=WdhKa.1167%24p8.43445%40newsfeeds.bigpond.com&rnum=6

I'm developing a backup-strategy for a large database. I'm not an Oracle-guru, so this means I'm reading lots of documentation and doing lots of experimenting. I bumped into something I don't understand, and the reading and experimenting doesn't help, I'm afraid.

System A is attached to an EMC Symmetrix storage array, and runs EMC PowerPath. This combination makes it possible to *atomically* split off a mirror of several volumes at exactly the same time. As far as the split-off copy is concerned, the splitting is indistinguishable from the host crashing.

On system A, a database named prod1 runs (in archivelog mode), using raw logical volumes.
On system B, the same version of Oracle is installed (9.2.0.4). The configuration files of the prod1 database are also present on this machine.

With a running prod1 database on system A, I issue the atomic split command. I don't issue any commands to the database; no "begin/end backup", no "archive log current", nothing. Just the split with a running database. The split command atomically splits off a mirror of *all* database logical volumes: the control files, the online redologs, the datafiles, etc. etc.

The split-off copy is then presented to system B. On system B I can issue a "startup" command without problems. Obviously, the split-off copy is inconsistent, but normal crash recovery takes care of this. A dbverify of each datafile reports no problems after a clean shutdown of prod1 on system B.

A slight change in the above procedure was the subject of the thread mentioned above:

1 On system A, prod1 database runs in archivelog mode. 2 On system A, an "alter database backup controlfile to '/oradb/control.bak'"   command is issued, after which a "sync ; sync ; sleep 2" command is issued. 3 On system A, the entire database is atomically split off, as described   above.
4 On system B, the database is mounted, but now using the   /oradb/control.bak controlfile.
5 On system B, I now issue a "recover database using backup controlfile until   cancel", and I apply the appropriate online redo log. This successfully   recovers the database, and I can open it with RESETLOGS.

This works, and the only question I have about this part is why the recover database command stops with the message "media recovery complete" after applying the appropriate online redolog file. I did say "until cancel" after all?

SQL> recover database using backup controlfile until cancel; ORA-00279: change 1938503093 generated at 01/28/2004 13:10:28 needed for thread
1
ORA-00289: suggestion : /oracle/app/oracle/admin/prod1/arch/prod1_5005.rdo ORA-00280: change 1938503093 for thread 1 is in sequence #5005

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dev/r8index3_100_02

ORA-00279: change 1938609204 generated at 01/28/2004 14:04:47 needed for thread
1

ORA-00289: suggestion : /oracle/app/oracle/admin/prod1/arch/prod1_5006.rdo
ORA-00280: change 1938609204 for thread 1 is in sequence #5006
ORA-00278: log file '/dev/r8index3_100_02' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dev/r8redo1_100_01

Log applied.
Media recovery complete.

And now the problem that has me puzzled. In the above scenario, I successfully recovered the split-off database by applying online redologs. The original database kept on running, on system A. This means, that sequences #5005 and #5006 would eventually be archived on system A. So, I think, the following sequence should work:

1 Perform the steps 1,2,3 and 4 exactly as shown above. 2 Wait until prod1 on system A generates the needed archived logfiles. 3 On system B, again issue "recover database using backup controlfile until   cancel", but now, instead of using the online redologs, use the archived   logs from step 2.

However, this doesn't work. I can apply the files, but it won't say "media recovery complete". Also, if I cancel the recovery and try to open the database (with resetlogs), it says that datafile #1 needs more recovery.

I don't understand why applying the online redologs from system B *does* work, but applying the archived logs does *not* work.

Thanks,

-- 
Jurjen Oskam

PGP Key available at http://www.stupendous.org/