eygle.com   eygle.com
eygle.com  
 

« June 19, 2006 | Blog首页 | June 21, 2006 »


June 20, 2006

新书名称确定－《深入浅出Oracle》

作者:eygle

出处:http://blog.eygle.com

在经历了艰难的取舍和选择之后，书名最终确定为《深入浅出Oracle-DBA入门、进阶与诊断案例》，这个名字基本上涵盖了本书的内容和特色。

目前书稿已经交付出版社，正在进行紧张的审阅和排版工作，希望可以尽快的和大家见面。

再次感谢大家的关注与支持！

 

Posted by eygle at 3:47 PM | Comments (0)

Dell的2850 + Oracle10gR2 I/O性能测试

作者:eygle

出处:http://blog.eygle.com

前几天帮朋友安装了一套Oracle10gR2的环境，顺便简单测试了一下IO性能。
机器是Dell的2850，三块硬盘的Raid5。

[oracle@order ~]$ sqlplus eygle/eygle

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Jun 16 11:29:49 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SGA设置如下:

SQL> show sga

Total System Global Area 2483027968 bytes
Fixed Size 1262344 bytes
Variable Size 536874232 bytes
Database Buffers 1929379840 bytes
Redo Buffers 15511552 bytes

插入数据测试性能:

SQL> create table t as select * from dba_objects where 1=0;

Table created.

SQL> begin
2 for i in 1 .. 10000 loop
3 insert into t select * from dba_objects where rownum <1001;
4 commit;
5 end loop
6 ;
7 end;
8 /

PL/SQL procedure successfully completed.

通过iostat观察IO性能，以下简要的几个采样显示：
在iowait达到25.24%时，系统的IO写入能力可以达到18M左右。
在iowait达到28.23%时，系统的IO写入能力可以达到48M左右。

而CPU的idle值都在60%以上，说明CPU仍然有相当空闲，IO仍然是主要瓶颈。
但是这个IO性能也已经不错了，SCSI的硬盘，能够满足一般业务的需求了。

[oracle@xcstat ~]$ iostat -k 5|grep -v "0.00 0.00 0.00"
Linux 2.6.9-34.ELsmp (xcstat.xcsky.cn) 06/16/2006

avg-cpu: %user %nice %sys %iowait %idle
4.70 0.00 2.40 25.24 67.67

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 1164.47 7.98 18447.11 40 92420
sda5 4613.37 7.98 18445.51 40 92412
sda7 0.40 0.00 1.60 0 8

avg-cpu: %user %nice %sys %iowait %idle
4.75 0.00 3.60 28.23 63.41

Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn
sda 670.94 0.00 48109.82 0 240068
sda5 12026.85 0.00 48107.41 0 240056
sda7 0.60 0.00 2.40 0 12
综合总体数据：
平均IO写入速度为:19183.18945 kB_wrtn/s
最大IO写入速度为:48107.41 kB_wrtn/s
最小IO写入速度为:978 kB_wrtn/s

简单的测试，记录些简单的数据供参考。

 

Posted by eygle at 11:44 AM | Comments (5)

隐含参数_disable_logging的几点说明

作者:eygle

出处:http://blog.eygle.com

在很久以前,曾经介绍过Oracle的一个内部隐含参数_disable_logging,看到有朋友论述这个参数,今天忍不住做一点补充说明.

1.当然,隐含有风险,设置请谨慎.

2.最初在9.2.0.6 Solaris版本上,设置该参数会触发Bug:3868748 使得数据库无法启动.

从警告日志中,通常可以获得的错误提示是:

ORA-07445: exception encountered:
core dump [kcrfwcint()+1625] [SIGFPE] [Integer divide by zero] [0x828739D] [] []

但是好在这个参数是动态的,在存在Bug版本中,我们可以修改数据库当前值来进行测试:

SQL> alter system set "_disable_logging"=true scope=memory;

System altered.

3.这个Bug的影响范围并非全部,9.2.0.6之下,Oracle9.2.0.5,Oracle9.2.0.4等,都不受这个Bug影响.Oracle声称的修正该Bug的版本是Oracle10gR2(我未验正).

4.这个参数在Oracle9.2.0.4版本的Linux/Solaris上是不存在这个Bug的.在我的环境中经过验证,过程请参考(附注1).

5.在归档模式下,设置该参数会导致日志文件损坏.因为在设置该参数与归档原则违背，归档进程无法识别该日志文件格式，会将该日志文件标记为损坏.所以需要谨慎测试,具体请参考(附注2).

附注1:Solaris Oracle9.2.0.4设置测试过程:

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 13 22:51:24 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> show parameter disa

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean FALSE
SQL> alter system set "_disable_logging"=true scope=both;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 286755168 bytes
Fixed Size 731488 bytes
Variable Size 167772160 bytes
Database Buffers 117440512 bytes
Redo Buffers 811008 bytes
Database mounted.
Database opened.
SQL> show parameter disable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean TRUE
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

附注2:归档模式下,该参数会导致日志文件损坏.

设置该参数后,切换日志:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/oradata/conner/archive
Oldest online log sequence 20
Next log sequence to archive 23
Current log sequence 23
SQL> alter system switch logfile;

System altered.

SQL> select group#,status from v$Log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 ACTIVE
3 INACTIVE
4 CURRENT

SQL> show parameter disable

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_disable_logging boolean TRUE

此时可以在警告日志中看到日志损坏的错误信息:

Thu Apr 13 23:33:25 2006
ARC0: Evaluating archive log 2 thread 1 sequence 23
ARC0: Beginning to archive log 2 thread 1 sequence 23
Creating archive destination LOG_ARCHIVE_DEST_1: '/opt/oracle/oradata/conner/archive/1_23.dbf'
ARC0: Log corruption near block 3849 change 0 time ?
ARC0: All Archive destinations made inactive due to error 354
Thu Apr 13 23:33:25 2006
Errors in file /opt/oracle/admin/conner/bdump/conner_arc0_21506.trc:
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 3849 change 0 time 04/13/2006 21:13:03
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/conner/redo02.log'
ARC0: Archiving not possible: error count exceeded
ARC0: Failed to archive log 2 thread 1 sequence 23
ARCH: Archival stopped, error occurred. Will continue retrying
Thu Apr 13 23:33:26 2006
ORACLE Instance conner - Archival Error
ARCH: Connecting to console port...
Thu Apr 13 23:33:26 2006
ORA-16038: log 2 sequence# 23 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/conner/redo02.log'
ARCH: Connecting to console port...
ARCH:
Thu Apr 13 23:33:26 2006
ORA-16038: log 2 sequence# 23 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/opt/oracle/oradata/conner/redo02.log'

Posted by eygle at 11:27 AM | Comments (0)

CopyRight © 2004-2008 eygle.com, All rights reserved.