Oracle Database runtime considerations
Runtime considerations when using Oracle Reader
Starting an Oracle Reader source automatically opens an Oracle session for the user specified in the Username property.
The session is closed when the source is stopped.
If a running Oracle Reader source fails with an error, the session will be closed.
Closing a PDB source while Oracle Reader is running will cause the application to terminate.
When Dictionary Mode is set to Offline Catalog, you should run the following command every six hours, or every three hours if you expect many large transactions:
EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
Runtime considerations when using OJet
When reading from Oracle 11g, the name of an OJet reader must not exceed 18 characters. When reading from Oracle 12c or higher, the name must not exceed 118 characters
Schema evolution does not support tables containing ROWID columns.
You must execute the following command before you create or deploy an OJet application. You should run the command again every six hours, or every three hours if you expect many large transactions.
EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
Initial load to CDC handover with open transactions
If there may be open transactions when you start an OJet application, do the following.
Perform a dictionary dump on the primary database. (This will not wait for open transactions.)
exec DBMS_LOGMNR_D.BUILD(OPTIONS => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
On the primary database, perform table instantiation for all tables to be read. This command will not complete until all open transactions related to those tables are completed.
for non-CDB (replace <schema.table> with the appropriate value for your environment):
exec DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION( table_name => ‘<schema.table>', supplemental_logging => 'NONE', container => 'CURRENT');
for CDB (replace <schema.table> and <container> with the appropriate value for your environment):
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION( table_name => ‘<schema.table>', supplemental_logging => 'NONE', container => '<container>’);Run the following command on the primary database and note the SCN returned. Replace <table owner> with the appropriate value for your environment and <tables> with the same string used in step 2.
select min(SCN) from DBA_CAPTURE_PREPARED_TABLES where TABLE_OWNER = '<table owner>' and TABLE_NAME in ( <tables>)
Perform initial load.
Start OJet from the SCN returned in step 3.
Modifying a deployed OJet application
Note
If you prefer to have database objects dropped when the application is undeployed, set the drop_on_undeploy:true option in the OJet Config property (see OJet properties).
When you start an application containing an OJet adapter, Striim creates various objects in the source Oracle database including capture processes, apply processes, queue tables, queues, capture rules, and apply rules. Starting in Striim 5.0, these objects are retained in the database when the application adapter is stopped, quiesced, or undeployed, or when it halts or terminates. The objects are dropped from the database only when the application is dropped. Consequently, to avoid errors, you must not perform a REPLACE or ALTER ... RECOMPILE on an application or flow containing an OJet adapter, or modify the following OJet properties:
CDDL Capture
Connection URL
Primary Database Connection URL
Primary Database Connection Username
Username
Instead, export the application (see Apps page), drop it, update the exported TQL with the new values, and import it (see Apps page).
If you do not plan to run an OJet application soon but do not want to drop it, after undeploying it use the following command to remove the database objects from Oracle:
ALTER SOURCE <OJet adapter name> CLEAN EXTERNALPROCESSES IN <name of deployment group where OJet was last deployed>;
It is not necessary to run a COMPILE command after this.
Using the Show command
Use the SHOW command to view OJet status or memory usage.
SHOW <OJet source name> MEMORY [ DETAILS ] SHOW <OJet source name> STATUS [ DETAILS ]
The STATUS output includes:
APPLIED_SCN - all changes below this SCN have been
CAPTURE_TIME - Elapsed time (in hundredths of a second) scanning for changes in the redo log since the capture process was last started
CAPTURED_SCN - SCN of the last redo log record scanned
ENQUEUE_TIME - Time when the last message was enqueued
FILTERED_SCN - SCN of the low watermark transaction processed
FIRST_SCN indicates the lowest SCN to which the capture can be repositioned
LCR_TIME - Elapsed time (in hundredths of a second) creating LCRs since the capture process was last started
MESSAGES_CAPTURED - Total number of redo entries passed by LogMiner to the capture process for rule evaluation since the capture process last started
MESSAGES_ENQUEUED - Total number of messages enqueued since the capture process was last started
OLDEST_SCN - Oldest SCN of the transactions currently being processed
REDO_MINED - The total amount of redo data mined (in bytes) since the capture process last started
REDO_WAIT_TIME - Elapsed time (in hundredths of a second) spent by the capture process in the WAITING FOR REDO state
RESTART_SCN - The SCN from which the capture process started mining redo data when it was last started
RULE_TIME - Elapsed time (in hundredths of a second) evaluating rules since the capture process was last started
START_SCN from which the capture process starts to capture changes.
OJet Smart Alerts
For general information about Smart Alerts, see Managing Smart Alerts.
Alert name | Alert condition (default) | Notes |
|---|---|---|
OJet_MemoryUsageApply | Memory usage on the apply reader process exceeds 90% | By default, an alert will be sent every hour. Due to the ways in which memory is reused by the apply reader process, this alert may trigger even when there is not real memory pressure. Check the other alerts to see whether there is spilling to disk. This alert should be a concern only if the memory used by the apply keeps building up over extended periods of time. For example, to set the apply reader size to 10 GB:
|
OJet_MemoryUsageCapture | Memory usage on the capture process exceeds 90% | By default, an alert will be sent every hour. If you get this alert, the capture process may require additional memory for high performance. For example, to set the max sga size to 1024 MB:
|
OJet_MemoryUsageLogminer | Memory usage on the LogMiner session exceeds 90% | By default, an alert will be sent every hour. If you get this alert, you can give the LogMiner session additional memory for higher performance by increasing the max_sga_size on the capture process. |
OJet_MemoryUsageStreamsPool | Memory usage on the Streams pool exceeds 90% | By default, an alert will be sent every hour. If you get this alert, increase the streams_pool_size on the db instance. For example, ti increase it to 20 GB, enter the SQL command:
|
OJet_TransactionsSpillingToDisk | Transactions are spilled to disk on the db instance | By default, an alert will be sent every hour. If you get this alert:
|
The memory usage and status on all the components that OJet uses (Apply, Capture, and Logminer), can be seen in MON output.
If any of these alerts is triggered, the administrator should review the SHOW STATUS command output to see if the performance of the application is affected over a period of time.
In general, when any of these alerts is triggered it means the system is under some memory pressure and can slow down, and affecting performance. In that case you may need to allocate more memory, starting by increasing the streams_pool_size on the database.
When using multiple Ojet adapters, a max cap should be set on the capture and apply processes for each applications, so that memory usage on one won’t affect the performance of the others. These settings need to take into account the potential workload. For example, when there are many large transactions, the apply process will require enough memory to handle them all so they won’t be spilled to disk.
Handling long-running open transactions in Oracle
Typically, database transactions are completed quickly, within a few seconds. Occasionally, however, a database transaction may be open for a long time, minutes, hours, or even days, before it completes. Potential causes of long-running open transactions include:
A user started a transaction but forgot to commit it or roll it back.
A SQL client application or utility opens a transaction automatically when connecting to the database and is keeps it open until disconnected. Such transactions frequently contain no operations.
A SQL application with poor transaction handling logic does not properly close a transaction.
Network problems or disconnections can leave a transaction open if they occur after the transaction begins but before it is committed.
High levels of concurrent access and locking issues can delay completion of transactions.
Long-running open transactions can have several impacts on Striim:
When long-running open transactions contain many operations (typically the case with batch jobs), CPU, memory, and disk usage may increase, resulting in increased lag in writing to targets. In the worst case, Striim may run out of memory or disk space and halt.
Recovery (see Recovering applications) from a crash or planned downtime may take longer as Striim will restart from the point where the oldest open transaction was started. In the worst case, the redo / archive log files may no longer be available and recovery will fail.
Quiesce may fail because the transaction cannot be flushed within the 30-second timeout.
Therefore, you must monitor your applications for signs of long-running open transactions and take prompt steps to address them to minimize impact to your Striim environment. Where possible, Striim strongly recommends that you modify your upstream Oracle applications and configurations to avoid long-running open transactions.
Monitoring long-running open transactions
Your applications may be impacted by long-running open transactions if you see signs such as low disk space or memory for the Striim server, the recovery checkpoint not advancing, recovery taking a long time, or inability to quiesce an application.
To determine whether you indeed have long-running open transactions, use the MON <Oracle Reader name> command. The output will include an entry similar to the following:
Oldest Open Transactions │
[{"5.21.21991":{"# of
Ops":1,"CommitSCN":"null","Sequence #":"1","StartSCN":"60601569",
"Rba block #":"3847","Thread #":"1","TimeStamp":"2023-03-02T00:47:10.000+05:30"}}] This tells us that the transaction with StartSCN 60601569 has been running since March 3, 2023. "# of Ops":1 suggests that this transaction is open because a user didn't commit it or roll it back or because a SQL client application is behaving inappropriately. An open transaction that might cause out-of-memory or disk errors would typically have a large number of operations.
Alternatively, use the SHOW command (see Viewing open transactions).
Best practices for managing long-running open transactions
Ideally you should avoid long-running open transactions by not running queries that create them. This may involve breaking large batch jobs up into multiple smaller jobs that can complete in less time. You should also run batch jobs, particularly those that may last for hours, during maintenance windows or at times when the database is not at its busiest.
You should also educate users with permission to connect to Oracle not to leave transactions open without committing them or rolling them back, and not to use client applications in such a way that they leave transactions open.
Handling long-running open transactions in Striim applications that use Oracle Reader
When there are long-running open transactions with many operations, Oracle Reader's transaction buffer helps avoid out-of-memory errors by buffering transactions to disk on the Striim server. By default, Oracle Reader is configured so that when a transaction's memory usage exceeds 100 MB (Transaction Buffer Spillover Size), it is buffered to disk (Transaction Buffer Type) on the Striim server, and processing is resumed when memory is available. When recovery is enabled, after the application halts, terminates, or is stopped, the buffer will be reset. During recovery, any previously buffered transactions will restart from the beginning.
If you believe that long-running transactions that do not contain any DML operations relevant to your Striim applications may be causing problems in your environment, you may use Striim's DISCARD TRANSACTION functionality to discard them. This clears a specified open transaction from the transaction buffer, sends any buffered operations to downstream components such as CQs and writers, and makes the recovery checkpoint progress. Any further operations that are part of the discarded transaction will be ignored. This command should be used only with guidance from Striim support (see Contact Striim support).
Handling long-running open transactions in Striim applications that use OJet
When there are long-running open transactions with many operations, OJet's transaction buffer helps avoid out-of-memory errors by buffering transactions to disk on the Oracle database server. By default, OJet is configured so that when any one of the statements in a transaction has been in memory for 1000 seconds (Transaction Age Spillover Limit) or the total number of operations exceeds 10,000 (Transaction Buffer Spillover Count), the transaction is buffered to disk on the Oracle server, and processing is resumed when memory is available. When recovery is enabled, after the application halts, terminates, or is stopped, recovery may begin from the oldest open transaction. If you need assistance in dealing with long-running open transactions, Contact Striim support .
Viewing open transactions
SHOW <namespace>.<Oracle Reader or OJet source name> OPENTRANSACTIONS [ -LIMIT <count> ] [ -TRANSACTIONID '<transaction ID>,...'] [ DUMP | -DUMP '<path>/<file name>' ];
This console command returns information about currently open Oracle transactions. The namespace may be omitted when the console is using the source's namespace.
With no optional parameters, SHOW <source> OPENTRANSACTIONS; will display summary information for up to ten open transactions (the default LIMIT count is 10). Output for OJet will not include Rba block or Thread #.
╒══════════════════╤════════════╤════════════╤══════════════════╤════════════╤════════════╤═══════════════════════════════════════╕ │ Transaction ID │ # of Ops │ Sequence # │ StartSCN │ Rba block │ Thread # │ TimeStamp │ ├──────────────────┼────────────┼────────────┼──────────────────┼────────────┼────────────┼───────────────────────────────────────┤ │ 3.5.222991 │ 5 │ 1 │ 588206203 │ 5189 │ 1 │ 2019-04-05T21:28:51.000-07:00 │ │ 5.26.224745 │ 1 │ 1 │ 588206395 │ 5189 │ 1 │ 2019-04-05T21:30:24.000-07:00 │ │ 8.20.223786 │ 16981 │ 1 │ 588213879 │ 5191 │ 1 │ 2019-04-05T21:31:17.000-07:00 │ └──────────────────┴────────────┴────────────┴──────────────────┴────────────┴────────────┴───────────────────────────────────────┘
To show all open transactions, add
-LIMIT ALL.Add
-TRANSACTIONIDwith a comma-separated list of transaction IDs (for example,-TRANSACTIONID '3.4.222991, 5.26.224745') to return summary information about specific transactions in the console and write the details to OpenTransactions_<timestamp> in the current directory.Add
DUMPto show summary information in the console and write the details to OpenTransactions_<timestamp> in the current directory.Add
-DUMP [<path>/<file name>'to show summary information in the console and write the details to the specified file.
File lineage in Oracle
See SHOW for instructions on using SHOW LINEAGE. The file lineage report is also available via the get/applications/applicationMetadata/olm endpoint in the Striim application management REST API (see Using the Striim Application Management REST API) and for deployed and running Oracle Readers in the Flow Designer.
By default, this feature is disabled. See Enabling file lineage.
For OracleReader, the file lineage data includes:
File Name | the archive log file name, for example, |
Status | PROCESSING when OracleReader is reading the file, COMPLETED when it has finished |
Directory Name | the directory in which the file is located |
File Creation Time | the time Striim created the file lineage entry for the file |
Number Of Events | the number of events OracleReader has read from the file; if the application has been restarted, OracleReader may not start reading from the first event, so the number will be less than the total number of events in the file |
First Event Timestamp |
|
Last Event Timestamp |
|
Wrap Number | the number of times OracleReader resumed reading the file after the application was restarted |
SequenceNumber | the unique sequence number from the file name, for example, for |
ThreadID | the thread number associated with the log sequence number and file name |
FirstChangeNumber |
|
LastChangeNumber |
|
When OracleReader is reading from redo logs, file-related values are NOT APPLICABLE or N/A.
For more information, see LOG_ARCHIVE_FORMAT in Oracle's Database Reference.