Mike Dietrich有一篇600多页的ppt，叫『Upgrade, Migrate & Consolidate to Oracle Database 12c』。在这个ppt里面，有一些是标记了“NEW”了的表示12c的新特性、新行为。 （其实升级到11g的时候，也有一篇差不多类似的400多页的ppt，被oracle售前广泛应用。）
我这里摘录和粗略解释一下。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
########################### # # (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效率的问题。 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
########################### # # (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。） |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 |
########################### # # (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 <图> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
########################### # # (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; |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
########################### # # (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; |
|
1 2 3 4 5 6 7 8 9 |
########################### # # (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'); |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
########################### # # (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 | | | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
|
1 2 3 4 5 6 7 8 9 |
########################### # # (8)DBMS_ROLLING # ########################### Data Guard Simple Rolling Upgrade Semi-automation of Transient Logical Standby Rolling Upgrade Works with Data Guard Broker Procedure DBMS_ROLLING |
|
1 2 3 4 5 6 7 8 |
########################### # # (9) Real-Time ADDM： # ########################### – MMON进程负责收集数据， 每隔3秒一次，不会有lock/latch - MMON的子进程创建report，并保留在AWR中，可以通过查询 DBA_HIST_REPORTS |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
########################### # # (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; |
7条评论
请问 11g 的那个ppt 名字是什么呢？ 能否下载
Upgrade,
Migrate &
Consolidate
to Oracle Database 12c
Roy Swonger & Mike Dietrich
Database Upgrade and Utilities
Oracle Corporation
“其实升级到11g的时候，也有一篇差不多类似的400多页的ppt，被oracle售前广泛应用” ，我想问的是这个400多页的11g PPT，是什么名字？
Re ron 叫Upgrade_and_Migrate_to_11gR2.pptx
Re ricky : 对，就是这个文档，不过也好几版了，最新的一版可能是2016年3月的。
啊、密密麻麻的
这两个ppt可否共享下？