Tag Archives: redo

ORA-00353: during startup of instance from archiver process

Posted on by
Reply

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.

Advertisements

How to add or drop online-redo logs?

Posted on by
Reply

Below are the steps on how to add a new online redo-group. It could be of the same size as other groups or different size.
1) List log groups, their archive status, activity status and size
SQL> SELECT GROUP#, ARCHIVED, STATUS, BYTES FROM V$LOG;

GROUP# ARC STATUS BYTES
———- — —————- ———–
1 NO ACTIVE 104857600
2 NO CURRENT 104857600
3 NO INACTIVE 104857600

2) Add a new log group with a different size and query v$log to show the activity status of the new log member is UNUSED
SQL> ALTER DATABASE
ADD LOGFILE GROUP 4 (‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04A.LOG’,
‘E:\ORACLE\DB\TESTDB\ORADATA\REDO04B.LOG’)
SIZE 200M;

Database altered.

SQL> select group#, archived, status, bytes from v$log;

GROUP# ARC STATUS BYTES
———- — —————- ———-
1 NO CURRENT 104857600
2 NO INACTIVE 104857600
3 NO INACTIVE 104857600
4 YES UNUSED 209715200

Droping online redo logs, if the redo log is Status is CURRENT or ACTIVE, the on-line redo log cannot be dropped. When trying to drop CURRENT online redo log, Oracle will return “ORA-01623: log 1 is current log for instance testdb (thread 1) – cannot drop” error message. When trying to drop online redo log in ACTIVE status, Oracle will return “ORA-01624: log 1 needed for crash recovery of instance testdb (thread 1)”

To drop the online redo log, the files in the group has to be dropped manually.
SQL> select * from v$logfile where group# = 3;

GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- —————————————- —
3 ONLINE E:\ORACLE\DB\TESTDB\ORADATA\REDO03A.LOG NO
3 ONLINE E:\ORACLE\DB\TESTDB\ORADATA\REDO03B.LOG NO

SQL> ALTER DATABASE DROP LOGFILE GROUP 3;

Database altered.

How to fix issue when the online redo logs are missing?

Posted on by
Reply

If for some reason the online redo log were lost or when cloning the copy of shutdown database you don’t have the on-line redo logs, you would need to open the database with “RESETLOGS” option which will create the online redo logs.

Example:
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 521936896 bytes
Fixed Size 2177328 bytes
Variable Size 356517584 bytes
Database Buffers 155189248 bytes
Redo Buffers 8052736 bytes
SQL> alter database mount;

Database altered.
SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.

-- shows online redo logs have been recreated
SQL> select member from v$logfile;

MEMBER
—————————————–
E:\ORACLE\DB\TESTDB\ORADATA\REDO01A.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO01B.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO02A.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO02B.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO03A.LOG
E:\ORACLE\DB\TESTDB\ORADATA\REDO03B.LOG

6 rows selected.

One has perform “RECOVER DATABASE UNTIL CANCEL” before opening the database, if one doesn’t Oracle will generate the following error when trying to open with resetlogs:
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

redo log generation during unrecoverable/nologging

Posted on by
Reply

# Capture amount of redo logs generated when table is created in UNRECOVERABLE
SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 597736

SQL> create table test_all unrecoverable as (select * from all_objects);

Table created.

SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 648936

CREATE TABLE with NOLOGGING option
SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 1193320

SQL> create table test_all nologging as (select * from all_objects);

Table created.

SQL> select vs.name, vm.value
from v$mystat vm, v$statname vs
where vm.statistic# = vs.statistic#
and vs.name like '%redo size%';

NAME VALUE
—————————————————————- ———-
redo size 1244436

Amount of redo logs generated for CREATE TABLE AS
– without UNRECOVERABLE/NOLOGGING option = 478904
– UNRECOVERABLE option = 51200
– NOLOGGING option = 51116
The amount of redo size is considerable less when nologging/unrecoverable is used.
Note: The database has to be in ARCHIVELOG mode to see the difference, if the database is in NOARCHIVELOG mode you won't see the difference.

How to fix Online redo log corruption?

Posted on by
Reply

Today, due to a network issue the host (Solaries) lost contact with the NetApp filer and it corrupted some datafiles and redo log files.
The solution was to recreate all indexes from the corrupted datafile in a new tablespace and drop the corrupted tablespace.
The archived log process hung because it could not archive the log 464704 which got corrupted, the solution was to clear the redo log and manually switch the logs to test the fix.
1- Archived log hung because the online redo log was corrupted and could not be archived.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
1 1 464713 104857600 2 NO CURRENT
2 1 464711 104857600 2 NO INACTIVE
3 1 464709 104857600 2 NO INACTIVE
4 1 464704 104857600 2 NO INACTIVE
5 1 464712 104857600 2 NO INACTIVE
6 1 464710 104857600 2 NO INACTIVE

6 rows selected.

2- When tried to clear the online redo log it wouldn’t let us do it because of the datafile corrupted as well.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4
*
ERROR at line 1:
ORA-00393: log 4 of thread 1 is needed for recovery of offline datafiles
ORA-00312: online log 4 thread 1: ‘/oracle/EPB/WISPRD/redo/redo2/redo4b.log’
ORA-00312: online log 4 thread 1: ‘/oracle/EPB/WISPRD/redo/redo1/redo4a.log’
ORA-01110: data file 281: ‘/oracle/EPB/WISPRD/data/cust_index_jul2007.dbf’

3- Found an additional clause to force the CLEAR.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 4 UNRECOVERABLE DATAFILE ;

Database altered.

4- Online redo log group is now clear but keep in mind that we don’t have that archive log (464704) and recoverability is compromised at this point. We need to take a full snap backup.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
1 1 464713 104857600 2 NO INACTIVE
2 1 464711 104857600 2 YES INACTIVE
3 1 464715 104857600 2 NO CURRENT
4 1 0 104857600 2 YES UNUSED
5 1 464712 104857600 2 YES INACTIVE
6 1 464714 104857600 2 NO INACTIVE
6 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> select GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
1 1 464719 104857600 2 YES INACTIVE
2 1 464717 104857600 2 YES INACTIVE
3 1 464715 104857600 2 YES INACTIVE
4 1 464720 104857600 2 YES ACTIVE
5 1 464718 104857600 2 YES INACTIVE
6 1 464721 104857600 2 NO CURRENT

6 rows selected.
SQL> DROP TABLESPACE CUST_INDEX_JUL2007 INCLUDING CONTENTS;

Tablespace dropped.
SQL> select distinct status from v$datafile;

STATUS
——-
ONLINE
SYSTEM

After all this a full backup was taken.

A hope that helps…

How full is the current redo log file?

Posted on by
Reply

SQL> SELECT le.leseq “Current log sequence No”,
100*cp.cpodr_bno/le.lesiz “Percent Full”,
cp.cpodr_bno “Current Block No”,
le.lesiz “Size of Log in Blocks”
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8;

Current log sequence No Percent Full Current Block No Size of Log in Blocks
———————– ———— —————- ———————
13 29.0625 29760 102400