老熊的三分地-Oracle及数据恢复

前面两篇文章简要介绍了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%的负荷。

Oracle数据库的global_name，在Database Link与GLOBAL_NAMES参数一文中提到了，设置global_names初始化参数为true后，本地的数据库链接名称必须与远程数据库的global_name相同，才能正常使用数据库链接。那么怎么查询数据库的global_name呢？

SQL> col global_name for a30
SQL> select * from global_name;

GLOBAL_NAME
------------------------------
DMDB

那么怎么样修改global_name？

ALTER DATABASE
RENAME GLOBAL_NAME TO NEW_NAME;

注意不要直接用update global_name set global_name=''将global_name设置为空，否则数据库不能启动，会报ORA-00600[18061] 或 ORA-00600[18062]这样的错误。 只有用备份进行恢复后才能打开。（参见metalink note 743676.1）。

那么global_name到底是个什么对象呢？

SQL> select owner,object_name,object_type from dba_objects where object_name='GLOBAL_NAME';

OWNER OBJECT_NAME OBJECT_TYPE
---------- -------------------- --------------------
SYS GLOBAL_NAME VIEW
PUBLIC GLOBAL_NAME SYNONYM

SQL> select text from dba_views where view_name='GLOBAL_NAME';

TEXT
----------------------------------------------------------------
select value$ from sys.props$ where name = 'GLOBAL_DB_NAME'

Read the rest of this entry

如果一个表的外键引用的是另一个用户的表，需要特别的权限吗？答案就是refrences权限。虽然一个schema（用户）下表的外键引用的是其他schema（用户）的表，是一种不太好的设计。但现实中仍然会有这种情况。下面来看看reference的作用：

测试环境：
Oracle 10.2.0.1
Redhat Linux AS4
数据库里用于测试的两个用户test1和test2，只有connect角色权限和表空间使用权限。

SQL> connect / as sysdba
Connected.
SQL> create table test1.t1 as select * from dba_objects where rownum< =1000; Table created. SQL> create table test2.t2 as select * from dba_objects where rownum< =1000; Table created. SQL> alter table test1.t1 add constraint pk_t1 primary key(object_id);

Table altered.

现在，我们用用户test2连接到数据库，在表test2.t2的object_id字段上增加一个外键，外键引用test1.t1表的object_id字段：

SQL> connect test2/test
Connected.
SQL> alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id);
alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id)
*
ERROR at line 1:
ORA-00942: table or view does not exist

我们将test1.t1表的查询权限赋给test2：

SQL> grant select on test1.t1 to test2;

Grant succeeded.

再次增加外键：

SQL> alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id);
alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id)
*
ERROR at line 1:
ORA-01031: insufficient privileges

可以看到报权限不足。我们再看看如果将DBA权限给test2会怎么样：

Read the rest of this entry

环境：HP-UX 11.31
Oracle 9.2.0.8

数据库异常崩溃，询问维护人员之前有大量数据操作。查看alert日志：

Thu Oct 9 02:20:18 2008
Errors in file /oracle/OraHome1/rdbms/log/acct_ora_11361.trc:
ORA-00600: internal error code, arguments: [ktprhtnew6], [], [], [], [], [], [], []
Thu Oct 9 02:20:19 2008
Fatal internal error happened while SMON was doing active transaction recovery.
Thu Oct 9 02:20:19 2008
Errors in file /oracle/OraHome1/rdbms/log/acct_ora_11361.trc:
ORA-00600: internal error code, arguments: [ktprhtnew6], [], [], [], [], [], [], []
SMON: terminating instance due to error 600
Instance terminated by SMON, pid = 11361

检查trace文件，没有发现有用的信息。

重启数据库，数据库能打开，但不到一分钟实例就crash。在alert日志中的错误信息均如上所示。
在网上及在metalink中以ktprhtnew6为关键字搜索，没有找到相似的BUG和案例。同时当时手边是用的163拨号上网，速度非常慢，只有通过分析来解决问题。

仔细分析alert日志可以发现是在做事务恢复时SMON出错，导致实际中止：

Fatal internal error happened while SMON was doing active transaction recovery.

我们知道ORA-600错误后面参数，如果像ktprhtnew6这样的均表示出错的函数。这里可以看出是在做并行恢复：

[K]enerl [T]ransaction [P]arallel [R]ecovery

设置fast_start_parallel_rollback参数为false，关闭数据库的并行恢复功能，重启数据库，数据库正常，故障消失。
看起来这应该又是一个BUG。

Oracle的监听(Listener)在缺省情况下，会在文件中记录日志，记录数据库实例注册操作、客户端的连接等。缺省（没有设置log_file参数时）的文件是$ORACLE_HOME/network/log/listener.log。对于一些使用短连接的，频繁的连接数据库的应用，listener.log增长很快。有的可以在比较短的时间内（十几天）就可以超过2GB。对于一些平台的某些版本的Oracle，在监听日志增大到2GB以后会导致监听不能正常工作（我没遇到过，不过感兴趣的朋友可以在网上搜索一下，有这样的案例）。

对于这种listener.log增长非常迅速的系统，可以关闭监听日志，不让监听写日志到文件。也可以写个job定期清理。本文主要描述怎么样关闭监听日志：

可以在监听命令行接口中使用命令：
D:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 9.2.0.1.0 - Production on 15-10月-2008 20:52:11

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

欢迎来到LSNRCTL，请键入"help"以获得信息。

LSNRCTL> set log_status off
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dreamf)(PORT=1521)))
LISTENER 参数 \log_status\ 被设为 OFF
命令执行成功
LSNRCTL> save_config
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dreamf)(PORT=1521)))
未保存对LISTENER所作的更改
命令执行成功
LSNRCTL>

我们可以在listener.ora文件中可以看到增加了下面的内容：

#----ADDED BY TNSLSNR 15-10月-2008 10:05:43---
LOGGING_LISTENER = OFF
#---------------------------------------------

所以我们也可以在listener.ora文件增加上面的内容来关闭监听日志。但是只有在监听重启后才会生效，而通过lsnrctl 这个命令接口设置，可以立即生效，Windows平台上的文件会立即关闭。因此可以利用这个特性，用来删除WINDOWS平台上的监听日志文件，因为不这样，在监听运行时监听日志是不能删除的。

在Oracle的备份恢复过程中，需要注意数据文件的unrecoverable，不适当的操作很容易造成恢复后有大量的坏块。在视图v$datafile中，UNRECOVERABLE_CHANGE#和UNRECOVERABLE_TIME分别表示数据文件最后一个unrecoverable操作的change#和时间。unrecoverable通常就是指不记录日志的操作（nologging），这样当用一个旧的数据文件还原后，用日志进行恢复时，由于日志文件没有记录unrecoverable的操作时的日志，导致那些操作的数据块为逻辑坏块（实际上在日志文件中为这样的操作产生了一些重做日志项，在恢复时，根据这些重做日志项，直接将相应的数据块标记为坏块）。常见的以下几种情况：
1. 非归档模式下的create table as 操作和直接路径插入（如加了append hint的insert语句和直接路径装载）
2. 归档模式下的create table xxx nologging（即创建表时为表指定了nologging）和nologging表的直接路径插入。
在数据库（或表空间）为force logging时，任何操作都会记录日志。不会有unrecoverable操作。

下面先做个实验（数据库版本为9.2.0.1）来看看这两列：

数据库当前处于非归档模式;

SQL> select name,checkpoint_time,unrecoverable_time from v$datafile where file#=10;

NAME CHECKPOINT_TIME UNRECOVERABLE_TIME
---------------------------------------- ------------------- -------------------
D:\ORACLE\ORADATA\XJ\TEST01.DBF 2008-09-23 09:28:22

SQL> create table t tablespace test nologging as select * from dba_objects where rownum< =10; 表已创建。 SQL> select name,checkpoint_time,unrecoverable_time from v$datafile where file#=10;

NAME CHECKPOINT_TIME UNRECOVERABLE_TIME
---------------------------------------- ------------------- -------------------
D:\ORACLE\ORADATA\XJ\TEST01.DBF 2008-09-23 09:28:22

可以看到，unrecoverable_time为空。想一想就可以理解，unrecoverable操作都是将数据直接写入了数据文件，没有经过SGA的缓存，非归档模式下的物理备份都是一致的冷备份，不需要日志来进行恢复，因此对于非归档模式下并不存在unrecoverable操作。unrecoverable只是针对归档模式的。下面将数据库置为归档模式后，重复上述过程，进行验证：

SQL> create table t tablespace test nologging as select * from dba_objects where rownum< =10; 表已创建。 SQL> select name,checkpoint_time,unrecoverable_time,unrecoverable_change# from v$datafile where file#=10;

NAME CHECKPOINT_TIME UNRECOVERABLE_TIME UNRECOVERABLE_CHANGE#
---------------------------------------- ------------------- ------------------- ---------------------
D:\ORACLE\ORADATA\XJ\TEST01.DBF 2008-09-23 09:45:03 2008-09-23 09:45:41 1298047

可以看到，v$datafile视图中unrecoverable_time和unrecoverable_change#已经有了值。

下面来看看unrecoverable_time是最后一次unrecoverable操作的开始时间还是结束时间？
创建一个具有延时功能的函数：

create or replace function f_cdate return date
as
begin
dbms_lock.sleep(10);
return sysdate;
end;

SQL> create table t (d date) nologging tablespace test;

表已创建。

SQL> begin
2 dbms_output.put_line('start test:'||sysdate);
3 insert /*+ append */ into t select f_cdate from dba_objects where rownum< =10; 4 dbms_output.put_line('after insert:'||sysdate); 5 dbms_lock.sleep(60); 6 commit; 7 dbms_output.put_line('end test:'||sysdate); 8 end; 9 / start test:2008-09-23 10:31:50 after insert:2008-09-23 10:33:33 end test:2008-09-23 10:34:34 PL/SQL 过程已成功完成。 SQL> select name,checkpoint_time,unrecoverable_time,unrecoverable_change# from v$datafile where file#=10;

NAME CHECKPOINT_TIME UNRECOVERABLE_TIME UNRECOVERABLE_CHANGE#
---------------------------------------- ------------------- ------------------- ---------------------
D:\ORACLE\ORADATA\XJ\TEST01.DBF 2008-09-23 09:45:03 2008-09-23 10:33:33 1299032
SQL> begin
2 dbms_output.put_line('start test:'||sysdate);
3 insert /*+ append */ into t select f_cdate from dba_objects where rownum< =10; 4 dbms_output.put_line('after insert:'||sysdate); 5 dbms_lock.sleep(60); 6 rollback; 7 dbms_output.put_line('end test:'||sysdate); 8 end; 9 / start test:2008-09-23 10:37:59 after insert:2008-09-23 10:39:42 end test:2008-09-23 10:40:43 PL/SQL 过程已成功完成。 SQL> select name,checkpoint_time,unrecoverable_time,unrecoverable_change# from v$datafile where file#=10;

NAME CHECKPOINT_TIME UNRECOVERABLE_TIME UNRECOVERABLE_CHANGE#
---------------------------------------- ------------------- ------------------- ---------------------
D:\ORACLE\ORADATA\XJ\TEST01.DBF 2008-09-23 09:45:03 2008-09-23 10:39:42 1299157

可以看到unrecoverable_time为unrecoverable操作完成的那个时间，不管事务是否提交。

对于数据库备份后的恢复，需要注意查询v$datafile视图中关于unrecoverable操作时间，如果unrecoverable操作时间在数据文件备份之后（更精确的比较是通过change#，比较文件的checkpoint_change#和unrecoverable_change#），则恢复会产生坏块。

建议重要的数据库，将数据库置为force logging（当然数据库应当是归档模式），避免无意的产生了unrecoverable操作。或者在做了unrecoverable操作之后立即进行数据文件的备份。

PS：关于不产生日志的操作，请参见metalink NOTE:269274.1 CHECK FOR LOGGING/NOLOGGING ON DB OBJECT(S)

先说说这个数据库的环境：
Oracle 9.2.0.4 RAC，只不过这个RAC只运行了一个节点，另一节点没有开启。
AIX 5.3 TL04
主机为p550，4CPU，16G内存
应用为部署在Weblogic下的WEB应用。

故障现象：
首先是客户端的操作没有响应，从weblogic上看连接数非常高，其日志里面不停报超出连接池的最大连接数。在主机上用sqlplus "/ as sysdba"，在显示sqlplus的banner后，停止响应。

从故障现象来看，是数据库hang住了。

由于sqlplus不能操作，那么这个时候没办法通过oracle来dump system state。先看看操作系统里面，用topas命令观察，发现一个oracle进程占用了26%左右的CPU资源，IO等待几乎为0，可用的物理内存还比较多。根据那个占用CPU的进程号用ps命令查看，是一个普通的Server Process。

看来起这个进程陷入死循环了，26%的CPU资源正好是1个CPU（因为系统共4个CPU）。如果一个oracle进程拿到比较重要的资源，比如shared pool latch、library cache latch等，然后陷入了死循环（SPIN）后，其他进程没法解析SQL等，也就只有挂起了。

用kill命令杀掉那个进程，系统恢复正常，看来前面对故障的推断是正确的，不过没过几分钟，又出现了此故障现象。

只有找到oracle当时正在干什么，才能进行处理。用dbx来dump system state：

# 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 0x9000000000c94d8 ($t2)
0x9000000000c94d8 (select+0xfffffffffff06318) e8410028 ld r2,0x28(r1)
(dbx) print ksudss(10)

Segmentation fault in slrac at 0x100083aa0 ($t2)
0x100083aa0 (slrac+0xe4) 88030000 lbz r0,0x0(r3)
(dbx) detach

Read the rest of this entry

经常遇到客户和其他一些Oracle开发与维护人员，问我为啥使用了RAC，没有感受到业务系统有明显的性能提升，有时反而觉得性能有所下降。这种认为RAC一定能够提高性能的想法，有着广泛的“群众基础”。可以说，使用RAC来提高性能是一种存在于广大ORACLE数据库使用者之间的误解。

这里我不想过多于技术上去解答这个问题，而是从下面这个类比来说明这个问题：

这里我们要谈论的是大部分的业务系统类型，事务处理型，也就是OLTP。虽然很多OLTP类型的系统还兼有生成一些报表和统计数据的功能，但那只是一部分小的功能，主要还是事务处理。

大家都去过银行，假设一个银行营业厅有6个业务窗口，来这个营业厅办理业务的客户一般为3至5个人，最多6个人。由于每个人办理业务的时间，是跟他（她）的业务类型有关的，比如取款2分钟，存款2分钟，开户要5分钟等等，不会以窗口数的增多而减少时间。以这个例子来说，6个窗口已经足够了，因为6个窗口数大于同时办理业务的客户数，而一个客户只会在一个窗口办理业务，就算再多的业务处理窗口，也不会对每个客户办理业务有速度上的提升。

现在假设银行的业务有了很大的发呢，银行营业厅里面的客户比较多了，同时来办理业务的常常超过10人，这个时候就是银行营业厅的窗口不够了（资源不足），客户存在了排队，严重影响了客户办理业务的效率。而营业厅由于受面积的限制，不能增加窗口了（对于机器来说，不能扩容了），这个时候银行在附近又开了一个新的营业厅（增加了一个新的结点），那样部分客户分流到了新的营业厅，这样消除了客户的排队，客户又能够高效率地在银行办理业务了。

使用RAC类似于上面提到的银行，如果业务系统能够在单台机器上跑，这个时候由于资源足够，增加新的结点不会带来性能上的提升，而如果随着业务的发展，机器资源受限，不能为更多的用户服务，这个时候增加新的结点，能够使业务系统能够为更多的用户服务。

然而在现实生活中，很多业务系统并没有为RAC进行一些优化，同时RAC的结点之间由于数据同步的代价比较高，因而使用RAC后往往感受到业务系统并没有更快，有时感觉反而更慢。

RAC的作用更体现于高可用性、水平可扩展性，其次才是某些条件下的性能提升（比如针对于某些DSS系统）。

下面一条SQL能够使用索引吗？

select object_id,object_name,object_type from t1 where object_id is null;

有人会说，索引不存储null值，所以这个SQL不会使用索引；也有人会说，能够使用索引啊，只要在object_id列上建位图索引就可以使用索引了，另外对于CLUSTER表的KEY列，null值也可以存储在索引中（也就是能够进行索引）。

本文要探讨的是，null值不能进行索引的真正含义：
实际上对于null值，除了位图索引、CLUSTER表的KEY列，也是有可能索引的。那就是复合索引，也就是多列索引。对于普通的索引，null值不能进行索引的正确理解应该是，对于某一行，索引的所有列的值都是null值时，该行才不能被索引。
那么对于上面的SQL语句，我们就可以想办法让它走索引。下面举例说明：

SQL> desc t1
名称 是否为空? 类型
----------------------------------------- -------- --------------
OWNER VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(18)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> create index t1_idx on t1(object_id,object_name);
SQL> analyze table t1 compute statistics for table for all indexes for all indexed columns;

表已分析。
SQL> select object_id,object_name,object_type from t1 where object_id is null;

OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ------------------------------
XTY DATABASE LINK

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=38)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=3 Card=1 Bytes
=38)

2 1 INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=2 Card
=1)

我们可以看到，这个SQL语句走了我们建的那个索引T1_IDX。
然而，如果我们把object_name列改为允许NULL值，那么情况就发生了变化：

SQL>alter table t1 modify (object_name null);
SQL> select object_id,object_name,object_type from t1 where object_id is null;

OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ ------------------------------
XTY DATABASE LINK

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=128 Card=1 Bytes=38)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=128 Card=1 Bytes=38)

把object_name列改为允许NULL值后，object_id和object_name列 有可能同时为NULL值，这个时候就存在着不被索引的行（就是有可能存在object_id值为null的行没有被索引），这样就只能走全表扫描了。

我们也有一种更简便的方法，不需要与其他列建立复合索引，而是与一个常量值建立复合索引：

SQL> create index t1_idx on t1(object_id,0);

SQL> analyze table t1 compute statistics for table for all indexes for all indexed columns;

SQL> explain plan for select object_id,object_name,object_type from t1 where object_id is null;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    81 |     3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |     1 |    81 |     3 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX      |     1 |       |     2 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

2 - access("T1"."OBJECT_ID" IS NULL)

Note: cpu costing is off

引申开来，如果有类似于col_name is null这样的条件的SQL语句，希望能够使用索引，那么将col_name与其他NOT NULL的列或一个常量值建一个复合索引，也是一个可能的选择。