This discussion is archived
9 Replies Latest reply: Dec 11, 2012 5:26 AM by 665048 RSS

Recover database but don't have archive log file

taohiko Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Oracle ACE Director
    Currently Being Moderated
    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
    316993 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points