Oracle高级压缩和透明数据加密组合实验

2024-09-06 21:52

本文主要是介绍Oracle高级压缩和透明数据加密组合实验,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

本文参考了实验DB Security - Advanced Compression with Transparent Data Encryption(TDE),其申请地址在这里。

本文只使用了实验中关于高级压缩和在线重定义的部分。并对要点进行说明及对实验进行了简化。

准备:环境设置

原文中的实验环境实际上是改自Oracle示例Sample Schema,其实唯一的改动就是去掉了SALES表中的分区的压缩属性。

git clone --depth 1 --branch v19c https://github.com/oracle-samples/db-sample-schemas.git
cd db-sample-schemas/
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat 

然后,如前所说。需要修改文件db-sample-schemas/sales_history/csh_v3.sql,去掉压缩设置:

$ grep COMPRESS csh_v3.sql.orig
...PCTFREE 5 NOLOGGING NOCOMPRESS(TO_DATE('1996-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,(TO_DATE('1997-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,(TO_DATE('1997-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,(TO_DATE('1998-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,(TO_DATE('1998-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,(TO_DATE('1998-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,(TO_DATE('1998-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,(TO_DATE('1999-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,(TO_DATE('1999-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,(TO_DATE('1999-07-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,(TO_DATE('1999-10-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,(TO_DATE('2000-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) COMPRESS,
...

先将文件备份一份,然后用vi命令s/ COMPRESS//g就可以了。

为了方便比较,我们单独建立一个PDB,并且只导入SH schema。

建立PDB:

create pluggable database pdb1 admin user pdbadmin identified by Welcome1;
alter pluggable database pdb1 open;
alter pluggable database pdb1 save state;
alter session set container=pdb1;
create tablespace TEST_DATA datafile 'test_data.dbf' size 10m autoextend on extent management local uniform size 512K;

导入数据到SH schema:

SQL> !pwd
/home/oracle/db-sample-schemas/sales_historySQL> alter session set container=pdb1;
Session altered.SQL> @sh_mainspecify password for SH as parameter 1:
Enter value for 1: ********specify default tablespace for SH as parameter 2:
Enter value for 2: TEST_DATAspecify temporary tablespace for SH as parameter 3:
Enter value for 3: tempspecify password for SYS as parameter 4:
Enter value for 4: ********-- directory path 是指数据库中建立的DIRECTORY对象
-- data file是需要数据泵导入的文件:sale1v3.dat
specify directory path for the data files as parameter 5:
Enter value for 5: /home/oracle/db-sample-schemas/sales_history/writeable directory path for the log files as parameter 6:
Enter value for 6: /u01/app/oracle/product/19c/dbhome_1/demo/schema/log/specify version as parameter 7:
Enter value for 7: v3specify connect string as parameter 8:
Enter value for 8: localhost:/pdb1

创建索引,也许是为演示索引压缩:

create index sh.sales_cust_channel_promo_idx on sh.sales(cust_id, channel_id, promo_id);

查看当前SH schema中的对象:

connect sh/Welcome1@localhost:/pdb1
col table_name for a30
set line 2000 pages 2000
select table_name, num_rows from user_tables order by table_name;TABLE_NAME                       NUM_ROWS
------------------------------ ----------
CAL_MONTH_SALES_MV                     48
CHANNELS                                5
COSTS                               82112
COUNTRIES                              23
CUSTOMERS                           55500
DR$SUP_TEXT_IDX$I
DR$SUP_TEXT_IDX$K
DR$SUP_TEXT_IDX$N
DR$SUP_TEXT_IDX$U
FWEEK_PSCAT_SALES_MV                11266
PRODUCTS                               72
PROMOTIONS                            503
SALES                              918843
SALES_TRANSACTIONS_EXT             916039
SUPPLEMENTARY_DEMOGRAPHICS           4500
TIMES                                182616 rows selected.

各类对象空间占用情况:

select SEGMENT_TYPE,count(*),sum(bytes/(1024*1024)) SIZE_MB 
from dba_segments 
where TABLESPACE_NAME in ('TEST_DATA') 
and SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION', 'INDEX PARTITION', 'INDEX')
group by SEGMENT_TYPE order by SEGMENT_TYPE;SEGMENT_TYPE         COUNT(*)    SIZE_MB
------------------ ---------- ----------
INDEX                      19       36.5
INDEX PARTITION           112         56
TABLE                      10         21
TABLE PARTITION            32         46

记录表空间当前大小,171M。

$ ls -sh /u01/app/oracle/product/19c/dbhome_1/dbs/test_data.dbf
171M /u01/app/oracle/product/19c/dbhome_1/dbs/test_data.dbf

gzip可将其压缩到1/10:

$ cp /u01/app/oracle/product/19c/dbhome_1/dbs/test_data.dbf /tmp
$ gzip /tmp/test_data.dbf
$ ls -sh /tmp/test_data.dbf.gz
17M /tmp/test_data.dbf.gz

任务 1:压缩顾问估计段压缩率

connect system/Welcome1@localhost:/pdb1
create tablespace comp_data_ts datafile size 10M autoextend on extent management local uniform size 512K default table compress for oltp;
select tablespace_name, encrypted from dba_tablespaces where tablespace_name = 'COMP_DATA_TS';TABLESPACE_NAME                ENC
------------------------------ ---
COMP_DATA_TS                   NO

表空间还未加密,需要将其加密,过程略。达到以下效果就可以了:

TABLESPACE_NAME                ENC
------------------------------ ---
COMP_DATA_TS                   YES

在表空间加密前后,数据文件大小没变,说明表空间加密不会增加额外的存储空间。:

$ ls -sh /u01/app/oracle/product/19c/dbhome_1/dbs/comp_data_ts.dbf
11M /u01/app/oracle/product/19c/dbhome_1/dbs/comp_data_ts.dbf

建议用离线加密,因为在线加密会移动数据文件的位置。

运行压缩顾问以实现高级行压缩:

SET SERVEROUTPUT ON
DECLAREl_blkcnt_cmp   PLS_INTEGER;l_blkcnt_uncmp PLS_INTEGER;l_row_cmp      PLS_INTEGER;l_row_uncmp    PLS_INTEGER;l_cmp_ratio    NUMBER;l_comptype_str VARCHAR2(32767);
BEGINDBMS_COMPRESSION.GET_COMPRESSION_RATIO (scratchtbsname => 'COMP_DATA_TS' ,ownname      => 'SH' ,objname      => 'SALES' ,subobjname     =>  NULL ,comptype       =>  DBMS_COMPRESSION.COMP_ADVANCED,blkcnt_cmp     => l_blkcnt_cmp,blkcnt_uncmp   => l_blkcnt_uncmp,row_cmp      => l_row_cmp,row_uncmp      => l_row_uncmp,cmp_ratio      => l_cmp_ratio,comptype_str   => l_comptype_str,subset_numrows => DBMS_COMPRESSION.comp_ratio_minrows,objtype      => DBMS_COMPRESSION.objtype_table);
DBMS_OUTPUT.put_line( 'Number of blocks used by the compressed sample of the object    :  ' || l_blkcnt_cmp);
DBMS_OUTPUT.put_line( 'Number of blocks used by the uncompressed sample of the object    :  ' || l_blkcnt_uncmp);
DBMS_OUTPUT.put_line( 'Number of rows in a block in compressed sample of the object    :  ' || l_row_cmp);
DBMS_OUTPUT.put_line( 'Number of rows in a block in uncompressed sample of the object    :  ' || l_row_uncmp);
DBMS_OUTPUT.put_line( 'Estimated Compression Ratio of Sample                           :  ' || l_cmp_ratio);
DBMS_OUTPUT.put_line( 'Compression Type                            :  ' || l_comptype_str);
END;
/

输出为:

Number of blocks used by the compressed sample of the object    :  640
Number of blocks used by the uncompressed sample of the object    :  1728
Number of rows in a block in compressed sample of the object    :  560
Number of rows in a block in uncompressed sample of the object    :  207
Estimated Compression Ratio of Sample                           :  2.7
Compression Type                            :  "Compress Advanced"PL/SQL procedure successfully completed.

运行分区表压缩顾问:

SET SERVEROUTPUT ON
DECLAREl_blkcnt_cmp   PLS_INTEGER;l_blkcnt_uncmp PLS_INTEGER;l_row_cmp      PLS_INTEGER;l_row_uncmp    PLS_INTEGER;l_cmp_ratio    NUMBER;l_comptype_str VARCHAR2(32767);
BEGINDBMS_COMPRESSION.GET_COMPRESSION_RATIO (scratchtbsname => 'COMP_DATA_TS' ,ownname      => 'SH' ,objname      => 'SALES' ,subobjname     =>  'SALES_Q4_2001' ,comptype       =>  DBMS_COMPRESSION.COMP_ADVANCED,blkcnt_cmp     => l_blkcnt_cmp,blkcnt_uncmp   => l_blkcnt_uncmp,row_cmp      => l_row_cmp,row_uncmp      => l_row_uncmp,cmp_ratio      => l_cmp_ratio,comptype_str   => l_comptype_str,subset_numrows => DBMS_COMPRESSION.comp_ratio_minrows,objtype      => DBMS_COMPRESSION.objtype_table);
DBMS_OUTPUT.put_line( 'Number of blocks used by the compressed sample of the object    :  ' || l_blkcnt_cmp);
DBMS_OUTPUT.put_line( 'Number of blocks used by the uncompressed sample of the object    :  ' || l_blkcnt_uncmp);
DBMS_OUTPUT.put_line( 'Number of rows in a block in compressed sample of the object    :  ' || l_row_cmp);
DBMS_OUTPUT.put_line( 'Number of rows in a block in uncompressed sample of the object    :  ' || l_row_uncmp);
DBMS_OUTPUT.put_line( 'Estimated Compression Ratio of Sample                           :  ' || l_cmp_ratio);
DBMS_OUTPUT.put_line( 'Compression Type                            :  ' || l_comptype_str);
END;
/

输出为:

Number of blocks used by the compressed sample of the object    :  128
Number of blocks used by the uncompressed sample of the object    :  333
Number of rows in a block in compressed sample of the object    :  538
Number of rows in a block in uncompressed sample of the object    :  206
Estimated Compression Ratio of Sample                           :  2.6
Compression Type                            :  "Compress Advanced"PL/SQL procedure successfully completed.

运行压缩顾问以进行高级索引压缩。

SET SERVEROUTPUT ON
DECLAREl_blkcnt_cmp    PLS_INTEGER;l_blkcnt_uncmp  PLS_INTEGER;l_row_cmp       PLS_INTEGER;l_row_uncmp     PLS_INTEGER;l_cmp_ratio     NUMBER;l_comptype_str  VARCHAR2(32767);
BEGINDBMS_COMPRESSION.get_compression_ratio (scratchtbsname  => 'COMP_DATA_TS',ownname         => 'SH',objname         => 'SALES_CUST_CHANNEL_PROMO_IDX',subobjname      => NULL,comptype        => DBMS_COMPRESSION.COMP_INDEX_ADVANCED_LOW,blkcnt_cmp      => l_blkcnt_cmp,blkcnt_uncmp    => l_blkcnt_uncmp,row_cmp         => l_row_cmp,row_uncmp       => l_row_uncmp,cmp_ratio       => l_cmp_ratio,comptype_str    => l_comptype_str,subset_numrows  => DBMS_COMPRESSION.comp_ratio_minrows,objtype         => DBMS_COMPRESSION.objtype_index);DBMS_OUTPUT.put_line('Number of blocks used (compressed)       : ' ||  l_blkcnt_cmp);DBMS_OUTPUT.put_line('Number of blocks used (uncompressed)     : ' ||  l_blkcnt_uncmp);DBMS_OUTPUT.put_line('Number of rows in a block (compressed)   : ' ||  l_row_cmp);DBMS_OUTPUT.put_line('Number of rows in a block (uncompressed) : ' ||  l_row_uncmp);DBMS_OUTPUT.put_line('Compression ratio                        : ' ||  l_cmp_ratio);DBMS_OUTPUT.put_line('Compression type                         : ' ||  l_comptype_str);
END;
/

输出为:

Number of blocks used (compressed)       : 1468
Number of blocks used (uncompressed)     : 2852
Number of rows in a block (compressed)   : 626
Number of rows in a block (uncompressed) : 322
Compression ratio                        : 1.9
Compression type                         : "Compress Advanced Low"PL/SQL procedure successfully completed.

任务 2:压缩选项 1:离线压缩方法

注意:离线压缩使用 ALTER TABLE MOVE 语法将数据段移动到不同的表空间。在数据段重组期间,由于段上的底层序列化机制,应用程序需要停机。此外,我们需要重建与移动的数据段相关的所有索引。

在线压缩使用 Oracle 在线重定义技术。对于在高级压缩和任何其他数据重组操作期间寻求最短停机时间的客户,建议采用这种方法。在线重定义只需要在很短的时间内锁定底层段以同步最终更改以完成段重组。

您可以从 MOS 说明中了解有关在线重新定义的更多信息:

  • 如何在在线时压缩表(文档 ID 1353967.1)
  • 主要说明:在线重新定义表 (DBMS_REDEFINITION) 概述(文档 ID 1357825.1)
  • 使用“DBMS_REDEFINITION.REDEF_TABLE”进行在线重新定义(文档 ID 2412059.1)

这个实验设计还是挺巧妙的,实现将一些不能在线重定义的表先行离线迁移了。没有主键的表和物化视图都不支持在线重定义。

ALTER TABLE SH.CAL_MONTH_SALES_MV MOVE TABLESPACE COMP_DATA_TS ROW STORE COMPRESS ADVANCED;
ALTER TABLE SH.FWEEK_PSCAT_SALES_MV MOVE TABLESPACE COMP_DATA_TS ROW STORE COMPRESS ADVANCED;
ALTER TABLE SH.DR$SUP_TEXT_IDX$K MOVE TABLESPACE COMP_DATA_TS ROW STORE COMPRESS ADVANCED;
ALTER TABLE SH.DR$SUP_TEXT_IDX$U MOVE TABLESPACE COMP_DATA_TS ROW STORE COMPRESS ADVANCED;

对没有主键的表或物化视图做在线重定义,会报错如下:

ERROR at line 1:
ORA-23540: Redefinition not defined or initiated
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5565
ORA-12092: cannot online redefine replicated table "SH"."CAL_MONTH_SALES_MV"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 4684
ORA-06512: at "SYS.DBMS_REDEFINITION", line 285
ORA-06512: at "SYS.DBMS_REDEFINITION", line 4680
ORA-12089: cannot online redefine table "SH"."CAL_MONTH_SALES_MV" with no
primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 285
ORA-06512: at "SYS.DBMS_REDEFINITION", line 4655
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5657
ORA-06512: at line 9
ORA-06512: at line 9

还有,物化视图和表可以同名。实际上是创建物化视图的同时,Oracle自动创建了同名的表。毕竟物化视图需要一个地方存数据。

任务 3:压缩选项 2:通过在线重新定义 REDEF_TABLE 进行在线压缩

在线压缩前检查段表空间信息:

set pagesize 20
col segment_name for a50
set pages 9999
col segment_name for a40
set lines 100
select segment_name, segment_type, tablespace_name from dba_segments where owner='SH';SEGMENT_NAME                             SEGMENT_TYPE       TABLESPACE_NAME
---------------------------------------- ------------------ ------------------------------
CHANNELS                                 TABLE              TEST_DATA
PROMOTIONS                               TABLE              TEST_DATA
CUSTOMERS                                TABLE              TEST_DATA
TIMES                                    TABLE              TEST_DATA
PRODUCTS                                 TABLE              TEST_DATA
COUNTRIES                                TABLE              TEST_DATA
SUPPLEMENTARY_DEMOGRAPHICS               TABLE              TEST_DATA
DR$SUP_TEXT_IDX$I                        TABLE              TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
SALES                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
COSTS                                    TABLE PARTITION    TEST_DATA
PROMO_PK                                 INDEX              TEST_DATA
TIMES_PK                                 INDEX              TEST_DATA
PRODUCTS_PK                              INDEX              TEST_DATA
PRODUCTS_PROD_STATUS_BIX                 INDEX              TEST_DATA
PRODUCTS_PROD_SUBCAT_IX                  INDEX              TEST_DATA
PRODUCTS_PROD_CAT_IX                     INDEX              TEST_DATA
CUSTOMERS_PK                             INDEX              TEST_DATA
CHANNELS_PK                              INDEX              TEST_DATA
COUNTRIES_PK                             INDEX              TEST_DATA
SYS_IL0000075000C00006$$                 LOBINDEX           TEST_DATA
SYS_IOT_TOP_75004                        INDEX              TEST_DATA
DR$SUP_TEXT_IDX$X                        INDEX              TEST_DATA
CUSTOMERS_GENDER_BIX                     INDEX              TEST_DATA
CUSTOMERS_MARITAL_BIX                    INDEX              TEST_DATA
CUSTOMERS_YOB_BIX                        INDEX              TEST_DATA
SALES_CUST_CHANNEL_PROMO_IDX             INDEX              TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_PROD_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_CUST_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_TIME_BIX                           INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_CHANNEL_BIX                        INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
SALES_PROMO_BIX                          INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_PROD_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
COSTS_TIME_BIX                           INDEX PARTITION    TEST_DATA
SYS_LOB0000075000C00006$$                LOBSEGMENT         TEST_DATA
CAL_MONTH_SALES_MV                       TABLE              COMP_DATA_TS
FWEEK_PSCAT_SALES_MV                     TABLE              COMP_DATA_TS171 rows selected.

为单表SH.PRODUCTS重新定义表在线压缩:

BEGINDBMS_REDEFINITION.REDEF_TABLE(uname                        => 'SH',tname                        => 'PRODUCTS',table_compression_type       => 'ROW STORE COMPRESS ADVANCED',table_part_tablespace        => 'COMP_DATA_TS',index_key_compression_type   => 'COMPRESS ADVANCED LOW',index_tablespace             => 'COMP_DATA_TS',lob_compression_type         => 'COMPRESS HIGH',lob_tablespace               => 'COMP_DATA_TS',lob_store_as                 => 'SECUREFILE');
END;
/

输出为:

PL/SQL procedure successfully completed.Elapsed: 00:00:26.65

在目标表空间中确认表和索引均已迁移成功:

SQL> select segment_name, segment_type, tablespace_name from dba_segments where owner='SH' and tablespace_name in ('COMP_DATA_TS');SEGMENT_NAME                             SEGMENT_TYPE       TABLESPACE_NAME
---------------------------------------- ------------------ ------------------------------
CAL_MONTH_SALES_MV                       TABLE              COMP_DATA_TS
FWEEK_PSCAT_SALES_MV                     TABLE              COMP_DATA_TS
PRODUCTS                                 TABLE              COMP_DATA_TS
PRODUCTS_PK                              INDEX              COMP_DATA_TS
PRODUCTS_PROD_STATUS_BIX                 INDEX              COMP_DATA_TS
PRODUCTS_PROD_SUBCAT_IX                  INDEX              COMP_DATA_TS
PRODUCTS_PROD_CAT_IX                     INDEX              COMP_DATA_TS7 rows selected.

为多个表自动重新定义在线压缩。其实就是通过一个循环得到所有的普通表和分区表,然后逐个迁移。注意:此步骤可能需要一段时间。

DECLAREv_table_name VARCHAR2(100);
BEGINFOR rec IN (SELECT segment_name FROM dba_segments WHERE owner = 'SH' AND segment_type = 'TABLE' AND tablespace_name='TEST_DATA' UNION SELECT distinct table_name FROM dba_tab_partitions WHERE table_owner='SH' AND tablespace_name='TEST_DATA')LOOPv_table_name := rec.segment_name;BEGINDBMS_REDEFINITION.REDEF_TABLE(uname                        => 'SH',tname                        =>  v_table_name,table_compression_type       => 'ROW STORE COMPRESS ADVANCED',table_part_tablespace        => 'COMP_DATA_TS',index_key_compression_type   => 'COMPRESS ADVANCED LOW',index_tablespace             => 'COMP_DATA_TS',lob_compression_type         => 'COMPRESS HIGH',lob_tablespace               => 'COMP_DATA_TS',lob_store_as                 => 'SECUREFILE');END;END LOOP;
END;
/

输出为:

PL/SQL procedure successfully completed.Elapsed: 00:03:58.74

确认:

select segment_name, segment_type, tablespace_name from dba_segments where owner='SH' and tablespace_name in ('COMP_DATA_TS');SEGMENT_NAME                             SEGMENT_TYPE       TABLESPACE_NAME
---------------------------------------- ------------------ ------------------------------
CAL_MONTH_SALES_MV                       TABLE              COMP_DATA_TS
FWEEK_PSCAT_SALES_MV                     TABLE              COMP_DATA_TS
PRODUCTS                                 TABLE              COMP_DATA_TS
CHANNELS                                 TABLE              COMP_DATA_TS
COUNTRIES                                TABLE              COMP_DATA_TS
CUSTOMERS                                TABLE              COMP_DATA_TS
PROMOTIONS                               TABLE              COMP_DATA_TS
SUPPLEMENTARY_DEMOGRAPHICS               TABLE              COMP_DATA_TS
TIMES                                    TABLE              COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
COSTS                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
SALES                                    TABLE PARTITION    COMP_DATA_TS
PRODUCTS_PK                              INDEX              COMP_DATA_TS
PRODUCTS_PROD_STATUS_BIX                 INDEX              COMP_DATA_TS
PRODUCTS_PROD_SUBCAT_IX                  INDEX              COMP_DATA_TS
PRODUCTS_PROD_CAT_IX                     INDEX              COMP_DATA_TS
CHANNELS_PK                              INDEX              COMP_DATA_TS
COUNTRIES_PK                             INDEX              COMP_DATA_TS
CUSTOMERS_GENDER_BIX                     INDEX              COMP_DATA_TS
CUSTOMERS_MARITAL_BIX                    INDEX              COMP_DATA_TS
CUSTOMERS_YOB_BIX                        INDEX              COMP_DATA_TS
CUSTOMERS_PK                             INDEX              COMP_DATA_TS
PROMO_PK                                 INDEX              COMP_DATA_TS
SALES_CUST_CHANNEL_PROMO_IDX             INDEX              COMP_DATA_TS
TIMES_PK                                 INDEX              COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
SALES_CHANNEL_BIX                        INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
COSTS_TIME_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROD_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_CUST_BIX                           INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS
SALES_PROMO_BIX                          INDEX PARTITION    COMP_DATA_TS166 rows selected.

任务 4:Oracle TDE 压缩的空间优势

比较压缩前后的空间使用情况:

select SEGMENT_TYPE,count(*),sum(bytes/(1024*1024)) SIZE_MB 
from dba_segments 
where TABLESPACE_NAME in ('COMP_DATA_TS') 
and SEGMENT_TYPE in ('TABLE', 'TABLE PARTITION', 'INDEX PARTITION', 'INDEX')
group by SEGMENT_TYPE order by SEGMENT_TYPE;SEGMENT_TYPE         COUNT(*)    SIZE_MB
------------------ ---------- ----------
INDEX                      13       22.5
INDEX PARTITION           112         56
TABLE                       9         11
TABLE PARTITION            32         44-- 以下是之前的数据
/*
SEGMENT_TYPE         COUNT(*)    SIZE_MB
------------------ ---------- ----------
INDEX                      19       36.5
INDEX PARTITION           112         56
TABLE                      10         21
TABLE PARTITION            32         46
*/

数据文件大小由171M减到143M:

$ ls -sh /u01/app/oracle/product/19c/dbhome_1/dbs/comp_data_ts.dbf
143M /u01/app/oracle/product/19c/dbhome_1/dbs/comp_data_ts.dbf

任务 5:通过在线重新定义启用会话并行性以加快压缩过程

在离线或在线压缩前,可以加入以下语句:

ALTER SESSION ENABLE PARALLEL DML ;
ALTER SESSION FORCE PARALLEL DML PARALLEL 2;
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION FORCE PARALLEL DDL PARALLEL 2;

以之前的在线重定义所有表为例,从3分58秒变为了7分钟:

PL/SQL procedure successfully completed.Elapsed: 00:07:17.14

没有加快,反而变慢,也许和我的CPU和I/O有关,不再细究。

参考

  • Primary Note: Overview of Oracle Tablespace Management (Doc ID 1493350.1)

这篇关于Oracle高级压缩和透明数据加密组合实验的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1143221

相关文章

大模型研发全揭秘:客服工单数据标注的完整攻略

在人工智能(AI)领域,数据标注是模型训练过程中至关重要的一步。无论你是新手还是有经验的从业者,掌握数据标注的技术细节和常见问题的解决方案都能为你的AI项目增添不少价值。在电信运营商的客服系统中,工单数据是客户问题和解决方案的重要记录。通过对这些工单数据进行有效标注,不仅能够帮助提升客服自动化系统的智能化水平,还能优化客户服务流程,提高客户满意度。本文将详细介绍如何在电信运营商客服工单的背景下进行

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展,我们的业务数据越来越多,单纯使用 MySQL 已经不能满足我们的数据查询需求,例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后,随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步,我们目前的解决方案是建立数据中间表。把需要检索的业务数据,统一放到一张M

关于数据埋点,你需要了解这些基本知识

产品汪每天都在和数据打交道,你知道数据来自哪里吗? 移动app端内的用户行为数据大多来自埋点,了解一些埋点知识,能和数据分析师、技术侃大山,参与到前期的数据采集,更重要是让最终的埋点数据能为我所用,否则可怜巴巴等上几个月是常有的事。   埋点类型 根据埋点方式,可以区分为: 手动埋点半自动埋点全自动埋点 秉承“任何事物都有两面性”的道理:自动程度高的,能解决通用统计,便于统一化管理,但个性化定

使用SecondaryNameNode恢复NameNode的数据

1)需求: NameNode进程挂了并且存储的数据也丢失了,如何恢复NameNode 此种方式恢复的数据可能存在小部分数据的丢失。 2)故障模拟 (1)kill -9 NameNode进程 [lytfly@hadoop102 current]$ kill -9 19886 (2)删除NameNode存储的数据(/opt/module/hadoop-3.1.4/data/tmp/dfs/na

异构存储(冷热数据分离)

异构存储主要解决不同的数据,存储在不同类型的硬盘中,达到最佳性能的问题。 异构存储Shell操作 (1)查看当前有哪些存储策略可以用 [lytfly@hadoop102 hadoop-3.1.4]$ hdfs storagepolicies -listPolicies (2)为指定路径(数据存储目录)设置指定的存储策略 hdfs storagepolicies -setStoragePo

Hadoop集群数据均衡之磁盘间数据均衡

生产环境,由于硬盘空间不足,往往需要增加一块硬盘。刚加载的硬盘没有数据时,可以执行磁盘数据均衡命令。(Hadoop3.x新特性) plan后面带的节点的名字必须是已经存在的,并且是需要均衡的节点。 如果节点不存在,会报如下错误: 如果节点只有一个硬盘的话,不会创建均衡计划: (1)生成均衡计划 hdfs diskbalancer -plan hadoop102 (2)执行均衡计划 hd

hdu1043(八数码问题,广搜 + hash(实现状态压缩) )

利用康拓展开将一个排列映射成一个自然数,然后就变成了普通的广搜题。 #include<iostream>#include<algorithm>#include<string>#include<stack>#include<queue>#include<map>#include<stdio.h>#include<stdlib.h>#include<ctype.h>#inclu

hdu1565(状态压缩)

本人第一道ac的状态压缩dp,这题的数据非常水,很容易过 题意:在n*n的矩阵中选数字使得不存在任意两个数字相邻,求最大值 解题思路: 一、因为在1<<20中有很多状态是无效的,所以第一步是选择有效状态,存到cnt[]数组中 二、dp[i][j]表示到第i行的状态cnt[j]所能得到的最大值,状态转移方程dp[i][j] = max(dp[i][j],dp[i-1][k]) ,其中k满足c

hdu4869(逆元+求组合数)

//输入n,m,n表示翻牌的次数,m表示牌的数目,求经过n次操作后共有几种状态#include<iostream>#include<algorithm>#include<cstring>#include<stack>#include<queue>#include<set>#include<map>#include<stdio.h>#include<stdlib.h>#includ

【Prometheus】PromQL向量匹配实现不同标签的向量数据进行运算

✨✨ 欢迎大家来到景天科技苑✨✨ 🎈🎈 养成好习惯,先赞后看哦~🎈🎈 🏆 作者简介:景天科技苑 🏆《头衔》:大厂架构师,华为云开发者社区专家博主,阿里云开发者社区专家博主,CSDN全栈领域优质创作者,掘金优秀博主,51CTO博客专家等。 🏆《博客》:Python全栈,前后端开发,小程序开发,人工智能,js逆向,App逆向,网络系统安全,数据分析,Django,fastapi