-
Notifications
You must be signed in to change notification settings - Fork 216
/
Copy pathcdc-oracle19-pdb-table.sh
executable file
·228 lines (200 loc) · 10.6 KB
/
cdc-oracle19-pdb-table.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
#!/bin/bash
set -e
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" >/dev/null && pwd )"
source ${DIR}/../../scripts/utils.sh
if [ ! -z "$GITHUB_RUN_NUMBER" ]
then
# running with github actions
remove_cdb_oracle_image "LINUX.X64_193000_db_home.zip" "../../connect/connect-cdc-oracle19-source/ora-setup-scripts-cdb-table"
fi
create_or_get_oracle_image "LINUX.X64_193000_db_home.zip" "../../connect/connect-cdc-oracle19-source/ora-setup-scripts-pdb-table"
if [ ! -z "$SQL_DATAGEN" ]
then
cd ../../connect/connect-cdc-oracle19-source
log "🌪️ SQL_DATAGEN is set"
for component in oracle-datagen
do
set +e
log "🏗 Building jar for ${component}"
docker run -i --rm -e KAFKA_CLIENT_TAG=$KAFKA_CLIENT_TAG -e TAG=$TAG_BASE -v "${PWD}/${component}":/usr/src/mymaven -v "$HOME/.m2":/root/.m2 -v "$PWD/../../scripts/settings.xml:/tmp/settings.xml" -v "${PWD}/${component}/target:/usr/src/mymaven/target" -w /usr/src/mymaven maven:3.6.1-jdk-11 mvn -s /tmp/settings.xml -Dkafka.tag=$TAG -Dkafka.client.tag=$KAFKA_CLIENT_TAG package > /tmp/result.log 2>&1
if [ $? != 0 ]
then
logerror "❌ failed to build java component "
tail -500 /tmp/result.log
exit 1
fi
set -e
done
cd -
else
log "🛑 SQL_DATAGEN is not set"
fi
PLAYGROUND_ENVIRONMENT=${PLAYGROUND_ENVIRONMENT:-"plaintext"}
playground start-environment --environment "${PLAYGROUND_ENVIRONMENT}" --docker-compose-override-file "${PWD}/docker-compose.plaintext.pdb-table.yml"
playground --output-level WARN container logs --container oracle --wait-for-log "DATABASE IS READY TO USE" --max-wait 2500
log "Oracle DB has started!"
log "Setting up Oracle Database Prerequisites"
docker exec -i oracle bash -c "ORACLE_SID=ORCLCDB;export ORACLE_SID;sqlplus /nolog" << EOF
CONNECT sys/Admin123 AS SYSDBA
ALTER SESSION SET CONTAINER=CDB\$ROOT;
CREATE ROLE C##CDC_PRIVS;
CREATE USER C##MYUSER IDENTIFIED BY mypassword CONTAINER=ALL;
ALTER USER C##MYUSER QUOTA UNLIMITED ON USERS;
ALTER USER C##MYUSER SET CONTAINER_DATA = (CDB\$ROOT, ORCLPDB1) CONTAINER=CURRENT;
GRANT C##CDC_PRIVS to C##MYUSER CONTAINER=ALL;
GRANT CREATE SESSION TO C##CDC_PRIVS CONTAINER=ALL;
GRANT EXECUTE ON SYS.DBMS_LOGMNR TO C##CDC_PRIVS CONTAINER=ALL;
GRANT LOGMINING TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_\$LOGMNR_CONTENTS TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_\$DATABASE TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_\$THREAD TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_\$PARAMETER TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_\$NLS_PARAMETERS TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_\$TIMEZONE_NAMES TO C##CDC_PRIVS CONTAINER=ALL;
GRANT CONNECT TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON DBA_PDBS TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON CDB_TABLES TO C##CDC_PRIVS CONTAINER=ALL;
GRANT CREATE TABLE TO C##MYUSER container=all;
GRANT CREATE SEQUENCE TO C##MYUSER container=all;
GRANT CREATE TRIGGER TO C##MYUSER container=all;
GRANT FLASHBACK ANY TABLE TO C##MYUSER container=all;
-- The following privileges are required additionally for 19c compared to 12c.
GRANT SELECT ON V_\$ARCHIVED_LOG TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_\$LOG TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_\$LOGFILE TO C##CDC_PRIVS CONTAINER=ALL;
GRANT SELECT ON V_\$INSTANCE to C##CDC_PRIVS CONTAINER=ALL;
GRANT EXECUTE ON SYS.DBMS_LOGMNR TO C##CDC_PRIVS;
GRANT EXECUTE ON SYS.DBMS_LOGMNR_D TO C##CDC_PRIVS;
-- Enable Supplemental Logging for All Columns
ALTER SESSION SET CONTAINER=cdb\$root;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
-- Check Database Instance Version
GRANT SELECT ON V_\$INSTANCE to C##CDC_PRIVS;
EOF
log "Inserting initial data"
docker exec -i oracle sqlplus C\#\#MYUSER/mypassword@//localhost:1521/ORCLPDB1 << EOF
create table CUSTOMERS (
id NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 42) NOT NULL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50),
gender VARCHAR(50),
club_status VARCHAR(20),
comments VARCHAR(90),
create_ts timestamp DEFAULT CURRENT_TIMESTAMP ,
update_ts timestamp
);
CREATE OR REPLACE TRIGGER TRG_CUSTOMERS_UPD
BEFORE INSERT OR UPDATE ON CUSTOMERS
REFERENCING NEW AS NEW_ROW
FOR EACH ROW
BEGIN
SELECT SYSDATE
INTO :NEW_ROW.UPDATE_TS
FROM DUAL;
END;
/
insert into CUSTOMERS (first_name, last_name, email, gender, club_status, comments) values ('Rica', 'Blaisdell', 'rblaisdell0@rambler.ru', 'Female', 'bronze', 'Universal optimal hierarchy');
insert into CUSTOMERS (first_name, last_name, email, gender, club_status, comments) values ('Ruthie', 'Brockherst', 'rbrockherst1@ow.ly', 'Female', 'platinum', 'Reverse-engineered tangible interface');
insert into CUSTOMERS (first_name, last_name, email, gender, club_status, comments) values ('Mariejeanne', 'Cocci', 'mcocci2@techcrunch.com', 'Female', 'bronze', 'Multi-tiered bandwidth-monitored capability');
insert into CUSTOMERS (first_name, last_name, email, gender, club_status, comments) values ('Hashim', 'Rumke', 'hrumke3@sohu.com', 'Male', 'platinum', 'Self-enabling 24/7 firmware');
insert into CUSTOMERS (first_name, last_name, email, gender, club_status, comments) values ('Hansiain', 'Coda', 'hcoda4@senate.gov', 'Male', 'platinum', 'Centralized full-range approach');
exit;
EOF
log "Grant select on CUSTOMERS table"
docker exec -i oracle sqlplus C\#\#MYUSER/mypassword@//localhost:1521/ORCLPDB1 << EOF
ALTER SESSION SET CONTAINER=ORCLPDB1;
GRANT select on CUSTOMERS TO C##MYUSER;
EOF
log "Creating Oracle source connector"
playground connector create-or-update --connector cdc-oracle-source-pdb --package "io.confluent.connect.oracle.cdc.util.metrics.MetricsReporter" --level DEBUG << EOF
{
"connector.class": "io.confluent.connect.oracle.cdc.OracleCdcSourceConnector",
"log.sensitive.data": "true",
"tasks.max":2,
"key.converter": "io.confluent.connect.avro.AvroConverter",
"key.converter.schema.registry.url": "http://schema-registry:8081",
"value.converter": "io.confluent.connect.avro.AvroConverter",
"value.converter.schema.registry.url": "http://schema-registry:8081",
"confluent.license": "",
"confluent.topic.bootstrap.servers": "broker:9092",
"confluent.topic.replication.factor": "1",
"oracle.server": "oracle",
"oracle.port": 1521,
"oracle.sid": "ORCLCDB",
"oracle.pdb.name": "ORCLPDB1",
"oracle.username": "C##MYUSER",
"oracle.password": "mypassword",
"start.from":"snapshot",
"enable.metrics.collection": "true",
"redo.log.topic.name": "redo-log-topic",
"redo.log.consumer.bootstrap.servers":"broker:9092",
"table.inclusion.regex": "ORCLPDB1[.].*[.]CUSTOMERS",
"table.topic.name.template": "\${databaseName}.\${schemaName}.\${tableName}",
"numeric.mapping": "best_fit",
"connection.pool.max.size": 20,
"oracle.dictionary.mode": "auto",
"topic.creation.groups": "redo",
"topic.creation.redo.include": "redo-log-topic",
"topic.creation.redo.replication.factor": 1,
"topic.creation.redo.partitions": 1,
"topic.creation.redo.cleanup.policy": "delete",
"topic.creation.redo.retention.ms": 1209600000,
"topic.creation.default.replication.factor": 1,
"topic.creation.default.partitions": 1,
"topic.creation.default.cleanup.policy": "delete",
"use.transaction.begin.for.mining.session": "true"
}
EOF
log "Waiting 20s for connector to read existing data"
sleep 20
log "Insert 2 customers in CUSTOMERS table"
docker exec -i oracle sqlplus C\#\#MYUSER/mypassword@//localhost:1521/ORCLPDB1 << EOF
insert into CUSTOMERS (first_name, last_name, email, gender, club_status, comments) values ('Frantz', 'Kafka', 'fkafka@confluent.io', 'Male', 'bronze', 'Evil is whatever distracts');
insert into CUSTOMERS (first_name, last_name, email, gender, club_status, comments) values ('Gregor', 'Samsa', 'gsamsa@confluent.io', 'Male', 'platinium', 'How about if I sleep a little bit longer and forget all this nonsense');
exit;
EOF
log "Update CUSTOMERS with email=fkafka@confluent.io"
docker exec -i oracle sqlplus C\#\#MYUSER/mypassword@//localhost:1521/ORCLPDB1 << EOF
update CUSTOMERS set club_status = 'gold' where email = 'fkafka@confluent.io';
exit;
EOF
log "Deleting CUSTOMERS with email=fkafka@confluent.io"
docker exec -i oracle sqlplus C\#\#MYUSER/mypassword@//localhost:1521/ORCLPDB1 << EOF
delete from CUSTOMERS where email = 'fkafka@confluent.io';
exit;
EOF
log "Altering CUSTOMERS table with an optional column"
docker exec -i oracle sqlplus C\#\#MYUSER/mypassword@//localhost:1521/ORCLPDB1 << EOF
ALTER SESSION SET CONTAINER=CDB\$ROOT;
EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
ALTER SESSION SET CONTAINER=ORCLPDB1;
alter table CUSTOMERS add (
country VARCHAR(50)
);
ALTER SESSION SET CONTAINER=CDB\$ROOT;
EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
exit;
EOF
log "Populating CUSTOMERS table after altering the structure"
docker exec -i oracle sqlplus C\#\#MYUSER/mypassword@//localhost:1521/ORCLPDB1 << EOF
insert into CUSTOMERS (first_name, last_name, email, gender, club_status, comments, country) values ('Josef', 'K', 'jk@confluent.io', 'Male', 'bronze', 'How is it even possible for someone to be guilty', 'Poland');
update CUSTOMERS set club_status = 'silver' where email = 'gsamsa@confluent.io';
update CUSTOMERS set club_status = 'gold' where email = 'gsamsa@confluent.io';
update CUSTOMERS set club_status = 'gold' where email = 'jk@confluent.io';
commit;
exit;
EOF
log "Waiting 20s for connector to read new data"
sleep 20
log "Verifying topic ORCLPDB1.C__MYUSER.CUSTOMERS: there should be 13 records"
playground topic consume --topic ORCLPDB1.C__MYUSER.CUSTOMERS --min-expected-messages 13 --timeout 60
log "Verifying topic redo-log-topic: there should be 14 records"
playground topic consume --topic redo-log-topic --min-expected-messages 14 --timeout 60
if [ ! -z "$SQL_DATAGEN" ]
then
DURATION=10
log "Injecting data for $DURATION minutes"
docker exec -d sql-datagen bash -c "java ${JAVA_OPTS} -jar sql-datagen-1.0-SNAPSHOT-jar-with-dependencies.jar --host oracle --username C##MYUSER --password mypassword --sidOrServerName service_name --sidOrServerNameVal ORCLPDB1 --maxPoolSize 10 --durationTimeMin $DURATION"
fi