标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,597)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (17)
- PostgreSQL恢复 (5)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
- Oracle 19C 备库DML重定向—DML Redirection
月归档:四月 2014
exp dmp文件损坏恢复
在有些时候,exp的dmp文件因为某种原因损坏(比如磁盘异常,exp过程损坏等),导致imp导入无法继续,下面的处理方法(直接读取dmp文件)来对dmp文件进行抢救性恢复,最大程度减少数据丢失损失
创建exp dmp文件并使用dd破坏
SQL> create table t_xifenfei as select * from dba_objects; Table created. SQL> select count(*) from t_xifenfei; COUNT(*) ---------- 90915 [oracle@localhost ~]$ exp chf/xifenfei@pdb1 file=/tmp/t_xifenfei.dmp tables=t_xifenfei Export: Release 12.1.0.2.0 on Sun Apr 27 21:39:26 2014 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8MSWIN1252 character set (possible charset conversion) About to export specified tables via Conventional Path ... . . exporting table T_XIFENFEI 90915 rows exported EXP-00091: Exporting questionable statistics. Export terminated successfully with warnings. [oracle@localhost ~]$ od -x /tmp/t_xifenfei.dmp |head -10 0000000 0003 4501 5058 524f 3a54 3156 2e32 3130 0000020 302e 0a30 4344 4648 520a 4154 4c42 5345 0000040 380a 3931 0a32 0a30 3237 300a 000a 0001 0000060 07b2 00d0 0001 0000 0000 0000 0000 0013 0000100 2020 2020 2020 2020 2020 2020 2020 2020 * 0000140 2020 2020 2020 2020 7553 206e 7041 2072 0000160 3732 3220 3a31 3933 323a 2036 3032 3431 0000200 742f 706d 742f 785f 6669 6e65 6566 2e69 0000220 6d64 0070 0000 0000 0000 0000 0000 0000 --strings命令看dmp文件 [oracle@localhost ~]$ strings /tmp/t_xifenfei.dmp |head -50 EXPORT:V12.01.00 DCHF RTABLES 8192 Tue Apr 29 0:39:49 2014/tmp/t_xifenfei.dmp #G#G #G#G +08:00 BYTE UNUSED INTERPRETED DISABLE:ALL METRICST TABLE "T_XIFENFEI" CREATE TABLE "T_XIFENFEI" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 13631488 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS INSERT INTO "T_XIFENFEI" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "EDITION_NAME", "SHARING", "EDITIONABLE", "ORACLE_MAINTAINED") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18) PUBLIC V$DATAGUARD_CONFIG SYNONYM 2014-04-22:17:51:05 VALID METADATA LINK V_$DATAGUARD_STATS VIEW 2014-04-22:17:51:05 --破坏exp dmp文件 [oracle@localhost ~]$ dd if=/dev/zero of=/tmp/t_xifenfei.dmp bs=1024 count=1 conv=notrunc 1+0 records in 1+0 records out 1024 bytes (1.0 kB) copied, 6.0291e-05 seconds, 17.0 MB/s [oracle@localhost ~]$ od -x /tmp/t_xifenfei.dmp |head -10 0000000 0000 0000 0000 0000 0000 0000 0000 0000 * 0020000 0064 0000 6000 2401 050f 0c0b 0c03 050c 0020020 0504 060d 0709 0508 0505 0505 050f 0505 0020040 0505 050a 0505 0505 0504 0706 0808 4723 0020060 4723 1108 0823 4111 47b0 8300 b200 d007 0020100 0003 0000 0000 0000 0000 0000 0000 0000 0020120 0000 0000 0000 0000 0000 0000 0000 0000 0020140 0000 0000 0000 0064 0000 6000 2401 050f 0020160 0c0b 0c03 050c 0504 060d 0709 0508 0505 --损坏后的dmp文件使用strings命令看 [oracle@localhost ~]$ strings /tmp/t_xifenfei.dmp |head -50 #G#G #G#G +08:00 BYTE UNUSED INTERPRETED DISABLE:ALL METRICST TABLE "T_XIFENFEI" CREATE TABLE "T_XIFENFEI" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 13631488 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS INSERT INTO "T_XIFENFEI" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "EDITION_NAME", "SHARING", "EDITIONABLE", "ORACLE_MAINTAINED") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18) PUBLIC V$DATAGUARD_CONFIG SYNONYM 2014-04-22:17:51:05 VALID METADATA LINK V_$DATAGUARD_STATS --imp 导入dmp文件失败 [oracle@localhost ~]$ imp chf/xifenfei@pdb1 file=/tmp/t_xifenfei.dmp full=y Import: Release 12.1.0.2.0 - on Sun Apr 27 22:02:40 2014 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options IMP-00037: Character set marker unknown IMP-00000: Import terminated unsuccessfully
这里通过分析可以知道,exp dmp文件虽然损坏了一点,但是通过strings命令看,相关记录依然存在,因此可以通过工具去读exp dmp文件,然后分析得出相关数据
恢复损坏exp dmp文件数据
CPFL> SEARCH TABLE T_XIFENFEI FROM EXPFILE /tmp/t_xifenfei.dmp 8461: TABLE "T_XIFENFEI" 8480: CREATE TABLE "T_XIFENFEI" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS 9145: INSERT INTO "T_XIFENFEI" ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY", "NAMESPACE", "EDITION_NAME", "SHARING", "EDITIONABLE", "ORACLE_MAINTAINED") VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18) Conventional export 9644: start of table data 12331252: TABLE "T_XIFENFEI" 12331349: ENDTABLE CPFL> UNLOAD TABLE "T_XIFENFEI" ("OWNER" VARCHAR2(128), "OBJECT_NAME" VARCHAR2(128), "SUBOBJECT_NAME" VARCHAR2(128), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJECT_TYPE" VARCHAR2(23), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" VARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VARCHAR2(1), "SECONDARY" VARCHAR2(1), "NAMESPACE" NUMBER, "EDITION_NAME" VARCHAR2(128), "SHARING" VARCHAR2(13), "EDITIONABLE" VARCHAR2(1), "ORACLE_MAINTAINED" VARCHAR2(1)) expfile /tmp/t_xifenfei.dmp from 8480 until 12331349 --因为exp dmp文件损坏记录 CPFL: Error: column 1 length 21059 exceeds max bind size 128 0000000000 45415445 20544142 4c452022 545f5849 EATE TAB LE " T_XI 0000000016 46454e46 45492220 28224f57 4e455222 FENF EI" ("OW NER" 0000000032 20564152 43484152 VAR CHAR 8480: column 1 type VARCHAR2 size 21059 failed 8480: row 1 failed row conversion failure, retrying from offset 8481 CPFL: Error: Zero (illegal) length column number 2 ………… CPFL: Error: Zero (illegal) length column number 1 9644: succesful conversion 1164 bytes skipped due to conversion problems 131877: row 1000 ok 253310: row 2000 ok ………… 12200617: row 90000 ok Unloaded 90915 rows, end of table marker at 12322835 [oracle@localhost CPFL]$ ls -ltr T_XIFENFEI.* -rw-r--r-- 1 oracle oinstall 17230747 Apr 27 22:12 T_XIFENFEI.dat -rw-r--r-- 1 oracle oinstall 1489 Apr 27 22:17 T_XIFENFEI.ctl
导入数据并对比
SQL> create table t_xifenfei_exp as select * from t_xifenfei where 1=0; Table created. [oracle@localhost CPFL]$ more T_XIFENFEI.ctl load data CHARACTERSET UTF8 infile 'T_XIFENFEI.dat' insert into table "T_XIFENFEI_EXP" ---修改为T_XIFENFEI_EXP表 fields terminated by whitespace ( "OWNER" CHAR(128) enclosed by X'7C' ,"OBJECT_NAME" CHAR(128) enclosed by X'7C' ,"SUBOBJECT_NAME" CHAR(29) enclosed by X'7C' ,"OBJECT_ID" CHAR(5) enclosed by X'7C' ,"DATA_OBJECT_ID" CHAR(5) enclosed by X'7C' ,"OBJECT_TYPE" CHAR(20) enclosed by X'7C' ,"CREATED" DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C' ,"LAST_DDL_TIME" DATE "DD-MON-YYYY AD HH24:MI:SS" enclosed by X'7C' ,"TIMESTAMP" CHAR(19) enclosed by X'7C' ,"STATUS" CHAR(5) enclosed by X'7C' ,"TEMPORARY" CHAR(1) enclosed by X'7C' ,"GENERATED" CHAR(1) enclosed by X'7C' ,"SECONDARY" CHAR(1) enclosed by X'7C' ,"NAMESPACE" CHAR(2) enclosed by X'7C' ,"EDITION_NAME" CHAR(1) enclosed by X'7C' ,"SHARING" CHAR(13) enclosed by X'7C' ,"EDITIONABLE" CHAR(1) enclosed by X'7C' ,"ORACLE_MAINTAINED" CHAR(1) enclosed by X'7C' ,"UNEXP_STATUS" FILLER CHAR(3) enclosed by X'7C' ) [oracle@localhost CPFL]$ sqlldr chf/xifenfei@pdb1 control=T_XIFENFEI.ctl SQL*Loader: Release 12.1.0.1.0 on Sun Apr 27 22:17:54 2014 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Path used: Conventional Commit point reached - logical record count 64 Commit point reached - logical record count 128 Commit point reached - logical record count 192 ………… Commit point reached - logical record count 90887 Commit point reached - logical record count 90915 Table "T_XIFENFEI_EXP": 90915 Rows successfully loaded. Check the log file: T_XIFENFEI.log for more information about the load. [oracle@localhost CPFL]$ sqlplus chf/xifenfei@pdb1 SQL*Plus: Release 12.1.0.2.0 Beta on Sun Apr 27 22:18:08 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Sun Apr 27 2014 22:17:54 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select count(*) from t_xifenfei_exp; COUNT(*) ---------- 90915 SQL> select * from t_xifenfei 2 minus 3 select * from t_xifenfei_exp; no rows selected
通过这里可以看出来,在exp dmp文件有部分损坏的情况下,还是可以通过直接读取dmp文件的方式恢复全部或者部分exp dmp文件中内容(具体恢复量取决于dmp文件损坏程度)
如果你在使用这些思路进行恢复遇到突发情况不能自行解决,请联系我们(ORACLE数据库恢复技术支持),将为您提供专业数据库技术支持:
Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com
ORACLE 12C In-Memory功能性能测试
启用In-Memory功能
数据库版本12.1.0.2及其以上版本,inmemory_size参数设置为合适值
SQL> SELECT * FROM V$VERSION; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit 0 PL/SQL Release 12.1.0.2.0 - 0 CORE 12.1.0.2.0 0 TNS for Linux: Version 12.1.0.2.0 - 0 NLSRTL Version 12.1.0.2.0 - 0 SQL> SHOW PARAMETER inmemory; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_query string ENABLE inmemory_size big integer 200M
创建表
这里可以知道,创建表大小为13631488,但是未使用In-Memory功能
SQL> create table t_xifenfei_in_memory as select * from dba_objects; Table created. SQL> SELECT BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME='T_XIFENFEI_IN_MEMORY'; BYTES ---------- 13631488 SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from user_tables; TABLE_NAME INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS ------------------------------ -------- --------------- ----------------- T_XIFENFEI_IN_MEMORY SQL> SELECT * FROM V$INMEMORY_AREA; POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID -------------------------- ----------- ---------- -------------------------- ---------- 1MB POOL 166723584 0 DONE 3 64KB POOL 33554432 0 DONE 3
未使用In-Memory功能测试
SQL> SET AUTOT TRACE SQL> SELECT * FROM T_XIFENFEI_IN_MEMORY; 90902 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3598036702 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 90902 | 9M| 427 (1)| 00:00:01 | | 1 | TABLE ACCESS FULL| T_XIFENFEI_IN_MEMORY | 90902 | 9M| 427 (1)| 00:00:01 | ------------------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 7505 consistent gets 1527 physical reads 0 redo size 12125231 bytes sent via SQL*Net to client 67212 bytes received via SQL*Net from client 6062 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 90902 rows processed
这里可以看到未使用In-Memory功能,数据库查询执行计划使用TABLE ACCESS FULL,consistent gets为7505
使用In-Memory功能测试
SQL> alter table T_XIFENFEI_IN_MEMORY inmemory; Table altered. SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from user_tables; TABLE_NAME INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS ------------------------------ -------- --------------- ----------------- T_XIFENFEI_IN_MEMORY NONE AUTO DISTRIBUTE FOR QUERY --因为只是把该表设置了INMEMORY,但是未查询过,所以查询V$INMEMORY_AREA中未使用相关内存 SQL> SELECT * FROM V$INMEMORY_AREA; POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID -------------------------- ----------- ---------- -------------------------- ---------- 1MB POOL 166723584 0 DONE 3 64KB POOL 33554432 0 DONE 3 --进行一次全表扫描 SQL> SELECT COUNT(*) FROM T_XIFENFEI_IN_MEMORY; COUNT(*) ---------- 90902 --再次查看,已经使用了分配的In-Memory中内存 SQL> SELECT * FROM V$INMEMORY_AREA; POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID -------------------------- ----------- ---------- -------------------------- ---------- 1MB POOL 166723584 4194304 DONE 3 64KB POOL 33554432 131072 DONE 3 SQL> SET AUTOT TRACE SQL> SELECT * FROM T_XIFENFEI_IN_MEMORY; 90902 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3598036702 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 90902 | 9M| 20 (45)| 00:00:01 | | 1 | TABLE ACCESS INMEMORY FULL| T_XIFENFEI_IN_MEMORY | 90902 | 9M| 20 (45)| 00:00:01 | --------------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 3 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 4946298 bytes sent via SQL*Net to client 67212 bytes received via SQL*Net from client 6062 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 90902 rows processed
这里我们可以发现,使用了In-Memory功能之后,数据库consistent gets为4,相比未使用In-Memory之前的7505,性能最少提高近2000倍.
ORACLE 12C In-Memory组件初试
根据Oracle官方的宣传12.1.0.2的In-Memory组件实现内存列存储提高Oracle性能而且弥补在列存储中的不足。感谢Lunar的文档支持
12.1.0.2版本
[oracle@localhost ~]$ sqlplus chf/xifenfei@pdb1 SQL*Plus: Release 12.1.0.2.0 Beta on Thu Apr 24 21:39:43 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit 0 PL/SQL Release 12.1.0.2.0 - 0 CORE 12.1.0.2.0 0 TNS for Linux: Version 12.1.0.2.0 - 0 NLSRTL Version 12.1.0.2.0 - 0
关于In-Memory组件
SQL> select parameter,value from v$option where parameter like 'In-Memory%'; PARAMETER VALUE ---------------------------------------------------------------- ------------- In-Memory Column Store TRUE In-Memory Aggregation TRUE
关于inmemory参数
SQL> select NAME,value,DESCRIPTION from v$parameter where NAME like 'inmemory%'; NAME VALUE -------------------- -------------------- DESCRIPTION --------------------------------------------------- inmemory_size 0 size in bytes of in-memory area inmemory_clause_defa ult Default in-memory clause for new tables inmemory_force DEFAULT Force tables to be in-memory or not inmemory_query ENABLE Specifies whether in-memory queries are allowed
启用In-Memory功能
SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_query string ENABLE inmemory_size big integer 0 SQL> alter system set inmemory_size=200M scope=spfile; System altered. SQL> shutdown immediate Pluggable Database closed. SQL> startup Pluggable Database opened. SQL> show parameter inmemory; NAME TYPE VALUE ------------------------------------ ----------- ---------------------- inmemory_clause_default string inmemory_force string DEFAULT inmemory_query string ENABLE inmemory_size big integer 200M
创建测试表
SQL> create table t_xifenfei_in_memory as select * from dba_objects; Table created. SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from user_tables; TABLE_NAME INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS ------------------------------ -------- --------------- ----------------- T_XIFENFEI_IN_MEMORY SQL> alter table T_XIFENFEI_IN_MEMORY inmemory; Table altered. SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from user_tables; TABLE_NAME INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS ------------------------- -------- --------------- ----------------- T_XIFENFEI_IN_MEMORY NONE AUTO DISTRIBUTE FOR QUERY