Home > Database > Mysql Tutorial > Oracle传输表空间总结

Oracle传输表空间总结

WBOY
Release: 2016-06-07 17:58:06
Original
1265 people have browsed it

0、准备工作 --创建被传输的表空间 SQL create tablespace tbs_single datafile c:\Oracle\oradata\ora9i\tbs_single01.dbf size 100M extent management local; --创建用户,并将表空间作为默认表空间 SQL create user tranp identified by Oracle default t

0、准备工作
--创建被传输的表空间
SQL> create tablespace tbs_single
datafile 'c:\Oracle\oradata\ora9i\tbs_single01.dbf' size 100M
extent management local;
--创建用户,并将表空间作为默认表空间
SQL> create user tranp identified by Oracle default tablespace tbs_single;


SQL> grant connect,resource to tranp;
--在该表空间创建表,用于测试
SQL> create table tranp.t01 as select * from sys.dba_objects;
1、检查源、目标平台Endianness
在源数据库平台上:
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
  2       FROM V$TRANSPORTABLE_PLATFORM. tp, V$DATABASE d
  3       WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME                          ENDIAN_FORMAT
------------------------------------   --------------
Microsoft Windows IA (32-bit)                 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
------------------------------------                    --------------
Linux IA (32-bit)                         Little
由于源和目标平台的Endianness一致,可以省去convert这一步。
2、检查要表空间是否自包含
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBS_SINGLE',true);
PL/SQL 过程已成功完成。
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
未选定行
说明表空间是自包含的。
3、产生传输表空间集
创建目录对象
SQL> create DIRECTORY tranp_dir as 'c:\software';
目录已创建。
SQL> grant read,write on DIRECTORY tranp_dir to public;
授权成功。
将表空间置为只读。
SQL> alter tablespace tbs_single read only;
表空间已更改。
使用数据泵导出传输表空间的元数据


注:如果Endianness不一致,还需要使用RMAN进行转换表空间的数据文件。
4、传送表空间集
将表空间的数据文件和导出的DMP文件,传送到目标数据库平台上。
5、导入表空间
在目标数据库中,创建相应的目录对象和用户。
SQL> create directory tranp_dir as '/home/Oracle';
Directory created.

SQL> grant read,write on directory tranp_dir to public;
Grant succeeded.

SQL> create user tranp identified by Oracle;
User created.

SQL> grant connect,resource to tranp;
Grant succeeded.
使用数据库泵,导入到目标数据库中。
[Oracle@ocmu ~]$ impdp system/oracle dumpfile=TBSINGLE.DMP directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/PROD/TBS_SINGLE01.DBF remap_schema=tranp:tranp

Import: Release 10.2.0.1.0 - Production on Thursday, 30 August, 2012 23:40:25

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=TBSINGLE.DMP directory=tranp_dir transport_datafiles=/u01/app/Oracle/oradata/PROD/TBS_SINGLE01.DBF remap_schema=tranp:tranp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 23:40:29
将被导入的表空间置为read write。
SQL> alter tablespace TBS_SINGLE read write;
Tablespace altered.

6、测试
目标库中,进行测试。
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
TBS_SINGLE
。。。。。。。。。。。。。。。。。
13 rows selected.



SQL> conn tranp/Oracle
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T01                            TABLE

SQL> select count(*) from t01;

  COUNT(*)
----------
     49795

SQL> conn / as sysdba
Connected.

SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TBS_SINGLE                     ONLINE
。。。。。。。。。。。。。。。。。。。。。。。。
13 rows selected.
7、问题
问题描述:
Oracle@ocmu ~]$ impdp system/oracle dumpfile=TBSINGLE.DMP directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/PROD/TBS_SINGLE01.DBF remap_schema=tranp:tranp

Import: Release 10.2.0.1.0 - Production on Thursday, 30 August, 2012 23:25:47

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** dumpfile=TBSINGLE.DMP directory=tranp_dir transport_datafiles=/u01/app/Oracle/oradata/PROD/TBS_SINGLE01.DBF remap_schema=tranp:tranp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29345: cannot plug a tablespace into a database using an incompatible character set

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 23:25:53

a元数据库:
SQL> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
  2   (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
  3   (select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
  4   (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;

TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
----------------------------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
b目标数据库:
SQL> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
  2   (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
  3   (select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
  4   (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;

TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
----------------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
原因:由于源和目标数据库的字符集不一致或不是子集和超集的关系,所以造成不能导入表空间的字符集。
解决方法:将源数据库和目标数据库的字符集调整为一致的字符集;或者源数据库的字符集是目标数据库的子集。
源数据库的修改:
SQL> alter database character set internal_use utf8;
alter database character set internal_use utf8
*
第1行出现错误:
ORA-12719:操作要求数据库处于RESTRICTED模式下

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
Oracle例程已经关闭。

SQL> startup mount;
Oracle例程已启动

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             138412736 bytes
Database Buffers          301989888 bytes
Redo Buffers                2973696 bytes
数据库已装载

SQL>  alter system enable restricted session;
系统已更改

SQL> alter database open;
数据库已更改

SQL> alter database character set internal_use utf8;
数据库已更改

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
Oracle例程已经关闭。
SQL> startup mount;
Oracle例程已启动

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             138412736 bytes
Database Buffers          301989888 bytes
Redo Buffers                2973696 bytes
数据库已装载

SQL>  alter system disable restricted session;
系统已更改

SQL> alter database open; 
数据库已更改

SQL> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
  2   (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
  3   (select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
  4   (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;

TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
----------------------------------------------------------------------
AMERICAN_AMERICA.UTF8
目标数据库修改:
SQL> alter database character set internal_use utf8;
alter database character set internal_use utf8
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode

SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.

SQL> startup mount;
Oracle instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             138412736 bytes
Database Buffers          301989888 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL>  alter system enable restricted session;
System altered.

SQL> alter database open;
Database altered.

SQL> alter database character set internal_use utf8;
Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
Oracle instance shut down.

SQL> startup mount;
Oracle instance started.

Total System Global Area  444596224 bytes
Fixed Size                  1219904 bytes
Variable Size             138412736 bytes
Database Buffers          301989888 bytes
Redo Buffers                2973696 bytes
Database mounted.

SQL>  alter system disable restricted session;
System altered.

SQL> alter database open; 
Database altered.

SQL> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
  2   (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
  3   (select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
  4   (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;

TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
----------------------------------------------------------------------
AMERICAN_AMERICA.UTF8
修改完成,再重新导出/导入一遍,即可成功。
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template