Fix ORA-00600 [4194] Undo Recovery Error

Quick Answer

For experienced DBAs: Set _offline_undo = true in your init.ora, start the database, drop the corrupted undo tablespace, recreate it, then remove the parameter. This gets you back up fast without restoring from backup.

Now the longer story. I've hit ORA-00600 [4194] on more than one client system — most recently on a 12c RAC that decided to crash during a power blip. The error shows up during instance recovery, usually in the alert log, and it's Oracle's way of saying "the undo data I'm trying to read is inconsistent with what I expect." That's because the undo tablespace has a corrupted segment, often from an unclean shutdown or a disk hiccup. The database won't open, and you're staring at a brick. The good news: you usually don't need to restore anything. You just need to bypass the bad undo and rebuild it.

Here's the fix that works in 95% of cases. This is for Oracle 11g and 12c — if you're on 19c or later, the parameter still exists but you might also have the option to use ALTER DATABASE UNDO TABLESPACE ... after mounting, but let's stick with the classic method.

Step-by-Step Fix

Back up your database (if you can). Even though we're not restoring, it's cheap insurance. RMAN incremental backup if possible.
Create a pfile from your current spfile, or edit your init.ora. Add this line:
```
_offline_undo = true
```
Start the database in mount mode, then open it. The database will open but the undo tablespace will be marked offline. The alert log will show something like Undo Tablespace Offlined.
Drop the corrupted undo tablespace. First, find its name:
```
SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO';
```
Then drop it. You might need to force it:
```
DROP TABLESPACE undo_ts INCLUDING CONTENTS AND DATAFILES;
```
If that fails with ORA-30013, you'll need to use DROP TABLESPACE undo_ts INCLUDING CONTENTS AND DATAFILES KEEP DATAFILES; and then manually delete the datafile, but usually it works.
Recreate the undo tablespace. Pick a reasonable size, like 8GB or whatever matches your workload:
```
CREATE UNDO TABLESPACE undo_ts DATAFILE '+DATA' SIZE 8G AUTOEXTEND ON;
```
Remove the _offline_undo parameter from your parameter file. If you're using spfile, do this:
```
ALTER SYSTEM RESET _offline_undo SCOPE=SPFILE;
```
Restart the database cleanly. It should come up without the error.

When This Doesn't Work

Sometimes the database won't even start in mount mode with _offline_undo. If that happens, you've got deeper corruption. Try these in order:

Set _corrupted_rollback_segments = (ALL) — this tells Oracle to ignore all undo segments. It's a sledgehammer, but it can get you up. Combine it with _offline_undo = true. Once up, do the drop and recreate. Then reset both parameters.
If it still fails, you might need to restore the undo tablespace from backup. Not ideal, but sometimes the corruption is beyond what parameters can bypass.
Check for disk issues — I've seen cases where the underlying storage had bad blocks, and no parameter fix will survive a re-scan. Run dbv file=... blocksize=... on the datafile to verify.

Prevention

The main trigger for ORA-00600 [4194] is a sudden shutdown — power loss, kill -9, or a server crash. Oracle's undo is not crash-safe in the sense that it can get inconsistent if the write to disk is interrupted. To avoid this:

Always shut down gracefully using shutdown immediate or shutdown transactional. It's boring but it works.
If you're on a VM, make sure the host isn't doing snapshots without quiescing. I had a client whose backup software was taking VM snapshots mid-transaction, causing exactly this.
Monitor the alert log for warnings about undo segment corruption before they turn into a full recovery failure.
Consider using local undo tablespaces (out of RAC) to isolate corruption, but that's a separate design choice.

One last note: don't just set _offline_undo = true and leave it. That parameter causes undo to be offline, which can lead to ORA-01555 errors for long-running queries. Once you've recreated the undo tablespace, reset it. The whole process takes about 15 minutes if you know your way around. And if you're on 19c and have the DROP UNDO TABLESPACE option, the steps are slightly simpler, but this method works across versions.