一个客户的重要生产系统,一个USER被删除了,USER下所有的对象都被删除了。还好是下班时间,也有有效的备份,数据得以完全恢复,也没有对生产造成重大影响。

引起这个事故的,除了人为因素之外,ORACLE的BUG要负一半的责任。通过操作录像(客户对系统内做系统管理的所有机器的操作都作了录像,这点非常好),我们搞清楚了数据被删除的经过。

一个开发人员,通过OEM(Oracle Enterprise Manager Console)连接到数据库上,经过他确认OEM上的那个连接字符串是正确的,然后对USER做了删除操作,但很快发现,生产库的数据被删除了。操作录像也证明那个连接字符串是正确的,那么问题出在哪里呢?

操作的那台机器(Windows系统),在系统环境变量(我的电脑=>属性=>高级=>环境变量=>系统变量)中设置了TNS_ADMIN,指向了另外的目录。现在,TNS_ADMIN指向的目录(下面简称TNS_ADMIN目录)和%ORACLE_HOME%\NETWORK\ADMIN(下面简称ORACLE目录)下都有TNSNAMES.ORA这个文件。在TNS_ADMIN中,TNSNAMES.ORA有一TNSNAME,指向生产库。在ORACLE目录中,TNSNAMES.ORA中有一同样的名称的TNSNAME指向开发库。

OEM在处理TNS_ADMIN上是有问题的。OEM在启动后,在左边的数据库目录树,是从ORACLE目录的TNSNAMES.ORA中解析出来的,完全忽略了TNS_ADMIN环境变量,就算是执行”将数据库添加到树“操作,也是完全忽略了TNS_ADMIN变量,操作的是ORACLE目录中的TNSNAMES.ORA文件,显示的连接字符串信息也是从那个文件中得到的。下面是显示信息的截图:

然而,在用这个TNSNAME进行连接数据库时,却是按照TNS_ADMIN目录中的TNSNAMES.ORA文件的配置进行连接的,如果这两个TNSNAMES.ORA都有这个TNSNAME,那么不幸就发生了,本来我们期望是连接到OEM中显示的那个数据库上,结果却连接到了另一个库上。这可以是说OEM的重大BUG。

这里谈到的OEM是9i的版本,NetCA也有这个问题,但Net Manager没有这个问题。

事情虽然过去了,但是以下几件事情我们仍然值得我们牢记:

  • 有效的备份,特别是归档模式下的有效物理备份,是保证数据不会被丢失的前提。
  • 数据库用户权限的管理,需要遵循”最少权限“的原则,不可忽视。很多数据库管理人员为了方便,给Oracle用户太大的权限,甚至是DBA角色权限。这是非常危险的。
  • 有了备份仍然不够,需要做恢复测试,避免出现问题发现备份不能恢复,否则悔之晚矣。这次事故中,由于第三方的备份软件问题,导致数据恢复至少多花了三个小时的时间。要是之前有做过完整的测试,则会发现备份软件的问题。
  • 一些危险的操作,如删除用户,删除表等操作,一定要有规范的流程,确认无误后再执行。

还有以下我的一些个人观点:

  • 数据库管理时,尽量少用图形化的软件,一次DEL按键就能葬送整个系统。
  • 尽量将生产系统、测试系统与开发库隔离,比如禁止在开发机器上直接连接生产库,开发完成后,需要部署到生产库时,遵循专门的流程进行。也就是要规范开发流程。

本文源起小荷博客中的文章“一次cpu的user比例过高的调优”,文章中提到的占用CPU比较高的SQL语句,消耗的CPU比逻辑读还要高的SQL还要多。这两条SQL是:

SELECT NETWORKTYPEID FROM PROBLEM_TAB WHERE PREFIXNUM = SUBSTR(:B1 , 1, 7)
select provinceid into :b0 from PROBLEM_TAB where PREFIXNUM=substr(:b1,1,7)

我关注这个的原因是,为啥这两个SQL比较消耗CPU,比逻辑读更高的SQL消耗得还多。这里让我感兴趣的是,这里使用了函数,函数的参数使用了绑定变量。那么,有一个问题,那就是这个函数会被调用多少次?1次还是与表中所有行相同的次数?我的推测是对于结果确定性(deterministic)的函数,如果传入参数有绑定变量,那么会是被引用(如值的比较)的次数,如果传入的参数是确定的值,那么应该是1次。这个推测是不是正确的呢,让我们来做一些测试。

首先创建测试用的函数:

create or replace package pkg_test is

g_cnt number;

function f_substr(iv_str varchar2, iv_pos in number, iv_len in number)
return varchar2 deterministic;

end pkg_test;

create or replace package body pkg_test is

function f_substr(iv_str varchar2, iv_pos in number, iv_len in number)
return varchar2 deterministic is
begin
g_cnt:=g_cnt+1;
return substr(iv_str, iv_pos, iv_len);
end;
end pkg_test;

Read the rest of this entry

如果Oracle数据库hang住了,对Oracle做system dump,或做hang analyze,是研究和解决问题的有效办法,至少在提交SR时能够有更多的有用信息。如果能够连接数据库,并能够进行操作,那么用oradebug是简单快捷的办法。

但有的时候,数据库由于hang住,sqlplus不能连接时(在10g可以尝试用sqlplus -prelim连接数据库),可以使用操作系统上的调试工具来dump oracle系统状态。在记一次Oracle数据库无响应(hang住)故障的处理一文中,就曾使用dbx做systemstate dump,并发现问题所在,并最终解决了问题。下面是当时用dbx做dump的过程:

# dbx -a 446910
Waiting to attach to process 446910 …
Successfully attached to oracle.
Type ‘help’ for help.
reading symbolic information …
stopped in iosl.select at 0×9000000000c94d8 ($t2)
0×9000000000c94d8 (select+0xfffffffffff06318) e8410028 ld r2,0×28(r1)
(dbx) print ksudss(10)

Segmentation fault in slrac at 0×100083aa0 ($t2)
0×100083aa0 (slrac+0xe4) 88030000 lbz r0,0×0(r3)
(dbx) detach

从上面可以看到,使用dbx做dump的过程为:

  • 找到有异常的进程号,比如CPU非常高,HANG住的进程等。如果做系统范围的systemstate dump,可以是其他的进程。
  • dbx -a < 进程号>
  • print ksudss(10) --这里是直接调用ORACLE程序中的ksudss函数,dump level为10,就等同于在sqlplus 中用oradebug dump systemstate 10
  • detach
  • quit

在LINUX下可以使用gdb,下面是一个例子:

[oracle@xty ~]$ ps -ef | grep LOCAL
oracle 3765 3764 1 05:55 ? 00:00:00 oraclexty (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 3767 3668 0 05:55 pts/2 00:00:00 grep LOCAL
[oracle@xty ~]$ gdb $ORACLE_HOME/bin/oracle 3765
GNU gdb Red Hat Linux (6.1post-1.20040607.62rh)
Copyright 2004 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for details.
This GDB was configured as "i386-redhat-linux-gnu"...(no debugging symbols found)...Using host libthread_db library "/lib/tls/libthread_db.so.1".

Attaching to program: /u01/app/oracle/product/10.1.0/db_1/bin/oracle, process 3765
Reading symbols from /u01/app/oracle/product/10.1.0/db_1/lib/libskgxp10.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/10.1.0/db_1/lib/libskgxp10.so
Reading symbols from /u01/app/oracle/product/10.1.0/db_1/lib/libhasgen10.so...done.
Loaded symbols for /u01/app/oracle/product/10.1.0/db_1/lib/libhasgen10.so
Reading symbols from /u01/app/oracle/product/10.1.0/db_1/lib/libskgxn2.so...done.
Loaded symbols for /u01/app/oracle/product/10.1.0/db_1/lib/libskgxn2.so
Reading symbols from /u01/app/oracle/product/10.1.0/db_1/lib/libocr10.so...done.
Loaded symbols for /u01/app/oracle/product/10.1.0/db_1/lib/libocr10.so
Reading symbols from /u01/app/oracle/product/10.1.0/db_1/lib/libocrb10.so...done.
Loaded symbols for /u01/app/oracle/product/10.1.0/db_1/lib/libocrb10.so
Reading symbols from /u01/app/oracle/product/10.1.0/db_1/lib/libocrutl10.so...done.
Loaded symbols for /u01/app/oracle/product/10.1.0/db_1/lib/libocrutl10.so
Reading symbols from /u01/app/oracle/product/10.1.0/db_1/lib/libjox10.so...done.
Loaded symbols for /u01/app/oracle/product/10.1.0/db_1/lib/libjox10.so
Reading symbols from /u01/app/oracle/product/10.1.0/db_1/lib/libclsra10.so...done.
Loaded symbols for /u01/app/oracle/product/10.1.0/db_1/lib/libclsra10.so
Reading symbols from /u01/app/oracle/product/10.1.0/db_1/lib/libdbcfg10.so...done.
Loaded symbols for /u01/app/oracle/product/10.1.0/db_1/lib/libdbcfg10.so
Reading symbols from /u01/app/oracle/product/10.1.0/db_1/lib/libnnz10.so...done.
Loaded symbols for /u01/app/oracle/product/10.1.0/db_1/lib/libnnz10.so
Reading symbols from /usr/lib/libaio.so.1...done.
Loaded symbols for /usr/lib/libaio.so.1
Reading symbols from /lib/libdl.so.2...done.
Loaded symbols for /lib/libdl.so.2
Reading symbols from /lib/tls/libm.so.6...done.
Loaded symbols for /lib/tls/libm.so.6
Reading symbols from /lib/tls/libpthread.so.0...done.
[Thread debugging using libthread_db enabled]
[New Thread -1219938624 (LWP 3765)]
Loaded symbols for /lib/tls/libpthread.so.0
Reading symbols from /lib/libnsl.so.1...done.
Loaded symbols for /lib/libnsl.so.1
Reading symbols from /lib/tls/libc.so.6...done.
Loaded symbols for /lib/tls/libc.so.6
Reading symbols from /lib/ld-linux.so.2...done.
Loaded symbols for /lib/ld-linux.so.2
Reading symbols from /lib/libnss_files.so.2...done.
Loaded symbols for /lib/libnss_files.so.2
0x006967a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2
(gdb) print ksudss(10)
[Switching to Thread -1219938624 (LWP 3765)]
$1 = 213658428
(gdb) detach
Detaching from program: /u01/app/oracle/product/10.1.0/db_1/bin/oracle, process 3765
(gdb) quit

然后我们可以找到有dump结果的trace文件:

Read the rest of this entry

,

我们知道,在Oracle 10g中,如果数据库实例hang住了,应用及sqlplus都不能连接时,可以用sqlplus -prelim连接数据库。那么sqlplus连接时,加上-prelim这个参数有什么特别的地方呢?下面,让我们来研究一下:

测试环境:Linux AS4上的Oracle 10.2.0.1,客户端(sqlplus)版本为Windows 2003下的10.2.0.1。

首先在数据库上启用10046事件:

SQL> alter system set events '10046 trace name context forever';

System altered.

先用普通的方式连接:

D:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 11月 15 15:36:02 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys/manage@xty as sysdba
已连接。
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开

再看看加prelim参数时的情况:

D:\>sqlplus -prelim /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 11月 15 15:36:34 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys/manage@xty as sysdba
初级连接已建立
SQL> exit
从 ORACLE 断开

从上面的信息可以看到,在使用prelim连接时,提示为“初级连接已建立”,退出sqlplus没有显示banner。
也可以通过下面的方式来用prelim方式连接数据库:

[oracle@xty ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Dec 2 07:04:28 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> set _prelim on
SQL> connect / as sysdba
Prelim connection established

再看看数据库的10046 trace:

从生成的trace文件中,可以发现在正常连接时,连接上数据库后,sqlplus自动执行了下面的SQL:

ALTER SESSION SET NLS_LANGUAGE= 'SIMPLIFIED CHINESE' NLS_TERRITORY= 'CHINA' NLS_CURRENCY= '¥' NLS_ISO_CURRENCY= 'CHINA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'SIMPLIFIED CHINESE' NLS_SORT= 'BINARY' TIME_ZONE= '+08:00' NLS_COMP= 'BINARY' NLS_DUAL_CURRENCY= '¥' NLS_TIME_FORMAT= 'HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT= 'DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT= 'HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT= 'DD-MON-RR HH.MI.SSXFF AM TZR'

select value$ from props$ where name = 'GLOBAL_DB_NAME'

select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE_NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME')

select decode(failover_method, NULL, 0 , 'BASIC', 1, 'PRECONNECT', 2 , 'PREPARSE', 4 , 0), decode(failover_type, NULL, 1 , 'NONE', 1 , 'SESSION', 2, 'SELECT', 4, 1), failover_retries, failover_delay, flags from service$ where name = :1

而sqlplus使用prelim连接上数据库后,没有生成10046 trace文件,看起来没有执行SQL,也就是没有执行任何初始化动作和查询必要的信息。也许这也就是称之为“初级连接”的来历吧。

由于使用prelim方式连接,没有执行sql语句的,所以在数据库的某些hang住的情况下,能够连接上数据库。比如由于library cache latch 被长时间持有不能释放,不能解析SQL语句引起的hang。有的人会说,我的应用刚连上去还没做任何操作就hang住了。这只是表面现象,连接上数据库后,一般都会做一些初始化的操作,如设定环境之类的。

sqlplus -prelim能够在数据库hang住的情况下连接数据库,但只能说是连接,并不代表能够做很多操作。比如执行SQL查询。这种情况下,可能最有用的就是使用oradebug。

本文只是简单地“inside”,其实并不很深入,Oracle还会有其他的跟prelim有关的东西,是我们还没发现的。有兴趣的朋友,可以进一步研究。比如通过抓取tns包进行分析等等。

,

前面两篇文章简要介绍了db_block_checking和db_block_checksum参数,并提到这两个参数对性能的影响。下面做个测试:

首先建一个测试表,并设置db_block_checking和db_block_checksum为false:

SQL> create table t2 (a int) tablespace test;

表已创建。
SQL> alter system set db_block_checking=false;

系统已更改。

SQL> alter system set db_block_checksum=false;

向测试表T2中四次分别插入100,000行数据:

SQL> begin
2 for i in 1..100000 loop
3 insert into t2 values(i);
4 end loop;
5 end;
6 /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 06.02
SQL> commit;

提交完成。

已用时间: 00: 00: 00.00
SQL> alter system checkpoint;

系统已更改。

已用时间: 00: 00: 01.02
SQL> begin
2 for i in 1..100000 loop
3 insert into t2 values(i);
4 end loop;
5 end;
6 /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 05.04
SQL> commit;

提交完成。

已用时间: 00: 00: 00.00
SQL> alter system checkpoint;

系统已更改。

已用时间: 00: 00: 01.02
SQL> begin
2 for i in 1..100000 loop
3 insert into t2 values(i);
4 end loop;
5 end;
6 /

PL/SQL 过程已成功完成。

Read the rest of this entry

在之前的一篇文章db_block_checking和db_block_checksum,简要地描述了db_block_checking和db_block_checksum这两个参数的作用以及性能方面的影响。在10gR1及这前的版本中,这两个参数可以设置为false和true。而在10gR2版本中,这两个参数发生了一点变化。

先来看看db_block_checking,这个参数现在有四个可能的设置:

  • OFF - 与原来的FALSE一样,对非SYSTEM表空间的块关闭检查,这个值在设置时仍然可以用false。
  • LOW - 只检查块头。这个检查发生在当块的内容在内存中发生改变时,比如UPDATE、INSERT、DELETE等,以及将块从磁盘读入、RAC结点间块的传输。
  • MEDIUM - 比LOW更高一级,还包括了非IOT的表的块内部检查(即不仅仅是块头)。
  • FULL - 与原来的TRUE一样,与MEDIUM相比,还包括了索引块的检查。

再看看db_block_checksum这个参数有什么变化:

  • OFF - 与原来的FALSE一样,只会给SYSTEM表空间的块计算checksum值。
  • TYPICAL - 与原来的TRUE一样,Oracle在向磁盘写入块时计算checksum值,下次读入时进行校验。
  • FULL - 这是新增的值,Oracle不关在写入块时计算checksum值,而且在更改块(比如执行UPDATE语句等)之前对checksum值进行校验,同时在更改块之后对checksum值进行重新计算。另外Oracle也会在写入日志块时,计算块的checksum。这个设置大大增加了系统负荷,大约带来了4-5%的负荷。而TYPICAL值会带来1-2%的负荷。