Skip to main content

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);

  • To modify an Oracle Reader connection profile, undeploy all applications using it, edit and save the connection profile, then restart the applications.

Transaction alerts

When the following conditions occur, alerts including text similar to the following examples will be written to the server log and appear in the web UI.

  • A transaction has been open for over 24 hours: Transaction cache alert: Transaction 6.27.716 has been open for 1 day(s) 0 hour(s)

  • Total number of open transactions currently being processed exceeds 1000: Transaction cache alert: Total open transactions 1002 exceeds threshold 1000

  • A single transaction contains more than 1 million operations: Transaction cache alert: Transaction 10.10.3033 has 1000006 operations, exceeds threshold 1000000

These alerts will repeat every ten minutes so long as the condition persists.

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.