Oracle DB Disk Split Backups - Can be done with SUSPEND only ?

[Alzhy]

Or is it still a "neccessity" to have the Tablespaces placed in HotBackup mode?

We're using disks plits which happens very quickly as in seconds. Currently we only place the TBs in hotbackup mode - and on our 3TB DB.. the process of placing the DB in hotbackup mode takes anywhere from 30 minutes to an hour!

So is it really necessary to have TBs be in HB mode or can we just do an ALTER SYSTEM SUSPEND, split and ALTER SYSTEM RESUME?

We're using a mix of 9.x and 10.x DBs... on HP-UX 11.11 with VxVM and VxFS 3.5

[Steven E. Protter]

Shalom,

With Online JFS and snapshots you should be able to do the following:
1) Shut the database briefly.
2) Create a snapshot logical volume
3) Bring the database back up.
4) Cold back up the database.

In testing this can be done very quickly to get a good cold backup.

SEP

[Volker Borowski · Accepted solution]

Nelson,

the amount of time for bringing tablespace into backup mode has the problem, that for each datafile that is placed into backup mode a complete dirty block scan is done. We struggled with this as well on a multi TB database.
Actually, the amount of time for placing the backup mode has nothing to do with the database size, but with the size of the db_cache and the number of datafiles. Now if you have 20GB of cache times 750 datafiles that gives some stuff to do ("checkpointing" 20G for 750 files).

Actually with 10g there is a
alter database begin backup;
command available that runs much better than the tablespace backup.

Since we upgraded, begin backup is not such a big problem any more.
I do not know if there is a solution for the tablespace backup command in 9.x yet, as I did not dig more into that since the 10g upgrade of our big beast.

If you have SAP-Net access, check out note 875477. If you can not upgrade, an option might be to
do resize the db_cachesize to a small value (2 GB?)
do begin backup
do snapshot
do end backup
do resize the db_cachesize to a big value

but this will be costy because you'll be losing cache quality.

Hope this helps
Volker

[Yogeeraj_1]

hi nelson,

I still believe that RMAN is the best way to do a hot backup. If you enable "DB block change tracking" and implement an Incremental Merge Backup, you can have the best backup you can think of for your 3TB database.

If you dont trust this, at least try it once. Backup and restore - both are simple.

Good luck!

kind regards
yogeeraj

[TwoProc]

Nelson,

I'm with Yogeeraj, You won't need to use the hot backup state anylonger if you just get your backups managed by rman. We do hot backups all the time with it, and don't put the database in a hot backup state. IMHO, it's the big selling point of Rman.

But, if you *do* use hotbackup state with a manual file backup - why not just put just a single tablespace in hotbackup mode, back that up, take it out of hotbackup mode, then move on to the next? This would keep you from having to manage the *whole thing* in hot backup mode. Just script your hot backup to manage the hotbackup state per tablespace. If you need I've got a script that I snagged from somewhere which will write the hotback script for you anytime you've made changes and want a new one.

Re: SEP's suggestion: this sounds great, but I've never tested it. Steven, can that process work even on FULL (practically 100%) volumes? Where does the "space" come from that manages the changes until the backup is done with the snapshot?

[Volker Borowski]

Hi Nelson,

got a reference to that suspend/resume topic meanwhile:

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/osbackup007.htm

To sum it up:
- You can not use RMAN to do a split
- It is "recommended" to do a BEGIN BACKUP before a suspend
- You can backup after a successfull suspend
(which suggests, that BEGIN BACKUP is "not required")

But, reading this:

[quote]
The ALTER SYSTEM SUSPEND statement suspends the database by halting I/Os to datafile headers, datafiles, and control files. When the database is suspended, all pre-existing I/O operations can complete; however, any new database I/O access attempts are queued.
[/quote]

[Volkers comment] So I/O to the online logs still can be done.

And this:
[quote]
Because suspending a database does not guarantee immediate termination of I/O, Oracle recommends that you precede the ALTER SYSTEM SUSPEND statement with a BEGIN BACKUP statement so that the tablespaces are placed in backup mode.
[/quote]

It looks to me, that the suspend function is just a tool to support splits on hardware that does require some low activity phase to do the split. It has nothing to do, with getting a "consistent" stop of DB activity.

If you need to split to do a backup, BEGIN BACKUP is required.
If you need to split because you want to attach your mirror to a DB copy, BEGIN BACKUP is required (because recreation of controlfile would require 'recover using backup controlfile').
The only point which "might" work is, if you split to attach the mirror to a DB clone (same SID, same filelayout), but I think, because the I/O to the Online logs is not suspended, you might get a problem when doing the instance recovery upon startup on the clone, because the split of your online log volume might have migt be "out of sync". And you'd need the current online log to do a instance recovery.

I'd try to stay to BEGIN BACKUP and work on speeding up the statement.

Volker

[Eric Antunes]

Hi Nelson,

I recommend Metalink Note 302615.1 ("RMAN and Split Mirror Disk Backups").

Best Regards,

Eric Antunes