Undo Recovery Archive Mode With Last Shutdown Abort

Gummula_malesh@yahoo.
com
Recovery from Undo datafile Corruption/deleted in Archive Mode with

no available backup.
It’s encouraged to restore and recover database using archive files as possible.
Without having any Backup to restore and recovery, let’s test recovery from corrupted undo datafile
using Hidden parameters (_OFFFLINE/CORRUPTED_ROLLBACK_SEGMENTS) in Archive Mode, which
should be your last option for recovery, as this would have data Inconsistency.
It is imperative that these parameters are not used without contacting Oracle first.
Contents
1) Check Archive mode of DB, follow steps for Archive Mode. .............................................................. 2
2) Check Undo segment Status for any Active transaction. ................................................................... 2
3) Run update on EMP to generate undo data in undo/rollback segments of UNDOTBS1 tablespace. 3
4) Check the active undo segments ........................................................................................................ 3
5) Simulate Instance Crash and Corrupt the UNDO datafile................................................................... 5
6) Now Start the DB to Offline the corrupted/deleted datafile and try to open the DB. ....................... 6
7) Set undo_management to manual, hidden parameter to open the Database. ................................. 7
8) Drop corrupted rollback Segments along with undo tablespace. .................................................... 10
9) Unset Hidden parameter and Re-create undo tablespace and restart the DB. ............................... 11
10) Re-check if the Parameters were unset.......................................................................................... 12
11) Verify the user data and database.................................................................................................. 13
12) Reference ........................................................................................................................................ 14
Page 1 of 15
Gummula_malesh@yahoo.com
1) Check Archive mode of DB, follow steps for Archive Mode.

SQL> select NAME,OPEN_MODE,LOG_MODE from v$database;
NAME OPEN_MODE LOG_MODE

--------- -------------------- ------------
D041 READ WRITE ARCHIVELOG
SQL> archive log list

Database log mode Archive Mode
Automatic archival Disabled
Archive destination C:\Oracle\oradata\d041\arch
Oldest online log sequence 22
Next log sequence to archive 24
Current log sequence 24
SQL>
2) Check Undo segment Status for any Active transaction.

SQL> select SEGMENT_NAME,SEGMENT_ID,status,tablespace_name from dba_rollback_segs ;
SEGMENT_NAME SEGMENT_ID STATUS TABLESPACE_NAME

------------------------------ ---------- ---------------- -----------------
SYSTEM 0 ONLINE SYSTEM
_SYSSMU10_3176102001$ 10 ONLINE UNDOTBS1
11 rows selected.
SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS

FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = 'UNDOTBS1' AND SEGMENT_ID = USN;
SEGMENT_NAME ACTIVE_TX STATUS

------------------------------ ---------- ---------------
_SYSSMU1_1518548437$ 0 ONLINE
…
…
10 rows selected.
Note: 0 indicates No Active transaction
Page 2 of 15
3) Run update on EMP to generate undo data in undo/rollback

segments of UNDOTBS1 tablespace.
User session1: (Note: Data is not committed)
SQL> Update emp set ENAME='ALLEN_Ses1' where ename='ALLEN';
1 row updated.
DBA session2:
Alter system switch logfile;

User session3: (Note: Data is not committed)
SQL> Update emp set ENAME='JAMES_Ses2' where ename='JAMES';
1 row updated.
DBA session2:

4) Check the active undo segments

set pages 200
col user0 form a15
col comm0 form a15
col name0 form a10
col extents0 form 99999 Heading "Extents"
col shrinks0 form 99999 Heading "Shrinks"
col waits form 999999 heading "Wraps"
col name format a30
col SEGMENT_NAME for a25
col TABLESPACE_NAME for a14
col descp for a80
col sid_serial for a15
col undoseg for a30
col PROGRAM for a25
set line 300
col NAME0 for a30
col undo for a10
col ORAUSER for a15
Page 3 of 15
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,

NVL(s.username, 'None') orauser,
s.program, r.USN,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';
SID_SERIAL ORAUSER PROGRAM USN UNDOSEG Undo
--------------- --------------- ------------------------- ---------- ------------------------------ ----
195,5 SCOTT sqlplus.exe 8 _SYSSMU8_1557854099$ 8K
68,7 SCOTT sqlplus.exe 3 _SYSSMU3_991555123$ 8K
OR
REM Check for active transactions with the following query

REM ===========================================================
SQL> show parameter undo_tablespace
NAME TYPE VALUE

------------------------------------ -------------------------------- ---------------
undo_tablespace string UNDOTBS1
SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS


------------------------- ---------- ---------------
10 rows selected.
10 rows selected.
Note : 1 indicates Active transaction
Page 4 of 15
SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"

FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R
WHERE R.NAME IN ('_SYSSMU3_991555123$',’_SYSSMU8_1557854099$’)
AND S.TADDR = T.ADDR AND T.XIDUSN = R.USN;
SID SERIAL# USERNAME ROLLBACK

----- ---------- ------------------------------ ---------------------
68 7 SCOTT _SYSSMU3_991555123$
195 5 SCOTT _SYSSMU8_1557854099$
5) Simulate Instance Crash and Corrupt the UNDO datafile
SQL> shut abort

ORACLE instance shut down.
Checking the undo file for corruption:

================================
C:\Oracle\oradata\d041>dbv FILE=UNDOTBS01.DBF
DBVERIFY: Release 11.2.0.1.0 - Production on Wed Jan 1 18:30:54 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF
DBVERIFY - Verification complete
Total Pages Examined : 7040
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 6734
Total Pages Processed (Seg) : 10
Total Pages Failing (Seg) : 0
Total Pages Empty : 306
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1078225 (0.1078225)
Now Corrupt Undo file
================================
Manually adding data to undo file to the end of file to simulate corruption.
C:\Oracle\oradata\d041>echo A >> UNDOTBS01.DBF
DBVERIFY: Release 11.2.0.1.0 - Production on Wed Jan 1 18:31:24 2014

DBV-00600: Fatal Error - [21] [5] [0] [0]
OR
If you Edit using the Edit plus , and delete some content from middle of file.
DBVERIFY: Release 11.2.0.1.0 - Production on Tue Dec 31 14:42:39 2013
DBV-00102: File I/O error on FILE (C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF) during end read operation (7680)
Page 5 of 15
6) Now Start the DB to Offline the corrupted/deleted datafile

and try to open the DB.
SQL> startup
ORACLE instance started.
Total System Global Area 368263168 bytes

Fixed Size 1374668 bytes
Variable Size 209716788 bytes
Database Buffers 150994944 bytes
Redo Buffers 6176768 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF'
Whenever DB shuts cleanly, CHECKPOINT_CHANGE# and LAST_CHANGE# must always be sync else the
shutdown was not clean and they need to do crash/media recovery, as below
SQL> set line 200

col LAST_TIME for a25
col name for a45
select NAME,STATUS,CHECKPOINT_CHANGE#,LAST_CHANGE#,to_char(LAST_TIME,'DD-MON-YYYY HH24:MI:SS')
LAST_TIME from v$datafile;
NAME STATUS CHECKPOINT_CHANGE# LAST_CHANGE# LAST_TIME

--------------------------------------------- ------- ------------------ ------------ -----------
C:\ORACLE\ORADATA\D041\SYSTEM01.DBF SYSTEM 1078239
C:\ORACLE\ORADATA\D041\SYSAUX01.DBF ONLINE 1078239
C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF ONLINE 1078239
C:\ORACLE\ORADATA\D041\USERS01.DBF ONLINE 1078239
C:\ORACLE\ORADATA\D041\EXAMPLE01.DBF ONLINE 1078239
SQL> col error for a20

select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

------ ------- ------- -------------------- ---------- ---------
1 ONLINE ONLINE UNKNOWN ERROR 1078239 31-DEC-13
2 ONLINE ONLINE UNKNOWN ERROR 1078239 31-DEC-13
3 ONLINE ONLINE FILE NOT FOUND 0
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
---------- --------------------
D041 MOUNTED
SQL> alter database datafile 3 offline;
Database altered.
To use this form of the ALTER DATABASE statement, the database must be in ARCHIVELOG mode.
This requirement prevents you from accidentally losing the datafile, since taking the datafile
offline while in NOARCHIVELOG mode is likely to result in losing the file.
Page 6 of 15
SQL> select NAME,STATUS,CHECKPOINT_CHANGE#,LAST_CHANGE#,to_char(LAST_TIME,'DD-MON-YYYY HH24:MI:SS')


--------------------------------------------- ------- ------------------ ------------ ---------------------
C:\ORACLE\ORADATA\D041\SYSTEM01.DBF SYSTEM 1078239
C:\ORACLE\ORADATA\D041\SYSAUX01.DBF ONLINE 1078239
C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF RECOVER 1078239
C:\ORACLE\ORADATA\D041\USERS01.DBF ONLINE 1078239
C:\ORACLE\ORADATA\D041\EXAMPLE01.DBF ONLINE 1078239
SQL> select * from v$recover_file;

---------- ------- ------- -------------------- ---------- ---------
3 OFFLINE OFFLINE FILE NOT FOUND 0
SQL> recover database;

Media recovery complete.
SQL> select NAME,STATUS,CHECKPOINT_CHANGE#,LAST_CHANGE#,to_char(LAST_TIME,'DD-MON-YYYY HH24:MI:SS')


--------------------------------------------- ------- ------------------ ------------ ---------------------
C:\ORACLE\ORADATA\D041\SYSTEM01.DBF SYSTEM 1098474 1098474 31-DEC-2013 14:30:09
C:\ORACLE\ORADATA\D041\SYSAUX01.DBF ONLINE 1098474 1098474 31-DEC-2013 14:30:09
C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF RECOVER 1078239
C:\ORACLE\ORADATA\D041\USERS01.DBF ONLINE 1098474 1098474 31-DEC-2013 14:30:09
C:\ORACLE\ORADATA\D041\EXAMPLE01.DBF ONLINE 1098474 1098474 31-DEC-2013 14:30:09
SQL> select * from v$recover_file;

---------- ------- ------- -------------------- ---------- ---------
3 OFFLINE OFFLINE FILE NOT FOUND 0
SQL> alter database open;

alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 3 cannot be read at this time
Process ID: 972
Session ID: 5 Serial number: 3
7) Set undo_management to manual, hidden parameter to open

the Database.
SQL> startup mount;

Database mounted.
Page 7 of 15
SQL> alter system set undo_management='MANUAL' scope=spfile;
System altered.
SQL> alter system set rollback_segments=SYSTEM scope=spfile;
System altered.
Trace the error ORA-01092 by setting event to find the Rollback

Segment names:
SQL> alter session set tracefile_identifier = 'Trace_1092_undo_seg';
Session altered.
SQL> alter system set events '1092 trace name errorstack level 3';
System altered.
SQL> SET LINESIZE 100

COLUMN trace_file FORMAT A60
SELECT s.sid,
s.serial#,
pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
SID SERIAL# TRACE_FILE

---------- ---------- ----------------------------------------------------------
191 1 c:\oracle\diag\rdbms\d041\d041\trace\d041_ora_3296.trc
SQL> alter database open; this would generate the trace file having Rollback.
alter database open
*
ERROR at line 1:
Process ID: 3296
cd c:\Oracle\diag\rdbms\d041\d041\trace
c:\Oracle\diag\rdbms\d041\d041\trace>type d041_ora_3296_Trace_1092_undo_seg.trc |FIND /N "SYSSMU"
[60522]2013-12-31 20:53:24.080870 :80002CFD:db_trace:ktu.c@14066:ktutrc():

[10444:19:191] Rec rbs _SYSSMU1_1518548437$
[60524]2013-12-31 20:53:24.129161 :80002D01:db_trace:ktu.c@14066:ktutrc():
[10444:19:191] Rec rbs _SYSSMU2_2082490410$
[10444:19:191] Rec rbs _SYSSMU3_991555123$
[60527]2013-12-31 20:53:24.129874 :80002D0A:db_trace:ktu.c@14066:ktutrc():
[10444:19:191] Rec rbs _SYSSMU4_2369290268$
Page 8 of 15
[60529]2013-12-31 20:53:24.130198 :80002D0C:db_trace:ktu.c@14066:ktutrc():

[10444:19:191] Rec rbs _SYSSMU5_1018230376$
[10444:19:191] Rec rbs _SYSSMU6_1834113595$
[10444:19:191] Rec rbs _SYSSMU7_137577888$
[10444:19:191] Rec rbs _SYSSMU8_1557854099$
[10444:19:191] Rec rbs _SYSSMU9_1126410412$
[60539]2013-12-31 20:53:24.132186 :80002D2A:db_trace:ktu.c@14066:ktutrc():
[10444:19:191] Rec rbs _SYSSMU10_3176102001$
_SYSSMU1_1518548437$
_SYSSMU2_2082490410$
_SYSSMU3_991555123$
_SYSSMU4_2369290268$
_SYSSMU5_1018230376$
_SYSSMU6_1834113595$
_SYSSMU7_137577888$
_SYSSMU8_1557854099$
_SYSSMU9_1126410412$
_SYSSMU10_3176102001$
SQL> Alter System set "_OFFLINE_ROLLBACK_SEGMENTS"=

"_SYSSMU1_1518548437$",
"_SYSSMU2_2082490410$",
"_SYSSMU3_991555123$",
"_SYSSMU4_2369290268$",
"_SYSSMU5_1018230376$",
"_SYSSMU6_1834113595$",
"_SYSSMU7_137577888$",
"_SYSSMU8_1557854099$",
"_SYSSMU9_1126410412$",
"_SYSSMU10_3176102001$"
scope=spfile;
System altered.
SQL> Alter System set "_CORRUPTED_ROLLBACK_SEGMENTS"=

"_SYSSMU1_1518548437$",
"_SYSSMU2_2082490410$",
"_SYSSMU3_991555123$",
"_SYSSMU4_2369290268$",
"_SYSSMU5_1018230376$",
"_SYSSMU6_1834113595$",
"_SYSSMU7_137577888$",
"_SYSSMU8_1557854099$",
"_SYSSMU9_1126410412$",
"_SYSSMU10_3176102001$"
scope=spfile;
System altered.
Page 9 of 15
SQL> startup force mount;


Database mounted.
SQL> set pages 100

set line 200
show parameter rollback_segments
NAME TYPE VALUE

------------------------------------ -------------------------------- ------------------------------
_corrupted_rollback_segments string _SYSSMU1_1518548437$, _SYSSMU2
_2082490410$, _SYSSMU3_9915551
23$, _SYSSMU4_2369290268$, _SY
SSMU5_1018230376$, _SYSSMU6_18
34113595$, _SYSSMU7_137577888$
, _SYSSMU8_1557854099$, _SYSSM
U9_1126410412$, _SYSSMU10_3176
102001$
_offline_rollback_segments string _SYSSMU1_1518548437$, _SYSSMU2
_2082490410$, _SYSSMU3_9915551
23$, _SYSSMU4_2369290268$, _SY
SSMU5_1018230376$, _SYSSMU6_18
34113595$, _SYSSMU7_137577888$
, _SYSSMU8_1557854099$, _SYSSM
U9_1126410412$, _SYSSMU10_3176
102001$
rollback_segments string SYSTEM
SQL> alter database open;
Database altered.
8) Drop corrupted rollback Segments along with undo

tablespace.
SQL> select 'drop rollback segment "'||segment_name||'";'
from dba_rollback_segs
where tablespace_name = 'UNDOTBS1';
'DROPROLLBACKSEGMENT'||SEGMENT_NAME||';'
-----------------------------------------------------------------------
drop rollback segment "_SYSSMU1_1518548437$";
10 rows selected.
Page 10 of 15
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
9) Unset Hidden parameter and Re-create undo tablespace and

restart the DB.
Unset all the Parameter’s which we set as part of this recovery.
SQL> Alter System reset "_OFFLINE_ROLLBACK_SEGMENTS";
System altered.
SQL> Alter System reset "_CORRUPTED_ROLLBACK_SEGMENTS";
System altered.
SQL> Alter System set undo_management=AUTO scope=spfile;
System altered.
SQL> Alter System Reset rollback_segments;
System altered.
SQL> show parameter undo
NAME TYPE VALUE

------------------- ---------- -------------
undo_management string MANUAL
undo_retention integer 900
SQL> create undo tablespace UNDOTBS1 datafile

'C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF' size 20M reuse;
Tablespace created.
SQL> shut immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

Database mounted.
Database opened.
Page 11 of 15
10) Re-check if the Parameters were unset.

SQL> show parameter rollback_segments
NAME TYPE VALUE

------------------------------------ -------------------------------- ------------
rollback_segments string
SQL> show parameter undo
NAME TYPE VALUE

------------------------------------ -------------------------------- ------------
undo_management string AUTO
undo_retention integer 900
SQL>
SQL> select SEGMENT_NAME,SEGMENT_ID,status,tablespace_name from dba_rollback_segs ;
SEGMENT_NAME SEGMENT_ID STATUS TABLESPACE_NAME

------------------------------ ---------- ---------------- -----------------
SYSTEM 0 ONLINE SYSTEM
11 rows selected.
SQL> SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS


------------------------------ ---------- ---------
10 rows selected.
Page 12 of 15
11) Verify the user data and database.

SQL> conn scott/tiger
Connected.
SQL> set line 200

select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN_Ses1 SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES_Ses2 CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> col name for a45

select name,status from v$datafile;
NAME STATUS
--------------------------------------------- -------
C:\ORACLE\ORADATA\D041\SYSTEM01.DBF SYSTEM
C:\ORACLE\ORADATA\D041\SYSAUX01.DBF ONLINE
C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF ONLINE
C:\ORACLE\ORADATA\D041\USERS01.DBF ONLINE
C:\ORACLE\ORADATA\D041\EXAMPLE01.DBF ONLINE
We see the Data inconsistency as above highlighted, though the above

data was not committed by user.
The data got committed due to the usage of Hidden parameter

_OFFLINE_ROLLBACK_SEGMENTS & _CORRUPTED_ROLLBACK_SEGMENTS
Page 13 of 15
12) Reference
When to use unsupported parameters _corrupted_rollback_segments

and others. (Doc ID 1360786.1)
There are many documents available on the internet and other electronic source that
show the use of unsupported parameters to open a database.
Two of these parameters are

_OFFLINE_ROLLBACK_SEGMENTS and _CORRUPTED_ROLLBACK_SEGMENTS
What are _OFFLINE_ROLLBACK_SEGMENTS and _CORRUPTED_ROLLBACK_SEGMENTS?
_OFFLINE_ROLLBACK_SEGMENTS is an unsupported init.ora parameter which can allow you

to cause logical database corruption.
_CORRUPTED_ROLLBACK_SEGMENTS is more dangerous parameter than

_OFFLINE_ROLLBACK_SEGMENTS. It basically prevents access to the listed rollback
segments headers and assumes all transactions in them are committed. This can very
easily cause logical database corruption.
It is imperative that these parameters are not used without contacting Oracle
first.
Oracle Premier Support - Oracle Database Support News - Issue

October, 2013 Volume 33 (Doc ID 1600806.1)
#######################################################
SQL> startup

Database mounted.
Process ID: 8168
Alert.log:
================
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
Errors in file c:\oracle\diag\rdbms\d041\d041\trace\d041_smon_7296.trc:
Page 14 of 15
Trace file(c:\oracle\diag\rdbms\d041\d041\trace\d041_smon_7296.trc)
=======================================================================
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
Page 15 of 15

Undo Recovery Archive Mode With Last Shutdown Abort

Uploaded by

Copyright:

Available Formats

Undo Recovery Archive Mode With Last Shutdown Abort

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Undo Recovery Archive Mode With Last Shutdown Abort

Uploaded by

Copyright:

Available Formats

Gummula_malesh@yahoo.

Recovery from Undo datafile Corruption/deleted in Archive Mode with

1) Check Archive mode of DB, follow steps for Archive Mode.

NAME OPEN_MODE LOG_MODE

SQL> archive log list

2) Check Undo segment Status for any Active transaction.

SEGMENT_NAME SEGMENT_ID STATUS TABLESPACE_NAME

SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS

SEGMENT_NAME ACTIVE_TX STATUS

3) Run update on EMP to generate undo data in undo/rollback

SQL> Update emp set ENAME='ALLEN_Ses1' where ename='ALLEN';

Alter system switch logfile;

User session3: (Note: Data is not committed)

SQL> Update emp set ENAME='JAMES_Ses2' where ename='JAMES';

Alter system switch logfile;

4) Check the active undo segments

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,

REM Check for active transactions with the following query

NAME TYPE VALUE

SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS

SEGMENT_NAME ACTIVE_TX STATUS

Note : 1 indicates Active transaction

SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME "ROLLBACK"

SID SERIAL# USERNAME ROLLBACK

5) Simulate Instance Crash and Corrupt the UNDO datafile

SQL> shut abort

Checking the undo file for corruption:

DBVERIFY: Release 11.2.0.1.0 - Production on Wed Jan 1 18:30:54 2014

C:\Oracle\oradata\d041>echo A >> UNDOTBS01.DBF

DBVERIFY: Release 11.2.0.1.0 - Production on Wed Jan 1 18:31:24 2014

DBV-00600: Fatal Error - [21] [5] [0] [0]

6) Now Start the DB to Offline the corrupted/deleted datafile

Total System Global Area 368263168 bytes

SQL> set line 200

NAME STATUS CHECKPOINT_CHANGE# LAST_CHANGE# LAST_TIME

SQL> col error for a20

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

SQL> select name,open_mode from v$database;

SQL> alter database datafile 3 offline;

SQL> select NAME,STATUS,CHECKPOINT_CHANGE#,LAST_CHANGE#,to_char(LAST_TIME,'DD-MON-YYYY HH24:MI:SS')

NAME STATUS CHECKPOINT_CHANGE# LAST_CHANGE# LAST_TIME

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

SQL> recover database;

SQL> select NAME,STATUS,CHECKPOINT_CHANGE#,LAST_CHANGE#,to_char(LAST_TIME,'DD-MON-YYYY HH24:MI:SS')

NAME STATUS CHECKPOINT_CHANGE# LAST_CHANGE# LAST_TIME

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

SQL> alter database open;

7) Set undo_management to manual, hidden parameter to open

Total System Global Area 368263168 bytes

SQL> alter system set undo_management='MANUAL' scope=spfile;

Trace the error ORA-01092 by setting event to find the Rollback

SQL> alter session set tracefile_identifier = 'Trace_1092_undo_seg';

SQL> SET LINESIZE 100

SID SERIAL# TRACE_FILE

[60522]2013-12-31 20:53:24.080870 :80002CFD:db_trace:ktu.c@14066:ktutrc():

[60529]2013-12-31 20:53:24.130198 :80002D0C:db_trace:ktu.c@14066:ktutrc():

SQL> Alter System set "_OFFLINE_ROLLBACK_SEGMENTS"=