Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Delayed block cleanout and changing automatic undo tablespace

Re: Delayed block cleanout and changing automatic undo tablespace

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Tue, 20 Jul 2004 08:01:36 -0600
Message-id: <40FD25C0.4090601@sun.com>


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_at_freelists.org 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
-----------------------------------------------------------------
Received on Tue Jul 20 2004 - 08:59:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US