如何查看此时的SCN?

lonion

Know more about checkpoint

checkpoint 分成很多种  full 、file、thread、parallel query、 object 、incremental 、logfile switch

每一种checkpoint 都有其自身的特性，例如Incremental Checkpoint会要求ckpt 每3s 更新一次controlfile 但是不更新datafile header， 而FULL CHECKPOINT要求立即完成(同步的) 且会同时更新 controlfile 和 datafile header。

Incremental Checkpoint会要求ckpt 每3s 更新一次controlfile
>>我想问的时：如何查看此时控制文件中更新的SCN?除了DUMP控制文件，有没有命令查询?

jxztj2010

等高手解释，我还是看不懂

aks5201314

select current_scn from v$database可以看当前的SCN

Maclean Liu(刘相兵

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug dump controlf 4;
oradebug tracefile_name ;Statement processed.
SQL>
/s01/admin/G10R25/udump/g10r25_ora_4660.trc






SQL>  exec dbms_lock.sleep(6);
oradebug setmypid;
oradebug dump controlf 4;
oradebug tracefile_name ;

PL/SQL procedure successfully completed.

SQL> Statement processed.
SQL> Statement processed.
SQL> /s01/admin/G10R25/udump/g10r25_ora_4663.trc



[oracle@vrh8 udump]$ diff /s01/admin/G10R25/udump/g10r25_ora_4660.trc /s01/admin/G10R25/udump/g10r25_ora_4663.trc
1c1
< /s01/admin/G10R25/udump/g10r25_ora_4660.trc
---
> /s01/admin/G10R25/udump/g10r25_ora_4663.trc
13c13
< Unix process pid: 4660, image: oracle@vrh8.oracle.com (TNS V1-V3)
---
> Unix process pid: 4663, image: oracle@vrh8.oracle.com (TNS V1-V3)
15,18c15,19
< *** ACTION NAME:() 2012-07-22 07:59:08.215
< *** MODULE NAME:(sqlplus@vrh8.oracle.com (TNS V1-V3)) 2012-07-22 07:59:08.215
< *** SERVICE NAME:(SYS$USERS) 2012-07-22 07:59:08.215
< *** SESSION ID:(159.7) 2012-07-22 07:59:08.215
---
> *** 2012-07-22 07:59:31.779
> *** ACTION NAME:() 2012-07-22 07:59:31.779
> *** MODULE NAME:(sqlplus@vrh8.oracle.com (TNS V1-V3)) 2012-07-22 07:59:31.779
> *** SERVICE NAME:(SYS$USERS) 2012-07-22 07:59:31.779
> *** SESSION ID:(159.9) 2012-07-22 07:59:31.779
96,98c97,99
< THREAD #1 - status:0x2 flags:0x0 dirty:56
< low cache rba:(0x1a.3.0) on disk rba:(0x1a.121.0)
< on disk scn: 0x0000.013fe7a8 07/22/2012 07:59:02
---
> THREAD #1 - status:0x2 flags:0x0 dirty:57
> low cache rba:(0x1a.3.0) on disk rba:(0x1a.148.0)
> on disk scn: 0x0000.013fe7c2 07/22/2012 07:59:27
100,101c101,102
< heartbeat: 789262462 mount id: 2675014163
< Flashback log tail log# 15 thread# 1 seq 229 block 274 byte 0
---
> heartbeat: 789262470 mount id: 2675014163
> Flashback log tail log# 15 thread# 1 seq 229 block 275 byte 0
2490c2491
<   V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288
---
>   V$RMAN_STATUS: recid=140733792718464, stamp=140733792718456
2501c2502
<   V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288
---
>   V$RMAN_STATUS: recid=140733792718464, stamp=140733792718456
2511c2512
<   V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288
---
>   V$RMAN_STATUS: recid=140733792718464, stamp=140733792718456
2521c2522
<   V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288
---
>   V$RMAN_STATUS: recid=140733792718464, stamp=140733792718456
2531c2532
<   V$RMAN_STATUS: recid=140734752341296, stamp=140734752341288
---
>   V$RMAN_STATUS: recid=140733792718464, stamp=140733792718456




存在差异的地方包括： CHECKPOINT PROGRESS RECORDS 这是因为 ckpt 每3s一次对controlfile做heartbeat 更新 CHECKPOINT PROGRESS RECORDS

第一次 controlf dump

***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
(size = 8180, compat size = 8180, section max = 11, section in-use = 0,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 2, numrecs = 11)
THREAD #1 - status:0x2 flags:0x0 dirty:56
low cache rba:(0x1a.3.0) on disk rba:(0x1a.121.0)
on disk scn: 0x0000.013fe7a8 07/22/2012 07:59:02
resetlogs scn: 0x0000.01394f1a 07/19/2012 07:27:21
heartbeat: 789262462 mount id: 2675014163
Flashback log tail log# 15 thread# 1 seq 229 block 274 byte 0
THREAD #2 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00
heartbeat: 0 mount id: 0
Flashback log tail log# 0 thread# 0 seq 0 block 0 byte 0


第二次 controlf dump


***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
(size = 8180, compat size = 8180, section max = 11, section in-use = 0,
  last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 2, numrecs = 11)
THREAD #1 - status:0x2 flags:0x0 dirty:57
low cache rba:(0x1a.3.0) on disk rba:(0x1a.148.0)
on disk scn: 0x0000.013fe7c2 07/22/2012 07:59:27
resetlogs scn: 0x0000.01394f1a 07/19/2012 07:27:21
heartbeat: 789262470 mount id: 2675014163
Flashback log tail log# 15 thread# 1 seq 229 block 275 byte 0
THREAD #2 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
on disk scn: 0x0000.00000000 01/01/1988 00:00:00
resetlogs scn: 0x0000.00000000 01/01/1988 00:00:00



差异在于
on disk rba
on disk scn
heartbeat
Flashback log tail log#



实际并不更新一个当前"SCN"(CURRENT SCN) ， 如果说更新SCN的话 那么只是更新 ON DISK SCN


如果查看该ON DISK SCN?


SQL> select cpods from x$kcccp;

CPODS
----------------
20966431
0
0
0
0
0
0
0

8 rows selected.

SQL>
SQL> exec dbms_lock.sleep(3);

PL/SQL procedure successfully completed.

SQL> select cpods from x$kcccp;

CPODS
----------------
20966440
0
0
0
0
0
0
0

8 rows selected.

SQL> exec dbms_lock.sleep(3);

PL/SQL procedure successfully completed.

SQL> select cpods from x$kcccp;

CPODS
----------------
20966450
0
0
0
0
0
0
0

Maclean Liu(刘相兵

请参考这篇文章
       
增量检查点如何更新控制文件?
http://www.askmaclean.com/archiv ... te-controlfile.html