[ORACLE basics 2] synchronize table data across servers in oracle

[ORACLE basics 2] I am not familiar with cross-server table data synchronization in oracle, but I am now scheduled to perform two database tables for Synchronous replication. I don't understand it. I can only start to study and create it. If you read my production process like me, it will be of great help to you. If you don't talk nonsense, you may not be able to remember the number of times. You can use tianyao for a week .. Sweaty ..... I am very grateful to those who have published articles. I have read many articles before I can complete them. Thank you! Summary: The need to operate databases on the Internet often arises: the application of similar databases distributed across cities across the country is unified, and the data changes on one node are not only reflected locally, but also to the remote end. The replication technology provides users with a way to quickly access Shared data. Prerequisites: Prepare two testing machines and start to make the following: 2 machines install oracle10g. Note that my version is the same. All SID files are music11: Install and run vnc... For Remote Installation of oracle2: After installing and running oracle10.2. .. after completing the above steps, you can complete the basic work and start configuring advanced replication. Assume that the database machine is A database machine... database machine B... database machine A address; 192.168.1.205SID: music1 Domain Name: master.anymusic.com database machine B address; 192.168.1.226SID: music1 Domain Name: salse.anymusic.com configure to confirm that the two machines can access each other, as follows: machine: use oracle to log on and modify the following file; vi tnsnames. add: MUSIC226 = this name (DE. ION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.226) (PORT = 1521) Here is the ADDRESS of machine B and the database PORT number (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = music1) This name is the SID value of machine B database) after the configuration is complete, restart database machine B. Operation: use oracle to log on and modify the following file; vi tnsnames. add: MUSIC205 = this name (DE. ION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.205) (PORT = 1522) Here is the ADDRESS of machine A and the database PORT number (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = music1) the name is the SID value of machine A.) restart the database to test database connectivity. The following figure is displayed on machine A. [oracle @ localhost admin] $ tnsping music226; TNS Ping Utility for Linux: Version 10.2.0.1.0-Production. 04-JAN-2009 16: 53: 23 Copyright (c) 199 7, 2005, Oracle. all rights reserved. used parameter files:/home/oracle/product/10.2.0/network/admin/sqlnet. ora Used TNSNAMES adapter to resolve the aliasAttempting to contact (DE. ION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.226) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = music1 ))) OK (10 msec) test database connectivity, as shown in machine B: [oracle @ localhost admin] $ tnsping music205; TNS Ping Utility for Linux: Version 10.2.0.1.0-Production. 04-JAN-2009 17: 09: 04 Copyright (c) 1997,200 5, Oracle. all rights reserved. used parameter files:/home/oracle/product/10.2.0/network/admin/sqlnet. oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DE. ION = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.205) (PORT = 1522) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = m Usic1) OK (0 msec) Note: Here, my two machines have no firewall or other security measures, and the database has not set a restricted address to log on. If you cannot connect, take a closer look at your other configurations. Next, perform the following operations on machine A: View v $ option, if Advanced replication is TRUE, the Advanced replication function is supported; otherwise, select * from v $ option is not supported; the default result of the preceding operations is TRUE, supports advanced replication. Check the global_name parameter SQL> show parameter global_name; name type value =----------- ------------------------------ global_names boolean FALSE. I have read most articles and set this to true. I have not changed it. The default VALUE is false, view the default global_name, database domain name SQL> select * from global_name; GLOBAL_NAME--------------------------------------------------------------------------------MUSIC1.REGRESS.RDBMS.DEV.US.ORACLE.COM modify global_name, Data Database domain name SQL> alter database rename global_name to master.anymusic.com; view modification results; SQL> select * from global_name; GLOBAL_NAME--------------------------------------------------------------------------------MASTER.ANYMUSIC.COM create account and data: SQL> create user wcms identified by abc123 default tablespace users; SQL> grant connect, resource to wcms; Switch to wcms, SQL> conn wcms/abc123 create table SQL> create table test (id number, name varcha R2 (20), constraint test_id_pk primary key (id); (the primary key must be required); insert data SQL> insert into test values (1, 'abc '); SQL> insert into test values (2, 'def '); Create repadmin, the user managing database replication, and grant permissions. 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 revoke ('repadmin'); SQL> execute dbms_repcat_admin.grant_admin_any_schema (username => 'repadmin'); SQL> grant comment any table to repadmin; SQL> gran T lock any table to repadmin; SQL> grant select any dictionary to repadmin; use repadmin to create a database link and connect to SQL> conn repadmin/repadminSQL> create database link "salse.anymusic.com" connect to repadmin identified by repadmin using 'music226; salse.anymusic.com changed the value of global_name to music226: tnsnames. in ora, I set the database connection value to machine B. Check that SQL> select owner, db_link, host from all_db_links; OWNER DB_LINK HOST REPADMIN SAL SE. ANYMUSIC. COM music226 perform the following operations on machine B. If the value of v $ option and Advanced replication is TRUE, Advanced replication is supported. Otherwise, select * from v $ option is not supported; the above check results are true by default and support advanced replication. View global_name parameter SQL> show parameter global_name; name type value values ----------- -------------------------------- global_names boolean FALSE view default global_name, database domain name SQL> select * from global_name; GLOBAL_NAME--------------------------------------------------------------------------------MUSIC1.REGRESS.RDBMS.DEV.US.ORACLE.COM modify global_name, database domain name SQL> alter database rename global _ Name to salse.anymusic.com; view modification results; SQL> select * from global_name; GLOBAL_NAME--------------------------------------------------------------------------------SALSE.ANYMUSIC.COM create account and data: SQL> create user wcms identified by abc123 default tablespace users; SQL> grant connect, resource to wcms; Switch to wcms, SQL> conn wcms/abc123 create table SQL> create table test (id number, name varchar2 (20), constraint test_id_pk primary ke Y (id); (the primary key must be required); insert data SQL> insert into test values (1, 'zhao'); SQL> insert into test values (2, 'yong'); configure copy management user SQL> create user repadmin identified by repadmin default tablespace users; create a user repadmin who manages database replication and grant permissions. 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 revoke ('repadmin'); SQL> execute dbms_repcat_admin.grant_admin_any_schema (username => 'repadmin'); SQL> grant comment any table to repadmin; SQL> gran T lock any table to repadmin; SQL> grant select any dictionary to repadmin; use repadmin to create database link to connect to SQL> create database link "master.anymusic.com" connect to repadmin identified by repadmin using 'music205 '; Note: master.anymusic.com modifies the value of global_name to music205: is the above tnsnames. in ora, I set the database connection value of machine A. Check that: SQL> select owner, db_link, host from all_db_links; SQL> select owner, db_link, host from all_db_links; OWNER D B _LINK HOST--------------------------------------------------------------------------------REPADMIN MASTER. ANYMUSIC. COM music205 Test start login machine A test database link: SQL> select * from global_name@salse.anymusic.com; display: GLOBAL_NAME--------------------------------------------------------------------------------SALSE.ANYMUSIC.COM indicates database to this connection successfully login machine B test database link: SQL> select * from global_name@master.anymusic.com; display: GLOBAL_NAME --- ----------------------------------------------------------------------------- MASTER. ANYMUSIC. COM indicates that the connection to the database is successful. Log on to database machine A and start the replication implementation process. log on to the database using repadmin to create the master replication group rep_anymusic. You can name SQL> execute dbms_repcat.create_master_repgroup ('rep _ anymusic '); view information about the replication subject group: SQL> select gname, master, status from dba_repgroupGNAME M STATUS ---------------------------------------- REP_ANYMUSIC Y QUIESCEDQUIESCED indicates that the master group has not started running. Add the replication object SQL> execute dbms_repcat.create_master_repobject (sname => 'wcms ', oname => 'test', type => 'table', use_existing_object => true, gname => 'rep _ anymusic ', copy_rows => true); sname implements database replication with the user name oname. The database object name type implements database replication with the database object type. (Supported categories: tables, indexes, synonyms, triggers, views, processes, functions, packages, and package bodies) use_existing_object true: use the database object gname that already exists on the master replication node. The master replication group name copy_rows true indicates that the first replication start is consistent with the master replication node to view information about the replication objects in the master replication group: SQL> select sname, oname, status, gname from dba_repobjectSNAME oname status GNAME------------------------------WCMS test valid REP_ANYMUSIC for replication object generation copy support: SQL> execute dbms_repcat.generate_replication_support ('wcms ', 'test ', 'table'); (Note: generate database triggers and packages that support test TABLE replication under wcms users) View the information about the Copied object again: SQL> select sname, oname, status, gname from dba_repobjectSNAME oname status GNAME------------------------------WCMS test valid REP_ANYMUSICWCMS TEST $ rp valid REP_ANYMUSIC Add a database master site for the replication master group: SQL> execute transform (gname => 'rep _ anymusic ', master => 'salse .anymusic.com', use_existing_objects => true, copy_rows => true, propagation_mode => 'synchro Nous '); gname: Another database in which the master node is added to the master replication node, use_existing_object true, indicates that the database object copy_rows false, which already exists on the master replication node, indicates that the first copy operation does not need to be consistent with the master replication node. asynchronously view the replication site information: SQL> select gname, dblink, masterdef, master from dba_repsitesGNAME DBLINK M MREP_ANYMUSIC MASTER. ANYMUSIC. com y YREP_ANYMUSIC SALSE. ANYMUSIC. com n y start the replication process: SQL> execute dbms_repcat.resume_master_activity ('rep _ anymusic ', true); view information about the replication subject group again: SQL> select gna Me, master, status from dba_repgroupGNAME m status ready---------- REP_ANYMUSIC y normal check that the copied task queue has been added to the database data dictionary SQL> select job, log_user, this_date, next_date, next_sec, broken, failures, what from user_jobs; JOB LOG_USER THIS_DATE NEXT_DATE NEXT_SEC B FAILURES WHAT 21 REPADMIN 05-JAN-09 14:23:42 N 0 values ('"REP_ANYMUSIC"', FALSE); note: job: the ID of the current job _ User: The operator this_date of the current job: indicates whether the current job is working and whether the job is idle. Next_date: indicates the next execution date of the job. next_sec: indicates the next execution time of the job. broken: indicates whether the job is suspended. failures: indicates the number of failed jobs. what: the statement should be a work execution statement. Note: the status of the replication subject group changes from QUIESCED to NORMAL. Pause (quiesced) normal (normal) Here we log on to machine B with repadmin. The result is as follows: Correct SQL> select job, log_user, this_date, next_date, next_sec, broken, failures, what from user_jobs; JOB LOG_USER THIS_DATE NEXT_DATE NEXT_SEC B FAILURES WHAT21 REPADMIN 05-JAN-09 14:52:04 N 0 records ('"REP_ANYMUSIC"', FALSE); so far, this replication process has been completed. You can perform related data operations to test and start the test: Use wcms to log on to machine A and insert data SQL> insert into test values (4, 'aaaaaaaaa') in table test '); view results SQL> select * from test; id name ---------- ------------------ 4 aaaaaaaa log on to machine B database with wcms to view data in table test, because I have only performed data synchronization on table test SQL> select * from test; id name ---------- -------------------- 4 aaaaaaaa if the data to be synchronized is not seen, I will wait for it again. Because only one table is synchronized above, insert the synchronized table in the existing group again, log on to the database with wcms, and create the table SQL> create table aa (id number, name varchar2 (20), constraint aa_id_pk primary key (id); then insert data SQL> insert into aa values (1, 'abc '); log on to the database with repadmin, add a new table, and then execute the synchronization operation SQL> execute dbms_repcat.suspend_master_activity ('rep _ anymusic '); cancel the suppression. Stop the subject group SQL> execute dbms_repcat.create_master_repobject (sname => 'wcms ', oname => 'A', type => 'table', use_existing_object => true, gname => 'rep _ anymusic ', copy_rows => true); add a new table to the group. SQL> execute dbms_repcat.generate_replication_support ('wcms ', 'A', 'table. SQL> execute dbms_repcat.resume_master_activity ('rep _ anymusic ', true); start the process and log on to machine B with the wcms account. SQL> select * from aa; id name ---------- -------------------- 1 abc log on to machine A to create A database copy schedule. The role is to synchronize the tables added to the master group, including, add, and delete tables, create a new table and table data. My personal opinion is as follows. If you know the specific functions of the following push tasks, please let me know .. Add a PUSH task SQL> begin 2 dbms_defer_sys.schedule_push (3 destination => 'salse. ANYMUSIC. COM ', 4 interval => 'sysdate + 100', run 5 next_date => sysdate every 10 minutes, the next run time is the current time 6 delay_seconds => 50, delay seconds: 50 7 parallelism => 1); 8 end; 9/If the following error occurs, check whether the syntax is correct. The error is shown in line 3rd, no single quotation marks have been added to row 3. Haha ERROR at line 3: ORA-06550: line 3, column 16: PLS-00201: identifier 'salse. ANYMUSIC 'must be declaredORA-06550: line 2, column 1: PL/SQL: Statement ignoredERROR at line 1: ORA-23319: parameter value "07-JAN-09" is not appropriateORA-06512: at "SYS. DBMS_DEFER_SYS ", line 2346ORA-01403: no data foundORA-06512: at line 2 add PURGE task SQL> begin 2 dbms_defer_sys.schedule_purge (3 next_date => sysdate, 4 inte Rval => 'sysdate + 10/1440 ', 5 delay_seconds => 0); ### 6 rollback_segment => 0); 7 end; 8/SQL> select job, what from user_jobs; job what 21 dbms_repcat.do_deferred_repcat_admin ('"REP_ANYMUSIC"', FALSE); 41 declare rc binary_integer; begin rc: = sys. dbms_defer_sys.push (destination => 'salse. ANYMUSIC. COM '); end; 42 declare rc binary_integer; begin rc: = sys. dbms_defer_sys.purge (delay_seconds => 0, rollba Ck_segment => '0'); end; log on to machine B: log on to the database with repadmin, and add the following code: SQL> begin 2 dbms_defer_sys.schedule_push (3 destination => 'master .anymusic.com ', 4 interval => 'sysdate + 100', 5 next_date => sysdate, 6 delay_seconds => 50, delay seconds 50 7 parallelism => 1); 8 end; 9/SQL> begin 2 dbms_defer_sys.schedule_purge (3 next_date => sysdate, 4 interval => 'sysdate + 100', 5 delay_seconds => 0 ); ### 6 rollback_segment => 0); 7 End; 8/SQL> select job, what from user_jobs; JOB WHAT should 21 dbms_repcat.do_deferred_repcat_admin ('"REP_ANYMUSIC"', FALSE); 41 declare rc binary_integer; begin rc: = sys. dbms_defer_sys.push (destination => 'master. ANYMUSIC. COM '); end; 42 declare rc binary_integer; begin rc: = sys. dbms_defer_sys.purge (delay_seconds => 0, roll Back_segment => '0'); end; OK !!!! So far, the synchronous replication operation has been completed. The following are some practical commands .. If there is anything wrong with the above, please specify ....... ####################################### Use select * from dba_repgroup; if the site status is NORMAL, the site is NORMAL. Related views "DBA_REPSITES ---- copy site information" DBA_REPGROUP ----- copy group information "DBA_REPOBJECT ---- copy Object Information" DBA_REPCATLOG --- synchronization logs ############## ####################### if you want to delete a synchronization table, run the following: SQL> execute dbms_repcat.suspend_master_activity ('rep _ anymusic '); stop the subject group SQL> EXECUTE Dbms_Repcat.Drop_Master_Repobject ('wcms', 'test', 'table '); delete the synchronization table SQL> EXECUTE Dbms_Repcat.Remove_Master_Databases ('rep _ anymusic ', 'Master .anymusic.com'); Delete the master site Click the group SQL> execute dbms_repcat.resume_master_activity ('rep _ anymusic ', true ); start a replication group ##################################### # Delete the private data link and log on to the database using repadmin EXECUTE Dbms_Repcat.Drop_Master_Repgroup ('subject group name '); delete the subject group drop database link master.anymusic.com; Delete the db link name Delete jobsselect job, what from user_jobs; query job numbersEXECUTE Dbms_Job.Remove (2 ); enter the queried job numbers ############################### ###### delete a REPADMIN user, note: Required You must first Delete jobs and group mygrp belonging to the repadmin user under the site to log on to EXECUTE Dbms_Defer_Sys.Unregister_Propagator (username => 'repadmin') with sys '); EXECUTE Dbms_Repcat_Admin.Revoke_Admin_Any_Schema (username => 'repadmin'); drop user repadmin cascade; ####################################### -- delete log on to the drop public database link master.anymusic.com using sys; ###################################### remarks: after running the repcat package, you should execute a commit command because some rep stored procedures do not It is also a troubleshooting. Generally, rep scripts return results quickly. If a command does not return results after a long time, it is likely that the preceding command does not have commit, canceling the current command, making a commit, and then executing it again can solve the problem. ####################################### Force delete the replication group SQL> Truncate table system. def $ _ aqcall; SQL> Exec dbms_repcat.drop_master_repgroup (gname => 'mygrp ', all_sites => true ); ########################################