ORA-00353: during startup of instance from archiver process

When starting one of the databases we received the ORA-00353 error. It indicates that archiver process couldn’t archive one of the redo logs due to corruption. Oracle tried to read both the logmembers from the same group but they both were corrupted and instance crashes when trying to open it.

ARC0: STARTING ARCH PROCESSES COMPLETE
ARCH: Log corruption near block 88727 change 12629319 time ?
CORRUPTION DETECTED: thread 1 sequence 221 log 2 at block 88727. Arch found corrupt blocks
Errors in file i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_3796.trc (incident=25175):
ORA-00353: log corruption near block 88727 change 12629319 time 05/07/2012 21:13:47
ORA-00312: online log 2 thread 1: ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02B.LOG’
ORA-00312: online log 2 thread 1: ‘J:\DB\ORACLE\TESTDB\ORADATA\REDO02A.LOG’

USER (ospid: 3796): terminating the instance due to error 16038
Instance terminated by USER, pid = 3796

So to confirm the first step was after database was open in mount state to verify both online redo logfiles in the group had corruption.

SQL> ALTER SYSTEM DUMP LOGFILE ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02B.LOG’;
ALTER SYSTEM DUMP LOGFILE ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02B.LOG’
*
ERROR at line 1:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 88727 change 12629410 time 05/08/2012
09:01:28
ORA-00334: archived log: ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02B.LOG’

SQL> ALTER SYSTEM DUMP LOGFILE ‘J:\DB\ORACLE\TESTDB\ORADATA\REDO02A.LOG’;
ALTER SYSTEM DUMP LOGFILE ‘J:\DB\ORACLE\TESTDB\ORADATA\REDO02A.LOG’
*
ERROR at line 1:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 88727 change 12629410 time 05/08/2012
09:01:28
ORA-00334: archived log: ‘I:\DB\ORACLE\TESTDB\ORADATA\REDO02A.LOG’

In this case both files had corruption but note if only of them had corruption you one can copy the file that is no corruption to the corrupted one.

After it was confirmed both files had corruption, using the following steps one can startup the database it will require changing the database to noarchivelog mode so the archiver process doesn’t try to archive the online redo logs. Note: You should take a full backup before you start this step and after this step is done.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 388354048 bytes
Fixed Size 2175968 bytes
Variable Size 331353120 bytes
Database Buffers 50331648 bytes
Redo Buffers 4493312 bytes
Database mounted.

SQL> ALTER DATABASE NOARCHIVELOG;
Database altered.

SQL> ALTER DATABASE OPEN;
Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 388354048 bytes
Fixed Size 2175968 bytes
Variable Size 331353120 bytes
Database Buffers 50331648 bytes
Redo Buffers 4493312 bytes

SQL> alter database mount;
Database altered.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

DBA_HIST_DATABASE_INSTANCE

In 10g there is a view called DBA_HIST_DATABASE_INSTANCE which has database instance information. It has history on information such as instance startup time, last ASH sample id.

Example:
SQL> column startup_time format a35
SQL> alter session set nls_timestamp_format=’DD-MON-YYYY HH24:MI:SS’;

Session altered.

SQL> SELECT startup_time, last_ash_sample_id FROM dba_hist_database_instance WHERE dbid = 1992878807 order by startup_time;

STARTUP_TIME LAST_ASH_SAMPLE_ID
—————————— ——————
12-AUG-2009 21:12:19 447735
21-AUG-2009 17:48:29 494755
..
27-SEP-2009 15:24:04 3036072
29-SEP-2009 21:44:22 0

8 rows selected.

How to tell if the database was started with spfile or pfile?

There are couple of ways of finding if the database was started with spfile or pfile.

One way would be tell show the check the value of parameter spfile, if it returns blank then database was started by pfile.

— belows show database was started by spfile
SQL> show parameter spfile;

NAME TYPE VALUE
—— —— ————————————————
spfile string /u01/apps/oracle/10g/dbs/spfileorcltest.ora

Another way to find would be set the parameter with scope=spfile, if database was started with spfile one will be able to set the value if it’s started with pfile you will see the “ORA-32001: write to spfile requested but no SPFILE specified at startup”. Note: You can set the new value to be the same as current value of the parameter, it doesn’t have to be a different one.

SQL> ALTER SYSTEM set open_cursors=300 scope=spfile;
ALTER SYSTEM set open_cursors=300 scope=spfile;
*
ERROR at line 1:
ORA-32001: write to spfile requested but no SPFILE specified at startup