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 can only be used on Oracle Enterprise Edition. |
Example:
| Section | ||
|---|---|---|
| Column | ||
| ||
| No Format | ||
| ||
RMAN> duplicate target database to reptest1 until scn 811782 |
| Info |
|---|
In this example, the duplicate database will be created upto SCN 811782 which you can later specify while configuring Dbvisit Replicate. |
...
| Note |
|---|
The source database is used in this example therefore creation of duplicate database is not included. |
In this example illustrate the steps to replicate test scema which contains the data around 3GB.
| Section | ||
|---|---|---|
| Column | ||
| ||
| No Format | ||
| ||
SQL> conn test/testConnected. 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.
| Section | ||
|---|---|---|
| Column | ||
| ||
| Panel | ||
| No Format | ||
| ||
SQL> select current_scn from v$database;CURRENT_SCN -----------
5568749 |
2. Check wether Check whether tablespace is self-contained or not. We can only transport a tablespaces that is self-contained.
| Section | ||
|---|---|---|
| Column | ||
| ||
| Panel | ||
| No Format | ||
| ||
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 | ||
| ||
| No Format | ||
| ||
SQL> alter tablespace data read only;Tablespace altered. |
4. Export the metadata of tablespace data.
| Section | ||
|---|---|---|
| Column | ||
| ||
| Panel | ||
| No Format | ||
| ||
oracle@dbvldemo101[/home/oracle] expdp system/****** dumpfiledumpfile=tbs_data_exp.dmptransport transport_tablespaces=datatransport transport_full_check=ylogfile 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 |
5.Move both the converted datafiles and metadata export file to the target server.
6. Create the target database if not already exists and create all the test schema to be imported from the source database.
7. Import the tablespace set.
| Section | ||
|---|---|---|
| Column | ||
| ||
| Panel | ||
| No Format | ||
| ||
C:\>impdp system/xxxx dumpfile=tbs_data_exp.dmp TRANSPORT_DATAFILES='C:\app\oracle\oradata\reptest2\data01.dbf' logfile=tes_tbs_imp.log Import: Release 11.2.0.3.0 - Production on Wed Jul 10 15:55: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 Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": dumpfile=tbs_data_exp.dmp TRANSPORT_DATAFILES='C:\app\oracle\oradata\reptest2\data01.dbf' logfile=tes_tbs_imp.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 Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:03:48 |
8. Set the imported tablespaces back to READ WRITE mode.
| Section | ||
|---|---|---|
| Column | ||
| ||
| No Format | ||
| ||
SQL> alter tablespace data read write; |
9. Now, you can configure Dbvisit Replicate by running setup wizard on the source server.
| Section | ||
|---|---|---|
| Column | ||
| ||
| No Format | ||
| ||
Step 2 - Replication pairs======================================== The second step is to set source and targets for each replication pair. This is usually just choosing the first database as source and the second one as target, but many more configurations are possible. Let's configure the replication pair, selecting source and target. Following databases are described: 1: reptest1 (Oracle) 2: reptest2 (Oracle) Select source database: [1] Select target database: [2] Will be DDL replication enabled? (If YES, the script will grant more privileges to the Dbvisit Replicate users and enable database-wide supplemental logging): [yes] Use fetcher to offload the mining to a different server? (yes/no) [no] (NETWORK_QUALITY) - Please specify your network type (LAN or WAN). Autoconfigures timeouts, use of compression etc. [LAN] Lock and copy the data initially one-by-one or at a single SCN? (one-by-one/single-scn/ddl-only/resetlogs) [single-scn] What data copy script to create? (dp_networklink/dp_exp/exp/ddl_file/ddl_run/none) [none]Following replication pairs are now configured: 1: reptest1 (Oracle) ==> reptest2 (Oracle), DDL: yes, fetcher: no, process suffix: (no suffix), network: LAN, prepare type: resetlogs, data load: none Enter number of replication pair to modify it, or "add", or "done": [done] |
10. Edit reptest1-setup.dbvrep script and set correct SCN for APPLY.INSTANTIATE_SCN parameter. The defaulf value is 'NOW'.
| Section | ||
|---|---|---|
| Column | ||
| ||
| Panel | ||
| No Format | ||
| ||
set APPLY.INSTANTIATE_SCN 5568749 |
11. Run the reptest1-all.sh script followed by starting MINE and APPLY processes.
...