AWS-DMS TASK ISSUE,SOLUTION

Problem : DMS task was failing due to the error:

Error  '2021-06-09T16:06:17 [TARGET_LOAD     ]W:  Load command output: psql: /usr/lib64/libcom_err.so.2: no version information available (required by /rdsdbbin/awsdms/lib/libgssapi_krb5.so.2)psql: /usr/lib64/libcom_err.so.2: no version information available (required by /rdsdbbin/awsdms/lib/libkrb5.so.3) ERROR:  record "aws$rec" has no field "nextval('mmmp.tblaomp_job_id_seq')"

CONTEXT:  SQL statement "SELECT ROW (aws$rec."nextval('miptd.tblaomp_job_id_seq')", new.job_number, new.prospect_number, new.a_address, new.a_postcode, new.a_contact_no, new.a_contact_name, new.b_address, new.b_postcode, new.b_contact_no, new.b_contact_name, new.bandwidth, new.product, new.customer_agreed_date, new.latest_order_date, new.job_originated_date, new.phased_planning_date, new.network_availability_date, new.notes, new.job_status)"PL/pgSQL function miptd."aomp_job_id_seq_trigger$tblaomp_job"() line 8 at assignment

COPY tblaomp_job, line 1: "29304,154,"0496/01","Hemsworth Arts  & Community College , ","WF9 4ND",attNULL,attNULL,"Hemsworth Ar..."  (csv_target.c:1018)'

Solutions This indicates that DMS encountered an error while writing to the table due to a user-defined trigger firing.

 To workaround this issue, we added the extra connection attribute after afterConnectScript=SET session_replication_role='replica';

[1] to your Postgres target endpoint, in order for sessions created by DMS to write to the database to bypass these triggers.  

(2) Other parameter Option to set replica once data migration over again set orign 

Error : ORA-01555 (Snapshot too old error messages) 

Solution  :  DMS Commit rate

https://aws.amazon.com/blogs/database/aws-dms-key-troubleshooting-metrics-and-performance-enhancers/

The commit rate parameter indicates the total number of records that can be transferred together at the target instance during a full load operation. By default, the value is set to 10,000 records. Increasing this parameter (up to 50,000) often leads to better performance for full load and helps in avoiding the ORA-01555 (Snapshot too old error messages). A greater value for the commit rate will lead to a faster data load, so it will take lesser time for source oracle instance to hold the consistent undo snapshot. However, you should be mindful of record sizes and especially if huge LOBs are involved, because they may lead to the replication instance running out of resources, leading to sub-optimal performance.

We can change this parameter in the full load task settings:

"FullLoadSettings": {

"CreatePkAfterFullLoad": false,

"StopTaskCachedChangesApplied": false,

"StopTaskCachedChangesNotApplied": false,

"MaxFullLoadSubTasks": "8",

"TransactionConsistencyTimeout": 600,

"CommitRate": "10000",

}

Solution  2 : There are different approaches to solve this issue. The idea is to either:

• Reduce the extract query runtime by optimizing the query in the source DB.

• Make sure the source is retaining the old image of data longer than the extract query runtime

https://aws.amazon.com/blogs/database/how-to-solve-some-common-challenges-faced-while-migrating-from-oracle-to-postgresql/

Solution  3 :To resolve this issue, either increase the parameter of UNDO_RETENTION if you are in AUM mode or use larger rollback segments. The latter solution will allow your rollback data for completed transactions to be kept longer.