Solved

Need some guidance with backup/restore strategy

Posted on 2013-01-30
3
379 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
  • 2
3 Comments
 
LVL 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now