BEP, Banner Events Publisher, can be used to capture the data change on the specific tables, like ODI
There are a lot of pre-defined events for the Ellucian delivered tables

Today, I will show you how to use BEP for the user-defined tables.

First, lets create a test table, two columns {TABLE_NAME}_VPDI_CODE and {TABLE_NAME}_GUID are mandatory, because the trigger the BEP generated uses these two columns. We don’t have to fill them.

create table baninst1.bep_test_1(
id number,
name varchar2(30),
gender char(1),
comments varchar2(3000),
bep_test_1_VPDI_CODE varchar2(6),
bep_test_1_guid varchar2(36),
constraint pk_bep_test_1 primary key (id)
)

Then, go to the BEP console, create a new event.
I use USER. as the prefix of the event name. The prefix is used by RabbitMQ to route data to the different queues.

Go to Capture Data tab to add this table into the event.
I am using ID as the reference column.
In BEP, the reference column can only be one column, we can use GUID if it is meaningful.

Go to Triggers table to make sure the trigger was created. If not, click Activate and then Refresh

To the RabbitMQ:
http://emb1.pprd.odu.edu:15672/
Create a queue named user_defined_queue, with a binding

Then, we can insert a data into baninst1.bep_test_1

insert into  baninst1.bep_test_1 values(8,'bbb','F','ccc','dasda','dasdasdsadasd');
commit;

Then, monitor table baninst1.bep_test_1 to wait until the message is consumed

select * from cdcadmin.BEP_MULTI_CONSUMER_TABLE

Go to the page of the queue user_defined_queue
We can check the message in RabbitMQ here

The format of the message in “DEFAULT” integration type:

Message 1
The server reported 2 messages remaining.
Exchange
bep_events_topic
Routing Key
USER.BEP_TEST_1
Redelivered

Properties
app_id:
BEP
priority:
0
delivery_mode:
2
headers:
event:
USER.BEP_TEST_1
content_type:
application/json
Payload 
624 bytes
Encoding: string
{
  "businessEvent": "USER.BEP_TEST_1",
  "messageId": "161572425917",
  "timeStamp": "2020-06-03T15:53:22.897-04:00",
  "sourceOfChange": "Banner",
  "key": "8",
  "entity": "BEP_TEST_1",
  "changeType": "INSERT",
  "changeData": [
    {
      "name": "ID",
      "new": "8"
    },
    {
      "name": "NAME",
      "new": "bbb"
    },
    {
      "name": "GENDER",
      "new": "F"
    },
    {
      "name": "COMMENTS",
      "new": "ccc"
    },
    {
      "name": "BEP_TEST_1_VPDI_CODE",
      "new": "dasda"
    },
    {
      "name": "BEP_TEST_1_GUID",
      "new": "dasdasdsadasd"
    }
  ],
  "additionalData": []
}
Message 2
The server reported 1 messages remaining.
Exchange
bep_events_topic
Routing Key
USER.BEP_TEST_1
Redelivered

Properties
app_id:
BEP
priority:
0
delivery_mode:
2
headers:
event:
USER.BEP_TEST_1
content_type:
application/json
Payload 
624 bytes
Encoding: string
{
  "businessEvent": "USER.BEP_TEST_1",
  "messageId": "161572451091",
  "timeStamp": "2020-06-03T16:00:26.028-04:00",
  "sourceOfChange": "Banner",
  "key": "8",
  "entity": "BEP_TEST_1",
  "changeType": "DELETE",
  "changeData": [
    {
      "name": "ID",
      "old": "8"
    },
    {
      "name": "NAME",
      "old": "bbb"
    },
    {
      "name": "GENDER",
      "old": "F"
    },
    {
      "name": "COMMENTS",
      "old": "ccc"
    },
    {
      "name": "BEP_TEST_1_VPDI_CODE",
      "old": "dasda"
    },
    {
      "name": "BEP_TEST_1_GUID",
      "old": "dasdasdsadasd"
    }
  ],
  "additionalData": []
}
Message 3
The server reported 0 messages remaining.
Exchange
bep_events_topic
Routing Key
USER.BEP_TEST_1
Redelivered

Properties
app_id:
BEP
priority:
0
delivery_mode:
2
headers:
event:
USER.BEP_TEST_1
content_type:
application/json
Payload 
752 bytes
Encoding: string
{
  "businessEvent": "USER.BEP_TEST_1",
  "messageId": "161572557570",
  "timeStamp": "2020-06-03T16:30:32.796-04:00",
  "sourceOfChange": "Banner",
  "key": "7",
  "entity": "BEP_TEST_1",
  "changeType": "UPDATE",
  "changeData": [
    {
      "name": "ID",
      "old": "7",
      "new": "7"
    },
    {
      "name": "NAME",
      "old": "bbb",
      "new": "bbb"
    },
    {
      "name": "GENDER",
      "old": "M",
      "new": "F"
    },
    {
      "name": "COMMENTS",
      "old": "ccc",
      "new": "ccc"
    },
    {
      "name": "BEP_TEST_1_VPDI_CODE",
      "old": "dasda",
      "new": "dasda"
    },
    {
      "name": "BEP_TEST_1_GUID",
      "old": "dasdasdsadasd",
      "new": "dasdasdsadasd"
    }
  ],
  "additionalData": []
}

For the RedHat delivered MySQL, the install is very simple, just use yum to install.
But the position of configuration files are different.

my.cnf: /etc/opt/rh/rh-mysql80/my.cnf -> /etc/opt/rh/rh-mysql80/my.cnf.d/mysql-server.cnf
binary: /opt/rh/rh-mysql80/root/usr/bin
mysqld: mysql_secure_installation
Systemctl Script: /usr/lib/systemd/system/rh-mysql80-mysqld.service

We will use this database for wordpress. nearly 10 tables will be created for a website. So we have 30w tables.
The MySQL parameter open_files_limit we set up to 12000. So we need to set “LimitNOFILE = 12000” in the rh-mysql80-mysqld.service

When I started service “rh-mysql80-mysqld.service”, I encountered the error “’./binlog.index’ not found (OS errno 13 - Permission denied)”

mysqld: File './binlog.index' not found (OS errno 13 - Permission denied)
2020-05-18T15:32:17.909138Z 0 [Warning] [MY-010091] [Server] Can't create test file /l01/data/mysqld_tmp_file_case_insensitive_test.lower-test
2020-05-18T15:32:17.909214Z 0 [System] [MY-010116] [Server] /opt/rh/rh-mysql80/root/usr/libexec/mysqld (mysqld 8.0.17) starting as process 3419
2020-05-18T15:32:17.912847Z 0 [Warning] [MY-010091] [Server] Can't create test file /l01/data/mysqld_tmp_file_case_insensitive_test.lower-test
2020-05-18T15:32:17.912864Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /l01/data/ is case insensitive
2020-05-18T15:32:17.914169Z 0 [ERROR] [MY-010119] [Server] Aborting

In the service log, we can see it is because of the SELinux, the service cannot access /l01/data which I set up for “datadir”

May 18 11:32:20 wordpresssq01p.pprd.odu.edu goferd[1425]: [INFO][worker-0] gofer.messaging.adapter.connect:30 - connected: proto
May 18 11:32:20 wordpresssq01p.pprd.odu.edu goferd[1425]: [WARNING][worker-0] gofer.messaging.adapter.proton.reliability:48 - re
May 18 11:32:21 wordpresssq01p.pprd.odu.edu setroubleshoot[3425]: failed to retrieve rpm info for /l01/data
May 18 11:32:21 wordpresssq01p.pprd.odu.edu setroubleshoot[3425]: SELinux is preventing /opt/rh/rh-mysql80/root/usr/libexec/mysq
May 18 11:32:21 wordpresssq01p.pprd.odu.edu python[3425]: SELinux is preventing /opt/rh/rh-mysql80/root/usr/libexec/mysqld from 

So, we have to set up ACLs for the related directories and port

# chcon -R -t mysqld_db_t /l01/data
# chcon -t mysqld_db_t /run/rh-mysql80-mysqld/mysqld.pid
# chcon -t mysqld_db_t '/l01/.my.cnf'
# semanage port -a -t mysqld_port_t -p tcp 2321

The template New_Database.dbt is used to run the script to create a database.
Using -dbOptions to disable all unecessary componments.

dbca -silent -createDatabase \
  -sid PHUB \
  -gdbName PHUB.WORLD \
  -templateName New_Database.dbt \
  -datafileDestination /data01/app/oracle/oradata \
  -systemPassword oracle \
  -sysPassword oracle \
  -characterSet US7ASCII \
  -storageType FS \
  -databaseConfigType SINGLE \
  -emConfiguration NONE \
  -dvConfiguration false \
  -enableArchive false \
  -memoryMgmtType AUTO_SGA \
  -useOMF true\
  -sampleSchema false \
  -dbOptions APEX:false,JSERVER:false,DV:false,SPATIAL:false,CWMLITE:false,ORACLE_TEXT:false,IMEDIA:false,XDB_PROTOCOLS:false

OWM will be installed. I don’t find the option name of it. We can deinstall OWM after creation.

select * from all_wm_versioned_tables;  -- return 0
$ORACLE_HOME/rdbms/admin/owmuinst.plb

XDB is mandatory after 12.1

The list of componments installed:

SQL> select COMP_NAME, VERSION from dba_registry;

COMP_NAME						     VERSION
------------------------------------------------------------ ------------------------------
Oracle Database Catalog Views				     19.0.0.0.0
Oracle Database Packages and Types			     19.0.0.0.0
Oracle Real Application Clusters			     19.0.0.0.0
Oracle XML Database					     19.0.0.0.0

Yesterday, we encountered an error in Banner PPRD env. Form REBCD21 failed due to the TNSNAMES used to connect to database was wrong.

:[jobpprd]:[PPRD]:/misc/jobpprd ... >>cat rebcd21_6188134.log
 
    Error Logging on to Oracle.. Aborting
 
    Error During GJBPRUN Delete.. Aborting
    SQLCODE=24324
    SQLERRMC=ORA-24324: service handle not initialized
                            
    Failure During Rollback.. FBA2
    SQLCODE=01012
    SQLERRMC=ORA-01012: not logged on                                              
 
Report Name..................REBCD21                       
Program Version..............8.37     
One Up Number ...............6188134                       
Aid Year ....................    
Resend Prev.Processed Recs... 
Resend for Prior Date .......           
Application ID ..............                              
Selection ID.................                              
Creator ID...................                              
User ID......................                              
Resend for Prior Date/Hour...  
Resend for Prior Date/Minute.  
Send with Discrepant Trans #.  
Processing Type..............     
Federal School Code..........      
 
Date and time stamp 
  for DATE SENT     
  DATE            =            
  Hour            =   
  Minute          =   
 
Inst.Change requests processed = 00000
Hous. Change requests processed = 00000
Corrections processed = 00000
Change/correction records written = 00000
 
ORA-12154: TNS:could not resolve the connect identifier specified
    
Username: ORA-12545: Connect failed because target host or object does not exist

Username: ORA-12545: Connect failed because target host or object does not exist
ERM-NOLOGIN: Unable to CONNECT to ORACLE after 3 attempts

:[jobpprd]:[PPRD]:/misc/jobpprd ... >>cat rebcd21_6188134.debug 
 START OF FORMFUSION 
 PRINTFF............. lp -c -dDATABASE
 END OF FORM FUSION DEBUG
Print option ..  enscript -B -r -fCourier8 -PDATABASE   
# ------- Current Jobsub Environment ---
 HOME............ /home/jobpprd
 USER............ jobpprd
 LANG............ en_US.UTF-8
 ORACLE_HOME..... /oracle_home
 ORACLE_SID...... PPRD
 ORAENV_ASK...... NO
 TWO_TASK........ 
 TNS_ADMIN....... /jobsubFolder/proxy_setup
 ORACLE_PATH..... .:/bannerFolder:/bannerFolder/pprd/odu/links:/home/jobpprd:/bannerFolder/pprd/charfmx:/bannerFolder/pprd/links:/bannerFolder/pprd/admin:/bannerFolder/pprd/admin/v6/oratools
 SQLPATH......... .:/bannerFolder:/bannerFolder/pprd/odu/links:/home/jobpprd:/bannerFolder/pprd/charfmx:/bannerFolder/pprd/links:/bannerFolder/pprd/admin:/bannerFolder/pprd/admin/v6/oratools
 BANNER_HOME..... /bannerFolder/pprd
 BANNER_LINKS.... /bannerFolder/pprd/links
 DATA_HOME....... /bannerFolder/pprd/dataload
 EXE_HOME........ /bannerFolder/pprd/general/exe
 LD_LIBRARY_PATH. /opt/FJSVcbl64/lib:/opt/FJSVXbsrt/lib:/opt/FJSVXmeft/lib:/oracle_home/lib32:/oracle_home/lib:/usr/local/microfocus/cobol51/lib:/bannerFolder/pprd/general/exe:/usr/local/lib:/usr/lib
 COBPREF......... perl /bannerFolder/pprd/links/banfjsv.pl
#------------- Current gjajobs.shl parameter values -------
 H............. /home/jobpprd
 JOB........... rebcd21.shl
 PROC.......... rebcd21
 PROG.......... REBCD21
 BANUID........ [Q1ZHANG]
 BUID........ Q1ZHANG
 PSWD.......... @NOTVALIDPASSWD
 UIPW.......... [Q1ZHANG]/NOTVALIDPASSWD
 TEMP.......... rebcd21_6188134
 LOG........... /home/jobpprd/rebcd21_6188134.log
 FORM..........  enscript -B -r -fCourier8 -PDATABASE 
 PRNT.......... DATABASE
 PRNTOPT.......  enscript -B -r -fCourier8 -PDATABASE   
 ONE_UP........ 6188134
 SUBTIME....... 
#----------------------------------------
NLS_LANG setting .. AMERICAN_AMERICA.AL32UTF8
LC_ALL setting .. 
#----------------------------------------
----- rebcd21_6188134.in -----
6188134
----- rebcd21_6188134.shl -----
$JOB $BANUID $PSWD $ONE_UP $PROG $PRNT $BUID
if [ -f $H/$TEMP.in ]
then
/bin/rm $H/$TEMP.in
fi
if [ -f $H/$TEMP.shl ]
then
/bin/rm $H/$TEMP.shl
fi

Normally, the password given here should be the TNSNAME - JOBSUB_PPRD, but here it is NOTVALIDPASSWD.
This is the reason why the form cannot connect to the database and throw the error.

I did research on Ellucian support, NOTVALIDPASSWD means the form will use the filesystem credential.
When filesystem credential was on, a file with credential will be generated automatically with named one_up_num.creds

:[jobpprd]:[PPRD]:/home/jobpprd ... >>cat 6188159.creds
[Q1ZHANG] JOBSUB_PPRD 6188159 REBCD21 DATABASE

In the rebcd21.shl, there is a “if” control to decide which method will be used.

#
if [ "$2" != "NOTVALIDPASSWD" ]
then
    CMDFILE=$HOME/$$.prm;export CMDFILE;

Unfortunately, The password given from the form is “@NOTVALIDPASSWD”
There is an additional “@” at the head. So I added “@” in the script and the problem was solved.

But I don’t think it is the right way for this problem.
Opened a ticket to Ellucian, and found the filesystem cred was set to on in the GJAJOBS.
It can be disabled by using sqlplus

update GJBJOBS set GJBJOBS_CREDS_IN_FILE=NULL where GJBJOBS_CREDS_IN_FILE='Y';

1. Pre-Generation
1. Ellucian announanced that GUID generation can be done when the application is online, but unfortunately, in our case, during generation, some triggers will be fired and generate a lot of messages to workflow to send email out to the end use which may confuse them.
2. Suggest to switch database into restricted mode and disable all triggers in the underlying tables except the triggers whose named end with %GUID_TRG’and ‘%SURROGATE_ID’
3. Revoke permission ‘restricted session’ and role ‘dba’ from all business users except SATURN. No connection through this user, and if remove dba from SATURN, some tables will be failed.
4. Backup table GOREQRC and GOBEQRC, after GUID generation, check the number of records on these two tables to make sure they are unchanged. Otherwise, after application up, some confusing messages will be sent out by workflow.
5. Grant restricted session to BANGUIDGEN, this is the database user we use to schedule the script.
6. The script used to schedule GUID generation is gurguid_in_restricted_mode.sql, we need to do the following changes:
        p_add_parm(‘08’, ‘N’); – change from Y to N to run multiple schedules
        p_add_parm(‘09’, ‘Y’); – change to Y to disable BEP trigger during GUID generation

2. Generation

# schedule 10 sessions working together on the GUID generation.
# It will use Oracle Scheduler to run in the background

$ sqlplus BANGUIDGEN/oracle123 @$GUIDGEN/gurguid_in_restricted_mode.sql  
$ sqlplus BANGUIDGEN/oracle123 @$GUIDGEN/gurguid_in_restricted_mode.sql  
$ sqlplus BANGUIDGEN/oracle123 @$GUIDGEN/gurguid_in_restricted_mode.sql 
$ sqlplus BANGUIDGEN/oracle123 @$GUIDGEN/gurguid_in_restricted_mode.sql 
$ sqlplus BANGUIDGEN/oracle123 @$GUIDGEN/gurguid_in_restricted_mode.sql  
$ sqlplus BANGUIDGEN/oracle123 @$GUIDGEN/gurguid_in_restricted_mode.sql 
$ sqlplus BANGUIDGEN/oracle123 @$GUIDGEN/gurguid_in_restricted_mode.sql 
$ sqlplus BANGUIDGEN/oracle123 @$GUIDGEN/gurguid_in_restricted_mode.sql  
$ sqlplus BANGUIDGEN/oracle123 @$GUIDGEN/gurguid_in_restricted_mode.sql 
$ sqlplus BANGUIDGEN/oracle123 @$GUIDGEN/gurguid_in_restricted_mode.sql 

In my case, it took two and half hours to be done.
SATURN.SFRSTCR spends 2 hours to be done. Others normally finish in 45 minutes.

3. Monitor

# The status of each tables 
select gurtrck_curr_object_status, count(*) 
  from GURTRCK 
 where gurtrck_object_type = 'GUID_GEN'
 group by gurtrck_curr_object_status;

# After we submit a scheduler, a one-up-no will be in the log file.
# Using this number to get the details.
select GUROUTP_LINE 
  from GENERAL.GUROUTP 
 where GUROUTP_ONE_UP_NO=6065907
 order by GUROUTP_LINE

3. Post-Generation
1. compare the number of records in COBEQRC and GOREQRC
2. enable triggers
3. grant dba role to the users which needs, be careful, some business set dba role as the default role.