Restore and Recovery using RMAN

[Kaustubh Kane]

Hi,

I am working on a system where we restore the Oracle 9i database using the RMAN utility.

For restore database we issue:
RESTORE DATABASE;

After the restore we Recover database using the RMAN command:
RECOVER DATABASE;

From the help docs I have read the RECOVER DATABASE will update the datafiles using redo records i.e. records of changes made to the database after the backup was taken.

But at times it is expected that the database be restored to the state it was when the backup was taken i.e. we do not need RECOVER DATABASE.

In such a situation I am thinking of just executing the RESTORE DATABASE and skipping the RECOVER DATABASE step.
Is it advisable to do so? This is DBA specific and I am new to this area hence I need your opinion. Please let me know.

Regards,
Kaustubh Kane.

[Peter Godron]

Hi,
and welcome to the forums !

Not sure I understand. Why would you want to restore your database to the last backup, unless it is to create a known test environment ?

If you only restore without adding the entries from the log files, you would need to clear down the log files, or allow them to be overwritten.

Oracle experts to the front, please.

Please read:
http://66.34.90.71/ITRCForumsEtiquette/after.html

Please also read:
http://forums1.itrc.hp.com/service/forums/helptips.do?#33 on how to reward any useful answers given to your questions.

[Yogeeraj_1]

Hi Kaustubh,

What's the use of doing RMAN backup then?

If this is your requirement, just shutdown the database before you do your backup and then when you have to recover, you just have to recover the files and lose all the transactions that might have been done since your backup and the time you are doing the recovery.

Not sure about what you are trying to archive here.

The ideal method of what you are trying to do is to flashback the database.

Please clarify on your main objective.

kind regards
yogeeraj

[Julio Yamawaki]

Hi,

If you want to just restore the database without recovering, you MUST use datafile copy when backup is done.
Then you just restore the datafiles and they will be ready to startup.
If you make a normal BACKUP, you allways have to RECOVER the database (complete or incomplete). In this case, you will be able to recover to a point in time (this time can be when you make this backup).


Regards,

[Peter Godron]

Hi,
if this problem is resolved, could you please award points to useful answers and then close the thread, summarising the solution used.
http://forums1.itrc.hp.com/service/forums/helptips.do?#40

[Kaustubh Kane]

Julio,
To RECOVER the database using point in time recovery to the time the backup was taken is almost not to perform any RECOVERY.

So can I skip the REOVER DATABSE step and then open the database with RESET LOGS option.
For e.g.

Please let me know.

Regards,
Kaustubh Kane.

[Julio Yamawaki]

Hi,

Normally, when you restore a backup, you will restore datafiles and control files (and, of course archivelogs, but it's not teh case).
After restoring, you will need to open the database (this will be in MOUNT state until this moment) with resetlogs because of the redologs, in order for these to stay in the same incarnation of the rest of the database files.
If you are not using raw devices or ASM, you'd better make a full backup with the database closed. In this case, you just restore backup and startup the database.

Regards,

[Kaustubh Kane]

I tried the Incomplete Recovery (Point in Time Incomplete Recovery) after doing database restore. But it failed.

I did the following:
Step1: RESTORE DATABSE.

Step2: SQL 'ALTER SESSION SET NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"';
SET UNTIL TIME 'Apr 23 2007 11:10:41';
RECOVER DATABASE;

The above gave an error while performing the Incomplete Recovery. The error is as follows:
RMAN-03002: failure of recover command at 04/23/2007 11:31:58
RMAN-06555: datafile 1 must be restored from backup created before 23-APR-07

Please advice me on this.

Regards,
Kaustubh Kane

[Julio Yamawaki]

Hi,

See this error message:
RMAN-06555: datafile 1 must be restored from backup created before 23-APR-07
It means that this datafile was created AFTER 23-APR-07, in order to perform a recover, you MUST have a datafile BEFORE the target date.

Regards,

[Kaustubh Kane]

Hi,
The database backup which we perform is a level 0 cumulative backup. From the info I recived this is equivalent to full backup. Hence all the datafiles should be present in the backup image. Is this correct?

When I would restore this image then all the datafiles should get restored in the RESTORE DATABASE step. (i.e. also the datafile which gave me error).

Is this correct, please confirm my understanding?

The Recovery I tried was Incomplete (Point in Time) and time specified was the time when the backup was taken.

In this case why should it try to recover the datafile 1.

Waiting for your kind reply.

Regards,
Kaustubh

[Yogeeraj_1]

hi,

The level 0 backup should normally contain all the required data files.

If you were running oracle 10g, you could have verified your RMAN backup by doing a test recovery (without actually recovering any data files) using the following command:

restore validate database;

kind regards
yogeeraj

[Julio Yamawaki]

Hi,

I think you are trying to simulate a disaster in your database, but, you are trying to restore rman backup into your last datafiles. These datafiles are up-to-date, so, probably rman is not restoring any block and when your try to recover, rman notice that this target date is older than datafiles' date. It would be better to remove some datafiles (for testing purposes, it's better to drop only data tablespaces - not system) and try to restore/recover this one.
Remember that, if you are in a real disaster situation, you probably lost your entire machine.

Regards,

[Kaustubh Kane]

To take the database back to the state when the backup was done, I am removing all the datafiles, control files and redo log files under u02, u03, u04, u05, u06, u07 and then performing a restore. It works fine, the changes made to the database after the backup was taken are not seen if restore is done in such a manner.

But I need to know if would have any side effects or serious impacts.

Please let me know.

[Julio Yamawaki]

Hi,

Now you made a restore like in a disaster.
Well, to recover until a SCS or a date, you must recover this until ...
Please, let me know what commands are you using and also I'd like to know what oracle version are you using.

Regards,

[Kaustubh Kane]

Hi Julio,
Earlier I had tried the Incomplte Recovery (Point in time recovery). But it failed. Please find the details in my message dated Apr 23, 2007 06:12:44 GMT in this chain above. Please also find the Incomplete recovery commands in the same message.

The Oracle version is Oracle 9i and the release is 9.2.0.7.0.

About deleting database file:
I just manually removed the files under u02 to u07 and then restored using RMAN tool.
The restore command is:
RESTORE DATABASE

The recover database command is:
RECOVER DATABASE

As the Incomplete recovery did not work for me I used the procedure of deleting all the files u02 to u07 and then restoring to bring the database back to the state it when backup was taken (i.e. to not have the changes made to the database after the backup was taken)
Is this wrong?

Please let me know.

[Julio Yamawaki]

Hi,

Can you post what commands you used to backup the whole database?
Let's start from the begning in order to clarify all your doubts.

Regards,

[Kaustubh Kane]

Hi Julio,

For performing database backup using RMAN I use the following commands:

Step 1: Backup Database
ALLOCATE CHANNEL C1 TYPE DISK;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP INCREMENTAL LEVEL=0 CUMULATIVE format '$dir/level0' DATABASE include curr
ent controlfile for standby FORCE;
BACKUP CURRENT CONTROLFILE format '$dir/control_primary' FORCE;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP ARCHIVELOG ALL format '$dir/archivelogs_%s_%t';

Step 2: Validate Restore
RESTORE DATABASE VALIDATE;

Regards,
Kaustubh Kane.

[Julio Yamawaki]

Hi,

Your backup is perfect.
Now let's recover a tablespace:
1. Shutdown database
2. Remove a datafile (users or data)
3. Startup database - you will get ORA-1157 and ORA-1110, but database will mount
4. Call rman
5. Execute this sequence in RMAN:
RMAN> SQL 'ALTER DATABASE DATAFILE n OFFLINE'; => n is the datafile number
RMAN> OPEN DATABASE;
RMAN> RESTORE DATAFILE n;
RMAN> RECOVER DATAFILE n; => this will make a complete recover because you have all archivelogs and redologs
RMAN> SQL 'ALTER DATABASE DATAFILE n ONLINE';

This is the procedure for a complete recovery;
Try this one, after that, if you want a incomplete recovery procedure, I will send this for you.

Regards,