迁移方案7-Oracle数据迁移4—一张7.5亿行的表（没有做分区，没有优化索引）进行优化测试，并用最短停机窗口迁移到新分区表—步骤3-生产库迁移方法2—expdp/impdp停机迁移（可回收空间）

2023年6月10日 414点热度 1人点赞 0条评论

一、迁移方法2—expdp/impdp停机迁移

一、迁移方法2—expdp/impdp停机迁移

如果需要收缩所有的表空间，需要根据此部分进行。

1、停库并重启

（1）将数据库置为非归档模式

（导入数据会产生大量的redo和archivelog)

$lsnrctl stop
shutdown immediate;
startup mount;
alter database noarchivelog;
archive log list;
alter databse open;

2、导出数据（全库/压缩共需要2小时-如果环境不好可能需要3小时）

（1）导出要求

导出mesprd下的所有对象和数据。

（2）导出命令

expdp mesprd/mes800 directory=BACKUP_MES dumpfile=expdp.dmp logfile=expdp.log full=y compression=all

3、获取表（SFC_BROOKNER_PARAM_LOG）当前索引的DDL

（1）查看表（SFC_BROOKNER_PARAM_LOG）有哪些索引

select * from user_indexes where table_name='SFC_BROOKNER_PARAM_LOG';

IDX_SFC_BROOKNER_PARAM_LOG
SYS_C0073069
SFC_BROOKNER_PARAM_LOG_IX2

（2）获取创建索引的DDL语句

select dbms_metadata.get_ddl('INDEX','SFC_BROOKNER_PARAM_LOG_IX2','MESPRD') from dual;
select dbms_metadata.get_ddl('INDEX','SYS_C0073069','MESPRD') from dual;
select dbms_metadata.get_ddl('INDEX','IDX_SFC_BROOKNER_PARAM_LOG','MESPRD') from dual;

4、删除生产库所有schema为mesprd的相关信息

（1）查找mesprd相关的表空间

select tablespace_name from user_tablespaces;

（2）删除这些表空间

drop tablespace mesprd including contents and datafiles;

5、导入部分数据——耗时需要1-2个小时

单通道2小时;

并行8通道50分钟-1小时。

（1）导入要求

导入除SFC_BROOKNER_PARAM_LOG，统计信息以外的所有数据和对象。

（2）导入命令

impdp mesprd/mes800 directory=backup_mes dumpfile=expdp.dmp logfile=expdp_imp.log exclude=statistics,table:\"in \(\'SFC_BROOKNER_PARAM_LOG\'\)\" schemas=mesprd

6、给表（SFC_BROOKNER_PARAM_LOG 创建表空间（用于存放分区表）

（1）给所有分区创建表空间

create tablespace TBSP_SFCBROOKPARAMLOG_2020 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2020.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;
create tablespace TBSP_SFCBROOKPARAMLOG_2021 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2021.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;
create tablespace TBSP_SFCBROOKPARAMLOG_2022 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2022.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;
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;
create tablespace TBSP_SFCBROOKPARAMLOG_2024 datafile '/oracle/app/oracle/oradata/mesorcl/SFC_BROOKNER_PARAM_LOG_partation_2024.dbf' size 1G autoextend on next 500M maxsize unlimited uniform size 1M logging extent management local segment space management auto;

（2）创建表，并进行范围分区

注：此处有主键，会创建一个主键索引，此索引是普通索引（全局非分区索引）。

 create table mesprd.sfc_brookner_param_log
( 
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
);

7、导入表SFC_BROOKNER_PARAM_LOG的数据—耗时20分钟-2小时

（1）导入要求

只导表SFC_BROOKNER_PARAM_LOG相关内容，不导统计信息，不导索引。

（2）配置parfile参数文件

vi impdp_sfc_param_log_retain_all.par

（3）导入数据

单通：1:45:24小时

并行（8通道）：16-20分钟

impdp impdp_sfc_param_log_retain_all.par

8、收集新表的统计信息—耗时：163秒

begin 
  dbms_stats.gather_table_stats(ownname => 'MESPRD', tabname => 'SFC_BROOKNER_PARAM_LOG'); 
end;

9、创建本地分区索引或全局分区索引

注意:主键列已经自行创建了索引。如下：

根据需要的列，创建分区索引。

（1）创建索引表空间

create tablespace ind_SFC_BROOKNER_PARAM_LOG 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(datetime_created) global tablespace ind_SFC_BROOKNER_PARAM_LOG; --153秒
create index sfc_broo_peralogindex on SFC_BROOKNER_PARAM_LOG(datetime_created) local tablespace ind_SFC_BROOKNER_PARAM_LOG;

（2）其他组合索引（开发沟通索引重建问题，需提前沟通）

10、更改为归档模式

$lsnrctl stop
shutdown immediate;
startup mount;
alter database archivelog;
archive log list;
alter databse open;

本作品采用知识共享署名 4.0 国际许可协议进行许可