Undo Tablespace Moved To Pending Switch-Out State


Applies to:


Oracle Server - Enterprise Edition - Version: 9.2.0.0 to 10.2.0.3
Information in this document applies to any platform.

Goal:

This article intends to explain the error "Undo Tablespace X moved to Pending Switch-Out state." which appears in the alert log file.

Solution:

PROBLEM DESCRIPTION WITH AN EXAMPLE TESTCASE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SESSION 1:
~~~~~~~~~~
SQL> create table new (a number(2));
Table created.
SQL> insert into new values (1);
1 row created.

Create a test table and insert some value and do not commit. This will keep the transaction active as the tranasaction is not commited or rolled back. This can be seen in the v$transactions view.

SQL> select ADDR, XIDUSN,STATUS, START_TIME from v$transaction;
ADDR XIDUSN STATUS START_TIME
-------- ---------- ---------------- --------------------
1477F404 5 ACTIVE 01/21/06 15:48:53

SESSION 2:
~~~~~~~~~~
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS1
SQL>

UNDOTBS1 is the undo tablespace which contains the rollback segment where the transactions from the
session 1 is still active.

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

Changing the default undo tablespace to UNDOTBS2.

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

Now the default undo tablespace has been changed, while there are active transactions in the undo tablespace UNDOTBS1. Since there are active transactions in the other undo tablespace, the corresponding rollback segment that was being used, will show a status "PENDING OFFLINE". While the v$rollstat shows "PENDING OFFLINE", the error message "Undo Tablespace X moved to Pending Switch-Out state.", where the 'X' is the old undo tablespace number, will be dumped in the alert log file very intermittently. This is because of the "PENDING OFFLINE" state of the rollback segment which has the active transactions.

SOLUTION:
~~~~~~~~~
Run the following query, which will return the Rollback Segment Number, name of the rollback segment, status of the rollback segment, undo tablespace name, address of transaction state object, session identifier, session serial number which uniquely identifies a session's objects, username, program, machine id and operating system client user name.

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';
Ex:
USN NAME STATUS TABLESPACE_NAME ADDR SID SERIAL# USERNAME PROGRAM
--- --------- --------------- --------------- -------- ---- -------- -------- -----------
5 _SYSSMU5$ PENDING OFFLINE UNDOTBS1 1477F404 9 2976 SYS sqlplus.exe
MACHINE OS_USER
---------------------- --------------------
IDC-ORACLE\bsriniva-pc bsriniva-pc\bsriniva

Compare the XIDUSN and ADDR returned by the v$transaction with the USN and ADDR returned by the above query. You will find the corresponding rollback segment details used for the transaction which is still active.
To prevent the alert log getting flooded with the error "Undo Tablespace X moved to Pending Switch-Out state.", we have to release the rollback segments with "pending offline" state. From the above query we can get the program, user and the machine details, so we can contact the specific users who have the rollback segments locked, to either commit or rollback the transaction. Once the transaction is committed or rolled back the alert log will no more be flooded with the errors.
The other option would be to kill the session that is holding the lock over the rollback segment. The above query also returns the sid# and the serial #. We can kill the session using the following statement:

ALTER SYSTEM KILL SESSION 'sid#,serial# ';

The above mentioned solution will prevent the error message "Undo Tablespace X moved to Pending Switch-Out state." from flooding the alert log file.


No comments:

Post a Comment