12 April, 2009

RMAN Backup and Recovery for Loss of ALL files

In response to this forums thread about backup and recovery, I am posting the RMAN Backup and Recovery scenario for the loss of *ALL* files (Database, RedoLogs, ArchiveLogs and spfile).
As long as the last Backup included
a. Database
b. ArchiveLogs generated from the beginning of the Database Backup to the first ArchiveLog after the end of the Database Backup -- having ArchiveLogs after that point is a bonus
c. ControlFile
d. spfile

Note : The controlfile is also always automatically included in backups that run for
Database
SYSTEM Tablespace
Change of database structure (if CONTROLFILE AUTOBACKUP ON is configured)


In this scenario, the Database Backup completes at (Archive)Log Sequence 8, while Log Sequence 9 is the additional file generated by the 'alter system switch logfile' command. (The BACKUP DATABASE had actually also issued it's own switch logfile for (Archive)Log Sequence 8 so Sequence 9 is not, strictly, necessary.)

In the RESTORE sequence, in order to restore the SPFILE, I must STARTUP FORCE NOMOUNT. This causes RMAN to use a "DUMMY" parameter file. This allows me to restore the actual SPFILE but causes the controlfile to be restored to $ORACLE_HOME/dbs and errors on the database name when I try to MOUNT with the controlfile. After I restore the SPFILE, I must SHUTDOWN and STARTUP NOMOUNT again so that I can restore the controlfile to the correct (target) locations.

In the RECOVER sequence, Oracle (RMAN) automatically determines that this is a BACKUP CONTROLFILE. (If I were using the SQLPLUS command-line, I would have to explicitly specify "USING BACKUP CONTROLFILE" in my RECOVER command).
The Recovery halts when attempting to apply Sequence 10, which does not exist.
Note, however, how Oracle detected the BackupSets containing ArchiveLogs 8 and 9 and restored the files. (It used the information in the controlfile that was an autobackup after the ArchiveLog backup).
After the Recovery seemingly failing at Sequence 10, I am able to issue an OPEN RESETLOGS because I know that I have done a valid INCOMPLETE RECOVERY. (ie, all Database Files Recovered upto the last available ArchiveLog but without the Online Redo Logs being available).

Without further ado, here is the sequence of steps : (I start with the BACKUP done before I remove and crash the database)


ora10204>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Apr 12 22:53:11 2009

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

connected to target database: ORT24FS (DBID=4137213278)

RMAN> backup database;

Starting backup at 12-APR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/oracle_fs/Databases/ORT24FS/users01.dbf
input datafile fno=00003 name=/oracle_fs/Databases/ORT24FS/sysaux01.dbf
input datafile fno=00008 name=/oracle_fs/Databases/ORT24FS/mssm01.dbf
channel ORA_DISK_1: starting piece 1 at 12-APR-09
channel ORA_DISK_2: starting full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle_fs/Databases/ORT24FS/system01.dbf
input datafile fno=00007 name=/oracle_fs/Databases/ORT24FS/assm01.dbf
input datafile fno=00005 name=/oracle_fs/Databases/ORT24FS/example01.dbf
input datafile fno=00006 name=/oracle_fs/Databases/ORT24FS/undotbs.dbf
channel ORA_DISK_2: starting piece 1 at 12-APR-09
channel ORA_DISK_2: finished piece 1 at 12-APR-09
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2009_04_12/o1_mf_nnndf_TAG20090412T225324_4y4037dl_.bkp tag=TAG20090412T225324 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: finished piece 1 at 12-APR-09
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2009_04_12/o1_mf_nnndf_TAG20090412T225324_4y4038mp_.bkp tag=TAG20090412T225324 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:41
Finished backup at 12-APR-09

Starting Control File and SPFILE Autobackup at 12-APR-09
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/2009_04_12/o1_mf_s_684024906_4y406c2k_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-APR-09

RMAN> sql 'alter system archive log all';

sql statement: alter system archive log all
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 04/12/2009 22:56:35
RMAN-11003: failure during parse/execution of SQL statement: alter system archive log all
ORA-00271: there are no logs that need archiving

RMAN> sql 'alter system switch logfile ';

sql statement: alter system switch logfile

RMAN> backup archivelog all;

Starting backup at 12-APR-09
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=361 stamp=684022751
input archive log thread=1 sequence=8 recid=362 stamp=684025008
channel ORA_DISK_1: starting piece 1 at 12-APR-09
channel ORA_DISK_2: starting archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=9 recid=363 stamp=684025017
channel ORA_DISK_2: starting piece 1 at 12-APR-09
channel ORA_DISK_2: finished piece 1 at 12-APR-09
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2009_04_12/o1_mf_annnn_TAG20090412T225657_4y409vqv_.bkp tag=TAG20090412T225657 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: finished piece 1 at 12-APR-09
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2009_04_12/o1_mf_annnn_TAG20090412T225657_4y409vod_.bkp tag=TAG20090412T225657 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 12-APR-09

Starting Control File and SPFILE Autobackup at 12-APR-09
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/2009_04_12/o1_mf_s_684025021_4y409ydg_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-APR-09

RMAN> exit


Recovery Manager complete.
ora10204>
ora10204>pwd
/oracle_fs/Databases/ORT24FS
ora10204>ls
assm01.dbf control02.ctl example01.dbf red01.dbf redo04.dbf sysaux01.dbf temp_01.dbf undotbs.dbf
control01.ctl control03.ctl mssm01.dbf redo03.dbf redo05.dbf system01.dbf temp01.dbf users01.dbf
ora10204>pwd
/oracle_fs/Databases/ORT24FS
ora10204>rm *
ora10204>cd /oracle_fs/Arch*/*T24*
ora10204>ls
1_7_682469351.dbf 1_8_682469351.dbf 1_9_682469351.dbf
ora10204>rm *
ora10204>sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 12 22:59:19 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name: / as sysdba
sh
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> utdown abort;
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ora10204>
ora10204>rm $ORACLE_HOME/dbs/*ORT24FS.ora
ora10204>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Apr 12 23:07:28 2009

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

connected to target database (not started)

RMAN> SET DBID=4137213278;

executing command: SET DBID

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle_fs/ora10204/product/10.2.0.4/dbs/initORT24FS.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area 159383552 bytes

Fixed Size 2082400 bytes
Variable Size 67111328 bytes
Database Buffers 83886080 bytes
Redo Buffers 6303744 bytes

RMAN> restore spfile from '/oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/2009_04_12/o1_mf_s_684025021_4y409ydg_.bkp';

Starting restore at 12-APR-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/2009_04_12/o1_mf_s_684025021_4y409ydg_.bkp
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 12-APR-09

RMAN> alter database mount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/12/2009 23:09:34
ORA-00205: error in identifying control file, check alert log for more info

RMAN> restore controlfile from '/oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/2009_04_12/o1_mf_s_684025021_4y409ydg_.bkp';

Starting restore at 12-APR-09
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/oracle_fs/ora10204/product/10.2.0.4/dbs/cntrlORT24FS.dbf
Finished restore at 12-APR-09

RMAN> alter database mount;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 04/12/2009 23:10:06
ORA-01103: database name 'ORT24FS' in control file is not 'DUMMY'

RMAN> shutdown

Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/12/2009 23:11:06
ORA-00205: error in identifying control file, check alert log for more info

RMAN> startup nomount;

database is already started

RMAN> restore controlfile from '/oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/2009_04_12/o1_mf_s_684025021_4y409ydg_.bkp';

Starting restore at 12-APR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/oracle_fs/Databases/ORT24FS/control01.ctl
output filename=/oracle_fs/Databases/ORT24FS/control02.ctl
output filename=/oracle_fs/Databases/ORT24FS/control03.ctl
Finished restore at 12-APR-09

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 12-APR-09
Starting implicit crosscheck backup at 12-APR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK
Crosschecked 35 objects
Crosschecked 18 objects
Finished implicit crosscheck backup at 12-APR-09

Starting implicit crosscheck copy at 12-APR-09
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 12-APR-09

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

List of Cataloged Files
=======================
File Name: /oracle_fs/FRAs/ORT24FS/ORT24FS/autobackup/2009_04_12/o1_mf_s_684025021_4y409ydg_.bkp

using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oracle_fs/Databases/ORT24FS/system01.dbf
restoring datafile 00005 to /oracle_fs/Databases/ORT24FS/example01.dbf
restoring datafile 00006 to /oracle_fs/Databases/ORT24FS/undotbs.dbf
restoring datafile 00007 to /oracle_fs/Databases/ORT24FS/assm01.dbf
channel ORA_DISK_1: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2009_04_12/o1_mf_nnndf_TAG20090412T225324_4y4037dl_.bkp
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /oracle_fs/Databases/ORT24FS/sysaux01.dbf
restoring datafile 00004 to /oracle_fs/Databases/ORT24FS/users01.dbf
restoring datafile 00008 to /oracle_fs/Databases/ORT24FS/mssm01.dbf
channel ORA_DISK_2: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2009_04_12/o1_mf_nnndf_TAG20090412T225324_4y4038mp_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2009_04_12/o1_mf_nnndf_TAG20090412T225324_4y4037dl_.bkp tag=TAG20090412T225324
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
channel ORA_DISK_2: restored backup piece 1
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2009_04_12/o1_mf_nnndf_TAG20090412T225324_4y4038mp_.bkp tag=TAG20090412T225324
channel ORA_DISK_2: restore complete, elapsed time: 00:01:51
Finished restore at 12-APR-09

RMAN> recover database;

Starting recover at 12-APR-09
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_2: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=9
channel ORA_DISK_1: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2009_04_12/o1_mf_annnn_TAG20090412T225657_4y409vqv_.bkp
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=8
channel ORA_DISK_2: reading from backup piece /oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2009_04_12/o1_mf_annnn_TAG20090412T225657_4y409vod_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2009_04_12/o1_mf_annnn_TAG20090412T225657_4y409vqv_.bkp tag=TAG20090412T225657
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_2: restored backup piece 1
piece handle=/oracle_fs/FRAs/ORT24FS/ORT24FS/backupset/2009_04_12/o1_mf_annnn_TAG20090412T225657_4y409vod_.bkp tag=TAG20090412T225657
channel ORA_DISK_2: restore complete, elapsed time: 00:00:05
archive log filename=/oracle_fs/ArchiveLogs/ORT24FS/1_8_682469351.dbf thread=1 sequence=8
archive log filename=/oracle_fs/ArchiveLogs/ORT24FS/1_9_682469351.dbf thread=1 sequence=9
unable to find archive log
archive log thread=1 sequence=10
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/12/2009 23:16:51
RMAN-06054: media recovery requesting unknown log: thread 1 seq 10 lowscn 108150439

RMAN> alter database open resetlogs;

database opened

RMAN> exit


Recovery Manager complete.
ora10204>cd $ORACLE_HOME/dbs
ora10204>ls -l spfileORT24FS.ora
-rw-r----- 1 ora10204 dba 3584 Apr 12 23:13 spfileORT24FS.ora
ora10204>strings -a spfileORT24FS.ora
ORT24FS.__db_cache_size=708837376
ORT24FS.__java_pool_size=4194304
ORT24FS.__large_pool_size=4194304
ORT24FS.__shared_pool_size=113246208
ORT24FS.__streams_pool_size=0
*.audit_file_dest='/oracle_fs/ora10204/admin/ORT24FS/adump'
*.background_dump_dest='/oracle_fs/ora10204/admin/ORT24FS/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/oracle_fs/Databases/ORT24FS/control01.ctl','/oracle_fs/Databases/ORT24FS/control02.ctl','/oracle_fs/Databases/ORT24FS/control03.ctl'#Restore Controlfile
*.core_dump_dest='/oracle_fs/ora10204/admin/ORT24FS/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORT24FS'
*.db_recovery_file_dest='/oracle_fs/FRAs/ORT24FS'
*.db_recovery_file_dest_size=21474836480
*.dbwr_io_slaves=0
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORT24FSXDB)'
*.filesystemio_options='NONE'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/oracle_fs/ArchiveLogs/ORT24FS'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.parallel_max_servers=4
*.pga_aggregate_target=179306496
*.processes=150
*.recovery_parallelism=4
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=880803840
*.sga_target=838860800
*.shared_pool_size=104857600
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='/oracle_fs/ora10204/admin/ORT24FS/udump'
ora10204>

I also demonstrate above how I did NOT have to use the "_allow_resetlogs_corruption" parameter.

What about the possible situation where my controlfile is older than ArchiveLog backups created subsequent to it ? Then, the RECOVER command would not have known about those Backupsets as they would be missing from the controlfile !
I would then
a. Restore those BackupSets from tape to disk (if not available on disk)
b. Use the RMAN "CATALOG" command to catalog the backupsets so that they are now "known" to the controlfile
and/or
b. Use the RMAN "CATALOG" command to catalog the backupsets and then use the RMAN "RESTORE ARCHIVELOG ..." command to restore archivelogs
c. Continued the Recovery either through RMAN or with SQLPLUS "RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL"

.
.
.

72 comments:

Khurram Siddiqui said...

Hemant do you mean to say older controlfile doe not know newer archivelog to be applied at restored backup.

I mean if i start restore and recovery since 01 Apr contorlfile then i can never apply today archivelog?

as you said


What about the possible situation where my controlfile is older than ArchiveLog backups created subsequent to it ? Then, the RECOVER command would not have known about those Backupsets as they would be missing from the controlfile !


i think RMAN will itself restore those archivelog regardless controlfile does know or not.

Khurram

Hemant K Chitale said...

I was talking of the controlfile not knowing of the ArchiveLog *backups*. RMAN has to know which BackupSets contain ArchiveLogs needed for recovery. In the absence of an RMAN Catalog database, it has to read this information from the control file.
If the ArchiveLog Backup is done after the Controlfile Backup, the name of the ArchiveLog BackupSet (and which ArchiveLogs it contains) is NOT in the Controlfile Backup which preceded it.

(Also, in any case, the controlfile backup doesn't know of ArchiveLogs created after the controlfile backup !. However, when doing a Recovery using Backup Controlfile, Oracle can "construct" the expected ArchiveLog filename. See the section titled "Getting the SCNs and Timestamps (and Sequence#s) : " at http://web.singnet.com.sg/~hkchital/Incomplete_Recovery_with_BackupControlfile.doc from http://hemantoracledba.blogspot.com/2008/02/database-recovery-rollforward-from.html )

If the ArchiveLogs, future to the controlfile, are still present on disk (or have been manually restored by other means) then RMAN can simply apply them.
If the ArchiveLogs are inside a BackupSet, RMAN must know *which* BackupSet they are in.

If you use an FRA, you are in luck -- Oracle will automatically CATALOG all the files there when it executes RESTORE and RECOVER.

Khurram Siddiqui said...

yes i was also expecting the same ,i relize from yours post that old controlfile cannot apply current archivelogs redo which is false.But its not like that within yours thread ,its clear to me now.

Khurram

Hemant K Chitale said...

See my next post
http://hemantoracledba.blogspot.com/2009/04/controlfile-backup-older-than.html
where I explain the scenario (controlfile oder than archivelog backupset).

Ed said...

Hi Hemant
Will the following work?
The database runs in archive mode.
Take the time before the backup starts.
Perform a hot backup of the whole database excluding the archive logs.
Delete all archive logs completed before the backup starts.
Therefore we are left with:
1 A full backup of the database excluding archive logs.
2 All generated archive logs since this backup.

If I restore the database and recover
Should the recovery work when there are only archive logs since the backup was taken?
I've tested this and cannot understand why it works when I don't have backed up archives for the db backup period.

Hemant K Chitale said...

Oracle needs archivelogs generated since the first datafile backup began. What is important is that the archivelogs be available. Technically they don't have to have been backed up. RMAN and SQLPlus RECOVER can read them from disk, using the expected location specified by log_archive_dest (or if you specify an alternate location in the RECOVER FROM location).

If you have backed up the ArchiveLogs but haven't deleted them from disk, RMAN doesn't even need to restore from the backup. You will see messages that RMAN has detected them as being present on disk. So RMAN applies them from that location.
If you have not backed up the ArchiveLogs but they are present on disk, RMAN detects them and uses them. Thus, it is obvious that RMAN first looks for the ArchiveLogs in the default location. It restores them from the backup only if they are not available.
SQLPlus RECOVER, obviously, expects them to be on disk (as it cannot restore files).

Why should you backup the archivelogs ? Because any disk crash or storage failure that "takes out" your database may well "take out" your archivelogs as well ! Entire SAN failure means that the archivelogs are lost with the database.
Failure of the disk volume that holds the ArchiveLogs together with the failure of even a single datafile means that you can't restore (even if the single datafile) and recover the database unless you had backed up the archivelogs. Even if archivelogs are on disk but the DBA or SysAdmin inadvertently deletes one of them, your recovery is stuck.

It is for all these reasons that you must backup the Archivelogs.
RMAN can recover using ArchiveLogs if all the required files are on disk. But the backup is protection against non-availability on account of any of these causes.

Ed said...

Hi Thanks for your advice.
You've put some good answers on this blog.

You mentioned
"Oracle needs archivelogs generated since the first datafile backup began."

So in this case If I backup the db only at 2pm today.
Then copy this backup to tape
Then delete all archive logs completed before 2pm.

I should be able in the future to restore from this backup and recover up to the current time using archive logs generated since 2pm today.

I loose point in time recovery before 2PM today but copying the backup of the db to tape would give me the same protection as backing up the archive logs. Unless of course I need them to do a recovery.

So in short I don't need to backup the archive logs?

Ed

Hemant K Chitale said...

I would never say "you don't need to backup archivelogs".
I would always say "you should backup the archivelogs".

What happens if someone inadvertently deletes archivelogs from disk ? OR if the filesystem holding the archivelogs gets corrupted ?


As for your first question :
If the backup begins at 2pm and ends at, say 4pm, you need all the archivelogs generated from the first one after 2pm (as it was the CURRENT online redo log at 2pm) t o the firsst one after 4pm (as it was the CURRENT online redo log at 4pm). Technically that is all you need.
However, as you rightly observe, if you don't backup the archivelogs of before 2pm, you lose the ability to do a point in time recovery to say, 10am, rolling forward from yesterdays 2pm-4pm backup and applying archivelogs from 4pm yesterday to 10am yesterday.

So :
1. Always backup archivelogs even if you think that you don't need to.
2. Delete archivelogs only if you really really really have no other choice. You never know when you may have to do a point in time recovery (e.g. to recover from a bad update or delete issued by a user).

Anonymous said...

Hi Hemant,

With reference to the example discussed with Ed, I have a following query

The database is 2 node RAC
backup starts at 02:00
instance 1 creates db01_12_02:02.arc
instance 2 creates db02_12_02:02.arc

The backup finishes at 04:00
and after the backup the fisrt archivelogs created on the instances are as follows

at 04:01
instance 1 creates db01_19_04:01.arc
instance 2 creates db02_20_04:02.arc

Thus as per my understanding to recover database till 04:00 (incomplete recovery) I will need
backup sets which includes archivelogs from 02:00 till 04:00 and above 2 archive logs
(created just after backup thus spanning the timestamp of backup finish 04:00)
and the archivelogs after the above 2 are not needed tp recover database till 04:00

Is that acorrect?

Thanks and Regards,
Chetana

Hemant K Chitale said...

Chetana,
Yes, you only need the archivelogs upto 19 and 20 for the two threads. If you do have subsequent archivelogs beyond them, you can continue to "roll-forward" in Recovery. You don't have to have those subsequent log files but, naturally, your management would expect you to be able to roll-forward as far as possible.
(For example, if the database is lost at 11:00, and you are able to apply archivelogs upto 10:50am, management is happier than if you applied archivelogs only as far as 04:02am).


Note that, depending on how you run the backup (what BACKUP commands you execute in your script), the backup itself may have cause log switch and archivelog generation and inclusion in the backupsets. Thus, the actual "first archivelog after the end of the database" might also have been captured by the backup script and 19 and 20 might be subsequent log files. It depends on your backup script and command set.

Anonymous said...

Thanks Hemant

It is much clear to me now

Regards,
Chetana

John said...

Hi Hemant
I know that oracle issues checkpoint prior to "alter tablespace begin backup".
Does oracle also issue checkpoint before Rman backup? (backup database)

Anonymous said...

Hi Hemant,

I have a query regarding RMAN database recovery

I have restored backup of live database on another host with the same file structure

If I recover the backup using following command, when the recovery will stop?

RECOVER automatic DATABASE USING BACKUP CONTROLFILE;
RECOVER DATABASE USING BACKUP CONTROLFILE until CANCEL;
CANCEL
ALTER database open RESETLOGS;

I assume the recovery using 'automatic' will not prompt me for archivelogs and since it will continuously apply archive logs generated at the log_archive_dest of live database which is shared to this Test database being recovered, it may not stop at all

Please help me understanding this

Thanks and Regards,
Chetana

Hemant K Chitale said...

The first command will stop at the last ArchiveLog it finds.
Remember that applying the ArchiveLogs in Automatic mode is a continous process and will likely be faster than the rate at which the source database is generating Archivelogs !
(say the source database generates no new archivelogs at night but the RECOVER is still running at night and does a complete catch up !)


What your are envisioning is akin to a Standby Database. A Standby Database is in Continous Recovery -- and this could DataGuard or manual commands or automated scripts. But it is *not* OPENed until and unless necessary (or is scheduled and managed).

Hemant

Anonymous said...

Hemant,

Thanks for your reply

I have performed such incomplete recovery in the past for database refresh and it was successful

Mostly because as you have mentioned applying logs was faster than creating logs;

My query is if the recovery comes to a stage that there are no archivelogs now at the specified destination,
1) how much time it will wait before it terminates the recovery
Or
2) it will terminate the next moment there is no next archivelog to apply -> because of the explicit 'cancel' argument in the script

Regards,
Chetana

Hemant K Chitale said...

There is no "timeout". If the RECOVER AUTOMATIC finds no new archivelogs it terminates immediately.
Your next command is interactive and will wait for you to specify an ArchiveLog.

Hemant K Chitale said...

John had a question that I hadn't replied to : "Does oracle also issue checkpoint before Rman backup?"

Yes it does checkpoint a Tablespaces's datafiles.

Since a database backup for a database with many datafiles can span multiple backupsets, the checkpoint is issued at the beginning of each backupset.

Hemant

John said...

Thanks Hemant.
One final question:)

Why does open resetlogs takes a little bit more time?

Hemant K Chitale said...

John,

A RESETLOGS also has to recreate (if missing) or reinitialize (if present) the online redo log files (besides updating the controlfile with information from the datafile headers and the log sequence numbers of the online redo logs).
Thus, it takes more time if you have :
a. More online redo logs and/or
b. larger online redo logs

You could simply test the difference between an OPEN RESETLOGS of a database with 3 online redo logs of 50MB each and another copy of the database (the same size, the same number of datafiles) with 8 online redo logs of 500MB each.
If you look at the alert.log messages being written when the OPEN RESETLOGS is issued, the amount of activity required will be obvious.

Hemant K Chitale

john said...

Hi Hemant;

When I shutdown abort, instance recovery is done automatically once I open the database.

What I want to ask is, when I issue
'recover database using backup controlfile' statement even only once,
I can not recover the database with normal 'recover database' clause.

Does this statement modify controlfile or datafiles? Whats the reason for this?


Here is the demo:

SQL>shutdown abort;
ORACLE instance shut down.
startup mount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1287016 bytes
Variable Size 92277912 bytes
Database Buffers 188743680 bytes
Redo Buffers 2904064 bytes
Database mounted.

SQL>recover database using backup controlfile;
ORA-00279: change 3574972 generated at 12/06/2009 18:34:54 needed for thread 1
ORA-00289: suggestion :
C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2009_12_06\O1_MF_1_4_%U_.
ORA-00280: change 3574972 for thread 1 is in sequence #4


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

cancel
Media recovery cancelled.


SQL>recover database;

ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


If I issue recover database at first rather than 'recover database using backup controlfile', I can successfully make the recovery.

Hemant K Chitale said...

John,
SHUTDOWN, STARTUP MOUNT and then RECOVER again.

JOHN said...

Hi sir;

Why does the last change# become null once I issue, backup controlfile statement?



sys@XE> select CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
3332929 3332929
3332929 3332929
3332929 3332929
3332929 3332929



sys@XE> recover database using backup controlfile;
cancel


sys@XE> select CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
3332929
3332929
3332929
3332929

Hemant K Chitale said...

Last Change# is null when the datafile is being changed. See the Database Reference definition of the view.

Hemant

Unknown said...

Since no log was applied, How come datafile is changed?

You can also try, open your database in mount state and issue:

recover database using backup controlfile;
cancel

Hemant K Chitale said...

The RECOVER DATABASE (even if CANCELled immediately) *does* update (i.e. "change") the datafiles. So, the NULLs for LAST_CHANGE# are as I expected. What I find surprising is that you had a non-null value for LAST_CHANGE# ! What set of commands had you tried ?

Here is what I get :

SQL> startup mount
ORACLE instance started.

Total System Global Area 880803840 bytes
Fixed Size 2087992 bytes
Variable Size 205521864 bytes
Database Buffers 666894336 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> !date
Sat Jan 30 22:40:17 SGT 2010

SQL> !ls -l *.dbf
-rw-r----- 1 ora10204 dba 104865792 Jan 27 22:54 example01.dbf
-rw-r----- 1 ora10204 dba 104865792 Jan 27 22:54 index_tbs_01.dbf
-rw-r----- 1 ora10204 dba 52429312 Jan 27 22:49 redo01.dbf
-rw-r----- 1 ora10204 dba 52429312 Jan 27 22:51 redo02.dbf
-rw-r----- 1 ora10204 dba 52429312 Jan 27 22:55 redo03.dbf
-rw-r----- 1 ora10204 dba 304095232 Jan 27 22:54 sysaux01.dbf
-rw-r----- 1 ora10204 dba 985669632 Jan 27 22:54 system01.dbf
-rw-r----- 1 ora10204 dba 104865792 Jan 27 22:54 table_tbs_01.dbf
-rw-r----- 1 ora10204 dba 104865792 Jan 27 22:54 tbs_temp_name01.dbf
-rw-r----- 1 ora10204 dba 131080192 Jan 27 22:51 temp01.dbf
-rw-r----- 1 ora10204 dba 104873984 Jan 27 22:54 test_16k.dbf
-rw-r----- 1 ora10204 dba 1048584192 Jan 27 22:54 test_8k.dbf
-rw-r----- 1 ora10204 dba 351281152 Jan 27 22:54 undotbs01.dbf
-rw-r----- 1 ora10204 dba 425992192 Jan 27 22:54 users01.dbf

SQL> select checkpoint_change#, last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
2622353
2622353
2622353
2622353
2622353
2622353
2622353
2622353
2622353
2622353

10 rows selected.

SQL> recover database using backup controlfile;
ORA-00279: change 2622353 generated at 01/27/2010 22:49:43 needed for thread 1
ORA-00289: suggestion :
/oracle_fs/FRAs/ORT24FS/ORT24FS/archivelog/2010_01_30/o1_mf_1_32_%u_.arc
ORA-00280: change 2622353 for thread 1 is in sequence #32


Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> select checkpoint_change#, last_change# from v$datafile;

CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
2622353
2622353
2622353
2622353
2622353
2622353
2622353
2622353
2622353
2622353

10 rows selected.

SQL> !ls -l *dbf
-rw-r----- 1 ora10204 dba 104865792 Jan 30 22:40 example01.dbf
-rw-r----- 1 ora10204 dba 104865792 Jan 30 22:40 index_tbs_01.dbf
-rw-r----- 1 ora10204 dba 52429312 Jan 27 22:49 redo01.dbf
-rw-r----- 1 ora10204 dba 52429312 Jan 27 22:51 redo02.dbf
-rw-r----- 1 ora10204 dba 52429312 Jan 27 22:55 redo03.dbf
-rw-r----- 1 ora10204 dba 304095232 Jan 30 22:40 sysaux01.dbf
-rw-r----- 1 ora10204 dba 985669632 Jan 30 22:40 system01.dbf
-rw-r----- 1 ora10204 dba 104865792 Jan 30 22:40 table_tbs_01.dbf
-rw-r----- 1 ora10204 dba 104865792 Jan 30 22:40 tbs_temp_name01.dbf
-rw-r----- 1 ora10204 dba 131080192 Jan 27 22:51 temp01.dbf
-rw-r----- 1 ora10204 dba 104873984 Jan 30 22:40 test_16k.dbf
-rw-r----- 1 ora10204 dba 1048584192 Jan 30 22:40 test_8k.dbf
-rw-r----- 1 ora10204 dba 351281152 Jan 30 22:40 undotbs01.dbf
-rw-r----- 1 ora10204 dba 425992192 Jan 30 22:40 users01.dbf

SQL>

The datafiles timestamps were updated.

Unknown said...

Did you shutdown abort? I was expecting numbers in last_change# before you issued "recover using backup controlfile" clause.

So Hemant, even no log is applied, what kind of change is done on datafiles after this command so that last_change# become null?

Hemant K Chitale said...

You can see that the datafiles did get updated. How about thinking that the file headers were updated ?

Hemant

John said...

1-)
so,
Does it mean that,
even no log is applied.
"Recover database using backup controlfile" clause always updates the datafile header?

2-)Do you know what kind of modification oracle performs in header?

Hemant K Chitale said...

John,

1) Yes it would seem so.

2) A datafile header does have different structures. You could dump the datafile header and view it.

Hemant

john said...

sys@XE> alter database begin backup;

Database altered.


----Copy all datafiles,controlfile,redologs.


sys@XE> alter database end backup;

Database altered.

sys@XE> alter system switch logfile;

System altered.

sys@XE> /

System altered.

sys@XE> /

System altered.


shut immediate;

---restore all datafiles,controlfile,redologs

sys@XE> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00314: log 1 of thread 1, expected sequence# 1 doesn't match 11
ORA-00312: online log 1 thread 1:
'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_4YORY24D_.LOG'



Whats the reason for this? When I checked the alertlog, none of the archived_log was applied?

Hemant K Chitale said...

John,
Obviously you did NOT restore the correct set of datafiles, redo log files and controlfiles. A redo log file seems to be for SEQ#1 but the controlfile / datafiles expect SEQ#11.

Hemant

john said...

Hi Hemant;
After "alter database begin backup"
I backed up all datafiles,controlfiles and redologs.

And then shutdown immediate.
and then restored all datafiles,controlfiles and redologs.

john said...

You ara right Hemant;
I forgot to restore redologs.
I was thinking that oracle first applies the archivelogs and checks redologs at the end.
However I notice that oracle checks redologs straight away once I start recovery

Hemant K Chitale said...

John,
If you think that the message "ORA-00314: log 1 of thread 1, expected sequence# 1 doesn't match 11" is wrong, take it up with Oracle Development and prove it to them.
To me, the message is very clear. there is a mismatch -- either different files are restored or a resetlogs has been issued in between.

Hemant

john said...

Thanks.

Suppose
Log switched occured therefore checkpoint.
and the current redolog become redo2.

Shutdowned abort occured hence checkpoint of redolog is not completed.

When I startup, oracle will do instance recovery.
It will write the blocks which are not written to disk in redolog1 and redolog2.
Upon startup, how does oracle understand wheter the redolog is written to disk or not?
and
How does it understand which of the blocks is already written to disk in redolog2 and which of them hasnt written yet?
Can I also see this information from dynamic performance views?

Hemant K Chitale said...

John,

A Checkpoint is for *datafiles*. Redo Logs are always written to disk if commits are issued.
Redo Logs are guaranteed to be updated. Any "lost" redo (ie present in log buffer at the time of an abort) can only represent uncomitted transactions and/or redo for modified data/index/undo blocks that have not been written to disk. Oracle does not need to bother about redo that did not get written to disk.

Hemant

john said...

Hemant;
My above question was about the
transfer between redolog file in the disk to datafiles.

I know that redo written to disk every commit but it is not written to datafiles every commit.
My question was about this.

Hemant K Chitale said...

John,

I still do not understand your question.

What do you mean by "transfer between redolog file in the disk to datafiles." ? Are you talking of rollforward ? Oracle checks datafile headers to identify what is last written to the datafile -- by the checkpoint. At Instance Recovery, it applies all subsequent changes by reading the redo and applying it to the datafiles. If the last checkpoint had not completed, the datafile header would not indicate completion -- so Oracle would rollforward from the previous checkpoint / incremental checkpoint. It does not matter if some of the modified blocks had been written in the incomplete checkpoint. Oracle will simply reapply the changes -- it guarantees that the changes are properly sequenced and not written out of order.

Hemant

Unknown said...

Thanks so much for this post!! Really helped a lot!

Anonymous said...

If I take a backup of the database in mount state, will it be consistent?

Hemant K Chitale said...

Anonymous,

Yes a database backup when the database is in a MOUNT state but not OPEN is consistent. No datafiles are updated in a Mount state, so no files are fuzzy.


Hemant

Anonymous said...

Thanks sir.
Suppose database is mount state, however media recovery is in progress.
If take a backup in this state, is it still consistent?

Hemant K Chitale said...

Anonymous,

If the database is in a MOUNT state and you have issued a RECOVER DATABASE command, I don't understand what Backup you would be executing.

During a RECOVER the datafiles are being updated. RMAN wouldn't allow you to attempt a BACKUP.

And if you state that you will be performing an OS backup, Oracle wouldn't be aware of it. However,t he datafile headers would be updated to the state that they are in Recovery, not OPEN, not CLOSED but in Recovery.

If you want to take a Backup, I would advice you to stop the RECOVER (e.g. CANCEL at the next ArchiveLog being applied) before you attempt a Backup.


Hemant K Chitale

Anonymous said...

Even I recreate the controlfile, during recovery how does oracle understand which archivelog sequence number to request?

Hemant K Chitale said...

Anonymous,

Oracle reads the DataFile headers. It has to read them to identify the Checkpoint SCNs of each of the DataFiles (so that it know which is "point" to start a Recovery from). The DataFile headers also contain the ast Log Sequence Numbers.
It then "constructs" the ArchiveLog file name using the Log Seq# and the log_archive_format.


Hemant K Chitale

Anonymous said...

...The DataFile headers also contain the ast Log Sequence Numbers.

Thanks hemant.
I cannot see the log sequence number in v$datafile_header.
Can you please shed some light on this?

Hemant K Chitale said...

Anonymous,

No, not all header information is published in the V$ views.

See the dumps I have posted in the file at http://hemantoracledba.blogspot.com/2008/02/database-recovery-rollforward-from.html

Hemant K Chitale

Hemant K Chitale said...

Anonymous,
I can. I can see a link to a document. The commands, results and dumps were too detailed to publish as a blog post so I put them together in a document.

Hemant K Chitale

Quaresma said...

Hemant,

If the control file scn is behind or above of the scn of datafiles,I am always to forced to issue:
recover database using backup controlfile
give redolog path
open resetlogs.

Whats the reason for this? Why oracle doesnt do the media recovery implicity? and I am forced to give redolog path in above scenerios?

Hemant K Chitale said...

Quaresma,

If the controlfile has an SCN lower than that of the datafiles, it is a Backup Controlfile and you need to tell Oracle to recover (i.e. roll-forward) the database to a point beyond that in the controlfile.
Similarly, if you have done a CREATE CONTROLFILE (and not yet done a RESETLOGS) then you need to use the RECOVER DATABASE USING BACKUP CONTROLFILE command so as to tell Oracle to roll-forward the database.

If the controlfile has an SCN higher than the database files, you do not need to use USING BACKUP CONTROLFILE.

In all cases, whether you do a COMPLETE or INCOMPLETE Recovery depends on whether you can recover upto the last (i.e. CURRENT) Online Redo Log file.


When doing a RECOVER, you do not need to specify the location of the archivelogs if you have configured LOG_ARCHIVE_DEST properly. Otherwise, you can use the SET ARCHIVELOG DESTINATION TO 'location' syntax to specify an alternate location.


Hemant K Chitale

Quaresma said...

Hi Hemant;


If the controlfile scn is higher than datafile and redolog scns:

I got this error once I open the database:

ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF'


I cannot do normal recovery:

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00322: log 2 of thread 1 is not current copy
ORA-00312: online log 2 thread 1: 'C:\ORACLEXE\ORADATA\XE\REDO4'


However, I can do recovery like below and open with resetlogs:

recover database using backup controlfile;

"give redolog path"

Log applied.

alter database open resetlogs;


What I want to ask is, where does this log applied to ?
I have already specifed "backup controlfile", this means that scn of the controlfile is ignored.
The scn of redologs and datafiles are already same.
Why does oracle require to apply log? Where does this redolog applied to (The redolog path which I gave above)?


Many thanks

Hemant K Chitale said...

Quarasma,

A Database file may have changes that have been applied to datablocks but need to be rolled back (because they were not committed).
Conversely, there may have been committed changes to data that hadn't been written down to database files by DBWR at the time of the instance/media failure.

Both sets of changes are applied from the Redo Logs. In the first case, after Redo is applied, when opening the database, Oracle checks for uncommitted transactions and does a rollback -- this is made possible because Redo also applies Undo. In the second case, applying Redo replays transactions that were committed (and captured in Redo) but hadn't yet gone to database file.

Therefore, "recovery" from Redo is *essential* to making the database consistent again.

Hemant K Chitale

Quaresma said...

shutdown immediate.
backup all datafiles, redolog and controlfiles. (copy by os command)
startup.
shutdown.
restore everything, except controlfiles. (restore by os command)

startup;

ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF'



recover database using backup controlfile;
redolog path
Log applied.
open resetlogs;




In above scenerio,controlscn is higher than datafile and redolog scn
However, there is no committed or uncommitted transaction.

Where does this redolog applied to (The redolog path which I gave above)?

Hemant K Chitale said...

Quaresma,
The Startup after the backup actually does increment SCNs -- even if you do no "user" transactions. Therefore, that controlfile is "future" to the datafiles. The redo is applied to update the datafiles.

You don't really have to use "USING BACKUP CONTROLFILE" in this case. If you do have the Online Redo Log files, you'd do a Complete Recovery and do not need OPEN RESETLOGS. Else (in the absence of Redo Log files) you have to have ensured ArchiveLogs available and done an Incomplete Recovery followed by an OPEN RESETLOGS.

Hemant K Chitale

Quaresma said...

The startup increments the scn but I am not using current redolog in above scenerio.
Redologs are restored as well.

let say
Datafile scn:1 redolog scn:1 controlfile scn:1

shutdown immediate;
Datafile scn:2 redolog scn:2 controlfile scn:2

backup everything;

startup;
Datafile scn:3 redolog scn:3 controlfile scn:3

shutdown;

Datafile scn:4 redolog scn:4 controlfile scn:4


Lets say I restore everything except controlfile;

Datafile scn:2 redolog scn:2 controlfile scn:4

Since redolog scn is behind, how come it is applied?

Hemant K Chitale said...

Quaresma,
Without the Online Redo Log, you cannot do a Complete Recovery. Even if you have restored the Online Redo Logs from the cold backup, the controlfile is not consistent with them. In the absence of the redo entry for the incremental SCN, you cannot also do a Recovery upto the SCN in the controlfile. Therefore, you must use the USING BACKUP CONTROLFILE specification.
Having used BACKUP CONTROLFILE, Oracle requires a RECOVER to be completed. However, it verifies the headers of all the datafiles and finds them to be consistent, as of the shutdown. It then requires the RESETLOGS to resynchronise this (lower SCN) from the datafiles to the controlfile and, also, to reinitialize the redo logs.

Hemant K Chitale

Quaresma said...

......Even if you have restored the Online Redo Logs from the cold backup, the controlfile is not consistent with them


So, how come the inconsistent redolog is applied in above scenerio ? The scn of redolog is already behind than controlfile

Unknown said...

Thank you so much for this precious information.

Hemant K Chitale said...

Quaresma,

There is no redo log to be applied. The file headers have to be synchronised.

Hemant K Chitale

Quaresma said...

Since there is no redolog to apply,
Why am I forced to give redolog path and I get log applied message afterwards ?

redolog path
Log applied.

Hemant K Chitale said...

Because you have to be provided a message.
How many different messages do you want Oracle to write ?

Hemant K Chitale

Quaresma said...

I think this is a bug :-)

Quaresma said...

Eventhough it doesnt actually apply redolog, if I give different redolog path, it doesnt apply, I have to try all redologs to find the correct one.

What is the reason for this?

Hemant K Chitale said...

I don't see it as a bug. Oracle has to verify the files.
Say your datafiles are all consistent and have an SCN 12345. The controlfile has been created and has to be treated as a "Backup controlfile". This means that it is possible that there are transactions beyond SCN 12345 in some redo/archivelog files.
(for example , a cold backup was done 7 days ago and there have been transactions captured in redo/archivelolg files since then. If you have restored that cold backup Oracle may be able to apply those subsequent transactions).
Remember : In your scenario, Oracle cannot know if the datafiles are as they last were on disk or have been restored from a backup -- it just happens to find them all on disk and consistent to SCN 12345. Oracle cannot also rely on the server clock because server clocks can have been reset or changed. It has to rely on the SCN. It has to verify the archivelogs to see if there are any "future" transactions beyond 12345.
It looks for the (expected) next log file. If there is one, it verifies it. If there is none, it has to know that there is no other file beyond this. That is why it has to rely on the interactive method for the DBA to provide a log file name which it can then open and verify.
When it finds that there are no "future" transactions it knows that the database is both consistent and complete. (The OPEN RESETLOGS is still required because you were using a BACKUP CONTROLFILE and everything has to be synchronised back to the controlfile).

Hemant K Chitale

ibne said...

During media recovery,
when a log is applied to standby database, does oracle only update corresponding datafile or all datafiles?

Hemant K Chitale said...

ibne,
The redo stream (redo and archivelog) captures and replays *physical* changes to the database. These span multiple datafiles. Therefore a single redo file being applied at the standby WILL update multiple datafiles.

rajorshi said...

Hi Hemant
Thanks a lot for the this post.
As I was trying to envisage this with a practical scenario of server crash and filesystem loss, I tried to use the steps to simulate the scenario when I was stuck at the point of setting the DBID.
This step:
RMAN> SET DBID=4137213278;

As all the filesystem is gone ,so are the rman logs along with it. Assuming only source of DBID as the backupset , how do we get the dbid from it?

thanks
---Raj

Hemant K Chitale said...

You get the DBID from RMAN Backup Logs -- which you should retain -- or from a Catalog schema.

Hemant

Unknown said...

Hemant,
I have a scenario where the storage replication does a bit by bit copy ( outside the purview of Oracle ) of the Data volume and the ArchiveLog Volume only ( not the Redolog volume ). The three files namely Data,Redolog and the ArchiveLogs, are in different volumes.
Now on the production server, suppose the Data-volume gets corrupted. So I do a restore ( called as reverse re-sync in storage terminology ) from the Secondary volume ( S-Vol ) to the Primary volume ( P-Vol ).

As the Redolog is out-of-sync, how do I get the database up and running using the restored data ?
Basically how do I recover the DB using the ArchiveLogs ( assuming no RMAN backup exists ) ?

Thanks

V V

Hemant K Chitale said...

V V (Thirumali Thathachary)

You should be making the storage copy of the database with the database in BEGIN BACKUP mode. (and END BACKUP after the copy is completed). Similarly, you must ensure that, at the minimum, the first Archivelog generated after the end of the database backup is sync'ed to the storage copy.

That way, when you do a "reverse sync" you can treat the reversed image as the RESTORE of an Online Backup for which you need to issue only a RECOVER command.

Note that your controlfile backup has to be handled separately from the database backup as the controlfile backup must be an atomic copy.

See the Oracle Backup and Recovery documentation chapters on "User Managed Backups".


Hemant

Anonymous said...

Hi Hemant,

With reference to this error,
sys@XE> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00314: log 1 of thread 1, expected sequence# 1 doesn't match 11
ORA-00312: online log 1 thread 1:
'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\ONLINELOG\O1_MF_1_4YORY24D_.LOG'


I could not understand, why is RMAN looking for online redo log for performing incomplete database recovery.
My understanding is that to perform incomplete recovery, RMAN only applies the archived log files ahead of data files, but not online redo logs.

Could you please comment on this query.


Hemant K Chitale said...

Anonymous,
Incomplete Recovery doesn't have to be to a point in the archivelogs. It can be to a point in the online redo log that hasn't been archived yet.

In your case, Oracle was looking for a the redo that hadn't been archived yet.
However, you seem to have run multiple recovery scenarios and recreated / reset the online redo logs.

Hemant