Forum Stats

  • 3,838,328 Users
  • 2,262,355 Discussions


how to list archivelogs that need backup



  • user8957255
    user8957255 Member Posts: 11
    edited Aug 29, 2013 7:49AM


    Hemant thanks for your reply.

    What you suggested was from sqlplus and querying backup count of v$archive_log view. Yes, this keeps track of backup count of each archivelogged file.

    I wanted to be able to list the archivelogseq files from RMAN with rman command.

    The issue was

    rman can list what has been backed up (list archivelog all backed up 1 times to disk)

    rman can backup (actual backup) (backup archivelog all not backed up times to disk)

    But, rman, does not like my syntax to list : list archivelog all not backed up 1 times to disk (rman doesnt like, "not backed up in" the list command).

    However I was able to find a workaround for this. This is backup validate archivelog. This doesnot do actual backup of archive log. but instead it checks and lists which have not been backedup,

    This is like validating the physical availability of the files. The limitation for my purpose of this command, seems to be even if 1 archivelog is missing ( i just renamed), it doesnt even list the archivelogs at all. but thats ok i guess. Another thing,

    it seems to switch the current log even if backup validate archivelog, output has errors or not. I guess that's because for safety sake current redolog  is switched and archive is created.

    Please see the output that I tried.

    RMAN> list archivelog all;

    List of Archived Log Copies for database with db_unique_name SR1


    Key     Thrd Seq     S Low Time

    ------- ---- ------- - ---------

    21621   1    79      A 27-AUG-13

            Name: C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_27\O1_MF_1_79_91T8DX20_.ARC

    21622   1    80      A 27-AUG-13

            Name: C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_27\O1_MF_1_80_91TJC1X5_.ARC

    21623   1    81      A 27-AUG-13

            Name: C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_27\O1_MF_1_81_91TPMCF3_.ARC

    21624   1    82      A 27-AUG-13

            Name: C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_28\O1_MF_1_82_91TXHOWH_.ARC

    21625   1    83      A 28-AUG-13

            Name: C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_28\O1_MF_1_83_91VP011K_.ARC

    21626   1    84      A 28-AUG-13

            Name: C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_28\O1_MF_1_84_91WC660R_.ARC

    21627   1    85      A 28-AUG-13

            Name: C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_28\O1_MF_1_85_91X3S7C8_.ARC

    21628   1    86      A 28-AUG-13

            Name: C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_28\O1_MF_1_86_91X4V58M_.ARC

    21629   1    87      A 28-AUG-13

            Name: C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_28\O1_MF_1_87_91XBZHSV_.ARC

    21630   1    88      A 28-AUG-13

            Name: C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_29\O1_MF_1_88_91XN64W8_.ARC

    RMAN> backup archivelog sequence=80;

    Starting backup at 29-AUG-13

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=140 device type=DISK

    channel ORA_DISK_1: starting archived log backup set

    channel ORA_DISK_1: specifying archived log(s) in backup set

    input archived log thread=1 sequence=80 RECID=322 STAMP=824588199

    channel ORA_DISK_1: starting piece 1 at 29-AUG-13

    channel ORA_DISK_1: finished piece 1 at 29-AUG-13

    piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\SR1\BACKUPSET\2013_08_29\O1_MF_ANNNN_TAG20130829T071248_91YC5JPJ_.BKP tag=TAG20130829T071248 comment=NONE

    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

    Finished backup at 29-AUG-13

    RMAN> backup validate archivelog all not backed up 1 times;

    Starting backup at 29-AUG-13

    current log archived

    using channel ORA_DISK_1

    skipping archived log file C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_27\O1_MF_1_80_91TJC1X5_.ARC; already backed up 1 time(s)

    channel ORA_DISK_1: starting archived log backup set

    channel ORA_DISK_1: specifying archived log(s) in backup set

    input archived log thread=1 sequence=79 RECID=321 STAMP=824580062

    input archived log thread=1 sequence=81 RECID=323 STAMP=824594611

    input archived log thread=1 sequence=82 RECID=324 STAMP=824601655

    input archived log thread=1 sequence=83 RECID=325 STAMP=824626754

    input archived log thread=1 sequence=84 RECID=326 STAMP=824648457

    input archived log thread=1 sequence=85 RECID=327 STAMP=824673645

    input archived log thread=1 sequence=86 RECID=328 STAMP=824674729

    input archived log thread=1 sequence=87 RECID=329 STAMP=824681009

    input archived log thread=1 sequence=88 RECID=330 STAMP=824690438

    input archived log thread=1 sequence=89 RECID=331 STAMP=824714047

    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

    List of Archived Logs


    Thrd Seq     Status Blocks Failing Blocks Examined Name

    ---- ------- ------ -------------- --------------- ---------------

    1    79      OK     0              76065           C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_27\O1_MF_1_79_91T8DX20_.ARC

    1    81      OK     0              75798           C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_27\O1_MF_1_81_91TPMCF3_.ARC

    1    82      OK     0              76217           C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_28\O1_MF_1_82_91TXHOWH_.ARC

    1    83      OK     0              76202           C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_28\O1_MF_1_83_91VP011K_.ARC

    1    84      OK     0              76389           C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_28\O1_MF_1_84_91WC660R_.ARC

    1    85      OK     0              76122           C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_28\O1_MF_1_85_91X3S7C8_.ARC

    1    86      OK     0              85711           C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_28\O1_MF_1_86_91X4V58M_.ARC

    1    87      OK     0              77335           C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_28\O1_MF_1_87_91XBZHSV_.ARC

    1    88      OK     0              76278           C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_29\O1_MF_1_88_91XN64W8_.ARC

    1    89      OK     0              75391           C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_29\O1_MF_1_89_91YC7YF5_.ARC

    Finished backup at 29-AUG-13

    RMAN> list backup of archivelog  from sequence 79;

    List of Backup Sets


    BS Key  Size       Device Type Elapsed Time Completion Time

    ------- ---------- ----------- ------------ ---------------

    21641   40.79M     DISK        00:00:02     29-AUG-13

            BP Key: 21643   Status: AVAILABLE  Compressed: NO  Tag: TAG20130829T071248

            Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\SR1\BACKUPSET\2013_08_29\O1_MF_ANNNN_TAG20130829T071248_91YC5JPJ_.BKP

      List of Archived Logs in backup set 21641

      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

      ---- ------- ---------- --------- ---------- ---------

      1    80      4884785    27-AUG-13 4899740    27-AUG-13

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    user8957255 wrote:
    Hi EdStevens,
    Thanks for taking time, to reply to my question/posting. My question was simple. Rman seems to know which archlogsequences files need back up by "doing" the backup itself (backup archivelog all not backed up 1 times). So this means rman has a list inside its software which archlogfiles have not been backed up atleast once (never backedup). how can as a user(me) of rman can know that list. if rman knows it, i should be able to see the list. why not. fair?!?
    thanks for script that you provided. yes, it was my lack of knowledge of rman and some curiosity, I am trying to learn. not for my PHB 
    thanks again.

    Fair enough.  It always helps clarify and focus a discussion if one understands the business problem being solved.  Else one spends a lot of time getting the wrong solution to the wrong problem.  In your case, the business problem is your own curiosity.  And that is a legitimate issue.  But just like anything else, now that we know that that is the "problem", we can better address it.

This discussion has been closed.