Transportable Tablespaces for Scalable Streams Resynchronization

Motivation

From CERN data is distributed using Oracle Streams to a number of sites around the globe (Tier1 sites), enabling a highly complex replication environment where the ongoing maintenance presents a variety of challenges. One of the main problems is the Streams resynchronization after a long intervention at one of the destination sites. After five days, the synchronization window is exceeded: archived log files recovery from backups is costly and the replication rate might be overpassed. The unique solution is to do a complete re-instantiation of the replica site. However, the data transfer (schemas and tables) using Data Pump utility may take days (example: re-instantiation of the ATLAS conditions data) depending on the destination site.

Find a fastest way to move data

Technology proposals

Tranportable Tablespaces

Transportable tablespaces is an Oracle utility which moves a tablespace or several from one database to another.

The quickest way to transport an entire tablespace to a target system is to imply transfer the the tablespace’s underlying files, using FTP or RCP. However, just copying the Oracle datafiles is not sufficient, the target database must recognize and import the files and the corresponding tablespaces before the tablespace data can become available to end users.

Restrictions:

• Database block size must be the same for the source and target database.
• Character set and national character set of the source and the target must be the same.
• The database signals an error if the compatibility level of the transportable tablespace set is higher than the compatibility level of the target database.
• To plug a transportable tablespace set into an Oracle Database on different platform, both databases must have compatibility set to at least 10.0.
• It is always possible to transport a tablespace from a database running an older release of Oracle to a database running a newer release of Oracle. While a transport tablespace to a lower release may work in some cases, it is not an action that Oracle supports.
• Tablespace must be self contained.
• User/s must exist at the target database.

Before Oracle Database 10g, using transportable tablespaces would not have been possible across different platforms. You couldn't have transported tablespaces unless the source and the target were running on the same platform. With Oracle Database 10g, this restriction has been relaxed significantly. Data files can be freely copied across operating systems when tablespaces are being transported. By default, copying across operating systems is possible only if both operating systems have the same byte order. BUT Solaris uses the big-endian byte order, which makes simply copying files between the databases impossible. Oracle provides a byte-order-conversion solution that uses Oracle Recovery Manager (RMAN).

Pulling Tablespaces

A new tool in Oracle Database 10g that simplifies the transportable tablespace approach so that it involves only the execution of a single packaged procedure. In this approach, the user "pulls" the tablespaces from the source system, using a supplied package, DBMS_STREAMS_TABLESPACE_ADM. This package uses Data Pump to transport tablespaces and transfers the data files to the target system's format. It also performs any required endian conversion automatically. All the tasks needed to transport a tablespace from one system to the other are encapsulated inside one program unit, with the details being completely transparent to the user. Even the function of transferring files from the source to the target is handled inside this procedure with the supplied package DBMS_FILE_TRANSFER. Users simply call this procedure with the names of the tablespaces that will be refreshed in the local database.

Pros and Cons

Using Transportable Tablespaces: Enables a full set of tablespaces to be moved. Fastest approach.

Using Pulling Tablespaces: Combines all steps in the transportable tablespaces approach into a single step. However, Less flexibility - multiple files transferred sequentially.

Both require that the tablespaces need to be set to read-only.

Using Transportable Tablespaces for Streams Resynchronization

The idea is to use one of the destination sites in order to synchronize another replica.

Why?

• The tablespaces at the source database cannot be read-only.
• The tablespaces at the source database contain more data than the tablespaces at the replica sites.

How to re-synchronize the Streams setup?

Example: Original streaming to sites 1, 2 , 4 and 5. Separate streaming for site 3. Site 3 is out of sync. Would like to use site 5 to synchronize site 3. Have to move tablespaces “TS1” and “TS2”.

Steps:

• Preparation:
  • Check tablespaces set is self-contained

     
       EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK ('TS1,TS2');
       SELECT * FROM TRANSPORT_SET_VIOLATIONS
       

  • Split Streaming for site 5 (we will use the data from site 5 to populate site 3)
  • Check sites 3 and 5 are able to connect to each other
  • Create database links between databases (from the streams administrator account)
  • Create directories pointing to datafiles and grant access to streams administrator on both sites
• Streams configuration for site 3 (out of the Streams recovery window):
  • A test schema (out of the normal application schemas) is being replicated from the source to all the sites. Used to check Streams replication when there is not activity
  • Add apply rules for site 3 to only apply changes in the test schema (normally apply does not have any rule set defined)
  • Clone queue and capture process (use a new streams build dictionary) at the source database and create a propagation job from the new queue to site 3
  • Check that replication for the test schema works in site 3
• Stop replication to site 5 and to site 3
• Ensure tablespaces are read-only in site 5
• Remove tablespaces and datafiles in site 3
• Transfer the data files of each tablespace to the remote system - datafiles can be transferred in parallel!
• Import tablespaces metadata in the target (site 3)

     impdp user/pwd
     TRANSPORT_DATAFILES="'/oradata/ts1_1.dbf','/oradata/ts2_1.dbf'"
     NETWORK_LINK='srcdb'
     TRANSPORT_TABLESPACES=(TS1,TS2)
     NOLOGFILE=Y
     

• Make tablespaces read-write

• Note: Triggers, views, synonyms and procedures/functions are not exported/imported using transportable tablespaces.

At this point we have a consistent copy of the data between sites 3 and 5. We can proceed with the Streams setup resynchronization:

• Remove rule set from the apply in site 3
• Enable streams processes (capture) on both sites (3 and 5)
• Once they are completely synchronized, merge all the setups into the original one.