GoldenGate downstream configuration

Time:2021-8-3

1. Overall database configuration
Three source side and one downstream extraction database configuration:

alter system set enable_goldengate_replication=TRUE;

Alter database add supplemental log data;

Alter database add supplemental log data(primary key,unique index) columns;

Alter database add supplemental log data(all) columns;

Alter database force logging;

select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,force_logging

from v$database;

2. Source db1, 2 and 3 database parameter adjustment and configuration:
1. Create tablespaces and users from three libraries: db1, DB2 and db3

create tablespace ogg datafile ‘ xxxxx.dbf ’ size 1g autoextend on next 1g;

alter tablespace ogg add datafile ‘ xxxx.dbf ’ size 1g autoextend on next 1g;

create user ogg account identified by oracle ;

  1. to grant authorization:

begin

dbms_goldengate_auth.grant_admin_privilege(grantee=>’OGG’,

GRANT_SELECT_PRIVILEGES=>TRUE,

PRIVILEGE_TYPE=>’CAPTURE’,

DO_GRANTS=>TRUE);

END;

/

EXEC DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(‘OGG’);

EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(‘OGG’);

GRANT SELECT ON V_$DATABASE TO OGG ;

GRANT EXECUTE ON OGG.UPDATESEQUENCE TO OGG;

GRANT EXECUTE ON OGG.REPLICATESEQUENCE TO OGG;

GRANT SELECT ON SYS.SEQ$ TO OGG;

GRANT SELECT ON SYS.USER$ TO OGG;

GRANT SELECT ON SYS.OBJ$ TO OGG;

GRANT SELECT ON DBA_SEQUENCES TO OGG;

GRANT CONNECT, RESOURCE TO OGG;

GRANT CREATE SESSION, ALTER SESSION TO OGG;

GRANT SELECT ANY DICTIONARY, SELECT ANY TABLE TO OGG;

GRANT ALTER ANY TABLE TO OGG;

GRANT FLASHBACK ANY TABLE TO OGG;

GRANT EXECUTE ON DBMS_FLASHBACK TO OGG;

GRANT SELECT ON DBA_CLUSTERS TO OGG;

GRANT SELECT ANY TRANSACTION TO OGG;

I usually give it directly to:

grant connect,resource,dba to ogg;

  1. Table creation:

create table ogg.t1 as select OBJECT_ID,OWNER,Owww.walajiao.comBJECT_NAME,CREATED from dba_segments ;

create table ogg.t2 (

pid number(20) primary key not null ,

product varchar2(40),

location varchar2(40),

produce varchar2(40)

);

–The data will not be inserted for the time being, and will be inserted after the configuration of the Ogg process is completed

begin

for v_count in 1..100000000 loop

insert into ogg.t2 values(v_count,’rose’,’hangzhou10′,’zhejiang’);

commit;

end loop;

end;

/

  1. Configure TNS connection information, three sourcedb and one downstream extraction DB midGame agentto configure

dba1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.249.104)(PORT = 21521))

(CONNECT_DATA =

  (SERVER = DEDICATED)

  (SERVICE_NAME = dba1)

)

)

dba2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.249.104)(PORT = 21521))

(CONNECT_DATA =

  (SERVER = DEDICATED)

  (SERVICE_NAME = dba2)

)

)

dba3 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.249.104)(PORT = 21521))

(CONNECT_DATA =

  (SERVER = DEDICATED)

  (SERVICE_NAME = dba3)

)

)

mid =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.249.105)(PORT = 21521))

(CONNECT_DATA =

  (SERVER = DEDICATED)

  (SERVICE_NAME = mid)

)

)

  1. Set the local archive path and configure log transfer parameters