Sunday, August 28, 2016

ORA-00600: internal error code, arguments: [4194] (Undo corruptions)

Recovery of Online Redo Log: Thread 1 Group 3 Seq 418 Reading mem 0
  Mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\BOND\REDO03.LOG
Block recovery completed at rba 418.70.16, scn 0.13842291
Sun Aug 28 15:36:51 2016
Errors in file d:\oracle\product\10.2.0\admin\bond\bdump\bond_smon_4584.trc:
ORA-01595: error freeing extent (3) of rollback segment (5))
ORA-00607: Internal error occurred while making a change to a data block
ORA-00600: internal error code, arguments: [4194], [93], [5], [], [], [], [], []

Cause : Undo Corruption.

Solution : Create a new undo tablespace and set it default undo tablespace (undo_tablespace).


1. Create pfile from spfile to edit
 >create pfile from spfile;

2. Shutdown the instance
>shutdown immediate;

3. Set the following parameters in the pfile
  undo_management = 'manual'
  event = '10513 trace name context forever, level 2'

4. Srart database in restrict mode.
 >startup restrict pfile=<initsid.ora>

5. Checking the status of segment.

 >select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

This is critical - we are looking for all undo segments to be offline - System will always be online.

6. Create new undo tablespace
 >create undo tablespace <new undo tablespace> datafile <datafile> size 1000M;

7. Shutdown the database.

 >shutdown immediate;

8. Modify the following parameters in the pfile

*.undo_management='AUTO'
*.undo_tablespace='<new undo tablespace>'

9. Srart the database using the pfile.

 >startup pfile=<initsid.ora>;

10. Create the spfile using pfile.

 > create spfile from pfile;

11. Shutdown the database.

>shutdown immediate;

12. Startup database.

>startup;  --> Using new spfile
 

Log

 
SQL> create pfile from spfile;

File created.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup restrict pfile=D:\ORACLE\PRODUCT\10.2.0\db_1\database\INITbond.ora;

ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             587204860 bytes
Database Buffers           16777216 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL>
SQL> select tablespace_name, status, segment_name from dba_rollback_segs where status != 'OFFLINE';

TABLESPACE_NAME                STATUS           SEGMENT_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM

SQL> create undo tablespace UNDOTBS2 datafile 'D:\oracle\product\10.2.0\oradata\bond\UNDOTBS02.DBF' size 2000M;

Tablespace created.

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile=D:\ORACLE\PRODUCT\10.2.0\db_1\database\INITbond.ora;
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             587204860 bytes
Database Buffers           16777216 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> create spfile from pfile;

File created.

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

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             587204860 bytes
Database Buffers           16777216 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.
SQL>

No comments:

Post a Comment