sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 产品DBA > Oracle DG >

学习笔记:Oracle EXADATA与非EXADATA x86设备配置dataguard实现容灾 以及EHCC特性测试

时间:2016-05-24 22:34   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净

记录Oracle EXADATA与非EXADATA x86服务器搭建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降级导致


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle EXADATA与非EXADATA x86设备配置dataguard实现容灾 以及EHCC特性测试

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/222.html

Oracle研究中心

关键词:

Oracle EXADATA与非EXADATA x86设备配置dataguard实现容灾

exadata EHCC特性测试