Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

checkpoint_change# in v$log?v$database?v$datafile

Status
Not open for further replies.

signalsys

Technical User
Sep 5, 2005
44
CN
checkpoint_change# in v$log?v$database?v$datafile
environment: AIX5206+ORACLE9204 RAC
First I supply part of the v$datafile:
SQL> select file#,checkpoint_change#,checkpoint_time,name from v$datafile;
566 7751870163147 20060124 07:22:37 /dev/ryz132047
567 7751869549130 20060124 07:00:08 /dev/ryz144028
568 7751870160664 20060124 07:22:22 /dev/ryz182019
569 7751870160664 20060124 07:22:22 /dev/ryz182020
570 7751869549130 20060124 07:00:08 /dev/ryz174008
572 7751869549130 20060124 07:00:08 /dev/ryz174019
577 7751870160664 20060124 07:22:22 /dev/ryz182024
587 7751870173476 20060124 07:23:08 /dev/ryz174026
588 7751870173834 20060124 07:23:10 /dev/ryz182027

Then the v$log
SQL> select group#,thread#,sequence#,archived,status,first_change#,first_time from v$log;

GROUP# THREAD# SEQUENCE# ARC STATUS FIRST_CHANGE# FIRST_TI
ME
--------------------- --------------------- --------------------- --- ---------- --------------------- --------
---------
1 1 37469 YES ACTIVE 7751870052187 20060124
07:16:32
2 1 37470 NO CURRENT 7751870241800 20060124
07:28:20
3 1 37465 YES INACTIVE 7751869423632 20060124
06:58:40
9 1 37467 YES INACTIVE 7751869546171 20060124
07:00:05
4 1 37466 YES INACTIVE 7751869486942 20060124
06:59:22
10 1 37468 YES INACTIVE 7751869695869 20060124
07:02:46
5 2 14735 YES INACTIVE 7751869183769 20060124
06:55:56
6 2 14736 YES INACTIVE 7751869360125 20060124
06:58:02
7 2 14737 YES ACTIVE 7751869549130 20060124
07:00:08
12 2 14734 YES INACTIVE 7751868735312 20060124
06:47:08
11 2 14733 YES INACTIVE 7751868083287 20060124
06:34:41

GROUP# THREAD# SEQUENCE# ARC STATUS FIRST_CHANGE# FIRST_TI
ME
--------------------- --------------------- --------------------- --- ---------- --------------------- --------
---------
8 2 14738 NO CURRENT 7751870242377 20060124

Then the v$database:
SQL> select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;

NAME OPEN_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
--------- ---------- ------------------ ------------------
SHYZ READ WRITE 7751869549130 7751869695869

I have some question about the above output:

1. Why the checkpoint_change# of each datafile in v$datafile are not equal ? I think after log switch happens, a checkpoint will enforce on all datafiles and every datafile will have the same new checkpoint_change#.

2. In the v$log, log sequence 37468 is already archived and in inactive status, that is to say log
37468 is no longer needed for instance recovery. its first checkpoint_change# is 7751869695869 which is
larger than checkpoint_change# of file #567,#570 and #572(7751869549130). I think since a log is in
archived and inactive status all datafiles' checkpoint_change# should be larger than the log's
checkpoint_change#. But the case is not, so i am very puzzled.

3. What the CHECKPOINT_CHANGE# in v$database stand for , the smallest checkpoint_change# in v$datafile ?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top