📋Steps in this guide1/4

Transportable Tablespaces

Copy tablespaces to new instances in the time it takes to copy the datafiles.

oracle miscconfigurationintermediate

by OracleDba

13 views

Setup

For this example I'm going to create a new tablespace, user and table to work with in the source database.

Code/Command (click line numbers to comment):

CONN / AS SYSDBA

CREATE TABLESPACE test_data
  DATAFILE '/u01/app/oracle/oradata/DB11G/test_data01.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE USER test_user IDENTIFIED BY test_user
  DEFAULT TABLESPACE test_data
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON test_data;

GRANT CREATE SESSION, CREATE TABLE TO test_user;

CONN test_user/test_user

CREATE TABLE test_tab (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

INSERT /*+ APPEND */ INTO test_tab (id, description)
SELECT level,
       'Description for ' || level
FROM   dual
CONNECT BY level <= 10000;

COMMIT;

Source Database

For a tablespace to be transportable it must be totally self contained. This can be checked using the procedure. The parameter accepts a comma separated list of tablespace names and the parameter indicates if constraints should be included in the check. The view is used to check for any violations. Assuming no violations are produced we are ready to proceed by switching the tablespace to read only mode. Next we export the tablespace metadata using the export (expdp or exp) utility. If you are using 10g or above you should use the expdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server. We can now export the tablespace metadata. If you are using a version prior to 10g, you do not need the directory object and your command would look something like this. Copy the datafile to the appropriate location on the destination database server. Also copy the dump file to a suitable place on the destination database server. You may use binary FTP or SCP to perform this copy. The source tablespace can now be switched back to read/write mode.

Code/Command (click line numbers to comment):

CONN / AS SYSDBA
EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST_DATA', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL>

SELECT * FROM transport_set_violations;

no rows selected

SQL>

SQL> ALTER TABLESPACE test_data READ ONLY;

Tablespace altered.

SQL>

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY temp_dir TO system;

$ expdp userid=system/password directory=temp_dir transport_tablespaces=test_data dumpfile=test_data.dmp logfile=test_data_exp.log

$ exp userid='system/password as sysdba' transport_tablespace=y tablespaces=test_data file=test_data.dmp log=test_data_exp.log

ALTER TABLESPACE test_data READ WRITE;

Tablespace altered.

SQL>

Destination Database

Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist. Now we import the metadata into the destination database. If you are using 10g or above you should use the impdp utility. This requires a directory object pointing to a physical directory with the necessary permissions on the database server. We can now import the tablespace metadata. If you are using a version prior to 10g, you do not need the directory object and your command would look something like this. Switch the new tablespace into read write mode. The tablespace is now available in the destination database.

Code/Command (click line numbers to comment):

CONN / AS SYSDBA

CREATE USER test_user IDENTIFIED BY test_user;
GRANT CREATE SESSION, CREATE TABLE TO test_user;

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
GRANT READ, WRITE ON DIRECTORY temp_dir TO system;

$ impdp userid=system/password directory=temp_dir dumpfile=test_data.dmp logfile=test_data_imp.log transport_datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf'

$ imp userid='system/password as sysdba' transport_tablespace=y datafiles='/u01/app/oracle/oradata/DB11GB/test_data01.dbf' tablespaces=test_data file=test_data.dmp log=test_data_imp.log

SQL> ALTER TABLESPACE test_data READ WRITE;

Tablespace altered.

SQL>

SELECT tablespace_name, plugged_in, status
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST_DATA';

TABLESPACE_NAME                PLU STATUS
------------------------------ --- ---------
TEST_DATA                      YES ONLINE

1 row selected.

SQL>

Cross-Platform Tablespace Conversions

If you are transporting tablespaces between platforms you need to perform Cross-Platform Tablespace Conversions . For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!