12 September, 2009

RMAN can identify and catalog / use ArchiveLogs automagically

When doing RESTORE DATABASE and RECOVER DATABASE using RMAN, you may find that RMAN automagically

a) CATALOGs files (BackupPieces and even ArchiveLogs) in the FRA, if you are using an FRA
b) Reads ArchiveLogs from the destination identified by log_archive_dest_1 if you use this instead of an FRA

Knowledge of this can also be used when you are restoring to another server where you haven't created an FRA. Copy/Restore your ArchiveLogs (using non-RMAN methods) to any alternate log_archive_dest directory, designate it as log_archive_dest_1 in the parameter file of the database you are creating (i.e. pretending to restore and recover on the new server) and let RMAN identify the files.

I had just posted an example of this in this forums thread.

.
.
.

18 comments:

Yogi said...

Hi Sir,

I have a question.

CHECKPOINT_CHANGE# ---- when this no get incremented on a log switch or at checkpoint ?

I tried to discuss it in oracle forum too.

http://forums.oracle.com/forums/thread.jspa?threadID=966985&tstart=0

Please advise.

Hemant K Chitale said...

It is at a Checkpoint.

A Log switch can initiate a checkpoint. But other actions (ALTER SYSTEM, ALTER ... BEGIN BACKUP, ALTER .. OFFLINE ) can also cause a checkpoint.

Hemant

Rahul said...

Hi,

Nice and interesting article. Thanks a lot.

Thanks and regards,
Rahul Jain
http://www.dbametrix.com

Scofield said...

Dear Hemant;
First of all, I would like to thank you for helping me with my rman questions.
To be honest, I really learned a lot from you.

I have one more question, that I`ve been thinking for the last couple of days.
I really appreciate if you clear my doubts.


I have a production database(orcl) and a copy of production database(orcl2) which is refreshed every night.
Everyday we take hot backup with (alter tablespace begin backup) and perform flash copy of the
datafiles to orcl2 and open resetlogs.

Yesterday, this operation failed and I even couldnt manage to open orcl2.
The thing is even the flash copy failed, I couldnt even open the database normally.(old version)

When I try to recover with latest controlfile it just says:

SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01190: controlfile or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: '...'



When I recreate the controlfile from controltrace, and perform recovery in the alertlog it says:

WARNING! Recovering data file ... from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command

Also it is requesting archivelog sequence _0000000010_1.arc which was generated in April?
However my current log seq is _0000019202_1.arc
I dont understand why it requests very very very old archivelog... It doesnt make sense



When I check datafile headers:

I see:

ERROR FUZ
----------------------------------------------------------------- ---
WRONG RESETLOGS YES


SQL> select CHECKPOINT_CHANGE# from v$datafile where rownum<3;

CHECKPOINT_CHANGE#
------------------
4446922742
4446922742

SQL> select CHECKPOINT_CHANGE# from v$datafile_header where rownum<3;

CHECKPOINT_CHANGE#
------------------
4443857329
4443857329




and in orcl

SQL> select RESETLOGS_CHANGE# from v$datafile_header where rownum<2;

RESETLOGS_CHANGE#
-----------------
3250834775

Flash copy one:

SQL> select RESETLOGS_CHANGE# from v$datafile_header where rownum<2;

RESETLOGS_CHANGE#
-----------------
4443659941



I think this shows that flash copy didnt happen, but why I cant open the database?
As I showed you above, I have also trace to recreate controlfile from trace

Hemant K Chitale said...

Scofield,

Your flashcopy backup should be properly issuing a BEGIN BACKUP before the flashcopy is created. Apparently, in this case, a BEGIN BACKUP was not issued.
That is why the RECOVER finds the datafiles to be fuzzy.
I do not understand which database you are referring to in the line " I couldnt even open the database normally.(old version)" Which is the "old version" ?

As for "Also it is requesting archivelog sequence _0000000010_1.arc", the RECOVER command would also state the SCN and the date-time of the transactions being attempted to be recovered. Do NOT go by Sequence#s because Sequence Numbers ARE reset to 0 when a RESETLOGS is issued. Look at the SCN and Date-Time.

The RESETLOGS_CHANGE# is the last time a Resetlogs was issued for *this* database. Since your production database "orcl" had undergone a RESETLOGS a long time ago, it has a very much older RESETLOGS_CHANGE# (In your place I would ask why a Production database had to undergo a RESETLOGS).

However, the RSETLOGS_CHANGE# in your Flashcopy controlfile obviously means that the controlfile isn't one that came from the latest flash backup. Apparently it wasn't refreshed but is a copy of the controlfile in the flash area (ie orcl2) without having been overwritten at all.


Your latest flash copy was incorrectly and partially created :
a. A BEGIN BACKUP wasn't issued
b. The controlfile is inconsistent with the expected database files

I wouldn't expect such a database to be successful at RECOVER.

Hemant K Chitale

Scofield said...

Thanks for clearing my doubts sir.

Scofield said...

Respected Mr Hemant;

shutdown abort;

startup mount;

sys@XE> recover database until cancel;
ORA-00279: change 3660170 generated at 12/10/2009 13:53:00 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2009_12_12\O1_MF_1_3_%U_.ARC
ORA-00280: change 3660170 for thread 1 is in sequence #3


Specify log: {=suggested | filename | AUTO | CANCEL}

auto
ORA-00308: cannot open archived log
'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2009_12_12\O1_MF_1_3_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-00308: cannot open archived log
'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2009_12_12\O1_MF_1_3_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF'



sys@XE> recover database;
Media recovery complete.


Even though I type "auto", Why "recover database until cancel" doesnt detect the redolog?

Also why this statement doesnt work in rman?

Scofield said...

Respected Mr Hemant;

shutdown abort;

startup mount;

sys@XE> recover database until cancel;
ORA-00279: change 3660170 generated at 12/10/2009 13:53:00 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2009_12_12\O1_MF_1_3_%U_.ARC
ORA-00280: change 3660170 for thread 1 is in sequence #3


Specify log: {=suggested | filename | AUTO | CANCEL}

auto
ORA-00308: cannot open archived log
'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2009_12_12\O1_MF_1_3_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-00308: cannot open archived log
'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2009_12_12\O1_MF_1_3_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF'



sys@XE> recover database;
Media recovery complete.


Even though I type "auto", Why "recover database until cancel" doesnt detect the redolog?

Also why this statement doesnt work in rman?

Hemant K Chitale said...

AUTO generates expected ArchiveLog names based on log_archive_dest and log_archive_format.

%U isn't what I would expect to be part of a file name at all !

Is that file "O1_MF_1_3_%U_.ARC" really present ?
Also, AUTO would keep going forward until it cannot find a file -- it doesn't know when to stop.

Scofield said...

Hi

I deleted all archivelogs and tested the above scenerio.

Since I issue "shurtdown abort", only instance recovery should be done which is from online redologs.(no need for archivelog)

so, when I type "auto", why it doesnt apply redologs?
however
As shown above
When I only type "recover database", it applies redologs.

Hemant K Chitale said...

A "STARTUP OPEN" will do an Instance Recovery when it finds that the datafiles are not consistent and were fuzzy -- ie were last open.
A "RECOVER" command is an explicit instruction to do a Media Recovery.

If you have merely shutdown abort the database instance (and no datafiles were in backup mode at that time !), a STARTUP is sufficient for Oracle to do an automatic Instance Recovery. Your RECOVER command is an instruction to do a Media Recovery. It is quite likely that these would require Online Redo Logs that haven't been archived yet. Since a RECOVER DATABASE UNTIL CANCEL is an instruction to do an Incomplete Recovery, Oracle attempts to log for ArchiveLogs to apply.
In your case a RECOVER DATABASE alone is an instruction to do a Complete Recovery so Oracle also checks the Online Redo Logs and applies them.

Hemant

John said...

Hi Hemant;

shutdown abort;
startup mount;

idle> recover database using backup controlfile until cancel;
ORA-00279: change 3678974 generated at 12/12/2009 13:10:32 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2009_12_12\O1_MF_1_1_%U_.ARC
ORA-00280: change 3678974 for thread 1 is in sequence #1


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF'


ORA-01112: media recovery not started



When I check alertlog:
I see:
ALTER DATABASE RECOVER database using backup controlfile until cancel
Sat Dec 12 13:12:45 2009
Media Recovery Start
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
ORA-279 signalled during: ALTER DATABASE RECOVER database using backup controlfile until cancel ...
Sat Dec 12 13:12:47 2009



Why do I see these warnings once I issue "recover database using backup controlfile" ?
Whats the reason for this?

Hemant K Chitale said...

John,
Please *read* my previous response again. In this scenario (SHUTDOWN ABORT, STARTUP MOUNT), a STARTUP OPEN or AN ALTER DATABASE OPEN is sufficient. The datafiles are fuzzy -- they haven't been restored from a backup. This is an Instance Recovery scenario.

Hemant

john said...

Hemant;

Why do I see above fuzzy errors once I issue "recover backup controlfile" ?

If I again shutdown abort and run "recover database", I dont see any fuzzy erros in alert log.

This is what I was trying to ask

Hemant K Chitale said...

John,
Your datafiles have got updated by the first RECOVER.


Hemant

john said...

Hemant;

In case2, when I check the alert logs, I see
"WARNING! Recovering data file 1 from a fuzzy file. If not the current file"
I want to know why oracle gives this warning in case2 and not in case1.


1-)
shutdown abort;
startup mount
recover database;


2-)
shutdown abort
startup mount;
recover database using backup controlfile.

john said...

Hemant;

In case2, when I check the alert logs, I see
"WARNING! Recovering data file 1 from a fuzzy file. If not the current file"
I want to know why oracle gives this warning in case2 and not in case1.


1-)
shutdown abort;
startup mount
recover database;


2-)
shutdown abort
startup mount;
recover database using backup controlfile.

Unknown said...

Hi , Thank you so much for this precious site , you can find more information about ORACLE's RMAN Technology at www.rmanbackup.com