Instantiation - Oracle Streams Basics

Instantiation is perhaps the most challenging yet most simple thing I faced trying to understand basics of Oracle Streams. This came up when I had to do a project involving streams replication and almost everywhere on web I got plenty of tutorials explaining how to do one-way replication, bi-directional replication etc. but none of them talked about setting up an environment.

Now, in my case, client’s environment had a terabyte of database and their requirement was to replicate a bunch of schemas from total of 50+ schemas in their database. So, requirement followed as:

a.  A source database with 14 schemas having several tables scattered over various tablespaces, 
b.  needs to be replicated to a target database, 
c.  source being Oracle 11gR2 Enterprise Edition and destination Oracle 11gR2 Standard Edition

If you have gone through documentation of Streams, you will know there are lot of ways to setup replication between two databases, basically employing a change capture, propagation and apply. These can be setup automatically/manually using DBMS_STREAMS_ADM package or Streams Replication Wizard etc.

After analyzing, I came up with plan to use DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS package to setup environment but before setting-up replication I needed a destination database. Here all confusion started arising and below were the challenges to setup destination database:

• Is RMAN cloning an option? Probably not because I do not want complete database at destination
• Is RMAN transportable tablespaces? I might miss schema objects in tablespaces that will not be transported!
• Is datapump? Yes, this sounds fine. I can obviously choose schemas I need

Thus I decided to go with datapump, it has its own limitations that needed to be addressed but it was the best option among all.

This is the place where instantiation comes in scenario. In a replication environment we cannot simply clone/copy a database and initiate capture/apply processes, it is must that the database objects are instantiated. When any database object is to be replicated it is important to have a reference point-of-time from which apply process will apply the changes at destination, the phenomenon of preparing objects for replication is called instantiation. If a database where changes to the source database objects will be applied is a different database than the source database, then the destination database must have a copy of these database objects.

In Oracle Streams, the following general steps instantiate a database object:

• Prepare the database object for instantiation at the source database.
• If a copy of the database object does not exist at the destination database, then create a database object physically at the destination database based on a database object at the source database. You can use export/import, transportable tablespaces, or RMAN to copy database objects for instantiation. If the database object already exists at the destination database, then this step is not necessary.
• Set the instantiation system change number (SCN) for the database object at the destination database. An instantiation SCN instructs an apply process at the destination database to apply only changes that committed at the source database after the specified SCN.

Will post further on replication in upcoming posts.

References:

      Instantiation and Oracle Streams Replication 

[http://docs.oracle.com/cd/E11882_01/server.112/e10705/instant.htm]