Oracle online redo log file recovery (redolog)

Non-current online Log File fault recovery 1. ORA-00313 error when starting the database. ORA-00313: openfailedformembersofloggroup

Non-current online Log File fault recovery 1. ORA-00313 error when starting the database. ORA-00313: open failed for members of log group

Non-current online Log File fault recovery

1. ORA-00313 error when starting the database.

ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/Oracle/oradata/s1/redo01.log'

2. view the v $ log view.

SQL> select * from v $ log;

GROUP # THREAD # SEQUENCE # bytes members arc status FIRST_CHANGE # FIRST_TIM
-------------------------------------------------------------------------------------------
1 1 11 52428800 1 yes inactive 489912 09-NOV-12
3 1 10 52428800 1 yes inactive 488868 08-NOV-12
2 1 12 52428800 1 no current 519028 11-NOV-12

3. Use the clear command to recreate the log file.

If the log file has been archived:
SQL> alter database clear logfile group 1;

Database altered.

If the log file is not archived:
SQL> alter database clear unarchived logfile group 3;

Database altered.

4. Open the database and back up the database again (clear, especially clear unarchived, is recommended for a full backup)
SQL> alter database open;

Database altered.

Current online Log File fault recovery

In archive mode, the current log file is damaged in two ways.

1. When the database is shut down normally, instances need to be restored for unresolved transactions in the log file. If the current log group is damaged, users can directly
Alter database clear unarchived logfile group n to recreate

2. There are active events in the log file, and the database needs to recover the media. There are two ways to remedy this problem.
A. Backup is available in archive mode.
B. No backup is available and mandatory recovery may cause Database Inconsistency.

A. Available backup in archive mode (Incomplete recovery)
1. An error was reported when the database was started and viewed with v $ log.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/oradata/s1/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

SQL> select * from v $ log;

GROUP # THREAD # SEQUENCE # bytes members arc status FIRST_CHANGE # FIRST_TIM
-------------------------------------------------------------------------------------------
1 1 16 52428800 1 no current 520744 11-NOV-12
2 1 15 52428800 1 yes active 520712 11-NOV-12
3 1 14 52428800 1 yes inactive 520705 11-NOV-12

2. Try clear recovery

SQL> alter database clear logfile group 1;

SQL> alter database clear unarchived logfile group 1;
Alter database clear unarchived logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance s1 (thread 1)
ORA-00312: online log 1 thread 1: '/home/oracle/oradata/s1/redo01.log'

3. Execute recover database until cancel. First the user auto and then use cancel. The error is as follows:
SQL> recover database until cancel;
ORA-00279: change 520744 generated at 11/11/2012 22:43:39 needed for thread 1
ORA-00289: suggestion:/archive1/10916_798842981.dbf
ORA-00280: change 520744 for thread 1 is in sequence #16
Specify log :{ = Suggested | filename | AUTO | CANCEL}
Auto
ORA-00308: cannot open archived log '/archive1/41516_798842981.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00308: cannot open archived log '/archive1/41516_798842981.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/home/oracle/oradata/s1/system01.dbf'

SQL> recover database until cancel;
ORA-00279: change 520744 generated at 11/11/2012 22:43:39 needed for thread 1
ORA-00289: suggestion:/archive1/10916_798842981.dbf
ORA-00280: change 520744 for thread 1 is in sequence #16
Specify log :{ = Suggested | filename | AUTO | CANCEL}
Cancel
ORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/home/oracle/oradata/s1/system01.dbf'
ORA-01112: media recovery not started

4. restore the database under rman;
RMAN> restore database;

5. recover database under rman: the error is as follows:
RMAN> recover database;

Starting recover at 11-NOV-12
Using channel ORA_DISK_1

Starting media recovery
Media recovery failed
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================
RMAN-03002: failure of recover command at 22:54:15
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
Start
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/home/oracle/oradata/s1/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3