共计 2323 个字符，预计需要花费 6 分钟才能阅读完成。
1. 整体数据库配置
三个源端和一个 downstream 上游抽取数据库配置：
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、3 端数据库参数调整和配置：
1.db1,db2,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 ;
- 受权：
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;
个别我都是间接给：
grant connect,resource,dba to ogg;
- 建表：
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)
);
— 临时不插入数据，前面 ogg 过程配置结束后插入
begin
for v_count in 1..100000000 loop
insert into ogg.t2 values(v_count,’rose’,’hangzhou10′,’zhejiang’);
commit;
end loop;
end;
/
- 配置 tns 连贯信息三个 sourceDB 和一个上游抽取 db-mid 都要游戏代理配置
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)
)
)
- 设置本地归档门路和配置日志传输参数