14 Troubleshooting Oracle Streams Replication

Recovery Scenario

This section contains a scenario in which the MAINTAIN_SCHEMAS procedure stops because it encounters an error. Assume that the following procedure encountered an error when it was run at the capture database:

BEGIN
  DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
    schema_names                 => 'hr',
    source_directory_object      => 'SOURCE_DIRECTORY',
    destination_directory_object => 'DEST_DIRECTORY',
    source_database              => 'inst1.net',
    destination_database         => 'inst2.net',
    perform_actions              => TRUE,
    dump_file_name               => 'export_hr.dmp',
    capture_queue_table          => 'rep_capture_queue_table',
    capture_queue_name           => 'rep_capture_queue',
    capture_queue_user           => NULL,
    apply_queue_table            => 'rep_dest_queue_table',
    apply_queue_name             => 'rep_dest_queue',
    apply_queue_user             => NULL,
    capture_name                 => 'capture_hr',
    propagation_name             => 'prop_hr',
    apply_name                   => 'apply_hr',
    log_file                     => 'export_hr.clg',
    bi_directional               => FALSE,
    include_ddl                  => TRUE,
    instantiation                => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA);
END;
/

Complete the following steps to diagnose the problem and recover the operation:

1. Query the DBA_RECOVERABLE_SCRIPT_ERRORS data dictionary view at the capture database to determine the error:

   COLUMN SCRIPT_ID     HEADING 'Script ID'     FORMAT A35
   COLUMN BLOCK_NUM     HEADING 'Block|Number' FORMAT 999999
   COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A33
   
   SELECT SCRIPT_ID, BLOCK_NUM, ERROR_MESSAGE FROM DBA_RECOVERABLE_SCRIPT_ERRORS;
   

   The query returns the following output:

   Block
   Script ID                            Number Error Message
   ----------------------------------- ------- ---------------------------------
   F73ED2C9E96B27B0E030578CB10B2424         12 ORA-39001: invalid argument value
   

2. Query the DBA_RECOVERABLE_SCRIPT_BLOCKS data dictionary view for the script ID and block number returned in Step 1 for information about the block in which the error occurred. For example, if the script ID is F73ED2C9E96B27B0E030578CB10B2424 and the block number is 12, run the following query:

   COLUMN FORWARD_BLOCK        HEADING 'Forward Block'               FORMAT A50
   COLUMN FORWARD_BLOCK_DBLINK HEADING 'Forward Block|Database Link' FORMAT A13
   COLUMN STATUS               HEADING 'Status'                      FORMAT A12
   
   SET LONG 10000
   SELECT FORWARD_BLOCK,
          FORWARD_BLOCK_DBLINK,
          STATUS
     FROM DBA_RECOVERABLE_SCRIPT_BLOCKS
     WHERE SCRIPT_ID = 'F73ED2C9E96B27B0E030578CB10B2424' AND
           BLOCK_NUM = 12;
   

   The output contains the following information:

   • The FORWARD_BLOCK column contains detailed information about the actions performed by the procedure in the specified block. If necessary, spool the output into a file. In this scenario, the FORWARD_BLOCK column for block 12 contains the code for the Data Pump export.

   • The FORWARD_BLOCK_DBLINK column shows the database where the block is executed. In this scenario, the FORWARD_BLOCK_DBLINK column for block 12 shows INST1.NET because the Data Pump export was being performed on INST1.NET when the error occurred.

   • The STATUS column shows the status of the block execution. In this scenario, the STATUS column for block 12 shows ERROR.

3. Interpret the output of the queries and diagnose the problem. The output returned in Step 1 provides the following information:

   • The unique identifier for the configuration operation is F73ED2C9E96B27B0E030578CB10B2424. This value is the RAW value returned in the SCRIPT_ID field.

   • Only one Oracle Streams configuration procedure is in the process of running because only one row was returned by the query. If more than one row was returned by the query, then query the DBA_RECOVERABLE_SCRIPT and DBA_RECOVERABLE_SCRIPT_PARAMS views to determine which script ID applies to the configuration operation.

   • The cause in Oracle Database Error Messages for the ORA-39001 error is the following: The user specified API parameters were of the wrong type or value range. Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS will further describe the error.

   • The query on the DBA_RECOVERABLE_SCRIPT_BLOCKS view shows that the error occurred during Data Pump export.

   The output from the queries shows that the MAINTAIN_SCHEMAS procedure encountered a Data Pump error. Notice that the instantiation parameter in the MAINTAIN_SCHEMAS procedure was set to DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA. This setting means that the MAINTAIN_SCHEMAS procedure performs the instantiation using a Data Pump export and import. A Data Pump export dump file is generated to complete the export/import.

   Data Pump errors usually are caused by one of the following conditions:

   • One or more of the directory objects used to store the export dump file do not exist.

   • The user running the procedure does not have access to specified directory objects.

   • An export dump file with the same name as the one generated by the procedure already exists in a directory specified in the source_directory_object or destination_directory_object parameter.

4. Query the DBA_RECOVERABLE_SCRIPT_PARAMS data dictionary view at the capture database to determine the names of the directory objects specified when the MAINTAIN_SCHEMAS procedure was run:

   COLUMN PARAMETER HEADING 'Parameter' FORMAT A30
   COLUMN VALUE     HEADING 'Value'     FORMAT A45
   
   SELECT PARAMETER,
          VALUE
          FROM DBA_RECOVERABLE_SCRIPT_PARAMS
          WHERE SCRIPT_ID = 'F73ED2C9E96B27B0E030578CB10B2424';
   

   The query returns the following output:

   Parameter                      Value
   ------------------------------ ---------------------------------------------
   SOURCE_DIRECTORY_OBJECT        SOURCE_DIRECTORY
   DESTINATION_DIRECTORY_OBJECT   DEST_DIRECTORY
   SOURCE_DATABASE                INST1.NET
   DESTINATION_DATABASE           INST2.NET
   CAPTURE_QUEUE_TABLE            REP_CAPTURE_QUEUE_TABLE
   CAPTURE_QUEUE_OWNER            STRMADMIN
   CAPTURE_QUEUE_NAME             REP_CAPTURE_QUEUE
   CAPTURE_QUEUE_USER
   APPLY_QUEUE_TABLE              REP_DEST_QUEUE_TABLE
   APPLY_QUEUE_OWNER              STRMADMIN
   APPLY_QUEUE_NAME               REP_DEST_QUEUE
   APPLY_QUEUE_USER
   CAPTURE_NAME                   CAPTURE_HR
   APPLY_NAME                     APPLY_HR
   PROPAGATION_NAME               PROP_HR
   INSTANTIATION                  INSTANTIATION_SCHEMA
   BI_DIRECTIONAL                 TRUE
   INCLUDE_DDL                    TRUE
   LOG_FILE                       export_hr.clg
   DUMP_FILE_NAME                 export_hr.dmp
   SCHEMA_NAMES                   HR
   

5. Ensure that the directory object specified for the source_directory_object parameter exists at the source database, and ensure that the directory object specified for the destination_directory_object parameter exists at the destination database. Check for these directory objects by querying the DBA_DIRECTORIES data dictionary view.

   For this scenario, assume that the SOURCE_DIRECTORY directory object does not exist at the source database, and the DEST_DIRECTORY directory object does not exist at the destination database. The Data Pump error occurred because the directory objects used for the export dump file did not exist.

6. Create the required directory objects at the source and destination databases using the SQL statement CREATE DIRECTORY. See "Create the Required Directory Objects" for instructions.

7. Run the RECOVER_OPERATION procedure at the capture database:

   BEGIN
     DBMS_STREAMS_ADM.RECOVER_OPERATION(
       script_id       => 'F73ED2C9E96B27B0E030578CB10B2424',
       operation_mode  => 'FORWARD');
   END;
   /
   

   Notice that the script_id parameter is set to the value determined in Step 1, and the operation_mode parameter is set to FORWARD to complete the configuration. Also, the RECOVER_OPERATION procedure must be run at the database where the configuration procedure was run.

Is the Capture Process Waiting for Redo?

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 the capture process state is PAUSED FOR FLOW CONTROL, then the capture process is unable to 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, if the capture process does not use combined capture and apply, then 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 Apply Process Encountering Contention?

An apply server is a component of an apply process. Apply servers apply DML and DDL changes to database objects at a destination database. An apply process can use one or more apply servers, and the parallelism apply process parameter specifies the number of apply servers that can concurrently apply transactions. For example, if parallelism is set to 5, then an apply process uses a total of five apply servers.

An apply server encounters contention when the apply server must wait for a resource that is being used by another session. Contention can result from logical dependencies. For example, when an apply server tries to apply a change to a row that a user has locked, then the apply server must wait for the user. Contention can also result from physical dependencies. For example, interested transaction list (ITL) contention results when two transactions that are being applied, which might not be logically dependent, are trying to lock the same block on disk. In this case, one apply server locks rows in the block, and the other apply server must wait for access to the block, even though the second apply server is trying to lock different rows. See "Is the Apply Process Waiting for a Dependent Transaction?" for detailed information about ITL contention.

When an apply server encounters contention that does not involve another apply server in the same apply process, it waits until the contention clears. When an apply server encounters contention that involves another apply server in the same apply process, one of the two apply servers is rolled back. An apply process that is using multiple apply servers might be applying multiple transactions at the same time. The apply process tracks the state of the apply server that is applying the transaction with the lowest commit SCN. If there is a dependency between two transactions, then an apply process always applies the transaction with the lowest commit SCN first. The transaction with the higher commit SCN waits for the other transaction to commit. Therefore, if the apply server with the lowest commit SCN transaction is encountering contention, then the contention results from something other than a dependent transaction. In this case, you can monitor the apply server with the lowest commit SCN transaction to determine the cause of the contention.

The following four wait states are possible for an apply server:

• Not waiting: The apply server is not encountering contention and is not waiting. No action is necessary in this case.

• Waiting for an event that is not related to another session: An example of an event that is not related to another session is a log file sync event, where redo data must be flushed because of a commit or rollback. In these cases, nothing is written to the log initially because such waits are common and are usually transient. If the apply server is waiting for the same event after a certain interval of time, then the apply server writes a message to the alert log and apply process trace file. For example, an apply server AS01 might write a message similar to the following:

  AS01: warning -- apply server 1, sid 26 waiting for event:
  AS01: [log file sync] ...
  

  This output is written to the alert log at intervals until the problem is rectified.

• Waiting for an event that is related to a non apply server session: The apply server writes a message to the alert log and apply process trace file immediately. For example, an apply server AS01 might write a message similar to the following:

  AS01: warning -- apply server 1, sid 10 waiting on user sid 36 for event:
  AS01: [enq: TM - contention] name|mode=544d0003, object #=a078, 
        table/partition=0
  

  This output is written to the alert log at intervals until the problem is rectified.

• Waiting for another apply server session: This state can be caused by interested transaction list (ITL) contention, but it can also be caused by more serious issues, such as an apply handler that obtains conflicting locks. In this case, the apply server that is blocked by another apply server prints only once to the alert log and the trace file for the apply process, and the blocked apply server issues a rollback to the blocking apply server. When the blocking apply server rolls back, another message indicating that the apply server has been rolled back is printed to the log files, and the rolled back transaction is reassigned by the coordinator process for the apply process.

  For example, if apply server 1 of apply process AP01 is blocked by apply server 2 of the same apply process (AP01), then the apply process writes the following messages to the log files:

  AP01: apply server 1 blocked on server 2
  AP01: [enq: TX - row lock contention] name|mode=54580006, usn<<16 | 
        slot=1000e, sequence=1853
  AP01: apply server 2 rolled back
  

  You can determine the total number of times an apply server was rolled back since the apply process last started by querying the TOTAL_ROLLBACKS column in the V$STREAMS_APPLY_COORDINATOR dynamic performance view.

Is the Apply Process Waiting for a Dependent Transaction?

If you set the parallelism parameter for an apply process to a value greater than 1, and you set the commit_serialization parameter of the apply process to full, then the apply process can detect interested transaction list (ITL) contention if there is a transaction that is dependent on another transaction with a higher SCN. ITL contention occurs if the session that created the transaction waited for an ITL slot in a block. This happens when the session wants to lock a row in the block, but one or more other sessions have rows locked in the same block, and there is no free ITL slot in the block.

ITL contention also is possible if the session is waiting due to a shared bitmap index fragment. Bitmap indexes index key values and a range of rowids. Each entry in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK.

When an apply process detects such a dependency, it resolves the ITL contention automatically and records information about it in the alert log and apply process trace file for the database. ITL contention can negatively affect the performance of an apply process because there might not be any progress while it is detecting the deadlock.

To avoid the problem in the future, perform one of the following actions:

• Increase the number of ITLs available. You can do so by changing the INITRANS setting for the table using the ALTER TABLE statement.

• Set the commit_serialization parameter to none for the apply process.

• Set the parallelism apply process parameter to 1 for the apply process.

Is an Apply Server Performing Poorly for Certain Transactions?

If an apply process is not performing well, then the reason might be that one or more apply servers used by the apply process are taking an inordinate amount of time to apply certain transactions. The following query displays information about the transactions being applied by each apply server used by an apply process named strm01_apply:

COLUMN SERVER_ID HEADING 'Apply Server ID' FORMAT 99999999
COLUMN STATE HEADING 'Apply Server State' FORMAT A20
COLUMN APPLIED_MESSAGE_NUMBER HEADING 'Applied Message|Number' FORMAT 99999999
COLUMN MESSAGE_SEQUENCE HEADING 'Message Sequence|Number' FORMAT 99999999

SELECT SERVER_ID, STATE, APPLIED_MESSAGE_NUMBER, MESSAGE_SEQUENCE 
  FROM V$STREAMS_APPLY_SERVER
  WHERE APPLY_NAME = 'STRM01_APPLY'
  ORDER BY SERVER_ID;

If you run this query repeatedly, then over time the apply server state, applied message number, and message sequence number should continue to change for each apply server as it applies transactions. If these values do not change for one or more apply servers, then the apply server might not be performing well. In this case, you should ensure that, for each table to which the apply process applies changes, every key column has an index.

If you have many such tables, then you might need to determine the specific table and DML or DDL operation that is causing an apply server to perform poorly. To do so, run the following query when an apply server is taking an inordinately long time to apply a transaction. In this example, assume that the name of the apply process is strm01_apply and that apply server number two is performing poorly:

COLUMN OPERATION HEADING 'Operation' FORMAT A20
COLUMN OPTIONS HEADING 'Options' FORMAT A20
COLUMN OBJECT_OWNER HEADING 'Object|Owner' FORMAT A10
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A10
COLUMN COST HEADING 'Cost' FORMAT 99999999

SELECT p.OPERATION, p.OPTIONS, p.OBJECT_OWNER, p.OBJECT_NAME, p.COST
  FROM V$SQL_PLAN p, V$SESSION s, V$STREAMS_APPLY_SERVER a
  WHERE a.APPLY_NAME = 'STRM01_APPLY' AND a.SERVER_ID = 2
    AND s.SID = a.SID
    AND p.HASH_VALUE = s.SQL_HASH_VALUE;

This query returns the operation being performed currently by the specified apply server. The query also returns the owner and name of the table on which the operation is being performed and the cost of the operation. Ensure that each key column in this table has an index. If the results show FULL for the COST column, then the operation is causing full table scans, and indexing the table's key columns might solve the problem.

In addition, you can run the following query to determine the specific DML or DDL SQL statement that is causing an apply server to perform poorly, assuming that the name of the apply process is strm01_apply and that apply server number two is performing poorly:

SELECT t.SQL_TEXT
  FROM V$SESSION s, V$SQLTEXT t, V$STREAMS_APPLY_SERVER a
  WHERE a.APPLY_NAME = 'STRM01_APPLY' AND a.SERVER_ID = 2
    AND s.SID = a.SID
    AND s.SQL_ADDRESS = t.ADDRESS
    AND s.SQL_HASH_VALUE = t.HASH_VALUE
    ORDER BY PIECE;

This query returns the SQL statement being run currently by the specified apply server. The statement includes the name of the table to which the transaction is being applied. Ensure that each key column in this table has an index.

If the SQL statement returned by the previous query is less than one thousand characters long, then you can run the following simplified query instead:

SELECT t.SQL_TEXT
  FROM V$SESSION s, V$SQLAREA t, V$STREAMS_APPLY_SERVER a
  WHERE a.APPLY_NAME = 'STRM01_APPLY' AND a.SERVER_ID = 2
    AND s.SID = a.SID
    AND s.SQL_ADDRESS = t.ADDRESS
    AND s.SQL_HASH_VALUE = t.HASH_VALUE;

Are There Any Apply Errors in the Error Queue?

When an apply process cannot apply a message, it moves the message and all of the other messages in the same transaction into the error queue. You should check for apply errors periodically to see if there are any transactions that could not be applied. You can check for apply errors by querying the DBA_APPLY_ERROR data dictionary view.

new_lcr.ADD_COLUMN('OLD','LANGUAGE',NULL);

new_lcr.ADD_COLUMN('OLD','LANGUAGE',ANYDATA.ConvertVarchar2(NULL));