Oracle Database operational 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 max(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).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: `OJetConfig: ‘{ "APPLY" : [ "max_sga_size:10240" ] }'`
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: `OJetConfig: ‘{ "CAPTURE" : [ "max_sga_size:1024" ] }'`
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: `alter system set streams_pool_size=20G scope=both;`
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: Increase TransactionBufferSpilloverCount to a value greater than the larget transaction size expected. Verify that TransactionAgeSpilloverLimit is set to a value that is not causing unnecessary spill to disk. Increase the streams_pool_size in order to keep the transactions in memory.

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.

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 -TRANSACTIONID with 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 DUMP to 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.