Solved

Need some guidance with backup/restore strategy

Posted on 2013-01-30
3
384 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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

730 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