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. 1. Prerequisites for realizing database replication 1. Database Support Advanced Replication capabilities 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 the database link and the connected database name. Today's global database name: DB_NAME "." DB_DOMAIN 3, with Database JOB execution related parameters job_queue_processes = 1 job_queue_interval = 60 distributed_transactions = 10 open_links = 4 The first line defines the starting number of SNP processes N. 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 implementing the database synchronization copy assumes that we have two databases on the Internet: a shenzhen, a beijing. See the table below:
Database name shenzhen beijing database domain name TEST.COM.CN TEST.COM.CN Database SID number SHENZHEN BEIJING LISTENER port number 1521 1521 server IP address 10.1.1.200 10.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 = (protocol = TCP) (host = 10.1.1.200)) (connect_data = Service_name = beijing)) Run $ tnsping beijing 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. ①, log in shenzhen database system status SQL> alter database rename global_name to shenzhen.test.com.cn; Log in beijing with the system database status: SQL> alter database rename global_name to beijing.test.com.cn; ②, with system status Sign in SHENZHEN Database SQL> CREATE PUBLIC DATABASE LINK BEIJING.TEST.COM.CN USING 'BEIJING' Test.com is right. Log in to the beijing database as SQL> create public database link Shenzhen '; test database global name and public database link SQL> Select * from global_name@shenzhen.test.com.cn; return The result is right for shenzhen.test.com.cn. 3. Establish a user repymin, and empower. ①, log in shenzhen database system status 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; 2, log in to the beijing database as the system, run the above command, manage the user replication user repymin, and empower. 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 as a repadmin database 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; return The result is right for beijing.test.com.cn. 2, log in to the beijing database as the reference database, 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; return results It 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. ①, log in shenzhen with the internal database identity, and empower users to create scott 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, Run the following SQL command Add: SQL> ALTER TABLE DEPT ADD (constraint dept_deptno_pk primary key); 4, the serial number of the primary key is created under the Shenzhen database Scott User, which avoids the conflict of Beijing. SQL> CREATE SEQUENCE DEPT_NO INCREMENT BY 1 START WITH 1 MaxValue 44 CYCLE NOCACHE; (Note: MaxValue 44 can be fixed according to the number of bits defined by the application and table structure ") 5, insertion of initialization data at the SHENZHEN database Scott User SQL> INSERT INTO Dept Values (dept_no.ness "; sql> INSERT INTO Dept Values (de PEPT_NO.NEXTVAL, 'Research', 'Dallas'); SQL> Commit; 6, in the beijing database There are also more than 1, 2, 3 7, and create the serial number of the primary key under the Beijing database Scott user, scope to avoid conflicts with Shenzhen.
SQL> create sequence dept_no increment by 1 start with 45 maxvalue 99 cycle nocache; ⑧, initialization data is inserted at beijing SQL database user scott> insert into dept values (dept_no.nextval, 'sales', 'chicago'); SQL> insert INTO Dept Values (dept_no.nextval, 'Operations', 'Boston'); SQL> Commit; 6, create group scott_mg to copy, add database objects, generate object copy support 1, log in to the shenzhen database as a repadmin database, create the master Copy Group Scott_MG SQL> EXECUTE DBMS_REPCAT.CREATE_MASTER_REPGROUP ('Scott_MG'); Description: The Scott_MG group name can be freely named according to the user's needs.
②, was added to the replication group scott_mg database objects SQL> execute dbms_repcat.create_master_repobject (sname => 'scott', oname => 'dept', type => 'table', use_existing_object => true, gname => 'scott_mg') Parameter Description: Sname Implementing the Database Copy User Name Oname Implementing the Database Object Name (Download Length in 27 bytes, the program package name is within 24 bytes) TYPE Database Clices ( Supported categories: table, index, synonym, trigger, view, process, function, package, program case) USE_EXISTING_OBJECT TRUE indicates that the database object GNAME master replicates group name 3, and the database object is copied. Support SQL> EXECUTE DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT ('Scott', 'DEPT', 'TABLE'); (Description: Generate database triggers and packages supporting Scott User DEPT tables) 4, confirm that the copied group and object have been added data Dictionary SQL database> select gname, master, status from dba_repgroup; SQL> select * from dba_repobject; 7, create a master copy node ①, log in shenzhen database with repadmin identity, 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 Main Copy Group name Master Add the primary copy node Another database USE_EXISTING_OBJECT TRUE indicates that the database object COPY_ROWS FALSE exists with the primary copy node indicates that the first start copy is not used asynchronously, and the primary copy node is not executed asynchronously. Recupied task queue has been added to the database's data dictionary SQL> Select * from user_jobs; 8, make the status of the sync group by the quiesced) to normal (Normal) 1, log in to the Shenzhen database as the repadmin, run the following command SQL> EXECUTE DBMS_REPCAT.RESUME_MASTER_AACTIVITY ('Scott_MG', FALSE); 2 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 replicate 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> begin dbms_defer_sys.schedule_purge (next_date => sysdate, interval => 'sysdate 10/1440', delay_seconds => 0, rollback_segment => ''); end; / ②, log in beijing database with repadmin identity 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 10/1440 ', delay_seconds => 0, rollback_segment =>'); END; / 10, add or modify the record of the two-sided database, track the copy process If you want to immediately See the change of the record of the database after adding or modifying the database, you can find the job_number of Push on both sides, run: SQL> EXEC DBMS_Job.Run (job_number); three, abnormal situation processing 1, check the copy work is normal, You can query user_jobs SQL> SELECT JOB, this_DATE, NEXT_DATE, WHAT, BROKEN from User_Jobs; normal status is two: Task --th_date is empty, NEXT_DATE is a time value task after the current time - t His_date is not empty, there are two time values after NEXT_DATE for the current time: task deadlock --next_date is a time value for a time value before the current time - Next_Date is a very large one time value, for example: 4001-01-01 This may be derived because of the deadlock of the deadlock in the network: $ PS-EF | GREP ORE Locate the deadlock of the refresh snapshot process number Ora_SNP *, delete this process with the kill -9 command and then enter Repadmin User SQL> Operation, Run command: SQL> EXEC DBMS_Job.Run (Job_Number); Description: Job_Number is the JOB number with SELECT JOB, this_DATE, NEXT_DATE, WHAT User_Jobs; command.
