Home » RDBMS Server » Server Administration » what's the difference between checkpoint_change# and controlfile_change#?
what's the difference between checkpoint_change# and controlfile_change#? [message #560941] Tue, 17 July 2012 22:10 Go to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
SQL> select checkpoint_change#,controlfile_change# from v$database;
 
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE#
------------------ -------------------
            203454              204955
 


yeah ,i know read the document ,but i still can't understand it . please tell me what's the difference between checkpoint_change# and controlfile_change#.
what's the checkpoint_change# use for ? does it use for recover ?
what's the controlfile_change# use for ?
when the controlfile_change# will be increase ?

SQL> select controlfile_sequence# from v$database;
 
CONTROLFILE_SEQUENCE#
---------------------
                  293
 

Qs.) what is controlfile_sequence# ?
Re: what's the difference between checkpoint_change# and controlfile_change#? [message #560971 is a reply to message #560941] Wed, 18 July 2012 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
checkpoint_change# gives the scn of the last checkpoint, it indicates to which point in time (scn) the files were updated.
controlfile_change# gives the scn of the last control file update, it indicates at which point in time (scn) the control file was last updated.
controlfile_sequence# gives the transaction number of the last control file change. It is control file transaction and has no relation with scn.

Checkpoint updates the control file (and so increment controlfile_sequence#) but control file can be updated by other events (for instance, rman update the control file).

SQL> select CHECKPOINT_CHANGE#, CONTROLFILE_CHANGE#, CONTROLFILE_SEQUENCE#, CURRENT_SCN
  2  from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_SEQUENCE# CURRENT_SCN
------------------ ------------------- --------------------- -----------
          30327451            30406500                 15613    30423178

1 row selected.

SQL> alter system checkpoint;

System altered.

SQL> select CHECKPOINT_CHANGE#, CONTROLFILE_CHANGE#, CONTROLFILE_SEQUENCE#, CURRENT_SCN
  2  from v$database;
CHECKPOINT_CHANGE# CONTROLFILE_CHANGE# CONTROLFILE_SEQUENCE# CURRENT_SCN
------------------ ------------------- --------------------- -----------
          30423181            30423181                 15614    30423183


Regards
Michel

[Edit: change to be more specific and, I think, correct, and add example.]

[Updated on: Wed, 18 July 2012 01:22]

Report message to a moderator

Re: what's the difference between checkpoint_change# and controlfile_change#? [message #560979 is a reply to message #560971] Wed, 18 July 2012 01:51 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
Hi,Michel Cadot.Thank u for reply.

controlfile_change# gives the scn of the last control file update, it indicates at which point in time (scn) the control file was last updated.
>>this answer is very helpful for me .

yeah ,i also found update controlfile would increase the controlfile_change#.there are some ways following:
·CREATE OR DROP TABLESPACE
·ADD LOGFILE OR DROP LOGFILE ,ADD LOG GROUP OR DROP LOG GROUP
·'ALTER TABLESPACE ONLINE' OR 'ALTER TABLESPACE OFFLINE';
·ALTER SYSTEM CHECKPOINT ; >>At this point ,CHECKPOINT_CHANGE# = CONTROLFILE_CHANGE#
·ALTER SYSTEM SWITCH LOGFILE ; >>At this point ,CHECKPOINT_CHANGE# = CONTROLFILE_CHANGE#
·RMAN> LIST BACKUPSET; (Question 1:this operation will increase the controlfile_change#,but i don't know why ? it also update the controlfile ??)

Question 2:
what's the controlfile_change# use for ?
Re: what's the difference between checkpoint_change# and controlfile_change#? [message #560984 is a reply to message #560979] Wed, 18 July 2012 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) Because rman has to lock the control file to list the backupset
2) It is used during recovery to know up to which point in time the control file information are valid. If you want to recover up to a scn before control file change then it is OK, if you want to recover to a scn after the control file scn then Oracle will ask you to specify the option "using backup controlfile".

Regards
Michel

[Edit: typo]

[Updated on: Wed, 18 July 2012 03:20]

Report message to a moderator

Re: what's the difference between checkpoint_change# and controlfile_change#? [message #560992 is a reply to message #560984] Wed, 18 July 2012 03:13 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
it make sense to me . thank u .

in addition, can u provide some article or web link about these for me ,i want to know more details about these ? thank u advance .
Re: what's the difference between checkpoint_change# and controlfile_change#? [message #561046 is a reply to message #560984] Wed, 18 July 2012 08:46 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
hi,Michel Cadot .i found a problem.
you know there have an action which called 'heartbeat' in controlfile. heartbeat update the controlfile every 3s. the following can query the heartbeat:
SQL> SELECT CHPBT FROM X$KCCCP;


My Question:
When heartbeat increase every 3s(controlfile is updated) ,why the controlfile_change# doesn't increase.
Re: what's the difference between checkpoint_change# and controlfile_change#? [message #561156 is a reply to message #561046] Thu, 19 July 2012 02:58 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry I didn't see you posted.
It is more a question to ask to Oracle but I don't think hearbeat updates the control file, X$KCCCP shows the cache of the control file (actually a part of it) not the control file itself. I think it is written to the control file only when an operation has to be done that implies a control file update (switch log or checkpoint for instance).

Regards
Michel
Previous Topic: enable row movement
Next Topic: Statement auditing
Goto Forum:
  


Current Time: Thu Mar 28 23:55:49 CDT 2024