patternMinor
Database backups in Oracle - Export the database or use other tools?
Viewed 0 times
thebackupsothertoolsdatabaseexportoracleuse
Problem
One of the "practices" that I've seen about being performed by DBA's in my organization is to treat a full database export using tools like
Would this be a good practice ? What would be the advantages of using RMAN over this approach ?
exp / expdp as backup.Would this be a good practice ? What would be the advantages of using RMAN over this approach ?
Solution
The advantage of RMAN is PITR - point in time recovery. You can take an RMAN backup of the DBFs and an RMAN backup of the archived redo logs and recover your database to any point in time up to the time of the most recent archived redo log backup. The disadvantage of this approach is that it is very coarse-grained - you can only recover at the level of the tablespace.
The advantage of exp/expdp is that you have a consistent copy of the database that you can just import into a freshly created blank database. However, you cannot roll it forward - it is at this point a completely separate, independent database with no logical relationship to its original. But it is easy to recover just a single table, or even a few rows from an export.
A good approach would be regular RMAN full and incremental datafile backups, continuous backups of archived redo logs (e.g. as soon as one archivelog backup completes, start the next one straight away so you are "streaming" the logs to tape) then educate your users in the use of exp/imp so they can perform their own "backups" (in the case of wanting an actual copy of the data) and flashback (so they can do their own recoveries in the case of DML going wrong).
Remember - a backup is intended for the DBA to recover from catastrophic failure of the hardware. It is not for the benefit of end users (or you will spend your whole time doing restores to a test system and copying a few rows back to the original!).
The advantage of exp/expdp is that you have a consistent copy of the database that you can just import into a freshly created blank database. However, you cannot roll it forward - it is at this point a completely separate, independent database with no logical relationship to its original. But it is easy to recover just a single table, or even a few rows from an export.
A good approach would be regular RMAN full and incremental datafile backups, continuous backups of archived redo logs (e.g. as soon as one archivelog backup completes, start the next one straight away so you are "streaming" the logs to tape) then educate your users in the use of exp/imp so they can perform their own "backups" (in the case of wanting an actual copy of the data) and flashback (so they can do their own recoveries in the case of DML going wrong).
Remember - a backup is intended for the DBA to recover from catastrophic failure of the hardware. It is not for the benefit of end users (or you will spend your whole time doing restores to a test system and copying a few rows back to the original!).
Context
StackExchange Database Administrators Q#152, answer score: 7
Revisions (0)
No revisions yet.