OraFAQ Forum: Server Administration » what's the difference between checkpoint_change# and controlfile

Home » RDBMS Server » Server Administration » what's the difference between checkpoint_change# and controlfile_change#?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

what's the difference between checkpoint_change# and controlfile_change#? [message #560941]

Tue, 17 July 2012 22:10

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# ?

Report message to a moderator

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

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 ?

Report message to a moderator

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

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 .

Report message to a moderator

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

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.

Report message to a moderator

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	enable row movement
Next Topic:	Statement auditing

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 29 02:49:22 CST 2024