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

实验部分

迁移环境介绍

实验目标

在实际的工作过程中，需要将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 ;

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 ;

无效对象情况

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

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

验证码：

请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。