9 Replies Latest reply: Dec 11, 2012 7:26 AM by 665048 RSS

    Recover database but don't have archive log file

    taohiko
      Hi

      I use old backup set in tape and restore all datafile completed but I cannot recover that show below errror in RMAN

      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
      RMAN> recover database;

      Starting recover at 08-SEP-09
      using channel ORA_DISK_1

      starting media recovery

      unable to find archive log
      archive log thread=1 sequence=29166
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-03002: failure of recover command at 09/08/2009 21:49:36
      RMAN-06054: media recovery requesting unknown log: thread 1 seq 29166 lowscn 1648727512

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

      But in backup set and include archive log already that don't have seq 29166 that have last seg is 29165 when I tyr recover in sqlplus that show below error

      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------
      SQL> recover database using backup controlfile;
      ORA-00279: change 1648727512 generated at 09/05/2009 00:02:07 needed for thread
      1
      ORA-00289: suggestion : /oradata/archive/hrprd/1_29166_671345511.arc
      ORA-00280: change 1648727512 for thread 1 is in sequence #29166


      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

      ORA-00308: cannot open archived log
      '/oradata/archive/hrprd/1_29166_671345511.arc'
      ORA-27037: unable to obtain file status
      IBM AIX RISC System/6000 Error: 2: No such file or directory
      Additional information: 3
      ---------------------------------------------------------------------------------------------------------------------------------------------------------------------

      And I tried to open with reset log that show below error

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

      SQL> alter database open resetlogs;
      alter database open resetlogs
      *
      ERROR at line 1:
      ORA-01113: file 1 needs media recovery
      ORA-01110: data file 1: '/oradata/data/hrprd/system01.dbf'

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


      How can I do to open database?


      Taohiko.
        • 1. Re: Recover database but don't have archive log file
          19426
          I hope this old backup was a cold one, try

          RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

          When prompted for the non-existing logfile type 'CANCEL', then open the database with 'resetlogs'.

          If this does not work the datafiles definitely need more archivelog information to become consistent.

          Werner
          • 2. Re: Recover database but don't have archive log file
            taohiko
            Hi Werner

            I tried and show below error

            --------------------------------------------------
            SQL> recover database using backup controlfile until cancel;
            ORA-00279: change 1648727512 generated at 09/05/2009 00:02:07 needed for thread
            1
            ORA-00289: suggestion : /oradata/archive/hrprd/1_29166_671345511.arc
            ORA-00280: change 1648727512 for thread 1 is in sequence #29166


            Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
            CANCEL
            ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
            ORA-01152: file 1 was not restored from a sufficiently old backup
            ORA-01110: data file 1: '/oradata/data/hrprd/system01.dbf'


            ORA-01112: media recovery not started
            --------------------------------------------------

            How to do next step?

            Taohiko
            • 3. Re: Recover database but don't have archive log file
              Kamran Agayev A.
              taohiko wrote:
              Hi Werner

              I tried and show below error

              --------------------------------------------------
              SQL> recover database using backup controlfile until cancel;
              ORA-00279: change 1648727512 generated at 09/05/2009 00:02:07 needed for thread
              1
              ORA-00289: suggestion : /oradata/archive/hrprd/1_29166_671345511.arc
              ORA-00280: change 1648727512 for thread 1 is in sequence #29166


              Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
              CANCEL
              ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
              ORA-01152: file 1 was not restored from a sufficiently old backup
              ORA-01110: data file 1: '/oradata/data/hrprd/system01.dbf'


              ORA-01112: media recovery not started
              --------------------------------------------------

              How to do next step?

              Taohiko
              Hi Taohiko

              Have you made backup of all archived redo log files? If so, why this one was disappeared?
              Have you applied all archived redo log files before that required archived redo log file?
              Please try to recover the database using the following command:
              RMAN> RESTORE DATABASE;
              RMAN> RECOVER DATABASE UNTIL SEQUENCE 29166;
              RMAN>ALTER DATABASE OPEN RESETLOGS;
              • 4. Re: Recover database but don't have archive log file
                orawarebyte
                within yours old backupset , there would be missing redo to be archived and then backed up this archived.Within backupscript you did not make log switch at end or before backup archivlog, this missing redo is within that redo log file which is required now in yours recovery.

                or

                check at RMAN

                RMAN>list archivelog all

                to make sure archivelog exist there 1_29166_671345511.arc.

                Khurram
                • 5. Re: Recover database but don't have archive log file
                  taohiko
                  Hi Kamran

                  I tried and show below error

                  RMAN> recover database until sequence 29166;

                  Starting recover at 09-SEP-09
                  using channel ORA_DISK_1
                  RMAN-00571: ===========================================================
                  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                  RMAN-00571: ===========================================================
                  RMAN-03002: failure of recover command at 09/09/2009 19:39:55
                  RMAN-06556: datafile 1 must be restored from backup older than scn 1648727279


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

                  Taohiko
                  • 6. Re: Recover database but don't have archive log file
                    taohiko
                    Hi Khurram

                    That show below

                    RMAN> list archivelog all;

                    specification does not match any archive log in the recovery catalog
                    ---------------------------------------------------

                    I try get old backup from tape to new server that didn't have database before.

                    How should I do next?

                    Taohiko
                    • 7. Re: Recover database but don't have archive log file
                      Kamran Agayev A.
                      taohiko wrote:
                      Hi Kamran

                      I tried and show below error

                      RMAN> recover database until sequence 29166;

                      Starting recover at 09-SEP-09
                      using channel ORA_DISK_1
                      RMAN-00571: ===========================================================
                      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                      RMAN-00571: ===========================================================
                      RMAN-03002: failure of recover command at 09/09/2009 19:39:55
                      RMAN-06556: datafile 1 must be restored from backup older than scn 1648727279


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

                      Taohiko
                      It means that your restored datafiles are already contains information which you want to apply from archived redo log files. Restore older version of your backup and recover until sequence 29166
                      • 8. Re: Recover database but don't have archive log file
                        19426
                        So your tape backup was a hot backup and archivelogs needed for consistency are missing. You need either - as already said - an older datafile backup or the requested newer logfiles. If you have none of them - bad situation.

                        Werner
                        • 9. Re: Recover database but don't have archive log file
                          665048
                          Hello,

                          Does this mean that lacking this particular archive log will render the database gone?

                          I have all the datafiles but i lost some archivelogs files and some redo log files