7 Altering an Active Standby Pair

This chapter includes the following sections:

Making DDL changes in an active standby pair

The following sections describe how you can configure automatic replication of certain DDL statements. That is, when you execute a supported DDL statement on the active master, it is automatically replicated to all databases in the active standby pair replication scheme.

Controlling replication of objects to all databases in an active standby pair

Use the DDLReplicationLevel and DDLReplicationAction connection attributes to control what objects that are created or dropped by DDL statements are replicated to the databases involved in an active standby pair replication scheme. For more information on connection attributes, see "Specifying Data Source Names to identify TimesTen databases" in the Oracle TimesTen In-Memory Database Operations Guide

The DDLReplicationLevel connection attribute controls what DDL statements are replicated.

  • DDLReplicationLevel=1. CREATE or DROP statements for tables, indexes, or synonyms are not replicated to the standby database. However, you can add or drop columns to or from a replicated table, and those actions are replicated to the standby database.

  • DDLReplicationLevel=2 (the default) enables replication of creating and dropping of tables, indexes, and synonyms.

    To include tables in the replication scheme, the DDLReplicationAction connection attribute must be set to 'INCLUDE' (the default) before creating the table. If DDLReplicationAction='EXCLUDE', then the table is not included in the replication scheme. If the table is excluded from the replication scheme, then the DDL statements for creating and dropping tables are replicated to the standby master; however, any DML statements executed on the table are not replicated.

    Note:

    You may want to exclude a table from a replication scheme if:

    • You want to create a table in the replication scheme without either a primary key or a unique index on non-nullable columns.

    • You want to create a temporary table where the data is only used locally and you do not want to replicate updates for this table.

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

    However, you cannot alter a table to add a NOT NULL column to a table that is part of a replication scheme with the ALTER TABLE ... ADD COLUMN NOT NULL DEFAULT statement. You must remove the table from the replication scheme first before you can add a NOT NULL column to it.

  • DDLReplicationLevel=3 enables the following:

    • Replication to all databases in the replication scheme of the same objects that are replicated when DDLReplicationLevel=2.

    • Replication of creating and dropping of views to all databases in the replication scheme.

    • Replication of creating and dropping of sequences to all databases in the replication scheme, as long as DDLReplicationAction='INCLUDE' (the default) before creation. If DDLReplicationAction='EXCLUDE', then the sequence is not included in the replication scheme. If the sequence is excluded from the replication scheme, then the DDL statements for creating and dropping sequences are replicated to the standby master; however, each sequence on the active master and standby master are separate objects.

    • Replication of the results to the standby master when you set the cache administration user name and password on the active master with the ttCacheUidPwdSet built-in procedure. You do not need to stop and restart the cache agent or replication agent in order to execute the ttCacheUidPwdSet built-in procedure on the active master. See "Changing user names or passwords used by replication" for more details.

    • With this level, you can alter a table to add a NOT NULL column to a table that is part of a replication scheme with the ALTER TABLE ... ADD COLUMN NOT NULL DEFAULT statement.

You can set the DDLReplicationLevel attribute as part of the connection string or after the connection starts with the ALTER SESSION statement:

ALTER SESSION SET DDL_REPLICATION_LEVEL=3;

You can set the DDLReplicationAction attribute as part of the connection string or after the connection starts with the ALTER SESSION statement:

ALTER SESSION SET ddl_replication_action='EXCLUDE';

For more information, see "ALTER SESSION" and "ALTER ACTIVE STANDBY PAIR" in the Oracle TimesTen In-Memory Database SQL Reference.

Note:

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.

DDL statements that can be automatically replicated

You can execute the following DDL statements in an active standby pair without stopping the replication agent. In addition, these statements are replicated to all databases in the replication scheme. The following statements are automatically replicated when DDLReplicationLevel is 2 or 3 (the default is 2):

  • Create, alter, or drop a user with the CREATE USER, ALTER USER, or DROP USER statements.

  • Grant or revoke privileges from a user with the GRANT or REVOKE statements.

  • Alter a table to add or drop a column with the ALTER TABLE ... ADD COLUMN or ALTER TABLE ... DROP COLUMN statements. These are the only ALTER TABLE clauses that are replicated. However, when DDLReplicationLevel=2, you cannot alter a table to add a NOT NULL column to a table that is part of a replication scheme with the ALTER TABLE ... ADD COLUMN NOT NULL DEFAULT statement. You can execute this statement if DDLReplicationLevel=3.

  • Create or drop a table, including global temporary tables with the CREATE TABLE or DROP TABLE statements. The new table is also included in the active standby pair.

  • Create or drop a synonym with the CREATE SYNONYM or DROP SYNONYM statements.

  • Create or drop an index with the CREATE INDEX or DROP INDEX statements.

You can perform the following tasks in an active standby pair without stopping the replication agent. In addition, these statements are replicated to all databases in the replication scheme. The following statements are automatically replicated when DDLReplicationLevel is set to 3:

  • Create or drop a view with the CREATE VIEW or DROP VIEW statements.

  • Create or drop a sequence with the CREATE SEQUENCE or DROP SEQUENCE statements. These statements are automatically replicated to all databases in the replication scheme and included in the active standby pair when the DDLReplicationAction connection attribute is set to INCLUDE (the default) before creating the sequence; the sequence is not included in the replication scheme if the DDLReplicationAction connection attribute is set to EXCLUDE.

You do not have to stop the cache agent or replication agent when you set the user name and password for the cache administration user on the active master with the ttCacheUidPwdSet built-in procedure. When DDLReplicationLevel=3, then this information is automatically replicated to the standby master. See "Changing user names or passwords used by replication" for more information.

You can perform the following tasks in an active standby pair only after stopping the replication agents. These statements are not replicated to the standby master, so you must ensure that the changes are propagated to the standby master and any subscribers by either performing a duplicate or executing these statements on all nodes in the replication scheme after the replication agents are stopped. After execution, restart the replication agents on all nodes.

  • The DDL statements for creating, dropping, or altering a materialized view.

  • Changing the autorefresh mode or interval using the ALTER CACHE GROUP ... SET AUTOREFRESH MODE or ALTER CACHE GROUP ... SET AUTOREFRESH INTERVAL statements.

You can perform the following tasks in an active standby pair without stopping the replication agent. However, these statements are not replicated to the standby master, so you must ensure that the changes are propagated to the standby master and any subscribers by either performing a duplicate or executing these statements on all nodes in the replication scheme.

  • Changing the autorefresh state of a cache group using the ALTER CACHE GROUP ... SET AUTOREFRESH STATE statement. However, you cannot set a cache group autorefresh state to OFF on the active master.

  • Create or drop a PL/SQL function, PL/SQL procedure, PL/SQL package, or PL/SQL package body. You do not need to stop the replication agents for these objects. See "Creating a new PL/SQL object in an existing active standby pair" for more information on PL/SQL objects.

  • Any other DDL statements that are not replicated (except for materialized views).

Creating a new PL/SQL object in an existing active standby pair

To add a new PL/SQL procedure, package, package body or function to an existing active standby pair, complete these tasks:

  1. Create the PL/SQL object on the active database. The CREATE statement is not replicated to the standby database.

  2. Create the PL/SQL object on the standby database and any subscribers.

  3. Grant privileges to the new PL/SQL object on the active database. The GRANT statement is replicated to the standby database and any subscribers.

Restrictions on making DDL changes in an active standby pair

When DDLReplicationLevel=2 or 3:

  • CREATE TABLE ... AS SELECT, ALTER TABLE ... ADD CONSTRAINT, ALTER TABLE ... ADD UNIQUE and ALTER TABLE ... MODIFY statements are not replicated.

  • The CREATE INDEX statement is replicated only when the index is created on an empty table. To create a new index on populated tables, set DDLReplicationLevel to a value less than 2 and create the index manually on both the active and standby.

  • These statements cannot be executed on the standby database when DDLReplicationLevel=2 or 3:

    • CREATE USER, ALTER USER, DROP USER

    • CREATE TABLE, DROP TABLE

    • CREATE INDEX, DROP INDEX

    • GRANT, REVOKE

    • CREATE SYNONYM, DROP SYNONYM

When DDLReplicationLevel=3:

  • CREATE SEQUENCE ... CYCLE statements are not replicated.

  • These statements cannot be executed on the standby database when DDLReplicationLevel=3:

    • CREATE INDEX, DROP INDEX

    • CREATE SEQUENCE, DROP SEQUENCE when DDLReplicationAction='INCLUDE'.

      Note:

      However, you can create or drop a sequence on the standby master when DDLReplicationLevel=3 if DDLReplicationAction='EXCLUDE'.

Examples: Making DDL changes in an active standby pair

Example 7-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 7-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 7-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 7-4 CREATE SYNONYM is replicated

The DDLReplicationLevel is already set to 2, since it is the default. 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 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:

  1. Stop the replication agent on the active database. See "Starting and stopping the replication agents".

  2. If the active standby pair includes cache groups, stop the cache agent on the active database.

  3. Use the ALTER ACTIVE STANDBY PAIR statement to make changes to the replication scheme. See "Examples: Altering an active standby pair".

  4. Start the replication agent on the active database. See "Starting and stopping the replication agents".

  5. If the active standby pair includes cache groups, start the cache agent on the active database.

  6. Destroy the standby database and the subscribers.

  7. 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".

  8. Set up the replication agent policy on the standby database and start the replication agent. See "Starting and stopping the replication agents".

  9. Wait for the standby database to enter the STANDBY state. Use the ttRepStateGet built-in procedure to check the state.

  10. If the active standby pair includes cache groups, start the cache agent for the standby database using the ttCacheStart built-in procedure or the ttAdmin -cacheStart utility.

  11. Duplicate all of the subscribers from the standby database. See "Duplicating 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".

  12. 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 7-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 7-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 7-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 7-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;