/
oracle.ad
148 lines (121 loc) · 6.2 KB
/
oracle.ad
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
=== Oracle
This section describes Oracle specific SymmetricDS details.
==== Database Permissions
The SymmetricDS database user generally needs privileges for connecting and creating tables (including indexes), triggers, sequences,
and procedures (including packages and functions). For change data capture, use the following permissions:
[source, SQL]
GRANT CONNECT TO SYMMETRIC;
GRANT RESOURCE TO SYMMETRIC;
GRANT CREATE ANY TRIGGER TO SYMMETRIC;
GRANT EXECUTE ON UTL_RAW TO SYMMETRIC;
ifdef::pro[]
.Log Miner Permissions
When using Log Miner instead of triggers, the SYSDBA should put the database into archive log mode:
[source, SQL]
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
alter database add supplemental log data (all) columns;
If using Oracle 11g without CDB, use the following permissions:
[source, SQL]
grant create session, alter session, select any dictionary, select any transaction, execute_catalog_role to symmetric;
If using Oracle 12c without CDB, use the following permissions:
[source, SQL]
grant create session, alter session, select any dictionary, execute_catalog_role, logmining to symmetric;
If using Oracle 12c with PDB, use the following permissions:
[source, SQL]
grant create session, alter session, set container, select any dictionary, logmining, execute_catalog_role to c##symmetric CONTAINER=all;
alter user c##symmetric set container_data = (cdb$root, <PDB name>) container=current;
endif::pro[]
==== Known Limitations
* The LONG data type is not supported with change capture, since LONG columns cannot be accessed from triggers. The data in LONG columns can still be kept in sync by enabling the use_stream_lobs feature in <<TRIGGER>>.
* The global precision of numeric columns is controlled by the oracle.template.precision parameter. It defaults to a precision of 30,10
* With the default settings a database row cannot exceed 4k. If the error `'ORA-01489: result of string concatenation is too long'` occurs
then set `use_capture_lobs` to 1 in the <<TRIGGER>> table and `contains_big_lobs` to 1 on the assigned <<CHANNEL>>. Triggers will need to be synchronized. By enabling
`use_capture_lobs`, the concatenated varchar string is cast to a clob which allows a length of more than 4k. By enabling `contains_big_lobs`, the extraction of
sym_data is cast to a clob which prevents truncation at 4k. There is overhead for both of these settings
* When multiple triggers are defined on the same table, then the order in which the triggers occur appears to be arbitrary
==== Loading Data
SymmetricDS has bulk loading capability available for Oracle. SymmetricDS specifies data loader types on a channel by channel basis.
To utilize bulk loading with Oracle, you should create a user that is not the default system user of the database. Whatever user you end up using, make sure it also has the permissions mentioned above in the Database Permission section, as well as the ones mentioned below. It's important to note that SQL Loader (what Oracle uses to bulk load) will not work if either your user name or password contains a '@' symbol, so make sure to avoid this in both your username and password.
----
create user yourusername identified by "yourpassword";
----
Once your user is created, make sure to add the following permissions to the user to make sure that Symmetric has the correct permissions to bulk load with your database. (Again, make sure your user has the permissions mentioned in the Database Permission section as well)
----
GRANT CREATE SESSION TO yourusername;
GRANT CREATE TABLE TO yourusername;
alter user yourusername quota unlimited on system;
GRANT CONNECT TO yourusername;
GRANT RESOURCE TO yourusername;
GRANT CREATE ANY TRIGGER TO yourusername;
GRANT EXECUTE ON UTL_RAW To yourusername;
----
Register your Oracle database with Symmetric using the user you've just created. Once you have done that, when you get to the Bulk Loading screen on the Connect Database Wizard you will need to provide a path to your SQL Loader installation. If you do not have SQL Loader installed, there is a link provided in the Connect Database Wizard where you can go to get this installed.
Once you have your database registered, to utilize Oracle Bulk loading versus straight JDBC insert, specify the Oracle Bulk Loader ("bulk") in the data_loader_type column of sym_channel table.
Once these steps are complete your database should be configured to bulk load with SymmetricDS.
==== Optional - Partitioning
Partitioning the DATA table by channel can help insert, routing and extraction performance on concurrent, high throughput systems.
TRIGGERs should be organized to put data that is expected to be inserted concurrently on separate CHANNELs.
The following is an example of partitioning. Note that both the table and the index should be partitioned. The default value
allows for more channels to be added without having to modify the partitions.
[source, SQL]
CREATE TABLE SYM_DATA
(
data_id INTEGER NOT NULL ,
table_name VARCHAR2(50) NOT NULL,
event_type CHAR(1) NOT NULL,
row_data CLOB,
pk_data CLOB,
old_data CLOB,
trigger_hist_id INTEGER NOT NULL,
channel_id VARCHAR2(20),
transaction_id VARCHAR2(1000),
source_node_id VARCHAR2(50),
external_data VARCHAR2(50),
create_time TIMESTAMP
) PARTITION BY LIST (channel_id) (
PARTITION P_CONFIG VALUES ('config'),
PARTITION P_CHANNEL_ONE VALUES ('channel_one'),
PARTITION P_CHANNEL_TWO VALUES ('channel_two'),
...
PARTITION P_CHANNEL_N VALUES ('channel_n'),
PARTITION P_DEFAULT VALUES (DEFAULT));
[source, SQL]
CREATE UNIQUE INDEX IDX_D_CHANNEL_ID ON SYM_DATA (DATA_ID, CHANNEL_ID) LOCAL
(
PARTITION I_CONFIG,
PARTITION I_CHANNEL_ONE,
PARTITION I_CHANNEL_TWO,
...
PARTITION I_CHANNEL_N,
PARTITION I_DEFAULT
);
==== Supported Data Types
.Supported Data Types
|===
| Data Type | Supported?
| Char | Yes
| NChar | Yes
| VarChar2 | Yes
| NVarChar2 | Yes
| Long | No
| Number | Yes
| Binary_Float | Yes
| Binary_Double | Yes
| Date | Yes
| Timestamp | Yes
| Timestamp With Time Zone | Yes
| Timestamp With Local Time Zone | Yes
| Interval Year to Month | Yes
| Interval Day to Second | Yes
| Raw | Yes
| Long Raw | No
| RowID | Yes
| URowID | No
| Clob | Yes
| NClob | Yes
| Blob | Yes
| BFile | No
|===