Errors - DMS Binary Reader Related With RDS for Oracle PDB

0

I would really appreciate any help with my problems. My DMS replication tasks failed with multiple errors. I will try to make my post short and clear. Here is the information that I think may help with diagnosis.

  1. Replication Instance version: 3.5.3
  2. Source DB: RDS for Oracle PDB 19.0.0

3. Errors/Info from CloudWatch log:

I:  Oracle source database version is 19.0.0.0.0  (oracle_endpoint_conn.c:366)
I:  Oracle Client version: 19.18.0.0.0  (oracle_endpoint_conn.c:379)
I:  PDB name 'HPMSIMPL', container id '3'  (oracle_endpoint_imp.c:1583)
E:  ORA-20900: Directory DATA_PUMP_DIR_PDB does not exist or no privileges. ORA-06512: at "SYS.RDS_SYS_UTIL", line 361 ORA-06512: at "SYS.RDS_SYS_UTIL", line 1214 ORA-06512: at "SYS.RDS_SYS_UTIL", line 1251 ORA-06512: at "RDSADMIN.RDSADMIN_UTIL", line 170 ORA-06512: at line 1  [1022307]  (oracle_endpoint_utils.c:3325)
I:  Database is not AWS RDS Oracle instance  (oracle_endpoint_conn.c:584)
I:  Checking create directory permissions by creating or replacing directory awsdms_dir_test  (oracle_endpoint_conn.c:593)
W:  Oracle error code is '4088' ORA-04088: error during execution of trigger 'RDSADMIN.RDS_DDL_TRIGGER2' ORA-00604: error occurred at recursive SQL level 1 ORA-20900: Invalid path used for directory: awsdms_dir_test ORA-06512: at "RDSADMIN.RDSADMIN_TRIGGER_UTIL", line 705 ORA-06512: at line 1 ORA-06512: at line 12   (oracle_endpoint_utils.c:3285)
I:  Dropping directory awsdms_dir_test  (oracle_endpoint_conn.c:600)
W:  Oracle error code is '1031' ORA-01031: insufficient privileges   (oracle_endpoint_utils.c:3285)
I:  Oracle compatibility version is 19.0.0  (oracle_endpoint_conn.c:93)
... ...

I:  Online Redo log with the sequence 1 was not found in V$LOG, retry up to 5 minutes, thread 1  (oradcdc_thread.c:570)
/* many occurrences of above */
... ...
D:  Stop signal received, retry once more in debug mode  (oracdc_merger.c:1509)	
T:  Get next log event from thread 1  (oradcdc_oralog.c:555)	
D:  Set start scn, thread 1  (oradcdc_thread.c:2711)	
D:  Going to prepare the statement 'select max(sequence#),max(resetlogs_id) from v$archived_log where thread# = 1 and resetlogs_id = (select max(resetlogs_id) from v$database_incarnation where resetlogs_change# < 780658169031 and STATUS <> 'ORPHAN' ) and dest_id in (1)'  (oradcdc_thread.c:126)	
D:  New max archived sequence for RLID '1158610777' is '38594', thread '1'  (oradcdc_thread.c:414)	
T:  Going to retrieve archived REDO log with sequence '38593', thread '1'  (oradcdc_thread.c:859)	
T:  Use a prepared statement to access v$archived_log, thread 1  (oradcdc_thread.c:945)	
D:  Going to open Redo Log with original name '/rdsdbdata/db/cdb/RDSCDB_A/arch/redolog-38593-1-1158610777.arc', thread id '1'  (oradcdc_redo.c:580)	
D:  Going to execute statement 'select directory_name from all_directories where directory_path = '/rdsdbdata/db/cdb/RDSCDB_A/arch' and (directory_name = 'DMSREP_395EDD40SEL2WRNDNTAILQU' or 'DMSREP_' != substr(directory_name,1,7) )'  (oradcdc_bfilectx.c:116)	
D:  The directory does not exist  (oradcdc_bfilectx.c:125)	
D:  Going to execute statement 'create or replace directory "DMSREP_395EDD40SEL2WRNDNTAILQU" as '/rdsdbdata/db/cdb/RDSCDB_A/arch''  (oradcdc_bfilectx.c:159)	
D:  OCI error 'ORA-04088: error during execution of trigger 'RDSADMIN.RDS_DDL_TRIGGER2' ORA-00604: error occurred at recursive SQL level 1 ORA-20900: Invalid path used for directory: /rdsdbdata/db/cdb/RDSCDB_A/arch ORA-06512: at "RDSADMIN.RDSADMIN_TRIGGER_UTIL", line 705 ORA-06512: at line 1 ORA-06512: at line 12' [1022307]  (oradcdc_bfilectx.c:164)	
D:  Cannot create Oracle directory name 'DMSREP_395EDD40SEL2WRNDNTAILQU' with path '/rdsdbdata/db/cdb/RDSCDB_A/arch' [-1]  (oradcdc_bfilectx.c:165)	
D:  Failed to open Redo log '/rdsdbdata/db/cdb/RDSCDB_A/arch/redolog-38593-1-1158610777.arc', retry till Redo log with this sequence could be opened successfully  (oradcdc_redo.c:716)	
T:  OCI call returned OCI_NO_DATA (BinaryReader/oradcdc_thread.c - 986)  (oradcdc_redoutil.c:300)	
T:  Going to retrieve online REDO log with sequence '38593', thread '1'  (oradcdc_thread.c:859)	
T:  Use a prepared statement to access v$log, thread 1  (oradcdc_thread.c:945)	
D:  Going to open Redo Log with original name '/rdsdbdata/db/RDSCDB_A/onlinelog/o1_mf_4_ltm1zln5_.log', thread id '1'  (oradcdc_redo.c:580)	
D:  Going to execute statement 'select directory_name from all_directories where directory_path = '/rdsdbdata/db/RDSCDB_A/onlinelog' and (directory_name = 'DMSREP_395EDD40SEL2WRNDNTAILQU' or 'DMSREP_' != substr(directory_name,1,7) )'  (oradcdc_bfilectx.c:116)	
D:  The directory does not exist  (oradcdc_bfilectx.c:125)	
D:  Going to execute statement 'create or replace directory "DMSREP_395EDD40SEL2WRNDNTAILQU" as '/rdsdbdata/db/RDSCDB_A/onlinelog''  (oradcdc_bfilectx.c:159)	
D:  OCI error 'ORA-04088: error during execution of trigger 'RDSADMIN.RDS_DDL_TRIGGER2' ORA-00604: error occurred at recursive SQL level 1 ORA-20900: Invalid path used for directory: /rdsdbdata/db/RDSCDB_A/onlinelog ORA-06512: at "RDSADMIN.RDSADMIN_TRIGGER_UTIL", line 705 ORA-06512: at line 1 ORA-06512: at line 12' [1022307]  (oradcdc_bfilectx.c:164)	
D:  Cannot create Oracle directory name 'DMSREP_395EDD40SEL2WRNDNTAILQU' with path '/rdsdbdata/db/RDSCDB_A/onlinelog' [-1]  (oradcdc_bfilectx.c:165)	
D:  Failed to open Redo log '/rdsdbdata/db/RDSCDB_A/onlinelog/o1_mf_4_ltm1zln5_.log', retry till Redo log with this sequence could be opened successfully  (oradcdc_redo.c:704)	

I have the following questions.

  1. Why the log reads "Database is not AWS RDS Oracle instance"? Is it because I am using RDS for Oracle container database PDB as the source? Additionally, DMS user's guide says if the source DB is a self-managed oracle DB, DMS needs the permission to create directories, but it does NOT say so for RDS. I guess that's why DMS tests the permission to create directories. Where am I missing?

  2. My DBA created two directories ONLINELOG_DIR and ARCHIVELOG_DIR. Apparently the two directories are created in the PDB. When I use rdsadmin.rds_file_util.listdir to list logs in the directory, I don't see log files as listed in V$LOG. Could this be the reason of "Online Redo log with the sequence 1 was not found "? Are the directories supposed to be created in the CDB root? If so, can DMS access them while connected to the PDB?

Thank you in advance for any help.

asked 6 months ago526 views
4 Answers
0

Hello,

DMS and Oracle PDB should work, as long as it isn’t the multi-tenant container root database (CDB$ROOT) as mentioned in our docs. [1]

I would recommend opening a Support Case with your details so that one of our engineers can review the setup and verify with you the prerequisites were met. We also have additional troubleshooting tools to verify if there is anything missing.

References: [1] Limitations: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.Limitations

AWS
SUPPORT ENGINEER
answered 5 months ago
  • Kyle, Thank you for responding my my questions. I did create a support case with AWS, but it's not helpful so far.

0
  1. You can ignore the warnings related to "Database is not AWS RDS Oracle instance". For RDS oracle, DMS will create all directories automatically If DMS user has required permissions mentioned in below link. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.Amazon-Managed.Privileges

  2. As per the Error message, Cannot create Oracle directory name 'DMSREP_395EDD40SEL2WRNDNTAILQU' with path '/rdsdbdata/db/cdb/RDSCDB_A/arch'. We might missing the privileges mentioned in our document. Kindly make sure that all the privileges should be granted to DMS user. For your Container database only "binary reader" mode is supported and don't use Logminer.

Please configure the below steps mentioned in below link

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.Amazon-Managed.CDC

Still if you are having issues please log a Support case.

AWS
SUPPORT ENGINEER
answered 5 months ago
  • Thank you for your response to my questions! Please review my comments below.

    To let DMS to create oracle directories, the DMS user would need "CREATE ANY DIRECTORY" system privilege. According to the document from your link, this is only required for self-managed to oracle database. In fact, in RDS, even the master user is not allowed to grant this privilege to anyone. This is an designed limitation of RDS.

0

In RDS oracle, we are creating these directories manually using below commands

exec rdsadmin.rdsadmin_master_util.create_archivelog_dir; exec rdsadmin.rdsadmin_master_util.create_onlinelog_dir; GRANT READ ON DIRECTORY ONLINELOG_DIR TO db_user; GRANT READ ON DIRECTORY ARCHIVELOG_DIR TO db_user;

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.Amazon-Managed.CDC

AWS
SUPPORT ENGINEER
answered 5 months ago
  • Thank you for your response. Yes, we had created the two directories and granted permissions as you suggested, but somehow DMS still cannot access the online redo logs. As stated in my original post, when I use rdsadmin.rds_file_util.listdir to list logs in the directory, I don't see log files as listed in V$LOG. This could be the reason DMS cannot find the logs. This seems to be unique for RDS Oracle CDB, which only became available late last year and has many limitations. When I create the same ONLINELOG_DIR in a non-CDB RDS, I can see log files in the directory. I really curious if there is a successful use case of RDS for Oracle CDB as DMS source.

0

The issues I have seem to be unique for RDS Oracle CDB, which only became available late last year and has many limitations. I used "exec rdsadmin.rdsadmin_master_util.create_onlinelog_dir" to create ONLINELOG_DIR, but don't see any log files but a file directory in the ONLINELOG_DIR. When I create the same ONLINELOG_DIR in a non-CDB RDS, I can see log files in the directory.

I really curious if there is a successful use case of RDS for Oracle CDB as DMS source using Binary Reader. I do have a few DMS jobs running successfully with RDS for Oracle non-CDB as source.

answered 5 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions