Deploy a Standard RAC using Oracle VM Templates - Part 1

Hey everyone!, this article would cover only preparing a two-node RAC system using Oracle VM templates. To deploy this, we would, as a prerequisite, required to have an Oracle VM server configured and should have capacity to host two additional VMs with atleast 2GB of RAM and 1 CPU per node.

To begin with, download Oracle VM templates from Oracle website to your VM server under location which is configured in OVM for ftp server. Unzip the files, if there is a multi-part archive then concatenate them first using command:

cat /tmp/OVM_OL7U2_X86_64_12102DBRAC_PVHVM-2of2-partA.tar.gz
/tmp/OVM_OL7U2_X86_64_12102DBRAC_PVHVM-2of2-partB.tar.gz > /tmp/OVM_OL7U2_X86_64_12102DBRAC_PVHVM-2of2.tar.gz

After this is done, opening ftp URL would look like below:

Using this, build URLs like below:

http://ovm.example.com/dl/OVM_OL7U2_X86_64_12102DBRAC_PVHVM-1of2.tar.gz
http://ovm.example.com/dl/OVM_OL7U2_X86_64_12102DBRAC_PVHVM-2of2.tar.gz

To import these files in OVM repository, open console, click on "Repositories" and select "Import VM Template" button

A dialog box will open up, provide URLs in the field:

Clicking OK would submit a job to import/extract these tar.gz files into repository. After the job is completed, you will see a new record under VM Templates section. By editing its properties you can give default options like template name, amount of RAM, CPU count etc.

Now open console for Oracle VM Manager. The URL should be something like:
https://<hostname>.<domain>.com:7002/ovm/console/

Navigate to your server pool and click "Create Virtual Machine"

Choose option - "Clone from an Existing VM Template", click Next

Provide a VM Name and Description.
Choose Clone Count: 2 and Name Index: 1, this would create two VMs named: "DB RAC - Node 1" and "DB RAC - Node 2"

Select correct Repository and VM Template name.

Respective jobs will be submitted to create VMs from template.

Edit Node 1

After the jobs are completed, select Node 1 and click on "Edit" button:

In Edit dialog box, go to "Networks" tab and choose respective network subnets for both vNICs

Now go to the "Disks" tab, select drop-down menu from an empty disk slot, choose option "Virtual Disk" and click on plus sign (create a virtual disk) . This will open a dialog box to edit virtual disk like below. Provide information similar to below screenshot, kindly note that size must be atleast 1GB. Do not forget to check "Shareable" option, as the same disks will be required to add on Node 2.

Choosing "Sparse Allocation" would not immediately allocate all of the physical disk space to virtual disk, whereas if you chose "Non-Sparse Allocation" all of the specified disk space is allocated immediately.

Click OK and repeat the steps to create total of five virtual disks named (ASM1, ASM2 till ASM5).

After this is done, you will see five disks listed as below:

Click on "OK" to close the Edit window.

Edit Node 2

Select Node 2 and click on "Edit" button:

In Edit dialog box, go to "Networks" tab and choose respective network subnets for both vNICs

Now go to "Disks" tab,  select drop-down menu from an empty disk slot, choose option "Virtual Disk" and click on search sign (select a virtual machine disk) . This will open a dialog box to select virtual disk like below. Select ASM disks created earlier, repeat the steps to add all five disks to Node 2.

This prepares two nodes ready for deploying RAC. In next part, I would be taking this further to starting up VMs,doing initial configuration and finally, deploying the RAC.

Quick Link to Part 2 

Oracle Streams - Checking Errors and LCRs

Transactions, LCRs (logical change records) are core part of Oracle Streams replication, they are set-up in a queue, shipped to destination, dequeued and applied. Thus there could be several possibilities that a transaction may end up in a conflict (in bidirectional replication), not be applied due to ORA- or other errors. Thus I will today discuss a few things about working with errors and replication:

To find out errors raised by APPLY process below query can be used on database where apply process is running:

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';
SET PAGES 1000 LINES 150
COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A10
COLUMN SOURCE_DB HEADING 'Source|Database' FORMAT A10
COLUMN LOCAL_TRANSACTION_ID HEADING 'Local|Transaction|ID' FORMAT A11
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A50
COLUMN MESSAGE_COUNT HEADING 'Messages in|Error|Transaction' FORMAT 99999999
SELECT APPLY_NAME,
       substr(SOURCE_DATABASE,1,4) SOURCE_DB,
       MESSAGE_NUMBER,
       LOCAL_TRANSACTION_ID,
       ERROR_NUMBER,
       ERROR_MESSAGE,
       MESSAGE_COUNT
  FROM DBA_APPLY_ERROR;

This would give an output like below:

Apply                                Local                                                                       Messages in
Process    Source                    Transaction                                                                       Error
Name       Database   MESSAGE_NUMBER ID          Error Number Error Message                                      Transaction
---------- ---------- -------------- ----------- ------------ -------------------------------------------------- -----------
APPLY_PRD  PROD                  2 10.12.18013          439 ORA-00439: feature not enabled: Basic Compression       352987

Using information from above query we can obtain LCR (logical change record) for transaction, however as a prerequisite we would need below two procedures created under Stream Administrator user:

$ sqlplus streamadm/password

PRINT_ANY procedure

SQL>CREATE OR REPLACE PROCEDURE print_any(data IN ANYDATA) IS
  tn  VARCHAR2(61);
  str VARCHAR2(4000);
  chr VARCHAR2(1000);
  num NUMBER;
  dat DATE;
  rw  RAW(4000);
  res NUMBER;
BEGIN
  IF data IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('NULL value');
    RETURN;
  END IF;
  tn := data.GETTYPENAME();
  IF tn = 'SYS.VARCHAR2' THEN
    res := data.GETVARCHAR2(str);
    DBMS_OUTPUT.PUT_LINE(SUBSTR(str,0,253));
  ELSIF tn = 'SYS.CHAR' then
    res := data.GETCHAR(chr);
    DBMS_OUTPUT.PUT_LINE(SUBSTR(chr,0,253));
  ELSIF tn = 'SYS.VARCHAR' THEN
    res := data.GETVARCHAR(chr);
    DBMS_OUTPUT.PUT_LINE(chr);
  ELSIF tn = 'SYS.NUMBER' THEN
    res := data.GETNUMBER(num);
    DBMS_OUTPUT.PUT_LINE(num);
  ELSIF tn = 'SYS.DATE' THEN
    res := data.GETDATE(dat);
    DBMS_OUTPUT.PUT_LINE(dat);
  ELSIF tn = 'SYS.RAW' THEN
    -- res := data.GETRAW(rw);
    -- DBMS_OUTPUT.PUT_LINE(SUBSTR(DBMS_LOB.SUBSTR(rw),0,253));
    DBMS_OUTPUT.PUT_LINE('BLOB Value');
  ELSIF tn = 'SYS.BLOB' THEN
    DBMS_OUTPUT.PUT_LINE('BLOB Found');
  ELSE
    DBMS_OUTPUT.PUT_LINE('typename is ' || tn);
  END IF;
END print_any;
/


PRINT_LCR procedure

SQL> CREATE OR REPLACE PROCEDURE print_lcr(lcr IN ANYDATA) IS
  typenm    VARCHAR2(61);
  ddllcr    SYS.LCR$_DDL_RECORD;
  proclcr   SYS.LCR$_PROCEDURE_RECORD;
  rowlcr    SYS.LCR$_ROW_RECORD;
  res       NUMBER;
  newlist   SYS.LCR$_ROW_LIST;
  oldlist   SYS.LCR$_ROW_LIST;
  ddl_text  CLOB;
  ext_attr  ANYDATA;
BEGIN
  typenm := lcr.GETTYPENAME();
  DBMS_OUTPUT.PUT_LINE('type name: ' || typenm);
  IF (typenm = 'SYS.LCR$_DDL_RECORD') THEN
    res := lcr.GETOBJECT(ddllcr);
    DBMS_OUTPUT.PUT_LINE('source database: ' ||
                         ddllcr.GET_SOURCE_DATABASE_NAME);
    DBMS_OUTPUT.PUT_LINE('owner: ' || ddllcr.GET_OBJECT_OWNER);
    DBMS_OUTPUT.PUT_LINE('object: ' || ddllcr.GET_OBJECT_NAME);
    DBMS_OUTPUT.PUT_LINE('is tag null: ' || ddllcr.IS_NULL_TAG);
    DBMS_LOB.CREATETEMPORARY(ddl_text, TRUE);
    ddllcr.GET_DDL_TEXT(ddl_text);
    DBMS_OUTPUT.PUT_LINE('ddl: ' || ddl_text);   
    -- Print extra attributes in DDL LCR
    ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('serial#');
      IF (ext_attr IS NOT NULL) THEN
        DBMS_OUTPUT.PUT_LINE('serial#: ' || ext_attr.ACCESSNUMBER());
      END IF;
    ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('session#');
      IF (ext_attr IS NOT NULL) THEN
        DBMS_OUTPUT.PUT_LINE('session#: ' || ext_attr.ACCESSNUMBER());
      END IF;
    ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('thread#');
      IF (ext_attr IS NOT NULL) THEN
        DBMS_OUTPUT.PUT_LINE('thread#: ' || ext_attr.ACCESSNUMBER());
      END IF;  
    ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('tx_name');
      IF (ext_attr IS NOT NULL) THEN
        DBMS_OUTPUT.PUT_LINE('transaction name: ' || ext_attr.ACCESSVARCHAR2());
      END IF;
    ext_attr := ddllcr.GET_EXTRA_ATTRIBUTE('username');
      IF (ext_attr IS NOT NULL) THEN
        DBMS_OUTPUT.PUT_LINE('username: ' || ext_attr.ACCESSVARCHAR2());
      END IF;     
    DBMS_LOB.FREETEMPORARY(ddl_text);
  ELSIF (typenm = 'SYS.LCR$_ROW_RECORD') THEN
    res := lcr.GETOBJECT(rowlcr);
    DBMS_OUTPUT.PUT_LINE('source database: ' ||
                         rowlcr.GET_SOURCE_DATABASE_NAME);
    DBMS_OUTPUT.PUT_LINE('owner: ' || rowlcr.GET_OBJECT_OWNER);
    DBMS_OUTPUT.PUT_LINE('object: ' || rowlcr.GET_OBJECT_NAME);
    DBMS_OUTPUT.PUT_LINE('is tag null: ' || rowlcr.IS_NULL_TAG);
    DBMS_OUTPUT.PUT_LINE('command_type: ' || rowlcr.GET_COMMAND_TYPE);
    oldlist := rowlcr.GET_VALUES('old');
    FOR i IN 1..oldlist.COUNT LOOP
      IF oldlist(i) IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('old(' || i || '): ' || oldlist(i).column_name);
        print_any(oldlist(i).data);
      END IF;
    END LOOP;
    newlist := rowlcr.GET_VALUES('new', 'n');
    FOR i in 1..newlist.count LOOP
      IF newlist(i) IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('new(' || i || '): ' || newlist(i).column_name);
        print_any(newlist(i).data);
      END IF;
    END LOOP;
    -- Print extra attributes in row LCR
    ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('row_id');
      IF (ext_attr IS NOT NULL) THEN
        DBMS_OUTPUT.PUT_LINE('row_id: ' || ext_attr.ACCESSUROWID());
      END IF;
    ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('serial#');
      IF (ext_attr IS NOT NULL) THEN
        DBMS_OUTPUT.PUT_LINE('serial#: ' || ext_attr.ACCESSNUMBER());
      END IF;
    ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('session#');
      IF (ext_attr IS NOT NULL) THEN
        DBMS_OUTPUT.PUT_LINE('session#: ' || ext_attr.ACCESSNUMBER());
      END IF;
    ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('thread#');
      IF (ext_attr IS NOT NULL) THEN
        DBMS_OUTPUT.PUT_LINE('thread#: ' || ext_attr.ACCESSNUMBER());
      END IF;  
    ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('tx_name');
      IF (ext_attr IS NOT NULL) THEN
        DBMS_OUTPUT.PUT_LINE('transaction name: ' || ext_attr.ACCESSVARCHAR2());
      END IF;
    ext_attr := rowlcr.GET_EXTRA_ATTRIBUTE('username');
      IF (ext_attr IS NOT NULL) THEN
        DBMS_OUTPUT.PUT_LINE('username: ' || ext_attr.ACCESSVARCHAR2());
      END IF;         
  ELSE
    DBMS_OUTPUT.PUT_LINE('Non-LCR Message with type ' || typenm);
  END IF;
END print_lcr;
/

These procedures are provided by Oracle Corporation. After they have been created run below procedure to obtain complete text of LCR:

SQL> SET SERVEROUTPUT ON;
DECLARE
   lcr SYS.AnyData;
BEGIN
    lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE
                ('<message_number>', '<transaction_id>');
    print_lcr(lcr);
END;
/

source database: PROD.EXAMPLE.COM
owner: SAMPLE
object: CMP4$1820266
is tag null: Y
ddl: create table "SAMPLE".CMP4$1820266 organization heap  tablespace "SAMPLEDATA"  compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0)
*/ * from "SAMPLE".CMP3$1820266 mytab

The error in my case is because of destination database being Standard Edition which does not supports Basic Compression feature.

For the errors which can be resolved OR in case you would like to clear-out the error from database below procedures can be used:

By specifying transaction_id a particular error message can be deleted:
SQL> exec DBMS_APPLY_ADM.DELETE_ERROR ('<local_transaction_id>');

To delete all the errors:
SQL> exec DBMS_APPLY_ADM.DELETE_ALL_ERRORS ();

Will see more on troubleshooting in later posts! Thanks for reading.

References:
Oracle Streams Concepts and Administration [https://docs.oracle.com/cd/B28359_01/server.111/b28321/strms_apmon.htm]

Oracle Streams - Adding a New Schema to Streams Configuration

Today I will be discussing about adding a new schema to existing streams replication configuration. In my current environment I have:

1. A source database: PROD.EXAMPLE.COM
    a. Having CHANGE CAPTURE process: CAPTURE_PROD
    b. An ANYDATA queue: STREAMS_QUEUE
    c. A PROPAGATION process: PROD_TO_RPL

2. A destination database: REPL.EXAMPLE.COM
    a. Having an ANYDATA queue: STREAMS_QUEUE
    b. An APPLY process: APPLY_RPL

To add a new schema, we must first stop replication processes running on source and destination:

Stop Streams Processes

1. Stop CHANGE CAPTURE on source

$ sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> exec DBMS_CAPTURE_ADM.STOP_CAPTURE('CAPTURE_PROD');

2. Stop PROPAGATION on source

$ sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> exec DBMS_PROPAGATION_ADM.STOP_PROPAGATION('PROD_TO_RPL');

3. Stop APPLY on destination

$ sqlplus streamadm/*****@REPL.EXAMPLE.COM
SQL> exec DBMS_APPLY_ADM.STOP_APPLY('APPLY_RPL');

Add a Schema and Setup Replication at Source and Destination Database

1. Create User and Assign Required Privileges/Roles etc

$  sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> CREATE USER SCOTTY IDENTIFIED BY "welcome" DEFAULT TABLESPACE USERS; SQL> GRANT CONNECT, RESOURCE TO SCOTTY;

$ sqlplus streamadm/*****@REPL.EXAMPLE.COM
SQL> CREATE USER SCOTTY IDENTIFIED BY "welcome" DEFAULT TABLESPACE USERS; SQL> GRANT CONNECT, RESOURCE TO SCOTTY;

2. Prepare Schema for Instantiation at Source Database

$  sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> BEGIN 
         DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
         schema_name          => 'SCOTTY',
         supplemental_logging => 'all');
     END;
     /

3. Create PROPAGATION Rule for new schema

$  sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> 
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
   schema_name              => 'SCOTTY',
   streams_name             => 'DBA_TO_RPL',
   source_queue_name        => 'streamadm.streams_queue',
   destination_queue_name   => 'streamadm.streams_queue@REPL.EXAMPLE.COM',
   include_dml              => TRUE,
   include_ddl              => TRUE,
   include_tagged_lcr       => FALSE,
   source_database          => 'PROD.EXAMPLE.COM',
   inclusion_rule           => TRUE,
   and_condition            => NULL,
   queue_to_queue           => TRUE);
END;
/

4. Create CHANGE CAPTURE Rule for new schema

$  sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL>BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
   schema_name          => 'SCOTTY',
   streams_type         => 'capture',
   streams_name         => 'CAPTURE_DBA',
   queue_name           => 'streamadm.streams_queue',
   include_dml          => TRUE,
   include_ddl          => TRUE,
   include_tagged_lcr   => FALSE,
   source_database      => 'PROD.EXAMPLE.COM',
   inclusion_rule       => TRUE,
   and_condition        => NULL);
END;
/

5. Instantiate Schema at Destination Database

$  sqlplus streamadm/*****@REPL.EXAMPLE.COM
SQL>DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@REPL.EXAMPLE.COM(
    source_schema_name    => 'SCOTTY',
    source_database_name  => 'PROD.EXAMPLE.COM',
    instantiation_scn     => iscn,
    recursive             => TRUE);
END;
/

6. Create APPLY Rules for new schema at Destination Database

$  sqlplus streamadm/*****@REPL.EXAMPLE.COM
SQL>BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
   schema_name          => 'SCOTTY',
   streams_type         => 'apply',
   streams_name         => 'APPLY_RPL',
   queue_name           => 'streamadm.streams_queue',
   include_dml          => TRUE,
   include_ddl          => TRUE,
   include_tagged_lcr   => FALSE,
   source_database      => 'PROD.EXAMPLE.COM',
   inclusion_rule       => TRUE,
   and_condition        => NULL);
END;
/

Start Streams Processes

1. Start CHANGE CAPTURE on source

$ sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> exec DBMS_CAPTURE_ADM.START_CAPTURE('CAPTURE_PROD');

2. Start PROPAGATION on source

$ sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> exec DBMS_PROPAGATION_ADM.START_PROPAGATION('PROD_TO_RPL');

3. Start APPLY on destination

$ sqlplus streamadm/*****@REPL.EXAMPLE.COM
SQL> exec DBMS_APPLY_ADM.START_APPLY('APPLY_RPL');


All Done! Now Test Replication..

Oracle Streams - Configuring Synchronous Capture

Synchronous capture (also implicit capture) is an optional Oracle Streams client that captures data manipulation language (DML) changes made to tables. Synchronous capture uses an internal mechanism to capture DML changes to specified tables.

When a DML change it made to a table, it can result in changes to one or more rows in the table. Synchronous capture captures each row change and converts it into a specific message format called a row logical change record (row LCR). After capturing a row LCR, synchronous capture enqueues a message containing the row LCR into a queue. Row LCRs created by synchronous capture always contain values for all the columns in a row, even if some of the columns where not modified by the change.

The need of using Synchronous capture is when:

a) you have fewer number of tables to replicate
b) replicating DML changes is a concern
c) you are using Oracle RDBMS Standard Edition

I will be using Oracle RDBMS 11.2.0.4 for this setup. We currently have:

1. Source database (11.2.0.4 Standard Edition) with schema ORDERS having only one table ORDATA - PROD.EXMPLE.COM
2. ORDERS schema has default tablespace USERS.
3. A newly created destination database on another server, a USERS tablespace has been added - REPL.EXAMPLE.COM
4. Schema Export of ORDERS is imported into REPL database.

Aim: Replicate DML changes in ORDERS.ORDATA table from PROD to REPL.

In my previous posts, I have already discussed about setting up environment and pre-requisite tasks. Please follow steps in Setup Oracle Streams - Datapump Instantiation to setup Stream Administrator user and other prerequisites.

Setup Synchronous Capture Replication

1. Create ANYDATA queue on source and destination databases

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
   queue_table        =>'prod_to_repl_queue_table',
   queue_name          =>'prod_to_repl');
END;
/

ANYDATA queue stores LCRs (logical change records) captured by synchronous capture process at source database and ships them to destination database's ANYDATA queue.

2. Create APPLY process at destination database

BEGIN
  DBMS_APPLY_ADM.CREATE_APPLY(
    queue_name     => 'streamadm.prod_to_repl',
    apply_name     => 'APPLY_REPL',
    apply_captured => FALSE);
END;
/

As we are using change capture by a synchronous capture the apply_captured parameter is set to FALSE because the apply process applies changes in the persistent queue. The apply_captured parameter should be set to TRUE only when the apply process applies changes captured by a capture process.

APPLY process will be started later, thus do not start it at this point.

3. Add APPLY rules at destination database

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'ORDERS.ORDATA',
    streams_type    => 'apply',
    streams_name    => 'APPLY_REPL',
    queue_name      => 'streamadm.prod_to_repl',
    source_database => 'PROD.EXAMPLE.COM');
END;
/

Apply rules indicates apply process to apply all DML changes that appear in apply queue to specified table name. Repeat this step for as many tables as replication is desired for.


4. Create PROPAGATION on source

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name             => 'ORDERS.ORDATA',
    streams_name           => 'CAPTURE_PROD',
    source_queue_name      => 'streamadm.prod_to_repl',
    destination_queue_name => 'streamadm.prod_to_repl@REPL.EXAMPLE.COM',
    source_database        => 'PROD.EXAMPLE.COM',
    queue_to_queue          => TRUE);
END;
/

The ADD_TABLE_PROPAGATION_RULES procedure creates the propagation and its positive rule set. This procedure also adds a rule to the propagation rule set that instructs it to send DML changes from ORDERS.ORDATA to apply_queue. Repeat this step for as many tables as replication is desired for.

5. Create SYNCHRONOUS CAPTURE process at source database

BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name      => 'ORDERS.ORDATA',
    streams_type    => 'sync_capture',
    streams_name    => 'CAPTURE_PROD',
    queue_name      => 'streamadm.prod_to_repl');
END; 
/

Executing this procedure:

• Creates a synchronous capture named sync_capture at the current database. A synchronous capture with the same name must not exist.

• Enables the synchronous capture. A synchronous capture cannot be disabled.

• Associates the synchronous capture with an existing queue named prod_to_repl owned by streamadm.

• Creates a positive rule set for synchronous capture sync_capture. The rule set has a system-generated name.

• Creates a rule that captures DML changes to the ORDERS.ORDATA table and adds the rule to the positive rule set for the synchronous capture. The rule has a system-generated name.

• Prepares the ORDERS.ORDATA table for instantiation by running the DBMS_CAPTURE_ADM.PREPARE_SYNC_INSTANTIATION function for the table automatically.

Repeat this step for as many tables as replication is desired for.

6. INSTANTIATE objects at destination

DECLARE
  iscn  NUMBER;
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@REPL.EXAMPLE.COM(
    source_object_name      => 'ORDERS.ORDATA',
    source_database_name    => 'PROD.EXAMPLE.COM',
    instantiation_scn       => iscn);
END;
/

An instantiation SCN is the lowest SCN for which an apply process can apply changes to a table. Before the apply process can apply changes to the tables at the destination database, an instantiation SCN must be set for each table.

7. Start APPLY process
Run below procedure to start APPLY process at destination database.

BEGIN
  DBMS_APPLY_ADM.START_APPLY(
    apply_name => 'APPLY_REPL');
END;
/

Test out the replication!

References:
Oracle's Data Replication and Integration Guide [https://docs.oracle.com/cd/E11882_01/server.112/e17516/toc.htm]

Setup Oracle Streams - Datapump Instantiation (Part 2) - Step by Step

Hi everyone, this post is in continuance with Oracle Streams - Datapump Instantiation (Part 1), please refer to that beforehand to get a clear picture on prerequisites. This post will discuss about setting up of Streams replication between to servers. To begin with, we have below things ready:

• A PROD.EXAMPLE.COM (Source) database and REPL.EXAMPLE.COM (Destination) database
• Oracle NET configured between these two and both are in ARCHIVELOG mode
• STREAMADM user is setup with default tablespace STREAM_TBS and Stream Administrator privileges on both source and destination databases respectively
• A database link has been created under STREAMADM user on source/destination database to access STREAMADM user on destination/source database

With above done, we are ready to create Streams related configurations in database.

Create ANYDATA queue 

An ANYDATA queue stores messages whose payloads are of ANYDATA type. Therefore, an ANYDATA queue can store a message with a payload of nearly any type, if the payload is wrapped in an ANYDATA wrapper.

Create ANYDATA queue on source and destination databases by executing below command:

EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();

Create CAPTURE Process/Rules

A Capture process will capture changes happening in database/schema/tables depending upon rules setup for it. The procedure used for setting up rules automatically creates a CAPTURE process if it does not exists.

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
   schema_name          => '<SCHEMA_NAME>',
   streams_type         => 'capture',
   streams_name         => 'CAPTURE_PROD',
   queue_name           => 'streamadm.streams_queue',
   include_dml          => TRUE,
   include_ddl          => TRUE,
   include_tagged_lcr   => FALSE,
   source_database      => 'PROD.EXAMPLE.COM',
   inclusion_rule       => TRUE,
   and_condition        => NULL);
END;
/

• streams_name parameter specifies name of the capture process, this if does not exists will be created in first execution. I named it CAPTURE_PROD
• include_dml and include_ddl parameters are set to TRUE since I want to capture both of these changes
• source_database must be provided with global database name of source

Run above procedure N number of times for N number of schemas with correct SCHEMA_NAME supplied.

Prepare Schemas for Instantiation

The procedure below enables supplemental logging for all columns in the tables in the schema being prepared for instantiation and for any table added to this schema in the future. The columns are logged unconditionally. This will ensure that all the changes to tables under schema are captured by capture process.

BEGIN

  DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(

    schema_name          => '<SCHEMA_NAME>',

    supplemental_logging => 'all'); 

END;  

/

Run above procedure N number of times for N number of schemas with correct SCHEMA_NAME supplied.

Take Datapump Export

Datapump export will create a dumpfile for all those selected schemas with are to be replicated from source to destination. As also discussed earlier, the objects to be replicated must exist on both databases, thus datapump will ensure to do this for us.

1. Find out current SCN number of database

SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; 
eg: 6069217041919

2. Take Datapump Export

expdp streamadm/streamadm DUMPFILE=exp_meta_user.dmp LOGFILE=exp_meta_user.log DIRECTORY=STREAM_SRC CONTENT=METADATA_ONLY FULL=y INCLUDE=ROLE,PROFILE


expdp streamadm/streamadm SCHEMAS=SCHEMA_NAMES_LIST DIRECTORY=STREAM_SRC DUMPFILE=exp_schemas.dmp FLASHBACK_SCN=6069217041919

Taking ROLE,PROFILE export is suggested as in my case import at destination failed due to absence of these objects. FLASHBACK_SCN parameter is used since the schema(s) may contain foreign key constraints. After we have obtained the SCN, it must be ensured that no DDL changes occur in database. SCHEMA_NAMES_LIST should be comma separated schema names.

Create Required Tablespaces

Permanent tablespaces associated with schemas that are exported from source must exist at destination database for import to be successful. The required tablespace names and their DDLs can be obtained by using query:

SET PAGES 10000 LINES 150 LONG 99999999;

SELECT DISTINCT 'SELECT DBMS_METADATA.GET_DDL(''TABLESPACE'','''
  || TABLESPACE_NAME || ''') FROM DUAL;'
FROM DBA_SEGMENTS
WHERE OWNER IN ('SCHEMA_NAMES_LIST');

SELECT DISTINCT 'SELECT DBMS_METADATA.GET_DDL(''TABLESPACE'','''
  || TEMPORARY_TABLESPACE || ''') FROM DUAL;'
FROM DBA_USERS
WHERE USERNAME IN ('SCHEMA_NAMES_LIST');

After obtaining DDL statements of these tablespaces re-construct them based on database filesystem of destination database. Copy OR spool the statements in a .sql file and create these tablespaces at destination database.

Import Schemas into Destination Database

Copy exported dumpfile exp_meta_user.dmp and exp_schemas.dmp from source server to destination server under /tmp/dest_dp directory which is path for directory object STREAM_DEST and initiate import.

impdp streamadm/streamadm DUMPFILE=exp_meta_user.dmp LOGFILE=imp_meta_user.log DIRECTORY=STREAM_DBA

impdp streamadm/streamadm SCHEMAS=SCHEMA_NAMES_LIST DIRECTORY=STREAM_DEST DUMPFILE=exp_schemas.dmp LOGFILE=imp_schemas.log
 

Instantiate Objects at Destination

Instantiating objects at destination database will set the SCN for schemas from where the changes will be applied at destination. Below procedure would be used for this operation:

DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@REPL.EXAMPLE.COM(
    source_schema_name    => 'SCHEMA_NAME',
    source_database_name  => 'PROD.EXAMPLE.COM',
    instantiation_scn     => iscn,
    recursive             => TRUE);
END;
/

Run above procedure N number of times for N number of schemas with correct SCHEMA_NAME supplied.

Create APPLY Process/Rules

Apply process is created at destination database, this process will apply changes sent by capture process from source database. The procedure used to setup APPLY rules automatically creates an Apply process if it does not exists.

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
   schema_name          => 'SCHEMA_NAME',
   streams_type         => 'apply',
   streams_name         => 'APPLY_RPL',
   queue_name           => 'streamadm.streams_queue',
   include_dml          => TRUE,
   include_ddl          => TRUE,
   include_tagged_lcr   => FALSE,
   source_database      => 'PROD.EXAMPLE.COM',
   inclusion_rule       => TRUE,
   and_condition        => NULL);
END;
/

• streams_name parameter specifies name of the apply process, this if does not exists will be created in first execution. I named it APPLY_RPL
• include_dml and include_ddl parameters are set to TRUE since I want to apply both of these changes
• source_database must be provided with global database name of source

Run above procedure N number of times for N number of schemas with correct SCHEMA_NAME supplied.

Create Propagation at Source

We have now Capture process ready to capture change, an Apply process waiting to apply changes and an anydata queue to hold changes. The propagation will setup mechanism to ship changes captured into queue at source database to queue at destination database.

A Propagation can created automatically when propagation rules are added for the first time using below procedure:

BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
   schema_name              => 'SCHEMA_NAME',
   streams_name             => 'PROD_TO_RPL',
   source_queue_name        => 'streamadm.streams_queue',
   destination_queue_name   => 'streamadm.streams_queue@RPL.EXAMPLE.COM',
   include_dml              => TRUE,
   include_ddl              => TRUE,
   include_tagged_lcr       => FALSE,
   source_database          => 'PROD.EXAMPLE.COM',
   inclusion_rule           => TRUE,
   and_condition            => NULL,
   queue_to_queue           => TRUE);
END;
/

Run above procedure N number of times for N number of schemas with correct SCHEMA_NAME supplied.

Start Apply Process

At destination database run below procedures to start APPLY process:

BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
    apply_name  => 'APPLY_RPL',
    parameter   => 'disable_on_error',
    value       => 'N');
END;
/

BEGIN
DBMS_APPLY_ADM.START_APPLY(
    apply_name  => 'APPLY_RPL');
END;
/

Start Capture Process

At source database run below procedure to start CAPTURE process:

BEGIN

   DBMS_CAPTURE_ADM.START_CAPTURE(

      capture_name  => 'CAPTURE_DBA');

END;

/
 

All Done! Now test replication by creating a table, inserting a few values and dropping it.

Setup Oracle Streams - Datapump Instantiation (Part 1) - Step by Step

Today,  I will continue from my previous post Instantiation - Oracle Streams Basics which talked about basics of instantiation and its role in replication using Streams. This post will cover details on setting up environment for replication between two databases, one being source containing schemas to be replicated, second one being blank destination database.
 

Prerequisites:

1. Source database: We already have it, an Oracle 11gR2 Enterprise Edition instance. Lets call it PROD.EXAMPLE.COM

2. Destination database: Install an Oracle 11gR2 Standard Edition software at destination server and setup a database with default tablespaces (SYSTEM, SYSAUX, UNDO etc). Lets call it REPL.EXAMPLE.COM

3. Oracle Net: Configure Oracle Net (listener.ora and tnsnames.ora) between two databases. Test connectivity by connecting as SYS or any database user. I configured TNS alias PROD for PROD.EXAMPLE.COM and REPL for REPL.EXAMPLE.COM

4. Update Instance Parameter File: Update instance pfile/spfile with below parameters set to recommended values:

compatible           : Should be same as database version (highest possible)

global_names         : Must be true

open_links           : 4 or higher

processes            : 100 or higher

sessions             : (1.5 x processes) + 22

Setting global_names to true is mandatory as Oracle Streams will use global names of source and destination databases respectively for identification.

5. Archivelog Mode: Both source and destination databases must be in ARCHIVELOG mode

$ sqlplus / as sysdba
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
-- If output shows "ARCHIVELOG" then move on to step 5, otherwise follow below steps
Set log_archive_dest and log_archive_format parameters
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;
-- Archivelog should be now enabled

6. Schemas Excluded: Remember SYS, SYSTEM and CTXSYS and other SYS related schemas are excluded by Streams, so make sure not to create any normal user related objects under these.

7. Unsupported Objects: Streams would not capture changes from specific data types / columns / change types etc. To know of such objects in database use below query:

SQL> SELECT * FROM DBA_STREAMS_UNSUPPORTED;

Note: Get specific details on Oracle Streams Restrictions from Oracle.

Setup Environment

Streams environment consists of a dedicated Streams Administrator user. This user will be configured to run capture process and propagation on source and apply process on destination. The user is specifically provided its own default tablespace to keep any relevant objects, this also makes cleanup job easier if Streams are to be removed from database. The user is also assigned set of privileges along with DBA and Streams Admin privilege

 

Connect to Source Database

Prepare directory for keeping datapump export from source database
$ mkdir /tmp/src_dp
$ sqlplus / as sysdba
SQL> CREATE DIRECTORY STREAM_SRC AS '/tmp/src_dp';

Create Tablespace for STREAMADM user - This user will act as Streams Administrator
SQL> CREATE TABLESPACE STREAM_TBS DATAFILE '/<location>/stream_tbs01.dbf' SIZE 25M AUTOEXTEND ON;

Create STREAMADM user
SQL> CREATE USER STREAMADM IDENTIFIED BY STREAMADM DEFAULT TABLESPACE STREAM_TBS QUOTA UNLIMITED ON STREAM_TBS;
SQL> GRANT CONNECT, RESOURCE, DBA, SELECT_CATALOG_ROLE TO STREAMADM;
SQL> BEGIN
     DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
                  GRANTEE=> 'STREAMADM',
                  GRANT_PRIVILEGES => TRUE);
     END;
     /
SQL> GRANT ALL ON DIRECTORY STREAM_SRC TO STREAMADM;

Create Database Link
SQL> CONNECT STREAMADM/STREAMADM
SQL> CREATE DATABASE LINK REPL.EXAMPLE.COM CONNECT TO STREAMADM IDENTIFIED BY STREAMADM USING 'REPL';

Connect to Destination Database

Prepare directory for keeping datapump export from source database
$ mkdir /tmp/dest_dp
$ sqlplus / as sysdba
SQL> CREATE DIRECTORY STREAM_DEST AS '/tmp/dest_dp';

Create Tablespace for STREAMADM user - This user will act as Streams Administrator
SQL> CREATE TABLESPACE STREAM_TBS DATAFILE '/<location>/stream_tbs01.dbf' SIZE 25M AUTOEXTEND ON;

Create STREAMADM user

SQL> CREATE USER STREAMADM IDENTIFIED BY STREAMADM DEFAULT TABLESPACE STREAM_TBS QUOTA UNLIMITED ON STREAM_TBS;
SQL> GRANT CONNECT, RESOURCE, DBA, SELECT_CATALOG_ROLE TO STREAMADM;
SQL> BEGIN
      DBMS_STREAMS_AUTH.GRANAT_ADMIN_PRIVILEGE(
           GRANTEE=> 'STREAMADM',
           GRANT_PRIVILEGES=> 'TRUE');
      END;
      /
SQL> GRANT ALL ON DIRECTORY STREAM_SRC TO STREAMADM;

Create Database Link

SQL> CONNECT STREAMADM/STREAMADM
SQL> CREATE DATABASE LINK PROD.EXAMPLE.COM CONNECT TO STREAMADM IDENTIFIED BY STREAMADM USING 'PROD';
This completes setting up of environment for Oracle Streams. In Part 2, I will post details on setting-up replication between these two databases.

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]