Very good comparison of both - but I have some question (I am not the original poster of the question btw).

1. How would your answer be different for a datawarehouse instance. Obviously, DG is not suitable there because most of the operations are not logged. How would the BCV mirroring affect the bulk load operations? What is the best backup strategy for a large warehouse (>10 TB), with rolling window partitioning? Is there any point in backing up even, because it would be easier to bulk load the data again if incase you face some issues.

2. Its not clear to me from your answer if BCVs and RMAN can be used together - meaning, can we take an RMAN backup on the split-mirrored side of the BCV and use it for recovering the database? This may need mounting the split mirrored array to a new instance in the DR site. Can such an RMAN backup be used to recover the Production site?

3. Can you clarify the first line of your answer again? What is it that you say RMAN could not do? OR did you mean to say it could do? Thanks.

Well, with all due respect, I think it is naive to suggest to turn on logging on large datawarehouses in the name of HA. There are many other options for HA, not DG alone. I think any serious DW person would pursue the option of BCVs + the ability to re-load the most recent missing data when necessary. I specifically wanted to ask you (or anyone who have done this) about any performance degradation during direc-loading with remote mirroring. I agree however that RMAN is a great option for non-disaster type of recoveries (corruptions, h/w failures etc).

I agree with making older data read-only. Thats exactly what we are planning to do. I have question - lets say I have 1000 tablespaces and 950 of them are read-only. I am little confused how will I organize my catalog with a complete backup set comprising of both read-only ts backups as well as read-write ts backups (Its rolling window, everyday I drop an old and add a new partition, also one or more tablespaces changes to read-only mode from read-write mode every day). My question is, how do I maintain my backups organized inside the rman catalog if I am taking the backups of the read-only and read-write tablespaces on different days. I think I will have to manually maintain the list of tapes which are needed for recovery. Don't know if you followed me - if you did, I would appreciate if you can show a small example how to to do this using the rman catalog.

Also, I was expecting to get some details about why RMAN cannot do a block level recoveries with the backup taken at the shadow image side of the BCVs. I read somewhere else too that it is not possible - can you please explain why? Are there any work-arounds?

Thanks again for your time!

Hi Tom

Just a thought with the 10g ASM and RMAN plus the flash back redo.I think oracle is trying to enter the arena of storage .This is increasingly observable when you use flash back database technology :-).Just a matter of time i think when oracle would be there.

Maybe who knows larry might even consider buying Redhat :-) so oracle would be there with lowest cost TCO and wipe out the competition totally :-)

would be nice if you have other thoughts on the ASM+RMAN+Flash Back Database combo :-)

Sorry to provoke you like that Tom, but I feel I did not get answer to my questions still.

I won't argue with you about logging - it is just out of question in my environment at least.

<Quote>
Direct path can generate log, the log can be shipped in near real time -- could be the way to go.
</Quote>

--Can you please explain what are you saying here?

<Quote>
It is the bookkeeper, that is its job...
</Quote>

I know about what RMAN does with the catalog - Obviously I could not get across to you what I was asking. Thats ok, I dont want to go over it again and get an answer back that rman is the keeper and thats it...


<Quote>
unless rman takes the backup, it cannot do block level recovery because it did not do the backup, something else did the physical backup ...
</Quote>

I was talking about the scenario where we take an RMAN backup of the split-mirrored data. I am talking about RMAN backup only, - Can you tell me WHY is it not possible.


I am not the original poster.. I just wanted to follow-up with some questions...

direct paths in an archivelog mode database generate redo.

---I thought direct path means bypass the redo? Isn't direct path means, bypassing the sql-layer and writng directly to the datablocks above hwm? How can it generate redo in that case? Can you explain the concept?

---About the RMAN question, let me try one last time to ask again what I am trying to ask...

Lets say we have 3 mirror copies for my data. Now I split one of the mirrors which is at my dr site. I then mount that singleton split disk array on a DIFFERENT (but similar, same name) instance running in the DR site. Then I take an RMAN backup of the data there. It is not same as just registering the files with RMAN. The question is, can I use that RMAN backup to do block level recovery on the Production site? If not can you please explain why?

Tom,

In one of the answers above you write:

<quote>
2) bcv's and rman can be used "together" but in different ways. if you use a
bcv split -- you'll just be using rman as a bookkeeper and not using any of its
features like block level recovery.... it'll just have big files and a catalog.
</quote>

We are using NetApps, and I use the NetApps snapshot functionality for the backups (along with a "normal" rman backup). After doing the snapshot (done with all the tablespaces in backup mode) I catalogue all the datafiles in the snapshot (catalog datafilecopy <snapshot datafile>)). The nice thing is, now I *can* use rman blockrecover! Wouldn't the same apply if I could catalog the BCV copy?

Just to show the snapshot recovery... I have a table called test in a tablespace called test02. I used xvi to edit datafile test02_01.dbf and mess up some data in there... Now
(pasted from my putty session, so please be patient..)

**********************************************
The catalogue script shows this in it's logfile:
.
catalog datafilecopy '/ora39base/admin/ids9i/data/x01/.snapshot/ids9i.0/test02_01.dbf' tag='ids9i';
.
.
cataloged datafile copy
datafile copy filename=/ora39base/admin/ids9i/data/x01/.snapshot/ids9i.0/test02_01.dbf recid=3942 stamp=557238041
.
.
**********************************************
oracle@houhpt24:ids9i=> sqlplus jvmo@ids9i

SQL*Plus: Release 9.2.0.3.0 - Production on Mon May 2 12:55:07 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

jvmo@ids9i> select table_name, tablespace_name from user_tables where table_name = 'TEST';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST TEST02

jvmo@ids9i> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 11)
ORA-01110: data file 5: '/ora39base/admin/ids9i/data/x01/test02_01.dbf'

***********************************
>>> This shows I did a good job messing up that datafile :-), so on to rman:
************************************

oracle@houhpt24:xvi=> rman target / catalog rman/rman@rcatcr1p

Recovery Manager: Release 9.2.0.3.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: IDS9I (DBID=1076478011)
connected to recovery catalog database

RMAN> blockrecover datafile 5 block 11;

Starting blockrecover at 02-MAY-05
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=17 devtype=DISK


channel ORA_DISK_1: restoring block(s) from datafilecopy /ora39base/admin/ids9i/data/x01/.snapshot/ids9i.0/test02_01.dbf

**********************************
Notice the blockrecover is using the datafilecopy catalogued from the snapshot. Very nice :-)
***********************************

starting media recovery
media recovery complete

Finished blockrecover at 02-MAY-05

RMAN> exit


Recovery Manager complete.
oracle@houhpt24:xvi=> sqlplus jvmo@ids9i

SQL*Plus: Release 9.2.0.3.0 - Production on Mon May 2 12:54:14 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

jvmo@ids9i> select count(*) from test;
select count(*) from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 12)
ORA-01110: data file 5: '/ora39base/admin/ids9i/data/x01/test02_01.dbf'


jvmo@ids9i>
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
oracle@houhpt24:xvi=>

***************************
>>> Bummer, I messed up two blocks ?!?
***************************

oracle@houhpt24:xvi=> rrman

Recovery Manager: Release 9.2.0.3.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: IDS9I (DBID=1076478011)
connected to recovery catalog database

RMAN> blockrecover datafile 5 block 12;

Starting blockrecover at 05/02/2005 12:54
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK


channel ORA_DISK_1: restoring block(s) from datafilecopy /ora39base/admin/ids9i/data/x01/.snapshot/ids9i.0/test02_01.dbf

starting media recovery
media recovery complete

Finished blockrecover at 05/02/2005 12:55

RMAN> exit


Recovery Manager complete.
oracle@houhpt24:ids9i=> sqlplus jvmo@ids9i

SQL*Plus: Release 9.2.0.3.0 - Production on Mon May 2 12:55:07 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production

jvmo@ids9i> select count(*) from test;

COUNT(*)
----------
18000

jvmo@ids9i>
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
oracle@houhpt24:ids9i=> oracle@houhpt24:ids9i=>
**********************************

Regards, jan

Tom,

<quote>
the OS is writing (and hence snapshoting) at blocksize X, oracle is writing at
some M*X.
</quote>

The snapshot process takes place on the NetApp filer itself, totally under the covers. All the snapshot really is, is a set of pointers to the original location of the blocks, any block change will be made to a new block (gross oversimplification going on here :-). And all the tablespaces are in backup mode, so there's enough data in the redo to fix fractured blocks I'd think?

Looking forward to your information. Oracle is using NetApp filers in a big way I understand, I'm curious what you guys do in this respect...

regards, jan

Just to make it clear for myself...
So you think that when I do a blockrecover using rman those possibly fractured blocks do *not* get fixed properly, like they would if I did an "old style" backup/restore?

Regards, jan

Actually I'm doing both, a normal rman backup of the database, and a "hot backup style" backup using snapshot. Then I catalogue that snapshot in rman. The reason being, since we don't have enough space, I have to gzip my rman backup. Some of the databases are 200~300G, in case of recovery I would be gunzipping for four or more hours before I could start the actual restore/recovery! Sad isn't it! By having these snapshots around I was hoping to have another "fast-path" option for restore...

Regards, jan

I was just hoping to add to the conversations here with some clarifications of what those acronyms are.

SRDF - Symmetrix Remote Data Facility. EMC remote mirroring software used to mirror volumes from one disk array to another. Generally the array's will be connected via fiber. SRDF transports changed disk blocks from one volume to another. A volume that is an R2 (remote mirror) via SRDF can be split and synchronized, however this volume can not be mounted to a host. If you were using a Clariion disk array the software that provides this functionality is called Mirrorview. EMC provides functionality where the primary volume can actually be restored from the SRDF mirror (R2).

BCV - Business Continuance Volume. EMC software that will create an exact copy of a volume. This volume in effect becomes a third mirror (R3) when used in conjunction with SRDF. The difference is a BCV volume can be mounted to the same or a different host. Note this is a full copy of the primary volume (takes up the same amount of space). You can write to and read from this volume without affecting the primary volume or the mirror. EMC also provides functionality where the primary (R1) volume or the SRDF volume (R2) can be restored from the BCV.

SnapClone - EMC provides functionality for both SnapClones and Snapshots. A snapshot is a point in time copy of a volume which generally takes up about 10% of the physical space occupied by the primary volume. When a snapshot is read you will read unchanged disk blocks from the primary volume. Any blocks that have been changed on the primary volume will be read from the snapshot, which has the "before" block. I'm not sure about the snap clone functionality as I haven't used it.

I would imagine NetApps and other storage vendors have similiar functionality with their products. I've only worked with EMC hardware and software.

Just me personally, I like to use these things this way. I haven't used RMAN with this scenario, so your mileage may vary.
Assumption - SRDF is active and BCV's are synchronized
I place the database in hot backup mode.
I split the BCV's off, meaning they are no longer receiving updates from the primary volumes.
I take the database out of hot backup mode.
I do a filesystem backup of the BCV's, including the archive logs.

With this process I now have a point in time copy of the database (redo logs and archive logs) which I can restore. If I did restore from it, the database would start and do automatic media recovery and become consistent to the point in time of the BCV split. Or you could do managed recovery and apply any newer archived logs beyond the BCV split time.

Just wanted to share my method of working with these guys. I'm sure everyone's processes here work well for them. Just wanted to share my experience.

Thanks,
Mark

Hey this is even an oracle publication ... yes you can use rman and bcv's together. You can break off a bcv's and use rman to back it up.

Even better after you break off the bcv (disk copy) leave it broken off until you need to backup again. That's a disk copy right? *** Very powerful concept want to play with this type of thing again and again and again on test sytems to make sure you know how to recover.

The following url if from oracle ... it does not go into how to design bcv's ... the doc is correct but you will want to change it considerably to make it workable in an actual environment.

You might have 12 bcv's ... 10 of them for database files and 1 for the control files and 1 for redo log's ... or whatever is best for you and your database. No one size fits all design.

</code> http://www.oracle.com/technology/deploy/availability/pdf/RMAN8i_BCV.pdf <code>

Don't read too much into the 8i thing it's really 8i and up but the doc doesn't go into 10g but still works.

I have not seen any problems with what we are doing "so far". My environment is raw using EMC symmetrix. After each rman backup we follow the backup with a "rman validate backup check logical".

What errors would we see from a split block ( I have seen the error messge documented somewhere its been a long week ) in my backup environment?

Thanks

Hi Tom,

We have a situation as explained below:

We have a system with some additional hard disk space (that will be configured as another drive) to be used to take backups. I just want to know what are the different options to take a backup. In previous situations, we have taken hot backups by doing ocopy. In Oracle 10g is there some other advanced mechanism to take backups? Pls suggest.

Thanks a lot for your help.

I went to the source, one of the authors of the paper on otn, Stephan Haisley. He says:

Tom,

I finally got round to testing this today. I dont have a set or EMC disks in my basement so used a 10.1.0.4 database and moved it out the way to take the backup, simulating it on the backup host.

The steps I followed:


1. Alter system archive log current;

2. Put database in backup mode

3. Copied all the datafiles to backup dir

4. Made a whole bunch of DML activity, and switch logfiles a few times to generate a log seq gap between the start and end backup markers

5. Took the database out of backup mode

6. Alter system archive log current

7. Resyncd the RMAN catalog

8. Create the controlfile backups with RMAN as listed on page 8 of the document

9. Manually simulated two fractured blocks on a backup datafile using a binary editor and changing the block sequence number in the tail of the block. Blocks show up using DBV:



Page 11619 is influx - most likely media corrupt

Corrupt block relative dba: 0x01002d63 (file 4, block 11619)

Fractured block found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x01002d63

last change scn: 0x0000.00051ba0 seq: 0x1 flg: 0x06

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x1ba00602

check value in block header: 0x7e33

computed block checksum: 0x3



Page 11624 is influx - most likely media corrupt

Corrupt block relative dba: 0x01002d68 (file 4, block 11624)

Fractured block found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x01002d68

last change scn: 0x0000.00051ba0 seq: 0x1 flg: 0x06

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x1ba00602

check value in block header: 0x7f00

computed block checksum: 0x3



10. Moved the primary database out of the way

11. Put the hot backup datafiles in place of the primary database location and changed init.ora to point to control_startup so that RMAN would know where to read the datafiles from

12. Tried to take a backup as listed on page 10 of the document, with the following failure from RMAN:



Starting backup at 10-MAY-05

RMAN-06554: WARNING: file 1 is in backup mode

RMAN-06554: WARNING: file 3 is in backup mode

RMAN-06554: WARNING: file 4 is in backup mode

RMAN-06554: WARNING: file 11 is in backup mode

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/oradata/S101/system01.dbf

input datafile fno=00011 name=/u02/oradata/S101/undotbs201.dbf

input datafile fno=00003 name=/u01/oradata/S101/sysaux01.dbf

input datafile fno=00004 name=/u01/oradata/S101/users01.dbf

channel d1: starting piece 1 at 10-MAY-05

released channel: d1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on d1 channel at 05/10/2005 16:13:23

ORA-19566: exceeded limit of 0 corrupt blocks for file /u01/oradata/S101/users01.dbf



Because the MAXCORRUPT setting defaults to 0 for all datafiles, RMAN will error out after rereading the fractured block 5 times and abort the backup. One solution would be to use SET MAXCORRUPT FOR DATAFILE n TO m for each file, which really is not the best way. When the database is restored, during recovery the fractured blocks will be addressed by applying all the redo from the start to the end hot backup time (like normal hot backups), or by applying incremental restores if they are used this is the reason we put the tablespaces into hot backup mode so we can always get over fractured blocks caused by changes being made during the split mirror phase. So if you are unlucky enough to get fractured blocks during the mirror split, you will get errors like this on the first one RMAN finds. This would also panic most people, which is not a good thing.



The other and I think better solution would be to apply the archives on the backup host BEFORE you take the backup. This wouldnt interfere with the production host and would also make the backup complete correctly assuming all archives were applied so that all datafiles are taken out of hot backup mode but if using the document ALL archives should be backed up and so will be available. I tried this with the following results (after using recover database until cancel using backup controlfile; where recovery continued until the last archive log found on disk was applied)



Starting backup at 10-MAY-05

channel d1: starting full datafile backupset

channel d1: specifying datafile(s) in backupset

input datafile fno=00001 name=/u01/oradata/S101/system01.dbf

input datafile fno=00011 name=/u02/oradata/S101/undotbs201.dbf

input datafile fno=00003 name=/u01/oradata/S101/sysaux01.dbf

input datafile fno=00004 name=/u01/oradata/S101/users01.dbf

channel d1: starting piece 1 at 10-MAY-05

channel d1: finished piece 1 at 10-MAY-05

piece handle=/u01/oradata/scripts/bcv/db_S101_557947312_67_1 comment=NONE

channel d1: backup set complete, elapsed time: 00:01:46

Finished backup at 10-MAY-05



So it works, and removes the fractured block problem!



The only difference between the solutions is at what stage you want to apply the redo. Personally I would want to know that my backup contains NO corruption, fixable or not. The amount of redo to be applied would depend on how short the time was between the first BEGIN BACKUP and the last END BACKUP commands, plus the amount of activity/number of logs generated over this time. For the slowest of mirror splits this should be very small indeed.



To finish the test, I used the restore/recovery steps outlined in the document, and it worked just fine without any problems using the backup created after applying the archive logs.



I hope this helps, but let me know if you have more questions. I will shoot Nitin an email to see how we can get this document changed

When the responder says they were using rman to backup the (simulated split clone) after applying the archive logs, was rman being used to update a recovery catalog or only the local control files?

One of the difficulties in the split mirror backup is that as soon as you move the split mirror to a backup host and mount it the control files you are operating on have diverged from your original database's control files.

You will need to be able to catalog the backup in a recovery catalog not just a control file ... it's not clear to me if this test is accurate.

I don't have a big problem with the attempted rman backup of a split mirror failing if a block has fractured. This is not a good backup ... so you still have the previous one that succeeed if any ... right?

Only when a backup does really succeed in split mirror and you also then validate that backup ... you should be sweet.

Final comment: it makes me a little nervous that the responders are simulating things rather than having the correct equipment available to really test it out against a matching environment. I have that equipment available ... so do lots of other people.

Sorry I guess I have one more comment. When you start applying the archived logs against the split mirror before you start doing a backup ... again you are diverging the database from the original one.

You are backing up something that "should be logically and physically similar" to where it came from ... but it still appears to be a somewhat bizarre and potentially risky workaround or bypass.

The whole reason people want to use rman is to avoid taking risks.

1: When the responder says they were using rman to backup the (simulated split clone) after applying the archive logs, was rman being used to update a recovery catalog or only the local control files?

A: The paper was written using a recovery catalog. If the local controlfile was used without a catalog the backup taken when the mirrors were split would not be recorded in the production controlfile. Restoration then becomes more involved - same as losing all controlfiles when not using a catalog.

2: One of the difficulties in the split mirror backup is that as soon as you move the split mirror to a backup host and mount it the control files you are operating on have diverged from your original database's control files.

You will need to be able to catalog the backup in a recovery catalog not just a control file ... it's not clear to me if this test is accurate.

A: The paper explains that you need two controlfile copies from the production database. One to mount after the split and a second to backup. Therefore the controlfile you are backing up is the same as it was in production (apart from the production controlfile moving forward in time), but the controlfile type is marked as a backup controlfile. See previous answer for using the recovery catalog.

3: I don't have a big problem with the attempted rman backup of a split mirror failing if a block has fractured. This is not a good backup ... so you still have the previous one that succeeed if any ... right?

A: Correct, but during a restoration RMAN will select the most recent backup. Therefore you will have manually select the older backup containing no corruptions for restoration.

4. Final comment: it makes me a little nervous that the responders are simulating things rather than having the correct equipment available to really test it out against a matching environment. I have that equipment available ... so do lots of other people.

A. Internally at Oracle we too have the hardware available, but time to play with it needs to be scheduled. As you can imagine we have a very large employee base and the hardware is not available for everyone to play with at any time they choose. Once you understand how RMAN works it becomes very easy to simulate most things on any platform instead of using the exact same hardware. I was not demonstrating performance of using RMAN and BCV backups, but the mechanics of how it could be effected if a fractured block occurs during a split. This can be simulated on any platform and does not require the same hardware as used during writing of the original paper.

5. When you start applying the archived logs against the split mirror before you start doing a backup ... again you are diverging the database from the original one.
You are backing up something that "should be logically and physically similar" to where it came from ... but it still appears to be a somewhat bizarre and potentially risky workaround or bypass.
The whole reason people want to use rman is to avoid taking risks.

A. Not quite. When you split the mirror and take a backup, you are taking a backup of a database as it appeared at the time of the split. The production database has already moved on from this time and so the backup is behind the production database and hence has already divurged from the current production database with regards to point in time. When applying the archivelogs before taking the backup you are reducing the risks of seeing fractured blocks during the backup. You should only need to apply one or two logs at most (depending on speed of mirror splits), enough to cover the last end backup point made in production. This will only move the database forward in time - this makes the database move closer to the current production point in time, it certainly does not deviate to make it look something different.
If you take a backup without applying the archive logs in this scenario, during recovery you will have to roll forward past the end backup points. I am only advising you do it before the backup instead of during recovery. As I said this reduces the risk of fractured blocks and increases the chances of a trouble free backup using RMAN.

The reason of using the split mirror backups is to reduce the I/O overhead of reading the whole database (even unused blocks have to be read remember). Checking each block for fractured state (head and tail comparisons), checksum calculations and comparisons, logical checks if using the CHECK LOGICAL keywords plus checking the block to make sure it requires backing up also requires CPU cycles. By using BCV split mirror backups you are moving all this to another server. Sure resilvering disks takes I/O and CPU cycles but this paper was not an exercise to compare the two, but to give an alternative approach.

With the introduction of 10g Fast Incremental backups, the overheads of reading the entire database has been reduced dramatically and reduces the dent on the production server during a full database backup.

I hope this explains things more clearly.

Stephan

I'm contemplating an RMAN/BCV implementation along the lines of that suggested in the above referenced paper. I have been warned (I've yet to actually test it myself) about the following problem :-

If you try to use RMAN to backup redologs which are generated after the split *whilst* backing up the split copy then RMAN will complain that the redologs are in the future.

This is something we will have to do - the database is large and the redo-generation rate such that frequent backup/purge of logs is required.

A suggested workaround is to use 2 recovery catalogs - one for the data and control files the other for the archivelogs. On the face of it this seems acceptable (if a little untidy). Can you see any potential drawbacks?

Many thanks,
John

Tom, thanks for your prompt reply.

The backup method we are using does require that we backup ONLINE redo logs.

By using EMC's consistent split technology (ECA) we take an image that is 'guaranteed' to be consistent at that point in time. The restore is essentially a crash recovery of this image and as such makes use of the online redo logs. It might sound a bit nasty but it is approved by EMC & Oracle (provided we are in backup mode) and has been fully tested.
The reason we do it this way is we are actually backing up a number of databases which rely upon each other for replication. To avoid problems with replication we split all the databases at the same instant and they all (crash) recover without replication problems.

But now you mention it this does give me a problem - how do I get rman to backup the split copy of the online redo logs?

Also - I'm not sure I explained myself properly with the "archivelogs in the future" thing. I'm talking about two simultaneous backups:-

- the database & control files from the mounted split copy on the backup server
- the archived redo logs which are constantly generated from the live server

I understood that syncing the recovery catalog would cause a conflict as you have, for the purpose of the backup, two different control files.

The hope is that by using two separate recovery catalogs we could remove the possibility of this conflict.

Thanks again,
John

Having used BCVs to clone a database for backup, my experience shows that a well-scripted split routine only requires a couple of secondes to execute. Therefor issuing an ALTER SYSTEM SUSPEND; command will halt all IO -- including log files, data files, and control files. Then when you split ALL of the volumes -- including those with control files and log files -- your split work.

I have not seen this method mentioned. Am I way off in trying to follow this issue here?

Tom,

I get the point about the risk of restoring backed up online redo logs over current, thereby rendering complete recovery impossible.

I should be clear that the backup strategy I'm talking about is to provide options for INCOMPLETE recovery ie. to some point in the past. I'm backing up a set of databases which replicate to each other. Consequently recovery to a point in time in the past is fraught with the possibility of the replication status being inconsistent (Site A - refresh has commited; Site B - refresh hasn't). The method I'm using ensures that all the databases are crash recovered to the same point in time.

In the event of COMPLETE recovery I will safeguard the real ORLs before restore.

[I'm looking into the possibility of engineering a regular, arbitrary distributed transaction across all the databases. The theory being that this would give an SCN which would be consistent across all the databases and therefore a consistent recovery point for all the databases. For now though all I have is the method described above]

Hope that makes sense!

John

Tom,

We need to provide options for incomplete recovery so we can, if need be, recover our systems to some point in the past.

We have tried the standard method - restore a backup & apply archivelogs to the desired point in time. This has proven problematic because of the replication. It is difficult to determine at which point to stop the recovery (RECOVER UNTIL TIME...) and synchronise this across all the replicating databases.

So instead we are taking a simultaneous split mirror image of all the systems at the same time. The EMC software (ECA) ensures that these images are all consistent as of the time of the split. This means we can use oracle CRASH recovery (not media recovery) to open all these databases recovered to the same point in time. It is equivalent to if we crashed all the instances at the same time.

If we have to do ANY incomplete media recovery (RECOVER UNTIL...) we are back with the problem of synchronising across systems.

So - given we are talking about CRASH recovery - we need :-

- datafiles at the time of the split[crash]
- control files at the time of the split[crash]
- online redo logs at the time of the split[crash]
(Unless I've got that wrong - can you use an archived log for this?)

To start an instance against these images we now do :-

startup mount
recover database (because the files were in BACKUP MODE at the split)
alter database open;


As I said we tested this - it works. So in this case the backed-up online redo logs are most "useful".

I would rather we had the ability to do conventional recovery. I am pursuing a plan to provide this.

Any thoughts on my other question about conflict with the recovery catalog?

Thanks again,
John

Tom,

Dead right. Unfortunately the architecture here is the end result of one questionable decision after another. The backup & recovery situation is only one of the painful consequences. Re-architecting though is too risky for the management to countenance for now.

I'm starting to think that using RMAN for me is more trouble than it is worth. What with the recovery catalog issue, the fracture blocks and inability to get ORLs backed up.

Thanks for your time,
John

John I understand completely what you are doing. When you are setup on EMC with BCV's or other hardware based techniques you have some recovery options "outside of" the oracle provided rman realm.

When we split the mirror's and get an rman backup I then re-synchronize some of our mirrors and keep other one's broken off.

This gives me an "online" disk based version of what I want so that I won't potentially need to use rman to do any recovery ( or at least restore from tape ).

Conceptually there are 3 different sets or types of data that you want to perhaps treat differently from each other.

The first set is your control files. The second set is your online redo logs. The third set is your actual oracle database files ( although they might be raw partitions ).

As you look at the oracle/emc provided paper on these techniques think about the design of your mirrors against those types of recovery related disk structures.

You might conceivably have one set of mirrors for control files and online redo logs that you re-synch after a backup and another set that you break off and leave broken off.

Recovery could be performed on the original system or on the backup host and sending over and restoring or not some of any of these types of recovery related material.

I still don't quite understand Stephan here ... ( this was his answer from an earlier post ).

3: I don't have a big problem with the attempted rman backup of a split mirror failing if a block has fractured. This is not a good backup ... so you still have the previous one that succeeed if any ... right?

A: Correct, but during a restoration RMAN will select the most recent backup. Therefore you will have manually select the older backup containing no corruptions for restoration.

*** My comments ....

The RMAN backup will fail if any fractured blocks. We are going to catalog this backup into a recovery catalog if it succeeds.

It has not succeeded (given a fractured block) therefore the recovery catalog should not have any knowledge of this failed backup.

So if you need to restore/recover you use your prior good one.

from one of the posts above:

"Assumption - SRDF is active and BCV's are synchronized
I place the database in hot backup mode.
I split the BCV's off, meaning they are no longer receiving updates from the
primary volumes.
I take the database out of hot backup mode.
I do a filesystem backup of the BCV's, including the archive logs.

With this process I now have a point in time copy of the database (redo logs and
archive logs) which I can restore. If I did restore from it, the database would
start and do automatic media recovery and become consistent to the point in time
of the BCV split. Or you could do managed recovery and apply any newer archived
logs beyond the BCV split time."

I understand that hot backup is for taking care of fractured blocks in data files - what about fractured redo log blocks? Would thta cause a problem - is this is a possibility at all?

This was a previous update of mine:

3: I don't have a big problem with the attempted rman backup of a split mirror failing if a block has fractured. This is not a good backup ... so you still have the previous one that succeeed if any ... right?

A: Correct, but during a restoration RMAN will select the most recent backup. Therefore you will have manually select the older backup containing no corruptions for restoration.

*** My comments ....

The RMAN backup will fail if any fractured blocks. We are going to catalog this backup into a recovery catalog if it succeeds.

It has not succeeded (given a fractured block) therefore the recovery catalog should not have any knowledge of this failed backup.

So if you need to restore/recover you use your prior good one.

**** End of previous comment

Maybe what Stephan was talking about was if you did an rman backup where you had overridden the default limit of bad blocks ( which is 1 ... you stop at the first one ) to some value that was higher than 1 ... and that backup with 1 or more bad blocks did actually work and get cataloged ... then you could run into a problem with a restore and have to point to a backup that had no bad blocks in it?

However, doesn't rman know if a backup had any bad blocks in it ( sure it does ) and wouldn't it restore from a backup that didn't have any bad blocks?

i have been pursuing the question of fractured blocks, in the context of split-mirror backups using RMAN in a non-proxy copy conifguration at length with some of the storage guys.

i am assured that when a BCV split command is received the SAN will insert a split operation into the i/o queue so that:
1. All BCV volumes are effectively split from their Primary volume at a single point in time.
2. The split will take place _between_ i/o operations and not in the middle of an i/o operation.

so if Oracle issues a single write (one system call) to write one or more blocks at a time (Oracle blocks) to a database file in a single i/o operations, i am having trouble trying to construct a scenario in which a block is fractured. it doesn't matter that the oracle block size is different to the device block size because the split command will be queued in between i/o operations and _will not_ be made to occur in the middle of a single i/o operation regardless of how much data is involved or how many device-size blocks are being written in that operation.

if Oracle issues multiple seperate writes (seperate system calls) to perform the write of a single block then i can construct a scenario in which the split command is queued and occurs between the first and last write to a block.

now i think redo is written a bit at a time, not in oracle blocks, so perhaps redo blocks can be fractured(?)

i thought datafile blocks were written a block (or multiple blocks) at a time so i cannot imagine datafile blocks being fractured.

any comments would be appreciated.

loved the wonderful details and polite banter... the link to oracle srdf/rman helped me find some details to using srdf and bcv's for a datacenter move.

thanks again!

steve

I have a 200GB database (and growing). We are using DG to maintain a remote standby site for disaster recovery.

There is a requirement to create another local replica of the database (on the same site as the primary) to be used for reporting and Dev/QA purposes. It has to be in read-write mode (to be able to change stored procedures, views and such), but the *data* changes made to this local copy are not important, they can be discarded at the end of the day.

Bottom line: It is copy of Production for Dev/QA to run reports against current/live Production data without impacting the real Production database.

What would you recommend as the best way to implement this sort of thing? I see the following options

1. Create another DG physical standby instance. But since I need this open read-write, I don't think this is a viable option.

2. Create a DG logical standby - Don't have much experience with this. Any ideas? How would this work?

3. Use EMC SRDF/BCV: We are already using this for remote-site replication for all the Windows servers, so the technology is there, but there is no on-site Oracle+EMC experience.

Looking at </code> http://www.emc.com/pdf/techlib/H1143_Using_EMC_SRDF_Oracle9i_ldv.pdf <code>
(3) does look like a very powerful (albeit expensive) option.

Seems like I can
1. create a local BCV,
2. use it read-write during the day
3. Discard it at the end of the day
4. re-sync with the Production volume next morning
5. Repeat

Does this sound right? I guess it all depends on how long step (4) takes. Is it proportional to the size of the database or is it some hardware/SRDF magic that does the sync instantaneously?

Comments welcome.

Thanks

"A logical standby would work for you - but it would be best to have it constantly being caught up"

But would the Logical Standby allow me to change views and stored procs on the standby?

1. Can I create additional stored procedures and views on the logical standby?

You already answered yes to this.

2. Can I modify views and stored procs that are being synced with the primary?

If yes, then when I change the view on the Primary, will it over-write my changed view on the standby?

3. Can I add new columns to the existing tables on the logical standby without disrupting the standby apply process?

Thanks

What I want is a copy of my Production database (as of last night is fine) so that QA can test/compare using live data and up to the cent numbers.

If there is a data issue that needs a view or something to be changed on this "standby", I would like to be able to do that.

If I do change a view/sp that *is* being maintained by the logical standby, what would happen? I am not sure I understood your earlier answer regarding this...Question (2).

Basically, I want a development-style database where I can change views/stored procs, add columns (ideally) but still use up-to-date Production *data*.

Any ideas?

Thanks

You can do this with Netapp Snapshots.

Say Database runs on vol1 mounted on SERVER1. This is your production server. You take snapshots of this every day. You can mount that snapshot on your dev server. The snapshot is read/write to your dev box, but the changes are not saved as part of the snap shot. Infact once the dev server unmounts it, they go away. The snapshot is unchanged so if you need it for any kind of recovery in production.

So for your dev server you can have a script that ever day unmounts the old snapshot and mounts the new one.

We are lookng at doing this for both Dev and Test. Dev doesn't get reloaded that often but Test is reloaded daily.

The best part is the saving of space. Say your database is 250 GB.. By mounting the snapshot, you only use what you use. So the testing DB would not us up that much space. Only the changed block add space. The longer you have a snapshot mounted or the more data you change, the more space you would use.

Can a similar approach be taken with a database using ASM?

I have recently been involved in a project using an IBM SAN to create backups using flash copy which are then mounted and rolled forward using the archive logs before being backed up to tape using RMAN on a separate storage node.

All our testing was performed using conventional filesystem datafiles as our client is currently opposed to using ASM however as it is likely that our client will change there mind in the future (no logical reason has been given by our client for not using ASM, I suspect it because it is new and 'scary'). Has anyone successfully used any of the snapshot/cloning technologies to create a usable database backup? Unfortunately the test servers and SAN where only available for a limited period and we encountered some peculiarities when making the backups to tape so we didn't get time to test with ASM.

Metalink advises that RMAN is the only method to backup ASM databases
from noteID: 305648.1

ASM (Automatic Storage Management) SUPPORT:
------------------------------------------

- RMAN can back up and restore files that are stored in ASM disk groups. In fact,
RMAN is the only way to back up and restore ASM files.
- The Flash Recovery Area can be an ASM disk group.
- Backup sets and image copies can be created in ASM, either by automatically
Creating them in the Flash Recovery Area, or by explicitly specifying an ASM
disk group for their location.

However OTN contains a joint whitepaper issued by oracle and EMC that describes a method of using BCV's to create backups when using ASM that contradicts this.

</code> http://www.oracle.com/technology/products/database/asm/pdf/EMC%20TF-SRDF%2002-06_0.pdf <code>

Hi Tom
The thread was excellent , it took long time to completely read this thread. but it is worth of it.
Now can i use 10g RAC ASM BCV's to present them as new non-RAC instance on new node.

We have 2 - node 10 g RAC with ASM,EMC .(almost 1 TB).
We use BCV's for backup .
can i mount this BCV after split on a single non-RAC instance and open the database for reporting .
I have to do this everyday with automated scripts.
We are already doing this with the normal instances on 8i,9i,10g.

Please accept my apologies if this seems to you a new question.

1-
We are using snap technology to make copy, i.e. taking
backup of all control , datafiles, online redologs at
given point in time. Its working fine, but some time
I think , is there any chance of fractured block?

2- There is another database, whose datafiles are
on two arrays of SAN, here we cannot take snap of both arrays at one point in time (although it might be possible, but right now we dont have that technology or knowledge).
What I was thinking is that can we HALT the database for few seconds? something like ALTER DATABASE [HALT|NOHALT] command, so that all files are freezed and then we take
snap of both arrays.

Thanks.