Configure In-Memory Database and Clusterware

The following seven tasks are required to configure an Active Standby pair with Oracle Clusterware and TimesTen In-Memory Database:

1. Register the TimesTen Cluster Information

a. TimesTen cluster information is stored in the Oracle Cluster Registry. As the root user on UNIX or Linux platforms, or as the instance administrator on Windows, register the TimesTen cluster information by executing the ttCWAdmin -ocrConfig command on one of the hosts.

ttCWAdmin -ocrConfig

2. Start the Cluster Agent

a. Start the TimesTen cluster agent by executing the ttCWAdmin -init command on one of the hosts. For example on mobile-qa7:

ttCWAdmin -init

This command starts the TimesTen clusterware agents and TimesTen daemons on all nodes and starts monitoring them.

3. Create a database

In order to set up an Active Standby Pair via ttCWAdmin, you need to define an 'active' TimesTen database on one of the hosts.

This database must include a user with ADMIN privilege and an application user with some objects to be used for replication.

a. Define a system DSN on the active master host as the instance administrator.

In this example we will use a pre-defined system DSN repdb1_1121 on the host mobile-qa4.

Note. An identical system DSN (repdb1_1121) must also be defined on the standby master host (mobile-qa7). This is used during the creation of the standby database.

b. Connect to and/or create the database repdb1_1121 using ttIsql from host mobile-qa4 as the instance administrator. For example:

ttIsql repdb1_1121

If necessary, refer to the section Creating a TimesTen Database for more information.

4. Create a user to administer the active standby pair

a. Create a database user and assign that user privilege as the database and replication administrator.
This admin user is responsible for setting up and managing the Active Standby Pair.

b. Create a database user and give them minimal user privileges
This user is responsible for creating the transactional workload to be replicated.

c. Log in as the user appuser in ttIsql and execute the following statements or run the script create_appuser_obj.sql located in the quickstart/sample_scripts/replication directory to create the 2 tables in the appuser schema.

connect "dsn=repdb1_1121;uid=appuser";

CREATE TABLE CUSTOMERS (
  CUST_NUMBER NUMBER,
  FIRST_NAME VARCHAR2(12) NOT NULL,
  LAST_NAME VARCHAR2(12) NOT NULL,
  ADDRESS VARCHAR2(100) NOT NULL,
  PRIMARY KEY (CUST_NUMBER));

insert into customers values (3700,'Peter','Burchard','882 Osborne Avenue, Boston, MA 02122');
insert into customers values (1121,'Saul','Mendoza','721 Stardust Street, Mountain View, CA 94043');

CREATE TABLE ORDERS (
  ORDER_NUMBER NUMBER NOT NULL,
  CUST_NUMBER NUMBER NOT NULL,
  PROD_NUMBER CHAR(10) NOT NULL,
  ORDER_DATE DATE NOT NULL,
  PRIMARY KEY (ORDER_NUMBER),
  FOREIGN KEY (CUST_NUMBER) REFERENCES CUSTOMERS (CUST_NUMBER));

insert into ORDERS values (6853036,3700,'0028616731',to_date('2008-04-05','yyyy-mm-dd'));
insert into ORDERS values (6853041,3700,'0198612710',to_date('2009-01-12','yyyy-mm-dd'));
insert into ORDERS values (6853169,1121,'0003750299',to_date('2008-08-01','yyyy-mm-dd'));
insert into ORDERS values (6853174,1121,'0789428741',to_date('2008-10-25','yyyy-mm-dd'));
insert into ORDERS values (6853179,1121,'0198612583',to_date('2009-02-02','yyyy-mm-dd'));

5. Create the TimesTen cluster.oracle.ini config file

a. Although there are many possible attributes in a cluster.oracle.ini file, for a basic Active Standby pair configuration,
only the MasterHosts attribute is needed.

The cluster.oracle.ini file is in the <install_dir>/info directory. For example:

/scratch/dhood/TimesTen/dhood_crs/info/cluster.oracle.ini

The cluster.oracle.ini entry name (eg [repdb1_1121]) must correspond to the system DSN for the active and standby databases.

Note When you use the installer or ttmodinstall to add support for Oracle Clusterware, the mandatory MasterHosts attribute will be created in the cluster.oracle.ini file for you. For more complex configurations, you need to add the relevant attributes yourself in the cluster.oracle.ini file on every node in the cluster.

6. Create an Active Standby Pair

a. Drop any existing [SQL] Active Standby pair for the repdb1_1121 database

DROP ACTIVE STANDBY PAIR;

You cannot mix Active Standby Pairs managed by SQL and managed by ttCWAdmin.
An Active Standby Pair for a database should be managed by either SQL or by ttCWAdmin, but not by both.

b. Create an Active Standby pair replication scheme by executing the ttCWAdmin -create command on any host. For example on mobile-qa7:

ttCWAdmin -create -dsn repdb1_1121

Based on the repdb1_1121 entries in the system DSN and the clusterware.oracle.ini, this command
created the replication scheme for the REPDB1_1121 database on the mobile-qa4 and mobile-qa7machines.

7. Start the Active Standby Pair

a. Start the Active Standby pair replication scheme by executing the ttCWAdmin -start command on any host. For example on mobile-qa4:

ttCWAdmin -start -dsn repdb1_1121

This command starts the Active Standby pair and can be executed from any node in the cluster.

Note
ttCWAdmin -start will automatically start the RepAgent on the standby machine and duplicate the active database onto the standby host.
The time taken to duplicate the active database will be proportional to the size of the active database and the performance of the hardware used.
Once the Active/Standby Pair has be started by ttCWAdmin, committed writes to the active database will be written to its local transaction logs, but they will not get replicated to the standby database until the duplicate operation is complete.

The ttCWAdmin -status -dsn your_DSN command enables you to check the progress of the database duplication.

a.	TimesTen cluster information is stored in the Oracle Cluster Registry. As the root user on UNIX or Linux platforms, or as the instance administrator on Windows, register the TimesTen cluster information by executing the ttCWAdmin -ocrConfig command on one of the hosts.

	ttCWAdmin -ocrConfig

a.	Start the TimesTen cluster agent by executing the ttCWAdmin -init command on one of the hosts. For example on mobile-qa7:

	ttCWAdmin -init



	This command starts the TimesTen clusterware agents and TimesTen daemons on all nodes and starts monitoring them.

	In order to set up an Active Standby Pair via ttCWAdmin, you need to define an 'active' TimesTen database on one of the hosts.
	This database must include a user with ADMIN privilege and an application user with some objects to be used for replication.

a.	Define a system DSN on the active master host as the instance administrator.
	In this example we will use a pre-defined system DSN repdb1_1121 on the host mobile-qa4.



	Note. An identical system DSN (repdb1_1121) must also be defined on the standby master host (mobile-qa7). This is used during the creation of the standby database.

b.	Connect to and/or create the database repdb1_1121 using ttIsql from host mobile-qa4 as the instance administrator. For example:

	ttIsql repdb1_1121



	If necessary, refer to the section Creating a TimesTen Database for more information.

a.	Create a database user and assign that user privilege as the database and replication administrator. This admin user is responsible for setting up and managing the Active Standby Pair.



b.	Create a database user and give them minimal user privileges This user is responsible for creating the transactional workload to be replicated.



c.	Log in as the user appuser in ttIsql and execute the following statements or run the script create_appuser_obj.sql located in the quickstart/sample_scripts/replication directory to create the 2 tables in the appuser schema.

	connect "dsn=repdb1_1121;uid=appuser"; CREATE TABLE CUSTOMERS ( CUST_NUMBER NUMBER, FIRST_NAME VARCHAR2(12) NOT NULL, LAST_NAME VARCHAR2(12) NOT NULL, ADDRESS VARCHAR2(100) NOT NULL, PRIMARY KEY (CUST_NUMBER)); insert into customers values (3700,'Peter','Burchard','882 Osborne Avenue, Boston, MA 02122'); insert into customers values (1121,'Saul','Mendoza','721 Stardust Street, Mountain View, CA 94043'); CREATE TABLE ORDERS ( ORDER_NUMBER NUMBER NOT NULL, CUST_NUMBER NUMBER NOT NULL, PROD_NUMBER CHAR(10) NOT NULL, ORDER_DATE DATE NOT NULL, PRIMARY KEY (ORDER_NUMBER), FOREIGN KEY (CUST_NUMBER) REFERENCES CUSTOMERS (CUST_NUMBER)); insert into ORDERS values (6853036,3700,'0028616731',to_date('2008-04-05','yyyy-mm-dd')); insert into ORDERS values (6853041,3700,'0198612710',to_date('2009-01-12','yyyy-mm-dd')); insert into ORDERS values (6853169,1121,'0003750299',to_date('2008-08-01','yyyy-mm-dd')); insert into ORDERS values (6853174,1121,'0789428741',to_date('2008-10-25','yyyy-mm-dd')); insert into ORDERS values (6853179,1121,'0198612583',to_date('2009-02-02','yyyy-mm-dd'));

a.	Although there are many possible attributes in a cluster.oracle.ini file, for a basic Active Standby pair configuration, only the MasterHosts attribute is needed.



	The cluster.oracle.ini file is in the <install_dir>/info directory. For example:
	/scratch/dhood/TimesTen/dhood_crs/info/cluster.oracle.ini
	The cluster.oracle.ini entry name (eg [repdb1_1121]) must correspond to the system DSN for the active and standby databases.

	Note When you use the installer or ttmodinstall to add support for Oracle Clusterware, the mandatory MasterHosts attribute will be created in the cluster.oracle.ini file for you. For more complex configurations, you need to add the relevant attributes yourself in the cluster.oracle.ini file on every node in the cluster.

a.	Drop any existing [SQL] Active Standby pair for the repdb1_1121 database

	DROP ACTIVE STANDBY PAIR;
	You cannot mix Active Standby Pairs managed by SQL and managed by ttCWAdmin. An Active Standby Pair for a database should be managed by either SQL or by ttCWAdmin, but not by both.

b.	Create an Active Standby pair replication scheme by executing the ttCWAdmin -create command on any host. For example on mobile-qa7:

	ttCWAdmin -create -dsn repdb1_1121



	Based on the repdb1_1121 entries in the system DSN and the clusterware.oracle.ini, this command created the replication scheme for the REPDB1_1121 database on the mobile-qa4 and mobile-qa7machines.

a.	Start the Active Standby pair replication scheme by executing the ttCWAdmin -start command on any host. For example on mobile-qa4:

	ttCWAdmin -start -dsn repdb1_1121


	This command starts the Active Standby pair and can be executed from any node in the cluster. Note ttCWAdmin -start will automatically start the RepAgent on the standby machine and duplicate the active database onto the standby host. The time taken to duplicate the active database will be proportional to the size of the active database and the performance of the hardware used. Once the Active/Standby Pair has be started by ttCWAdmin, committed writes to the active database will be written to its local transaction logs, but they will not get replicated to the standby database until the duplicate operation is complete. The ttCWAdmin -status -dsn your_DSN command enables you to check the progress of the database duplication.