Oracle跨版本与平台执行传输表空间

166次阅读

共计 12470 个字符，预计需要花费 32 分钟才能阅读完成。

将 aix(10.2.0.4) 平台上的源数据库中的 tspitr 表空间传到 linux(11.2.0.4) 平台，并在源主机上使用目录 /yb_oradata/transport 来存储被转换的数据文件。操作步骤如下:

1. 将要被传输的表空间 tspitr 设置为只读
SQL> alter tablespace tspitr read only;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=’TSPITR’;
TABLESPACE_NAME STATUS
—————————— ———
TSPITR READ ONLY

2. 检查源平台与目标平台信息看是滞支持传输操作，数据库所支持的平台信息如下:
SQL> select platform_name,endian_format from v$transportable_platform;
PLATFORM_NAME ENDIAN_FORMAT
——————————————————————————– ————–
Solaris[tm] OE (32-bit) Big
Solaris[tm] OE (64-bit) Big
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
AIX-Based Systems (64-bit) Big
HP-UX (64-bit) Big
HP Tru64 UNIX Little
HP-UX IA (64-bit) Big
Linux IA (64-bit) Little
HP Open VMS Little
Microsoft Windows IA (64-bit) Little
IBM zSeries Based Linux Big
Linux x86 64-bit Little
Apple Mac OS Big
Microsoft Windows x86 64-bit Little
Solaris Operating System (x86) Little
IBM Power Based Linux Big
Solaris Operating System (x86-64) Little
HP IA Open VMS Little

源平台:
SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name;
PLATFORM_NAME ENDIAN_FORMAT
——————————————————————————– ————–
AIX-Based Systems (64-bit) Big

目标平台:
SQL> select d.platform_name,endian_format from v$transportable_platform tp,v$database d where tp.platform_name=d.platform_name;

PLATFORM_NAME ENDIAN_FORMAT
—————————————————————————————————– ————–
Linux x86 64-bit Little

3. 确认要被传输的表空间是否是自包含表空间 (TSPITR):
SQL> exec sys.dbms_tts.transport_set_check(‘TSPITR’,true);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

没有记录，表示该表空间只包含表数据，可以传输。

4. 记录表空间传输前表 tspitr 中的记录：
SQL> select count(*) from tspitr.tspitr;

COUNT(*)
———-
50315

5. 使用 RMAN 将源数据库中的表空间 tspitrt 转换为目标平台字节序格式，使用 format 参数来控制被转换后数据文件的文件名和存储目录.
[IBMP740-1:Oracle:/yb_oradata/transport]$export NLS_DATE_FORMAT=’yyyy-mm-dd hh24:mi:ss’
[IBMP740-1:oracle:/yb_oradata/transport]$rman target/

Recovery Manager: Release 10.2.0.4.0 – Production on Mon Oct 24 17:07:59 2016

connected to target database: RLZY (DBID=1589671076)

RMAN> convert tablespace “TSPITR” to platform ‘Linux x86 64-bit’ format =’/yb_oradata/transport/%U’;

Starting backup at 2016-10-24 17:09:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1265 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00021 name=/yb_oradata/transport_after/TSPITR01.DBF
converted datafile=/yb_oradata/transport/data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished backup at 2016-10-24 17:09:35

[IBMP740-1:oracle:/yb_oradata/transport]$ls -lrt
total 102416
-rw-r—– 1 oracle dba 52436992 Oct 24 17:09 data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic

6. 使用导出工具创建传输表空间元数据 dump 文件
SQL> create or replace directory test_dump as ‘/yb_oradata/transport’;

Directory created.

SQL> grant read,write on directory test_dump to public;

Grant succeeded.

-rw-r—– 1 oracle dba 52436992 Oct 24 17:09 data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic
[IBMP740-1:oracle:/yb_oradata/transport]$ expdp \’sys/admin_7817600@RLZY as sysdba\’ directory=test_dump dumpfile=tspitr.dmp logfile=tspitr.log

transport_tablespaces=TSPITR < Export: Release 10.2.0.4.0 – 64bit Production on Monday, 24 October, 2016 17:12:42

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″: ‘sys/********@RLZY AS SYSDBA’ directory=test_dump dumpfile=tspitr.dmp logfile=tspitr.log transport_tablespaces=TSPITR
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/yb_oradata/transport/tspitr.dmp
Job “SYS”.”SYS_EXPORT_TRANSPORTABLE_01″ successfully completed at 17:13:41

导出除表之外的��户 tspitr 中的其它对象的元数据
[IBMP740-1:oracle:/yb_oradata/transport]$expdp tspitr/tspitr directory=test_dump dumpfile=tspitr_metadata_only.dmp logfile=tspitr_metadata_only.log content=metadata_only exclude=table

Export: Release 10.2.0.4.0 – 64bit Production on Monday, 24 October, 2016 17:45:22

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “TSPITR”.”SYS_EXPORT_SCHEMA_01″: tspitr/******** directory=test_dump dumpfile=tspitr_metadata_only.dmp logfile=tspitr_metadata_only.log content=metadata_only

exclude=table
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Master table “TSPITR”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for TSPITR.SYS_EXPORT_SCHEMA_01 is:
/yb_oradata/transport/tspitr_metadata_only.dmp
Job “TSPITR”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 17:45:25

7. 将转换后存储在 /u02/transport 目录中的数据文件与导出的元数据文件, 传输到目标主机的目录 /home/transport 中
SQL> create or replace directory test_dump as ‘/home/transport’;

Directory created.

SQL> grant read,write on directory test_dump to public;

Grant succeeded.

[oracle@sjjh transport]$ ftp 10.138.129.3
Connected to 10.138.129.3.
220 IBMP740-1 FTP server (Version 4.2 Mon Nov 28 14:12:02 CST 2011) ready.
502 authentication type cannot be set to GSSAPI
Name (10.138.129.3:oracle): oracle
331 Password required for oracle.
Password:
230-Last unsuccessful login: Mon Dec 1 16:07:13 BEIST 2014 on ftp from ::ffff:10.138.135.235
230-Last login: Mon Oct 24 14:51:18 BEIST 2016 on /dev/pts/0 from 10.138.133.203
230 User oracle logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> cd /yb_oradata/transport
250 CWD command successful.
ftp> lcd /home/transport
Local directory now /home/transport
ftp> bin
200 Type set to I.
ftp> get data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic
local: data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic remote: data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic
227 Entering Passive Mode (10,138,129,3,215,220)
150 Opening data connection for data_D-RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic (52436992 bytes).
226 Transfer complete.
52436992 bytes received in 0.5 seconds (1e+05 Kbytes/s)
ftp> get tspitr.dmp
local: tspitr.dmp remote: tspitr.dmp
227 Entering Passive Mode (10,138,129,3,215,222)
150 Opening data connection for tspitr.dmp (86016 bytes).
226 Transfer complete.
86016 bytes received in 0.0028 seconds (3e+04 Kbytes/s)

ftp> get tspitr_metadata_only.dmp
local: tspitr_metadata_only.dmp remote: tspitr_metadata_only.dmp
227 Entering Passive Mode (10,138,129,3,217,16)
150 Opening data connection for tspitr_metadata_only.dmp (147456 bytes).
226 Transfer complete.
147456 bytes received in 0.0036 seconds (4e+04 Kbytes/s)

8. 将要被传输的表空间附加到目标数据库中
SQL> create user tspitr identified by “tspitr”;

User created.

SQL> grant dba,connect,resource to tspitr;

Grant succeeded.

SQL> select username,default_tablespace,temporary_tablespace from dba_users where username=’TSPITR’;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
—————————— —————————— ——————————
TSPITR USERS TEMP

[oracle@sjjh transport]$ impdp \’sys/xxzx7817600@SJJH as sysdba\’ directory=test_dump dumpfile=tspitr.dmp transport_datafiles=/home/transport/data_D-RLZY_I-

1589671076_TS-TSPITR_FNO-21_orrj67ic

Import: Release 11.2.0.4.0 – Production on Mon Oct 24 17:22:29 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYS”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_TRANSPORTABLE_01″: “sys/********@SJJH AS SYSDBA” directory=test_dump dumpfile=tspitr.dmp transport_datafiles=/home/transport/data_D-

RLZY_I-1589671076_TS-TSPITR_FNO-21_orrj67ic
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job “SYS”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully completed at Mon Oct 24 17:22:37 2016 elapsed 0 00:00:04

导入用户 tspitr 下其它对象的元数据:
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=’TSPITR’;

TABLESPACE_NAME STATUS
—————————— ———
TSPITR READ ONLY

SQL> alter tablespace tspitr read write;

Tablespace altered.

[oracle@sjjh transport]$ impdp tspitr/tspitr directory=test_dump dumpfile=tspitr_metadata_only.dmp

Import: Release 11.2.0.4.0 – Production on Mon Oct 24 17:47:38 2016

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “TSPITR”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “TSPITR”.”SYS_IMPORT_FULL_01″: tspitr/******** directory=test_dump dumpfile=tspitr_metadata_only.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:”TSPITR” already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Job “TSPITR”.”SYS_IMPORT_FULL_01″ completed with 1 error(s) at Mon Oct 24 17:47:40 2016 elapsed 0 00:00:01

SQL> select owner,view_name,text from dba_views where owner=’TSPITR’;
OWNER VIEW_NAME TEXT
—————————— —————————— ——————————————————————————–
TSPITR TSPITR_VIEW select “OWNER”,”OBJECT_NAME”,”SUBOBJECT_NAME”,”OBJECT_ID”,”DATA_OBJECT_ID”,”OBJE

查询 tspitr 表中的记录，与源数据库中的记录数一致。
SQL> select count(*) from tspitr.tspitr;
COUNT(*)
———-
50315

现在用户 tspitr 的缺省表空间不是 tspitr
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username=’TSPITR’;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
—————————— —————————— ——————————
TSPITR USERS TEMP

将用户 tspitr 的缺省表空间修改为 tspitr
SQL> alter user tspitr default tablespace tspitr;
User altered

SQL> select username,default_tablespace,temporary_tablespace from dba_users where username=’TSPITR’;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
—————————— —————————— ——————————
TSPITR TSPITR TEMP

SQL> select count(*) from tspitr.tspitr;
COUNT(*)
———-
50315

现在表 tspitr 的存储表空间也变为 tspitr
SQL> select owner,table_name,tablespace_name from dba_tables where table_name=’TSPITR’;
OWNER TABLE_NAME TABLESPACE_NAME
—————————— —————————— ——————————
TSPITR TSPITR TSPITR

9. 将源数据库中的 tspitr 表空间修改为读写模式
SQL> alter tablespace tspitr read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=’TSPITR’;
TABLESPACE_NAME STATUS
—————————— ———
TSPITR ONLINE

更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-10/136563.htm

正文完

星哥玩云-微信公众号