分类：oracle

使用In-Memory Column Store
从Oracle 12.1.0.2开始可以使用In-Memory Column Store。In-Memory Column Store对于SGA是一个可选部分用来存储表，表分区与其它数据库对象副本。在In-Memory Column Store中，数据是以列式被加载而不是像SGA中其它部分的数据是以行式被加载，并且数据被优化用于快速扫描。

In-Memory Column Store在SGA中是一个新的静态内存池。在In-Memory Column Store中所存储的数据不是使用传统行格式而是使用列式。每个列作为单独的结构进行存储。In-Memory Column Store不会替代buffer cache，是对buffer cache的一种补充，因此数据在内存中可以以行与列的形式进行存储。为了启用In-Memory Column Store，必须将inmemory_size参数设置为非零值。

可以在以下级别启用In-Memory Column Store
.列
.表
.物化视图
.表空间
.分区

如果在表空间级别启用In-Memory Column Store，那么所有存储在该表空间中的所有表与物化视图将抽默认启用In-Memory Column Store。可以将一个数据库对象的所有列或者将一个数据库对象的部分列加载到In-Memory Column Store中。类似地，对于分区表或物化视图，可以将所有分区或部分分区加载到In-Memory Column Store中。

在In-Memory Column Store中存储数据库对象可以显然提高对数据库对象执行以下类型操作的性能:
.查询扫描大量数据并且使用=,< ,>与in操作来进行过滤
.查询从表中或者从有大量列的物化视图中选择少量列，比如从有100列的表中选择5列
.查询对小表与大表进行关联
.查询将聚集数据

通常情况下，创建多列索引可以提高分析与报告查询的性能。这些索引可能影响DML语句的性能。当数据库对象被加载到In-Memory Column Store中，用于分析或报告查询的索引可以被减少或消除而不会影响查询的性能。消除这些索引可以提高事务和数据加载操作的性能。

可以通过对以下语句增加inmemory子句的方式来对数据库对象启用In-Memory Column Store:
.create table
.alter table
.create tablespace
.alter tablespace
.create materialized view
.alter materialized view

为了判断那个数据库对象被加载到In-Memory Column Store中，可以查询v$in_segments视图：

SQL> alter table jy inmemory;
Table altered

SQL> 
SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION   FROM V$IM_SEGMENTS;
OWNER                                                                            SEGMENT_NAME                                                                     INMEMORY_PRIORITY INMEMORY_COMPRESSION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------

SQL> 
SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION   FROM V$IM_SEGMENTS;
OWNER                                                                            SEGMENT_NAME                                                                     INMEMORY_PRIORITY INMEMORY_COMPRESSION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------

SQL> select count(*) from jy;
  COUNT(*)
----------
     72736

SQL> 
SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION  FROM V$IM_SEGMENTS;
OWNER                                                                            SEGMENT_NAME                                                                     INMEMORY_PRIORITY INMEMORY_COMPRESSION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------
JY                                                                               JY                                                                               NONE              FOR QUERY LOW

In-Memory Column Store不能对以下操作类型提高性能:
.有复杂谓词的查询
.选择大量列的查询
.返回大量数据行的查询
.使用大表联接的查询

SYS用户的对象并且存储在system或sysaux表空间中，那么这种对象不能被加载到In-Memory Column Store中。

In-Memory Column Store压缩方法
在In-Memory Column Store中，数据是可以被压缩的，并且SQL查询可以直接对压缩数据进行查询。In-Memory Column Store压缩方法有:
no memcompress 数据不会被压缩

memcompress for dml 这种方法只对DML操作的数据进行优化与压缩

memcompress for query low 这种方法将提供最佳的查询性能。这种压缩方法比memcompress for dml所压缩的数据要多但比
memcompress for query high压缩方法所压缩的数据要少。当在create或alter语句中指定inmemory子句但没指定压缩方法时或者当指定memcompress for query而没指定low或high时所使用的缺省值

memcompress for query high 这种方法会提供杰出的查询性能。这种压缩方法所压缩的数据量要比memcompress for query low方法多，但比memcompress for capacity low方法所压缩的数据量少

memcompress for capacity low 这种方法会提供很好的查询性能。这种压缩方法所压缩的数据量要比memcompress for query high方法所压缩的数据量多，但比memcompress for capacity high方法所压缩的数据量少。当指定memcompress for capacity而没有指定low或high时所使用的缺省值

memcompress for capacity high 这种方法提供的查询性能一般，但它的所压缩的数据量最多

SQL> alter table jy inmemory;
Table altered

SQL> 
SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION
  2  FROM V$IM_SEGMENTS;
OWNER                                                                            SEGMENT_NAME                                                                     INMEMORY_PRIORITY INMEMORY_COMPRESSION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------

SQL> 
SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION
  2  FROM V$IM_SEGMENTS;
OWNER                                                                            SEGMENT_NAME                                                                     INMEMORY_PRIORITY INMEMORY_COMPRESSION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------

SQL> select count(*) from jy;
  COUNT(*)
----------
     72736

SQL> 
SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION
  2  FROM V$IM_SEGMENTS;
OWNER                                                                            SEGMENT_NAME                                                                     INMEMORY_PRIORITY INMEMORY_COMPRESSION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------
JY                                                                               JY                                                                               NONE              FOR QUERY LOW

关键字memcompres必须要出现在inmemory后面

SQL> alter table jy  inmemory memcompress for query high;
Table altered

SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM V$IM_SEGMENTS;
OWNER                                                                            SEGMENT_NAME                                                                     INMEMORY_PRIORITY INMEMORY_COMPRESSION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------

SQL> select count(*) from jy;
  COUNT(*)
----------
     72736


SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM V$IM_SEGMENTS;
OWNER                                                                            SEGMENT_NAME                                                                     INMEMORY_PRIORITY INMEMORY_COMPRESSION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------
JY                                                                               JY                                                                               NONE              FOR QUERY HIGH

In-Memory Column Store Data Population Options
当对数据库对象雇用In-Memory Column Store时，可以让数据库来控制数据库对象何时被加载到In-Memory Column Store中，或者可以指定一个优先级别来决定加载队列中数据库对象的优先级。Oracle SQL包含一个inmemory priority子句来对于加载队列提供更多的控制。例如，它可以在加载其它数据库对象的数据之前将更重要或不重要的数据库对象的数据进行加载。

在In-Memory Column Store中对于加载数据库对象提供了以下优先级:
priority none Oracle数据库控制何时将数据库对象的数据加载到In-Memory Column Store中。对这个数据库对象进行扫描会触发将这个对象加载到In-Memory Column Store中。当priority在inmemory子句中没有指定时这是它的缺省级别。例如当对表jy修改In-Memory Column Store的压缩方法后，立即查询这个对象是否被加载到In-Memory Column Store中会发现并没有加载

SQL> alter table jy  inmemory memcompress for query low;
Table altered

SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM V$IM_SEGMENTS;
OWNER                                                                            SEGMENT_NAME                                                                     INMEMORY_PRIORITY INMEMORY_COMPRESSION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------

因为没有指定加载数据库对象的优先级，所以需要执行查询进行扫描来触发将其加载到In-Memory Column Store中

SQL> select count(*) from jy;
  COUNT(*)
----------
     72736

SQL> SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM V$IM_SEGMENTS;
OWNER                                                                            SEGMENT_NAME                                                                     INMEMORY_PRIORITY INMEMORY_COMPRESSION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------
JY                                                                               JY                                                                               NONE              FOR QUERY LOW

现在可以看到表jy已经被加载到In-Memory Column Store中了，可以看到inmemory_priority为none

priority low 在优先级为none的数据库对象之前将优先级为low的对象加载到In-Memory Column Store中，但在优先级为medium,high或critical的数据库对象被加载到In-Memory Column Store中之后才会将优先级为low的对象加载到In-Memory Column Store中

priority medium 在优先级为none或low的数据库对象之前将优先级为medium的对象加载到In-Memory Column Store中，但在优先级为high或critical的数据库对象被加载到In-Memory Column Store中之后才会将优先级为medium的对象加载到In-Memory ColumnStore中。

priority high 在优先级为none,low或medium的数据库对象之前将优先级为high的对象加载到In-Memory Column Store中，但在优先级为critical的数据库对象被加载到In-Memory Column Store中之后才会将优先级为medium的对象加载到In-Memory ColumnStore中。

priority critical 在优先级为none,low,medium或high的数据库对象之前将优先级为critical的对象加载到In-Memory Column Store中

当多个数据库对象设置了优先级而不为none时，Oracle数据库将对象的所有数据基于它们的优先级别进行排队来将它们加载到In-Memory Column Store中。优先为critical的对象将会最先被加载，接下来就是优先级为high的对象会被加载，依此类推。如果在In-Memory Column Store中没有足够的空间，那么额外要被加载的对象直到有足够空间之前是不会被加载的。当数据库被重启时，优先级不为none的对象会在数据库启动时被加载到In-Memory Column Store中。对于优先级不为none的数据库对象来说，alter table或alter materialized view DDL语句在DDL语句被记录在In-Memory Column Store之前不会返回执行结果。

下面的例子，创建表t1，在没有设置inmemory属性时，表t1是没有被加载到In-Memory Column Store中的

SQL> create table t1 as select * from jy;
Table created

SQL> select owner, segment_name, inmemory_priority, inmemory_compression from v$im_segments;
OWNER                                                                            SEGMENT_NAME                                                                     INMEMORY_PRIORITY INMEMORY_COMPRESSION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------
JY                                                                               JY                                                                               NONE              FOR QUERY LOW

修改表的inmemory属性，并且设置了加载优先级为high,数据压缩为memcompress for query high,在修改完成后，表t1就已经被加载到In-Memory Column Store中了

SQL> alter table t1 inmemory priority high memcompress for query high;
Table altered


SQL> select owner, segment_name, inmemory_priority, inmemory_compression from v$im_segments;
OWNER                                                                            SEGMENT_NAME                                                                     INMEMORY_PRIORITY INMEMORY_COMPRESSION
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------- --------------------
JY                                                                               JY                                                                               NONE              FOR QUERY LOW
JY                                                                               T1                                                                               HIGH              FOR QUERY HIGH

优先级的设置必须应用给整个表或一个表分区。对表中的不同列集合指定不同的In-Memory Column Store加载优先级别是不允许的。如果一个段在磁盘上的大小为64KB或者更小，那么这个对象不会被加载到In-Memory Column Store中。因此对于一些较小的对象虽然启用了In-Memory Column Store，但可能并不会被加载到In-Memory Column Store中。

In-Memory Column Store相关的初始化参数
inmemory_size:这个参数设置实例中的In-Memory Column Store的大小，它的缺省值为0，这意味着没有雇用In-Memory Column Store。为了雇用In-Memory Column Store这个参数必须设置为非0值。如果这个参数设置为非0值，那么它的最小值为100M。

在多租户环境中，在root容器中设置这个参数是对整个CDB生效的。这个参数也可以对每个PDB进行设置来限制每个PDB中的In-Memory Column Store的大小。PDB的In-Memory Column Store的总大小可以小于，等于或大于CDB的值。然而CDB所设置的In-Memory Column Store的大小是整个CDB中，包含root与所有PDB的In-Memory Column Store可以使用的最大内存，这意味着PDB可以CDB中所有可以使用的In-Memory Column Store的内存。

inmemory_force：这个参数可以对表与物化视图启用In-Memory Column Store或者对表与物化视图禁用In-Memory Column Store，设置这个参数为default,这是它的缺省值，它将允许通过单个对象的inmemory或no inmemory属性来决定是否将对象加载到In-Memory Column Store中。将这个参数设置为off来指定所有表与物化视图将禁用In-Memory Column Store.

inmemory_clause_default:这个参数能让你对新表与物化视图指定一个缺省的In-Memory Column Store子句。不设置这个参数或者设置为一个空字符串来指定对于新表与物化视图不存在缺省的In-Memory Column Store子句。将这个参数设置为no inmemory与它的缺省值(空字符串）有相同的作用。将这个参数设置为有效的inmemory子句来指定它为所有新表与物化视图的In-Memory Column Store的缺省值。这个子句可以包含In-Memory Column Store压缩方法与数据加载选项。如果子句以inmemory开头，那么所有新表与物化视图，包含那些没有inmemory子句的对象，将会被加载到In-Memory Column Store中。如果子句忽略了inmemory,那么它只会对在创建时指定了inmemory子句的新表与物化视图雇用In-Memory Column Store。

inmemory_query：这个参数指定是否允许in-memory查询。将这个参数设置为enable,它是缺省值，允许查询所访问的对象被加载到In-Memory Column Store中，将这个参数设置为disable来禁止将查询所访问的对象加载到In-Memory Column Store中。

inmemory_max_populate_servers:这个参数指定执行In-Memory Column Store加载操作的后台加载服务器进程的最大数量，因此这些服务器进程不能超过系统所能承受的负载。基于系统中的CPU内核数来设置一个合理值。

inmemory_trickle_repopulate_servers_percent:这个参数用来限制用于In-Memory Column Store重新加载的后台加载服务器进程的最大数量，trickle repopulation被设计只占加载服务器进程总数量的很小百分比。这个参数的值为
inmemory_max_populate_servers参数值的一个百分比。例如，如果这个参数设置为10，并且inmemory_max_populate_servers被设置为10，那么将会有一个cpu内核用于tricle repopulation操作。

optimizer_inmemory_aware:这个参数用来启用或禁用优化器成本模型来增强In-Memory Column Store。将这个参数设置为false，将造成优化器在优化SQL语句时忽略有in-memory属性的表。

对数据库启用In-Memory Column Store
在表，表空间或物化视图被启用In-Memory Column Store之前，必须对数据库启用In-Memory Column Store。对数据库启用In-Memory Column Store需要执行以下步骤:
1.确保数据库的兼容性参数设置为12.1.0或更高版本。
2.将inmemory_size参数设置为非零值。当对spfile参数文件设置这个参数时，可以使用alter system语句，并且必须指定scope=spfile，它的最小值为100M。
3.重启数据库，为了在SGA中初始化In-Memory Column Store必须要重启数据库。
4.可选操作，可以执行下面的语句来检查当前为In-Memory Column Store所分配的内存大小。

SQL> show parameter inmemory_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_size                        big integer 1G

对表启用与禁用In-Memory Column Store
通过对create table或alter table语句指定inmemory子句来对表启用In-Memory Column Store。通过对create table或alter table语句指定no inmemory子句对表禁用In-Memory Column Store。

为了对表启用与禁用In-Memory Column Store，需要执行以下步骤:
1.确保数据库已经启用了In-Memory Column Store
2.使用有合适权限的用户连接到数据库并创建或修改表
3.执行create table或alter table并指定inmemory或no inmmeory子句

创建表并启用In-Memory Column Store
下面的例子将创建一个名叫test_inmem的表并启用In-Memory Column Store

SQL> create table test_inmem(id number(5) primary key,test_col varchar2(15)) inmemory;

Table created.

这个例子对inmemory子句使用缺省值。因此将使用memcompress for query与priority none

对表启用In-Memory Column Store
下面的例子将对表oe.product_information表启用In-Memory Column Store:

SQL> alter table oe.product_information inmemory;

Table altered.

这个例子对inmemory子句使用缺省值。因此将使用memcompress for query与priority none

对表启用In-Memory Column Store并且指定压缩方法为for capacity low
下面的例子对表oe.product_information启用In-Memory Column Store并且指定压缩方法为for capacity low:

SQL> alter table oe.product_information inmemory memcompress for capacity low;

Table altered.

这个例子对priority子句使用缺省值，因此优先级为priority none。

对表启用In-Memory Column Store并且指定数据加载优先级为high
下面的例子对表oe.product_information启用In-Memory Column Store并且指定数据加载优先级为priority high

SQL> alter table oe.product_information inmemory priority high;

Table altered.

这个例子对memcompress子句使用缺省值，因此压缩方法为memcompress for query

对表启用In-Memory Column Store并且指定压缩方法为for capacity high，数据加载优先级为low
下面的例子对表oe.product_information启用In-Memory Column Store并且指定压缩方法为for capacity high，数据加载优先级为priority low

SQL> alter table oe.product_information inmemory  memcompress for capacity high priority low;

Table altered.

对表中的列启用In-Memory Column Store
下面的例子对表oe.product_information表中的一些列启用In-Memory Column Store而剩余的列不雇用。并且对不同的列子集启用不同的In-Memory Column Store压缩方法。

SQL> alter table oe.product_information
  2  inmemory memcompress for query (product_id, product_name, category_id, supplier_id, min_price)
  3  inmemory memcompress for capacity high (product_description, warranty_period, product_status, list_price)
  4  no inmemory (weight_class, catalog_url);

Table altered.

在这个例子中定义如下
以product_id开始至min_price结束的字段列表启用了In-Memory Column Store并且指定压缩方法为memcompress for query.

以product_description开始至list_price结束的字段列表启用了In-Memory Column Store并且指定压缩方法为memcompress for
capacity high

字段weight_class与catalog_url没有启用In-Memory Column Store。

在这个例子中对于priority子句使用了缺省值。因此将使用priority none。可以查询v$in_column_level视图，注意优先级的设置
必须应用给整个表或表分区。对不同字段列表指定不同的优先级是不允许的。

对表禁用In-Memory Column Store
为了对表禁用In-Memory Column Store，只需使用no inmemory子句。下面将对表oe.product_information禁用In-Memory Column Store:

SQL> alter table oe.product_information no inmemory;

Table altered.

对表空间启用与禁用In-Memory Column Store
在使用create tablespace语句来创建表空间时可以通过指定inmemory子句来让表空间启用In-Memory Column Store。也可以使用包含inmemory子句的alter tablespace语句来修改表空间让其启用In-Memory Column Store。

通过执行包含no inmemory子句的create tablespace或alter tablespace语句来对表空间禁用In-Memory Column Store。

当表空间启用In-Memory Column Store之后，存储在该表空间中的所有表与物化视图会默认启用In-Memory Column Store。对于表，物化视图与表空间inmemory子句是相同的。当对表空间启用In-Memory Column Store时在inmemory子句之前，并且当对表空间禁用In-Memory Column Store时在no inmemory子句之前需要使用default存储子句。

当表空间启用In-Memory Column Store后，存储在表空间中的单个表与物化视图可以有不同的in-memory设置，并且单个对象的设置会覆盖表空间级别的设置。例如，如果表空间对于数据加载优先级设置为priority low，但在表空间中的表的数据加载优先级设置为priority high，那么表将使用priority high。

为了对表空间启用或禁用In-Memory Column Store，执行以下操作:
1.确保对数据库启用了In-Memory Column Store。
2.使用有合适权限的用户连接到数据库来创建或修改表空间。
3.执行有inmemory或no inmemory子句的create tablespace或alter tablespace语句

下面的例子将创建一个表空间tbs1并对它启用In-Memory Column Store

SQL> create tablespace tbs1 datafile 'tbs1.dbf' size 40m online default inmemory;

Tablespace created.

这个例子对inmemory子句使用了缺省值，因此压缩方法为memcompress for query，数据加载优先级为priority none。

下面的例子将修改表空间tbs1让其启用In-Memory Column Store并且指定数据压缩方法为memcompress for capacity high，数据加载优先级为priority low

SQL> alter tablespace tbs1 default inmemory memcompress for capacity high priority low;

Tablespace altered.

对物化视图启用与禁用In-Memory Column Store
通过执行包含inmemory子句的create materialized view或alter materialized view语句来对物化视图启用In-Memory Column Store。通过执行包含no inmemory子句的ceate materialized view或alter materialized view语句来对物化视图禁用In-Memory Column Store。

为了对物化视图雇用与禁用In-Memory Column Store，需要执行以下操作:
1.确保对数据库启用了In-Memory Column Store。
2.使用有合适权限的用户连接到数据库来创建或修改物化视图。
3.执行包含inmemory或no inmemory子句的create materialized view或alter materialized语句

下面的例子创建一个名叫oe.prod_info_mv的物化视图并且启用In-Memory Column Store:

SQL> create materialized view oe.prod_info_mv inmemory as select * from oe.product_information;

Materialized view created.

这个例子对inmemory子句使用缺省值。因此将使用memcompress for query与priority none

下面的例子将让物化视图oe.prod_info_mv启用In-Memory Column Store并且指定数据加载优先级为priority high

SQL> alter materialized view oe.prod_info_mv inmemory priority high;

Materialized view altered.

Data Pump与In-Memory Column Store
在impdp命令中通过使用transform=inmemory:y选项来导入启用了In-Memory Column Store的数据库对象。使用这个选项，Data Pump将对所有对象保留In-Memory Column Store子句。当transform=inmemory:n时，Data Pump将对对象删除In-Memory Column Store子句。

也可以使用transform=inmemory_clause:string选项来覆盖在dump文件中数据库对象所设置的In-Memory Column Store子句。例如可以使用这个选项来修改被导入对象的In-Memory Column Store的数据压缩方法。

操作系统为Oracle Linux 7.1 数据库为Oracle 12.2.0.1，今天在登录pdb时使用sysdba权限登录正常，使用非sysdba权限登录出现ora-01033错误，错误信息如下:

[root@jytest1 ~]# su - oracle
Last login: Tue May 16 18:32:29 CST 2017
[oracle@jytest1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 16 18:39:42 2017

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

SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.
SQL> conn jy/jy@jypdb
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


Warning: You are no longer connected to ORACLE.

在MOS上有篇文档” Connecting To A 12c RAC Pluggable Database Intermittently Fails With ORA-1033 (Doc ID 1998112.1)”描述相关问题，原因有两个，一是pdb所使用的服务名与pdb数据库名相同，二是PDB没有在所有RAC实例上open，说使用pdb数据库名作为服务名对于RAC来说不是一个最佳方案，因为当实例使用SCAN来注册pdb名时并且节点监听到pdb被mounted。这可能造成连接被发送到pdb被mounted的实例上，当以非sysdba权限登录时就会出现ora-0133错误。

pdb的服务名确实是使用pdb名作为其服务名

[grid@jytest1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-MAY-2017 18:42:17

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                02-MAY-2017 11:14:02
Uptime                    14 days 7 hr. 28 min. 15 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/12.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jytest1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.175)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.171)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=jytest1.jydba.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.2.0/db/admin/jy/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_TEST" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "4b2c6373ae2547cce053ab828a0a7ca3" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "4e0ba8d9d278217be053ab828a0a1330" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jy" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jyXDB" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jycs" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jycsXDB" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jycspdb" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jypdb" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully

pdb数据库在所有实例上都open了

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

给pdb增加服务名jypdb_srv

[grid@jytest1 ~]$ su - oracle
Password: 
Last login: Tue May 16 18:39:02 CST 2017 on pts/0
[oracle@jytest1 ~]$ srvctl add service  -db jy -pdb jypdb -s jypdb_srv  -preferred "jy1" -available "jy2"
[oracle@jytest1 ~]$ srvctl start service -db jy -s jypdb_srv

[grid@jytest1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-MAY-2017 18:56:55

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                02-MAY-2017 11:14:02
Uptime                    14 days 7 hr. 42 min. 54 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/12.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jytest1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.175)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.171)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=jytest1.jydba.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.2.0/db/admin/jy/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_CRS" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_TEST" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "4b2c6373ae2547cce053ab828a0a7ca3" has 2 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
  Instance "jy1", status READY, has 2 handler(s) for this service...
Service "4e0ba8d9d278217be053ab828a0a1330" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jy" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jyXDB" has 1 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
Service "jycs" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jycsXDB" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jycspdb" has 1 instance(s).
  Instance "jycs1", status READY, has 1 handler(s) for this service...
Service "jypdb" has 2 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
  Instance "jy1", status READY, has 2 handler(s) for this service...
Service "jypdb_srv" has 2 instance(s).
  Instance "jy1", status READY, has 1 handler(s) for this service...
  Instance "jy1", status READY, has 2 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully

使用新服务名再次以非sysdba权限登录成功
SQL> conn jy/jy@jypdb_srv
Connected.
SQL>

Oracle 12CR2发布了，学习的季节也到了，学习的第一步就是得安装数据库，这篇文章介绍如何在Oracle Linux 7.1上安装Oracle 12c Grid Infrastructure for a Standalone Server with a New Database

检查Linux内核与软件包
1.检查linux版本

[root@jytest ~]# cat /etc/oracle-release
Oracle Linux Server release 7.1
[root@jytest ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.1 (Maipo)

2.检查内核版本

[root@jytest ~]# uname -r
3.8.13-55.1.6.el7uek.x86_64

3.检查需要的软件包是否安装

rpm -q package_name

也可以指定系统结构信息，例如:

rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" | grep package_name

也可以将查询多个软件包的语句组合在一起，并查看正确的软件版本.

[root@jytest ~]# rpm -q binutils compat-libstdc++ gcc glibc libaio libgcc libstdc++ make sysstat unixodbc
binutils-2.23.52.0.1-30.el7.x86_64
package compat-libstdc++ is not installed
gcc-4.8.3-9.el7.x86_64
glibc-2.17-78.0.1.el7.x86_64
libaio-0.3.109-12.el7.x86_64
libgcc-4.8.3-9.el7.x86_64
libstdc++-4.8.3-9.el7.x86_64
make-3.82-21.el7.x86_64
sysstat-10.1.5-7.el7.x86_64
package unixodbc is not installed

禁用Transparent HugePages
对于Red Hat Enterprise Linux kernels:

#cat /sys/kernel/mm/redhat_transparent_hugepage/enabled

其它kernels:

#cat /sys/kernel/mm/transparent_hugepage/enabled

下面是输出示例，显示Transparent HugePages被使用[always]标记被启用
[always] never
如果Transparent HugePages从内核中被删除，那么/sys/kernel/mm/transparent_hugepage或/sys/kernel/mm/redhat_transparent_hugepage文件会不存在。

为了禁用transparent hugepage执行以下操作:
1.向内核心/etc/grub.conf文件中增加以下记录

transparent_hugepage=never

2.重启操作系统

#reboot

创建用户组

[root@jytest /]# groupadd asmadmin
[root@jytest /]# groupadd asmdba
[root@jytest /]# groupadd asmoper
[root@jytest /]# groupadd dba
[root@jytest /]# groupadd oper
[root@jytest /]# groupadd oinstall
[root@jytest /]# groupadd backupdba
[root@jytest /]# groupadd dgdba
[root@jytest /]# groupadd kmdba
[root@jytest /]# groupadd racdba

创建用户

[root@jytest /]# useradd  -g oinstall -G dba,asmdba,backupdba,dgdba,kmdba,racdba,oper 
oracle
[root@jytest /]# useradd  -g oinstall -G asmadmin,asmdba,asmoper,dba grid

[root@jytest /]# passwd oracle
Changing password for user oracle.
New password: 
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 
passwd: all authentication tokens updated successfully.
[root@jytest /]# passwd grid
Changing password for user grid.
New password: 
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 
passwd: all authentication tokens updated successfully.

以root用户创建“Oracle inventory 目录”

[root@jytest /]# mkdir -p /u01/app/oraInventory
[root@jytest /]# chown -R grid:oinstall /u01/app/oraInventory
[root@jytest /]# chmod -R 775 /u01/app/oraInventory

以root用户创建“Grid Infrastructure BASE 目录”

[root@jytest /]# mkdir -p /u01/app/grid
[root@jytest /]# chown -R grid:oinstall /u01/app/grid
[root@jytest /]# chmod -R 775 /u01/app/grid

以root用户创建“Grid Infrastructure Home 目录”

[root@jytest /]# mkdir -p /u01/app/product/12.2.0/crs
[root@jytest /]# chown -R grid:oinstall /u01/app/product/12.2.0/crs
[root@jytest /]# chmod -R 775 /u01/app/product/12.2.0/crs

以root用户创建“Oracle Base 目录”

[root@jytest /]# mkdir -p /u01/app/oracle
[root@jytest /]# chown -R oracle:oinstall /u01/app/oracle
[root@jytest /]# chmod -R 775 /u01/app/oracle

以root用户创建“Oracle RDBMS Home 目录”

[root@jytest /]# mkdir -p /u01/app/oracle/product/12.2.0/db
[root@jytest /]# chown -R oracle:oinstall /u01/app/oracle/product/12.2.0/db
[root@jytest /]# chmod -R 775 /u01/app/oracle/product/12.2.0/db

创建一个tmp目录

[root@jytest /]# mkdir /u01/tmp
[root@jytest /]# chmod a+wr /u01/tmp

设置环境变量

[root@jytest /]# su - grid
[grid@jytest ~]$ vi .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

TEMP=/u01/tmp
TMPDIR=/u01/tmp
export TEMP TMPDIR
export LD_ASSUME_KERNEL=3.8.13
export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=/u01/app/product/12.2.0/crs
export ORACLE_SID=+ASM
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH
umask=022


TEMP=/u01/tmp
TMPDIR=/u01/tmp
export TEMP TMPDIR
export LD_ASSUME_KERNEL=3.8.13
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db
export ORACLE_SID=jy
export ORACLE_UNQNAME=jy
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export CLASSPATH
umask=022

修改内核参数编辑/etc/sysctl.conf文件

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576


[root@jytest3 ~]# sysctl -p
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

修改oarcle参数的shell限制
在/etc/security/limits.conf中添加以下参数：

grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536
grid soft stack 10240
grid hard stack 32768
grid soft memlock 3145728
grid hard memlock 3145728

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
oracle soft memlock 3145728
oracle hard memlock 3145728

修改shell的默认参数文件，在/etc/profile文件中添加以下内容：

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi


if [ $USER = "grid" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

对C shell(csh or tcsh)在/etc/csh.login文件中增加以下代码

if ( $USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
endif

if ( $USER == "grid" ) then
limit maxproc 16384
limit descriptors 65536
endif

配置创建ASM磁盘组的asm磁盘

[root@jytest3 ~]# fdisk -l

Disk /dev/sdb: 21.5 GB, 21474836480 bytes, 41943040 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sdc: 21.5 GB, 21474836480 bytes, 41943040 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/sda: 53.7 GB, 53687091200 bytes, 104857600 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk label type: dos
Disk identifier: 0x000209aa

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     1026047      512000   83  Linux
/dev/sda2         1026048   104857599    51915776   8e  Linux LVM

Disk /dev/mapper/ol-root: 47.7 GB, 47747956736 bytes, 93257728 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/mapper/ol-swap: 5368 MB, 5368709120 bytes, 10485760 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

查看磁盘uuid

[root@jytest3 ~]# /usr/lib/udev/scsi_id -g -u /dev/sdb
36000c295f8b38933c37ef4a42f446599
[root@jytest3 ~]# /usr/lib/udev/scsi_id -g -u /dev/sdc
36000c29739826dc8ef28ef9a3589d3a0

udev绑定

[root@jytest3 ~]# vi /etc/udev/rules.d/99-my-asmdevices.rules


KERNEL=="sd*[!0-9]", ENV{DEVTYPE}=="disk", SUBSYSTEM=="block",  PROGRAM=="/usr/lib/udev/scsi_id -g -u -d $devnode",  RESULT=="36000c295f8b38933c37ef4a42f446599", RUN 
+="/bin/sh -c 'mknod /dev/asmdisk01 b  $major $minor; chown grid:oinstall /dev/asmdisk01; chmod 0660 /dev/asmdisk01'"

KERNEL=="sd*[!0-9]", ENV{DEVTYPE}=="disk", SUBSYSTEM=="block",  PROGRAM=="/usr/lib/udev/scsi_id -g -u -d $devnode",  RESULT=="36000c29739826dc8ef28ef9a3589d3a0", RUN 
+="/bin/sh -c 'mknod /dev/asmdisk02 b  $major $minor; chown grid:oinstall /dev/asmdisk02; chmod 0660 /dev/asmdisk02'"

~

[root@jytest3 ~]# /sbin/udevadm trigger --type=devices --action=change
[root@jytest3 ~]# ls -lrt /dev/asm*
brw-rw----. 1 grid oinstall 8, 32 Mar  8 22:24 /dev/asmdisk02
brw-rw----. 1 grid oinstall 8, 16 Mar  8 22:24 /dev/asmdisk01

解压GI安装压缩包：

[grid@jytest3 soft]cd /u01/app/grid/product/12.2.0/crs/
[grid@jytest3 soft]# unzip -q /soft/linuxx64_12201_grid_home.zip


配置vnc
检查系统是否已经安装vncserver软件包

[root@jytest3 system]# rpm -qa|grep tigervnc
tigervnc-server-minimal-1.2.80-0.30.20130314svn5065.el7.x86_64
tigervnc-license-1.2.80-0.30.20130314svn5065.el7.noarch

没有安装执行以下命令进行安装

[root@jytest3 /]# cd soft
[root@jytest3 soft]# yum -y install tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64.rpm
Loaded plugins: langpacks
Examining tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64.rpm: tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64
Marking tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package tigervnc-server.x86_64 0:1.2.80-0.30.20130314svn5065.el7 will be installed
--> Finished Dependency Resolution
http://public-yum.oracle.com/repo/OracleLinux/OL7/UEKR3/x86_64/repodata/repomd.xml: [Errno 14] curl#6 - "Could not resolve host: public-yum.oracle.com; Unknown error"
Trying other mirror.
http://public-yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/repodata/repomd.xml: [Errno 14] curl#6 - "Could not resolve host: public-yum.oracle.com; Unknown error"
Trying other mirror.

Dependencies Resolved

=======================================================================================================================================================================
 Package                     Arch               Version                                      Repository                                                           Size
=======================================================================================================================================================================
Installing:
 tigervnc-server             x86_64             1.2.80-0.30.20130314svn5065.el7              /tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64             488 k

Transaction Summary
=======================================================================================================================================================================
Install  1 Package

Total size: 488 k
Installed size: 488 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
  Installing : tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64                                                                                              1/1 
  Verifying  : tigervnc-server-1.2.80-0.30.20130314svn5065.el7.x86_64                                                                                              1/1 

Installed:
  tigervnc-server.x86_64 0:1.2.80-0.30.20130314svn5065.el7                                                                                                             

Complete!

之前的版本，如果安装vnc一般都需要使用vncserver命令来设置口令，然后配置/etc/sysconfig/vncservers文件。
在Oracle Linux7中，虽然然还存在这个文件，不过其内容只有如下一行：

root@jytest3 soft]#  cat /etc/sysconfig/vncservers
# THIS FILE HAS BEEN REPLACED BY /lib/systemd/system/vncserver@.service

复制文件

[root@jytest3 soft]# cp /lib/systemd/system/vncserver@.service /etc/systemd/system/vncserver_root@:2.service

编辑vncserver_root@:2.service文件将文件中的用root替换

[root@jytest3 soft]# cd /etc/systemd/system/
[root@jytest3 system]# vi vncserver_root@:2.service
# The vncserver service unit file
#
# Quick HowTo:
# 1. Copy this file to /etc/systemd/system/vncserver@:.service
# 2. Edit  and vncserver parameters appropriately
#   ("runuser -l  -c /usr/bin/vncserver %i -arg1 -arg2")
# 3. Run `systemctl daemon-reload`
# 4. Run `systemctl enable vncserver@:.service`
#
# DO NOT RUN THIS SERVICE if your local area network is
# untrusted!  For a secure way of using VNC, you should
# limit connections to the local host and then tunnel from
# the machine you want to view VNC on (host A) to the machine
# whose VNC output you want to view (host B)
#
# [user@hostA ~]$ ssh -v -C -L 590N:localhost:590M hostB
#
# this will open a connection on port 590N of your hostA to hostB's port 590M
# (in fact, it ssh-connects to hostB and then connects to localhost (on hostB).
# See the ssh man page for details on port forwarding)
#
# You can then point a VNC client on hostA at vncdisplay N of localhost and with
# the help of ssh, you end up seeing what hostB makes available on port 590M
#
# Use "-nolisten tcp" to prevent X connections to your VNC server via TCP.
#
# Use "-localhost" to prevent remote VNC clients connecting except when
# doing so through a secure tunnel.  See the "-via" option in the
# `man vncviewer' manual page.


[Unit]
Description=Remote desktop service (VNC)
After=syslog.target network.target

[Service]
Type=simple
# Clean any existing files in /tmp/.X11-unix environment
ExecStartPre=/bin/sh -c '/usr/bin/vncserver -kill %i > /dev/null 2>&1 || :'
ExecStart=/sbin/runuser -l root -c "/usr/bin/vncserver %i"
PIDFile=/root/.vnc/%H%i.pid
ExecStop=/bin/sh -c '/usr/bin/vncserver -kill %i > /dev/null 2>&1 || :'

[Install]
"vncserver_root@:2.service" 45L, 1739C written

启用vncserver配置

[root@jytest3 system]# systemctl enable vncserver_root@:2.service
ln -s '/etc/systemd/system/vncserver_root@:2.service' '/etc/systemd/system/multi-user.target.wants/vncserver_root@:2.service'

启动vncserver服务

[root@jytest3 system]# vncserver

You will require a password to access your desktops.

Password:
Verify:
xauth:  file /root/.Xauthority does not exist

New 'jytest3:1 (root)' desktop is jytest3:1

Creating default startup script /root/.vnc/xstartup
Starting applications specified in /root/.vnc/xstartup
Log file is /root/.vnc/jytest3:1.log

关闭防火墙

[root@jytest3 system]# systemctl stop firewalld
[root@jytest3 system]# systemctl disable firewalld
rm '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service'
rm '/etc/systemd/system/basic.target.wants/firewalld.service'

用vnc登录服务器安装软件

[root@jytest3 Desktop]# xhost +
access control disabled, clients can connect from any host
[root@jytest3 Desktop]# su - grid
Last login: Tue Mar  7 17:50:57 CST 2017 on pts/0
[grid@jytest3 ~]$ cd /soft/grid


[grid@jytest3 crs]$ ./gridSetup.sh 
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.

Can't connect to X11 window server using '10.138.135.167:1.0' as the value of the DISPLAY variable.

出现在错误，并且错误信息提示需要设置环境变量DISPLAY，下面设置环境变量DISPLAY

[grid@jytest3 grid]$ exit
logout
[root@jytest3 Desktop]# xdpyinfo
name of display:    :1
version number:    11.0
vendor string:    The X.Org Foundation
vendor release number:    11500000
X.Org version: 1.15.0
maximum request size:  16777212 bytes
motion buffer size:  256
bitmap unit, bit order, padding:    32, LSBFirst, 32
image byte order:    LSBFirst
number of supported pixmap formats:    6
...省略...只需要记录name of display:的值

[root@jytest3 Desktop]# xhost +
access control disabled, clients can connect from any host
[root@jytest3 Desktop]# su - grid
Last login: Tue Mar  7 17:50:40 CST 2017 on pts/0
[grid@jytest3 ~]$ export DISPLAY=:1(这时设置为name of display的值:1)
[grid@jytest3 ~]$ xdpyinfo
name of display:    :1
version number:    11.0
vendor string:    The X.Org Foundation
vendor release number:    11500000
X.Org version: 1.15.0
maximum request size:  16777212 bytes
motion buffer size:  256
bitmap unit, bit order, padding:    32, LSBFirst, 32
image byte order:    LSBFirst
number of supported pixmap formats:    6
...省略...
[grid@jytest3 ~]$cd /u01/app/grid/product/12.2.0/crs/
[grid@jytest3 crs]$ ./gridSetup.sh

选择for a Standalone Server (Oracle Restart)

现在可以让安装程序来以root用户自动执行安装所需要执行的脚本，指定root用户的密码

检查磁盘组data是否创建成功

[grid@jytest3 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512             512   4096  4194304     40960    40760                0           20380              0             N  DATA/

安装数据库软件

[root@jytest3 soft]# unzip linuxx64_12201_database.zip -d 

[root@jytest3 Desktop]# xhost +
access control disabled, clients can connect from any host

[root@jytest3 Desktop]# xdpyinfo
name of display:    :1
version number:    11.0
vendor string:    The X.Org Foundation
vendor release number:    11500000
X.Org version: 1.15.0
maximum request size:  16777212 bytes
motion buffer size:  256
bitmap unit, bit order, padding:    32, LSBFirst, 32
image byte order:    LSBFirst
number of supported pixmap formats:    6
...省略...只需要记录name of display:的值

[root@jytest3 Desktop]# su - oracle
Last login: Thu Mar  9 04:17:35 CST 2017 on pts/2
[oracle@jytest3 ~]$ export DISPLAY=:1
[oracle@jytest3 ~]$ cd /soft/database
[oracle@jytest3 database]$ ./runInstaller

执行脚本

[root@jytest3 /]# ./u01/app/oracle/product/12.2.0/db/root.sh
Performing root user operation.

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/12.2.0/db

Enter the full pathname of the local bin directory: [/usr/local/bin]: 
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Do you want to setup Oracle Trace File Analyzer (TFA) now ? yes|[no] : 
yes
Installing Oracle Trace File Analyzer (TFA).
Log File: /u01/app/oracle/product/12.2.0/db/install/root_jytest3_2017-03-09_15-33-03-156667468.log
Finished installing Oracle Trace File Analyzer (TFA)

创建数据库

[oracle@jytest3 database]$ dbca

在创建的过程中出现了以下错误

[Thread-301] [ 2017-03-09 22:15:26.774 CST ] [PostDBCreationStep.executeImpl:1156]  Exception while Starting with HA Database Resource PRCR-1079 : Failed to start resource ora.jy.db
CRS-5017: The resource action "ora.jy.db start" encountered the following error: 
ORA-01017: invalid username/password; logon denied
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/jytest3/crs/trace/ohasd_oraagent_grid.trc".

CRS-2674: Start of 'ora.jy.db' on 'jytest3' failed
ORA-01017: invalid username/password; logon denied

数据库jy确实没能通过oracle restart自动启动

[grid@jytest3 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       jytest3                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest3                  STABLE
ora.asm
               ONLINE  ONLINE       jytest3                  Started,STABLE
ora.ons
               OFFLINE OFFLINE      jytest3                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.driver.afd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.evmd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.jy.db
      1        ONLINE  OFFLINE                               Instance Shutdown,ST
                                                             ABLE
--------------------------------------------------------------------------------

如是手动启动数据库

[root@jytest3 ~]# su - oracle
Last login: Thu Mar  9 22:08:36 CST 2017 on pts/4
[oracle@jytest3 ~]$ echo $ORACLE_SID
jy
[oracle@jytest3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Mar 9 22:17:21 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size                  8795904 bytes
Variable Size            1358956800 bytes
Database Buffers         1140850688 bytes
Redo Buffers                7979008 bytes
Database mounted.
Database opened.

[grid@jytest3 trace]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       jytest3                  STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       jytest3                  STABLE
ora.asm
               ONLINE  ONLINE       jytest3                  Started,STABLE
ora.ons
               OFFLINE OFFLINE      jytest3                  STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.driver.afd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.evmd
      1        ONLINE  ONLINE       jytest3                  STABLE
ora.jy.db
      1        ONLINE  ONLINE       jytest3                  Open,HOME=/u01/app/o
                                                             racle/product/12.2.0
                                                             /db,STABLE
--------------------------------------------------------------------------------

到此安装完成!

为了跨网络传输表，可以在执行导入操作时使用network_link参数，这样导入操作将会使用数据库链路而不用先导出dump文件。其操作步骤如下:
1.选择一组表，分区或子分区。
如果是要传输分区，那么在传输表操作中可以指定一个表的分区，并且在同一操作中没有其它的表将被传输。如果在传输表操作中中只有表分区的子集被导出，那么在导入后每个分区将变成非分区表。

2.在源数据库中，将要被传输的表，分区或子分区所在表空间设置为只读模式。为了查询表所在的表空间可以查询dba_tables视图，为了查询表空间的所有文件可以查询dba_data_files视图。

3.传输表，分区或子分区所在表空间的所有数据文件到目标数据库。如果源平台与目标平台的字节编码不一样，那么可以使用以下
任何一种方法来转换数据文件。
–使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件，它们会自动将数据文件转换为目标平台的字节编码。

–使用rman的convert命令来将数据文件转换为目标平台的字节编码。

4.在目标数据库上执行导入操作

5.可选操作，将源数据库中的表空间设置为读写模式

下面的例子将介绍如何使用跨网传输表，分区或子分区的方法来将一个数据库中的hr.emp_test与oe.orders_test表传输到另一个数
据库中。其中源平台与目标平台的字节编码相同。

1.先在源数据库中创建表hr.emp_test与oe.orders_test

SQL> create tablespace emp_test datafile '+DATADG/jyrac/datafile/emp_test_01.dbf' size 100M  autoextend off  extent management local segment space management auto;
Tablespace created

SQL> create tablespace orders_test datafile '+DATADG/jyrac/datafile/orders_test_01.dbf' size 100M  autoextend off  extent management local segment space management auto;
Tablespace created



SQL> create table hr.emp_test tablespace emp_test as select * from hr.employees;
Table created

SQL> create table oe.orders_test tablespace orders_test as select * from oe.orders;
Table created

2.在目标数据库中创建数据库链路连接到源数据库

SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.


SQL> create public database link jyrac_link
  2    connect to jy identified by "jy"
  3    using '(DESCRIPTION =
  4      (ADDRESS_LIST =
  5        (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.153)(PORT = 1521))
  6      )
  7      (CONNECT_DATA =
  8        (SERVER = DEDICATED)
  9        (SERVICE_NAME =jyrac)
 10      )
 11    )';

Database link created.

3.将源数据库中表hr.emp_test与oe.orders_test所在的表空间设置为只读状态

SQL> alter tablespace emp_test read only;
Tablespace altered

SQL> alter tablespace orders_test read only;
Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
SALES_TEST                     ONLINE
EMP_TEST                       READ ONLY
ORDERS_TEST                    READ ONLY
11 rows selected

4.将表空间tem_test与orders_test的所有数据文件复制到目标数据库中
在源数据库中创建目录tts_datafile（存储数据文件)

SQL> create or replace directory tts_datafile as '+datadg/jyrac/datafile/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中创建目录tts_datafile（存储数据文件)

SQL> create or replace directory tts_datafile as '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.


SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'emp_test_01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'emp_test_01.dbf');
PL/SQL procedure successfully completed

SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'orders_test_01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'orders_test_01.dbf');
PL/SQL procedure successfully completed


ASMCMD [+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile] > ls -lt  
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  N    orders_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.303.945987633
DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  N    emp_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.302.945987591
DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  Y    FILE_TRANSFER.303.945987633
DATAFILE  UNPROT  COARSE   JUN 06 22:00:00  Y    FILE_TRANSFER.302.945987591
DATAFILE  UNPROT  COARSE   JUN 06 19:00:00  N    sales_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.301.945975283
DATAFILE  UNPROT  COARSE   JUN 06 19:00:00  Y    FILE_TRANSFER.301.945975283
DATAFILE  UNPROT  COARSE   JUN 05 23:00:00  Y    SYSAUX.275.939167015
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    users01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    test01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    example01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    SYSTEM.274.939167015
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  N    testtb01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDO_2.277.939167063
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS2.278.945029905
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS1.273.939167015
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    TESTTB.295.944828399

5.在目标数据库中执行导入操作

[oracle@jytest1 tts]$ impdp system/xxzx7817600@JYPDB_175 network_link=jyrac_link transportable=always transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/emp_test_01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/orders_test_01.dbf' tables=hr.emp_test,oe.orders_test logfile=imp_tables.log directory=tts_dump

Import: Release 12.2.0.1.0 - Production on Tue Jun 6 22:24:24 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********@JYPDB_175 network_link=jyrac_link transportable=always transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/emp_test_01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/orders_test_01.dbf tables=hr.emp_test,oe.orders_test logfile=imp_tables.log directory=tts_dump 
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jun 6 22:24:57 2017 elapsed 0 00:00:30

6.可选操作，将源数据库中的表空间emp_test与orders_test设置为读写模式

SQL> alter tablespace emp_test read write;
Tablespace altered

SQL> alter tablespace orders_test read write;
Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
SALES_TEST                     ONLINE
EMP_TEST                       ONLINE
ORDERS_TEST                    ONLINE
11 rows selected

在Oracle 12cr2中，可以使用传输表功能来从一个数据库中复制一组表，分区或子分区到另一个数据库中。传输表操作将会指定表，分区或子分区的元数据移到另一个数据库中。传输表操作会自动识别所指定表所在的表空间。为了移动数据，需要将这些表所在表空间的所有数据文件复制到目标数据库。Data Pump导入会自动释放由表，分区或子分区所占有的数据块，这些数据块不是传输表操作的一部分。

可以使用以下方法来传输表，分区或子分区:
.使用导出dump文件
在执行导出时，指定tables参数并且设置transportable参数为always。在执行导入时，不需要指定transportable参数。Data Pump导入会自动识别传输表操作。

.跨网络
在执行导入时，指定tables参数并且设置transportable参数为always,并且指定network_link参数来指定数据链路

传输表操作的限制
.不能将相同方案中相同表名的表传输到目标数据库中。然而可以使用remap_table导入参数来将表中的数据导入到不同的表中。另外，在传输操作执行之前，可以重命名被传输表或目标表。

.对于加密有以下限制:
–不能传输加密表空间中的表
–不能包含加密列的表

.不能在使用不同的time zone文件版本的不同平台之间传输使用timestamp with timezone的表

使用导出dump文件方式来传输表，分区，或子分区
在数据库之间使用志出dump文件来传输表需要执行以下步骤。
1.选择一组表，分区或子分区。
如果是要传输分区，那么在传输表操作中可以指定一个表的分区，并且在同一操作中没有其它的表将被传输。如果在传输表操作中中只有表分区的子集被导出，那么在导入后每个分区将变成非分区表。

2.在源数据库中，将要被传输的表，分区或子分区所在表空间设置为只读模式。为了查询表所在的表空间可以查询dba_tables视图，为了查询表空间的所有文件可以查询dba_data_files视图。

3.执行Data Pump导出

4.传输导出的dump文件，将导出的dump文件复制到目标数据库并且让其可以访问。

5.传输表，分区或子分区所在表空间的所有数据文件到目标数据库。如果源平台与目标平台的字节编码不一样，那么可以使用以下任何一种方法来转换数据文件。
–使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件，它们会自动将数据文件转换为目标平台的字节编码。

–使用rman的convert命令来将数据文件转换为目标平台的字节编码。

6.可选操作，将源数据库中的表空间设置为读写模式

7.在目标数据库上执行导入操作

下面的例子将分区表sh.sales_test表中的部分分区(sales_test_q1_2000,sales_test_q2_2000)传输到目标数据库中。源平台与目标平台字节编码一样，都是linux 64位操作系统

1.先创建分区表sales_test

SQL> create tablespace sales_test datafile '+DATADG/jyrac/datafile/sales_test_01.dbf' size 100M  autoextend off  extent management local segment space management auto;
Tablespace created

-- Create table
create table SH.SALES_TEST
(
  prod_id       /* NUMBER not null*/,
  cust_id       /*NUMBER not null*/,
  time_id       /*DATE not null*/,
  channel_id    /*NUMBER not null*/,
  promo_id      /*NUMBER not null*/,
  quantity_sold /*NUMBER(10,2) not null*/,
  amount_sold   /*NUMBER(10,2) not null*/
)
partition by range (TIME_ID)
(
  partition SALES_TEST_1995 values less than (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_1996 values less than (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_H1_1997 values less than (TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_H2_1997 values less than (TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q1_1998 values less than (TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q2_1998 values less than (TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q3_1998 values less than (TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q4_1998 values less than (TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q1_1999 values less than (TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q2_1999 values less than (TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q3_1999 values less than (TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q4_1999 values less than (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q1_2000 values less than (TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q2_2000 values less than (TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q3_2000 values less than (TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q4_2000 values less than (TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q1_2001 values less than (TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q2_2001 values less than (TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q3_2001 values less than (TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q4_2001 values less than (TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition SALES_TEST_Q1_2002 values less than (TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q2_2002 values less than (TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q3_2002 values less than (TO_DATE(' 2002-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q4_2002 values less than (TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q1_2003 values less than (TO_DATE(' 2003-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q2_2003 values less than (TO_DATE(' 2003-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q3_2003 values less than (TO_DATE(' 2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 0
    initrans 1
    maxtrans 255,
  partition SALES_TEST_Q4_2003 values less than (TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace SALES_TEST
    pctfree 5
    initrans 1
    maxtrans 255
) tablespace sales_test as select * from sh.sales;
-- Add comments to the table
comment on table SH.SALES_TEST
  is 'facts table, without a primary key; all rows are uniquely identified by the combination of all foreign keys';
-- Add comments to the columns
comment on column SH.SALES_TEST.prod_id
  is 'FK to the products dimension table';
comment on column SH.SALES_TEST.cust_id
  is 'FK to the customers dimension table';
comment on column SH.SALES_TEST.time_id
  is 'FK to the times dimension table';
comment on column SH.SALES_TEST.channel_id
  is 'FK to the channels dimension table';
comment on column SH.SALES_TEST.promo_id
  is 'promotion identifier, without FK constraint (intentionally) to show outer join optimization';
comment on column SH.SALES_TEST.quantity_sold
  is 'product quantity sold with the transaction';
comment on column SH.SALES_TEST.amount_sold
  is 'invoiced amount to the customer';
-- Create/Recreate indexes
create bitmap index SH.SALES_TEST_CHANNEL_BIX on SH.SALES_TEST (CHANNEL_ID)
  nologging  local;
create bitmap index SH.SALES_TEST_CUST_BIX on SH.SALES_TEST (CUST_ID)
  nologging  local;
create bitmap index SH.SALES_TEST_PROD_BIX on SH.SALES_TEST (PROD_ID)
  nologging  local;
create bitmap index SH.SALES_TEST_PROMO_BIX on SH.SALES_TEST (PROMO_ID)
  nologging  local;
create bitmap index SH.SALES_TEST_TIME_BIX on SH.SALES_TEST (TIME_ID)
  nologging  local;
-- Create/Recreate primary, unique and foreign key constraints
alter table SH.SALES_TEST
  add constraint SALES_TEST_CHANNEL_FK foreign key (CHANNEL_ID)
  references SH.CHANNELS (CHANNEL_ID)
  novalidate;
alter table SH.SALES_TEST
  add constraint SALES_TEST_CUSTOMER_FK foreign key (CUST_ID)
  references SH.CUSTOMERS (CUST_ID)
  novalidate;
alter table SH.SALES_TEST
  add constraint SALES_TEST_PRODUCT_FK foreign key (PROD_ID)
  references SH.PRODUCTS (PROD_ID)
  novalidate;
alter table SH.SALES_TEST
  add constraint SALES_TEST_PROMO_FK foreign key (PROMO_ID)
  references SH.PROMOTIONS (PROMO_ID)
  novalidate;
alter table SH.SALES_TEST
  add constraint SALES_TEST_TIME_FK foreign key (TIME_ID)
  references SH.TIMES (TIME_ID)
  novalidate;

2.登录到源数据库，将表sh.sales_test所在的表空间设置为只读状态

SQL> alter tablespace sales_test read only;
Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
SALES_TEST                     READ ONLY
9 rows selected

3.导出dump文件

SQL> create or replace directory tts_dump as '/tts';
Directory created
SQL> grant execute,read,write on directory tts_dump to public;
Grant succeeded


[root@jyrac1 ~]# su - oracle
[oracle@jyrac1 ~]$ expdp system/xxzx7817600 dumpfile=sales_test.dmp directory=tts_dump tables=sh.sales_test:sales_test_q1_2000,sh.sales_test:sales_test_q2_2000 transportable=always logfile=sales_test.log

Export: Release 11.2.0.4.0 - Production on Tue Jun 6 11:21:02 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=sales_test.dmp directory=tts_dump tables=sh.sales_test:sales_test_q1_2000,sh.sales_test:sales_test_q2_2000 transportable=always logfile=sales_test.log
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /tts/sales_test.dmp
******************************************************************************
Datafiles required for transportable tablespace SALES_TEST:
  +DATADG/jyrac/datafile/sales_test_01.dbf
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jun 6 11:21:22 2017 elapsed 0 00:00:17

4.将导出的dump文件传输到目标数据库

[oracle@jytest1 tts]$ scp oracle@10.138.130.151:/tts/sales_test.* /tts/
The authenticity of host '10.138.130.151 (10.138.130.151)' can't be established.
RSA key fingerprint is 92:b7:e1:f5:a4:99:5a:de:d5:d3:f2:25:f7:98:0a:a1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.138.130.151' (RSA) to the list of known hosts.
oracle@10.138.130.151's password:
sales_test.dmp                                                                                                                                                                                            100%  264KB 264.0KB/s   00:00
sales_test.log                                                                                                                                                                                            100% 1542     1.5KB/s   00:00
[oracle@jytest1 tts]$ ls -lrt
total 268
-rw-r----- 1 oracle oinstall 270336 Jun  6 18:49 sales_test.dmp
-rw-r--r-- 1 oracle oinstall   1542 Jun  6 18:49 sales_test.log

5.将sales_test表空间的数据文件传输到目标数据库
在源数据库中创建目录tts_datafile（存储数据文件)

SQL> create or replace directory tts_datafile as '+datadg/jyrac/datafile/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中创建目录tts_datafile（存储数据文件)

SQL> create or replace directory tts_datafile as '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.


SQL> conn sys/xxzx7817600@jypdb as sysdba
Connected.


SQL> create public database link jyrac_link
  2    connect to jy identified by "jy"
  3    using '(DESCRIPTION =
  4      (ADDRESS_LIST =
  5        (ADDRESS = (PROTOCOL = TCP)(HOST =10.138.130.153)(PORT = 1521))
  6      )
  7      (CONNECT_DATA =
  8        (SERVER = DEDICATED)
  9        (SERVICE_NAME =jyrac)
 10      )
 11    )';

Database link created.


SQL> exec dbms_file_transfer.get_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'sales_test_01.dbf',source_database =>'jyrac_link',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'sales_test_01.dbf');
PL/SQL procedure successfully completed

ASMCMD [+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile] > ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   JUN 06 18:00:00  N    sales_test_01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.301.945975283
DATAFILE  UNPROT  COARSE   JUN 06 18:00:00  Y    FILE_TRANSFER.301.945975283
DATAFILE  UNPROT  COARSE   JUN 05 23:00:00  Y    SYSAUX.275.939167015
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    users01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    test01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  N    example01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    SYSTEM.274.939167015
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.300.945620337
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.299.945620391
DATAFILE  UNPROT  COARSE   JUN 02 16:00:00  Y    FILE_TRANSFER.298.945620417
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  N    testtb01.dbf => +DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/TESTTB.295.944828399
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDO_2.277.939167063
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS2.278.945029905
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    UNDOTBS1.273.939167015
DATAFILE  UNPROT  COARSE   JUN 02 00:00:00  Y    TESTTB.295.944828399

6.可选操作，将源数据库中的表空间sales_test设置为读写模式

SQL> alter tablespace sales_test read write;
Tablespace altered

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
SALES_TEST                     ONLINE
9 rows selected

7.在目标数据库上执行导入操作

[oracle@jytest1 tts]$ impdp system/xxzx7817600@JYPDB_175 dumpfile=sales_test.dmp directory=tts_dump transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/sales_test_01.dbf' tables=sh.sales_test:sales_test_q1_2000,sh.sales_test:sales_test_q2_2000 logfile=imp_sales_test.log

Import: Release 12.2.0.1.0 - Production on Tue Jun 6 19:23:09 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********@JYPDB_175 dumpfile=sales_test.dmp directory=tts_dump transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/sales_test_01.dbf tables=sh.sales_test:sales_test_q1_2000,sh.sales_test:sales_test_q2_2000 logfile=imp_sales_test.log
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TABLE_01" completed with 13 error(s) at Tue Jun 6 19:25:06 2017 elapsed 0 00:01:46

SQL> select owner,table_name,tablespace_name from dba_tables where owner='SH';
OWNER                                                                            TABLE_NAME                                                                       TABLESPACE_NAME
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------
SH                                                                               SALES_TEST_SALES_TEST_Q1_2000                                                    SALES_TEST
SH                                                                               SALES_TEST_SALES_TEST_Q2_2000                                                    SALES_TEST
SH                                                                               SALES_TRANSACTIONS_EXT
SH                                                                               COSTS
SH                                                                               SALES
SH                                                                               CAL_MONTH_SALES_MV                                                               EXAMPLE
SH                                                                               FWEEK_PSCAT_SALES_MV                                                             EXAMPLE
SH                                                                               DIMENSION_EXCEPTIONS                                                             EXAMPLE
SH                                                                               SUPPLEMENTARY_DEMOGRAPHICS                                                       EXAMPLE
SH                                                                               COUNTRIES                                                                        EXAMPLE
SH                                                                               CUSTOMERS                                                                        EXAMPLE
SH                                                                               PROMOTIONS                                                                       EXAMPLE
SH                                                                               PRODUCTS                                                                         EXAMPLE
SH                                                                               TIMES                                                                            EXAMPLE
SH                                                                               CHANNELS                                                                         EXAMPLE

可以看到分区sales_test_q1_2000与sale_test_q2_2000导入后分别成为了一张非分区表

传输数据库full transportable export/import
可以使用full transportable export/import功能将整个数据库从一个数据库实例复制到另一个数据库实例。可以使用Data Pump来生成一个导出dump文件，如果需要将这个dump文件传输到目标数据库，然后导入dump文件。另外也可以使用Data Pump跨网络来复制数据库。

数据库中要被传输的表空间可以是字典管理或本地管理表空间。源数据库中的表空间的块大小不必与目标数据库中标准块大小相同。

这种传输数据库的方法要求直到完成导出dump文件之前所要传输的用户创建的表空间必须设置为只读状态。如果不能满足这个条件那么可以使用备份功能来完成传输表空间。

full transportable export/import的限制
full transportable export/import有以下限制:
.对于不同字节编码的平台不能传输加密表空间，对于相同字节编码的平台为了传输加密表空间，在执行导出dump文件时需要设置encryption_pwd_prompt导出参数设置为yes，或者使用encryption_password导出参数。在导入dump文件时，使用与导出时相同的参数设置。
.当跨网络传输数据库时，如果在管理表空间(比如system或sysaux表空间）中存在包含long或long raw列的表，那么是不支持传输的。
.full transportable export/import可以使用传统的Data Pump导出/导入来导出与导入存储在管理表空间中用户创建的数据库对象，比如直接路径或外部表。管理表空间不是用户创建而是由数据库提供,比如sytem与sysaux表空间。
.full transportable export/import不能传输同时存储在管理表空间(比如system与sysaux）与用户创建表空间中的数据库对象。例如，一个分区表可能会同时存储在管理表空间与用户表空间中。如果有这样的对象，那么在传输之前应该重新定义这些对象，因此它们将整个存储在管理表空间或者用户表空间中。如果对象不能重定义，那么可以使用传统的Data Pump导出/导入。.当跨网络传输数据库时，当存储在管理表空间(比如system与sysaux)中的表它的审计跟踪住处本身存储在用户表空间中就不能启用审计。

使用导出dump文件来传输数据库
使用导出dump文件方式来传输数据库必须执行以下步骤：
1.在源数据库上，将每个用户表空间设置为只读状态。在执行导出操作时要确保设置参数transportable=always与full=y。如果源数据库的版本是11.2.0.3或11G之后的版本，那么还必须设置version=12或更高版本号。导出的dump文件包含了存储在用户表空间中对象的元数据与存储在管理表空间(比如system与sysaux)中用户创建对象的元数据与实际数据。

2.将导出的dump文件传输到目标数据库

3.将所有用户表空间的所有数据文件传输到目标数据库，如果源平台与目标平台不同，那么需要检查字节编码，可以通过查询v$transportable_platform视图进行查看。如果源平台与目标平台的字节编码不一样，那么使用以下一种方法来转换数据文件:
.使用dbms_file_transfer包中的get_file或put_file过程来传输数据文件。这些过程会将源数据文件自动转换为目标平台的字节编码方式。
.使用rman的convert命令将源数据文件转换为目标平台的字节编码方式

4.可选操作，将源数据库中的将被传输的表空间设置为读写状态

5.在目标数据库中导入数据，当导入完成后，用户表空间将会设置为读写状态。

下面的例子将把jyrac数据库(11.2.0.4)传输到jypdb数据库(12.2的PDB),源数据库jyrac中用户表空间为test，users,example,源平台与目标平台的字节编码相同。具体操作如下:
1.将表空间test设置为只读状态

SQL> alter tablespace test read only;

Tablespace altered.

SQL> alter tablespace users read only;

Tablespace altered.

SQL> alter tablespace example read only;

Tablespace altered.



SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          READ ONLY
UNDOTBS2                       ONLINE
EXAMPLE                        READ ONLY
TEST                           READ ONLY

8 rows selected.

2.使用Data Pump导出工具执行full transportable export操作

SQL> create or replace directory tts_dump as '/tts';
Directory created
SQL> grant execute,read,write on directory tts_dump to public;
Grant succeeded

SQL> host expdp tts/tts@JYRAC dumpfile=exp_test.dmp directory=tts_dump transportable=always full=y version=12 logfile=exp_test.log

Export: Release 11.2.0.4.0 - Production on Fri May 26 17:41:33 2017

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "TTS"."SYS_EXPORT_FULL_01":  tts/********@JYRAC dumpfile=exp_test.dmp directory=tts_dump transportable=always full=y version=12 logfile=exp_test.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/PLUGTS_FULL/FULL/PLUGTS_TABLESPACE
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 47.43 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/PROCEDURE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/ALTER_FUNCTION
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_BODY
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/SECONDARY_TABLE/INDEX/INDEX
ORA-39043: Object type INDEX is not supported for "SH"."SYS_IL0000088402C00006$$".
ORA-39043: Object type INDEX is not supported for "SH"."SYS_IL0000088405C00002$$".
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/DOMAIN_INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/TRIGGER
Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW
Processing object type DATABASE_EXPORT/SCHEMA/JOB
Processing object type DATABASE_EXPORT/SCHEMA/DIMENSION
Processing object type DATABASE_EXPORT/END_PLUGTS_BLK
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW"               5.976 KB      38 rows
. . exported "SYS"."AUD$"                                473.3 KB    2931 rows
. . exported "SYS"."DAM_CONFIG_PARAM$"                   6.367 KB      10 rows
. . exported "WMSYS"."WM$ENV_VARS"                       5.921 KB       3 rows
......
. . exported "SYSTEM"."REPCAT$_TEMPLATE_REFGROUPS"           0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_SITES"               0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_TEMPLATE_TARGETS"             0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_AUTHORIZATIONS"          0 KB       0 rows
. . exported "SYSTEM"."REPCAT$_USER_PARM_VALUES"             0 KB       0 rows
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE"              0 KB       0 rows
Master table "TTS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TTS.SYS_EXPORT_FULL_01 is:
  /tts/exp_test.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
  +DATADG/jyrac/datafile/example.260.930413057
Datafiles required for transportable tablespace TEST:
  +DATADG/jyrac/datafile/test01.dbf
Datafiles required for transportable tablespace USERS:
  +DATADG/jyrac/datafile/users.263.930413057
Job "TTS"."SYS_EXPORT_FULL_01" completed with 2 error(s) at Fri May 26 17:47:08 2017 elapsed 0 00:05:31

在执行导出时必须指定transportable=always，它用来判断是否使用传输选项。full参数用来指定将导出整个数据库。dumpfile参数指定dump文件名。directory参数指定目录，它可以指向操作系统或ASM磁盘组。在执行导出前必须先创建目录，并授予读写权限。在non-CDB中，会自动创建目录对象DATA_PUMP_DIR，并且会自动授予DBA角色可以对其执行读写访问。因此sys与system用户就可以对目录执行读写操作。然而在PDB中不会自动创建目录DATA_PUMP_DIR。因此在导入PDB时，需要先创建目录。logfile参数用来指定导出操作日志文件。为了对数据库版本为11.2.0.3或以后的11G版本执行full transportable导出，必须使用version参数，并且必须指定为12或更高版本。

full transportable导入操作只有在Oracle 12c中支持，因此目标数据库必须为12c

3.将导出的dump文件传输到目标平台的所选定的目录中，该目录可以被目标数据库所访问在目标数据库中创建目录tts_dump(存储dump文件),tts_datafile(存储数据文件)

SQL> create or replace directory tts_dump as '/tts';

Directory created.

SQL> grant execute,read,write on directory tts_dump to public;

Grant succeeded.


SQL> create or replace directory tts_datafile as '+test/jycs/datafile';

Directory created.

SQL> grant execute,read,write on directory tts_datafile to public;

Grant succeeded.

在目标数据库中执行以下命令来传输dump文件

[oracle@jytest1 tts]$ scp -r oracle@10.138.130.152:/tts/exp_test.dmp /tts/
The authenticity of host '10.138.130.152 (10.138.130.152)' can't be established.
RSA key fingerprint is 92:b7:e1:f5:a4:99:5a:de:d5:d3:f2:25:f7:98:0a:a1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.138.130.152' (RSA) to the list of known hosts.
oracle@10.138.130.152's password:
exp_test.dmp                                                                                                                                                                                              100%   59MB  29.5MB/s   00:02
[oracle@jytest1 tts]$

4.从源平台将所有用户表空间传的相关数据文件输到目标平台的tts_datafile文件，通过dbms_file_transfer.put_file过程来实现。
创建源数据库连接目标数据库的数据链路

SQL> create database link jycs_link
  2  connect to system identified by "xxzx7817600"
  3  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.138.130.175)(PORT = 1521)) )(CONNECT_DATA =(SERVICE_NAME = jycs)))';
Database link created

SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATADG/jyrac/datafile/users.263.930413057                                       USERS
+DATADG/jyrac/datafile/undotbs1.262.930413057                                    UNDOTBS1
+DATADG/jyrac/datafile/sysaux.258.930413055                                      SYSAUX
+DATADG/jyrac/datafile/system.259.930413057                                      SYSTEM
+DATADG/jyrac/datafile/example.260.930413057                                     EXAMPLE
+DATADG/jyrac/datafile/undotbs2.261.930413057                                    UNDOTBS2
+DATADG/jyrac/datafile/test01.dbf                                                TEST
7 rows selected

需要传输的数据文件为test01.dbf,example.260.930413057与users.263.930413057

SQL> exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'test01.dbf',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'test01.dbf',destination_database => 'jypdb_link');
PL/SQL procedure successfully completed

SQL> exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'example.260.930413057',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'example01.dbf',destination_database => 'jypdb_link');
PL/SQL procedure successfully completed

SQL> exec dbms_file_transfer.put_file(source_directory_object =>'TTS_DATAFILE',source_file_name => 'users.263.930413057',destination_directory_object => 'TTS_DATAFILE',destination_file_name => 'users01.dbf',destination_database => 'jypdb_link');
PL/SQL procedure successfully completed

在目标数据库的ASM磁盘组可以看到相关的数据文件

ASMCMD [+test/jycs/datafile] > ls -lt
Type      Redund  Striped  Time             Sys  Name
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  N    users01.dbf => +TEST/jycs/DATAFILE/FILE_TRANSFER.281.945022371
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  N    test01.dbf => +TEST/jycs/DATAFILE/FILE_TRANSFER.279.945022099
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  N    example01.dbf => +TEST/jycs/DATAFILE/FILE_TRANSFER.280.945022161
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  Y    FILE_TRANSFER.281.945022371
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  Y    FILE_TRANSFER.280.945022161
DATAFILE  MIRROR  COARSE   MAY 26 18:00:00  Y    FILE_TRANSFER.279.945022099
DATAFILE  MIRROR  COARSE   MAY 26 06:00:00  Y    SYSAUX.260.942323941
DATAFILE  MIRROR  COARSE   MAY 20 22:00:00  Y    UNDOTBS1.259.942323977
DATAFILE  MIRROR  COARSE   MAY 11 12:00:00  Y    SYSTEM.269.942323889
DATAFILE  MIRROR  COARSE   MAY 11 00:00:00  Y    UNDOTBS2.266.942324411
DATAFILE  MIRROR  COARSE   MAY 02 11:00:00  Y    USERS.258.942323981

5.可选操作，将源数据库中的所有用户表空间设置为读写模式

SQL> alter tablespace users read write;

Tablespace altered.

SQL> alter tablespace test read write;

Tablespace altered.

SQL> alter tablespace example read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
UNDOTBS2                       ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE

8 rows selected.

5.在目标数据库上执行数据库导入

[oracle@jytest1 admin]$ impdp jy/jy@JYPDB_175 dumpfile=exp_test.dmp directory=TTS_DUMP transport_datafiles='+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf','+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf' logfile=import.log

Import: Release 12.2.0.1.0 - Production on Fri May 26 20:18:03 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "JY"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "JY"."SYS_IMPORT_TRANSPORTABLE_01":  jy/********@JYPDB_175 dumpfile=exp_test.dmp directory=TTS_DUMP transport_datafiles=+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf,+data/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf logfile=import.log
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PLUGTS_FULL/PLUGTS_BLK
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists

ORA-31684: Object type TABLESPACE:"TEMP" already exists

Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
ORA-31685: Object type USER:"SYS" failed due to insufficient privileges. Failing sql is:
 ALTER USER "SYS" IDENTIFIED BY VALUES 'S:0C82FC9FD1570D45359355071D58A402378ABB404B83306BEA34DD19216F;D50A6384B1C2A4CF' TEMPORARY TABLESPACE "TEMP"
.....

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."KU$_EXPORT_USER_MAP"                 5.976 KB      38 rows
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/XMLSCHEMA/XMLSCHEMA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "SYS"."AMGT$DP$AUD$"                        473.3 KB    2931 rows
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$"           6.367 KB      10 rows
. . imported "WMSYS"."E$ENV_VARS"                        5.921 KB       3 rows
. . imported "WMSYS"."E$EVENTS_INFO"                      5.75 KB      12 rows
. . imported "WMSYS"."E$HINT_TABLE"                       9.25 KB      72 rows
. . imported "WMSYS"."E$NEXTVER_TABLE"                   6.265 KB       1 rows
. . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE"         5.875 KB       1 rows
. . imported "WMSYS"."E$WORKSPACES_TABLE"                14.51 KB       1 rows
. . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE"            6.851 KB       8 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$"             0 KB       0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$"               0 KB       0 rows
. . imported "SYS"."NET$_ACL"                                0 KB       0 rows
. . imported "SYS"."WALLET$_ACL"                             0 KB       0 rows
. . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES"           0 KB       0 rows
. . imported "WMSYS"."E$CONSTRAINTS_TABLE"                   0 KB       0 rows
. . imported "WMSYS"."E$CONS_COLUMNS"                        0 KB       0 rows
. . imported "WMSYS"."E$INSTEADOF_TRIGS_TABLE"               0 KB       0 rows
. . imported "WMSYS"."E$LOCKROWS_INFO"                       0 KB       0 rows
. . imported "WMSYS"."E$MODIFIED_TABLES"                     0 KB       0 rows
. . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE"           0 KB       0 rows
. . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE"          0 KB       0 rows
. . imported "WMSYS"."E$NESTED_COLUMNS_TABLE"                0 KB       0 rows
. . imported "WMSYS"."E$REMOVED_WORKSPACES_TABLE"            0 KB       0 rows
. . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE"            0 KB       0 rows
. . imported "WMSYS"."E$RIC_LOCKING_TABLE"                   0 KB       0 rows
. . imported "WMSYS"."E$RIC_TABLE"                           0 KB       0 rows
. . imported "WMSYS"."E$RIC_TRIGGERS_TABLE"                  0 KB       0 rows
. . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS"               0 KB       0 rows
. . imported "WMSYS"."E$UDTRIG_INFO"                         0 KB       0 rows
. . imported "WMSYS"."E$VERSION_TABLE"                       0 KB       0 rows
. . imported "WMSYS"."E$VT_ERRORS_TABLE"                     0 KB       0 rows
. . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE"          0 KB       0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
ORA-31693: Table data object "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-22303: type "SYS"."JDM_STR_VALS" not found
ORA-21700: object does not exist or is marked for delete

. . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT"       5.859 KB       2 rows
. . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT"              0 KB       0 rows
. . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP"               0 KB       0 rows
. . imported "WMSYS"."E$EXP_MAP"                             0 KB       0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/PROCEDURE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_SPEC
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/OPTION_PACKAGE/PACKAGE_BODY
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PROCACT_INSTANCE
ORA-39082: Object type PACKAGE BODY:"SYS"."WWV_DBMS_SQL" created with compilation warnings
......

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_WORKSHEET_EXPR" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_WORKSHEET_FORM" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_WORKSHEET_STANDARD" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_FLOW_XLIFF" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_META_CLEANUP" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_ACC_LOAD" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRMMENU_LOAD_XML" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_LOAD_XML" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_OLB_LOAD_XML" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_UPDATE_APX_APP" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_FRM_UTILITIES" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_MIG_RPT_LOAD_XML" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_CALENDAR2" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_CHART2" created with compilation warnings

ORA-39082: Object type PACKAGE BODY:"APEX_030200"."WWV_RENDER_REPORT3" created with compilation warnings

ORA-39082: Object type TRIGGER:"APEX_030200"."WWV_BIU_FLOW_SESSIONS" created with compilation warnings

Job "JY"."SYS_IMPORT_TRANSPORTABLE_01" completed with 536 error(s) at Fri May 26 20:45:45 2017 elapsed 0 00:27:38

检查表空间及其状态

SQL> select file_name,tablespace_name from dba_data_files;
FILE_NAME                                                                        TABLESPACE_NAME
-------------------------------------------------------------------------------- ------------------------------
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/system.274.939167015          SYSTEM
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/sysaux.275.939167015          SYSAUX
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undotbs1.273.939167015        UNDOTBS1
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undo_2.277.939167063          UNDO_2
+DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/users01.dbf                   USERS
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/testtb01.dbf                  TESTTB
+DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/example01.dbf                 EXAMPLE
+DATA/jy/4B2C6373AE2547CCE053AB828A0A7CA3/datafile/test01.dbf                    TEST
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undotbs2.278.945029905        UNDOTBS2
9 rows selected

SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
UNDO_2                         ONLINE
USERS                          ONLINE
TESTTB                         ONLINE
TEMP2                          ONLINE
TEMP3                          ONLINE
EXAMPLE                        ONLINE
TEST                           ONLINE
UNDOTBS2                       ONLINE
12 rows selected

对于要传输整个数据库来说，使用full transportable export /import这种方法要比传输表空间方便很多。

这篇文章主要介绍dbms_monitor包中新增加的跟踪方法。新增加的跟踪过程可以基于特定的客户端标识或服务名，模块名与操作名的组合来启用诊断与工作量管理。跟踪也可以在会话级别启用。在有些情况下可以产生多个跟踪文件（例如，当对一个模块跟踪服务级别时）。trcsess工具可以用来扫描所有跟踪文件并将它们合并成一个跟踪文件。在合并之后可以使用标准跟踪文件分析方法比如tkprof。对客户端标识或服务/模块/操作的跟踪状态是永久的可以跨会话的断开与数据库的关闭，并且可以应用于所有实例。跟踪直到使用dbms_monitor禁用之前都是启用状态。

如何查看是否启用跟踪
当客户端与服务/模块/操作跨会话断开与数据库关闭永久存在时，有一种方法来判断是否启用了跟踪。当启用跟踪时，跟踪信息会被记录到dba_enabled_traces中。

SQL> select trace_type, primary_id, qualifier_id1, waits, binds from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID                              QUALIFIER_ID1                   WAITS BINDS
--------------------- --------------------------------------- ------------------------------- ----- -----
SERVICE_MODULE        SYS$USERS                               SQL*Plus                       TRUE  FALSE
CLIENT_ID             HUGO                                                                   TRUE  FALSE
SERVICE               v101_DGB                                                               TRUE  FALSE  

可以看到三个不同的跟踪被启用。
第一行:跟踪所有由SQL*Plus所执行的SQL语句
第二行：跟踪所有客户端标识符为’HUGO’的所有会话
第三行:跟踪通过服务’v101_DGB’连接到数据库的所有程序

session_trace_enable函数
session_trace_enable对本地实例的指定会话启用SQL跟踪,语法如下:
启用跟踪

dbms_monitor.session_trace_enable(session_id=>x,serial_num=>y,waits=>(TRUE|FALSE),binds=>(TRUE|FALSE));

禁用跟踪

dbms_monitor.session_trace_disable(session_id=>x,serial_num=>y);

缺省情况下跟踪对于等待为true，而绑定变量为false

通过查询v$session得到会话与serial号

SQL> select sid, serial#,username from  v$session;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
         2       3257 INSUR_CHANGDE
         4      45331
        41      20923 INSUR_CHANGDE
        42      19225 INSUR_CHANGDE
        77          1
        78       2191 CARD_DB
       115          1
       118      47221 YBCX
       153          1
       157      25173 INSUR_CHANGDE
       191          1

执行下面的命令开始跟踪

SQL> execute dbms_monitor.session_trace_enable(157,25173);

PL/SQL procedure successfully completed.

需要注意的是在dba_enabled_traces视图中没有记录，因为跟踪并没有经历数据库关闭。

可以通过查询v$session得到被跟踪会话列表:

SQL> select sid,serial#,username,sql_trace_waits,sql_trace_binds,sql_trace_plan_stats
  2  from   v$session 
  3  where  sql_trace = 'enabled'; 

no rows selected

当会话断羡慕或执行以下命令可以停止跟踪

SQL> execute dbms_monitor.session_trace_disable(157,25173);

PL/SQL procedure successfully completed.

client_id_trace_enable函数
在多层架构环境中，来自终端用户的一个请求将会通过中间层被路由到不同的数据库会话。这意味着在终端客户端与数据库会话之间不存在静态关联。10g之前的版本，没有一种简单方法来跨不同数据库会话对客户端进行跟踪。通过引入新的属性client_identifier使用端对端的跟踪成为可能，它用来唯一标识一个指定的终端。客户端标识被记录在v$session视图的client_identifier列中。还可以通过系统上下文来查看。语法如下:
启用跟踪

execute dbms_monitor.client_id_trace_enable ( client_id =>'client_id', waits => (TRUE|FALSE), binds => (TRUE|FALSE) );

禁用跟踪

execute dbms_monitor.client_id_trace_disable ( client_id =>'client_id');

缺省情况下跟踪对于等待为true，绑定变量为false

下面通过使用dbms_session.set_identifier过程来设置client_identifier

SQL> execute dbms_session.set_identifier('JY');

PL/SQL procedure successfully completed.

可以通过两种方法来找到客户端标识
1.在实际会话中

SQL> select sys_context('USERENV','CLIENT_IDENTIFIER') client_id from dual;

CLIENT_ID
--------------------------------------------------------------------------------
JY

2.从不同的会话中

SQL> select client_identifier client_id from v$session where sid =18;
CLIENT_ID
----------------------------------------------------------------
JY

对客户端标识为’JY’的所有会话启用跟踪

SQL> execute dbms_monitor.client_id_trace_enable('JY');

PL/SQL procedure successfully completed.

现在这个跟踪可以跨越数据库的关闭

SQL>  select trace_type, primary_id, qualifier_id1, waits, binds from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID                                                       QUALIFIER_ID1                                                    WAITS BINDS
--------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----
CLIENT_ID             JY                                                                                                                                TRUE  FALSE

为了禁用跟踪，执行以下命令:

SQL> execute dbms_monitor.client_id_trace_disable('JY');

PL/SQL procedure successfully completed.


SQL>  select trace_type, primary_id, qualifier_id1, waits, binds from dba_enabled_traces;
TRACE_TYPE            PRIMARY_ID                                                       QUALIFIER_ID1                                                    WAITS BINDS
--------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----

这可能造成有时生成多个跟踪文件。例如，当使用共享服务器时，不同的共享服务器进程可以执行SQL语句。这将导致生成多个跟踪文件。对于RAC来说也同样会生成多个跟踪文件。后面将介绍如何使用trcsess工具将多个跟踪文件合并成一个跟踪文件。

SERV_MOD_ACT_TRACE_ENABLE函数
端到端的跟踪对于有效管理与使用services，module与action来计算应用程序工作量很有用。可以使用serv_mod_act_trace_enable函数来对服务名，模块名与操作名特定的组合对全局或特定实例启用SQL跟踪。

通过查询v$session视图的service_name,module和action列可以查看服务名，模块与操作名。语法如下:
启用跟踪

execute dbms_monitor.serv_mod_act_trace_enable('Service1', 'Module1', 'Action1', waits => (TRUE|FALSE), binds => (TRUE|FALSE), instance_name => 'ORCL' );

禁用跟踪

execute dbms_monitor.serv_mod_act_trace_disable('Service1', 'Module1', 'Action1');

缺省情况下跟踪对于等待为true，对于绑变量为false。缺省实例名为null。

示例
下面跟踪通过SQL*Plus与缺省服务SYS$USERS所执行的所有SQL语句

SQL> execute dbms_monitor.serv_mod_act_trace_enable('SYS$USERS', 'SQL*Plus' );

PL/SQL procedure successfully completed.

检查是否启用跟踪

SQL> select primary_id, qualifier_id1, waits, binds from  dba_enabled_traces where trace_type = 'SERVICE_MODULE';
PRIMARY_ID                                                       QUALIFIER_ID1                                                    WAITS BINDS
---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----
SYS$USERS                                                        SQL*Plus                                                         TRUE  FALSE

禁用跟踪

SQL> execute dbms_monitor.serv_mod_act_trace_disable('SYS$USERS', 'SQL*Plus');

PL/SQL procedure successfully completed.


SQL> select primary_id, qualifier_id1, waits, binds from  dba_enabled_traces where trace_type = 'SERVICE_MODULE';
PRIMARY_ID                                                       QUALIFIER_ID1                                                    WAITS BINDS
---------------------------------------------------------------- ---------------------------------------------------------------- ----- -----

使用trcsess合并跟踪文件
有些跟踪操作会生成多个跟踪文件。 trcsess可以根据特定会话或客户端标识来合并跟踪文件。
语法如下:

trcsess [output=
] [session=] [clientid=] [service=] [action=] [module=] 

会话1：

SQL> execute dbms_session.set_identifier('JY');

PL/SQL procedure successfully completed.


SQL> execute dbms_monitor.client_id_trace_enable('JY');

PL/SQL procedure successfully completed.

SQL> select 'session 1' from dual;

'SESSION1
---------
session 1

SQL> execute dbms_monitor.client_id_trace_disable('JY');

PL/SQL procedure successfully completed.

会话2：

SQL> execute dbms_session.set_identifier('JY');

PL/SQL procedure successfully completed.

SQL> execute dbms_monitor.client_id_trace_enable('JY');

PL/SQL procedure successfully completed.

SQL> select 'session 2' from dual;

'SESSION2
---------
session 2

SQL> execute dbms_monitor.client_id_trace_disable('JY');

PL/SQL procedure successfully completed.

使用trcsess合并跟踪文件

[oracle@jyrac1 trace]$ trcsess output=trcsess_Jy_Trace.txt clientid='JY'  *.trc
[oracle@jyrac1 trace]$ ls -lrt *Jy*.txt
-rw-r--r-- 1 oracle oinstall 97786 Mar  2 15:17 trcsess_Jy_Trace.txt

dbms_application_info
dbms_application_info.set_x_info过程在会话开始前调用可以用来注册并命名事务/客户端信息/模块为以后的性能检查所使用。
dbms_application_info包含以下过程:
set_client_info(client_info in varchar2)
set_action(action_name in varchar2)
set_module(module_name in varchar2,action_name in varchar2)

SQL> begin
  2  dbms_application_info.set_module(module_name => 'add_employee',action_name => 'insert into emp');
  3  insert into scott.emp (ename, empno, sal, mgr, job, hiredate, comm, deptno )
  4  values ( 'scott', 9998, 1000, 7698,'clerk', sysdate,0, 10);
  5  dbms_application_info.set_module(null,null); 
  6  end;
  7  /

PL/SQL procedure successfully completed.

下面通过使用module与action列作为查询条件来查询v$sqlarea视图来获得上面执行的SQL语句

SQL> select sql_text from v$sqlarea where module = 'add_employee' and action = 'insert into emp';

SQL_TEXT
--------------------------------------------------------------------------------
INSERT INTO SCOTT.EMP (ENAME, EMPNO, SAL, MGR, JOB, HIREDATE, COMM, DEPTNO ) VAL
UES ( 'scott', 9998, 1000, 7698,'clerk', SYSDATE,0, 10)

也可以执行以下过程来获得信息

SQL> set serveroutput on
SQL> declare
  2  l_clinent varchar2(100);
  3  l_mod_name varchar2(100);
  4  l_act_name varchar2(100);
  5  begin
  6  dbms_application_info.set_client_info('my client');
  7  dbms_application_info.read_client_info(l_clinent);
  8  dbms_output.put_line('client='||l_clinent);
  9  dbms_application_info.set_module('my mod','inserting');
 10  dbms_application_info.read_module(l_mod_name,l_act_name);
 11  dbms_output.put_line('mod_name='||l_mod_name);
 12  dbms_output.put_line('act_name='||l_act_name);
 13  end;
 14  /
client=my client
mod_name=my mod
act_name=inserting
PL/SQL procedure successfully completed

重命名与迁移联机数据文件
在12C中可以使用alter database move datafile语句来重命名或迁移联机数据文件。当数据库处于open状态，并且用户正在访问数据文件时仍然能够重命名与迁移联机数据文件。

当你重命名与迁移联机数据文件时，在控制文件中所记录指向数据文件的指针会被改变。数据文件会在操作系统层面被重命名与迁移。

当执行以下任务时因为需要允许用户访问数据文件所以需要对联机数据文件执行重命名与迁移:
.将数据文件从一种类型的存储迁移到另一种类型存储上。
.将不频繁访问的数据文件迁移到低成本存储上。
.将表空间置为只读并将它的数据文件写入存储设备上
.将数据库迁移到Oracle ASM中

当执行alter database move datafile语句时，如果在目标目录中存在相同的文件，可以指定reuse选项来覆盖现有文件。当没有指定reuse选项时，如果在目标目录中存在相同文件时，现有文件不会被覆盖并且语句会返回错误信息。

缺省情况下，当执行alter database move datafile语句并指定新目录时，语句会移动数据文件到新目录中。然而可以指定keep选项来保留旧目录中的数据文件。在这种情况下当语句完成后数据库只会使用新目录中的数据文件。

使用alter database move datafile语句来重命名或迁移数据文件时，Oracle数据库会创建一份数据文件副本。确保对于有足够的空间来执行此操作。

注意:如果指定的数据文件处于脱机状态那么执行alter database move datafile语句将会触发错误,如果配置了备库，那么对于主库和备库所执行的联机数据文件迁移操作是相互独立的。当对主库移动数据文件时备库不受影响，反之一样。闪回操作不会将被移动的数据文件重新迁移回之前的目录中。如果将一个联机数据文件从一个目录移动到另一个目录，之后执行闪回数据库操作将其闪回到数据文件迁移之前的时间点，那么数据文件仍然会存储在新目录中，但数据文件的内容将会回到闪回操作所指定的时间点。当在Windows平台上执行数据文件迁移时，就算没有指定keep选项，原始数据文件也会保留在旧目录中。在这种情况下，当迁移操作完成之后，数据库就只会使用新目录中的数据文件。如果需要这时可以使用手动删了旧数据文件。

重命名联机数据文件

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/system.274.939167015
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/sysaux.275.939167015
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undotbs1.273.939167015
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undo_2.277.939167063
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/users.278.939167083
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/testtb1.dbf

6 rows selected.

SQL> alter database move datafile '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/testtb1.dbf' to '+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/testtb01.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------------------------
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/system.274.939167015
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/sysaux.275.939167015
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undotbs1.273.939167015
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/undo_2.277.939167063
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/users.278.939167083
+DATA/JY/4B2C6373AE2547CCE053AB828A0A7CA3/DATAFILE/testtb01.dbf

6 rows selected.

迁移联机数据文件

SQL>ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '/u02/oracle/rbdb1/user1.dbf';

复制联机数据文件

SQL>ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '/u02/oracle/rbdb1/user1.dbf' KEEP;

迁移联机数据文件并覆盖所存在的数据文件

SQL>ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '/u02/oracle/rbdb1/user1.dbf' REUSE;

迁移联机数据文件到ASM磁盘组

SQL>ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '+dgroup_01/data/orcl/datafile/user1.dbf';

将联机数据文件从一个ASM磁盘组迁移到另一个ASM磁盘组中

SQL>ALTER DATABASE MOVE DATAFILE '+dgroup_01/data/orcl/datafile/user1.dbf' TO '+dgroup_02/data/orcl/datafile/user1.dbf';