很多人都对oracle都知道oracle坏块有逻辑坏块和物理坏块之分，其实根据物理和逻辑坏块的分类，还可以继续分下去；对于坏块的检查，很多人知道物理和逻辑坏块使用dbv和rman检测，那其他种类的坏块该怎么办呢？这篇文章整理自MOS，给大家整理个分析坏块的思路，在后续文章中，会给出各种坏块的解决思路
http://www.xifenfei.com/wp-content/uploads/2012/03/Corruption_block_type_and_analysis.pdf
附件:hout和hcheck
通过修改col$.col#改变列展示顺序
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
有网友提出在表中新增加一列，然后让这个列在中间(大家都知道默认情况下，增加一列，这列会在其他列之后)，通过修改col$基表可以实现改变列的显示顺序(增加一个新列是小儿科的事情不再研究)。我这里通过建立一个表，有id和name列，现在要改变这两列的默认展示顺序
1.创建模拟表
SQL> create table chf.t_xff (id number,name varchar2(100));
Table created.
SQL> insert into chf.t_xff values(1,'xifenfei');
1 row created.
SQL> insert into chf.t_xff values(2,'www.xifenfei.com');
1 row created.
SQL> commit;
Commit complete.
SQL> desc chf.t_xff;
Name Null? Type
----------------------------------------- -------- -------------------
ID NUMBER
NAME VARCHAR2(100)
SQL> col name for a30
SQL> select * from chf.t_xff;
ID NAME
---------- ------------------------------
1 xifenfei
2 www.xifenfei.com
2.修改col$.col#
SQL> select COLUMN_ID,COLUMN_NAME from dba_tab_cols
2 where table_name='T_XFF' AND OWNER='CHF';
COLUMN_ID COLUMN_NAME
---------- ------------------------------------------------------------
2 NAME
1 ID
SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T_XFF';
OBJECT_ID
----------
75598
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME COL#
------------------------------ ----------
NAME 2
ID 1
SQL> UPDATE COL$ SET COL#=2 WHERE OBJ#=75598 AND NAME='ID';
1 row updated.
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME COL#
------------------------------ ----------
NAME 2
ID 2
SQL> UPDATE COL$ SET COL#=1 WHERE OBJ#=75598 AND NAME='NAME';
1 row updated.
SQL> SELECT NAME,COL# FROM COL$ WHERE OBJ#=75598;
NAME COL#
------------------------------ ----------
NAME 1
ID 2
SQL> COMMIT;
Commit complete.
3.验证结果
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 368263168 bytes Fixed Size 1345016 bytes Variable Size 306186760 bytes Database Buffers 54525952 bytes Redo Buffers 6205440 bytes Database mounted. Database opened. SQL> select * from chf.t_xff; NAME ID ------------------------------ ---------- xifenfei 1 www.xifenfei.com 2 SQL> desc chf.t_xff Name Null? Type ----------------------------------------- -------- --------------- NAME VARCHAR2(100) ID NUMBER
误删除dual表恢复
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：误删除dual表恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1.10G中删除dual表恢复
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select object_type,owner from dba_objects where object_name='DUAL';
OBJECT_TYPE OWNER
------------------- ------------------------------
TABLE SYS
SYNONYM PUBLIC
SQL> drop table sys.dual;
Table dropped.
SQL> select object_type from dba_objects where object_name='DUAL';
OBJECT_TYPE
-------------------
SYNONYM
SQL> SELECT SYSDATE FROM dual;
SELECT SYSDATE FROM dual
*
ERROR at line 1:
ORA-01775: looping chain of synonyms
SQL> CREATE TABLE XFF AS SELECT * from dba_objects;
Table created.
SQL> drop table xff purge;
drop table xff purge
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01775: looping chain of synonyms
设置10046跟踪会话发现，在每次删除表操作的时候发现如下错误
select dummy from dual where ora_dict_obj_type = 'TABLE'
其实这里错误都很明显，是因为dual表不存在了，表对应的同义词还存在，当查询dual的时候，会去查询同义词，然后该同义词去找表，而表不存在，所以出现上述的ORA-01775: looping chain of synonyms错误
2.解决方法
SQL> CREATE TABLE "SYS"."DUAL"
2 ( "DUMMY" VARCHAR2(1)
3 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
4 STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
5 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
6 TABLESPACE "SYSTEM" ;
Table created.
SQL> GRANT SELECT ON "SYS"."DUAL" TO PUBLIC WITH GRANT OPTION;
Grant succeeded.
SQL> insert into dual values('X');
1 row created.
SQL> COMMIT;
Commit complete.
--编译对象
SQL> @?/rdbms/admin/utlrp.sql
3.测试结果
SQL> select sysdate from dual; SYSDATE ------------ 13-MAR-12 SQL> drop table xff purge; Table dropped.
tempfile真正文件号
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1.发现问题
这里看到文件号为201，但是查询了v$datafile和v$tempfile视图都没有文件号为201
SQL> select username,segtype,segfile#,segblk#,extents,segrfno# from v$sort_usage;
USERN SEGTYPE SEGFILE# SEGBLK# EXTENTS SEGRFNO#
----- --------- ---------- ---------- ---------- ----------
SYS SORT 201 260745 650 1
SEGFILE# NUMBER File number of initial extent
SEGRFNO# NUMBER Relative file number of initial extent
SQL> SELECT FILE#,RFILE# FROM V$DATAFILE;
FILE# RFILE#
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
FILE# RFILE#
---------- ----------
13 13
14 14
13 rows selected.
SQL> SELECT FILE#,RFILE# FROM V$TEMPFILE;
FILE# RFILE#
---------- ----------
1 1
2.查看v$sort_usage的原始表
SELECT USERNAME,
USERNAME,
KTSSOSES,
KTSSOSNO,
PREV_SQL_ADDR,
PREV_HASH_VALUE,
PREV_SQL_ID,
KTSSOTSN,
DECODE(KTSSOCNT, 0, 'PERMANENT', 1, 'TEMPORARY'),
DECODE(KTSSOSEGT,
1,
'SORT',
2,
'HASH',
3,
'DATA',
4,
'INDEX',
5,
'LOB_DATA',
6,
'LOB_INDEX',
'UNDEFINED'),
KTSSOFNO,
KTSSOBNO,
KTSSOEXTS,
KTSSOBLKS,
KTSSORFNO
FROM X$KTSSO, V$SESSION
WHERE KTSSOSES = V$SESSION.SADDR
AND KTSSOSNO = V$SESSION.SERIAL#
and inst_id = USERENV('Instance')
这里没有发现有用信息，只是知道X$KTSSO.KTSSOFNO是v$sort_usage.SEGFILE#，通过v$sort_usage视图是查询临时表空用来排序的数据文件使用情况。所以把问题定位在v$tempfile视图中，检查它为什么没有显示文件号为201的文件
3.查看v$tempfile视图
SELECT TF.TFNUM,
TO_NUMBER(TF.TFCRC_SCN),
TO_DATE(TF.TFCRC_TIM,
'MM/DD/RR HH24:MI:SS',
'NLS_CALENDAR=Gregorian'),
TF.TFTSN,
TF.TFRFN,
DECODE(BITAND(TF.TFSTA, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'),
DECODE(BITAND(TF.TFSTA, 12),
0,
'DISABLED',
4,
'READ ONLY',
12,
'READ WRITE',
'UNKNOWN'),
FH.FHTMPFSZ * TF.TFBSZ,
FH.FHTMPFSZ,
TF.TFCSZ * TF.TFBSZ,
TF.TFBSZ,
FN.FNNAM
FROM X$KCCTF TF, X$KCCFN FN, X$KCVFHTMP FH
WHERE FN.FNFNO = TF.TFNUM
AND FN.FNFNO = FH.HTMPXFIL
AND TF.TFFNH = FN.FNNUM
AND TF.TFDUP != 0
AND BITAND(TF.TFSTA, 32) <> 32
AND FN.FNTYP = 7
AND FN.FNNAM IS NOT NULL
and inst_id = USERENV('Instance')
从这里可以看出v$tempfile.file#出自X$KCCTF.TFNUM
4.继续查看X$KCCTF表
SQL> desc X$KCCTF Name Null? Type ----------------------------------------- -------- ------------- ADDR RAW(8) INDX NUMBER INST_ID NUMBER TFNUM NUMBER TFAFN NUMBER TFCSZ NUMBER TFBSZ NUMBER TFSTA NUMBER TFCRC_SCN VARCHAR2(16) TFCRC_TIM VARCHAR2(20) TFFNH NUMBER TFFNT NUMBER TFDUP NUMBER TFTSN NUMBER TFTSI NUMBER TFRFN NUMBER TFPFT NUMBER TFMSZ NUMBER TFNSZ NUMBER
这里发现一列TFAFN，初步怀疑这个才是真正的绝对文件号。
5.证明X$KCCTF.TFAFN是绝对文件号
SQL> select TFAFN,TFNUM from X$KCCTF;
TFAFN TFNUM
---------- ----------
201 1
如果证明X$KCCTF.TFAFN才是真正的文件号，而TFNUM是临时文件的文件号
6.temp file绝对文件号结论
再进一步可以知道，实际上，为了分离临时文件号和数据文件号，Oracle对临时文件的编号以db_files为起点，所以临时文件的绝对文件号应该等于db_files+file#。
SQL> show parameter db_files; NAME TYPE VALUE ------------------------------------ ----------- -------- db_files integer 200
利用oradebug释放被删除文件空间
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在很多时候，检查系统时候发现，由于某个Oracle的trace文件导致磁盘空间告警，因为业务需要不能让数据库down下来。这个时候你想到的方法可能是直接删除掉这个trace文件，如果是win系统，那恭喜你这样做可以解决问题；如果是linux/unix系统，那就等着事故的发生吧。在linux/unix中，如果直接rm掉oracle进程的某个文件(该进程还存在)，文件句柄不会释放，即磁盘使用空间不会释放。可以通过df命名看到磁盘的空间释放释放。下面通过对lgwr进程的一系列操作，使用oradebug来释放oracle进程句柄，从而达到释放oracle某个被删除的trace文件的磁盘空间
一、查找lgwr进程的trace文件
[oracle@localhost /]$ cd $ORACLE_BASE/admin/$ORACLE_SID/bdump [oracle@localhost bdump]$ pwd /opt/oracle/admin/mcrm/bdump [oracle@localhost bdump]$ ls -l|grep lgwr -rw-r----- 1 oracle oinstall 32133 Dec 22 21:00 mcrm_lgwr_3485.trc -rw-r----- 1 oracle oinstall 3713 Oct 8 07:13 mcrm_lgwr_3489.trc -rw-r----- 1 oracle oinstall 22507 Mar 3 06:00 mcrm_lgwr_3598.trc -rw-r----- 1 oracle oinstall 8441 Sep 15 10:29 mcrm_lgwr_4963.trc [oracle@localhost bdump]$ ps -ef|grep lgwr oracle 1056 30718 0 21:10 pts/3 00:00:00 grep lgwr oracle 3598 1 0 2011 ? 00:04:10 ora_lgwr_mcrm [oracle@localhost bdump]$ df |grep /opt /dev/sda6 37798668 33312588 2534988 93% /opt [oracle@localhost bdump]$ du -s . 948 .
从这里得出几点结论：
1.当前lgwr进程的spid为：3598
2.当前lgwr进程产生的trace文件大小为：22507B
3.包含该trace文件的分区大小使用大小为：33312588KB
4.bdump目录大小为：948KB
二、删除lgwr进程对应trace文件
[oracle@localhost bdump]$ rm mcrm_lgwr_3598.trc [oracle@localhost bdump]$ du -s . 924 . [oracle@localhost bdump]$ df |grep /opt /dev/sda6 37798668 33312588 2534988 93% /opt [oracle@localhost bdump]$ ls -l /proc/3598/fd|grep lgwr l-wx------ 1 oracle oinstall 64 Mar 3 20:54 2 -> /opt/oracle/admin/mcrm/bdump/mcrm_lgwr_3598.trc (deleted)
从这里得出结论：
1.bdump目录当前大小变为：924KB(大约等于948KB-22507B)
2.包含该trace文件的分区大小使用大小依然为：33312588KB(没有因为删除trace文件而释放空间)
三、释放被删除trace文件空间
[oracle@localhost bdump]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 3 21:12:41 2012 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> !ls -l /proc/3598/fd|grep lgwr l-wx------ 1 oracle oinstall 64 Mar 3 20:54 2 -> /opt/oracle/admin/mcrm/bdump/mcrm_lgwr_3598.trc (deleted) SQL> oradebug setospid 3598 Oracle pid: 6, Unix process pid: 3598, image: oracle@localhost.localdomain (LGWR) SQL> oradebug flush; Statement processed. SQL> oradebug close_trace; Statement processed. SQL> !ls -l /proc/3598/fd|grep lgwr SQL> !df |grep /opt /dev/sda6 37798668 33312564 2535012 93% /opt SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
从这里可以得出结论：
1.包含该trace文件的分区大小使用大小为：33312564KB(大约等于948KB-22507B)
2./proc/spid/fd下面的句柄已经释放
3.总这里可以看出使用oradebug可以真正释放oracle进程磁盘使用空间
statspack报告中逻辑读为负值
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
最近遇到两次在sp报告中，显示逻辑读为负数。进行分析情况如下：
一台是运营商的crm库（aix 5.3+9.2.0.8+rac）
--系统版本
[zwq_crm2:/home/crm_oraeye]oslevel -s
5300-08-07-0920
--数据库版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
--数据库启动时间
SQL> SELECT A.INST_ID, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss')
2 FROM gv$instance a;
INST_ID TO_CHAR(STARTUP_TIM
---------- -------------------
2 2011-02-19 03:33:49
1 2011-02-19 03:56:17
--异常的逻辑读统计数据
SQL> SELECT b.snap_id,
2 TO_CHAR (B.SNAP_TIME, 'yyyy-mm-dd hh24:mi:ss'),
3 TO_CHAR (A.VALUE, '999,999,999,999,999')
4 FROM stats$sysstat a, stats$snapshot b
5 WHERE a.NAME = 'session logical reads'
6 AND a.instance_number = 2
7 AND a.snap_id = b.snap_id
8 AND A.SNAP_ID >=47913
9 AND A.SNAP_ID <=47920
10 ORDER BY a.snap_id;
SNAP_ID TO_CHAR(B.SNAP_TIME TO_CHAR(A.VALUE,'999
---------- ------------------- --------------------
47913 2012-02-10 20:00 4,764,705,272,783
47914 2012-02-10 21:00 4,761,539,910,574
47915 2012-02-10 22:00 4,749,529,436,021
47916 2012-02-10 23:00 4,745,952,040,146
47917 2012-02-11 00:00 4,738,052,256,634
47918 2012-02-11 01:00 4,738,894,245,521
47919 2012-02-11 02:00 4,739,587,095,184
47920 2012-02-11 03:00 4,740,409,262,259
另一台是运营商的开停机库（aix 5.3+9.2.0.8+rac）
--系统版本
[zwq_offon2:/home/oraeye]oslevel -s
5300-08-07-0920
--数据库版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
--数据库启动时间
SQL> SELECT A.INST_ID, TO_CHAR (startup_time, 'yyyy-mm-dd hh24:mi:ss')
2 FROM gv$instance a;
INST_ID TO_CHAR(STARTUP_TIM
---------- -------------------
2 2010-01-23 19:16:46
1 2010-01-23 19:15:09
--异常的逻辑读统计数据
SQL> SELECT b.snap_id,
2 TO_CHAR (B.SNAP_TIME, 'yyyy-mm-dd hh24:mi:ss'),
3 TO_CHAR (A.VALUE, '999,999,999,999,999')
4 FROM stats$sysstat a, stats$snapshot b
5 WHERE a.NAME = 'session logical reads'
6 AND a.instance_number = 2
7 AND a.snap_id = b.snap_id
8 AND A.SNAP_ID IN ('38271', '38272', '38339', '38340')
9 ORDER BY a.snap_id;
SNAP_ID TO_CHAR(B.SNAP_TIME TO_CHAR(A.VALUE,'999
---------- ------------------- --------------------
38271 2012-01-31 19:00:05 4,269,858,122,434
38272 2012-01-31 20:00:02 4,266,001,522,867
38339 2012-02-02 09:00:02 4,275,651,080,526
38340 2012-02-02 10:00:02 4,250,263,107,466
这两个数据库中都出现了在数据库没有重启的情况下stats$sysstat.value的值出现波动情况，而且都是在40万亿以上的时候。不知道是特点的版本巧合(特定的数据库版本，特定的操作系统版本)，还是Oracle未公布bug。出现这样的情况，目前只能通过重启实例来使得statspack捕获到的逻辑读值变小，从而避免这样的波动，从而解决sp中出现逻辑读为负数的情况。
ARCn: Media recovery disabled原因分析
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：ARCn: Media recovery disabled原因分析
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1.数据库版本
SQL> select * from v$version; BANNER ------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production PL/SQL Release 9.2.0.8.0 - Production CORE 9.2.0.8.0 Production TNS for Linux: Version 9.2.0.8.0 - Production NLSRTL Version 9.2.0.8.0 - Production
2.alert日志出现类此记录
Fri Feb 10 05:16:55 2012 ARC1: Media recovery disabled Fri Feb 10 05:17:23 2012 ARC0: Media recovery disabled Fri Feb 10 05:17:23 2012 ARC1: Media recovery disabled Fri Feb 10 05:18:23 2012 ARC1: Media recovery disabled Fri Feb 10 05:19:12 2012 Thread 1 advanced to log sequence 24 Current log# 1 seq# 24 mem# 0: /tmp/recover/redo01.log Fri Feb 10 05:19:12 2012 ARC0: Media recovery disabled Fri Feb 10 05:19:23 2012 ARC1: Media recovery disabled Fri Feb 10 05:20:23 2012 ARC1: Media recovery disabled Fri Feb 10 05:21:23 2012 ARC1: Media recovery disabled
3.数据库归档信息
SQL> archive log list; Database log mode No Archive Mode Automatic archival Enabled Archive destination /tmp/recover Oldest online log sequence 25 Current log sequence 27 SQL> show parameter log_archive_start NAME TYPE VALUE ------------------------------------ ----------- --------------- log_archive_start boolean TRUE
从这里可以看出，数据库处于非归档模式，但是log_archive_start=true，这样会导致归档进程自动启动(ARCn),因为数据库设置为非归档模式，从而导致该进程启动后，又自己关闭
4.解决方法
SQL> alter system set log_archive_start=false scope=spfile; System altered. SQL> startup force ORACLE instance started. Total System Global Area 202445176 bytes Fixed Size 450936 bytes Variable Size 167772160 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> show parameter log_archive_start NAME TYPE VALUE ------------------------------------ ----------- -------------- log_archive_start boolean FALSE SQL> ARCHIVE LOG LIST; Database log mode No Archive Mode Automatic archival Disabled Archive destination /tmp/recover Oldest online log sequence 26 Current log sequence 28
ALERT中无类此记录出现
通过sql语句获取awr/statspack逻辑读/物理读
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：通过sql语句获取awr/statspack逻辑读/物理读
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在日常的性能监控中，我们经常需要需要通过一段时间内数据库的逻辑读/物理读来大致反映数据库的性能情况，这里列出通过awr和statspack来获取相关数据（查询最近30天数据，除掉第一条和最后一条数据）
awr逻辑读
WITH A AS
(SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
FROM DBA_HIST_SYSSTAT B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND b.INSTANCE_NUMBER = &INST_NUM
AND B.STAT_NAME IN ('session logical reads')
GROUP BY B.SNAP_ID
ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
VALUE "END_VALUE(G)",
TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS')"END_TIME",
VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
FROM A,(SELECT END_INTERVAL_TIME,SNAP_ID
FROM DBA_HIST_SNAPSHOT B
WHERE B.DBID = (SELECT dbid FROM v$database)
AND B.INSTANCE_NUMBER = &INST_NUM) B
WHERE A.SNAP_ID=B.snap_id
AND END_INTERVAL_TIME>=SYSDATE-30;
awr物理读
WITH A AS
(SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
FROM DBA_HIST_SYSSTAT B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND b.INSTANCE_NUMBER = &INST_NUM
AND B.STAT_NAME IN ('physical reads')
GROUP BY B.SNAP_ID
ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
VALUE "END_VALUE(G)",
TO_CHAR(END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS')"END_TIME",
VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
FROM A,(SELECT END_INTERVAL_TIME,SNAP_ID
FROM DBA_HIST_SNAPSHOT B
WHERE B.DBID = (SELECT dbid FROM v$database)
AND B.INSTANCE_NUMBER = &INST_NUM) B
WHERE A.SNAP_ID=B.snap_id
AND END_INTERVAL_TIME>=SYSDATE-30;
statspack逻辑读
WITH A AS
(SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
FROM STATS$SYSSTAT B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = &INST_NUM
AND B.NAME IN ('session logical reads')
GROUP BY B.SNAP_ID
ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
VALUE "END_VALUE(G)",
TO_CHAR(SNAP_TIME, 'YYYY-MM-DD HH24:MI:SS') "END_TIME",
VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
FROM A,
(SELECT SNAP_TIME, SNAP_ID
FROM STATS$SNAPSHOT B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = &INST_NUM) B
WHERE A.SNAP_ID = B.SNAP_ID
AND snap_time>=SYSDATE-30;
statspack物理读
WITH A AS
(SELECT B.SNAP_ID, SUM(VALUE)/1024/1024/1024*8192 VALUE
FROM STATS$SYSSTAT B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = &INST_NUM
AND B.NAME IN ('physical reads')
GROUP BY B.SNAP_ID
ORDER BY SNAP_ID)
SELECT A.SNAP_ID,
LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "START_VALUE(G)",
VALUE "END_VALUE(G)",
TO_CHAR(SNAP_TIME, 'YYYY-MM-DD HH24:MI:SS') "END_TIME",
VALUE - LAG(VALUE, 1, '0') OVER(ORDER BY A.SNAP_ID) "D-VALUE(G)"
FROM A,
(SELECT SNAP_TIME, SNAP_ID
FROM STATS$SNAPSHOT B
WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
AND B.INSTANCE_NUMBER = &INST_NUM) B
WHERE A.SNAP_ID = B.SNAP_ID
AND snap_time>=SYSDATE-30;
FAST_START_PARALLEL_ROLLBACK与回滚恢复
联系：手机/微信(+86 17813235971) QQ(107644445)
标题：FAST_START_PARALLEL_ROLLBACK与回滚恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
1.模拟产生大事务需回滚
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 16 12:47:08 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> show sga;
Total System Global Area 622149632 bytes
Fixed Size 2230912 bytes
Variable Size 406848896 bytes
Database Buffers 205520896 bytes
Redo Buffers 7548928 bytes
SQL> create table chf.xifenfei tablespace users
2 as
3 select * from dba_objects;
--下面两句多次执行
SQL> insert into chf.xifenfei
2 select * from chf.xifenfei;
73831 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from chf.xifenfei;
COUNT(*)
----------
18900736
SQL> select bytes/1024/1024 from dba_segments where segment_name='XIFENFEI';
BYTES/1024/1024
---------------
2103
--删除数据不提交
SQL> delete from chf.xifenfei;
18900736 rows deleted.
--直接kill掉ora_dbw进程
2.FAST_START_PARALLEL_ROLLBACK=LOW(默认值)
SQL> select undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
2 "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone)
3 / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss'))
4 "Estimated time to complete"，to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
5 from v$fast_start_transactions;
Total Done ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
545624 103020 442604 2012-02-16 13:57:38 2012-02-16 13:47:02
SQL> /
Total Done ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
545624 122614 423010 2012-02-16 13:57:42 2012-02-16 13:47:31
--每秒钟回滚undo数据块数量
SQL> select (122614-103020)/29 from dual;
(122614-103020)/29
------------------
675.655172
--数据库并发回滚进程数
[oracle@node1 ~]$ ps -ef|grep ora_p0
oracle 24901 1 4 13:44 ? 00:00:15 ora_p000_chf
oracle 24903 1 3 13:44 ? 00:00:12 ora_p001_chf
oracle 24905 1 3 13:44 ? 00:00:12 ora_p002_chf
oracle 24907 1 3 13:44 ? 00:00:12 ora_p003_chf
oracle 24909 1 3 13:44 ? 00:00:12 ora_p004_chf
oracle 24911 1 3 13:44 ? 00:00:12 ora_p005_chf
oracle 24913 1 3 13:44 ? 00:00:12 ora_p006_chf
oracle 24915 1 3 13:44 ? 00:00:12 ora_p007_chf
oracle 24917 1 3 13:44 ? 00:00:12 ora_p008_chf
oracle 24919 1 3 13:44 ? 00:00:12 ora_p009_chf
oracle 24921 1 3 13:44 ? 00:00:12 ora_p010_chf
oracle 24923 1 3 13:44 ? 00:00:12 ora_p011_chf
oracle 24925 1 3 13:44 ? 00:00:12 ora_p012_chf
oracle 24927 1 3 13:44 ? 00:00:12 ora_p013_chf
oracle 24929 1 3 13:44 ? 00:00:12 ora_p014_chf
oracle 24931 1 3 13:44 ? 00:00:12 ora_p015_chf
说明：该机器操作系统是8个CPU
并发数=CPU*2
3.FAST_START_PARALLEL_ROLLBACK =HIGH
SQL> alter system set FAST_START_PARALLEL_ROLLBACK =HIGH;
System altered.
SQL> select undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
2 "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone)
3 / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss'))
4 "Estimated time to complete"，to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
5 from v$fast_start_transactions;
Total Done ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
380434 25351 355083 2012-02-16 14:03:53 2012-02-16 13:49:39
SQL> /
Total Done ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
380434 39592 340842 2012-02-16 14:03:33 2012-02-16 13:50:12
--每秒钟回滚undo数据块数量
SQL> select (39592-25351)/33 from dual;
(39592-25351)/33
----------------
431.545455
--数据库并发回滚进程数
[oracle@node1 ~]$ ps -ef|grep ora_p0
oracle 24901 1 4 13:44 ? 00:00:15 ora_p000_chf
oracle 24903 1 3 13:44 ? 00:00:12 ora_p001_chf
oracle 24905 1 3 13:44 ? 00:00:12 ora_p002_chf
oracle 24907 1 3 13:44 ? 00:00:12 ora_p003_chf
oracle 24909 1 3 13:44 ? 00:00:12 ora_p004_chf
oracle 24911 1 3 13:44 ? 00:00:12 ora_p005_chf
oracle 24913 1 3 13:44 ? 00:00:12 ora_p006_chf
oracle 24915 1 3 13:44 ? 00:00:12 ora_p007_chf
oracle 24917 1 3 13:44 ? 00:00:12 ora_p008_chf
oracle 24919 1 3 13:44 ? 00:00:12 ora_p009_chf
oracle 24921 1 3 13:44 ? 00:00:12 ora_p010_chf
oracle 24923 1 3 13:44 ? 00:00:12 ora_p011_chf
oracle 24925 1 3 13:44 ? 00:00:12 ora_p012_chf
oracle 24927 1 3 13:44 ? 00:00:12 ora_p013_chf
oracle 24929 1 3 13:44 ? 00:00:12 ora_p014_chf
oracle 24931 1 3 13:44 ? 00:00:12 ora_p015_chf
oracle 25072 1 0 13:48 ? 00:00:01 ora_p016_chf
oracle 25074 1 0 13:48 ? 00:00:01 ora_p017_chf
oracle 25076 1 0 13:48 ? 00:00:01 ora_p018_chf
oracle 25078 1 0 13:48 ? 00:00:01 ora_p019_chf
oracle 25080 1 0 13:48 ? 00:00:01 ora_p020_chf
oracle 25082 1 0 13:48 ? 00:00:01 ora_p021_chf
oracle 25084 1 0 13:48 ? 00:00:01 ora_p022_chf
oracle 25086 1 0 13:48 ? 00:00:01 ora_p023_chf
oracle 25088 1 0 13:48 ? 00:00:01 ora_p024_chf
oracle 25090 1 0 13:48 ? 00:00:01 ora_p025_chf
oracle 25092 1 0 13:48 ? 00:00:01 ora_p026_chf
oracle 25094 1 0 13:48 ? 00:00:01 ora_p027_chf
oracle 25096 1 0 13:48 ? 00:00:01 ora_p028_chf
oracle 25098 1 0 13:48 ? 00:00:01 ora_p029_chf
oracle 25100 1 0 13:48 ? 00:00:01 ora_p030_chf
oracle 25102 1 0 13:48 ? 00:00:01 ora_p031_chf
1.说明问题：直接修改FAST_START_PARALLEL_ROLLBACK =HIGH后，
数据库在原来并发进程基础上，又重新启动额外进程
2.修改FAST_START_PARALLEL_ROLLBACK后，以前回滚过的数据块是成功的
v$fast_start_transactions视图重新开始计算
3.并发数=CPU*4
[/shell]
<strong>4.FAST_START_PARALLEL_ROLLBACK=FALSE</strong>
SQL> alter system set FAST_START_PARALLEL_ROLLBACK=FALSE;
System altered.
--直接修改为FALSE后，观察到数据库的并发等资源都没有释放，重启数据库释放资源继续试验
SQL> startup force;
ORACLE instance started.
Total System Global Area 622149632 bytes
Fixed Size 2230912 bytes
Variable Size 406848896 bytes
Database Buffers 205520896 bytes
Redo Buffers 7548928 bytes
Database mounted.
Database opened.
SQL> select undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone
2 "ToDo",decode(cputime,0,'unknown',to_char(sysdate+(((undoblockstotal-undoblocksdone)
3 / (undoblocksdone / cputime)) / 86400),'yyyy-mm-dd hh24:mi:ss'))
4 "Estimated time to complete"，to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
5 from v$fast_start_transactions;
Total Done ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
306828 15735 291093 2012-02-16 14:04:34 2012-02-16 13:52:33
SQL> /
Total Done ToDo Estimated time to c TO_CHAR(SYSDATE,'YY
---------- ---------- ---------- ------------------- -------------------
306828 65861 240967 2012-02-16 14:05:15 2012-02-16 13:54:46
--每秒钟回滚undo数据块数量
SQL> select (65861-15735)/133 from dual;
(65861-15735)/133
-----------------
376.887218
--数据库并发回滚进程数
[oracle@node1 ~]$ ps -ef|grep ora_p0
oracle 25252 1 1 13:51 ? 00:00:00 ora_p000_chf
oracle 25254 1 0 13:51 ? 00:00:00 ora_p001_chf
oracle 25256 1 1 13:51 ? 00:00:00 ora_p002_chf
oracle 25258 1 1 13:51 ? 00:00:00 ora_p003_chf
oracle 25260 1 1 13:51 ? 00:00:00 ora_p004_chf
oracle 25262 1 1 13:51 ? 00:00:00 ora_p005_chf
oracle 25264 1 1 13:51 ? 00:00:00 ora_p006_chf
1.数据库重启后，以前的回滚依然生效(v$fast_start_transactions.undoblockstotal变小)
2.FAST_START_PARALLEL_ROLLBACK=false还是有并发，而非官方文档描述(Parallel rollback is disabled)
5.总结
通过这三种情况下的每秒钟回滚undo数据块数量比较可以知道在LOW状态下最快，HIGH状态下次之，FALSE最慢。其实这个实验没有任何实际说明力，只是想说明几个问题：
1)Oracle大事物回滚，是没有办法取消，但是可以通过FAST_START_PARALLEL_ROLLBACK干预回滚速度
2)数据库的并发效率高于低，取决于系统的资源情况(如果你系统的cpu非常强大，那么可能设置HIGH速度最快)
3)回滚的数据类型，在回滚表中数据时可能设置并发比FALSE快，
但是如果是要回滚串行数据(如:index),那么可能串行方法方式速度更快
4)根据你的系统的使用状况，比如你想让系统的业务受到的影响最小，那么设置FALSE可能是个不错的选择。
6.补充官方说明
FAST_START_PARALLEL_ROLLBACK specifies the degree of parallelism used when recovering terminated transactions. Terminated transactions are transactions that are active before a system failure. If a system fails when there are uncommitted parallel DML or DDL transactions, then you can speed up transaction recovery during startup by using this parameter. Values: FALSE Parallel rollback is disabled LOW Limits the maximum degree of parallelism to 2 * CPU_COUNT HIGH Limits the maximum degree of parallelism to 4 * CPU_COUNT Note:If you change the value of this parameter, then transaction recovery will be stopped and restarted with the new implied degree of parallelism.
清除掉shared pool中某条sql语句方法
联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在论坛中看到一个帖子，如何清除掉shared pool中某条sql语句，如果是在10g以前的版本，那估计会比较麻烦，为了删除一条sql语句记录，需要清空整个shared pool，在10g中提供了新的dbms_shared_pool包可以实现该功能(如果该包没有安装，可以通过?/rdbms/admin/dbmspool.sql安装)，下面我通过在11g中试验证明该问题
1.数据库版本信息
SQL> select * from v$version; BANNER ---------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for 32-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production
2.模拟一条sql语句
SQL> create table xff as
2 select * from dba_tables
3 where rownum<10;
表已创建。
SQL> select count(*) from xff;
COUNT(*)
----------
9
SQL> col sql_text for a30
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
2 WHERE SQL_TEXT LIKE 'select % xff';
ADDRESS HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
3.打破神话一:权限操作
对表进行权限操作，可以清除该表在shared pool中关于该表记录
SQL> grant select on xff to chf; 授权成功。 SQL> col sql_text for a30 SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA 2 WHERE SQL_TEXT LIKE 'select % xff'; ADDRESS HASH_VALUE SQL_TEXT -------- ---------- ------------------------------ 1EFB91B8 3642190903 select count(*) from xff SQL> revoke select on xff from chf; 撤销成功。 SQL> col sql_text for a30 SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA 2 WHERE SQL_TEXT LIKE 'select % xff'; ADDRESS HASH_VALUE SQL_TEXT -------- ---------- ------------------------------ 1EFB91B8 3642190903 select count(*) from xff
证明grant和revoke操作没有清除shared pool中关于该表的sql语句
4.打破神话二:ddl操作
对表进行ddl操作，可以清除该表在shared pool中关于该表记录
SQL> alter table xff add owner1 varchar2(100); 表已更改。 SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA 2 WHERE SQL_TEXT LIKE 'select % xff'; ADDRESS HASH_VALUE SQL_TEXT -------- ---------- ------------------------------ 1EFB91B8 3642190903 select count(*) from xff
事实证明ddl操作不能达到预期效果，没有清除掉这条sql语句
5.刷新shared pool
SQL> alter system flush shared_pool 2 ; 系统已更改。 SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA 2 WHERE SQL_TEXT LIKE 'select % xff'; 未选定行
把整个shared pool都刷新了，自然其中的一条sql语句也没有了，在10g前只能通过这种方法实现
6.使用dbms_shared_pool.purge
SQL> select count(*) from xff;
COUNT(*)
----------
9
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
2 WHERE SQL_TEXT LIKE 'select % xff';
ADDRESS HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------
1EFB91B8 3642190903 select count(*) from xff
SQL> exec dbms_shared_pool.purge('1EFB91B8, 3642190903','C');
PL/SQL 过程已成功完成。
SQL> SELECT ADDRESS,HASH_VALUE,SQL_TEXT FROM V$SQLAREA
2 WHERE SQL_TEXT LIKE 'select % xff';
未选定行
试验证明在shared pool中清除了一条sql记录
7.关于dbms_shared_pool.purge参数说明
purge(name varchar2, flag char DEFAULT 'P', heaps number DEFAULT 1);
-- name
-- The name of the object to keep. There are two kinds of objects:
-- PL/SQL objects, triggers, sequences, types and Java objects,
-- which are specified by name, and
-- SQL cursor objects which are specified by a two-part number
-- (indicating a location in the shared pool). For example:
-- dbms_shared_pool.keep('scott.hispackage')
-- will keep package HISPACKAGE, owned by SCOTT. The names for
-- PL/SQL objects follows SQL rules for naming objects (i.e.,
-- delimited identifiers, multi-byte names, etc. are allowed).
-- A cursor can be keeped by
-- dbms_shared_pool.keep('0034CDFF, 20348871', 'C')
-- The complete hexadecimal address must be in the first 8 characters.
-- The value for this identifier is the concatenation of the
-- 'address' and 'hash_value' columns from the v$sqlarea view. This
-- is displayed by the 'sizes' call above.
-- Currently 'TABLE' and 'VIEW' objects may not be keeped.
--
-- flag
-- This is an optional parameter. If the parameter is not specified,
-- the package assumes that the first parameter is the name of a
-- package/procedure/function and will resolve the name. Otherwise,
-- the parameter is a character string indicating what kind of object
-- to keep the name identifies. The string is case insensitive.
-- The possible values and the kinds of objects they indicate are
-- given in the following table:
-- Value Kind of Object to keep
-- ----- ----------------------
-- P package/procedure/function
-- Q sequence
-- R trigger
-- T type
-- JS java source
-- JC java class
-- JR java resource
-- JD java shared data
-- C cursor
-- If and only if the first argument is a cursor address and hash-value,
-- the flag parameter should be set to 'C' (or 'c').
--
-- heaps
-- heaps to purge. e.g if heap 0 and heap 6 are to be purged.
-- 1<<0 | 1<<6 => hex 0x41 => decimal 65. so specify heaps=>65.
-- Default is 1 i.e heap 0 which means the whole object will be purged.