一.1 BLOG文档结构图
一.2 前言部分
一.2.1 导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 异构平台下传输表空间的实施
② 传输表空间基于表空间的read only和rman2种方式
③ 平台字节序、自包含概念
④ expdp/impdp的应用
⑤ 数据库迁移一般情况下应该收集哪些信息及相应的脚本
Tips：
① 若文章代码格式有错乱，推荐使用搜狗或360浏览器，也可以下载pdf格式的文档来查看，pdf文档下载地址：http://yunpan.cn/cdEQedhCs2kFz （提取码：ed9b）
② 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方；而命令一般使用黄色背景和红色字体标注；对代码或代码输出部分的注释一般采用蓝色字体表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZFXDESKDB1:root]:/>lsvg -o
T_XDESK_APP1_vg
rootvg
[ZFXDESKDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。
一.2.2 相关参考文章链接
其他异构平台迁移的一些文章参考：
【推荐】 oracle 异构平台迁移之传输表空间一例 http://blog.itpub.net/26736162/viewspace-1391913/
【推荐】 oracle 传输表空间一例 http://blog.itpub.net/26736162/viewspace-1375260/
【推荐】 利用rman来实现linux平台数据库复制到windows平台数据库 http://blog.itpub.net/26736162/viewspace-1352436/
【推荐】 直接复制数据文件实现linux平台数据库复制到windows平台数据库 http://blog.itpub.net/26736162/viewspace-1352243/
【TTS】传输表空间Linux asm -> AIX asm http://blog.itpub.net/26736162/viewspace-1987949/
【TTS】传输表空间Linux asm -> AIX asm 基于rman http://blog.itpub.net/26736162/viewspace-1987953/
【TTS】传输表空间AIX asm -> linux asm http://blog.itpub.net/26736162/viewspace-1987957/
【TTS】传输表空间AIX asm -> linux asm 基于rman http://blog.itpub.net/26736162/viewspace-1987961/
一.3 相关知识点扫盲
可传输表空间的特性主要用于进行库对库的表空间复制，要进行传输的表空间必须置于read-only模式。如果生产库不允许表空间置为只读模式，没关系，方法还是有的，通过RMAN备份也可以创建可传输表空间集。要使用可传输表空间的特性，oracle至少是8i企业版或更高版本。如果是相同操作系统平台相互导入，则8i及以上版本均可支持，但如果是不同操作系统平台，数据库版本至少10g。被传输的表空间即可以是字典管理，也可以是本地管理。并且自oracle9i开始，被传输表空间的block size可以与目标数据库的block size不同。
可传输表空间(还有个集)最大的优势是其速度比export/import或unload/load要快的多。因为可传输表空间主要是复制数据文件到目标路径，然后再使用export/import或Data Pump export/import等应用仅导出/导入表空间对象的元数据到新数据库。
关于可传输表空间，还有个集(Transportable Tablespace Sets)的创建，其中都提到了很重要一点，就是被传输的表空间在传输过程中必须置为 read-only。而在实际操作过程中，对于某些生产数据库，将表空间置为 read-only 是件非常复杂的事情甚至完全不允许，有了 RMAN 的 Transportable Tablespace，这一切都得以避免。RMAN 通过备份创建可传输表空间集，它并不需要存取活动的数据文件，相应也就不需要将表空间置为 read-only。因此，数据库可用性得到提升，尤其对于超大的表空间，因为被传输的表空间在此期间仍可进行读写操作，而且把表空间置为 read-only 模式可能会花费较长时间，
使用 RMAN 创建可传输表空间集，允许你在传输过程中指定目标恢复时间点或 SCN，这样传输的数据可以更灵活，不必完全复制现有表空间，只要备份中存在，你就可以选择性的恢复数据。例如，你的备份策略为保留一周，你希望创建的可传输表空间中数据是截止本月底最后一天的数据，那么你在下个月第一周内任何时候都可以进行传输操作而不需要考虑这期间生产库是否会有写入操作。
一.3.1 注意事项
☞ 注意：
① source和target database的数据库版本最好一致，否则会因为db time zone 不一致导致报如下错误，但是如果source大于等于target的话是可以的，向下兼容的
ORA-39002: invalid operation
ORA-39322: Cannot use transportabletablespace with timestamp with timezone columns and different timezone version.
② source和target端的字符集必须一致，例如如下情况报错：
source为 ZHS16GBK，target为AL32UTF8
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29345: cannot plug a tablespace into a database using an incompatible character set
Tartget db char set AL32UTF8 is not a superset of ZHS16GBK.
Failed to plug in a tablespace due to incompatible
database character set"AL32UTF8" and
transportable set database character set "ZHS16GBK"
③ source和target database的compatible 参数最好一致，但source如果小于等于target端的话是可以的，例如source为11.2.0.4.0，target为11.2.0.0.0就不行，impdp的时候报错：
ORA-39123: Data Pump transportable tablespace job aborted
ORA-00721: changes by release 11.2.0.4.0 cannot be used by release 11.2.0.0.0
一.4 实验部分
一.4.1 迁移环境介绍
项目 |
source db |
target db |
db 类型 |
单实例 |
单实例 |
db version |
11.2.0.3 |
11.2.0.3 |
db 存储 |
ASM |
ASM |
ORACLE_SID |
oraSKY |
oraSKY |
db_name |
ORASKY |
ORASKY |
主机IP地址： |
22.188.139.33 |
192.168.59.30 |
OS版本及kernel版本 |
AIX 64位 5.3.0.0 |
RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64 |
OS hostname |
ZDMTRAIN2 |
rhel6_lhr |
platform_name |
AIX-Based Systems (64-bit) |
Linux x86 64-bit |
compatible |
11.2.0.0.0 |
11.2.0.0.0 |
db time zone |
14 |
14 |
字符集 |
AL32UTF8 |
AL32UTF8 |
归档模式 |
Archive Mode |
Archive Mode |
需迁移的SCHEMA个数 |
3 （T,XPADAD,TEST1） |
3 （T,XPADAD,TEST1） |
需迁移的TS个数 |
3 （USERS，XPADDATA，TEST_USER1） |
3（USERS，XPADDATA，TEST_USER1） |
无效对象个数 |
0 |
0 |
数据文件路径 |
+DATA1/ora11g/datafile/ |
+DATA |
日志及日志组情况 |
+DATA1/ora11g/onlinelog/group_3.388.936264969 +DATA1/ora11g/onlinelog/group_3.389.936264969 +DATA1/ora11g/onlinelog/group_2.386.936264967 +DATA1/ora11g/onlinelog/group_2.387.936264967 +DATA1/ora11g/onlinelog/group_1.384.936264967 +DATA1/ora11g/onlinelog/group_1.385.936264967 |
+DATA |
控制文件 |
+DATA1/ora11g/controlfile/current.381.936264963, +DATA1/ora11g/controlfile/current.383.936264963 |
+DATA |
使用spfile还是pfile |
spfile |
spfile |
需要迁移的库的实际大小 |
100M |
|
表空间总大小 |
14G |
|
需要copy到target端的文件大小 |
450M |
一.4.2 实验目标
在实际的工作过程中，需要将AIX上的数据库迁移到Linux，或者将Linux上的数据库迁移到AIX上，除了exp/imp和expdp/impdp外，最常用的就是传输表空间了，若是整个库迁移的话，我们需要做的就是把业务用户和业务表空间的数据迁移过来就行，Undo、temp、system等等的就不用迁移了，整个处理过程和本文档的处理过程大同小异，需要关注的是业务对象的个数、大小、状态等，本文档要实现将AIX上的数据库oraSKY从源平台传递到目标平台Linux上。
一.4.3 实验过程
-------------------------------------------------------------------------------------------------------------
第二章 AIX平台数据库迁移到Linux--基于RMAN(真实环境)
二.1 源库信息收集
二.1.1 先跑一下健康检查(可选)
根据我自己写的脚本，在源库上跑一下健康检查，主要为了收集一下源库的信息，脚本可以找我私聊，检查后的html文件如下：
例如：
二.1.2 表空间及数据文件情况
二.1.2.1 表空间大小
WITH wt1 AS
(SELECT ts.TABLESPACE_NAME,
df.all_bytes,
decode(df.TYPE,
'D',
nvl(fs.FREESIZ, 0),
'T',
df.all_bytes - nvl(fs.FREESIZ, 0)) FREESIZ,
df.MAXSIZ,
ts.BLOCK_SIZE,
ts.LOGGING,
ts.FORCE_LOGGING,
ts.CONTENTS,
ts.EXTENT_MANAGEMENT,
ts.SEGMENT_SPACE_MANAGEMENT,
ts.RETENTION,
ts.DEF_TAB_COMPRESSION,
ts.STATUS,
df.ts_df_count,
df.FILE_NAME
FROM dba_tablespaces ts,
--表空间大小
(SELECT 'D' TYPE,
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)) MAXSIZ,
to_char(wm_concat(d.FILE_NAME)) FILE_NAME
FROM dba_data_files d
GROUP BY TABLESPACE_NAME
UNION ALL
--临时表空间大小也可以用(SELECT SUM(bytes) FROM v$tempfile)
SELECT 'T',
TABLESPACE_NAME,
COUNT(*) ts_df_count,
SUM(BYTES) all_bytes,
SUM(decode(MAXBYTES, 0, BYTES, MAXBYTES)),
to_char(wm_concat(d.FILE_NAME)) FILE_NAME
FROM dba_temp_files d
GROUP BY TABLESPACE_NAME) df,
--可用空间大小
(SELECT TABLESPACE_NAME,
SUM(BYTES) FREESIZ
FROM dba_free_space
GROUP BY TABLESPACE_NAME
UNION ALL
SELECT tablespace_name,
SUM(d.BLOCK_SIZE * a.BLOCKS) bytes --这里查询出来的是已使用大小
FROM gv$sort_usage a, --或 v$tempseg_usage
dba_tablespaces d
WHERE a.tablespace = d.tablespace_name
GROUP BY tablespace_name) fs
WHERE ts.TABLESPACE_NAME = df.TABLESPACE_NAME
AND ts.TABLESPACE_NAME = fs.TABLESPACE_NAME(+))
SELECT (SELECT A.TS#
FROM V$TABLESPACE A
WHERE A.NAME = UPPER(t.TABLESPACE_NAME)) TS#,
t.TABLESPACE_NAME TS_Name,
t.contents,
round(t.all_bytes / 1024 / 1024) ts_size_M,
round(t.freesiz / 1024 / 1024) Free_Size_M,
round((t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round((t.all_bytes - t.FREESIZ) * 100 / t.all_bytes, 3) Used_per,
round(MAXSIZ / 1024 / 1024 / 1024, 3) MAX_Size_g,
round((MAXSIZ - (t.all_bytes - t.FREESIZ)) / 1024 / 1024 / 1024, 3) MAX_Size_free_g,
round(decode(MAXSIZ, 0, to_number(NULL), (t.all_bytes - FREESIZ)) * 100 /
MAXSIZ,
3) USED_per_MAX,
round(t.BLOCK_SIZE) BLOCK_SIZE,
t.LOGGING,
t.STATUS,
t.ts_df_count,
t.FILE_NAME data_file_name,
t.FORCE_LOGGING,
t.EXTENT_MANAGEMENT,
t.SEGMENT_SPACE_MANAGEMENT,
t.RETENTION,
t.DEF_TAB_COMPRESSION
FROM wt1 t
UNION ALL
SELECT to_number('') TS#,
'所有表空间' TS_Name,
'' contents,
round(SUM(t.all_bytes) / 1024 / 1024, 3) ts_size_M,
round(SUM(t.freesiz) / 1024 / 1024) Free_Size_m,
round(SUM(t.all_bytes - t.FREESIZ) / 1024 / 1024) Used_Size_M,
round(SUM(t.all_bytes - t.FREESIZ) * 100 / SUM(t.all_bytes), 3) Used_per,
round(SUM(MAXSIZ) / 1024 / 1024 / 1024) MAX_Size,
round((SUM(MAXSIZ) - SUM(t.all_bytes - t.FREESIZ)) / 1024 / 1024 / 1024,
3) MAX_Size_free_g,
to_number('') "USED,% of MAX Size",
to_number('') BLOCK_SIZE,
'' LOGGING,
'' STATUS,
to_number('') ts_df_count,
'' data_file_name,
'' FORCE_LOGGING,
'' EXTENT_MANAGEMENT,
'' SEGMENT_SPACE_MANAGEMENT,
'' RETENTION,
'' DEF_TAB_COMPRESSION
FROM wt1 t
ORDER BY TS#;
由此可以看出，真正迁移的数据大约为100M，但是表空间有14G，就是说本地文件最少需要14G+100M的空间才能完成后续的操作。
二.1.2.2 需要传输的数据文件大小
SELECT d.FILE_ID,
d.TABLESPACE_NAME,
(SELECT (SUM(nb.BYTES/1024/1024))
FROM dba_data_files nb
WHERE nb.TABLESPACE_NAME = d.TABLESPACE_NAME) ts_size_m,
d.FILE_NAME,
(d.BYTES/1024/1024) file_size_m,
(d.USER_BYTES/1024/1024) file_use_size_m
FROM dba_data_files d
WHERE d.TABLESPACE_NAME in ('USERS','XPADDATA','TEST_USER1')
ORDER BY file_id;
也就是说最终需要拷贝到target端的数据文件大小为450M。
二.1.3 用户情况（密码、默认表空间、角色和权限，需迁移的schema对象大小、个数、列表）
二.1.3.1 需要迁移的用户
SELECT d.username,
d.default_tablespace,
D.temporary_tablespace,
d.account_status
FROM dba_users d
WHERE d.account_status = 'OPEN'
and d.username not like '%SYS%';
二.1.3.2 用户权限
drop table t_tmp_user_lhr;
create table t_tmp_user_lhr( id number, username varchar2(50), exec_sql varchar2(4000),create_typevarchar2(20) );
DROP sequence s_t_tmp_user_lhr;
create sequence s_t_tmp_user_lhr;
begin
for cur in (SELECT d.username,
d.default_tablespace,
d.account_status,
'create user ' || d.username || ' identified by ' ||
d.username || ' default tablespace ' ||
d.default_tablespace || ' TEMPORARY TABLESPACE ' ||
D.temporary_tablespace || ';' CREATE_USER,
replace(to_char(DBMS_METADATA.GET_DDL('USER',
D.username)),
chr(10),
'') create_USER1
FROM dba_users d
WHERE d.username in ('T', 'XPADAD', 'TEST1')) loop
INSERT INTO t_tmp_user_lhr
(id, username, exec_sql, create_type)
values
(s_t_tmp_user_lhr.nextval, cur.username, cur.CREATE_USER, 'USER');
INSERT INTO t_tmp_user_lhr
(id, username, exec_sql, create_type)
SELECT s_t_tmp_user_lhr.nextval,
cur.username,
CASE
WHEN D.ADMIN_OPTION = 'YES' THEN
'GRANT ' || d.privilege || ' TO ' || d.GRANTEE ||
' WITH GRANT OPTION ;'
ELSE
'GRANT ' || d.privilege || ' TO ' || d.GRANTEE || ';'
END priv,
'DBA_SYS_PRIVS'
FROM dba_sys_privs d
WHERE D.GRANTEE = CUR.USERNAME;
INSERT INTO t_tmp_user_lhr
(id, username, exec_sql, create_type)
SELECT s_t_tmp_user_lhr.nextval,
cur.username,
CASE
WHEN D.ADMIN_OPTION = 'YES' THEN
'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE ||
' WITH GRANT OPTION;'
ELSE
'GRANT ' || d.GRANTED_ROLE || ' TO ' || d.GRANTEE || ';'
END priv,
'DBA_ROLE_PRIVS'
FROM DBA_ROLE_PRIVS d
WHERE D.GRANTEE = CUR.USERNAME;
INSERT INTO t_tmp_user_lhr
(id, username, exec_sql, create_type)
SELECT s_t_tmp_user_lhr.nextval,
cur.username,
CASE
WHEN d.grantable = 'YES' THEN
'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||
d.table_name || ' TO ' || d.GRANTEE ||
' WITH GRANT OPTION ;'
ELSE
'GRANT ' || d.privilege || ' ON ' || d.owner || '.' ||
d.table_name || ' TO ' || d.GRANTEE || ';'
END priv,
'DBA_TAB_PRIVS'
FROM DBA_TAB_PRIVS d
WHERE D.GRANTEE = CUR.USERNAME;
end loop;
COMMIT;
end;
/
SELECT * FROM t_tmp_user_lhr;
create user TEST1 identified by TEST1 default tablespace TEST_USER1 TEMPORARY TABLESPACE TEMP;
GRANT UNLIMITED TABLESPACE TO TEST1;
GRANT CONNECT TO TEST1;
GRANT RESOURCE TO TEST1;
GRANT WRITE ON SYS.TEST_DIR TO TEST1;
GRANT READ ON SYS.TEST_DIR TO TEST1;
GRANT WRITE ON SYS.TEST_LOG TO TEST1;
GRANT READ ON SYS.TEST_LOG TO TEST1;
create user XPADAD identified by XPADAD default tablespace XPADDATA TEMPORARY TABLESPACE TEMP;
GRANT CREATE VIEW TO XPADAD;
GRANT UNLIMITED TABLESPACE TO XPADAD;
GRANT CREATE DATABASE LINK TO XPADAD;
GRANT DBA TO XPADAD;
GRANT CONNECT TO XPADAD;
GRANT RESOURCE TO XPADAD;
create user T identified by T default tablespace USERS TEMPORARY TABLESPACE TEMP;
GRANT UNLIMITED TABLESPACE TO T;
GRANT RESOURCE TO T;
GRANT CONNECT TO T;
GRANT WRITE ON SYS.TT TO T;
GRANT READ ON SYS.TT TO T;
二.1.3.3 用户表大小
select d.owner, (sum(bytes) / 1024 / 1024) sizes_m
from dba_segments d
where d.owner in ('T', 'XPADAD', 'TEST1')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.segment_name ANDD.OWNER=B.owner)
GROUP BY d.owner
order by sum(bytes) desc;
;
二.1.3.4 对象个数
SELECT D.OWNER,COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME ANDD.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER ;
SELECT D.OWNER, D.OBJECT_TYPE, COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1
FROM DBA_RECYCLEBIN B
WHERE B.object_name = D.OBJECT_NAME
AND D.OWNER = B.owner)
GROUP BY D.OWNER, D.OBJECT_TYPE
ORDER BY D.OWNER;
二.1.3.5 对象详细信息
---- 以下数据导出到excel表格备份
SELECT d.OWNER, d.OBJECT_NAME, d.SUBOBJECT_NAME, d.OBJECT_TYPE,d.status
FROM dba_objects d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME ANDD.OWNER=B.owner)
ORDER BY D.OWNER ;
OWNER |
OBJECT_NAME |
SUBOBJECT_NAME |
OBJECT_TYPE |
STATUS |
|
1 |
T |
T1_IND |
INDEX |
VALID |
|
2 |
T |
TTT |
TABLE |
VALID |
|
3 |
T |
MONTH_PART |
SYS_P65 |
TABLE PARTITION |
VALID |
4 |
T |
MONTH_PART |
SYS_P64 |
TABLE PARTITION |
VALID |
5 |
T |
MONTH_PART |
SYS_P63 |
TABLE PARTITION |
VALID |
6 |
T |
MONTH_PART |
SYS_P61 |
TABLE PARTITION |
VALID |
7 |
T |
MONTH_PART |
TABLE |
VALID |
|
8 |
T |
T1 |
TABLE |
VALID |
|
9 |
T |
PT1 |
PT1_20161001 |
TABLE PARTITION |
VALID |
10 |
T |
PT1 |
PT1_20250918 |
TABLE PARTITION |
VALID |
11 |
T |
PT1 |
PT1_20250620 |
TABLE PARTITION |
VALID |
12 |
T |
PT1 |
TABLE |
VALID |
|
13 |
T |
PT1_IND1 |
INDEX |
VALID |
|
14 |
T |
PT2 |
PT1_20161001 |
TABLE PARTITION |
VALID |
15 |
T |
PT2 |
PT1_20250918 |
TABLE PARTITION |
VALID |
16 |
T |
PT2 |
PT1_20250620 |
TABLE PARTITION |
VALID |
17 |
T |
PT2 |
TABLE |
VALID |
|
18 |
T |
PT2_IND1 |
INDEX |
VALID |
|
19 |
T |
MONTH_PART |
PART2 |
TABLE PARTITION |
VALID |
20 |
T |
MONTH_PART |
PART1 |
TABLE PARTITION |
VALID |
21 |
TEST1 |
TEST |
TABLE |
VALID |
|
22 |
TEST1 |
TEST_TABLE |
TABLE |
VALID |
|
23 |
XPADAD |
WH_CONCAT_IMPL_LHR |
TYPE BODY |
VALID |
|
24 |
XPADAD |
WH_CONCAT_IMPL_LHR |
TYPE |
VALID |
|
25 |
XPADAD |
TEST |
TABLE |
VALID |
|
26 |
XPADAD |
WH_CONCAT_LHR |
FUNCTION |
VALID |
SELECT d.owner,
d.segment_name,
d.partition_name,
d.segment_type,
d.tablespace_name,
d.BYTES
FROM dba_segments d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.segment_name ANDD.OWNER=B.owner)
ORDER BY D.OWNER ;
OWNER |
SEGMENT_NAME |
PARTITION_NAME |
SEGMENT_TYPE |
TABLESPACE_NAME |
BYTES |
|
1 |
T |
T1 |
TABLE |
USERS |
65536 |
|
2 |
T |
PT2 |
PT1_20250918 |
TABLE PARTITION |
USERS |
8388608 |
3 |
T |
PT1_IND1 |
INDEX |
USERS |
65536 |
|
4 |
T |
PT2_IND1 |
INDEX |
USERS |
65536 |
|
5 |
T |
TTT |
TABLE |
USERS |
65536 |
|
6 |
T |
PT1 |
PT1_20250620 |
TABLE PARTITION |
USERS |
8388608 |
7 |
T |
PT1 |
PT1_20250918 |
TABLE PARTITION |
USERS |
8388608 |
8 |
T |
PT1 |
PT1_20161001 |
TABLE PARTITION |
USERS |
8388608 |
9 |
T |
PT2 |
PT1_20250620 |
TABLE PARTITION |
USERS |
8388608 |
10 |
T |
T1_IND |
INDEX |
USERS |
65536 |
|
11 |
T |
PT2 |
PT1_20161001 |
TABLE PARTITION |
USERS |
8388608 |
12 |
T |
MONTH_PART |
PART1 |
TABLE PARTITION |
USERS |
8388608 |
13 |
T |
MONTH_PART |
PART2 |
TABLE PARTITION |
USERS |
8388608 |
14 |
T |
MONTH_PART |
SYS_P61 |
TABLE PARTITION |
USERS |
8388608 |
15 |
T |
MONTH_PART |
SYS_P63 |
TABLE PARTITION |
USERS |
8388608 |
16 |
T |
MONTH_PART |
SYS_P64 |
TABLE PARTITION |
USERS |
8388608 |
17 |
T |
MONTH_PART |
SYS_P65 |
TABLE PARTITION |
USERS |
8388608 |
18 |
TEST1 |
TEST |
TABLE |
TEST_USER1 |
9437184 |
|
19 |
TEST1 |
TEST_TABLE |
TABLE |
TEST_USER1 |
65536 |
|
20 |
XPADAD |
TEST |
TABLE |
XPADDATA |
9437184 |
二.1.4 无效对象情况
SELECT owner owner,
count(1)
FROM dba_objects d
WHERE status <> 'VALID'
and d.OWNER in ('T', 'XPADAD', 'TEST1')
AND D.OWNER NOT IN ('PUBLIC')
group by d.OWNER
ORDER BY owner;
SELECT owner owner,
object_name,
object_type,
status,
'alter ' || decode(object_type,
'PACKAGE BODY',
'PACKAGE',
'TYPE BODY',
'TYPE',
object_type) || ' ' || owner || '.' ||
object_name || ' ' ||
decode(object_type, 'PACKAGE BODY', 'compile body', 'compile') || ';' hands_on
FROM dba_objects d
WHERE status <> 'VALID'
and d.OWNER in ('T', 'XPADAD', 'TEST1')
ORDER BY owner, object_name;
二.1.5 索引情况
SELECT D.OWNER,COUNT(1)
FROM dba_indexes d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.index_name ANDD.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER ;
二.1.6 确定是否有业务数据、脚本在例如sys用户等的默认用户下
跟开放确认是否有业务数据表在sys等默认用户下，若有是否需要迁移。
二.2 判断平台支持并确定字节序
如果传输表空间集到不同的平台，则要确定对于源和目标平台这种跨平台表空间被支持，也要确定每个平台的字节序，如果平台具有相同的字节序，则不需要进行转化，否则必须做一个表空间集转化，在源端或目标端都可以进行转换。
col platform_name for a40
select d.platform_name,tp.endian_format from vtransportableplatformtp,vtransportableplatformtp,vdatabase d
where tp.platform_name=d.platform_name;
col platform_name for a40
select tp.platform_name, tp.endian_format
from v$transportable_platform tp
where tp.platform_name in ('Linux x86 64-bit', 'AIX-Based Systems (64-bit)');
SQL> col platform_name for a40
SQL> select tp.platform_name, tp.endian_format
2 from v$transportable_platform tp
3 where tp.platform_name in ('Linux x86 64-bit', 'AIX-Based Systems (64-bit)');
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- ----------------------------
AIX-Based Systems (64-bit) Big
Linux x86 64-bit Little
SQL>
可以看到source端的字节序为Big，而target端的字节序为Little，所以需要进行字节序的转换，前边说过在源端或目标端都可以进行转换，这里我们选择在目标端来进行转换。
二.3 判断表空间集是否自包含
Indicates whether a full or partial dependency check is required. If TRUE, treats all IN and OUT pointers(dependencies) and captures them as violations if they are not self-contained in the transportable set.
execute sys.dbms_tts.transport_set_check('TEST_USER1,USERS,XPADDATA',true);
col violations for a70
select * from sys.transport_set_violations;
oracle@ZDMTRAIN2:/oracle$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 17 16:59:34 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@oraSKY> exec sys.dbms_tts.transport_set_check('TEST_USER1,USERS,XPADDATA',true);
PL/SQL procedure successfully completed.
SYS@oraSKY> col violations for a70
SYS@oraSKY> select * from sys.transport_set_violations;
no rows selected
SYS@oraSKY>
结论： 此时这个表空间集已经不再违背自包含的条件，可以确定为一个可传输表空间集。在实际生产环境中也是如此检查的，若是全库迁移，得把需要迁移的表空间修改为自包含的。
二.4 产生可传输表空间集
二.4.1 rman备份source库
当然，如果已经有全库备份了就可以省略这个步骤。
oracle@ZDMTRAIN2:/oracle$ mkdir -p /lxm/oracle_bk/
oracle@ZDMTRAIN2:/oracle$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 17 17:14:24 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORASKY (DBID=98202371)
RMAN> backup as compressed backupset format '/lxm/oracle_bk/full_%n_%T_%t_%s.bak' database include current controlfile plus archivelog delete input ;
Starting backup at 2017-02-17 17:14:34
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1716 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=28 RECID=3 STAMP=936206075
channel ORA_DISK_1: starting piece 1 at 2017-02-17 17:14:36
channel ORA_DISK_1: finished piece 1 at 2017-02-17 17:14:37
piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936206076_11.bak tag=TAG20170217T171436 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/oracle/app/oracle/product/11.2.0/db/dbs/arch1_28_1268916931.dbf RECID=3 STAMP=936206075
Finished backup at 2017-02-17 17:14:37
Starting backup at 2017-02-17 17:14:37
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA1/orasky/datafile/system.288.1268916951
input datafile file number=00002 name=+DATA1/orasky/datafile/sysaux.271.1268916981
input datafile file number=00003 name=+DATA1/orasky/datafile/undotbs1.270.1268917011
input datafile file number=00004 name=+DATA1/orasky/datafile/users.257.1268917057
input datafile file number=00005 name=+DATA1/orasky/datafile/xpaddata.258.917189283
input datafile file number=00006 name=+DATA1/orasky/datafile/xpaddata.330.917189289
input datafile file number=00007 name=+DATA1/orasky/datafile/xpaddata.323.917189295
input datafile file number=00008 name=+DATA1/orasky/datafile/xpaddata.320.917189301
input datafile file number=00009 name=+DATA1/orasky/datafile/xpaddata.307.917189307
input datafile file number=00010 name=+DATA1/orasky/datafile/xpaddata.301.917189313
input datafile file number=00011 name=+DATA1/orasky/datafile/xpaddata.298.917189319
input datafile file number=00012 name=+DATA1/orasky/datafile/xpaddata.261.917189327
input datafile file number=00013 name=+DATA1/orasky/datafile/xpaddata.259.917189333
input datafile file number=00014 name=+DATA1/orasky/datafile/xpaddata.262.917189339
input datafile file number=00015 name=+DATA1/orasky/datafile/xpadindex.269.917189671
input datafile file number=00016 name=+DATA1/orasky/datafile/xpadindex.263.917189713
input datafile file number=00017 name=+DATA1/orasky/datafile/xpadindex.264.917189751
input datafile file number=00018 name=+DATA1/orasky/datafile/xpadindex.265.917189757
input datafile file number=00019 name=+DATA1/orasky/datafile/xpadindex.266.917189763
input datafile file number=00020 name=+DATA1/orasky/datafile/xpadtemp.375.917189803
input datafile file number=00021 name=+DATA1/orasky/datafile/xpadtemp.376.917189809
input datafile file number=00022 name=+DATA1/orasky/datafile/test_user1.377.921670035
channel ORA_DISK_1: starting piece 1 at 2017-02-17 17:14:39
channel ORA_DISK_1: finished piece 1 at 2017-02-17 17:15:54
piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936206078_12.bak tag=TAG20170217T171438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2017-02-17 17:15:55
channel ORA_DISK_1: finished piece 1 at 2017-02-17 17:15:56
piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936206154_13.bak tag=TAG20170217T171438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-02-17 17:15:56
Starting backup at 2017-02-17 17:15:56
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=29 RECID=4 STAMP=936206156
channel ORA_DISK_1: starting piece 1 at 2017-02-17 17:15:56
channel ORA_DISK_1: finished piece 1 at 2017-02-17 17:15:57
piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936206156_14.bak tag=TAG20170217T171556 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/oracle/app/oracle/product/11.2.0/db/dbs/arch1_29_1268916931.dbf RECID=4 STAMP=936206156
Finished backup at 2017-02-17 17:15:57
RMAN> list backupset;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
6 2.50K DISK 00:00:00 2017-02-17 17:14:36
BP Key: 6 Status: AVAILABLE Compressed: YES Tag: TAG20170217T171436
Piece Name: /lxm/oracle_bk/full_ORASKYxx_20170217_936206076_11.bak
List of Archived Logs in backup set 6
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 28 8892357 2017-02-17 17:13:28 8892419 2017-02-17 17:14:34
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
7 Full 266.39M DISK 00:01:13 2017-02-17 17:15:51
BP Key: 7 Status: AVAILABLE Compressed: YES Tag: TAG20170217T171438
Piece Name: /lxm/oracle_bk/full_ORASKYxx_20170217_936206078_12.bak
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/system.288.1268916951
2 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/sysaux.271.1268916981
3 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/undotbs1.270.1268917011
4 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/users.257.1268917057
5 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.258.917189283
6 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.330.917189289
7 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.323.917189295
8 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.320.917189301
9 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.307.917189307
10 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.301.917189313
11 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.298.917189319
12 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.261.917189327
13 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.259.917189333
14 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpaddata.262.917189339
15 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadindex.269.917189671
16 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadindex.263.917189713
17 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadindex.264.917189751
18 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadindex.265.917189757
19 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadindex.266.917189763
20 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadtemp.375.917189803
21 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/xpadtemp.376.917189809
22 Full 8892431 2017-02-17 17:14:39 +DATA1/orasky/datafile/test_user1.377.921670035
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
8 Full 1.03M DISK 00:00:01 2017-02-17 17:15:55
BP Key: 8 Status: AVAILABLE Compressed: YES Tag: TAG20170217T171438
Piece Name: /lxm/oracle_bk/full_ORASKYxx_20170217_936206154_13.bak
SPFILE Included: Modification time: 2017-02-17 16:28:52
SPFILE db_unique_name: ORASKY
Control File Included: Ckp SCN: 8892462 Ckp time: 2017-02-17 17:15:54
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
9 2.00K DISK 00:00:00 2017-02-17 17:15:56
BP Key: 9 Status: AVAILABLE Compressed: YES Tag: TAG20170217T171556
Piece Name: /lxm/oracle_bk/full_ORASKYxx_20170217_936206156_14.bak
List of Archived Logs in backup set 9
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 29 8892419 2017-02-17 17:14:34 8892467 2017-02-17 17:15:56
RMAN> exit
Recovery Manager complete.
oracle@ZDMTRAIN2:/oracle$
二.4.2 transport tablespace 生成文件
oracle@ZDMTRAIN2:/oracle/transportdest$ df -g
oracle@ZDMTRAIN2:/oracle/transportdest$
oracle@ZDMTRAIN2:/oracle/app$ df -g
Filesystem GB blocks Free %Used Iused %Iused Mounted on
/dev/hd4 6.00 2.29 62% 12356 3% /
《《《《。。。。。。。。篇幅原因，有省略。。。。。。。。》》》》
/dev/Tlv_fta 8.00 7.74 4% 2627 1% /fta
/dev/fslv100 0.12 0.12 1% 9 1% /zling
/dev/lxmlv 20.00 19.73 2% 18 1% /lxm
22.188.189.42:/privatebk 8000.00 7954.59 1% 4381 1% /privatebk
oracle@ZDMTRAIN2:/oracle/app$
oracle@ZDMTRAIN2:/oracle$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Feb 17 18:07:19 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORASKY (DBID=98202371)
RMAN> transport tablespace TEST_USER1,USERS,XPADDATA tablespace destination '/lxm/transportdest' auxiliary destination '/lxm/transportdest';
using target database control file instead of recovery catalog
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='wmch'
initialization parameters used for automatic instance:
db_name=ORASKY
db_unique_name=wmch_tspitr_ORASKY
compatible=11.2.0.0.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/lxm/transportdest
log_archive_dest_1='location=/lxm/transportdest'
#No auxiliary parameter file used
starting up automatic instance ORASKY
Oracle instance started
Total System Global Area 292278272 bytes
Fixed Size 2220880 bytes
Variable Size 100666544 bytes
Database Buffers 184549376 bytes
Redo Buffers 4841472 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully
contents of Memory Script:
{
# set requested point in time
set until scn 8915883;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 2017-02-17 18:07:39
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=80 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /lxm/oracle_bk/full_ORASKYxx_20170217_936208508_21.bak
channel ORA_AUX_DISK_1: piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936208508_21.bak tag=TAG20170217T175351
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/lxm/transportdest/ORASKY/controlfile/o1_mf_dbflvdrg_.ctl
Finished restore at 2017-02-17 18:07:41
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until scn 8915883;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 22 to
"/lxm/transportdest/o1_mf_test_use_%u_.dbf";
set newname for datafile 4 to
"/lxm/transportdest/o1_mf_users_%u_.dbf";
set newname for datafile 5 to
"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";
set newname for datafile 6 to
"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";
set newname for datafile 7 to
"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";
set newname for datafile 8 to
"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";
set newname for datafile 9 to
"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";
set newname for datafile 10 to
"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";
set newname for datafile 11 to
"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";
set newname for datafile 12 to
"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";
set newname for datafile 13 to
"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";
set newname for datafile 14 to
"/lxm/transportdest/o1_mf_xpaddata_%u_.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 22, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /lxm/transportdest/ORASKY/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2017-02-17 18:07:49
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /lxm/transportdest/ORASKY/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /lxm/transportdest/ORASKY/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /lxm/transportdest/ORASKY/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00022 to /lxm/transportdest/o1_mf_test_use_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /lxm/transportdest/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00012 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00013 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00014 to /lxm/transportdest/o1_mf_xpaddata_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /lxm/oracle_bk/full_ORASKYxx_20170217_936208432_20.bak
channel ORA_AUX_DISK_1: piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936208432_20.bak tag=TAG20170217T175351
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:03:09
Finished restore at 2017-02-17 18:11:04
datafile 1 switched to datafile copy
input datafile copy RECID=16 STAMP=936209464 file name=/lxm/transportdest/ORASKY/datafile/o1_mf_system_dbflvvqx_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=936209464 file name=/lxm/transportdest/ORASKY/datafile/o1_mf_undotbs1_dbflvvr9_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=18 STAMP=936209464 file name=/lxm/transportdest/ORASKY/datafile/o1_mf_sysaux_dbflvvr1_.dbf
datafile 22 switched to datafile copy
input datafile copy RECID=19 STAMP=936209464 file name=/lxm/transportdest/o1_mf_test_use_dbflvw0f_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=20 STAMP=936209464 file name=/lxm/transportdest/o1_mf_users_dbflvvv1_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=21 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvw2j_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=22 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvw2s_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=23 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvw3p_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=24 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvwhy_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=25 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvwpy_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=26 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvwrv_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=27 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvx6o_.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=28 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvxgk_.dbf
datafile 13 switched to datafile copy
input datafile copy RECID=29 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvxjw_.dbf
datafile 14 switched to datafile copy
input datafile copy RECID=30 STAMP=936209464 file name=/lxm/transportdest/o1_mf_xpaddata_dbflvy06_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 8915883;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 22 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 5 online";
sql clone "alter database datafile 6 online";
sql clone "alter database datafile 7 online";
sql clone "alter database datafile 8 online";
sql clone "alter database datafile 9 online";
sql clone "alter database datafile 10 online";
sql clone "alter database datafile 11 online";
sql clone "alter database datafile 12 online";
sql clone "alter database datafile 13 online";
sql clone "alter database datafile 14 online";
# recover and open resetlogs
recover clone database tablespace "TEST_USER1", "USERS", "XPADDATA", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 22 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 6 online
sql statement: alter database datafile 7 online
sql statement: alter database datafile 8 online
sql statement: alter database datafile 9 online
sql statement: alter database datafile 10 online
sql statement: alter database datafile 11 online
sql statement: alter database datafile 12 online
sql statement: alter database datafile 13 online
sql statement: alter database datafile 14 online
Starting recover at 2017-02-17 18:11:06
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=36
channel ORA_AUX_DISK_1: reading from backup piece /lxm/oracle_bk/full_ORASKYxx_20170217_936208510_22.bak
channel ORA_AUX_DISK_1: piece handle=/lxm/oracle_bk/full_ORASKYxx_20170217_936208510_22.bak tag=TAG20170217T175510
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/lxm/transportdest/1_36_1268916931.dbf thread=1 sequence=36
channel clone_default: deleting archived log(s)
archived log file name=/lxm/transportdest/1_36_1268916931.dbf RECID=11 STAMP=936209471
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-02-17 18:11:12
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace TEST_USER1 read only';
sql clone 'alter tablespace USERS read only';
sql clone 'alter tablespace XPADDATA read only';
# create directory for datapump export
sql clone "create or replace directory STREAMS_DIROBJ_DPDIR as ''
/lxm/transportdest''";
}
executing Memory Script
sql statement: alter tablespace TEST_USER1 read only
sql statement: alter tablespace USERS read only
sql statement: alter tablespace XPADDATA read only
sql statement: create or replace directory STREAMS_DIROBJ_DPDIR as ''/lxm/transportdest''
Performing export of metadata...
EXPDP> Starting "SYS"."TSPITR_EXP_wmch":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_wmch" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_wmch is:
EXPDP> /lxm/transportdest/dmpfile.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TEST_USER1:
EXPDP> /lxm/transportdest/o1_mf_test_use_dbflvw0f_.dbf
EXPDP> Datafiles required for transportable tablespace USERS:
EXPDP> /lxm/transportdest/o1_mf_users_dbflvvv1_.dbf
EXPDP> Datafiles required for transportable tablespace XPADDATA:
EXPDP> /lxm/transportdest/o1_mf_xpaddata_dbflvw2j_.dbf
EXPDP> /lxm/transportdest/o1_mf_xpaddata_dbflvw2s_.dbf
EXPDP> /lxm/transportdest/o1_mf_xpaddata_dbflvw3p_.dbf
EXPDP> /lxm/transportdest/o1_mf_xpaddata_dbflvwhy_.dbf
EXPDP> /lxm/transportdest/o1_mf_xpaddata_dbflvwpy_.dbf
EXPDP> /lxm/transportdest/o1_mf_xpaddata_dbflvwrv_.dbf
EXPDP> /lxm/transportdest/o1_mf_xpaddata_dbflvx6o_.dbf
EXPDP> /lxm/transportdest/o1_mf_xpaddata_dbflvxgk_.dbf
EXPDP> /lxm/transportdest/o1_mf_xpaddata_dbflvxjw_.dbf
EXPDP> /lxm/transportdest/o1_mf_xpaddata_dbflvy06_.dbf
EXPDP> Job "SYS"."TSPITR_EXP_wmch" successfully completed at 18:13:03
Export completed
/*
The following command may be used to import the tablespaces.
Substitute values for <logon> and <directory>.
impdp <logon> directory=<directory> dumpfile= 'dmpfile.dmp' transport_datafiles= /lxm/transportdest/o1_mf_test_use_dbflvw0f_.dbf, /lxm/transportdest/o1_mf_users_dbflvvv1_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvw2j_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvw2s_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvw3p_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvwhy_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvwpy_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvwrv_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvx6o_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvxgk_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvxjw_.dbf, /lxm/transportdest/o1_mf_xpaddata_dbflvy06_.dbf
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMSDIROBJDIROBJ1 AS '/lxm/transportdest/';
CREATE DIRECTORY STREAMSDIROBJDIROBJDPDIR AS '/lxm/transportdest';
/* PL/SQL Script to import the exported tablespaces */
DECLARE
-- the datafiles
tbs_files dbms_streams_tablespace_adm.file_set;
cvt_files dbms_streams_tablespace_adm.file_set;
-- the dumpfile to import
dump_file dbms_streams_tablespace_adm.file;
dp_job_name VARCHAR2(30) := NULL;
-- names of tablespaces that were imported
ts_names dbms_streams_tablespace_adm.tablespace_set;
BEGIN
-- dump file name and location
dump_file.file_name := 'dmpfile.dmp';
dump_file.directory_object := 'STREAMSDIROBJDIROBJDPDIR';
-- forming list of datafiles for import
tbs_files( 1).file_name := 'o1_mf_test_use_dbflvw0f_.dbf';
tbs_files( 1).directory_object := 'STREAMSDIROBJDIROBJ1';
tbs_files( 2).file_name := 'o1_mf_users_dbflvvv1_.dbf';
tbs_files( 2).directory_object := 'STREAMSDIROBJDIROBJ1';
tbs_files( 3).file_name := 'o1_mf_xpaddata_dbflvw2j_.dbf';
tbs_files( 3).directory_object := 'STREAMSDIROBJDIROBJ1';
tbs_files( 4).file_name := 'o1_mf_xpaddata_dbflvw2s_.dbf';
tbs_files( 4).directory_object := 'STREAMSDIROBJDIROBJ1';
tbs_files( 5).file_name := 'o1_mf_xpaddata_dbflvw3p_.dbf';
tbs_files( 5).directory_object := 'STREAMSDIROBJDIROBJ1';
tbs_files( 6).file_name := 'o1_mf_xpaddata_dbflvwhy_.dbf';
tbs_files( 6).directory_object := 'STREAMSDIROBJDIROBJ1';
tbs_files( 7).file_name := 'o1_mf_xpaddata_dbflvwpy_.dbf';
tbs_files( 7).directory_object := 'STREAMSDIROBJDIROBJ1';
tbs_files( 8).file_name := 'o1_mf_xpaddata_dbflvwrv_.dbf';
tbs_files( 8).directory_object := 'STREAMSDIROBJDIROBJ1';
tbs_files( 9).file_name := 'o1_mf_xpaddata_dbflvx6o_.dbf';
tbs_files( 9).directory_object := 'STREAMSDIROBJDIROBJ1';
tbs_files( 10).file_name := 'o1_mf_xpaddata_dbflvxgk_.dbf';
tbs_files( 10).directory_object := 'STREAMSDIROBJDIROBJ1';
tbs_files( 11).file_name := 'o1_mf_xpaddata_dbflvxjw_.dbf';
tbs_files( 11).directory_object := 'STREAMSDIROBJDIROBJ1';
tbs_files( 12).file_name := 'o1_mf_xpaddata_dbflvy06_.dbf';
tbs_files( 12).directory_object := 'STREAMSDIROBJDIROBJ1';
-- import tablespaces
dbms_streams_tablespace_adm.attach_tablespaces(
datapump_job_name => dp_job_name,
dump_file => dump_file,
tablespace_files => tbs_files,
converted_files => cvt_files,
tablespace_names => ts_names);
-- output names of imported tablespaces
IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
FOR i IN ts_names.first .. ts_names.last LOOP
dbms_output.put_line('imported tablespace '|| ts_names(i));
END LOOP;
END IF;
END;
/
-- dropping directory objects
DROP DIRECTORY STREAMSDIROBJDIROBJ1;
DROP DIRECTORY STREAMSDIROBJDIROBJDPDIR;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------
Removing automatic instance
shutting down automatic instance
database closed
database dismounted
Oracle instance shut down
Automatic instance removed
auxiliary instance file /lxm/transportdest/ORASKY/datafile/o1_mf_temp_dbfm2j8n_.tmp deleted
auxiliary instance file /lxm/transportdest/ORASKY/onlinelog/o1_mf_3_dbfm29jx_.log deleted
auxiliary instance file /lxm/transportdest/ORASKY/onlinelog/o1_mf_2_dbfm258v_.log deleted
auxiliary instance file /lxm/transportdest/ORASKY/onlinelog/o1_mf_1_dbfm20wr_.log deleted
auxiliary instance file /lxm/transportdest/ORASKY/datafile/o1_mf_sysaux_dbflvvr1_.dbf deleted
auxiliary instance file /lxm/transportdest/ORASKY/datafile/o1_mf_undotbs1_dbflvvr9_.dbf deleted
auxiliary instance file /lxm/transportdest/ORASKY/datafile/o1_mf_system_dbflvvqx_.dbf deleted
auxiliary instance file /lxm/transportdest/ORASKY/controlfile/o1_mf_dbflvdrg_.ctl deleted
RMAN>
---------执行过程查看文件的大小
root@ZDMTRAIN2:/# df -g
Filesystem GB blocks Free %Used Iused %Iused Mounted on
《《《《。。。。。。。。篇幅原因，有省略。。。。。。。。》》》》
/dev/fslv100 0.12 0.12 1% 9 1% /zling
/dev/lxmlv 20.00 5.78 72% 43 1% /lxm
22.188.189.42:/privatebk 8000.00 7954.59 1% 4381 1% /privatebk
22.188.189.42:/publicbk 8000.00 186.82 98% 21670216 34% /publicbk
ZTDNETAP3:/nfs 1240.00 25.15 98% 509154 8% /nfs
22.188.129.202:/nfs 1240.00 25.15 98% 509154 8% /nfs
root@ZDMTRAIN2:/#
完成后文件大小：
root@ZDMTRAIN2:/lxm/transportdest# df -g
Filesystem GB blocks Free %Used Iused %Iused Mounted on
《《《《。。。。。。。。篇幅原因，有省略。。。。。。。。》》》》
/dev/Tlv_fta 8.00 7.74 4% 2627 1% /fta
/dev/fslv100 0.12 0.12 1% 9 1% /zling
/dev/lxmlv 20.00 19.29 4% 36 1% /lxm
22.188.189.42:/privatebk 8000.00 7954.59 1% 4381 1% /privatebk
22.188.189.42:/publicbk 8000.00 186.82 98% 21670216 34% /publicbk
/dev/Tlv_zca 4.00 4.00 1% 17 1% /zca
/dev/Tlv_tt 10.00 9.05 10% 18 1% /tt
ZTINIMSERVER:/sharebkup 5500.00 1629.20 71% 2455829 1% /sharebkup
ZTDNETAP3:/nfs 1240.00 25.15 98% 509154 8% /nfs
22.188.129.202:/nfs 1240.00 25.15 98% 509154 8% /nfs
root@ZDMTRAIN2:/lxm/transportdest#
至此，已和源库没有任何关系。
二.5 传输数据文件和元数据到target端
这里需要传输转储元文件和数据文件到目标库
二.5.1 dbca创建target库
[oracle@rhel6_lhr dbca]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbname oraSKY -sid oraSKY -sysPassword lhr -systemPassword lhr -responseFile NO_VALUE -datafileDestination 'DATA/' -redoLogFileSize 50 -recoveryAreaDestination 'FRA/' -storageType ASM -asmsnmpPassword lhr -diskGroupName 'DATA' -characterSet AL32UTF8 -nationalCharacterSet AL16UTF16 -sampleSchema true -memoryPercentage 20 -totalMemory 200 -databaseType OLTP -emConfiguration NONE -automaticMemoryManagement true
Copying database files
1% complete
3% complete
35% complete
Creating and starting Oracle instance
37% complete
42% complete
47% complete
52% complete
53% complete
56% complete
58% complete
Registering database with Oracle Restart
64% complete
Completing Database Creation
68% complete
71% complete
75% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/oraSKY/oraSKY.log" for further details.
[oracle@rhel6_lhr dbca]$ ORACLE_SID=oraSKY
[oracle@rhel6_lhr dbca]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期三 2月 3 00:14:49 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
00:14:49 SYS@oraSKY > archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 3
下一个存档日志序列 5
当前日志序列 5
00:14:53 SYS@oraSKY >
二.5.2 查看目标库数据文件位置和导入目录
[oracle@rhel6_lhr dbs]echoechoORACLE_SID
oraSKY
[oracle@rhel6_lhr dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 2月 2 22:58:34 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
00:14:53 SYS@oraSKY > select name from v$datafile;
NAME
-------------------------------------------------
+DATA/orasky/datafile/system.295.902793257
+DATA/orasky/datafile/sysaux.294.902793261
+DATA/orasky/datafile/undotbs1.293.902793263
+DATA/orasky/datafile/users.292.902793265
+DATA/orasky/datafile/example.274.902793775
已用时间: 00: 00: 00.03
00:15:31 SYS@oraSKY >
SYS@oraSKY > select directory_name,directory_path from dba_directories;
DIRECTORY_NAME DIRECTORY_PATH
------------------------------ -------------------------------------------------------------------------------
SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep
SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/
LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/
MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/
XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
DATA_PUMP_DIR /u01/app/oracle/admin/oraSKY/dpdump/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
已选择8行。
二.5.3 利用ftp工具传输转储元文件到目标库
二.5.4 拷贝文件到目标库相应位置并修改文件权限
[root@rhel6_lhr ~]# cd /u01/app/oracle/admin/oraSKY/dpdump
[root@rhel6_lhr dpdump]# ll
total 461108
-rw-r--r-- 1 root root 204800 Feb 17 2017 dmpfile.dmp
-rw-r--r-- 1 root root 3943 Feb 17 2017 impscrpt.sql
-rw-r--r-- 1 root root 104865792 Feb 17 2017 o1_mf_test_use_dbflvw0f_.dbf
-rw-r--r-- 1 root root 314580992 Feb 17 2017 o1_mf_users_dbflvvv1_.dbf
-rw-r--r-- 1 root root 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvw2j_.dbf
-rw-r--r-- 1 root root 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvw2s_.dbf
-rw-r--r-- 1 root root 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvw3p_.dbf
-rw-r--r-- 1 root root 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvwhy_.dbf
-rw-r--r-- 1 root root 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvwpy_.dbf
-rw-r--r-- 1 root root 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvwrv_.dbf
-rw-r--r-- 1 root root 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvx6o_.dbf
-rw-r--r-- 1 root root 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvxgk_.dbf
-rw-r--r-- 1 root root 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvxjw_.dbf
-rw-r--r-- 1 root root 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvy06_.dbf
[root@rhel6_lhr dpdump]# chown oracle:dba *
[root@rhel6_lhr dpdump]# ll
total 461108
-rw-r--r-- 1 oracle dba 204800 Feb 17 2017 dmpfile.dmp
-rw-r--r-- 1 oracle dba 3943 Feb 17 2017 impscrpt.sql
-rw-r--r-- 1 oracle dba 104865792 Feb 17 2017 o1_mf_test_use_dbflvw0f_.dbf
-rw-r--r-- 1 oracle dba 314580992 Feb 17 2017 o1_mf_users_dbflvvv1_.dbf
-rw-r--r-- 1 oracle dba 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvw2j_.dbf
-rw-r--r-- 1 oracle dba 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvw2s_.dbf
-rw-r--r-- 1 oracle dba 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvw3p_.dbf
-rw-r--r-- 1 oracle dba 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvwhy_.dbf
-rw-r--r-- 1 oracle dba 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvwpy_.dbf
-rw-r--r-- 1 oracle dba 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvwrv_.dbf
-rw-r--r-- 1 oracle dba 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvx6o_.dbf
-rw-r--r-- 1 oracle dba 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvxgk_.dbf
-rw-r--r-- 1 oracle dba 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvxjw_.dbf
-rw-r--r-- 1 oracle dba 5251072 Feb 17 2017 o1_mf_xpaddata_dbflvy06_.dbf
[root@rhel6_lhr dpdump]#
二.6 target端转换字节序
[oracle@rhel6_lhr dbca]$ rman target /
恢复管理器: Release 11.2.0.3.0 - Production on 星期三 2月 3 00:24:06 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库: ORASKY (DBID=4027046368)
RMAN> CONVERT DATAFILE
2> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_test_use_dbflvw0f_.dbf",
3> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_users_dbflvvv1_.dbf",
4> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2j_.dbf",
5> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2s_.dbf",
6> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw3p_.dbf",
7> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwhy_.dbf",
8> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwpy_.dbf",
9> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwrv_.dbf",
10> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvx6o_.dbf",
11> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxgk_.dbf",
12> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxjw_.dbf",
13> "/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvy06_.dbf"
14> TO PLATFORM="Linux x86 64-bit"
15> FROM PLATFORM="AIX-Based Systems (64-bit)"
16> FORMAT '+DATA';
启动 conversion at target 于 2016-02-03 00:24:09
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=147 设备类型=DISK
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_users_dbflvvv1_.dbf
已转换的数据文件 = +DATA/orasky/datafile/users.280.902795051
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:45
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_test_use_dbflvw0f_.dbf
已转换的数据文件 = +DATA/orasky/datafile/test_user1.278.902795095
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:25
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2j_.dbf
已转换的数据文件 = +DATA/orasky/datafile/xpaddata.277.902795121
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw2s_.dbf
已转换的数据文件 = +DATA/orasky/datafile/xpaddata.276.902795121
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvw3p_.dbf
已转换的数据文件 = +DATA/orasky/datafile/xpaddata.275.902795123
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:02
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwhy_.dbf
已转换的数据文件 = +DATA/orasky/datafile/xpaddata.270.902795125
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwpy_.dbf
已转换的数据文件 = +DATA/orasky/datafile/xpaddata.267.902795125
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvwrv_.dbf
已转换的数据文件 = +DATA/orasky/datafile/xpaddata.268.902795127
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvx6o_.dbf
已转换的数据文件 = +DATA/orasky/datafile/xpaddata.281.902795127
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxgk_.dbf
已转换的数据文件 = +DATA/orasky/datafile/xpaddata.296.902795129
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:04
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvxjw_.dbf
已转换的数据文件 = +DATA/orasky/datafile/xpaddata.297.902795133
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
通道 ORA_DISK_1: 启动数据文件转换
输入文件名=/u01/app/oracle/admin/oraSKY/dpdump/o1_mf_xpaddata_dbflvy06_.dbf
已转换的数据文件 = +DATA/orasky/datafile/xpaddata.298.902795133
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
完成 conversion at target 于 2016-02-03 00:25:34
RMAN>
[grid@rhel6_lhr ~]$ asmcmd
[grid@rhel6_lhr asmdisk]$ cd
[grid@rhel6_lhr ~]$ asmcmd
ASMCMD> cd +data/ORASKY/datafile
ASMCMD> ls -lt
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.298.902795133
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.297.902795133
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.296.902795129
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.281.902795127
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.277.902795121
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.276.902795121
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.275.902795123
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.270.902795125
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.268.902795127
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y XPADDATA.267.902795125
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y USERS.292.902793265
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y USERS.280.902795051
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y UNDOTBS1.293.902793263
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y TEST_USER1.278.902795095
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y SYSTEM.295.902793257
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y SYSAUX.294.902793261
DATAFILE MIRROR COARSE FEB 03 00:00:00 Y EXAMPLE.274.902793775
ASMCMD>
ASMCMD>
二.7 开始导入
二.7.1 创建source库的需要迁移的3个用户并赋权限(前边的脚本已经生成，直接拿过来执行)
如果不创建用户会报如下的错误：
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29342: user USER_APP1 does not exist in the database
create user TEST1 identified by TEST1 TEMPORARY TABLESPACE TEMP;
GRANT UNLIMITED TABLESPACE TO TEST1;
GRANT CONNECT TO TEST1;
GRANT RESOURCE TO TEST1;
GRANT WRITE ON SYS.TEST_DIR TO TEST1;
GRANT READ ON SYS.TEST_DIR TO TEST1;
GRANT WRITE ON SYS.TEST_LOG TO TEST1;
GRANT READ ON SYS.TEST_LOG TO TEST1;
create user XPADAD identified by XPADAD TEMPORARY TABLESPACE TEMP;
GRANT CREATE VIEW TO XPADAD;
GRANT UNLIMITED TABLESPACE TO XPADAD;
GRANT CREATE DATABASE LINK TO XPADAD;
GRANT DBA TO XPADAD;
GRANT CONNECT TO XPADAD;
GRANT RESOURCE TO XPADAD;
create user T identified by T default TEMPORARY TABLESPACE TEMP;
GRANT UNLIMITED TABLESPACE TO T;
GRANT RESOURCE TO T;
GRANT CONNECT TO T;
GRANT WRITE ON SYS.TT TO T;
GRANT READ ON SYS.TT TO T;
二.7.2 开始导入
[oracle@rhel6_lhr dbca]$ impdp \'/ as sysdba \' DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES='+data/ORASKY/datafile/XPADDATA.298.902795133','+data/ORASKY/datafile/XPADDATA.297.902795133','+data/ORASKY/datafile/XPADDATA.296.902795129','+data/ORASKY/datafile/XPADDATA.281.902795127','+data/ORASKY/datafile/XPADDATA.277.902795121','+data/ORASKY/datafile/XPADDATA.276.902795121','+data/ORASKY/datafile/XPADDATA.275.902795123','+data/ORASKY/datafile/XPADDATA.270.902795125','+data/ORASKY/datafile/XPADDATA.268.902795127','+data/ORASKY/datafile/XPADDATA.267.902795125','+data/ORASKY/datafile/USERS.292.902793265','+data/ORASKY/datafile/TEST_USER1.278.902795095' LOGFILE=impdp_tts_20160202.log
Import: Release 11.2.0.3.0 - Production on 星期三 2月 3 00:35:45 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=+data/ORASKY/datafile/XPADDATA.298.902795133,+data/ORASKY/datafile/XPADDATA.297.902795133,+data/ORASKY/datafile/XPADDATA.296.902795129,+data/ORASKY/datafile/XPADDATA.281.902795127,+data/ORASKY/datafile/XPADDATA.277.902795121,+data/ORASKY/datafile/XPADDATA.276.902795121,+data/ORASKY/datafile/XPADDATA.275.902795123,+data/ORASKY/datafile/XPADDATA.270.902795125,+data/ORASKY/datafile/XPADDATA.268.902795127,+data/ORASKY/datafile/XPADDATA.267.902795125,+data/ORASKY/datafile/USERS.292.902793265,+data/ORASKY/datafile/TEST_USER1.278.902795095 LOGFILE=impdp_tts_20160202.log
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 数据泵可传输的表空间作业中止
ORA-29349: 表空间 'USERS' 已存在
作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 00:35:50 停止
users表空间已经存在了，这里把target端的users表空间重命名一下就可以了：
[oracle@rhel6_lhr dbca]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期三 2月 3 00:36:26 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@oraSKY > alter tablespace users rename to users01;
表空间已更改。
SYS@oraSKY > exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options 断开
[oracle@rhel6_lhr dbca]$ impdp \'/ as sysdba \' DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES='+data/ORASKY/datafile/XPADDATA.298.902795133','+data/ORASKY/datafile/XPADDATA.297.902795133','+data/ORASKY/datafile/XPADDATA.296.902795129','+data/ORASKY/datafile/XPADDATA.281.902795127','+data/ORASKY/datafile/XPADDATA.277.902795121','+data/ORASKY/datafile/XPADDATA.276.902795121','+data/ORASKY/datafile/XPADDATA.275.902795123','+data/ORASKY/datafile/XPADDATA.270.902795125','+data/ORASKY/datafile/XPADDATA.268.902795127','+data/ORASKY/datafile/XPADDATA.267.902795125','+data/ORASKY/datafile/USERS.280.902795051','+data/ORASKY/datafile/TEST_USER1.278.902795095' LOGFILE=impdp_tts_20160202.log
Import: Release 11.2.0.3.0 - Production on 星期三 2月 3 00:40:46 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=+data/ORASKY/datafile/XPADDATA.298.902795133,+data/ORASKY/datafile/XPADDATA.297.902795133,+data/ORASKY/datafile/XPADDATA.296.902795129,+data/ORASKY/datafile/XPADDATA.281.902795127,+data/ORASKY/datafile/XPADDATA.277.902795121,+data/ORASKY/datafile/XPADDATA.276.902795121,+data/ORASKY/datafile/XPADDATA.275.902795123,+data/ORASKY/datafile/XPADDATA.270.902795125,+data/ORASKY/datafile/XPADDATA.268.902795127,+data/ORASKY/datafile/XPADDATA.267.902795125,+data/ORASKY/datafile/USERS.280.902795051,+data/ORASKY/datafile/TEST_USER1.278.902795095 LOGFILE=impdp_tts_20160202.log
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
ORA-39151: 表 "SCOTT"."EMP" 已存在。由于跳过了 table_exists_action, 将跳过所有相关元数据和数据。
处理对象类型 TRANSPORTABLE_EXPORT/INDEX/INDEX
处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 已经完成, 但是有 1 个错误 (于 00:40:51 完成)
[oracle@rhel6_lhr dbca]$
[oracle@rhel6_lhr dbca]$
[ZFXDESKDB2:oracle]:/oracle>
二.7.2.1 报错：source和target的compatible参数不同引起ora-00721错误
[oracle@rhel6_lhr dbs]$ impdp \'/ as sysdba \' DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES='+DATA/orclasm/datafile/app1tbs.271.90278175','+DATA/orclasm/datafile/APP2TBS.276.902781757','+DATA/orclasm/datafile/IDXTBS.279.902781761' LOGFILE=impdp_tts_20160202.log version=latest
Import: Release 11.2.0.3.0 - Production on 星期二 2月 2 21:04:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" DUMPFILE=dmpfile.dmp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=+DATA/orclasm/datafile/app1tbs.271.90278175,+DATA/orclasm/datafile/APP2TBS.276.902781757,+DATA/orclasm/datafile/IDXTBS.279.902781761 LOGFILE=impdp_tts_20160202.log version=latest
处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 数据泵可传输的表空间作业中止
ORA-00721: 发行版 11.2.0.4.0 中的更改无法用于发行版 11.2.0.3.0
作业 "SYS"."SYS_IMPORT_TRANSPORTABLE_01" 因致命错误于 21:04:37 停止
[oracle@rhel6_lhr dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期二 2月 2 21:04:58 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
21:04:58 SYS@orclasm > show parameter com
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offload_compaction string ADAPTIVE
commit_logging string
commit_point_strength integer 1
commit_wait string
commit_write string
compatible string 11.2.0.3.0
nls_comp string BINARY
plsql_v2_compatibility boolean FALSE
21:05:03 SYS@orclasm >
解决办法：保持source和target的版本一致，或source端小于等于target端，若版本一致，则修改target端的compatible参数和source端一致。
二.7.3 查看目标平台信息
[oracle@rhel6_lhr dbca]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期三 2月 3 00:42:23 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SYS@oraSKY > select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS01 ONLINE
EXAMPLE ONLINE
TEST_USER1 READ ONLY
USERS READ ONLY
XPADDATA READ ONLY
已选择9行。
SYS@oraSKY > alter tablespace TEST_USER1 read write;
表空间已更改。
SYS@oraSKY > alter tablespace USERS read write;
表空间已更改。
SYS@oraSKY > alter tablespace XPADDATA read write;
表空间已更改。
SYS@oraSKY > select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------------------------------------ ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS01 ONLINE
EXAMPLE ONLINE
TEST_USER1 ONLINE
USERS ONLINE
XPADDATA ONLINE
已选择9行。
二.8 导入完成后的结果校验
二.8.1 校验用户情况（密码、默认表空间、角色和权限，需迁移的schema对象大小、个数、列表）
二.8.1.1 校验用户
SELECT d.username,
d.default_tablespace,
D.temporary_tablespace,
d.account_status
FROM dba_users d
WHERE d.account_status = 'OPEN'
and d.username in ('T','TEST1','XPADAD');
SQL> alter user T default tablespace users;
User altered.
SQL> alter user XPADAD default tablespace XPADDATA;
User altered.
SQL> alter user TEST1 default tablespace TEST_USER1;
User altered.
SQL>
二.8.1.2 用户对象个数
SELECT D.OWNER,COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME ANDD.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER ;
SELECT D.OWNER, D.OBJECT_TYPE, COUNT(1)
FROM dba_objects d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1
FROM DBA_RECYCLEBIN B
WHERE B.object_name = D.OBJECT_NAME
AND D.OWNER = B.owner)
GROUP BY D.OWNER, D.OBJECT_TYPE
ORDER BY D.OWNER;
二.8.1.3 对象详细信息
---- 以下数据导出到excel表格备份
SELECT d.OWNER, d.OBJECT_NAME, d.SUBOBJECT_NAME, d.OBJECT_TYPE,d.status
FROM dba_objects d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.OBJECT_NAME ANDD.OWNER=B.owner)
ORDER BY D.OWNER ;
OWNER |
OBJECT_NAME |
SUBOBJECT_NAME |
OBJECT_TYPE |
STATUS |
|
1 |
T |
T1_IND |
INDEX |
VALID |
|
2 |
T |
TTT |
TABLE |
VALID |
|
3 |
T |
MONTH_PART |
SYS_P65 |
TABLE PARTITION |
VALID |
4 |
T |
MONTH_PART |
SYS_P64 |
TABLE PARTITION |
VALID |
5 |
T |
MONTH_PART |
SYS_P63 |
TABLE PARTITION |
VALID |
6 |
T |
MONTH_PART |
SYS_P61 |
TABLE PARTITION |
VALID |
7 |
T |
MONTH_PART |
TABLE |
VALID |
|
8 |
T |
T1 |
TABLE |
VALID |
|
9 |
T |
PT1 |
PT1_20161001 |
TABLE PARTITION |
VALID |
10 |
T |
PT1 |
PT1_20250918 |
TABLE PARTITION |
VALID |
11 |
T |
PT1 |
PT1_20250620 |
TABLE PARTITION |
VALID |
12 |
T |
PT1 |
TABLE |
VALID |
|
13 |
T |
PT1_IND1 |
INDEX |
VALID |
|
14 |
T |
PT2 |
PT1_20161001 |
TABLE PARTITION |
VALID |
15 |
T |
PT2 |
PT1_20250918 |
TABLE PARTITION |
VALID |
16 |
T |
PT2 |
PT1_20250620 |
TABLE PARTITION |
VALID |
17 |
T |
PT2 |
TABLE |
VALID |
|
18 |
T |
PT2_IND1 |
INDEX |
VALID |
|
19 |
T |
MONTH_PART |
PART2 |
TABLE PARTITION |
VALID |
20 |
T |
MONTH_PART |
PART1 |
TABLE PARTITION |
VALID |
21 |
TEST1 |
TEST |
TABLE |
VALID |
|
22 |
TEST1 |
TEST_TABLE |
TABLE |
VALID |
|
23 |
XPADAD |
WH_CONCAT_IMPL_LHR |
TYPE BODY |
VALID |
|
24 |
XPADAD |
WH_CONCAT_IMPL_LHR |
TYPE |
VALID |
|
25 |
XPADAD |
TEST |
TABLE |
VALID |
|
26 |
XPADAD |
WH_CONCAT_LHR |
FUNCTION |
VALID |
SELECT d.owner,
d.segment_name,
d.partition_name,
d.segment_type,
d.tablespace_name,
d.BYTES
FROM dba_segments d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.segment_name ANDD.OWNER=B.owner)
ORDER BY D.OWNER ;
OWNER |
SEGMENT_NAME |
PARTITION_NAME |
SEGMENT_TYPE |
TABLESPACE_NAME |
BYTES |
|
1 |
T |
T1 |
TABLE |
USERS |
65536 |
|
2 |
T |
PT2 |
PT1_20250918 |
TABLE PARTITION |
USERS |
8388608 |
3 |
T |
PT1_IND1 |
INDEX |
USERS |
65536 |
|
4 |
T |
PT2_IND1 |
INDEX |
USERS |
65536 |
|
5 |
T |
TTT |
TABLE |
USERS |
65536 |
|
6 |
T |
PT1 |
PT1_20250620 |
TABLE PARTITION |
USERS |
8388608 |
7 |
T |
PT1 |
PT1_20250918 |
TABLE PARTITION |
USERS |
8388608 |
8 |
T |
PT1 |
PT1_20161001 |
TABLE PARTITION |
USERS |
8388608 |
9 |
T |
PT2 |
PT1_20250620 |
TABLE PARTITION |
USERS |
8388608 |
10 |
T |
T1_IND |
INDEX |
USERS |
65536 |
|
11 |
T |
PT2 |
PT1_20161001 |
TABLE PARTITION |
USERS |
8388608 |
12 |
T |
MONTH_PART |
PART1 |
TABLE PARTITION |
USERS |
8388608 |
13 |
T |
MONTH_PART |
PART2 |
TABLE PARTITION |
USERS |
8388608 |
14 |
T |
MONTH_PART |
SYS_P61 |
TABLE PARTITION |
USERS |
8388608 |
15 |
T |
MONTH_PART |
SYS_P63 |
TABLE PARTITION |
USERS |
8388608 |
16 |
T |
MONTH_PART |
SYS_P64 |
TABLE PARTITION |
USERS |
8388608 |
17 |
T |
MONTH_PART |
SYS_P65 |
TABLE PARTITION |
USERS |
8388608 |
18 |
TEST1 |
TEST |
TABLE |
TEST_USER1 |
9437184 |
|
19 |
TEST1 |
TEST_TABLE |
TABLE |
TEST_USER1 |
65536 |
|
20 |
XPADAD |
TEST |
TABLE |
XPADDATA |
9437184 |
二.8.2 无效对象情况
SELECT owner owner,
count(1)
FROM dba_objects d
WHERE status <> 'VALID'
and d.OWNER in ('T', 'XPADAD', 'TEST1')
AND D.OWNER NOT IN ('PUBLIC')
group by d.OWNER
ORDER BY owner;
SELECT owner owner,
object_name,
object_type,
status,
'alter ' || decode(object_type,
'PACKAGE BODY',
'PACKAGE',
'TYPE BODY',
'TYPE',
object_type) || ' ' || owner || '.' ||
object_name || ' ' ||
decode(object_type, 'PACKAGE BODY', 'compile body', 'compile') || ';' hands_on
FROM dba_objects d
WHERE status <> 'VALID'
and d.OWNER in ('T', 'XPADAD', 'TEST1')
ORDER BY owner, object_name;
二.8.3 索引情况
SELECT D.OWNER,COUNT(1)
FROM dba_indexes d
WHERE d.OWNER in ('T', 'XPADAD', 'TEST1')
and d.OWNER not in ('PUBLIC')
AND NOT EXISTS (SELECT 1 FROM DBA_RECYCLEBIN B WHERE B.object_name=D.index_name ANDD.OWNER=B.owner)
GROUP BY D.OWNER
ORDER BY D.OWNER ;
二.9 迁移后续收尾工作
确保数据已经完全迁移到新的主机上后，接下来就是一些琐碎的收尾工作，包括sys密码，监听，job，crontab等等工作。
-------------------------------------------------------------------------------------------------------------
二.10 总结
到此所有的处理算是基本完毕，过程很简单，但是不同的场景处理方式有很多种，我们应该学会灵活变通。