Oracle 10.2.0.5 non-archived current Redolog corruption treatment example

Operating system: RHEL5.8 x64
Database: Oracle 10.2.0.5.0
Failure Condition:
A single-machine dawning PC server 4 300G SAS disk, RAID5 bad two disk (server panel no fault prompt, no one found), causing RAID5 disk array hangs, operating system when the machine, the system can not boot. After the data recovery company recovers the disk data, the data file is recovered, and the following error message is found when starting the database:

1. Database Warning Log

Tail-f Alert_orcl.log

Database mounted in Exclusive Mode
Completed:alter DATABASE MOUNT
Sat Sep 2017 02:32:44 CST
ALTER DATABASE OPEN
Sat Sep 2017 02:32:44 CST
Beginning crash Recovery of 1 threads
Parallel recovery started with 2 processes
Sat Sep 2017 02:32:44 CST
Started Redo Scan
Sat Sep 2017 02:32:44 CST
Errors in FILE/U01/APP/ORACLE/ADMIN/ORCL/UDUMP/ORCL_ORA_27990.TRC:
Ora-00313:open failed for members of log Group 3 of thread 1
Ora-00312:online Log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log '
Ora-27046:file size is not a multiple of logical block size
Additional Information:1
Sat Sep 2017 02:32:44 CST
Aborting crash recovery due to error 313
Sat Sep 2017 02:32:44 CST
Errors in FILE/U01/APP/ORACLE/ADMIN/ORCL/UDUMP/ORCL_ORA_27990.TRC:
Ora-00313:open failed for members of log Group 3 of thread 1
Ora-00312:online Log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log '
Ora-27046:file size is not a multiple of logical block size
Additional Information:1
ORA-313 signalled During:alter DATABASE OPEN ...

2. Prompt at startup

[Email protected] ~]$ Sqlplus/as sysdba;

Sql*plus:release 10.2.0.5.0-production on 6 02:28:33 2017

Copyright (c) 1982, Oracle. All rights Reserved.

Connected to an idle instance.

Sql> startup;
ORACLE instance started.

Total System Global area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 553648648 bytes
Database buffers 1040187392 bytes
Redo buffers 14680064 bytes
Database mounted.
Ora-00313:open failed for members of log Group 3 of thread 1
Ora-00312:online Log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log '
Ora-27046:file size is not a multiple of logical block size
Additional Information:1

Sql>

According to the situation is the database non-compliant files, redo file corruption, failure in the morning 7 O ' time.

Sql> select Open_mode from V$database;

Open_mode
----------
Mounted

Sql> set pagesize 999;
Sql> set Linesize 200;
Sql> alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';
Session altered.

Sql> select * from V$log;

group# thread# sequence# BYTES members ARC STATUS first_change# first_time
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
1 1 3406 52428800 1 NO INACTIVE 187001363 2017-08-20 07:02:30
3 1 3408 52428800 1 NO current 187084379 2017-08-21 07:03:24
2 1 3407 52428800 1 NO INACTIVE 187044837 2017-08-20 19:40:06

Current log corruption

Sql>
Sql> create Pfile from SPFile;

File created.

Add an implicit parameter to the Pfile:
*._allow_resetlogs_corruption=true
*._allow_error_simulation=true

sql> startup Mount Pfile= '/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora '
ORACLE instance started.

Total System Global area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 553648648 bytes
Database buffers 1040187392 bytes
Redo buffers 14680064 bytes
Database mounted.

Try Resetlogs Open the database again

sql> ALTER DATABASE open resetlogs;
ALTER DATABASE open Resetlogs
*
ERROR at line 1:
Ora-01139:resetlogs option Valid after an incomplete database recovery

sql> recover database until cancel;
Ora-00279:change 187084379 generated at 08/21/2017 07:03:24 needed for thread 1
Ora-00289:suggestion:/u01/app/oracle/flash_recovery_area/orcl/archivelog/2017_10_18/o1_mf_1_3408_%u_.arc
Ora-00280:change 187084379 for thread 1 are in sequence #3408

Specify log: {<ret>=suggested | AUTO | CANCEL}
Auto
Ora-00308:cannot Open archived log '/u01/app/oracle/flash_recovery_area/orcl/archivelog/2017_10_18/o1_mf_1_3408_%u _.arc '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3

Ora-00308:cannot Open archived log '/u01/app/oracle/flash_recovery_area/orcl/archivelog/2017_10_18/o1_mf_1_3408_%u _.arc '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3

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: '/U01/APP/ORACLE/ORADATA/ORCL/SYSTEM01.DBF '

sql> ALTER DATABASE open resetlogs;
ALTER DATABASE open Resetlogs
*
ERROR at line 1:
Ora-00603:oracle server session terminated by fatal error

The Alter log reported the following error:

Wed Oct 03:06:38 CST 2017
Errors in FILE/U01/APP/ORACLE/ADMIN/ORCL/UDUMP/ORCL_ORA_18057.TRC:
Ora-00600:internal error code, arguments: [2662], [0], [187086068], [0], [187092671], [8388617], [], []
Ora-01092:oracle instance terminated. Disconnection forced
Ora-00600:internal error code, arguments: [2662], [0], [187086066], [0], [187092671], [8388617], [], []
Wed Oct 03:06:39 CST 2017
Errors in FILE/U01/APP/ORACLE/ADMIN/ORCL/UDUMP/ORCL_ORA_18057.TRC:
Ora-00600:internal error code, arguments: [2662], [0], [187086069], [0], [187092671], [8388617], [], []
Ora-00600:internal error code, arguments: [2662], [0], [187086068], [0], [187092671], [8388617], [], []
Ora-01092:oracle instance terminated. Disconnection forced
Ora-00600:internal error code, arguments: [2662], [0], [187086066], [0], [187092671], [8388617], [], []
Wed Oct 03:06:40 CST 2017
Errors in FILE/U01/APP/ORACLE/ADMIN/ORCL/UDUMP/ORCL_ORA_18057.TRC:
Ora-00600:internal error code, arguments: [2662], [0], [187086069], [0], [187092671], [8388617], [], []
Ora-00600:internal error code, arguments: [2662], [0], [187086068], [0], [187092671], [8388617], [], []
Ora-01092:oracle instance terminated. Disconnection forced
Ora-00600:internal error code, arguments: [2662], [0], [187086066], [0], [187092671], [8388617], [], []

[2662] The need to advance the SCN to make the SCN into a consistent state.

Sql> Conn/as SYSDBA
Connected to an idle instance.

sql> startup Mount Pfile= '/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora ';
ORACLE instance started.

Total System Global area 167772160 bytes
Fixed Size 1272600 bytes
Variable Size 96470248 bytes
Database buffers 67108864 bytes
Redo buffers 2920448 bytes
Database mounted.

Sql> alter session SET events ' 10015 trace name ADJUST_SCN level 1 ';
Session altered.

sql> ALTER DATABASE open;
Database altered.

Note: After removing two parameters, create SPFile from Pfile;

The database starts normally, the current redo log is corrupted, the data is lost theoretically, considering the early 7 o'clock, the application has no business data generation, the observation application and the test function are normal, so the database is not logically rebuilt.

This article is from the "Koumm Linux Technology blog" blog, be sure to keep this source http://koumm.blog.51cto.com/703525/1973695

Oracle 10.2.0.5 non-archived current redolog corruption handling example