how to list archivelogs that need backup

user8957255 · Aug 27, 2013 8:40AM

Can someone help me with RMAN command. I would like to rman command syntax only for the following scenarios.

I have archivelogs the disk , sequence = 40, 41, 42 , 43

and say, I backed up only sequence 41 file.

next time I want to list (not backup) which archivelogfiles have not been backed up since the last backup (which was sequence 41 was backedup).

I would like to be able to list 40, 42. 43 that needed to be backed up as a list in the RMAN.

I understand that I can look at find from v$archived_log view and I can backup using (backup archivelog all not backed up 1 times to device type disk - i am ok with this).

I need to be able to list the archivelogs that need backup from existing disk-directory of archivelog files from RMAN prompt.

please help me with the command syntax. I appreciate it.

Balazs Papp · Aug 27, 2013 9:00AM

list archivelog all backed up 0 times to disk/sbt_tape;

DK2010 · Aug 27, 2013 9:01AM

Hi,

You can check with the Command

RMAN>crosscheck archivelog all;

HTH

user8957255 · Aug 27, 2013 9:16AM

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

user8957255 · Aug 27, 2013 9:21AM

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

DK2010 · Aug 27, 2013 9:56AM

Hi

Try this Backup the sequence using the command BACKUP ARCHIVELOG SEQUENCE 71 DELETE INPUT;

and then check it won't come is list.

user8957255 · Aug 27, 2013 10:07AM

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

EdStevens · Aug 27, 2013 4:50PM

DK2010 wrote:

Hi,

You can check with  the Command
RMAN>crosscheck archivelog all;

HTH

Please explain how a crosschek is supposed to report archivelogs that need to be backed up.

the purpose of crosscheck is to determine if the archivelogs (or backups, if that is what you crosscheck) listed in the repository are still where the repository record says they are. If they have gone missing, their record in the repository is flagged as 'expired'. Has nothing to do with 'needing backup'

EdStevens · Aug 27, 2013 5:29PM

user8957255 wrote:

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

I can certainly understand the intellectual curiosity, and off-hand I don't have an answer. But from a pragmatic level, one has to wonder why you need to report this? If you have a regularly scheduled job to backup your archivelogs, you know that at some point after said job runs you will start to collect new (and un-backed up) archivelogs. So why need to report the ones that haven't yet been backed up.

If this is for the clueless PHB (is that redundant?) try this:

rman target / << EOF

backup archivelog all not backed up 1 times;

exit

EOF

echo Archivelogs not backed up:

echo No archivelogs to report.

user8957255 · Aug 28, 2013 7:19AM

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.

Hemant K Chitale · Aug 28, 2013 9:53PM

Never tried this and I don't have access to a database to validate this .... but how about querying the BACKUP_COUNT column in V$ARCHIVED_LOG ?

Hemant K Chitale

Forum Stats

Quick Links

Categories

how to list archivelogs that need backup

Answers

On-Premise Applications

On-Premise Infrastructure

Industry Applications

Leadership Forums

On-Premise Applications

On-Premise Infrastructure

Industry Applications

Leadership Forums

Forum Stats

Categories

how to list archivelogs that need backup

Answers