IT博客汇 | 升级到12c，你应该了解的一些东西

###########################
#
# (1) 初始化参数设置的建议
#
###########################
(1.1) sec_case_sensitive_loacgon
在12.1.0.1中不建议修改。默认值是true，但是如果设置成false，启动的时候会有提示：ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance。但是设置成false仍然生效，即忽略大小写的密码可以登录。

(1.2) SQLNET.ALLOWED_LOGON_VERSION_SERVER
在12c中默认值是11，所以如10.2.0.5的JDBC连接过来，就会报错ora-28040，虽然可以设置SQLNET.ALLOWED_LOGON_VERSION_SERVER=8来解决这个问题，但是由于今后11.1以下版本的JDBC不再被oracle支持，因此还是建议升级JDBC驱动来实现。
jar file和JDBC驱动之间的关系，参考Doc ID 401934.1。 10.2版本的JDBC，使用ojdbc14.jar；11.2版本的JDBC，使用ojdbc6.jar；12.1.0的JDBC使用ojdbc7.jar。

SQLNET.ALLOWED_LOGON_VERSION_SERVER 的值有{ 8 | 10 | 11 | 12 | 12a }，各个值如下含义：
12a for Oracle Database 12c authentication protocols (strongest protection)
12 for the critical patch updates CPUOct2012 and later Oracle Database 11g ,authentication protocols (recommended)
11 for Oracle Database 11g authentication protocols (default)
10 for Oracle Database 10g authentication protocols
8 for Oracle9i authentication protocol
默认值是11，推荐值是12（如果你没有小于10.2.0.5的客户端）

(1.3) AUDIT_TRAIL
取值范围是: { none | os | db [, extended] | xml [, extended] }，默认值是db或者none。
和12c的新特性Unified Auditing有关。是否启用unified auditing，可以用select VALUE from V$OPTION where PARAMETER='Unified Auditing';检查。

Unified Auditing默认是工作在mixed模式。
建议：
如果你以前没有审计，那么你可以设置成none。
如果以前有审计，建议设置成db。
更多信息，参考 http://tinyurl.com/UnifiedAuditing

(1.4)DEFERRED_SEGMENT_CREATION
从11.2开始，这个值的默认值是true，建议12c中设置成false。

(1.5)JOB_QUEUE_PROCESSES
从11.1开始，这个值是1000，建议设置成和CPU core相等的值。

(1.6)_DATAFILE_WRITE_ERRORS_CRASH_INSTANCE
这个值默认值是true。所有datafile的IO写error，都会导致数据库crash。
在11.2.0.2之前这个值是false，即只是offline datafile（非system），而不crash instance。11.2.0.2之后，是true。
建议：注意这个从11.2.0.2之后的变化。

(1.7) MAX_STRING_SIZE
这是在12c中的新参数。默认值是standard。
这个参数用于控制varchar2，nvarchar2，raw类型的最大值。standard下，行为和12c之前一样，即varchar2和nvarchar2是4000 bytes，raw是2000 bytes，
改成extended之后，启用了32k strings新特性，varchar2、nvarchar2、raw最大长度可以达到32767 bytes。
修改方式：
1. startup upgrade
2. ALTER SYSTEM SET MAX_STRING_SIZE = EXTENDED;
3.运行@?/rdbms/admin/utl32k.sql
但是注意，单向修改，改了之后，就改不回来了。（可以flashback）

注意原生32k strings是以out-of-line的blob方式存储，且还是basic file，而如果是以modify成32k strings的是以in-line的方式行链接存储。
所以转32k strings需要考虑lob效率的问题。

###########################
#
# (2)其他在12c中的参数：
#
###########################

(2.1) _OPTIMIZER_AGGR_GROUPBY_ELIM
Values: { TRUE | FALSE } 默认值是true
Recommendation: FALSE - Wrong Results with GROUP BY Clause in Nested Query (Doc ID 19567916.8)
建议值：false

(2.2) INMEMORY_FORCE
Values: { DEFAULT | OFF } 默认值是default
Explanation: In-Memory Optimization
Recommendation: OFF – Unless you have an Oracle In-Memory license
建议值为off，除非你有in-memory的license

(2.3) OPTIMIZER_DYNAMIC_SAMPLING
如果统计信息不存在，进行动态采样的比例。
Values: { 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 } 默认值是2
解释:
a.0: Off
b.2: Check 不多于64个blocks - generate stats during parse
c.11:12c的新增加值，进行动态采样的block数由系统自动决定，采样结果会保留在statistics repository，供下次使用。
建议值：参考在线文档SQL Tuning Guide，最佳实践是在session级设置。

(2.4) AWR Lite Snapshots
参考Doc ID 1993045.1，建议在手工snapshot的时候，用lite模式
--默认值是bestfit
_AWR_SNAPSHOT_LEVEL = BASIC | LITE | TYPICAL | ALL | BESTFIT
--手工snapshot的时候，建议lite
SQL> exec dbms_workload_repository.create_snapshot('LITE');

(2.5) _OPTIMIZER_COST_BASED_TRANSFORMATION
默认值是on，从10.2开始，默认值就是on了，但是在11.2.0.3之前，建议设置off

(2.6) SESSION_CACHED_CURSORS
默认值是50，建议设置成200，然后按照208857.1进行调整。
太大容易有shared pool碎片

(2.7) _MEMORY_IMM_MODE_WITHOUT_AUTOSGA
默认值是true，如果需要禁用偷buffer cache的属性，设置成false

(2.8) OPTIMIZER_USE_PENDING_STATISTICS
默认值是false，可以在session级设置成true后，测试新的但是还没发布的统计信息，对sql的影响。

(2.9)OPTIMIZER_USE_INVISIBLE_INDEXES
是否让优化器看见invisible的索引，默认值是false
ALTER INDEX idx_ename ON emp(ename) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;

(2.10) 其他建议值：
a. _optimizer_adaptive_plans=FALSE （需要评估，Adaptive Query Optimization Doc ID 2031605.1）
b. _optimizer_unnest_scalar_sq=FALSE （Bug 19894622 - ORA-600 [kkqcsfixfro:1 -- frooutj] error occur in 12c (Doc ID 19894622.8)）
c. _rowsets_enabled=FALSE （Bug 22173980 : WRONG RESULTS WHEN "_ROWSETS_ENABLED" = TRUE）
d. _optimizer_reduce_groupby_key=FALSE （Bug 20634449 - Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause in 12.1.0.2）
e. _kks_obsolete_dump_threshold=0 or 8（Huge Trace Files Created Containing "----- Cursor Obsoletion Dump sql_id=%s -----" (Doc ID 1955319.1) ，该参数取值范围是0～8，默认值是1。设置0表示永远不dump，设置8表示parent cursor obsoleted 8次之后才dump。）

###########################
#
# (3)Data Pump的一些参数：
#
###########################
(3.1)VIEWS_AS_TABLES 参数，可以将view导出成表。
SQL> create table t1 as select * from dba_users;
SQL> create view v_t1 as select * from dba_users where user_id<=200;

--我们将视图v_t1导出来:
C:\Users\jijihe>expdp test/test VIEWS_AS_TABLES=test.v_t1 DUMPFILE=test_dump_view.dmp DIRECTORY=DATA_PUMP_DIR

Export: Release 12.1.0.2.0 - Production on Fri Jul 1 17:51:25 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** VIEWS_AS_TABLES=test.v_t1 DUMPFILE=test_dump_view.dmp DIRECTORY=DATA_PUMP_DIR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "TEST"."V_T1" 14.73 KB 15 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
E:\ORA12C\APP\ORACLEUSER\ADMIN\ORA12C\DPDUMP\TEST_DUMP_VIEW.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Fri Jul 1 17:51:30 2016 elapsed 0 00:00:05

--我们将v_t1的内容导入进去，导成表t1：
C:\Users\jijihe>impdp test/test remap_table=v_t1:t1 DUMPFILE=test_dump_view.dmp DIRECTORY=DATA_PUMP_DIR

Import: Release 12.1.0.2.0 - Production on Fri Jul 1 18:00:02 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** remap_table=v_t1:t1 DUMPFILE=test_dump_view.dmp DIRECTORY=DATA_PUMP_DIR
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "TEST"."T1" 14.73 KB 15 rows
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Fri Jul 1 18:00:03 2016 elapsed 0 00:00:01

C:\Users\jijihe>

(3.2)导入时，TRANSFORM 参数
(3.2.1)TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y，也可以TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE，或者TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX
会在导入时候，不写导入table的日志，或者不写导入index的日志，或者都不写日志。（注意，这个参数只是减少些日志，并不是完全不写日志）

--导出表：
C:\Users\jijihe>expdp test/test TABLES=t1 DUMPFILE=test_dump_t1.dmp DIRECTORY=DATA_PUMP_DIR

Export: Release 12.1.0.2.0 - Production on Mon Jul 11 14:20:26 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** TABLES=t1 DUMPFILE=test_dump_t1.dmp DIRECTORY=DATA_PUMP_DIR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 183 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TEST"."T1" 154.5 MB 1245184 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
E:\ORA12C\APP\ORACLEUSER\ADMIN\ORA12C\DPDUMP\TEST_DUMP_T1.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jul 11 14:20:33 2016 elapsed 0 00:00:06

C:\Users\jijihe>

--导入，不写table和index的日志：
C:\Users\jijihe>impdp test/test directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

Import: Release 12.1.0.2.0 - Production on Mon Jul 11 14:39:20 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T1" 154.5 MB 1245184 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 11 14:39:25 2016 elapsed 0 00:00:05

--导入，不设置，默认状态下会写日志：
C:\Users\jijihe>impdp test/test directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Mon Jul 11 14:40:00 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T1" 154.5 MB 1245184 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 11 14:40:11 2016 elapsed 0 00:00:10

--导入，不写table的日志：
C:\Users\jijihe>impdp test/test directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE

Import: Release 12.1.0.2.0 - Production on Mon Jul 11 14:41:44 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T1" 154.5 MB 1245184 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 11 14:41:50 2016 elapsed 0 00:00:06

--导入，不写index的日志：
C:\Users\jijihe>impdp test/test directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX

Import: Release 12.1.0.2.0 - Production on Mon Jul 11 14:43:24 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T1" 154.5 MB 1245184 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 11 14:43:40 2016 elapsed 0 00:00:15

-- 各个情况产生redo的情况对比：
END_INTERVAL_TIME DELTA_TIME_MIN DELTA_VALUE REDO_MB_PER_MIN
-------------------------------------- -------------- ----------- ---------------
2016-07-11 14:10:39 10.0333333 1838192 .17
2016-07-11 14:20:41 10.0333333 444928412 42.29
2016-07-11 14:30:43 10.0333333 4660284 .44
2016-07-11 14:38:30 7.78333333 2000116 .25
2016-07-11 14:39:30 1 4981120 4.75 << TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
2016-07-11 14:39:48 .3 1942548 6.18
2016-07-11 14:40:18 .5 213224196 406.69 << 不设置
2016-07-11 14:41:37 1.31666667 2180120 1.58
2016-07-11 14:42:02 .416666667 25733268 58.9 << TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE
2016-07-11 14:43:03 1.01666667 1863552 1.75
2016-07-11 14:43:45 .7 191931064 261.49 << DISABLE_ARCHIVE_LOGGING:Y:INDEX

(3.2.2)TRANSFORM=LOB_STORAGE:SECURE_FILE
这个参数的作用是，将basic file的lob，导入时转换成securefile。
在12c中，DB_SECUREFILE = { NEVER | PERMITTED | PREFERRED | ALWAYS | IGNORE }为这几个取值，在COMPATIBLE 为12.0.0.0以上的情况下，DB_SECUREFILE值为PREFERRED，即在默认情况下，创建的lob是security file。如果要basic file，那么需要手工指定。
注：DB_SECUREFILE值为PREFERRED，也是12c中的改变，在12c之前没有。

11g：
SQL> CREATE TABLE t1 (a CLOB);
SQL> select table_name, securefile from user_lobs where table_name='T1';

TABLE_NAME SECURE
------------------------------------------------------------ ------
T1 NO

SQL> insert into t1 select rpad('x',99999999999,'Y') from dual connect by level<=100;
SQL> commit;

--把11g中的这个表导出，注意securefile是no的；
C:\Users\jijihe>expdp test/test TABLES=t1 DUMPFILE=test_dump_t1.dmp DIRECTORY=DATA_PUMP_DIR

Export: Release 11.2.0.4.0 - Production on Mon Jul 11 15:31:04 2016

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, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** TABLES=t1 DUMPFILE=test_dump_t1.dmp DIRECTORY=DATA_PUMP_DIR
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1024 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."T1" 786.7 KB 100 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
E:\ORA11G\APP\ORACLEUSER\ADMIN\ORA11G\DPDUMP\TEST_DUMP_T1.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jul 11 15:31:12 2016 elapsed 0 00:00:08

12c：
--导入到12c中，我们不加TRANSFORM=LOB_STORAGE:SECURE_FILE参数。可以看到，导入后，我们可以看到，这个表的securefile属性还是和11g一样。即还是basic file。
C:\Users\jijihe>impdp test/test directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Mon Jul 11 15:35:24 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T1" 786.7 KB 100 rows
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 11 15:35:27 2016 elapsed 0 00:00:02

SQL> select table_name, securefile from user_lobs where table_name='T1';

TABLE_NAME SECURE
------------------------------ ------
T1 NO

SQL>

--导入到12c中，我们加TRANSFORM=LOB_STORAGE:SECURE_FILE参数:
C:\Users\jijihe>impdp test/test directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace TRANSFORM=LOB_STORAGE:SECUREFILE

Import: Release 12.1.0.2.0 - Production on Mon Jul 11 15:45:55 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace TRANSFORM=LOB_STORAGE:SECUREFILE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T1" 786.7 KB 100 rows
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 11 15:45:56 2016 elapsed 0 00:00:01

C:\Users\jijihe>sqlplus test/test

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jul 11 15:46:02 2016

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

Last Successful login time: Mon Jul 11 2016 15:45:55 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> col table_name for a30
SQL> set line 1000
SQL> select table_name, securefile from user_lobs where table_name='T1';

TABLE_NAME SECURE
------------------------------ ------
T1 YES

SQL>

(3.2.3)TRANSFORM=STORAGE:N
这个参数的作用是，在导入的时候，忽略原库中DDL中的storage的设置。类似用dbms_metadata.get_ddl获取DDL语句之前，执行EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);从而忽略storage参数。

--创建表，然后导出，注意我们的storage参数中buffer_pool是keep:
SQL> CREATE TABLE T1
2 (a varchar2(20))
3 STORAGE(BUFFER_POOL keep);

Table created.

SQL>

--默认情况下，不做transform导入：
C:\Users\jijihe>impdp test/test directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Mon Jul 11 16:33:54 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T1" 5.046 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 11 16:34:06 2016 elapsed 0 00:00:12

--检查，可以看到，buffer pool也是keep，即是原来一样。
SQL> select dbms_metadata.get_ddl('TABLE','T1','TEST')FROM dual;

CREATE TABLE "TEST"."T1"
   ( "A" VARCHAR2(20)
   ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL KEEP FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

SQL>

--采用TRANSFORM=STORAGE:N参数进行导入：
C:\Users\jijihe>impdp test/test directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace TRANSFORM=STORAGE:N

Import: Release 12.1.0.2.0 - Production on Mon Jul 11 16:35:50 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace TRANSFORM=STORAGE:N
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T1" 5.046 KB 1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Mon Jul 11 16:35:51 2016 elapsed 0 00:00:01

--可以看到storage中的buffer pool恢复成了默认值default：
SQL> select dbms_metadata.get_ddl('TABLE','T1','TEST')FROM dual;

CREATE TABLE "TEST"."T1"
   ( "A" VARCHAR2(20)
   ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"

(3.2.4)TRANSFORM=TABLE_COMPRESSION:<compression_clause>
该参数的作用是，在导入时，即可采用高级压缩或者HCC压缩的属性。
--常规的导入：
C:\Users\jijihe>impdp test/test directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Wed Jul 13 13:37:15 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T1" 3.876 GB 40164544 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Wed Jul 13 13:37:36 2016 elapsed 0 00:00:21

SQL> select sum(bytes)/1024/1024 size_MB from dba_segments where segment_name='T1';

   SIZE_MB
----------
   4603

SQL>
SQL> select table_name,COMPRESSION,COMPRESS_FOR from dba_tables where table_name='T1';

TABLE_NAME COMPRESSION COMPRESS_FOR
-------------------- ---------------- ----------------------------------------------------------
T1 DISABLED

SQL>

--启用压缩导入：
C:\Users\jijihe>impdp test/test directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace TRANSFORM=TABLE_COMPRESSION_CLAUSE:"COMPRESS"

Import: Release 12.1.0.2.0 - Production on Wed Jul 13 13:35:07 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01": test/******** directory=data_pump_dir dumpfile=test_dump_t1.dmp logfile=test_dump_t1.log table_exists_action=replace TRANSFORM=TABLE_COMPRESSION_CLAUSE:COMPRESS
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T1" 3.876 GB 40164544 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "TEST"."SYS_IMPORT_FULL_01" successfully completed at Wed Jul 13 13:36:32 2016 elapsed 0 00:01:24

SQL> select sum(bytes)/1024/1024 size_MB from dba_segments where segment_name='T1';

   SIZE_MB
----------
   1536

SQL> select table_name,COMPRESSION,COMPRESS_FOR from dba_tables where table_name='T1';

TABLE_NAME COMPRESSION COMPRESS_FOR
------------------------------ ---------------- ---------------------------------------------------------
T1 ENABLED BASIC

SQL>

(3.3)LOGTIME=[ NONE | STATUS | LOGFILE | ALL ] parameter
该参数的作用是导入或者导出时打上时间戳。
C:\Users\jijihe>expdp test/test tables=t1 dumpfile=test_dump_t1.dmp directory=data_pump_dir LOGTIME=ALL

Export: Release 12.1.0.2.0 - Production on Wed Jul 13 13:43:08 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
13-JUL-16 13:43:09.952: Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** tables=t1 dumpfile=test_dump_t1.dmp directory=data_pump_dir LOGTIME=ALL
13-JUL-16 13:43:10.055: Estimate in progress using BLOCKS method...
13-JUL-16 13:43:10.247: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
13-JUL-16 13:43:10.277: Total estimation using BLOCKS method: 1.5 GB
13-JUL-16 13:43:12.672: Processing object type TABLE_EXPORT/TABLE/TABLE
13-JUL-16 13:43:12.751: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
13-JUL-16 13:43:12.793: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
13-JUL-16 13:43:56.137: . . exported "TEST"."T1" 3.876 GB 40164544 rows
13-JUL-16 13:43:56.307: Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
13-JUL-16 13:43:56.308: ******************************************************************************
13-JUL-16 13:43:56.309: Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
13-JUL-16 13:43:56.311: E:\ORA12C\APP\ORACLEUSER\ADMIN\ORA12C\DPDUMP\TEST_DUMP_T1.DMP
13-JUL-16 13:43:56.321: Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jul 13 13:43:56 2016 elapsed 0 00:00:47

(3.4)COMPRESSION_ALGORITHM
COMPRESSION_ALGORITHM = [ BASIC | LOW | MEDIUM | HIGH ]，用于控制在导出时的，且启用压缩功能时，CPU的消耗。有如下4个值
a. BASIC 和原来一样
b. LOW 减少CPU使用率
c. MEDIUM 建议值，和basic类似，但是有不同的算法
d. HIGH: 使用更多的CPU用于压缩

--非压缩时：
C:\Users\jijihe>expdp test/test tables=t1 directory=data_pump_dir dumpfile=test_dump_t1.dmp

Export: Release 12.1.0.2.0 - Production on Wed Jul 13 14:04:12 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** tables=t1 directory=data_pump_dir dumpfile=test_dump_t1.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4.498 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TEST"."T1" 3.876 GB 40164544 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
E:\ORA12C\APP\ORACLEUSER\ADMIN\ORA12C\DPDUMP\TEST_DUMP_T1.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jul 13 14:04:42 2016 elapsed 0 00:00:30

C:\Users\jijihe>
C:\Users\jijihe>
C:\Users\jijihe>ls -l E:\ora12c\app\oracleuser\admin\ora12c\dpdump\test_dump_t1.dmp
-rwxrwxrwa 1 Administrators SYSTEM 4162502656 Jul 13 15:04 E:/ora12c/app/oracleuser/admin/ora12c/dpdump/TEST_DUMP_T1.DMP
<图>

--medium压缩时：
C:\Users\jijihe>expdp test/test tables=t1 directory=data_pump_dir dumpfile=test_dump_t1.dmp compression=all COMPRESSION_ALGORITHM=MEDIUM

Export: Release 12.1.0.2.0 - Production on Wed Jul 13 14:12:57 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** tables=t1 directory=data_pump_dir dumpfile=test_dump_t1.dmp compression=all compression_algorithm=medium
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4.498 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TEST"."T1" 590.9 MB 40164544 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
E:\ORA12C\APP\ORACLEUSER\ADMIN\ORA12C\DPDUMP\TEST_DUMP_T1.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jul 13 14:13:40 2016 elapsed 0 00:00:43

C:\Users\jijihe>ls -l E:\ora12c\app\oracleuser\admin\ora12c\dpdump\test_dump_t1.dmp
-rwxrwxrwa 1 Administrators SYSTEM 619708416 Jul 13 15:13 E:/ora12c/app/oracleuser/admin/ora12c/dpdump/TEST_DUMP_T1.DMP
<图>

--high压缩时：
C:\Users\jijihe>expdp test/test tables=t1 directory=data_pump_dir dumpfile=test_dump_t1.dmp compression=all COMPRESSION_ALGORITHM=HIGH

Export: Release 12.1.0.2.0 - Production on Wed Jul 13 13:57:49 2016

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** tables=t1 directory=data_pump_dir dumpfile=test_dump_t1.dmp compression=all compression_algor
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4.498 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TEST"."T1" 461.8 MB 40164544 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
E:\ORA12C\APP\ORACLEUSER\ADMIN\ORA12C\DPDUMP\TEST_DUMP_T1.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jul 13 14:01:13 2016 elapsed 0 00:03:23

C:\Users\jijihe>
C:\Users\jijihe>ls -l E:\ora12c\app\oracleuser\admin\ora12c\dpdump\test_dump_t1.dmp
-rwxrwxrwa 1 Administrators SYSTEM 484368384 Jul 13 15:01 E:/ora12c/app/oracleuser/admin/ora12c/dpdump/TEST_DUMP_T1.DMP

<图>

###########################
#
# (4) 查找补丁信息：
#
###########################
原来是通过dba_registry_history 查询，现在可以通过下面的方法查询：
SQL> exec dbms_qopatch.get_sqlpatch_status;

下面的语句是用来查询inventory的位置：
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY,DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;

查询某个patch是否被安装：
SQL> select xmltransform(DBMS_QOPATCH.IS_PATCH_INSTALLED('19303936 '),DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;

查询所有的patch的情况：
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_LIST,DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;

###########################
#
# (5)升级catalog变化：
#
###########################
RMAN Catalog Upgrade:
– SQL> @$ORACLE_HOME/rdbms/admin/dbmsrmansys.sql <<<<需要先运行这一步（以前没有），再运行upgrade catalog
– $ rman CATALOG my_catalog_owner@catdb
recovery catalog database Password:
RMAN> UPGRADE CATALOG;
RMAN> UPGRADE CATALOG;
RMAN> EXIT;

###########################
#
# (6)增量统计信息收集增强(增量统计信息收集和交换分区相结合)
#
###########################
--先设置使用过期比例USE_STALE_PERCENT。定义“变化”的分区不会收集新的统计信息，除非有xx%百分比的变化。
SQL> exec DBMS_STATS.SET_DATABASE_PREFS('INCREMENTAL_STALENESS','USE_STALE_PERCENT');
--设置过期比例为12%（默认是10%）
SQL> exec DBMS_STATS.SET_DATABASE_PREFS('STALE_PERCENT','12');

###########################
#
# (7)统计信息情况查看：
#
###########################
--查看整个库的统计信息情况:
SQL> variable mystatrep2 clob;
SQL> set long 1000000
SQL> begin
2 :mystatrep2 := DBMS_STATS.REPORT_STATS_OPERATIONS(since=>SYSTIMESTAMP-
3 1,until=>SYSTIMESTAMP, detail_level=>'TYPICAL',format=>'TEXT');
4 end;
5 /

PL/SQL procedure successfully completed.

SQL>
SQL> set serverout on
SQL> set long 999999999
SQL> set line 10000
SQL> set pages 10000
SQL> col mystatrep2 for a200
SQL> print mystatrep2

MYSTATREP2
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Operation Id | Operation | Target | Start Time | End Time | Status | Total Tasks | Successful Tasks | Failed Tasks | Active Tasks |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 366 | export_stats_for_dp | TEST | 13-JUL-16 02.12.59.130000 PM +08:00 | 13-JUL-16 02.12.59.203000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 365 | export_stats_for_dp | TEST | 13-JUL-16 02.08.18.857000 PM +08:00 | 13-JUL-16 02.08.18.880000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 364 | export_stats_for_dp | TEST | 13-JUL-16 02.04.13.425000 PM +08:00 | 13-JUL-16 02.04.13.480000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 363 | export_stats_for_dp | TEST | 13-JUL-16 01.57.50.812000 PM +08:00 | 13-JUL-16 01.57.50.846000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 362 | export_stats_for_dp | TEST | 13-JUL-16 01.56.34.637000 PM +08:00 | 13-JUL-16 01.56.34.667000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 361 | import_stats_for_dp | TEST | 13-JUL-16 01.55.50.018000 PM +08:00 | 13-JUL-16 01.55.50.294000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 360 | export_stats_for_dp | TEST | 13-JUL-16 01.53.54.840000 PM +08:00 | 13-JUL-16 01.53.54.879000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 359 | import_stats_for_dp | TEST | 13-JUL-16 01.52.17.822000 PM +08:00 | 13-JUL-16 01.52.18.059000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 358 | import_stats_for_dp | TEST | 13-JUL-16 01.46.44.289000 PM +08:00 | 13-JUL-16 01.46.44.611000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 357 | export_stats_for_dp | TEST | 13-JUL-16 01.43.12.810000 PM +08:00 | 13-JUL-16 01.43.13.543000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 356 | import_stats_for_dp | TEST | 13-JUL-16 01.40.03.349000 PM +08:00 | 13-JUL-16 01.40.03.650000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 355 | import_stats_for_dp | TEST | 13-JUL-16 01.37.36.601000 PM +08:00 | 13-JUL-16 01.37.36.843000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 354 | import_stats_for_dp | TEST | 13-JUL-16 01.36.32.072000 PM +08:00 | 13-JUL-16 01.36.32.321000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 353 | import_stats_for_dp | TEST | 13-JUL-16 01.34.56.514000 PM +08:00 | 13-JUL-16 01.34.56.790000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 352 | import_stats_for_dp | TEST | 13-JUL-16 12.01.25.756000 PM +08:00 | 13-JUL-16 12.01.26.022000 PM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 351 | import_stats_for_dp | TEST | 13-JUL-16 11.45.08.131000 AM +08:00 | 13-JUL-16 11.45.08.703000 AM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 350 | export_stats_for_dp | TEST | 13-JUL-16 11.42.21.767000 AM +08:00 | 13-JUL-16 11.42.24.557000 AM +08:00 | COMPLETED | 0 | 0 | 0 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL>
--查看某个schema的统计信息收集情况：
SQL> set serverout on
SQL> set long 999999999
SQL> set line 10000
SQL> set pages 10000
SQL> col my_report for a200
SQL> variable my_report clob;
SQL> BEGIN
2 :my_report := DBMS_STATS.REPORT_GATHER_SCHEMA_STATS(ownname => 'TEST',
3 detail_level => 'TYPICAL', format => 'TEXT');
4 END;
5 /

PL/SQL procedure successfully completed.

SQL>
SQL> print my_report

MY_REPORT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Operation Id | Operation | Target | Start Time | End Time | Status | Total Tasks | Successful Tasks | Failed Tasks | Active Tasks |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 368 | gather_schema_stats (reporting mode) | TEST | | | | 1 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| T A S K S |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| | Target | Type | Start Time | End Time | Status | |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| | TEST.T1 | TABLE | | | | |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| |
| |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

###########################
#
# (8)DBMS_ROLLING
#
###########################
Data Guard Simple Rolling Upgrade
Semi-automation of Transient Logical Standby Rolling Upgrade
Works with Data Guard Broker
Procedure DBMS_ROLLING

###########################
#
# (9) Real-Time ADDM：
#
###########################

– MMON进程负责收集数据，每隔3秒一次，不会有lock/latch
- MMON的子进程创建report，并保留在AWR中，可以通过查询 DBA_HIST_REPORTS

###########################
#
# (10) AUTODOP：
#
###########################
--Oracle 12c 不再需要 I/O calibration

--在11g还是需要I/O calibration：
SET SERVEROUTPUT ON
DECLARE lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (84, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
DBMS_OUTPUT.PUT_LINE ('latency = ' || lat);
DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || mbps);
end;
/
SELECT * FROM DBA_RSRC_IO_CALIBRATE;