Advanced Replication allows you to propagate changes among 2 or more databases. Each database is called a Master Destination database and 1 database is called a Master Definition database.
This worked example, tested in Oracle 9, sets it up in 2 databases.
The first is referred to as DB1.WORLD in the tnsnames.ora file and has a global name of NODE1.WORLD. This database is the Master Definition database as well as being one of the Master Destination databases.
The second is referred to as DB2.WORLD in the tnsnames.ora file and has a global name of NODE2.WORLD. This database is one of the Master Destination databases.
This post explains how to set up Advanced Replication in each Master Destination database:
The first few parts are run as user SYS:
SQL> conn / as sysdba
Connected.
SQL>
Before you can use Advanced Replication, you need to check that it has been installed in each database involved. If it has, the database will have a table called SYSTEM.REPCAT$_REPCAT:
SQL> desc system.repcat$_repcat
Name Null? Type
-------------------------- -------- ------------------
GOWNER NOT NULL VARCHAR2(30)
SNAME NOT NULL VARCHAR2(30)
MASTER VARCHAR2(1)
STATUS NUMBER(38)
SCHEMA_COMMENT VARCHAR2(80)
FLAVOR_ID NUMBER
FLAG RAW(4)
SQL>
It will also have several objects with names beginning with REPCAT in the SYSTEM schema:
SQL> select count(*) from dba_objects
2 where owner = 'SYSTEM'
3 and substr(object_name,1,6) = 'REPCAT'
4 /
COUNT(*)
----------
143
SQL>
If Advanced Replication has not been installed, you must run $ORACLE_HOME/rdbms/admin/catrep.sql. However, this should not be necessary for Oracle 9 and above as catproc.sql now calls catrep.sql.
Parameter job_queue_processes must be >= 1 otherwise no jobs will run. You can change it without bouncing the database if necessary:
SQL> select value from v$parameter
2 where name = 'job_queue_processes'
3 /
VALUE
--------------------
0
SQL> alter system set job_queue_processes = 1
2 scope = both
3 /
System altered.
SQL> select value from v$parameter
2 where name = 'job_queue_processes'
3 /
VALUE
--------------------
1
SQL>
Some sites suggest that _job_queue_interval needs to be <= the smallest job interval. Other sites say that this does not apply for Oracle 9 or above. Either way, I am going to leave it alone because:
- I no longer use any version below Oracle 9.
- This is a hidden parameter, which should not be changed without speaking to Oracle first.
- It is measured in seconds and looks as if it is small enough already:
SQL> select ksppinm, ksppstvl
2 from x$ksppi a, x$ksppsv b
3 where a.indx = b.indx
4 and ksppinm like '_job%'
5 /
KSPPINM KSPPSTVL
------------------------- ---------------
_job_queue_interval 5
SQL>
Parameter global_names must be set to true. It can be changed without bouncing the database:
SQL> select value from v$parameter
2 where name = 'global_names'
3 /
VALUE
--------------------
FALSE
SQL> alter system set global_names = true
2 scope = both
3 /
System altered.
SQL> select value from v$parameter
2 where name = 'global_names'
3 /
VALUE
--------------------
TRUE
... and you need to ensure that the global name is correct:
SQL> alter database rename global_name to node1.world
2 /
Database altered.
SQL> select global_name from global_name
2 /
GLOBAL_NAME
--------------------
NODE1.WORLD
SQL>
Each Master Destination database needs to be linked to every other Master Destination database with a public database link:
SQL> create public database link node2.world
2 using 'db2.world'
3 /
Database link created.
SQL>
You need an administrator to send and receive data:
SQL> create user replication_administrator
2 identified by replication_administrator
3 default tablespace user_data
4 temporary tablespace temporary_data
5 quota unlimited on user_data
6 /
User created.
SQL>
You allow the administrator to send data to other databases as follows:
SQL> execute dbms_defer_sys.register_propagator -
> (username => 'replication_administrator');
PL/SQL procedure successfully completed.
SQL>
... and you allow the administrator to receive data like this:
SQL> grant execute any procedure to replication_administrator
2 /
Grant succeeded.
SQL>
Next you allow the administrator to administer replication groups and schemas:
SQL> execute dbms_repcat_admin.grant_admin_any_repgroup -
> (userid => 'replication_administrator');
PL/SQL procedure successfully completed.
SQL> execute dbms_repcat_admin.grant_admin_any_schema -
> (username => 'replication_administrator');
PL/SQL procedure successfully completed.
SQL>
The administrator also needs to lock and comment tables:
SQL> grant lock any table, comment any table
2 to replication_administrator
3 /
Grant succeeded.
SQL>
The administrator does the remaining steps. First he creates private database links from each master destination database to all the others:
SQL> conn replication_administrator/replication_administrator
Connected.
SQL> create database link node2.world
2 connect to replication_administrator
3 identified by replication_administrator
4 using 'db2.world'
5 /
Database link created.
SQL>
Then a job is set up to send data to each master destination sites:
SQL> execute dbms_defer_sys.schedule_push -
> (destination => 'node2.world', -
> next_date => sysdate+1/24/60, -
> interval => 'sysdate+1/24/60', -
> stop_on_error => false, -
> delay_seconds => 0, -
> parallelism => 1);
PL/SQL procedure successfully completed.
SQL>
Finally a job is set up to delete data after it has been successfully sent:
SQL> execute dbms_defer_sys.schedule_purge -
> (next_date => sysdate+1/24/60, -
> interval => 'sysdate+1/24/60');
PL/SQL procedure successfully completed.
SQL>
No comments:
Post a Comment