Recover Database from ORA-00333: redo log read error

In development environment, it is very common scenario that we have multiple databases in a single machine by using VMware (i.e, each VMware contains one database). Again those machines doesn't have consistant power backup. Therefore we have to face power failure or VMware hang-up. So, we are forced to restart the machine while databases are still up & running. After restarting the machine, we have mostly got he following error:

   ORA-00333: redo log read error block count .

Here are the steps to overcome the error

SQL> startup
ORACLE instance started.
Total System Global Area ***** bytes
Fixed Size               ***** bytes
Variable Size            ***** bytes
Database Buffers         ***** bytes
Redo Buffers             ***** bytes
Database mounted.
 
 ORA-00333: redo log read error block *Number* count *Number*

Step 1: As the Db is in mount mode, We can query v$log & v$logfile to identify the status of log file group and their member.

 SQL> select l.status, member from v$logfile inner join v$log l using (group#); 

 STATUS  MEMBER
 ------------- --------------------------------------
 CURRENT /oracle/fast_recovery_area/redo01.log
 INACTIVE /oracle/fast_recovery_area/redo02.log
 INACTIVE /oracle/fast_recovery_area/redo03.log

Step 2: Recover the database using ackup controlfile.

 SQL> recover database using backup controlfile;

ORA-00279: change  generated at  needed for thread 1
ORA-00289: suggestion : /oracle/fast_recovery_area/archivelog/o1_mf_1_634_%u_.arc
ORA-00280: change  for thread 1 is in sequence #
Specify log: {=suggested | filename | AUTO | CANCEL}

Step3: Give 'CURRENT' log file member along with location as input. If it does not work give other log file members along with location in input prompt. In our case we give 

/oracle/fast_recovery_area/redo01.log

Log applied.
Media recovery complete.

Step 4: Open the database with reset logfile

SQL> alter database open resetlogs;

Database altered.

Manage SYS.AUD$ table

From 11g, Oracle by default enable auditing with 'DB' option.  That means audit information will store in database table. With this settings Oracle will audit following activities:

ALTER ANY PROCEDURE, ALTER ANY TABLE, ALTER DATABASE,ALTER PROFILE, ALTER SYSTEM, ALTER USER, AUDIT SYSTEM, CREATE ANY JOB, CREATE ANY LIBRARY, CREATE ANY PROCEDURE, CREATE ANY TABLE, CREATE EXTERNAL JOB, CREATE PUBLIC DATABASE LINK, CREATE SESSION, CREATE USER, DATABASE LINK, DROP ANY PROCEDURE, DROP ANY TABLE, DROP PROFILE
DROP USER, EXEMPT ACCESS POLICY, GRANT ANY OBJECT PRIVILEGE, GRANT ANY PRIVILEGE, GRANT ANY ROLE, PROFILE, PUBLIC SYNONYM ROLE, SYSTEM AUDIT

Apart from lot of benefits,  auditing may raise performance issues and the reasons are:

• Too much activity is being audited
• AUD$ table still placed in the SYSTEM tablespace
• Oracle bugs.

1. Too much is being audited

The more activity you audit the more audit records will generate.  You need to restrict unnecessary auditing because it cost your resources and hamper performance. To see which activities are being audited

  

SELECT  * FROM DBA_PRIV_AUDIT_OPTS UNION SELECT * FROM DBA_STMT_AUDIT_OPTS;

2. AUD$ table still placed in SYSTEM tablespace:

By default AUD$ table lies in SYSTEM tablespace.  You will face space managment issue if you not move AUD$ table in SYSAUX tablespace along with indexes.  Use Oracle recommended package DBMS_AUDIT  to purge old unnecessary entries. Periodically shrink / truncate AUD$ table.

 BEGIN

  DBMS_AUDIT_MGMT.set_audit_trail_location(

    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,

    audit_trail_location_value => 'SYSAUX');

END;

/

BEGIN
  DBMS_AUDIT_MGMT.clean_audit_trail(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;
/

CREATE TABLE AUD_BACKUP AS SELECT * FROM AUD$;

TRUNCATE AUD$;

INSERT INTO AUD$ SELECT * FROM AUD_BACKUP;

DROP TABLE AUD_BACKUP PURGE;

3. Oracle bugs

Well there are couple of  known bugs, so apply latest patch.

Resources:

1.  http://juliandontcheff.wordpress.com/2011/05/12/auditing-vs-performance-in-the-oracle-database 
2. Oracle support - [ID 1375419.1, 1080112.1, 1375419.1]
3. http://www.oracle-base.com/articles/11g/auditing-enhancements-11gr2.php#purging_audit_trail_records