According to Tom,
system change number (SCN) is Oracle's
clock - every time we commit, the clock increments. The SCN just marks a
consistent point in time in the database.
Transactions are always happening in the
database, either user initiated or oracle implicit; hence, the SCN of database
would always keep progressing whenever these transactions are committed.
The following exercise aims to show the various
SCN numbers and their significance.
CURRENT_SCN : This is the current system change number
of database. It will keep change as a result of the various transactions
getting committed in the database. If database is not open, it is NULL. For Standby
database, it is current standby redo application SCN.
RESETLOGS_CHANGE# : It is the SCN when the last OPEN RESETLOGS
statement was issued.
This can be verified from the RMAN
repository.
RMAN> list
incarnation;
List of Database
Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN
Reset Time
------- -------
-------- ---------------- --- ---------- ----------
1 1
ORCL 1369005126 PARENT
1 13-AUG-2009 23:00:48
2 2
ORCL 1369005126 CURRENT 754488
03-MAR-2014 09:26:33
CHECKPOINT_CHANGE# : It is the SCN
at the last checkpoint. This changes whenever as checkpoint occurs.
CONTROLFILE_CHANGE# : it is the
SCN of when last time the controlfile was updated. This also changes at the
checkpoint, as the controlfile is updated by the checkpoint process.
ARCHIVELOG_CHANGE# : it the
highest V$ARCHIVED_LOG.NEXT_CHANGE#. This changes when
the log switch occurs and the redo logfile is archived.
SQL> select current_scn, resetlogs_change#, checkpoint_change#, controlfile_change#, archivelog_change# from v$database;
CURRENT_SCN
RESETLOGS_CHANGE# CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# ARCHIVELOG_CHANGE#
-----------
----------------- ------------------ ------------------- ----------------------------
1337349 754488 1336728
1336961 1336727
SQL> select sequence#, resetlogs_change#,
first_change#, next_change#,
first_time, next_time from v$archived_log;
SEQUENCE# RESETLOGS_CHANGE# FIRST_CHANGE#
NEXT_CHANGE# FIRST_TIM NEXT_TIME
----------
----------------- ------------- ------------ --------- ---------
7 754488 836239 849791 04-MAR-14 04-MAR-14
8 754488 849791 871897 04-MAR-14 05-MAR-14
9 754488 871897 899518 05-MAR-14 04-APR-14
10 754488 899518 927310 04-APR-14 05-APR-14
11 754488 927310 944887 05-APR-14 05-APR-14
12 754488 944887 977295 05-APR-14 08-APR-14
13 754488 977295 1024120 08-APR-14 09-APR-14
14 754488 1024120 1047871 09-APR-14 09-APR-14
15 754488 1047871 1059596 09-APR-14 09-APR-14
16 754488 1059596 1064794 09-APR-14 09-APR-14
17 754488 1064794 1069996 09-APR-14 09-APR-14
18 754488 1069996 1078366 09-APR-14 09-APR-14
19 754488 1078366 1102677 09-APR-14 09-APR-14
20 754488 1102677 1126438 09-APR-14 09-APR-14
21 754488 1126438 1133559 09-APR-14 09-APR-14
22 754488 1133559 1136571 09-APR-14 09-APR-14
23 754488 1136571 1141765 09-APR-14 09-APR-14
24 754488 1141765 1150843 09-APR-14 09-APR-14
25 754488 1150843 1161944 09-APR-14 09-APR-14
26 754488 1161944 1167053 09-APR-14 09-APR-14
27 754488 1167053 1179993 09-APR-14 09-APR-14
28 754488 1179993 1189333 09-APR-14 09-APR-14
29 754488 1189333 1212452 09-APR-14 09-APR-14
30 754488 1212452 1237939 09-APR-14 09-APR-14
31 754488 1237939 1258472 09-APR-14 10-APR-14
32 754488 1258472 1288770 10-APR-14 10-APR-14
33 754488 1288770 1311084 10-APR-14 10-APR-14
34 754488 1311084 1336727
10-APR-14
14-APR-14
28 rows selected.
Observing SCN
-
Check the current_scn,
checkpoint_change#, controlfile_change#
SQL> select current_scn, resetlogs_change#,
checkpoint_change#, controlfile_change#, controlfile_sequence#,
archivelog_change# from v$database;
CURRENT_SCN
RESETLOGS_CHANGE# CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_SEQUENCE#
ARCHIVELOG_CHANGE#
-----------
----------------- ------------------ ------------------- ---------------------
------------------
1337465 754488 1336728
1337459 2640 1336727
-
Force a
checkpoint
SQL> alter system checkpoint;
System altered.
-
Check the current_scn,
checkpoint_change#, controlfile_change#
SQL> select current_scn, resetlogs_change#,
checkpoint_change#, controlfile_change#, controlfile_sequence#,
archivelog_change# from v$database;
CURRENT_SCN
RESETLOGS_CHANGE# CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_SEQUENCE#
ARCHIVELOG_CHANGE#
-----------
----------------- ------------------ ------------------- ---------------------
------------------
1337485
754488 1337482 1337482 2641 1336727
Check the current_scn, checkpoint_change#,
controlfile_change#
Datafile SCN
The
datafile SCN information is stored in the controlfile and in the datafile
header. At each checkpoint, the datafile header and controlfile are updated of
this information. The V$DATAFILE view presents this information from the
controlfile.
CHECKPOINT_CHANGE# : It is the SCN
at the last checkpoint. This changes whenever as checkpoint occurs.
OFFLINE_CHANGE# : It is the
SCN when the datafile was last offline. Updated when the datafile is made
online
ONLINE_CHANGE# : it is the
SCN when the datafile was last made online.
Observing Datafile SCN
-
Check the current_scn,
checkpoint_change#, controlfile_change# from V$DATABASE
SQL> select current_scn, resetlogs_change#,
checkpoint_change#, controlfile_change#, controlfile_sequence#,
archivelog_change# from v$database;
CURRENT_SCN
RESETLOGS_CHANGE# CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_SEQUENCE#
ARCHIVELOG_CHANGE#
-----------
----------------- ------------------ ------------------- ---------------------
------------------
1339005 754488 1337482 1338762 2651 1336727
-
Check the
checkpoint_change# from V$DATAFILE
SQL> select file#, checkpoint_change#,
online_change#, offline_change#, plugin_change#, name from v$datafile;
FILE# CHECKPOINT_CHANGE# ONLINE_CHANGE#
OFFLINE_CHANGE# PLUGIN_CHANGE# NAME
----------
------------------ -------------- --------------- --------------
------------------------------------------------------------
1 1337482 754488 754487 0 /u01/app/oracle/oradata/orcl/system01.dbf
2 1337482 754488 754487 0
/u01/app/oracle/oradata/orcl/sysaux01.dbf
3 1337482 754488 754487 0
/u01/app/oracle/oradata/orcl/undotbs01.dbf
4 1337482 754488 754487 0
/u01/app/oracle/oradata/orcl/users01.dbf
-
Force
checkpoint
SQL> alter system
checkpoint;
System altered.
-
Check the current_scn,
checkpoint_change#, controlfile_change# from V$DATABASE
SQL> select current_scn, resetlogs_change#,
checkpoint_change#, controlfile_change#, controlfile_sequence#,
archivelog_change# from v$database;
CURRENT_SCN
RESETLOGS_CHANGE# CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_SEQUENCE#
ARCHIVELOG_CHANGE#
-----------
----------------- ------------------ ------------------- ---------------------
------------------
1339019 754488 1339016 1339016 2652 1336727
-
Check the
checkpoint_change# from V$DATAFILE
SQL> select file#,
checkpoint_change#, last_change#,
online_change#, offline_change#, plugin_change#, name from v$datafile;
FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
ONLINE_CHANGE# OFFLINE_CHANGE# PLUGIN_CHANGE# NAME
----------
------------------ ------------ -------------- --------------- --------------
------------------------------------------------------------
1 1339016 754488 754487 0
/u01/app/oracle/oradata/orcl/system01.dbf
2 1339016 754488 754487 0
/u01/app/oracle/oradata/orcl/sysaux01.dbf
3 1339016 754488 754487 0
/u01/app/oracle/oradata/orcl/undotbs01.dbf
4 1339016 754488 754487 0
/u01/app/oracle/oradata/orcl/users01.dbf
No comments:
Post a Comment