Move UNDO Tablespace from one location to another due to a mount point space constraint.

Hi,

 

Below is a simple case where the mount point is filled due to undo tablespace.

 

 

Issue: Due to space shortage on disk need to move UNDO Tablespace to another location/ recreate the undo.

 

Step: 1

Need to verify if there are any active transactions in undo/rollback segment.

col o format a10

col u format a10

select osuser o, username u, sid,

segment_name s, substr(sa.sql_text,1,200) txt

from v$session s,

v$transaction t,

dba_rollback_segs r,

v$sqlarea sa

where s.taddr=t.addr

and t.xidusn=r.segment_id(+)

and s.sql_address=sa.address(+)

And substr(sa.sql_text,1,200) is not null

order by 3;

 

col name format a8

col username format a8

col osuser format a8

col start_time format a17

col status format a12

tti 'Active transactions'

select username, osuser,

t.start_time, r.name, t.used_ublk "ROLLB BLKS",

decode(t.space, 'YES', 'SPACE TX',

decode(t.recursive, 'YES', 'RECURSIVE TX',

decode(t.noundo, 'YES', 'NO UNDO TX', t.status)

)) status

from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s

where t.xidusn = r.usn

and t.ses_addr = s.saddr

/

 

Once we have the active transactions, either a COMMIT/ROLLBACK to be issued for the active transactions in the UNDO to free up.

 

Step :2

Verify what is the undo tablespace being used in the database.

 

show parameter undo- to confirm the tablespace name.

 

Step 3:

Verify the space available mount point and create a new tablespace for UNDO.

 

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oracle/db/tbs/_undotbs_02.dbf' SIZE 10000M;

ALTER TABLESPACE UNDOTBS2 ADD DATAFILE '/oracle/db/tbs/_undotbs_03.dbf'  SIZE 30G;

 

Step 4:

 

Change the UNDO settings as below.

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

 

DROP TABLESPACE UNDOTBS INCLUDING CONTENTS AND DATAFILES;

 

Now, either update SPFILE to use the new name UNDOTBS2 via:

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;

 

Or move UNDO tablespace back to the old name

 

CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/oracle_db/db2/db_igt/tbs/ora_undotbs_01.dbf' SIZE 10000M;

 

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS;

 

DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

 

Inside spfile, the UNDO Tablespace name is stored, but not the actual file location. So we need to make sure that the undo parameter is set and altered to the new one.

*.undo_tablespace='UNDOTBS'

 

 

----

 

 

This message may contain confidential information. If you are not the intended recipient, please delete this message and contact the sender. Evolving Systems, Inc. is a Delaware corporation, Number 2580274. Corporate Address: 9800 Pyramid Court, Suite 400, Englewood, Colorado 80112. www.evolving.com.