1.环境源库: 192.168.18.51 ORACLE11.2.0.4 LINUX7.4 目标库: 192.168.18.61/62 ORACLE19.29 LINUX8.1 2.源端安装OGG安装OGG19.1.0.0.4 3.源端11G创建OGG空间和用户
(1).修改数据库配置
SQL> select supplemental_log_data_min from v$database;
SQL> alter database add supplemental log data;
SQL> select FORCE_LOGGING from v$database;
SQL> alter database force logging;
SQL> alter system switch logfile;
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH SID='*';
(2).创建ogg工作空间
-- 创建表空间
create tablespace ogg_tbs datafile size 200m autoextend off;
(3).创建 OGG 用户并授权
create user goldengate identified by goldengate default tablespace ogg_tbs quota unlimited on ogg_tbs;
-- 基础权限
grant connect,resource to goldengate;
grant alter any table to goldengate;
grant alter session to goldengate;
grant create session to goldengate;
grant flashback any table to goldengate;
grant select any dictionary to goldengate;
grant select any table to goldengate;
-- 高级权限
grant execute any type to goldengate;
grant select any transaction to goldengate;
grant create any table,create any sequence to goldengate;
grant insert any table to goldengate;
grant update any table to goldengate;
grant delete any table to goldengate;
grant create any index to goldengate;
grant unlimited tablespace to goldengate;
grant execute on dbms_flashback to goldengate;
grant comment any table to goldengate;
grant dba to goldengate;
-- 以下是被注释掉的命令（未执行）：
# exec dbms_goldengate_auth.grant_admin_privilege('goldengate') ;
# alter system set streams_pool_size='500M' scope=both;
4.源端创建表空间并导入测试数据
# 1. 创建目录对象（用sysdba执行） sqlplus / as sysdba create or replace directory MY_DUMP as '/home/oracle'; grant read,write on directory MY_DUMP to public; # 2. 执行导入 CREATE TABLESPACE ITPUX01 DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M; CREATE TABLESPACE ITPUX02 DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M; CREATE TABLESPACE ITPUX03 DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M; impdp system/oracle directory=MY_DUMP dumpfile=expdp_itpux_utf8.dmp logfile=imp.log
5.目标端安装OGG
安装OGG19.1.0.0.4 (如果长期使用需要安装在共享存储ACFS)
6.目标端 创建OGG空间和用户
#对于目标端，表空间只需要建在接收数据的 PDB 中，不需要在 CDB$ROOT 建
alter session set container=ITPUXPDB;
(1).修改数据库配置
SQL> select supplemental_log_data_min from v$database;
SQL> alter database add supplemental log data;
SQL> select FORCE_LOGGING from v$database;
SQL> alter database force logging;
SQL> alter system switch logfile;
SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH SID='*';
(2).创建ogg工作空间
-- 创建表空间
create tablespace ogg_tbs datafile '+DGDATA01' size 200m autoextend off;
(3).创建 OGG 用户并授权
create user goldengate identified by goldengate default tablespace ogg_tbs quota unlimited on ogg_tbs;
-- 基础权限
grant connect,resource to goldengate;
grant alter any table to goldengate;
grant alter session to goldengate;
grant create session to goldengate;
grant flashback any table to goldengate;
grant select any dictionary to goldengate;
grant select any table to goldengate;
-- 高级权限
grant execute any type to goldengate;
grant select any transaction to goldengate;
grant create any table,create any sequence to goldengate;
grant insert any table to goldengate;
grant update any table to goldengate;
grant delete any table to goldengate;
grant create any index to goldengate;
grant unlimited tablespace to goldengate;
grant execute on dbms_flashback to goldengate;
grant comment any table to goldengate;
grant dba to goldengate;
-- 以下是被注释掉的命令（未执行）：
# exec dbms_goldengate_auth.grant_admin_privilege('goldengate') ;
# alter system set streams_pool_size='500M' scope=both;
7.密码过期问题
alter profile default limit password_life_time unlimited;
8.配置源端管理进程
edit params mgr PORT 7809 DYNAMICPORTLIST 7810-7899 PURGEOLDEXTRACTS ./dirdat/*, usecheckpoints, minkeepdays 3 PURGEOLDEXTRACTS ./dirdat/EFG02/*, usecheckpoints, minkeepdays 3 PURGEOLDEXTRACTS ./dirdat/PFG02/*, usecheckpoints, minkeepdays 3 PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 ACCESSRULE, PROG SERVER, IPADDR *, PRI 1, ALLOW LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 AUTOSTART ER * AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3 start mgr
| 参数 | 说明 |
|---|---|
| PORT 7809 | Manager 进程监听端口 |
| DYNAMICPORTLIST 7810-7899 | 动态端口范围，用于数据传输 |
| PURGEOLDEXTRACTS | 清理旧的 trail 文件（保留3天） |
| PURGEDDLHISTORY | 清理 DDL 历史记录 |
| PURGEMARKERHISTORY | 清理标记历史记录 |
| ACCESSRULE | 访问规则（允许所有 IP） |
| LAGREPORTHOURS 1 | 每小时报告延迟 |
| LAGINFOMINUTES 30 | 30分钟报告信息级延迟 |
| LAGCRITICALMINUTES 45 | 45分钟报告严重级延迟 |
| **AUTOSTART ER *** | 自动启动所有 Extract 和 Replicat |
| AUTORESTART | 进程异常退出后自动重启（最多5次，间隔3分钟） |
9.源端安装DDL
#goldengate设置为管理用户 ggsci GGSCI>EDIT PARAMS ./GLOBALS GGSCHEMA goldengate #权限 grant execute on UTL_FILE to goldengate; -- 授予基本权限 grant restricted session to goldengate; -- 授予创建表和序列的权限 grant create table, create sequence to goldengate; -- 设置标记表（marker table） @marker_setup.sql goldengate -- 安装DDL复制环境 @ddl_setup.sql -- 设置DDL角色 @role_setup.sql -- 授予GGS角色给goldengate用户 GRANT GGS_GGSUSER_ROLE TO goldengate; -- 启用DDL复制 @ddl_enable.sql -- 执行dbmspool脚本（管理共享池） @$ORACLE_HOME/rdbms/admin/dbmspool.sql -- 固定DDL对象到内存 @ddl_pin.sql goldengate #设置检查点表 EDIT PARAMS ./GLOBALS CHECKPOINTTABLE goldengate.checkpoint #重新计入ggsci使参数生效 dblogin userid goldengate, password goldengate -- 以goldengate用户登录数据库 add checkpointtable goldengate.checkpoint -- 创建检查点表 #添加并查看心跳表 ADD HEARTBEATTABLE INFO HEARTBEATTABLE
10.配置目标端管理进程
PORT 7809 DYNAMICPORTLIST 7810-7899 PURGEOLDEXTRACTS ./dirdat/*, usecheckpoints, minkeepdays 3 PURGEOLDEXTRACTS ./dirdat/RFG02/*, usecheckpoints, minkeepdays 3 PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 accessrule, prog server, ipaddr *, pri 1, allow LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45 AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 3
11.目标端安装DDL(如果只作为目标端不需要安装DDL)
#goldengate设置为管理用户 ggsci GGSCI>EDIT PARAMS ./GLOBALS GGSCHEMA goldengate #安装DDL 由于不支持多租户模式不进行DDL的安装 cd /ogg sqlplus "/as sysdba" alter session set container=ITPUXPDB; grant execute on UTL_FILE to goldengate; grant restricted session to goldengate; grant create table, create sequence to goldengate; @marker_setup.sql goldengate @ddl_setup.sql @role_setup.sql GRANT GGS_GGSUSER_ROLE TO goldengate; @ddl_enable.sql @$ORACLE_HOME/rdbms/admin/dbmspool.sql @ddl_pin.sql goldengate #设置检查点表 EDIT PARAMS ./GLOBALS CHECKPOINTTABLE goldengate.checkpoint #重新计入ggsci使参数生效 dblogin userid goldengate@ITPUXPDB, password goldengate -- 以goldengate用户登录数据库 add checkpointtable goldengate.checkpoint -- 创建检查点表 #添加并查看心跳表 ADD HEARTBEATTABLE INFO HEARTBEATTABLE
12.源端抽取进程配置
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
cd /ogg
mkdir -p ./dirdat/EFG02
mkdir -p ./dirrpt/EFG02
ggsci
edit params EFG02
EXTRACT EFG02
SETENV (ORACLE_HOME ="/oracle/app/oracle/product/19c/db_1")
SETENV (ORACLE_SID = "itpuxdb")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID goldengate, PASSWORD goldengate
DBOPTIONS ALLOWUNUSEDCOLUMN
FETCHOPTIONS NOUSESNAPSHOT
WARNLONGTRANS 2h, CHECKINTERVAL 5m
DISCARDFILE ./dirrpt/EFG02/EFG02.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 02:30
REPORTCOUNT EVERY 1 MINUTES, RATE
EXTTRAIL ./dirdat/EFG02/ex
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA, REPORT
TABLE itpux01.*;
TABLE itpux02.*;
TABLE itpux03.*;
add extract EFG02,tranlog,begin now
add exttrail ./dirdat/EFG02/ex,extract EFG02,MEGABYTES 1024
13. 源端数据泵进程配置
cd /ogg mkdir -p ./dirdat/PFG02 mkdir -p ./dirrpt/PFG02 ggsci edit params PFG02 EXTRACT PFG02 SETENV (ORACLE_HOME ="/oracle/app/oracle/product/19c/db_1") SETENV (ORACLE_SID = "itpuxdb") SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") USERID goldengate, PASSWORD goldengate PASSTHRU RMTHOST 192.168.18.61, MGRPORT 7809 RMTTRAIL ./dirdat/RFG02/re DISCARDFILE ./dirrpt/PFG02/PFG02.dsc, APPEND, MEGABYTES 1024 DISCARDROLLOVER AT 02:30 TABLE itpux01.*; TABLE itpux02.*; TABLE itpux03.*; -- 先退出参数编辑，回到GGSCI命令行 -- 添加数据泵进程（注意拼写：exttrailsource） add extract PFG02, exttrailsource ./dirdat/EFG02/ex -- 添加远程trail文件 add rmttrail ./dirdat/RFG02/re, extract PFG02, megabytes 1024 -- 验证添加结果 info extract PFG02 info rmttrail ./dirdat/RFG02/re
14.目标端应用进程配置
cd /ogg mkdir -p ./dirdat/RFG02 mkdir -p ./dirrpt/RFG02 edit params RFG02 REPLICAT RFG02 -- 环境变量 setenv (ORACLE_HOME ="/oracle/app/oracle/product/19c/db_1") setenv (ORACLE_SID = "itpuxdb") setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) -- 数据库连接（使用加密密码，并指定PDB） userid goldengate@ITPUXPDB, password goldengate -- 丢弃文件管理 DISCARDFILE ./dirrpt/RFG02/RFG02.dsc, APPEND, MEGABYTES 1024 DISCARDROLLOVER AT 02:30 -- 错误处理 REPERROR DEFAULT, ABEND REPORTCOUNT EVERY 30 MINUTES, RATE -- 数据应用选项 ALLOWNOOPUPDATES ASSUMETARGETDEFS -- DDL复制配置 DDL INCLUDE MAPPED DDLOPTIONS REPORT -- 对所有DDL错误：重试3次，重试后仍失败则忽略 DDLERROR DEFAULT RETRYOP MAXRETRIES 3, IGNORE -- 数据映射 MAP itpux01.*, TARGET itpux01.*; MAP itpux02.*, TARGET itpux02.*; MAP itpux03.*, TARGET itpux03.*; 使用检查点表（推荐） add replicat RFG02, exttrail ./dirdat/RFG02/re, checkpointtable goldengate.checkpoint
15.模拟生产环境产生数据
如果源库创建新表需要执行下面命令添加附加日志 begin for i in 1..10000 loop insert into itpux01.itpux001 values(i); commit; dbms_lock.sleep(5); end loop; end; / 如果源库创建新表需要执行下面命令添加附加日志 add trandata 表名
16.使用expdp方式初始化数据
#目标端创建
alter session set container=itpuxpdb;
create public database link itpuxdb_dblink connect to system identified by oracle using 'itpuxdb11g';
select * from dual@itpuxdb_dblink;
select instance_name from v$instance@itpuxdb_dblink;
create directory itpuxbak as '/home/oracle';
grant read, write on directory itpuxbak to system;
grant create any directory to system;
CREATE TABLESPACE ITPUX01 DATAFILE '+DGDATA01' SIZE 10M AUTOEXTEND ON NEXT 10M;
CREATE TABLESPACE ITPUX02 DATAFILE '+DGDATA01' SIZE 10M AUTOEXTEND ON NEXT 10M;
CREATE TABLESPACE ITPUX03 DATAFILE '+DGDATA01' SIZE 10M AUTOEXTEND ON NEXT 10M;
#查询源端SCN
select current_scn from v$database;
#目标端执行初始化数据
impdp system/oracle@itpuxdb11g directory=itpuxbak version=11.2.0.4 \
NETWORK_LINK=itpuxdb_dblink \
flashback_scn=4302306 exclude=statistics parallel=4 cluster=no \
schemas=ITPUX01, ITPUX02, ITPUX03 \
logfile=expdp_itpuxdb.log logtime=ALL TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
#编译无效对象
@?/rdbms/admin/utlrp.sql
#收集统计信息
#disable 所有的trigger,cascading delete,check,job
set pagesize 2000
select 'alter table ' || owner || '.' || table_name ||
' DISABLE CONSTRAINT ' || constraint_name || ';'
from dba_constraints
where constraint_type = 'R'
and delete_rule = 'CASCADE'
and owner in ('ITPUX01', 'ITPUX02', 'ITPUX03');
select 'alter table ' || owner || '.' || table_name ||
' DISABLE CONSTRAINT ' || constraint_name || ';'
from dba_constraints
where constraint_type = 'C'
and owner in ('ITPUX01', 'ITPUX02', 'ITPUX03');
select 'alter trigger ' || owner || '.' || object_name || ' disable;'
from dba_objects
where object_type = 'TRIGGER'
and owner in ('ITPUX01', 'ITPUX02', 'ITPUX03');
select job, next_date, next_sec, failures, broken
from dba_jobs
where SCHEMA_USER in ('ITPUX01', 'ITPUX02', 'ITPUX03');
begin
sys.dbms_job.broken(job => 21, broken => true);
commit;
end;
17.开始进行OGG增量同步
#源端 start EFG02 start PFG02 info all view report EFG02 view report PFG02 #目标端 start RFG02, aftercsn 4302306 info all view report RFG02
