oracle 表空间传输

 

 

 

 

 

确定平台的 Endian 格式

源平台

SQL> col PLATFORM_NAME for a30
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 IA (32-bit)              Little

目标平台：

SQL> col platform_name for a40
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 IA (32-bit)              Little 

 

如果不一致可以使用如下命令

$ rman target /
RMAN> convert tablespace trans
2> to platform 'Microsoft Windows IA (32-bit)'
3> format '/tmp/%N_%f';

 

源数据库与目标数据库的字符集要一致

 

 

 

实验：（使用表空间传输，将源数据库tt表空间导入到目标数据库中）

创建实验表空间

[oracle@dongyang ~]$ echo $ORACLE_SID fengzi [oracle@dongyang ~]$   SQL> create tablespace tt datafile '/u01/app/oracle/oradata/tt.dbf' size 10m;   Tablespace created.   SQL> create user tt identified by tt default tablespace tt;   User created.   SQL> grant connect,resource to tt;   Grant succeeded.   SQL> create table tt.e as select * from u1.temp;   Table created.   SQL>

 

 

将tt表空间导出

首先将tt表空间设为只读 SQL> alter tablespace tt read only;   Tablespace altered.   SQL> 导出表空间tt [oracle@dongyang ~]$ exp \'sys/oracle123 as sysdba\' file=/u02/tt.tmp transport_tablespace=y tablespaces=tt   Export: Release 10.2.0.1.0 - Production on Tue Apr 29 09:13:31 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.     Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Export done in ZHS16GBK character set and UTF8 NCHAR character set Note: table data (rows) will not be exported About to export transportable tablespace metadata... For tablespace TT ... . exporting cluster definitions . exporting table definitions . . exporting table                              E . exporting referential integrity constraints . exporting triggers . end transportable tablespace metadata export Export terminated successfully without warnings. [oracle@dongyang ~]$   将表空间设为读写 SQL> alter tablespace tt read write;   Tablespace altered.   SQL>

 

将exp导出的文件tt.tmp与tt表空间数据文件拷贝到目标数据库中（我使用ssh）

 

[oracle@oracle ~]$ echo $ORACLE_SID orcl [oracle@oracle ~]$   [oracle@oracle ~]$ pwd /home/oracle [oracle@oracle ~]$ ls tt.dbf  tt.tmp [oracle@oracle ~]$

 

 

在目标数据库创建tt用户并导入tt表空间

SQL> create user tt identified by tt;   User created.   SQL> grant connect,resource to tt;   Grant succeeded.   SQL> [oracle@oracle ~]$ imp \'sys/oracle as sysdba\' file=/home/oracle/tt.tmp transport_tablespace=y tablespaces=tt datafiles=/home/oracle/tt.dbf   导入成功后可以将tt表空间给予tt用户使用 SQL> alter user tt default tablespace tt;     SQL> select table_name,tablespace_name from dba_tables where  owner='TT';   TABLE_NAME                     TABLESPACE_NAME ------------------------------ ------------------------------ E                              TT   SQL>

 

 

可以使用csscan工具获取 字符集是否可以转换

SQL> select parameter,value from nls_database_parameters where parameter like '%CHARACTERSET%';   PARAMETER ------------------------------ VALUE -------------------------------------------------------------------------------- NLS_CHARACTERSET ZHS16GBK   NLS_NCHAR_CHARACTERSET UTF8     SQL> @?rdbms/admin/csminst.sql   oracle@yang ~]$ csscan full=y tochar=zhs16gbk array=102400 process=4     Character Set Scanner v2.1 : Release 10.2.0.0.0 - Production on Tue May 13 08:29:20 2014   Copyright (c) 1982, 2005, Oracle.  All rights reserved.     Username: sys/oracle as sysdba   Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options   (1)Full database, (2)User, (3)Table, (4)Column: 1 > 1 . process 1 scanning ORDSYS.SI_IMAGE_FORMATS_TAB[AAAKZSAADAAADYJAAA] . process 4 scanning MDSYS.SDO_COORD_SYS[AAALQTAADAAADdRAAA] . process 3 scanning MDSYS.SDO_THEMES_TABLE[AAALUSAADAAAEEJAAA] . process 1 scanning OLAPSYS.CWM$DOMAIN[AAALnxAADAAAEwBAAA] . process 2 scanning SYS.OLAPI_MEMORY_OP_HISTORY[AAALZeAADAAAEvJAAA] . process 4 scanning OLAPSYS.CWM$FACTLEVELUSE[AAALoDAADAAAEyRAAA] . process 2 scanning OLAPSYS.CWM2$AWVIEWS[AAALseAADAAAE9hAAA] . process 1 scanning OLAPSYS.CWM2$LEVELATTRIBUTE[AAALsPAADAAAE7pAAA] . process 3 scanning OLAPSYS.CWM2$MRALL_JOIN_KEY_COL_USES[AAAL1AAADAAAFKZAAA] . process 4 scanning OLAPSYS.CWM2$AWDIMLOAD[AAALsxAADAAAE+pAAA] . process 2 scanning OLAPSYS.CWM2$MRALL_OLAP2_AGG_USES[AAAL1MAADAAAFL5AAA] . process 2 scanning SYSMAN.MGMT_TARGET_TYPES[AAAMLHAADAAAFoJAAA] . process 3 scanning OLAPSYS.MRAC_OLAP2_AW_CUBE_AGG_OP_T[AAAL2eAADAAAFOZAAA] . process 1 scanning SYSMAN.MGMT_E2E_DETAILS_1DAY[AAAMMBAADAAAFu5AAA] . process 4 scanning SYSMAN.MGMT_CREDENTIAL_SET_COLUMNS[AAAMMqAADAAAFzhAAA] . process 3 scanning SYSMAN.MGMT_ARU_LANGUAGES[AAAMN+AADAAAF9RAAA] . process 2 scanning SYSMAN.MGMT_ECM_CSA[AAAMPgAADAAAGJhAAA] . process 3 scanning SYSMAN.MGMT_JOB_NESTED_JOB_TARGETS[AAAMQGAADAAAGOBAAA] . process 2 scanning SYSMAN.MGMT_ROLES[AAAMSeAADAAAGghAAA] . process 4 scanning SYSMAN.MGMT_PURGE_POLICY[AAAMSOAADAAAGexAAA] . process 1 scanning SYSMAN.MGMT_DBNET_TNS_ADMINS[AAAMcAAADAAAG/hAAA] . process 2 scanning SYS.KUPC$DATAPUMP_QUETAB[AAABvWAABAAADzRAAA] . process 3 scanning SYSTEM.REPCAT$_INSTANTIATION_DDL[AAAB6jAABAAAEfxAAA] . process 4 scanning SYS.DBMS_UPG_CHANGE$[AAACG1AABAAAGFBAAA] . process 3 scanning SYS.WRI$_SQLSET_STATISTICS[AAACQJAADAAABI5AAA] . process 1 scanning SYS.WRH$_MTTR_TARGET_ADVICE[AAACOeAADAAABBJAAA] . process 3 scanning WMSYS.WM$NEXTVER_TABLE[AAACYGAADAAABYRAAA] . process 4 scanning WMSYS.WM$EVENT_QUEUE_TABLE[AAACdiAADAAABhpAAA] . process 3 scanning CTXSYS.DR$INDEX[AAAJ+nAADAAABt5AAA] . process 1 scanning SYS.EPG$_AUTH[AAAKV4AABAAAMKxAAA] . process 4 scanning MDSYS.SDO_PREFERRED_OPS_USER[AAALQkAADAAADfRAAA] . process 3 scanning SYS.AW$AWCREATE10G[AAALYIAADAAAERJAAA] . process 1 scanning OLAPSYS.CWM2$DIMENSION[AAALsJAADAAAE65AAA] . process 4 scanning OLAPSYS.CWM2$AWCUBELOADPARM[AAALs4AADAAAE/hAAA] . process 3 scanning OLAPSYS.CWM2$MRALL_CUBE_MEASURES[AAAL1FAADAAAFLBAAA] . process 2 scanning SYS.OLAP_OLEDB_KEYWORDS[AAAL5XAABAAANK5AAA] . process 4 scanning MDSYS.SDO_ELLIPSOIDS_OLD_SNAPSHOT[AAAL7CAADAAAD9RAAA] . process 3 scanning SYSMAN.MGMT_BCN_TARGET_LOCK[AAAML1AADAAAFtZAAA] . process 2 scanning SYSMAN.MGMT_CREDENTIAL_SETS[AAAMMoAADAAAFzRAAA] . process 1 scanning SYSMAN.MGMT_E2E_SQL_1HOUR[AAAMMCAADAAAFvBAAA] . process 4 scanning SYSMAN.MGMT_BUG_ADVISORY_BUG[AAAMOIAADAAAF+hAAA] . process 1 scanning SYSMAN.MGMT_POLICY_RULE_CRITERIA[AAAMPNAADAAAGHJAAA] . process 2 scanning SYSMAN.MGMT_FAILOVER_CALLBACKS[AAAMP1AADAAAGMBAAA] . process 3 scanning SYSMAN.MGMT_NOTIFY_DEVICES[AAAMR8AADAAAGcpAAA] . process 4 scanning SYSMAN.EM_IPW_INFO[AAAMSXAADAAAGf5AAA] . process 1 scanning HR.JOB_HISTORY[AAAMg+AAFAAAABpAAA] . process 2 scanning SH.COUNTRIES[AAAMmFAAFAAAAhBAAA] . process 4 scanning SYSTEM.LOGMNR_TS$[AAABcFAADAAAAfpAAA] . process 3 scanning SYS.WRH$_WAITSTAT[AAAMg8AADAAAAsRAAA] . process 4 scanning SYSTEM.LOGMNR_OBJ$[AAABblAADAAAAgpAAA] . process 1 scanning SYS.WRH$_TABLESPACE_STAT[AAAMh0AADAAAA+hAAA]   Creating Database Scan Summary Report...   Creating Individual Exception Report...   Scanner terminated successfully. [oracle@yang ~]$   [oracle@yang ~]$ ls scan* scan.err  scan.out  scan.txt [oracle@yang ~]$

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29532781/viewspace-1174680/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29532781/viewspace-1174680/