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>

Hello,
Your only option is to invent a time machine, go back in time and set up a backup policy.
Sorry
Phil

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?

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

What happens if you just do...

sqlplus / as sysdba
startup force

Please post session log.

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.

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...
$

Please follow Gandolf's suggestion - don't forget to crosse you fingers when you hit enter :-)

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.

Quote:

---

Originally Posted by stecal

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... :rolleyes:

similar kind of scenario is here..
http://shonythomas.blogspot.in/2012/...corrupted.html