I am trying to do a POC where I want to move a whole table from Oracle database into MemSQL. Please suggest the best way to do it (possibly with an example will give a good picture to understand). I have exported the table from Oracle in .csv format and have kept it locally on MemSQL server.Trying load data is not working. Appreciate if you can share the complete step.
Regards
Suvendu
There are many ways to migrate a table and exporting to csv is certainly a basic one to leverage. You should be able to use load data to accomplish the task. What error are you getting from load data?
Thanks Deloy for your quick response. I scrapped loading using CSV file. Please suggest how do I bring a complete table from an OnPrem RAC Oracle database 12c to MemSQL. Please suggest the best mentod, we do not have any cloud option.
Your CSV option is the easiest to migrate a single table once. You can also use the MemSQL Replicate tool available through this link: SingleStore Replicate · SingleStore Documentation
Here is a sample load data command that should be fine with Oracle csv to MemSQL:
LOAD DATA INFILE
/home/files/load_data_data_file
INTO TABLE
myTableName
FIELDS TERMINATED BY ‘,’
(column1 ,column2,column3,column4,column5 )
You only need the columns if the file structure does not match the table structure. There are many other options as well at this link: SingleStoreDB Cloud · SingleStore Documentation
Thanks Deloy, I was able to insert into table using CSV format. Looks like the format I had exported from source was wrong, but as you rightly said CSV is the basic way to import a single table. For lager migration I am looking at the tools page as shared. I will comeback soon.
Regards
Suvendu
Hi Deloy, I saw the migration tool as replication SingleStore Replicate · SingleStore Documentation but seems to use it, we need license as well to download installer. Is there any trial version available for POC purpose?
Regards
Suvendu
Thanks Deloy, that was an easy deployment. But while I am trying to run the replicate, I encountered many errors which I fixed but following are some of errors, I am not sure where to change what. Please shed some lights.
[root@memserver replicate-cli]# ./bin/replicate snapshot /u001/replicate/replicate-cli/conf/conn/config_gen_snapshot_oracle_src.yaml /u001/replicate/replicate-cli/conf/conn/config_gen_snapshot_memsql_dst.yaml --filter /u001/replicate/replicate-cli/filter/config_gen_snapshot_oracle_filter.yaml --replace-existing
Progress: ====================================100.00%=====================================
Elapsed time: 00:00:00 ETA: 00:00:00 Rate: 0.00
Table name Rows Size Progress ETA Rate
Oracle --> MemSQL
Progress: ====================================100.00%=====================================
Elapsed time: 00:00:01 ETA: 00:00:00 Rate: 0.00
Table name Rows Size Progress ETA Rate
Replication error
tech.replicant.IStorage$StorageException: No tables or views specified for replication
at tech.replicant.db.DBReplicationManager.a(SourceFile:246)
at tech.replicant.db.DBReplicationManager.a(SourceFile:336)
at tech.replicant.db.DBReplicationManager.b(SourceFile:457)
at tech.replicant.Main.main(SourceFile:2551)
Oracle --> MemSQL
Progress: ====================================100.00%=====================================
Elapsed time: 00:00:01 ETA: 00:00:00 Rate: 0.00
Table name Rows Size Progress ETA Rate
[root@memserver replicate-cli]# cat /u001/replicate/replicate-cli/filter/config_gen_snapshot_oracle_filter.yaml
allow:
Adding info from trace.log
11:44:03.230 [DEBUG] ================= MAIN =================
11:44:03.582 [DEBUG] Replication mode: SNAPSHOT
11:44:03.582 [DEBUG] Version: 0.8.2.0
11:44:03.926 [DEBUG] Source type: ORACLE
11:44:03.971 [DEBUG] Source connection config:
host: hecgblrdb-sc
port: 1525
username: MEMSQL_REP
password: ********
max-conns: 30
service-name: crp12dw
11:44:03.973 [DEBUG] Filter:
rules: {dacrep=
types: [TABLE]
tables: {}
allow-unlisted: true}
11:44:03.974 [DEBUG] Destination type: MEMSQL
11:44:03.978 [DEBUG] Destination connection config:
host: localhost
port: 3306
username: root
password: ********
max-conns: 30
11:44:03.981 [DEBUG] Extractor snapshot configuration:
fetch-size-rows: 5000
threads: 16
max-jobs-per-chunk: 32
min-job-size-rows: 1000000
inf-number-behavior: EXCEPTION
flashback-query: true
parallel-query: true
11:44:03.984 [DEBUG] Applier snapshot configuration:
id-column: null
batch-size-rows: 5000
threads: 16
bulk-load:
enable: false
txn-size-rows: 1000000
11:44:04.763 [DEBUG] ORACLE: active connections: 0
11:44:04.817 [DEBUG] MEMSQL: active connections: 0
11:44:04.826 [DEBUG] Initializing…
11:44:04.993 [DEBUG] []: fetching table schemas…
11:44:04.994 [DEBUG] []: fetching view schemas…
11:44:04.994 [DEBUG] Namespaces: []
11:44:04.994 [DEBUG] Source tables: []
11:44:04.994 [DEBUG] Source views: []
11:44:05.077 [DEBUG] Cursor: SQN: 0, Cursor(commit_SCN=295710024189, seq_num=0)
11:44:05.079 [DEBUG] Existing tables: []
11:44:05.079 [DEBUG] Existing views: []
11:44:05.112 [ERROR] Replication error
tech.replicant.IStorage$StorageException: No tables or views specified for replication
at tech.replicant.db.DBReplicationManager.a(SourceFile:246)
at tech.replicant.db.DBReplicationManager.a(SourceFile:336)
at tech.replicant.db.DBReplicationManager.b(SourceFile:457)
at tech.replicant.Main.main(SourceFile:2551)
11:44:05.113 [DEBUG] Shutting down Data Collector.
Hello @Suvendu. Please specify the schema name in all upper case in filter file.
Your filter file will look like below
allow:
Hi Path - I was able to fix the issue. It the database server name which was connecting incorrectly. For Oracle RAC databases replicator seems not able to resolve the name which running the replication command, I replaced the SCAN name with VIP and it worked.