Re: Delayed block cleanout and changing automatic undo tablespace

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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: