Oracle Database Backups

Can anyone help provide a basic overview of the various "parts" of an Oracle database that need to be backed up, and which of those "parts" are done within RMAN, and which need to be covered by the OS backup/Server level backup?

I know with MSSQL you have both the logs and the database files themselves. But Oracle seems to have many more elements that need to be configured for backups. I was after a full list if possible.
LVL 4
pma111Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
If you want a complete backup, RMAN can do it all for you.

You want to backup your database, your archive logs, online redo logs and control files.

RMAN can do all that.

Once you have your backups written out, you probably want some OS level tool to get them off site / to alternate media.

You can configure RMAN to automatically backup your controlfile with each backup (CONFIGURE CONTROLFILE AUTOBACKUP ON is the command if memory serves).

Backup Database is the command (at it's simplest level) to backup your database and online redo logs.
Backup archivelogall (again, at the simplest level) backs up your archived redo logs.

There are a squillion options you can use to configure your backups though.  I would have a good read of the Docs on RMAN - I am pretty sure there's even a 2-Day guide on Backups at Oracle docs site.

Edit: Couldn't find 2 day guide.  But it's included in Chapter 9 of the 2 Day DBA Guide: http://docs.oracle.com/database/121/ADMQS/toc.htm

But Backup and Recovery Recovery User's Guide: http://docs.oracle.com/database/121/BRADV/toc.htm
Backup and Recovery Reference: http://docs.oracle.com/database/121/RCMRF/toc.htm

If you have some specific questions, might be good to come back with them because this is such a LARGE topic.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
I agree with the post above:  squillion options is about right.

You want to backup everything you need to recover from the worst possible disaster.

Think 'smoldering crater' where the data center used to be.

I not only create a backup of my control file but a 'text' version using:
alter database backup controlfile to trace;

This places a text versions in the dump folders for use if the binary versions fail you.

I also create a text version of my spfile:
create pfile='C:\mypfile.ora' from spfile;

If on windows, you might even go so far as to export the Oracle parts of the registry (I don't go that far...).

If you paycheck depends on recovery, BACK IT ALL UP!!!  PUT IT ON AS MANY DIFFERENT TAPES AS POSSIBLE!!!  TAKE THE TAPES CLEAR OF THE BLAST ZONE!!!

As far as what does what:
RMAN will allow the SQL commands (this is how I execute the SQL commands above) and may even allow hosting out to perform OS commands (Not tried this).

Sorry but a 'full list' probably isn't possible.  That equates to asking a mechanic to provide a list of every reparable part of a car.  He would likely hand you the blueprints, assuming he had them...
pma111Author Commented:
Thanks.... are there queries to see the last time:

your database, your archive logs, online redo logs and control files.

Were all backed up?
slightwv (䄆 Netminder) Commented:
There are several database views that will likely have some, if not all, of that information.  The online docs will tell you what each view is for.

I don't know them off the top of my head but save this simple select statement.  It will save you a LOT of time:
select view_name from dba_views where view_name like upper('%&view_to_find%') order by 1;

Open in new window


I have it saved to find_view.sql.

Oracle is REALLY REALLY good about naming views with what they are about.  For this question, then prompted enter backup.  You will see all the views with backup in the name.

The 'catch':  When you see a view like V_$BACKUP_REDOLOG, use V$BACKUP_REDOLOG in your query.


If you cannot find what you need in the views, RMAN will likely have the information in a report.
Steve WalesSenior Database AdministratorCommented:
There are views you can query to get this information.

Examples:

Want a query showing you when all your last backups ran for a DB?  Try this?

set feedback off
set echo off
set linesize 132
set pagesize 500
set heading on
clear breaks
column status format a9
column hrs format 999.99
set markup html on preformat on
ttitle "Backup Summary since Midnight Yesterday"
select session_key, input_type, status,
       to_char(start_time, 'MM/DD/YYYY HH24:MI:SS') start_time,
       to_char(end_time, 'MM/DD/YYYY HH24:MI:SS') end_time,
       elapsed_seconds/3600 hrs
from v$rman_backup_job_details
where start_time >= trunc(sysdate-1)
order by session_key
/

Open in new window


You can vary on that too:

Just want to see if any jobs that have failed?

select count(*) from v$rman_backup_job_details where start_time >= sysdate-25/24 and
(STATUS like '%ERRORS%' or STATUS like '%WARNINGS%' or STATUS = 'FAILED');

Open in new window


Value returned > 0, then you have a failure.

Using this sort of query, I have jobs set up where if I don't have archive log backups completed in the past hour, 30 minutes (whatever the backup interval is), I get an email.  If I database hasn't had an incremental performed in the last 24 hours, I get an email.  All sorts of things you can do based upon your needs.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.