In this chapter, the implementation of
Streams in a typical Real Application Clusters (RAC) environment
will be presented. With many businesses adopting RAC database
technology for their data needs, the topic of Streams in a RAC
environment will be of high importance. The differences in the
Streams configuration in a RAC environment will be highlighted.
Information will be included on the extra things that need to be
considered on account of the database environment being a RAC.
Streams in a RAC
Environment
A RAC is a single database with multiple
instances. Each instance in the RAC database environment has its
own set of redo log files. Each instance thread produces its own
set of archived log files; however, those redo log files and
archived log files are accessible to all of the instances. Redo
and archived log files reside on shared storage. Shared storage
is configured either as an approved cluster file system or
through Oracle’s Automatic Storage Management (ASM).
As shown in Figure 9.1, each of the RAC
database instances consists of the following: Instance-1 has
redo groups from 1 to 3; Instance-2 has redo groups 4 to 6; and
Instance-3 has redo groups 7 to 9. The Streams Capture process
extracts all the database changes recorded in all of the redo
log files.
Figure 9.1 Typical RAC database
environment
The Oracle10g Database release has brought
in two important enhancements:
Streams in a RAC
Environment
-
In order to improve the performance of
Streams for RAC databases, a Capture process can now capture
changes from the archived redo logs or from the online redo.
This feature allows changes to be captured closer to the time
they were executed, thereby reducing the capture latency. In
other words, the moment database changes are effected and
committed the transactions are available for the Capture process
to extract.
-
When the owner instance for a
queue table containing a queue used by a Capture process or
Apply process fails, queue ownership is transferred
automatically to another instance in the cluster. Then the
Capture process or Apply process is restarted automatically, if
it had been running. In previous releases, the Capture process
or Apply process used to be ABORTED under these circumstances
which would warrant a manual restart. This is an important
improvement from the administrative point of view. Without the
intervention of the DBA, the Streams process will be restarted.
Capture and Apply Processes in a RAC
Instance
Since there are multiple instances in a RAC
database, the database changes can occur at any instance. Such
database changes are recorded in the respective instance’s redo
logs and corresponding archive log files. A Capture process
configured within any instance of the RAC database can scan and
extract the transactional activity from the all the
participating instance’s redo log files and convert them into
LCR events. In this way, even though the Capture process is only
running on one instance, it is aware of all the redo logs of all
the RAC instances and does not miss any transactions.
Each Capture process is started on the
owner instance for its SYS.AnyData queue, even if the start
capture is executed on a different instance. The
dba_queue_tables data dictionary view contains information
about the owner instance for a queue table. Any parallel
execution servers used by a single Capture process run on a
single instance in a RAC environment.
Capture and Apply Processes in a RAC
Instance
Whenever an instance in an Oracle RAC cluster
fails, the instance is recovered immediately by another instance in
the cluster. The following actions occur that affect the Streams
environment:
-
Each queue owned by the failed
instance is assigned to a new instance.
-
The Capture process is restarted
automatically on the instance that now owns the queue. If the failed
instance is brought back online later, the Capture process does not
move back to the original instance. Even though the failed instance
was running the Capture process at the time of failure, it is no
longer the owner of the queue used by the Capture process. This is
the normal behavior unless it is altered by the alter_queue_table
procedure, which is explained in a later section.
-
All propagation jobs are
automatically migrated to the new instance from the failed instance.
The SYS.AnyData queues can be configured on any
instances within the RAC database. Only the owner instance may have
a buffer for a queue, but different instances may have buffers for
different queues. A buffered queue is System Global Area (SGA)
memory associated with a SYS.AnyData queue that contains only
captured events.
Figure 9.2 shows that instance DNYDBA2A has the
SYS.AnyData and the associated buffers.
Figure 9.2 Streams in a RAC environment
Only the instance that has the queue, and
therefore the buffers, can propagate and receive the LCR events.
Each of the propagations that propagate captured events to a RAC
destination database must use an instance-specific database link
that refers to the owner instance of the destination queue. If the
propagation connects to any other instance, the propagation will
raise an error.
Capture and Apply Processes in a RAC
Instance
That is the reason that when the Non-RAC
instance, DNYTST10, receives or propagates the captured events,
it needs to use the instance specific database link to DNYDBA2A.
Whenever the owner instance for a queue
table containing a destination queue becomes unavailable, the
queue ownership is transferred automatically to another instance
in the cluster.
Since the queue is migrated to a new
instance, the database link will need to be recreated. For the
propagations that were configured with the failed instance as
the destination, the database link should be dropped and then
recreated using the same global name. During recreation, the
link will be pointed to the new instance that now owns the
queue. Scripts that drop and re-create all necessary database
links can be created and then run at the sites that are
attempting to propagate to the failed instance. The propagation
details do not need to be modified since the name of the
database link is not being changed.
Next, the failover process with in the RAC
environment will be illustrated with an example. Information on
the effect of the failover process on the Streams related
components will be presented.
As seen in Figure 9.2, the DNYTST10
database propagates and receives replication changes from the
RAC database, DNYDBA20. The RAC database has two instances:
DNYDBA2A and DNYDBA2B.
The DNYTST10 instance will have a database
link to the RAC database named DNYDBA20, which corresponds to
the database name. Assuming that the initial queue is on the
DNYDBA2A instance, it will use the TNS entry that points to
DNYDBA2A instance as shown below:
Use the following SQL queries to view the
instance details and buffers details at the DNYDBA2A instance:
SQL> select * from v$active_instances ;
INST_NUMBER INST_NAME
-----------
--------------------------------------------
1 nytms-a:DNYDBA2A
2 nytms-b:DNYDBA2B
SQL> select instance_name, instance_number
from v$instance ;
INSTANCE_NAME INSTANCE_NUMBER
---------------- ---------------
DNYDBA2A 1
SQL> select QUEUE_TABLE, TYPE, OBJECT_TYPE,
PRIMARY_INSTANCE,
SECONDARY_INSTANCE, OWNER_INSTANCE from
DBA_QUEUE_TABLES
SEE CODE DEPOT FOR FULL SCRIPT
/
Primry Secndry Owner
QUEUE_TABLE TYPE OBJECT_TYPE
Insta Insta Insta
------------------- ------- ------------
-------- --------- ------
NY1_QUEUE_TABLE OBJECT
SYS.ANYDATA 0 0 1
SQL> show user
USER is "SYS"
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
After the DNYDBA2A is shutdown, the queue
buffers move over to instance DNYDBA2B automatically. Now,
connect to the DNYDBA2B instance and query the owner instance
for the NY1_QUEUE_TABLE queue.
SQL> select instance_name, instance_number
from v$instance ;
INSTANCE_NAME INSTANCE_NUMBER
---------------- ---------------
DNYDBA2B 2
SQL> select QUEUE_TABLE, TYPE, OBJECT_TYPE,
PRIMARY_INSTANCE,
SECONDARY_INSTANCE, OWNER_INSTANCE from
DBA_QUEUE_TABLES
SEE CODE DEPOT FOR FULL SCRIPT
/
