Solved

Need some guidance with backup/restore strategy

Posted on 2013-01-30
3
385 Views
Last Modified: 2013-02-01
I have a database I want to be able to recover to a time when failure has occurred. The database in NOARCHIVELOG mode and planning on using incremental differential backups, running level 0 backup on Friday at 11PM and level 1 backups daily.

This is the script to create level 0 backup:

run {
      SHUTDOWN IMMEDIATE;
      STARTUP NOMOUNT;
      BACKUP LEVEL 0 WITH TAG 'inc_lvl0' DATABASE;
      ALTER DATABASE OPEN;
}

and for level 1:

run {
      SHUTDOWN IMMEDIATE;
      STARTUP NOMOUNT;
      BACKUP LEVEL 1 WITH TAG 'inc_lvl1' DATABASE;
      ALTER DATABASE OPEN;
}


Then the recovery script is as follows:

run {
      SHUTDOWN IMMEDIATE;
      STARTUP NOMOUNT;
      RESTORE CONTROLFILE;
      ALTER DATABASE MOUNT;
      set until time  "TO_DATE('29-JAN-2013 10:51:00','DD-MON-YYYY HH24:MI:SS')";
      RESTORE DATABASE;
      RECOVER DATABASE;
      ALTER DATABASE OPEN RESETLOGS;
}  


Can someone tell me what can I do to improve my backup/recovery strategy and also see if anything I am doing is wrong?
I'd appreciate your recommendations.

P.S. I am not sure if I need to restore a control file.. Do I?
0
Comment
Question by:YZlat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38836548
>>recover to a time when failure has occurred

May not be possible in no archive mode.  You may only be able to restore to the point of the last backup if the current online redo logs have been overwritten but are necessary for the recovery.

Any particular reason you don't want to run in archive log mode?  Typically if a database is important enough to need a recovery plan, it is important enough to need archive log mode.


I'll have to defer to the 'correctness' of your strategy to the RMAN other Experts on the site.  From your previous questions, I know the basics and prettry much what I needed to use for my specific databases.

I'm not fully versed on ALL RMAN workings.

I would suggest running in archive log mode, enable block change tracking and cumulative incrementals (rolls the last lvl1 into the current lvl0).  This is what I do.

You still have an outstnading issue to address:
backup retention.  how long to keep backups on disk,  how to ensure you have access to a backup when you need it, etc...
0
 
LVL 35

Author Comment

by:YZlat
ID: 38836689
a few questions:

1) so UNTIL TIME will not work with a database in NOARCHIVELOG mode?

2) how do i eneble block change tracking and cumulative incrementals ans what will it give me?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 38837586
From our other questions:  This is where this type of site is lacking.  We can answer specific questions.  When it comes to concepts and 'how should I' type questions it really doesn't work.  We can only offer advice.  This advice may or may not work for you in your current situation.

You need to understand why and how Oracle does what it does.  Then and only then can you apply that knowledge to solve a specific issue.

We don't know your system or your requirements to provide 100% solid advice on what will work or you.

You are trying to learn disaster recovery.  Unfortunately people make entire careers out of this exact topic.  There is no way to get into all possibilities here.  Every situation/system/database/??? is unique.

Once you learn the concepts, then the decisions are easier.

You need to know the differences in archivelog and noarchivelog modes.
You need to know what the different options of backups are available.
Then how they impact each other.

Sorry, but documentation/books and training are really what you need to achieve this.

All that said, I'll try to answer your latest two questions:

1)  it may or may not.  You have to understand what is and is not possible by running in archivelog mode.

In noarchivelog mode, online redo logs are overwritten as they fill up.  Say you have 3 sets and one set fills up every 15 minutes.  You then only have 45 minutes of redo available for recovery.  After that, your last incremental will fail because Oracle doesn't know what transactions to replay as part of the recovery.

For example:
midnight, I shutdown and do an incremental and restart the database.
Users come in at 8 AM and start work.
After 45 minutes the first redolog is overwritten.
At noon the database crashes.

Where is Oracle supposed to get the transactions from 8:45 AM until noon to recover the database to how it looked at noon when the crash occurred?

2) Your least favorite answer but unfortunately, the best:  The docs.

Block change tracking:
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmbckba.htm#BRADV8125

Cumulative incrementals:
Sorry, I misused a term.  What I was actually referring to was Incrementally Updating Backups. Cumulative backups are something different but are talked about in the docs if you want to read about them.

The doc link for incrementally updating backups:
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmcncpt.htm#i1007616
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question