31 Troubleshooting Implicit Capture

Is Capture Process Creation or Data Dictionary Build Taking a Long Time?

If capture process creation or a data dictionary build is taking an inordinately long time, then it might be because one or more in-flight transactions have not yet committed. An in-flight transaction is one that is active during capture process creation or a data dictionary build.

To determine whether there are in-flight transactions, check the alert log for the following messages:

wait for inflight txns at this scn
Done with waiting for inflight txns at this scn

If you see only the first message in the alert log, then the capture process creation or data dictionary build is waiting for the inflight transactions and will complete after all of the in-flight transactions have committed.

Is the Capture Process Enabled?

A capture process captures changes only when it is enabled.

You can check whether a capture process is enabled, disabled, or aborted by querying the DBA_CAPTURE data dictionary view. For example, to check whether a capture process named capture is enabled, run the following query:

SELECT STATUS FROM DBA_CAPTURE WHERE CAPTURE_NAME = 'CAPTURE';

If the capture process is disabled, then your output looks similar to the following:

STATUS
--------
DISABLED

If the capture process is disabled, then try restarting it. If the capture process is aborted, then you might need to correct an error before you can restart it successfully.

To determine why the capture process aborted, query the DBA_CAPTURE data dictionary view or check the trace file for the capture process. The following query shows when the capture process aborted and the error that caused it to abort:

COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A10
COLUMN STATUS_CHANGE_TIME HEADING 'Abort Time'
COLUMN ERROR_NUMBER HEADING 'Error Number' FORMAT 99999999
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A40

SELECT CAPTURE_NAME, STATUS_CHANGE_TIME, ERROR_NUMBER, ERROR_MESSAGE
  FROM DBA_CAPTURE WHERE STATUS='ABORTED';

Is the Capture Process Waiting for Redo?

If an enabled capture process is not capturing changes as expected, then the capture process might be in WAITING FOR REDO state.

To check the state of each capture process in a database, run the following query:

COLUMN CAPTURE_NAME HEADING 'Capture Name' FORMAT A30
COLUMN STATE HEADING 'State' FORMAT A30
 
SELECT CAPTURE_NAME, STATE FROM V$STREAMS_CAPTURE;

If the capture process state is WAITING FOR REDO, then the capture process is waiting for new redo log files to be added to the capture process session. This state is possible if a redo log file is missing or if there is no activity at a source database. For a downstream capture process, this state is possible if the capture process is waiting for new log files to be added to its session.

Additional information might be displayed along with the state information when you query the V$STREAMS_CAPTURE view. The additional information can help you to determine why the capture process is waiting for redo. For example, a statement similar to the following might appear for the STATE column when you query the view:

WAITING FOR REDO: LAST SCN MINED 8077284

In this case, the output only identifies the last system change number (SCN) scanned by the capture process. In other cases, the output might identify the redo log file name explicitly. Either way, the additional information can help you identify the redo log file for which the capture process is waiting. To correct the problem, make any missing redo log files available to the capture process.

Is the Capture Process Paused for Flow Control?

If an enabled capture process is not capturing changes as expected, then the capture process might be in PAUSED FOR FLOW CONTROL state.

To check the state of each capture process in a database, run the following query:

COLUMN CAPTURE_NAME HEADING 'Capture Name' FORMAT A30
COLUMN STATE HEADING 'State' FORMAT A30
 
SELECT CAPTURE_NAME, STATE FROM V$STREAMS_CAPTURE;

If the capture process state is PAUSED FOR FLOW CONTROL, then the capture process cannot enqueue logical change records (LCRs) either because of low memory or because propagations and apply processes are consuming messages at a slower rate than the capture process is creating them. This state indicates flow control that is used to reduce the spilling of captured LCRs when propagation or apply has fallen behind or is unavailable.

If a capture process is in this state, then check for the following issues:

• An apply process is disabled or is performing slowly.

• A propagation is disabled or is performing poorly.

• There is not enough memory in the Streams pool.

You can query the V$STREAMS_APPLY_READER view to monitor the LCRs being received by the apply process. You can also query V$STREAMS_APPLY_SERVER view to determine whether all apply servers are applying LCRs and executing transactions.

Also, you can query the PUBLISHER_STATE column in the V$BUFFERED_PUBLISHERS view to determine the exact reason why the capture process is paused for flow control.

To correct the problem, perform one or more of the following actions:

• If any propagation or apply process is disabled, then enable the propagation or apply process.

• If the apply reader is not receiving data fast enough, then try removing propagation and apply process rules or simplifying the rule conditions.

• If there is not enough memory in the Streams pool at the capture process database, then try increasing the size of the Streams pool.

Is the Capture Process Current?

If a capture process has not captured recent changes, then the cause might be that the capture process has fallen behind. To check, you can query the V$STREAMS_CAPTURE dynamic performance view. If capture process latency is high, then you might be able to improve performance by adjusting the setting of the parallelism capture process parameter.

Are Required Redo Log Files Missing?

When a capture process is started or restarted, it might need to scan redo log files that were generated before the log file that contains the start SCN. You can query the DBA_CAPTURE data dictionary view to determine the first SCN and start SCN for a capture process. Removing required redo log files before they are scanned by a capture process causes the capture process to abort and results in the following error in a capture process trace file:

ORA-01291: missing logfile

If you see this error, then try restoring any missing redo log file and restarting the capture process. You can check the V$LOGMNR_LOGS dynamic performance view to determine the missing SCN range, and add the relevant redo log files. A capture process needs the redo log file that includes the required checkpoint SCN and all subsequent redo log files. You can query the REQUIRED_CHECKPOINT_SCN column in the DBA_CAPTURE data dictionary view to determine the required checkpoint SCN for a capture process.

If you are using the fast recovery area feature of Recovery Manager (RMAN) on a source database in an Oracle Streams environment, then RMAN might delete archived redo log files that are required by a capture process. RMAN might delete these files when the disk space used by the recovery-related files is nearing the specified disk quota for the fast recovery area. To prevent this problem in the future, complete one or more of the following actions:

• Increase the disk quota for the fast recovery area. Increasing the disk quota makes it less likely that RMAN will delete a required archived redo log file, but it will not always prevent the problem.

• Configure the source database to store archived redo log files in a location other than the fast recovery area. A local capture process will be able to use the log files in the other location if the required log files are missing in the fast recovery area. In this case, a database administrator must manage the log files manually in the other location.

RMAN always ensures that archived redo log files are backed up before it deletes them. If RMAN deletes an archived redo log file that is required by a capture process, then RMAN records this action in the alert log.

Is a Downstream Capture Process Waiting for Redo Data?

If a downstream capture process is not capturing changes, then it might be waiting for redo data to scan. Redo log files can be registered implicitly or explicitly for a downstream capture process. Redo log files registered implicitly typically are registered in one of the following ways:

• For a real-time downstream capture process, redo transport services use the log writer process (LGWR) to transfer the redo data from the source database to the standby redo log at the downstream database. Next, the archiver at the downstream database registers the redo log files with the downstream capture process when it archives them.

• For an archived-log downstream capture process, redo transport services transfer the archived redo log files from the source database to the downstream database and register the archived redo log files with the downstream capture process.

If redo log files are registered explicitly for a downstream capture process, then you must manually transfer the redo log files to the downstream database and register them with the downstream capture process.

Regardless of whether the redo log files are registered implicitly or explicitly, the downstream capture process can capture changes made to the source database only if the appropriate redo log files are registered with the downstream capture process. You can query the V$STREAMS_CAPTURE dynamic performance view to determine whether a downstream capture process is waiting for a redo log file. For example, run the following query for a downstream capture process named strm05_capture:

SELECT STATE FROM V$STREAMS_CAPTURE WHERE CAPTURE_NAME='STRM05_CAPTURE';

If the capture process state is either WAITING FOR DICTIONARY REDO or WAITING FOR REDO, then verify that the redo log files have been registered with the downstream capture process by querying the DBA_REGISTERED_ARCHIVED_LOG and DBA_CAPTURE data dictionary views. For example, the following query lists the redo log files currently registered with the strm05_capture downstream capture process:

COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A15
COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 9999999
COLUMN NAME HEADING 'Archived Redo Log|File Name' FORMAT A30
COLUMN DICTIONARY_BEGIN HEADING 'Dictionary|Build|Begin' FORMAT A10
COLUMN DICTIONARY_END HEADING 'Dictionary|Build|End' FORMAT A10

SELECT r.SOURCE_DATABASE,
       r.SEQUENCE#, 
       r.NAME, 
       r.DICTIONARY_BEGIN, 
       r.DICTIONARY_END 
  FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
  WHERE c.CAPTURE_NAME = 'STRM05_CAPTURE' AND 
        r.CONSUMER_NAME = c.CAPTURE_NAME;

If this query does not return any rows, then no redo log files are registered with the capture process currently. If you configured redo transport services to transfer redo data from the source database to the downstream database for this capture process, then ensure that the redo transport services are configured correctly. If the redo transport services are configured correctly, then run the ALTER SYSTEM ARCHIVE LOG CURRENT statement at the source database to archive a log file. If you did not configure redo transport services to transfer redo data, then ensure that the method you are using for log file transfer and registration is working properly. You can register log files explicitly using an ALTER DATABASE REGISTER LOGICAL LOGFILE statement.

If the downstream capture process is waiting for redo, then it also is possible that there is a problem with the network connection between the source database and the downstream database. There also might be a problem with the log file transfer method. Check your network connection and log file transfer method to ensure that they are working properly.

If you configured a real-time downstream capture process, and no redo log files are registered with the capture process, then try switching the log file at the source database. You might need to switch the log file more than once if there is little or no activity at the source database.

Also, if you plan to use a downstream capture process to capture changes to historical data, then consider the following additional issues:

• Both the source database that generates the redo log files and the database that runs a downstream capture process must be Oracle Database 10g or later databases.

• The start of a data dictionary build must be present in the oldest redo log file added, and the capture process must be configured with a first SCN that matches the start of the data dictionary build.

• The database objects for which the capture process will capture changes must be prepared for instantiation at the source database, not at the downstream database. In addition, you cannot specify a time in the past when you prepare objects for instantiation. Objects are always prepared for instantiation at the current database SCN, and only changes to a database object that occurred after the object was prepared for instantiation can be captured by a capture process.

Are You Trying to Configure Downstream Capture Incorrectly?

To create a downstream capture process, you must use one of the following procedures:

• DBMS_CAPTURE_ADM.CREATE_CAPTURE

• DBMS_STREAMS_ADM.MAINTAIN_GLOBAL

• DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS

• DBMS_STREAMS_ADM.MAINTAIN_SIMPLE_TTS

• DBMS_STREAMS_ADM.MAINTAIN_TABLES

• DBMS_STREAMS_ADM.MAINTAIN_TTS

• PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP in the DBMS_STREAMS_ADM package

The procedures in the DBMS_STREAMS_ADM package can configure a downstream capture process as well as the other Oracle Streams components in an Oracle Streams replication environment.

If you try to create a downstream capture process without using one of these procedures, then Oracle returns the following error:

ORA-26678: Streams capture process must be created first

To correct the problem, use one of these procedures to create the downstream capture process.

If you are trying to create a local capture process using a procedure in the DBMS_STREAMS_ADM package, and you encounter this error, then make sure the database name specified in the source_database parameter of the procedure you are running matches the global name of the local database.

Are You Trying to Configure Downstream Capture Without Proper Authentication?

If authentication is not configured properly between the source database and the downstream capture database, redo data transfer fails with one of the following errors:

ORA-16191: Primary log shipping client not logged on standby
 
ORA-1017: Invalid username/password; login denied

Redo transport sessions are authenticated using either the Secure Sockets Layer (SSL) protocol or a remote login password file. The password file must be the same at the source database and the downstream capture database.

To correct the problem, perform one of the following actions:

• If the source database has a remote login password file, then copy it to the appropriate directory on the downstream capture database system.

• Turn off the case sensitivity option by setting the initialization parameter SEC_CASE_SENSITIVE_LOGON to FALSE. Next, create the password file on the source and downstream capture database systems using ORAPWD. Make sure the password is the same on both systems, and set the ignorecase argument to Y.

Are More Actions Required for Downstream Capture Without a Database Link?

When downstream capture is configured with a database link, the database link can be used to perform operations at the source database and obtain information from the source database automatically. When downstream capture is configured without a database link, these actions must be performed manually, and the information must be obtained manually. If you do not complete these actions manually, then errors result when you try to create the downstream capture process.

Specifically, the following actions must be performed manually when you configure downstream capture without a database link:

• In certain situations, you must run the DBMS_CAPTURE_ADM.BUILD procedure at the source database to extract the data dictionary at the source database to the redo log before a capture process is created.

• You must prepare the source database objects for instantiation.

• You must obtain the first SCN for the downstream capture process and specify the first SCN using the first_scn parameter when you create the capture process with the CREATE_CAPTURE procedure in the DBMS_CAPTURE_ADM package.

Is a Synchronous Capture Failing to Capture Changes to Tables?

If a synchronous capture is not capturing changes to tables as you expected, then the rules in the synchronous capture rule set might not be configured properly. To avoid problems, always use the ADD_TABLE_RULES or ADD_SUBSET_RULES procedure in the DBMS_STREAMS_ADM package to add rules to a synchronous capture rules set.

The following are common reasons why a synchronous capture is not capturing changes as expected:

• Global rules or schema rules are being used to try to control the behavior of the synchronous capture. A synchronous capture ignores global rules and schema rules in its rule set. A synchronous capture only captures changes that satisfy table rules and subset rules.

• The DBMS_RULE_ADM package was used to configure the rules for a synchronous capture. A synchronous capture does not behave correctly when

  • The DBMS_RULE_ADM package is used to create rules that are added to a synchronous capture rule set.

  • The DBMS_RULE_ADM package is used to add rules to a synchronous capture rule set.

If a synchronous capture is not capturing changes to tables as expected, then complete the following steps to identify and correct problems:

1. Query the DBA_SYNC_CAPTURE_TABLES data dictionary view to determine the tables for which a synchronous capture is capturing changes. The synchronous capture captures changes to a table only if the ENABLED column is set to YES for the table.

2. If the DBA_SYNC_CAPTURE_TABLES view does not list tables for which a synchronous capture should capture changes, then use the ADD_TABLE_RULES or ADD_SUBSET_RULES procedure in the DBMS_STREAMS_ADM package to add rules for the tables.

If the DBA_SYNC_CAPTURE_TABLES view shows ENABLED for a table, and a synchronous capture still does not capture changes to the table, then there might be a problem with the rule condition in the rule for the table. In this case, check the rule condition and correct any errors, or drop the rule and re-create it using the ADD_TABLE_RULES or ADD_SUBSET_RULES procedure.