Informatica BDM 10.4.1 – Oracle19c POWEREXCHANGE(PWX) CDC

Abstract:
This blog explains the concept of PWX, Oracle CDC 10.4.1 (latest, at the time of writing this blog) and discusses about the benefits and Challenges of PWX with CDC.

High level Architecture on Informatica BDM with Oracle PWXCDC:

Architecture for Oracle PWXCDC:

**The Explanation for the CDC Publisher is given in separate blog.

Overview on PWX with Change Data Capture:
The PWX CDC option recognizes business events, such as customer creation or order shipment data, by capturing the database inserts, updates, and deletes these events as soon as they occur on their application database systems. This captured stream of database activity can be delivered to multiple targets in real time, without intermediate queues or staging tables.
PWX Listener:
The PWX Listener manages to capture registrations and extraction maps for all CDC data sources. The PWX Listener maintains this information in the following files: CCT file for capture registrations.
PWX Logger:
The PWX Logger writes changed data to its redo log files. PWX can then extract changed data from the PWX Logger log files rather than from the database log files.

PWX Express CDC for Oracle19c:
PWX Express requires some configuration changes to DBMOVER.CFG and PWXCCL.CFG, and requires a new configuration file, PWXORAD.CFG.
dbmover.cfg :
LISTENER=(EEEE,TCPIP,2480)
NODE=(local,TCPIP,127.0.0.1,2480)
NODE=(EEEE,TCPIP, 127.0.0.1,2480)
NODE=(default,TCPIP,x,2480)
/* SVC & CMD node for Listener (dtllst)
SVCNODE=(EEEE,8500)
CMDNODE=(EEEE,LISTENER,127.0.0.1,8500)
/* SVC & CMD node for Logger (pwxccl)
SVCNODE=(pwxccl_eeee,8501)
CMDNODE=(pwxccl_eeee,CONDENSE,127.0.0.1, 8501)
/**********************/* Default Stuff*****************************************
APPBUFSIZE=8388608
APPBUFSIZEDYN=Y
COLON=:
CONSOLE_TRACE=N
DECPOINT=.
DEFAULTCHAR=*
DEFAULTDATE=19800101
MAXTASKS=30
MSGPREFIX=PWX
NEGSIGN=-
PIPE=|
POLLTIME=1000
PRE861_COMPAT=N
EXT_CP_SUPPT=Y
/*PATH */
CAPT_PATH=/PWX_HOME/CAPT_PATH_DIR/
CAPT_XTRA=/PWX_HOME/CAPT_XTRA_DIR/
LOGPATH=/PWX_HOME/LOGS/
DMX_DIR=/PWX_HOME/DMX_DIR/
/*————-*/
ORACLE_CAPTURE_TYPE=D
ORACLEID= (BBBB, ORACLESID, TNSENTRY, TNSENTRY) /*(TNSENTRY will be shared by the DBA team on request basis) */
CAPI_CONNECTION= (NAME=CAPXDDDD, TYPE= (CAPX, DFLTINST=BBBB))
CAPI_CONNECTION= (NAME=ORADDDDD, TYPE= (ORAD, ORACOLL=BBBBBBBB,PARMFILE=pwxorad.cfg))
/*————–*/
TRACING= (PFX=AltLogs, FLUSH=10, FILENUM=99, VIEW=Y, APPEND=Y, SIZE=100, RECLEN=255)

EEEE – the name of the listener (will also use this for commands)
BBBB – whatever you want to call the ORACLEID
CCCC – the name of the logger (will also use this for commands)
ORACLESID – the Oracle SID / Service Name for the source
TNSENTRY – the TNSNAMES.ORA entry for the Oracle source
PWX_HOME – the actual directory path for $PWX_HOME (PWX doesn’t expand shell variables in this file)
CAPT_PATH_DIR – the path where you want the CCT, CDCT, CDEP. you may use this for “CAPTURE” in pwxccl.cfg
CAPT_XTRA_DIR – the path where you want extraction maps
CAPXDDDD – the name of the CAPX CAPI_CONNECTION
ORADDDDD – the name of the ORAD CAPI_CONNECTION

 PWXCCL.CFG file for PWX Express CDC for Oracle:
CONDENSENAME=pwxccl_eeee
DB_TYPE=ORA
DBID=BBBB
CAPTURE_NODE_UID=XXXX
CAPTURE_NODE_EPWD=YYYY123
PROMPT=N
SIGNALLING=Y
VERBOSE=N
STATS=(MONITOR,15)
EXT_CAPT_MASK=PWX_HOME/CAPTURE/CondenseO
COND_CDCT_RET_P=14
LOGGER_DELETES_EXPIRED_CDCT_RECORDS=Y
/* 0 = continuous, 1 = Stop at end-of-log (batch)
COLL_END_LOG=0
/* Number of minutes to wait between CAPI read cycles in seconds
NO_DATA_WAIT=0
/* Number of seconds to wait at the end-of-log for more change data
NO_DATA_WAIT2=10
/* Number of seconds before flushing, or writing, data to the current log file on disk
/* -1 = No flush, 0 = flush every record, 1 to N flush every N seconds
FILE_FLUSH_VAL=10
FILE_SWITCH_CRIT=M
FILE_SWITCH_VAL=15
CAPT_IMAGE=BA
/*SEQUENCE_TOKEN=00
/*RESTART_TOKEN=00

 PWXORAD Oracle Express CDC Configuration File:
DATABASE
CONNECT_STRING= SSSS
/* PASSWORD=YYYY
EPWD=YYYY123
USERID=XXXX
TDEWALLETDIR=/ PWX_HOME/wallet/wwww /*(will be placed by the DBA team on request basis)*/
TDEWALLETEPWD=9991BC     /*(will be shared by the DBA team on request basis)*/
/* TDEWALLETEPWD=1999CB;
DICTIONARY
MODE=STATIC
SOURCE=ONLINE
EXCEPTIONS=FAIL;
OPTIONS
MEMOPS=5120
SPILLMAX=10240
RSTRADV=60
AGEOUTPERIOD=60
LOGARCHIVEWAIT=120
TIME_STAMP_MODE=COMMITTIME;
READER MODE=ACTIVE
READBUFFSIZE=262144
STATUSCHECKINTERVAL=12000
ASM_ASSYSASM=Y
ASM_CONNECT_STRING=asm_connectstring
ASM_USERID=asm_login   /*(will be shared by the DBA team on request basis) */
ASM_EPWD=asm_password_encrypted /*(will be shared by the DBA team on request basis) * /;
ADVANCED
CHECKSUMVERIFY=CURRENT
READAHEAD=N;
/*ASM_EPWD=
RAC MEMBERS= 3;
/* END
ASMSTAGING
STAGINGDIR=”/PWX_HOME/asm/staging/xxxx”
TARGETSIZE=50;

Listener and Logger Steps:
Step1.Start the listener by going to the PWX directory PWX_HOME and execute the command in below format:
nohup dtllst EEEE CONFIG=/PWX_HOME/eeee/dbmover_eeee.cfg >
/PWX_HOME/logs/eeee/start_listener.log 2>&1 &
Execute below command will display the listener running status.
ps -ef|grep dtllst|grep eeee

Step2.Start the logger by going to the PWX directory PWX_HOME and execute the command in below format:
nohup pwxccl coldstart=n cs=/PWX_HOME/eeee/pwxccl_eeee.cfg
config=/PWX_HOME/eeee/dbmover_eeee.cfg > / PWX_HOME /logs/eeee/start_logger_eeee.log 2>&1 &

Note:
Coldstart=n – this will bring the data from the last processed condense file
Coldstart=y – this will bring all data from the beginning (chances to have duplicate data)

Execute the below command to check the logger status.
pwxcmd displayall -sv pwxccl_eeee
Execute below command will display the logger running status.
ps -ef|grep pwxccl|grep eeee

Note: Bring up the listener in server then make an entry in the dbmover.cfg file where installed in navigator and then execute the below steps.

Informatica PWX 10.4.1 tool registration steps:
Initially an entry should be made in dbmover configuration file before proceeding to the registration
Step 1:  Go to the Informatica PWX 10.4.1 tool. Click the PWX Navigator then page will be opened as in the below screenshot.

Step 2: Open the Data Capture tab à Registration group.

Step 3: Right click Registration group then add the Add Registration Group by entering the valid user id and password and click finish.

Step 4: Once authenticated, right click on Add Capture Registration and give the details of Name, Schema and Table.

Note: The given topic name should be in 8 characters.
Fill the details and click next.
Step 5:  Double click on the schema name.

Step 6: Click on ‘Select All Columns’ and click next

Step 7: Change the status to Active and click Finish.

Step 8: Save the Supplemental log in a specific directory in our local.
Step 9: Execute log in EEEE database.

Row Testing:
One way to test your extraction mapping is by doing row test. This is done from the Extraction group.

DTL__CAPXUSER: The username used for the oracle should be populated in this column.
Challenges with PWX CDC :

• BLOB/CLOB columns are not supported.
• When there is a mass update in source, there will be huge CPU consumption and takes a longer time to read the condense file.
• If any new/modify/drop column action is carried out in source, then the table must be registered again, supplemental logs to be re-executed/shared to the DBA team, logger must be brought down and then brought up.

PWX with CDC – Benefits:
Below are the benefits by using the PWX with CDC.

• Access and deliver the data in “right time”.
• For audit and other purposes of keeping track of the deleted record CDC can be great help as the deleted record in source will be captured in dtl__capxaction=D in PWXCDC.

Conclusion:
The PWX Change Data Capture (CDC) Option can satisfy our business requirements for up-to-the-minute data. PWX Change Data Capture (CDC) Option is available for all popular enterprise      database systems, this Option is essential whenever you need timelier access to data. When combined with real-time data integration platform, event-driven data can be accessed, transformed, and cleansed continuously and used to drive business results in any enterprise, large or small .Please feel free to reach Cittabase for more information. Visit our blogs for more topics  on Informatica.

References:
https://knowledge.informatica.com/