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

534533

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

595286

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 !

595286

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

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

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

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

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

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

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

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

Yap..thts possible...u will have to bear the data loss.


Anand

591309

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

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

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

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

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

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

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

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

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]

26741

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

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

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

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

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

First, explain what has happened to your db
second, run the query on v$log to have group# and sequence#....

26741

The alert.log messages before this startup please .. Those would tell us your log sequence numbers