Create a new UNDO_TABLESPACE
2. Type "ALTER SYSTEM SET UNDO_TABLESPACE=NEW TABLESPACE". If using pfile, change the parameter on init.ora. If using spfile add the clause SCOPE=BOTH

All the active transactions will remain using the actual tablespace, but the new ones will be addressed to the new undo tablespace. Once the active transactions finish, you can put your old tablespace offline and then drop it, until then it will be "PENDING OFFLINE".

About the redo logs, just create new groups and drop the old ones. Remember you must have at least 2 groups at any given time, so you'll need to "add a new one/remove an old one" and so on.

I wanted to drop the existing Undo tablespace (undoTbs1), so I did the following:

1) I created a new undo tablespace 'undoTbs2'
2) Alter System SET Undo_Tablespace=undoTbs2

Then I tried the following :

DROP TABLESPACE undoTbs1
this did not work
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

I tried this also
DROP TABLESPACE undoTbs2 INCLUDING CONTENTS
this also did not work same error.

Can anyone help me in accomplishing this.

Thanks in advance
qAnand

The problem is that there is someone that still has a transaction with active undo segments. You could....

1) wait until all pending transactions using the old undo tablespace have commited or rolled back... you're the DBA(!)... surely we don't have to wait on the user, or

2) kill the offending session(!), or

3) restart your instance.

Afterwards, you should be able to drop the old undo tablespace.

JoeB

Check your alert log file and if you getting "Undo Tablespace X moved to Pending Switch-Out state." error message than you need to find the pending transactions which blocking you to drop UNDO tablespace. Use query below to find the pending offline transaction.

SELECT
a.usn,
a.name,
b.status,
c.tablespace_name,
d.addr,
e.sid,
e.serial#,
e.username,
e.program,
e.machine,
e.osuser
FROM
v$rollname a,
v$rollstat b,
dba_rollback_segs c,
v$transaction d,
v$session e
WHERE
a.usn=b.usn AND
a.name=c.segment_name AND
a.usn=d.xidusn AND
d.addr=e.taddr AND
b.status='PENDING OFFLINE';

You can either kill the transaction or call the user to commit his/her transaction. After this you should be able to drop UNDO tablespace.

Check metalink note 341372.1 for more info.

Move/rename datafiles in Oracle

Moving datafiles of a database: The datafiles reside under /home/oracle/OraHome1/databases/ora9 and have go to /home/oracle/databases/ora9.

SQL> select tablespace_name, substr(file_name,1,70) from dba_data_files;

TABLESPACE_NAME                SUBSTR(FILE_NAME,1,70)

------------------------------ ----------------------------------------------------------------------

SYSTEM                         /home/oracle/OraHome1/databases/ora9/system.dbf

UNDO                           /home/oracle/OraHome1/databases/ora9/undo.dbf

DATA                           /home/oracle/OraHome1/databases/ora9/data.dbf

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/home/oracle/OraHome1/databases/ora9/redo1.ora

/home/oracle/OraHome1/databases/ora9/redo2.ora

/home/oracle/OraHome1/databases/ora9/redo3.ora

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/home/oracle/OraHome1/databases/ora9/ctl_1.ora

/home/oracle/OraHome1/databases/ora9/ctl_2.ora

/home/oracle/OraHome1/databases/ora9/ctl_3.ora

Now, as the files to be moved are known, the database can be shut down:

SQL> shutdown

The files can be copied to their destination:

$ cp /home/oracle/OraHome1/databases/ora9/system.dbf  /home/oracle/databases/ora9/system.dbf

$ cp /home/oracle/OraHome1/databases/ora9/undo.dbf    /home/oracle/databases/ora9/undo.dbf

$ cp /home/oracle/OraHome1/databases/ora9/data.dbf    /home/oracle/databases/ora9/data.dbf

$ cp /home/oracle/OraHome1/databases/ora9/redo1.ora   /home/oracle/databases/ora9/redo1.ora

$ cp /home/oracle/OraHome1/databases/ora9/redo2.ora   /home/oracle/databases/ora9/redo2.ora

$ cp /home/oracle/OraHome1/databases/ora9/redo3.ora   /home/oracle/databases/ora9/redo3.ora

$ cp /home/oracle/OraHome1/databases/ora9/ctl_1.ora   /home/oracle/databases/ora9/ctl_1.ora

$ cp /home/oracle/OraHome1/databases/ora9/ctl_2.ora   /home/oracle/databases/ora9/ctl_2.ora

$ cp /home/oracle/OraHome1/databases/ora9/ctl_3.ora   /home/oracle/databases/ora9/ctl_3.ora

The init.ora file is also copied because it references the control files. I name the copied file just init.ora because it is not in a standard place anymore and it will have to be named explicitely anyway when the database is started up.

$ cp /home/oracle/OraHome1/dbs/initORA9.ora /home/oracle/databases/ora9/init.ora

The new location for the control files must be written into the (copied) init.ora file:

/home/oracle/databases/ora9/init.ora

control_files = (/home/oracle/databases/ora9/ctl_1.ora,

                 /home/oracle/databases/ora9/ctl_2.ora,

                 /home/oracle/databases/ora9/ctl_3.ora)

$ sqlplus "/ as sysdba"

SQL> startup exclusive mount pfile=/home/oracle/databases/ora9/init.ora

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/system.dbf' to '/home/oracle/databases/ora9/system.dbf';

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/undo.dbf'   to '/home/oracle/databases/ora9/undo.dbf';

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/data.dbf'   to '/home/oracle/databases/ora9/data.dbf';

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo1.ora'  to '/home/oracle/databases/ora9/redo1.ora';

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo2.ora'  to '/home/oracle/databases/ora9/redo2.ora';

SQL> alter database rename file '/home/oracle/OraHome1/databases/ora9/redo3.ora'  to '/home/oracle/databases/ora9/redo3.ora';

SQL> shutdown

SQL> startup pfile=/home/oracle/databases/ora9/init.ora

How to Drop UNDO Tablespace

It is not an easy task to drop the undo tablespace . Once I have to delete the undo tablespace due to some reason and i find that it is not straight forward to delete the undo tablespace . I got the following error while dropping the error :

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
------------------------------ ---------------------------------------------------------------------
USERS D:\ORACLE\ORADATA\NOIDA\USERS01.DBF
UNDOTBS1 D:\ORACLE\ORADATA\NOIDA\UNDOTBS01.DBF
SYSAUX D:\ORACLE\ORADATA\NOIDA\SYSAUX01.DBF
SYSTEM D:\ORACLE\ORADATA\NOIDA\SYSTEM01.DBF
EXAMPLE D:\ORACLE\ORADATA\NOIDA\EXAMPLE01.DBF

SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

As the error indicate that the undo tablespace is in use so i issue the following command.

SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace.

Therefore, to drop undo tablespace, we have to perform following steps:

1.) Create new undo tablespace
2.) Make it defalut tablepsace and undo management manual by editting parameter file and restart it.
3.) Check the all segment of old undo tablespace to be offline.
4.) Drop the old tablespace.
5.) Change undo management to auto by editting parameter file and restart the database

Step 1 : Create Tablespace : Create undo tablespace undotbs2

SQL> create undo tablespace UNDOTBS2 datafile 'D:\ORACLE\ORADATA\NOIDA\UNDOTBS02.DBF' size 100M;
Tablespace created.

Step 2 : Edit the parameter file

SQL> alter system set undo_tablespace=UNDOTBS2 ;
System altered.

SQL> alter system set undo_management=MANUAL scope=spfile;
System altered.

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

SQL> startup
ORACLE instance started.
Total System Global Area 426852352 bytes
Fixed Size 1333648 bytes
Variable Size 360711792 bytes
Database Buffers 58720256 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2

Step 3: Check the all segment of old undo tablespace to be offline

SQL> select owner, segment_name, tablespace_name, status from dba_rollback_segs order by 3;

OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
------ ------------------------------ ------------------------------ ----------------
SYS SYSTEM SYSTEM ONLINE
PUBLIC _SYSSMU10_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU1_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU2_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU3_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU4_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU5_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU6_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU7_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU8_1192467665$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU9_1192467665$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU12_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU13_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU14_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU15_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU11_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU17_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU18_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU19_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU20_1304934663$ UNDOTBS2 OFFLINE
PUBLIC _SYSSMU16_1304934663$ UNDOTBS2 OFFLINE

21 rows selected.

If any one the above segment is online then change it status to offline by using below command .

SQL>alter rollback segment "_SYSSMU9_1192467665$" offline;

Step 4 : Drop old undo tablespace

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

Step 5 : Change undo management to auto and restart the database

SQL> alter system set undo_management=auto scope=spfile;
System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 426852352 bytes
Fixed Size 1333648 bytes
Variable Size 364906096 bytes
Database Buffers 54525952 bytes
Redo Buffers 6086656 bytes
Database mounted.
Database opened.

SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2

Enjoy J J J

Switch Undo Tablespace

SQL> select name from v$tablespace where name like 'UNDO%';

NAME
------------------------------
UNDOTBS1
UNDOTBS2

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace = 'UNDOTBS2';

System altered.

On another session which connect as scott user

SQL> insert into test select * from all_objects;

47327 rows created.

SQL> select status from v$rollstat;

STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE

STATUS
---------------
PENDING OFFLINE
ONLINE
ONLINE

14 rows selected.

On another session which connect as scott user

SQL> commit;

Commit complete.
SQL> select status from v$rollstat;

STATUS
---------------
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE

11 rows selected.
SQL> show parameter undo_tablespace

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2

Note: When you change Undo Tbs say undotbs1 to undotbs2 then all existing trasaction they using UNDOTBS1 they still using undotbs1 and status show "OFFLINE PENDING".and all new transaction after changed undotbs "alter system undo_tablespace" statements issued used New Undo Tablespace.

You cann't drop or offline if status show offline pending.
when all transaction which using old undotbs are COMMIT;
then you can drop or offline old undotbs.

SQL> select status from v$rollstat;

STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE

STATUS
---------------
PENDING OFFLINE
ONLINE
ONLINE

14 rows selected.

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace

st it again
https://<hostname>:1158/em/console

Amr Blog

Wednesday 17 July 2013

Moving Undo Tablespace from one location to another location

Move/rename datafiles in Oracle

How to Drop UNDO Tablespace

Switch Undo Tablespace

No comments:

Post a Comment