sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

学习笔记:Oracle传输表空间 结合案例记录transport tablespace操作过程

时间:2016-06-19 17:07   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 通过案例记录一次使用transport tablespace传输表空间来迁移数据库的操作过程

1.检查数据库平台信息

所有tts支持平台
SELECT * FROM V$TRANSPORTABLE_PLATFORM;

当前系统平台情况
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

2.传输表空间源端数据库操作

检查是否符合TTS要求
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘ODU’, TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL> SELECT COUNT(*) FROM DBA_TABLES WHERE TABLESPACE_NAME=’ODU’;

COUNT(*)
———-
59

SQL> SELECT file_name from dba_data_files where tablespace_name=’ODU’;

FILE_NAME
————————————————–
/opt/oracle/oradata/chf/odu01.dbf
/opt/oracle/oradata/chf/odu02.dbf

需要传输表空间至于readonly模式
SQL> ALTER TABLESPACE ODU READ ONLY;

Tablespace altered.

导出表空间元数据
[oracle@node1 ~]$ exp userid=\’/ as sysdba\’ tablespaces=ODU file=/tmp/ODU.dmp transport_tablespace=y

Export: Release 10.2.0.4.0 – Production on Sun Sep 11 10:01:52 2011

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

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
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata…
For tablespace ODU …
. exporting cluster definitions
. exporting table definitions
. . exporting table T_ODU_03
. . exporting table T_ODU_01
. . exporting table T_ODU
. . exporting table DB
. . exporting table NODE
. . exporting table CONF
. . exporting table DBINC
. . exporting table CKP
. . exporting table TS
. . exporting table TSATT
. . exporting table DF
. . exporting table DFATT
. . exporting table TF
. . exporting table TFATT
. . exporting table OFFR
. . exporting table RR
. . exporting table RT
. . exporting table ORL
. . exporting table RLH
. . exporting table AL
. . exporting table BS
. . exporting table BP
. . exporting table BCF
. . exporting table CCF
. . exporting table XCF
. . exporting table BSF
. . exporting table BDF
. . exporting table CDF
. . exporting table XDF
. . exporting table BRL
. . exporting table BCB
. . exporting table CCB
. . exporting table SCR
. . exporting table SCRL
. . exporting table CONFIG
. . exporting table XAL
. . exporting table RSR
. . exporting table FB
. . exporting table GRSP
. . exporting table ROUT
. . exporting table RCVER
. . exporting table F_DROP
. . exporting table T_QUERY
. . exporting table T_UNDO
. . exporting table A
. . exporting table T1
. . exporting table T2_1
. . exporting table T2
. . exporting table T_MV
. . exporting table TAB2
. . exporting table MLOG$_T_MV
. . exporting table T_N
. . exporting table T_M
. . exporting table MLOG$_T_N
. . exporting table T_1
. . exporting table T_2
. . exporting table T_3
. . exporting table T_4
. . exporting table T_5
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

SQL> alter tablespace odu read write;

Tablespace altered.

传输到目标段
[oracle@node1 ~]$ scp /opt/oracle/oradata/chf/odu0* 192.168.11.12:/opt/oracle/oradata/test
The authenticity of host ’192.168.11.12 (192.168.11.12)’ can’t be established.
RSA key fingerprint is db:3c:b4:34:7f:d7:e4:97:ab:b6:8b:b0:ab:22:43:35.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ’192.168.11.12′ (RSA) to the list of known hosts.
oracle@192.168.11.12′s password:
odu01.dbf 100% 100MB 3.3MB/s 00:30
odu02.dbf 100% 11GB 2.8MB/s 1:05:00
[oracle@node1 ~]$ scp /tmp/ODU.dmp 192.168.11.12:/tmp
oracle@192.168.11.12′s password:
Permission denied, please try again.
oracle@192.168.11.12′s password:
ODU.dmp 100% 456KB 456.0KB/s 00:00

3.传输表空间目标端数据库操作

导入元数据库
[oracle@ECP-UC-DB1 ~]$ imp userid=\’/ as sysdba\’ tablespaces=ODU file=/tmp/ODU.dmp transport_tablespace=y datafiles=/opt/oracle/oradata/test/odu01.dbf, /opt/oracle/oradata/test/odu02.dbf fromuser=chf touser=chf

Import: Release 10.2.0.4.0 – Production on Sun Sep 11 11:13:25 2011

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

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

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata…
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF’s objects into CHF
. . importing table “T_ODU_03″
. . importing table “T_ODU_01″
. . importing table “T_ODU”
. . importing table “DB”
. . importing table “NODE”
. . importing table “CONF”
. . importing table “DBINC”
. . importing table “CKP”
. . importing table “TS”
. . importing table “TSATT”
. . importing table “DF”
. . importing table “DFATT”
. . importing table “TF”
. . importing table “TFATT”
. . importing table “OFFR”
. . importing table “RR”
. . importing table “RT”
. . importing table “ORL”
. . importing table “RLH”
. . importing table “AL”
. . importing table “BS”
. . importing table “BP”
. . importing table “BCF”
. . importing table “CCF”
. . importing table “XCF”
. . importing table “BSF”
. . importing table “BDF”
. . importing table “CDF”
. . importing table “XDF”
. . importing table “BRL”
. . importing table “BCB”
. . importing table “CCB”
. . importing table “SCR”
. . importing table “SCRL”
. . importing table “CONFIG”
. . importing table “XAL”
. . importing table “RSR”
. . importing table “FB”
. . importing table “GRSP”
. . importing table “ROUT”
. . importing table “RCVER”
. . importing table “F_DROP”
. . importing table “T_QUERY”
. . importing table “T_UNDO”
. . importing table “A”
. . importing table “T1″
. . importing table “T2_1″
. . importing table “T2″
. . importing table “T_MV”
. . importing table “TAB2″
. . importing table “MLOG$_T_MV”
. . importing table “T_N”
. . importing table “T_M”
. . importing table “MLOG$_T_N”
. . importing table “T_1″
. . importing table “T_2″
. . importing table “T_3″
. . importing table “T_4″
. . importing table “T_5″
About to enable constraints…
Import terminated successfully without warnings.

SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME STATUS
—————————— ———
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
oracleplus ONLINE
ODU READ ONLY

7 rows selected.

修改为readwrite模式(根据需求)
SQL> alter tablespace odu read write;

Tablespace altered.

SQL> SELECT COUNT(*) FROM DBA_TABLES WHERE TABLESPACE_NAME=’ODU’;

COUNT(*)
———-
59

4.Oracle数据库迁移之传输表空间相关说明

1、如果平台字节顺序不同,需要使用rman convert转换
2、导出导入元数据可以使用data pump实现
3、检查视图、触发器、包、过程、函数等对象,如果没有需要使用exp/imp row=n导入或者人工建立


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle传输表空间 结合案例记录transport tablespace操作过程

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/438.html

Oracle研究中心

关键词:

Oracle传输表空间

Oracle数据库迁移

结合案例记录transport tablespace操作过程