redo undo and data consistency

the following text was copied from http://www.freelists.org/archives/oracle-l/12-2005/msg00537.html

=========================

When modify a table with normal insert/update/delete, then


1) redo for corresponding UNDO block change for this update is placed in redolog buffer
2) corresponding UNDO block is changed
3) redo for DATA block change for this update is placed in redolog buffer
4) DATA block itself is changed

Note that during buffer cache block changes we record the RBA (redo byte address in redo stream) of last change in buffer headers (can be seen from X$BH). RBA is an address in redo stream, it can uniquely identify a redo record/change vector in redologs.

As long as we don't commit (with sync option) here, no redo has to be written onto disk.

1) If instance crashes now, we don't have neither the redo nor datablock changes persisting, thus no inconsistencies
2) If instance crashes after LGWR has written the redo to disk, but DBWR hasn't written the datablocks, then during instance recovery we:
a) read old versions of datablocks from disk
b) apply redo to the old versions - note that Oracle guarantees every change vector between old version on disk and current version until commit to be in online redologs - e.g. it doesn't allow any redolog to be overwritten before all corresponding datablocks are checkpointed to disk.
c) this above is called cache recover - we make sure that all datafiles are physically consistent. After this point if we still have inconsistent/fractured blocks, we have encountered a hardware fault or software bug
d) database can be opened now, if any server processes see uncommitted datablocks, they can roll these back on their own - leaving the blocks they don't need for SMON (this is called fast start rollback if memory serves correctly)
e) SMON goes through undo segments to see whether there are uncommitted transactions (the committed transactions have a commit record at their end in redologs) and rolls those back (or hands those over to parallel slaves to roll back depending on transaction sizes)

3) It is not possible to have a situation where DBWR writes modified buffers back to disk before their redo is written. This is called write-ahead logging protocol and is mentioned in documentation. It is achieved by marking RBA of latest change to buffer header (as mentioned above) and DBWR checks whether redo up to max(RBA) in write block batch is already on disk (from memory structure underlying X$KCCCP).
If it's not, DBWR will signal LGWR and won't write those buffers to disk until LGWR acknowledges that redo is on disk.

So, as long as the foundations on what Oracle relies (OS, hardware) works properly and we don't hit any bugs (and unless I'm wrong) Oracle ALWAYS guarantees the blocks to be consistent after cache recovery in case of instance failure. It doesn't matter whether only half of DBWR's block write succeeded, or 99% or 1%, Oracle's recovery mechanisms are conceptually reliable.

All of above was a simplification, especially from 10g where we can have some redo log buffers in shared pool (private strands), some undo data cached in shared pool (in-memory undo), out-of order physical redo (multiple redolog strands and redo allocation latches), but again, I'm sure Oracle has based their recovery mechanisms on solid theoretical foundations here - no vulnerabilities on race conditions nor dependencies on which order you execute your stuff.

Tanel.

tablespace command

• create:

create tablespace mytablespace datafile '/path/to/file1.dbf' size 20m, '/path/to'file2.dbf' size 20m;

create tablespace

users

datafile

‘/ora01/oracle/oradata/booktst_users_01.dbf’ size 50m

autoextend on

next 10m

maxsize 100m;

• resize:

ALTER DATABASE DATAFILE /ora01/oracle/oradata/booktst_users_02.dbf’ resize 150M
alter tablespace data datafile '/path/to/file.dbf' resize 200M;

• alter
  

alter tabelspace data datafile '/path/to/file.dbf' autoextend on maxsize unlimited;

• move tablespace data file (2 ways: database way or tablespace way)

=====tablespace way========
note: tablespace must not be system tablespace
data file can be moved to new place during the instance is running.

1. set tablespace as offline. (alter tablespace tbsname offline;)
2. use OS commands to move file to new place(mv）
3. use alter tablespace command to rename filename in database (alter tablespace tbsname rename datafile '/path/to/file1.dbf' to '/path/to/file2.dbf';)
4. set tablespace status to online (alter tablespace tbsname online;)
   

======database way========
note: can be any data file (tablespace, online redo log file)

1. shutdown immediate
   
2. use OS commands to mv /path1/data1.dbf to /path2/data2.dbf
   
3. startup mount
   
4. alter database rename file '/path1/file1.dbf' to '/path2/file2.dbf'
5. alter database open;
   

• add:

alter tablespace name add datafile '/path/to/file.dbf' size 20m

• delete:

drop tablespace name
note: if tablespace got data inside, then using 'including contents and datafiles' keyword; this command won't delete tablespace datafile, you need to use operating system command to delete it.

• missing tablespace datafile, cannot start up oracle database:

Try the following:

shutdown abort
startup mount
alter database datafile '/directory/filename' offline drop;
alter database open;
drop tablespace ts_name;

In the above steps, you tell Oracle that the missing datafile is now missing (offline drop). You should then be able to open the database. Once open, you should be able to drop the tablespace with the missing datafile. You can then recreate the tablespace.