Making DDL Changes in an Active Standby Pair
You can configure automatic replication of certain DDL statements. When you run 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
orDROP
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. IfDDLReplicationAction='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 run 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 theALTER TABLE ... ADD COLUMN NOT NULL DEFAULT
statement. You must remove the table from the replication scheme first before you can add aNOT 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. IfDDLReplicationAction='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 run thettCacheUidPwdSet
built-in procedure on the active master. See Changing User Names or Passwords Used by Replication. -
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 theALTER 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';
See ALTER SESSION and ALTER ACTIVE STANDBY PAIR in the Oracle TimesTen In-Memory Database SQL Reference.
Note:
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 run 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
, orDROP USER
statements. -
Grant or revoke privileges from a user with the
GRANT
orREVOKE
statements. -
Alter a table to add or drop a column with the
ALTER TABLE ... ADD COLUMN
orALTER TABLE ... DROP COLUMN
statements.These are the only
ALTER TABLE
clauses that are replicated. However, whenDDLReplicationLevel=2
, you cannot alter a table to add aNOT NULL
column to a table that is part of a replication scheme with theALTER TABLE ... ADD COLUMN NOT NULL DEFAULT
statement. You can run this statement ifDDLReplicationLevel=3
. -
Create or drop a table with the
CREATE TABLE
orDROP TABLE
statements. The new table is included in the active standby pair. -
Create or drop a synonym with the
CREATE SYNONYM
orDROP SYNONYM
statements. -
Create or drop an index with the
CREATE INDEX
orDROP 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
orDROP VIEW
statements. -
Create or drop a sequence with the
CREATE SEQUENCE
orDROP SEQUENCE
statements. These statements are automatically replicated to all databases in the replication scheme and included in the active standby pair when theDDLReplicationAction
connection attribute is set toINCLUDE
(the default) before creating the sequence; the sequence is not included in the replication scheme if theDDLReplicationAction
connection attribute is set toEXCLUDE
.
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.
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 running these statements on all nodes in the replication scheme after the replication agents are stopped. After processing completes, 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
orALTER 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 running 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 toOFF
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.
-
Any other DDL statements that are not replicated (except for materialized views).
Creating a New PL/SQL Object in an Existing Active Standby Pair
There are certain tasks to perform to add a new PL/SQL procedure, package, package body or function to an existing active standby pair.
-
Create the PL/SQL object on the active database. The
CREATE
statement is not replicated to the standby database. -
Create the PL/SQL object on the standby database and any subscribers.
-
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
There are certain restrictions when 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
andALTER 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, setDDLReplicationLevel
to a value less than 2 and create the index manually on both the active and standby. -
These statements cannot run 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 run on the standby database when
DDLReplicationLevel=3
:-
CREATE INDEX
,DROP INDEX
-
CREATE SEQUENCE
,DROP SEQUENCE
whenDDLReplicationAction='INCLUDE'
.Note:
However, you can create or drop a sequence on the standby master when
DDLReplicationLevel=3
ifDDLReplicationAction='EXCLUDE'
.
-
Examples Showing How to Making DDL changes in an Active Standby Pair
These examples demonstrate how to make DDL changes in an active standby pair.
The following example demonstrates DDL changes when you 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.
The following example demonstrates DDL changes when you 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.
The following example demonstrates DDL changes when 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 range index on columns: B TAB2: unique range index on columns: A 2 indexes found. Indexes on table TERRY.NEWTAB: NEWTAB: unique range index on columns: A 1 index found. Indexes on table TERRY.TABINCLUDE: TABINCLUDE: unique range index on columns: A 1 index found. 4 indexes found on 3 tables.
The following example demonstrates DDL changes when 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.