By doing so, the consistent backup of the tablespace concerned could be transfered to the desired location and straightaway plugged in by importing the meta data instead of importing the whole data.
This feature is very useful for datawarehousing/applications which needs data to be distributed to various places. Using this feature we can reduce the time required to export import the data.
RESTRICTIONS:
1. Operating system and processor type must be the same at source and target database
2. Database block size must be the same for the source and target database
3. Character set as well as national character set of the source and the target must be the same
4. Users whose default tablespace is getting exported must exist in the target database before importing
5. Target database must not have tablespace of the same name
6. Oracle versions in source and target must be 8.1.x and above
7. Snapshot/replication, function-based indexes,Scoped REFs, domain indexes, 8.0-compatible advanced queues with multiple recipients are not supported
8. The tablespace must be self contained to ensure that the table is complete in case of partitioning.
Setup for Implementation:
The required scripts for transportable tablespaces are run by catproc.sql itself. Check that DBMS_PLUGTS and DBMS_TTS are valid.
If not, run the following scripts $ORACLE_HOME/rdbms/admin/catplug.sql $ORACLE_HOME/rdbms/admin/dbmsplts.sql $ORACLE_HOME/rdbms/admin/prvtplts.plb as 'SYS' user.
If you are importing the tablespace as a user other than 'SYS', create a public synonym for SYS.DBMS_PLUGTS as DBMS_PLUGTS else you would get the errors similar to the following .
EXPORT THE TABLESPACE In this scenario Transporting TSTAB_01, TSIND_01 tablespace.
Step 1:
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TSTAB_01, TSIND _01 ',TRUE,TRUE);
The above statement can be used to determine whether TSTAB_01, TSIND_01 self-contained,with referential integrity constraints taken into consideration (indicated by TRUE) and following statement will show that is there are any violations: a foreign key constraint, dept_fk, across the tablespace set , and a partitioned table.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; If there is any violation which must resolved.
Step 2:
Generate a Transportable Tablespace Set Any privileged user can perform this step. However, you must have been assigned the EXP_FULL_DATABASE role to perform a transportable tablespace export operation. After ensuring you have a self-contained set of tablespaces that you want to transport, generate a transportable tablespace set by performing the following actions:
1. Make all tablespaces in the set you are copying read-only.
2. SQL> ALTER TABLESPACE TSTAB_01 READ ONLY;
3. Tablespace altered.
4. SQL> ALTER TABLESPACE TSIND_01 READ ONLY;
5. Tablespace altered.
Step3 :
Export parameter file to export the tablespace vi exptrans.par userid=sys@DBNAME1 file=/oradata4/transtab.dmp log=/oradata4/ transtab.log TRANSPORT_TABLESPACE=y tablespaces=TSTAB_01,TSIND_01 rows=y CONSTRAINTS=Y GRANTS=Y TRIGGERS=Y statistics=none
Note: (AFTER COMPLETION OF EXPORT DON’T CHANGE THE STATUS OF THE TABLESPACE BECAUSE AFTER COMPLETION OF THE IMPORT THEN ONLY WE NEED TO CHANGE THE STATUS OF TABLESPACE OTHERWISE WE NEED TO TAKE THE FRESH EXPORT FOR TABLESPACE AGAIN)
Step4:
Transfer the meta data export dump file and the copy of the datafile(s) to the target database. (Could be ftp or copy or via cdrom or any other compatible media)
Step 5:
Import the metadata using TABLESPACES, TRANSPORT_TABLESPACE, DATAFILES clause If more than one datafiles are available then specify all of them comma seperated. userid=sys@DBNAME2 file=/oradata4/transtab.dmp log=/oradata4/transtab.imp TRANSPORT_TABLESPACE=y tablespaces=TSTAB_01,TSIND_01 datafiles=/oradata1/*.dbf(mention all the datafiles related to that particular tablespace) rows=y CONSTRAINTS=Y GRANTS=Y TRIGGERS=Y Ignore=y statistics=none
Step 6:
Make the tablespace read write at the target database
SQL>alter tablespace TSTAB_01 read write; SQL> alter tablespace TSIND_01 read write; USEFUL VIEWS
1. DBA_TABLESPACES and V$DATAFILE These contains a new field PLUGGED_IN which is helpful in identifying the plugged in tabelspaces. The value of PLUGGED_IN would be YES in dba_tablespaces and 1 in v$datafile if the tablespace/datafile was plugged in.
SQL> select tablespace_name, status, plugged_in from dba_tablespaces;
SQL> select name, status, plugged_in from v$datafile
2. PLUGGABLE_SET_CHECK This view is used by the export utility to check that the tablespace to be exported is complete and self-contained.
No comments:
Post a Comment