Role of SCN in Instance Recovery & Media Recovery

In last post we have discussed the different types of checkpoints and association of SCN. Also we have examined the status of datafile headers wrt End (checkpoint) SCN after a clean shutdown. In this post we are examining role of SCN in instance recovery and media recovery.

SCN after an Instance Crash

In this session we are going to demonstrate the behavior of the checkpoints after an instance crash, the following SQL creates a table (which performs an implicit commit) and inserts a row of data into it without a commit:

SQL> create table dummytable (x number) tablespace test;
Table created.

SQL> insert into dummytable values(100);
1 row created.


During subsequent startup after instance crash Oracle will realize before opening database that value for Last_Change# column for datafiles are not showing SCN value , which means END Checkpoint SCN was not done, which would be indication for Oracle that instance recovery is required.

Let’s mimic instance failure and then startup mount to examine the situation

Read more of this post

Pierre blog

Pierre Forstmann Oracle Database blog

flashdba

Oracle databases, storage and the high-performance world of flash memory

Future Veterans

Ramblings about Oracle

Ranjeet Srivastava

Smile! You’re at the best blog ever

Kevin Closson's Blog: Platforms, Databases and Storage

Platform, Database and Storage Topics

Real Life Database / SQL Experiences : An Oracle Blog from Vivek Sharma

Being an Oracle Professional, I like to share all my Real Life Performance Tuning Challenges and Experiences. The Content and Views on this site are my own and not necessarily those of Oracle. While, I write on my real life experiences, the resolutions mentioned are solely mine. Comments / Criticisms are always a welcome.

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

ASM Support Guy

Just Another Crazy Oracle DBA

Exadata Certification

Just Another Crazy Oracle DBA

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Sangram keshari's Oracle Blog

The Fusion Middleware Administration & Database Administration Blog

Amit Saraswat

Just Another Crazy Oracle DBA

Oracle Scratchpad

Just another Oracle weblog

The Tom Kyte Blog

Just Another Crazy Oracle DBA

Hemant's Oracle DBA Blog

Just Another Crazy Oracle DBA

Uwe Hesse

about Database Technology

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music