6 Altering an Active Standby Pair

This chapter includes the following sections:

Making DDL changes in an active standby pair
Making other changes to an active standby pair

Making DDL changes in an active standby pair

You can perform the following tasks in an active standby pair without stopping the replication agent:

Create, alter or drop a user. This does not affect the replication scheme.
Grant or revoke privileges from a user. This does not affect the replication scheme.
Create or drop a view, a materialized view, a PL/SQL function, PL/SQL procedure, PL/SQL package or PL/SQL package body. These objects are not replicated.
Add a column to a replicated table or drop a column from a replicated table. The change is replicated to the table in the standby database.
Create or drop a table, including global temporary tables. The CREATE TABLE and DROP TABLE statements can be replicated to the standby database. The new table can also be included in the active standby pair.
Create or drop a synonym. The CREATE SYNONYM and DROP SYNONYM statements can be replicated to the standby database.
Create or drop an index. The CREATE INDEX and DROP INDEX statements can be replicated to the standby database.

Use the DDLReplicationLevel and DDLReplicationAction connection attributes to control what happens when you want to perform these tasks.

DDLReplicationLevel can be set as follows:

DDLReplicationLevel=1 is the default value. No CREATE or DROP statements for tables, indexes or synonyms are replicated to the standby database. You can add or drop columns from a replicated table, and those actions will be replicated to the standby database.
DDLReplicationLevel=2 enables replication of creating and dropping of tables, indexes and synonyms.

You can set the DDLReplicationLevel attribute by using the ALTER SESSION statement:

ALTER SESSION SET ddl_replication_level=2;

If you want to include a table in the active standby pair when the table is created, set the DDLReplicationAction connection attribute to 'INCLUDE'. If you do not want to include a table in the active standby pair when the table is created, set DDLReplicationAction='EXCLUDE'. The default is 'INCLUDE'.

You can set the DDLReplicationAction attribute by using the ALTER SESSION statement:

ALTER SESSION SET ddl_replication_action='EXCLUDE';

To add an existing table to an active standby pair, set DDLReplicationLevel=2 and use the ALTER ACTIVE STANDBY PAIR INCLUDE TABLE statement. The table must be empty.

When DDLCommitBehavior=0 (the default), DDL operations are automatically committed. When RETURN TWOSAFE has been specified, errors and timeouts may occur as described in "RETURN TWOSAFE". If a RETURN TWOSAFE timeout occurs, the DDL transaction is committed locally regardless of the LOCAL COMMIT ACTION that has been specified.

Restrictions on making DDL changes in an active standby pair

CREATE TABLE AS SELECT is not replicated.
The CREATE INDEX statement is replicated only when the index is created on an empty table.

Examples: Making DDL changes in an active standby pair

Example 6-1 Create a table and include it in the active standby pair

On the active database, set DDLReplicationLevel to 2 and DDLReplicationAction to 'INCLUDE'.

Command > ALTER SESSION SET ddl_replication_level=2;
Session altered.
Command > ALTER SESSION SET ddl_replication_action='INCLUDE';
Session altered.

Create a table. The table must have a primary key or index.

Command > CREATE TABLE tabinclude (col1 NUMBER NOT NULL PRIMARY KEY);
Table created.

Insert a row into tabinclude.

Command > INSERT INTO tabinclude VALUES (55);
1 row inserted.

On the standby database, verify that the INSERT statement has been replicated. This indicates that the tabinclude table has been included in the active standby pair.

Command > SELECT * FROM tabinclude;
< 55 >
1 row found.

Alternatively, use the ttIsql repschemes command to see what tables are included in the active standby pair.

Example 6-2 Create a table and add it to the active standby pair later

On the active database, set DDLReplicationLevel to 2 and DDLReplicationAction to 'EXCLUDE'.

Command> ALTER SESSION SET ddl_replication_level=2;
Session altered.
Command> ALTER SESSION SET ddl_replication_action='exclude';
Session altered.

Create a table that does not have a primary key or index. Try to include it in the active standby pair.

Command> CREATE TABLE newtab (a NUMBER NOT NULL);
Command> ALTER ACTIVE STANDBY PAIR INCLUDE TABLE newtab;
 8000: No primary or unique index on non-nullable column found for replicated 
 table TERRY.NEWTAB
The command failed.

Create an index on the table. Include the table in the active standby pair.

Command> CREATE UNIQUE INDEX ixnewtab ON newtab(a);
Command> ALTER ACTIVE STANDBY PAIR INCLUDE TABLE newtab;

Insert a row into the table.

Command> INSERT INTO newtab VALUES (5);
1 row inserted.

On the standby database, verify that the row was inserted.

Command> SELECT * FROM newtab;
< 5 >
1 row found.

This example illustrates that a table does not need a primary key to be part of an active standby pair.

Example 6-3 CREATE INDEX is replicated

On the active database, set DDLReplicationLevel=2 and DDLReplicationAction='INCLUDE'.

Command> ALTER SESSION SET ddl_replication_level=2;
Session altered.
Command> ALTER SESSION SET ddl_replication_action='include';
Session altered.

Create a table with a primary key. The table is automatically included in the active standby pair.

Command> CREATE TABLE tab2 (a NUMBER NOT NULL, b NUMBER NOT NULL, 
       > PRIMARY KEY (a));

Create an index on the table.

Command> CREATE UNIQUE INDEX ixtab2 ON tab2 (b);

On the standby database, verify that the CREATE INDEX statement has been replicated.

Command> indexes;
 
Indexes on table TERRY.TAB2:
  IXTAB2: unique T-tree index on columns:
    B
  TAB2: unique T-tree index on columns:
    A
  2 indexes found.
 
Indexes on table TERRY.NEWTAB:
  NEWTAB: unique T-tree index on columns:
    A
  1 index found.
 
Indexes on table TERRY.TABINCLUDE:
  TABINCLUDE: unique T-tree index on columns:
    A
  1 index found.
4 indexes found on 3 tables.

Example 6-4 CREATE SYNONYM is replicated

On the active database, set DDLReplicationLevel to 2 and DDLReplicationAction to 'INCLUDE'.

Command > ALTER SESSION SET ddl_replication_level=2;
Session altered.
Command > ALTER SESSION SET ddl_replication_action='INCLUDE';
Session altered.

Create a synonym for tabinclude.

Command> CREATE SYNONYM syntabinclude FOR tabinclude;
Synonym created.

On the standby database, use the ttIsql synonyms command to verify that the CREATE SYNONYM statement has been replicated.

Command> synonyms;
TERRY.SYNTABINCLUDE
1 synonym found.

Making other changes to an active standby pair

You must stop the replication agent to make these changes to an active standby pair:

Include or exclude a sequence
Include or exclude a cache group
Add or drop a subscriber
Change values in the STORE clause
Change network operations (ADD ROUTE or DROP ROUTE clause)

To alter an active standby pair according to the preceding list, complete the following tasks:

Stop the replication agent on the active database. See "Starting and stopping the replication agents".
If the active standby pair includes cache groups, stop the cache agent on the active database.
Use the ALTER ACTIVE STANDBY PAIR statement to make changes to the replication scheme. See "Examples: Altering an active standby pair".
Start the replication agent on the active database. See "Starting and stopping the replication agents".
If the active standby pair includes cache groups, start the cache agent on the active database.
Destroy the standby database and the subscribers.
Duplicate the active database to the standby database. You can use either the ttRepAdmin -duplicate utility or the ttRepDuplicateEx C function to duplicate a database. If the active standby pair includes cache groups, use the -keepCG command line option with ttRepAdmin to preserve the cache group. See "Duplicating a database".
Set up the replication agent policy on the standby database and start the replication agent. See "Starting and stopping the replication agents".
Wait for the standby database to enter the STANDBY state. Use the ttRepStateGet procedure to check the state.
If the active standby pair includes cache groups, start the cache agent for the standby database using the ttCacheStart procedure or the ttAdmin -cacheStart utility.
Duplicate all of the subscribers from the standby database. See "Copying a master database to a subscriber". If the active standby pair includes cache groups, use the -noKeepCG command line option with ttRepAdmin in order to convert the cache group to regular TimesTen tables on the subscribers. See "Duplicating a database".
Set up the replication agent policy on the subscribers and start the agent on each of the subscriber databases. See "Starting and stopping the replication agents".

Examples: Altering an active standby pair

Example 6-5 Adding a subscriber to an active standby pair

Add a subscriber database to the active standby pair.

ALTER ACTIVE STANDBY PAIR
  ADD SUBSCRIBER sub1;

Example 6-6 Dropping subscribers from an active standby pair

Drop subscriber databases from the active standby pair.

ALTER ACTIVE STANDBY PAIR
  DROP SUBSCRIBER sub1
  DROP SUBSCRIBER sub2;

Example 6-7 Changing the PORT and TIMEOUT settings for subscribers

Alter the PORT and TIMEOUT settings for subscribers sub1 and sub2.

ALTER ACTIVE STANDBY PAIR
  ALTER STORE sub1 SET PORT 23000 TIMEOUT 180
  ALTER STORE sub2 SET PORT 23000 TIMEOUT 180;

Example 6-8 Adding a cache group to an active standby pair

Add a cache group to the active standby pair.

ALTER ACTIVE STANDBY PAIR
  INCLUDE CACHE GROUP cg0;