Back to Blog
DBAs and system architects know that Disaster Recovery (DR) is about more than just a failover.
What "v$datafile.last_change#" (Stop SCN) Means
As mentioned in my previous post here, before getting into the details of the famous ORA-01152 and ORA-01547 error messages there are a number of other areas that should be reviewed first. Once we have covered those off then these errors, both cause and resolution will make more sense.
We briefly touched on a datafile Stop SCN in the previous post, and here we would like to spend a little more time on the v$datafile.last_change# column. If you look at a dump of the controlfile, this is the “Stop scn” of the datafile - as stored in the datafile record section in this file. In the rest of the post, we will refer to it as the Stop SCN.
What is the Stop SCN (last_change#)?
In short we can state that the Stop SCN is the SCN number up to which there is redo information available for a datafile – according to the controlfile. Let’s start with a few basic examples that will help shed a bit more light on the above statement. We have a 12.1.0.2 database on which we performed a clean shutdown and startup. On reviewing v$datafile and v$datafile_header, we can see the following:
SQL> select file#, name, checkpoint_change#, last_change#, status, enabled from v$datafile;
Checkpoint Last
File# Name Change# Change# Status Enabled
------ ------------------------------------------ ------------- ------------ ---------- ----------
1 +DATA/DEV/DATAFILE/system.258.886973229 3887297 SYSTEM READ WRITE
2 +DATA/DEV/DATAFILE/soe.366.896447941 3887297 ONLINE READ WRITE
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 3887297 ONLINE READ WRITE
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 3887297 ONLINE READ WRITE
5 +DATA/DEV/DATAFILE/data01.dbf 3887297 ONLINE READ WRITE
6 +DATA/DEV/DATAFILE/users.259.886973275 3887297 ONLINE READ WRITE
6 rows selected.
SQL> select file#, name, checkpoint_change#, status, recover from v$datafile_header;
Checkpoint
File# Name Change# Status REC
------ ------------------------------------------ ----------------- ------------ ---
1 +DATA/DEV/DATAFILE/system.258.886973229 3887297 ONLINE NO
2 +DATA/DEV/DATAFILE/soe.366.896447941 3887297 ONLINE NO
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 3887297 ONLINE NO
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 3887297 ONLINE NO
5 +DATA/DEV/DATAFILE/data01.dbf 3887297 ONLINE NO
6 +DATA/DEV/DATAFILE/users.259.886973275 3887297 ONLINE NO
From the results above we can see that the checkpoint_change# in the controlfile matches the checkpoint in the datafile at this particular time and that the last_change# value is empty. It is empty when you look at v$datafile, but if you review the entry in the controlfile it will be “0xffff.ffffffff”
***************************************************************************
DATA FILE RECORDS
***************************************************************************
…
DATA FILE #5:
name #12: +DATA/DEV/DATAFILE/data01.dbf
…
Checkpoint cnt:159 scn: 0x0000.003b50c1 12/21/2015 15:10:30
Stop scn: 0xffff.ffffffff 12/21/2015 15:00:18
…
Now let’s take file 5 as shown above offline and and then review v$datafile and v$datafile_header:
SQL> alter database datafile 5 offline;
SQL> select file#, name, checkpoint_change#, last_change#, status, enabled from v$datafile;
Checkpoint Last
File# Name Change# Change# Status Enabled
------ ------------------------------------------ ------------- ------------ ---------- ----------
1 +DATA/DEV/DATAFILE/system.258.886973229 3895568 SYSTEM READ WRITE
2 +DATA/DEV/DATAFILE/soe.366.896447941 3895568 ONLINE READ WRITE
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 3895568 ONLINE READ WRITE
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 3895568 ONLINE READ WRITE
5 +DATA/DEV/DATAFILE/data01.dbf 3887297 3889337 RECOVER READ WRITE
6 +DATA/DEV/DATAFILE/users.259.886973275 3895568 ONLINE READ WRITE
6 rows selected.
SQL> select file#, name, checkpoint_change#, status, recover from v$datafile_header;
Checkpoint
File# Name Change# Status REC
------ ------------------------------------------ ----------------- ------------ ---
1 +DATA/DEV/DATAFILE/system.258.886973229 3895568 ONLINE NO
2 +DATA/DEV/DATAFILE/soe.366.896447941 3895568 ONLINE NO
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 3895568 ONLINE NO
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 3895568 ONLINE NO
5 +DATA/DEV/DATAFILE/data01.dbf 3887297 OFFLINE YES
6 +DATA/DEV/DATAFILE/users.259.886973275 3895568 ONLINE NO
6 rows selected.
To show you what is stored in the controlfile, let’s review a dump of the controlfile using dump level 3:
***************************************************************************
DATA FILE RECORDS
***************************************************************************
…
DATA FILE #5:
name #12: +DATA/DEV/DATAFILE/data01.dbf
…
Checkpoint cnt:159 scn: 0x0000.003b50c1 12/21/2015 15:10:30
Stop scn: 0x0000.003b58b9 12/21/2015 15:31:00
…
From the above extract of the datafile record section, we can see the “Stop scn” in hexadecimal is 0x0000.003b58b9 – which, when converted to decimal, is 3889337
Now let’s try and place the file online:
SQL> alter database datafile 5 online;
alter database datafile 5 online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '+DATA/DEV/DATAFILE/data01.dbf'
Most people would likely assume this to have been expected behavior. That is, the file cannot be placed online without doing recovery. But now the question becomes to what point does this file need to be recovered? Well, it needs to be recovered to a consistent state at which it can be opened, and the SCN number to which the file needs to be recovered to is the scn number specified by the Stop SCN. As mentioned earlier, that is the maximum SCN number for which redo information is available for this datafile. Many might think you need to recover the file to the current SCN of the database, but in fact you only need to recover it to this particular SCN. For example:
RMAN> recover datafile 5 until scn 3889337;
Starting recover at 21/12/2015:16:16:54
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=250 device type=DISK
starting media recovery
archived log for thread 1 with sequence 148 is already on disk as file +DATA/DEV/ARCHIVELOG/2015_12_21/thread_1_seq_148.651.899049839
archived log file name=+DATA/DEV/ARCHIVELOG/2015_12_21/thread_1_seq_148.651.899049839 thread=1 sequence=148
media recovery complete, elapsed time: 00:00:00
Finished recover at 21/12/2015:16:16:55
Now if we review v$datafile and v$datafile_header we see a few changes:
SQL> select file#, name, checkpoint_change#, last_change#, status, enabled from v$datafile;
Checkpoint Last
File# Name Change# Change# Status Enabled
------ ------------------------------------------ ------------- ------------ ---------- ----------
1 +DATA/DEV/DATAFILE/system.258.886973229 3921225 SYSTEM READ WRITE
2 +DATA/DEV/DATAFILE/soe.366.896447941 3921225 ONLINE READ WRITE
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 3921225 ONLINE READ WRITE
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 3921225 ONLINE READ WRITE
5 +DATA/DEV/DATAFILE/data01.dbf 3889337 3889337 OFFLINE READ WRITE
6 +DATA/DEV/DATAFILE/users.259.886973275 3921225 ONLINE READ WRITE
6 rows selected.
SQL> select file#, name, checkpoint_change#, status, recover from v$datafile_header;
Checkpoint
File# Name Change# Status REC
------ ------------------------------------------ ----------------- ------------ ---
1 +DATA/DEV/DATAFILE/system.258.886973229 3921225 ONLINE NO
2 +DATA/DEV/DATAFILE/soe.366.896447941 3921225 ONLINE NO
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 3921225 ONLINE NO
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 3921225 ONLINE NO
5 +DATA/DEV/DATAFILE/data01.dbf 3889337 OFFLINE NO
6 +DATA/DEV/DATAFILE/users.259.886973275 3921225 ONLINE NO
6 rows selected.
We can now see that the checkpoint_change# value for this particular datafile in the controlfile and the datafile matches with the Stop SCN (last_change#) – and notice that the “RECOVERY” flag in v$datafile_header has changed from YES to NO.
We can now attempt placing the file online:
SQL> alter database datafile 5 online;
Database altered.
We have recovered datafile 5 and it is back online again.
The Stop SCN is not only updated when a file is taken offline, but also when datafiles are put into read only mode. Example:
SQL> alter tablespace data read only;
Tablespace altered.
SQL> select file#, name, checkpoint_change#, last_change#, status, enabled from v$datafile;
Checkpoint Last
File# Name Change# Change# Status Enabled
------ ------------------------------------------ ------------- ------------ ---------- ----------
1 +DATA/DEV/DATAFILE/system.258.886973229 3921225 SYSTEM READ WRITE
2 +DATA/DEV/DATAFILE/soe.366.896447941 3921225 ONLINE READ WRITE
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 3921225 ONLINE READ WRITE
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 3921225 ONLINE READ WRITE
5 +DATA/DEV/DATAFILE/data01.dbf 3924165 3924165 ONLINE READ ONLY
6 +DATA/DEV/DATAFILE/users.259.886973275 3921225 ONLINE READ WRITE
6 rows selected.
SQL> select file#, name, checkpoint_change#, status, recover from v$datafile_header;
Checkpoint
File# Name Change# Status REC
------ ------------------------------------------ ----------------- ------------ ---
1 +DATA/DEV/DATAFILE/system.258.886973229 3921225 ONLINE NO
2 +DATA/DEV/DATAFILE/soe.366.896447941 3921225 ONLINE NO
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 3921225 ONLINE NO
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 3921225 ONLINE NO
5 +DATA/DEV/DATAFILE/data01.dbf 3924165 ONLINE NO
6 +DATA/DEV/DATAFILE/users.259.886973275 3921225 ONLINE NO
6 rows selected.
SQL> alter tablespace data read write;
Tablespace altered.
From the above we can see that when it is placed in read only mode, the Stop SCN (last_change) matches the checkpoint_ change# for the datafile in the controlfile and the datafile header, which means no recovery is needed to place it back into read-write mode. But what happens if we take the tablespace offline normal?
SQL> alter tablespace data offline normal;
Tablespace altered.
SQL> select file#, name, checkpoint_change#, last_change#, status, enabled from v$datafile;
Checkpoint Last
File# Name Change# Change# Status Enabled
------ ------------------------------------------ ------------- ------------ ---------- ----------
1 +DATA/DEV/DATAFILE/system.258.886973229 3956993 SYSTEM READ WRITE
2 +DATA/DEV/DATAFILE/soe.366.896447941 3956993 ONLINE READ WRITE
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 3956993 ONLINE READ WRITE
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 3956993 ONLINE READ WRITE
5 +DATA/DEV/DATAFILE/data01.dbf 3924572 3924572 OFFLINE DISABLED
6 +DATA/DEV/DATAFILE/users.259.886973275 3956993 ONLINE READ WRITE
6 rows selected.
SQL> select file#, name, checkpoint_change#, status, recover, error from v$datafile_header;
Checkpoint
File# Name Change# Status REC Error
------ ------------------------------------------ ----------------- ------------ --- -----------------
1 +DATA/DEV/DATAFILE/system.258.886973229 3995658 ONLINE NO
2 +DATA/DEV/DATAFILE/soe.366.896447941 3995658 ONLINE NO
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 3995658 ONLINE NO
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 3995658 ONLINE NO
5 0 OFFLINE OFFLINE NORMAL
6 +DATA/DEV/DATAFILE/users.259.886973275 3995658 ONLINE NO
6 rows selected.
The question now is: will recovery be required? …And the answer is NO.
SQL> alter tablespace data online;
Tablespace altered.
When you take a file offline “normal” the database will first perform a datafile checkpoint. The datafile will be consistent, as dirty buffers would have been written to the file before it was taken offline. So when the files are brought back online, no recovery will be required.
But if you take a file offline immediate, no datafile checkpoint is performed meaning that dirty buffers are discarded – requiring you to perform recovery on the file to bring it to a consistent state where you can place it online again.
The last point we would like to mention is also about the Stop SCN. When the database is shutdown with either immediate or normal, a checkpoint is performed, and the Stop SCN is written to the controlfile. The Stop SCN will match the checkpoint_change# for the datafile, so that during instance startup no recovery will be required. Example output showing this:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 322964208 bytes
Database Buffers 507510784 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> select file#, name, checkpoint_change#, last_change#, status, enabled from v$datafile;
Checkpoint Last
File# Name Change# Change# Status Enabled
------ ------------------------------------------ ------------- ------------ ---------- ----------
1 +DATA/DEV/DATAFILE/system.258.886973229 4223621 4223621 SYSTEM READ WRITE
2 +DATA/DEV/DATAFILE/soe.366.896447941 4223621 4223621 ONLINE READ WRITE
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 4223621 4223621 ONLINE READ WRITE
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 4223621 4223621 ONLINE READ WRITE
5 +DATA/DEV/DATAFILE/data01.dbf 4223621 4223621 ONLINE READ WRITE
6 +DATA/DEV/DATAFILE/users.259.886973275 4223621 4223621 ONLINE READ WRITE
6 rows selected.
SQL> select file#, name, checkpoint_change#, status, recover, error from v$datafile_header;
Checkpoint
File# Name Change# Status REC
------ ------------------------------------------ ----------------- ------------ ---
1 +DATA/DEV/DATAFILE/system.258.886973229 4223621 ONLINE NO
2 +DATA/DEV/DATAFILE/soe.366.896447941 4223621 ONLINE NO
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 4223621 ONLINE NO
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 4223621 ONLINE NO
5 +DATA/DEV/DATAFILE/data01.dbf 4223621 ONLINE NO
6 +DATA/DEV/DATAFILE/users.259.886973275 4223621 ONLINE NO
6 rows selected.
But if a database instance crashes or is shutdown with the abort option, there is no datafile checkpoint and the Stop SCN will not be written, meaning that during database instance startup, recovery will be performed – in this case, instance crash recovery. Below is an example providing more detail on the process:
SQL> select file#, name, checkpoint_change#, last_change#, status, enabled from v$datafile;
Checkpoint Last
File# Name Change# Change# Status Enabled
------ ------------------------------------------ ------------- ------------ ---------- ----------
1 +DATA/DEV/DATAFILE/system.258.886973229 4544754 SYSTEM READ WRITE
2 +DATA/DEV/DATAFILE/soe.366.896447941 4544754 ONLINE READ WRITE
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 4544754 ONLINE READ WRITE
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 4544754 ONLINE READ WRITE
5 +DATA/DEV/DATAFILE/data01.dbf 4544754 ONLINE READ WRITE
6 +DATA/DEV/DATAFILE/users.259.886973275 4544754 ONLINE READ WRITE
6 rows selected.
SQL> select file#, name, checkpoint_change#, status, recover, error from v$datafile_header;
Checkpoint
File# Name Change# Status REC
------ ------------------------------------------ ------------ -------- ---
1 +DATA/DEV/DATAFILE/system.258.886973229 4544754 ONLINE NO
2 +DATA/DEV/DATAFILE/soe.366.896447941 4544754 ONLINE NO
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 4544754 ONLINE NO
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 4544754 ONLINE NO
5 +DATA/DEV/DATAFILE/data01.dbf 4544754 ONLINE NO
6 +DATA/DEV/DATAFILE/users.259.886973275 4544754 ONLINE NO
6 rows selected.
SQL> select thread#, group#, sequence#, status, first_change#, next_change# from v$log;
THREAD# GROUP# SEQUENCE# Status FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------- ---------- ------------- -----------------
1 1 175 ACTIVE 4558938 4574638
1 2 176 CURRENT 4574638 281474976710655
1 3 174 ACTIVE 4544754 4558938
The above is the current state of the database prior to performing a shutdown abort. Next execute the shutdown abort, basically killing the instance. But what we see next when starting the database in a mounted state is where it gets interesting:
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 322964208 bytes
Database Buffers 507510784 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> select file#, name, checkpoint_change#, last_change#, status, enabled from v$datafile;
Checkpoint Last
File# Name Change# Change# Status Enabled
------ ------------------------------------------ ------------- ------------ ---------- ----------
1 +DATA/DEV/DATAFILE/system.258.886973229 4544754 SYSTEM READ WRITE
2 +DATA/DEV/DATAFILE/soe.366.896447941 4544754 ONLINE READ WRITE
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 4544754 ONLINE READ WRITE
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 4544754 ONLINE READ WRITE
5 +DATA/DEV/DATAFILE/data01.dbf 4544754 ONLINE READ WRITE
6 +DATA/DEV/DATAFILE/users.259.886973275 4544754 ONLINE READ WRITE
6 rows selected.
We can see that no Stop SCN (last_change#) was written to the controlfile. Also when looking at the datafile headers we see that the last checkpoint to the datafile headers was not updated and that the checkpoint_change# in this case, is the same as the oldest redo log group’s first_change# (group 3, sequence 174) which still has a status of “Active”.
SQL> select file#, name, checkpoint_change#, status, recover, error from v$datafile_header;
Checkpoint
File# Name Change# Status REC
------ ------------------------------------------ --------------- --------- ---
1 +DATA/DEV/DATAFILE/system.258.886973229 4544754 ONLINE NO
2 +DATA/DEV/DATAFILE/soe.366.896447941 4544754 ONLINE NO
3 +DATA/DEV/DATAFILE/sysaux.257.886973193 4544754 ONLINE NO
4 +DATA/DEV/DATAFILE/undotbs1.260.886973275 4544754 ONLINE NO
5 +DATA/DEV/DATAFILE/data01.dbf 4544754 ONLINE NO
6 +DATA/DEV/DATAFILE/users.259.886973275 4544754 ONLINE NO
6 rows selected.
SQL> select thread#, group#, sequence#, status, first_change#, next_change# from v$log;
THREAD# GROUP# SEQUENCE# Status FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- ---------- ---------- ------------- -----------------
1 1 175 ACTIVE 4558938 4574638
1 3 174 ACTIVE 4544754 4558938
1 2 176 CURRENT 4574638 281474976710655
It is important at this stage to briefly mention what the status column means with regards to the CURRENT and ACTIVE values. The online documentation states the following:
• CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed. • ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
Now let us have a look at what the last controlfile_change# was (latest checkpoint in the controlfile):
SQL> select checkpoint_change#, controlfile_change# from v$database;
Checkpoint Controlfile
Change# Change#
------------- -------------
4544754 4583955
From the above we can see that the controlfile_change# value when compared to the online redo logs are located in the current log group 2 which is in sequence 176. Based on the information above which includes:
• online redo logs - in an active state • the controlfile_change# - ahead of the datafile header checkpoint_change# • no Stop SCN - for the datafiles in the controlfile
From the above we can conclude that the database will be performing crash recovery during startup
When executing the “alter database open” command and reviewing the alert log, we can see how the crash recovery is started and the database is recovered – and in this case, until the controlfile_change# 4583955. Below is an extract from the alert log:
Mon Dec 21 22:27:53 2015
alter database open
Mon Dec 21 22:27:53 2015
Ping without log force is disabled
.
Mon Dec 21 22:27:53 2015
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Mon Dec 21 22:27:53 2015
Started redo scan
Mon Dec 21 22:27:53 2015
Completed redo scan
read 82903 KB redo, 21720 data blocks need recovery
Mon Dec 21 22:27:55 2015
Started redo application at
Thread 1: logseq 174, block 62943
Mon Dec 21 22:27:56 2015
Recovery of Online Redo Log: Thread 1 Group 3 Seq 174 Reading mem 0
Mem# 0: +DATA/DEV/ONLINELOG/group_3.267.886973317
Mem# 1: +DATA/DEV/ONLINELOG/group_3.268.886973317
Mon Dec 21 22:27:56 2015
Recovery of Online Redo Log: Thread 1 Group 1 Seq 175 Reading mem 0
Mem# 0: +DATA/DEV/ONLINELOG/group_1.263.886973315
Mem# 1: +DATA/DEV/ONLINELOG/group_1.264.886973315
Mon Dec 21 22:27:58 2015
Recovery of Online Redo Log: Thread 1 Group 2 Seq 176 Reading mem 0
Mem# 0: +DATA/DEV/ONLINELOG/group_2.265.886973317
Mem# 1: +DATA/DEV/ONLINELOG/group_2.266.886973317
Mon Dec 21 22:28:00 2015
Completed redo application of 32.16MB
Mon Dec 21 22:28:02 2015
Completed crash recovery at
Thread 1: logseq 176, block 44353, scn 4583955
21720 data blocks read, 21720 data blocks written, 82903 redo k-bytes read
…
…
AQPC started with pid=43, OS id=14544
Starting background process CJQ0
Mon Dec 21 22:28:05 2015
CJQ0 started with pid=46, OS id=14551
Completed: alter database open
Mon Dec 21 22:28:08 2015
This brings us to the next post in the series where I will talk a bit more about how using the controlfile checkpoint information, the datafile header minimum and maximum checkpoint information together with the Stop SCN (last_change#) value can be used to determine if recovery will be required, and if so, up to what SCN number before you can open a database.
Note: The above tests were run on an Oracle 12c (12.1.0.2) database running on Oracle Linux 7.1
IT Manager
Subscribe to our monthly blog updates
By subscribing, you are agreeing to have your personal information managed in accordance with the terms of DBVisit's Privacy Policy
