asked on

Incomplete Recovery (11.1.6 windows)

Hi ,
I am trying to do point in time recovery using SCN on windows(11.1.6) for lost of current redolog after shutdown abort(removed logfiles).
Below are the steps.
why is that i'm not getting my table aa back after doing recovery?Please can someone explain me.

Thanks

SQL> select status,group#,first_change# from v$log;
 
STATUS               GROUP# FIRST_CHANGE#
---------------- ---------- -------------
INACTIVE                  1       1952117
CURRENT                   2       1983844
UNUSED                    3             0
 
SQL> conn scott/tiger
Connected.
SQL> create table aa (id number);
 
Table created.
 
SQL> insert into aa values(&1);
Enter value for 1: 1
old   1: insert into aa values(&1)
new   1: insert into aa values(1)
 
1 row created.
 
SQL> /
Enter value for 1: 2
old   1: insert into aa values(&1)
new   1: insert into aa values(2)
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> conn / as sysdba
Connected.
SQL> alter system switch logfile;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
SQL> select status,group#,first_change# from v$log;
 
STATUS               GROUP# FIRST_CHANGE#
---------------- ---------- -------------
INACTIVE                  1       1997211
CURRENT                   2       1997214
INACTIVE                  3       1997209
 
SQL> select max(NEXT_CHANGE#)-1 as "SCN" from V$ARCHIVED_LOG;
 
       SCN
----------
   1997213
 
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
C:\app\asma\oradata\test>rm *.log
 
C:\Users\asma>rman target /
 
Recovery Manager: Release 11.1.0.6.0 - Production on Sat Jun 27 11:41:29 2009
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
connected to target database (not started)
 
RMAN> startup nomount
 
Oracle instance started
 
Total System Global Area     426852352 bytes
 
Fixed Size                     1333648 bytes
Variable Size                285214320 bytes
Database Buffers             134217728 bytes
Redo Buffers                   6086656 bytes
 
RMAN> restore controlfile from autobackup;
 
Starting restore at 27-JUN-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
 
recovery area destination: C:\app\asma\flash_recovery_area
database name (or database unique name) used for search: TEST
channel ORA_DISK_1: AUTOBACKUP C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2
009_06_26\O1_MF_S_690594541_54C5PSC1_.BKP found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP C:\APP\ASMA\FLASH_REC
OVERY_AREA\TEST\AUTOBACKUP\2009_06_26\O1_MF_S_690594541_54C5PSC1_.BKP
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=C:\APP\ASMA\ORADATA\TEST\CONTROL01.CTL
output file name=C:\APP\ASMA\ORADATA\TEST\CONTROL02.CTL
output file name=C:\APP\ASMA\ORADATA\TEST\CONTROL03.CTL
Finished restore at 27-JUN-09
 
RMAN> list incarnation;
 
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 06/27/2009 11:43:36
ORA-01507: database not mounted
 
RMAN> alter database mount;
 
database mounted
released channel: ORA_DISK_1
 
RMAN> list incarnation;
 
 
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     1988467378       PARENT  1          15-OCT-07
2       2       TEST     1988467378       PARENT  886308     23-JUN-09
3       3       TEST     1988467378       PARENT  1950836    26-JUN-09
4       4       TEST     1988467378       PARENT  1951672    26-JUN-09
6       6       TEST     1988467378       ORPHAN  1952117    26-JUN-09
7       7       TEST     1988467378       CURRENT 1952117    26-JUN-09
5       5       TEST     1988467378       ORPHAN  1952344    26-JUN-09
 
RMAN> reset database to incarnation 4;
 
database reset to incarnation 4
 
RMAN> restore database until scn 1997213;
 
Starting restore at 27-JUN-09
Starting implicit crosscheck backup at 27-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
Crosschecked 9 objects
Finished implicit crosscheck backup at 27-JUN-09
 
Starting implicit crosscheck copy at 27-JUN-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 27-JUN-09
 
searching for all files in the recovery area
cataloging files...
cataloging done
 
List of Cataloged Files
=======================
File Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_27\O1_MF_1_1_
54DB81TJ_.ARC
File Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_27\O1_MF_1_2_
54DH6QCR_.ARC
File Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_27\O1_MF_1_3_
54DH6T9T_.ARC
File Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_27\O1_MF_1_4_
54DH6WYB_.ARC
File Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2009_06_26\O1_MF_S_69
0594541_54C5PSC1_.BKP
 
using channel ORA_DISK_1
 
creating datafile file number=6 name=C:\APP\ASMA\ORADATA\TEST\USER02.DBF
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to C:\APP\ASMA\ORADATA\TEST\SYSTEM0
1.DBF
channel ORA_DISK_1: restoring datafile 00002 to C:\APP\ASMA\ORADATA\TEST\SYSAUX0
1.DBF
channel ORA_DISK_1: restoring datafile 00003 to C:\APP\ASMA\ORADATA\TEST\UNDOTBS
01.DBF
channel ORA_DISK_1: restoring datafile 00004 to C:\APP\ASMA\ORADATA\TEST\USERS01
.DBF
channel ORA_DISK_1: restoring datafile 00005 to C:\APP\ASMA\ORADATA\TEST\EXAMPLE
01.DBF
channel ORA_DISK_1: reading from backup piece C:\APP\ASMA\FLASH_RECOVERY_AREA\TE
ST\BACKUPSET\2009_06_24\O1_MF_NNNDF_TAG20090624T103303_544G9GF8_.BKP
channel ORA_DISK_1: piece handle=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\BACKUPSET\
2009_06_24\O1_MF_NNNDF_TAG20090624T103303_544G9GF8_.BKP tag=TAG20090624T103303
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 27-JUN-09
 
RMAN> recover database until scn 1997213;
 
Starting recover at 27-JUN-09
using channel ORA_DISK_1
 
starting media recovery
 
archived log for thread 1 with sequence 52 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_24\O1_MF_1_52_544GG9LY_.ARC
archived log for thread 1 with sequence 53 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_24\O1_MF_1_53_544JOW42_.ARC
archived log for thread 1 with sequence 54 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_24\O1_MF_1_54_544KG613_.ARC
archived log for thread 1 with sequence 55 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_24\O1_MF_1_55_545KFV3K_.ARC
archived log for thread 1 with sequence 56 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_25\O1_MF_1_56_546YP173_.ARC
archived log for thread 1 with sequence 57 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_57_549HVW3T_.ARC
archived log for thread 1 with sequence 58 is already on disk as file C:\APP\ASM
A\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_58_549SRJFK_.ARC
archived log for thread 1 with sequence 1 is already on disk as file C:\APP\ASMA
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_1_54BXDKH5_.ARC
archived log for thread 1 with sequence 2 is already on disk as file C:\APP\ASMA
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_2_54BXDLGO_.ARC
archived log for thread 1 with sequence 3 is already on disk as file C:\APP\ASMA
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_3_54BXDR2G_.ARC
archived log for thread 1 with sequence 1 is already on disk as file C:\APP\ASMA
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_1_54BYK6R6_.ARC
archived log for thread 1 with sequence 2 is already on disk as file C:\APP\ASMA
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_2_54BYK8RB_.ARC
archived log for thread 1 with sequence 3 is already on disk as file C:\APP\ASMA
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_3_54BYKG92_.ARC
archived log for thread 1 with sequence 4 is already on disk as file C:\APP\ASMA
\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_26\O1_MF_1_4_54BYKJ7O_.ARC
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_52_544GG9LY_.ARC thread=1 sequence=52
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_53_544JOW42_.ARC thread=1 sequence=53
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_54_544KG613_.ARC thread=1 sequence=54
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_55_545KFV3K_.ARC thread=1 sequence=55
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
5\O1_MF_1_56_546YP173_.ARC thread=1 sequence=56
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_57_549HVW3T_.ARC thread=1 sequence=57
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_58_549SRJFK_.ARC thread=1 sequence=58
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_1_54BXDKH5_.ARC thread=1 sequence=1
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_2_54BXDLGO_.ARC thread=1 sequence=2
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_3_54BXDR2G_.ARC thread=1 sequence=3
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_1_54BYK6R6_.ARC thread=1 sequence=1
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_2_54BYK8RB_.ARC thread=1 sequence=2
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_3_54BYKG92_.ARC thread=1 sequence=3
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_4_54BYKJ7O_.ARC thread=1 sequence=4
unable to find archived log
archived log thread=1 sequence=5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/27/2009 11:58:56
RMAN-06054: media recovery requesting unknown archived log for thread 1 with seq
uence 5 and starting SCN of 1952116
 
RMAN> alter database open resetlogs;
 
database opened
 
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
 
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

Open in new window

ASKER CERTIFIED SOLUTION

mrjoltcola

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

monto1

ASKER

Because i had open database with resetlogs few times.

RMAN> list backup summary;
 
using target database control file instead of recovery catalog
 
List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1       B  A  A DISK        24-JUN-09       1       1       NO         TAG200906
24T103244
2       B  F  A DISK        24-JUN-09       1       1       NO         TAG200906
24T103303
3       B  A  A DISK        24-JUN-09       1       1       NO         TAG200906
24T103554
4       B  F  A DISK        24-JUN-09       1       1       NO         TAG200906
24T103608
5       B  F  A DISK        26-JUN-09       1       1       NO         TAG200906
26T211713
6       B  F  A DISK        26-JUN-09       1       1       NO         TAG200906
26T212638
7       B  F  A DISK        26-JUN-09       1       1       NO         TAG200906
26T214417
8       B  F  A DISK        26-JUN-09       1       1       NO         TAG200906
26T215443
9       B  F  A DISK        26-JUN-09       1       1       NO         TAG200906
26T224502
10      B  F  A DISK        26-JUN-09       1       1       NO         TAG200906
26T234901
11      B  F  A DISK        27-JUN-09       1       1       NO         TAG200906
27T120855
 
RMAN>

Open in new window

mrjoltcola

Yes, but RMAN is restoring from backupset from 24-JUN-09, then rolling forward.

I think due to your command --> reset database to incarnation 4;

Skip that command and just try:

restore database until scn 1997213;

And see which backupset RMAN chooses. It should choose a later backupset.

monto1

ASKER

Here is the output,Still picking up backup from 24.

RMAN> list backupset;
 
 
List of Backup Sets
===================
 
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1       3.22M      DISK        00:00:17     24-JUN-09
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20090624T103244
        Piece Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2009_06_24\O1
_MF_ANNNN_TAG20090624T103244_544G8WGQ_.BKP
 
  List of Archived Logs in backup set 1
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    51      1777767    24-JUN-09 1781471    24-JUN-09
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.09G      DISK        00:02:43     24-JUN-09
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20090624T103303
        Piece Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2009_06_24\O1
_MF_NNNDF_TAG20090624T103303_544G9GF8_.BKP
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1781489    24-JUN-09 C:\APP\ASMA\ORADATA\TEST\SYSTEM01.DBF
  2       Full 1781489    24-JUN-09 C:\APP\ASMA\ORADATA\TEST\SYSAUX01.DBF
  3       Full 1781489    24-JUN-09 C:\APP\ASMA\ORADATA\TEST\UNDOTBS01.DBF
  4       Full 1781489    24-JUN-09 C:\APP\ASMA\ORADATA\TEST\USERS01.DBF
  5       Full 1781489    24-JUN-09 C:\APP\ASMA\ORADATA\TEST\EXAMPLE01.DBF
 
BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3       20.00K     DISK        00:00:11     24-JUN-09
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20090624T103554
        Piece Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2009_06_24\O1
_MF_ANNNN_TAG20090624T103554_544GGPLH_.BKP
 
  List of Archived Logs in backup set 3
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    52      1781471    24-JUN-09 1781556    24-JUN-09
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    9.36M      DISK        00:00:00     24-JUN-09
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20090624T103608
        Piece Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2009_06_24\O
1_MF_S_690374168_544GH587_.BKP
  SPFILE Included: Modification time: 24-JUN-09
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 1781566      Ckp time: 24-JUN-09
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    9.36M      DISK        00:00:39     26-JUN-09
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20090626T211713
        Piece Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2009_06_26\O
1_MF_S_690585433_54BWSZJ9_.BKP
  SPFILE Included: Modification time: 26-JUN-09
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 1951335      Ckp time: 26-JUN-09
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    9.36M      DISK        00:00:18     26-JUN-09
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20090626T212638
        Piece Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2009_06_26\O
1_MF_S_690585998_54BXBZ1Z_.BKP
  SPFILE Included: Modification time: 26-JUN-09
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 1951599      Ckp time: 26-JUN-09
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    9.36M      DISK        00:00:23     26-JUN-09
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20090626T214417
        Piece Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2009_06_26\O
1_MF_S_690587057_54BYD7DZ_.BKP
  SPFILE Included: Modification time: 26-JUN-09
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 1952013      Ckp time: 26-JUN-09
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    9.36M      DISK        00:00:00     26-JUN-09
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20090626T215443
        Piece Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2009_06_26\O
1_MF_S_690587683_54BYZR8C_.BKP
  SPFILE Included: Modification time: 26-JUN-09
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 1952474      Ckp time: 26-JUN-09
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    9.36M      DISK        00:00:00     26-JUN-09
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20090626T224502
        Piece Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2009_06_26\O
1_MF_S_690590702_54C1Y783_.BKP
  SPFILE Included: Modification time: 26-JUN-09
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 1952418      Ckp time: 26-JUN-09
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    9.42M      DISK        00:00:00     26-JUN-09
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20090626T234901
        Piece Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2009_06_26\O
1_MF_S_690594541_54C5PSC1_.BKP
  SPFILE Included: Modification time: 26-JUN-09
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 1953368      Ckp time: 26-JUN-09
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11      Full    9.42M      DISK        00:00:00     27-JUN-09
        BP Key: 11   Status: AVAILABLE  Compressed: NO  Tag: TAG20090627T120855
        Piece Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2009_06_27\O
1_MF_S_690638935_54DK2B8K_.BKP
  SPFILE Included: Modification time: 27-JUN-09
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 1952471      Ckp time: 27-JUN-09
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12      Full    9.42M      DISK        00:00:31     27-JUN-09
        BP Key: 12   Status: AVAILABLE  Compressed: NO  Tag: TAG20090627T165048
        Piece Name: C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\AUTOBACKUP\2009_06_27\O
1_MF_S_690655847_54F1L2ON_.BKP
  SPFILE Included: Modification time: 27-JUN-09
  SPFILE db_unique_name: TEST
  Control File Included: Ckp SCN: 1972702      Ckp time: 27-JUN-09
 
 
RMAN> restore database validate until scn 1997213;
 
Starting restore at 27-JUN-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK
 
datafile 6 will be created automatically during restore operation
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece C:\APP\asma\FLASH_RECOVERY_AREA\TE
ST\BACKUPSET\2009_06_24\O1_MF_NNNDF_TAG20090624T103303_544G9GF8_.BKP
channel ORA_DISK_1: piece handle=C:\APP\asma\FLASH_RECOVERY_AREA\TEST\BACKUPSET\
2009_06_24\O1_MF_NNNDF_TAG20090624T103303_544G9GF8_.BKP tag=TAG20090624T103303
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:35
failover to previous backup
 
datafile 6 will be created automatically during restore operation
Finished restore at 27-JUN-09

Open in new window

mrjoltcola

Still seems you have missing logs. Find out if you have missing archive logs.

RMAN> crosscheck archivelog all;
RMAN> list expired archivelog all;

mrjoltcola

Also try:

RMAN> list expired archivelog from scn 1951672;

That is the SCN from your last resetlogs.

monto1

ASKER

Thanks!
Below is the output.

RMAN> crosscheck archivelog all;
 
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
validation succeeded for archived log
archived log file name=C:\APP\ASMA\PRODUCT\11.1.0\DB_1\RDBMS\ARC00051_0690320951
.001 RECID=1 STAMP=690373961
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_51_544G87TX_.ARC RECID=2 STAMP=690373961
validation succeeded for archived log
archived log file name=C:\APP\ASMA\PRODUCT\11.1.0\DB_1\RDBMS\ARC00052_0690320951
.001 RECID=3 STAMP=690374154
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_52_544GG9LY_.ARC RECID=4 STAMP=690374154
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_53_544JOW42_.ARC RECID=5 STAMP=690584984
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_54_544KG613_.ARC RECID=6 STAMP=690584985
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
4\O1_MF_1_55_545KFV3K_.ARC RECID=7 STAMP=690584985
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
5\O1_MF_1_56_546YP173_.ARC RECID=8 STAMP=690584985
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_57_549HVW3T_.ARC RECID=9 STAMP=690584985
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_58_549SRJFK_.ARC RECID=10 STAMP=690584985
validation succeeded for archived log
archived log file name=C:\APP\ASMA\PRODUCT\11.1.0\DB_1\RDBMS\ARC00001_0690585350
.001 RECID=11 STAMP=690586066
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_1_54BXDKH5_.ARC RECID=12 STAMP=690586066
validation succeeded for archived log
archived log file name=C:\APP\ASMA\PRODUCT\11.1.0\DB_1\RDBMS\ARC00002_0690585350
.001 RECID=13 STAMP=690586066
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_2_54BXDLGO_.ARC RECID=14 STAMP=690586066
validation succeeded for archived log
archived log file name=C:\APP\ASMA\PRODUCT\11.1.0\DB_1\RDBMS\ARC00003_0690585350
.001 RECID=15 STAMP=690586072
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_3_54BXDR2G_.ARC RECID=16 STAMP=690586072
validation succeeded for archived log
archived log file name=C:\APP\ASMA\PRODUCT\11.1.0\DB_1\RDBMS\ARC00001_0690586980
.001 RECID=17 STAMP=690587239
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_1_54BYK6R6_.ARC RECID=18 STAMP=690587239
validation succeeded for archived log
archived log file name=C:\APP\ASMA\PRODUCT\11.1.0\DB_1\RDBMS\ARC00002_0690586980
.001 RECID=19 STAMP=690587241
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_2_54BYK8RB_.ARC RECID=20 STAMP=690587241
validation succeeded for archived log
archived log file name=C:\APP\ASMA\PRODUCT\11.1.0\DB_1\RDBMS\ARC00003_0690586980
.001 RECID=21 STAMP=690587246
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_3_54BYKG92_.ARC RECID=22 STAMP=690587246
validation succeeded for archived log
archived log file name=C:\APP\ASMA\PRODUCT\11.1.0\DB_1\RDBMS\ARC00004_0690586980
.001 RECID=23 STAMP=690587248
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_4_54BYKJ7O_.ARC RECID=24 STAMP=690587248
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_1_54C30R5O_.ARC RECID=29 STAMP=690592259
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_2_54C30TYL_.ARC RECID=30 STAMP=690592260
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_3_54C30Z09_.ARC RECID=31 STAMP=690592260
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_4_54C35164_.ARC RECID=32 STAMP=690592260
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
7\O1_MF_1_1_54DB81TJ_.ARC RECID=33 STAMP=690638093
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
7\O1_MF_1_2_54DH6QCR_.ARC RECID=34 STAMP=690638094
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
7\O1_MF_1_3_54DH6T9T_.ARC RECID=35 STAMP=690638094
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
7\O1_MF_1_4_54DH6WYB_.ARC RECID=36 STAMP=690638094
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
7\O1_MF_1_1_54F09D13_.ARC RECID=37 STAMP=690655003
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
7\O1_MF_1_2_54F09HT0_.ARC RECID=38 STAMP=690655004
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
7\O1_MF_1_3_54F09M5Z_.ARC RECID=39 STAMP=690655004
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_1_54BZ160N_.ARC RECID=25 STAMP=690590221
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_2_54BZ17LK_.ARC RECID=26 STAMP=690590221
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_3_54BZ1DMY_.ARC RECID=27 STAMP=690590221
validation succeeded for archived log
archived log file name=C:\APP\ASMA\FLASH_RECOVERY_AREA\TEST\ARCHIVELOG\2009_06_2
6\O1_MF_1_4_54BZ1HFL_.ARC RECID=28 STAMP=690590222
Crosschecked 39 objects
 
 
RMAN> list expired archivelog all;
 
specification does not match any archived log in the recovery catalog
 
RMAN> list expired archivelog from scn 1951672;
 
specification does not match any archived log in the recovery catalog
 
RMAN>
 
RMAN> exit
 
 
Recovery Manager complete.

Open in new window

mrjoltcola

Please retry the whole restore without the "reset database to incarnation 4;" command. Don't use validate, actually do the restore and full recover. I think this is simply due to your setting to incarnation 4, then RMAN will not restore past the last SCN in incarnation 4.

When I recover across resetlogs boundaries, I still always restore the most recent controlfile / incarnation, not a previous one.