十二月 | 2014 | Lynn の世界

---查看当前集群数据库实例与主机对应关系 [oracle@k1rac2 ~]$ crs_stat.sh Name Target State Host ----------------------------------- ---------- --------- ------- ora.etltest.db ONLINE ONLINE k1rac4 ora.etltest.etltest1.inst ONLINE ONLINE k1rac4 ora.etltest.etltest2.inst ONLINE ONLINE k1rac2 ora.etltest.etltests.cs ONLINE ONLINE k1rac2 ora.etltest.etltests.etltest2.srv ONLINE ONLINE k1rac2 ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2 ora.k1rac2.gsd ONLINE ONLINE k1rac2 ora.k1rac2.ons ONLINE ONLINE k1rac2 ora.k1rac2.vip ONLINE ONLINE k1rac2 ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4 ora.k1rac4.gsd ONLINE ONLINE k1rac4 ora.k1rac4.ons ONLINE ONLINE k1rac4 ora.k1rac4.vip ONLINE ONLINE k1rac4 ---查看集群数据库实例spfile文件位置 SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ---------------------------------- spfile string /home/db/oracle/oradata/etl_spfile ---创建集群数据库实例pfile文件 SQL> create pfile='/tmp/pfileetl.ora' from spfile; File created. ---将以下两行添加到新创建的集群数据库实例pfile文件中 etltest1.instance_name=etltest1 etltest2.instance_name=etltest2 ---将主机k1rac2上数据库库实例etltest2密码文件改为实例etltest1所有 [oracle@k1rac2 dbs]$ mv orapwetltest2 orapwetltest1 ---将主机k1rac2上数据库库实例etltest2 pfile文件改为实例etltest1所有 [oracle@k1rac2 dbs]$ more initetltest2.ora SPFILE='/home/db/oracle/oradata/etl_spfile' [oracle@k1rac2 dbs]$ mv initetltest2.ora initetltest2.ora ---将主机k1rac4上数据库库实例etltest1密码文件改为实例etltest2所有 [oracle@k1rac4 dbs]$ mv orapwetltest1 orapwetltest2 ---将主机k1rac4上数据库库实例etltest1 pfile文件改为实例etltest2所有 [oracle@k1rac4 dbs]$ more initetltest1.ora SPFILE='/home/db/oracle/oradata/etl_spfile' [oracle@k1rac4 dbs]$ mv initetltest1.ora initetltest2.ora ---停止集群数据库实例 [oracle@k1rac2 dbs]$ srvctl stop database -d etltest [oracle@k1rac2 dbs]$ crs_stat.sh Name Target State Host ----------------------------------- ---------- --------- ------- ora.etltest.db OFFLINE OFFLINE ora.etltest.etltest1.inst OFFLINE OFFLINE ora.etltest.etltest2.inst OFFLINE OFFLINE ora.etltest.etltests.cs OFFLINE OFFLINE ora.etltest.etltests.etltest2.srv OFFLINE OFFLINE ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2 ora.k1rac2.gsd ONLINE ONLINE k1rac2 ora.k1rac2.ons ONLINE ONLINE k1rac2 ora.k1rac2.vip ONLINE ONLINE k1rac2 ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4 ora.k1rac4.gsd ONLINE ONLINE k1rac4 ora.k1rac4.ons ONLINE ONLINE k1rac4 ora.k1rac4.vip ONLINE ONLINE k1rac4 ---更新集群数据库spfile文件 SQL> create SPFILE='/home/db/oracle/oradata/etl_spfile' from pfile='/tmp/pfileetl.ora'; File created. ---将数据库及实例从集群中移除 [oracle@k1rac2 dbs]$ srvctl remove database -d etltest Remove the database etltest? (y/[n]) y [oracle@k1rac2 dbs]$ crs_stat.sh Name Target State Host ----------------------------------- ---------- --------- ------- ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2 ora.k1rac2.gsd ONLINE ONLINE k1rac2 ora.k1rac2.ons ONLINE ONLINE k1rac2 ora.k1rac2.vip ONLINE ONLINE k1rac2 ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4 ora.k1rac4.gsd ONLINE ONLINE k1rac4 ora.k1rac4.ons ONLINE ONLINE k1rac4 ora.k1rac4.vip ONLINE ONLINE k1rac4 ---重新将数据库及实例添加到集群 [oracle@k1rac2 dbs]$ srvctl add database -d etltest -o $ORACLE_HOME [oracle@k1rac2 dbs]$ crs_stat.sh Name Target State Host ----------------------------------- ---------- --------- ------- ora.etltest.db OFFLINE OFFLINE ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2 ora.k1rac2.gsd ONLINE ONLINE k1rac2 ora.k1rac2.ons ONLINE ONLINE k1rac2 ora.k1rac2.vip ONLINE ONLINE k1rac2 ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4 ora.k1rac4.gsd ONLINE ONLINE k1rac4 ora.k1rac4.ons ONLINE ONLINE k1rac4 ora.k1rac4.vip ONLINE ONLINE k1rac4 [oracle@k1rac2 dbs]$ srvctl add instance -d etltest -i etltest1 -n k1rac2 [oracle@k1rac2 dbs]$ srvctl add instance -d etltest -i etltest2 -n k1rac4 ---查看集群中数据库及实例状态 [oracle@k1rac2 dbs]$ crs_stat.sh Name Target State Host ----------------------------------- ---------- --------- ------- ora.etltest.db OFFLINE OFFLINE ora.etltest.etltest1.inst OFFLINE OFFLINE ora.etltest.etltest2.inst OFFLINE OFFLINE ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2 ora.k1rac2.gsd ONLINE ONLINE k1rac2 ora.k1rac2.ons ONLINE ONLINE k1rac2 ora.k1rac2.vip ONLINE ONLINE k1rac2 ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4 ora.k1rac4.gsd ONLINE ONLINE k1rac4 ora.k1rac4.ons ONLINE ONLINE k1rac4 ora.k1rac4.vip ONLINE ONLINE k1rac4 ---启动集群数据库 [oracle@k1rac2 dbs]$ srvctl start database -d etltest [oracle@k1rac2 dbs]$ crs_stat.sh Name Target State Host ----------------------------------- ---------- --------- ------- ora.etltest.db ONLINE ONLINE k1rac2 ora.etltest.etltest1.inst ONLINE ONLINE k1rac2 ora.etltest.etltest2.inst ONLINE ONLINE k1rac4 ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2 ora.k1rac2.gsd ONLINE ONLINE k1rac2 ora.k1rac2.ons ONLINE ONLINE k1rac2 ora.k1rac2.vip ONLINE ONLINE k1rac2 ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4 ora.k1rac4.gsd ONLINE ONLINE k1rac4 ora.k1rac4.ons ONLINE ONLINE k1rac4 ora.k1rac4.vip ONLINE ONLINE k1rac4 ---添加相应的Service服务 [oracle@k1rac2 dbs]$ srvctl add service -d etltest -s etltests -r etltest1 -a etltest2 -P BASIC [oracle@k1rac2 dbs]$ crs_stat.sh Name Target State Host ----------------------------------- ---------- --------- ------- ora.etltest.db ONLINE ONLINE k1rac2 ora.etltest.etltest1.inst ONLINE ONLINE k1rac2 ora.etltest.etltest2.inst ONLINE ONLINE k1rac4 ora.etltest.etltests.cs OFFLINE OFFLINE ora.etltest.etltests.etltest1.srv OFFLINE OFFLINE ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2 ora.k1rac2.gsd ONLINE ONLINE k1rac2 ora.k1rac2.ons ONLINE ONLINE k1rac2 ora.k1rac2.vip ONLINE ONLINE k1rac2 ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4 ora.k1rac4.gsd ONLINE ONLINE k1rac4 ora.k1rac4.ons ONLINE ONLINE k1rac4 ora.k1rac4.vip ONLINE ONLINE k1rac4 ---启动新添加的服务并查看集群状态 [oracle@k1rac2 dbs]$ srvctl start service -d etltest [oracle@k1rac2 dbs]$ crs_stat.sh Name Target State Host ----------------------------------- ---------- --------- ------- ora.etltest.db ONLINE ONLINE k1rac2 ora.etltest.etltest1.inst ONLINE ONLINE k1rac2 ora.etltest.etltest2.inst ONLINE ONLINE k1rac4 ora.etltest.etltests.cs ONLINE ONLINE k1rac2 ora.etltest.etltests.etltest1.srv ONLINE ONLINE k1rac2 ora.k1rac2.LISTENER_K1RAC2.lsnr ONLINE ONLINE k1rac2 ora.k1rac2.gsd ONLINE ONLINE k1rac2 ora.k1rac2.ons ONLINE ONLINE k1rac2 ora.k1rac2.vip ONLINE ONLINE k1rac2 ora.k1rac4.LISTENER_K1RAC4.lsnr ONLINE ONLINE k1rac4 ora.k1rac4.gsd ONLINE ONLINE k1rac4 ora.k1rac4.ons ONLINE ONLINE k1rac4 ora.k1rac4.vip ONLINE ONLINE k1rac4 --- 更新集群节点listener和tns文件

关于Oracle数据库表的主键，通常可以理解为非空且唯一，关于这种理解是否准确，请看下面的测试！

################################ 默认情况 #################################
lynn@10G> create table t1 (id number(8) primary key,name varchar2(10));

Table created.

lynn@10G> desc t1
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER(8)
 NAME                                                           VARCHAR2(10)

lynn@10G> insert into t1 values (1,'aa');

1 row created.

lynn@10G> insert into t1 values (2,'bb');

1 row created.

lynn@10G> select * from t1;

        ID NAME
---------- ----------
         1 aa
         2 bb

lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T1';

INDEX_NAME      INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE  UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
SYS_C005647     NORMAL     LYNN       T1         TABLE       UNIQUE

lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T1';

OWNER                          CONSTRAINT_NAME                C TABLE_NAME
------------------------------ ------------------------------ - ----------
LYNN                           SYS_C005647                    P T1

lynn@10G> drop index SYS_C005647;
drop index SYS_C005647
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

lynn@10G> alter table t1 drop primary key;

Table altered.

lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T1';

no rows selected

lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T1';

no rows selected


##################### 创建表时同时创建索引并指定给主键列 #####################
lynn@10G> CREATE TABLE t2
  2    (
  3      id NUMBER(8) PRIMARY KEY USING INDEX
  4      (CREATE INDEX t2_indx ON t2
  5        (id
  6        )
  7      ),
  8      name VARCHAR2(10)
  9    );

Table created.

lynn@10G> desc t2
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER(8)
 NAME                                                           VARCHAR2(10)

lynn@10G> insert into t2 values(1,'aa');

1 row created.

lynn@10G> insert into t2 values(2,'bb');

1 row created.

lynn@10G> insert into t2 values(2,'bb');
insert into t2 values(2,'bb')
*
ERROR at line 1:
ORA-00001: unique constraint (LYNN.SYS_C005649) violated


lynn@10G> select * from t2;

        ID NAME
---------- ----------
         1 aa
         2 bb

lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T2';

INDEX_NAME      INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE  UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
T2_INDX         NORMAL     LYNN       T2         TABLE       NONUNIQUE

lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T2';

OWNER                          CONSTRAINT_NAME                C TABLE_NAME
------------------------------ ------------------------------ - ----------
LYNN                           SYS_C005649                    P T2

lynn@10G> drop index T2_INDX;
drop index T2_INDX
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

lynn@10G> alter table t2 drop primary key;

Table altered.

lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T2';

INDEX_NAME      INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE  UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
T2_INDX         NORMAL     LYNN       T2         TABLE       NONUNIQUE

lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T2';

no rows selected


################### 先创建表 | 再创建索引 | 再指定给主键列 ###################
lynn@10G> create table t3 (id number(8),name varchar2(10));

Table created.

lynn@10G> insert into t3 values (1,'aa');

1 row created.

lynn@10G> insert into t3 values (2,'bb');

1 row created.

lynn@10G> desc t3
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             NUMBER(8)
 NAME                                                           VARCHAR2(10)

lynn@10G> create bitmap index t3_indx on t3(id);

Index created.

lynn@10G> alter table t3 add primary key(id) using index t3_indx;
alter table t3 add primary key(id) using index t3_indx
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

lynn@10G> drop index t3_indx;

Index dropped.

lynn@10G> create index t3_indx on t3(id) reverse;

Index created.

lynn@10G> alter table t3 add primary key(id) using index t3_indx;

Table altered.

lynn@10G> desc t3
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER(8)
 NAME                                                           VARCHAR2(10)

lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T3';

INDEX_NAME      INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE  UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
T3_INDX         NORMAL/REV LYNN       T3         TABLE       NONUNIQUE

lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T3';

OWNER                          CONSTRAINT_NAME                C TABLE_NAME
------------------------------ ------------------------------ - ----------
LYNN                           SYS_C005651                    P T3

lynn@10G> drop index t3_indx;
drop index t3_indx
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

lynn@10G> alter table t3 drop constraint SYS_C005651;

Table altered.

lynn@10G> select OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from user_constraints where TABLE_NAME='T3';

no rows selected

lynn@10G> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,UNIQUENESS from user_indexes where TABLE_NAME='T3';

INDEX_NAME      INDEX_TYPE TABLE_OWNE TABLE_NAME TABLE_TYPE  UNIQUENES
--------------- ---------- ---------- ---------- ----------- ---------
T3_INDX         NORMAL/REV LYNN       T3         TABLE       NONUNIQUE

lynn@10G> drop index t3_indx;

Index dropped.

lynn@10G> create index t3_indx on t3 (round(id,0));

Index created.

lynn@10G> alter table t3 add primary key(id) using index t3_indx;
alter table t3 add primary key(id) using index t3_indx
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

############################## 主键测试结论 ###############################
关于主键的官方解释链接：
http://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT322
http://docs.oracle.com/cd/E11882_01/server.112/e40540/datainte.htm#CNCPT1646
http://docs.oracle.com/cd/E11882_01/server.112/e41084/clauses002.htm#SQLRF52180

To enable a unique or primary key constraint, you must have the privileges necessary to create an index on the table. You need these privileges because Oracle Database creates an index on the columns of the unique or primary key in the schema containing the table.

primary key 可理解为如下形式：
not null constraint + unique constraint + index

1.虽然这个index可以不是unique，但却必须存在，对于Oracle DB而言，它认为这个index是unique的，这个索引不能是函数和位图索引；
2.如果主键列上的索引是人为指定创建的，当主键约束被删除后，主键列上的索引不会像默认情况那样也被删除；
3.通常理解的primary key是not null + unique index也没错，因为not null + unique index可以实现主键的作用，但是主键的唯一性不是通过主键列上的这个index来保证实现，而是通过主键约束本身来保证唯一性的！

Lynn の世界

远方，除了遥远，还有梦想！

月度归档：2014年12月

Oracle 10g RAC修改数据库实例与主机对应关系

关于Primary Key测试