LATEST TOPICS

Oracle: All about recovering UNDO with no backup

Today’s post is all about recovering a database from a UNDO datafile loss (when there is no backup available). Ideally, we keep backup of our databases for PROD and PRE-PROD environments. However, most of the organizations do not maintain backup for lower environment (Dev, Test) databases as the data in those databases are not critical as well as maintaining a backup incurs additional cost for an organization.

I will discuss two scenarios related to recovering a database from a UNDO datafile loss when there is no backup available.

Scenario 1: Database was shutdown in a consistent state for maintenance. Post maintenance, somehow the UNDO datafile went missing or got corrupted. We need to recover the database and get it in a functional state.

Scenario 2: Database crashed in a inconsistent state. While starting it up, it was found that the UNDO datafile is missing or corrupted.

When database is in consistent state

Since, the database was brought down in a CONSISTENT state, we do not need UNDO or REDO data (as there is no need of Instance recovery) to open my database. I can just MOUNT the database (I can not open the database as DBWR would fail while trying to access the UNDO datafile), change the UNDO management to MANUAL (go back to old days of ROLLBACK segments in SYSTEM tablespace), drop the UNDO datafile (which is missing) while being in the MOUNT state and then open the database. Once the database is opened with MANUAL ROLLBACK segments, we can simply create a new UNDO tablespace with “CREATE UNDO TABLESPACE” command and assign it to the database and can revert back to the AUTO UNDO management.

Here, I am just simulating the loss of UNDO datafile while the database was brought down in consistent state. Lets check list of current datafiles from the database.

---//
---// list of datafiles //---
---//
sys@LABDB> select name from v$datafile;

NAME
------------------------------------------------
+DATA/LABDB/DATAFILE/system.257.860815555
/app/oracle/data/prodcdb/users1.dbf
+DATA/LABDB/DATAFILE/sysaux.256.860815491
+DATA/LABDB/DATAFILE/undotbs1.259.860815623
+DATA/LABDB/DATAFILE/users.258.860815621

Lets bring down the database in CONSISTENT state.

---//
---// bringing down database in consistent state //---
---//
sys@LABDB> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@LABDB>

Lets simulate the loss of UNDO datafile by manually deleting the UNDO datafile.

---//
---// simulating UNDO loss //---
---//
[oracle@labserver ~]$ asmcmd
ASMCMD> cd +DATA/LABDB/DATAFILE/
ASMCMD> ls
SYSAUX.256.860815491
SYSTEM.257.860815555
UNDOTBS1.259.860815623
USERS.258.860815621
ASMCMD> rm UNDOTBS1.259.860815623
ASMCMD>

Now,lets try to start the database.

---//
---// not able to locate UNDO file //---
---//
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2291472 bytes
Variable Size             276826352 bytes
Database Buffers          343932928 bytes
Redo Buffers                3276800 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DATA/LABDB/DATAFILE/undotbs1.259.860815623'

As expected, DBWR is not able to identify the UNDO datafile recorded in the CONTROL file. Lets, change the UNDO management to MANUAL from AUTO.

---//
---// change UNDO management to MANUAL //---
---//
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

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

System altered.

Now, lets try to start the database.

---//
---// not able to start database //---
---//
SQL> startup force
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2291472 bytes
Variable Size             276826352 bytes
Database Buffers          343932928 bytes
Redo Buffers                3276800 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DATA/LABDB/DATAFILE/undotbs1.259.860815623'

We are still getting the DBWR error as the UNDO datafile entry is there in the control file. Lets drop the missing UNDO datafile and try to open the database.

---//
---// drop missing UNDO file //---
---//
SQL>  alter database datafile 4 offline drop;

Database altered.

SQL> alter database open;

Database altered.

Here we are. Our database is back in Operational state. However it is now using ROLLBACK segments (rather than UNDO as we have changed UNDO management to MANUAL)

We can drop and recreate the missing UNDO tablespace or altogether can create a new UNDO tablespace and assign it to the database for AUTO UNDO management.

---//
---// recreate UNDO with AUTO management //---
---//
SQL> drop tablespace UNDOTBS1;

Tablespace dropped.

SQL> create undo tablespace UNDOTBS1;

Tablespace created.

SQL>  alter system set undo_management=AUTO scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2291472 bytes
Variable Size             276826352 bytes
Database Buffers          343932928 bytes
Redo Buffers                3276800 bytes
Database mounted.
Database opened.
SQL> show parameter UNDO

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

Our database is now completely back in the Operational state with the AUTO UNDO management.

Lets see the other recover scenario, where the database was brought down in a INCONSISTENT state.

When database is in Inconsistent state

Note: Please do not try this method without consulting Oracle Support as this method involves changing UNDOCUMENTED database parameter and has potential to cause data logical inconsistency

Lets check the list of available datafiles.

---//
---// list of datafiles //---
---//
SQL> select name from v$datafile;

NAME
---------------------------------------------
+DATA/LABDB/DATAFILE/system.257.860815555
/app/oracle/data/prodcdb/users1.dbf
+DATA/LABDB/DATAFILE/sysaux.256.860815491
+DATA/LABDB/DATAFILE/undotbs1.424.866401917
+DATA/LABDB/DATAFILE/users.258.860815621

Lets perform some DML against the database

---//
---// perform uncommitted DMLs //---
---//
SQL> insert into TEST_REDACT.CREDIT_CARD_INFO (ENROLL_DATE,CARD_NO,EXP_DATE,CARD_STR) 
2 values (sysdate,1234123412341234,sysdate+365,'1234-1234-1234-1234');

1 row created.

SQL> insert into TEST_REDACT.CREDIT_CARD_INFO (ENROLL_DATE,CARD_NO,EXP_DATE,CARD_STR)
2 values (sysdate,5678567856785678,sysdate+365,'5678-5678-5678-5678');

1 row created.

---// Note, I have not committed the changes //---

---// Lets bring down the database in a INCONSISTENT state with SHUT ABORT //---

SQL> shut abort
ORACLE instance shut down.
SQL>

Now, lets simulate the loss of UNDO datafile by manually deleting it.

---//
---// simulate loss of UNDO file //---
---//
[oracle@labserver ~]$ asmcmd
ASMCMD> cd +DATA/LABDB/DATAFILE
ASMCMD> ls
SYSAUX.256.860815491
SYSTEM.257.860815555
UNDOTBS1.424.866401917
USERS.258.860815621
ASMCMD> rm UNDOTBS1.424.866401917

Okay, Now lets try if we can open the database.

---//
---// DB startup is failing //---
---//
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2291472 bytes
Variable Size             276826352 bytes
Database Buffers          343932928 bytes
Redo Buffers                3276800 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '+DATA/LABDB/DATAFILE/undotbs1.424.866401917'

Like, the earlier case. DBWR is not able to identify the UNDO datafile.

Lets use the same approach, that we had used for the database when it was brought down in CONSISTENT state i.e. changing the UNDO management to MANUAL.

---//
---// change UNDO management to MANUAL //---
---//
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> alter system set undo_management=MANUAL scope=spfile;

System altered.

---//
---// drop missing UNDO file //---
---//
SQL> alter database datafile 4 offline drop;

Database altered.

---//
---// start database //---
---//
SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2291472 bytes
Variable Size             276826352 bytes
Database Buffers          343932928 bytes
Redo Buffers                3276800 bytes
Database mounted.
Database opened.

Hurray, its the same approach that we can use to bring back the database in Operational state for both CONSISTENT and INCONSISTENT database state.

Is it really the case? Recall the transactions (Uncommitted update) that we had performed against the database before it went down in a INCONSISTENT state. Lets see, what happens when we query from the table.

---//
---// not able to query database //---
---//
SQL> select * from TEST_REDACT.CREDIT_CARD_INFO;
select * from TEST_REDACT.CREDIT_CARD_INFO
                          *
ERROR at line 1:
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '+DATA/LABDB/DATAFILE/undotbs1.424.866401917'

What? I am not able to query the table against which I had performed the transactions. The database is looking for the missing UNDO datafile.

But why? This is because the read consistent snapshot of the data was captured in the UNDO segments while we were updating the table.

Lets see if we can get rid of that snapshot data and can view whatever is there in the current data blocks.

---//
---// active rollback segments exist //---
---//
SQL> drop tablespace UNDOTBS1;
drop tablespace UNDOTBS1
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU11_2842692639$' found, terminate dropping tablespace

No, we are not even allowed to drop the UNDO tablespace as it has the ACTIVE rollback segments. Lets see what all rollback segments we have from the missing UNDO datafile.

---//
---// list of active rollback segments //---
---//
SQL>  select segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name='UNDOTBS1';

SEGMENT_NAME                   TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ----------------
_SYSSMU11_2842692639$          UNDOTBS1                       NEEDS RECOVERY
_SYSSMU12_3631842673$          UNDOTBS1                       NEEDS RECOVERY
_SYSSMU13_543390606$           UNDOTBS1                       NEEDS RECOVERY
_SYSSMU14_1141270304$          UNDOTBS1                       NEEDS RECOVERY
_SYSSMU15_4256630628$          UNDOTBS1                       NEEDS RECOVERY
_SYSSMU16_1706077410$          UNDOTBS1                       NEEDS RECOVERY
_SYSSMU17_2943159071$          UNDOTBS1                       NEEDS RECOVERY
_SYSSMU18_4202619447$          UNDOTBS1                       NEEDS RECOVERY
_SYSSMU19_437051883$           UNDOTBS1                       NEEDS RECOVERY
_SYSSMU20_3541319746$          UNDOTBS1                       NEEDS RECOVERY

10 rows selected.

Let try to drop these missing rollback segments.

---//
---// not allowed to drop active rollback segments //---
---//
SQL> select 'drop rollback segment "'||segment_name||'";' from dba_rollback_segs where tablespace_name='UNDOTBS1';

'DROPROLLBACKSEGMENT"'||SEGMENT_NAME||'";'
-------------------------------------------------------
drop rollback segment "_SYSSMU11_2842692639$";
drop rollback segment "_SYSSMU12_3631842673$";
drop rollback segment "_SYSSMU13_543390606$";
drop rollback segment "_SYSSMU14_1141270304$";
drop rollback segment "_SYSSMU15_4256630628$";
drop rollback segment "_SYSSMU16_1706077410$";
drop rollback segment "_SYSSMU17_2943159071$";
drop rollback segment "_SYSSMU18_4202619447$";
drop rollback segment "_SYSSMU19_437051883$";
drop rollback segment "_SYSSMU20_3541319746$";

10 rows selected.

SQL> drop rollback segment "_SYSSMU11_2842692639$";
drop rollback segment "_SYSSMU11_2842692639$"
*
ERROR at line 1:
ORA-30025: DROP segment '_SYSSMU11_2842692639$' (in undo tablespace) not allowed

We are not even allowed to drop the active rollback segments.

Here is, what we need to do. We need to mark these active rollback segments from the missing UNDO datafile as OFFLINE. Once the rollback segments are marked OFFLINE, we can easily drop them off. But how to mark them OFFLINE.

This can be done through a UNDOCUMENTED parameter called _offline_rollback_segments. Assign this hidden parameter with the list of all the rollback segments (as found from the previous step) that need to be marked OFFLINE.

---//
---// mark active rollback segments as OFFLINE //---
---//

SQL> alter system set "_offline_rollback_segments"='_SYSSMU11_2842692639$','_SYSSMU12_3631842673$','_SYSSMU13_543390606$','_SYSSMU14_1141270304$','_SYSSMU15_4256630628$','_SYSSMU16_1706077410$','_SYSSMU17_2943159071$','_SYSSMU18_4202619447$','_SYSSMU19_437051883$','_SYSSMU20_3541319746$' scope=spfile;

System altered.

---//
---// restart database //---
---//
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2291472 bytes
Variable Size             276826352 bytes
Database Buffers          343932928 bytes
Redo Buffers                3276800 bytes
Database mounted.
Database opened.

Now, we can easily drop the rollback segments belonging to the missing UNDO datafile.

---//
---// drop all the marked active rollback segments //---
---//
SQL>  select 'drop rollback segment "'||segment_name||'";' from dba_rollback_segs where tablespace_name='UNDOTBS1';

'DROPROLLBACKSEGMENT"'||SEGMENT_NAME||'";'
-------------------------------------------------------
drop rollback segment "_SYSSMU11_2842692639$";
drop rollback segment "_SYSSMU12_3631842673$";
drop rollback segment "_SYSSMU13_543390606$";
drop rollback segment "_SYSSMU14_1141270304$";
drop rollback segment "_SYSSMU15_4256630628$";
drop rollback segment "_SYSSMU16_1706077410$";
drop rollback segment "_SYSSMU17_2943159071$";
drop rollback segment "_SYSSMU18_4202619447$";
drop rollback segment "_SYSSMU19_437051883$";
drop rollback segment "_SYSSMU20_3541319746$";

10 rows selected.

SQL> drop rollback segment "_SYSSMU11_2842692639$";
drop rollback segment "_SYSSMU12_3631842673$";
drop rollback segment "_SYSSMU13_543390606$";
drop rollback segment "_SYSSMU14_1141270304$";
drop rollback segment "_SYSSMU15_4256630628$";
drop rollback segment "_SYSSMU16_1706077410$";
drop rollback segment "_SYSSMU17_2943159071$";
drop rollback segment "_SYSSMU18_4202619447$";
drop rollback segment "_SYSSMU19_437051883$";
drop rollback segment "_SYSSMU20_3541319746$";

Rollback segment dropped.

SQL>
Rollback segment dropped.

SQL>
Rollback segment dropped.

SQL>
Rollback segment dropped.

SQL>
Rollback segment dropped.

SQL>
Rollback segment dropped.

SQL>
Rollback segment dropped.

SQL>
Rollback segment dropped.

SQL>
Rollback segment dropped.

SQL>
Rollback segment dropped.

SQL>

We can now drop the missing UNDO tablespace and create a new UNDO tablespace for the database AUTO UNDO management.

---//
---// recreate missing UNDO with AUTO management //---
---//
SQL> drop tablespace UNDOTBS1;

Tablespace dropped.

SQL> create undo tablespace UNDOTBS2;

Tablespace created.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL> alter system set undo_tablespace='UNDOTBS2' scope=spfile;

System altered.

SQL>  alter system set undo_management=AUTO  scope=spfile;

System altered.


---//
---// restart database //---
---//
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2291472 bytes
Variable Size             276826352 bytes
Database Buffers          343932928 bytes
Redo Buffers                3276800 bytes
Database mounted.
Database opened.
SQL>

Now, lets see what happened to our table with the missing UNDO segments.

---//
---// validate database can be queried //---
---//
SQL>  select * from TEST_REDACT.CREDIT_CARD_INFO;

   CUST_ID ENROLL_DA                        CARD_NO EXP_DATE  CARD_STR
---------- --------- ------------------------------ --------- ------------------------------
	  1021 15-DEC-14               5678567856785678 15-DEC-14 5678-5678-5678-5678
      1020 15-DEC-14               1234123412341234 15-DEC-15 1234-1234-1234-1234
      1001 08-NOV-14               1285145836589848 08-NOV-17 1285-1458-3658-9848
      1002 08-NOV-14               7844896487984154 08-NOV-17 7844-8964-8798-4154
      1003 08-NOV-14               8554884663181228 08-NOV-17 8554-8846-6318-1228
      1004 08-NOV-14               9487545796548985 08-NOV-17 9487-5457-9654-8985

We have data, that were not committed. This is because the REDO was applied, however there was no UNDO to rollback the uncommitted transactions.

The database is now back in Operational state. However, it has changes from uncommitted transactions due to missing active rollback segments.

We can now get rid of UNDOCUMENTED parameter by resetting it from the parameter file.

---//
---// reset undocumented parameter //---
---//
SQL> alter system reset "_offline_rollback_segments";

System altered.

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

Total System Global Area  626327552 bytes
Fixed Size                  2291472 bytes
Variable Size             276826352 bytes
Database Buffers          343932928 bytes
Redo Buffers                3276800 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
SQL>

Conclusion

We can always recover from the loss of UNDO datafile, even if there is no backup available. However, we might get UNWANTED (inconsistent) data in our database, depending on the transactional state of the database.

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed