{"id":2523,"date":"2023-05-02T11:46:23","date_gmt":"2023-05-02T18:46:23","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/data-migration-from-oracle-to-couchbase\/"},"modified":"2023-05-02T11:46:23","modified_gmt":"2023-05-02T18:46:23","slug":"data-migration-from-oracle-to-couchbase","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/es\/data-migration-from-oracle-to-couchbase\/","title":{"rendered":"Data Migration from Oracle to Couchbase"},"content":{"rendered":"\n

You may know Couchbase as the fastest, most scalable NoSQL platform available. However, did you know that Couchbase can also be a relational database? Couchbase is a data platform that can operate with multiple simultaneous personalities all in the same platform. It can be structured or unstructured. And it can be transactional or analytical. In this blog, we are going to look at moving data from Oracle to Couchbase.<\/span><\/p>\n\n\n\n

Source Oracle Database<\/span><\/h2>\n\n\n\n

For a source database, we are going to use <\/span>Swingbench<\/span><\/a>. It is a synthetic workload generator for Oracle. It is self-contained and creates schemas that are reminiscent of what might be found in an actual production application. For this blog, we are going to use the Simple Order Entry (SOE) schema that is created by the utility. A visual representation of the SOE schema can be seen below. Generating the schema with a scale parameter of 1 <\/em>results in a dataset with about 15 million records.<\/span><\/p>\n\n\n\n

\"Oracle<\/p>\n\n\n\n

Data Import<\/span><\/h2>\n\n\n\n

To migrate the data from Oracle to Couchbase, we will use the <\/span>cbperf<\/span><\/a><\/em> demo tool. This utility primarily generates randomized synthetic schemas for Couchbase, but it also can import data into Couchbase via a plugin from external data sources. The Oracle plugin for cbperf<\/em> will replicate all tables available to the connecting user, copy associated table data, and for each table, it will extract the columns that are indexed to recreate the indexes on Couchbase.\u00a0<\/span><\/p>\n\n\n

[crayon nums=”false” lang=”default” decode=”true”]% bin\/cb_perf import -h cbhost -b soe -s soe -P oracle -V connect=soe\/soe@orahost\/testdb[\/crayon]<\/p>\n\n\n\n

Assuming that the SOE schema was set up with the default username and password, which is soe<\/em>\u00a0and soe<\/em>, respectively, this syntax will export the data from the database SID testdb<\/em> on host orahost<\/em> and import it to the Couchbase bucket soe<\/em> with scope soe<\/em> where one node in the cluster is cbhost<\/em>.<\/span><\/p>\n\n\n\n

The Data in Couchbase<\/span><\/h2>\n\n\n\n

Let’s get the important disclaimer out of the way up front. For a production application, you will likely not want to just “lift and shift” the data and indexes. Instead, an analysis needs to be done to determine the optimal configuration in Couchbase along with the required application changes. The ultimate level of effort will vary by application.<\/span><\/p>\n\n\n\n

That said, once the cbperf<\/em> import is complete, there will be about 15 million documents in Couchbase. The soe<\/em>\u00a0scope will have collections that correspond to each table in Oracle.<\/span><\/p>\n\n\n\n

Here are the tables in Oracle:<\/span><\/p>\n\n\n\n

\"Oracle<\/p>\n\n\n\n

Here are the collections in Couchbase:<\/span><\/p>\n\n\n\n

\"Couchbase<\/p>\n\n\n\n

Each row that was in the Oracle database is now a document in Couchbase. The document key is constructed from the collection name plus a colon “:” plus the sequential document number (basically the row number). The document JSON keys are the table column names, and the values are the column values for that row.<\/span><\/p>\n\n\n

[crayon nums=”false” lang=”pgsql” decode=”true”]select * from CUSTOMERS where CUSTOMER_ID = 1 ;[\/crayon]<\/p>\n\n\n\n

\"Query<\/p>\n\n\n

[crayon nums=”false” lang=”pgsql” decode=”true”]select * from soe.soe.customers where customer_id = 1 ;[\/crayon]<\/p>\n\n\n\n

\"Oracle<\/p>\n\n\n\n

Finally, we can see all the indexes that were automatically generated based on the indexed columns in the Oracle database. To allow for the most query flexibility, each index covers one column.\u00a0<\/span><\/p>\n\n\n\n

\"Oracle<\/p>\n\n\n\n

Running a Query<\/span><\/h2>\n\n\n\n

Looking through the Swingbench source code, I found the following query:<\/span><\/p>\n\n\n

[crayon nums=”false” lang=”pgsql” decode=”true”]SELECT tt.order_total,
\n\u00a0\u00a0tt.sales_rep_id,
\n\u00a0\u00a0tt.order_date,
\n\u00a0\u00a0customers.cust_first_name,
\n\u00a0\u00a0customers.cust_last_name
\nFROM
\n\u00a0\u00a0(SELECT orders.order_total,
\n\u00a0\u00a0\u00a0\u00a0orders.sales_rep_id,
\n\u00a0\u00a0\u00a0\u00a0orders.order_date,
\n\u00a0\u00a0\u00a0\u00a0orders.customer_id,
\n\u00a0\u00a0\u00a0\u00a0rank() Over (Order By orders.order_total DESC) sal_rank
\n\u00a0\u00a0FROM orders
\n\u00a0\u00a0WHERE orders.sales_rep_id = 1
\n\u00a0\u00a0) tt,
\n\u00a0\u00a0customers
\nWHERE tt.sal_rank <= 10
\nand customers.customer_id = tt.customer_id[\/crayon]<\/p>\n\n\n\n

Running this query against the Oracle database, I get the following results:<\/span><\/p>\n\n\n\n

\"Oracle<\/p>\n\n\n\n

And running the query on Couchbase, I get the same results. The only thing I had to change in the query was to prefix the collections with soe.soe<\/em>. to construct a Couchbase key space.<\/span><\/p>\n\n\n\n

\"Couchbase<\/p>\n\n\n\n

\"table<\/p>\n\n\n\n

Fun Fact<\/span><\/h2>\n\n\n\n

You may be familiar with the ORACLE_SID<\/em> as the Oracle database instance identifier, but do you know what it does under the covers? It stands for the Site Identifier<\/em>. It is hashed with ORACLE_HOME<\/em> to get an SGA (System Global Area) key which is the shared memory key on Unix\/Linux systems. When you get an ORA-01034: ORACLE not available<\/em> error, it is because this shared memory key is not present on the system. Thus you can\u2019t communicate with the database. Fortunately, working with Couchbase is vastly simpler, allowing you to focus on other things. Unless, of course, you like operating system internals.<\/span><\/p>\n\n\n\n

Conclusion<\/span><\/h2>\n\n\n\n

While it might seem like a daunting task, importing data from a relational database into Couchbase is not that difficult. To be fair, there is more to consider than just columns and indexes, such as stored procedures (user-defined functions in Couchbase), but it is certainly very doable.<\/span><\/p>\n\n\n\n

References<\/b><\/h3>\n\n\n\n