XTTS 手工RMAN迁移

原创万毓鹏 2020-07-16

2743

如果源端是11.2.0.4及以上，目标是12以上多租户，那么xtts v4就满足。
如果目标不是多租户那么 xtts v3 就满足。
如果源端低于11.2.0.4，目标又是多租户，那么就需要手工方式。这里就写下手工方式。
如果源端是12c的可以使用rman中backup for transport，参考https://dbamarco.wordpress.com/2019/03/
源端和目标端迁移要遵循版本升级路线。
以下为11.2.0.3 AIX 到19 Linux 的步骤。

1.前期准备(重要)

1.1 字符集检查

select * from nls_database_parameters where parameter like '%CHARACTERSET%';

1.2 时区检查

select dbtimezone from dual;

1.3 db_files参数检查

show parameter db_files;

1.4 rman配置检查

RMAN的default device type必须为DISK，并且不能有COMPRESSED属性

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

1.5 平台检查

select PLATFORM_NAME,PLATFORM_ID from v$database;

1.6 表空间datafile 检查

要迁移的表空间必须都处于READ WRITE状态，datafile都必须处于ONLINE状态

select tablespace_name,status from dba_tablespaces where status<>'ONLINE';
select file#,ts#,status from v$datafile where status not in('ONLINE','SYSTEM');

1.7 识别迁移用户

要迁移的业务用户。如果一个用户的对象跨多个表空间这种，要么整合到一起，要么多个表空间迁移

1.8 将非系统对象移除system，sysaux 表空间

1.9 处理系统对象存在于业务表空间

1.10 表空间信息采集

select  name from v$tablespace where name not in ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2','TEMP');

1.11 temp表空间信息采集

select tablespace_name from dba_temp_files;

1.12 修改BCT 加快增量备份

开启块改变跟踪，TRACE文件应位于共享存储上(RAC)

select * from v$BLOCK_CHANGE_TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/XXX/bct.trace';
select * from v$BLOCK_CHANGE_TRACKING;
alter system set "_bct_bitmaps_per_file"=100 sid='*';

1.13 调整job参数 (目标端)

防止job调度任务，修改迁移后的数据。

alter system set job_queue_processes=0 sid='*';

1.14 清理users表空间 (目标端)

users表空间要从源端迁移

CREATE TABLESPACE  newuser DATAFILE '+data' SIZE 1g AUTOEXTEND ON;
ALTER DATABASE DEFAULT TABLESPACE newuser;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;

1.15 创建用户，并全部用户授予dba权限(避免导入元数据时因权限问题报错，后面回收dba权限)

SELECT name, password  FROM sys.user$;
create user xxxx identified by values xxx;
grant dba to xxxxx;

1.16 IOT表检查

select index_name,table_name from dba_indexes where compression='ENABLED';
select owner,table_name from dba_tables where iot_type is not null;

如果存在key compression的索引组织表，目标端需要安装patch 14835322，否则索引组织表无法导入到目标端，需要手动重建，或者通过手工方式导入。

2.RMAN备份

2.1 查询开始的scn

SELECT TO_CHAR(MIN(start_scn) ) AS SCN FROM gv$transaction UNION ALL SELECT TO_CHAR(current_scn) FROM gv$database;

2.2 backup copy

select 'backup as copy datafile ' ||file_id||' format ''/u01/xtts/xtts/datafile/' || tablespace_name||'_'||file_id||''';' from  dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2') ;

可以分成多个rman脚本并行执行
建议最好命名规范，不建议使用自动命名，后面比较麻烦

2.3 rman convert

sql源端执行

set heanding off
set linesize 2000
select 'convert from platform ''AIX-Based Systems (64-bit)'' datafile ''/mnt/dbbackup/xtts/xtts/datafile/'   
|| tablespace_name||'_'||file_id||''''|| ' format ''+DATA/CDBxx/xxxxxx/DATAFILE/' 
 ||  tablespace_name||'_'||file_id||'.dbf;' 
from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2');

脚本目标端执行

2.4 记录scn

SELECT TO_CHAR(MIN(start_scn) ) AS "Please select the minimum SCN" FROM gv$transaction
UNION ALL
SELECT TO_CHAR(current_scn) FROM gv$database;

2.5 备份增量备份

run{
allocate channel c1 device type disk;  
backup incremental from scn xxxx tablespace USERS,TS_DEERP,TS_DEERP_BLOB,TS_DEERP_INX  filesperset 400 
format '/u01/xtts/xtts/datafile_inc/incr_1';
release channel c1;
}

xxxx为rman copy时的 scn
可以按表空间分成多个脚本执行，如果没开bct 数据量大巨慢

2.6 增量convert

目标端执行

set timing on 
spool incr_conv1.log
DECLARE 
   handle    varchar2(512); 
   comment   varchar2(80); 
   media     varchar2(80); 
   concur    boolean; 
   recid     number; 
   stamp     number; 
   pltfrmfr number; 
   devtype   VARCHAR2(512); 
BEGIN
  
   sys.dbms_backup_restore.restoreCancel(TRUE);
   devtype := sys.dbms_backup_restore.deviceAllocate;
   sys.dbms_backup_restore.backupBackupPiece(bpname => '/mnt/dbbackup/xtts/xtts/datafile_inc/xxxx',fname => '/mnt/dbbackup/xtts/xtts/datafile_inc/xxx_con',handle => handle,media=> media,comment=> comment, concur=> concur,recid=> recid,stamp => stamp, check_logical => FALSE,copyno=> 1, deffmt=> 0, copy_recid=> 0,copy_stamp => 0,npieces=> 1,dest=> 0,pltfrmfr=>xxx);

END;
/

xxxx为增量备份文件名称
pltfrmfr=>源端平台ID

2.7 应用convert后的增量备份

目标数据库需要在nomount状态下

set timing on 
set serveroutput on; 
spool apply_incr1.log 
DECLARE 
   outhandle varchar2(512); 
   outtag varchar2(30); 
   done boolean; 
   failover boolean; 
   devtype VARCHAR2(512); 
BEGIN 
   DBMS_OUTPUT.put_line('Entering RollForward'); 
   -- Now the rolling forward. 
   devtype := sys.dbms_backup_restore.deviceAllocate; 
   sys.dbms_backup_restore.applySetDatafile(check_logical => FALSE, cleanup => FALSE);
   DBMS_OUTPUT.put_line('After applySetDataFile'); 
--USR_ARC_IDX,USR_ACC_TBS,EPMGIS_TB,WIPTBS

--USR_ARC_IDX(106)
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>5,toname=>'+DATA/CDBxx/xxxx/DATAFILE/USERS_5.dbf',fuzziness_hint=>0,max_corrupt=>0,islevel0=>0,recid=>0,stamp=>0);
sys.dbms_backup_restore.applyDatafileTo(dfnumber=>253,toname=>'+DATA/CDBxxx/xxxxx/DATAFILE/USERS_253.dbf',fuzziness_hint=>0,max_corrupt=>0,islevel0=>0,recid=>0,stamp=>0);

------------------------------------------------
   DBMS_OUTPUT.put_line('Done: applyDataFileTo'); 
   DBMS_OUTPUT.put_line('Done: applyDataFileTo'); 
   -- Restore Set Piece 
   sys.dbms_backup_restore.restoreSetPiece(handle => '/mnt/dbbackup/xtts/xtts/datafile_inc/xxxxx_con',tag => null, fromdisk => true, recid => 0, stamp => 0); 
   DBMS_OUTPUT.put_line('Done: RestoreSetPiece'); 
   -- Restore Backup Piece 
   sys.dbms_backup_restore.restoreBackupPiece(done => done, params => null, outhandle => outhandle,outtag => outtag, failover => failover); 
   DBMS_OUTPUT.put_line('Done: RestoreBackupPiece'); 
   sys.dbms_backup_restore.restoreCancel(TRUE); 
   sys.dbms_backup_restore.deviceDeallocate; 
END; 
/

2.8 可多次重复4,5,6,7步

3.正式迁移

3.1 源端关闭监听，kill会话，禁用job

3.2 表空间只读

3.3 检查分布式事物，检查表空间只读

3.4 重复2.4,2.5,2.6,2.7

3.5 清空回收站

purge dba_recyclebin;
select count(*) from dba_recyclebin;

3.6 导出元数据

dumpfile =xttdump.dmp
directory=xtts_expdp
statistics =NONE 
transport_tablespaces=USERS,xxxx
transport_full_check=y

dumpfile=other.dmp
logfile=expdpother.log
directory=xtts_expdp
schemas=HSIP,xxxx
content=metadata_only
exclude=table,index,INDEX_STATISTICS,TABLE_STATISTICS 
metrics=y

3.7 导入前创建还原点 (12.2 可pdb闪回)

alter database flashback on;
select flashback_on from v$database;
show parameter recovery; 
alter session set container=pdbxxx;
create restore point before_imp_xtts guarantee flashback database;
select name from v$restore_point;

3.8 导入元数据

directory=xtts_expdp 
dumpfile=xttdump.dmp  
transport_datafiles ='+DATA/CDBRE/91299DE69F14093CE053101119AC2D30/DATAFILE/USERS_5.dbf',\
'+DATA/CDBRE/91299DE69F14093CE053101119AC2D30/DATAFILE/USERS_253.dbf'

如果异常可以闪回

alter pluggable database pdbxxx close immediate;
FLASHBACK PLUGGABLE DATABASE pdbanbob TO RESTORE POINT before_imp_xtts;
alter pluggable database pdbanbob open resetlogs;

3.9 表空间读写

3.10 导入用户数据

impdp xxx/xxx@pdbxxx  dumpfile=other.dmp directory=xtts_expdp logfile=impdpother.log

3.11 修改用户默认的表空间

3.12 删除闪回点

3.13 public对象单独导出导入

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者