Solved

CDC problem with 21C

  • 7 February 2024
  • 8 replies
  • 114 views
Emrah
Userlevel 1
Badge
  • Emrah
  • Participating Frequently
  • 6 replies

Dear All,

i am trying to create 2 pipelines from Oracle to Postgres.the first one is initial Oracle to Postgres, it worked very well, and schema conversion tool migrated 2 example tables to postgres.

 

but i have an issue with Oracle CDC.

i have logminer user on oracle which is striim and i have table owner which is scale_user and i have 2 tables scale_data and scale_data2.

i am not using container and PDB on 21C, striim and scale_user are common user.

i am receieving an error like below when i insert a data to the tables after commit.i see that reader is looking for tables like CDB$ROOT.TEST.SCALE_DATA but i am not using container so how can resolve the issue.There was no problem with initial load, the problem is related with CD process.

 

Thanks in advance.

 

[{ "_id" : null, "timeStamp" : 1707166962232, "originTimeStamp" : 1707166964000, "key" : null, "sourceUUID" : { "uuidstring" : "01eec465-3101-b301-b854-42010a9c000a" }, "data" : [ "862412", "1", "1", "460" ], "metadata" : { "RbaSqn" : "502", "AuditSessionId" : "4643512", "TableSpace" : "TMS1034", "CURRENTSCN" : "39715088", "SQLRedoLength" : 93, "BytesProcessed" : null, "OperationCode" : 1, "ParentTxnID" : "1.11.25618", "SRC_CON_NAME" : "CDB$ROOT", "RbaByte" : 16, "SessionInfo" : "login_username=TEST client_info= OS_username=oracle Machine_name=oracle-poc OS_terminal=pts/1 OS_process_id=12496 OS_program_name=sqlplus@oracle-poc (TNS V1-V3)", "RecordSetID" : " 0x0001f6.00037416.0010 ", "DBCommitTimestamp" : 1707166964000, "COMMITSCN" : 39715189, "SEQUENCE" : "1", "Rollback" : "0", "STARTSCN" : "39715088", "Status" : "0", "SegmentName" : "SCALE_DATA", "OperationName" : "INSERT", "TimeStamp" : 1707166961000, "RbaBlk" : 226326, "SSN" : "294", "TxnUserID" : "TEST", "SegmentType" : "TABLE", "TableName" : "CDB$ROOT.TEST.SCALE_DATA", "Serial" : "48658", "TxnID" : "1.11.25618", "ThreadID" : "1", "COMMIT_TIMESTAMP" : 1707166964000, "OperationType" : "DML", "ROWID" : "AAAfDzAAEAAF7kOAEm", "DBTimeStamp" : 1707166961000, "TransactionName" : null, "SCN" : "39715088", "Session" : "413", "OPERATION_TS" : 1707166961000 }, "userdata" : null, "before" : null, "dataPresenceBitMap" : "Dw==", "beforePresenceBitMap" : "AA==", "typeUUID" : { "uuidstring" : "01eec469-e70d-b731-b854-42010a9c000a" } }]

 

 

 

 

icon

Best answer by DeepakBang 7 February 2024, 17:14

View original

8 replies

Emrah
Userlevel 1
Badge

sorry table owner is test

Emrah
Userlevel 1
Badge

i exported my pipeline to tql file so which is like below as your reference

 


CREATE APPLICATION emrah_cdc USE EXCEPTIONSTORE TTL : '7d' ;

CREATE FLOW emrah_cdc_SourceFlow;

CREATE SOURCE emrah_cdc_OracleSource USING Global.OracleReader ( 
  Password: 'DaOAJTW+rsG/7Bh9KwaK7w==', 
  Compression: false, 
  Tables: '"TEST"."SCALE_DATA";"TEST"."SCALE_DATA2"', 
  connectionRetryPolicy: 'timeOut=30, retryInterval=30, maxRetries=3', 
  Password_encrypted: 'true', 
  SupportPDB: false, 
  QuiesceMarkerTable: 'QUIESCEMARKER', 
  FetchSize: 1000, 
  DictionaryMode: 'OnlineCatalog', 
  QueueSize: 2048, 
  CommittedTransactions: true, 
  Username: 'striim', 
  TransactionBufferSpilloverSize: '1MB', 
  TransactionBufferType: 'Memory', 
  ConnectionURL: 'jdbc:oracle:thin:@//10.156.0.5:1521:1521/TMSNEW', 
  FilterTransactionBoundaries: true, 
  SendBeforeImage: true, 
  DatabaseRole: 'Primary' ) 
OUTPUT TO emrah_cdc_OutputStream;

END FLOW emrah_cdc_SourceFlow;

CREATE TARGET PostgreSQL_emrah_cdc_Target USING Global.DatabaseWriter ( 
  ConnectionRetryPolicy: 'retryInterval=30, maxRetries=3', 
  Tables: '"TEST"."%",TEST.%', 
  ParallelThreads: '2', 
  Password: 'h4sJIDx9CklrNjRl4Rw9rQ==', 
  CheckPointTable: 'CHKPOINT', 
  Password_encrypted: 'true', 
  CDDLAction: 'Process', 
  CommitPolicy: 'EventCount:1000,Interval:60', 
  StatementCacheSize: '50', 
  DatabaseProviderType: 'Postgres', 
  BatchPolicy: 'EventCount:1000,Interval:60', 
  ConnectionURL: 'jdbc:postgresql://10.30.21.2:5432/tmsnew', 
  PreserveSourceTransactionBoundary: 'false', 
  Username: 'postgres', 
  IgnorableExceptionCode: 'DUPLICATE_ROW_EXISTS, NO_OP_UPDATE, NO_OP_DELETE' ) 
INPUT FROM emrah_cdc_OutputStream;


END APPLICATION emrah_cdc;

AKG

Hello @Emrah 

can you please share output of the following queries - 

SELECT CDB FROM V$DATABASE;

SELECT NAME FROM V$CONTAINERS;

I just wanted to check if you are using a multitenant environment for capturing with OracleReader. If it is a multitenant environment, you should use three parts in the tables parameter. For example - ... - <container>.<schema>.<tablename>

-best

AKG

DeepakBang
Badge +2

Hello

Thank you for reporting your issue.
Few points i observed 

1. from the tql file I see that connection URL has two ports mentioned 1521. 
Can you have it in the following format 

jdbc:oracle:thin:@//<container_db_fqdn>:1521/<pdb_service_name>



2. The database user must have access to root container , i think you are using the database user of a PDB. Please follow these steps to create a common user and use this in our application 
 

create role c##striim_privs;grant create session,execute_catalog_role,select any transaction,select any dictionary,logminingto c##striim_privs;grant select on SYSTEM.LOGMNR_COL$ to c##striim_privs;grant select on SYSTEM.LOGMNR_OBJ$ to c##striim_privs;grant select on SYSTEM.LOGMNR_USER$ to c##striim_privs;grant select on SYSTEM.LOGMNR_UID$ to c##striim_privs;create user c##striim identified by ******* container=all;grant c##striim_privs to c##striim container=all;alter user c##striim set container_data = (cdb$root, <PDB name>) container=current;

 Reference - https://www.striim.com/docs/en/oracle-database-cdc.html

Emrah
Userlevel 1
Badge

Dear All,

 

striim and test users are common user, these are not part of any PDB and i am not using any PDB.

here is the output of commands.

 

SELECT CDB FROM V$DATABASE;

YES

SELECT NAME FROM V$CONTAINERS;

CDB$ROOT

 

[oracle@oracle-poc ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 21.0.0.0.0 - Production on Wed Feb 7 16:56:31 2024
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> conn striim/password;
Connected.
SQL> 

Emrah
Userlevel 1
Badge

Summary of the problem :

Target table CDB$ROOT.SCALE_USER.SCALE_DATA doesn't exist

Emrah
Userlevel 1
Badge

i changed the user from test to scale_user but the issue is same and i fixed 2 1521 ports on the connection URL but the result is same

Emrah
Userlevel 1
Badge

Guys, i just changed the tables section on postgresql writer like this "CDB$ROOT.SCALE_USER"."%",SCALE_USER.%

 

and i did not get any error so it worked.

 

Thanks fo ryour support.

 

Regards

Reply


Terms & ConditionsCookie settings