Evolution of Oracle replication methods

The following article mainly introduces the Oracle replication method in detail, which involves the connection of public databases during the running process. It requires three database connections for each replication, and the global name of the database specified by the public database connection.

Set the materialized view site:

1. Create a replication administrator and grant the following permissions:

 
 

  
  
disconnect;  
  
  
connect system/passwd@BJ;  
  
  
create user mvadmin identified by  
  
  
mvadmin;  
  
  
begin  
  
  
dbms_repcat_admin.  
  
  
grant_admin_any_schema(username=> ’mvadmin’);  
  
  
end;  
  
  
/  
  
  
grant comment any table to mvadmin;  
  
  
grant lock any table to mvadmin;  
  
  
grant select any dictionary to mvadmin;  
 
 

Registered advertiser:

 
 

  
  
begin  
  
  
dbms_defer_sys.reGISter_propagator(  
  
  
username => ’mvadmin’);  
  
  
end;  
  
  
/  
 
 

Public database connection. Three database connections are required for each replication. The global name of the specified database for public database connection:

 
 

  
  
create public database link ORACLSH using ’Oracle.shanghai.  
  
  
com’;  
 
 

The Using clause is followed by the global database name or connection string.

 
 

  
  
create public database link ORACLSH using ’(description=  
  
  
(address=(protocol=tcp)(host=127.0.0.1)(port=1521))  
  
  
(connect_data=(service_name=oracl)))’   
 
 

Create a job to clear delayed transaction queue scheduling:

 
 

  
  
disconnect;  
  
  
connect mvadmin/mvadmin@BJ;  
  
  
begin  
  
  
dbms_defer_sys.schedule_purge(  
  
  
next_date => sysdate,  
  
  
interval => ’/*1:hr*/ sysdate + 1’,  
  
  
delay_seconds => 0,  
  
  
rollback_segment => ’’);  
  
  
end;   
 
 

Create a database connection for the Oracle replication administrator mvadmin:

 
 

  
  
create database link ORACLSH connect to proxy_bjOracle  
  
  
identified by proxy_bjoralce  
  
  
Connect to ... Identified by ...  
 
 

Clause specifies which user is used to connect to the remote database

Create an Oracle replication and scheduling database connection job:

 
 

  
  
begin  
  
  
dbms_defer_sys.schedule_push(  
  
  
destination => ’ora92zjk’,interval => ’/*1:hr*/ 
sysdate + 1’,  
  
  
next_date => sysdate,stop_on_error => false,  
  
  
delay_seconds => 0,parallelism => 0);  
  
  
end;  
  
  
/  
 
 

Grant the shyml user (corresponding to the shyml solution) the corresponding permissions to create the Materialized View:

 
 

  
  
disconnect;  
  
  
connect system/passwd@BJ;  
  
  
grant alter session to crm;  
  
  
grant create cluster to crm;  
  
  
grant create database link to crm;  
  
  
grant create sequence to crm;  
  
  
grant create session to crm;  
  
  
grant create synonym to crm;  
  
  
grant create table to crm;  
  
  
grant create view to crm;  
  
  
grant create procedure to crm;  
  
  
grant create trigger to crm;  
  
  
grant unlimited tablespace to crm;  
  
  
grant create type to crm;  
  
  
grant create any snapshot to crm;  
  
  
grant alter any snapshot to crm;  
 
 

The above content is an introduction to the Oracle replication method, and I hope you will get some benefits.