Experiments – Oracle and Linux

Oracle索引组织表学习系列三(完)

11月 23,2012 留下评论 Oracle, oracle 11g

5 索引组织表(IOT Index-Organized Table)的键压缩

在创建IOT表时，有一个存储选项NOCOMPRESS，这个选项的意思是不压缩，即对IOT表的索引条目不压缩。该选项不单对IOT表有效，同样对B-Tree索引也适用。

与NOCOMPRESS选项对应的就是COMPRESS N，这个N只能取值整数，表示要压缩的列数，在数据块儿级别提取"公因子"，用以避免存储重复值。如IOT表的主键是(col1,col2,col3)的联合主键的话，那么COMPRESS 2就可以表示，如果在表中重复出现多个col1,col2的重复值的话，那么Oracle就只存储col1,col2 1次，对于重复的记录不再存储。所以，达到压缩的目的。

接下来，看一个示例：

1 创建一个NOCOMPRESS的IOT表：

[oracle@oracle11g ~]$ sqlplus 

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 23 14:21:43 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> conn hr/hr
Connected.
SQL> create table iot
  2  ( owner, object_type, object_name,
  3  constraint iot_pk primary key(owner,object_type,object_name)
  4  )
  5  organization index
  6  NOCOMPRESS
  7  as
  8  select distinct owner, object_type, object_name
  9  from all_objects;

Table created.

SQL> select count(*) from iot;

  COUNT(*)
----------
     55646

SQL> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

no rows selected

SQL>

看到表里有55646条记录。

2 用ANALYZE INDEX VALID STRUCTURE来分析这个IOT表的主键：

SQL> analyze index iot_pk validate structure;

Index analyzed.

SQL> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

   LF_BLKS    BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
       347          3    2493786              2               33

SQL>

看到这个IOT表的索引使用了347个叶子块，3个分枝块，占用空间大小是2493786字节，约2.4M。

3 使用COMPRESS 1选项来重建IOT表：

SQL> alter table iot move compress 1;

Table altered.

SQL> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

no rows selected

SQL> analyze index iot_pk validate structure;

Index analyzed.

SQL> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

   LF_BLKS    BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
       304          3    2178229              2               23

SQL>

然后，看到这个IOT表的索引现在使用了304个叶子块，3个分枝块，占用空间大小是2178229字节，约2.1M。

4 使用COMPRESS 2继续压缩这个IOT表：

SQL> alter table iot move compress 2;

Table altered.

SQL> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

no rows selected

SQL> analyze index iot_pk validate structure;

Index analyzed.

SQL> select lf_blks, br_blks, used_space, opt_cmpr_count, opt_cmpr_pctsave from index_stats;

   LF_BLKS    BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
       233          1    1670153              2                0

SQL>

最终，看到这个IOT表的索引现在使用了233个叶子块，1个分枝块，占用空间大小是1670153字节，约1.6M。

说明：

从上可以看出，使用不同的压缩级别，索引的占用的空间大小逐渐变小，从最初的2.4M变成1.6M。叶子块从最初的347变成233个，分枝块最终只有1个。

对于上述分析索引之后的OPT_CMPR_COUNT字段表示最优压缩数，即对于该索引而言如果使用COMPRESS 2来压缩的话，可以得到最优的压缩效果。

同时OPT_CMPR_PCTSAVE字段值则表示最优压缩节省的空间百分比，针对第一次分析之后的结果，可以看到OPT_CMPR_PCTSAVE为33，即可以节省大约33%的空间。如下，压缩后1670153大约是压缩前2493786的2/3，即节约了1/3的空间：

SQL> select 1670153/2493786 from dual;

1670153/2493786
---------------
     .669725871

SQL>

6 索引组织表(IOT Index-Organized Table)的INCLUDING和PCTTHRESHOLD参数选择

对于IOT表而言，如果在建表的语句中同时使用了INCLUDING和PCTTHRESHOLD参数的话，那么PCTTHRESHOLD参数的级别较高。

PCTTHRESHOLD：行中的数据量超过数据块的这个百分比时，行中其它的列则存放到OVERFLOW段中；

INCLUDING：行中从第一列直到INCLUDING子句中指定的列(包括该列)都存放在索引块中，其它的列存放到OVERFLOW段中。

即，对于大小为8K的数据块而言，有下面的IOT表：

SQL> create table iot1
  2  (id number,
  3  name char(2000),
  4  addr char(2000),
  5  salary number,
  6  constraint iot1_pk primary key(id,name)
  7  )
  8  organization index
  9  pctthreshold 50
 10  overflow
 11  including addr;

Table created.

SQL>

那么，如果PCTTHRESHOLD参数指定为50的话，那么索引块中至多会使用4K的空间来存放主键字段和非主键字段，其它字段则存放到OVERFLOW段中，而会忽略此时的INCLUDING选项，并非将addr字段随主键字段一起存放，而是将其存放到溢出段中。

关于IOT表的学习，先到这个地方，以后有新的关于IOT表的知识，再补充在这里！

Oracle索引组织表学习系列一

10月 24,2012 留下评论 Experiments, Oracle

1 索引组织表(IOT Index-Organized Table)概述

在Oracle数据库中，有一类表被称之为索引组织表，即IOT(Index-Organized Table)。顾名思义，所谓的索引组织表，表面上看是一种表，实质上其数据是以索引的形式来存放的，也就是说IOT表不会占用表段，其占用的是索引段。

相比较传统的堆表( heap-organized table，即常见的普通表)而言，IOT表的数据是以已经按主键字段排好序后存放在B-tree索引中的，而堆表的数据则是无序的存放在表段的数据块中。此外，在IOT的索引叶子节点块中，既存放主键字段数据，也存放非主键字段的值。

一张IOT表同Heap表的对比示意图：

2 索引组织表(IOT Index-Organized Table)特征

通过IOT表的主键字段来访问数据可以快速完成，因为IOT表的数据全部存放在B-Tree索引上，只需定位到索引上的数据即可，而无需再去像访问heap表那样进一步通过索引去定位表段上的数据；对IOT表执行DML操作，只会影响到B-Tree索引；

通过IOT表的主键字段快速范围访问数据很快，因为记录已经是事先按主键排好序的；

IOT表可以有效的降低存储开销，因为主键字段的数据只是存放在B-Tree索引上，并没有像heap那样，主键字段数据既存放在表段上，也存放在索引上；

IOT表除了像Heap表那样可以支持约束、触发器、LOB字段、对象字段、分区、并行操作、在线重定义、复制操作等，还支持主键字段压缩、提供溢出存储段(Overflow storage area )、二次索引(Secondary indexes, including bitmap indexes)

3 创建索引组织表(IOT Index-Organized Table)

创建IOT表时，必须包含下列2个从句：

1 ORGANIZATION INDEX，用来标识该表是IOT表；

2 在建表的同时要指定主键约束，可以是单字段主键，也可以是复合主键约束。

创建IOT表时，也可以同时指定下列3个从句：

1 OVERFLOW从句，用于标识非主键字段存放在独立的溢出存储段数据区；

2 PCTTHRESHOLD value，如果指定了溢出存储段的话，该值用于限定可以存放在索引数据块中的最大数据的百分比，即如果IOT表中的行记录超过该值的话，剩余的字段就存放在溢出存储段数据区。也就是说，IOT表中的一条记录有可能被拆分成两部分：头数据区(Head Piece)和尾数据区(Tail Piece)。将主键字段和不超过PCTTHRESHOLD限定的其它非主键字段存放在头数据区，而将其它的非主键字段存放在尾数据区。因此，此时的IOT表的索引记录存放的数据就成了主键字段+满足PCTTHRESHOLD限定的其它非主键字段+指针，指针指向剩余非主键字段存放的地址；PCTTHRESHOLD的取值范围是1-50，默认值是50；

3 INCLUDING从句，用于显示声明哪些非主键字段可以和主键字段一起存放在索引数据块中。这样，剩下的非主键字段就会存放到独立的溢出存储段数据区。

创建IOT表的一个示例：

OS版本：

[oracle@oracle11g ~]$ uname -rm
2.6.18-194.el5 x86_64
[oracle@oracle11g ~]$

Oracle版本：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

建表：

SQL> conn hr/hr
Connected.
SQL> CREATE TABLE admin_docindex(
  2  token char(20), 
  3  doc_id NUMBER,
  4  token_frequency NUMBER,
  5  token_offsets VARCHAR2(2000),
  6  CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id))
  7  ORGANIZATION INDEX 
  8  TABLESPACE admin_tbs
  9  PCTTHRESHOLD 20
 10  OVERFLOW TABLESPACE admin_tbs2;

Table created.

SQL> insert into ADMIN_DOCINDEX values('oracleonlinux.cn',1,1,'test iot table');

1 row created.

SQL> commit;

Commit complete.

SQL>

查看该表的信息，可以看到该表不属于任何表空间，因为没有数据段：

SQL> select table_name,tablespace_name,iot_name,iot_type from user_tables where table_name='ADMIN_DOCINDEX';

TABLE_NAME                     TABLESPACE_NAME                IOT_NAME                       IOT_TYPE
------------------------------ ------------------------------ ------------------------------ ------------
ADMIN_DOCINDEX                                                                               IOT

SQL> select segment_name,segment_type,tablespace_name from user_segments where SEGMENT_NAME='ADMIN_DOCINDEX';

no rows selected

SQL>

查看索引的信息，我们能看到索引反而占有表空间：

SQL> select index_name,index_type,table_name,tablespace_name,table_type ,PCT_THRESHOLD from user_indexes where table_name='ADMIN_DOCINDEX';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     TABLESPACE_NAME                TABLE_TYPE  PCT_THRESHOLD
------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- -------------
PK_ADMIN_DOCINDEX              IOT - TOP                   ADMIN_DOCINDEX                 ADMIN_TBS                      TABLE                  20

SQL> select segment_name,segment_type,tablespace_name from user_segments where SEGMENT_NAME='PK_ADMIN_DOCINDEX';

SEGMENT_NAME                                                                      SEGMENT_TYPE       TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ------------------------------
PK_ADMIN_DOCINDEX                                                                 INDEX              ADMIN_TBS

SQL>

最后，关于IOT表的学习下篇继续。

Oracle 11g Automatic Maintenance Tasks

3月 28,2012 留下评论 Experiments, Oracle

从Oracle 11g开始，Oracle的自动化维护任务更智能化了，默认情况下，在安装数据库的过程中，如果启用自动化维护任务的话，数据库会在工作日的每晚22:00到第二天的凌晨2:00，周末的凌晨6:00到第二天的凌晨2:00,自动对数据库进行诸如优化器的统计信息收集、自动SQL的优化。且在自动化维护的过程中，数据库会使用较少的CPU资源，以防止自动化维护任务使用到过多的资源而影响到用户的正常使用。

其一，在使用DBCA建库的时候，我们可以看到这一自动维护任务的选项：

其二，如果启用了自动化维护任务的话，也可以从数据库的alert日志里看到下述信息：

Fri Mar 23 22:00:00 2012
Setting Resource Manager plan SCHEDULER[0x3007]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Fri Mar 23 22:00:00 2012
Starting background process VKRM
Fri Mar 23 22:00:00 2012
VKRM started with pid=24, OS id=10743
Fri Mar 23 22:00:33 2012
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Fri Mar 23 22:01:32 2012
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Fri Mar 23 22:05:56 2012
Thread 1 cannot allocate new log, sequence 8
Private strand flush not complete
  Current log# 1 seq# 7 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 7 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Thread 1 advanced to log sequence 8 (LGWR switch)
  Current log# 2 seq# 8 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 8 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Sat Mar 24 02:00:00 2012
Clearing Resource Manager plan via parameter
Sat Mar 24 06:00:00 2012
Setting Resource Manager plan SCHEDULER[0x3008]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sat Mar 24 06:00:00 2012
Starting background process VKRM
Sat Mar 24 06:00:01 2012
VKRM started with pid=29, OS id=15277
Sat Mar 24 06:00:21 2012
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sat Mar 24 06:00:49 2012
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sat Mar 24 06:02:05 2012
Thread 1 cannot allocate new log, sequence 9
Private strand flush not complete
  Current log# 2 seq# 8 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 8 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Thread 1 advanced to log sequence 9 (LGWR switch)
  Current log# 3 seq# 9 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 9 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Sat Mar 24 08:56:14 2012
Thread 1 advanced to log sequence 10 (LGWR switch)
  Current log# 1 seq# 10 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 10 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Sat Mar 24 14:06:51 2012
Thread 1 cannot allocate new log, sequence 11
Private strand flush not complete
  Current log# 1 seq# 10 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 10 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Thread 1 advanced to log sequence 11 (LGWR switch)
  Current log# 2 seq# 11 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 11 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Sat Mar 24 18:11:25 2012
Thread 1 advanced to log sequence 12 (LGWR switch)
  Current log# 3 seq# 12 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 12 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Sun Mar 25 02:00:00 2012
Clearing Resource Manager plan via parameter
Sun Mar 25 02:00:30 2012
Thread 1 advanced to log sequence 13 (LGWR switch)
  Current log# 1 seq# 13 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 13 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Sun Mar 25 06:00:00 2012
Setting Resource Manager plan SCHEDULER[0x3009]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sun Mar 25 06:00:00 2012
Starting background process VKRM
Sun Mar 25 06:00:00 2012
VKRM started with pid=29, OS id=21059
Sun Mar 25 06:00:15 2012
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sun Mar 25 06:00:33 2012
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Sun Mar 25 08:56:21 2012
Thread 1 advanced to log sequence 14 (LGWR switch)
  Current log# 2 seq# 14 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 14 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Sun Mar 25 14:08:09 2012
Thread 1 cannot allocate new log, sequence 15
Private strand flush not complete
  Current log# 2 seq# 14 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 14 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Thread 1 advanced to log sequence 15 (LGWR switch)
  Current log# 3 seq# 15 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 15 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Sun Mar 25 22:08:35 2012 
Thread 1 cannot allocate new log, sequence 16
Private strand flush not complete
  Current log# 3 seq# 15 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 15 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Thread 1 advanced to log sequence 16 (LGWR switch)
  Current log# 1 seq# 16 mem# 0: +DATA/ocm11g/onlinelog/group_1.261.778667669
  Current log# 1 seq# 16 mem# 1: +FLASH/ocm11g/onlinelog/group_1.257.778667673
Mon Mar 26 02:00:00 2012 
Clearing Resource Manager plan via parameter
Mon Mar 26 10:14:25 2012 
Thread 1 advanced to log sequence 17 (LGWR switch)
  Current log# 2 seq# 17 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 17 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Mon Mar 26 22:00:00 2012 
Setting Resource Manager plan SCHEDULER[0x3003]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Mon Mar 26 22:00:00 2012 
Starting background process VKRM 
Mon Mar 26 22:00:00 2012 
VKRM started with pid=24, OS id=9542 
Mon Mar 26 22:00:30 2012 
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Mon Mar 26 22:00:59 2012 
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Mon Mar 26 22:02:17 2012 
Thread 1 cannot allocate new log, sequence 18
Private strand flush not complete
  Current log# 2 seq# 17 mem# 0: +DATA/ocm11g/onlinelog/group_2.262.778667673
  Current log# 2 seq# 17 mem# 1: +FLASH/ocm11g/onlinelog/group_2.258.778667675
Thread 1 advanced to log sequence 18 (LGWR switch)
  Current log# 3 seq# 18 mem# 0: +DATA/ocm11g/onlinelog/group_3.263.778667677
  Current log# 3 seq# 18 mem# 1: +FLASH/ocm11g/onlinelog/group_3.259.778667677
Tue Mar 27 02:00:00 2012 
Clearing Resource Manager plan via parameter

从上，上周五晚上22:00(Fri Mar 23 22:00:00 2012)到上周六凌晨2:00(Sat Mar 24 02:00:00 2012)数据库执行自动化维护任务，上周六、日凌晨6:00到次日凌晨2:00数据库执行自动化维护任务，本周一自动化维护任务的窗口又开始回到晚上22:00到次日凌晨2：00点之间。可见，Oracle 11g相比以前版本的数据库更加自动化、智能化。

Oracle 11g Administrator’s Guide笔记1：DDL_LOCK_TIMEOUT初始化参数介绍

3月 26,2012 留下评论 Experiments, Oracle

今天在看Oracle 11gR2 Administrator‘s Guide(文档编号E25494-01)时，在第102页读到一个新的初始化参数：DDL_LOCK_TIMEOUT，该参数是从11g Release 1开始已经引入的，用来控制执行DDL语句时需要获取独占锁(exclusive locks)的延时时间，如果在该参数指定的时间范围内获取不到独占锁的话，那么DDL语句就会失败。

DDL_LOCK_TIMEOUT参数的取值范围是0~1000000秒，是一个整形参数，既可以在会话级别(ALTER SESSION)也可以在系统级别(ALTER SYSTEM)动态调整。其默认值是0，也就是说，执行DDL语句是要求立即获取独占锁资源，如果无法获取，那么所执行的DDL语句将立即报错。

我们也知道，执行DML语句时需要获取2个锁资源：

① 针对正在更新的一行或者多行的一个行级独占锁EXCLUSIVE，防止在事务结束之前有其它会话修改了被锁定行的数据，也叫排他锁；
② 针对要更新的表的 ROW EXCLUSIVE 表级锁定。这可避免在进行更改时由另一会话锁定整个表(可能会删除、截断该表，或修改了该表的表结构)，也叫共享锁；

正是由于锁的特征，结合DDL_LOCK_TIMEOUT初始化参数，实验如下：

会话1，创建一张表，并在该表上执行DML事务，且不提交，使该DML事务获取上述的两个锁资源：

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> show parameter ddl_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE
SQL> conn hr/hR;
Connected.
SQL> create table t(id number);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> select * from t;

        ID
----------
         1

SQL>

会话2，会话级别修改DDL_LOCK_TIMEOUT初始化参数超出0~1000000范围值，直接报错：

SQL> conn hr/hR;
Connected.
SQL> alter session set ddl_lock_timeout=1000001;
ERROR:
ORA-00068: invalid value 1000001 for parameter ddl_lock_timeout, must be
between 0 and 1000000


SQL>

会话2，执行DDL语句，使之需要获取独占锁，立即抛出错误，因为DDL_LOCK_TIMEOUT初始化参数默认值是0：

SQL> alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn';
alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn'
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL>

会话2，调整DDL_LOCK_TIMEOUT初始化参数为30秒，在30秒内获取不到独占锁，最终抛出错误：

SQL> alter session set ddl_lock_timeout=30;

Session altered.

SQL> set time on; 
17:00:26 SQL> alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn';
alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn'
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


17:00:58 SQL>

会话2重新执行DDL，当会话1提交事务后，会话2在30秒内获取到独占锁，DDL执行成功：

17:03:13 SQL> alter table t add  name varchar2(20) default 'www.OracleOnLinux.cn';

Table altered.

17:03:21 SQL> select * from t;

        ID NAME
---------- --------------------
         1 www.OracleOnLinux.cn

17:03:32 SQL>

Oracle 11gR2 Administrator's Guide，仍然需要多读多看，多做实验。

Oracle 10g RAC 配置物理dataguard系列4：switchover及功能测试

2月 22,2012 有2条评论 Dataguard, Oracle

在上一篇文章Oracle 10g RAC 配置物理dataguard系列3：物理备库配置步骤中，我们成功地给双节点的RAC主库成功配置Physical Standby database。在本篇中，我们开始对这套由双节点RAC主库+单实例Physical Standby database的dataguard环境做下述3点的功能测试。

主库创建数据文件，确认物理备库是否自动生成对应的数据文件？
主库做事务操作，在物理备库上确认是否可以看到对应的记录？
主备库完成一次switchover的Role transaction。

测试一：主库创建数据文件，备库确认能否看到对应的数据文件？

1 首先，在主库上创建一个测试表空间TEST：

[oracle@oracle-rac1 arch1]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 22 13:58:23 2012

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

SQL> conn / as sysdba;
Connected.
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      glndb
SQL> show parameter db_create_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +ORADATA
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/glndb/datafile/system.260.726057851
         3 +ORADATA/glndb/datafile/sysaux.262.726057863
         4 +ORADATA/glndb/datafile/undotbs2.264.726057869
         5 +ORADATA/glndb/datafile/users.265.726057871
         6 +ORADATA/glndb/datafile/undotbs3.268.774870843
       169 +ORADATA/glndb/datafile/example.435.756233417

6 rows selected.

SQL> create tablespace TEST datafile size 5m autoextend on;

Tablespace created.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/glndb/datafile/system.260.726057851
         2 +ORADATA/glndb/datafile/test.259.775925113
         3 +ORADATA/glndb/datafile/sysaux.262.726057863
         4 +ORADATA/glndb/datafile/undotbs2.264.726057869
         5 +ORADATA/glndb/datafile/users.265.726057871
         6 +ORADATA/glndb/datafile/undotbs3.268.774870843
       169 +ORADATA/glndb/datafile/example.435.756233417

7 rows selected.

SQL>

2 然后，在物理备库上查看是否生成该文件？

[oracle@ora10grac-dg arch2]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 22 14:50:43 2012

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

SQL> conn / as sysdba
Connected.
SQL> select open_mode from v$database;

OPEN_MODE
----------------------------------------
MOUNTED

SQL> col name for a50
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/pridb/datafile/system.258.775476897
         3 +ORADATA/pridb/datafile/sysaux.257.775476887
         4 +ORADATA/pridb/datafile/undotbs2.259.775476909
         5 +ORADATA/pridb/datafile/users.260.775476921
         6 +ORADATA/pridb/datafile/undotbs3.262.775476939
       169 +ORADATA/pridb/datafile/example.261.775476929

6 rows selected.

SQL>

发现，在备库上并没有看到我们预期的TEST表空间被自动创建出来，说明数据数据并没有同步到备库上来。其实，原因是备库并没有启动redo apply，也没有相应的MRP0后台进程。

3 其次，我们在备库上启动Redo Apply：

SQL> show parameter instance_name

NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
instance_name                        string   pridb
SQL> show parameter db_unique_name

NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
db_unique_name                       string   pridb
SQL> show parameter db_create_file

NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
db_create_file_dest                  string   +ORADATA
SQL> show parameter db_file_name_convert

NAME                                 TYPE     VALUE
------------------------------------ -------- ------------------------------
db_file_name_convert                 string   +ORADATA/glndb/, +ORADATA/pridb/
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/pridb/datafile/system.258.775476897
         3 +ORADATA/pridb/datafile/sysaux.257.775476887
         4 +ORADATA/pridb/datafile/undotbs2.259.775476909
         5 +ORADATA/pridb/datafile/users.260.775476921
         6 +ORADATA/pridb/datafile/undotbs3.262.775476939
       169 +ORADATA/pridb/datafile/example.261.775476929

6 rows selected.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/pridb/datafile/system.258.775476897
         2 +ORADATA/pridb/datafile/test.274.775925977
         3 +ORADATA/pridb/datafile/sysaux.257.775476887
         4 +ORADATA/pridb/datafile/undotbs2.259.775476909
         5 +ORADATA/pridb/datafile/users.260.775476921
         6 +ORADATA/pridb/datafile/undotbs3.262.775476939
       169 +ORADATA/pridb/datafile/example.261.775476929

7 rows selected.

SQL>

此时，我们可以看到一旦在备库启用redo apply之后，我们几乎可以立即看到备库上自动创建出预期的TEST表空间，说明测试成功！因为，我们主库上log_archive_dest_1参数是是：service=pridb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=pridb 同时，我们在物理备库的alert日志文件里也看到了下述信息：

Wed Feb 22 14:59:29 CST 2012
alter database recover managed standby database using current logfile disconnect from session
Wed Feb 22 14:59:29 CST 2012
Attempt to start background Managed Standby Recovery process (pridb)
MRP0 started with pid=14, OS id=18837
Wed Feb 22 14:59:29 CST 2012
MRP0: Background Managed Standby Recovery process started (pridb)
Managed Standby Recovery starting Real Time Apply
 parallel recovery started with 3 processes
Wed Feb 22 14:59:35 CST 2012
Waiting for all non-current ORLs to be archived...
Media Recovery Waiting for thread 2 sequence 3297 (in transit)
Wed Feb 22 14:59:35 CST 2012
Recovery of Online Redo Log: Thread 2 Group 8 Seq 3297 Reading mem 0
  Mem# 0: +ORADATA/pridb/onlinelog/group_8.266.775479229
Media Recovery Waiting for thread 1 sequence 7745 (in transit)
Wed Feb 22 14:59:35 CST 2012
Recovery of Online Redo Log: Thread 1 Group 3 Seq 7745 Reading mem 0
  Mem# 0: +ORADATA/pridb/onlinelog/group_3.263.775479203
Wed Feb 22 14:59:35 CST 2012
Completed: alter database recover managed standby database using current logfile disconnect from session
Wed Feb 22 14:59:38 CST 2012
Successfully added datafile 2 to media recovery
Datafile #2: '+ORADATA/pridb/datafile/test.274.775925977'

4 最后，我们在主库上删除TEST表空间后，同样在备库上看到TEST表空间自动被删除！至此，说明我们的Dataguard功能测试一完成。

主库删TEST：

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/glndb/datafile/system.260.726057851
         2 +ORADATA/glndb/datafile/test.259.775925113
         3 +ORADATA/glndb/datafile/sysaux.262.726057863
         4 +ORADATA/glndb/datafile/undotbs2.264.726057869
         5 +ORADATA/glndb/datafile/users.265.726057871
         6 +ORADATA/glndb/datafile/undotbs3.268.774870843
       169 +ORADATA/glndb/datafile/example.435.756233417

7 rows selected.

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/glndb/datafile/system.260.726057851
         3 +ORADATA/glndb/datafile/sysaux.262.726057863
         4 +ORADATA/glndb/datafile/undotbs2.264.726057869
         5 +ORADATA/glndb/datafile/users.265.726057871
         6 +ORADATA/glndb/datafile/undotbs3.268.774870843
       169 +ORADATA/glndb/datafile/example.435.756233417

6 rows selected.

SQL>

备库确认：

Wed Feb 22 15:11:07 CST 2012
Recovery deleting file #2:'+ORADATA/pridb/datafile/test.274.775925977' from controlfile.
Deleted Oracle managed file +ORADATA/pridb/datafile/test.274.775925977
Recovery dropped tablespace 'TEST'
.....
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 +ORADATA/pridb/datafile/system.258.775476897
         3 +ORADATA/pridb/datafile/sysaux.257.775476887
         4 +ORADATA/pridb/datafile/undotbs2.259.775476909
         5 +ORADATA/pridb/datafile/users.260.775476921
         6 +ORADATA/pridb/datafile/undotbs3.262.775476939
       169 +ORADATA/pridb/datafile/example.261.775476929

6 rows selected.

SQL>

测试二：主库创建测试数据，到备库上去查看，query是否同步？

1 主库创建测试数据：

SQL> conn human/hr
Connected.
SQL> create table test(id number,name varchar2(20));

Table created.

SQL> insert into test values(1,'WWW.OracleOnLinux.CN');

1 row created.

SQL> insert into test select * from test;

1 row created.

SQL> insert into test select * from test;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN

SQL>

2 备库确认：

SQL> select name,open_mode from v$database;

NAME                OPEN_MODE
------------------- ----------
GLNDB               MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME                 OPEN_MODE
-------------------- ----------
GLNDB                READ ONLY

SQL> conn human/hr
Connected.
SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN
         1 WWW.OracleOnLinux.CN

SQL>

3 最后，发现备库停止Redo Apply后，并将数据库以read only方式打开后，我们可以看到数据已经同步到物理备库！说明功能测试二已经达到预期目的！

测试三：角色转换之switchover

1 物理备库置于MOUNT状态。在switchover过程中，如果目标库是物理备库的话，最好是将物理备库置于MOUNT状态，当然也可以置于OPEN READ ONLY方式，不过官方文档说处于OPEN READ ONLY方式时，可能会延长switchover的时间。

另：在physical standby database配置下如需switchover时，如果主库、物理备库都是RAC数据库时，需要先将主库的其它实例停止，只留一个实例运行；然后，将物理备库的其它实例停止，只留一个实例运行。即：如果主、备库都是RAC数据库的话，要确保主、备库均留一个实例运行。

在这里，我们的物理备库是单实例数据库，所以只需将该库置于MOUNT状态：

SQL> select name,open_mode from v$database;

NAME                 OPEN_MODE
-------------------- ----------
GLNDB                READ ONLY
SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
----------------------------------------
MOUNTED

SQL>

2 RAC主库停止glndb2实例，只留glndb1运行：

SQL> show user;
USER is "SYS"
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb2
[oracle@oracle-rac2 arch2]$ /u01/app/oracle/product/10.2.0/crs/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.glndb.db   application    ONLINE    ONLINE    oracle-rac2 
ora...._svc.cs application    ONLINE    ONLINE    oracle-rac1 
ora....db1.srv application    ONLINE    ONLINE    oracle-rac1 
ora....b1.inst application    ONLINE    ONLINE    oracle-rac1 
ora....b2.inst application    ONLINE    ONLINE    oracle-rac2 
ora....svc2.cs application    ONLINE    ONLINE    oracle-rac2 
ora....db2.srv application    ONLINE    ONLINE    oracle-rac2 
ora....SM1.asm application    ONLINE    ONLINE    oracle-rac1 
ora....C1.lsnr application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.gsd application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.ons application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.vip application    ONLINE    ONLINE    oracle-rac1 
ora....SM2.asm application    ONLINE    ONLINE    oracle-rac2 
ora....C2.lsnr application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.gsd application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.ons application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.vip application    ONLINE    ONLINE    oracle-rac2 
[oracle@oracle-rac2 arch2]$ exit
exit

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[oracle@oracle-rac2 arch2]$ /u01/app/oracle/product/10.2.0/crs/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.glndb.db   application    ONLINE    ONLINE    oracle-rac2 
ora...._svc.cs application    ONLINE    ONLINE    oracle-rac1 
ora....db1.srv application    ONLINE    ONLINE    oracle-rac1 
ora....b1.inst application    ONLINE    ONLINE    oracle-rac1 
ora....b2.inst application    OFFLINE   OFFLINE               
ora....svc2.cs application    ONLINE    ONLINE    oracle-rac2 
ora....db2.srv application    ONLINE    ONLINE    oracle-rac1 
ora....SM1.asm application    ONLINE    ONLINE    oracle-rac1 
ora....C1.lsnr application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.gsd application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.ons application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.vip application    ONLINE    ONLINE    oracle-rac1 
ora....SM2.asm application    ONLINE    ONLINE    oracle-rac2 
ora....C2.lsnr application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.gsd application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.ons application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.vip application    ONLINE    ONLINE    oracle-rac2 
[oracle@oracle-rac2 arch2]$

3 确认主库switchover状态。如果是to standby状态，说明一切正常。当然，也有可能是SESSIONS ACTIVE状态，不过也不要紧，这只是说明在主库上还有其他会话连接中，我们可以断开这些会话，或者直接在切换语句[ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY]后面加上WITH SESSION SHUTDOWN 从句来切换。

SQL> show user;
USER is "SYS"
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE  SWITCHOVER_STATUS
----------- -------------------- ---------------- --- ---------- --------------------
  447326322 MAXIMUM PERFORMANCE  PRIMARY          YES READ WRITE SESSIONS ACTIVE

SQL>

4 切换原RAC主库到备库角色。

SQL> show user;
USER is "SYS"
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> select * from v$log;
select * from v$log
              *
ERROR at line 1:
ORA-01507: database not mounted


SQL> select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;
select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database
                                                                                                *
ERROR at line 1:
ORA-01507: database not mounted


SQL> !ps  -ef |grep ora_
oracle   18271     1  0 14:04 ?        00:00:00 ora_o000_glndb1
oracle   22321     1  0 11:31 ?        00:00:01 ora_pmon_glndb1
oracle   22323     1  0 11:31 ?        00:00:00 ora_diag_glndb1
oracle   22325     1  0 11:31 ?        00:00:00 ora_psp0_glndb1
oracle   22332     1  0 11:31 ?        00:00:14 ora_lmon_glndb1
oracle   22334     1  0 11:31 ?        00:00:10 ora_lmd0_glndb1
oracle   22341     1  0 11:31 ?        00:00:07 ora_lms0_glndb1
oracle   22345     1  0 11:31 ?        00:00:07 ora_lms1_glndb1
oracle   22350     1  0 11:31 ?        00:00:02 ora_mman_glndb1
oracle   22356     1  0 11:31 ?        00:00:01 ora_dbw0_glndb1
oracle   22360     1  0 11:31 ?        00:00:02 ora_lgwr_glndb1
oracle   22370     1  0 11:31 ?        00:00:04 ora_ckpt_glndb1
oracle   22378     1  0 11:31 ?        00:00:10 ora_smon_glndb1
oracle   22391     1  0 11:31 ?        00:00:00 ora_reco_glndb1
oracle   22452     1  0 11:31 ?        00:00:03 ora_lck0_glndb1
oracle   22468     1  0 11:31 ?        00:00:00 ora_asmb_glndb1
oracle   22482     1  0 11:31 ?        00:00:00 ora_rbal_glndb1
oracle   30712     1  0 17:20 ?        00:00:00 ora_s001_glndb1
oracle   30730     1  0 17:20 ?        00:00:00 ora_o001_glndb1
oracle   30797     1  0 17:20 ?        00:00:00 ora_o002_glndb1
oracle   31131     1  0 17:21 ?        00:00:00 ora_d000_glndb1
oracle   31135     1  0 17:21 ?        00:00:00 ora_mmon_glndb1
oracle   31144     1  0 17:21 ?        00:00:00 ora_mmnl_glndb1
oracle   31683  3106  0 17:22 pts/2    00:00:00 /bin/bash -c ps  -ef |grep ora_
oracle   31685 31683  0 17:22 pts/2    00:00:00 grep ora_

SQL>

5 关闭原主库实例glndb1，重新启动glndb1到MOUNT状态：

SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb1
SQL> show user;
USER is "SYS"
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
SQL> select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from v$database;

CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE  SWITCHOVER_STATUS
----------- -------------------- ---------------- --- ---------- --------------------
  447326596 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED    SESSIONS ACTIVE

SQL>

6 确认原备库switchover状态。如果是TO PRIMARY状态，说明一切正常。当然，也有可能是SESSIONS ACTIVE状态，不过也不要紧，这只是说明在备库上还有其他会话连接中，我们可以断开这些会话，或者直接在切换语句[ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY ]后面加上WITH SESSION SHUTDOWN 从句来切换。

SQL> show user;
USER is "SYS"
SQL> show  parameter instance_name;

NAME              TYPE    VALUE
----------------- ------- -------
instance_name     string  pridb
SQL>select open_mode,switchover_status,database_role from v$database;

OPEN_MODE  SWITCHOVER_STATUS    DATABASE_ROLE
---------- -------------------- --------------------
MOUNTED    TO PRIMARY           PHYSICAL STANDBY

SQL>

7 切换原备库至主库角色。

SQL> show user;
USER is "SYS"
SQL> show  parameter instance_name;

NAME              TYPE    VALUE
----------------- ------- -------
instance_name     string  pridb
SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE  SWITCHOVER_STATUS    DATABASE_ROLE
---------- -------------------- --------------------
MOUNTED    TO PRIMARY           PHYSICAL STANDBY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> select open_mode,switchover_status,database_role from v$database;
select open_mode,switchover_status,database_role from v$database
*
ERROR at line 1:
ORA-01507: database not mounted


SQL>

此时，原备库alert日志信息如下：

......
......
Wed Feb 22 17:35:52 CST 2012
alter database commit to switchover to primary
Wed Feb 22 17:35:52 CST 2012
ALTER DATABASE SWITCHOVER TO PRIMARY (pridb)
Wed Feb 22 17:35:52 CST 2012
If media recovery active, switchover will wait 900 seconds
SwitchOver after complete recovery through change 447326597
Online log +ORADATA/pridb/onlinelog/group_1.269.775479753: Thread 1 Group 1 was previously cleared
Online log +ORADATA/pridb/onlinelog/group_2.270.775479755: Thread 1 Group 2 was previously cleared
Online log +ORADATA/pridb/onlinelog/group_4.271.775479755: Thread 2 Group 4 was previously cleared
Online log +ORADATA/pridb/onlinelog/group_5.272.775479757: Thread 2 Group 5 was previously cleared
Standby became primary SCN: 447326595
Wed Feb 22 17:35:53 CST 2012
Switchover: Complete - Database shutdown required (pridb)
Completed: alter database commit to switchover to primary
Wed Feb 22 17:35:58 CST 2012
SUCCESS: diskgroup ORADATA was dismounted
Wed Feb 22 17:36:05 CST 2012
ARC1: Archival disabled due to instance shutdown
Shutting down archive processes
Archiving is disabled
Wed Feb 22 17:36:15 CST 2012
ARCH shutting down
ARC0: Archival stopped
Wed Feb 22 17:36:20 CST 2012
ARCH shutting down
ARC1: Archival stopped
.....
.....

8 完成原主库到新主库的角色转换。

这里需要注意：

①如果该原备库自从上次启动以来，从来没有以READ ONLY方式打开过，那么可以直接通过ALTER DATABASE OPEN命令来打开；
②如果该原备库自从上次启动以来，曾经以READ ONLY方式打开过，那么需要先SHUTDOWN，然后STARTUP。这里，我们的原物理备库复合第②中情况，需要先SHUTDOWN，然后STARTUP。

SQL> show user;
USER is "SYS"
SQL> show  parameter instance_name;

NAME              TYPE    VALUE
----------------- ------- -------
instance_name     string  pridb
SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
Database opened.
SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE  SWITCHOVER_STATUS    DATABASE_ROLE
---------- -------------------- --------------------
READ WRITE SESSIONS ACTIVE      PRIMARY

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
         1          1       7749   52428800          1 YES          INACTIVE       447326597 2012/02/22 17:35:53
         2          1       7750   52428800          1 NO           CURRENT        447326600 2012/02/22 17:40:47
         4          2          0   52428800          1 YES          UNUSED                 0
         5          2          0   52428800          1 YES          UNUSED                 0

SQL>

9 在新备库上[原RAC主库]启动glndb2实例至MOUNT状态，并启用redo apply，确认数据是否能与新主库[原单实例备库]同步？

SQL> show user;
USER is "SYS"
SQL> show parameter instance;
ORA-01034: ORACLE not available


SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             520094216 bytes
Database Buffers         1073741824 bytes
Redo Buffers               14680064 bytes
SQL> show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      glndb2
SQL> !
[oracle@oracle-rac2 arch2]$ /u01/app/oracle/product/10.2.0/crs/bin/crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora.glndb.db   application    ONLINE    ONLINE    oracle-rac2 
ora...._svc.cs application    OFFLINE   OFFLINE               
ora....db1.srv application    OFFLINE   OFFLINE               
ora....b1.inst application    ONLINE    ONLINE    oracle-rac1 
ora....b2.inst application    ONLINE    ONLINE    oracle-rac2 
ora....svc2.cs application    OFFLINE   OFFLINE               
ora....db2.srv application    OFFLINE   OFFLINE               
ora....SM1.asm application    ONLINE    ONLINE    oracle-rac1 
ora....C1.lsnr application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.gsd application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.ons application    ONLINE    ONLINE    oracle-rac1 
ora....ac1.vip application    ONLINE    ONLINE    oracle-rac1 
ora....SM2.asm application    ONLINE    ONLINE    oracle-rac2 
ora....C2.lsnr application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.gsd application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.ons application    ONLINE    ONLINE    oracle-rac2 
ora....ac2.vip application    ONLINE    ONLINE    oracle-rac2 
[oracle@oracle-rac2 arch2]$ ps -ef | grep ora_
oracle   26363     1  0 17:42 ?        00:00:00 ora_pmon_glndb2
oracle   26370     1  0 17:42 ?        00:00:00 ora_diag_glndb2
oracle   26377     1  0 17:42 ?        00:00:00 ora_psp0_glndb2
oracle   26384     1  0 17:42 ?        00:00:00 ora_lmon_glndb2
oracle   26387     1  2 17:42 ?        00:00:01 ora_lmd0_glndb2
oracle   26393     1  2 17:42 ?        00:00:01 ora_lms0_glndb2
oracle   26402     1  2 17:42 ?        00:00:01 ora_lms1_glndb2
oracle   26406     1  6 17:42 ?        00:00:03 ora_mman_glndb2
oracle   26415     1  0 17:42 ?        00:00:00 ora_dbw0_glndb2
oracle   26417     1  0 17:42 ?        00:00:00 ora_lgwr_glndb2
oracle   26424     1  0 17:42 ?        00:00:00 ora_ckpt_glndb2
oracle   26426     1  0 17:42 ?        00:00:00 ora_smon_glndb2
oracle   26433     1  0 17:42 ?        00:00:00 ora_reco_glndb2
oracle   26435     1  0 17:43 ?        00:00:00 ora_cjq0_glndb2
oracle   26442     1  0 17:43 ?        00:00:00 ora_mmon_glndb2
oracle   26445     1  0 17:43 ?        00:00:00 ora_mmnl_glndb2
oracle   26451     1  0 17:43 ?        00:00:00 ora_d000_glndb2
oracle   26457     1  0 17:43 ?        00:00:00 ora_s000_glndb2
oracle   26506     1  0 17:43 ?        00:00:00 ora_lck0_glndb2
oracle   26706     1  0 17:43 ?        00:00:00 ora_pz99_glndb2
oracle   26860 26712  0 17:43 pts/1    00:00:00 grep ora_
[oracle@oracle-rac2 arch2]$ exit
exit

SQL> alter database mount;

Database altered.

SQL> select current_scn,protection_mode,database_role,force_logging,open_mode,switchover_status from gv$database;

CURRENT_SCN PROTECTION_MODE      DATABASE_ROLE    FOR OPEN_MODE  SWITCHOVER_STATUS
----------- -------------------- ---------------- --- ---------- --------------------
  447326596 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED    SESSIONS ACTIVE
  447326596 MAXIMUM PERFORMANCE  PHYSICAL STANDBY YES MOUNTED    SESSIONS ACTIVE

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1       7750   52428800          1 YES CLEARING             447326600 2012/02/22 17:40:47
         2          1       7750   52428800          1 YES CLEARING_CURRENT     447326600 2012/02/22 17:40:47
         4          2       3297   52428800          1 YES INACTIVE             447302887 2012/02/22 14:07:29
         5          2       3298   52428800          1 YES ACTIVE               447326021 2012/02/22 17:11:49

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          0   52428800          1 YES UNUSED               447326600 2012/02/22 17:40:47
         2          1       7750   52428800          1 YES CLEARING_CURRENT     447326600 2012/02/22 17:40:47
         4          2          0   52428800          1 YES UNUSED               447302887 2012/02/22 14:07:29
         5          2          0   52428800          1 YES UNUSED               447326021 2012/02/22 17:11:49

SQL>

新主库切换日志：

SQL> show  parameter instance_name;

NAME              TYPE    VALUE
----------------- ------- -------
instance_name     string  pridb
SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
     1          1       7749   52428800          1 YES          INACTIVE       447326597 2012/02/22 17:35:53
     2          1       7750   52428800          1 NO           CURRENT        447326600 2012/02/22 17:40:47
     4          2          0   52428800          1 YES          UNUSED                 0
     5          2          0   52428800          1 YES          UNUSED                 0

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
     1          1       7751   52428800          1 NO           CURRENT        447336270 2012/02/22 20:42:28
     2          1       7750   52428800          1 YES          ACTIVE         447326600 2012/02/22 17:40:47
     4          2          0   52428800          1 YES          UNUSED                 0
     5          2          0   52428800          1 YES          UNUSED                 0

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED     STATUS     FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- ------------ ---------- ------------- -------------------
     1          1       7753   52428800          1 YES          INACTIVE       447336484 2012/02/22 20:47:35
     2          1       7754   52428800          1 NO           CURRENT        447336486 2012/02/22 20:47:39
     4          2          0   52428800          1 YES          UNUSED                 0
     5          2          0   52428800          1 YES          UNUSED                 0

SQL>

最后，从新备库[原RAC主库]的alert日志中可以看到：

[root@oracle-rac1 ~]# tail -f /u01/app/oracle/admin/glndb/bdump/alert_glndb1.log 
 All grantable enqueues granted
Wed Feb 22 17:43:15 CST 2012
 LMS 1: 0 GCS shadows traversed, 0 replayed
Wed Feb 22 17:43:15 CST 2012
 LMS 0: 0 GCS shadows traversed, 0 replayed
Wed Feb 22 17:43:15 CST 2012
 Submitted all GCS remote-cache requests
 Post SMON to start 1st pass IR
 Fix write in gcs resources
Reconfiguration complete
Wed Feb 22 20:38:32 CST 2012
Managed Standby Recovery starting Real Time Apply
Wed Feb 22 20:40:45 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 6: '+ORADATA/glndb/onlinelog/group_6.430.775490705'
Wed Feb 22 20:45:45 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 3: '+ORADATA/glndb/onlinelog/group_3.261.775490705'
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 6: '+ORADATA/glndb/onlinelog/group_6.430.775490705'
Wed Feb 22 20:45:55 CST 2012
Primary database is in MAXIMUM PERFORMANCE mode
RFS[1]: Successfully opened standby log 3: '+ORADATA/glndb/onlinelog/group_3.261.775490705'

同时，在新备库中可以看到下述信息

SQL> select thread#,sequence#,name,first_time,next_time,applied from v$archived_log where thread#=1 order by 1;
   THREAD#  SEQUENCE# NAME                                               FIRST_TIM NEXT_TIME APP
---------- ---------- -------------------------------------------------- --------- --------- ---
         1       7751 /home/oracle/arch1/ARC_1_0000007751_726057844.arc  22-FEB-12 22-FEB-12 YES
         1       7752 /home/oracle/arch1/ARC_1_0000007752_726057844.arc  22-FEB-12 22-FEB-12 YES
         1       7753 /home/oracle/arch1/ARC_1_0000007753_726057844.arc  22-FEB-12 22-FEB-12 YES

从上，可以确定我们的新备库同新主库已经同步！至此，我们的switchover角色切换成功！

解析Oracle rowid系列三(完)

11月 11,2011 有1条评论 Experiments, Oracle

承接解析Oracle rowid系列一、系列二。今天，我们来探讨特定场景下的Oracle rowid,大文件表空间下的Oracle rowid。大文件的表空间是Oracle 10g的新特性,关于该特性暂且不作过多表述，简单一句话来说，同小文件类型的表空间(数据库默认的表空间类型)相比，该类型的表空间只能包含一个而且最多只能有一个数据文件。正是因为如此，所以位于大文件类型表空间下的表的rowid显得有些特殊，接下来我们探讨究竟特殊在哪儿？

首先，准备场景，建立大文件表空间，并在该表空间下建立一张普通的Heap表。

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create bigfile tablespace big_tbs datafile size 10m autoextend on;
Tablespace created.
SQL> select d.file_id,d.tablespace_name,t.bigfile
2  from dba_data_files d,dba_tablespaces t
3  where d.tablespace_name=t.tablespace_name
4  order by 1
5  ;
FILE_ID   TABLESPACE_NAME        BIG
---------- ----------------- ---
1 SYSTEM                         NO
2 SYSAUX                         NO
3 UNDOTBS1                       NO
4 USERS                          NO
5 EXAMPLE                        NO
6 UNDOTBS2                       NO
7 TEST_TBS                       NO
8 TEST_TBS                       NO
9 BIG_TBS                        YES
9 rows selected.
SQL> alter user hr quota unlimited on big_tbs;
User altered.
SQL> conn hr/hr
Connected.
SQL> create table bigfile_tab(id number,name varchar2(10))
2  tablespace big_tbs
3  ;
Table created.
SQL> insert into bigfile_tab values(1,'oracle');
1 row created.
SQL> insert into bigfile_tab values(2,'oracle');
1 row created.
SQL> commit;
Commit complete.
SQL>

然后，我们来查询bigfile_tab表中的rowid：

SQL> select id,rowid from bigfile_tab;
ID ROWID
---------- ------------------
1 AAAUHfAAAAAAACGAAA
2 AAAUHfAAAAAAACGAAB
SQL> select rowid,
2  dbms_rowid.rowid_object(rowid) object_id,
3  dbms_rowid.rowid_relative_fno(rowid)  file_id,
4  dbms_rowid.rowid_block_number(rowid)  block_id ,
5  dbms_rowid.rowid_row_number(rowid)   num
6  from bigfile_tab
7  ;
ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM
------------------ ---------- ---------- ---------- ----------
AAAUHfAAAAAAACGAAA      82399          0        134          0
AAAUHfAAAAAAACGAAB      82399          0        134          1
SQL>

这时，我们发到貌似诡异的相对文件号竟然为0，本来我们的这张测试表位于bigfile_tbs表空间下，而bigfile_tbs表空间的file_id为9，我们从上述的第一次查询结果可以验证。可是为什么从rowid中查询的相对文件号为什么为0呢？

原来，对于大文件表空间下的rowid有如下特定的格式，这有别于小文件表空间下的rowid格式：

OOOOOOBBBBBBBBBRRR

即，6位的数据库对象号+9位的数据块号+3位的行号，同样也是以18位的64进制值来表示80位的二进制数。只不过，在这里少了相对文件号，其中6位的数据库对象号用32位的二进制数来存放(即一个数据库最多可以拥有232=4G个数据块对象)，9位的数据块号同样用32位的二进制数来存放(即一个大文件表空间可以拥有232个数据块儿)，最后3位的行号占用剩余的16位的二进制数，正好占满80位。

最后，我们就可以很容易理解为什么本实验中bigfile_tab表的相对文件号为0了？因为该表的rowid格式中根本就不存在相对文件号的信息，最本质的原因是大文件表空间下永远只能有且仅有1个数据文件，也就没有相对文件号的概念了。

同时，我们也可以推算出为什么官方文档中说，对于大文件的表空间，如果数据块大小为32K的话，那么这个表空间的上限是128Tb？因为大文件表空间下最多可以有232个数据块，那么该表空间大小=232*32K=237K=227M=217G=27T=128T,答案也就在于此。

至此，关于Oracle rowid的探讨一、二、三系列结束。如果，大家对该系列有不同的理解，或认为本人理解有误的地方，还请不吝指正！！！

SQL> conn / as sysdba;

Connected.

SQL> select * from v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production

PL/SQL Release 11.2.0.1.0 – Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 – Production

NLSRTL Version 11.2.0.1.0 – Production

SQL> create bigfile tablespace big_tbs datafile size 10m autoextend on;

Tablespace created.

SQL> select d.file_id,d.tablespace_name,t.bigfile

2 from dba_data_files d,dba_tablespaces t

3 where d.tablespace_name=t.tablespace_name

4 order by 1

5 ;

FILE_ID TABLESPACE_NAME BIG

———- —————————— —

1 SYSTEM NO

2 SYSAUX NO

3 UNDOTBS1 NO

4 USERS NO

5 EXAMPLE NO

6 UNDOTBS2 NO

7 TEST_TBS NO

8 TEST_TBS NO

9 BIG_TBS YES

9 rows selected.

SQL> alter user hr quota unlimited on big_tbs;

User altered.

SQL> conn hr/hr

Connected.

SQL> create table bigfile_tab(id number,name varchar2(10))

2 tablespace big_tbs

3 ;

Table created.

SQL> insert into bigfile_tab values(1,’oracle’);

1 row created.

SQL> insert into bigfile_tab values(2,’oracle’);

1 row created.

SQL> commit;

Commit complete.

SQL>

解析Oracle rowid系列二

11月 8,2011 有1条评论 Experiments, Oracle

在解析Oracle rowid系列一里，我们简单探讨了Oracle rowid的格式及获取rowid的方法，在本篇中我们探讨的主题是：通过Oracle rowid真的可以唯一定位到表的一条记录吗？或者说在同一Oracle数据库中，rowid就一定唯一、没有重复的吗？
实验步骤：
1 构建cluster table并插入测试数据：

SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> conn hr/hr;
Connected.
SQL> create cluster dept_emp_cluster(department_id number(4));
Cluster created.
SQL> create index idx_dept_emp_cluster on cluster dept_emp_cluster;
Index created.
SQL> create table dept cluster dept_emp_cluster(department_id)
2  as select * from departments;
Table created.
SQL> create table emp cluster dept_emp_cluster(department_id)
2  as select * from employees;
Table created.
SQL> select count(*) from dept;
COUNT(*)
----------
27
SQL> select count(*) from emp;
COUNT(*)
----------
107

2 接下来我们分别查询emp、dept表中department_id=10的rowid：

SQL> select department_id,rowid from dept where department_id=10;
DEPARTMENT_ID ROWID
------------- ------------------
           10 AAAUB3AAEAAAAK3AAA
SQL> select department_id,rowid from emp where department_id=10;
DEPARTMENT_ID ROWID
------------- ------------------
           10 AAAUB3AAEAAAAK3AAA
SQL> select department_id,rowid,
  2  dbms_rowid.rowid_object(rowid) object_id,
  3  dbms_rowid.rowid_relative_fno(rowid)  file_id,
  4  dbms_rowid.rowid_block_number(rowid)  block_id,
  5  dbms_rowid.rowid_row_number(rowid)   num
  6  from dept where department_id=10;
DEPARTMENT_ID ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM
------------- ------------------ ---------- ---------- ---------- ----------
           10 AAAUB3AAEAAAAK3AAA      82039          4        695          0
SQL> select department_id,rowid,
  2  dbms_rowid.rowid_object(rowid) object_id,
  3  dbms_rowid.rowid_relative_fno(rowid)  file_id,
  4  dbms_rowid.rowid_block_number(rowid)  block_id,
  5  dbms_rowid.rowid_row_number(rowid)   num
  6  from emp where department_id=10;
DEPARTMENT_ID ROWID               OBJECT_ID    FILE_ID   BLOCK_ID        NUM
------------- ------------------ ---------- ---------- ---------- ----------
           10 AAAUB3AAEAAAAK3AAA      82039          4        695          0
SQL>

从上，我们看到对于dept、emp这两张不同的表，对于department_id=10的两条记录的rowid完全一样，均为AAAUB3AAEAAAAK3AAA。
其中AAAUB3为数据库对象号，即转成10进制后位82309；数据文件号为AAE=4，位于第4号数据文件上；位于第4号文件的第AAAAK3=695个数据块上。
为什么会这样呢？不是说，rowid是唯一的吗，通过rowid可以唯一定位表里的一条记录吗？可是，现在却有两张完全不同的表中的rowid竟然完全重复？
3 原来，我们这个场景比较特殊，对象号为82309的对象是一个聚簇表，而dept,emp是位于该cluster下的。那么，emp、dept表中拥有完全重复的rowid也就不足为奇了。因为，这本身就是聚簇表的特征。Oracle的Cluster Table就是要将不同表中的数据放在同一个数据块中存放。关于Cluster Table我们将在后续探讨。

SQL> select object_name,object_id,object_type
  2  from user_objects where object_id='82039';
OBJECT_NAME           OBJECT_ID OBJECT_TYPE
-------------------- ---------- -------------------
DEPT_EMP_CLUSTER          82039 CLUSTER
SQL> select table_name,tablespace_name,cluster_name from user_tables
  2  where table_name in ('DEPT','EMP');
TABLE_NAME               TABLESPACE_NAME         CLUSTER_NAME
------------------------ ----------------------- --------------------
DEPT                     USERS                   DEPT_EMP_CLUSTER
EMP                      USERS                   DEPT_EMP_CLUSTER
SQL>

4 结论：对于普通的Heap Table，我们说rowid的确可以精确定位到表里的记录，并且任何两张表中得rowid绝对不会重复。而对于Cluster Table就不一样了，完全有可能会出现rowid重复的情况。

Oracle 11g Concepts 笔记2：什么是唯一、非唯一索引及索引分类

11月 2,2011 有1条评论 Experiments, Oracle

从Oracle索引的特征上，我们可以简单的把索引分为Unique Indexes and Nonunique Indexes，即唯一索引和非唯一索引。

1 对于唯一索引，唯一索引确保被索引的字段或多个联合字段在表中绝对不会有重复值；通常，我们在建表时，创建唯一约束或者主键约束，再或者建表以后给表添加唯一、主键约束时，Oracle会自动在主键、唯一约束的字段上创建唯一索引，并且索引的名字跟约束的名字一样，如：

SQL> create table tt(id1 number primary key,id2 number,id3 number);
Table created.
SQL> select index_name,table_name,uniqueness from user_indexes where table_name='TT';
INDEX_NAME                     TABLE_NAME                     UNIQUENESS
------------------------------ ------------------------------ ----------
SYS_C0015420                   TT                             UNIQUE
SQL> select table_name,constraint_name,index_name,constraint_type
2  from user_constraints
3  where table_name='TT';
TABLE_NAME     CONSTRAINT_NAME  INDEX_NAME    CONSTRAINT_TYPE
-------------- ---------------- ------------- ---------------
TT             SYS_C0015420     SYS_C0015420  P

我们在建测试表tt时在id1字段上创建了一个主键约束，当然该约束名字SYS_C0015420是Oracle自动分配给我们的，同时我们也看到tt表上有1个名为SYS_C0015420的索引。
接下来，在id2字段上添加一个唯一约束，并且手动指定名字：

SQL> alter table tt add constraint tt_id2_uk unique(id2);
Table altered.
SQL> select table_name,constraint_name,index_name,constraint_type
2  from user_constraints
3  where table_name='TT';
TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME                     C
------------------------------ ------------------------------ ------------------------------ -
TT                             SYS_C0015420                   SYS_C0015420                   P
TT                             TT_ID2_UK                      TT_ID2_UK                      U
SQL>  select index_name,table_name,uniqueness from user_indexes where table_name='TT';
INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
TT_ID2_UK                      TT                             UNIQUE
SYS_C0015420                   TT                             UNIQUE
SQL>

我们发现，这tt表上的两个索引都是唯一索引，且名字都等同于各自对应的约束名字。
然后，在该表上创建一个索引：

SQL> create index idx_tt_id3 on tt(id3);
Index created.
SQL> select index_name,table_name,uniqueness from user_indexes where table_name='TT';
INDEX_NAME                     TABLE_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
IDX_TT_ID3                     TT                             NONUNIQUE
TT_ID2_UK                      TT                             UNIQUE
SYS_C0015420                   TT                             UNIQUE
SQL>

此时，发现该索引IDX_TT_ID3是一个非唯一索引，我们也可以使用create unique index…语句显示创建一个唯一索引。
就唯一索引存储而言，每一个rowid会唯一对应表中的一条记录，在索引的叶子块儿中，数据按照索引字段升序存放。
2 对于非唯一索引，允许被索引的字段有重复值。并且，非唯一索引的存放有别于唯一索引，它的数据在索引叶子块儿中是按照索引字段和rowid进行升序存放的。
3 在Oracle数据库中，索引分类有以下几种：

B树索引 B-tree indexes；B树索引又可分为以下子类：
- 索引组织表Index-organized tables；
- 反转索引Reverse key indexes；
- 降序索引Descending indexes；
- B树聚簇索引B-tree cluster indexes；
位图和位图联合索引Bitmap and bitmap join indexes；
基于函数的索引Function-based indexes；
应用域索引Application domain indexes；

4 关于Oracle的索引还有更多知识需要了解和探索，下篇继续，To Be Continued…

Oracle 11g Concepts 笔记1：联合索引的创建及注意事项

10月 31,2011 有5条评论 Concepts, Oracle

众所周知，每一位Oracle数据库技术牛人，每一名Oracle DBA在谈到自己的成长历程或者分享学习经验时，都会强调阅读Oracle的官方文档的重要性和必要性。而Oracle提供的官方文档数量之多，范围之广让很多初学者都望而却步，尤其是对于英语基础不好的网友更是头痛不已。其实，就我个人而言，也是经历过诸多痛苦的。事实上，我想但凡每一位认认真真地阅读过官方文档的过来人都或多或少的会有同感。既然Oracle提供给我们的学习文档如此之多，那么我们又该选择哪些文档入手呢？

我的建议是，优先阅读也是最应该阅读 Oracle Database Concepts这本书，因为这本书涵盖了Oracle数据库的全部基本概念，多读此书，收获颇多。记得当初08年的时候，自学Oracle，备考Oracle 10g OCA时，自己坚持着把10g 版本的 Oracle Concepts 10g Release 2前2/3部分反复阅读了几次，得以对Oracle数据库算是有个基本了解。

本系列文章将以笔记的形式记录自己在阅读和学习Oracle 11gR2 Database Concepts 【文档编号为E16508-05】的心得和体会。一方面算是对自己的知识总结和记录，另一方面也希望这一系列的文章能够对广大Oracle数据库技术爱好者带来帮助。当然，如果有理解的不对或者是纰漏之处，还恳请广大网友及时回复给予指正。好了，废话不再赘述。

Oracle 11gR2 Database Concepts第3章Page 57讲到Indexes and Index-Organized Tables，其中Page 59讲到Composite Indexes。

首先，我们创建测试表，并在该表上创建联合索引：

SQL> conn / as sysdba
Connected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production 

SQL> conn hr/hr

Connected.

SQL> create table t(id1 number,id2 number,name varchar2(10));

Table created.

SQL> create index idx_t_id1_id2 on t(id1,id2);

Index created.

SQL> insert into t values(1,1,'oracle');

1 row created.

SQL> insert into t values(2,2,'on');

1 row created.

SQL> insert into t values(3,2,'linux');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

ID1        ID2 NAME

---------- ---------- ----------

1          1 oracle

2          2 on

3          2 linux

然后，查看执行计划1：

SQL> set autot trace exp

SQL> select * from t where id1=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 1660670018

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |    33 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |    33 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_ID1_ID2 |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID1"=1)

Note

-----

- dynamic sampling used for this statement (level=2)

SQL>

发现该语句走索引。

查看执行计划2：

SQL> select * from t where id2=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    33 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |    33 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID2"=1)

Note

-----

- dynamic sampling used for this statement (level=2)

SQL>

SQL> select * from t where id2=2;

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     2 |    66 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     2 |    66 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID2"=2)

Note

-----

- dynamic sampling used for this statement (level=2)

SQL>

发现该语句走的全表扫描，而没走索引，id2字段上创建了联合索引，为什么不走索引呢？

查看执行计划3：

SQL> select * from t where id1=3 and id2=2;

Execution Plan

----------------------------------------------------------

Plan hash value: 1660670018

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |    33 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |    33 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_ID1_ID2 |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID1"=3 AND "ID2"=2)

Note

-----

- dynamic sampling used for this statement (level=2)

SQL> select * from t where id2=2 and id1=3;

Execution Plan

----------------------------------------------------------

Plan hash value: 1660670018

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |    33 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |    33 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_ID1_ID2 |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ID1"=3 AND "ID2"=2)

Note

-----

- dynamic sampling used for this statement (level=2)

SQL>

Where从句中包含联合索引的所有字段时，优化器都会选择走索引，不论是将id1放前还是id2放前。

查看执行计划4：

SQL> select * from t where id1=2 and name='on';

Execution Plan

----------------------------------------------------------

Plan hash value: 1660670018

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |    33 |     1   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T             |     1 |    33 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T_ID1_ID2 |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("NAME"='on')

2 - access("ID1"=2)

Note

-----

- dynamic sampling used for this statement (level=2)

SQL> select * from t where id2=2 and name='on';

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    33 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    |     1 |    33 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("ID2"=2 AND "NAME"='on')

Note

-----

- dynamic sampling used for this statement (level=2)

SQL>

看到，第一条SQL语句（id1=2 and name=’on’）走了索引，而第二条SQL语句（id2=2 and name=’on’）走的却是全表扫描，似乎更奇怪？

最后，我们可以得出以下结论：

1 对于联合索引，在Where限制条件中出现所有被索引的列时，优化器会选择走索引（上述执行计划3）；

2 对于联合索引，在Where限制条件中出现联合索引中前导的列，即创建联合索引时前导的列时，优化器会选择走索引（执行计划1,2,4中，凡是有id1作为限制条件时都会走索引，而将id2作为限制条件时均不走索引，而走全表扫描）；

3 对于创建联合索引时，应考虑被索引字段的优先顺序，应将经常作为限制条件的字段放在首位；重复值少，即高基数(high-cardinaltiy)的列往前靠，而重复值多的字段往后靠；

4 对于同一张表，可以创建多个不同的联合索引，前提是，被索引的字段的个数、顺序不能一样,否则报错，ORA-01408。如：

SQL> create index idx1_t_id1_id2 on t(id1,id2);

create index idx1_t_id1_id2 on t(id1,id2)

*

ERROR at line 1:

ORA-01408: such column list already indexed

SQL> create index idx1_t_id1_id2 on t(id2,id1);

Index created.

SQL>

5 联合索引及前导列的解释：

A composite index, also called a concatenated index,is an index on multiple columns in a table.
A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index.

Consider this CREATE INDEX statement:

CREATE INDEX comp_ind ON tab1(x, y, z);

These combinations of columns are leading portions of the index: x, xy, and xyz.

These combinations of columns are not leading portions of the index: yz, y, and z.

A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index.

ORA-00600: internal error code, arguments: [4194]

2月 17,2011 留下评论 Oracle

在修改_allow_resetlogs_corruption参数为TRUE，并强制打开数据库后，遭遇下述错误信息：该错误信息来自alert告警日志文件：

Thu Feb 17 14:07:30 2011
Errors in file /u01/app/admin/orcl/bdump/orcl_smon_26850.trc:
ORA-00600: internal error code, arguments: [4194], [41], [31], [], [], [], [], []

通常ORA-00600 4194级的错误是跟回滚段错误相关！继续查看TRACE文件（/u01/app/admin/orcl/bdump/orcl_smon_26850.trc）看到有下述相关信息：

 Acq rbs _SYSSMU1$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 1 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU2$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 2 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU3$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 3 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU4$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 4 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU5$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 5 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU6$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 6 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU7$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 7 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU8$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 8 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU9$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 9 Onlined
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Acq rbs _SYSSMU10$
 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name=   type=0
 Undo Segment 10 Onlined

Google之，获取相关解决办法： ① 启动数据库，获取回滚段信息：

SYS@orcl > conn / as sysdba;
Connected to an idle instance.
SYS@orcl > startup
ORACLE instance started.
Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             171966832 bytes
Database Buffers          327155712 bytes
Redo Buffers                7168000 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
SYS@orcl > select * from v$rollname;
ERROR:
ORA-03114: not connected to ORACLE
SYS@orcl > conn / as sysdba;
Connected.
SYS@orcl > startup mount;
ORA-01081: cannot start already-running ORACLE - shut it down first
SYS@orcl > conn / as sysdba;
Connected.
SYS@orcl > select * from V$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
         1 _SYSSMU1$
         2 _SYSSMU2$
         3 _SYSSMU3$
         4 _SYSSMU4$
         5 _SYSSMU5$
         6 _SYSSMU6$
         7 _SYSSMU7$
         8 _SYSSMU8$
         9 _SYSSMU9$
        10 _SYSSMU10$
11 rows selected.
SYS@orcl >

此时，数据库虽可以使用，但是还是有问题的，过一段儿时间，莫名其妙的宕机！！！再次尝试启动数据库的时候，却非常正常，并未报错！！！

SYS@orcl > startup
ORACLE instance started.
Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             171966832 bytes
Database Buffers          327155712 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$
SEGMENT_NAME
------------------------------
_SYSSMU11$
12 rows selected.
SYS@orcl > select segment_name from dba_rollback_segs;
select segment_name from dba_rollback_segs
*
ERROR at line 1:
ORA-03135: connection lost contact
SYS@orcl >

② 开始通过调整参数的方式，来修复数据库：

SYS@orcl > conn / as sysdba;
Connected to an idle instance.
SYS@orcl > create pfile from spfile;
File created.
SYS@orcl >

修改pfile，在该文件中，添加如下信息：

[oracle@rhel10g dbs]$ tail -2  /u01/app/oracle/dbs/initorcl.ora
*.undo_management='MANUAL'
_corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$',

'_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$',

'_SYSSMU9$','_SYSSMU10$','_SYSSMU11$'

这两个参数表示，回滚段手工管理，并设置_corrupted_rollback_segments后面的那11个回滚段为损坏状态！ ③ 然后，用该PFILE来启动数据库：

SYS@orcl > startup pfile=/u01/app/oracle/dbs/initorcl.ora
ORACLE instance started.
Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             171966832 bytes
Database Buffers          327155712 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select * from V$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
SYS@orcl > select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$
SEGMENT_NAME
------------------------------
_SYSSMU11$
12 rows selected.

数据库正常启动，并未像刚才的那样，莫名其妙的“死”掉！！！但是，数据看出现下述症状，普通用户在数据库内无法做事务操作，而SYS用户却可以。

SYS@orcl > select count(*) from m;
  COUNT(*)
----------
       176
SYS@orcl > insert into m select * from m;
176 rows created.
SYS@orcl >

上述看到，SYS用户正常做操作。而HR用户执行事务就报错：ORA-01552！

SYS@orcl > conn hr/hr;
Connected.
HR@orcl > select count(*) from t;
  COUNT(*)
----------
       214
HR@orcl > insert into t select * from t;
insert into t select * from t
            *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
HR@orcl >

④ 删除，并重建UNDO表空间：

SYS@orcl > conn / as sysdba;
Connected.
SYS@orcl > show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SYS@orcl > col file_name for a40
SYS@orcl > col tablespace_name for a15
SYS@orcl > set line 120
SYS@orcl > select file_name,tablespace_name from dba_data_files;
FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ---------------
/u01/app/oradata/orcl/users01.dbf        USERS
/u01/app/oradata/orcl/sysaux01.dbf       SYSAUX
/u01/app/oradata/orcl/undotbs1.dbf       UNDOTBS1
/u01/app/oradata/orcl/system01.dbf       SYSTEM
/u01/app/oradata/orcl/example01.dbf      EXAMPLE
SYS@orcl > drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SYS@orcl > select file_name,tablespace_name from dba_data_files;
FILE_NAME                                TABLESPACE_NAME
---------------------------------------- ---------------
/u01/app/oradata/orcl/users01.dbf        USERS
/u01/app/oradata/orcl/sysaux01.dbf       SYSAUX
/u01/app/oradata/orcl/system01.dbf       SYSTEM
/u01/app/oradata/orcl/example01.dbf      EXAMPLE
SYS@orcl > create undo tablespace undotbs1 datafile '/u01/app/oradata/orcl/undotbs1.dbf' size 50m;
Tablespace created.
SYS@orcl >

⑤ 关闭实例，生成新的参数文件：

SYS@orcl > shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl >

⑥ 修改PFILE，这次要去掉_corrupted_rollback_segments参数的设置，并将undo_management改回为AUTO：

[oracle@rhel10g ~]$ tail -3 /u01/app/oracle/dbs/initorcl.ora
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/admin/orcl/udump'
*.undo_management='auto'

⑦ 然后创建SPFILE：

SYS@orcl > create spfile from pfile;
File created.
SYS@orcl > !strings /u01/app/oracle/dbs/spfileorcle.ora | grep undo
strings: '/u01/app/oracle/dbs/spfileorcle.ora': No such file
SYS@orcl > !strings /u01/app/oracle/dbs/spfileorcl.ora | grep undo
*.undo_management='auto'
*.undo_tablespace='UNDOTBS1'
SYS@orcl >

⑧ 用新SPFILE启动数据库，并做校验：

SYS@orcl > startup
ORACLE instance started.
Total System Global Area  507510784 bytes
Fixed Size                  1220240 bytes
Variable Size             171966832 bytes
Database Buffers          327155712 bytes
Redo Buffers                7168000 bytes
Database mounted.
Database opened.
SYS@orcl > select count(*) from m;
  COUNT(*)
----------
       352
SYS@orcl > select * from v$rollname;
       USN NAME
---------- ------------------------------
         0 SYSTEM
         1 _SYSSMU1$
         2 _SYSSMU2$
         3 _SYSSMU3$
         4 _SYSSMU4$
         5 _SYSSMU5$
         6 _SYSSMU6$
         7 _SYSSMU7$
         8 _SYSSMU8$
         9 _SYSSMU9$
        10 _SYSSMU10$
11 rows selected.
SYS@orcl > select segment_name from dba_rollback_segs;
SEGMENT_NAME
------------------------------
SYSTEM
_SYSSMU1$
_SYSSMU2$
_SYSSMU3$
_SYSSMU4$
_SYSSMU5$
_SYSSMU6$
_SYSSMU7$
_SYSSMU8$
_SYSSMU9$
_SYSSMU10$
11 rows selected.
SYS@orcl > select count(*) from m;
  COUNT(*)
----------
       352
SYS@orcl > conn hr/hr;
Connected.
HR@orcl > select count(*) from t;
  COUNT(*)
----------
       214
HR@orcl > insert into t select * from t;
214 rows created.
HR@orcl > commit;
Commit complete.
HR@orcl > select count(*) from t;
  COUNT(*)
----------
       428
HR@orcl >

运行一段时间，发现数据库并无其它异常。至此，解决ORA-00600 4194的错误！

Category Experiments

主库创建数据文件，确认物理备库是否自动生成对应的数据文件？

主库做事务操作，在物理备库上确认是否可以看到对应的记录？

主备库完成一次switchover的Role transaction。

测试一：主库创建数据文件，备库确认能否看到对应的数据文件？

1 首先，在主库上创建一个测试表空间TEST：

2 然后，在物理备库上查看是否生成该文件？

3 其次，我们在备库上启动Redo Apply：

4 最后，我们在主库上删除TEST表空间后，同样在备库上看到TEST表空间自动被删除！至此，说明我们的Dataguard功能测试一完成。

测试二：主库创建测试数据，到备库上去查看，query是否同步？

1 主库创建测试数据：

2 备库确认：

3 最后，发现备库停止Redo Apply后，并将数据库以read only方式打开后，我们可以看到数据已经同步到物理备库！说明功能测试二已经达到预期目的！

测试三：角色转换之switchover

1 物理备库置于MOUNT状态。在switchover过程中，如果目标库是物理备库的话，最好是将物理备库置于MOUNT状态，当然也可以置于OPEN READ ONLY方式，不过官方文档说处于OPEN READ ONLY方式时，可能会延长switchover的时间。

2 RAC主库停止glndb2实例，只留glndb1运行：

4 切换原RAC主库到备库角色。

5 关闭原主库实例glndb1，重新启动glndb1到MOUNT状态：

7 切换原备库至主库角色。

8 完成原主库到新主库的角色转换。

9 在新备库上[原RAC主库]启动glndb2实例至MOUNT状态，并启用redo apply，确认数据是否能与新主库[原单实例备库]同步？