How to synchronize two databases in Oracle (using the materialized view) (oracle snapshot instance)

How to synchronize two databases in Oracle (using the materialized view) (oracle snapshot instance) I. Technical implementation details unless otherwise specified, the following SQL commands are run under the SYSETM user of the database ora_db2. Assume that you want to copy (or synchronize) all user db1 tables in database ora_db1 on the other server. 1. Create a database connection (dblink) 1.1 used to connect to database 1 (ora_db1). Only by creating a snapshot log of the table specified by db1 can you perform a quick refresh in the snapshot. SQL> select 'create snapshot log on' | table_name | ';' from user_tables; -- Obtain the create snapshot statement of the User table, as follows: create snapshot log on table 1; appendix Delete table snapshot log: SQL> select 'drop snapshot log on' | substr (table_name, INSTR (table_name, '$ _') + 2, length (table_name )) | ';' from user_tables where table_name like '% MLOG $ _ %'; -- the statement for deleting the user table snapshot log obtained above is as follows: drop snapshot log on table 1; 1.2 SQL> CREATE PUBLIC DATABASE LINK testLK CONNECT TO db1 identified by db1 using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521 ))) (CONNECT_DATA = (service_name = ora_db1) '; ** for security reasons, you can use a private data connection. 2. Create a tablespace named Snapshot_ts to store snapshots and create a user named db2 associated with the tablespace. SQL> CREATE TABLESPACE snapshot_ts DATAFILE 'd: \ db \ snapshot_ts.dbf 'size 30 M DEFAULT STORAGE (INITIAL 30 K NEXT 15 K MINEXTENTS 1 MAXEXTENTS 100 PCTINCREASE 0) ONLINE PERMANENT; SQL> create user db2 identified by db2 default tablespace snapshot_ts; SQL> GRANT CONNECT, RESOURCE TO db2; you can roughly estimate the size of the tablespace snapshot_ts by using the following SQL statement in the ora_db1 database as the db1 user. SQL> SELECT SUM (bytes) FROM USER_SEGMENTS WHERE SEGMENT_NAME IN (select table_name from user_tables); 3. run the following script to generate a snapshot script for creating the db1 user code table on the ora_db1 Database: Note: run the following select statement under db1 and run the create_snapshot. SQL script in db2. SQL> spool d: \ snap \ create_snapshot. SQL> SELECT 'create SNAPSHOT db2. '| TABLE_NAME | 'pctfree 10 PCTUSED 40 TABLESPACE snapshot_ts' | 'Storage (initial' | INITIAL_EXTENT | 'Next' | NEXT_EXTENT | 'pctincrease 0) '| 'as SELECT * FROM db1.' | TABLE_NAME | '@ testLK;' FROM USER_TABLES; SQL> spool off; note that the script used to generate the required table snapshot has some limitations. If the table to generate the snapshot contains a long column, 'select * 'will not work here, the preceding SQL script does not To automatically create a script to generate the required snapshot, you must explicitly add a long column name in the select list to create a table snapshot. The following is an example. If the note type of a column in table 1 on which we want to create a snapshot is long, we need to write the following snapshot creation script separately: SQL> CREATE SNAPSHOT db2.table1 PCTFREE 10 PCTUSED 40 TABLESPACE snapcost_ts STORAGE (INITIAL 40960 NEXT 57344 PCTINCREASE 0) AS SELECT * FROM db1.table1 @ testLK where TABLE_NAME not like '% $ _ %'; 4. run the script file create_snapshot. SQL created in step 1 to CREATE all snapshots. The create_snapshot. SQL script file contains the following code: CREATE SNAPSHOT db2. table name PCTFREE 10 PCTUSED 40 TABLESPACE sna Pshot_ts STORAGE (INITIAL 163840 NEXT 57344 PCTINCREASE 0) as select * FROM db1.table1 @ testLK where TABLE_NAME not like '% $ _ %'; after running the script file create_snapshot. SQL, the required snapshot is created in the snap mode. The next step is to consider how to refresh the snapshot. For snapshot refresh, you can use some desktop DBA tools to refresh snapshots or use the system package dbms_snapshot.refresh to refresh a snapshot: manual refresh method 1 SQL> begin DBMS_SNAPSHOT.REFRESH ('snapshot name '); // or 'user. snapshot name 'end; manual refresh method 2 EXEC DBMS_SNAPSHOT.REFRESH ('snapshot name'); // or 'user. snapshot name 'or EXEC DBMS_SNAPSHOT.REFRESH ('table 1', 'F'); // The first parameter is the snapshot name, and the second parameter F is the quick refresh C is the full refresh. 5. create a scheduled refresh process to regularly refresh the snapshot: Alter snapshot db2. table name 1 refresh fast Start with sysdate + 1/1440 next sysdate + 1/144; (this SQL statement means: set oracle to automatically within 1 minute (1/24*6 0) then perform the first quick refresh, And then refresh every 10 minutes (10/24*60 .) Or SQL> CREATE OR REPLACE PROCEDURE sp_snapshot_refresh IS BEGIN DBMS_REFRESH.MAKE (NAME => 'snapshot name', LIST => 'snap. table 1, 'snap. table 2', 'snap. table 3', NEXT_DATE => TRUNC (SYSDATE + 1) + 2/24, INTERVAL => '(SYSDATE + 1)', IMPLICIT_DESTROY => FALSE, LAX => TRUE); END; /SQL> EXECUTE sp_snapshot_refresh creates a scheduled task to regularly refresh the snapshot at every morning. Run the following SQL statement to view the task you just joined. SQL> SELECT JOB, WHAT FROM DBA_JOBS; 6. private synonym for creating a snapshot under user db3: SQL> CREATE SYNONYM db3. table 1 FOR db2. table 1; 7. the db2 User grants the select permission for the snapshot to the db3 user. SQL> GRANT SELECT ON table 1 TO db3; similarly, create a code table snapshot and a scheduled refresh task at location 3 (ora_db2) and location 4 (ora_db3) at location 1 (ora_db1. In this way, you can maintain the code table at location 1 and use the code table at location 2, 3, and 4. In the following SQL statement, UserB of Location 2 (ora_db2) browses the code table in location 1 (ora_db1. SQL> SELECT * FROM table 1; 2. Daily maintenance will fail if network connection problems occur at any time. These error messages can be found in the alert log File. The following describes how to solve this problem: 1. first, find the task number SQL> SELECT JOB, what FROM DBA_JOBS; 2. delete the task SQL> EXECUTE DBMS_JOB.REMOVE (JOBNO); 3. delete a snapshot group SQL> drop snapshot 1; 4. re-create a snapshot group and re-schedule the task to regularly refresh the snapshot SQL> EXECUTE sp_snapshot_refresh 5. You can monitor the SQL> SELECT NAME, TO_CHAR (last_refresh, 'dd-MON-YY HH: MM: ss') FROM DBA_SNAPSHOTS; supplement: -- 1 modify SESSION time format alter session set NLS_DATE_FORMAT = ''yyyy-MM-DD HH24: MI: Ss''; -- 2. view the snapshot last refresh time select name, LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES; -- 3. view the next snapshot execution time select last_date, next_date, what from user_jobs order by next_date; -- 4. print the debugging information dbms_output.put_line (''use ''| ''plsql''); --- 5. if you only want one-way synchronization, create the following triggers in the target database (when the source database table changes, the target database table changes, but the target database table changes, the source database table does not change ). create or replace trigger TRI_test_user_AFR after insert or update or delete on sn_test_user for each rowbegin if Deleting then delete from test_user where id =: old. id; end if; if inserting then insert into test_user (id, name) values (: new. id,: new. name); end if; if updating then update test_user set name =: new. name where id =: old. id; end if; end TRI_test_user_AFR; -- 6. if you want two-way synchronization, perform the first six steps in the source database and create the following triggers on both sides. (when the source database table changes, the target database table changes accordingly, when the target database table changes, the source database table also changes.) create or replace trigger BST114.TRI _ TEST_USER_AFRAFTER DELETE OR INSERT Or updateon BST114.SN _ TEST_USER referencing new as new old as oldfor each ROWdeclare tmp_id number (10): =-1; begin dbms_output.put_line ('beginin ''); if inserting then -- select id into tmp_id from test_user where id =: new. id; for p in (select id from test_user where id =: new. id) loop tmp_id: = p. id; end loop; dbms_output.put_line (tmp_id | ''===------------''); if (tmp_id =-1) then insert into test_user (id, na Me, age) values (: new. id,: new. name,: new. age); end if; if updating then dbms_output.put_line (''updated'); for p in (select name, age from test_user where id =: old. id) loop if (p. name! =: New. name) or (p. age! =: New. age) then update test_user set name =: new. name, age =: new. age where id =: old. id; end if; end loop; end if; if deleting then dbms_output.put_line (''deleted''); delete from test_user where id =: old. id; end if; dbms_output.put_line (''end''); end TRI_test_user_AFR; -- to prevent endless loops of two-way synchronization triggers, add some judgments to the triggers to prevent endless loops. -- above synchronization principle 1. create a dblink to access the remote database. create a local snapshot to map the remote data table. When the remote data table changes, it will be reflected in the snapshot. 3. because snapshots are similar to visual charts, a trigger is created locally for a snapshot. When the snapshot changes, the corresponding event is triggered. 4. write the data synchronization code in the trigger. -- Appendix: the snapshot refresh time parameter specifies the number of seconds in a day = 24 hours * 60 minutes * 60 notes, so if you want to refresh the bill after 30 seconds, the parameter should be written as sysdate + 30/(24*60*60) 1 minute = sysdate + 60/(24*60*60) the number of minutes in a day = 24 hours * 60 minutes a minute can also be written in this way sysdate + 1/(24*60) 30 minutes = sysdate + 30/(24*60) 60 minutes = sysdate + 60/(24*60) and so on 1 hour = sysdate + 1/24 = sysdate + 60/(24*60) 1 day = sysdate + 1 month = sysdate + 30