The global_name of the three databases is as follows:
Orcl16.com 192.168.100.16 (primary database, that is, data source)
Orcl8.com 192.168.100.8 (copy database)
Dtgdb.com 192.168.100.150 (copy database)
Set initialization parameters for the three databases:
Global_names = true
The Job_queue_process value must be greater than 0.
Open_links = 4 or greater depends on the number of connections
Requirements: the data and structure of the indexes must be the same for the scott schema tables of the three databases.
Application User: scott
Run the following command to modify the workgroup on each site:
Alter database rename global_name TO orcl16.Com;
Create a main site: orcl16.com
Connect system/xl1100@100.16 ------ use system user link 100.16
Create user repadmin identified by repadmin; ------ CREATE a copy administrator repadmin account
BEGIN ------ assign permissions to the repadmin user
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
Username => 'repadmin ');
END;
/
Grant dba to repadmin
Grant comment any table to repadmin;
Grant lock any table to repadmin;
Grant select any dictionary to repadmin;
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
Username => 'repadmin ');
END;
/
CONNECT repadmin/repadmin@100.16 --------- use repadmin User Login 100.16
BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
Next_date => SYSDATE,
Interval => 'sysdate + 100', ---------- the sysdate + 1/24 interval of tasks added to PURGE is one hour.
Delay_seconds => 0 );
END;
/
Set orcl8.com
Connect system/xl1100@100.8
Create user repadmin identified by repadmin;
BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
Username => 'repadmin ');
END;
/
Grant comment any table to repadmin;
Grant lock any table to repadmin;
Grant select any dictionary to repadmin;
BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
Username => 'repadmin ');
END;
/
Establish database links (select * from global_name@orcl8.com can be used to check whether data links are accessible)
100.16:
Connect system/xl1100@100.16
Create public database link orcl8.com USING 'orcl8'; ---------- orcl8 is the name configured in the TNS file.
Create public database link dtgdb.com USING 'dtgdb ';
CONNECT repadmin/repadmin@100.16
Create database link orcl8.com connect to repadmin identified by repadmin;
Create database link dtgdb.com connect to repadmin identified by repadmin;
100.8:
Connect system/xl1100@100.8
Create public database link orcl16.com USING 'orcl16 ';
Create public database link dtgdb.com USING 'dtgdb ';
CONNECT repadmin/repadmin@100.8
Create database link orcl16.com connect to repadmin identified by repadmin;
Create database link dtgdb.com connect to repadmin identified by repadmin;
100.150:
Connect system/xl1100@100.150
Create public database link orcl16.com USING 'orcl16 ';
Create public database link orcl8.com USING 'orcl8 ';
CONNECT repadmin/repadmin@100.150
Create database link orcl16.com connect to repadmin identified by repadmin;
Create database link orcl8.com connect to repadmin identified by repadmin;
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
Gname => 'mygrp '); ---------- create a group name named mygrp
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
Gname => 'mygrp ', ---------- group name
Type => 'table', ---------- type (TABLE type here)
Oname => 'emp', ---------- table (the table to be copied under the sname user)
Sname => 'Scott ', ---------- user (User participating in the copy)
Use_existing_object => TRUE,
Copy_rows => FALSE );
END;
/
---------- How many tables need to be copied write more than one statement
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
Gname => 'mygrp ',
Type => 'table ',
Oname => 'dept ',
Sname => 'Scott ',
Use_existing_object => TRUE,
Copy_rows => FALSE );
END;
/
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
Gname => 'mygrp ',
Type => 'table ',
Oname => 'A ',
Sname => 'Scott ',
Use_existing_object => TRUE,
Copy_rows => FALSE );
END;
/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
Sname => 'Scott ', -------------- User Name
Oname => 'emp', -------------- name of the table to be copied
Type => 'table', -------------- type
Min_communication => TRUE );
END;
/
------------ How many tables involved in the replication should be written?
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
Sname => 'Scott ',
Oname => 'dept ',
Type => 'table ',
Min_communication => TRUE );
END;
/
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
Sname => 'Scott ',
Oname => 'A ',
Type => 'table ',
Min_communication => TRUE );
END;
/
-- After the preceding execution, you can use select * from dbs_repcatlog to view the synchronization status.
Manage several request statuses in the DBA_REPCATLOG View:
1) READY: indicates that the request is READY to be executed. If it remains in this status for a long time, you can manually execute the DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN stored procedure to execute the request.
2) AWAIT_CALLBACK: this status only appears on the subject-defined site, indicating that it is waiting for other main sites to execute the request and return the result.
3) ERROR: indicates the request execution ERROR.
4) DO_CALLBACK: this status only appears on a non-subject-defined site, indicating the result of the request to be sent to the subject-defined site.
The following script is used to solve the problem that jobs fail more than 16 times after the status changes to broken due to network problems (each site must run)
Create or replace procedure change_job_broken is
BEGIN
Declare
CURSOR my_broken_jobs is ------ create a CURSOR
SELECT job FROM user_jobs WHERE broken = 'y'; ------ check whether the broken status of user_jobs is "Y"
Begin
FOR broken_job IN my_broken_jobs LOOP
BEGIN
Dbms_job.broken (broken_job.job, FALSE); ------- if the broken status is "Y", change it to "N" through this line"
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
End loop;
END;
End;
/
Then, you can create a job to run the preceding script once a day.
Variable n number;
Begin
Dbms_job.submit (: n, 'change _ job_broken; ', sysdate, 'sysdate + 1 ');
Commit;
Dbms_job.run (: n );
Commit;
End;
/
Start copying
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
Gname => 'mygrp ');
END;
/
After all the work is complete, start replication and use select * from dba_repgroup on each site. If the site status is NORMAL, the site is NORMAL.
Related View
"DBA_REPSITES-copy site information
"DBA_REPGROUP ----- copy group information
"DBA_REPOBJECT ---- copying objects
"DBA_REPCATLOG --- synchronization logs
---- Delete JOBS
Select job, what from user_jobs; ---------- query job numbers
EXECUTE Dbms_Job.Remove (2); ------ enter the queried job numbers in brackets.
EXECUTE Dbms_Job.Remove (3 );
-- Delete a REPADMIN user. Note: you must first Delete jobs and group mygrp belonging to the repadmin user under the site to delete the user (that is, you must run the preceding DELETE command before using the following command)
Connect system/xl1100@100.16
Note: You should execute a commit every time you run the repcat package, because some rep stored procedures do not
Automatically commit, and this is also a troubleshooting. Generally, rep scripts will return results quickly,
If no result is returned after a command for a long time, it is likely that the preceding command does not have a commit and is canceled when
The previous command, then make a commit, and then re-execute, generally can solve the problem.
Force delete a replication group
SQL> Truncate table system. def $ _ aqcall;
SQL> Exec dbms_repcat.drop_master_repgroup (gname => 'mygrp ', all_sites => true );
There are still a lot of content related to advanced replication, such as solutions to conflicts in Advanced Replication. We can discuss it later.
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
