Forum Stats

  • 3,837,795 Users
  • 2,262,300 Discussions
  • 7,900,394 Comments

Discussions

how to list archivelogs that need backup

user8957255
user8957255 Member Posts: 11
edited Aug 29, 2013 8:55AM in Recovery Manager (RMAN)

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.

Tagged:
«1

Answers

  • Balazs Papp
    Balazs Papp Member Posts: 748 Gold Badge

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

  • DK2010
    DK2010 Member Posts: 1,542 Silver Trophy

    Hi,

    You can check with  the Command

    RMAN>crosscheck archivelog all;

    HTH

  • user8957255
    user8957255 Member Posts: 11
    edited Aug 27, 2013 9:24AM

    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
    user8957255 Member Posts: 11
    edited Aug 27, 2013 9:25AM

    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
    DK2010 Member Posts: 1,542 Silver Trophy

    Hi

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

    and then check it won't come is list.

  • 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
    EdStevens Member Posts: 28,778 Gold Crown
    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
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    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
    user8957255 Member Posts: 11
    edited Aug 28, 2013 7:21AM

    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
    Hemant K Chitale Member Posts: 15,759 Blue Diamond

    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

    Hemant K Chitale
This discussion has been closed.