Skip to content

Recovery from Undo datafile Corruption or deleted in Archive Mode with no available backup.

January 1, 2014

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.

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
_SYSSMU9_1126410412$                    9 ONLINE           UNDOTBS1
_SYSSMU8_1557854099$                    8 ONLINE           UNDOTBS1
_SYSSMU7_137577888$                     7 ONLINE           UNDOTBS1
_SYSSMU6_1834113595$                    6 ONLINE           UNDOTBS1
_SYSSMU5_1018230376$                    5 ONLINE           UNDOTBS1
_SYSSMU4_2369290268$                    4 ONLINE           UNDOTBS1
_SYSSMU3_991555123$                     3 ONLINE           UNDOTBS1
_SYSSMU2_2082490410$                    2 ONLINE           UNDOTBS1
_SYSSMU1_1518548437$                    1 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
_SYSSMU2_2082490410$                    0 ONLINE
_SYSSMU3_991555123$                     0 ONLINE
…
…
_SYSSMU8_1557854099$                    0 ONLINE
_SYSSMU9_1126410412$                    0 ONLINE
_SYSSMU10_3176102001$                   0 ONLINE
10 rows selected.

Note: 0 indicates No Active transaction

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;

Alter system switch logfile;

Alter system switch logfile;

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:

Alter system switch logfile;

Alter system switch logfile;

Alter system switch logfile;

Alter system switch logfile;

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
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
FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS
WHERE TABLESPACE_NAME = ‘UNDOTBS1‘ AND SEGMENT_ID = USN;
SEGMENT_NAME               ACTIVE_TX STATUS
------------------------- ---------- ---------------
_SYSSMU1_1518548437$               0 ONLINE
_SYSSMU2_2082490410$               0 ONLINE
_SYSSMU3_991555123$                1 ONLINE
_SYSSMU4_2369290268$               0 ONLINE
_SYSSMU5_1018230376$               0 ONLINE
_SYSSMU6_1834113595$               0 ONLINE
_SYSSMU7_137577888$                0 ONLINE
_SYSSMU8_1557854099$               1 ONLINE
_SYSSMU9_1126410412$               0 ONLINE
_SYSSMU10_3176102001$              0 ONLINE
10 rows selected.

Note : 1 indicates Active transaction

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

C:\Oracle\oradata\d041>dbv FILE=UNDOTBS01.DBF
DBVERIFY: Release 11.2.0.1.0 – Production on Wed Jan 1 18:31:24 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBV-00600: Fatal Error – [21] [5] [0] [0]

OR

If you Edit using the Edit plus , and delete some content from middle of file.

C:\Oracle\oradata\d041>dbv FILE=UNDOTBS01.DBF
DBVERIFY: Release 11.2.0.1.0 – Production on Tue Dec 31 14:42:39 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBV-00102: File I/O error on FILE (C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF) during end read operation (7680)
 

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.

SQL> 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          RECOVER            1078239
C:\ORACLE\ORADATA\D041\USERS01.DBF            ONLINE             1078239
C:\ORACLE\ORADATA\D041\EXAMPLE01.DBF          ONLINE             1078239
SQL> select * from v$recover_file;
FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME
---------- ------- ------- -------------------- ---------- ---------
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’) LAST_TIME from  v$datafile;

NAME                                          STATUS  CHECKPOINT_CHANGE# LAST_CHANGE# LAST_TIME
--------------------------------------------- ------- ------------------ ------------ ---------------------
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;
FILE# ONLINE  ONLINE_ ERROR                   CHANGE# TIME
---------- ------- ------- -------------------- ---------- ---------
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
ORA-01110: data file 3: ‘C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF’
Process ID: 972
Session ID: 5 Serial number: 3
 

7) Set undo_management to manual, hidden parameter to open the Database.

SQL> startup mount;
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.

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:
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
ORA-01110: data file 3: ‘C:\ORACLE\ORADATA\D041\UNDOTBS01.DBF’
Process ID: 3296
Session ID: 191 Serial number: 1

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$
[60526]2013-12-31 20:53:24.129521 :80002D03:db_trace:ktu.c@14066:ktutrc(): [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$
[60529]2013-12-31 20:53:24.130198 :80002D0C:db_trace:ktu.c@14066:ktutrc(): [10444:19:191] Rec rbs _SYSSMU5_1018230376$
[60533]2013-12-31 20:53:24.130520 :80002D18:db_trace:ktu.c@14066:ktutrc(): [10444:19:191] Rec rbs _SYSSMU6_1834113595$
[60534]2013-12-31 20:53:24.130886 :80002D19:db_trace:ktu.c@14066:ktutrc(): [10444:19:191] Rec rbs _SYSSMU7_137577888$
[60536]2013-12-31 20:53:24.131252 :80002D21:db_trace:ktu.c@14066:ktutrc(): [10444:19:191] Rec rbs _SYSSMU8_1557854099$
[60537]2013-12-31 20:53:24.131755 :80002D28:db_trace:ktu.c@14066:ktutrc(): [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.
SQL> startup force mount;
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.
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$";
drop rollback segment "_SYSSMU2_2082490410$";
drop rollback segment "_SYSSMU3_991555123$";
drop rollback segment "_SYSSMU4_2369290268$";
drop rollback segment "_SYSSMU5_1018230376$";
drop rollback segment "_SYSSMU6_1834113595$";
drop rollback segment "_SYSSMU7_137577888$";
drop rollback segment "_SYSSMU8_1557854099$";
drop rollback segment "_SYSSMU9_1126410412$";
drop rollback segment "_SYSSMU10_3176102001$";
10 rows selected.

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
undo_tablespace     string     UNDOTBS1

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
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.
Database opened.

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
undo_tablespace                      string                           UNDOTBS1
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
_SYSSMU30_1810713337$                  30 ONLINE           UNDOTBS1
_SYSSMU29_1022259346$                  29 ONLINE           UNDOTBS1
_SYSSMU28_1625692062$                  28 ONLINE           UNDOTBS1
_SYSSMU27_3471436577$                  27 ONLINE           UNDOTBS1
_SYSSMU26_817196595$                   26 ONLINE           UNDOTBS1
_SYSSMU25_1627785437$                  25 ONLINE           UNDOTBS1
_SYSSMU24_104039466$                   24 ONLINE           UNDOTBS1
_SYSSMU23_2220002037$                  23 ONLINE           UNDOTBS1
_SYSSMU22_2141280352$                  22 ONLINE           UNDOTBS1
_SYSSMU21_2921185900$                  21 ONLINE           UNDOTBS1
11 rows selected.

SQL> 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
------------------------------ ---------- ---------
_SYSSMU21_2921185900$                   0 ONLINE
_SYSSMU22_2141280352$                   0 ONLINE
_SYSSMU23_2220002037$                   0 ONLINE
_SYSSMU24_104039466$                    0 ONLINE
_SYSSMU25_1627785437$                   0 ONLINE
_SYSSMU26_817196595$                    0 ONLINE
_SYSSMU27_3471436577$                   0 ONLINE
_SYSSMU28_1625692062$                   0 ONLINE
_SYSSMU29_1022259346$                   0 ONLINE
_SYSSMU30_1810713337$                   0 ONLINE
10 rows selected.

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

 

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)

##########################################################################

Click Here for the PDF

Advertisements
2 Comments
  1. Julian permalink

    THANK YOU very much!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: