以前我有个想法，我只想对其中的某个或者某几个分区上创建索引，其他分区不想创建，在12C之前的版本，无论是Local还是Global index，都不能实现该需求，但是从ORACLE 12C开始引进了Partial Global/Local Indexes for Partitioned Tables，解决了该问题，可以在指定的分区上创建本地索引或者全局索引，主要语法是在表或者分区,子分区级别设置[INDEXING { ON | OFF }]
创建测试表

CDB_PDB@CHF> SELECT BANNER FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
CDB_PDB@CHF> CREATE TABLE xifenfei_orders (
  2  order_id NUMBER(12),
  3  order_address varchar2(100),
  4  order_mode VARCHAR2(20))
  5  INDEXING OFF
  6  PARTITION BY RANGE (order_id)
  7  (PARTITION ord_p1 VALUES LESS THAN (100) INDEXING ON,
  8  PARTITION ord_p2 VALUES LESS THAN (200) INDEXING OFF,
  9  PARTITION ord_p3 VALUES LESS THAN (300) INDEXING ON,
 10  PARTITION ord_p4 VALUES LESS THAN (400),
 11  PARTITION ord_p5 VALUES LESS THAN (500));
表已创建。

创建LOCAL INDEX

CDB_PDB@CHF> CREATE INDEX ind_lox on xifenfei_orders(order_address) LOCAL INDEXING PARTIAL;
索引已创建。
CDB_PDB@CHF> COL INDEX_NAME FOR A10
CDB_PDB@CHF> COL PARTITION_NAME FOR A15
CDB_PDB@CHF> select index_name, partition_name,STATUS
  2   from user_ind_partitions
  3   where index_name = 'IND_LOX';
INDEX_NAME PARTITION_NAME  STATUS
---------- --------------- --------
IND_LOX    ORD_P5          UNUSABLE
IND_LOX    ORD_P4          UNUSABLE
IND_LOX    ORD_P3          USABLE
IND_LOX    ORD_P2          UNUSABLE
IND_LOX    ORD_P1          USABLE
--设置INDEXING OFF对应的index 状态为UNUSABLE，分区继承表
CDB_PDB@CHF> select partition_name,indexing from user_tab_partitions where table_name='XIFENFEI_ORDERS';
PARTITION_NAME  INDE
--------------- ----
ORD_P5          OFF
ORD_P4          OFF
ORD_P3          ON
ORD_P2          OFF
ORD_P1          ON
--因为segment 延迟，无数据，所以无分区和索引记录
CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IND_LOX';
未选定行
CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='XIFENFEI_ORDERS';
未选定行
CDB_PDB@CHF> begin
  2    for i in 1 .. 449 loop
  3      insert into xifenfei_orders
  4      values
  5        (i,'www.xifenfei.com'||i,'惜分飞'||i);
  6    end loop;
  7  commit;
  8  end;
  9  /
PL/SQL 过程已成功完成。
--插入记录后，分区表有相关记录
CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME,blocks FROM USER_SEGMENTS WHERE SEGMENT_NAME='XIFENFEI_ORDERS';
PARTITION_NAME  SEGMENT_NAME        BLOCKS
--------------- --------------- ----------
ORD_P5          XIFENFEI_ORDERS       1024
ORD_P4          XIFENFEI_ORDERS       1024
ORD_P3          XIFENFEI_ORDERS       1024
ORD_P2          XIFENFEI_ORDERS       1024
ORD_P1          XIFENFEI_ORDERS       1024
--注意:这里只有user_tab_partitions.indexing为on的有记录,也就是说，至于这些分区的索引被创建，其他的未被创建
CDB_PDB@CHF> SELECT PARTITION_NAME,SEGMENT_NAME FROM USER_SEGMENTS WHERE SEGMENT_NAME='IND_LOX';
PARTITION_NAME  SEGMENT_NAME
--------------- ---------------
ORD_P1          IND_LOX
ORD_P3          IND_LOX

分析执行计划

CDB_PDB@CHF> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'XIFENFEI_ORDERS',CASCADE=>TRUE);
PL/SQL 过程已成功完成。
CDB_PDB@CHF> SET AUTOT TRACE
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS WHERE ORDER_ADDRESS='www.xifenfei.com99';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2800545636
------------------------------------------------------------------------------------------------------------------------
--------
| Id  | Operation                                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|
 Pstop |
------------------------------------------------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT                             |                 |     1 |    30 |    40   (0)| 00:00:01 |       |
       |
|   1 |  VIEW                                        | VW_TE_2         |     2 |    24 |    40   (0)| 00:00:01 |       |
       |
|   2 |   UNION-ALL                                  |                 |       |       |            |          |       |
       |
|   3 |    PARTITION RANGE OR                        |                 |     1 |    34 |     1   (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| XIFENFEI_ORDERS |     1 |    34 |     1   (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
|*  5 |      INDEX RANGE SCAN                        | IND_LOX         |     1 |       |     1   (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
|   6 |    PARTITION RANGE OR                        |                 |     1 |    34 |    39   (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
|*  7 |     TABLE ACCESS FULL                        | XIFENFEI_ORDERS |     1 |    34 |    39   (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
------------------------------------------------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("XIFENFEI_ORDERS"."ORDER_ID"<100 OR "XIFENFEI_ORDERS"."ORDER_ID">=200 AND
              "XIFENFEI_ORDERS"."ORDER_ID"<300)
   5 - access("ORDER_ADDRESS"='www.xifenfei.com99')
   7 - filter("ORDER_ADDRESS"='www.xifenfei.com99' AND ("XIFENFEI_ORDERS"."ORDER_ID">=300 AND
"XIFENFEI_ORDERS"."ORDER_ID"<500 OR "XIFENFEI_ORDERS"."ORDER_ID"<200 AND "XIFENFEI_ORDERS"."ORDER_ID">=100))
--这里可以看到，当我们没有指定分区范围的时候，显示的执行计划是有分区index的部分直接走index，没有分区index的部分是扫描分区
统计信息
----------------------------------------------------------
         34  recursive calls
          0  db block gets
        120  consistent gets
          1  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        533  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          0  rows processed
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com499' and order_id>200 and ord
er_id<300;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3337708912
------------------------------------------------------------------------------------------------------------------------
------
| Id  | Operation                                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| P
stop |
------------------------------------------------------------------------------------------------------------------------
------
|   0 | SELECT STATEMENT                           |                 |     1 |    34 |     2   (0)| 00:00:01 |       |
     |
|   1 |  PARTITION RANGE SINGLE                    |                 |     1 |    34 |     2   (0)| 00:00:01 |     3 |
   3 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| XIFENFEI_ORDERS |     1 |    34 |     2   (0)| 00:00:01 |     3 |
   3 |
|*  3 |    INDEX RANGE SCAN                        | IND_LOX         |     1 |       |     1   (0)| 00:00:01 |     3 |
   3 |
------------------------------------------------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORDER_ID">200)
   3 - access("ORDER_ADDRESS"='www.xifenfei.com499')
--指定分区查询，可以明确的看到，该sql直接使用了分区索引
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        533  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com499' and order_id>300 and ord
er_id<400;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2072227240
----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 |    34 |    14   (0)| 00:00:01 |       |    |
|   1 |  PARTITION RANGE SINGLE|                 |     1 |    34 |    14   (0)| 00:00:01 |     4 |     4 |
|*  2 |   TABLE ACCESS FULL    | XIFENFEI_ORDERS |     1 |    34 |    14   (0)| 00:00:01 |     4 |     4 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORDER_ADDRESS"='www.xifenfei.com499' AND "ORDER_ID">300)
--当指定的分区无index之时，直接判断走全表扫描
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        533  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

创建Global index

CDB_PDB@CHF> ALTER INDEX ind_lox INVISIBLE;
索引已更改。
CDB_PDB@CHF> CREATE INDEX IND_G_LOX ON XIFENFEI_ORDERS(ORDER_ADDRESS) Global  INDEXING PARTIAL;
索引已创建。
CDB_PDB@CHF> select index_name,indexing from dba_indexes where index_name='IND_G_LOX';
INDEX_NAME INDEXIN
---------- -------
IND_G_LOX  PARTIAL

执行计划

CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com99';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1912382893
------------------------------------------------------------------------------------------------------------------------
--------
| Id  | Operation                                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart|
 Pstop |
------------------------------------------------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT                             |                 |     1 |    30 |    41   (0)| 00:00:01 |       |
       |
|   1 |  VIEW                                        | VW_TE_2         |     2 |    24 |    41   (0)| 00:00:01 |       |
       |
|   2 |   UNION-ALL                                  |                 |       |       |            |          |       |
       |
|*  3 |    TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| XIFENFEI_ORDERS |     1 |    34 |     2   (0)| 00:00:01 | ROWID |
 ROWID |
|*  4 |     INDEX RANGE SCAN                         | IND_G_LOX       |     1 |       |     1   (0)| 00:00:01 |       |
       |
|   5 |    PARTITION RANGE OR                        |                 |     1 |    34 |    39   (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
|*  6 |     TABLE ACCESS FULL                        | XIFENFEI_ORDERS |     1 |    34 |    39   (0)| 00:00:01 |KEY(OR)|
KEY(OR)|
------------------------------------------------------------------------------------------------------------------------
--------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T"."ORDER_ID"<100 OR "T"."ORDER_ID">=200 AND "T"."ORDER_ID"<300)
   4 - access("ORDER_ADDRESS"='www.xifenfei.com99')
   6 - filter("ORDER_ADDRESS"='www.xifenfei.com99' AND ("T"."ORDER_ID">=300 AND "T"."ORDER_ID"<500 OR
              "T"."ORDER_ID"<200 AND "T"."ORDER_ID">=100))
--这里可以看到因为没有指定分区范围，该sql在含index的分区使用全局index，在没有index的分区直接使用全表扫描
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         67  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        533  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com99' and order_id<100;
执行计划
----------------------------------------------------------
Plan hash value: 3717359654
------------------------------------------------------------------------------------------------------------------------
------
| Id  | Operation                                  | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| P
stop |
------------------------------------------------------------------------------------------------------------------------
------
|   0 | SELECT STATEMENT                           |                 |     1 |    31 |     2   (0)| 00:00:01 |       |
     |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| XIFENFEI_ORDERS |     1 |    31 |     2   (0)| 00:00:01 |     1 |
   1 |
|*  2 |   INDEX RANGE SCAN                         | IND_G_LOX       |     1 |       |     1   (0)| 00:00:01 |       |
     |
------------------------------------------------------------------------------------------------------------------------
------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ORDER_ID"<100)
   2 - access("ORDER_ADDRESS"='www.xifenfei.com99')
--指定了分区范围，而且该分区又有index，直接使用全局index
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        551  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
CDB_PDB@CHF> SELECT ORDER_MODE FROM XIFENFEI_ORDERS t WHERE ORDER_ADDRESS='www.xifenfei.com99' and order_id>400
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 2072227240
----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 |    34 |    14   (0)| 00:00:01 |       |    |
|   1 |  PARTITION RANGE SINGLE|                 |     1 |    34 |    14   (0)| 00:00:01 |     5 |     5 |
|*  2 |   TABLE ACCESS FULL    | XIFENFEI_ORDERS |     1 |    34 |    14   (0)| 00:00:01 |     5 |     5 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ORDER_ADDRESS"='www.xifenfei.com99' AND "ORDER_ID">400)
--指定了分区范围，但是该分区无index，直接使用全表扫描
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        347  bytes sent via SQL*Net to client
        533  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

结论
通过测试，证明Partial Global/Local Indexes for Partitioned Tables确实能够实现对部分分区创建index。
1)如果查询条件确定的分区无index，那直接走全表扫描；
2)如果查询条件可以确定的分区范围内有index，会直接使用index(无论是Local还是GLobal)；
3)如果查询条件未确定分区范围(含index和无index分区情况)，那该sql会在有index分区使用index，在没有index区域走全表扫描

从ORACLE 12C开始允许在同一列上创建不同类型的index，用来做性能调优，不同类型的index主要包括（B*TREE vs BITMAP，Local vs Global)
ORACLE 11G

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> CREATE TABLE T_XIFENFEI
  2    (
  3    EMPNO      NUMBER(9)       PRIMARY KEY ,
  4    EMPNAME    VARCHAR2(15)    NOT NULL,
  5    )
  6    /
Table created.
SQL> CREATE INDEX EMP_DEPT_BTREE_IDX ON EMP(DEPTNO);
Index created.
SQL> CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE  ;
CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE
                                                 *
ERROR at line 1:
ORA-01408: such column list already indexed

ORACLE 12C

CDB_PDB@CHF> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production                                   0
NLSRTL Version 12.1.0.1.0 - Production                                                    0
CDB_PDB@CHF> CREATE TABLE T_XIFENFEI
  2    (
  3    EMPNO      NUMBER(9)       PRIMARY KEY ,
  4    EMPNAME    VARCHAR2(15)    NOT NULL
  5    )
  6    /
表已创建。
CDB_PDB@CHF> CREATE INDEX EMP_DEPT_BTREE_IDX ON EMP(DEPTNO);
索引已创建。
CDB_PDB@CHF> CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO);
CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO)
                                                 *
第 1 行出现错误:
ORA-01408: 此列列表已索引
CDB_PDB@CHF> CREATE BITMAP INDEX EMP_DEPTNO_BITMAP_IDX ON EMP(DEPTNO) INVISIBLE ;
索引已创建。

上篇文章(某个pdb可以在root pdb open状态下进行恢复)发布后,还是有不少朋友有各种争议,比如我的cdb本来就是open的,比如与oracle 12c 新特性文档描述不符等等,这里根据weibo上的各种争论和各位想的测试情况,我继续测试如下
查询数据库相关状态

C:\Users\XIFENFEI>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on 星期二 8月 13 12:49:47 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
SQL> select name from v$datafile where con_id=3;
NAME
--------------------------------------------------------------------------------
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF

删除pdb system01.dbf文件

SQL> host dir E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
 驱动器 E 中的卷没有标签。
 卷的序列号是 000C-3B41
 E:\APP\XIFENFEI\ORADATA\CDB\PDB 的目录
找不到文件

做checkpoint操作

--删除pdb的数据文件(本测试中是system01.dbf),因为dbwr无法写入数据到文件，导致crash，在实际中也有可能是ckpt,lgwr进程等
SQL> alter system checkpoint ;
alter system checkpoint
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结尾
进程 ID: 9316
会话 ID: 136 序列号: 3165

alert日志中写明是dbwr进程无法写文件导致整个cdb crash

Tue Aug 13 12:51:10 2013
Thread 1 advanced to log sequence 316 (LGWR switch)
  Current log# 4 seq# 316 mem# 0: E:\APP\XIFENFEI\ORADATA\CDB\REDO04.LOG
Tue Aug 13 12:51:12 2013
Archived Log entry 5 added for thread 1 sequence 315 ID 0x7377d8de dest 1:
Tue Aug 13 12:51:16 2013
KCF: read, write or open error, block=0x6ad1 online=1
        file=7 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF'
        error=27070 txt: 'OSD-04016: 异步 I/O 请求排队时出错。'
Tue Aug 13 12:51:16 2013
Errors in file E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_dbw0_8612.trc:
Tue Aug 13 12:51:16 2013
Errors in file E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_dbw0_8612.trc:
ORA-63999: 数据文件出现介质故障
ORA-01114: 将块写入文件 7 时出现 IO 错误 (块 # 27345)
ORA-01110: 数据文件 7: 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF'
ORA-27070: 异步读取/写入失败
OSD-04016: 异步 I/O 请求排队时出错。
USER (ospid: 8612): terminating the instance due to error 63999
Tue Aug 13 12:51:17 2013
System state dump requested by (instance=1, osid=8612 (DBW0)), summary=[abnormal instance termination].
System State dumped to trace file E:\APP\XIFENFEI\diag\rdbms\cdb\cdb\trace\cdb_diag_9720.trc
Dumping diagnostic data in directory=[cdmp_20130813125117], requested by (instance=1, osid=8612 (DBW0)), summary=[abnormal instance termination].
Tue Aug 13 12:51:29 2013
Instance terminated by USER, pid = 8612

open cdb数据库

SQL> conn / as sysdba
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area  521936896 bytes
Fixed Size                  2404552 bytes
Variable Size             293605176 bytes
Database Buffers          218103808 bytes
Redo Buffers                7823360 bytes
数据库装载完毕。
ORA-01157: 无法标识/锁定数据文件 7 - 请参阅 DBWR 跟踪文件
ORA-01110: 数据文件 7: 'E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF'
--直接在cdb中无法offline pdb数据文件
SQL> alter database datafile 7 offline;
alter database datafile 7 offline
*
第 1 行出现错误:
ORA-01516: 不存在的日志文件, 数据文件或临时文件 "7"
SQL> alter session set container=pdb;
会话已更改。
SQL> alter database datafile 7 offline;
数据库已更改。
SQL> conn / as sysdba
已连接。
SQL> alter database open;
数据库已更改。
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开

恢复并open pdb

C:\Users\XIFENFEI>rman target /
恢复管理器: Release 12.1.0.1.0 - Production on 星期二 8月 13 12:56:54 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
已连接到目标数据库: CDB (DBID=1937199326)
RMAN> restore datafile 7;
启动 restore 于 13-8月 -13
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=361 设备类型=DISK
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00007 还原到 E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
通道 ORA_DISK_1: 正在读取备份片段 D:\PDB_SYS_01OH54LF_1_1.RMAN
通道 ORA_DISK_1: 段句柄 = D:\PDB_SYS_01OH54LF_1_1.RMAN 标记 = TAG20130812T223943
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:00:16
完成 restore 于 13-8月 -13
RMAN> recover datafile 7;
启动 recover 于 13-8月 -13
使用通道 ORA_DISK_1
正在开始介质的恢复
线程 1 序列 314 的归档日志已作为文件 E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2013_08_12\O1_MF_1_314_90KXOZSF_.
ARC 存在于磁盘上
线程 1 序列 315 的归档日志已作为文件 E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2013_08_13\O1_MF_1_315_90MGSZ0X_.
ARC 存在于磁盘上
线程 1 序列 316 的归档日志已作为文件 E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2013_08_13\O1_MF_1_316_90MH1FGD_.
ARC 存在于磁盘上
归档日志文件名=E:\APP\XIFENFEI\FAST_RECOVERY_AREA\CDB\ARCHIVELOG\2013_08_12\O1_MF_1_314_90KXOZSF_.ARC 线程=1 序列=314
介质恢复完成, 用时: 00:00:02
完成 recover 于 13-8月 -13
RMAN> exit
恢复管理器完成。
C:\Users\XIFENFEI>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on 星期二 8月 13 12:58:42 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter session set container=pdb;
会话已更改。
SQL> alter database open;
alter database open
*
第 1 行出现错误:
ORA-01147: SYSTEM 表空间文件 7 处于脱机状态
SQL> alter database datafile 7 online;
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> conn / as sysdba
已连接。
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO

整个操作过程说明一下问题
1.当pdb丢失数据文件或者影响dbwr,lgwr,ckpt等后台进程工作之时,将导致整个cdb crash,因为这些进程都是公用的
2.如果pdb丢失数据文件,通过在pdb中offline该文件,可以正常open cdb和其他pdb,不会长时间影响其他pdb工作
3.在cdb open的情况下,可以恢复pdb的任何数据文件(特殊pdb除外,root,seed之类)

有时候,多么的希望ORACLE能够导出某个视图中的数据,然后通过这个视图来迁移需要的数据,现在ORACLE 12C通过expdp的views_as_tables来实现了该功能,把视图当作一个普通表从而导出数据,导入的时候直接和一个正常表一样,通过视图的导出,表的导入来实现相关需求
准备测试环境

SQL> SELECT * FROM V$VERSION;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2
SQL> create table t_lx as select * from v$log;
Table created.
SQL> create table t_xl as select * from v$logfile;
Table created.
SQL> create view v_xifenfei  as
  2   SELECT thread#,
  3           a.sequence#,
  4           a.group#,
  5           TO_CHAR (first_change#, '9999999999999999') "SCN",
  6           a.status,
  7           MEMBER
  8      FROM t_lx a, t_xl b
  9     WHERE a.group# = B.GROUP#
 10  ORDER BY a.sequence# DESC;
View created.
SQL> col member for a50
SQL> set lines 134
SQL> select * from v_xifenfei;
   THREAD#  SEQUENCE#     GROUP# SCN               STATUS           MEMBER
---------- ---------- ---------- ----------------- ---------------- -------------------------------------------
         1         30          3            391892 CURRENT          /u01/app/oracle/oradata/xifenfei/redo03.log
         1         29          2            377363 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo02.log
         1         28          1            374892 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo01.log

expdp结合VIEWS_AS_TABLES导出视图

[oracle@xifenfei ~]$ expdp xff/xifenfei views_as_tables=v_xifenfei directory=data_pump_dir dumpfile=xifenfei.dmp
Export: Release 12.1.0.0.2        on Sun Dec 16 07:56:48 2012
Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XFF"."SYS_EXPORT_TABLE_01":xff/******** views_as_tables=v_xifenfei directory=data_pump_dir
 dumpfile=xifenfei.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "XFF"."V_XIFENFEI"                          7.390 KB       3 rows
Master table "XFF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XFF.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/xifenfei/dpdump/xifenfei.dmp
Job "XFF"."SYS_EXPORT_TABLE_01" successfully completed at Sun Dec 16 07:58:17 2012 elapsed 0 00:00:56

impdp导入数据

[oracle@xifenfei ~]$ impdp xff/xifenfei remap_table=v_xifenfei:v_xff directory=data_pump_dir dumpfile=xifenfei.dmp
Import: Release 12.1.0.0.2        on Sun Dec 16 08:06:06 2012
Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "XFF"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XFF"."SYS_IMPORT_FULL_01":xff/******** remap_table=v_xifenfei:v_xff directory=data_pump_dir
 dumpfile=xifenfei.dmp
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "XFF"."V_XFF"                               7.390 KB       3 rows
Job "XFF"."SYS_IMPORT_FULL_01" successfully completed at Sun Dec 16 08:06:20 2012 elapsed 0 00:00:10

验证数据

SQL> COL OBJECT_NAME FOR A20
SQL> select object_type,object_name from user_objectS where object_name like 'V_X%';
OBJECT_TYPE             OBJECT_NAME
----------------------- --------------------
VIEW                    V_XIFENFEI
TABLE                   V_XFF
SQL> col member for a50
SQL> set lines 134
SQL> select * from v_XFF;
   THREAD#  SEQUENCE#     GROUP# SCN               STATUS           MEMBER
---------- ---------- ---------- ----------------- ---------------- --------------------------------------------
         1         30          3            391892 CURRENT          /u01/app/oracle/oradata/xifenfei/redo03.log
         1         29          2            377363 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo02.log
         1         28          1            374892 INACTIVE         /u01/app/oracle/oradata/xifenfei/redo01.log

通过测试证明在12C中ORACLE的expdp/impdp可以实现导出视图数据,进入导入到[其他库]其他表中