分类：oracle

把索引分区设置为不可用后重建
在数据仓库环境中装载大量数据时，由于索引的存在，批量dml操作的速度可能会非常慢。分区索引的关键好处之一是，可以在批量加载数据前，把索引的某些部分设置为unusable，然后在加载完成后，仅仅需要对那些受影响的部分索引分区进行重建。

从根本上说，先把索引标记为不可用，然后重建索引是相当简单的。例如:

SQL> alter table employees_part modify partition pmax unusable local indexes;

Table altered.
SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              USABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     UNUSABLE

23 rows selected.

然后，在批量加载操作完成之后，就可以执行以下命令，重建那些受批量加载操作影响的索引分区:

SQL> alter table employees_part modify partition pmax rebuild unusable local indexes;

Table altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              USABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     USABLE

23 rows selected.

使用此命令重建索引有一个明显的缺点：索引分区是顺序建立的，即一次重建一个索引分区。如果表上有许多索引，那么用这个命令会减缓重建过程，如果此操作是常规性处理任务的一部分，那么这种减缓会令人难以接受。一种缓解alter table … rebuild local indexes unusable命令的串行限制的方法是，并行重建每个受影响的分区。

实现并行重建索引操作有好几种方法。

重建全局分区索引和非分区索引

对表进行任何一种分区级的操作，几乎都会使用所有全局分区索引或非分区索引无法使用。从本质上讲，索引始终必须重建。Oracle11g中的一个内置功能，允许把重建索引作为分区表操作的一部分来执行。使用分区级合并操作的例子，你可以看到，可以在alter table … merge命令中添加更新索引(update indexes)子句，这指示Oracle重建被分区级操作标记为不可用的任何索引。请看下面的例子:

SQL> alter table employees_part merge partitions p2001,pmax into partition pmax update indexes;

Table altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              USABLE
EMPLOYEES_PART_UK1             MANAGER_200                              USABLE
EMPLOYEES_PART_UK1             MANAGER_300                              USABLE
EMPLOYEES_PART_UK1             MANAGER_400                              USABLE
EMPLOYEES_PART_UK1             MANAGER_500                              USABLE
EMPLOYEES_PART_UK1             MANAGER_600                              USABLE
EMPLOYEES_PART_UK1             MANAGER_700                              USABLE
EMPLOYEES_PART_UK1             MANAGER_800                              USABLE
EMPLOYEES_PART_UK1             MANAGER_900                              USABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              USABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     USABLE

23 rows selected.

使用update indexes子句重建索引的一个关键优势是，在重建操作过程中索引仍然是在线和可用的。虽然上述例子非常简单，但使用update indexes子句的缺点之一是，由于在分区级别的操作中包装了索引重建操作，在如何重建索引方面，你失去了一定的灵活性。例如，如果你有多个索引需要重建。分别发出命令来重建每个索引的速度可能会更快。要达到这种效果，就可以同时并发地运行多个alter index … rebuild命令。虽然这种方法比较复杂，但哪怕只为了加快速度，这么做可能也是有必要的。

要重建非分区索引，只需要执行alter index … rebuild命令即可。

SQL> alter index employees_part_pk rebuild;

Index altered.

此外，对于每个索引，可以选择性地决定使用并行机制，如下面的例子所示:

SQL> alter index employees_part_pk rebuild parallel(degree 4);

Index altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     USABLE

23 rows selected.

使用这种方法同样需要有足够的判断力和常识。如果需要一次性重建许多索引，同时又想使用并行机制，那么你必须注意到并发操作会有一个缺点。这需要根据具体的环境来作出决定。最初，最好用谨慎的态度开始。可以先慢慢提交少量的操作，以确保并发操作不造成IO或CPU瓶颈，也不造成临时表空间的瓶颈。如果并发操作没有造成这些瓶颈，那么就可以同时运行更多的重建命令。

重建非分区索引还有另一种选择，即简单地删除非分区不可用索引，并使用create index命令重新创建索引。

如果要重建全局分区索引，也有两种选择，既可以删除索引并使用create index命令作为一个整体重新创建索引，也可以逐个分区的重建全局分区索引。请看下面的例子:

SQL> alter index employees_part_uk1 rebuild partition manager_max;

Index altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              USABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     USABLE

23 rows selected.

你将需要对每个全局索引分区执行alter index … rebuild命令。

请注意不能把全局分区索引作为一个整体来重建。请看下面的例子:

SQL> alter index employees_part_uk1 rebuild;
alter index employees_part_uk1 rebuild
            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

正因为如此，当整个全局分区索引被标记为unusable时，删除它并使用create index命令重新创建索引，这样操作起来可能更为简单。

维护分区表的索引
虽然分区表和分区索引提供了许多好处，但在设计应用程序时就必须考虑到，创建分区表和分区索引对维护产生的影响。根据环境的情况和具体的应用程序设计和数据库设计，维护操作也会有所不同。对于你的具体应用程序，这包括应用程序的DML模式和被插入，更新和删除的数据量。索引的存在会造成DML操作速度减慢。在某些应用程序中，特别是在数据仓库领域，执行分区一级的操作有利于加快应用程序整体处理速度。分区级的操作可能会对索引产生重大影响，这取决于应用程序使用了什么类型的索引。

下面的例子展示了分区级的操作对不同类型索引的影响。每个例子使用一个测试表，其中包含员工数据。此表上有三个索引：一个非分区索引（employees_part_i1)，一个本地分区索引(employees_part_li1)和一个全局分区索引(employees_parttest_gi1)。你会看到对表执行分区级别的操作对表内每个索引的实际影响。

注意：如果在分区表任何一个空的分区上执行下列操作，所有相关索引均不受影响。这对各种索引类型都是成立的，不管它们是本地分区，全局分区还是非分区的索引。

SQL> create table employees_part
  2  (
  3  employee_id number(6) not null,
  4  first_name varchar2(20),
  5  last_name varchar2(25) not null,
  6  email varchar2(25) not null,
  7  phone_numbr varchar2(20),
  8  hire_date date not null,
  9  job_id varchar2(10) not null,
 10  salary number(8,2),
 11  commission_pct number(2,2),
 12  manager_id number(6),
 13  department_id number(4),
 14  constraint employees_part_pk primary key(employee_id,hire_date)
 15  )
 16  partition by range(hire_date)
 17  (
 18  partition p1990 values less than(to_date('1991-01-01','yyyy-mm-dd')),
 19  partition p1991 values less than(to_date('1992-01-01','yyyy-mm-dd')),
 20  partition p1992 values less than(to_date('1993-01-01','yyyy-mm-dd')),
 21  partition p1993 values less than(to_date('1994-01-01','yyyy-mm-dd')),
 22  partition p1994 values less than(to_date('1995-01-01','yyyy-mm-dd')),
 23  partition p1995 values less than(to_date('1996-01-01','yyyy-mm-dd')),
 24  partition p1996 values less than(to_date('1997-01-01','yyyy-mm-dd')),
 25  partition p1997 values less than(to_date('1998-01-01','yyyy-mm-dd')),
 26  partition p1998 values less than(to_date('1999-01-01','yyyy-mm-dd')),
 27  partition p1999 values less than(to_date('2000-01-01','yyyy-mm-dd')),
 28  partition p2000 values less than(to_date('2001-01-01','yyyy-mm-dd')),
 29  partition pmax values less than(maxvalue)
 30  );

Table created.

SQL> create index employee_part_li on employees_part(hire_date) local;

Index created.

SQL> select partition_name,status from user_ind_partitions where index_name='EMPLOYEE_PART_LI';

PARTITION_NAME                                                                                                                   STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
P1990                                                                                                                            USABLE
P1991                                                                                                                            USABLE
P1992                                                                                                                            USABLE
P1993                                                                                                                            USABLE
P1994                                                                                                                            USABLE
P1995                                                                                                                            USABLE
P1996                                                                                                                            USABLE
P1997                                                                                                                            USABLE
P1998                                                                                                                            USABLE
P1999                                                                                                                            USABLE
P2000                                                                                                                            USABLE
PMAX                                                                                                                             USABLE

12 rows selected.

1 添加分区
往表中添加分区对表上现有的索引影响最小。事实上，无论你选择使用什么索引类型（本地分区，全局分区或非分区），在添加分区操作后，任何现存的索引分区都不会受到影响，也不需要特定的索引级别的操作。下面的例子向测试员工表中添加一个分区，用于保存2001年的历史数据:

SQL> alter table employees_part add partition p2001 values less than(to_date('2002-01-01','yyyy-mm-dd'));
alter table employees_part add partition p2001 values less than(to_date('2002-01-01','yyyy-mm-dd'))
                                         *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition


SQL> alter table employees_part drop partition pmax;

Table altered.

SQL> select partition_name,status from user_ind_partitions where index_name='EMPLOYEE_PART_LI';

PARTITION_NAME                                                                                                                   STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
P1990                                                                                                                            USABLE
P1991                                                                                                                            USABLE
P1992                                                                                                                            USABLE
P1993                                                                                                                            USABLE
P1994                                                                                                                            USABLE
P1995                                                                                                                            USABLE
P1996                                                                                                                            USABLE
P1997                                                                                                                            USABLE
P1998                                                                                                                            USABLE
P1999                                                                                                                            USABLE
P2000                                                                                                                            USABLE

11 rows selected.

SQL> alter table employees_part add partition p2001 values less than(to_date('2002-01-01','yyyy-mm-dd'));

Table altered.

添加分区后，可以运行以下查询来确实添加分区(add partition)操作对表上现有索引的影响:

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE

13 rows selected.

2 截断分区
截断分区是删除一个表分区内所有数据的最简单的方法。而且，对于创建了本地分区索引的表，截断表分区不影响底层的索引分区，其中包括被截断的分区。然而，如果你的表上有非分区索引或者全局分区索引，截断分区就会使得Oracle无法知道哪些索引条目受到了截断操作的影响。因此，Oracle别无选择，只能简单地把整个索引标记为unusable。例如:

SQL> create index employees_part_uk1 on employees_part(manager_id)
  2  global
  3  partition by range(manager_id)
  4  (
  5  partition manager_100 values less than(100),
  6  partition manager_200 values less than(200),
  7  partition manager_300 values less than(300),
  8  partition manager_400 values less than(400),
  9  partition manager_500 values less than(500),
 10  partition manager_600 values less than(600),
 11  partition manager_700 values less than(700),
 12  partition manager_800 values less than(800),
 13  partition manager_900 values less than(900),
 14  partition manager_max values less than(maxvalue)
 15  );

Index created.

SQL> alter table employees_part truncate partition p1995;

Table truncated.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE

23 rows selected.

3 移动分区
移动分区有各种原因。你可能需要将一些表或分区移动到不同的表空间，也可能会决定压缩某个分区的数据，或者可能因为大量的更新操作已经导致了行迁移而需要重组表的分区。

执行移动分区操作时，你会再次看到本地分区索引受到的影响是最小的。本地分区索引的分区级移动操作与截断操作的一个关键的区别在于，被移动分区的索引已被标记为unusable。因为移动分区中的每一行后，现在每一行的rowid值都与先前的不同，所以Oracle需要这么做，因此该索引分区的条目现在是无效的，因为它们现在包含过时的rowid项。被移动的索引分区需要重建，以反映新的rowid值。

与截断分区的例子一样，Oracle不了解该表上的全局分区和非分区索引的表分区边界，因此需要把整个索引标志为unusable，且整个索引都需要重建安。对于本地分区索引，只需要重建受移动的表分区影响的索引分区。

在下面的例子中，需要把较旧的1995年的员工数据移动到它自己的表空间，这可能是为了能把它设置成只读:

SQL> alter table employees_part move partition p1995 tablespace reporting_index;

Table altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    UNUSABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE

23 rows selected.

4 拆分分区
拆分分区表的分区，通常是因为它不再符合应用程序的要求，或出于维护的原因需要拆分。最常见的原因之一是，需要简单地往表中添加一个分区，而它不是表最高端的分区，正因为如此，这需要通过拆分分区，而不是添加分区来实现。

下面的例子拆分了maxvalue分区以便为早于2003年1月的数据添加一个分区。对于本地分区索引，因为拆分操作触动了pmax分区，并创建一个新的分区(p2002)，pmax分区中的行已经被拆分到两个分区中。因为一些数据可能从pmax表分区移动到新的p2002表分区，所以pmax分区的索引项是过时的。因此，在此拆分操作的情况下，这两个本地索引分区都被标记为unusable。由于新的分区以前不存在，其实没有现有的本地索引分区与它对应，所以Oracle自动创建一个索引分区，并把它标记为unusable。

与前面提到的截断和移动操作一样，拆分分区后，所有的全局分区和非分区索引全部被标记为unusable。

SQL> alter table employees_part add partition pmax values less than(maxvalue);

Table altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    UNUSABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     UNUSABLE

24 rows selected.



SQL> alter table employees_part split partition pmax at (to_date('2003-01-01','yyyy-mm-dd')) into
  2  (partition p2002 tablespace reporting_index,partition pmax tablespace reporting_index);

Table altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    UNUSABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE
EMPLOYEE_PART_LI               P2002                                    UNUSABLE
EMPLOYEE_PART_LI               PMAX                                     UNUSABLE

25 rows selected.
< ?pre>
要强调的重要注意事项是，如果某个实质上往表中添加分区的分区拆分操作是必要的，因为需要一个高端分区来保存某种形式的默认数据，即使只是添加一个空分区的分区拆分操作，也可能会花费相当多的时间。因为Oracle需要分析现有的分区中的每一行，以便有效地执行正确的拆分。在这种情况下，对于指定用maxvalue指定了高端值的表，在高端分区以下，添加一个未被使用且始终为空的“虚拟”分区可能是有益的。那么，如果添加历史分区到表中，你可以始使用空的“虚拟”分区来拆分分区。这提供了两个关键的好处：首先，分区拆分操作会很快，因为没有需要分析的数据；其次，也没有索引将被标记为不可用，因为被拆分的分区没有数据，而新的分区也没有数据。

查看下面的创建表的DDL语句。对于employees_part表，员工离开了公司以后，他们的记录中的hire_date列被修改为maxvalue并留在表中作为历史记录。在这种情况下，pmax分区中将永远有数据。当你在表的pmax分区上执行分区拆分以添加今后几年的分区时，这么做总是会花时间的，而底层索引分区将标记为unusable。通过把p9999分区创建为“虚拟”分区，你绝不会把任何行添加到这个分区中。那么，当你拆分p9999分区来添加2001年及以后年份的数据分区时，它始终是在一个空的分区上进行拆分。因为拆分操作发生在空的分区上面，所以拆分操作将很快完成，而所有底层的本地索引分区将继续可用。

create table employees_part
(
employee_id number(6) not null,
first_name varchar2(20),
last_name varchar2(25) not null,
email varchar2(25) not null,
phone_numbr varchar2(20),
hire_date date not null,
job_id varchar2(10) not null,
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
constraint employees_part_pk primary key(employee_id,hire_date)
)
partition by range(hire_date)
(
partition p1990 values less than(to_date('1991-01-01','yyyy-mm-dd')),
partition p1991 values less than(to_date('1992-01-01','yyyy-mm-dd')),
partition p1992 values less than(to_date('1993-01-01','yyyy-mm-dd')),
partition p1993 values less than(to_date('1994-01-01','yyyy-mm-dd')),
partition p1994 values less than(to_date('1995-01-01','yyyy-mm-dd')),
partition p1995 values less than(to_date('1996-01-01','yyyy-mm-dd')),
partition p1996 values less than(to_date('1997-01-01','yyyy-mm-dd')),
partition p1997 values less than(to_date('1998-01-01','yyyy-mm-dd')),
partition p1998 values less than(to_date('1999-01-01','yyyy-mm-dd')),
partition p1999 values less than(to_date('2000-01-01','yyyy-mm-dd')),
partition p2000 values less than(to_date('2001-01-01','yyyy-mm-dd')),
partition p9999 values less than(to_date('9999-12-31','yyyy-mm-dd')),
partition pmax values less than(maxvalue)
);

5 交换分区
在数据仓库环境中，交换分区进行大批量数据加载特别常见。因为数据被加载到一个独立的表中，所以在加载操作期间读取操作不会受到影响。然后做一个分区交换，这实质上是对数据字典的修改，把一个独立的表段重新指向一个分区表的一部分，并把相关受影响的（原）表分区指向一个独立的表段。

分区交换与分区移动是类似的，只有受影响的本地分区索引的交换分区被标记为unusable。对于全局分区和非分区索引，你可以看到整个索引都已被标记为unusable。

SQL> alter table employees_part exchange partition p1995 with table employees_part_exch;

Table altered.


SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    UNUSABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE
EMPLOYEE_PART_LI               P2002                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     USABLE

25 rows selected.

6 删除分区
删除分区，通常发生在基于日期或时间戳的分区表中，因为有些数据已经过期，数据库不再需要这些数据了。对于本地分区索引，删除某个分区对其余的本地索引分区没有任何影响。所有本地分区索引保持在可用(usable)状态。然而，对于全局范围内分区和非分区索引，将再次把整个索引标记为不可用，因为Oracle无法确定这些索引中的哪些行已通过删除分区操作被删除了。

SQL> alter table employees_part drop partition p1995;

Table altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE
EMPLOYEE_PART_LI               P2002                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     USABLE

24 rows selected.

7 合并分区
合并分区操作本质上是分区拆分的逆操作，正如分区拆分操作，执行分区合并也是为了满足某些应用程序要求或是由于维护的原因。

下面的例子将把p2002和pmax分区全并成一个pmax分区。在这种情况下，两个分区中的所有行实质上被合并到一个新的分区。对于本地分区索引，它有一个匹配pmax的索引分区。因为分区合并改变了分区中行的组成，本地分区索引的pmax分区已被标记为unusable.

因为全局分区索引和非分区索引没有表分区组成的信息，所以整个索引又被标记为unusable.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE
EMPLOYEE_PART_LI               P2002                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     USABLE

25 rows selected.


SQL> alter table employees_part merge partitions p2002,pmax into partition pmax;

Table altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEE_PART_LI               P2001                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     UNUSABLE

24 rows selected.

注意：对于表的大多数分区级操作，如果受影响的分区是空的，那么这种操作不会影响任何相关的索引。

全局分区索引实质上意味着该索引与数据表有不同的分区方案，且是基于不同于数据表的一个或一组分区列进行分区的。这主要是为了提高数据库中数据查询的性能。基于用户对给定表的查询，为提高查询性能，可以在给定的查询列上创建全局分区的索引。

可以创建以下几种类型的全局分区索引:
.范围
.哈希

为了说明基于范围的全局分区索引，假定你发现有很多的查询要访问employees表中的manager_id列，那么就可以在manager_id列上创建分区索引，它的分区完全独立于表。查看如下范围分区索引的例子:

SQL> create table employees_jy as select * from employees;

Table created.

SQL> create index employees_gi1
  2  on employees_jy(manager_id)
  3  global
  4  partition by range(manager_id)
  5  (
  6  partition manager_100 values less than(100),
  7  partition manager_200 values less than(200),
  8  partition manager_300 values less than(300),
  9  partition manager_400 values less than(400),
 10  partition manager_500 values less than(500),
 11  partition manager_600 values less than(600),
 12  partition manager_700 values less than(700),
 13  partition manager_800 values less than(800),
 14  partition manager_900 values less than(900),
 15  partition manager_max values less than(maxvalue)
 16  );

Index created.

你可以在分区表或非分区表上创建全局分区索引。换句话说，创建全局分区索引并不需要对表进行分区。正如本章后面所讨论的，因为分区表的全局分区索引维护起来不太方便，全局分区索引并不常用，所以通常很少甚至不用它来提高查询性能，特别是在非分区表中。

基于范围的全局分区索引，必须始终为索引指定一个最大值，即用maxvalue作为最大值。这保证了新插入到相应表的任何数据将在分局分区索引中有地方保存。换句话说，对于全局分区索引，它永远不会有“越界”的情况。如果不在全局分区索引中指定上限分区，Oracle将不允许创建索引，若执行下列语句，就会出现以下错误提示信息:

SQL> create index employees_gi2
  2  on employees_jy(manager_id)
  3  global
  4  partition by range(manager_id)
  5  (
  6  partition manager_100 values less than(100),
  7  partition manager_200 values less than(200),
  8  partition manager_300 values less than(300),
  9  partition manager_400 values less than(400),
 10  partition manager_500 values less than(500),
 11  partition manager_600 values less than(600),
 12  partition manager_700 values less than(700),
 13  partition manager_800 values less than(800),
 14  partition manager_900 values less than(900)
 15  );
)
*
ERROR at line 15:
ORA-14021: MAXVALUE must be specified for all columns

由于每个全局分区索引吕必须包含一个上限分区，因此把分区添加到全局分区索引的唯一方式就是，使用alter index … split分区命令。例如:

SQL> alter index employees_gi1 split partition manager_max at(1000) into (partition manager_max,partition manager_1000);

Index altered.


SQL> select partition_name,status from user_ind_partitions where index_name='EMPLOYEES_GI1';

PARTITION_NAME                                                                                                                   STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
MANAGER_100                                                                                                                      USABLE
MANAGER_1000                                                                                                                     USABLE
MANAGER_200                                                                                                                      USABLE
MANAGER_300                                                                                                                      USABLE
MANAGER_400                                                                                                                      USABLE
MANAGER_500                                                                                                                      USABLE
MANAGER_600                                                                                                                      USABLE
MANAGER_700                                                                                                                      USABLE
MANAGER_800                                                                                                                      USABLE
MANAGER_900                                                                                                                      USABLE
MANAGER_MAX                                                                                                                      USABLE

11 rows selected.

SQL>

还可以删除全局分区索引的部分分区。然而，这样做时，如果被删除的分区包含索引条目，上限比它高的相邻分区就被标记为不可用。在下面的代码中，查询了删除分区manager_200前该索引各分区的状态:

SQL> select partition_name,status from user_ind_partitions where index_name='EMPLOYEES_GI1';

PARTITION_NAME                                                                                                                   STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
MANAGER_100                                                                                                                      USABLE
MANAGER_1000                                                                                                                     USABLE
MANAGER_200                                                                                                                      USABLE
MANAGER_300                                                                                                                      USABLE
MANAGER_400                                                                                                                      USABLE
MANAGER_500                                                                                                                      USABLE
MANAGER_600                                                                                                                      USABLE
MANAGER_700                                                                                                                      USABLE
MANAGER_800                                                                                                                      USABLE
MANAGER_900                                                                                                                      USABLE
MANAGER_MAX                                                                                                                      USABLE

11 rows selected.

然后删除分区manager_200

SQL> alter index employees_gi1 drop partition manager_200;

Index altered.

当再次查询user_ind_partitions时，你可以看到数据库把上限更高的相邻分区标记为unusable状态。

SQL> select partition_name,status from user_ind_partitions where index_name='EMPLOYEES_GI1';

PARTITION_NAME                                                                                                                   STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
MANAGER_100                                                                                                                      USABLE
MANAGER_1000                                                                                                                     USABLE
MANAGER_300                                                                                                                      UNUSABLE
MANAGER_400                                                                                                                      USABLE
MANAGER_500                                                                                                                      USABLE
MANAGER_600                                                                                                                      USABLE
MANAGER_700                                                                                                                      USABLE
MANAGER_800                                                                                                                      USABLE
MANAGER_900                                                                                                                      USABLE
MANAGER_MAX                                                                                                                      USABLE

10 rows selected.

一旦删除了全局索引分区，就必须执行alter index命令重建由于drop partition操作而被标记为unusable的分区。

SQL> alter index employees_gi1 rebuild partition manager_300;

Index altered.

SQL> select partition_name,status from user_ind_partitions where index_name='EMPLOYEES_GI1';

PARTITION_NAME                                                                                                                   STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
MANAGER_100                                                                                                                      USABLE
MANAGER_1000                                                                                                                     USABLE
MANAGER_300                                                                                                                      USABLE
MANAGER_400                                                                                                                      USABLE
MANAGER_500                                                                                                                      USABLE
MANAGER_600                                                                                                                      USABLE
MANAGER_700                                                                                                                      USABLE
MANAGER_800                                                                                                                      USABLE
MANAGER_900                                                                                                                      USABLE
MANAGER_MAX                                                                                                                      USABLE

10 rows selected.

因为必须在全局分区索引上用maxvalue指定的一个分区，所以永远无法删除上限最高的分区。例如:

SQL> alter index employees_gi1 drop partition manager_1000;
alter index employees_gi1 drop partition manager_1000
                                         *
ERROR at line 1:
ORA-14078: you may not drop the highest partition of a GLOBAL index

全局分区索引可以是唯一索引，也可以是非唯一索引。本章到目前为止，只创建了非唯一索引。下面列出的是在表上创建全局唯一索引分区的语句:

SQL> create unique index employees_uk1 on employees_jy(manager_id,employee_id)
  2  global
  3  partition by range(manager_id)
  4  (
  5  partition manager_100 values less than(100),
  6  partition manager_200 values less than(200),
  7  partition manager_300 values less than(300),
  8  partition manager_400 values less than(400),
  9  partition manager_500 values less than(500),
 10  partition manager_600 values less than(600),
 11  partition manager_700 values less than(700),
 12  partition manager_800 values less than(800),
 13  partition manager_900 values less than(900),
 14  partition manager_max values less than(maxvalue)
 15  );

Index created.

本地分区索引可以定义为前缀或非前缀索引，而全局分区索引必须创建为前缀索引，也就是说，分区列必须在该索引的前导端。如果你试图创建全局分区的非前缀索引，就会收到以下错误 提示信息:

SQL> create unique index employees_uk1 on employees_jy(employee_id)
  2  global
  3  partition by range(manager_id)
  4  (
  5  partition manager_100 values less than(100),
  6  partition manager_200 values less than(200),
  7  partition manager_300 values less than(300),
  8  partition manager_400 values less than(400),
  9  partition manager_500 values less than(500),
 10  partition manager_600 values less than(600),
 11  partition manager_700 values less than(700),
 12  partition manager_800 values less than(800),
 13  partition manager_900 values less than(900),
 14  partition manager_max values less than(maxvalue)
 15  );
partition by range(manager_id)
                             *
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed

另一种全局分区索引类型是哈希分区索引。使用哈希分区索引通常是出于性能原因，因为这使用索引在分区之间更均匀地分布。请看下面创建基于哈希的全局分区索引的例子:

SQL> create index employees_jy_ih1 on employees_jy(department_id) global partition by hash(department_id) partitions 4;

Index created.

注意:全局分区索引必须在堆组织表上创建。而且，不能创建全局分区位图索引。

管理分区表的主键和唯一索引
当需要在表上定义主键约束时，首先使用主键约束的列创建唯一索引，然后再添加约束，这通常被视为一种良好的做法。例如:

SQL> create unique index employees_part_pk on employees_part(employee_id,hire_date) local;

Index created.

SQL> alter table employees_part add constraint employees_part_pk primary key(employee_id,hire_date);

Table altered.

这样做的好处是，它允许你在必要时禁用和重新启用约束，而不必删除底层索引。当必须在一个大表上执行约束管理时，这可以节省大量的时间。在数据仓库环境中，经常有大量数据要批量加载到表中，禁用约束然后重新启用约束是很常见的操作，因为在这种情况下，在加载之前禁用约束并在加载后重新启用约束，可以节省大量的整体数据处理时间。

如果你首先创建了唯一索引，然后启用主键约束，如果禁用这个主键约束，那么你的约束和索引在数据库中仍然存在。如下面的例子所示:

SQL> alter table employees_part disable constraint employees_part_pk;

Table altered.

SQL> select i.index_name,c.constraint_type,i.partitioned from user_indexes i left join user_constraints c
  2  on (i.index_name=c.constraint_name) where i.index_name='EMPLOYEES_PART_PK';

INDEX_NAME                     CONSTRAINT_TYPE                PARTITIONED
------------------------------ ------------------------------ ----------------------------------------
EMPLOYEES_PART_PK              P                              YES

如果你用create tabel语句或单个alter table语句内联地创建主键，将同时创建底层的唯一索引。在这种情况下，如果禁用这个主键约束，你可以看到它删除了底层索引。

SQL> alter table employees_part disable constraint employees_part_pk;

Table altered.

SQL> select i.index_name,c.constraint_type,i.partitioned from user_indexes i left join user_constraints c
  2  on (i.index_name=c.constraint_name) where i.index_name='EMPLOYEES_PART_PK';
no rows selected

当对表的分区一级执行操作（如在表上执行分区拆分)时，会使用一个分区的索引变得不可用(unusable)，这种情况下，本地分区索引就变得非常有用。至于主键，你可以简单地禁用主键约束（在这种情况下，底层索引保持不变），执行分区操作，再重建该分区索引，然后重新启用主键约束。这样，索引唯一需要重建的是受上述操作影响的分区。

在唯一索引最终要成为主键时，唯一索引必须包含分区列这一限制会导致另一个常见的问题，即，有时用户提出需求，不要把分区列放到表的主键列中去。如下面的例子所示，通过使用先前创建的唯一索引，就可以简单地改变员工表来创建主键约束:

SQL> alter table employees_part add constraint employees_part_pk_1 primary key(employee_id);

Table altered.

如果你的客户需求中规定，分区列（在本列是hire_date)不得作为主键的一部分，那么也可以使用相同的语法简单地创建主键约束，这同时也创建了未分区的底层索引。下面的查询显示了这个结果:

SQL> select i.index_name,c.constraint_type,i.partitioned from user_indexes i,user_constraints c
  2  where i.index_name=c.constraint_name and i.index_name='EMPLOYEES_PART_PK_1';

INDEX_NAME                     CONSTRAINT_TYPE                PARTITIONED
------------------------------ ------------------------------ ----------------------------------------
EMPLOYEES_PART_PK_1            P                              NO

你的表上确实建立了索引，但它不是分区的。这样做的好处是，解除了唯一索引必须包含分区列的限制，可以在单个employee_id列上创建“自然”的主键约束了。缺点是，现在分区表employees上有一个非分区索引。如果现在需要在该表上执行任何分区级操作，如截断分区，移动分区，或拆分分区，等等，那么整个底层非分区索引将被标记为unusable，而且必须在执行任何分区级操作后重建它。

1 分区索引
分区索引与非分区索引相比，具有许多好处。使用分区索引的关键好处如下:
.性能方面的优势
.通过DML操作载入数据。
.通过DDL操作载入数据。
.用select语句查询数据。

.维护方面的好处
.重建索引。
.在分区一级把索引设置为不可用或不可见。

可以创建本地分区或全局分区的分区索引。本地分区索引只能存在于分区表，而全局分区的索引既可以在分区表创建，也可以在非分区表上创建。还可以在分区表上创建非分区索引。分区索引最常见的配置是在分区表上创建本地分区索引，只是因为这种配置的整体优势一般大于分区表上的全局分区索引和非分区索引。

注意：创建分区表上的非分区索引与创建非分区表上的非分区索引是相同的。

2 创建本地分区索引
最常见的分区索引类型是本地分区索引。本地分区索引只能在分区表上创建。顾名思义，“本地”是指索引条目和相应的数据之间有直接的关系。数据分区和索引分区之间存在一对一的关系。如果有某个按日期范围分区的表，今年每个月都有一个分区，那么对于2012年1月分区的所有数据，创建的每个索引在2012年1月分区都有相应的索引项。

2.1 最简单的形式
除LOCAL关键字外，最基本的创建本地分区索引的形式与创建非分区索引相同。

create table employees_part
(
EMPLOYEE_ID   NUMBER(6),
FIRST_NAME    VARCHAR2(20),
LAST_NAME     VARCHAR2(25),
EMAIL         VARCHAR2(25),
PHONE_NUMBER  VARCHAR2(20),
HIRE_DATE     DATE,
JOB_ID        VARCHAR2(10),
SALARY        NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID     NUMBER(6),
DEPARTMENT_ID  NUMBER(4)
)
partition by range(hire_date)
(
   partition p01 values less than(to_date('2002-01-01','yyyy-mm-dd')),
   partition p02 values less than(to_date('2003-01-01','yyyy-mm-dd')),
   partition p03 values less than(to_date('2004-01-01','yyyy-mm-dd')),
   partition p04 values less than(to_date('2005-01-01','yyyy-mm-dd')),
   partition p05 values less than(to_date('2006-01-01','yyyy-mm-dd')),
   partition p06 values less than(to_date('2007-01-01','yyyy-mm-dd')),
   partition p07 values less than(to_date('2008-01-01','yyyy-mm-dd')),
   partition p08 values less than(to_date('2009-01-01','yyyy-mm-dd')),
   partition p09 values less than(to_date('2010-01-01','yyyy-mm-dd')),
   partition p10 values less than(to_date('2011-01-01','yyyy-mm-dd')),
   partition p11 values less than(to_date('2012-01-01','yyyy-mm-dd')),
   partition p12 values less than(to_date('2013-01-01','yyyy-mm-dd'))
);
/




SQL> create table employees_part
  2  (
  3  EMPLOYEE_ID   NUMBER(6),
  4  FIRST_NAME    VARCHAR2(20),
  5  LAST_NAME     VARCHAR2(25),
  6  EMAIL         VARCHAR2(25),
  7  PHONE_NUMBER  VARCHAR2(20),
  8  HIRE_DATE     DATE,
  9  JOB_ID        VARCHAR2(10),
 10  SALARY        NUMBER(8,2),
 11  COMMISSION_PCT NUMBER(2,2),
 12  MANAGER_ID     NUMBER(6),
 13  DEPARTMENT_ID  NUMBER(4)
 14  )
 15  partition by range(hire_date)
 16  (
 17     partition p01 values less than(to_date('2002-01-01','yyyy-mm-dd')),
 18     partition p02 values less than(to_date('2003-01-01','yyyy-mm-dd')),
 19     partition p03 values less than(to_date('2004-01-01','yyyy-mm-dd')),
 20     partition p04 values less than(to_date('2005-01-01','yyyy-mm-dd')),
 21     partition p05 values less than(to_date('2006-01-01','yyyy-mm-dd')),
 22     partition p06 values less than(to_date('2007-01-01','yyyy-mm-dd')),
 23     partition p07 values less than(to_date('2008-01-01','yyyy-mm-dd')),
 24     partition p08 values less than(to_date('2009-01-01','yyyy-mm-dd')),
 25     partition p09 values less than(to_date('2010-01-01','yyyy-mm-dd')),
 26     partition p10 values less than(to_date('2011-01-01','yyyy-mm-dd')),
 27     partition p11 values less than(to_date('2012-01-01','yyyy-mm-dd')),
 28     partition p12 values less than(to_date('2013-01-01','yyyy-mm-dd'))
 29  );
/
Table created.


SQL> insert into employees_part select * from employees;

107 rows created.

SQL> commit;

Commit complete.


SQL> create index employees_part_i1 on employees_part(hire_date) local;

Index created.

对于这个例子，Oracle创建的分区名称将与数据分区的名称相同。此外，所有分区都将在USERS表空间内创建。

根据应用程序的需求，可能需要指定各个分区的具体信息，比如下列内容:
.分区名称
.表空间名称
.存储参数

2.2 分区级的需求
如果有特定的分区级的需求，就需要在create index ddl内指定每个分区的信息。例如:

SQL> create index employees_part_i2 on employees_part(hire_date) local
  2  (
  3  partition pi01,
  4  partition pi02,
  5  partition pi03,
  6  partition pi04,
  7  partition pi05,
  8  partition pi06,
  9  partition pi07,
 10  partition pi08,
 11  partition pi09,
 12  partition pi10,
 13  partition pi11,
 14  partition pi12
 15  );

Index created.

在这个例子中，对分区名称进行了修改，在表分区名中间插入一个i来表示索引分区。为了让索引具有与表不同的分区名称，必须在create indexddl中指定每个分区的名称。还可以为每个分区指定不同的表空间，因为它代表了一年的数据。把每年的数据放在自己的表空间内，就可以把往年数据的表空间设置为只读。这有助于提高查询速度和备份速度，因为你不需要在数据库的每个备份中都备份只读表空间。

再次强调，要创建本地分区索引，必须是在分区表上创建。否则，就会出现以下错误提示信息:

SQL> create index employees_i1 on employees(hire_date) tablespace users local;
create index employees_i1 on employees(hire_date) tablespace users local
                             *
ERROR at line 1:
ORA-14016: underlying table of a LOCAL partitioned index must be partitioned

2.3 前缀和非前缀选项
可以把本地分区索引创建为前缀或非前缀的。在创建本地分区的前缀索引时，这意味着表的分区列在索引的前导端。如果分区列不在索引的前导端，就被当作非前缘索引。在Oracle的早期版本中，本地前缀索引与非前缀索引相比，在性能上有一些优势。在Oracle后来的版本中，包括版本11gR2，建立本地前缀索引的优势已经减弱。不过，如果你的数据库环境是OLTP系统，本地前缀索引比非前缀索引更有利于提高查询性能，因为为了检索某个查询所需的数据，优化器需要扫描的索引分区可能会较少。

在创建本地分区的唯一索引时，索引必须包括分区列，否则就会出现以下错误提示信息:

SQL> create unique index employees_part_pk on employees_part(employee_id) local;
create unique index employees_part_pk on employees_part(employee_id) local
                                         *
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

在唯一索引的定义中添加分区列（在本例中是hire_date)后，就可以在employees_part表上创建唯一索引了。

SQL> create unique index employees_part_pk on employees_part(employee_id,hire_date) local;

Index created.

应用程序域索引是最不为人所知且大多数开发人员和DBA也是最少使用的一种。应用域索引支持创建自己的索引结构，以实现目前Oracle数据库中不存在的新的索引类型。Oracle数据库本身实现的全文索引，就是应用程序域索引的一个很好的例子，全文索引常用来促进在大的文本项中的搜索。为了创建全文索引，需要在create index语句中包含indextype子句，如下所示:

SQL> create table test_tab(dsc clob);

Table created.

SQL> insert into test_tab values('hi,this is xiaoming');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_tab;

DSC
--------------------------------------------------------------------------------
hi,this is xiaoming

SQL> create index test_idx on test_tab(dsc) indextype is ctxsys.context;

Index created.

SQL>
SQL> select * from test_tab where contains(dsc,'this is')>0;

no rows selected

就像收集普通索引的统计信息一样，可以调用dbms_stats.gather_index_stats存储过程来收集全文索引的统计信息。虽然我们这里只是简单地讨论了应用程序域索引，在大多数情况下，这些索引对第三方解决方案供应商有更多的用处，因为他们需要使用创新的索引解决方案来访问各类非传统的数据，如文本和图像。例如，在使用某些类型的数据，如视频剪辑时，Oracle提供的数据类型可能不太适用。此外，这些特殊类型的数据，可能需要特殊类型的操作，例如，图像中的颜色等级。你可以使用能把图像中的颜色分级的比较运算符，来定义这些特殊类型的操作。

创建用户与组
创建组(在各个主机上执行)

mkgroup -'A' id='501' adms='root' oinstall
mkgroup -'A' id='502' adms='root' asmdba
mkgroup -'A' id='503' adms='root' asmoper
mkgroup -'A' id='504' adms='root' dba
mkgroup -'A' id='505' adms='root' oper
mkgroup -'A' id='506' adms='root' backdba
mkgroup -'A' id='507' adms='root' dgdba
mkgroup -'A' id='508' adms='root' racdba
mkgroup -'A' id='509' adms='root' kmdba

创建用户(在各个主机上执行)

mkuser id=601 pgrp=oinstall groups=dba,asmdba,oper,asmoper,backdba,dgdba,racdba,kmdba home=/home/oracle oracle

创建安装目录

mkdir -p /u01/app/oraInventory
mkdir -p /u01/app/oracle/product/19.3/db
mkdir -p /u01/temp

chown -R oracle:oinstall /u01
chmod -R 775 /u01

设置用户环境变量（用oracle用户来编辑.profile)

umask 022
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3/db
export ORACLE_SID=hxsy
export ORACLE_UNQNAME=hxsy
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin
export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/u01/tmp
export TMPDIR=/u01/tmp
export PS1=`hostname`:'$PWD'"$"

使用如下命令查看虚拟内存管理参数

vmo -L minperm%
vmo -L maxperm%
vmo -L maxclient%
vmo -L lru_file_repage
vmo -L strict_maxclient
vmo -L strict_maxperm

如果设置不合适，使用如下命令修改：

vmo -p -o minperm%=3
vmo -p -o maxperm%=90
vmo -p -o maxclient%=90
vmo -p -o lru_file_repage=0
vmo -p -o strict_maxclient=1
vmo -p -o strict_maxperm=0

检查网络参数设置
ephemeral参数：
使用命令no -a |fgrep ephemeral可以查看当前系统ephemeral参数设置，建议的参数设置如下

tcp_ephemeral_high = 65500
tcp_ephemeral_low = 9000
udp_ephemeral_high= 65500
udp_ephemeral_low = 9000

如果系统中参数设置和上述值不一样，使用命令修改：

#no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500
#no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500

使用如下命令修改网络可调整参数：

#no -p -o rfc1323=1
#no -r -o ipqmaxlen=512
#no -p -o ipqmaxlen=512
#no -p -o sb_max=4194304
#no -p -o tcp_recvspace=65536
#no -p -o tcp_sendspace=65536
#no -p -o udp_recvspace=1351680 该值是udp_sendspace的10倍，但须小于sb_max
#no -p -o udp_sendspace=135168

备注：-r表示reboot后生效，-p表示即刻生效.
检查内核参数maxuproc（建议16384）和ncargs（至少128）

#lsattr -E -l sys0 -a ncargs
#lsattr -E -l sys0 -a maxuproc

如果设置不合适使用如下命令修改：

#chdev -l sys0 -a ncargs=256
#chdev -l sys0 -a maxuproc=16384

解压数据库软件

aix1:/u01/soft$unzip -q AIX.PPC64_193000_db_home.zip -d $ORACLE_HOME

安装数据库软件
在$ORACLE_HOME/install/response目录中有一个安装rsp文件示例
配置响应文件

[oracle@ora19c ~]$ vi 19c_dbinstall.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.3/db
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=racdba
oracle.install.db.rootconfig.executeRootScript=true
oracle.install.db.rootconfig.configMethod=ROOT

执行安装

openaix1:/home/oracle$$ORACLE_HOME/runInstaller -silent  -force -noconfig  -ignorePrereq  -responseFile /home/oracle/19c_dbinstall.rsp

********************************************************************************

Your platform requires the root user to perform certain pre-installation
OS preparation.  The root user should run the shell script 'rootpre.sh' before
you proceed with Oracle installation. The rootpre.sh script can be found at:
/u01/app/oracle/product/19.3/db/clone/rootpre.sh

Answer 'y' if root has run 'rootpre.sh' so you can proceed with Oracle
installation.
Answer 'n' to abort installation and then ask root to run 'rootpre.sh'.

********************************************************************************

Has 'rootpre.sh' been run by root in this machine? [y/n] (n)
y
Launching Oracle Database Setup Wizard...


 Enter password for 'root' user: 
[WARNING] [INS-13014] Target environment does not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. installActions2020-11-17_01-49-09AM.log
   ACTION: Identify the list of failed prerequisite checks from the log: installActions2020-11-17_01-49-09AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
The response file for this session can be found at:
 /u01/app/oracle/product/19.3/db/install/response/db_2020-11-17_01-49-09AM.rsp

You can find the log of this install session at:
 /u01/tmp/InstallActions2020-11-17_01-49-09AM/installActions2020-11-17_01-49-09AM.log
Successfully Setup Software with warning(s).
Moved the install session logs to:
 /u01/app/oraInventory/logs/InstallActions2020-11-17_01-49-09AM

配置监听
创建配置监听的响应文件
在/u01/app/oracle/product/19.3/db/assistants/netca/目录有一个示例文件

aix1:/home/oracle$vi 19c_netca.rsp
"19c_netca.rsp" [New file] 

[GENERAL]
RESPONSEFILE_VERSION="19.0"
CREATE_TYPE="CUSTOM"

[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""typical""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START=""LISTENER""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
NSN_NUMBER=1
NSN_NAMES={"EXTPROC_CONNECTION_DATA"}
NSN_SERVICE={"PLSExtProc"}
NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}


aix1:/home/oracle$netca /silent /responsefile /home/oracle/19c_netca.rsp
UnsatisfiedLinkError exception loading native library: njni19
java.lang.UnsatisfiedLinkError: njni19 (Not found in java.library.path)
java.lang.UnsatisfiedLinkError: oracle/net/common/NetGetEnv.jniGetOracleHome()Ljava/lang/String;
        at oracle.net.common.NetGetEnv.getOracleHome(Unknown Source)
        at oracle.net.ca.NetCALogger.getOracleHome(NetCALogger.java:230)
        at oracle.net.ca.NetCALogger.initOracleParameters(NetCALogger.java:215)
        at oracle.net.ca.NetCALogger.initLogger(NetCALogger.java:130)
        at oracle.net.ca.NetCA.main(NetCA.java:459)

Error: oracle/net/common/NetGetEnv.jniGetOracleHome()Ljava/lang/String;
Oracle Net Services configuration failed.  The exit code is 1


# lsdev | grep iocp
iocp0      Defined         I/O Completion Ports
# smitty iocp

# lsdev | grep iocp
iocp0      Available       I/O Completion Ports


aix1:/home/oracle$$ORACLE_HOME/bin/relink all 
writing relink log to: /u01/app/oracle/product/19.3/db/install/relinkActions2020-11-16_11-17-27PM.log

aix1:/home/oracle$netca /silent /responsefile /home/oracle/19c_netca.rsp

Parsing command line arguments:
    Parameter "silent" = true
    Parameter "responsefile" = /home/oracle/19c_netca.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Profile configuration complete.
Oracle Net Listener Startup:
    Running Listener Control: 
      /u01/app/oracle/product/19.3/db/bin/lsnrctl start LISTENER
    Listener Control complete.
    Listener started successfully.
Listener configuration complete.
Oracle Net Services configuration successful. The exit code is 0

aix1:/home/oracle$lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production on 16-NOV-2020 23:24:53

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=aix1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production
Start Date                16-NOV-2020 23:23:28
Uptime                    0 days 0 hr. 1 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/aix1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aix1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

创建数据库
创建配置数据库的响应文件
在/u01/app/oracle/product/19.3/db/assistants/dbca/目录中有一个示例文件

[oracle@ora19c install]$ cat /u01/app/oracle/product/19.3/db/assistants/dbca/dbca.rsp


aix1:/home/oracle$vi 19c_dbca.rsp
automaticMemoryManagement=TRUE
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0
templateName=General_Purpose.dbc
gdbName=hxsy
sid=hxsy
databaseConfigType=SI
createAsContainerDatabase=TRUE
numberOfPDBs=1
pdbName=hxsy1
useLocalUndoForPDBs=TRUE
pdbAdminPassword=hxsy_123456
sysPassword=hxsy_123456
systemPassword=hxsy_123456
storageType=FS
characterSet=ZHS16GBK
nationalCharacterSet=AL16UTF16
listeners=LISTENER
sampleSchema=TRUE
totalMemory 2048
databaseType=MULTIPURPOSE
automaticMemoryManagement=FALSE
totalMemory=4096

创建数据库

aix1:/home/oracle$dbca -silent  -createDatabase -responseFile /home/oracle/19c_dbca.rsp
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards.
   CAUSE: 
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
53% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/hxsy.
Database Information:
Global Database Name:hxsy
System Identifier(SID):hxsy
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/hxsy/hxsy.log" for further details.

给主库配置归档

SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u01/app/oracle/product/19.3/db/dbs/arch
Oldest online log sequence     5
Current log sequence           7


SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/arch' scope=both sid='*';

System altered.

SQL> show parameter log_archive_for

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      %t_%s_%r.dbf

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

aix1:/u01/app/oracle/product/19.3/db/dbs$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 16:32:33 2020
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3221222936 bytes
Fixed Size                  9303576 bytes
Variable Size             956301312 bytes
Database Buffers         2231369728 bytes
Redo Buffers               24248320 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HXSY1                          MOUNTED

SQL> alter pluggable database hxsy1 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HXSY1                          READ WRITE NO

这里主库已经启用了归档

启用force logging

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

给备库创建密码文件
这里通过复制主库的密码文件来创建备库密码文件

openaix1:/u01/app/oracle/product/19.3/db/dbs$scp oracle@aix1:/u01/app/oracle/product/19.3/db/dbs/orapwhxsy /u01/app/oracle/product/19.3/db/dbs/
oracle@aix1's password: 
orapwhxsy                                                                                                                                                                                                 100% 2048   199.4KB/s   00:00    
openaix1:/u01/app/oracle/product/19.3/db/dbs$ls -lrt
total 16
-rw-r--r--    1 oracle   oinstall       3079 May 16 2015  init.ora
-rw-r-----    1 oracle   oinstall       2048 Nov 17 16:40 orapwhxsy

给备库创建参数文件
使用主库的参数文件进行创建

SQL> create pfile from spfile;

File created.



aix1:/u01/app/oracle/product/19.3/db/dbs$cat inithxsy.ora

*.audit_file_dest='/u01/app/oracle/admin/hxsy/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/HXSY_DG/control01.ctl','/u01/app/oracle/oradata/HXSY_DG/control02.ctl'
*.db_block_size=8192
*.db_name='hxsy'
*.db_unique_name='hxsy_dg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hxsyXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_HXSY'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=hxsy_dg'
*.log_archive_dest_2='service=hxsy LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=hxsy'
*.log_archive_config='DG_CONFIG=(hxsy,hxsy_dg)'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1024m
*.processes=2560
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3072m
*.undo_tablespace='UNDOTBS1'
--备库以备库角色运行时需要设置的参数
*.db_file_name_convert='/u01/app/oracle/oradata/HXSY','/u01/app/oracle/oradata/HXSY_DG'
*.fal_client='hxsy_dg'
*.fal_server='hxsy'
*.log_file_name_convert='/u01/app/oracle/oradata/HXSY','/u01/app/oracle/oradata/HXSY_DG'
*.standby_file_management='auto'

在备库主机上创建参数文件

openaix1:/u01/app/oracle/product/19.3/db/dbs$vi inithxsy.ora
"inithxsy.ora" [New file] 
*.audit_file_dest='/u01/app/oracle/admin/hxsy/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/HXSY_DG/control01.ctl','/u01/app/oracle/oradata/HXSY_DG/control02.ctl'
*.db_block_size=8192
*.db_name='hxsy'
*.db_unique_name='hxsy_dg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hxsyXDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_HXSY'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=hxsy_dg'
*.log_archive_dest_2='service=hxsy LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=hxsy'
*.log_archive_config='DG_CONFIG=(hxsy,hxsy_dg)'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1024m
*.processes=2560
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3072m
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='/u01/app/oracle/oradata/HXSY','/u01/app/oracle/oradata/HXSY_DG'
*.fal_client='hxsy_dg'
*.fal_server='hxsy'
*.log_file_name_convert='/u01/app/oracle/oradata/HXSY','/u01/app/oracle/oradata/HXSY_DG'
*.standby_file_management='auto'

为主库和备库配置监听
主库

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = hxsy)
      (ORACLE_HOME =/u01/app/oracle/product/19.3/db)
      (GLOBAL_DBNAME=hxsy)
    )
  )

备库

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = hxsy)
      (ORACLE_HOME =/u01/app/oracle/product/19.3/db)
      (GLOBAL_DBNAME=hxsy_dg)
    )
  )

重启主库和备库的监听
主库:

aix1:/u01/app/oracle/product/19.3/db/network/admin$lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production on 16-NOV-2020 16:58:45

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=aix1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production
Start Date                16-NOV-2020 16:58:06
Uptime                    0 days 0 hr. 0 min. 39 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/aix1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aix1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "hxsy" has 1 instance(s).
  Instance "hxsy", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

备库:

openaix1:/home/oracle$lsnrctl start

LSNRCTL for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production on 17-NOV-2020 17:01:03

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/19.3/db/bin/tnslsnr: please wait...

TNSLSNR for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production
System parameter file is /u01/app/oracle/product/19.3/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/openaix1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=openaix1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=openaix1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production
Start Date                17-NOV-2020 17:01:04
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/openaix1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=openaix1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "hxsy_dg" has 1 instance(s).
  Instance "hxsy", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

为主库和备库创建Oracle Net服务名
主库:

HXSY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.24.117)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hxsy)
    )
  )


HXSY_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.24.116)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = hxsy_dg)
      (UR=A)
    )
  )

备库:
使用备份创建备库

openaix1:/u01/app/oracle/oradata$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 17:07:09 2020
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3221222936 bytes
Fixed Size                  9242136 bytes
Variable Size             754974720 bytes
Database Buffers         2449473536 bytes
Redo Buffers                7532544 bytes

aix1:/u01/app/oracle/product/19.3/db/dbs$rman target sys/hxsy_123456@hxsy auxiliary sys/hxsy_123456@hxsy_dg

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Nov 16 17:23:03 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: HXSY (DBID=1728588152)
connected to auxiliary database: HXSY (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 2020-11-16 17:23:12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2420 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/19.3/db/dbs/orapwhxsy'   ;
}
executing Memory Script

Starting backup at 2020-11-16 17:23:14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2908 device type=DISK
Finished backup at 2020-11-16 17:23:16

contents of Memory Script:
{
   restore clone from service  'hxsy' standby controlfile;
}
executing Memory Script

Starting restore at 2020-11-16 17:23:16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output file name=/u01/app/oracle/oradata/HXSY_DG/control01.ctl
output file name=/u01/app/oracle/oradata/HXSY_DG/control02.ctl
Finished restore at 2020-11-16 17:23:22

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/HXSY_DG/temp01.dbf";
   set newname for tempfile  2 to 
 "/u01/app/oracle/oradata/HXSY_DG/pdbseed/temp012020-11-17_00-11-36-085-AM.dbf";
   set newname for tempfile  3 to 
 "/u01/app/oracle/oradata/HXSY_DG/hxsy1/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/HXSY_DG/system01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/HXSY_DG/sysaux01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/HXSY_DG/undotbs01.dbf";
   set newname for datafile  5 to 
 "/u01/app/oracle/oradata/HXSY_DG/pdbseed/system01.dbf";
   set newname for datafile  6 to 
 "/u01/app/oracle/oradata/HXSY_DG/pdbseed/sysaux01.dbf";
   set newname for datafile  7 to 
 "/u01/app/oracle/oradata/HXSY_DG/users01.dbf";
   set newname for datafile  8 to 
 "/u01/app/oracle/oradata/HXSY_DG/pdbseed/undotbs01.dbf";
   set newname for datafile  9 to 
 "/u01/app/oracle/oradata/HXSY_DG/hxsy1/system01.dbf";
   set newname for datafile  10 to 
 "/u01/app/oracle/oradata/HXSY_DG/hxsy1/sysaux01.dbf";
   set newname for datafile  11 to 
 "/u01/app/oracle/oradata/HXSY_DG/hxsy1/undotbs01.dbf";
   set newname for datafile  12 to 
 "/u01/app/oracle/oradata/HXSY_DG/hxsy1/users01.dbf";
   restore
   from  nonsparse   from service 
 'hxsy'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/HXSY_DG/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/HXSY_DG/pdbseed/temp012020-11-17_00-11-36-085-AM.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/HXSY_DG/hxsy1/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2020-11-16 17:23:32
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/HXSY_DG/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/HXSY_DG/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/HXSY_DG/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/HXSY_DG/pdbseed/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/HXSY_DG/pdbseed/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/HXSY_DG/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/HXSY_DG/pdbseed/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/HXSY_DG/hxsy1/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/HXSY_DG/hxsy1/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/HXSY_DG/hxsy1/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service hxsy
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/HXSY_DG/hxsy1/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 2020-11-16 17:25:31

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1056734739 file name=/u01/app/oracle/oradata/HXSY_DG/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1056734739 file name=/u01/app/oracle/oradata/HXSY_DG/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/hxsy1/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/hxsy1/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/hxsy1/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=1056734740 file name=/u01/app/oracle/oradata/HXSY_DG/hxsy1/users01.dbf
Finished Duplicate Db at 2020-11-16 17:25:45

openaix1:/u01/app/oracle/oradata/HXSY_DG$sqlplus / as sysdba 

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 17 17:26:54 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
hxsy             MOUNTED

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      hxsy_dg

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/HXSY_DG/system01.dbf
/u01/app/oracle/oradata/HXSY_DG/sysaux01.dbf
/u01/app/oracle/oradata/HXSY_DG/undotbs01.dbf
/u01/app/oracle/oradata/HXSY_DG/pdbseed/system01.dbf
/u01/app/oracle/oradata/HXSY_DG/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/HXSY_DG/users01.dbf
/u01/app/oracle/oradata/HXSY_DG/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/HXSY_DG/hxsy1/system01.dbf
/u01/app/oracle/oradata/HXSY_DG/hxsy1/sysaux01.dbf
/u01/app/oracle/oradata/HXSY_DG/hxsy1/undotbs01.dbf
/u01/app/oracle/oradata/HXSY_DG/hxsy1/users01.dbf

11 rows selected.

对物理备库创建备重做日志文件
查询主库的联机重做日志文件，备重做日志文件的大小应该与主库联机重做日志文件的大小相同，备重做日志文件组的数量应该比主库联机重做日志文件组多一组，计算公式为

(maximum # of logfiles +1) * maximum # of threads 

aix1:/u01/app/oracle/product/19.3/db/dbs$sqlplus sys/hxsy_123456@hxsy as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 17:28:21 2020
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>  select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/HXSY/redo03.log
/u01/app/oracle/oradata/HXSY/redo02.log
/u01/app/oracle/oradata/HXSY/redo01.log


SQL> select group#,thread#,bytes/1024/1024 from v$log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1             200
         2          1             200
         3          1             200



SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/HXSY_DG/redo03.log
/u01/app/oracle/oradata/HXSY_DG/redo02.log
/u01/app/oracle/oradata/HXSY_DG/redo01.log

SQL> select group#,thread#,bytes/1024/1024 from v$log;

    GROUP#    THREAD# BYTES/1024/1024
---------- ---------- ---------------
         1          1             200
         3          1             200
         2          1             200

备库:

SQL> alter database add standby logfile thread 1 group 4('/u01/app/oracle/oradata/HXSY_DG/redo04.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 5('/u01/app/oracle/oradata/HXSY_DG/redo05.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6('/u01/app/oracle/oradata/HXSY_DG/redo06.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7('/u01/app/oracle/oradata/HXSY_DG/redo07.log') size 200M;

Database altered.

主库:

SQL> alter database add standby logfile thread 1 group 4('/u01/app/oracle/oradata/HXSY/redo04.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 5('/u01/app/oracle/oradata/HXSY/redo05.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 6('/u01/app/oracle/oradata/HXSY/redo06.log') size 200M;

Database altered.

SQL> alter database add standby logfile thread 1 group 7('/u01/app/oracle/oradata/HXSY/redo07.log') size 200M;

Database altered.

设置主库相关初始化参数

log_archive_config='DG_CONFIG=(hxsy,hxsy_dg)'
log_archive_dest_1='LOCATION=/u01/app/oracle/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=hxsy'
log_archive_dest_2='SERVICE=hxsy_dg LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=hxsy_dg'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
remote_login_passwordfile=exclusive
log_archive_format= %t_%s_%r.dbf
log_archive_max_processes=30

主库以备库角色运行时需要额外设置的参数。这些参数当主库被转换为备库角色运行时生效:

fal_server='hxsy_dg'
fal_client='hxsy'
db_file_name_convert='/u01/app/oracle/oradata/HXSY_DG', '/u01/app/oracle/oradata/HXSY'
log_file_name_convert='/u01/app/oracle/oradata/HXSY_DG', '/u01/app/oracle/oradata/HXSY'
standby_file_management='auto'

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=hxsy' scope=both sid='*';

System altered.

SQL> alter system set log_archive_dest_2='SERVICE=hxsy_dg LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=hxsy_dg'scope=both sid='*';

System altered.

SQL> alter system set log_archive_dest_state_1=enable scope=both sid='*';

System altered.

SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*';

System altered.


SQL> alter system set log_archive_max_processes=30 scope=both sid='*';

System altered.

SQL> alter system set fal_server='hxsy_dg' scope=both sid='*';

System altered.

SQL> alter system set fal_client='hxsy' scope=both sid='*';

System altered.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/HXSY_DG', '/u01/app/oracle/oradata/HXSY' scope=spfile sid='*';

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/HXSY_DG', '/u01/app/oracle/oradata/HXSY' scope=spfile sid='*';

System altered.

SQL> alter system set standby_file_management='auto' scope=both sid='*';

System altered.


SQL> alter system set log_archive_config='DG_CONFIG=(hxsy,hxsy_dg)' scope=both sid='*';

System altered.



SQL> select name, database_role, open_mode from gv$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
HXSY      PRIMARY          READ WRITE


SQL> select name, database_role, open_mode from gv$database;

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
HXSY      PHYSICAL STANDBY READ ONLY

使用alter database语句来启用实时应用功能:
.对于物理备库执行alter database recover managed standby database(在Oracle 12.1中需要指定current logfile子句来启用实时应用，但在12.2中不再需要)。

在备库节点上执行实时重做应用

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

Database altered.

alert日志信息如下

# tail -f /u01/app/oracle/diag/rdbms/hxsy_dg/hxsy/trace/alert_hxsy.log
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/u01/app/oracle/oradata/HXSY_DG/pdbseed/temp012020-11-17_00-11-36-085-AM.dbf'
2020-11-17T17:47:58.239174-06:00
File 202 not verified due to error ORA-01157
2020-11-17T17:47:58.390428-06:00
PDB$SEED(2):Re-creating tempfile /u01/app/oracle/oradata/HXSY_DG/pdbseed/temp012020-11-17_00-11-36-085-AM.dbf
2020-11-17T17:47:59.013123-06:00
PDB$SEED(2):Opening pdb with no Resource Manager plan active
Physical standby database opened for read only access.
Completed: alter database open
2020-11-17T17:50:46.966690-06:00
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
alter database recover managed standby database using current logfile disconnect from session
2020-11-17T17:50:47.069179-06:00
Attempt to start background Managed Standby Recovery process (hxsy)
Starting background process MRP0
2020-11-17T17:50:47.296690-06:00
MRP0 started with pid=67, OS id=10682718 
2020-11-17T17:50:47.329349-06:00
Background Managed Standby Recovery process started (hxsy)
2020-11-17T17:50:52.560871-06:00
 Started logmerger process
2020-11-17T17:50:52.734970-06:00

IM on ADG: Start of Empty Journal 

IM on ADG: End of Empty Journal 
PR00 (PID:12190072): Managed Standby Recovery starting Real Time Apply
max_pdb is 3
2020-11-17T17:50:54.165887-06:00
Parallel Media Recovery started with 8 slaves
2020-11-17T17:50:54.389175-06:00
stopping change tracking
2020-11-17T17:50:54.609199-06:00
PR00 (PID:12190072): Media Recovery Log /u01/app/oracle/arch/1_7_1056672314.dbf
2020-11-17T17:50:54.799176-06:00
PR00 (PID:12190072): Media Recovery Log /u01/app/oracle/arch/1_8_1056672314.dbf
2020-11-17T17:50:54.877923-06:00
TT02 (PID:12779914): Waiting for all non-current ORLs to be archived
2020-11-17T17:50:54.987923-06:00
TT02 (PID:12779914): All non-current ORLs have been archived
2020-11-17T17:50:55.311006-06:00
Completed: alter database recover managed standby database using current logfile disconnect from session
2020-11-17T17:50:55.335874-06:00
PR00 (PID:12190072): Media Recovery Waiting for T-1.S-9 (in transit)
2020-11-17T17:50:55.376975-06:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 9 Reading mem 0
2020-11-17T17:50:55.412407-06:00
  Mem# 0: /u01/app/oracle/oradata/HXSY_DG/redo04.log

在主库创建表空间test

SQL> create tablespace test datafile '/u01/app/oracle/oradata/HXSY/test01.dbf' size 50M;

Tablespace created.


SQL>  select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST

6 rows selected.

SQL> select con_id,ts#,name from v$tablespace;

    CON_ID        TS# NAME
---------- ---------- ------------------------------
         1          1 SYSAUX
         1          0 SYSTEM
         1          2 UNDOTBS1
         1          4 USERS
         1          3 TEMP
         2          0 SYSTEM
         2          1 SYSAUX
         2          2 UNDOTBS1
         2          3 TEMP
         3          0 SYSTEM
         3          1 SYSAUX

    CON_ID        TS# NAME
---------- ---------- ------------------------------
         3          2 UNDOTBS1
         3          3 TEMP
         3          5 USERS
         1          6 TEST

15 rows selected.

SQL> select ts#,name from v$datafile where ts#=6 and con_id=1;

       TS# NAME
---------- --------------------------------------------------
         6 /u01/app/oracle/oradata/HXSY/test01.dbf

备库alert日志如下:

Recovery of Online Redo Log: Thread 1 Group 4 Seq 9 Reading mem 0
2020-11-17T17:50:55.412407-06:00
  Mem# 0: /u01/app/oracle/oradata/HXSY_DG/redo04.log
2020-11-17T17:52:48.412685-06:00
Recovery created file /u01/app/oracle/oradata/HXSY_DG/test01.dbf
2020-11-17T17:52:48.453155-06:00
Errors in file /u01/app/oracle/diag/rdbms/hxsy_dg/hxsy/trace/hxsy_dbw0_7602670.trc:
ORA-01157: cannot identify/lock data file 203 - see DBWR trace file
ORA-01110: data file 203: '/u01/app/oracle/oradata/HXSY_DG/hxsy1/temp01.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 7
Successfully added datafile 13 to media recovery
Datafile #13: '/u01/app/oracle/oradata/HXSY_DG/test01.dbf'
2020-11-17T17:53:13.126690-06:00
Control autobackup written to DISK device

备库:

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST

6 rows selected.

SQL> select con_id,ts#,name from v$tablespace;

    CON_ID        TS# NAME
---------- ---------- ------------------------------
         1          1 SYSAUX
         1          0 SYSTEM
         1          2 UNDOTBS1
         1          4 USERS
         1          3 TEMP
         2          0 SYSTEM
         2          1 SYSAUX
         2          2 UNDOTBS1
         2          3 TEMP
         3          0 SYSTEM
         3          1 SYSAUX

    CON_ID        TS# NAME
---------- ---------- ------------------------------
         3          2 UNDOTBS1
         3          3 TEMP
         3          5 USERS
         1          6 TEST

15 rows selected.

SQL> select ts#,name from v$datafile where ts#=6 and con_id=1;

       TS# NAME
---------- --------------------------------------------------
         6 /u01/app/oracle/oradata/HXSY_DG/test01.dbf

验证
主库:

SQL> create table t1 as select * from dba_users;

Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
        36


SQL> create table t2(id number(20),name varchar2(20));

Table created.

SQL> insert into t2 values(1,'jy');

1 row created.

SQL> commit;

Commit complete.

SQL> update t2 set name='hy' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete from t2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> create table t3(id number(20),name varchar2(20)) tablespace test;

Table created.

SQL> insert into t3 values(1,'jy');

1 row created.

SQL> commit;

Commit complete.

备库

SQL> select count(*) from t1;

  COUNT(*)
----------
        36

SQL> desc t2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(20)
 NAME                                               VARCHAR2(20)

SQL> select * from t2;

        ID NAME
---------- --------------------------------------------------
         1 jy

SQL> select * from t2;

        ID NAME
---------- --------------------------------------------------
         1 hy

SQL> select * from t2;

no rows selected

SQL> 
SQL> 
SQL> desc t3;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(20)
 NAME                                               VARCHAR2(20)

SQL> select * from t3;

        ID NAME
---------- --------------------------------------------------
         1 jy

验证物理备库是否执行正确
在创建物理备库后并且设置重做传输服务，可能想要验证主库的数据库修改是否会成功的传输到备库。对于备库可以查询v$managed_standby视图来验证重做是否被从主库传输到备库并应用。

SQL> select client_process,process,thread#,sequence#,status from v$managed_standby where client_process='LGWR' or process='MRP0';

CLIENT_P PROCESS      THREAD#  SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR     RFS                1          9 IDLE
N/A      MRP0               1          9 APPLYING_LOG

上面的查询对于使用CLIENT_PROCESS为LGWR的主库会显示一行记录，它指示重做传输工作正常并且主重做线程将会被发送到备库。 如果主库是RAC数据库，那么对于使用CLIENT_PROCESS为LGWR的当前活动的每个主库实例都会显示一行记录。

上面的查询对于MRP也行显示一行。如果MRP的状态显示为APPLYING_LOG并且SEQUENCE#等于主库当前正被发送的日志序列号，那么备库已经解决了所有的日志差异并且当前处于实时应用日志模式。

查询主库当前正被发送日志的序列号为9与上面的MRP进程所显示的sequence#(9)相同

SQL> select group#,thread#,sequence#,status from v$log;

    GROUP#    THREAD#  SEQUENCE# STATUS
---------- ---------- ---------- ----------------
         1          1          7 INACTIVE
         2          1          8 INACTIVE
         3          1          9 CURRENT

注意MRP进程可能显示的sequence#比主库当前被发送的日志序列号小，那么这就表示正在应用的归档重做日志文件与发送的日志文件之间存在差异并且它并没有赶上。一旦所有差异被解决，相同的查询将显示MRP正在应用当前sequence#。

在openPower服务器上安装Oracle 19C
1.创建用户与组
1.1.创建组(在各个主机上执行)

mkgroup -'A' id='501' adms='root' oinstall
mkgroup -'A' id='502' adms='root' asmdba
mkgroup -'A' id='503' adms='root' asmoper
mkgroup -'A' id='504' adms='root' dba
mkgroup -'A' id='505' adms='root' oper
mkgroup -'A' id='506' adms='root' backdba
mkgroup -'A' id='507' adms='root' dgdba
mkgroup -'A' id='508' adms='root' racdba
mkgroup -'A' id='509' adms='root' kmdba

1.2.创建用户(在各个主机上执行)

mkuser id=601 pgrp=oinstall groups=dba,asmdba,oper,asmoper,backdba,dgdba,racdba,kmdba home=/home/oracle oracle

创建安装目录

mkdir -p /u01/app/oraInventory
mkdir -p /u01/app/oracle/product/19.3/db
mkdir -p /u01/temp

chown -R oracle:oinstall /u01
chmod -R 775 /u01

2.设置用户环境变量（用oracle用户来编辑.profile)

umask 022
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3/db
export ORACLE_SID=hxsy
export ORACLE_UNQNAME=hxsy
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
export PATH=.:${PATH}:$HOME/bin:$ORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}:$ORACLE_BASE/common/oracle/bin
export ORACLE_PATH=${PATH}:$ORACLE_BASE/common/oracle/sql:.:$ORACLE_HOME/rdbms/admin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/u01/tmp
export TMPDIR=/u01/tmp
export PS1=`hostname`:'$PWD'"$"

3.使用如下命令查看虚拟内存管理参数

vmo -L minperm%
vmo -L maxperm%
vmo -L maxclient%
vmo -L lru_file_repage
vmo -L strict_maxclient
vmo -L strict_maxperm

4.如果设置不合适，使用如下命令修改：

vmo -p -o minperm%=3
vmo -p -o maxperm%=90
vmo -p -o maxclient%=90
vmo -p -o lru_file_repage=0
vmo -p -o strict_maxclient=1
vmo -p -o strict_maxperm=0

5.检查网络参数设置
ephemeral参数：
5.1 使用命令no -a |fgrep ephemeral可以查看当前系统ephemeral参数设置，建议的参数设置如下

tcp_ephemeral_high = 65500
tcp_ephemeral_low = 9000
udp_ephemeral_high= 65500
udp_ephemeral_low = 9000

如果系统中参数设置和上述值不一样，使用命令修改：

#no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500
#no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500

使用如下命令修改网络可调整参数：

#no -p -o rfc1323=1
#no -r -o ipqmaxlen=512
#no -p -o ipqmaxlen=512
#no -p -o sb_max=4194304
#no -p -o tcp_recvspace=65536
#no -p -o tcp_sendspace=65536
#no -p -o udp_recvspace=1351680 该值是udp_sendspace的10倍，但须小于sb_max
#no -p -o udp_sendspace=135168

备注：-r表示reboot后生效，-p表示即刻生效.
检查内核参数maxuproc（建议16384）和ncargs（至少128）

#lsattr -E -l sys0 -a ncargs
#lsattr -E -l sys0 -a maxuproc

如果设置不合适使用如下命令修改：

#chdev -l sys0 -a ncargs=256
#chdev -l sys0 -a maxuproc=16384

6.解压数据库软件

aix1:/u01/soft$unzip -q AIX.PPC64_193000_db_home.zip -d $ORACLE_HOME

7.安装数据库软件
在$ORACLE_HOME/install/response目录中有一个安装rsp文件示例
7.1.配置响应文件

[oracle@ora19c ~]$ vi 19c_dbinstall.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.3/db
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=racdba
oracle.install.db.rootconfig.executeRootScript=true
oracle.install.db.rootconfig.configMethod=ROOT

8.执行安装

openaix1:/home/oracle$$ORACLE_HOME/runInstaller -silent  -force -noconfig  -ignorePrereq  -responseFile /home/oracle/19c_dbinstall.rsp

********************************************************************************

Your platform requires the root user to perform certain pre-installation
OS preparation.  The root user should run the shell script 'rootpre.sh' before
you proceed with Oracle installation. The rootpre.sh script can be found at:
/u01/app/oracle/product/19.3/db/clone/rootpre.sh

Answer 'y' if root has run 'rootpre.sh' so you can proceed with Oracle
installation.
Answer 'n' to abort installation and then ask root to run 'rootpre.sh'.

********************************************************************************

Has 'rootpre.sh' been run by root in this machine? [y/n] (n)
y
Launching Oracle Database Setup Wizard...


 Enter password for 'root' user:
[WARNING] [INS-13014] Target environment does not meet some optional requirements.
   CAUSE: Some of the optional prerequisites are not met. See logs for details. installActions2020-11-17_01-49-09AM.log
   ACTION: Identify the list of failed prerequisite checks from the log: installActions2020-11-17_01-49-09AM.log. Then either from the log file or from installation manual find the appropriate configuration to meet the prerequisites and fix it manually.
The response file for this session can be found at:
 /u01/app/oracle/product/19.3/db/install/response/db_2020-11-17_01-49-09AM.rsp

You can find the log of this install session at:
 /u01/tmp/InstallActions2020-11-17_01-49-09AM/installActions2020-11-17_01-49-09AM.log
Successfully Setup Software with warning(s).
Moved the install session logs to:
 /u01/app/oraInventory/logs/InstallActions2020-11-17_01-49-09AM

9.配置监听
创建配置监听的响应文件
在/u01/app/oracle/product/19.3/db/assistants/netca/目录有一个示例文件

aix1:/home/oracle$vi 19c_netca.rsp
"19c_netca.rsp" [New file]

[GENERAL]
RESPONSEFILE_VERSION="19.0"
CREATE_TYPE="CUSTOM"

[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE=""typical""
LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START=""LISTENER""
NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}
NSN_NUMBER=1
NSN_NAMES={"EXTPROC_CONNECTION_DATA"}
NSN_SERVICE={"PLSExtProc"}
NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}


aix1:/home/oracle$netca /silent /responsefile /home/oracle/19c_netca.rsp
UnsatisfiedLinkError exception loading native library: njni19
java.lang.UnsatisfiedLinkError: njni19 (Not found in java.library.path)
java.lang.UnsatisfiedLinkError: oracle/net/common/NetGetEnv.jniGetOracleHome()Ljava/lang/String;
        at oracle.net.common.NetGetEnv.getOracleHome(Unknown Source)
        at oracle.net.ca.NetCALogger.getOracleHome(NetCALogger.java:230)
        at oracle.net.ca.NetCALogger.initOracleParameters(NetCALogger.java:215)
        at oracle.net.ca.NetCALogger.initLogger(NetCALogger.java:130)
        at oracle.net.ca.NetCA.main(NetCA.java:459)

Error: oracle/net/common/NetGetEnv.jniGetOracleHome()Ljava/lang/String;
Oracle Net Services configuration failed.  The exit code is 1


# lsdev | grep iocp
iocp0      Defined         I/O Completion Ports
# smitty iocp

# lsdev | grep iocp
iocp0      Available       I/O Completion Ports


aix1:/home/oracle$$ORACLE_HOME/bin/relink all
writing relink log to: /u01/app/oracle/product/19.3/db/install/relinkActions2020-11-16_11-17-27PM.log

aix1:/home/oracle$netca /silent /responsefile /home/oracle/19c_netca.rsp

Parsing command line arguments:
    Parameter "silent" = true
    Parameter "responsefile" = /home/oracle/19c_netca.rsp
Done parsing command line arguments.
Oracle Net Services Configuration:
Profile configuration complete.
Oracle Net Listener Startup:
    Running Listener Control:
      /u01/app/oracle/product/19.3/db/bin/lsnrctl start LISTENER
    Listener Control complete.
    Listener started successfully.
Listener configuration complete.
Oracle Net Services configuration successful. The exit code is 0

aix1:/home/oracle$lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production on 16-NOV-2020 23:24:53

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=aix1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production
Start Date                16-NOV-2020 23:23:28
Uptime                    0 days 0 hr. 1 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/aix1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=aix1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

10.创建数据库
10.1 创建配置数据库的响应文件
在/u01/app/oracle/product/19.3/db/assistants/dbca/目录中有一个示例文件

[oracle@ora19c install]$ cat /u01/app/oracle/product/19.3/db/assistants/dbca/dbca.rsp


aix1:/home/oracle$vi 19c_dbca.rsp
automaticMemoryManagement=TRUE
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0
templateName=General_Purpose.dbc
gdbName=hxsy
sid=hxsy
databaseConfigType=SI
createAsContainerDatabase=TRUE
numberOfPDBs=1
pdbName=hxsy1
useLocalUndoForPDBs=TRUE
pdbAdminPassword=hxsy_123456
sysPassword=hxsy_123456
systemPassword=hxsy_123456
storageType=FS
characterSet=ZHS16GBK
nationalCharacterSet=AL16UTF16
listeners=LISTENER
sampleSchema=TRUE
totalMemory 2048
databaseType=MULTIPURPOSE
automaticMemoryManagement=FALSE
totalMemory=4096

11.创建数据库

aix1:/home/oracle$dbca -silent  -createDatabase -responseFile /home/oracle/19c_dbca.rsp
[WARNING] [DBT-06208] The 'SYS' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'SYSTEM' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
[WARNING] [DBT-06208] The 'PDBADMIN' password entered does not conform to the Oracle recommended standards.
   CAUSE:
a. Oracle recommends that the password entered should be at least 8 characters in length, contain at least 1 uppercase character, 1 lower case character and 1 digit [0-9].
b.The password entered is a keyword that Oracle does not recommend to be used as password
   ACTION: Specify a strong password. If required refer Oracle documentation for guidelines.
Prepare for db operation
8% complete
Copying database files
31% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
43% complete
46% complete
Completing Database Creation
51% complete
53% complete
54% complete
Creating Pluggable Databases
58% complete
77% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/hxsy.
Database Information:
Global Database Name:hxsy
System Identifier(SID):hxsy
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/hxsy/hxsy.log" for further details.

12.验证是否可以登录数据库

aix1:/u01/app/oracle/product/19.3/db/dbs$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 16 16:32:33 2020
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3221222936 bytes
Fixed Size                  9303576 bytes
Variable Size             956301312 bytes
Database Buffers         2231369728 bytes
Redo Buffers               24248320 bytes
Database mounted.

SQL> alter database open;

Database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HXSY1                          MOUNTED

SQL> alter pluggable database hxsy1 open;

Pluggable database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 HXSY1                          READ WRITE NO

管理带约束的B树索引
B树索引与主键和唯一键约束是分不开的。这是因为Oracle使用索引来强制执行主键和唯一键约束。若没有相关联的B树索引，将不能启用主键或唯一约束。

在创建主键或唯一键约束时，你可以选择让Oracle自动创建相应索引。在这种情况下，如果删除或禁用约束，Oracle也将自动删除相应的索引。

单独创建索引和约束也是可以的。当单独创建索引和约束时，允许删了或禁用约束，而不会自动删除相应的索引。如果有大量数据需要处理，你可能希望禁用约束，而不删除相应索引。

因为在定义外键约束时，Oracle不会自动创建索引，所以必须手动在与外键约束相关的列上创建索引。在大多数情况下，在外键列上创建B树索引是有益的，因为它有助于避免锁定问题，并提高通过主键和外键列连接父/子表的查询的性能。

1 在主键列上创建B树索引
主键约束保证在一列(或列的组合)中的值可用于唯一标识表内的记录。每个表只能有一个主键约束。主键约束不能包含空值。主键约束可以被看做是唯一(unique)约束和非空(NOT NULL)约束的结合。为每个表创建主键索引有如下几个很好的理由。
.强制执行了主键列在表内必须是唯一的这一业务需求。是的，在某些情况下，可能有一个不需要主键的表(比如日志表)，但在大多数情况下，主键对每个表都是必需的。
.主键中的许多列，在访问应用程序的查询的where子句中被频繁使用。这些列上的索引将会改善查询的性能。
.除非已定义父表主键或唯一键约束，否则Oracle将不允许创建子表的外键约束。因此，如果需要外键约束，就必须使用主键或唯一键约束。

对任何启用的主键，Oracle都需要一个与之对应的索引。有几种技术可用来创建主键约束及其对应的索引。
.首先创建表。然后在单独的alter table语句中添加主键约束。alter table语句同时创建了主键约束和索引。
.在create table 语句中内联(与列一起)或在单独的部分中指定主键约束。
.首先创建表，然后使用create index语句创建包含主键列的索引，最后使用alter table … add constraint语句添加主键约束。

1.1.使用alter table来创建主键约束和索引
下面介绍的这种技术是创建主键约束和相关联的索引最可取的方法。这种方法允许对表的创建与约束和索引的定义分别进行管理。如果你使用的应用程序包含数千个表，约束和索引，那么将创建表与建立相应的约束和索引分开，可以使用管理和诊断安装问题变得更容易。这不是死板的规定，相反，它是从诊断问题演变而来的一种偏好。

在这个例子中，表和主键约束是分别创建的。首先，创建表时没有定义任何约束。

SQL> create table cust2
  2  (
  3  cust_id number,
  4  first_name varchar2(200),
  5  last_name varchar2(200)
  6  ) tablespace reporting_data;

Table created.

然后添加主键约束

SQL> alter table cust2 add constraint cust2_pk primary key(cust_id) using index tablespace reporting_index;

Table altered.

此代码示例使用alter table … add constraint语句同时创建主键约束和唯一索引。约束和索引都被命名为CUSTS_PK。

1.2.使用create table创建主键约束和索引
另一种常见的方法是用create table语句创建主键约束和索引。可以内联(和列一起)直接指定一个约束。这种方法的优点是简单。如果在开发或测试环境中进行试验，这种方法是快速且有效的。但这种方法也有一个缺点，它不允许在多个列上定义主键。例如:

SQL> create table cust3
  2  (
  3  cust_id number primary key,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> insert into cust3 values(1,'jing','yong');

1 row created.

SQL> commit;

Commit complete.

SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST3');

SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
SYS_C0023486                                                                                                                     INDEX                       1    1048576

在这段代码中，Oracle创建了主键约束和相应的唯一索引。Oracle自动生成像SYS_C0023486这样的随机名称(约束和索引也被赋予了相同的名称)。

如果想要明确地对约束和索引提供名称，就可以执行下面这样的语句:

SQL> create table cust4
  2  (
  3  cust_id number constraint cust4_pk primary key,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> insert into cust4 values(1,'jing','yong');

1 row created.

SQL> commit;

Commit complete.

SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST4');

SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
CUST4_PK                                                                                                                         INDEX                       1    1048576

也可以指定放置索引的表空间，如下所示:

SQL> create table cust5
  2  (
  3  cust_id number constraint cust5_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

还可以在create table语句单独的部分中定义主键约束(不和列在一起)。下面是在单独的部分定义主键约束的例子:

SQL> create table cust6
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  constraint cust6_pk primary key(cust_id) using index tablespace reporting_index
  7  )
  8  tablespace reporting_data;

Table created.

这种技术被称为外联(out-of-line)，因为约束的声明与列定义之间是用逗号隔开的。该做法与内联的方法相比，具有可以为主键指定多列的优势。

1.3.分别创建B-tree索引和主键约束
还可以首先创建索引，然后改变表以应用主键约束。为了保持这个例子的完整性，这里也显示了create table语句。

SQL> create table cust7
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> create unique index cust7_pk on cust7(cust_id) tablespace reporting_index;

Index created.

SQL> alter table cust7 add constraint cust7_pk primary key(cust_id);

Table altered.

这种方法的优点是，可以独立于索引删除或禁用主键约束。在大型数据库环境中，出于数据加载时性能方面的原因，有时可能想要删除或禁用约束。有时可能需要能删除约束，但不删除索引的灵活性。在大型数据库环境中，重建索引会花费很长的时间并消耗大量的系统资源。

另一种稍微有些牵强的情况是，有可能创建一个与主键约束定义的列不同的列的索引。例如:

SQL> create table cust8
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> create index cust8_pk on cust8(cust_id,first_name,last_name) tablespace reporting_index;

Index created.

SQL> alter table cust8 add constraint cust8_pk primary key(cust_id);

Table altered.

建议不要创建与约束列不同的列的主键索引，但这么做是可以的。你应该知道有这种情况，避免在诊断问题时感到困惑。

1.4.查看主键约束和索引的详细信息
用如下语句可以确认某个索引的详细信息:

SQL> select index_name,index_type,uniqueness from user_indexes where table_name='CUST7';

INDEX_NAME                                                                                                                       INDEX_TYPE                  UNIQUENES
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- ---------
CUST7_PK                                                                                                                         NORMAL                      UNIQUE

输出如上所示，为了验证约束的信息，可执行如下查询:

SQL> select constraint_name,constraint_type from user_constraints where table_name='CUST7';

CONSTRAINT_NAME                                                                                                                  CONSTRAINT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
CUST7_PK                                                                                                                         P

1.5.删除主键约束和索引
创建主键约束时自动创建的索引不能直接删除。在这种情况下，如果像下面这样直接删除索引:

SQL> drop index cust4_pk;

就会收到如下错误信息:

drop index cust4_pk
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

要删除索引，必须首先删除或禁用主键约束。例如，下面的语句将删除创建约束时自动创建的索引:

SQL> alter table cust4 disable constraint cust4_pk;

Table altered.
SQL> alter table cust4 drop constraint cust4_pk;

Table altered.

或者

SQL> alter table cust4 drop primary key;

Table altered.

在删除或禁用主键约束时，可以选择不删除相关索引。可以使用drop/disable constraint子句的keep index子句来保留索引。例如:

SQL> alter table cust4 drop constraint cust4_pk keep index;

Table altered.

此代码指示Oracle删除约束，但保留索引。如果要处理的是很大的表，那么出于载入或操纵数据时性能方面的原因，可能要禁用或删除约束，而非索引，因为删除与一个大表相关联的索引后，可能需要相当长的时间和大量资源来重新创建它。

要注意的另一个方面是，如果主键或唯一键被已启用的外键引用，而试图删除父表上的约束，如下所示:

SQL> alter table cust1 drop primary key;

就会收到如下错误信息:

alter table cust1 drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

在这种情况下，需要先删除或禁用引用的外键，或使用cascade子句在主键约束已经被删除或禁用时，自动删除外键约束。例如:

SQL>alter table cust1 drop constraint cust1_pk cascade;
SQL>alter table cust1 disable constraint cust1_pk cascade;
SQL>alter table cust1 drop primary key cascade;

级联删除约束只删除了所有依赖外键的约束，但不从子表中删除任何数据。

2 在唯一键列上创建B树索引
唯一键约束的主要目的是强制地不属于主键一部分的列的唯一性。如果有非主键列在一个表中是唯一的这种业务需求，那么应该使用唯一约束。例如，你可能有定义在客户表的cust_id列上的主键，但可能还需要一个last_name和first_name列的组合上的唯一键约束。

唯一键与主键有两方面的差别。首先，唯一键可以包含NULL值，其次每个表可以定义多个唯一键(而每个表只能定义一个主键)。

如果需要为某个列创建唯一约束，那么可以通过下列几种不同的方式来实现这一需求。
.使用alter table语句来创建一个唯一约束。这将自动创建一个唯一的B树索引。
.使用create table语句来创建一个唯一约束。这也将自动创建一个唯一的B树索引。
.分别创建B树索引和约束。如果想要在禁用或删除约束时分别管理索引和约束，那么可以使用这种方法。
.只创建唯一B树索引，而不费心去创建唯一键约束。如果索引中的列不能被子表的外键引用，那么可以使用这种方法。

2.1.使用alter table来创建唯一约束和索引
这种方法是我们启用唯一键约束并创建相应索引的首选方法。正如在主键约束和索引部分中提到的，将创建表的语句与创建约束和索引的语句分离，往往更容易诊断安装问题。

下面的示例演示如何创建一个表，然后在非主键列上添加一个唯一键约束。

SQL> create table cust9
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

接下来，使用alter table语句在cust表的last_name和first_name列的组合上创建一个名为cust9_ux1的唯一约束。

SQL> alter table cust9 add constraint cust9_uk1 unique(last_name,first_name) using index tablespace reporting_index;

Table altered.

此语句创建了该唯一约束。此外，Oracle会自动创建一个具有相同名称的相关索引。

2.2.使用create table创建唯一约束和索引
使用create table方法的优点在于，它简单且可以把索引和约束的创建封装在一个语句中。在用create table语句定义唯一约束时，它可以用内网联方式，也可以用外联方式来定义。

第一个例子显示了如何在一个列上内联地创建唯一键约束和索引。由于内联唯一键约束只可以定义在列上，所以我们添加了SSN列，它以内联内式定义了唯一键约束。

SQL> create table cust10
  2  (
  3  cust_id number constraint cust10_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  ssn varchar2(15) constraint cust10_uk1 unique using index tablespace reporting_index
  7  )
  8  tablespace reporting_data;

Table created.

下一个例子使用外联技术在first_name和last_name列的组合上创建了一个唯一约束:

SQL> create table cust11
  2  (
  3  cust_id number constraint cust11_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  ssn varchar2(15),
  7  constraint cust11_uk1 unique(first_name,last_name) using index tablespace reporting_index
  8  )
  9  tablespace reporting_data;

Table created.

外联方式的定义具有允许在多列上创建一个唯一键约束的优势。

2.3.分别创建B树索引和唯一键约束
如果需要分别管理索引和约束，那么可以先创建索引，然后再创建约束。例如:

SQL> create table cust12
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> create unique index cust12_uk1 on cust12(first_name,last_name) tablespace reporting_index;

Index created.

SQL> alter table cust12 add constraint cust12_uk1 unique(first_name,last_name);

Table altered.

分别创建索引和约束的好处是，可以在不删除底层索引的情况下删了或禁用约束。在处理大量数据时，可考虑使用这种方法。如果你有任何理由需要禁用约束，然后重新启用它，就可以这样做而不删除索引(因为重新创建大索引可能需要很长一段时间)。

2.4.只创建唯一索引
还可以只创建唯一索引而不添加唯一约束。如果你从来没有计划用外键引用一个唯一键，那么只创建一个唯一索引而不定义唯一约束也是可以的。下面是创建一个无关联约束的唯一索引的例子:

SQL> create table cust13
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;

Table created.

SQL> create unique index cust13_uk1 on cust13(first_name,last_name) tablespace reporting_index;

Index created.

在用上述语句明确地创建唯一索引时，Oracle虽然创建了一个唯一索引，但却没有在dba/all/user_constraints中为约束添加条目。为什么这很重要呢?，考虑这种情况:

SQL> insert into cust13 values(1,'JAMES','STARK');

1 row created.

SQL> insert into cust13 values(2,'JAMES','STARK');

下面是被抛出的相应错误消息提示:

insert into cust13 values(2,'JAMES','STARK')
*
ERROR at line 1:
ORA-00001: unique constraint (JY.CUST13_UK1) violated

如果让你来诊断这个问题的话，你首先会检查的地方是dba_constraints，你会按错误消息中显示的名称在其中查找一个约束。然而，没有查到任何信息。

SQL> select constraint_name from dba_constraints where constraint_name='CUST13_UK1';

no rows selected

没有相关记录可能会使用人困惑:向表中插入数据时，抛出的错误消息已经表明违反了唯一约束，但在与约束相关的数据字典视图中却没有它的信息。在这种情况下，必须在dba_indexes中查看已经创建的唯一索引的详细信息。例如:

SQL> select index_name,uniqueness from dba_indexes where index_name='CUST13_UK1';

INDEX_NAME                                         UNIQUENES
-------------------------------------------------- ---------
CUST13_UK1                                         UNIQUE

如果你希望能够使用与约束相关的数据字典视图来报告唯一键约束，就应该也定义一个约束。

2.5.删除唯一键约束和索引
如果索引是创建唯一键约束时自动创建的，那么不能直接删除该索引。在这种情况下，必须删除或禁用唯一键约束，而相关的索引会自动被删除。例如:

SQL> drop index cust11_uk1;
drop index cust11_uk1
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL>alter table cust11 drop constraint cust11_uk1;

这行语句同时删除约束和索引。如果想保留索引，那么可以指定keep index子句。

SQL>alter table cust drop constraint cust11_uk1 keep index;

如果分别创建索引和唯一键约束，或者如果没有唯一键约束与唯一索引相关联，那么可以直接删除该索引。

3 索引外键列
外键约束确保插入数据到子表时，相应的父表记录存在。这是一个保证数据符合父/子业务关系规则的机制。外键也被称为参照完整性约束。

不同于主键和唯一键约束，Oracle不会自动创建外键列上的索引。因此，必须在定义为外键约束的列的基础上手动创建一个外键索引。在大多数情况下，应该在与外键关联的列上创建索引。这里有两个原因。.Oracle经常可以利用外键列上的索引，来改善使用外键列来连接父表和子表的查询性能。.如果外键列上没有B树索引存在，在往子表插入数据或从子表删除数据时，它会锁定父表中的所有行。对于频繁修改父表和子表的应用程序，这将导致锁定和死锁问题。

首先讨论如何在一个外键列上创建B树索引，然后再介绍用来检测未被索引的外键列的一些技巧。

3.1.在外键列上实现索引
假设有这样的需求:必须为address1表的每条记录分配cust14表中存在的一个相应cust_id列。为了强制执行这种关系，在address1表上创建如下外键约束:

SQL> create table cust14(
  2  cust_id number,
  3  last_name varchar2(30),
  4  first_name varchar2(30)
  5  )
  6  tablespace reporting_data;

Table created.



SQL> create table address1(
  2  address_id number,
  3  cust_id number,
  4  street varchar2(30),
  5  city varchar2(30),
  6  state varchar2(30)
  7  )
  8  tablespace reporting_data;

Table created.


SQL> alter table address1 add constraint addr1_fk1 foreign key(cust_id) references cust14(cust_id);
alter table address1 add constraint addr1_fk1 foreign key(cust_id) references cust14(cust_id)
                                                                                     *
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

注意，外键列必须引用父表中定义了主键或唯一键约束的列。否则，就会收到错误提示信息”ORA-02270: 此列列表无匹配的唯一键或主键”。

因为在连接cust14表和address1表时，外键列被广泛使用，且外键列上的索引会提高性能，所以在这种情况下，必须手动创建一个索引。例如，在address1表的cust_id外键列上创建普通的B树索引。

SQL>create index addr1_fk1 on address1(cust_id);

索引名不必与外键名称相同。是否这样做，只是一种个人喜好。我们觉得约束和相应的索引具有相同的名称时，维护环境更容易。

创建索引时，如果不指定表空间名称，Oracle会在用户的默认表空间放置索引。一般情况下，最好明确指定该表的索引的存放位置。例如:

create index addr1_fk1 on address1(cust_id) tablespace reporting_index;

注意，外键列上的索引，并不一定是B树类型的。在数据仓库环境中，经常在星型模式的事实表的外键列上使用位图索引。与B树索引不同，外键列上的位图索引不能解决父/子表锁定的问题。使用星型模式的应用程序通常不从事实表删除或修改子记录，因此在数据仓库环境中，在外键列上使用位图索引，锁定不是什么问题。

3.2.确定外键列是否已经被索引
如果你从头开始创建一个应用程序，那么创建程序代码，并确保每一个外键约束都有相应的索引很容易。但是，如果你继承了一个现成的数据库，就需要审慎地检查外键列是否已经被索引。

你可以使用数据字典视图来验证，外键约束的所有列上是否有相应的索引。其基本思路是检查每个外键约束，看它是否有一个相应的索引，这个任务并不像一开始看上去那么简单。用下面的查询作为例子，它可以用来指导你按正确的途径入手:

SQL> col owner for a30
SQL> col cons_name for a30
SQL> col tab_name for a30
SQL> col cons_column for a30
SQL> col ind_column for a30
SQL> select distinct
  2  a.owner owner,
  3  a.constraint_name cons_name,
  4  a.table_name tab_name,
  5  b.column_name cons_column,
  6  nvl(c.column_name,'***Check index***') ind_column
  7  from dba_constraints a,dba_cons_columns b,dba_ind_columns c
  8  where a.constraint_type='R'
  9  and a.owner=UpPER('&&user_name')
 10  and a.owner=b.owner
 11  and a.constraint_name=b.constraint_name
 12  and b.column_name=c.column_name(+)
 13  and b.table_name=c.table_name(+)
 14  and b.position=c.column_position(+)
 15  order by tab_name,ind_column;
old   9: and a.owner=UpPER('&&user_name')
new   9: and a.owner=UpPER('JY')

OWNER                          CONS_NAME                      TAB_NAME                       CONS_COLUMN                    IND_COLUMN
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
JY                             ADDR_FK1                       ADDRESS                        CUST_ID                        CUST_ID

虽然此查询简单并易于理解，但它并不能在所有情况下都正确地报告出未索引的外键。例如，在多列外键的情况下，以不同于索引列的顺序定义约束也不要紧，只要索引列在该索引中位于前面即可。换句话说，如果约束被定义为col1和col2，那么定义一个先是col2,然后是col1的B树索引也没关系。

另一方面，使用B树索引有助于避免锁定的问题，但位图索引却做不到这点。在这种情况下，查询还应该检查索引类型。

在这些情况下，需要用更复杂的查询来检测与外键列相关的索引问题。下面的例子是一个更复杂的查询，它使用listagg分析函数来比较外键约束列(作为字符串返回一行)与相应的索引列:

这个查询会先提示你输入一个模式名称，然后将显示没有相应的索引的外键约束。此查询还检查了索引类型，位图索引可以在外键列上存在，但它不能防止锁定问题。

SQL> select
  2   case when ind.index_name is not null then
  3     case when ind.index_type in('BITMAP') then
  4        '** Bitmp idx **'
  5     else
  6        'indexed'
  7     end
  8   else
  9     '** Check idx **'
 10   end checker,
 11   ind.index_type,
 12   cons.owner,cons.table_name,ind.index_name,cons.constraint_name,cons.cols
 13   from( select
 14         c.owner,c.table_name,c.constraint_name,
 15         listagg(cc.column_name,',') within group (order by cc.column_name) cols
 16         from dba_constraints c,dba_cons_columns cc
 17         where c.owner=cc.owner
 18         and c.owner=UPPER('&&schema')
 19         and c.constraint_name=cc.constraint_name
 20         and c.constraint_type='R'
 21         group by c.owner,c.table_name,c.constraint_name) cons
 22  left outer join
 23  (select
 24    table_owner,table_name,index_name,index_type,cbr,listagg(column_name,',') within group (order by column_name) cols
 25    from(select
 26         ic.table_owner,ic.table_name,ic.index_name,ic.column_name,ic.column_position,i.index_type,
 27         connect_by_root(ic.column_name) cbr
 28         from dba_ind_columns ic,dba_indexes i
 29         where ic.table_owner=UPPER('&&schema')
 30         and ic.table_owner=i.table_owner
 31         and ic.table_name=i.table_name
 32         and ic.index_name=i.index_name
 33         connect by prior ic.column_position-1=ic.column_position
 34         and prior ic.index_name=ic.index_name)
 35  group by table_owner,table_name,index_name,index_type,cbr) ind
 36  on cons.cols=ind.cols
 37  and cons.table_name=ind.table_name
 38  and cons.owner=ind.table_owner
 39  order by checker,cons.owner,cons.table_name;
Enter value for schema: JY
old  18:        and c.owner=UPPER('&&schema')
new  18:        and c.owner=UPPER('JY')
old  29:        where ic.table_owner=UPPER('&&schema')
new  29:        where ic.table_owner=UPPER('JY')


CHECKER                        INDEX_TYPE                     OWNER                          TABLE_NAME                     INDEX_NAME                     CONSTRAINT_NAME                COLS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
** Check idx **                                               JY                             ADDRESS                                                       ADDR_FK1                       CUST_ID

表锁和外键
下面用一个简单的例子来演示外键列没有索引时的锁定问题。

首先创建两个表(dept和emp)并用一个外键约束把它们关联起来。

SQL> create table emp(emp_id number primary key,dept_id number);

Table created.

SQL> create table dept(dept_id number primary key);

Table created.

SQL> alter table emp add constraint emp_fk1 foreign key(dept_id) references dept(dept_id);

Table altered.

插入数据

SQL> insert into dept values(10);

1 row created.

SQL> insert into dept values(20);

1 row created.

SQL> insert into dept values(30);

1 row created.

SQL> insert into emp values(1,10);

1 row created.

SQL> insert into emp values(2,20);

1 row created.

SQL> insert into emp values(3,30);

1 row created.

SQL> commit;

Commit complete.

打开两个终端会话。在一个会话中，从子表删除一条记录，但不提交。

SQL> delete from emp where dept_id=10;

1 row deleted.

现在尝试(在另一个会话里)从父表中删除一些不受子表删除操作影响的数据。

SQL> delete from dept where dept_id=30;

对父表数据的删除操作会挂起，直到子表的事务初步提交(或回滚)。如果子表中的外键列上没有常规的B树索引，那么任何时间尝试往子表插入数据或删除子表的数据时，它都会在父表上放置一个全表锁，在子表的事务完成前，该全表锁会一直阻止删除或更新父表的数据。

回滚删除子表数据的操作

SQL> rollback;

Rollback complete.

当回滚删除子表数据的操作后，删除父表数据的操作报错，因为违反完整性约束

SQL> delete from dept where dept_id=30;
delete from dept where dept_id=30
*
ERROR at line 1:
ORA-02292: integrity constraint (JY.EMP_FK1) violated - child record found

现在额外创建一个子表外键列上的索引，并再次运行前面的操作。

SQL> create index emp_fk1 on emp(dept_id);

Index created.

再次执行删除操作，删除子表的数据

SQL> delete from emp where dept_id=10;

1 row deleted.

在另一个会话中删除父表数据不会挂起会立即报违反完整性约束

SQL> delete from dept where dept_id=30;
delete from dept where dept_id=30
*
ERROR at line 1:
ORA-02292: integrity constraint (JY.EMP_FK1) violated - child record found

这样就能够独立地运行前面列出的两个delete语句。当外键列上有B树索引时，如果从子表中删除数据，Oracle将不会过分地锁定父表中的所有行数据。