using dbms_file_transfer transportable tablespace between asm

eric0435發表於2016-12-14
ASM

這裡介紹當原資料庫與目標資料庫使用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中的資料與原資料庫中一致。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2130480/，如需轉載，請註明出處，否則將追究法律責任。

相關文章