08 May, 2008

RMAN Consistent ("COLD" ?) Backup and Restore

The RMAN documentation differentiates between "inconsistent" and "consistent" backups on the basis of whether the database is OPEN or not (respectively) during the Backup.
The nomenclature "inconsistent backup" makes me nervous. Why not call it the good old "HOT" Backup ?

To do a consistent" backup with RMAN, the database must be mounted as RMAN needs to access and update the controlfiles. With an OS scripted backup pre-RMAN, the database was truly "COLD" -- there would be no Oracle processes running.
RMAN does not backup the Online Redo Log files. With an OS scripted backup, you had the option -- you could choose to include these files in your backup if you were careful about how you planned to use Cold Backups for Roll-Forward recoveries with ArchiveLog.

Since RMAN does not backup the Online Redo Log files, you must, perforce, OPEN RESETLOGS on a Restore. With a scripted backup, if you also included your Online Redo Log files in your backup and restore (provided that you did not plan to apply any ArchiveLogs), you could simply STARTUP the database and continue LogSequenceNumbers again. (Of course, you might be duplicating LogSequenceNumbers if the database had been active in ArchiveLog mode since the backup, so you have to be careful to distinguish the two "streams" of ArchiveLogs).


Here below is the simplest "consistent" Backup and Restore using RMAN :



C:\>rman

Recovery Manager: Release 10.2.0.3.0 - Production on Thu May 8 22:20:17 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 536870912 bytes

Fixed Size 1291652 bytes
Variable Size 297798268 bytes
Database Buffers 234881024 bytes
Redo Buffers 2899968 bytes

RMAN> backup database;

Starting backup at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=58 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\OR10G2DB\SYSTEM01.DBF
input datafile fno=00003 name=C:\OR10G2DB\SYSAUX01.DBF
input datafile fno=00002 name=F:\OR10G2DB\TEST_TBS_01.DBF
input datafile fno=00004 name=C:\OR10G2DB\USERS01.DBF
input datafile fno=00005 name=C:\OR10G2DB\EXAMPLE01.DBF
input datafile fno=00007 name=C:\OR10G2DB\UNDO.DBF
channel ORA_DISK_1: starting piece 1 at 08-MAY-08
channel ORA_DISK_1: finished piece 1 at 08-MAY-08
piece handle=C:\OR10G2DB_FLASH\OR10G2DB\BACKUPSET\2008_05_08\O1_MF_NNNDF_TAG200
0508T222041_42631X6C_.BKP tag=TAG20080508T222041 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:47
Finished backup at 08-MAY-08

Starting Control File and SPFILE Autobackup at 08-MAY-08
piece handle=C:\OR10G2DB_FLASH\OR10G2DB\AUTOBACKUP\2008_05_08\O1_MF_S_654214767
426358M1_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAY-08

RMAN>
RMAN> shutdown

database dismounted
Oracle instance shut down

RMAN>

******************* BACKUP COMPLETED *****************
******************************************************

========= database files deleted ====================
++++++++++++++++++++++++++++++++++++++++++++++++++++++
======================================================



C:\>rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Thu May 8 22:32:07 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area 536870912 bytes

Fixed Size 1291652 bytes
Variable Size 301992572 bytes
Database Buffers 230686720 bytes
Redo Buffers 2899968 bytes

RMAN>
RMAN> restore controlfile from autobackup;

Starting restore at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=57 devtype=DISK

recovery area destination: \OR10G2DB_FLASH
database name (or database unique name) used for search: OR10G2DB
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\OR10G2DB_FLASH\OR10G2DB\AUTOBACKUP\2008
_05_08\O1_MF_S_654214767_426358M1_.BKP
channel ORA_DISK_1: control file restore from autobackup complete
output filename=C:\OR10G2DB\CONTROL01.CTL
output filename=C:\OR10G2DB\CONTROL02.CTL
output filename=C:\OR10G2DB\CONTROL03.CTL
Finished restore at 08-MAY-08

RMAN>
RMAN> restore database;

Starting restore at 08-MAY-08
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/08/2008 22:33:28
ORA-01507: database not mounted

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>
RMAN> restore database;

Starting restore at 08-MAY-08
Starting implicit crosscheck backup at 08-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=57 devtype=DISK
Crosschecked 45 objects
Finished implicit crosscheck backup at 08-MAY-08

Starting implicit crosscheck copy at 08-MAY-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 08-MAY-08

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\OR10G2DB_FLASH\OR10G2DB\AUTOBACKUP\2008_05_08\O1_MF_S_654214767_42
6358M1_.BKP

using channel ORA_DISK_1

skipping datafile 2; already restored to file F:\OR10G2DB\TEST_TBS_01.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\OR10G2DB\SYSTEM01.DBF
restoring datafile 00003 to C:\OR10G2DB\SYSAUX01.DBF
restoring datafile 00004 to C:\OR10G2DB\USERS01.DBF
restoring datafile 00005 to C:\OR10G2DB\EXAMPLE01.DBF
restoring datafile 00007 to C:\OR10G2DB\UNDO.DBF
channel ORA_DISK_1: reading from backup piece C:\OR10G2DB_FLASH\OR10G2DB\BACKUPS
ET\2008_05_08\O1_MF_NNNDF_TAG20080508T222041_42631X6C_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\OR10G2DB_FLASH\OR10G2DB\BACKUPSET\2008_05_08\O1_MF_NNNDF_TAG2008
0508T222041_42631X6C_.BKP tag=TAG20080508T222041
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 08-MAY-08

RMAN>
RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 05/08/2008 22:36:48
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN>
RMAN> alter database open resetlogs;

database opened

RMAN>



The OPEN RESETLOGS is necessary because RMAN does not backup and restore
Online Redo Logs.

14 comments:

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

Two questions for discussion:

1. Was your db in archivelog mode when you did the RMAN offline backup? So it requires a recovery?
A non-archivelog mode DB backup normally didn't require recovery, only restore can result in db open.

I am using control file for RMAN and db is in archivelog mode. I don't know if it possible to have a kind of image backups through RMAN in my conditions. I like a complete cold backup in RMAN which won't involve control file. Because the control file keeps track of what is going on while the backup is running, and when you do restore, it involves the control file also. That case, control file will capture all the backup/restore activity, which is what I try to avoid.

2. How can we restore db to a previous (not the latest) cold backup, assuming we can have multiple offline backups available?

Thanks.
Kevin

Hemant K Chitale said...

1a. "Was your db in archivelog mode when you did the RMAN offline backup?" It doesn't matter if the database was in archivelog or noarchivelog mode. I was restoring from a "consistent" backup (one taken when the database is *not* OPEN).
1b. "So it requires a recovery?". No. I did a RESTORE and then attempted an OPEN. No "recovery" was required (or even desired) as it was a consistent backup and I didn't need to apply archivelogs.

The point of my post is that RMAN does not backup Online Redo Log files and since I did not have Online Redo Log files available, I had to OPEN RESETLOGS. Had I used a "scripted" (aka "User Managed") backup for Cold Backups, I might well have included Online Redo Logs in the backup and restore and the OPEN would have succeeded without a RESETLOGS.

1c. "That case, control file will capture all the backup/restore activity, which is what I try to avoid." WHY ? RMAN only updates information about backups into the Control File. That can be useful.
For example, you could restore a controlfile to another server / instance and then query it to find out what archivelogs were available -- for example if you need to restore and recover to a particular point in time, the controlfile can tell you which archivelogs were available. (V$ARCHIVED_LOG is queryable from a controlfile in MOUNT state).


2. "How can we restore db to a previous (not the latest) cold backup, assuming we can have multiple offline backups available?" Restore the corresponding controlfile first before you restore the database. Your daily backup strategy should be database+archivelogs+controlfile (particularly as you do not use a Repository).
You WILL need the controlfile to be able to restore the database.
Since the controlfile retains information about backups only for a limited duration (eg controlfile_record_keep_time defaults to 7 days), you require the corresponding controlfile.
Say, you need to restore the database as of 31-Mar for "investigation or audit". You need to get the controlfile backup of 31-Mar or 01-April restored before you can restore the database (a restore database command would not know which backupsets contain the datafile backups until and unless it can query the controlfile !).

Anonymous said...

Hemant, thanks a lot for providing your comments and advices promptly.

I need a little more your comments:

I understand the corresponding controlfile is needed to put the db back to the specific one before restore starts. I would like to know how in detail, such as how to find which controlfile to use, from where, etc. Is incarnation info helpful?

Actually, here are the objectives I got from co-worker:
1. Find out a way to have controlfile backup, so I can re-create the controlfile to get rid of the incarnation information.
2. Find out a way to have a snaptshot of the datafile, so I don't need to keep track of the archivelogs.

But if the archivelog doesn't bother us, the current backup method still OK. And for the objective 1, if I can use ASM alias to manage the control file, it is OK too.

What I means is that currently, I use controlfile alias in the parameter file (like +DATA/fnprd/fnprd_control01.ctl, +FRA/fnprd/fnprd_control02.ctl). I can change the parameter file to point new alias, so that way I create new controlfile without losing the old one. If that works, I have a way to accompliish task one.

Do you think these two objectives making sense? Thanks again.

Hemant K Chitale said...

I really don't understand "Find out a way to have a snaptshot of the datafile, so I don't need to keep track of the archivelogs."

If you are restoring the database back to the same server and location why do you need to "keep" the pre-restoration controlfile ? If you want to revert your database, might as well backup the database (ie datafiles). The controlfile can be backed up and can also be recreated.

Chris Ruel said...

Thanks for this post Hemant. I was missing the restore of the controlfile autobackup and your post help me get past that. Interesting note, I first did the restore of the cold backup, then I could not open the database resetlogs or noresetlogs. I restored the controlfile autobackup as your post suggessted, and I was able to open resetlogs without re-restoring the datafiles.

Hemant K Chitale said...

Good to know that my blog helped. !

Anonymous said...

It's amazing how many people still don't back up but they are starting to catch on...

Damir Vadas said...

Hi!
"controlfile from autobackup" is something that I'd liek to avoid. In a case of ASM I want to make consistent backup (with controlfile as well) outside from FLASH_RECOVERY AREA. let us say that those files should be saved on some additional OCFS2 mounted shared disk, how to backup and restore then?
Example please
THX
I mean I want to have all needed files on that OCFS2 mount point (outside ASM storage)...

THX

Hemant K Chitale said...

The RESTORE CONTROLFILE command can be used with a FROM location

where location is a BackupPiece that contains a ControlFile backup -- even if it is a BackupPiece of a BACKUP DATABASE Backupset or BACKUP TABLESPACE SYSTEM BackupSet.

Thus you would
RESTORE CONTROLFILE FROM /backupsdir/rman/....bkp

Amer said...

as u said i can user FROM instruction to restore control file
RESTORE CONTROLFILE FROM /backupsdir/rman/....bkp

In the same way can i use restore DATABASE FROM if i want to restore it from different location..assuming i dont have same directory structure available at test server.

Hemant K Chitale said...

Amer,

RESTORE can also use FROM TAG 'tagname' but this requires that the BackupSet for that tag be cataloged (i.e. be known to RMAN). Since RMAN defaults to restoring the latest backup, FROM TAG is used to restore from a backup older than the latest backup.
If your backuppieces are NOT in the expected location OR you are using a NOCATALOG method and the backuppieces were created after the controlfile backup, you need to first CATALOG START WITH to catalog the backuppieces -- i.e. update the controlfile with knowledge of these backuppieces -- before you can RESTORE from them.

Unknown said...

Hi Hemanth,

for doing RMAN cold backup we put the database in mount state.

1. what is the difference between RMAN COLD BACKUP and Normal COLD BACKUP(OS level copy) ?
2. what are the advantages of RMAN COLD BACKUP?

Hemant K Chitale said...

Damodhar,
An RMAN Backup, by default, creates BackupSet Backups. These can be smaller than OS backups because of Null Block Compression and Unused Block Compression (and, also, eliminating unused Undo blocks). Moreover, a Compression algorithm can also be applied with BACKUP AS COMPRESSED BACKUPSET.
RMAN Backups are registered in the Controlfile (and the Catalog if you use one). Thus, Oracle keeps track of the backups, knows the Checkpoint SCNs of the datafiles and can identify the right backup to use for a Restore.

Hemant