Usability Improvements - Oracle 11g

Oracle Database 11g improves Oracle Streams to make the product easier to use, manage, and implement. Improvements include features such as the following:

  • Message tracking
  • Automated alerts
  • Identifying newly supported tables
  • Identifying minimum required checkpoint SCN
  • Background process name changes
  • Support for XMLTypes stored as CLOBs
  • Support for TDE
  • Support for Oracle Scheduler

Message Tracking

You can now monitor messages across all the databases. The V$STREAMS_ MESSAGE_TRACKING view will capture all the areas of a message from capture to propagation to apply.

This view provides information including timestamp,action, object name,and command type Another benefit that this view provides is the ability to see the path of the message.Streams message tracking is turned on by executing theset_message_tracking procedure:

dbms_streams_adm.set_message_tracking (tracking_label => 'DOC_TRACK');

You can use the set_message_tracking procedure to assign a custom label. By default, the default label is called Streams_tracking. You can query theV$STREAMS_ MESSAGE_TRACKING view to track the LCRs and the process flow through each of the clients.

When you detect that LCRs are not being applied, this view can be helpful to diagnose the problems.This view will provide the necessary information to determine where in the process the LCRs are bottlenecked. You can confirm that your tracking label is set for your session:

SQL> select dbms_streams_adm.get_message_tracking () from dual;

To stop message tracking for the session, you can call the same procedure that you used to set message tracking on, except now, you will set the tracking label to NULL:

dbms_streams_adm.set_message_tracking(tracking_label => NULL);

Automated Alerts for Clients and Thresholds

Oracle Enterprise Manager Database Console detects when the Streams client is disabled or exceeds the specified threshold and triggers an alert. Oracle categorizes two kinds of alerts: stateful and stateless.

A stateful alert is generally associated with a numeric value and has threshold-level settings for warning and critical levels.In Oracle Database 11g,Oracle provides alerts for Streams pool memory usage.

Enterprise Manager Database Console would trigger a warning alert when the Streams pool memory hits 85 percent utilization.You can modify this threshold using Enterprise Manager Database Console or using the dbms_server_alert.set_ threshold procedure.

A stateless alert is an alert that generates a single event and is not tied to a state.Here are all the valid conditions that would generate a stateless alert:

  • A capture process aborts.
  • A propagation aborts after 16 consecutive errors.
  • An apply process aborts.
  • An apply process with an empty error queue encounters an apply error.

Streams alerts can be viewed using the Enterprise Manager or via the database dictionary views.You can view DBA_OUTSTANDING_ ALERTS for current stateful alerts and refer to DBA_ALERT_HISTORY for alerts that have been cleared from the DBA_OUTSTANDING_ ALERTS view.

The DBA_ALERT_HISTORY view also has information about the stateless alerts that have been cleared.Both of these views have two common columns: REASON and SUGGESTED_ACTION. Here’s an example query from the DBA_OUTSTANDING_ALERTS view:

select reason, suggested_action
from dba_alert_history
where module_id like '%STREAMS%';

You can use the same query as earlier and change the view name to DBA_OUTSTANDING_ALERTS to get a list of the current stateful alerts.

Background Process Name Changes

The DBA_STREAMS_NEWLY_SUPPORTED view displays information about tables that are newly supported by Oracle Stream.Even though this view is not new to Oracle Database 11g, you can query this view to determine what tables are supported with each version of the database.You can see that Oracle extends support for XMLTypes in release 11.1:

1 select owner||'.'||table_name table_name, reason, compatible
2 from dba_streams_newly_supported
3* order by compatible desc
SQL> /
---------------------- ------------------------- ----
OE.EDBA_PO table with XMLType column 11.1
OE.WAREHOUSES table with XMLType column 11.1
WKSYS.WK$_HTTPAUTH IOT with row overflow 10.2
WKSYS.WK$_SYSINFO IOT with row overflow 10.2

Identifying Minimum Required Checkpoint SCN

Oracle Database 11g adds a new column in the V$DATABASE view calledMIN_REQUIRED_CAPTURE_ CHANGE#.

The purpose of this column is to indicate the minimum required checkpoint SCN for all local capture processes.You can join this to the V$ARCHIVED_LOG view to determine the required archivelogs.

Background Process Name Changes

Oracle renames their process names in Oracle Database 11g. Here are the naming convention changes:

  • Capture process names have changed to cp00 to cpnn.
  • Apply process names have changed to ap00 to apnn.
  • Apply reader and apply servers have changed to as00 to asnn.
  • LogMiner processes are now v000 to vnnn.

The values for nn can be 0–9 or a–z. You can see from the following output the new process names:

r11b > ps -fu oracle |grep -i ora_ |egrep -i "cp|ap|v0|as"
oracle 3810 1 0 04:35 ? 00:00:07 ora_cp01_r11b
r11a > ps -fu oracle |grep -i ora_ |egrep -i "cp|ap|v0|as"
oracle 20141 1 0 06:08 ? 00:00:00 ora_ap01_r11a
oracle 20143 1 0 06:08 ? 00:00:00 ora_as01_r11a
oracle 20145 1 0 06:08 ? 00:00:00 ora_as00_r11a

Support for XMLType Values Stored As CLOBs

Effective as of Oracle Database 11g, Oracle Streams extends support for XMLTypes stored as CLOBs.There is one little caveat worth mentioning, though. Old values for the XMLTypes stored as CLOBs are not logged. For conflict detection and resolution, you will need a TIMESTAMP column to be added to your table.

Support for Transparent Data Encryption

Oracle Database 11g Streams starts support for data encrypted with trans parent data encryption.Oracle supports both column- and tablespace-level encryption. Whether the data is encrypted via the column level or tablespace level, Streams components handle the column data in the same way.

By default,Oracle Streams handles the capture and apply processes similarly.If the source column or tablespace was encrypted with TDE, the Streams apply process will keep intact the level of encryption set at the target.

The PRESERVE_ENCRYPTION apply parameter can change a column from TDE to non-TDE as it applies the messages on the target database.For example,the source database can enforce encryption with TDE,but if the target system does not have licensing for the Advanced Security Option,the column data can be decrypted before it is written to disk.

By specifying PRESERVE_ENCRYPTION to N on the target apply process,data will be decrypted; on the other hand,if the target column is not TDE enabled and PRESERVE_ENCRYPTION is set to Y, you will receive an error.The row LCRs will also be moved to the error queue.You can set the preserve encryption using the set_parameter procedure:

SQL> begin
2 dbms_apply_adm.set_parameter (
3 apply_name => 'APPLY_STREAM',
4 parameter => 'preserve_encryption',
5 value => 'n');
6* end;
SQL> /
PL/SQL procedure successfully completed.

Intrinsically, Streams also supports TDE capture for downstream capture processes if the wallet is shared with the source database.The wallet file can be shared via the Network File System (NFS) mount or can be copied manually using the traditional secure file transfer protocols such as sftp or scp.

Support for Oracle Scheduler

In previous releases,Oracle Streams was configured to use job queue processes with the DBMS_JOB package for such tasks as propagation and event notification.DBMS_JOB has a dependency on the job_queue_processes initiali zation parameter being set.

Oracle DBAs had to monitor for failed jobs,or worse, broken jobs.Oracle Database 11g Streams propagation uses Oracle Scheduler instead of DBA_JOBS.This allows for the following:

  • Optimized propagated scheduling with a dedicated job for lowest propagation latency
  • Shared job processing where propagation can be run in batch mode
  • Event-driven processes where propagation is started where there are data to be propagated

Oracle Scheduler can be used to adjust the schedules of a Streams timetable. Propagation jobs use slave processes when needed to execute jobs.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics