Applies to: xSQL Oracle Data Compare v1.0 - 2.0
As you may have noticed, most of the articles in this blog focus around scenarios and solutions for databases in Microsoft's SQL Server. However, most of the experienced DBA's in the field of information technology and data sciences have probably dealt with more than one database engine. Anyone working in big enterprises certainly knows of Oracle's database engines (12c being their latest version). According to DB-Engines' ranking, Oracle's databases have the number one spot in the popularity ranking of major database engines with 1413.01 points. Having said this, I felt that it was high time I posted an article that provides some insight into data synchronization in Oracle databases.
Without further ado, let's see how to perform a data comparison and synchronization with xSQL Oracle Data Compare. In this demo I have used the sample HR schema that can be found in this Git repository. I created two databases (orcl and orcl2) and added the HR schema in both. Then, I deleted all the data from the COUNTRIES table in orcl2.
The first thing you do is add the databases. For those of you who are used to xSQL Data Compare for SQL Server, you will notice that the dialog is a bit different:
In the Connection Name, you can specify any name you like to identify the connection with. Personally, I like to keep the Connection Name the same as the Service Name (which is basically the same as Database name in SQL Server). But this will work with any type of string, as long as you type the Service Name correctly. Then you specify the username (Schema name) and the password for that user. In the Host Name and Port fields enter the server name and service's port number on that server. After adding the databases, the comparison and synchronization process is more or less the same as in xSQL Data Compare for SQL Server:
- You click on Compare Schemas and choose the databases and schemas you would like to compare. For the schema selection, just leave the '<default>' option selected and xSQL Data Compare will automatically retrieve the tables associated with the user specified when the connection was added. The following tables from the HR schemas in both databases are mapped together.
- After the objects are analyzed and mapped together, you can perform the comparison by clicking on 'Compare Data'. Before you do this, you can also edit the comparison and scripting options to customize the comparison process by specifying for example, whether Views should be compared, or if the character case should be ignored in text columns. Apart from this you can also define some simple mapping rules to ignore table name prefixes or suffixes. The comparison result below shows that the COUNTRIES and LOCATIONS tables are different in both databases (LOCATIONS table is different because it contains a foreign key to the COUNTRIES table whose values I set to null in order to delete all the records from the COUNTRIES table).
- Now, all that is left is to generate the synchronization script for the left database (which is orcl2) and execute it so that the synchronization process is completed. Here is a screen shot of the script:
