Recovery of UNDO tablespace in a Non Archive Log mode Database

Every transaction stores the post modification data in the undo tablespace for a rollback or read consistency or flash back query or for recovery in case of abort. All system transactions will use the rollback segments created in the system tablespace. It cannot be used by other schema operations, they should use the undo tablespace. So an Undo tablespace is a must for any transaction to occur. If the database is in Archivelog mode, the recovery of an Undo tablespace is same as that of any Non-System tablespace,but how to recover if the database is in Non Archivelog Mode ?

While shutdown
++++++++++++++
This will work only if the database is shutdown properly(shutdown immediate).
If the database was aborted, oracle would do the ‘instance recovery’ whilst next startup and would terminate if the undo tablespace isn’t present.

SQL> startup;
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/u01/oradata/burp/undotbs01.dbf'

Since the database in Non Archive Log mode, we cannot open the database by taking the datafile offline using ‘alter database datafile 2 offline’,but we need to use ‘…OFFLINE FOR DROP‘. This will mark the datafile for subsequent dropping.A datafile once marked ‘OFFLINE FOR DROP’ can never be brought online.

SQL>alter database datafile 2 offline for drop;
SQL>alter database open;

Now we need to create another undo tablespace for the transactions to use. Once the new undo tablespace is created,shutdown the database and edit the init.ora file and change the parameter undo_tablepace=<new_undo_tablespace>

SQL>create undo tablespace UNDOTBS2 datafile '/path/undotbs02.dbf' size 20M;
SQL>shutdown immediate;

edit the init.ora file and set paramter undo_tablespace=UNDOTBS2

SQL>startup;

Now we will drop UNDOTBS1 as it is nolonger used.

SQL>drop tablespace UNDOTBS1 including contents and datafiles;

While database is OPEN
+++++++++++++++++++

SQL>create undo tablespace UNDOTBS2 datafile '/path/undotbs02.dbf' size 20M;
SQL>shutdown immediate;

edit the init.ora file and set paramter undo_tablespace=UNDOTBS2

SQL>startup;

Now we will drop UNDOTBS1 as it is nolonger used.

SQL>drop tablespace UNDOTBS1 including contents and datafiles;
Advertisements

3 thoughts on “Recovery of UNDO tablespace in a Non Archive Log mode Database

  1. Pingback: Index « My confrontations with oracle

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