Skip to content

alter tablespace read only hangs- new oracle dba





Yesterday my production toad session blocked the alter tablespace read only operation. . My session just did a explain plan on some sql statement through toad and so the plan_table was on the public schema(system tablespace) and this transaction was not committed as toad doesn’t autocommit by default.Looks like due to a bug any uncommitted transaction in any tablespace could block a alter tablespace readonly operation. This information is FYI.

Bug No: 2227223  
Filed 14-FEB-2002 Updated 13-AUG-2009
Product Oracle Server – Enterprise Edition Product Version 8.1.7.2
Platform Microsoft Windows 2000 Platform Version SP 2
Database Version 8.1.7.2 Affects Platforms Generic
Severity Severe Loss of Service Status Development to Q/A
Base Bug N/A Fixed in Product Version 11.2

Problem statement:

ALTER TABLESPACE READ ONLY HANGING WHEN THERE ARE ACTIVE TX IN ANY TABLESPACE

*** 02/14/02 03:53 pm ***

TAR:

—-

1988689.995

.

PROBLEM:

——–

alter tablespace < ts name> read only hangs when there is active transaction

in

any tablespace till all active transactions commit or rollback.

.

Customer considers this is a serious limitation for 24*7 oracle database or

mission critical databases. It is understandable that Oracle expects no active

transaction in the tablespace that is alterred. But why need not to have

active transactions in any other user tablespaces.

.

DIAGNOSTIC ANALYSIS:

——————–

Testcase

=========

The steps to reproduce the problem:

1. insert a row into foo table and don’t commit the transaction. foo table

resides in USERS tablespace.

.

SQL> connect julia

SQL> desc foo

Name Null? Type

—————————————– ——– —————-

ABC NUMBER(38)

DEFNOT NULL NUMBER(38)

GHI NUMBER(38)

.

SQL> insert into foo values (1,2,3);

.

1 row created.

.

.

SQL> alter tablespace users2 read only;

.

Tablespace altered.

.

This session is hanging till the insert transaction commits and users2

tablespace was alterred immediately.

.

Analysis

======

SQL> !ps -ef |grep internal

osupport 29996 29886 0 13:23:54 pts/2 0:00 sqlplus internal

osupport 30800 31330 2 14:33:00 pts/2 0:00 grep internal

osupport 31152 25016 0 14:28:18 pts/0 0:00 sqlplus internal

osupport 31330 29996 1 14:33:00 pts/2 0:00 -bin/csh -c ps -ef |grep

internal

.

.

SQL> !ps -ef |grep 31152

osupport 25686 31152 0 14:28:18 – 0:00 oracle817c

(DESCRIPTION=(LOCAL=YE

S)(ADDRESS=(PROTOCOL=beq)))

osupport 28604 31334 1 14:33:32 pts/2 0:00 grep 31152

osupport 31152 25016 0 14:28:18 pts/0 0:00 sqlplus internal

osupport 31334 29996 2 14:33:32 pts/20:00 -bin/csh -c ps -ef |grep 31152

.

SQL> select sid from v$session, v$process

2 where spid=25686 and addr=paddr;

.

SID

———-

10

.

SQL> select event,p1,p2,p3,wait_time from v$session_wait where sid=10;

.

EVENT P1

—————————————————————- ———-

P2 P3 WAIT_TIME

———- ———- ———-

enqueue 1415053316

589835 5004 0

.

.

SQL> alter session set events ‘immediate trace name systemstate level 10’;

.

Session altered.

.

SQL> alter session set events ‘immediate trace name systemstate level 10′;

.

Session altered.

.

SQL> select p1raw from v$session_wait where sid=10;

.

P1RAW

——–

54580004

.

SQL> select * from v$lock where type=’TX’;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST

——– ——– ———- — ———- ———- ———- ———-

CTIME BLOCK

———- ———-

201456A8 20145774 9 TX 589835 5004 6 0

778 2

.

201456A8 20145774 10 TX 524361 5145 6 0

664 2

.

340864F8 34086508 10 TX 589835 5004 0 4

664 2

.

SQL> select username from v$session where sid=9;

.

USERNAME

——————————

JULIA

.

You can see session id 10 is requesting TX 589835 5004 in shared mode

that is held by session id 9 in exclusive mode. session 9 is doing insert

statement. session 10 is altering tablespace users2 read only.

.

WORKAROUND:

———–

N/A

.

RELATED BUGS:

————-

No

.

REPRODUCIBILITY:

—————-

Yes. reproducible at will.

.

TEST CASE:

———-

Please see the above

.

STACK TRACE:

————

N/A

.

SUPPORTING INFORMATION:

———————–

Will upload couple of systemstate dumps and alert logs to ess30.

.

24 HOUR CONTACT INFORMATION FOR P1 BUGS:

—————————————-

.

DIAL-IN INFORMATION:

——————–

.

IMPACT DATE:

————

.

*** 02/14/02 03:53 pm *** (CHG: Sta->16)

*** 02/14/02 04:10 pm ***

files are on ess30:/bug/bug2227223

*** 02/15/02 05:09 pm *** (CHG: Asg->NEW OWNER)

*** 02/15/02 05:33 pm *** (CHG: Sta->32)

*** 02/15/02 05:33 pm ***

However, this is expected behaviour. Pls see explanation in bug 2106117 and

(ie, "making a tablespace read only" in Ch 11 of the Server Administrators

guide. ).

.

To quote the note :

.

You do not have to wait for transactions to complete before issuing the ALTER

TABLESPACE … READ ONLY statement. When the statement is issued, the target

tablespace goes into a transitional read-only mode in which no further write

operations (DML statements) are allowed against the tablespace. Existing

transactions that modified the tablespace are allowed to commit or rollback.

Once all transactions (in the database) have completed, the tablespace becomes

read-only.

.

.

The significant line here is ‘Once all transactions (in the database) have

completed, the tablespace becomes read-only.’ This means all transactions and

not just transactions in that tablespace. Thanks

*** 02/25/02 01:41 pm *** (CHG: Sta->16)

*** 02/25/02 01:41 pm ***

*** 02/25/02 01:50 pm *** (CHG: Sta->32)

*** 02/25/02 01:50 pm ***

*** 04/22/05 12:36 am *** (CHG: Sta->92)

*** 02/13/06 08:02 pm *** (CHG: SubComp->UNKNOWN)

*** 02/13/06 08:02 pm ***

*** 12/04/07 06:19 pm *** (CHG: Sta->11 Asg->NEW OWNER)

*** 12/04/07 06:19 pm ***

*** 12/07/07 05:38 pm ***

RELEASE NOTES:

]]Hang occurs during ‘alter tablespace read only’ due to unrelated active trans

]]actions.

REDISCOVERY INFORMATION:

Hang occurs during ‘alter tablespace read only’ due to unrelated active transac

tions.

WORKAROUND:

None

*** 12/09/07 09:29 pm *** (CHG: Asg->NEW OWNER)

*** 12/09/07 09:29 pm *** (CHG: Fixed->11.1.0.7)

*** 12/09/07 09:29 pm *** (CHG: Sta->80)

*** 12/10/07 07:21 am ***

*** 12/10/07 08:30 am ***

*** 12/10/07 10:38 am ***

*** 12/11/07 07:31 am ***

*** 12/16/07 11:49 pm ***

*** 12/20/07 05:53 pm ***

*** 01/16/08 01:30 am ***

*** 01/16/08 08:23 pm ***

*** 01/16/08 10:46 pm ***

*** 01/27/08 04:44 pm ***

*** 03/03/08 05:20 pm ***

*** 03/26/08 04:08 pm *** (CHG: Fixed->11.2)

*** 04/01/08 01:52 pm *** (CHG: Asg->NEW OWNER)

*** 04/01/08 01:52 pm ***

*** 05/17/08 11:53 am ***

*** 05/27/08 02:27 am ***

*** 05/27/08 02:27 am ***

*** 05/27/08 10:09 pm ***

*** 05/28/08 11:24 pm ***

*** 05/29/08 11:43 pm ***

*** 07/01/08 07:13 pm ***

*** 07/03/08 01:19 pm ***

*** 07/08/08 06:13 pm ***

*** 07/09/08 02:29 am ***

*** 07/22/08 03:33 pm ***

*** 07/22/08 03:34 pm ***

*** 07/22/08 03:34 pm ***

*** 07/23/08 08:48 pm ***

*** 07/25/08 10:24 am ***

*** 07/25/08 10:28 am ***

*** 07/25/08 10:59 am ***

*** 07/31/08 02:21 pm ***

*** 07/31/08 02:25 pm ***

*** 07/31/08 05:19 pm ***

*** 07/31/08 05:22 pm ***

*** 08/04/08 02:45 pm ***

*** 08/04/08 02:45 pm ***

bug 2227223

*** 08/04/08 10:36 pm ***

bug 7298754

*** 08/07/08 09:51 pm ***

*** 08/07/08 09:51 pm ***

*** 08/27/08 08:06 am ***

*** 09/01/08 11:03 pm ***

*** 09/01/08 11:04 pm ***

*** 09/01/08 11:05 pm ***

*** 09/02/08 01:33 pm ***

*** 09/07/08 09:36 pm ***

*** 09/07/08 09:40 pm ***

*** 09/09/08 10:24 pm ***

*** 09/10/08 07:25 pm ***

*** 09/12/08 11:59 pm ***

*** 09/15/08 05:24 pm ***

bug 5092032

*** 09/18/08 11:55 am ***

*** 09/22/08 09:09 am ***

*** 09/22/08 04:30 pm ***

*** 09/22/08 04:30 pm ***

*** 10/03/08 12:53 pm ***

*** 02/05/09 08:36 am ***

*** 02/05/09 09:39 am ***

*** 04/13/09 02:58 pm ***

*** 04/29/09 07:04 am ***

*** 04/30/09 06:06 am ***

*** 04/30/09 06:09 am ***

*** 05/01/09 03:55 am ***

*** 05/06/09 04:48 pm ***

*** 05/13/09 05:13 pm ***

*** 05/15/09 12:24 am ***

*** 05/15/09 12:33 am ***

*** 05/26/09 11:43 pm ***

*** 06/16/09 06:37 am ***

bug 6404447 bug 6795880 bug 8210889 bug 7462072

*** 06/23/09 05:33 am ***

*** 06/25/09 10:12 am ***

*** 06/25/09 10:18 am ***

*** 06/30/09 09:25 pm ***

*** 06/30/09 09:42 pm ***

bug 6327692 bug 7238163 bug 7462072 bug 6904068 bug 8210889

*** 07/14/09 06:39 pm ***

*** 08/13/09 09:52 am ***

bug 6954722 bug 6053134 bug 6638558 bug 6870994 bug 6454456 bug 2227223

.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.