[Oracle-l] Making a tablespace read-only - identifying blocking tx

Just a slight correction it will wait until any transaction against the entire database, not just the tablespace is completed.


Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu

Here's an even more kludgy guess. Maybe you can execute the alter
tablespace in the pl/sql job queue. If it runs too long (how long is too
long?), then you know that it's waiting (i.e., failing), and you could do
something appropriate action?

"Fink, Dan" @fatcity.com on 10/09/2002 04:48:54 PM

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: Multiple recipients of list ORACLE-L
cc:

I am creating a stored proc that will export a tablespace. One task it
needs to perform is to place the tablespace(s) in read only mode to make a
copy. Based upon the application and proc logic, there should not be any
transactions against objects in the ts. However, if there are, the ALTER
TABLESPACE command will wait until the transaction is completed. I would
rather have the ALTER TABLESPACE command fail immediately. If I cannot do
that, I would like to be able to test for locks on objects in the
tablespace (figured that one out, but it is rather kludgy).

Is there a method to force an immediate failure of ALTER TABLESPACE
READ ONLY if it cannot be immediatly completed?
Is there a clean method/proc to determine if the ALTER TABLESPACE command
will work?

Dan Fink

And with that correction, it seems checking for active transactions (in
v$transaction) would address this.


However, by the time one gets a 'green' light from v$transaction and issues
alter tablespace... there is the slight possibility of someone starting a
new transaction locally or just selecting over a dblink...


Too bad that the new 'transitional read-only' mode does not allow a graceful
exit... Per the Admin Guide one must set compatible to < 8.1.0 to make the
command fail...


I would be interested in learning how you tackle this issue as I am also
trying to implement TTS in some of my databases.


Thanks.


Kirti

so if it's waiting for any active transaction, I guess you could put
the database in restricted mode until existing transactions complete.
Of course, that sort of defeats the purpose of putting it in read-only
so other people can access it.

um, 9ir2 has an export parameter of "tablespace", if you want it
"read-only" so nothing changes while you export it, how about using the
consistent=y export parameter in conjunction with the tablespace
export?

"Deshpande, Kirti" wrote:
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com

Why don't you just to a consistant export? That will not take any
transaction after the start of the export, at leasst this is how I
understand it.

Ruth.

It is not the export per se that causes the problem. It is the copying of
the datafile that is the issue. The tablespace must be made read only so
that the datafile can be copied in a consistent version. I can understand
(and support) no active tx in the tablespace, but why the whole (*#(&*$#
database? If I need to take INVOICE_1999 tablespace and migrate it to an
ODS, why does it matter if Joe Accountant is adding an expense report in the
EXPENSE_2002 ts?

In the Oracle doc, it lists the requirements for making a ts read only. On
the next page it states (verbatim from doc)

"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."

I love how Oracle buries a very important consideration in the very last
line of a paragraph!

We are on 9ir1, so the TABLESPACE parameter is not helpful, but we do have
other options. The application architecture is such that I am pretty certain
very bad things would happen if I tried to but the database in restricted
mode.

Dan

-----Original Message-----
Sent: Thursday, October 10, 2002 5:09 AM
To: Multiple recipients of list ORACLE-L

so if it's waiting for any active transaction, I guess you could put
the database in restricted mode until existing transactions complete.
Of course, that sort of defeats the purpose of putting it in read-only
so other people can access it.

um, 9ir2 has an export parameter of "tablespace", if you want it
"read-only" so nothing changes while you export it, how about using the
consistent=y export parameter in conjunction with the tablespace
export?

"Deshpande, Kirti" wrote:
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com

That would be one option, but we are trying to avoid the overhead of the
import process as we move the data to a new database. This process will
happen daily and transporting a tablespace and plugging in a datafile is
perceived to be faster (has not been tested yet).

Dan

But can you know for sure if an open txn will hit your tablespace until the
txn is closed?

Henry

-----Original Message-----
Sent: Thursday, October 10, 2002 11:39 AM
To: Multiple recipients of list ORACLE-L

It is not the export per se that causes the problem. It is the copying of
the datafile that is the issue. The tablespace must be made read only so
that the datafile can be copied in a consistent version. I can understand
(and support) no active tx in the tablespace, but why the whole (*#(&*$#
database? If I need to take INVOICE_1999 tablespace and migrate it to an
ODS, why does it matter if Joe Accountant is adding an expense report in the
EXPENSE_2002 ts?

In the Oracle doc, it lists the requirements for making a ts read only. On
the next page it states (verbatim from doc)

"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."

I love how Oracle buries a very important consideration in the very last
line of a paragraph!

We are on 9ir1, so the TABLESPACE parameter is not helpful, but we do have
other options. The application architecture is such that I am pretty certain
very bad things would happen if I tried to but the database in restricted
mode.

Dan

-----Original Message-----
Sent: Thursday, October 10, 2002 5:09 AM
To: Multiple recipients of list ORACLE-L

so if it's waiting for any active transaction, I guess you could put
the database in restricted mode until existing transactions complete.
Of course, that sort of defeats the purpose of putting it in read-only
so other people can access it.

um, 9ir2 has an export parameter of "tablespace", if you want it
"read-only" so nothing changes while you export it, how about using the
consistent=y export parameter in conjunction with the tablespace
export?

"Deshpande, Kirti" wrote:
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com

D'OH!

You are absolutely correct. I completely overthought the issue and missed
the obvious.

Thanks Henry

-----Original Message-----
Sent: Thursday, October 10, 2002 11:09 AM
To: Multiple recipients of list ORACLE-L

But can you know for sure if an open txn will hit your tablespace until the
txn is closed?

Henry

-----Original Message-----
Sent: Thursday, October 10, 2002 11:39 AM
To: Multiple recipients of list ORACLE-L

It is not the export per se that causes the problem. It is the copying of
the datafile that is the issue. The tablespace must be made read only so
that the datafile can be copied in a consistent version. I can understand
(and support) no active tx in the tablespace, but why the whole (*#(&*$#
database? If I need to take INVOICE_1999 tablespace and migrate it to an
ODS, why does it matter if Joe Accountant is adding an expense report in the
EXPENSE_2002 ts?

In the Oracle doc, it lists the requirements for making a ts read only. On
the next page it states (verbatim from doc)

"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."

I love how Oracle buries a very important consideration in the very last
line of a paragraph!

We are on 9ir1, so the TABLESPACE parameter is not helpful, but we do have
other options. The application architecture is such that I am pretty certain
very bad things would happen if I tried to but the database in restricted
mode.

Dan

-----Original Message-----
Sent: Thursday, October 10, 2002 5:09 AM
To: Multiple recipients of list ORACLE-L

so if it's waiting for any active transaction, I guess you could put
the database in restricted mode until existing transactions complete.
Of course, that sort of defeats the purpose of putting it in read-only
so other people can access it.

um, 9ir2 has an export parameter of "tablespace", if you want it
"read-only" so nothing changes while you export it, how about using the
consistent=y export parameter in conjunction with the tablespace
export?

"Deshpande, Kirti" wrote:
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com

ah! transportable tablespaces? you did't SAY that

"Fink, Dan" wrote:
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com

That information is not relevant and should be ignored. I would expect YOU
to know as much!

-----Original Message-----
Sent: Thursday, October 10, 2002 1:25 PM
To: Multiple recipients of list ORACLE-L

ah! transportable tablespaces? you did't SAY that

"Fink, Dan" wrote:
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com

yes it's relevant, it explains why you can't use a consistent export
only to move the data and have to copy the tablespace as well. It also
explains why the tablespace has to be in read-only mode.

"Fink, Dan" wrote:
=== message truncated ===

Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com

First, I ommitted the;) when replying to Rachel's post. I was just
funnin...

Second, in this case TTS will not work. While we can 99.99% guarantee that
there will be no tx against the tablespace, there will be active tx in the
database and we cannot guarantee that they will not cause the RO command to
wait.

Interestingly, it is possible to offline the tablespace while there are
active tx (even against objects in the ts). If you can offline and then
online with active tx, why not alter it to RO? Perhaps, once again, I am
missing the obvious.

Dan

-----Original Message-----
Sent: Thursday, October 10, 2002 8:23 PM
To: Multiple recipients of list ORACLE-L

yes it's relevant, it explains why you can't use a consistent export
only to move the data and have to copy the tablespace as well. It also
explains why the tablespace has to be in read-only mode.

"Fink, Dan" wrote:
=== message truncated ===

Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com

thanks :)

it sort of makes sense that you can offline a tablespace with an active
transaction in it. You can offline a rollback segment with an active
transaction in it, no new transactions can start in that rbs but the
one(s) there will finish.

With the tablespace, if I remember what I read in the docs correctly
(and if the docs are correct), when you offline a tablespace, the
active transactions in it continue to process as long as they do not
try to modify a block in that tablespace. Once they try that, the
transaction dies.

Hm, that doesn't sound right...... maybe it's if the blocks from that
tablespace are still in the buffer cache?

Okay, found it in the 9ir2 Concepts manual, quoted below:

When a tablespace goes offline, Oracle does not permit any subsequent
SQL statements to reference objects contained in that tablespace.
Active transactions with completed statements that refer to data in
that tablespace are not affected at the transaction level. Oracle saves
rollback data corresponding to those completed statements in a deferred
rollback segment in the SYSTEM tablespace. When the tablespace is
brought back online, Oracle applies the rollback data to the
tablespace, if needed.

When a tablespace goes offline or comes back online, this is recorded
in the data dictionary in the SYSTEM tablespace. If a tablespace is
offline when you shut down a database, the tablespace remains offline
when the database is subsequently mounted and reopened.

You can bring a tablespace online only in the database in which it was
created because the necessary data dictionary information is maintained
in the SYSTEM tablespace of that database. An offline tablespace cannot
be read or edited by any utility other than Oracle. Thus, offline
tablespaces cannot be transposed to other databases.

"Fink, Dan" wrote:
=== message truncated ===

Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com