Solved

Incomplete Recovery (11.1.6 windows)

Posted on 2009-06-27
8
418 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:monto1
  • 5
  • 3
8 Comments
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
Comment Utility
Is there a specific reason you are recovering from incarnation 4? You are recovering through resetlogs (which is ok in 10g+ if you have 100% archive history). At first glance, my feeling is you've deleted an archivelog at some point, so RMAN cannot find the log to recover past incarnation 7, sequence 5, for SCN 1952116

When is the last backup from? Your test table was created in incarnation 7. Do you not have a more recent backup of the database?

I think the core problem is missing an archive log and restoring from too old of a backupset.
0
 

Author Comment

by:monto1
Comment Utility
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

0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
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.
0
 

Author Comment

by:monto1
Comment Utility
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

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
Still seems you have missing logs. Find out if you have missing archive logs.

RMAN> crosscheck archivelog all;
RMAN> list expired archivelog all;
0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
Also try:

RMAN> list expired archivelog from scn 1951672;

That is the SCN from your last resetlogs.
0
 

Author Comment

by:monto1
Comment Utility
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

0
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now