ORA – 476 on windows

Successfully onlined Undo Tablespace 1.

Wed Jul 15 09:07:50 2009

SMON: enabling tx recovery

Wed Jul 15 09:07:50 2009

replication_dependency_tracking turned off (no async multimaster replication found)

Completed: ALTER DATABASE OPEN

Wed Jul 15 09:16:43 2009

Errors in file i:\oracle\admin\prepaid\bdump\prepaidPMON.TRC:

ORA-00476: RECO process terminated with error

 

Wed Jul 15 09:16:43 2009

PMON: terminating instance due to error 476

Instance terminated by PMON, pid = 6700

 

Solution

Windows 2003 server service pack 1

open 

Services.msc

 

Stop the oracle service and start

 

 

SQL> ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

 

System altered.

SQL>

 

SQL> Set pagesize 1000
SQL> select * from DBA_2PC_PENDING;

LOCAL_TRAN_ID

----------------------
GLOBAL_TRAN_ID
--------------------------------------------------------------------------------
STATE MIX A
---------------- --- -
TRAN_COMMENT
--------------------------------------------------------------------------------
FAIL_TIME FORCE_TIM RETRY_TIM
--------- --------- ---------
OS_USER
----------------------------------------------------------------
OS_TERMINAL
--------------------------------------------------------------------------------
HOST
--------------------------------------------------------------------------------
DB_USER COMMIT#
------------------------------ ----------------
3.13.33045
PREPAID.US.ORACLE.COM.c75b4cab.3.13.33045
collecting no

14-JUL-09 14-JUL-09
IN-PCSOFFLINE\Administrator
IN-PCSOFFLINE
WORK GROUP\IN-PCSOFFLINE
PCS2008 7809889209382

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('3.13.33045');

BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('3.13.33045'); END;

*

ERROR at line 1:

ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

ORA-06512: at "SYS.DBMS_TRANSACTION", line 65

ORA-06512: at "SYS.DBMS_TRANSACTION", line 85

ORA-06512: at line 1

 

 

 

** NOTE1: If using Oracle 9i or later and DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY fails with

   ORA-30019: Illegal rollback Segment operation in Automatic Undo mode, use the

   following workaround

      SQL> alter session set "_smu_debug_mode" = 4;

      SQL> commit;      <-- added="" be="" must="" o:p="" ora-1453="" prevent="" to="">

      SQL>execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');

SQL> alter session set "_smu_debug_mode" = 4;

 

Session altered.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('3.13.33045');

 

PL/SQL procedure successfully completed.

 

SQL> select * from DBA_2PC_PENDING;

 

no rows selected

 

SQL> select * from DBA_2PC_NEIGHBORS;

 

no rows selected

 

SQL> ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

 

System altered.