Forum Stats

  • 3,734,278 Users
  • 2,246,936 Discussions
  • 7,857,217 Comments

Discussions

How to recover the database when some of the archive log file get deleted.

534533
534533 Member Posts: 95
edited Sep 10, 2008 11:25AM in General Database Discussions
I am facing a problem with Oracle database, which is related to archivelogs.
Our development database is running in archivelog mode, but we don't have backups scheduled and have no recovery catalog.
When the database was in running condition, disk got full, so some archivelogs were deleted manually.
After this they restarted the DB, and now DB is not coming up. Errors are as follows:

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


SQL> startup
ORACLE instance started.

Total System Global Area 1444383504 bytes
Fixed Size 731920 bytes
Variable Size 486539264 bytes
Database Buffers 956301312 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS 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: '/export/home/oracle/dev/ADVFRW/ADVFRW.system'


SQL> recover datafile '/export/home/oracle/dev/ADVFRW/ADVFRW.system'
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile;
ORA-00279: change 215548705 generated at 09/02/2008 17:06:10 needed for thread
1
ORA-00289: suggestion :
/export/home/oracle/dev/ADVFRW/ADVFRW.archivelog1/LOG_ADVFRW_1107_1.ARC
ORA-00280: change 215548705 for thread 1 is in sequence #1107


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/export/home/oracle/dev/ADVFRW/ADVFRW.archivelog1/LOG_ADVFRW_1107_1.ARC
ORA-00308: cannot open archived log
'/export/home/oracle/dev/ADVFRW/ADVFRW.archivelog1/LOG_ADVFRW_1107_1.ARC'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL>

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


1. How to recover the database and bring it online

Any help will be highly appreciated.

With Regards
Hemant Joshi

Edited by: hem_Kec on Sep 7, 2008 9:07 AM
Tagged:

Best Answer

  • 595286
    595286 Member Posts: 243
    Accepted Answer
    Hi,

    It is in fact quite easy.
    We knew Oracle was looking for logsequence 1107. The archived log does not exist so either it was deleted, or it was never generated. As you delete some files, the 1st solution could be possible BUT as there was only an instance crash so older archived logs are not required for crash recovery. There are need when restoring files from old backup, which is not our case.

    So, the missing log sequence is not archived =&gt; this is a redo log.
    To identify the required online redo log, we just have to query v$log as I suggest you.

    Once the logfile identified, we can play the recover specifying the redo log when asking for logsequence 1107.

    And that's over. We win ! :)
«1

Answers

  • 595286
    595286 Member Posts: 243
    why are you using the "using backup controlfile" clause ?

    do you have the logsequence 1107 ? if no, I'm afraid you won't be able to perform a full recovery.

    what was the incident ? what is your backup strategy ? your tool ? the retention ?
  • Anand...
    Anand... Member Posts: 3,590
    U won't be able to do full database recovery, as ur archive log is missing.

    use cancel when prompted in recover database command, and then try to alter database open resetlogs;



    Anand
  • Aravind N
    Aravind N Member Posts: 73
    Did your Database crash after the archivelog destination was full? What was the incident?

    If Anands suggestion is not working, you can try applying the Redolog files instead of archivelog files.

    Aravind
  • 534533
    534533 Member Posts: 95
    Hello Leo Anderson,
    First of all thanks for the your quick response.

    The 'LOG_ADVFRW_1107_1.ARC' archive log get deleted manually because of our disk was full. Since it is a development database so we have not any backup strategy. Even we are not taking any backups scheduled and have no recovery catalog.

    Now we want to recover the database (even with cost of some data lose). What is the best way to recover from this situation.



    Thanks
    With Regards
    Hemant Joshi
  • Anand...
    Anand... Member Posts: 3,590
    There is no way u can recover ur data.Data will be lost from where the archives are absent. The only way is to cancel and aletr database open resetlogs;


    Regards,
    Anand
  • 534533
    534533 Member Posts: 95
    Hello Anand,
    Thanks for your quick response.
    i will try the following command in our database & let you know.
    1) recover database until cancel
    2)alter database open resetlogs


    Thanks a lot.

    With Regards
    Hemant Joshi
  • 534533
    534533 Member Posts: 95
    Hello Aravind,
    Thanks for the quick response.

    The archivelog destination was full so some archivelogs were deleted manually. After this we restarted the DB, and now DB is not coming up.

    what is difference applying the Redolog files instead of archivelog files. Can you please give me some pointer.


    Thanks
    With Regards
    Hemant Joshi
  • 534533
    534533 Member Posts: 95
    Hello Anand,
    We can bear some data lose but we want database should be in working condition. Is it possible.

    Thanks
    With Regards
    Hemant Joshi
  • Anand...
    Anand... Member Posts: 3,590
    edited Sep 7, 2008 12:39PM
    Yap..thts possible...u will have to bear the data loss.


    Anand
  • 591309
    591309 Member Posts: 753
    Hi,
    Archive log files are the copies of redolog files.As redo log files are circularly overwritten,oracle generates archive log file of the corresponding redo logfiles being overwritten.So if you have a backup that dates back to 10 am in the morning and if your database creashed at 3 pm,you cannot use the redo log files alone as they have incomplete information.To completely recover the database upto 3 pm,you need archive log files generated between 10 am to 3 pm. In your case since you are missing one archive log file,you cannot perform complete recovery and hence would suffer data loss.
  • 595286
    595286 Member Posts: 243
    You have to restore ALL datafiles and ALL controlfiles from a backup taken before logsequence 1107
    and the perform an incomplete recovery : recover database until ...

    then, you'll have to perform an open resetlogs

    If you are using rman : http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/flashptr006.htm#sthref669

    else http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/flashptr001.htm#sthref596
  • 534533
    534533 Member Posts: 95
    Hello Leo Andreson,
    Thanks for your response and giving me your valuable time.

    We have not any backup (neither hot nor cold).

    is it oky if i try following command in our database (with exiting files).

    1) recover database until cancel;
    2)alter database open resetlogs;

    or i should use allowresetlogs_corruption=TRUE Ref: http://www.dbspecialists.com/files/presentations/missing_logs.html

    With Regards
    Hemant Joshi.
  • Anand...
    Anand... Member Posts: 3,590
    As u have no backups as mentioned earlier by u...u have no options rather than going for

    recover database until cancel and then open database resetlogs;

    DON"T GO WITH THIS PARAMETER until ur not able to open the database using resetlogs.Atleast once give a try to alter database open resetlogs;.


    _allow_resetlogs_corruption=TRUE

    This parameter is a “hidden” or undocumented parameter—one of those which you’re never supposed to use unless told to do so by Oracle Support.


    Reagards,
    Anand
  • 595286
    595286 Member Posts: 243
    You do no have any backup ? hum... :S
    What has happen to your DB ?

    could you please upload the following results :
    select group#, sequence# from v$log:
    but the recover + open must be enough (you do not need archived log for an instance crash recovery).


    The link need backup (point 2 At this point you should restore the lost datafile from a backup)



  • 465343
    465343 Member Posts: 87
    Anand... wrote:
    This parameter is a “hidden” or undocumented parameter—one of those which you’re never supposed to use unless told to do so by Oracle Support.
    you are contradicting yourself.
    If you are not "supposed to use that parameter unless told to do so by Oracle Support", then why are you mentioning it?
    would it better to recommend the OP to ask Oracle Support for it?
    Just curious
  • Aravind N
    Aravind N Member Posts: 73
    Hi Hemant,

    Did you try this?

    1) recover database until cancel;
    2)alter database open resetlogs

    If not, can you do the same and post results?

    Aravind
  • 534533
    534533 Member Posts: 95
    Hello Aravind,
    Thanks a lot.
    Since it's already 11:45 PM in India. I will try the following command tomorrow n will update you as soon as possible.


    Thanks
    With Regards
    Hemant Joshi.
  • oradebug
    oradebug Member Posts: 88
    Archivelogs are only necessary when recovering from a backup. I am disappointed by some other contributors to this thread stating categorically that you cannot recover or must suffer data loss, or worse should use something drastic like allowresetlogs_corruption. The basic fact is that any change needed for crash recvoery must be in an onlne redolog. Oracle will not reuse online logs until all R/W datafiles have been checkpointed and their checkpoint SCN advanced.

    On restart after the database crashed, it should not have needed you to do anything. It should have applied crash recovery automatically and opened. I'm not sure how you got it asking for archivelogs, but this should not be a big problem.

    As others have implied, sequence 1107 is almost certainly an online log. Simply obtain the sequence numbers of the current logs:

    SQL> column member format a65
    SQL> select sequence#, member from v$log a, v$logfile b where a.group#=b.group# order by sequence#;

    SEQUENCE# MEMBER
    1105 /u01/oradata/redo04.log
    1106 /u01/oradata/redo01.log
    1107 /u01/oradata/redo02.log
    1108 /u01/oradata/redo03.log

    Then when you do recover database, you can specify the path and filename of an online log with sequence# 1107 (and subsequently any others it asks for).

    Regards,

    Jeremiah Wilton
    ORA-600 Consulting
    [http://www.ora-600.net]
    oradebug
  • 26741
    26741 Member Posts: 4,923 Gold Trophy
    If you haven't restored database files from a backup, you should not need to apply archivelogs.
    Check the alert.log to see what was the last log sequence archived out and which was the sequence number for the active online redo log.

    Also, as you can STARTUP MOUNT, you can query V$LOG for the Sequence Numbers of the online redo logs.


    However, the messages indicate that Oracle is trying to rollforward through transactions of 02-Sep. When was the database last shutdown ?
  • 626479
    626479 Member Posts: 103
    i'm not a expert in this

    but just curious ,if he don't even have a backup to restore , how can you guys suggest he do cancel based recover ,

    as Burleson insturected here , you have to restore the file first , and apply archive log in order , without backup , is it possible ?

    or am i missing something ?

    thanks

    br/ricky

    You should perform cancel-based media recovery in these stages:

    Prepare for recovery by backing up the database and correct any media failures as described in "Preparing for Incomplete Recovery".
    Restore backup datafiles as described in "Restoring Datafiles Before Performing Incomplete Recovery". If you have a current control file, then do not restore a backup control file.
    Perform media recovery on the restored database backup as described in the following procedure.
    To perform cancel-based recovery:

    Start SQL*Plus and connect to Oracle with administrator privileges. For example, enter:
    % sqlplus '/ AS SYSDBA'


    Start a new instance and mount the database:
    STARTUP MOUNT


    Begin cancel-based recovery by issuing the following command:
    RECOVER DATABASE UNTIL CANCEL


    If you are using a backup control file with this incomplete recovery, then specify the USING BACKUP CONTROLFILE option in the RECOVER command.

    RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
  • 26741
    26741 Member Posts: 4,923 Gold Trophy
    If he doesn't have a backup to restore, why are you suggesting steps that start with a restoration ? There's nothing to restore.

    What Jeremiah and I are pointing out is that this is not a backup-restore issue but that since there has been on restoration, the recover commands themselves are in the wrong direction. A Recover is required after a Restore. If no Restore has been done, the "recovery" is actually an Instance Recovery from the online redo logs. Therefore, the OP is to check the sequence numbers of his online redo logs.
  • 26741
    26741 Member Posts: 4,923 Gold Trophy
    Another possibility is that your database was in BEGIN BACKUP mode at the time it was shutdown.

    That would also cause Oracle to prompt for recovery (seemingly using archivelogs) at startup. If the database was in BEGIN BACKUP mode (and you'd know that or check your alert.log for messages), then you should be issuing an ALTER DATABASE END BACKUP ; at STARTUP MOUNT; before you attempt an ALTER DATABASE OPEN.


    Hemant K Chitale
    http://hemantoracledba.blogspot.com
  • 534533
    534533 Member Posts: 95
    Hello All,
    Please find attached brief from alert log.

    -----------------------------------------------------------------------------------
    LICENSE_MAX_SESSION = 0
    LICENSE_SESSIONS_WARNING = 0
    SCN scheme 3
    Using log_archive_dest parameter default value
    LICENSE_MAX_USERS = 0
    SYS auditing is disabled
    Starting up ORACLE RDBMS Version: 9.2.0.2.0.
    System parameters with non-default values:
    processes = 3000
    timed_statistics = FALSE
    shared_pool_size = 318767104
    large_pool_size = 33554432
    spfile = /export/home/oracle/dev/ADVFRW/ADVFRW.spfile
    nls_length_semantics = CHAR
    control_files = /export/home/oracle/dev/ADVFRW/ADVFRW.ctrl1, /export/home/oracle/dev/ADVFRW/ADVFRW.ctrl2
    db_block_checksum = TRUE
    db_block_size = 4096
    db_cache_size = 956301312
    max_commit_propagation_delay= 0
    compatible = 9.2.0
    log_archive_start = TRUE
    log_archive_dest_1 = LOCATION=/export/home/oracle/dev/ADVFRW/ADVFRW.archivelog1 REOPEN=10
    log_archive_format = LOG_ADVFRW_%s_%t.ARC
    db_files = 32
    db_file_multiblock_read_count= 64
    cluster_database_instances= 1
    standby_file_management = auto
    thread = 1
    fast_start_mttr_target = 120
    dml_locks = 6000
    instance_number = 1
    transaction_auditing = FALSE
    undo_management = AUTO
    undo_tablespace = tbs_rollback1
    db_block_checking = TRUE
    remote_login_passwordfile= NONE
    service_names = ADVFRW
    local_listener = (address=(protocol=ipc)(key=ADVFRW))
    cursor_space_for_time = TRUE
    session_cached_cursors = 100
    background_dump_dest = /export/home/oracle/admin/ADVFRW/bdump
    user_dump_dest = /export/home/oracle/admin/ADVFRW/udump
    core_dump_dest = /export/home/oracle/admin/ADVFRW/cdump
    audit_trail = FALSE
    sort_area_size = 1572864
    sort_area_retained_size = 1572864
    db_name = ADVFRW
    open_cursors = 9000
    parallel_automatic_tuning= TRUE
    PMON started with pid=2
    DBW0 started with pid=3
    LGWR started with pid=4
    CKPT started with pid=5
    SMON started with pid=6
    RECO started with pid=7
    Fri Sep 5 06:09:58 2008
    ARCH: STARTING ARCH PROCESSES
    ARC0 started with pid=8
    ARC0: Archival started
    ARC1 started with pid=9
    Fri Sep 5 06:09:58 2008
    ARCH: STARTING ARCH PROCESSES COMPLETE
    Fri Sep 5 06:09:58 2008
    ARC0: Thread not mounted
    Fri Sep 5 06:09:58 2008
    ARC1: Archival started
    ARC1: Thread not mounted
    Fri Sep 5 06:09:58 2008
    ALTER DATABASE MOUNT
    Fri Sep 5 06:10:02 2008
    Successful mount of redo thread 1, with mount id 3547008246.
    Fri Sep 5 06:10:02 2008
    Database mounted in Exclusive Mode.
    Completed: ALTER DATABASE MOUNT
    Fri Sep 5 06:10:02 2008
    ALTER DATABASE OPEN
    ORA-1589 signalled during: ALTER DATABASE OPEN...
    Fri Sep 5 06:10:19 2008
    alter database open resetlogs
    Fri Sep 5 06:10:19 2008
    ORA-1113 signalled during: alter database open resetlogs...
    Fri Sep 5 06:11:49 2008
    ALTER DATABASE RECOVER datafile '/export/home/oracle/dev/ADVFRW/ADVFRW.system'
    Fri Sep 5 06:11:49 2008
    Media Recovery Datafile: '/export/home/oracle/dev/ADVFRW/ADVFRW.system'
    Media Recovery Start
    Media Recovery failed with error 1610
    ORA-283 signalled during: ALTER DATABASE RECOVER datafile '/export/home/ora...
    Fri Sep 5 06:12:28 2008
    ALTER DATABASE RECOVER database using backup controlfile
    Fri Sep 5 06:12:28 2008
    Media Recovery Start
    Starting datafile 1 recovery in thread 1 sequence 1107
    Datafile 1: '/export/home/oracle/dev/ADVFRW/ADVFRW.system'
    Starting datafile 2 recovery in thread 1 sequence 1107
    Datafile 2: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_rollback1'
    Starting datafile 3 recovery in thread 1 sequence 1107
    Datafile 3: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_cframe'
    Starting datafile 4 recovery in thread 1 sequence 1107
    Datafile 4: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_vacdata_tab'
    Starting datafile 5 recovery in thread 1 sequence 1107
    Datafile 5: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_vacdata_idx'
    Starting datafile 6 recovery in thread 1 sequence 1107
    Datafile 6: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_logging_tab'
    Starting datafile 7 recovery in thread 1 sequence 1107
    Datafile 7: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_logging_idx'
    Starting datafile 8 recovery in thread 1 sequence 1107
    Datafile 8: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_dbl_tab'
    Starting datafile 9 recovery in thread 1 sequence 1107
    Datafile 9: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_plfkmg_tab'
    Starting datafile 10 recovery in thread 1 sequence 1107
    Datafile 10: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_inappl_tab'
    Starting datafile 11 recovery in thread 1 sequence 1107
    Datafile 11: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_inappl_idx'
    Starting datafile 12 recovery in thread 1 sequence 1107
    Datafile 12: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_inappl1_idx'
    Starting datafile 13 recovery in thread 1 sequence 1107
    Datafile 13: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_inappl1_tab'
    Starting datafile 14 recovery in thread 1 sequence 1107
    Datafile 14: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_inappl2_tab'
    Starting datafile 15 recovery in thread 1 sequence 1107
    Datafile 15: '/export/home/oracle/dev/ADVFRW/ADVFRW.tbs_inappl2_idx'
    Starting datafile 16 recovery in thread 1 sequence 1107
    Datafile 16: '/export/home/oracle/products/9.2.0/dbs/TEMP_FILE_FOR_INAPPL_TAB.dbf'
    Media Recovery Log
    ORA-279 signalled during: ALTER DATABASE RECOVER database using backup cont...
    Fri Sep 5 06:13:01 2008
    ALTER DATABASE RECOVER LOGFILE '/export/home/oracle/dev/ADVFRW/ADVFRW.archivelog1/LOG_ADVFRW_1107_1.ARC'
    Fri Sep 5 06:13:01 2008
    Media Recovery Log /export/home/oracle/dev/ADVFRW/ADVFRW.archivelog1/LOG_ADVFRW_1107_1.ARC
    Errors with log /export/home/oracle/dev/ADVFRW/ADVFRW.archivelog1/LOG_ADVFRW_1107_1.ARC.
    ORA-308 signalled during: ALTER DATABASE RECOVER LOGFILE '/export/home/or...
    Fri Sep 5 06:13:21 2008
    ALTER DATABASE RECOVER CANCEL
    Fri Sep 5 06:13:21 2008
    Media Recovery Cancelled
    Completed: ALTER DATABASE RECOVER CANCEL
    --------------------------------------------------------------------------------------------

    i am confused now what to do because are lot of pointers. Can anybody let me know what steps should be executed first.



    Thanks
    With Regards
    Hemant Joshi
  • 595286
    595286 Member Posts: 243
    First, explain what has happened to your db
    second, run the query on v$log to have group# and sequence#....
  • 26741
    26741 Member Posts: 4,923 Gold Trophy
    The alert.log messages before this startup please .. Those would tell us your log sequence numbers
This discussion has been closed.