ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 丢失redo log的恢复

丢失redo log的恢复

原创 Linux操作系统 作者:ilsyx 时间:2011-02-10 16:49:01 0 删除 编辑
改动数据库时,忘记修改一个redo group的位置.然后就将目录删掉了.数据库startup时 报redo 丢失.


Loss Of Online Redo Log And ORA-312 And ORA-313 [ID 117481.1]

  Modified 19-OCT-2010     Type BULLETIN     Status PUBLISHED  

"Checked for relevance on 28-Jun-2009"


Scenario
--------

You have a database in archive log mode, shutdown immediate and deleted
one of the online redo logs, in this case there are only 2 groups with
1 log member in each. When you try to open the database you receive
the following errors:

ora-313 open failed for memebers of log group 2 of thread 1.
ora-312 online log 2 thread 1 'filename'

It is not possible to recover the missing log, so the following needs
to be performed -

Mount the database and check v$log to see if the deleted log is current;

-If the log is not current, simply drop the log group (alter database drop
logfile group N).
If there are only 2 log groups then it will be necessary to add another
group before dropping this one.

-If the log is current they should simply perform. fake recovery and then
open resetlogs

connect internal
startup mount
recover database until cancel;
(cancel immediately)
alter database open resetlogs;

The database will open up as required, providing the log file directory is
available. If not available then create it and rerun the resetlogs. This
will give error

ora-344 unable to recreate online log

search words
------------
ORA-00354 ORA-00353
lost online redo
ora-312
ora-313

Recovering After the Loss of Online Redo Log Files: Scenarios
=============================================================

If a media failure has affected the online redo logs of a database, then the
appropriate recovery procedure depends on the following:

The configuration of the online redo log: mirrored or non-mirrored
The type of media failure: temporary or permanent
The types of online redo log files affected by the media failure: current,
active, unarchived, or inactive


1) Recovering After Losing a Member of a Multiplexed Online Redo Log Group
---------------------------------------------------------------------------

If the online redo log of a database is multiplexed, and if at least one
member of each online redo log group is not affected by the media failure,
then the database continues functioning as normal, but error messages are
written to the log writer trace file and the alert_SID.log of the database.


ACTION PLAN
=============
If the hardware problem is temporary, then correct it. The log writer process
accesses the previously unavailable online redo log files as if the problem
never existed.

If the hardware problem is permanent, then drop the damaged member and add a
new member by using the following procedure.


To replace a damaged member of a redo log group:
===============================================
Locate the filename of the damaged member in V$LOGFILE. The status is INVALID
if the file is inaccessible:

SQL> SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE WHERE STATUS='INVALID';

GROUP# STATUS MEMBER
------- ----------- ---------------------
0002 INVALID /oracle/oradata/trgt/redo02.log


++ Drop the damaged member. For example, to drop member redo01.log from group 2,
issue:

SQL > ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/trgt/redo02.log';

++ Add a new member to the group. For example, to add redo02.log to group 2,
issue:
SQL> ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log'
TO GROUP 2;

If the file you want to add already exists, then it must be the same size
as the other group members, and you must specify REUSE. For example:

SQL > ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/trgt/redo02b.log'
REUSE TO GROUP 2;

2) Losing an Inactive Online Redo Log Group
===========================================

If all members of an online redo log group with INACTIVE status are damaged,
then the procedure depends on whether you can fix the media problem that
damaged the inactive redo log group.

If the failure is . . Temporary... then Fix the problem. LGWR can reuse
the redo log group when required.
If the failure is ... Permanent then the damaged inactive online redo log
group eventually halts normal database operation.

ACTION PLAN
============

Reinitialize the damaged group manually by issuing the ALTER DATABASE CLEAR
LOGFILE
You can clear an inactive redo log group when the database is open or closed.
The procedure depends on whether the damaged group has been archived.

To clear an inactive, online redo log group that has been archived:
--------------------------------------------------------------------
If the database is shut down, then start a new instance and mount the database:
STARTUP MOUNT


Reinitialize the damaged log group. For example, to clear redo log group 2,
issue the following statement:
ALTER DATABASE CLEAR LOGFILE GROUP 2;


Clearing Inactive, Not-Yet-Archived Redo
========================================
Clearing a not-yet-archived redo log allows it to be reused without archiving
it. This action makes backups unusable if they were started before the last
change in the log, unless the file was taken offline prior to the first change
in the log. Hence, if you need the cleared log file for recovery of a backup,
then you cannot recover that backup. Also, it prevents complete recovery from
backups due to the missing log.

To clear an inactive, online redo log group that has not been archived:

If the database is shut down, then start a new instance and mount the database:
STARTUP MOUNT


Clear the log using the UNARCHIVED keyword. For example, to clear log group 2,
issue:
ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2;

If there is an offline datafile that requires the cleared log to bring it
online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile
and its entire tablespace have to be dropped because the redo necessary to
bring it online is being cleared, and there is no copy of it. For example,
enter:

ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2 UNRECOVERABLE DATAFILE;

Immediately back up the whole database with an operating system utility,
so that you have a backup you can use for complete recovery without relying
on the cleared log group. For example, enter:
% cp /disk1/oracle/dbs/*.f /disk2/backup

Back up the database's control file with the ALTER DATABASE statement. For
example, enter:
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/dbs/cf_backup.f';


Failure of CLEAR LOGFILE Operation
----------------------------------------
The ALTER DATABASE CLEAR LOGFILE statement can fail with an I/O error due to
media failure when it is not possible to:

* Relocate the redo log file onto alternative media by re-creating it under
the currently configured redo log filename

* Reuse the currently configured log filename to re-create the redo log file
because the name itself is invalid or unusable (for example, due to media
failure)

In these cases, the ALTER DATABASE CLEAR LOGFILE statement (before receiving
the I/O error) would have successfully informed the control file that the log
was being cleared and did not require archiving.

The I/O error occurred at the step in which the CLEAR LOGFILE statement
attempts to create the new redo log file and write zeros to it. This fact is
reflected in V$LOG.CLEARING_CURRENT.

Show Related Information Related


Products
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Standard Edition
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Personal Edition
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
  • Enterprise Management > Enterprise Manager Consoles, Packs, and Plugins > Managing Databases using Enterprise Manager > Enterprise Manager for RDBMS
Errors
ORA-353; ORA-344; ORA-354; ORA-313; ORA-312

Back to topBack to top



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-686981/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-12

  • 博文量
    196
  • 访问量
    607596