How to recover database with the loss of redo logs and without backup.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to recover database with the loss of redo logs and without backup.

  1. #1
    Join Date
    Feb 2012
    Posts
    3

    How to recover database with the loss of redo logs and without backup.

    Hi All,

    Could you please suggest me "How to recover database with the loss of redo logs and without backup". My oracle database is running in no archive log mode, and I have no previous backup. Please suggest the possiblity of recovering the database.

    I am getting below error when trying to recover.

    $ sqlplus / as sysdba

    SQL*Plus: Release 10.2.0.1.0 - Production on Thu Feb 16 22:21:25 2012

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 5133828096 bytes
    Fixed Size 2000800 bytes
    Variable Size 1157630048 bytes
    Database Buffers 3959422976 bytes
    Redo Buffers 14774272 bytes
    Database mounted.
    SQL> recover database using backup controlfile;
    ORA-00279: change 3977276 generated at 12/25/2011 03:46:59 needed for thread 1
    ORA-00289: suggestion :
    /oravl01/oracle/flash_recovery_area/LUXGSM/archivelog/2012_02_16/o1_mf_1_115_%u_
    .arc
    ORA-00280: change 3977276 for thread 1 is in sequence #115


    Specify log: {=suggested | filename | AUTO | CANCEL}
    CANCEL
    Media recovery cancelled.
    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: '/oravl01/oracle/oradata/LUXGSM/system01.dbf'


    SQL>

  2. #2
    Join Date
    Oct 2008
    Posts
    69
    Hello,
    Your only option is to invent a time machine, go back in time and set up a backup policy.
    Sorry
    Phil

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Okay - not going to lecture about the need of having a tested backup/recovery strategy in place, you have already figured it out.

    Does file /oravl01/oracle/flash_recovery_area/LUXGSM/archivelog/2012_02_16/o1_mf_1_115_* exists at all?

    Why are you cancelling the recovery?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Feb 2012
    Posts
    3
    Hi Paul,

    Thanks for the quick response.

    I am newbie to Oracle administarion. I manage the test databases in which it is not manadatory to maintain the cold backup. I am currently giving OCA and have more interest in Oracle db administartion which leads me to post a blog in this site. As of now, I am not very clear about the recovery concepts.

    The file /oravl01/oracle/flash_recovery_area/LUXGSM/archivelog/2012_02_16/o1_mf_1_115_* is not really exist. Also my db is in no-archive log mode.

    Why are you cancelling the recovery?
    >> In one of the blogs, I found that suggestions provided by chao_ping worked for me in some other database with out the parameter _allow_resetlogs_corruption. I was able to recover my consistent database with the loss of online redo files of some other db. The online redo logs got created when I tried the above recovery command which got mentioed in blog by chao_ping.

    http://www.dbasupport.com/forums/arc...p/t-33564.html

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    What happens if you just do...

    sqlplus / as sysdba
    startup force

    Please post session log.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    If you have your online redo, but not any archive logs, you could try recover database and point the database to your online redo logs. You need to try it with each online redo log until you have gotten one to work.
    this space intentionally left blank

  7. #7
    Join Date
    Feb 2012
    Posts
    3
    Hi,

    Please find the o/p of the below commands and the alert log file:
    sqlplus / as sysdba
    startup force


    SQL> shut abort
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.

    Total System Global Area 5133828096 bytes
    Fixed Size 2000800 bytes
    Variable Size 1157630048 bytes
    Database Buffers 3959422976 bytes
    Redo Buffers 14774272 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-01194: file 1 needs more recovery to be consistent
    ORA-01110: data file 1: '/oravl01/oracle/oradata/LUXGSM/system01.dbf'


    SQL>





    Alert LOg file:
    ====================
    Mon Feb 20 15:17:59 2012
    Starting ORACLE instance (normal)
    LICENSE_MAX_SESSION = 0
    LICENSE_SESSIONS_WARNING = 0
    Picked latch-free SCN scheme 3
    Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
    Autotune of undo retention is turned on.
    IMODE=BR
    ILAT =18
    LICENSE_MAX_USERS = 0
    SYS auditing is disabled
    ksdpec: called for event 13740 prior to event group initialization
    Starting up ORACLE RDBMS Version: 10.2.0.1.0.
    System parameters with non-default values:
    processes = 150
    __shared_pool_size = 1124073472
    __large_pool_size = 16777216
    __java_pool_size = 16777216
    __streams_pool_size = 0
    sga_target = 5133828096
    control_files = /oravl01/oracle/oradata/LUXGSM/control01.ctl, /oravl01/oracle/oradata/LUXGSM/control02.ctl, /oravl01/oracle/oradata/LUXGSM/control03.ctl
    db_block_size = 8192
    __db_cache_size = 3959422976
    compatible = 10.2.0.1.0
    db_file_multiblock_read_count= 16
    db_recovery_file_dest = /oravl01/oracle/flash_recovery_area
    db_recovery_file_dest_size= 2147483648
    undo_management = AUTO
    undo_tablespace = UNDOTBS1
    remote_login_passwordfile= EXCLUSIVE
    db_domain =
    dispatchers = (PROTOCOL=TCP) (SERVICE=LUXGSMXDB)
    job_queue_processes = 10
    background_dump_dest = /oravl01/oracle/admin/LUXGSM/bdump
    user_dump_dest = /oravl01/oracle/admin/LUXGSM/udump
    core_dump_dest = /oravl01/oracle/admin/LUXGSM/cdump
    audit_file_dest = /oravl01/oracle/admin/LUXGSM/adump
    db_name = LUXGSM
    open_cursors = 300
    pga_aggregate_target = 1706033152
    PMON started with pid=2, OS id=1451
    PSP0 started with pid=3, OS id=1455
    MMAN started with pid=4, OS id=1457
    DBW0 started with pid=5, OS id=1461
    LGWR started with pid=6, OS id=1465
    CKPT started with pid=7, OS id=1468
    SMON started with pid=8, OS id=1472
    RECO started with pid=9, OS id=1476
    CJQ0 started with pid=10, OS id=1478
    MMON started with pid=11, OS id=1481
    Mon Feb 20 15:18:19 2012
    starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
    MMNL started with pid=12, OS id=1489
    Mon Feb 20 15:18:20 2012
    starting up 1 shared server(s) ...
    Mon Feb 20 15:18:23 2012
    ALTER DATABASE MOUNT
    Mon Feb 20 15:18:27 2012
    Setting recovery target incarnation to 2
    Mon Feb 20 15:18:27 2012
    Successful mount of redo thread 1, with mount id 3427235967
    Mon Feb 20 15:18:27 2012
    Database mounted in Exclusive Mode
    Completed: ALTER DATABASE MOUNT
    Mon Feb 20 15:18:28 2012
    ALTER DATABASE OPEN
    ORA-1589 signalled during: ALTER DATABASE OPEN...
    Mon Feb 20 15:18:55 2012
    alter database open resetlogs
    Mon Feb 20 15:18:55 2012
    ORA-1194 signalled during: alter database open resetlogs...
    $

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Please follow Gandolf's suggestion - don't forget to crosse you fingers when you hit enter :-)
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    The missing question or piece of information is: when were they lost?

    http://asktom.oracle.com/pls/apex/f?...39200346240329

    Recover until cancel is the step needed, followed by resetlogs - if your database was in a recoverable state to begin with.

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Quote Originally Posted by stecal View Post
    The missing question or piece of information is: when were they lost?

    http://asktom.oracle.com/pls/apex/f?...39200346240329

    Recover until cancel is the step needed, followed by resetlogs - if your database was in a recoverable state to begin with.
    Agreed, if they could just apply one redo log/archive log, they could do an open database resetlogs; Otherwise, they should take a good look at their backup and recovery procedures...
    this space intentionally left blank

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