Do Not Try This at Work

I ran this example on my home PC, which has Oracle 10 installed purely for testing and educational purposes. You should not try it at work, at least not if you want to keep your job. I started doing Oracle DBA work in the late 1990's after several years as a COBOL programmer. An electrician pulled the plug on one of our production servers. Once the power had been restored, I started the databases and raised a TAR with Oracle to ask if this had done any damage. I was told that Oracle would save anything which had been committed and roll back uncommitted transactions. This was fortunate as a few days later he did the same thing again. I decided to recreate this scenario for my blog. First I created a table, added 1527 rows and did a commit:

SQL> create table andrews_table
  2  as select * from dba_tables
  3  where 1 = 2
  4  /

Table created.

SQL> insert into andrews_table
  2  select * from dba_tables
  3  /

1527 rows created.

SQL> commit
  2  /

Commit complete.

SQL> select count(*) from andrews_table
  2  /

  COUNT(*)
----------
      1527

SQL>

I would expect these rows to be saved if the computer's power was switched off. Then I duplicated the table's contents:

SQL> insert into andrews_table
  2  select * from andrews_table
  3  /

1527 rows created.

SQL> select count(*) from andrews_table
  2  /

  COUNT(*)
----------
      3054

SQL>

I would not expect these new rows to be saved if the computer's power was switched off i.e.
the table should have 1527 rows, not 3054. Next I turned the power off and on and restarted the database:

[oracle@localhost crash_recovery]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 26 18:54:46 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              79694068 bytes
Database Buffers           83886080 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>

Looking in the alert log, Oracle did a crash recovery:

Sun Feb 26 18:55:00 2012
Beginning crash recovery of 1 threads
Sun Feb 26 18:55:00 2012
Started redo scan
Sun Feb 26 18:55:01 2012
Completed redo scan
 35 redo blocks read, 22 data blocks need recovery
Sun Feb 26 18:55:01 2012
Started redo application at
 Thread 1: logseq 48, block 5057
Sun Feb 26 18:55:01 2012
Recovery of Online Redo Log: Thread 1 Group 2 Seq 48 Reading mem 0
  Mem# 0 errs 0: /home/oracle/andrew/ANDREW/redo02.log
Sun Feb 26 18:55:01 2012
Completed redo application
Sun Feb 26 18:55:01 2012
Completed crash recovery at
 Thread 1: logseq 48, block 5092, scn 1087729
 22 data blocks read, 22 data blocks written, 35 redo blocks read

... and when I looked in the table, it had the expected number of rows:

SQL> select count(*) from andrews_table
  2  /

  COUNT(*)
----------
      1527

SQL>