how to list archivelogs that need backup

Thanks for reply Balazrapp,

I tried list archivelog all backed up 0 times to disk OR list archivelog all backed up 0 times to device type disk;

Both commands still include archivelog (sequence 71 - in this example). I dont know why. Help me please.

I need to be able list only archive logs that have not been atleast once backed up to disk (OR list only archivelogs that have been backed 0 times to disk). In this case, example it should have excluded archlogsequence 71 file. It did not. I dont understand why.

Please see the output.

RMAN> list backup of archivelog all;

specification does not match any backup in the repository

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name SR1

=====================================================================

Key     Thrd Seq     S Low Time

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

19415   1    70      A 27-AUG-13

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

19416   1    71      A 27-AUG-13

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

19417   1    72      A 27-AUG-13

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

19418   1    73      A 27-AUG-13

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

RMAN> backup archivelog sequence = 71;

Starting backup at 27-AUG-13

using channel ORA_DISK_1

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=71 RECID=313 STAMP=824548206

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

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

piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\SR1\BACKUPSET\2013_08_27\O1_MF_ANNNN_TAG20130827T091036_91S9BDQR_.BKP tag=TAG20130827T091036 comment=NONE

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

Finished backup at 27-AUG-13

RMAN> list backup of archivelog all;

List of Backup Sets

===================

BS Key  Size       Device Type Elapsed Time Completion Time

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

19697   9.00K      DISK        00:00:00     27-AUG-13

        BP Key: 19698   Status: AVAILABLE  Compressed: NO  Tag: TAG20130827T091036

        Piece Name: C:\ORACLE\FLASH_RECOVERY_AREA\SR1\BACKUPSET\2013_08_27\O1_MF_ANNNN_TAG20130827T091036_91S9BDQR_.BKP

  List of Archived Logs in backup set 19697

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    71      4832738    27-AUG-13 4832752    27-AUG-13

RMAN> list archivelog all backed up 0 times to device type disk;

List of Archived Log Copies for database with db_unique_name SR1

=====================================================================

Key     Thrd Seq     S Low Time

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

19415   1    70      A 27-AUG-13

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

19416   1    71      A 27-AUG-13

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

19417   1    72      A 27-AUG-13

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

19418   1    73      A 27-AUG-13

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

RMAN> list archivelog all backed up 0 times to disk;

List of Archived Log Copies for database with db_unique_name SR1

=====================================================================

Key     Thrd Seq     S Low Time

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

19415   1    70      A 27-AUG-13

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

19416   1    71      A 27-AUG-13

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

19417   1    72      A 27-AUG-13

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

19418   1    73      A 27-AUG-13

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

Hi DK2010,

I did run the crosscheck archivelog all;

I still am not able to list only archivelog files that have not been backed up to disk.

The output I got is as follows. The output shows that the archivelogs (sequences 70, 71, 72, 73 are on disk - it still does not show me it excludes 71 which has already been backed up - please see my output in the above reply)

Thanks for taking time to reply to my question.

RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=139 device type=DISK

validation succeeded for archived log

archived log file name=C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_27\O1_MF_1_70_91S99BHY_.ARC RECID=312 STAMP=824548202

validation succeeded for archived log

archived log file name=C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_27\O1_MF_1_71_91S99GFM_.ARC RECID=313 STAMP=824548206

validation succeeded for archived log

archived log file name=C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_27\O1_MF_1_72_91S99KHP_.ARC RECID=314 STAMP=824548209

validation succeeded for archived log

archived log file name=C:\ORACLE\FLASH_RECOVERY_AREA\SR1\ARCHIVELOG\2013_08_27\O1_MF_1_73_91S99NCL_.ARC RECID=315 STAMP=824548212

Crosschecked 4 objects

Hi,

I know I could do that. But deleting backup archivelog sequence 71 does not do the job.  crosscheck will check with disk files for availability. If i delete 71 and 72 (delete 72 without a backup), then it will list 70 and 73 (basically lists whats on archivelog destination on disk) ...

but the issue for me here, is to be able to, list which archivelogs really need backup. I want to be able list archivelogs that have not been backedup at all. The backup command does the job (but before doing the back up, I want a list of the archivelog files that need back), the backup command: backup archivelog all not backed up 1 times; this works.

what i need is listing: list archivelog all not backed up 1 times (this does not work).

RMAN> backup archivelog all not backed up 1 times;

Starting backup at 27-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_71_91S99GFM_.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=70 RECID=312 STAMP=824548202

input archived log thread=1 sequence=72 RECID=314 STAMP=824548209

input archived log thread=1 sequence=73 RECID=315 STAMP=824548212

input archived log thread=1 sequence=74 RECID=316 STAMP=824551447

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

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

piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\SR1\BACKUPSET\2013_08_27\O1_MF_ANNNN_TAG20130827T100408_91SDGRR3_.BKP tag=TAG20130827T100408 comment=NONE

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

Finished backup at 27-AUG-13

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.

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