联系：手机/微信(+86 17813235971) QQ(107644445)
标题：EXADATA与非EXADATA搭建DATAGURAD关于EHCC特性测试
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
随着xd的越来越普及，不少的企业使用了xd，但是不少企业因为资金有限，只有一台xd，但是为了实现数据的容灾，可能会使用一台非xd的机器来通过dataguard来实现容灾,但是因为xd的ehcc新特性,官方宣传是只在xd中支持,如果dg的备库不是xd。那么会怎么样,这里通过测试得出如下一些结论：xd与非xd可以构造dg,ehcc功能在xd上无法高效使用。对于这样的环境条件下,使用ORACLE自带压缩效率更高.针对ehcc压缩效率很低,个人猜测,是因为xd检查到备库是非xd环境,直接对ehcc进行了降级压缩处理,从而出现了ehcc的压缩效率比oltp还低(牺牲了xd的性能,确保了数据的安全,看来xd的设计还是考虑的比较全面)
xd基本信息
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL> show parameter clu; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean TRUE cluster_database_instances integer 2 cluster_interconnects string SQL> SELECT OPEN_MODE, DATABASE_ROLE ,NAME FROM V$DATABASE; OPEN_MODE DATABASE_ROLE NAME -------------------- ---------------- --------- READ WRITE PRIMARY xxxxxx SQL> !uname -a Linux dm01db02 2.6.18-194.3.1.0.4.el5 #1 SMP Sat Feb 19 03:38:37 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
xd创建模拟表
SQL> create table t_FF_c compress as select * from dba_objects; Table created. SQL> create table t_FF_c_o compress for oltp as select * from dba_objects; Table created. SQL> create table t_FF_q_l compress for query low as select * from dba_objects; Table created. SQL> create table t_FF_q_h compress for query high as select * from dba_objects; Table created. SQL> create table t_FF_a_l compress for archive low as select * from dba_objects; Table created. SQL> create table t_FF_a_h compress for archive high as select * from dba_objects; Table created. SQL> create table t_ff as select * from dba_objects; Table created.
xd查询模拟表
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for from dba_segments s,dba_tables t where s.owner=t.owner and t.table_name=s.segment_name and t.table_name like 'T_FF%'; 2 3 OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR ------------------------------ ------------------------------ ---------- ------------ SYS T_FF 11 SYS T_FF_A_H 10 ARCHIVE HIGH SYS T_FF_A_L 10 ARCHIVE LOW SYS T_FF_Q_H 10 QUERY HIGH SYS T_FF_Q_L 10 QUERY LOW SYS T_FF_C_O 4 OLTP SYS T_FF_C 4 BASIC
通过这里发现,带有非dg的xd使用ehcc压缩效率都低了很多
非xd备库基本信息
SQL> SELECT OPEN_MODE, DATABASE_ROLE ,NAME FROM V$DATABASE; OPEN_MODE DATABASE_ROLE NAME -------------------- ---------------- --------- READ ONLY WITH APPLY PHYSICAL STANDBY xxxxxx SQL> show parameter clu; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean FALSE cluster_database_instances integer 1 cluster_interconnects string SQL> !uname -a Linux oradg 2.6.18-238.el5xen #1 SMP Sun Dec 19 14:42:02 EST 2010 x86_64 x86_64 x86_64 GNU/Linux
查询非xd dg备库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name and t.table_name like 'T_FF%';
OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS T_FF 11
SYS T_FF_A_H 10 ARCHIVE HIGH
SYS T_FF_A_L 10 ARCHIVE LOW
SYS T_FF_Q_H 10 QUERY HIGH
SYS T_FF_Q_L 10 QUERY LOW
SYS T_FF_C_O 4 OLTP
SYS T_FF_C 4 BASIC
SQL> SELECT COUNT(*) FROM T_FF_Q_L;
COUNT(*)
----------
94709
SQL> SELECT COUNT(*) FROM T_FF_Q_H;
COUNT(*)
----------
94710
SQL> SELECT COUNT(*) FROM T_FF_C_O;
COUNT(*)
----------
94708
SQL> SELECT COUNT(*) FROM T_FF_C;
COUNT(*)
----------
94707
SQL> SELECT COUNT(*) FROM T_FF_A_L;
COUNT(*)
----------
94711
SQL> SELECT COUNT(*) FROM T_FF_A_H;
COUNT(*)
----------
94712
SQL> select count(*) from t_FF;
COUNT(*)
----------
94713
通过这里测试证明,对于非xd dg库,可以正常的查询xd上的ehcc相关表,而且相关大小也相同(物理dg当然相同了)
测试xd与非xd dg测试ehcc的dml操作
--xd 主库 SQL> update t_ff_a_h set owner='www.xifenfei.com'; 94712 rows updated. SQL> commit; Commit complete. SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for from dba_segments s,dba_tables t where s.owner=t.owner and t.table_name=s.segment_name and t.table_name like 'T_FF%'; 2 3 OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR ------------------------------ ------------------------------ ---------- ------------ SYS T_FF 11 SYS T_FF_A_H 11 ARCHIVE HIGH SYS T_FF_A_L 10 ARCHIVE LOW SYS T_FF_Q_H 10 QUERY HIGH SYS T_FF_Q_L 10 QUERY LOW SYS T_FF_C_O 4 OLTP SYS T_FF_C 4 BASIC 7 rows selected. SQL> alter system switch logfile; System altered. --非xd 备库 SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for from dba_segments s,dba_tables t where s.owner=t.owner and t.table_name=s.segment_name and t.table_name like 'T_FF%'; OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR ------------------------------ ------------------------------ ---------- ------------ SYS T_FF 11 SYS T_FF_A_H 11 ARCHIVE HIGH SYS T_FF_A_L 10 ARCHIVE LOW SYS T_FF_Q_H 10 QUERY HIGH SYS T_FF_Q_L 10 QUERY LOW SYS T_FF_C_O 4 OLTP SYS T_FF_C 4 BASIC
证明对于xd与非xd构成的dg环境,可以执行dml操作.
测试xd与非xd dg的ehcc的append操作
--xd准备三张测试空表
SQL> create table t_FF_a_l_1 compress for archive low as select * from t_FF where 1=0;
Table created.
SQL> create table t_FF_a_h_1 compress for archive high as select * from t_FF where 1=0;
Table created.
SQL>truncate table t_FF;
Table truncated.
--插入数据(每个表执行5次)
SQL> insert /*+ APPEND */ into t_FF_a_l_1 select * from dba_objects;
94714 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into t_FF_h_l_1 select * from dba_objects;
94714 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into t_FF select * from dba_objects;
94714 rows created.
SQL> commit;
Commit complete.
--查看相关表数据量
SQL> select count(*) from t_FF_a_l_1;
COUNT(*)
----------
473570
SQL> select count(*) from t_FF_a_h_1;
COUNT(*)
----------
473570
SQL> select count(*) from t_FF;
COUNT(*)
----------
473570
--查看xd主库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name and t.table_name like 'T_FF%';
OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS T_FF 52
SYS T_FF_A_H 11 ARCHIVE HIGH
SYS T_FF_A_L 10 ARCHIVE LOW
SYS T_FF_Q_H 10 QUERY HIGH
SYS T_FF_Q_L 10 QUERY LOW
SYS T_FF_C_O 4 OLTP
SYS T_FF_C 4 BASIC
SYS T_FF_A_H_1 47 ARCHIVE HIGH
SYS T_FF_A_L_1 47 ARCHIVE LOW
--查看非xd备库
SQL> select s.owner,segment_name,s.bytes/1024/1024 t_size,compress_for
from dba_segments s,dba_tables t
where s.owner=t.owner and t.table_name=s.segment_name and t.table_name like 'T_FF%';
OWNER SEGMENT_NAME T_SIZE COMPRESS_FOR
------------------------------ ------------------------------ ---------- ------------
SYS T_FF 52
SYS T_FF_A_H 11 ARCHIVE HIGH
SYS T_FF_A_L 10 ARCHIVE LOW
SYS T_FF_Q_H 10 QUERY HIGH
SYS T_FF_Q_L 10 QUERY LOW
SYS T_FF_C_O 4 OLTP
SYS T_FF_C 4 BASIC
SYS T_FF_A_H_1 47 ARCHIVE HIGH
SYS T_FF_A_L_1 47 ARCHIVE LOW
试验整体结论
1.xd可以与非xd机器构建dg容灾环境(不会因为非dg不支持ehcc而导致无法搭建他们之间的dg)
2.xd与非xd的dg,ehcc功能大大缩水,基本上和非压缩状态差不多,比OLTP低很多
3.xd与非xd的dg在备库中支持select,dml,hint append等操作,这些操作是因为ehcc表在xd端就进行了ehcc降级导致
加我QQ
maa-wp-dr-dbm-130065.pdf 中对非Exdata的standby这样的描述：
However, given that third-party storage is unable to support HCC, the following complications and restrictions arise:
● Active Data Guard cannot read HCC compressed tables on a third party storage. In read-only mode, any attempt to select from an HCC table at the standby will yield the following error: ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata, ZFSSA, or AXIOM storage
● Data Guard Snapshot Standby cannot read HCC compressed tables on third party storage
To access HCC tables you must failover to the standby and decompress using ‘alter table move’ for each HCC table. During the alter table move operation the table will be locked. SQL> recover managed standby database finish; SQL> alter database commit to switchover to primary; SQL> alter database open;
SQL> alter table move ;
看楼主的测试貌似并没遇到白皮书里描述的这些问题？