Delayed block cleanout and changing automatic undo tablespace 2004-07-20 - By Daniel Fink
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 (See ORA-01157.ora-code.com): cannot identify/lock data file 2 - see DBWR trace file
ORA-01110 (See ORA-01110.ora-code.com): 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@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
