How to recover and open the database if the archive log required for recovery is missing.

ORA-00600: internal error code, arguments: [4194], [17], [9], [], [], [], [], []
Tue OCT 16 12:45:55 2008
Errors in file /u01/XSCLFYDB/admin/XSCLFY/bdump/xsclfy_smon_24975.trc:
ORA-00600: internal error code, arguments: [4193], [53085], [50433], [], [], [], [], []
Doing block recovery for file 433 block 13525
Block recovery from logseq 2, block 31 to scn 9867098416340
——————————————————————————————

To resolve undo corruption issue, change undo_management to “Manual” in init.ora. Now it allowed to open database successfully.

Once database was up and running, create new undo tablespace and dropped old corrupted undo tablespace

and change back the undo_management to “Auto” and undo_tablespace to “NewUndoTablespace”.

1. Create Pfile

2. Change undomanagement auto to manual

UNDO_MANAGEMENT = MANUAL

3. Up the db with Pfile

Note: due to undo segment corruption we need to create a new undo file

5. create New undofile with same specifcation of old one.

CREATE UNDO TABLESPACE undotbs_02
DATAFILE ‘/u01/oracle/rbdb1/undo0201.dbf’ SIZE 2M;

6. Change undomanagement to manual to auto.

ALTER SYSTEM SET UNDO_MANAGEMENT=AUTO SCOPE=BOTH;

Note: If it not works,change the undo_management parameter in pfile.

UNDO_MANAGEMENT = AUTO

7. Switch the new unod tablespace with new one.

ALTER SYSTEM SET UNDO_TABLESPACE = ”;
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;

8. Create SPfile from pfile.

9. Up the databse.

——————————————
Monitoring:
———-

Undo information can be queried using the following views.

V$UNDOSTAT
V$ROLLSTAT
V$TRANSACTION
DBA_UNDO_EXTENTS

— Display the rollback segments

select segment_name
, status
from dba_rollback_segs
/

— Is anything rolling back at the moment?

Look for the used_ublk value decreasing. If it is, the session connected with it is rolling back.
When it reaches zero, rollback is complete.

set lines 100 pages 999
col username format a15
col command format a20
select ses.username
, substr(ses.program, 1, 19) command
, tra.used_ublk
from v$session ses
, v$transaction tra
where ses.saddr = tra.ses_addr
/

—————————————
Maintenance:
————

— Add a datafile.
ALTER TABLESPACE undotbs_01
ADD DATAFILE ‘C:\Oracle\Ordata\TSH1\undo0102.dbf’
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED;

— Resize an undo datafile.
ALTER DATABASE DATAFILE ‘C:\Oracle\Ordata\TSH1\undo0102.dbf’
RESIZE 10M;

— Perform backup operations
ALTER TABLESPACE undotbs_01 BEGIN BACKUP;
ALTER TABLESPACE undotbs_01 END BACKUP;

— Drop an undo tablespace.
DROP TABLESPACE undotbs_01;