oracle表空间传输技术

不同版本数据库,不同平台的迁移!使用表空间传输技术

环境:rhel6.5+oracle10g64bit      windows+oracle11g32bit

准备工作:

要想使用传输表空间，必须满足以下几个条件：

1.源与目标库都必须大于8i；

2.对于低于10G的版本，源与目标库必须为统一平台；

3.自包含：可以通过以下语句予以检测：

 

本例子:使用新建tts表空间,将scott用户的信息修改到tts表空间.创建tts01表空间,将pk_emp,pk_dept索引修改到tt01表空间.

然后使用表空间传输技术,从linux平台迁移到windows平台

linux平台

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

SQL>

windows平台

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

 

SQL>

1.创建tts,tts01表空间

SQL> create tablespace tts datafile '/u01/oracle/oradata/dg/tts.dbf' size 5m autoextend on next 1m maxsize unlimited;

 

Tablespace created.

 

SQL> create tablespace tts01 datafile '/u01/oracle/oradata/dg/tts01.dbf' size 2m autoextend on next 1m;

 

Tablespace created.

 

SQL>

2.修改scott的表空间

SQL> select username,default_tablespace from dba_users where username='SCOTT';

 

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

SCOTT                          USERS

 

SQL> alter user scott default tablespace tts;    ------------修改scott用户的表空间

 

User altered.

 

SQL> select username,default_tablespace from dba_users where username='SCOTT';

 

USERNAME                       DEFAULT_TABLESPACE

------------------------------ ------------------------------

SCOTT                          TTS

 

SQL> select table_name,tablespace_name from dba_tables where owner='SCOTT';

 

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

SALGRADE                       USERS

DEPT                           USERS

EMP                            USERS

BONUS                          USERS

 

SQL> alter table scott.salgrade move tablespace tts;     ----修改表的表空间,可以使用该方法降低高水位,另外一种方法alter table table_name shrink space;

 

Table altered.

 

SQL> alter table scott.dept move tablespace tts;

 

Table altered.

 

SQL> alter table scott.emp move tablespace tts;

 

Table altered.

 

SQL> alter table scott.bonus move tablespace tts;

 

Table altered.

 

SQL> select table_name,tablespace_name from dba_tables where owner='SCOTT';

 

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

DEPT                           TTS

EMP                            TTS

SALGRADE                       TTS

BONUS                          TTS

 

SQL> select index_name,tablespace_name from dba_indexes where owner='SCOTT';

 

INDEX_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

PK_DEPT                        USERS

PK_EMP                         USERS

 

SQL> alter index scott.pk_emp rebuild online tablespace tts01;

 

Index altered.

 

SQL> alter index scott.pk_dept rebuild online tablespace tts01;

 

Index altered.

 

SQL>  select index_name,tablespace_name from dba_indexes where owner='SCOTT';

 

INDEX_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

PK_DEPT                        TTS01

PK_EMP                         TTS01

 

SQL>

开始实验：

1.自包含的检查，传输表空间不要包含sys的对象:

SQL> exec dbms_tts.transport_set_check('TTS',true);

 

PL/SQL procedure successfully completed.

 

SQL> select * from transport_set_violations;

 

VIOLATIONS

--------------------------------------------------------------------------------

Index SCOTT.PK_DEPT in tablespace TTS01 enforces primary constriants  of table SCOTT.DEPT in tablespace TTS

 

Index SCOTT.PK_EMP in tablespace TTS01 enforces primary constriants  of table SCOTT.EMP in tablespace TTS

 

 

SQL>

2.将表空间设置为read only，然后执行导出

SQL> alter tablespace tts read only;   -------根据具体业务，表空间是否能够置为read only

 

Tablespace altered.

 

SQL> alter tablespace tts01 read only;

 

Tablespace altered.

 

SQL>

[oracle@dg1 tmp]$ exp \'/ as sysdba\' tablespaces=tts file='/tmp/tts.dmp' transport_tablespace=y log=tts.log

 

Export: Release 10.2.0.1.0 - Production on Sat Jan 16 20:55:08 2016

 

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 AL32UTF8 character set and AL16UTF16 NCHAR character set

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

EXP-00008: ORACLE error 29341 encountered

ORA-29341: The transportable set is not self-contained    ------没有自包含，考虑将tts，tts01表空一起导出

ORA-06512: at "SYS.DBMS_PLUGTS", line 1387

ORA-06512: at line 1

EXP-00000: Export terminated unsuccessfully

[oracle@dg1 tmp]$

[oracle@dg1 tmp]$ exp \'/ as sysdba\' tablespaces=tts,tts01 file='/tmp/20160116.dmp' transport_tablespace=y log=20160116.log

 

Export: Release 10.2.0.1.0 - Production on Sat Jan 16 21:00:45 2016

 

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 US7ASCII character set and AL16UTF16 NCHAR character set

server uses AL32UTF8 character set (possible charset conversion)

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

For tablespace TTS ...

. exporting cluster definitions

. exporting table definitions

. . exporting table                       SALGRADE

. . exporting table                           DEPT

EXP-00091: Exporting questionable statistics.   

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

----这个错误是因为没有设置NLS_LANG环境变量导致的错误！查看数据库，然后设置环境变量

SQL> select userenv('language') from dual;

 

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.AL32UTF8

 

SQL>

linux设置

[oracle@dg1 tmp]$ echo $NLS_LANG

 

[oracle@dg1 tmp]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

[oracle@dg1 tmp]$ echo $NLS_LANG

AMERICAN_AMERICA.AL32UTF8

[oracle@dg1 tmp]$

windows设置

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

. . exporting table                            EMP

EXP-00091: Exporting questionable statistics.

. . exporting table                          BONUS

For tablespace TTS01 ...

. exporting cluster definitions

. exporting table definitions

. exporting referential integrity constraints

. exporting triggers

. end transportable tablespace metadata export

Export terminated successfully with warnings.

[oracle@dg1 tmp]$

[oracle@dg1 tmp]$ exp \'/ as sysdba\' tablespaces=tts,tts01 file='/tmp/20160116.dmp' transport_tablespace=y log=20160116.log    

 

Export: Release 10.2.0.1.0 - Production on Sat Jan 16 20:59:08 2016

 

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 AL32UTF8 character set and AL16UTF16 NCHAR character set

Note: table data (rows) will not be exported

About to export transportable tablespace metadata...

For tablespace TTS ...

. exporting cluster definitions

. exporting table definitions

. . exporting table                       SALGRADE

. . exporting table                           DEPT

. . exporting table                            EMP

. . exporting table                          BONUS

For tablespace TTS01 ...

. exporting cluster definitions

. exporting table definitions

. exporting referential integrity constraints

. exporting triggers

. end transportable tablespace metadata export

Export terminated successfully without warnings.    ----导出成功

[oracle@dg1 tmp]$

3.使用rman中的convent，转换一下平台

SQL> select * from v$db_transportable_platform;               ------查看数据支持的那些平台的转换

 

PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT

----------- ---------------------------------------- --------------

          7 Microsoft Windows IA (32-bit)            Little

         10 Linux IA (32-bit)                        Little

          5 HP Tru64 UNIX                            Little

         11 Linux IA (64-bit)                        Little

         15 HP Open VMS                              Little

          8 Microsoft Windows IA (64-bit)            Little

         13 Linux 64-bit for AMD                     Little

         12 Microsoft Windows 64-bit for AMD         Little

         17 Solaris Operating System (x86)           Little

 

9 rows selected.

 

SQL>

SQL> ho rman target /

 

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Jan 16 21:15:01 2016

 

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

 

connected to target database: DG (DBID=1814215426)

 

RMAN> convert tablespace tts to platform 'Microsoft Windows IA (32-bit)' db_file_name_convert('/u01/oracle/oradata/dg/tts.dbf','/tmp/tts.dbf');

 

Starting backup at 16-JAN-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=147 devtype=DISK

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00005 name=/u01/oracle/oradata/dg/tts.dbf

converted datafile=/tmp/tts.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Finished backup at 16-JAN-16

 

RMAN> convert tablespace tts01 to platform 'Microsoft Windows IA (32-bit)' db_file_name_convert('/u01/oracle/oradata/dg/tts01.dbf','/tmp/tts01.dbf');

 

Starting backup at 16-JAN-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00006 name=/u01/oracle/oradata/dg/tts01.dbf

converted datafile=/tmp/tts01.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Finished backup at 16-JAN-16

 

RMAN>

4.使用ftp/或者samba服务器等，将exp的dmp文件和rman转换dbf文件传输到windows

作者采用windows共享，linux直接放文件到共享文件夹中，类似samba共享

[root@dg1 tmp]# mount -o username=administrator%1 //30.30.30.1/oracle /mnt    

SQL> grant resource,connect to scott identified by 1;  -------在windows平台建立对应的用户

 

Grant succeeded.

 

SQL>

5.执行传输表空间导入

C:\Users\Administrator>imp '/ as sysdba' file=D:\aaa\tts\20160116.dmp datafiles=D:\aaa\tts\tts.dbf,D:\

aaa\tts\tts01.dbf transport_tablespace=y;

 

Import: Release 11.2.0.1.0 - Production on Sat Jan 16 21:42:57 2016

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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 AL32UTF8 character set and AL16UTF16 NCHAR character set

. importing SYS's objects into SYS

. importing SYS's objects into SYS

. importing SCOTT's objects into SCOTT

. . importing table                     "SALGRADE"

. . importing table                         "DEPT"

. . importing table                          "EMP"

. . importing table                        "BONUS"

. importing SYS's objects into SYS

Import terminated successfully without warnings.

C:\Users\Administrator>sqlplus scott/1

 

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 16 21:44:09 2016

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

BONUS                          TABLE

DEPT                           TABLE

EMP                            TABLE

SALGRADE                       TABLE

 

SQL> select table_name,tablespace_name from dba_tables where owner='SCOTT';    可以查看表

 

TABLE_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

SALGRADE                       TTS

DEPT                           TTS

EMP                            TTS

BONUS                          TTS

 

SQL>

SQL> select index_name,tablespace_name from dba_indexes where owner='SCOTT';  可以查看索引

 

INDEX_NAME                     TABLESPACE_NAME

------------------------------ --------------------

PK_DEPT                        TTS01

PK_EMP                         TTS01

 

SQL>

SQL> select file#,ts#,checkpoint_change#,name from v$datafile_header;

 

     FILE#        TS# CHECKPOINT_CHANGE# NAME

---------- ---------- ------------------ ------------------------------------------------------------

         1          0           12434384 D:\APP\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_BY0T4NRC_.DBF

         2          1           12434384 D:\APP\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_BY0T4NSB_.DBF

         3          2           12434384 D:\APP\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_BY0T4NSB_.DBF

         4          4           12434384 D:\APP\ORADATA\TEST\DATAFILE\O1_MF_USERS_BY0T4NST_.DBF

         5          5           12434384 D:\APP\ORADATA\TEST\DATAFILE\UNDOTBS2.DBF

         6          7           12434384 D:\APP\ORADATA\TEST\DATAFILE\GGDATA.DBF

         7          6             757451 D:\AAA\TTS\TTS.DBF    ------通过该文件的checkpoint_change#,就是scn。发现和数据库中原有的scn都会有一些变化，也会有一些体现

         8          8             757463 D:\AAA\TTS\TTS01.DBF

        11         14           12434384 D:\APP\ORADATA\TEST\DATAFILE\R1.DBF

        12         15           12434384 D:\APP\ORADATA\TEST\DATAFILE\R2.DBF

        13         16           12434384 D:\APP\ORADATA\TEST\DATAFILE\R3.DBF

        14         17           12434384 D:\APP\ORADATA\TEST\DATAFILE\R4.DBF

 

12 rows selected.

 

SQL>

后续工作：检查trigger的可用性，索引的可用性等，考虑oracle性能升级后是否降低，并确定性能降低的原因

 

总结：

 

表空间传输适用于数据量较小的数据库操作，如果数据量比较大，有TB的级别，考虑搭建DG