select * from v$diag_info;
1 Default trace file
/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3459.trc
1.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1303933
2.内存中获取SCN
SYS@orcl>oradebug setmypid
Statement processed.
SYS@orcl>oradebug dumpvar sga kcsgscn_
SYS@orcl>oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [060017F58, 060017F88) = 0013E57A 00000000 00000000 00000000 00000518 00000000 00000000 00000000 00000000 00000000 60017C38 00000000
SQL> select to_number('13E57A','xxxxxx') from dual;
TO_NUMBER('13E476','XXXXXX')
----------------------------
1303930
@@@@@@@@ 1与2执行顺序有先后之分,2早,1晚,因此有3个数的差距
alter session set events 'immediate trace name file_hdrs level 10';
3.
dump control:
@@Checkpoint cnt:161 scn: 0x0000.0013dbb4 07/19/2012 14:47:08
Stop scn: 0xffff.ffffffff 07/19/2012 10:41:21
dump file_hdrs:
@@Checkpoint cnt:161 scn: 0x0000.0013dbb4 07/19/2012 14:47:08
Stop scn: 0xffff.ffffffff 07/19/2012 10:41:21
file header:
Creation at scn: 0x0000.00000007 08/15/2009 00:16:48
@@Checkpointed at scn: 0x0000.0013dbb4 07/19/2012 14:47:08
4.
logfile:low scn----high scn
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1304935
SQL> alter system switch logfile;
System altered
SQL> select group#,status,first_change#,next_change# from v$log;
GROUP# STATUS FIRST_CHANGE# NEXT_CHANGE#
---------- ---------------- ------------- ------------
1 INACTIVE 1299225 1304959
2 CURRENT 1304959 281474976710
3 INACTIVE 1299052 1299225
scn 1304935相关事务redo信息在group1 中
SQL> alter session set events 'immediate trace name controlf level 8';
Session altered
dump controlfile ,查看其中的redo日志scn
SQL> select to_number('13d319','xxxxxx') from dual;
TO_NUMBER('13D319','XXXXXX')
----------------------------
1299225
SQL> select to_number('13e97f','xxxxxx') from dual;
TO_NUMBER('13E97F','XXXXXX')
----------------------------
1304959
5. dump redo 日志
SQL> alter system dump logfile '/opt/app/oracle/oradata/orcl/redo01.log';
prev resetlogs count: 0x296b946b scn: 0x0000.00000001 (1)
@@@Low scn: 0x0000.0013d319 (1299225) 07/19/2012 09:51:03
@@@Next scn: 0x0000.0013e97f (1304959) 07/19/2012 16:03:47
oradebug 方法:
dump数据文件头
SYS@orcl>oradebug setmypid
Statement processed.
SYS@orcl>oradebug dump file_hdrs 3;
Statement processed.
SYS@orcl>oradebug tracefile_name;
/opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3946.trc
总结:
1.系统SCN,写在控制文件中,该SCN是全局范围的,当发生文件级别的SCN时,例如将表空间置于只读状态,
则不会更新系统检查点SCN。查询系统检查点SCN的命令如下:
sys@ORCL>select CHECKPOINT_CHANGE# from v$database;
2.文件SCN
当ckpt进程启动时,包括全局范围的(比如日志切换)以及文件级别的检查点(将表空间置为只读、begin backup或将某个数据文件设置为offline等)
,这时会在控制文件中记录的SCN。查询数据文件SCN的命令如下:
sys@ORCL> altertablespace users read only;
3.结束SCN
每个数据文件都有一个结束SCN,在数据库的正常运行中,只要数据文件在线且是可读写的,结束SCN为NULL。否则则存在具体的SCN值。结束SCN也记录在控制文件中。
sys@ORCL>select TABLESPACE_NAME,STATUS from dba_tablespaces;
4.数据文件头SCN
不同于上述的SCN数据文件开始SCN记录在每个数据文件中。当发生系统及文件级别的检查点后,不仅将这时的SCN号记录在控制文件中,同样也记录在数据文件中。查询数据文件头SCN的命令如下:
sys@ORCL>select file#,CHECKPOINT_CHANGE# from v$datafile_header;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22604380/viewspace-736051/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22604380/viewspace-736051/