Oracle Streams Enhancements

Previously introduced with Oracle 9.2, Oracle Streams is a lightweight replication feature that enables the sharing of data and events via a data stream within a database or from one database to another. Oracle Database 10g has taken the initial baseline of this technology and greatly enhanced its footprint in the latest version of the database. Following is a short summary of these features now available with Oracle Database 10g.

Streams Architecture

Starting with Oracle Database 10g, you can specify a new SGA pool for Oracle streams memory allocation called the streams pool. This new pool is created by specifying a value for the new dynamic initialization parameter STREAMS_POOL_SIZE. Prior to Oracle Database 10g, memory allocation for Oracle streams was taken from the shared pool and therefore DBAs who used Oracle streams had to provide extra space for this type of processing.

At instance startup, if the size of the streams pool is 0, then the memory used by Oracle streams is allocated from the shared pool. Even knowing that the STREAMS_POOL_SIZE parameter is dynamic, increasing it beyond 0 after the instance has already started has no effect on the current instance. Also, if the STREAMS_POOL_SIZE parameter is set to a value greater than 0 when the instance starts and then is reduced to 0 while the instance is already running, Oracle streams will not allocate any additional memory from the shared pool, thus not allowing streams to properly run in your environment.

When specifying the size for the STREAMS_POOL_SIZE, you should consider the following factors:

• 10MB for each capture process parallelism

• 1MB for each apply process parallelism

• 10MB or more for each queue staging captured events

Along with the SGA changes, Oracle Database 10g has also moved the necessary streams and LogMiner tables out of the SYSTEM tablespace over to the new SYSAUX tablespace. For more information about the new SYSAUX tablespace, refer to Chapter 1.

Along with the new streams pool, Oracle 10g also provides a new dynamic performance view called V$STREAMS_POOL_ADVICE that can help determine the best size for optimal performance.

Streams Administrator

Prior to Oracle 10g, the DBA had to create a user and grant the necessary privileges to connect and manage the various objects for streams. Missing any of these privileges would have caused a loss of functionality for your environment. To address this limitation, Oracle Database 10g introduces a new streams administrator package called DBMS_STREAMS_AUTH to assist with this effort. DBMS_STREAMS_AUTH can be used to grant and revoke the necessary privileges that are needed to manage streams. This new package enables a user to perform all the steps in the streams process such as capture, apply, propagate, queue, rule, and so on for the streams environment. For example, to assign someone the streams administrator privilege, you will need to use the GRANT_ADMIN_PRIVILEGE procedure:

 SQL> conn / as sysdba Connected. SQL> begin   2  dbms_streams_auth.grant_admin_privilege (   3  grantee => 'STROUPE',   4  grant_privileges => TRUE);   5  end;   6  / PL/SQL procedure successfully completed. 

You can also optionally assign someone the streams administrator privilege as well as generate a script to manually assign the necessary privileges for future users. The generated script will have the same benefit as running the GRANT_ADMIN_PRIVILEGE procedure.

 SQL> create directory TEMP as 'C:\temp'; Directory created. SQL> begin   2  dbms_streams_auth.grant_admin_privilege(   3  grantee => 'STROUPE',   4  grant_privileges => TRUE,   5  file_name => 'gen_streams_admin.sql',   6  directory_name => 'TEMP');   7  end;   8  / PL/SQL procedure successfully completed. 

To monitor the current streams administrators for your environment, query the new DBA view DBA_STREAMS_ADMINISTRATOR like so:

 SQL> select * from dba_streams_administrator; USERNAME                       LOC ACC ------------------------------ --- --- STROUPE                        YES YES 

To remove the streams administrator privilege, use the REVOKE_ADMIN_PRIVILEGE procedure. When removed, the user is also removed from the DBA_STREAMS_ADMINISTRATOR view.

 SQL> begin   2  dbms_streams_auth.revoke_admin_privilege (   3  grantee => 'STROUPE');   4  end;   5  / PL/SQL procedure successfully completed. SQL> select * from dba_streams_administrator; no rows selected 

Streams Downstream Capture

Streams capture involves a rigorous process of relaying changes from the redo logs over to a staging queue. This process requires extra resource overhead at the source database, which could have a negative impact for overall performance. Starting with Oracle Database 10g, you can shift this work to an alternative database to eliminate this overhead. This new process, known as downstream capture, creates the related database objects such as the LogMiner session, queues, rules, and necessary streams processes on another database for processing. The redo logs generated at the source database can be transported to the downstream database using generic log transport services, File Transfer Protocol (FTP), or the DBMS_FILE_TRANSFER package. Creating a database link from the downstream site to the source database is optional; however, it will greatly simplify the management between the two. If you do not use log transport services for the redo log copy, you must register the log file to the capture process by using the following command:

 SQL> alter database register logical logfile <FILENAME> for <CAPTURE_PROCESS>; 

Because the source and downstream databases use the same archive log files, both databases must run Oracle Database 10g on the same operating system. If, however, the downstream database is set up to send event messaging to another destination with streams, the remote destination is not required to be on the same operation system, although it must use Oracle Database 10g.

Streams-Enhanced RAC Support

To increase performance of streams in a RAC environment, a capture process can now capture changes propagated via archive redo log files or from the online redo log files. Modifications using streams can now be captured more closely to the time they were executed, thereby reducing the capture latency between the environments.

Also with Oracle Database 10g, RAC instance failover has been enhanced to provide greater support for streams capture and propagation. Now when there is a RAC failover, each queue used by the capture or apply from the failed instance is automatically assigned to a new instance in the cluster; the capture and apply processes follow their queues to the new instance, and all propagation jobs also automatically migrate over to the new instance. Please note, however, that for any propagations that were configured with the failed instance as the destination using database links, you must drop and re-create the database link using the same global name but point it to the new instance that owns the queue.

Other Streams Enhancements

Oracle Database 10g also offers the following enhancements for stream propagation:

• Enhanced message propagation and notification.

• Manageability improvements such as a new streams queue purging API, the capability to remove unnecessary archive log files, a new Point-In-Time Recovery API with flashback support, delete cascade constraints across databases, the capability to drop an unused streams rule set, subset rules for capture and propagation, replication support for a single tablespace or set of tablespaces, the capability to configure a capture user, support for negative rule sets, user-defined pre-commit handlers, enhanced DBMS_STREAMS_ADM methods to remove a streams queue or the entire streams environment, and enhancements to many existing and new streams-monitoring and performance views.

• Enhanced data type support for LONG, LONG RAW, CLOB, NCLOB, BINARY_FLOAT, BINARY_DOUBLE, and UROWID columns.

  Streams cannot support IOTs that contain LOB columns, cannot support overflow segments, and cannot have row movement enabled.

  
  

• New support for index-organized tables (IOTs) as well as tables with function-based and descending indexes.

• New streams-instantiation support for RMAN and Data Pump, or manually with SET_SCHEMA_INSTANTIATION_SCN and SET_GLOBAL_INSTANTIATION_SCN procedures in the DBMS_APPLY_ADM built-in PL/SQL package.

• Migration support from advanced replication to streams using the DBMS_REPCAT.STREAMS_MIGRATION procedure.