目录
迁移方案7-Oracle数据迁移6—一张7.5亿行的表(没有做分区,没有优化索引)进行优化测试,并用最短停机窗口迁移到新分区表—步骤3-生产库迁移方法4—在线重定义
一、在线重定义的概念及功能
转自:https://www.cnblogs.com/hfliyi/p/3626302.html
1、概念
在一个高可用系统中,如果需要改变一个表的定义是一件比较棘手的问题,尤其是对于7×24系统。
Oracle提供的基本语法基本可以满足一般性修改,但是对于把普通堆表改为分区表,把索引组织表修改为堆表等操作就无法完成了。
而且,对于被大量DML语句访问的表,幸运的是,Oracle从9i版本开始提供了在线重定义表功能,通过调用DBMS_REDEFINITION包,可以在修改表结构的同时允许DML操作。
2、在线重定义表具有以下功能:
修改表的存储参数; 可以将表转移到其他表空间; 增加并行查询选项; 增加或删除分区; 重建表以减少碎片; 将堆表改为索引组织表或相反的操作; 增加或删除一个列; 调用DBMS_REDEFINITION包需要 EXECUTE_CATALOG_ROLE 角色,除此之外,还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE的权限。
3、在线重定义表的步骤如下:
(1)选择一种重定义方法:
存在两种重定义方法,一种是基于主键,另一种是基于ROWID。
ROWID的方式不能用于索引组织表,而且重定义后会存在隐藏列M_ROW$$。
默认采用主键的方式。
(2)验证表是否满足在线重定义
调用DBMS_REDEFINITION.CAN_REDEF_TABLE()过程,如果表不满足重定义的条件,将会报错并给出原因。
(3)创建空的中间表
在用一个方案中建立一个空的中间表,根据重定义后你期望得到的结构建立中间表。比如:采用分区表,增加了COLUMN等。
(4)开始在线重定义
调用DBMS_REDEFINITION.START_REDEF_TABLE()过程,并提供下列参数:被重定义的表的名称、中间表的名称、列的映射规则、重定义方法。
如果映射方法没有提供,则认为所有包括在中间表中的列用于表的重定义。如果给出了映射方法,则只考虑映射方法中给出的列。如果没有给出重定义方法,则认为使用主键方式。
(5)中间简历触发器/索引/约束
在中间表上建立触发器、索引和约束,并进行相应的授权。任何包含中间表的完整性约束应将状态置为disabled。
当重定义完成时,中间表上建立的触发器、索引、约束和授权将替换重定义表上的触发器、索引、约束和授权。中间表上disabled的约束将在重定义表上enable。
(6)可选-增量过程
如果在执行DBMS_REDEFINITION.START_REDEF_TABLE()过程和执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程直接在重定义表上执行了大量的DML操作,
那么可以选择执行一次或多次的SYNC_INTERIM_TABLE()过程,以减少最后一步执行FINISH_REDEF_TABLE()过程时的锁定时间。
(7)结束在线重定义
执行DBMS_REDEFINITION.FINISH_REDEF_TABLE()过程完成表的重定义。这个过程中,原始表会被独占模式锁定一小段时间,具体时间和表的数据量有关。
执行完FINISH_REDEF_TABLE()过程后,原始表重定义后具有了中间表的属性、索引、约束、授权和触发器。中间表上disabled的约束在原始表上处于enabled状态。
(8)可选-可以重命名索引、触发器和约束。
对于采用了ROWID方式重定义的表,包括了一个隐含列M_ROW$$。推荐使用下列语句经隐含列置为UNUSED状态或删除。
ALTER TABLE TABLE_NAME SET UNUSED (M_ROW$$); ALTER TABLE TABLE_NAME DROP UNUSED COLUMNS;
4、下面是进行重定义操作后的结果:
原始表根据中间表的属性和特性进行重定义;
START_REDEF_TABLE()和FINISH_REDEF_TABLE()操作之间在中间表上建立的触发器、索引、约束和授权,现在定义在原始表上。
中间表上disabled的约束在原始表上处于enabled状态。
原始表上定义的触发器、索引、约束和授权建立在中间表上,并会在删除中间表时删除。原始表上原来enabled状态的索引,建立在中间表上,并处于disabled状态。
任何定义在原始表上的存储过程和游标都会变为INVALID,当下次调用时后自动进行编译。
如果执行过程中出现错误或者人为选择退出的话,可以执行DBMS_REDEFINITION.ABORT_REDEF_TABLE()过程。
其中UNAME 参数是指用户;
二、在线重定义案例
1、选择基于主键的在线重定义(默认)
2、检测当前表SFC_BROOKNER_PARAM_LOG是否满足在线重定义
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('MESPRD', 'SFC_BROOKNER_PARAM_LOG', DBMS_REDEFINITION.CONS_USE_PK);
3、创建中间表(分区表)
(1)给所有分区预创建表空间
create tablespace TBSP_SFCBROOKPARAMLOG_2020 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2020_01.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; alter tablespace TBSP_SFCBROOKPARAMLOG_2020 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2020_02.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2020 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2020_03.dbf' size 1G autoextend on; create tablespace TBSP_SFCBROOKPARAMLOG_2021 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_01.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; alter tablespace TBSP_SFCBROOKPARAMLOG_2021 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_02.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2021 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_03.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2021 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_04.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2021 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_05.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2021 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_06.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2021 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021_07.dbf' size 1G autoextend on; create tablespace TBSP_SFCBROOKPARAMLOG_2022 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_01.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; alter tablespace TBSP_SFCBROOKPARAMLOG_2022 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_02.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2022 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_03.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2022 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_04.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2022 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_05.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2022 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_06.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2022 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022_07.dbf' size 1G autoextend on; create tablespace TBSP_SFCBROOKPARAMLOG_2023 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_01.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; alter tablespace TBSP_SFCBROOKPARAMLOG_2023 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_02.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2023 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_03.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2023 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_04.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2023 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_05.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2023 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_06.dbf' size 1G autoextend on; alter tablespace TBSP_SFCBROOKPARAMLOG_2023 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2023_07.dbf' size 1G autoextend on; create tablespace TBSP_SFCBROOKPARAMLOG_2024 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2024_01.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto; alter tablespace TBSP_SFCBROOKPARAMLOG_2024 add datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2024_02.dbf' size 1G autoextend on;
(2)创建表,并进行范围分区
注:此处有主键,会创建一个主键索引,此索引是普通索引(全局非分区索引)。
create table mesprd.sfc_brookner_param_log_p ( id varchar2(32) default sys_guid() not null enable, datetime_created date default sysdate not null enable, user_created varchar2(80) default 'sys' not null enable, datetime_modified date, user_modified varchar2(80), state char(1) default 'a' not null enable, enterprise_id varchar2(32) default '*' not null enable, org_id varchar2(32) not null enable, workcenter_code varchar2(80) not null enable, equipment_code varchar2(80) not null enable, param_code varchar2(80) not null enable, param_name varchar2(240) not null enable, param_point varchar2(240) not null enable, actual_value varchar2(2000), incident_desc varchar2(2000), bruckner_code varchar2(80) not null enable, is_recom char(1) default 'n' not null enable, primary key (id) using index pctfree 10 initrans 2 maxtrans 255 compute statistics storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default flash_cache default cell_flash_cache default) tablespace mesprd_tbsp enable ) segment creation immediate pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default flash_cache default cell_flash_cache default) partition by range(datetime_created)( partition p_2020 values less than(to_date('2021/01/01','yyyy/mm/dd')) tablespace TBSP_SFCBROOKPARAMLOG_2020, partition p_2021 values less than(to_date('2022/01/01','yyyy/mm/dd')) tablespace TBSP_SFCBROOKPARAMLOG_2021, partition p_2022 values less than(to_date('2023/01/01','yyyy/mm/dd')) tablespace TBSP_SFCBROOKPARAMLOG_2022, partition p_2023 values less than(to_date('2024/01/01','yyyy/mm/dd')) tablespace TBSP_SFCBROOKPARAMLOG_2023, partition p_2024 values less than(to_date('2025/01/01','yyyy/mm/dd')) tablespace TBSP_SFCBROOKPARAMLOG_2024 );
4、开始在线重定义
(1)开始重定义
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MESPRD', 'SFC_BROOKNER_PARAM_LOG', 'SFC_BROOKNER_PARAM_LOG_P', 'DBMS_REDEFINITION.CONS_USE_PK');
或可以改为默认(使用主键在线重定义):
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MESPRD', 'SFC_BROOKNER_PARAM_LOG', 'SFC_BROOKNER_PARAM_LOG_P');
(2)提示完成重定义
(3)查看数据量
5、如果中间原始表有数据更新,需要执行异步传输(此时需要前端业务停止执行,并将此表设置为只读模式,以免原表有新数据写入)
(1)模拟数据删除(一条)
delete from SFC_BROOKNER_PARAM_LOG where rownum<2;
(2)设置表为只读权限(加锁)
ALTER TABLE SFC_BROOKNER_PARAM_LOG READ ONLY;
--如果后期要解锁,可以设置表的读写权限(解锁)
ALTER TABLE SFC_BROOKNER_PARAM_LOG READ WRITE;
(3)更新新增数据到中间表
SQL> EXEC dbms_redefinition.sync_interim_table('MESPRD', 'SFC_BROOKNER_PARAM_LOG', 'SFC_BROOKNER_PARAM_LOG_P');
现在,将中间表与原始表同步,仅当要对表SFC_BROOKNER_PARAM_LOG进行更新时才需要执行该操作。
(4)执行完成,查看数据是否少一行。
SQL>select count(*) from mesprd.SFC_BROOKNER_PARAM_LOG_P;
6、手动结束在线重定义任务
SDL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('MESPRD', 'SFC_BROOKNER_PARAM_LOG', 'SFC_BROOKNER_PARAM_LOG_P');
7、失败的情况解
如果重组织失败(可能是因为temp空间不足等,如下;需要先解决失败情况后再重新开始。),那么你就必须采取特殊的步骤来让它重新开始。由于重定义过程需要创建表格的快照,因此为了重新开始这一过程,你必须调用DBMS_REDEFINITION.ABORT_REDEF_TABLE来释放快照。
DBMS_REDEFINITION.ABORT_REDEF_TABLE过程有三个参数,即用户(schema)、原始表格(original table name)名称以及持有表格名称(holding table name)。
它“出栈”并允许你开始重组织表格。
SQL> exec DBMS_REDEFINITION.ABORT_REDEF_TABLE('MESPRD', 'SFC_BROOKNER_PARAM_LOG', 'SFC_BROOKNER_PARAM_LOG_P');
8、数据查看测试(和原表进行对比)
SELECT COUNT(*) FROM SFC_BROOKNER_PARAM_LOG_P;
SELECT COUNT(*) FROM FC_BROOKNER_PARAM_LOG_P PARTITION (p_2020); SELECT COUNT(*) FROM T PARTITION (p_2021);
需要说明的是完成后,原表和中间表的结构也同时进行了交换,并且中间表里面有原表的数据备份。
9、收集新表的统计信息—耗时:163秒
begin dbms_stats.gather_table_stats(ownname => 'MESPRD', tabname => 'SFC_BROOKNER_PARAM_LOG_P'); end;
10、创建本地分区索引或全局分区索引
注意:主键列已经自行创建了索引。如下:
根据需要的列,创建分区索引。
(1)创建索引表空间
create tablespace ind_SFC_BROOKNER_PARAM_LOG_P datafile '/oracle/oradata2/ind_SFC_BROOKNER_PARAM_LOG_01.dbf' size 4000M autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;
(2)时间列-datectime_reated的索引必须要创建(创建本地分区索引)
如果要创建全局索引,麻烦一些,分区删除时,需要重新更新索引。使用本地分区索引则不会有此问题。不过全局也可解决,在删除分区时进行update也可以。
--create index sfc_broo_peralogindex on SFC_BROOKNER_PARAM_LOG_P(datetime_created) global tablespace ind_SFC_BROOKNER_PARAM_LOG; --153秒 create index sfc_broo_peralogindex on SFC_BROOKNER_PARAM_LOG_P(datetime_created) local tablespace ind_SFC_BROOKNER_PARAM_LOG;
(2)其他组合索引(开发沟通索引重建问题,需提前沟通)
11、删除原表
drop table SFC_BROOKNER_PARAM_LOG purge;
12、重命名新表
rename SFC_BROOKNER_PARAM_LOG to SFC_BROOKNER_PARAM_LOG;
13、生产测试
三、在线重定义的优缺点
1、优点:
--保证数据的一致性,在大部分时间内,表原表都可以正常进行DML操作。
--只在切换的瞬间锁表,具有很高的可用性。这种方法具有很强的灵活性,对各种不同的需要都能满足。
--而且可以在切换前进行相应的授权并建立各种约束,可以做到切换完成后不再需要任何额外的管理操作。
2、缺点:
--你必须有足以维护两份表格拷贝的空间。 --你不能更改主键栏。 --表格必须有主键。 --必须在同一个大纲中进行表格重定义。 --在重定义操作完成之前,你不能对新加栏加以NOT NULL约束。 --表格不能包含LONG、BFILE以及用户类型(UDT)。 --不能重定义链表(clustered tables)。 --不能在SYS和SYSTEM大纲中重定义表格。 --不能用具体化视图日志(materialized view logs)来重定义表格;不能重定义含有具体化视图的表格。 --不能在重定义过程中进行横向分集(horizontal subsetting)。