Transportable Tablespaces学习笔记

Transportable Tablespaces

Task 1: Determine if Platforms are Supported and Determine Endianness

SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
     FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
     WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

Task 2: Pick a Self-Contained Set of Tablespaces

     To determine whether a set of tablespaces is self-contained, you can invoke the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS. You must have been granted the EXECUTE_CATALOG_ROLE role (initially signed to SYS) to execute this procedure.

The following statement can be used to determine whether tablespaces sales_1 andsales_2 are self-contained, with referential integrity constraints taken into consideration (indicated byTRUE).

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);

After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view：

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
---------------------------------------------------------------------------
Constraint DEPT_FK between table JIM.EMP in tablespace SALES_1 and table
JIM.DEPT in tablespace OTHER
Partitioned table JIM.SALES is partially contained in the transportable set

Task 3: Generate a Transportable Tablespace Set

1.Start SQL*Plus and connect to the database as an administrator or as a user who has either the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

2 .Make all tablespaces in the set read-only.

• SQL> ALTER TABLESPACE sales_1 READ ONLY;
  
  Tablespace altered.
  
  SQL> ALTER TABLESPACE sales_2 READ ONLY;
  
  Tablespace altered.
  

3. Invoke the Data Pump export utility as user system and specify the tablespaces in the transportable set.

SQL> HOST

$ expdp system dumpfile=expdat.dmp directory=data_pump_dir 
        transport_tablespaces=sales_1,sales_2 logfile=tts_export.log

Password: password

4.Check the log file for errors, and take note of the dump file and datafiles that you must transport to the destination database. EXPDP outputs the names and paths of these files in messages like these:


******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/oracle/admin/salesdb/dpdump/expdat.dmp
******************************************************************************
Datafiles required for transportable tablespace SALES_1:
  /u01/app/oracle/oradata/salesdb/sales_101.dbf
Datafiles required for transportable tablespace SALES_2:
  /u01/app/oracle/oradata/salesdb/sales_201.dbf

5.

When finished, exit back to SQL*Plus:

$ EXIT

Task 4: Transport the Tablespace Set

1. Transport both the datafiles and the export (dump) file of the tablespaces to a place that is accessible to the destination database. To accomplish this, do one of the following:
Transport the dump file to the directory pointed to by the DATA_PUMP_DIR directory object, or to any other directory of your choosing.Run the following query to determine the location of DATA_PUMP_DIR:

SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DATA_PUMP_DIR';

• OWNER      DIRECTORY_NAME   DIRECTORY_PATH
  ---------- ---------------- -----------------------------------
  SYS        DATA_PUMP_DIR    C:\app\orauser\admin\orawin\dpdump\
  

  transport the datafiles to the location of the existing datafiles of the destination database.

  On the UNIX and Linux platforms, this location is typically /u01/app/oracle/oradata/SID/ or +DISKGROUP/SID/datafile/.

  Task 5: (Optional) Restore Tablespaces to Read/Write Mode

  Make the transported tablespaces read/write again at the source database, as follows:

  ALTER TABLESPACE sales_1 READ WRITE;
  ALTER TABLESPACE sales_2 READ WRITE;
  

  Task 6: Import the Tablespace Set

  Import the tablespace metadata using the Data Pump Import utility, impdp:

  impdp system dumpfile=expdat.dmp directory=data_pump_dir
     transport_datafiles=
     c:\app\orauser\oradata\orawin\sales_101.dbf,
     c:\app\orauser\oradata\orawin\sales_201.dbf
     remap_schema=sales1:crm1  remap_schema=sales2:crm2
     logfile=tts_import.log
  
  Password: password
  

  In this example we specify the following:

  • The DUMPFILE parameter specifies the exported file containing the metadata for the tablespaces to be imported.

  • The DIRECTORY parameter specifies the directory object that identifies the location of the dump file.

  • The TRANSPORT_DATAFILES parameter identifies all of the datafiles containing the tablespaces to be imported.