how to list archivelogs that need backup

user8957255

Hi

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

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.