wait for shrink lock2 event while a dms task is running against Oracle

Question

Hello
I have a high wait event related to 'shrink lock2', while migrating a table with a DMS task.
It truncates the table and fill it. After a few minutes the wait events are showing in the Performance Insight monitor and the row count is very slow.
The source of the task is an on premise Oracle instance, and the target a Rds Oracle version 19.
No lob/long columns. Is a migrate task, no ongoing changes.

On the Oracle side, I know that wiat if I try to alter the table to shrink space. 
Can I avoid that automatic shrink on the RDS with some endpoint or task settings? I don't find good information about it.
Thanks

rePost-User-1879170 · Accepted Answer

Hello Team,

Greetings!

Thank you for reaching out with this issue. I'd like to provide some clarity on the error you're encountering: "wait for shrink lock2 event while a DMS task is running against Oracle."

This error typically arises in Oracle database environments when certain operations are attempted while a Data Migration Service (DMS) task is active. Here's an overview of the root cause:

Root Cause:

The error is primarily due to a locking conflict between two concurrent operations:

1. Shrink Operation: This is likely an attempt to shrink a tablespace or a segment within the Oracle database to reclaim unused space.
2. Active DMS Task: AWS Database Migration Service (DMS) is concurrently performing data migration or replication tasks on the Oracle database.

The shrink operation requires exclusive access to specific database objects to proceed. However, while DMS is actively accessing these objects, a lock contention occurs, preventing the shrink operation from acquiring the necessary "shrink lock2" due to conflicting locks held by the DMS task.

Oracle's locking mechanism is designed to avoid data inconsistencies by blocking simultaneous modifications on the same objects. In this instance, it's ensuring data integrity by preventing the shrink operation from proceeding while the DMS task is accessing the data.

Recommended Actions:

To resolve this locking issue, consider the following options:

-- Wait for the DMS Task to Complete: Allow the current DMS task to finish, then retry the shrink operation.
-- Pause and Resume the DMS Task: Temporarily pause or stop the DMS task, perform the shrink operation, and then resume the DMS task once completed.
-- Schedule During a Maintenance Window: Plan the shrink operation during a maintenance window or a time when DMS tasks are not running to avoid lock contention.

These steps should help resolve the lock contention issue. Please don't hesitate to reach out if you need further assistance with the process.

Reference
------------
[*] https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-space-for-schema-objects.html#GUID-17A2FDF6-937E-4E3A-B9A6-3830FAD32B40
[*] https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html

re:Post Agent · Answer

The "wait for shrink lock2" event you're experiencing during your AWS DMS task from an on-premise Oracle instance to RDS Oracle is likely related to automatic space management operations in the target RDS Oracle database.

While AWS DMS is designed to efficiently migrate data, it can sometimes trigger automatic space management operations on the target database, especially when truncating and refilling tables. This can lead to the "shrink lock2" wait events you're observing, which are typically associated with space reclamation operations.

Unfortunately, there isn't a direct setting in AWS DMS to prevent this automatic shrinking behavior on the RDS Oracle target. However, you can try a few approaches to potentially mitigate this issue:

1. Adjust the target table preparation mode: In your DMS task settings, you can set the "Target table preparation mode" to "Do nothing" instead of "Truncate". This might help avoid triggering the automatic shrink operations. However, be aware that this means you'll need to manually manage the target table preparation.

2. Optimize task settings: Review and adjust your task settings, particularly those related to LOB handling and error policies. For example, if you're using limited LOB mode, ensure the size limit is appropriate for your data to avoid truncation issues.

3. Monitor and tune RDS Oracle: While you can't directly control the automatic space management in RDS Oracle, you can monitor the database's performance and potentially adjust some parameters within the allowed RDS Oracle parameter groups to optimize space management.

4. Consider batch processing: If possible, try migrating data in smaller batches to reduce the impact of large-scale truncate and refill operations.

5. Evaluate table partitioning: If your data model allows, consider using table partitioning on the target RDS Oracle. This can sometimes help manage space more efficiently during migrations.

If these approaches don't sufficiently address the issue, you may need to engage AWS Support for more specific guidance tailored to your migration scenario. They might be able to provide additional insights or potential workarounds based on the specifics of your DMS task and RDS Oracle configuration.

Remember, thoroughly testing your migration process in a non-production environment is crucial to identify and address such issues before attempting the final migration.
      
**Sources**  
[Reloading tables during a task - AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.ReloadTables.html)  
[Insights into AWS DMS resiliency and recovery scenarios with mitigations – Part 2 | AWS Database Blog](https://aws.amazon.com/blogs/database/insights-into-aws-dms-resiliency-and-recovery-scenarios-with-mitigations-part-2/)  
[Step 7: Run the AWS DMS Task - Database Migration Guide](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdssqlserver2s3datalake.steps.runtask.html)

wait for shrink lock2 event while a dms task is running against Oracle

Reference

Add your answer

Relevant content