Home » RDBMS Server » Backup & Recovery » How to recover redologfile, which is deleted
icon5.gif  How to recover redologfile, which is deleted [message #257597] Wed, 08 August 2007 13:25 Go to next message
JcReddy123
Messages: 1
Registered: October 2006
Location: Hyderabad
Junior Member

hi im beginner in Database backup & recovery.I am using Oracle 9i.

My question is

In my Demo database there are 2 groups of redo log files.Each group has 2 members. I deleted one group of them wantedly, which is already archived. At that point, database gone to instable state.

I have read so many documents about recovery but i couldn't get back my files.

Can any one say how to make my DB to run again?


Thanx in Advance

Jay Razz
Re: How to recover redologfile, which is deleted [message #257627 is a reply to message #257597] Wed, 08 August 2007 16:24 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Check here for all the details (Recovering After the Loss of Online Redo Log Files: Scenarios).
Re: How to recover redologfile, which is deleted [message #257882 is a reply to message #257597] Thu, 09 August 2007 07:21 Go to previous messageGo to next message
JayC
Messages: 2
Registered: August 2007
Location: Hyderabad
Junior Member

thanx ebrain!

but, i have that document n i tried also, still prblm.
i will give full details later

bye

Jay
Re: How to recover redologfile, which is deleted [message #258000 is a reply to message #257882] Thu, 09 August 2007 15:12 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
All members of a log group lost.

In this case an incomplete recovery is the best we can do. We will lose all transactions from the missing log and all subsequent logs. We illustrate using the same example as above. The error message indicates that members of log group 3 are missing. We don't have a copy of this file, so we know that an incomplete recovery is required. The first step is to determine how much can be recovered. In order to do this, we query the V$LOG view (when in the mount state) to find the system change number (SCN) that we can recover to (Reminder: the SCN is a monotonically increasing number that is incremented whenever a commit is issued)

--The database should be in the mount state for v$log access

SQL> select first_change# from v$log whnhi….ere group#=3 ;

FIRST_CHANGE#
-------------
370255

SQL>

The FIRST_CHANGE# is the first SCN stamped in the missing log. This implies that the last SCN stamped in the previous log is 370254 (FIRST_CHANGE#-1). This is the highest SCN that we can recover to. In order to do the recovery we must first restore ALL datafiles to this SCN, followed by recovery (also up to this SCN). This is an incomplete recovery, so we must open the database resetlogs after we're done. Here's a transcript of the recovery session (typed commands in bold, comments in italics, all other lines are RMAN feedback):

C:\>rman target /

Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--Restore ENTIRE database to determined SCN

RMAN> restore database until scn 370254;

Starting restore at 26/JAN/05
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\SYSTEM01.DBF
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS01.DBF
channel ORA_DISK_2: restored backup piece 1
piece handle=E:\BACKUP\13GB14IB_1_1.BAK tag=TAG20050124T171139 params=NUL
channel ORA_DISK_2: restore complete
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\14GB14IB_1_1.BAK tag=TAG20050124T171139 params=NUL
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05

--Recover database

RMAN> recover database until scn 370254;

Starting recover at 26/JAN/05
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

archive log thread 1 sequence 9 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_9.ARC
archive log thread 1 sequence 10 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_10.ARC
archive log thread 1 sequence 11 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_11.ARC
archive log thread 1 sequence 12 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_12.ARC
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_9.ARC thread=1 sequence=9
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_10.ARC thread=1 sequence=10
media recovery complete
Finished recover at 26/JAN/05

--open database with RESETLOGS (see comments below)

RMAN> alter database open resetlogs;

database opened
RMAN>

The following points should be noted:

1. The entire database must be restored to the SCN that has been determined by querying v$log.

2. All changes beyond that SCN are lost. This method of recovery should be used only if you are sure that you cannot do better. Be sure to multiplex your redo logs, and (space permitting) your archived logs!

3. The database must be opened with RESETLOGS, as a required log has not been applied. This resets the log sequence to zero, thereby rendering all prior backups worthless. Therefore, the first step after opening a database RESETLOGS is to take a fresh backup. Note that the RESETLOGS option must be used for any incomplete recovery.
Re: How to recover redologfile, which is deleted [message #258012 is a reply to message #258000] Thu, 09 August 2007 17:06 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Gotta give it to ya Dreamz, you are the absolute master in finding docs on the internet !!

Strategies for Oracle Database Backup and Recovery: Case Studies
Re: How to recover redologfile, which is deleted [message #258014 is a reply to message #258012] Thu, 09 August 2007 17:12 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
http://www.scribd.com/doc/238297/Oracle-rman-documents
Re: How to recover redologfile, which is deleted [message #258015 is a reply to message #258014] Thu, 09 August 2007 17:16 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Thanks for sharing.
Re: How to recover redologfile, which is deleted [message #258256 is a reply to message #257597] Fri, 10 August 2007 13:00 Go to previous message
JayC
Messages: 2
Registered: August 2007
Location: Hyderabad
Junior Member

hi dremz, ebrain

thanx for ur involvement.

regards
Jay

Previous Topic: backup strategy
Next Topic: RMAN Clone Operation Fails with RMAN-05001
Goto Forum:
  


Current Time: Sat Dec 03 08:17:57 CST 2016

Total time taken to generate the page: 0.13527 seconds