一次不可思议需求的数据迁移

OCM之家 2021-08-19

391

作者：李和祥 中国OCM之家核心成员；Oracle OCM认证获得者；10年ORACLE管理、实施经验，曾服务于某省移动BOSS平台，有着丰富的大型数据库管理、故障分析处理经验，曾为多个行业客户大型ORACLE系统实施数据库跨平台迁移，近几年致力于研究解决ORACLE数据库在公安行业的应用及相关问题研究。

环境现状：

OS：AIX 6100

DB：11.2.0.3 RAC ASM存储管理方式

需求：

要把数据库迁移至另外一个机房
存储管理方式弃用ASM，要采用GPFS
停机时间最多2小时

刚接到任务时，第一反应是这么干完全没有必要，后来看见IBM的销售常跑领导办公室，一切都明白了，哪怕是ASM有太多优点，领导都已经拍板了。

经过两天思考，最后定了两个迁移思路，首选是采用rman copy方式，备选是ADG方式，rman copy方式一次停机就能完成，ADG方式则要两次，因为部署的时候要重启实例，要占一次停机，切换的时候还要停一次，中间还要维护ADG不能出问题，所以ADG作为备选。

下面详细说说采用rman copy迁移的过程吧：

在迁移之前，已经安装好了新环境的数据库（和原生产端同版本），IBM工程师也配好了GPFS，GPFS也以客户端（类似NFS吧）方式挂载到了原生产主机。新生产端挂载路径/oradata，原生产端以客户端方式挂载路径/oradata_client。

为了偷懒，在新生产环境使用DBCA创建了同名的数据库，主要是不想再折腾CRS资源管理和参数文件的事了，创建完成后，把数据库起动到nomount状态待用，生成的数据文件和日志文件可以干掉了，使命已经完成，省得占空间。

在原生产库执行控制文件备份：

backup current controlfile format'/oradata_client/ctl';

备出后在新生产库执行恢复：

restore controlfile from '/oradata/ctl';

然后挂载数据库：

mount database;

注意GPFS源端和客户端的路径变化

使用rman copy在原生产端复制出数据文件：

select distinct 'copy datafile'''||name||''' to'''||replace(name,'+DATA/xxdb/datafile/','/oradata_client/')||''''||';' from v$datafile;

以上SQL用于拼接具体执行命令

copy datafile'+DATA/xxdb/datafile/system.609.937843193' to'/oradata_client/system.609.937843193';

copy datafile'+DATA/xxdb/datafile/sysaux.272.937843207' to'/oradata_client/sysaux.272.937843207';

copy datafile'+DATA/xxdb/datafile/undotbs1.300.937843223' to'/oradata_client/undotbs1.300.937843223';

copy datafile'+DATA/xxdb/datafile/users.302.937843229' to '/oradata_client/users.302.937843229';

copy datafile'+DATA/xxdb/datafile/example.299.937843215' to'/oradata_client/example.299.937843215';

copy datafile'+DATA/xxdb/datafile/undotbs2.301.937843225' to'/oradata_client/undotbs2.301.937843225';

copy datafile'+DATA/xxdb/datafile/tt2.493.937845135' to '/oradata_client/tt2.493.937845135';

在新生产环境更改数据文件路径：

select distinct 'alter database rename file'''||name||''' to'''||replace(name,'+DATA/xxdb/datafile/','/oradata/')||''''||';' from v$datafile;

以上SQL用于生成批量更改命令

alter database rename file'+DATA/xxdb/datafile/system.609.937843193' to '/oradata/system.609.937843193';

alter database rename file'+DATA/xxdb/datafile/sysaux.272.937843207' to '/oradata/sysaux.272.937843207';

alter database rename file '+DATA/xxdb/datafile/undotbs1.300.937843223'to '/oradata/undotbs1.300.937843223';

alter database rename file'+DATA/xxdb/datafile/users.302.937843229' to '/oradata/users.302.937843229';

alter database rename file'+DATA/xxdb/datafile/example.299.937843215' to '/oradata/example.299.937843215';

alter database rename file'+DATA/xxdb/datafile/undotbs2.301.937843225' to'/oradata/undotbs2.301.937843225';

alter database rename file'+DATA/xxdb/datafile/tt2.493.937845135' to '/oradata/tt2.493.937845135';

在新生产环境更改REDO文件路径：

select distinct 'alter database rename file'''||member||''' to'''||replace(member,'+DATA/xxdb/onlinelog/','/oradata/')||''''||';' from v$logfile;

以上SQL用于生成批量更改命令

alter database rename file'+DATA/xxdb/onlinelog/group_2.534.937843253' to'/oradata/group_2.534.937843253';

alter database rename file'+DATA/xxdb/onlinelog/group_2.535.937843255' to'/oradata/group_2.535.937843255';

alter database rename file'+DATA/xxdb/onlinelog/group_1.532.937843253' to'/oradata/group_1.532.937843253';

alter database rename file'+DATA/xxdb/onlinelog/group_1.533.937843253' to'/oradata/group_1.533.937843253';

alter database rename file'+DATA/xxdb/onlinelog/group_3.536.937843255' to'/oradata/group_3.536.937843255';

alter database rename file '+DATA/xxdb/onlinelog/group_3.537.937843255'to '/oradata/group_3.537.937843255';

alter database rename file'+DATA/xxdb/onlinelog/group_4.538.937843255' to'/oradata/group_4.538.937843255';

alter database rename file'+DATA/xxdb/onlinelog/group_4.539.937843255' to '/oradata/group_4.539.937843255';

alter database rename file'+DATA/xxdb/onlinelog/group_5.596.937843255' to'/oradata/group_5.596.937843255';

alter database rename file'+DATA/xxdb/onlinelog/group_5.597.937843255' to'/oradata/group_5.597.937843255';

alter database rename file'+DATA/xxdb/onlinelog/group_6.598.937843257' to'/oradata/group_6.598.937843257';

alter database rename file'+DATA/xxdb/onlinelog/group_6.599.937843257' to'/oradata/group_6.599.937843257';

alter database rename file'+DATA/xxdb/onlinelog/group_7.600.937843257' to'/oradata/group_7.600.937843257';

alter database rename file'+DATA/xxdb/onlinelog/group_7.296.937843257' to'/oradata/group_7.296.937843257';

alter database rename file'+DATA/xxdb/onlinelog/group_8.480.937843257' to '/oradata/group_8.480.937843257';

alter database rename file'+DATA/xxdb/onlinelog/group_8.481.937843257' to'/oradata/group_8.481.937843257';

alter database rename file'+DATA/xxdb/onlinelog/group_9.482.937843257' to'/oradata/group_9.482.937843257';

alter database rename file'+DATA/xxdb/onlinelog/group_9.483.937843257' to'/oradata/group_9.483.937843257';

alter database rename file'+DATA/xxdb/onlinelog/group_10.484.937843259' to'/oradata/group_10.484.937843259';

alter database rename file'+DATA/xxdb/onlinelog/group_10.485.937843259' to'/oradata/group_10.485.937843259';

在新生产环境更改临时文件路径：

select 'alter database rename file'''||name||''' to'''||replace(name,'+DATA/xxdb/tempfile/','/oradata/')||''''||';' from v$tempfile;

以上SQL用于生成批量执行命令

alter database rename file'+DATA/xxdb/tempfile/temp.496.937845239' to '/oradata/temp.496.937845239';

在原生产环境复制出自COPY数据文件以来的归档日志：

日志起始编号可以在新生产库执行recover根据报出的日志号确定

select distinct 'copy archivelog'''||name||''' to'''||replace(name,'+DATA/xxdb/archivelog/2017_03_07/','/oradata_client/')||''''||';'from v$archived_log where thread#=1 and sequence#>318;

select distinct 'copy archivelog'''||name||''' to'''||replace(name,'+DATA/xxdb/archivelog/2017_03_07/','/oradata_client/')||''''||';'from v$archived_log where thread#=2 and sequence#>318;

以上SQL用于生成批量命令

copy archivelog'+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_319.477.938012713' to'/oradata_client/thread_1_seq_319.477.938012713';

copy archivelog'+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_320.476.938012715' to'/oradata_client/thread_1_seq_320.476.938012715';

copy archivelog'+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_321.475.938012715' to'/oradata_client/thread_1_seq_321.475.938012715';

copy archivelog'+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_322.474.938012719' to '/oradata_client/thread_1_seq_322.474.938012719';

copy archivelog'+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_323.473.938012719' to'/oradata_client/thread_1_seq_323.473.938012719';

copy archivelog '+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_324.471.938012721'to '/oradata_client/thread_1_seq_324.471.938012721';

copy archivelog'+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_325.470.938012723' to'/oradata_client/thread_1_seq_325.470.938012723';

copy archivelog'+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_326.469.938012725' to'/oradata_client/thread_1_seq_326.469.938012725';

copy archivelog'+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_327.468.938012725' to'/oradata_client/thread_1_seq_327.468.938012725';

copy archivelog'+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_328.467.938012725' to'/oradata_client/thread_1_seq_328.467.938012725';

copy archivelog'+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_329.466.938012743' to'/oradata_client/thread_1_seq_329.466.938012743';

copy archivelog'+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_330.465.938012745' to'/oradata_client/thread_1_seq_330.465.938012745';

copy archivelog'+DATA/xxdb/archivelog/2017_03_07/thread_1_seq_331.464.938012745' to'/oradata_client/thread_1_seq_331.464.938012745';

在新生产端注册后来的控制文件未知的归档日志：

select distinct 'catalog archivelog'''||replace(name,'+DATA/xxdb/archivelog/2017_03_07/','/oradata/')||''''||';' from v$archived_log where thread#=1 andsequence#>=318;

select distinct 'catalog archivelog'''||replace(name,'+DATA/xxdb/archivelog/2017_03_07/','/oradata/')||''''||';' from v$archived_log where thread#=2 andsequence#>=278;

以上SQL用于生成批量命令：

catalog archivelog'/oradata/thread_1_seq_318.479.938002457';

catalog archivelog '/oradata/thread_1_seq_319.477.938012713';

catalog archivelog'/oradata/thread_1_seq_320.476.938012715';

catalog archivelog'/oradata/thread_1_seq_321.475.938012715';

catalog archivelog'/oradata/thread_1_seq_322.474.938012719';

catalog archivelog'/oradata/thread_1_seq_323.473.938012719';

catalog archivelog'/oradata/thread_1_seq_324.471.938012721';

catalog archivelog'/oradata/thread_1_seq_325.470.938012723';

catalog archivelog'/oradata/thread_1_seq_326.469.938012725';

catalog archivelog'/oradata/thread_1_seq_327.468.938012725';

catalog archivelog'/oradata/thread_1_seq_328.467.938012725';