1. Overall database configuration: three source terminals and one downstream 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_DA TA_ALL,force_logging
from v$database;
Source DB1, 2, 3 database parameters adjustment and configuration: 1. Create tablespace and user of DB1, DB2, DB3 database
Create tablespace ogg datafile ‘xxx. DBF’ size 1g autoextend on next 1g;
Alter tablespace ogg add datafile ‘xxx. DBF’ size 1g autoextend on next 1g;
create user ogg account identified by oracle ;
- 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;
- Build tables:
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)
);
Do not insert data until the OGG process is configured
begin
for v_count in 1.. 100000000 loop
insert into ogg.t2 values(v_count,’rose’,’hangzhou10′,’zhejiang’);
commit;
end loop;
end;
/
- Configure TNS connection information for three sourceDB and one downstream extraction DB-mid to the Game Agent configuration
dba1 =
(DESCRIPTION =
CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.249.104)(PORT = 21521)) (CONNECT_DATA = (SERVER = DEDICATED dba1) ))
dba2 =
(DESCRIPTION =
CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.249.104)(PORT = 21521)) (CONNECT_DATA = (SERVER = DEDICATED dba2) ))
dba3 =
(DESCRIPTION =
CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.249.104)(PORT = 21521)) (CONNECT_DATA = (SERVER = DEDICATED dba3) ))
mid =
(DESCRIPTION =
CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.147.249.105)(PORT = 21521)) (CONNECT_DATA = (SERVER = DEDICATED mid) ))
- Set the local archive path and configure the log transport parameters