Page Comparison

For In the complex , heterogeneous environments it may be necessary to convert Oracle files to the target OS format. To minimize the outage of the source database we will create a duplicate database on the same host as the source database first and then this duplicate database will be used for the creation of the target database.

The following is the example of creating the target database Oracle 11.1 on Linux from the source database Oracle 10.2 on Windows. The source database SID is DBVT, duplicate database SID is DBVTDUPL and the target database SID is ORCL.

...

Start the DBVTDUPL instance and create spfile from the pfile and exit the SQL*Plus.

...

bgColor	ccc

e:\app\oracle\product\10.2.0>set ORACLE_SID=dbvtdupl

e:\app\oracle\product\10.2.0>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Jun 13 08:45:22 2013

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> conn / as sysdba
Connected to an idle instance.

...

SQL> create spfile from pfile;

File created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

e:\app\oracle\product\10.2.0>

Duplicate the database.

...

bgColor	ccc

e:\app\oracle\product\10.2.0>set ORACLE_SID=dbvtdupl

e:\app\oracle\product\10.2.0>rman target=sys/oracle@dbvt auxiliary /

...

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:

No Format

bgColor	CCC

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.

Example

Following is a step-by-step illustrative example of using transportable tablespace for data instantiation before configuring Dbvisit Replicate. Following environment is used in this example.

Server/Database	Name
Source database	reptest1
Source server	dbvldemo101 (Linux)
Standby/Target database	reptest2
Target server	dbvisit410(Linux)
TNS alias for source database	reptest1
TNS alias for target database	reptest2
Oracle	11.2.0.3.0
Tablespace	data

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.

No Format

bgColor	CCC

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. Check whether tablespace is self-contained or not. We can only transport a tablespaces that is self-contained.

No Format

bgColor	CCC

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

2. Set tablespace to read-only mode.

No Format

bgColor	CCC

SQL> alter tablespace data read only;
Tablespace altered.

3. Get the current SCN from source database.

No Format

bgColor	CCC

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
5568749

4. Export the metadata of tablespace data.

No Format

bgColor	CCC

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

...

15:

...

40:

...

41 2013

...


Copyright (c) 1982,

...

2011, Oracle and/or its affiliates. All rights reserved.

...


Connected to

...

...

contents of Memory Script:
{
 Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 13-JUN-13

Recovery Manager complete.

e:\app\oracle\product\10.2.0>

Pick a self-contained set of tablespaces

...

bgColor	ccc

e:\app\oracle\product\10.2.0>set ORACLE_SID=dbvtdupl

e:\app\oracle\product\10.2.0>sqlplus /nolog

...

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.

No Format

bgColor	CCC

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

...

15:

...

55:

...

41 2013

...


Copyright (c) 1982,

...

2011, Oracle and/or its affiliates. All

...

rights

...

SQL> begin
 2 DBMS_TTS.TRANSPORT_SET_CHECK('USERS', TRUE);
 3 end;
 4 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected

SQL>

The TRANSPORT_SET_VIOLATIONS view is empty, so the picked set of tablespaces is self-contained.

...

Make all tablespaces in the set read-only.

...

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.

No Format

bgColor	CCC

SQL> alter tablespace

...

data read

...

write;

Tablespace altered.

SQL>

...

Export the metadata using Data Pump utility.

Panel

bgColor	ccc

e:\app\oracle\product\10.2.0> EXPDP system/oracle DUMPFILE=ttsexp.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = users

...

Convert all the datafiles composing the tablespaces to be transported.

Panel

bgColor	ccc

e:\app\oracle\product\10.2.0>rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 13 10:49:24 2013

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: DBVTDUPL (DBID=3681752573)

RMAN> convert tablespace users
2> to platform 'Linux IA (64-bit)'
3> format 'e:\tmp\%U';

Starting backup at 13-JUN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=E:\APP\ORACLE\PRODUCT\10.2.0\ORADATA\DBVTDUPL\USERS01.DBF
converted datafile=E:\TMP\DATA_D-DBVTDUPL_I-3681752573_TS-USERS_FNO-4_01OC30GF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 13-JUN-13

RMAN>

...

Import the tablespace set

Panel

bgColor	ccc

[oracle@ora11-1 dpdump]$ impdp system/oracle@orcl dumpfile=TTSEXP.DMP directory=DATA_PUMP_DIR
 transport_datafiles=/u01/app/oracle/admin/orcl/dpdump/USERS01.DBF

...

Set the imported tablespaces back to READ/WRITE mode:

Panel

bgColor	ccc

SQL> alter tablespace users read write;

Return back to the page Using the Database Backups and finish the Dbvisit Replicate setup process

9. Now, you can configure Dbvisit Replicate by running setup wizard on the source server.

No Format

bgColor	CCC

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] 
Would you like to encrypt the data across the network (yes or no) [no]
Would you like to compress the data across the network (yes or no) [no]
How long do you want to set the network timeouts.
Recommended range between 60-300 seconds [60]
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), 
compression: no, encryption: no, network timeout: 60, 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'.

No Format

bgColor	CCC

set APPLY.INSTANTIATE_SCN 5568749

11. Run the reptest1-all.sh script followed by starting MINE and APPLY processes.

Note

This will require the MINE process to go back in time to start mining from the SCN starting point. You will need archivelogs available from the SCN starting point for MINE to process all the changes.

When target database creation takes a long time the MINE process has to do a lot of catchup work before it can actually start to replicate the current changes.

Versions Compared

Old Version 2

New Version Current

Key