1 2 Previous Next 15 Replies Latest reply on Mar 20, 2019 8:09 AM by zhangshengdongly

    Missing Archivelogs

    serandrzej

      Hello,

       

      I have two Oracle DBs. On source I do on SAT full backup at 20:00 and every day archivelog backup at 19:00.

      Every day I copy all backups to the target, do catalog and recover.

      Today I noticed that few SCNs are missing and I cannot recover database.

      RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 774328 and starting SCN of 22398552207

      I executed rman list backup on destination and got following:

       

      Tag: TAG20190105T190015 Piece Name: /mnt/backup/ch1_ddddd_1_1.bk

           backup archivelog all not backed up 1 times delete input;

      ...

      1    774327  22398538539 05-JAN-19 22398552207 05-JAN-19

       

      Tag: TAG20190105T200013 INCR0 Piece Name: /mnt/backup/ch1_ddddd_1_1.bk

           backup as compressed backupset incremental level=0 database;

           delete noprompt obsolete;

       

      Tag: TAG20190106T190014 Piece Name: /mnt/backup/ch1_ddddd_1_1.bk

                backup archivelog all not backed up 1 times delete input;

       

        1    774337  22398761772 05-JAN-19 22398784885 05-JAN-19

      ...

       

      Any idea what has happened ? Did full backup break archivelog chain or smth else ? Thank you for any ideas.

        • 1. Re: Missing Archivelogs
          top.gun

          "On source I do on SAT full backup at 20:00 and every day archivelog backup at 19:00."

          Why are you backing up the archivelogs before the database backup?

          You are suppose to backup the archivelogs after the database, so you capture all the archivelogs generated while the database backup was running.

           

           

          "delete noprompt obsolete;"

          You also are doing this, which will delete archivelogs before the most recent full database backup.

          So any archvelogs not backed up, will be deleted as they are obsolete.

          1 person found this helpful
          • 2. Re: Missing Archivelogs
            Dude!

            Are you familiar with the RMAN concept of consistent and inconsistent backups? RMAN automatically performs a log switch before and after the backup. The later is necessary to accomplish consistency during the recovery process since not all data files are essentially copied by RMAN at the same time. The only way to get a consistent backup, which does not need recovery using archivelogs, is a backup while the database was properly shut down.

             

            Since you are running the backup of archivelogs the next day and use "delete input", you will have to make those backups available in order to be able to recover your level 0 backup. Why not simply using the following:

             

            RMAN> backup as compressed backupset incremental level 0 database plus archivelog delete input;

             

            This will make it easier to determine what backups need to be available to restore and recover the level 0 backup.

            1 person found this helpful
            • 3. Re: Missing Archivelogs
              serandrzej

              Full backup takes 16 h so when it starts at 20:00 is ready next day around 12:00. I thought that next day archivelog backup will catch up all missing archivelogs. But now I understand that after full backup they are obsolete as not needed any more and so deleted.

              • 4. Re: Missing Archivelogs
                Dude!

                 

                • 5. Re: Missing Archivelogs
                  serandrzej

                  Hello,

                  Around 1 T. I think I will replace my two commands with one you proposed. I assume that because of this delete obsolete my full backup cannot be recovered any more or is my assumption wrong?

                  On second server I started restore and recover after copying and catalog and got:

                   

                  ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

                  ORA-01194: file 1 needs more recovery to be consistent

                  ORA-01110: data file 1: '/opt/app/oracle/oradata/orcl/datafile/o1_mf_system_g3b9bxg8_.dbf'

                  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

                  RMAN-03002: failure of recover command at 01/09/2019 07:12:23

                  RMAN-06053: unable to perform media recovery because of missing log

                  RMAN-06025: no backup of archived log for thread 1 with sequence 777167 and starting SCN of 22459327149 found to restore

                   

                  first log in list backup:

                    List of Archived Logs in backup set 17544

                    Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

                    1    774337  22398761772 05-JAN-19 22398784885 05-JAN-19

                   

                  So looks I am missing Seq 777167 - 774336.

                  So basically this fatal command "delete noprompt obsolete;" destroyed two full backups: one I did on last SAT and current one. I can probably fix this issue by doing incremental backup lvl1.

                   

                  What I do not understand is following. Last week I did exactly the same procedure. I restored database and got the same error. But I was able to recover database after copying new archivelogs and catalog them.

                  • 6. Re: Missing Archivelogs
                    top.gun

                    If you want to make minimum changes to your backup method, then do this:

                     

                    # daily backup

                    backup archivelog all not backed up 1 times delete input;

                     

                    # SAT backup

                    backup as compressed backupset incremental level=0 database;

                    backup archivelog all not backed up 1 times delete input;

                    1 person found this helpful
                    • 7. Re: Missing Archivelogs
                      Dude!

                      Around 1 T. I think I will replace my two commands with one you proposed. I assume that because of this delete obsolete my full backup cannot be recovered any more or is my assumption wrong?

                      Wrong.

                       

                      So basically this fatal command "delete noprompt obsolete;" destroyed two full backups: one I did on last SAT and current one.

                       

                      Nonsense. Delete obsolete will not jeopardize any RMAN backup or recovery options according to your retention policy. RMAN will not delete any records that are required to satisfy the retention policy.

                       

                      What I do not understand is following. Last week I did exactly the same procedure. I restored database and got the same error. But I was able to recover database after copying new archivelogs and catalog them.

                      The same procedure should produce the same results, so something is different. Perhaps you did not restore the control file, or need to perform a RMAN crosscheck in order to remove invalid RMAN metadata, and also copy and catalog the necessary archivelog backups.

                      • 8. Re: Missing Archivelogs
                        Dude!

                        Btw, 16 hours for 1 TB is extremely inefficient. What are you using?

                        • 9. Re: Missing Archivelogs
                          serandrzej

                          Retention policy:

                          CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

                          If delete noprompt obsolete is not the problem what has happened with missing archive log sequences ?

                           

                          Regarding backup time no idea why it is so slow. This is source from which I have to migrate and it is not under my management.

                          • 10. Re: Missing Archivelogs
                            Dude!

                            I cannot look over your shoulder to know exactly what you are doing or using. Again, "delete obsolete" does not remove data that is required to satisfy your restore and recovery options according to the retention policy. If archivelogs are indeed missing then they must have been removed by the OS, but not RMAN, or still exist. I suggest you check "list backup" on the original server.

                            • 11. Re: Missing Archivelogs
                              serandrzej

                              I thought I understand what was going on but after conversetion with you I don't. The fact is that after full backup I am not able any more to recover database on second box because of missing archivelog sequences. In first Post I showed output from list backup. Last sequence before full backup was 774327  and first after ful backup 774337. So somwhere between last archive log backup SAM 19:00 and full backup SAM 20:00 and another archivelog backup SON 19:00 archivelogs disappeared.

                              • 12. Re: Missing Archivelogs
                                EdStevens

                                serandrzej wrote:

                                 

                                I thought I understand what was going on but after conversetion with you I don't. The fact is that after full backup I am not able any more to recover database on second box because of missing archivelog sequences. In first Post I showed output from list backup. Last sequence before full backup was 774327 and first after ful backup 774337. So somwhere between last archive log backup SAM 19:00 and full backup SAM 20:00 and another archivelog backup SON 19:00 archivelogs disappeared.

                                You've never actually shown you backup scripts, so as Dude! said, we have no way knowing what you are actually doing.  All we have is your assertion of your interpretation of what you think you are doing.

                                • 13. Re: Missing Archivelogs
                                  serandrzej

                                  archivelog backup script: starts daily at 19:00

                                   

                                  rman nocatalog <<EOF> ${LOGFILE}

                                  connect target /

                                  set echo on;

                                  show all;

                                  report schema;

                                  configure default device type to disk;

                                  configure device type disk parallelism 1;

                                  configure channel device type disk format '/backup/%d/ch1_%U.bk';

                                  CROSSCHECK ARCHIVELOG ALL;

                                  backup archivelog all not backed up 1 times delete input;

                                  EOF

                                   

                                  full backup script: starts SAM at 20:00, finishes SUN 12:00

                                   

                                  rman nocatalog <<EOF> ${LOGFILE}

                                  connect target /

                                  set echo on;

                                  show all;

                                  report schema;

                                  crosscheck backup;

                                  crosscheck copy;

                                  configure default device type to disk;

                                  configure controlfile autobackup on;

                                  configure controlfile autobackup format for device type disk to '/backup/%d/ctl_%F.bk';

                                  configure retention policy to redundancy 1;

                                  configure device type disk parallelism 1;

                                  configure channel device type disk format '/backup/%d/ch1_%U.bk';

                                  backup as compressed backupset incremental level=0 database;

                                  delete noprompt obsolete;

                                  EOF

                                  • 14. Re: Missing Archivelogs
                                    Dude!

                                    Telling that archivelogs on the second computer are missing is not helpful to troubleshooting the issue. We already know that from the RMAN error. Sorry, but unless you show evidence instead of repeating your own conclusion as to what happened, no one can help you to troubleshoot the issue and this will continue to go in circles.

                                     

                                    What have you copied between the 2 computers and is the second computer using the very same directory structure?

                                    Did you catalog the copied RMAN backups or archivelogs successfully, i.e. using the correct syntax?

                                    Are you using FRA? A lot can go wrong here, for example, insufficient privileges or incorrect syntax to catalog the information.

                                    How did you restore the database? Did you restore the controlfile?

                                    Do the missing archivelogs exist in the RMAN repository on the original server?

                                    Did you run RMAN crosscheck on the other machine and deleted expired RMAN records that do not exist anymore or are invalid?

                                    1 2 Previous Next