Re: Backup Strategy - Multiple schema database

On Dec 15, 8:16 am, "Noghri64" <cbeauche..._at_gmail.com> wrote:
> At a high level, what is the recommended backup strategy for a
> multi-schema (100s) 24/7 database?
> Database has a high volume of DML activity.
> Point-in-time recovery is required on occasions for specific schemas.
>
> Thanks.

Some Backup Thoughts:

Prereq:
A) Read the recovery manuals:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14196/backrest001.htm#sthref606

B) Know your SLAs.

1. Use RMAN for physical backups
2. If you can't use RMAN because db is just too big, use split mirror
3. Archive Mode Enabled
4. Any direct-path inserts? Rman: backup not backed up command is nice for this.
5. Incremental uses recover and is scn based. Great feature, but beware incremental. Incremental takes longer to recover. Align this will recovery SLA choose full or incremental. Note: Choosing RMAN Incremental Level 0 if full now but flexible for level 1.
6. If version appropriate, use flashback for point-in-time recovery.
7. Enable recycle bin if version appropriate and performance.
8. Maybe align datafile with schema to allow for physical block point-in-time schema recovery.
9. Multi-channel backups direct to tape if current infrastructure allows.

Things to consider:
Schema Export - not the greatest but makes short work when logical recovery is the issue.
Rman Backup Set based Transportable Tablespace backups.

Thing To avoid:
Backup tablespace - unless using spit mirror

Gotchas:
Single action point-in-time recovery is difficult to master. The database operates on SCN and point-in-time is a misnomer for this number. Time, if specified, is approximate and converted to SCN. This means that logical point-in-time data recovery for any single user action should really sit at the application layer. While the backup and recovery tools available today on the latest Oracle versions make restoring single action point-in-time data easier, it still requires high maintenance and should really be architected into the application design. Understanding your backup options and limitations is key. Don't buy into a problem you should not own. You will be working long days and nights and in need of personal recovery if you do.

There are lots of options. The key is make it work for you.

There is no DBA without a DB: always test your recovery strategies, over and over and over.

Read the fine RMAN manual all the way through.

Hope this helps. Received on Fri Dec 15 2006 - 09:46:48 CST