Operating a database on the Internet often has such a need: unifying database applications similar to all cities across the country, and a node's data change is not only reflected locally, but also reflects the remote. Replication technology provides users with a quick access to shared data.
First, the prerequisite for realizing database replication
1. Database Support Advanced Replication
You can log in to the database as a System, view the V $ Option view, if the Advanced Replication is true, support advanced replication (otherwise it is not supported.
2, database initialization parameter requirements
1, db_domain = Test.com.cn
Indicates the domain name of the database (default is World), here you can use your company's domain name.
2, global_names = TRUE
It requires database links and the connected database name.
Today's Global Database Name: db_name "." DB_DOMAIN
3, have the parameters related to database JOB
Job_Queue_Processes = 1
Job_Queue_INTERVAL = 60
Distributed_Transactions = 10
Open_LINKS = 4
The first line defines the number of startups from the SNP process. The system default is 0, and the normal definition range is from 0 to 36. Different values can be configured according to the task.
The second line definition system wakes up the process every n second. The system default is 60 seconds, the normal range is 1 ~ 3600 seconds. In fact, after the process performs a foundation task, it enters the sleep state, and after a while, the system's total control is responsible for waking it.
If you have modified these parameters, you need to restart the database to make the parameters take effect.
Second, the step of realizing database synchronization
Suppose we have two databases on the Internet: a shenzhen, a beijing.
See the table below:
Database name shenzhenbeijing database domain name test.com.cntest.com.cn Database SID number SHENZHENBEIJINGLISTENER port number 15211521 server IP address 10.1.10010.1.1.200
1. Confirm that the two databases can be accessed to each other, and the database connection string is set in TNSNames.ora.
1. For example: the database connection string on Shenzhen is the following format
Beijing =
(Description =
(Address_list =
(Address = (protocol = TCP) (Host = 10.1.1.200) (port = 1521)))
)
(Connect_data =
(Service_name = beijing)
)
)
Run $ TNSPING Beijing
The following prompt appears:
Attempting to contact (pecol = tcp) (host = 10.1.1.200) (port = 1521))
OK (n milliseconds)
Indicates that Shenzhen database can access Beijing database.
2, also configured in Beijing, confirm that $ tnsping shenzhen is through.
2, change the database global name, build a public database link.
1, log in to the shenzhen database as SYSTEM
SQL> ALTER DATABASE RENAME GLOBAL_NAME TO SHENZHEN.TEST.COM.CN;
Log in to the beijing database as SYSTEM:
SQL> ALTER DATABASE RENAME GLOBAL_NAME to Beijing.test.com.cn; 2, log in to the shenzhen database as SYSTEM
SQL> CREATE PUBLIC DATABASE LINK BEIJING.TEST.COM.CN Using 'Beijing'
Test database global name and public database link
SQL> Select * from global_name@beijing.test.com.cn;
The return result is right in beijing.test.com.cn.
Log in to the beijing database as SYSTEM:
SQL> CREATE PUBLIC DATABASE LINK SHENZHEN.TEST.COM.CN USING 'SHENZHEN';
Test database global name and public database link
SQL> Select * from global_name@shenzhen.test.com.cn;
The return result is right for shenzhen.test.com.cn.
3. Establish a user repymin, and empower.
1, log in to the shenzhen database as SYSTEM
SQL> CREATE User Repadmin Identified by Repadmin Default TableSpace Uses Temporary TableSpace Temp;
SQL> EXECUTE DBMS_DEFER_SYS.REGOSTER_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;
2, log in to the beijing database as you log in to the beijing database, run the above command, manage the user repymin, and empower the database.
Description: The RepAdmin username and password can be freely named according to the needs of the user.
4. Create a private database link under the database replication user repymin.
1, log in to the shenzhen database as a repadmin
SQL> CREATE DATABASE LINK BEIJING.TEST.COM.CN Connect to Repadmin Identified by Repadmin
Test this private database link:
SQL> Select * from global_name@beijing.test.com.cn;
The return result is right in beijing.test.com.cn.
2, log in to the beijing database with RepAdmin
SQL> CREATE DATABASE LINK SHENZHEN.TEST.COM.CN Connect to Repadmin Identified by Repadmin
Test this private database link
SQL> Select * from global_name@shenzhen.test.com.cn;
The return result is right for shenzhen.test.com.cn.
5. Create or select the user and object that implements the database replication, empower the user, the database object must have the primary keyword.
Suppose we use the Scott users used in Oracle, the DEPT table.
1, log in to the SHENZHEN database as INTERNAL, create Scott users and empower
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;
2, log in to the SHENZHEN database as SCOTT, create a table DEPT
SQL> CREATE TABLE DEPT
(DePTNO Number (2) Primary Key,
DNAME VARCHAR2 (14),
Loc varchar2 (13));
3, if the database object does not have a primary keyword, you can run the following SQL command to add:
SQL> ALTER TABLE DEPT ADD (constraint dept_deptno_pk primary key (deptno));
4, create the serial number of the primary key under the SHENZHEN database Scott User, and avoid conflicts with Beijing.
SQL> CREATE SEQUENCE DEPT_NO INCREMENT BY 1 Start with 1 MaxValue 44 CYCLE NOCACHE
(Note: MaxValue 44 can be determined according to the number of bits defined by the application and table structure main keyword)
5, insert the initialization data at the SHENZHEN database SCOTT user
SQL> INSERT INTO Dept Values (DEPT_NO.NEXTVAL, 'Accounting', 'New York');
SQL> INSERT INTO Dept Values (de PEPT_NO.NEXTVAL, 'Research', 'Dallas');
SQL> commit;
6, the same runs above the beijing database 1, 2, 3
7, create the serial number of the primary key under the Beijing database Scott User, range to avoid conflict with the shenzhen.
SQL> CREATE SEQUENCE DEPT_NO INCREMENT BY 1 Start with 45 MaxValue 99 Cycle Nocache;
8, insert the initialization data at the beijing database SCOTT user
SQL> INSERT INTO Dept Values (DEPT_NO.NEXTVAL, 'SALES', 'Chicago');
SQL> INSERT INTO Dept Values (de PEPT_NO.NEXTVAL, 'Operations', 'Boston');
SQL> commit;
6. Create group scott_mg to copy, join the database object, generate replication support for objects
1, log in to the shenzhen database as a repadmin, create a main replication group scott_mg
SQL> EXECUTE DBMS_REPCAT.CREATE_MASTER_REPGROUP ('scott_mg');
Note: The Scott_MG group name can be freely named according to the needs of the user.
2, join the database object in the replication group scott_mg
SQL> EXECUTE DBMS_REPCAT.CREATE_MASTER_REPOBJECT (SNAME => 'Scott', oname => 'Dept', Type => 'Table', use_existing_object => true, gname => 'scott_mg');
Parameter Description:
Sname implements a database replicated user name oname oname on the database object name
(The length of the table name is in 27 bytes, the program package is within 24 bytes)
TYPE implements database object categories for database replication
(Supported category: table, index, synonym, trigger, view, process, function, package, programbore)
Use_existing_Object true Indicates the database object that already exists with the primary copy node
GNAME master replication group name
3, generate replication support for database objects
SQL> EXECUTE DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('Scott', 'DEPT', 'TABLE');
(Description: Generate database triggers and packages that support SCOTT users
4, confirm that the copied group and object have joined the data dictionary of the database
SQL> SELECT GNAME, MASTER, STATUS from DBA_REPGROUP
SQL> Select * from DBA_REPOBJECT;
7. Create a master copy node
1, log in to the Shenzhen database as a repadmin, create a master copy node
SQL> EXECUTE DBMS_REPCAT.ADD_MASTER_DATABASE
(gname => 'scott_mg', master => 'beijing.test.com.cn', use_existing_objects => true, copy_rows => false, propAgation_mode => 'asynchronous');
Parameter Description:
GNAME master replication group name
Master adds another database of the primary copy node
Use_existing_Object true Indicates the database object that already exists with the primary copy node
COPY_ROWS FALSE indicates that it is not necessary to keep the primary copy node when copying the copy.
Propagation_Mode Executive Executive
2, confirm that the copying task queue has been added to the database data dictionary
SQL> SELECT * from user_jobs;
8. Change the status of the sync group by the quiesced) to normal (NORMAL)
1, log in to the Shenzhen database as a repadmin, run the following command
SQL> EXECUTE DBMS_REPCAT.RESUME_MASTER_ACTIVITY ('Scott_MG', FALSE);
2, confirm the status of the synchronization group is normal (Normal)
SQL> SELECT GNAME, MASTER, STATUS from DBA_REPGROUP
3, if this 1 command does not make the status of the sync group to normal (Normal), there may be some pauses, run the following command, try again (suggesting in an emergency):
SQL> EXECUTE DBMS_REPCAT.RESUME_MASTER_ACTIVITY ('Scott_MG', True);
9. Create a schedule for copying the database, we assume that the fixed timetable: 10 minutes copy once.
1, log in to the Shenzhen database as a repadmin, run the following command
SQL> Begin
DBMS_DEFER_SYS.SCHEDULE_PUSH (
Destination => 'beijing.test.com.cn',
Interval => 'sysdate 10/1440',
Next_date => sysdate;
END;
/
SQL> BegindBMS_DEFER_SYS.SCHEDULE_PURGE (
Next_date => SYSDATE,
Interval => 'sysdate 10/1440',
DELAY_SECONDS => 0,
Rollback_segment => ');
END;
/
2, log in to the beijing database as a repadmin, run the following command
SQL> Begin
DBMS_DEFER_SYS.SCHEDULE_PUSH (
Destination => 'shenzhen.test.com.cn',
Interval => 'sysdate 10/1440',
Next_date => sysdate;
END;
/
SQL> Begin
DBMS_DEFER_SYS.SCHEDULE_PURGE
Next_date => SYSDATE,
Interval => 'sysdate 10/1440',
DELAY_SECONDS => 0,
Rollback_segment => ');
END;
/
10, add or modify the record of the two-sided database, track the replication process
If you want to immediately see the change of the record of the database, you can find the job_number on both sides of the repadmin user, then run:
SQL> EXEC DBMS_Job.Run (job_number);
Third, the processing of abnormal conditions
1. Check whether the replication work is normal, you can query User_JOBS at the Repadmin user
SQL> SELECT JOB, this_DATE, NEXT_DATE, WHAT, BROKEN ASER_JOBS;
There are two normally:
Task - tei_date is empty, next_date is a time value after the current time
Task busy --th_date is not empty, next_date is a time value after the current time
There are two ways:
Task deadlock --next_date is a time value before the current time
Task deadlock - Next_date is a very big time value, for example: 4001-01-01
This may be due to the deadlocks illuminated by the network
Unlock the deadlock:
$ PS - EF | GREP ORE
Find the process number Ora_SNP * of the deadlock refresh snapshot, remove this process with the kill -9 command
Then enter the RepAdmin user SQL> operator, run the command:
SQL> EXEC DBMS_Job.Run (job_number);
Description: Job_number is a JOB number that uses SELECT JOB, this_DATE, NEXT_DATE, WHAT User_Jobs; command.
2, increase or reduce copy objects of replication groups
1, stop the copying action of the primary database node, make the status of the sync group by normal (Normal) to a pause (quiesced)
Log in to the Shenzhen database as a repadmin, run the following command
SQL> EXECUTE DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (gName => 'scott_mg');
2, add the database object in the replication group scott_mg to ensure that the database object must have a primary keyword.
SQL> execute dbms_repcat.create_master_repobject (sname => 'scott', oname => 'emp', type => 'table', use_existing_object => true, gname => 'scott_mg'); added to produce replication support SQL database objects > EXECUTE DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('Scott', 'EMP', 'TABLE'); 3, delete database objects in replication group scott_mg.
SQL> EXECUTE DBMS_REPCAT.DROP_MASTER_REPOBJECT ('Scott', 'DEPT', 'TABLE');
4, re-enable the status of the synchronization group to be changed to normal (NORMAL).
SQL> EXECUTE DBMS_REPCAT.RESUME_MASTER_ACTIVITY ('Scott_MG', FALSE);
