Home > Oracle, RDBMS > Recovery of UNDO tablespace in the database with NOARCHIVELOG mode.

Recovery of UNDO tablespace in the database with NOARCHIVELOG mode.

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 the UNDO tablespace is the same as that of any other non-system tablespace (with backup/without backup), but how to recover if the database is in NOARCHIVELOG mode?

1. Database is open.
– create the new UNDO tablespace

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'd:\oracle\oradata\ORACLE_SID\undotbs02.dbf' SIZE 128M;

– set the initialization parameter UNDO_TABLESPACE to the proper value, if you use PFILE then edit the init.ora file, otherwise use the following command

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=SPFILE;

– restart the database

SHUTDOWN IMMEDIATE;
STARTUP;

– drop the old UNDO tablespace as it is no longer used

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

2. Database is close.
This will work only if the database was 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.
– mount the database

STARTUP MOUNT;

– identify the datafile which needs recovery

select * from v$recover_file;

– connect datafile with tablespace

select t.name, f.name
from v$tablespace t, v$datafile f
where t.ts# = f.ts#
and f.file# = <file_number>;

– offline the missing datafile with ‘FOR DROP’ option (a datafile once marked ‘FOR DROP’ can never be brought online)

ALTER DATABASE DATAFILE <file_number> OFFLINE FOR DROP;

– open the database

ALTER DATABASE OPEN;

– create the new UNDO tablespace

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'd:\oracle\oradata\ORACLE_SID\undotbs02.dbf' SIZE 128M;

– set the initialization parameter UNDO_TABLESPACE to the proper value, if you use PFILE then edit the init.ora file, otherwise use the following command

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=SPFILE;

– restart the database

SHUTDOWN IMMEDIATE;
STARTUP;

– drop the old UNDO tablespace as it is no longer used

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Hope, this helps.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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: