3

I am fully backing up my standby database every night with following statement. (please note that I do not use rman catalog but control file)

backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL_STBY format '/oradata/archive/fast_recovery_area/%d_%T_%s_%p_FULL_STBY' plus archivelog tag ORCL_FULL_STBY delete all input;

I wanted to check with 'restore ... preview' if my backup is good enough to restore database from zero.

result= failure!

reason:

"no backup of archived log for thread 1 with sequence 65 and starting SCN of 49020676 found to restore"

I have been reading oracle documentation and blogs since days but I could not understand that even though I take full online backup yesterday if I want to restore my database today I still need archive logs of last month?

can you please review my analysis and tell me where do I do wrong ?

for example a piece from my last backup;

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1008    Full    199.25M    DISK        00:04:44     08-OCT-15
        BP Key: 1010   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1057_1_FULL_STBY
  List of Datafiles in backup set 1008
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 50570723   08-OCT-15 /oradata/data/qipdb1/system01.dbf
  4       Full 50570723   08-OCT-15 /oradata/data/qipdb1/users01.dbf

as you can see 'Ckp SCN' is 50570723 so my understanding tells me if restore these backups I will need archive logs on top of these SCN. for me best candidate is

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1004    65.25M     DISK        00:01:24     08-OCT-15
        BP Key: 1006   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_STBY/backupset/2015_10_08/o1_mf_annnn_ORCL_FULL_STBY_c1dkfw9k_.bkp

  List of Archived Logs in backup set 1004
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    79      50310555   07-OCT-15 50570723   08-OCT-15

BUT in output of 'restore ... preview' RMAN stil tells me that

no backup of archived log for thread 1 with sequence 65 and starting SCN of 49020676 found to restore
no backup of archived log for thread 1 with sequence 66 and starting SCN of 49020693 found to restore
no backup of archived log for thread 1 with sequence 67 and starting SCN of 49020696 found to restore
no backup of archived log for thread 1 with sequence 68 and starting SCN of 49020761 found to restore
no backup of archived log for thread 1 with sequence 69 and starting SCN of 49020836 found to restore
no backup of archived log for thread 1 with sequence 70 and starting SCN of 49020919 found to restore
no backup of archived log for thread 1 with sequence 71 and starting SCN of 49021021 found to restore
no backup of archived log for thread 1 with sequence 72 and starting SCN of 49155291 found to restore
no backup of archived log for thread 1 with sequence 73 and starting SCN of 49324912 found to restore
no backup of archived log for thread 1 with sequence 74 and starting SCN of 49498102 found to restore

when I look at V$ARCHIVED_LOG I see that indeed these archived logs seem deleted because of 'plus archivelog tag ORCL_FULL_STBY delete all input' they were deleetd after being backed up. and due to my retention policy & script ; I delete backups of archived logs also that are older than 2 days.

S  SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
- ---------- ------------- ------------
D         65      49020676     49020693
A         65      49020676     49020693
D         68      49020761     49020836
D         66      49020693     49020696
D         67      49020696     49020761
D         69      49020836     49020919
D         70      49020919     49021021
D         71      49021021     49155291
D         72      49155291     49324912
D         73      49324912     49498102
D         74      49498102     49632131

still I cannot understand why rman complains about archived log which are for older sequence (and SCN) than my last backup :( because I was assuming that relevent archived logs should have been already applied to these data files, no?

my final aim is to deliver below for the case we lost everything.

run {
restore database;
recover database;
}

thanks in advance, Halit

P.S:

RMAN> list backup;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
978     61.26M     DISK        00:01:20     06-OCT-15
        BP Key: 978   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_STBY/backupset/2015_10_06/o1_mf_annnn_ORCL_FULL_STBY_c1620vb0_.bkp

  List of Archived Logs in backup set 978
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    76      49786113   05-OCT-15 49979629   06-OCT-15

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
979     63.87M     DISK        00:01:23     06-OCT-15
        BP Key: 979   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_STBY/backupset/2015_10_06/o1_mf_annnn_ORCL_FULL_STBY_c1620v7l_.bkp

  List of Archived Logs in backup set 979
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    75      49632131   04-OCT-15 49786113   05-OCT-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
980     Full    38.45M     DISK        00:01:18     06-OCT-15
        BP Key: 980   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151006_1034_1_FULL_STBY
  List of Datafiles in backup set 980
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 49786113   05-OCT-15 /oradata/data/qipdb1/qip_data01.dbf
  7       Full 49786113   05-OCT-15 /oradata/data/qipdb1/qip_lob01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
981     Full    44.01M     DISK        00:01:27     06-OCT-15
        BP Key: 981   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151006_1033_1_FULL_STBY
  List of Datafiles in backup set 981
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3       Full 49786113   05-OCT-15 /oradata/data/qipdb1/undotbs01.dbf
  6       Full 49786113   05-OCT-15 /oradata/data/qipdb1/qip_index01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
982     Full    133.90M    DISK        00:03:52     06-OCT-15
        BP Key: 982   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151006_1031_1_FULL_STBY
  List of Datafiles in backup set 982
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 49786113   05-OCT-15 /oradata/data/qipdb1/sysaux01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
983     Full    198.91M    DISK        00:04:43     06-OCT-15
        BP Key: 983   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151006_1032_1_FULL_STBY
  List of Datafiles in backup set 983
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 49786113   05-OCT-15 /oradata/data/qipdb1/system01.dbf
  4       Full 49786113   05-OCT-15 /oradata/data/qipdb1/users01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
987     61.89M     DISK        00:01:21     07-OCT-15
        BP Key: 987   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_STBY/backupset/2015_10_07/o1_mf_annnn_ORCL_FULL_STBY_c18pdvyd_.bkp

  List of Archived Logs in backup set 987
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    77      49979629   06-OCT-15 50170342   06-OCT-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
988     Full    38.45M     DISK        00:01:17     07-OCT-15
        BP Key: 988   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151007_1042_1_FULL_STBY
  List of Datafiles in backup set 988
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 49979629   06-OCT-15 /oradata/data/qipdb1/qip_data01.dbf
  7       Full 49979629   06-OCT-15 /oradata/data/qipdb1/qip_lob01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
989     Full    44.04M     DISK        00:01:27     07-OCT-15
        BP Key: 989   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151007_1041_1_FULL_STBY
  List of Datafiles in backup set 989
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3       Full 49979629   06-OCT-15 /oradata/data/qipdb1/undotbs01.dbf
  6       Full 49979629   06-OCT-15 /oradata/data/qipdb1/qip_index01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
990     Full    135.09M    DISK        00:03:53     07-OCT-15
        BP Key: 990   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151007_1039_1_FULL_STBY
  List of Datafiles in backup set 990
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 49979629   06-OCT-15 /oradata/data/qipdb1/sysaux01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
991     Full    199.02M    DISK        00:04:43     07-OCT-15
        BP Key: 991   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151007_1040_1_FULL_STBY
  List of Datafiles in backup set 991
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 49979629   06-OCT-15 /oradata/data/qipdb1/system01.dbf
  4       Full 49979629   06-OCT-15 /oradata/data/qipdb1/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
992     Full    80.00K     DISK        00:00:00     07-OCT-15
        BP Key: 992   Status: AVAILABLE  Compressed: NO  Tag: SPFILE_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151007_1043_1_SPFILE_STBY
  SPFILE Included: Modification time: 01-OCT-15
  SPFILE db_unique_name: QIPDB1_STBY

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
993     Full    11.92M     DISK        00:00:01     07-OCT-15
        BP Key: 993   Status: AVAILABLE  Compressed: NO  Tag: ORCL_CURRENT_CONTROL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151007_1044_1_CURRENT_CONTROLFILE_STBY
  Standby Control File Included: Ckp SCN: 49979629     Ckp time: 06-OCT-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
994     Full    11.95M     DISK        00:00:01     07-OCT-15
        BP Key: 994   Status: AVAILABLE  Compressed: NO  Tag: TAG20151007T013655
        Piece Name: /oradata/archive/fast_recovery_area/c-2277588724-20151007-00_AUTOBACKUP_STBY
  SPFILE Included: Modification time: 01-OCT-15
  SPFILE db_unique_name: QIPDB1_STBY
  Standby Control File Included: Ckp SCN: 49979629     Ckp time: 06-OCT-15

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
995     62.50M     DISK        00:01:21     08-OCT-15
        BP Key: 997   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_STBY/backupset/2015_10_08/o1_mf_annnn_ORCL_FULL_STBY_c1cbrvdy_.bkp

  List of Archived Logs in backup set 995
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    78      50170342   06-OCT-15 50310555   07-OCT-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
996     Full    38.45M     DISK        00:01:18     08-OCT-15
        BP Key: 998   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1050_1_FULL_STBY
  List of Datafiles in backup set 996
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 50170342   06-OCT-15 /oradata/data/qipdb1/qip_data01.dbf
  7       Full 50170342   06-OCT-15 /oradata/data/qipdb1/qip_lob01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
997     Full    47.53M     DISK        00:01:33     08-OCT-15
        BP Key: 999   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1049_1_FULL_STBY
  List of Datafiles in backup set 997
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3       Full 50170342   06-OCT-15 /oradata/data/qipdb1/undotbs01.dbf
  6       Full 50170342   06-OCT-15 /oradata/data/qipdb1/qip_index01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
998     Full    135.12M    DISK        00:03:54     08-OCT-15
        BP Key: 1000   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1047_1_FULL_STBY
  List of Datafiles in backup set 998
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 50170342   06-OCT-15 /oradata/data/qipdb1/sysaux01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
999     Full    199.20M    DISK        00:04:43     08-OCT-15
        BP Key: 1001   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1048_1_FULL_STBY
  List of Datafiles in backup set 999
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 50170342   06-OCT-15 /oradata/data/qipdb1/system01.dbf
  4       Full 50170342   06-OCT-15 /oradata/data/qipdb1/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1000    Full    80.00K     DISK        00:00:00     08-OCT-15
        BP Key: 1002   Status: AVAILABLE  Compressed: NO  Tag: SPFILE_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1051_1_SPFILE_STBY
  SPFILE Included: Modification time: 01-OCT-15
  SPFILE db_unique_name: QIPDB1_STBY

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1001    Full    11.92M     DISK        00:00:02     08-OCT-15
        BP Key: 1003   Status: AVAILABLE  Compressed: NO  Tag: ORCL_CURRENT_CONTROL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1052_1_CURRENT_CONTROLFILE_STBY
  Standby Control File Included: Ckp SCN: 50170342     Ckp time: 06-OCT-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1002    Full    11.95M     DISK        00:00:01     08-OCT-15
        BP Key: 1004   Status: AVAILABLE  Compressed: NO  Tag: TAG20151008T013655
        Piece Name: /oradata/archive/fast_recovery_area/c-2277588724-20151008-00_AUTOBACKUP_STBY
  SPFILE Included: Modification time: 01-OCT-15
  SPFILE db_unique_name: QIPDB1_STBY
  Standby Control File Included: Ckp SCN: 50170342     Ckp time: 06-OCT-15

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1003    18.12M     DISK        00:00:23     08-OCT-15
        BP Key: 1005   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_STBY/backupset/2015_10_08/o1_mf_annnn_ORCL_FULL_STBY_c1dkfwd1_.bkp

  List of Archived Logs in backup set 1003
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    80      50570723   08-OCT-15 50633991   08-OCT-15

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1004    65.25M     DISK        00:01:24     08-OCT-15
        BP Key: 1006   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_STBY/backupset/2015_10_08/o1_mf_annnn_ORCL_FULL_STBY_c1dkfw9k_.bkp

  List of Archived Logs in backup set 1004
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    79      50310555   07-OCT-15 50570723   08-OCT-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1005    Full    38.45M     DISK        00:01:18     08-OCT-15
        BP Key: 1007   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1059_1_FULL_STBY
  List of Datafiles in backup set 1005
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 50570723   08-OCT-15 /oradata/data/qipdb1/qip_data01.dbf
  7       Full 50570723   08-OCT-15 /oradata/data/qipdb1/qip_lob01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1006    Full    43.21M     DISK        00:01:26     08-OCT-15
        BP Key: 1008   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1058_1_FULL_STBY
  List of Datafiles in backup set 1006
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3       Full 50570723   08-OCT-15 /oradata/data/qipdb1/undotbs01.dbf
  6       Full 50570723   08-OCT-15 /oradata/data/qipdb1/qip_index01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1007    Full    134.84M    DISK        00:03:54     08-OCT-15
        BP Key: 1009   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1056_1_FULL_STBY
  List of Datafiles in backup set 1007
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 50570723   08-OCT-15 /oradata/data/qipdb1/sysaux01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1008    Full    199.25M    DISK        00:04:44     08-OCT-15
        BP Key: 1010   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1057_1_FULL_STBY
  List of Datafiles in backup set 1008
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 50570723   08-OCT-15 /oradata/data/qipdb1/system01.dbf
  4       Full 50570723   08-OCT-15 /oradata/data/qipdb1/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1009    Full    80.00K     DISK        00:00:00     08-OCT-15
        BP Key: 1011   Status: AVAILABLE  Compressed: NO  Tag: SPFILE_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1060_1_SPFILE_STBY
  SPFILE Included: Modification time: 08-OCT-15
  SPFILE db_unique_name: QIPDB1_STBY

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1010    Full    11.92M     DISK        00:00:02     08-OCT-15
        BP Key: 1012   Status: AVAILABLE  Compressed: NO  Tag: ORCL_CURRENT_CONTROL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151008_1061_1_CURRENT_CONTROLFILE_STBY
  Standby Control File Included: Ckp SCN: 50570723     Ckp time: 08-OCT-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1011    Full    11.95M     DISK        00:00:01     08-OCT-15
        BP Key: 1013   Status: AVAILABLE  Compressed: NO  Tag: TAG20151008T123640
        Piece Name: /oradata/archive/fast_recovery_area/c-2277588724-20151008-01_AUTOBACKUP_STBY
  SPFILE Included: Modification time: 08-OCT-15
  SPFILE db_unique_name: QIPDB1_STBY
  Standby Control File Included: Ckp SCN: 50570723     Ckp time: 08-OCT-15

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1012    63.90M     DISK        00:01:22     09-OCT-15
        BP Key: 1014   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_STBY/backupset/2015_10_09/o1_mf_annnn_ORCL_FULL_STBY_c1h5t2ck_.bkp

  List of Archived Logs in backup set 1012
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    81      50633991   08-OCT-15 50793900   09-OCT-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1013    Full    38.45M     DISK        00:01:18     09-OCT-15
        BP Key: 1015   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151009_1067_1_FULL_STBY
  List of Datafiles in backup set 1013
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 50793900   09-OCT-15 /oradata/data/qipdb1/qip_data01.dbf
  7       Full 50793900   09-OCT-15 /oradata/data/qipdb1/qip_lob01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1014    Full    43.20M     DISK        00:01:26     09-OCT-15
        BP Key: 1016   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151009_1066_1_FULL_STBY
  List of Datafiles in backup set 1014
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  3       Full 50793900   09-OCT-15 /oradata/data/qipdb1/undotbs01.dbf
  6       Full 50793900   09-OCT-15 /oradata/data/qipdb1/qip_index01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1015    Full    134.90M    DISK        00:03:54     09-OCT-15
        BP Key: 1017   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151009_1064_1_FULL_STBY
  List of Datafiles in backup set 1015
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  2       Full 50793900   09-OCT-15 /oradata/data/qipdb1/sysaux01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1016    Full    199.40M    DISK        00:04:44     09-OCT-15
        BP Key: 1018   Status: AVAILABLE  Compressed: YES  Tag: ORCL_FULL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151009_1065_1_FULL_STBY
  List of Datafiles in backup set 1016
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 50793900   09-OCT-15 /oradata/data/qipdb1/system01.dbf
  4       Full 50793900   09-OCT-15 /oradata/data/qipdb1/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1017    Full    80.00K     DISK        00:00:01     09-OCT-15
        BP Key: 1019   Status: AVAILABLE  Compressed: NO  Tag: SPFILE_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151009_1068_1_SPFILE_STBY
  SPFILE Included: Modification time: 08-OCT-15
  SPFILE db_unique_name: QIPDB1_STBY

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1018    Full    11.92M     DISK        00:00:01     09-OCT-15
        BP Key: 1020   Status: AVAILABLE  Compressed: NO  Tag: ORCL_CURRENT_CONTROL_STBY
        Piece Name: /oradata/archive/fast_recovery_area/QIPDB1_20151009_1069_1_CURRENT_CONTROLFILE_STBY
  Standby Control File Included: Ckp SCN: 50793900     Ckp time: 09-OCT-15

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1019    Full    11.95M     DISK        00:00:01     09-OCT-15
        BP Key: 1021   Status: AVAILABLE  Compressed: NO  Tag: TAG20151009T123646
        Piece Name: /oradata/archive/fast_recovery_area/c-2277588724-20151009-00_AUTOBACKUP_STBY
  SPFILE Included: Modification time: 08-OCT-15
  SPFILE db_unique_name: QIPDB1_STBY
  Standby Control File Included: Ckp SCN: 50793900     Ckp time: 09-OCT-15

RMAN>
2

I do not see your exact database version anywhere, but it seems to be the bug described in the below MOS note:

RMAN-06025 - RMAN RESTORE DATABASE PREVIEW at standby site is asking for old log (Doc ID 1599013.1)

CAUSE

Checkpoint_change# standby controlfile not refreshed properly.

BUG 15876029 - RESTORE PREVIEW ASKING OLD ARCHIVE LOG IN STANDBY DATABASE

Closed as DUPLICATE of

BUG 8740124 - CURRENT STANDBY REDO LOG GROUP SHOULD BE INCLUDED IN THE DATABASE BACKUP BY RMAN

  SOLUTION

1) Workaround is to recreate the standby controlfile.

OR

2) upgrade to 11.2.0.4 or above where Bug 8740124  is fixed

OR

3) check for availability of patch 8740124
  • Thanks, I will try to apply workaround if it is really bug; Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options – Halit Sakca Oct 12 '15 at 7:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.