I am an IT professional with 10 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

Tuesday, February 24, 2009

Offline datafile always require recovery when it turns to be online

There is a little confusion about offline datafile that it is always required recovery when it turns to be online,unlike with offline tablespace which never requires recovery after turn it to be online under some cirumstances.To understand Why it happens requires a little attention towards checkpoint event in oracle. Whenever checkpoints occur, Oracle writes a START SCN to the data file headers as well it writes the START SCN within controlfile for each datafile.The control file also maintains another SCN value for each data file with START SCN is the STOP SCN,this STOP SCN is usually null (infinity) when yours database status is open and datafile status is online, the null status shows that recovery will start from START SCN and will goes to the end of redo thread. Here is an excerpt which is showing from SQL
SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                          CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------ ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF             371976              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF            371976              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF             371976              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF              371976              ONLINE
F:\ORACLE\..\PROD\UNDO0201.DBF             371976              ONLINE
Here CHECKPOINT_CHANGE# is START SCN 371976 and LAST_CHANGE# is STOP SCN for each datafile header within controlfile. As you can see the LAST_CHANGE# (STOP SCN) is null which shows the data file is in open state (online).Whenever you shutdown the database normally a checkpoint occurs and the LAST_CHANGE# (STOP SCN) is updated with CHECKPOINT_CHANGE# (START SCN) which tell the mechanism that no recovery required whenver the database started again. Here is an excerpt which i am showing from SQL
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                          CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------ ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF             372600       372600 SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF            372600       372600 ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF             372600       372600 ONLINE
F:\ORACLE\..\PROD\USERS01.DBF              372600       372600 ONLINE
F:\ORACLE\..\PROD\UNDO0201.DBF             372600       372600 ONLINE
See above CHECKPOINT_CHANGE# (START SCN)=LAST_CHANGE# (STOP SCN). Whenever there is mismatch between CHECKPOINT_CHANGE# (START SCN) and LAST_CHANGE# (STOP SCN) then oracle requires recovery. Here is an excerpt which is showing from SQL
SQL> shutdown abort
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                           CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------- ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF              372601              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF             372601              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF              372601              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF               372601              ONLINE
F:\ORACLE\..\PROD\UNDO0201.DBF              372601              ONLINE
See above LAST_CHANGE# (STOP SCN) is null after shutting the database abnormally ,it is set to NULL (i.e NO cehckpoint STOP SCN) updated with CHECKPOINT_CHANGE# (START SCN). when you open the database after that event instance recovery started automatically. Now come to the point for which we started the topic that offline datafile always requires recovery whenver we turn it back to online while the same not happen with tablespace in some circumstances.The reason is that when we offline the tablespace a checkpoint occurs for alls datafile within this tablespaces and synchronized the controlfile's datafile header LAST_CHANGE# (STOP SCN) with CHECKPOINT_CHANGE# (START SCN).Upon turning back to online the datafile within tablespace will not require recovery. You can offline tablespace with three parameters
Normal : You use Normal parameter when yours any datafile don't contain any error the database takes a checkpoint for all datafiles of the tablespace as it takes them offline. NORMAL is the default.Media recovery dont require when bring back to online.
Temporary : You use Temporary parameter when one of yours datafile contains error media recovery require if yours datafile which is turn to be offline using TEMPORARY parameter has some error,if not then no media recovery requires.
Immediate : You use Immediate parameter when yours database in archivelog and after taking the tablespace offline using Immediate parameter then no checkpoint occurs and the tablespace datafile require recovery when it will bring back to online
Here is an excerpt which is showing from SQL.
SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                           CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------- ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF              392812              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF             392812              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF              392812              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF               392812              ONLINE
F:\ORACLE\..\PROD\UNDO0201.DBF              392812              ONLINE

SQL> alter tablespace users offline normal
  2  /

Tablespace altered.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                          CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------ ----------------- ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF            392812              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF           392812              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF            392812              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF             393058       393058 OFFLINE
F:\ORACLE\..\PROD\UNDO0201.DBF            392812              ONLINE

SQL> alter system switch logfile
  2  /

System altered.

SQL> /

System altered.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                           CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------- ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF              393076              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF             393076              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF              393076              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF               393058       393058 OFFLINE
F:\ORACLE\..\PROD\UNDO0201.DBF              393076              ONLINE
You can see that CHECKPOINT_CHANGE# (START SCN)=LAST_CHANGE# (STOP SCN) for the tabelspace USERS datafile F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF (399058).
SQL> alter tablespace users online
  2  /

Tablespace altered.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                            CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
-------------------------------- ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF               393076              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF              393076              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF               393076              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF                393121              ONLINE
F:\ORACLE\..\PROD\UNDO0201.DBF               393076              ONLINE
But its not the case with datafile scenario when it turns to be offline and then bring back to online,it will always require recovery cause there is no checkpoint occurence process occurs for the datafile and in turn LAST_CHANGE# (STOP SCN) dont synchronize with CHECKPOINT_CHANGE# (START SCN).Oracle will always check this two datafile SCN (START AND STOP) whenver yours database started or you turn back the offline tablespace or datafile to be online. Here is an excerpt which is showing from SQL.
SQL> alter database datafile 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF' offline
  2  /

Database altered.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                           CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------- ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF              393076              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF             393076              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF              393076              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF               393121       393263 RECOVER
F:\ORACLE\..\PROD\UNDO0201.DBF              393076              ONLINE 
You can see that CHECKPOINT_CHANGE# (START SCN)<>LAST_CHANGE# (STOP SCN) for the tabelspace USERS datafile F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF.Both are not synchronized at the time of datafile to turn it to offline,when you will turn back to online it will require recovery ,notice the file status is RECOVER which is not the case with at the time of tablespaces to make it offline. Here is an excerpt which is showing from SQL.
SQL> alter database datafile 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF' online
  2  /
alter database datafile 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF' online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF'


SQL> recover datafile 4
Media recovery complete.
SQL> alter database datafile 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF' online
  2  /

Database altered.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                           CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------- ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF              393076              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF             393076              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF              393076              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF               393452              ONLINE
F:\ORACLE\..\PROD\UNDO0201.DBF              393076              ONLINE

Followers

About Me

My photo
Melbourne, Victoria, Australia