首页 » Exadata Machine, ORACLE » Troubleshooting Exadata to Non Exadata ORA-64307 HCC not supported

Troubleshooting Exadata to Non Exadata ORA-64307 HCC not supported

当从Oracle Exadata工程系统迁移到非Exadata环境时,或配置Datagurad时需要注意,有些功能时Exadata专属特性,如EHCC (Exadata Hybrid Columnar Compression )的表对象在standby 查询或使用datapump迁移时会遇到下面的错误:

ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage
-- or --

CREATE TABLE "ANBOB"."BILLING_XXXXX" ("BILLING_FREQUENCY" NUMBER(3,0) NOT NULL ENABLE, .. "DISPLAY_VALUE" VARCHAR2(240 BYTE) NOT NULL ENABLE) SEGMENT CREATION IMMEDIATE PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING NOLOGGING 
STORAGE(INITIAL 81920 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"

ORA-39083: Object type TABLE:"ANBOB"."XXXXX" failed to create with error:
ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type

Exadata Hybrid Columnar Compression (EHCC)

■ EHCC is Exadata-specific and can realize both significant savings AND performance benefits:
▪ EHCC can be applied to tables and partitions.
■ Compression Units (CU) stores groups of rows in a table in columnar format.
▪ Values of each column stored and compressed together.
▪ More data compressed at once = larger CU = better compression.

Am I Using EHCC?

■‘%cell CU%’ statistics show EHCC usage.
■ 2 statistics determine rows / compression unit:
▪ EHCC Total Rows for Decompression – e.g. 5,000,000,000
▪ EHCC CUs Decompressed – e.g. 5,000,000
▪ Average rows per EHCC compression unit = 1,000.
■ Determine compression used for table / partition / row:
▪ DBMS_COMPRESSION procedures.
▪ COMPRESS_FOR column in DBA_TABLES / DBA_INDEXES.

EHCC支持的压缩级别为:

COMPRESS FOR QUERY LOW
COMPRESS FOR QUERY High
COMPRESS FOR ARCHIVE LOW
COMPRESS FOR ARCHIVE HIGH

解决方法:

1, 对于DATAGUARD环境,需要在primary side上先解压

alter table anbob.xxx   move nocompress;

Note: You may also convert HCC tables using the following compression options on third party storage: Basic Table Compression [compress/compress basic], or OLTP compression[ compress for oltp] . OLTP compression requires a license for the Oracle Advanced Compression Option.

2, 对于datapump环境,增加忽略storage选项: transform = segment_attributes: n: table

impdp \"/ as sysdba\" SCHEMAS=Anobob directory=PUMP dumpfile=xx%U.dmp logfile=xxx.log cluster=n parallel=4   transform = segment_attributes: n: table
打赏

对不起,这篇文章暂时关闭评论。