On-line backups related query

[Sarah23]

Hi, can some one recommend a good source of information regarding online backups and in particular restoring from an online backups?

You see I don't totally understand what goes on internally within Oracle when tablespaces are put into hot backup during an Online backup. For example how are database changes (committed & uncommitted) dealt with?... when doing a restore from an online backup will it ask for offline redo logs? etc etc

This maybe basic stuff to some of you out there but I'm still learning!!!

Thanks in advance

Sarah J

 

[SantaMufasa]

This maybe basic stuff to some of you out there but I'm still learning

Frankly, Sarah, we're all still learning...And if anyone claims that they know everthing about how Oracle works, then they would lie about other stuff, too. Additionally, Oracle's behaviours during Hot Backups are perhaps some of the most esoteric, least understood aspects of Oracle...you are not alone.


Here is a (Readers Digest) explanation of what is going on once you issue the command, "ALTER TABLESPACE <name> BEGIN BACKUP;":

First, as you probably are aware, if one simply copies Oracle's database data files without the benefit of a shutdown database or the "...BEGIN BACKUP..." feature, the copies (backups) are virtually useless due to "fractured blocks" (i.e., data blocks that are "in-flight" with changes at the time the file-copy occurs). That is why one must either cleanly shutdown the database or have the database in ARCHIVELOG mode and perform a "HOT BACKUP" (as you are wishing to do).

The clever methodology that Oracle uses for its "HOT BACKUP" to avoid "fractured blocks" during hot backups is to:
a) advise the Oracle instance that you are backing up the files of a particular tablespace, and
b) (as a result of the "...begin backup..." signaling) Oracle invokes "block protect" mode during your backup activities.

Block protect mode during backup represents the magic of hot backups:

Normally, (when you are not doing "hot backups") your Oracle server process does the following:

1) Writes a pre-change image of your data to the UNDO (Rollback) segment (in the database buffer cache),
2) Writes a post-change image directly to your database data block (in the database buffer cache)
3) Writes just the row-level changes that result from INSERTs, UPDATEs, or DELETEs to the on-line redo log buffer.

This "business-as-usual" behaviour properly preserves your data integrity in the event of the following scenarios:

1) You decide to COMMIT your changes (the changed data is already in place in your database block(s)).
2) You decide to ROLLBACK your changes (the pre-change image is in the UNDO/Rollback segment and Oracle replaces the changed data in your data blocks with the contents of the UNDO/Rollback segment.
3) A power failure or "shutdown abort" occurs either
a) before you COMMIT (and thus needs to rollback your changes) or
b) after you COMMIT, but before the DBWR (DataBase WRiter) process can write your committed change to disk,
...then upon database restart, the on-line redo log files contain both the COMMITted changes and the pointers to the applicable UNDO/Rollback segments to put the database back to its pre-change committed state prior to the abnormal end to the instance.

(This "business-as-usual" scenario, above, was to give you the background that you need to understand the magic that occurs during a hot backup.)

Since Oracle can, at any time, write either COMMITTED or UNCOMMITTED data physically to your database datafiles (which, in the case of an abnormal end to the instance, is reconciled by entries in the redo log files), we normally "don't care" whether the physical image of your database data files is COMMITTED or UNCOMMITTED data.

The problem arises, however, if we are doing a "hot backup" (i.e., doing an o/s copy of database data files) while UNCOMMITTED changes are occurring. Without Oracle's "protective magic" during hot backups, we could unwittingly be backing up "fractured blocks" (i.e., spurious images) of data.

Therefore, we now reach the explanation of the protective magic that Oracle implements during a "hot backup":

When you issue the above "...begin backup..." command for a tablespace, this slightly changes Oracle's redo logging behaviour during the time that a tablespace is in this mode. Instead of recording to the redo log buffer just the data changes that it normally does, Oracle writes the entire pre-changed image of a soon-to-change database block to the redo log buffer! This methodology absolutely prevents "fractured blocks" by preserving to the redo log buffer, every pristine, unchanged, "un-fractured" block that DML will change momentarily.

Using this technique, we don't care when our backup copy of a database data file contains a spurious, fractured block that was changing during the backup process since a pristine, untouched copy of the block resides in physically in either an on-line redo log file or in an archived redo log file, guaranteed.

So, this protective process, although it consumes more redo log space, is a small price to pay for the insurance that our hot backups preserve COMMITTED data integrity.

Once you are done copying the data files for a tablespace, we no longer want the more-expensive block-preservation process to continue, so we issue the command:

ALTER TABLESPACE <name> END BACKUP;

Does this help to explain how Oracle's clever "hot backup" strategy works?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Sarah23]

Hi Dave of Sandy, Utah, USA! (aka Mufasa)

Thanks for a very comprehensive explanation. Had to read it several times though to get my head round it!… I have a couple of DBA books but none of them seem to go into it in any great detail. At least I understand also now why (in your own words) it’s the least understood aspect of Oracle!

Just one more thing to clarify for me please. We do off-line backups once a month and on-line backups daily. To do a COMPLETE restore using the latest on-line backup, I presume the on-line tape must contain the following files?

- backup control file
- tablespace datafiles (whilst in hot backup)
- Offline redo logs generate since the START of the on-line backup

It’s really the archive log files I want to be sure of and exactly which ones. You see I recently did a Disaster Recovery Trial off-site and wasn’t able to restore the database from my on-line tape because I was being asked for archive logs during the recover database phase but none were on the tape (i.e. we do separate redo log backups). So it seemed to me that we needed to change our on-line backups to include the archive logs on the same on-line tape, but the question is which archive logs do we include that the recover database will ask for??? (I think it's ALL archive logs created since the START of the on-line backup?)

Sorry if I’m waffling, a habit of mine!… hope my question makes sense?!

Thanks again

S

 

[SantaMufasa]

Sarah,

Again, excellent questions!

Amongst the "adjustments" that I shall make to your list of "should haves" for a complete backup set are:

- all control files
- tablespace datafiles (whilst in hot backup)
- Offline redo logs generated since the START of the on-line backup
- On-line redo log files taken at the time of your last backup.

You should have (as part of your backup set) access to an un-broken chain of all archive redo log files right up until the most recent archive redo log file. Obviously, you will not have the most recent archive redo log files residing on the backup tape that you took a few days ago, but you will have the more recent archive redo log files still residing in the directory to which the Oracle ARCHive process wrote those files since your previous backup.

Also, note that the driving force behind a complete, up-to-the-moment recovery is at least one control file that is not corrupt and that the Recovery process can use as a target point until which to recover.

So, the best practice during a database recovery is to restore backup data files of only the file (or files) that are corrupt...not all of your backup file set. The Oracle Recovery process will then do a roll-forward on just the entries in the restored backup files that it needs to bring the files current with the un-corrupted database files.

Oracle, by default, will "look" for your archived redo log files in the same path to which it wrote the most recent archived redo log file. Therefore, restore any non-resident archived redo log files to the current LOG_ARCHIVE_DEST. (If Oracle says that it cannot find a needed archived redo log file, but you know it exists in another directory, you can manually enter the fully qualified path and file name of the actual location of the next archived redo log file for which it is looking, and when you press the [enter] key after you enter the "correct" path and file name, the Oracle Recovery process takes off as soon as it "sees" that the file with the header information for which it was looking is, in fact, where you said it was.

Hopefully, all of these very-obscure details are starting to become a bit clearer. You have every right (and responsibility) to be tenacious with your questions about Backup and Recovery until it becomes comfortable and successful for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Sarah23]

Sorry Dave, there was one more thing... after addressing my question above regarding required archive logs, could you also please tell me the sequence of commands to start up the database after restoring the necessary files from the On-line backup tape?

Thanks

Sarah of London, UK.

 

[SantaMufasa]

There are a couple of ways to invoke the recovery. One sequence of events is that you don't know that the database has a media corruption and you simply attempt to do a normal database startup. When Oracle throws an error regarding the corruption of one or more files, then you:

1) restore the backup copy of the specific corrupt file
2) restore (to the LOG_ARCHIVE_DEST path) the archived redo log files with date/time stamps that begin with the archived redo log file that just precedes the date/time stamp of the oldest restored backup data file
3) issue (from the SQL*Plus prompt, for example) the command:

recover database

4) monitor the recovery process as it applies the archived, and finally the on-line, redo log files.
5) when the message appears, "Recovery complete", then issue the command,

alter database open;

The other recovery scenarios are simply variations on the above scenario.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.