Jephe Wu - http://linuxtechres.blogspot.com
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
Posts
