Recover from loss of undo tablespace datafile

by Snehashish Ghosh

Scenario


 Undo tablespace datafile has been corrupted or had been deleted by sysadmin.

Database was properly shutdown i.e. no active rollback segment exists

Location of Undo Tablespace

undo1

 

 

 

 

 

 

 Undo Tablespace is corrupted and sysadmin deleted the undo file, no backup of undo file exists

Recovery


 Issue Identification

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1385840 bytes

Variable Size             297798288 bytes

Database Buffers          230686720 bytes

Redo Buffers                5791744 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 – see DBWR trace file

ORA-01110: data file 3: ‘C:\ORACLE\DATA\PRE\UNDOTBS01.DBF’

Steps to recover the database from loss of undo data file


 

1. Check the undo_management parameter. make it manual if it is set to auto

SQL> show parameter undo

NAME                                 TYPE        VALUE

———————————— ———– ——————————

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

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

System altered.

2. To enable the manual undo tablespace parameter Shutdown & startup the database
SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1385840 bytes

Variable Size             297798288 bytes

Database Buffers          230686720 bytes

Redo Buffers                5791744 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 – see DBWR trace file

ORA-01110: data file 3: ‘C:\ORACLE\DATA\PRE\UNDOTBS01.DBF’

SQL> show parameter undo

NAME                                 TYPE        VALUE

———————————— ———– ——————————

undo_management                      string      MANUAL

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

3. Drop the undo datafile so that the information about the missing undo file is removed from control file as a mandatory file to open the database.

SQL> alter database datafile ‘C:\ORACLE\DATA\PRE\UNDOTBS01.DBF’ offline drop;

Database altered.

4. As the undo datafile is dropped the database can now be opened.

SQL> alter database open;

Database altered.

5. Create a new undo tablespace, make it default undo tablespace and drop the old undo tablespace.

SQL> drop tablespace undotbs1;

drop tablespace undotbs1

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01552: cannot use system rollback segment for non-system tablespace ‘USERS’

ORA-06512: at line 999

ORA-01552: cannot use system rollback segment for non-system tablespace ‘USERS’

The above error is encountered because of CDC (Change Data Capture) trigger is enabled.

Solution is to disable the CDC trigger and then undo tablespace can be dropped successfully.

To disable the CDC trigger follow the below steps

5.1  Create pfile from current spfile

 SQL> create pfile=’c:\pfile.ora’ from spfile;

 File created.

 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 5.2  Edit the pfile and add the following in the pfile

 _system_trig_enabled=false

 5.3  Startup the database using the pfile

SQL> startup pfile=’c:\pfile.ora’

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1385840 bytes

Variable Size             297798288 bytes

Database Buffers          230686720 bytes

Redo Buffers                5791744 bytes

Database mounted.

Database opened.

5.4  Check the _system_trig_enabled parameter whether it is set to false now.

SQL> show parameter _system

 NAME                                 TYPE        VALUE

———————————— ———– ——

_system_trig_enabled                 boolean     FALSE

filesystemio_options                 string

 5.5  Disable cdc triggers using the following commands

SQL> ALTER TRIGGER sys.cdc_alter_ctable_before DISABLE;

Trigger altered.

SQL> ALTER TRIGGER sys.cdc_create_ctable_after DISABLE;

Trigger altered.

SQL> ALTER TRIGGER sys.cdc_create_ctable_before DISABLE;

Trigger altered.

SQL> ALTER TRIGGER sys.cdc_drop_ctable_before DISABLE;

Trigger altered.

 Once the undo tablespace is dropped and created successfully CDC triggers can be enabled and the system should be restarted with _system_trig_enabled=true

 Now the new undo tablespace can be created

SQL> create undo tablespace undotbs1 datafile ‘C:\oracle\data\pre\UNDOTBS01.DBF’

size 60M autoextend on next 2M maxsize 2G;

Tablespace created.

6. Enable the cdc triggers again

 SQL> ALTER TRIGGER sys.cdc_alter_ctable_before ENABLE;

Trigger altered.

SQL> ALTER TRIGGER sys.cdc_create_ctable_after ENABLE;

Trigger altered.

SQL> ALTER TRIGGER sys.cdc_create_ctable_before ENABLE;

Trigger altered.

SQL> ALTER TRIGGER sys.cdc_drop_ctable_before ENABLE;

Trigger altered.

 7. shutdown the db, edit the pfile so that _system_trig_enabled=true and start the database with this pfile,create the spfile from this pfile, shutdown and again start the database.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile=’c:\pfile.ora’;

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1385840 bytes

Variable Size             297798288 bytes

Database Buffers          230686720 bytes

Redo Buffers                5791744 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile=’c:\pfile.ora’;

File created.

 SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1385840 bytes

Variable Size             297798288 bytes

Database Buffers          230686720 bytes

Redo Buffers                5791744 bytes

Database mounted.

Database opened.

 8. Set the undo_management parameter to auto, shutdown and restart the database.

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

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area  535662592 bytes

Fixed Size                  1385840 bytes

Variable Size             297798288 bytes

Database Buffers          230686720 bytes

Redo Buffers                5791744 bytes

Database mounted.

Database opened.

SQL> show parameter undo

NAME                                 TYPE        VALUE

———————————— ———– ——————————

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

 9. Perform a operation to ensure that undo tablespace is working properly.

SQL> create table tmp01 as select * from dba_segments;

Table created.

SQL> insert into tmp01 select * from dba_segments;

6433 rows created.

SQL> commit;

Commit complete.

 The following screenshot shows the existence of undo tablespace in proper location.

undo2

 

 

 

 

 

Conclusion


 To recover from a loss undo tablespace datafile if the database is properly shutdown i.e. no active rollback segment exists – then the following need to be performed to recover:

  1. configure manual undo_management
  2. offline drop the undo datafile
  3. disable the cdc trigger
  4. drop the undo tablespace
  5. recreate a new undo tablespace preferably with the same name
  6. configure automatic undo management.

 

 

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*