Oracle database SCN and Recovery



Background:  Oracle 11.2 64bit with Rman
Objective: understanding the relationship between SCN and Rman recovery.


Diagram:  3 SCNs in controlfile and 1 SCN in individual datafile 






Cases:


1. when database is running normally, stop scn is null
SQL> show parameter FAST_START_MTTR_TARGET;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target     integer 0
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
  1828954

SQL> select name,checkpoint_change# from v$datafile where name like '%users01%';

NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE#
------------------
/home/oracle/app/oracle/oradata/testdb/users01.dbf
  1828954


SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE#
------------------
/home/oracle/app/oracle/oradata/testdb/users01.dbf
  1828954


SQL> select name,last_change# from v$datafile where name like '%users01%';

NAME
--------------------------------------------------------------------------------
LAST_CHANGE#
------------
/home/oracle/app/oracle/oradata/testdb/users01.dbf

2. clean shutdown shows all 4 numbers are same
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size    2211928 bytes
Variable Size  159387560 bytes
Database Buffers   50331648 bytes
Redo Buffers    5226496 bytes
Database mounted.
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
  1840147

SQL> select name,checkpoint_change# from v$datafile_header where name like '%users01%';

NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE#
------------------
/home/oracle/app/oracle/oradata/testdb/users01.dbf
  1840147


SQL> select name,checkpoint_change#,last_change# from v$datafile where name like '%users01%';

NAME
--------------------------------------------------------------------------------
CHECKPOINT_CHANGE# LAST_CHANGE#
------------------ ------------
/home/oracle/app/oracle/oradata/testdb/users01.dbf
  1840147 1840147

3. simulate a media failure which need media recovery

SQL> select name,checkpoint_change#,last_change# from v$datafile;

NAME



CHECKPOINT_CHANGE# LAST_CHANGE#

/home/oracle/app/oracle/oradata/testdb/system01.dbf

 

1846335
/home/oracle/app/oracle/oradata/testdb/sysaux01.dbf

 

1846335
/home/oracle/app/oracle/oradata/testdb/undotbs01.dbf

 

1846335
/home/oracle/app/oracle/oradata/testdb/users01.dbf

 

1846335
/home/oracle/app/oracle/oradata/testdb/example01.dbf

 

1846335

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
  1846335

RMAN> sql 'alter database datafile 4 offline';

sql statement: alter database datafile 4 offline

RMAN> restore datafile 4 ;

Starting restore at 04-AUG-12
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/app/oracle/oradata/testdb/users01.dbf
channel ORA_DISK_1: reading from backup piece

/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/05nhr2ul_1_1
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/05nhr2ul_1_1

tag=TAG20120804T214452
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 04-AUG-12



SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR     CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
4 OFFLINE OFFLINE
    1846297
04-AUG-12


SQL> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/testdb/users01.dbf'


SQL> set line 32000
SQL> r
  1* select checkpoint_change#,last_change#,file#,name from v$datafile

CHECKPOINT_CHANGE# LAST_CHANGE#      FILE# NAME

  1856063 1856063 1 /home/oracle/app/oracle/oradata/testdb/system01.dbf
  1856063 1856063 2 /home/oracle/app/oracle/oradata/testdb/sysaux01.dbf
  1856063 1856063 3 /home/oracle/app/oracle/oradata/testdb/undotbs01.dbf
  1846335 1850112 4 /home/oracle/app/oracle/oradata/testdb/users01.dbf
  1856063 1856063 5 /home/oracle/app/oracle/oradata/testdb/example01.dbf

SQL> select * from v$datafile_header;

     FILE# STATUS  ERROR FORMAT REC FUZ

CREATION_CHANGE# CREATION_ TABLESPACE_NAME TS# RFILE# RESETLOGS_CHANGE# RESETLOGS

CHECKPOINT_CHANGE# CHECKPOIN CHECKPOINT_COUNT      BYTES BLOCKS NAME



SPACE_HEADER   LAST_DEALLOC_CHA UNDO_OPT_CURRENT_CHANGE#

1 ONLINE     10 NO  NO

       7 15-AUG-09 SYSTEM   0      1  1815157 06-MAY-12  

  1856063 05-AUG-12      244  723517440  88320 /home/oracle/app/oracle/oradata/testdb/system01.dbf

 4194306

  1013911    1811966
2 ONLINE     10 NO  NO

    2140 15-AUG-09 SYSAUX   1      2  1815157 06-MAY-12  

  1856063 05-AUG-12      244  660602880  80640 /home/oracle/app/oracle/oradata/testdb/sysaux01.dbf

 8388610

  1808221
3 ONLINE     10 NO  NO

  942603 15-AUG-09 UNDOTBS1   2      3  1815157 06-MAY-12  

  1856063 05-AUG-12      169  110100480  13440 /home/oracle/app/oracle/oradata/testdb/undotbs01.dbf

 12582914

  1771742    1811966
4 OFFLINE     10 YES NO

   17993 15-AUG-09 USERS   4      4  1815157 06-MAY-12  

  1846297 04-AUG-12      241    5242880    640 /home/oracle/app/oracle/oradata/testdb/users01.dbf

 16777218
5 ONLINE     10 NO  NO

  973209 03-NOV-11 EXAMPLE   6      5  1815157 06-MAY-12  

  1856063 05-AUG-12      165  104857600  12800 /home/oracle/app/oracle/oradata/testdb/example01.dbf

 20971522

  1387524

[oracle@jephe ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 5 02:11:28 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2565863629)

RMAN> recover datafile 4;

Starting recover at 05-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 05-AUG-12

RMAN> exit


Recovery Manager complete.
[oracle@jephe ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 5 02:11:41 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database datafile 4 online;

Database altered.
SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
  1856066

SQL> select file#,name,checkpoint_change#,last_change# from v$datafile;

     FILE# NAME

 

CHECKPOINT_CHANGE# LAST_CHANGE#
----------

1 /home/oracle/app/oracle/oradata/testdb/system01.dbf



1856066
2 /home/oracle/app/oracle/oradata/testdb/sysaux01.dbf



1856066
3 /home/oracle/app/oracle/oradata/testdb/undotbs01.dbf



1856066
4 /home/oracle/app/oracle/oradata/testdb/users01.dbf



1856340
5 /home/oracle/app/oracle/oradata/testdb/example01.dbf



1856066


SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
  1856385

SQL> select file#,name,checkpoint_change#,last_change# from v$datafile;

     FILE# NAME

 

CHECKPOINT_CHANGE# LAST_CHANGE#

1 /home/oracle/app/oracle/oradata/testdb/system01.dbf



1856385
2 /home/oracle/app/oracle/oradata/testdb/sysaux01.dbf



1856385
3 /home/oracle/app/oracle/oradata/testdb/undotbs01.dbf



1856385
4 /home/oracle/app/oracle/oradata/testdb/users01.dbf



1856385
5 /home/oracle/app/oracle/oradata/testdb/example01.dbf



1856385


SQL> select * from v$recover_file;

no rows selected


4.  check controlfile content 
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> alter session set events 'immediate trace name controlf level 9';

Session altered.

SQL> oradebug tracefile_name;
/home/oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_12972.trc
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


[oracle@jephe ~]$ grep ' Database checkpoint' /home/oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_12972.trc
 Database checkpoint: Thread=1 scn: 0x0000.001c5381
[oracle@jephe ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 5 03:17:30 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#
------------------
  1856385


[oracle@jephe ~]$ egrep "ENTRY|RECORD"

/home/oracle/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_ora_12972.trc
DATABASE ENTRY
CHECKPOINT PROGRESS RECORDS
EXTENDED DATABASE ENTRY
REDO THREAD RECORDS
LOG FILE RECORDS
DATA FILE RECORDS
TEMP FILE RECORDS
TABLESPACE RECORDS
RMAN CONFIGURATION RECORDS
FLASHBACK LOGFILE RECORDS
THREAD INSTANCE MAPPING RECORDS
MTTR RECORDS
STANDBY DATABASE MAP RECORDS
RESTORE POINT RECORDS
ACM SERVICE RECORDS
LOG FILE HISTORY RECORDS
OFFLINE RANGE RECORDS
ARCHIVED LOG RECORDS
FOREIGN ARCHIVED LOG RECORDS
BACKUP SET RECORDS
BACKUP PIECE RECORDS
BACKUP DATAFILE RECORDS
BACKUP LOG RECORDS
DATAFILE COPY RECORDS
BACKUP DATAFILE CORRUPTION RECORDS
DATAFILE COPY CORRUPTION RECORDS
DELETION RECORDS
PROXY COPY RECORDS
INCARNATION RECORDS
RMAN STATUS RECORDS
DATAFILE HISTORY RECORDS
NORMAL RESTORE POINT RECORDS
DATABASE BLOCK CORRUPTION RECORDS