Re: Delayed block cleanout and changing automatic undo tablespace
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 20 Jul 2004 08:01:36 -0600
Now the tough one...
Tim, please take no offense to my questions. It is just that something does not
sound right. I have been able to duplicate this type
of condition, but not under the circumstances you describe. I have duplicated
this type of error in the test below. Granted, I am
not using delayed block cleanout, but performing a recovery will exercise the
same structures. In this case, I think it is close
enough to be valid (at least to shed light on the issue)
Did you perform a consistent shutdown when changing undo tablespaces?
Did you remove the datafiles for the original undo tablespace?
What were the exact steps you took to change undo tablespaces?
Delayed block cleanout does not make sense to me. If the db was *consistent* on
startup (no recovery needed whatsoever), all the
blocks in any datafile are guaranteed to be committed. Any query that accesses
any 'uncommitted' blocks will know that they have
been committed before the query began, therefore they do not need to read the
undo segments.
If Oracle requires data from an offline undo tablespace and the tablespace
definition and datafiles are still valid, it can read the
undo to generate a read consistent version.
# Create a new undo tablespace, but it is not active. In a separate session, I
edited the init.ora to reflect the new undo ts.
SQL> create undo tablespace undo_ts2 datafile
'/ora01/oradata/DWF9i/undo_ts2_01.dbf' size 1001m;
Tablespace created.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string undots
SQL> create table test_dbc as select * from dba_objects;
Table created.
SQL> update test_dbc set object_id = object_id * 42;
5808 rows updated.
# The update from the tx has not been committed. By performing a shutdown
abort, I guarantee that recovery will be required on startup.
SQL> shutdown abort;
ORACLE instance shut down.
# Move the old undots datafile.
SQL> !mv /ora01/oradata/DWF9i/undots_01.dbf
/ora01/oradata/DWF9i/undots_01.dbf.bak
SQL> startup
ORACLE instance started.
Total System Global Area 256411304 bytes
Fixed Size 730792 bytes
Variable Size 234881024 bytes
Database Buffers 20480000 bytes
Redo Buffers 319488 bytes
Database mounted.
# The undo required to recover the database to a consistent version is not
available. Note that this is not the 1555 error.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/ora01/oradata/DWF9i/undots_01.dbf'
SQL> shutdown abort;
ORACLE instance shut down.
# Rename the inactive undo tablespace back to the original name.
SQL> !mv /ora01/oradata/DWF9i/undots_01.dbf.bak
/ora01/oradata/DWF9i/undots_01.dbf
SQL> startup
ORACLE instance started.
Total System Global Area 256411304 bytes
Fixed Size 730792 bytes
Variable Size 234881024 bytes
Database Buffers 20480000 bytes
Redo Buffers 319488 bytes
Database mounted.
Database opened.
# Oracle can now see the undo, even if it is an inactive tablespace.
SQL> select count(*) from test_dbc where object_id < 42;
COUNT(*)
----------
40
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string undo_ts2
Regards,
Daniel Fink
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
