In the complex and heterogeneous environments, when the data volume is high you can consider using transportable tablespace for the data instantiation. The tablespace is required to be switched to read-only mode therefore you can use this option if outage on source database is acceptable otherwise you can create a duplicate database and use that for this task.
Dbvisit Replicate uses the SCN to mark the starting point of where to start replicating from. Only changes made from the SCN specified are replicated. Therefore, you have to get the SCN from where you want to start the replication. If you are using duplicate database then recover that database upto that SCN before using it for transportable tablespace task.
| Note |
|---|
Transportable tablespace feature is available only in can only be used if the source database is Oracle Enterprise Edition. Target database can be of standard or enterprise edition. |
Example:
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| RMAN> duplicate target database to reptest1 until scn 811782 |
|
|
...
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| SQL> conn test/test
Connected. SQL> select * from tab where tabtype='TABLE'; TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
WAREHOUSES TABLE
PRODUCT_INFORMATION TABLE
PRODUCT_DESCRIPTIONS TABLE
ORDER_ITEMS TABLE
ORDERS TABLE
ORDERENTRY_METADATA TABLE
LOGON TABLE
INVENTORIES TABLE
CUSTOMERS TABLE 9 rows selected. SQL> select round(sum(bytes/1024/1024/1024),3) SIZE_GB from user_segments; SIZE_GB
----------
2.85 |
|
|
| Note |
|---|
If the endian formats are different then a conversion is necessary for transporting the tablespace. It can be done using RMAN convert tablespace and convert datafile commands. In this example endian formats are identical on the source and the target. |
1. Get the current SCN from source database.
...
2. Check wether tablespace is self-contained or not. We can only transport a tablespaces that is self-contained.
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| SQL> EXECUTE dbms_tts.transport_set_check('DATA',TRUE);PL/SQL procedure successfully completed. SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; no rows selected. |
|
|
The TRANSPORT_SET_VIOLATIONS view is empty, so the tablespace data is self-contained
3. Set tablespace to read-only mode.
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| SQL> alter tablespace data read only; Tablespace altered. |
|
|
4. Export the metadata of tablespace data.
| Section |
|---|
| Column |
|---|
| | Panel |
|---|
| oracle@dbvldemo101[/home/oracle] expdp system/****** dumpfile=tbs_data_exp.dmp transport_tablespaces=data transport_full_check=y logfile=tes_tbs_export.log Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Export: Release 11.2.0.3.0 - Production on Wed Jul 10 15:40:41 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":
system/******** dumpfile=tbs_data_exp.dmp transport_tablespaces=data transport_full_check=y logfile=tes_tbs_export.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
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:
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log/tbs_data_exp.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 14:07:44 |
|
|
...