合【TTS】传输表空间 Linux和AIX 之间传输数据

发布日期 2022年11月11日 · 已更新 2022年11月11日

0 334 1

👉 本文共约114163个字，系统预计阅读时间或需430分钟。

实验部分
迁移环境介绍
实验目标
源库信息收集
先跑一下健康检查(可选)
表空间及数据文件情况
表空间大小
需要传输的数据文件大小
用户情况（密码、默认表空间、角色和权限，需迁移的schema对象大小、个数、列表）
需要迁移的用户
用户权限
用户表大小
对象个数
对象详细信息
无效对象情况
索引情况
确定是否有业务数据、脚本在例如sys用户等的默认用户下
判断平台支持并确定字节序
判断表空间集是否自包含
产生可传输表空间集
rman备份source库
transport tablespace 生成文件
传输数据文件和元数据到target端
dbca创建target库
查看目标库数据文件位置和导入目录
利用ftp工具传输转储元文件到目标库
拷贝文件到目标库相应位置并修改文件权限
target端转换字节序
开始导入
创建source库的需要迁移的3个用户并赋权限(前边的脚本已经生成，直接拿过来执行)
开始导入
报错：source和target的compatible参数不同引起ora-00721错误
查看目标平台信息
导入完成后的结果校验
校验用户情况（密码、默认表空间、角色和权限，需迁移的schema对象大小、个数、列表）
校验用户
用户对象个数
对象详细信息
无效对象情况
索引情况
迁移后续收尾工作

【TTS】AIX平台数据库迁移到Linux--基于RMAN(真实环境)

实验部分

迁移环境介绍

项目	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

实验目标

在实际的工作过程中，需要将AIX上的数据库迁移到Linux，或者将Linux上的数据库迁移到AIX上，除了exp/imp和expdp/impdp外，最常用的就是传输表空间了，若是整个库迁移的话，我们需要做的就是把业务用户和业务表空间的数据迁移过来就行，Undo、temp、system等等的就不用迁移了，整个处理过程和本文档的处理过程大同小异，需要关注的是业务对象的个数、大小、状态等，本文档要实现将AIX上的数据库oraSKY从源平台传递到目标平台Linux上。

源库信息收集

先跑一下健康检查(可选)

根据我自己写的脚本，在源库上跑一下健康检查，主要为了收集一下源库的信息，脚本可以找我私聊，检查后的html文件如下：

例如：

表空间及数据文件情况

表空间大小

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的空间才能完成后续的操作。

需要传输的数据文件大小

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。

用户情况（密码、默认表空间、角色和权限，需迁移的schema对象大小、个数、列表）

需要迁移的用户

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%';

用户权限

drop table t_tmp_user_lhr;

create table t_tmp_user_lhr( id number, username varchar2(50), exec_sql varchar2(4000),create_type varchar2(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;

用户表大小

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 AND D.OWNER=B.owner)

GROUP BY d.owner

order by sum(bytes) desc;

;

对象个数

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 AND D.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;

对象详细信息

---- 以下数据导出到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 AND D.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 AND D.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

无效对象情况

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;

索引情况

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 AND D.OWNER=B.owner)

GROUP BY D.OWNER

ORDER BY D.OWNER ;

确定是否有业务数据、脚本在例如sys用户等的默认用户下

跟开放确认是否有业务数据表在sys等默认用户下，若有是否需要迁移。

判断平台支持并确定字节序

如果传输表空间集到不同的平台，则要确定对于源和目标平台这种跨平台表空间被支持，也要确定每个平台的字节序，如果平台具有相同的字节序，则不需要进行转化，否则必须做一个表空间集转化，在源端或目标端都可以进行转换。

col platform_name for a40

select d.platform_name,tp.endian_format from v$transportable_platform tp,v$database 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，所以需要进行字节序的转换，前边说过在源端或目标端都可以进行转换，这里我们选择在目标端来进行转换。

判断表空间集是否自包含

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

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>

结论：此时这个表空间集已经不再违背自包含的条件，可以确定为一个可传输表空间集。在实际生产环境中也是如此检查的，若是全库迁移，得把需要迁移的表空间修改为自包含的。

产生可传输表空间集

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

connected to target database: ORASKY (DBID=98202371)

RMAN> backup as compressed backupset format '/lxm/oraclebk/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$

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

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_mfdbflvdrg.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_testuse%u_.dbf";

set newname for datafile 4 to

"/lxm/transportdest/o1_mfusers%u_.dbf";

set newname for datafile 5 to