Prerequisites of XTTS

平台字节序和字符集，不支持 Windows 平台，源端与目标端的字符集需相同

-- 查询平台和字节序
col PLATFORM_NAME format a30
SELECT d.PLATFORM_ID,d.PLATFORM_NAME, tp.ENDIAN_FORMAT
 FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

-- 查询字符集
select * from nls_database_parameters where parameter like '%CHARACTERSET%';
select userenv('language') from dual;

数据库时区，源端与目标端的时区需相同
select dbtimezone from dual;
数据库版本及补丁，源端 ≥10.2.0.3 企业版，目标端 ≥11.2.0.4 企业版
select * from v$version;
SELECT * FROM dba_registry_history;

数据库组件，目标端需要包含源端的所有组件

select comp_id,comp_name,version,status from dba_registry;

归档模式，源端需开启归档模式
archive log list;
compatible 参数和 db_files 参数，源端 compatible 参数需要设置为 ≥10.2.0，目标端 compatible 参数和 db_files 参数要 ≥ 源端
show parameter compatible;
show parameter db_files;

需要迁移的表空间和用户

select TABLESPACE_NAME,BLOCK_SIZE,CONTENTS,STATUS
 from dba_tablespaces
 where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','EXAMPLE','SCOTT');

select owner,tablespace_name,count(*)
 from dba_segments
 where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','EXAMPLE','SCOTT')
 group by owner,tablespace_name order by tablespace_name;

确保需要迁移的表空间处于联机可读写模式

select tablespace_name,status from dba_tablespaces
 where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','EXAMPLE','SCOTT');

表空间自包含检查

-- 自包含检查
execute dbms_tts.transport_set_check('TBS_COCO,ITPUX01,ITPUX02,ITPUX03',TRUE);

-- 查看结果，结果为空，表示自包含
col violations for a70
select * from sys.transport_set_violations;

获取用户及其权限的 SQL

SET LONG 9999
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || ';' DDL_SQL
 FROM DBA_USERS WHERE USERNAME
 IN (select owner  from dba_segments where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','EXAMPLE')
 group by owner,tablespace_name) AND USERNAME NOT IN ('SYS','SCOTT');

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || ';' DDL_SQL
 FROM DBA_USERS WHERE USERNAME
 IN (select owner  from dba_segments where  tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','EXAMPLE')
 group by owner,tablespace_name) AND USERNAME NOT IN ('SYS','SCOTT');

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || ';' DDL_SQL
 FROM DBA_USERS WHERE USERNAME
 IN (select owner  from dba_segments where  tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','EXAMPLE')
 group by owner,tablespace_name) AND USERNAME NOT IN ('SYS','SCOTT');

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || ';' DDL_SQL
 FROM DBA_USERS WHERE USERNAME
 IN (select owner  from dba_segments where  tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP','EXAMPLE')
 group by owner,tablespace_name) AND USERNAME NOT IN ('SYS','SCOTT');

对象数量统计

-- 需要迁移的对象和数量
select owner, object_type, count(*)
 from dba_objects
 where object_name not like 'BIN%'
 and owner in ('COCO','ITPUX01','ITPUX02','ITPUX03')
 group by owner, object_type
 order by 1,2 desc;

-- 无效对象
select owner, object_type, count(*)
 from dba_objects
where status <> 'VALID'
 and owner in ('COCO','ITPUX01','ITPUX02','ITPUX03')
group by owner, object_type
order by 1, 2 desc;

-- 非业务用户下是否有业务数据
SELECT *
 FROM dba_objects d
 where d.created >= sysdate - 10
 and d.object_name not like 'WR%'
 and d.owner IN ('SYS','SYSTEM')
 order by d.created desc;

开启块跟踪

select * from v$block_change_tracking;
alter database enable block change tracking using file '+DATA';

目标端调整 job 参数，防止 job 调度任务，修改迁移后的数据
alter system set job_queue_processes=0 sid='*';