Problem of archive log
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Problem of archive log

  1. #1
    Join Date
    Dec 2001
    Posts
    8

    Angry

    Hi,

    I have lost my control file,so I need recover system.

    After I input command archive log list,system gave me the following message:

    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination /app/oracle/admin/oracle8/arch
    Oldest online log sequence 67
    Next log sequence to archive 69
    Current log sequence 69

    when I recover database,system can't find file /app/oracle/admin/oracle8/arch/arch_1_69.arc
    and give me the following error messsage,I can only find the last file is arch_1_68.arc.

    ORA-00279: change 765190 generated at 07/04/2002 10:25:53 needed for thread 1
    ORA-00289: suggestion : /app/oracle/admin/oracle8/arch/arch_1_69.arc
    ORA-00280: change 765190 for thread 1 is in sequence #69
    ORA-00278: log file '/app/oracle/admin/oracle8/arch/arch_1_68.arc' no longer
    needed for this recovery


    Specify log: {=suggested | filename | AUTO | CANCEL}

    ORA-00308: cannot open archived log
    '/app/oracle/admin/oracle8/arch/arch_1_69.arc'
    ORA-27037: unable to obtain file status
    Linux Error: 2: No such file or directory
    Additional information: 3

    How can I recover my database,please help me .

    Thanks,
    Michael Ni

  2. #2
    Join Date
    Apr 2002
    Posts
    291
    See,
    Its asking for archive log with sequence number 69, and in the out put of archivelog list observe that , current log sequence is also 69. So just cancel the recovery and open your database with resetlogs option. Becuase you lost your control file, and has to recover using backup control file.

    thanks
    PNRDBA

  3. #3
    Join Date
    Dec 2001
    Posts
    8
    Hi Pnrdba,

    I have try before you teach me, could you tell me where I make a mistake.

    SQL> startup
    ORACLE instance started.

    Total System Global Area 56012784 bytes
    Fixed Size 69616 bytes
    Variable Size 38993920 bytes
    Database Buffers 16777216 bytes
    Redo Buffers 172032 bytes
    ORA-00205: error in identifying controlfile, check alert log for more info

    copy backup control file

    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 56012784 bytes
    Fixed Size 69616 bytes
    Variable Size 38993920 bytes
    Database Buffers 16777216 bytes
    Redo Buffers 172032 bytes
    Database mounted.

    SQL> recover database using backup controlfile;
    ORA-00279: change 704948 generated at 07/03/2002 12:40:11 needed for thread 1
    ORA-00289: suggestion : /app/oracle/admin/oracle8/arch/arch_1_62.arc
    ORA-00280: change 704948 for thread 1 is in sequence #62


    Specify log: {=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 725005 generated at 07/04/2002 13:04:06 needed for thread 1
    ORA-00289: suggestion : /app/oracle/admin/oracle8/arch/arch_1_63.arc
    ORA-00280: change 725005 for thread 1 is in sequence #63
    ORA-00278: log file '/app/oracle/admin/oracle8/arch/arch_1_62.arc' no longer
    needed for this recovery
    .
    .
    .
    .
    .
    ORA-00279: change 725067 generated at 07/04/2002 13:04:47 needed for thread 1
    ORA-00289: suggestion : /app/oracle/admin/oracle8/arch/arch_1_67.arc
    ORA-00280: change 725067 for thread 1 is in sequence #67
    ORA-00278: log file '/app/oracle/admin/oracle8/arch/arch_1_66.arc' no longer
    needed for this recovery


    Specify log: {=suggested | filename | AUTO | CANCEL}

    ORA-00279: change 725068 generated at 07/04/2002 13:04:47 needed for thread 1
    ORA-00289: suggestion : /app/oracle/admin/oracle8/arch/arch_1_68.arc
    ORA-00280: change 725068 for thread 1 is in sequence #68
    ORA-00278: log file '/app/oracle/admin/oracle8/arch/arch_1_67.arc' no longer
    needed for this recovery


    Specify log: {=suggested | filename | AUTO | CANCEL}

    ORA-00308: cannot open archived log
    '/app/oracle/admin/oracle8/arch/arch_1_68.arc'
    ORA-27037: unable to obtain file status
    Linux Error: 2: No such file or directory
    Additional information: 3


    SQL> alter database open noresetlogs;
    alter database open noresetlogs
    *
    ERROR at line 1:
    ORA-01588: must use RESETLOGS option for database open


    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: '/data/oradata/oracle8/system01.dbf'


    SQL> archive log list;
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination /app/oracle/admin/oracle8/arch
    Oldest online log sequence 60
    Next log sequence to archive 60
    Current log sequence 62

    [oracle@oradb arch]$ ls
    arch_1_60.arc arch_1_62.arc arch_1_64.arc arch_1_66.arc
    arch_1_61.arc arch_1_63.arc arch_1_65.arc arch_1_67.arc

    I don't know how to do next, I will grateful you if you can help me.

    Regards,
    Michael Ni

  4. #4
    Join Date
    Mar 2002
    Posts
    301
    Hi,

    The main problem in recovering with a backup controlfile is that you have to open your database using RESETLOGS.

    Instead, write a script for re-creating the controlfile and then do a recovery.

    HTH.
    Vijay.
    Say No To Plastics

  5. #5
    Join Date
    Dec 2001
    Posts
    8
    Why I can't find arch_1_69.arc ?
    I have use command archive log all,but it still can't generate file arch_1_67.arc

    Regards,
    Michael Ni

  6. #6
    Join Date
    Jul 2002
    Location
    California
    Posts
    128
    Since you are missing log 69 you can recover up to and including log 68


    Your statement below should be changed:

    OLD:
    SQL> recover database using backup controlfile;

    NEW:
    SQL> recover database using backup controlfile UNTIL CANCEL;

    when it asks for log 69... type in CANCEL

    Open the database with the resetlogs option.

    This will perform an incomplete recovery through log 68 (all that is available)..

    Hope this helps...

  7. #7
    Join Date
    Dec 2001
    Posts
    8
    Hi Alapps,

    I have done as you teach me,but still can't recover database,can you tell me other ways?

    SQL> recover database using backup controlfile until cancel
    ORA-00279: change 725062 generated at 07/04/2002 15:30:19 needed for thread 1
    ORA-00289: suggestion : /app/oracle/admin/oracle8/arch/arch_1_68.arc
    ORA-00280: change 725062 for thread 1 is in sequence #68


    Specify log: {=suggested | filename | AUTO | CANCEL}
    cancel
    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: '/data/oradata/oracle8/system01.dbf'


    ORA-01112: media recovery not started


    SQL> alter database open resetlogs;
    alter database open resetlogs
    *
    ERROR at line 1:
    ORA-01194: file 1 needs more recovery to be consistent
    ORA-01110: data file 1: '/data/oradata/oracle8/system01.dbf'

    Best regards,
    Michael Ni

  8. #8
    Join Date
    Apr 2002
    Posts
    291
    After u said "alter database recover automatic using backup controlfile until cancel" Then it asks for
    Specify log: {=suggested | filename | AUTO | CANCEL}

    Here, TYPE CANCEL, and then
    Open database with resetlogs Option.

    This will definitely work.

    Thanks
    PNRDBA

  9. #9
    Join Date
    Dec 2001
    Posts
    8
    Hi Pnrdba,

    Alapps has told me which you just told me, I have tried but failed, you can see the forward reply which has result of performance.

    Regards,
    Michael Ni

  10. #10
    Join Date
    Jul 2002
    Location
    California
    Posts
    128

    Post

    michael_nh,

    Since you have attempted a full recovery (UNTIL CANCEL not specified), you need to restore from the last backup, perform the incomplete recovery (recover automatic using backup controlfile until cancel), open the database with resetlogs and you should be "Back in Business"...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width