Data synchronization between Oracle databases

This period of time is responsible for the database management of a project development. The development database in this project is separated from the test database. Some system table data and basic data often need to be synchronized, to facilitate the synchronization of the specified data table, you can use the dblink and merge methods, which is simple and convenient.

Operating Environment: the ip address of the Oracle database server is 192.168.196.76, which has two databases: center and branch. Generally, You need to synchronize table data from the center to branch. The center is the source database and branch is the target database, the procedure is as follows:

1. dblink created from the source database to the target database

Create database link branch -- enter the name of the dblink to be created, which is customized.

Connect to dbuser identified by "password" -- set the user name and password used to connect to the remote database.

Using '192. 168.196.76/branch'; -- specify the connection method of the target database. The tns name is available.

When creating a dblink, you must note that the user name and password may be incorrect sometimes, but the account information we entered is correct, at this time, pay attention to the password case based on the input set on the server, and add double quotation marks (caused by different server versions) before the account password ).

2. Verify dblink after success

Select * from tb_bd_action @ branch;

Normally, if dblink is successfully created, you can access the table in the remote database in this way.

3. Synchronize table data using the merge statement

In this example, You need to synchronize the tb_sys_sqlscripe table in the center database to branch. The simple syntax is as follows:

Merge into tb_sys_sqlscripe @ branch B using tb_sys_sqlscripe c on (B. pk = c. pk) -- synchronize the table merge from the center to branch based on the pk of the two tables

When matched then update set B. sqlscripe = c. sqlscripe, B. author = c. author -- if the pK value is the same, update the value of the specified table to the target table.

When not matched then -- if the pK value is different, insert the entire data in the source table to the target table.

Insert values (c. pk, c. fk, c. createtime, c. lastmodifytime, c. author, c. module, c. deleteflag, c. scripttype );

Commit; -- remember that commit is required after merge; otherwise, the change cannot be submitted.

4. to automatically complete synchronization every time synchronization is required, you can make the statement into a stored procedure or script for regular execution or manual execution as required. Let's simply explain how to create a script:

A. Create a merge folder

B. Write the complete merge statement and save it to the merge. SQL file.

C. Create a merge. bat file and write the following content after editing.

Sqlplus user/password @ serverip/database @ "% cd % \ merge. SQL"

When you run merge. bat, the synchronization between the specified databases is automatically completed.