墨墨导读:本文来自墨天轮用户“你好我是李白”的投稿,记录一个Oracle数据库迁移过程 :异构传输表空间TTS HP-UX迁移至Redhat Linux 7.7。 墨天轮主页:https://www./u/3997 传输表空间异构跨平台传输表空间分为两种
1. 环境介绍1.1 版本介绍
1.2 迁移方式由于本次迁移为历史库迁移,且数据库未开启归档模式,所以选择较为便捷第二种方式进行迁移。 2. 环境准备2.1 挂载NFS
HP-UX mount -F nfs hard,bg,proto=tcp,rsize=32768,wsize=32768,nointr,noac,forcedirectio,llock xx.xx.xx.xx:/data/nfs /tts Linux: mount -t nfs -o hard,bg,nolock xx.xx.xx.xx:/data/nfs /data 2.2 查询源端平台与目标平台是否可转换select * from v$transportable_platform; PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT ------------ ------------------------------------ -------------- ... 4 HP-UX IA (64-bit) Big ... 13 Linux x86 64-bit Little ...
--可以看到可以转换,但是由于字节序不同,所以需要转换。
select platform_id,platform_name from v$database; PLATFORM_ID PLATFORM_NAME ------------ -------------------- 4 HP-UX IA (64-bit) 3. 迁移准备3.1 提取需要转换的表空间
select owner ,table_name ,tablespace_name from dba_tables where owner not in ('SYS','SYSTEM','OUTLN','SCOTT') and tablespace_name in ('SYSTEM','SYSAUX','USERS');
select distinct tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2') minus select distinct tablespace_name from dba_temp_files; 3.2 验证要传输表空间是否自包含SYS > EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => '<TABLESPACE_NAME>', incl_constraints => TRUE); SYS > SELECT * FROM TRANSPORT_SET_VIOLATIONS; VIOLATIONS -------------------------------------------------------------------- ORA-39921: Default Partition (Table) Tablespace xxx(表空间名) for xxx(表名) not contained in transportable set. # 可以根据下满Mos文档进行相应处理 # 我的环境中,违反表空间不存在,也无相应segment在该表空间,根据表空间名称,创建之后,一起转换解决报错问题。
3.3 表空间置为read onlyselect 'ALTER TABLESPACE '||TABLESPACE_NAME||' READ ONLY;' FROM ( SELECT DISTINCT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE TABLESPACE_NAME NOT IN ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2') MINUS SELECT DISTINCT TABLESPACE_NAME FROM DBA_TEMP_FILES );
'ALTERTABLESPACE'||TABLESPACE_NAME||'READONLY;' ---------------------------------------------------------- ALTER TABLESPACE XXX READ ONLY; ...
SELECT count(*) FROM x$ktuxe WHERE ktuxesta != 'INACTIVE' AND ktuxecfl LIKE '%DEAD%'; SELECT ktuxesiz FROM x$ktuxe WHERE ktuxesta != 'INACTIVE' AND ktuxecfl LIKE '%DEAD%';
select distinct tablespace_name,status from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2') minus select distinct tablespace_name,status from dba_temp_files; 4. 源库转换与元数据导出4.1 rman convert转换
#!/bin/sh # rman convert script export ORACLE_SID= v_date=$(date +%Y%m%d%H%M%S)
echo "Job Start time ${v_date}\n" >> rman_xtts_${v_date}.log rman target / >> rman_xtts_${v_date}.log <<EOF run{ allocate channel c1 type disk; ... convert tablespace tbs1,tbs2... to platform 'Linux x86 64-bit' format '/rman/xtts/xtts_%U.dbf'; release channel c1; ... } EOF v_end_date=$(date +%Y%m%d%H%M%S) echo "Job End Time ${v_date}\n" >> rman_xtts_${v_date}.log 4.2 expdp导出元数据$ nohup expdp system/111111 directory=xtts dumpfile=xtts.dmp parfile=expdp.par logfile=xtts_expdp.log & $ vi expdp.par TRANSPORT_TABLESPACES=tbs1 ,tbs2 ... ,tbsn
Slow DataPump Import (IMPDP) For A Partitioned Table (Doc ID 2014960.1)
Bug 13717234 - Datapump export for transport is slow handling a large number of objects (Doc ID 13717234.8) Transport Tablespace Expdp is Slow when Handling Large Number of Objects in the Database (Doc ID 1554570.1) 5. 目标端准备
5.1 目标端安装oracle软件dbca建库根据安装文档安装oracle软件,dbca创建空库。 5.2 处理users表空间
create tablespace users_xtts datafile 'path' size 1g autoextend on; alter database default tablespace users_xtts; drop tablespace users including contents and datafiles; 5.3 目标端创建用户
# 源端库生成建用户语句,在目标库执行创建用户 select 'create user '||username||' identified by oracle default tablespace users_xtts temporary tablespace temp;' from dba_users where username not in ('SYS','SYSTEM','DBSNMP','OUTLN','ORACLE_OCM','XS$NULL','MDDATA','SPATIAL_UFS_ADMIN_USR','SPATIAL_CSW_ADMIN_USR','MGMT_VIEW','APEX_PUBLIC_USER') AND DEFAULT_TABLESPACE NOT IN ('SYSAUX') ORDER BY TEMPORARY_TABLESPACE;
# 先赋予DBA权限,防止导入时由于缺乏权限失败,待导入数据完成,最后再根据源库权限赋予目标库用户相应权限 select 'grant dba to '||username||';' from dba_users where username not in ('SYS','SYSTEM','DBSNMP','OUTLN','ORACLE_OCM','XS$NULL','MDDATA','SPATIAL_UFS_ADMIN_USR','SPATIAL_CSW_ADMIN_USR','MGMT_VIEW','APEX_PUBLIC_USER') AND DEFAULT_TABLESPACE NOT IN ('SYSAUX') ORDER BY TEMPORARY_TABLESPACE; 6. 目标库导入6.1 impdp导入元数据与datafile# 下面为测试环境示例,并非真实环境,真实环境只是使用路径与名称不同 $ impdp \'\/ as sysdba\' directory=xtts dumpfile=xtts.dmp parfile=xtts.par logfile=impdp_xtts.log
$ vi xtts.par TRANSPORT_DATAFILES= ( '/rman/xtts/xtts_data_D-MESSAY_I-2399541070_TS-XTTS1_FNO-15_04vhgsno.dbf', '/rman/xtts/xtts_data_D-MESSAY_I-2399541070_TS-XTTS_FNO-14_03vhgsnm.dbf', '/rman/xtts/xtts_data_D-MESSAY_I-2399541070_TS-XTTS_FNO-8_02vhgsnl.dbf' )
Import: Release 11.2.0.4.0 - Production on Fri Jan 8 12:40:24 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=xtts dumpfile=xtts.dmp parfile=xtts.par logfile=impdp_xtts.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Fri Jan 8 12:40:34 2021 elapsed 0 00:00:08 6.2 表空间置为读写SYS > alter tablespace tbs1 read write; ... 7. 目标库后续处理7.1 目标库创建临时表空间
# 源库查询是否使用临时表空间组 SQL > select * from DBA_TABLESPACE_GROUPS; # 源库生成创建语句 SELECT 'create temporary tablespace '||TABLESPACE_NAME||' tempfile ''/path/'||tablespace_name||file_id||'.dbf'' size 32767M;' from dba_temp_files order by tablespace_name; # 如果表空间有多个文件,还需要适当修改上述输出。 7.2 目标库赋权# 源库获取用户列表 select username from dba_users where username not in ('SYS','SYSTEM','DBSNMP','OUTLN','ORACLE_OCM','XS$NULL','MDDATA','SPATIAL_UFS_ADMIN_USR','SPATIAL_CSW_ADMIN_USR','MGMT_VIEW','APEX_PUBLIC_USER') AND DEFAULT_TABLESPACE NOT IN ('SYSAUX');
# 源库生成系统权限授权语句 #!/bin/sh # sys privs generate script for c in user1 user2 do sqlplus -S system/oracle >> sys_priv_grants.log <<EOF col PRIVILEGE for a30 col GRANTEE for a20 col ADMIN_OPT for a15 set lines 180 set pagesize 1000 set echo off set heading off set feedback off SELECT 'GRANT '||a.PRIVILEGE||' TO '||'${c}'||';' FROM ( SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '${c}' UNION ALL SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '${c}' AND granted_role not in('EXP_FULL_DATABASE','IMP_FULL_DATABASE')) ) a; exit; EOF done
# 源库生成对象权限授权语句 #!/bin/sh # tab privs generate script for c in user1 user2 do sqlplus -S system/oracle >> object_grants.log <<EOF set lines 180 set pagesize 1000 set echo off set heading off set feedback off col grantee for a15 col owner for a15 col table_name for a30 col grantor for a15 SELECT 'GRANT '||a.PRIVILEGE||' ON '||a.owner||'."'||a.table_name||'" TO '||'${c}'||';' FROM ( SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = '${c}' UNION ALL SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN (SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '${c}' and granted_role not in('EXP_FULL_DATABASE','IMP_FULL_DATABASE')) ) a; exit; EOF done 7.3 回收DBA权限select 'revoke dba from '||username||';' from dba_users where username not in ('SYS','SYSTEM','DBSNMP','OUTLN','ORACLE_OCM','XS$NULL','MDDATA','SPATIAL_UFS_ADMIN_USR','SPATIAL_CSW_ADMIN_USR','MGMT_VIEW','APEX_PUBLIC_USER') AND DEFAULT_TABLESPACE NOT IN ('SYSAUX') ORDER BY TEMPORARY_TABLESPACE; 7.4 目标库表空间置为读写select 'ALTER TABLESPACE '||TABLESPACE_NAME||' READ WRITE;' FROM ( SELECT DISTINCT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE TABLESPACE_NAME NOT IN ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2') MINUS SELECT DISTINCT TABLESPACE_NAME FROM DBA_TEMP_FILES );
SELECT TABLESPACE_NAME,STATUS FROM DBA_TABLESPACES; 7.5 全局临时表、db link 存储过程等对象处理
--源端导出元数据 nohup expdp system/oracle directory=xtts parfile=expdp.par dumpfile=xtts_expdp_%U.dmp cluster=no logfile=xtts_expdp.log & $ vi expdp.par content=metadata_only schemas=user1 ,user2 ... ,userN
--目标端导入 --一些目录对象 nohup impdp system/oracle directory=xtts table_exists_action=append dumpfile=xtts_expdp_%U.dmp logfile=impdp_xtts.log &
7.6 数据比对
-- 源端目标端均执行 -- 会生成user_object_count.log文件,源端目标端比对即可 -- 也可以采用下面比对行数方法创建oracle内部表进行比对 #!/bin/sh # object count scripts for c in user1 user2 do sqlplus -S system/oracle >> users_object_count.log <<EOF set echo off set heading off set lines 180 set pagesize 1000 SELECT DISTINCT OBJECT_TYPE,COUNT(*) FROM dba_objects where owner='${c}' GROUP BY OBJECT_TYPE ORDER BY 1; exit; EOF done
- 比对对象数量过程中遇到如下几个问题 1.table partition数量不一致,经排查,为源端drop了一部分分区表,drop的分区表在查询dba_tab_partitions时,依然可以查到分区,只不过名称都变成了BIN开头。 2.index partition数量不一致,也是由于上述原因,排除BIN$开头索引分区其余一致。 3.部分materialized view、view由于使用db link,而db link由于网络导致创建失败导致失效,后面需要手工迁移db link下面为批量获取源端db link ddl语句 --供参考 declare v_objowner varchar2(50); v_objname varchar2(50); v_test clob; cursor c is select owner,db_link from dba_db_links; begin open c; loop fetch c into v_objowner,v_objname; if c%found then execute immediate 'select dbms_metadata.get_ddl(''DB_LINK'''||','''||v_objname||''','''||v_objowner||''') from dual' into v_test; dbms_output.put_line(v_test||';'); else exit; end if; end loop; close c; end; /
4.目录对象 需要手工在目标端创建。
-- 源端目标端均执行,然后可以将源库table_hash导入目标库通过语句校验。 -- 创建存储表数据量table_hash表 sqlplus -S system/oracle <<EOF CREATE TABLE table_hash(owner varchar2(100),table_name varchar2(100),tc number,primary key(owner,table_name)); EXIT; EOF
-- 计算所有表数据量 for c in user1 user2 do sqlplus -S system/oracle >> user_count.log <<EOF DECLARE v_cc number; CURSOR v_tbl IS SELECT owner,table_name FROM dba_tables WHERE OWNER='${c}'; BEGIN FOR c IN v_tbl LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||c.owner||'.'||'"'||c.table_name||'"' INTO v_cc; INSERT INTO TABLE_HASH VALUES(c.owner,c.table_name,v_cc); END LOOP; COMMIT; END; / EXIT; EOF done
-- 校验 -- 可以在目标库创建源端db link进行比对 -- 也可以将源端比对数据表导入目标库进行比对 COL OWNER FOR A20 COL TABLE_NAME FOR A40 SET LINES 200 PAGES 2000 SELECT DEST.OWNER,DEST.TABLE_NAME,DEST.TC,SOURCE.TC FROM TABLE_HASH DEST, SOURCE.TABLE_HASH SOURCE WHERE DEST.OWNER = SOURCE.OWNER AND DEST.TABLE_NAME = SOURCE.TABLE_NAME AND DEST.TC != SOURCE.TC;
SELECT * FROM SOURCE.TABLE_HASH SOURCE MINUS SELECT * FROM TABLE_HASH DEST;
--如果要计算hash值,计算绝对是否一致需要使用函数进行离线计算或者使用ogg veridata进行比对,SharePlex复制软件也有compare比对功能 7.7 目标库无效对象编译SQL> EXEC UTL_RECOMP.recomp_parallel(4); # 查询是否还有无效对象,再根据具体无效原因进行处理 SQL> select owner,object_type,object_name from dba_objects where status='INVALID'; 7.8 收集统计信息begin dbms_stats.gather_database_stats(estimate_percent=>5,method_opt=>'for all columns size 1',degree=>16,cascade=>true,gather_sys=>true,gather_temp=>false,gather_fixed=>true); end; /
begin dbms_stats.gather_dictionary_stats(method_opt=>'for all columns size 1',degree=>16); end; / 7.9 根据源端调整数据库参数
7.10 修改弱密码
8. 迁移过程遇到问题
8.1 exp 使用tts传输表空间遇到报错# 10g以后引入的分区技术,exp均不支持 EXP-00113: Feature New Composite Partitioning Method is unsupported,table will not be exported
# 11g引入了Virtual Column与基于Virtual Column的分区技术 EXP-00107: Feature(VIRTUAL COLUMN) of column xxx in table xxx.xxx is not supported,table will not be exported
# BINARY_DOUBLE BINARY_FLOAT为Oracle 10g引入的两种数据类型 EXP-00104: datatype (BINARY_DOUBLE) of column xxx in table xxx.xxx is not supported,table will not be exported EXP-00104: datatype (BINARY_FLOAT) of column xxx in table xxx.xxx is not supported,table will not be exported 8.2 expdp导出缓慢
Slow DataPump Import (IMPDP) For A Partitioned Table (Doc ID 2014960.1) # 解决方案: 需要使用非sysdba用户导出 Bug 13717234 - Datapump export for transport is slow handling a large number of objects (Doc ID 13717234.8) Transport Tablespace Expdp is Slow when Handling Large Number of Objects in the Database (Doc ID 1554570.1) # 解决方案:需要为11.2.0.3打补丁patch 13717234 8.3 一些元数据处理
作者 陈振海,4年数据库运维经验,擅长Oracle、MySQL,具有Oracle 11g OCM、OBCA等数据库认证,长期服务于公安、保险、税务等客户。 墨天轮原文链接:https://www./db/45423(复制到浏览器打开或者点击“阅读原文”立即查看) |
|