using dbms_file_transfer transportable tablespace between asm

Posted on by YongJing(敬勇)

这里介绍当原数据库与目标数据库使用ASM存储数据文件时如何传输表空间。这里将介绍如何使用标准工具比如DataPump与dbms_file_transfer软件包来完成表空间的传输。

下面的例子中将表空间test从一个RAC数据库的ASM磁盘组传输到另一个RAC数据禀报ASM磁盘组
1.在原数据库上创建或使用一个已经存在的表空间

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      +DATADG

SQL> create tablespace test;

Tablespace created.


SQL> col name for a15
SQL> col file_name for a50
SQL> select b.name,a.name as file_name from v$datafile a ,v$tablespace b where  a.ts#=b.ts# and b.name='TEST';

NAME            FILE_NAME
--------------- --------------------------------------------------
TEST            +DATADG/test/datafile/test.269.930512093

2.创建用户test与测试表emp

SQL> create user test identified by "test" default tablespace test temporary tablespace  temp;

User created.

SQL> grant dba,connect,resource to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL> create table emp as select * from scott.emp;

Table created.

SQL> select count(*) from test.emp;

  COUNT(*)
----------
        14

3.检查确保表空间是自包含也就是检查表空间的对象不依赖于其它表空间的对象而独立存在

SQL> conn / as sysdba
Connected.
SQL> execute dbms_tts.transport_set_check('test',true);

PL/SQL procedure successfully completed.

4.查询transport_set_violations视图，来查看是否有违反依赖的对象存在

SQL> select * from transport_set_violations;

no rows selected

5.在原数据库服务器上编辑tnsnames.ora文件来创建一个新的服务名来指向目标数据库

[oracle@jyrac3 admin]$ vi tnsnames.ora
JYRAC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.10.153)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jyrac)
    )
  )

6.在原数据库服务器上使用system用户来创建dblink来链接到目标数据库。这是因为使用 dbms_file_transfer来在两个数据库之间移动元数据所需要

SQL> conn system/system
Connected.
SQL> create database link JYRAC connect to system identified by system using 'JYRAC';

Database link created.

SQL> select count(*) from dba_tables@JYRAC;

  COUNT(*)
----------
      2138

7.在原数据库上创建目录对象tts_dump,tts_dump_log,tts_datafile来存储 dumpfile,logfile,datafile

[root@jyrac3 /]# mkdir tts
[root@jyrac3 /]# chown oracle:oinstall tts
[root@jyrac3 /]# chmod 777 tts

SQL> create directory tts_dump as '+datadg/';

Directory created.

SQL> create directory tts_dump_log as '/tts';

Directory created.


SQL> create directory tts_datafile as '+datadg/test/datafile/';

Directory created.

将给要执行导出元数据的用户system授予对上面所创建的三个目录读写权限

SQL> grant read,write on directory tts_dump to system;

Grant succeeded.

SQL> grant read,write on directory tts_dump_log to system;

Grant succeeded.

SQL> grant read,write on directory tts_datafile to system;

Grant succeeded.

8.在目标数据库重复步骤7的操作

[root@jyrac1 /]# mkdir tts
[root@jyrac1 /]# chown oracle:oinstall tts
[root@jyrac1 /]# chmod 777 tts

SQL> create directory tts_dump as '+datadg/';

Directory created.

SQL> create directory tts_dump_log as '/tts';

Directory created.

SQL> create directory tts_datafile as '+datadg/jyrac/datafile/';

Directory created.

SQL> grant read,write on directory tts_dump to system;

Grant succeeded.

SQL> grant read,write on directory tts_dump_log to system;

Grant succeeded.

SQL> grant read,write on directory tts_datafile to system;

Grant succeeded.

9.使用原数据库要被传输的表空间test设置为只读模式

SQL> alter tablespace test read only;

Tablespace altered.

10.检查原数据库被传输表空间test的状态是否为只读模式

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TEST';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TEST                           READ ONLY

11.导出元数据

[oracle@jyrac3 /]$ expdp system/system directory=tts_dump dumpfile=tts.dmp  logfile=tts_dump_log:tts.log transport_tablespaces=test transport_full_check=y

Export: Release 10.2.0.5.0 - Production on Tuesday, 13 December, 2016 20:17:10

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=tts_dump  dumpfile=tts.dmp logfile=tts_dump_log:tts.log transport_tablespaces=test  transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  +DATADG/tts.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 20:21:52

12.使用dbms_file_transfer将导出的元数据dump文件发送到目标数据库服务器

SQL> conn system/system
Connected.

SQL> begin
  2   dbms_file_transfer.put_file(
  3       source_directory_object=>'TTS_DUMP',
  4       source_file_name=>'tts.dmp',
  5       destination_directory_object=>'TTS_DUMP',
  6       destination_file_name=>'tts.dmp',
  7       destination_database=>'JYRAC');
  8  end;
  9  /

PL/SQL procedure successfully completed.

13.查看表空间test的数据文件名

SQL> select file_name from dba_data_files  where tablespace_name='TEST';

FILE_NAME
--------------------------------------------------
+DATADG/test/datafile/test.269.930512093

14.使用dbms_file_transfer来传输表空间test的数据文件

SQL> begin
  2   dbms_file_transfer.put_file(
  3       source_directory_object=>'TTS_DATAFILE',
  4       source_file_name=>'test.269.930512093',
  5       destination_directory_object=>'TTS_DATAFILE',
  6       destination_file_name=>'test01.dbf',
  7       destination_database=>'JYRAC');
  8  end;
  9  /

PL/SQL procedure successfully completed.

ASMCMD [+DATADG/jyrac/datafile] > ls -l
Type      Redund  Striped  Time             Sys  Name
DATAFILE  MIRROR  COARSE   DEC 12 16:00:00  Y    EXAMPLE.260.930413057
DATAFILE  MIRROR  COARSE   DEC 13 20:00:00  Y    FILE_TRANSFER.270.930515465
DATAFILE  MIRROR  COARSE   DEC 13 13:00:00  Y    SYSAUX.258.930413055
DATAFILE  MIRROR  COARSE   DEC 13 11:00:00  Y    SYSTEM.259.930413057
DATAFILE  MIRROR  COARSE   DEC 12 16:00:00  Y    UNDOTBS1.262.930413057
DATAFILE  MIRROR  COARSE   DEC 12 16:00:00  Y    UNDOTBS2.261.930413057
DATAFILE  MIRROR  COARSE   DEC 13 10:00:00  Y    USERS.263.930413057
                                            N    test01.dbf =>  +DATADG/JYRAC/DATAFILE/FILE_TRANSFER.270.930515465

15.在目标数据库服务器上使用datapump导入数据文件元数据

SQL> create user test identified by "test";

User created.

SQL> grant dba,connect,resource to test;

Grant succeeded.

[oracle@jyrac1 dbs]$ impdp system/system directory=tts_dump dumpfile=tts.dmp  logfile=tts_dump_log:tts.log transport_datafiles='+DATADG/jyrac/datafile/test01.dbf'  keep_master=y

Import: Release 11.2.0.4.0 - Production on Tue Dec 13 20:45:11 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit  Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_02":  system/******** directory=tts_dump  dumpfile=tts.dmp logfile=tts_dump_log:tts.log transport_datafiles= +DATADG/jyrac/datafile/test01.dbf keep_master=y
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_02" successfully completed at Tue Dec 13 20:45:17  2016 elapsed 0 00:00:05

16.将原数据库中的表空间test设置为读写模式

SQL> alter tablespace test read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TEST';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TEST                           ONLINE

17.在目标数据库中验证表空间数据文件是否成功附加

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATADG/jyrac/datafile/system.259.930413057
+DATADG/jyrac/datafile/sysaux.258.930413055
+DATADG/jyrac/datafile/undotbs1.262.930413057
+DATADG/jyrac/datafile/users.263.930413057
+DATADG/jyrac/datafile/example.260.930413057
+DATADG/jyrac/datafile/undotbs2.261.930413057
+DATADG/jyrac/datafile/test01.dbf

18.验证表emp中的数据是否存在

SQL> select count(*) from test.emp;

  COUNT(*)
----------
        14

可以看到通过传输表空间后表emp中的数据与原数据库中一致。

发表评论

电子邮件地址不会被公开。

Proudly powered by WordPress | Indrajeet by Sus Hill.