Redo logs
---------
In normal cases, we would not have backups of online redo log files. But the
inactive logfile changes could already have been checkpointed on the datafiles
and even archive log files may be available.
SQL> startup mount
Oracle Instance Started
Database mounted
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/ORACLE/ORADATA/H817/REDO01.LOG'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
** Verify if the lost redolog file is Current or not.
SQL> select * from v$log;
SQL> select * from v$logfile;
--> If the lost redo log is an Inactive logfile, you can clear the logfile:
SQL> alter database clear logfile '/ORACLE/ORADATA/H817/REDO01.LOG';
Alternatively, you can drop the logfile if you have atleast two other
logfiles:
SQL> alter database drop logfile group 1;
--> If the logfile is the Current logfile, then do the following:
SQL> recover database until cancel;
Type Cancel when prompted
SQL>alter database open resetlogs;
The 'recover database until cancel' command can fail with the following
errors:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error
below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/ORACLE/ORADATA/H817/SYSTEM01.DBF'
In this case , restore an old backup of the database files and apply the
archive logs to perform incomplete recovery.
--> restore old backup
SQL> startup mount
SQL> recover database until cancel using backup controlfile;
SQL> alter database open resetlogs;
If the database is in noarchivelog mode and if ORA-1547, ORA-1194 and ORA-1110 errors occur, then you would have restore from an old backup and start the database.
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=230829.1
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=186137.1
Losing an Active Online Redo Log Group
If the database is still running and the lost active log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If successful, your active log is rendered inactive, and you can follow the procedure in "Losing an Inactive Online Redo Log Group". If unsuccessful, or if your database has halted, perform one of these procedures, depending on the archiving mode.
Note that the current log is the one LGWR is currently writing to. If a LGWR I/O fails, then LGWR terminates and the instance crashes. In this case, you must restore a backup, perform incomplete recovery, and open the database with the RESETLOGS option.
To recover from loss of an active online redo log group in NOARCHIVELOG mode:
1. If the media failure is temporary, correct the problem so that Oracle can reuse the group when required.
2. Restore the database from a whole database backup using an operating system utility. For example, enter:
NT > !copy c:\backup\*.dbf c:\data\
3. Mount the database:
SVRMGR> startup mount;
4. Open the database using the RESETLOGS option:
SVRMGR> alter database open resetlogs;
5. Shut down the database normally:
SVRMGR> shutdown immediate;
6. Perform a closed whole backup
To recover from loss of an active online redo log group in ARCHIVELOG mode:
1. If the media failure is temporary, then correct the problem so that Oracle can reuse the group when required.
2. Perform incomplete media recovery. Use the procedure given in Loss of Current Redo Logs, recovering up through the log before the damaged log.
Loss of Current Redo Logs -- database is Closed.
If the Database is closed, media failure may have occurred or a background process may have terminated. Used the steps below to rectify this situation:
1. Attempting to open the database will immediately notify you of the current Redo Log group is missing.
SVRMGR> startup;
ORACLE instance started.
Total System Global Area 73418780 bytes
Fixed Size 75804 bytes
Variable Size 56487936 bytes
Database Buffers 16777216 bytes
Redo Buffers 77824 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\INFOMAX\REDO03.LOG'
2. Since the log group 3 is the current log group, it will not have been archived. Using the "Clear logfile" command is no use since:
SVRMGR> alter database clear unarchived logfile group 3;
ORA-01624: log 3 needed for crash recovery of thread 1
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\INFOMAX\REDO03.LOG'
3. Incomplete recovery is therefore required. First, you must note the current log sequence number:
SQL> select * from v$log;
GROUP#.... SEQUENCE# BYTES MEMBERS ARC STATUS ........ FIRST_TIME
------------ ---------------- ----------- --------------- ------- -------------- ------------------
1 ..... 448 1048576 1 YES INACTIVE ..... 09-MAR-01
2 ..... 449 1048576 1 YES INACTIVE ..... 09-MAR-01
3 ..... 450 1048576 1 NO CURRENT ..... 09-MAR-01
4. Restore all datafiles from a previous backup:
UNIX> !cp /disk1/backup/*.dbf /disk2/data/
5. Recover using "Until Cancel"
SVRMGR> recover database until cancel';
6. NOTE: When it comes to apply the Redo Logs # 450, type cancel.
7. Open the database using "resetlogs" options:
SVRMGR> alter database open resetlogs;
8. The database should now be operational, since any missing logfiles will be recreated.
NOTE: If the logfiles need to be recreated on another disk due to media failure, use the "alter database drop logfile group" and "alter database add log group" commands to manually create the log files. See Re-creating Redo Logs.
9. Since you just performed incomplete recovery, the database should now be backed up.
The following information is useful for implementation of recovery on redolog group
How to recover using the online redo log
========================================
PURPOSE
-------
To easily and quickly find out if the online redo log files can be used to recover a database.
AUDIENCE
--------
This document is addressed to DBAs that want to quickly find the best recovery solution in case of a database crash.
HOW TO
------
Many databases today are run without archive logging enabled, this reduces the available options to quickly recover a database. Basically 2 options are available:
a) restore from a backup
b) recover the database using the online redo logs.
Option a) is straight forward and will not be covered here. Only important
thing to mention is that option a) WILL cause loss of data if there has
been updates/inserts to the database since the backup was taken.
Let us instead take a look at option b):
In case of a database crash or a database that will not startup
due to ORA-1110, ORA-1113 or both, we first need to identify which
files need to be recovered.
1) First we mount the database then issue the following query:
select * from v$recover_file;
This will give us a list of the files that need media recovery. It
will also give us CHANGE#, i.e. the SCN where the media recovery
must start. To get the name of the file use the FILE# column and
query against v$datafile like this:
select name from v$datafile
where file# =
or like this in 9i:
select substr(d.name,1,30) fname
, r.online_status
, r.error
, r.change#
, r.time
from v$datafile d, v$recover_file r
where d.file# = r.file#
2) Next we do:
archive log list
This will give us the current log sequence. We need the current
log sequence -1. This will give us the last redo log file that
was in use.
3) As the last step we do:
select recid
, stamp
, sequence#
, first_change#
, next_change#
from v$log_history
where recid = less than symbol current log sequence -1)
This will show us the NEXT_CHANGE#, i.e.the highest SCN, in the redo
log file. It will also give us the FIRST_CHANGE# SCN in
this redo log file. We need these 2 SCN numbers to find out if we
can use the redo log file for recovery against the file(s) found
in 1). If the CHANGE# from 1) is between the FIRST_CHANGE# and the
NEXT_CHANGE# then we can use the redo log file for recovery.
If the CHANGE# from 1) is lower than FIRST_CHANGE# we need to
investigate an earlier online redo log file.
When we have identified which redo log files to use for the recovery we
perform the recovery using the redo log file(s) in the following way:
- mount the database
- recover database
The recover process will now display something similar to the following:
ORA-00279: change 12599 generated at 08/18/98 13:25:48 needed for thread 1
ORA-00289: suggestion : /oracle/OFA_base/app/oracle/admin/NE804DB1/arch/129.arc
ORA-00280: change 12599 for thread 1 is in sequence #129
Specify log: {
As the database is not in ARCHIVELOG mode we will not have the 129.arc
file. Instead the name of the redo log file must be entered on the
command line. The filename must include the full path. After the redo
log file has been applied the recover process will return: Log applied.
At this stage 2 things can happend:
1) Media Recovery Completed
2) or additional redo log files must be applied
If 2) then just specify the next redo log file on the command line and
continue to do so until Media Recovery Completed is displayed. Then the
database can be opened with: alter database open;
If we do not find any online redo log files which covers the CHANGE#
from 1) we cannot do a recover of the database or datafile(s). This
means we are left with only 2 options of bringing the database back online:
1) restore from a valid backup taken before the crash. Doing so and
running the database in NOARCHIVELOG MODE will cause a loss of data.
This is unavoidable.
2) force the database open. This will override Oracle's internal
datafile synchronisation and consistency check. The result is an
inconsistent database. The database MUST now be exported and rebuild
as an inconsistent database is unreliable, and hence not supported.
This last option should only be used in cooperation with Oracle Support
ALTER SYSTEM SWITCH LOGFILE and CHECKPOINT:
Question: Will "alter system switch logfile" trigger checkpoint?
Answer: It will only trigger a complete checkpoint when active redo log group to be overwritten due to circular fashion. In other words, "alter system switch logfile" may trigger complete checkpoint, but not always
http://www.dbaglobe.com/2010/02/alter-system-switch-logfile-and.html
1) Alter system switch logfile.
Well this you can fire even when your database is not in archive log mode.What it does is switch your log from one log file to other even though its not filled.
Now if the database is in archive log mode then after the log switch the archive for the logfile is created. This will archive only inactive logs..
2) Alter system switch archive log all ;
Will archive all your inactive redo log files but not your current redo log file.
In certain scenario where you need to manually archive your existin gredo log file you use alter system archive log all;
3) While alter system swich archive log current;
Will archive all inactive redolog files and also our current redo logfile.
**Now while firing alter system swich logfile and alter system switch archive log current the database has to be open
while firing alter system archive log all it can be done even in mount state
http://forums.oracle.com/forums/thread.jspa?messageID=3296310�
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
ALTER SYSTEM ARCHIVE LOG CURRENT and SCN
TESTING1 >select max(next_change#) from v$archived_log;
MAX(NEXT_CHANGE#)
-----------------
4095858
TESTING1 >select current_scn from v$database;
CURRENT_SCN
-----------
4097620
Current changes are written to archived log triggered by "alter system archive log current".
TESTING1 >alter system archive log current;
System altered.
TESTING1 >select max(next_change#) from v$archived_log;
MAX(NEXT_CHANGE#)
-----------------
4097634
TESTING1 >select current_scn from v$database;
CURRENT_SCN
-----------
4097639
http://www.dbaglobe.com/search/label/Oracle
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=186137.1
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=230829.1
No comments:
Post a Comment