recovery case when configuring data guard. Version 10g

When trying  to configured the manual data guard with full backup taken with archived log to that sequence. But the restoring the backup it prompts the error:ORA-01194. Also when tried to open the database in read only mode, it is asking for recovery.

============
contents of Memory Script:
{
set until scn 34141556490;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 29-MAR-11
using channel ORA_AUX_DISK_1

starting media recovery

Oracle Error:
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: '+ASMDB/sambandh/datafile/system.257.747046353'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 3 seq 56278 lowscn 34141556410 found to restore
RMAN-06025: no backup of log thread 3 seq 56277 lowscn 34141426623 found to restore
RMAN-06025: no backup of log thread 3 seq 56276 lowscn 34141291340 found to restore

============
SQL> startup nomount pfile=/home/oracle/pfile.ora
ORACLE instance started.

Total System Global Area 1.1811E+10 bytes
Fixed Size 2092760 bytes
Variable Size 3338666280 bytes
Database Buffers 8455716864 bytes
Redo Buffers 14684160 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+ASMDB/sambandh/datafile/system.257.747046353'

Rman backup command used is:

run {
backup database include currect controlfile for stanndby format '/backup/sambandh/%U';
backup archivelog all format '/backup/sambandh/arch_%U';
}

Checking for consistency, I found some the files are rman fuzzy.

select hxfil FILENUMBER,fhsta STATUS,fhscn SCN,fhrba_Seq SEQUENCE from x$kcvfh;

2 64 34126777046 34407
3 64 34126777046 34407
4 64 34126798434 34407
5 0 34126777046 34407
6 64 34126777046 34407
7 64 34126798434 34407
8 64 34126777046 34407
9 64 34126798434 34407
10 64 34126777046 34407
11 64 34126798434 34407
12 64 34126777046 34407
13 64 34126798434 34407
14 64 34126777046 34407
15 64 34126798434 34407

SQL> select distinct (fuzzy) from v$datafile_header;

FUZ

---                                                                                                                                                                                

NO

YES

2 rows selected.                                                                                                                                                                                 

This indicates few files are Rman fuzzy.

Now looking for the recovery, I need information how much archive files are needed. Doing google and reading some blogs where the way I work on.

In my case Rman just did a restore of the file and recovery failed because the archivelogs were not available.


When we check from which backupset/ piece Rman(list backup from prod) will use based on the time you give . the third column in the list backup would be the Checkpoint SCN at which the file was backed up by rman

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20997 Full 115.28G DISK 00:33:16 29-JAN-12
BP Key: 23032 Status: AVAILABLE Compressed: NO Tag: TAG20110329T195444
Piece Name: /backup/sambandh/sambandhqdm8f885_1_1
List of Datafiles in backup set 20997
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 34126777046 29-JAN-12 +ASMDB/sambandh/datafile/undotbs1.260.619180219
3 Full 34126777046 29-JAN-12 +ASMDB/sambandh/datafile/sysaux.261.619180221
5 Full 34126777046 29-JAN-12 +ASMDB/sambandh/datafile/users.264.619180231
6 Full 34126777046 29-JAN-12 +ASMDB/sambandh/datafile/sambandh.268.619210565 ---->Lowest checkpoint SCN



95 Full 34126798434 29-JAN-12 +ASMDB/sambandh/datafile/sambandh.366.728427425
97 Full 34126798434 29-JAN-12 +ASMDB/sambandh/datafile/users.368.729170637
99 Full 34126798434 29-JAN-12 +ASMDB/sambandh/datafile/sambandhindx.370.742497811---->Highest checkpoint SCN


So to remove rman fuzziness, first need to recovery beyond the Highest check point SCN shown in that Full backup of JAN 29 2012 in this case.

So in our case the Lowest Checkpoint SCN was 34126777046 and Highest SCN number was 34126798434

So which means we need all archive log generated having SCN greater than 34126777046 till a SCN number beyond 34126798434 to remove the Rman fuziness

Which in our case was three archive lgos

3 56194 34125893045 29-JAN-12 34126821324 29-MAR-11 ---.> This archive log has scn start at 34125893045 and ending at 34126821324(greater than your Highest checkpoint SCN)
2 32632 34121902156 29-JAN-12  34126821314 29-MAR-11
1 34407 34121906267 29-JAN-12 34126821342 29-MAR-11

Coping these files to secondary and doing, we have recoved the DB.