How to synchronize two databases in Oracle

We often hope to unify data stored in different regions for application. Now we can use the replication technology to solve this problem. However, Database Replication also requires some conditions.

First, the database must have the Advanced replication function (log on to the database as a system and view the v $ option view. If Advanced replication is TRUE, the Advanced replication function is supported; otherwise, the Advanced replication function is not supported ).

If Advanced Replication is available, some parameter initialization is required for the database.

Db_domain = test.com.cn indicates the database domain name (the default is WORLD). Here, you can use your company domain name; global_names = true it requires database link) the name is the same as the name of the connected database. Now the global database name is db_name + ". "+ db_domain;

Parameters related to database job execution:
Job_queue_processes = 1;
Job_queue_interval = 60;
Distributed_transactions = 10;
Open_links = 4

The first line defines the number of initiation of the SNP process as n. The default value is 0, and the normal definition range is 0 ~ 36. You can configure different values based on the number of tasks. The second line defines the system to wake up the process every N seconds. The default value is 60 seconds. The normal range is 1 ~ 3600 seconds. In fact, the process enters the sleep state after the current task is executed. After a period of sleep, the general control of the system will wake it up. If you have modified these parameters, You need to restart the database to make the parameters take effect.

After completing the preliminary preparations, we will implement synchronous database replication.

Suppose there are two databases on the Internet: China and Japan ).
The specific configuration is as follows:
Database Name: China, Japan
Database domain name test.com.cn
Database sid numbers: China and Japan
Listener port 1521
Server IP address 10.1.0.100 10.1.0.200
Make sure that the two databases can access each other and set the database connection string in tnsnames. ora.
The database connection string on the Chinese side is in the following format:
Japan =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.1.1.200) (PORT = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = Japan)
)
)
Run $ tnsping Japan and the following prompt appears:
Attempting to contact (ADDRESS = (PROTOCOL = TCP) (HOST = 10.1.1.200) (PORT = 1521 ))
OK (n ms)
Indicates that the chinese database can access the Japanese database. The configuration is the same in Japan. Make sure that $ tnsping China is available.
Change the global name of the database and create a public database link.
Log on to the China database as a system
SQL> alter database rename global_name to China.test.com.cn;
Log on to the Japan database as a system:
SQL> alter database rename global_name to Japan.test.com.cn;
Log on to the China database as a system.
SQL> create public database link Japan .test.com.cn using 'Japan ';
Test the global database name and public database connection.
SQL> select * from global_name@Japan.test.com.cn;
The returned result is Japan.test.com.cn.
Log on to the Japan database as a system:
SQL> create public database link China.test.com.cn using 'China ';
Test the global database name and public database connection.
SQL> select * from global_name@China.test.com.cn;
The returned result is China.test.com.cn.
Create a user repadmin who manages database replication and grant permissions.
Log on to the China database as a system:
SQL> create user repadmin identified by repadmin default tablespace users temporary tablespace temp;
SQL> execute dbms_defer_sys.register_propagator ('repadmin ');
SQL> grant execute any procedure to repadmin;
SQL> execute dbms_repcat_admin.grant_admin_any_repgroup ('repadmin ');
SQL> grant comment any table to repadmin;
SQL> grant lock any table to repadmin;
Log on to the Japan database as a system, run the preceding commands, manage the database replication user repadmin, and grant permissions.
Create a private database link under the user repadmin who copied the database.
Log on to the China database as repadmin.
SQL> create database link Japan.test.com.cn connect to repadmin identified:
SQL> select * from global_name@Japan.test.com.cn;
The returned result is Japan.test.com.cn.
Log on to the Japan database as the repadmin user.
SQL> create database link China.test.com.cn connect to repadmin identified by repadmin;
Test the private database link:
SQL> select * from global_name@China.test.com.cn;
The returned result is China.test.com.cn.
Create or select users and objects for database replication to grant permissions to users. database objects must have primary keywords.
Log on to the China database as internal, create a scott user, and grant permissions:
SQL> create user scott identified by tiger default tablespace users temporary tablespace temp;
SQL> grant connect, resource to scott;
SQL> grant execute on sys. dbms_defer to scott;
Log on to the China database as scott and create the table dept:
SQL> create table dept
(Deptno number (2) primary key,
Dname varchar2 (14 ),
Loc varchar2 (13 ));
If the database object does not have a primary keyword, run the following SQL command to add it:
SQL> alter table dept add (constraint dept_deptno_pk primary key (deptno ));
Create the serial number of the primary keyword under the scott user in the China database to avoid conflicts with the Japanese.
SQL> create sequence dept_no increment by 1 start with 1 maxvalue 44 cycle nocache;
Insert initialization data under China database scott
SQL> insert into dept values (dept_no.nextval, 'accounting', 'New york ');
SQL> insert into dept values (dept_no.nextval, 'Research ', 'Dallas ');
SQL> commit;
Run the above 1, 2, and 3 operations on the Japanese database.
Create the serial number of the primary keyword under the scott user of the Japan database to avoid conflicts with China.
SQL> create sequence dept_no increment by 1 start with 45 maxvalue 99 cycle nocache;
Insert initialization data under the scott user in the Japan database.
SQL> insert into dept values (dept_no.nextval, 'sales', 'Chicago ');
SQL> insert into dept values (dept_no.nextval, 'operations', 'boston ');
SQL> commit;

1 2 3 Next Page