Oracle GoldenGate复制与容灾实施手册

1.GoldenGate 文件系统-文件系统

1.1 安装OGG软件(源端-目标端)

1.2 创建OGG用户(源端-目标端)

源端

CREATE TABLESPACE ogg_tbs
	DATAFILE '/u01/oradata/orcl/ogg_tbs.dbf'
		SIZE 100 M
	    AUTOEXTEND ON NEXT 10 M MAXSIZE 500 M
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;


CREATE USER goldengate IDENTIFIED BY goldengate DEFAULT TABLESPACE ogg_tbs TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;

GRANT CONNECT,RESOURCE TO GOLDENGATE;
GRANT CREATE SESSION TO GOLDENGATE;
GRANT ALTER SESSION TO GOLDENGATE;
GRANT ALTER ANY TABLE TO GOLDENGATE;
GRANT ALTER SYSTEM TO GOLDENGATE;
GRANT CREATE TABLE TO GOLDENGATE;
GRANT INSERT ANY TABLE,UPDATE ANY TABLE,DELETE ANY TABLE,LOCK ANY TABLE TO GOLDENGATE;
GRANT SELECT ANY TRANSACTION TO GOLDENGATE;
GRANT SELECT ANY DICTIONARY TO GOLDENGATE;
GRANT FLASHBACK ANY TABLE TO GOLDENGATE;
GRANT UNLIMITED TABLESPACE TO GOLDENGATE;
GRANT EXECUTE on DBMS_FLASHBACK TO GOLDENGATE;
GRANT EXECUTE on DBMS_GOLDENGATE_AUTH TO GOLDENGATE;
GRANT DBA TO GOLDENGATE;
EXEC dbms_goldengate_auth.grant_admin_privilege('GOLDENGATE','*',TRUE)

目标端

CREATE TABLESPACE ogg_tbs
		DATAFILE '/u01/oradata/itpux/ogg_tbs.dbf'
		SIZE 100 M
AUTOEXTEND ON NEXT 10 M MAXSIZE 500 M
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER goldengate IDENTIFIED BY goldengate DEFAULT TABLESPACE ogg_tbs TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;

GRANT CONNECT,RESOURCE TO GOLDENGATE;
GRANT CREATE SESSION TO GOLDENGATE;
GRANT ALTER SESSION TO GOLDENGATE;
GRANT ALTER ANY TABLE TO GOLDENGATE;
GRANT ALTER SYSTEM TO GOLDENGATE;
GRANT CREATE TABLE TO GOLDENGATE;
GRANT INSERT ANY TABLE,UPDATE ANY TABLE,DELETE ANY TABLE,LOCK ANY TABLE TO GOLDENGATE;
GRANT SELECT ANY TRANSACTION TO GOLDENGATE;
GRANT SELECT ANY DICTIONARY TO GOLDENGATE;
GRANT FLASHBACK ANY TABLE TO GOLDENGATE;
GRANT UNLIMITED TABLESPACE TO GOLDENGATE;
GRANT EXECUTE on DBMS_FLASHBACK TO GOLDENGATE;
GRANT EXECUTE on DBMS_GOLDENGATE_AUTH TO GOLDENGATE;
GRANT DBA TO GOLDENGATE;
EXEC dbms_goldengate_auth.grant_admin_privilege('GOLDENGATE','*',TRUE)

1.3 创建测试用户(源端-目标端)

源端

CREATE  TABLESPACE "KYLE"
DATAFILE '/u01/app/oracle/oradata/orcl/kyle01.dbf'
    SIZE 100 M
    AUTOEXTEND ON NEXT 10 M MAXSIZE 500 M
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER "KYLE"
PROFILE "DEFAULT" 
IDENTIFIED BY "kyle" 
DEFAULT TABLESPACE "KYLE" 
TEMPORARY TABLESPACE "TEMP" 
ACCOUNT UNLOCK;

--测试数据
SELECT table_name FROM user_tables;

CREATE SEQUENCE seq_kyle START WITH 1 INCREMENT BY 1;
CREATE TABLE kyle01 (id INT PRIMARY KEY, rand INT , name VARCHAR2(30));
CREATE TABLE kyle02 (id INT PRIMARY KEY, rand INT , name VARCHAR2(30));
CREATE TABLE kyle03 (id INT PRIMARY KEY, rand INT , name VARCHAR2(30));

INSERT INTO kyle01 VALUES(1,1,'Kyle01');
INSERT INTO kyle01 VALUES(2,2,'Kyle02');
INSERT INTO kyle01 VALUES(3,3,'Kyle03');
INSERT INTO kyle01 VALUES(4,4,'Kyle04');

DECLARE
rnd number(9,2);
BEGIN
for i in 1..20000 loop
 IF MOD(i,2000)=0 THEN
 	commit;
 ELSE
 	insert into kyle03 values(seq_kyle.nextval,i*dbms_random.value,'Kyle Is Testing');
 END IF;
END LOOP;
END;
/

BEGIN
LOOP
delete from kyle03 where rownum=1;
 commit;
 insert into kyle03 values(seq_kyle.nextval,200000*dbms_random.value,'MACLEAN IS UPDATING');
 commit;
	 insert into kyle03 values(seq_kyle.nextval,300000*dbms_random.value,'MACLEAN IS UPDATING');
	 commit;
	update kyle03 set rand=rand+10 where rownum=1;
	commit;
 dbms_lock.sleep(1);
 END loop;
END;

/

目标端

CREATE  TABLESPACE "KYLE"
DATAFILE '/u01/oradata/itpux/kyle01.dbf'
    SIZE 100 M
    AUTOEXTEND ON NEXT 10 M MAXSIZE 500 M
LOGGING
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER "KYLE"
PROFILE "DEFAULT" 
IDENTIFIED BY "kyle" 
DEFAULT TABLESPACE "KYLE" 
TEMPORARY TABLESPACE "TEMP" 
ACCOUNT UNLOCK;

1.4 数据导入导出

create directory bakdir as '/home/oracle';
grant read,write on directory bakdir to system;
grant create any directory to system;

--导出结构
CREATE DIRECTORY BAKDIR AS '/home/oracle';
GRANT READ,WRITE ON DIRECTORY BAKDIR TO SYSTEM;
GRANT CREATE ANY DIRECTORY TO SYSTEM;
--导出某个或多个schema
expdp system/jia  DIRECTORY=bakdir DUMPFILE=expdp_schema_kyle.dmp LOGFILE=expdp_schema_kyle.log SCHEMAS=kyle
impdp system/jia  DIRECTORY=bakdir DUMPFILE=expdp_schema_kyle.dmp LOGFILE=expdp_schema_kyle.log TABLE_EXISTS_ACTION=truncate SCHEMAS=kyle
scp /home/oracle/expdp_schema_kyle.dmp orcl-122:/home/oracle/

1.5 配置环境变量

alias sqlplus="rlwrap sqlplus"
alias ggsci="rlwrap ggsci"
alias rman="rlwrap rman"
alias asmcmd="rlwrap asmcmd"

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=/ggs:$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
OGG_PATH=/u01/ggs export OGG_PATH
PATH=.:$PATH:$OGG_PATH:$HOME/bin:$ORACLE_BASE/product/11.2.0/db_1/bin:$ORACLE_HOME/bin; export PATH

1.6 修改系统参数开启归档

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
alter database add supplemental log data;
alter database force logging;

1.7 配置OGG(源端)

1.创建目录
```
create subdirs
```

2.配置MGR

GGSCI>
EDIT PARAMS mgr
PORT 7809
AUTOSTART ER * 
AUTORESTART ER *, RETRIES 3, WAITMINUTES 3, RESETMINUTES 15
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2
*/
--端口 7809
--自动启动 ER(EXTARCT REPLACT)

3.配置检查点

--GLOBALS储存了运行的一些信息
GGSCI> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE goldengate.checkpoint
GGSCI> dblogin userid goldengate,password goldengate
GGSCI> ADD CHECKPOINTTABLE goldengate.checkpoint

4.添加补充日志

--ADD TRANDATA itpux.*
ADD SCHEMATRANDATA kyle
--配置ddl 的时候，一定要用ADD SCHEMATRANDATA
--如果不用ddl,可以用ADD TRANDATA
info SCHEMATRANDATA kyle

5.配置extract进程

--建立EXTRACT目录
mkdir -p ./dirdat/rkyle
mkdir -p ./dirrpt/rkyle
mkdir -p ./dirdat/ekyle
mkdir -p ./dirrpt/ekyle

GGSCI>
EDIT PARAMS ekyle

setenv(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
setenv(ORACLE_SID="itpux")
EXTRACT ekyle
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA,REPORT
USERID goldengate, PASSWORD goldengate
EXTTRAIL ./dirdat/ekyle/ex
TRANLOGOPTIONS excludeuser goldengate
TRANLOGOPTIONS convertucs2clobs
WARNLONGTRANS 12h,CHECKINTERVAL 30m
DISCARDFILE ./dirrpt/ekyle/ekyle.dsc, APPEND, MEGABYTES 200
TABLE kyle.*;

--设置语言
--设置SID
--设置EXTRACT名称不能操作过8个字符
--抽取所有的DDL操作
--
--定义OGG使用用户
--设置抽取目录
--排除用于管理抽取的OGG用户
--某某参数，可以传输大字段
--设置告警阈值
--配置文件存放位置
--抽取的表

--添加一个抽取进程
--THREADS 2表示源端有2个RAC节点
ADD EXTRACT ekyle, TRANLOG, BEGIN NOW
ADD EXTRACT ekyle, TRANLOG, BEGIN NOW, THREADS 2
--添加一个队列文件
GGSCI>
ADD EXTTRAIL ./dirdat/ekyle/ex, EXTRACT ekyle, MEGABYTES 200

--启动停止测试
GGSCI>
START ekyle
STOP ekyle
VIEW REPORT ekyle

6.配置PUMP进程

cd /u01/ggs
mkdir -p ./dirdat/ekyle
mkdir -p ./dirrpt/ekyle

GGSCI>
EDIT PARAMS pkyle

setenv(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
setenv(ORACLE_SID="orcl")
EXTRACT pkyle
USERID goldengate,PASSWORD goldengate
PASSTHRU						
RMTHOST 172.17.0.122,MGRPORT 7809
RMTTRAIL ./dirdat/rkyle/re
DISCARDFILE ./dirrpt/rkyle/rkyle.dsc, APPEND, MEGABYTES 200
TABLE ekyle.*;

--
--
--
-- 禁止extract与数据库交互，适合于PUMP传输进程
-- 远端的IP,端口
-- 指定写入到远程目标端的哪个队列
-- 指定报错输出文件
-- 指定传输表


--增加pump 进程(指定本地trail 文件)
GGSCI>
ADD EXTRACT pkyle,EXTTRAILSOURCE ./dirdat/ekyle/ex

--增加rmttail 文件
GGSCI>
ADD RMTTRAIL ./dirdat/rkyle/re, EXTRACT pkyle, MEGABYTES 200
--检查:
GGSCI>
INFO  pkyle

1.8 配置OGG(目标端)

1.配置mgr

GGSCI> EDIT PARAMS MGR
PORT 7809
AUTOSTART ER * 
AUTORESTART ER *, RETRIES 3, WAITMINUTES 3, RESETMINUTES 15
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 2
*/
GGSCI>
START MGR
STOP MGR

2.配置检查点

GGSCI> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE goldengate.checkpoint
GGSCI> dblogin userid goldengate,password goldengate
GGSCI> ADD CHECKPOINTTABLE goldengate.checkpoint

3.配置replicat进程

cd /u01/ggs
mkdir -p ./dirdat/rkyle
mkdir -p ./dirrpt/rkyle

ggsci> edit params rkyle

setenv(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
setenv(ORACLE_SID="itpux")
REPLICAT rkyle
USERID goldengate,PASSWORD goldengate
handlecollisions
assumetargetdefs
DISCARDFILE ./dirrpt/rkyle/rkyle.dsc, APPEND, MEGABYTES 200
MAP kyle.*, target kyle.*;

--添加replicat 进程
GGSCI>
ADD REPLICAT rkyle EXTTRAIL ./dirdat/rkyle/re, CHECKPOINTTABLE goldengate.checkpoint
--启动相关进程(目标)
GGSCI>
START rkyle
VIEW REPORT rkyle
INFO ALL
--启动相关进程(源端)
GGSCI>
START rkyle
START pkyle
VIEW REPORT rkyle
VIEW REPORT pkyle
INFO ALL

1.9 disable 目标库所有的trigger、cascading delete、check、job

SET PAGESIZE 2000
SET LINESIZE 100
--Foreign key Constraints/Cascading Deletes
SELECT    'alter table '
       || owner
       || '.'
       || table_name
       || ' DISABLE CONSTRAINT '
       || constraint_name
       || ';'
  FROM dba_constraints
 WHERE     constraint_type = 'R'
       AND delete_rule = 'CASCADE'
       AND owner IN ('KYLE',
                     'KYLE01');


--查找生成并disable 约束(Check)：
SELECT    'alter table '
       || owner
       || '.'
       || table_name
       || ' DISABLE CONSTRAINT '
       || constraint_name
       || ';'
  FROM dba_constraints
 WHERE     constraint_type = 'C'
       AND owner IN ('KYLE',
                     'KYLE01');

--查找生成并disable trigger：
SELECT 'alter trigger ' || owner || '.' || object_name || ' disable;'
  FROM dba_objects
 WHERE     object_type = 'TRIGGER'
       AND owner IN ('KYLE',
                     'KYLE01');


--查找并disable job
SELECT job,
       next_date,
       next_sec,
       failures,
       broken
  FROM dba_jobs
 WHERE SCHEMA_USER IN ('KYLE',
                       'KYLE01');

BEGIN
    sys.DBMS_JOB.broken (job => 21, broken => TRUE);
    COMMIT;
END;


--生成结果如下，然后在目标数据库中执行：
alter table ... DISABLE CONSTRAINT SYS_C0017353;
alter table ... DISABLE CONSTRAINT SYS_C0017355;…
…
alter trigger ... disable;
alter trigger ... disable;
alter trigger ... disable;
--确认外键已经被禁用
SELECT OWNER,
       CONSTRAINT_NAME,
       CONSTRAINT_TYPE,
       STATUS
  FROM dba_CONSTRAINTS
 WHERE     CONSTRAINT_TYPE = 'R'
       AND status = 'ENABLED'
       AND owner IN ('KYLE',
                     'KYLE01');
--确认目标端目标表的主键可用:
SELECT T1.STATUS,
       T1.VALIDATED,
       T2.status,
       T1.constraint_name,
       T1.owner
  FROM dba_constraints T1, dba_objects T2
 WHERE     T2.OBJECT_NAME = T1.constraint_name
       AND T1.OWNER IN ('KYLE',
                        'KYLE01');

--验证job 确实被禁用
SELECT job,
       LOG_USER,
       PRIV_USER,
       SCHEMA_USER,
       broken
  FROM dba_jobs
 WHERE schema_user IN ('KYLE',
                       'KYLE01');

SELECT OWNER, JOB_NAME, STATE
  FROM DBA_SCHEDULER_JOBS
 WHERE OWNER IN ('KYLE',
                 'KYLE01');

--确认trigger 已经全部关闭
SELECT DISTINCT status
  FROM dba_triggers
 WHERE owner IN ('KYLE',
                 'KYLE01');

2,.添加DDL功能与加密

2.1 添加DDL功能

1.关闭所有OGG进程
```
STOP ekyle
STOP pkyle
STOP rkyle
```

2.用户的默认表空间不能自动SYSTEM表空间

set PAGRSIZE 200
set LINESIZE 200
col USERNAME format A20
col DEFAULT_TABLESPACE format A20 

SELECT username, default_tablespace FROM dba_users;

3.关闭回收站并清空回收站

--11G可以启动回收站，10G必须关闭回收站
ALTER SYSTEM SET recyclebin = off SCOPE = SPFILE;
PURGE DBA_RECYCLEBIN;

4.指明支持DDL的对象放在那个SCHEMA下载

ggsci> edit params ./GLOBALS
GGSCHEMA goldengate

5.添加相关权限

GRANT EXECUTE ON UTL_FILE TO goldengate;
GRANT RESTRICTED SESSION TO goldengate;
GRANT CREATE TABLE, CREATE SEQUENCE TO goldengate;
GRANT GGS_GGSUSER_ROLE TO goldengate;

6.执行脚本（源目标）

cd /ggs
sqlplus "/as sysdba"
@marker_setup.sql
@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

7.修改参数，增加DDL复制参数

ggsci >edit params eitpux01
--DDL INCLUDE OBJNAME "ITPUX01.*"
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA,REPORT

ggsci >edit params ritpux01
--DDL INCLUDE OBJNAME "ITPUX01.*"
DDL INCLUDE ALL
DDLERROR default ignore retryop

8.启动进程
```
START ekyle
START pkyle
START ryle
```

DDL其他功能

--开启
ddl_enable.sql
--关闭
ddl_disable.sql

--清空DDL trace ggs_ddl_trace.log
ddl_cleartrace.sql

--ddl 参数：
optype alert
objtype "table"
ojbname "user.tab*"
include mapped object "*";
exclude mapped object "itpux.itux*"
DDL 环境重配（删除就是1-5，12，去掉参数中的DDL）:
1.停止所有的OGG ex/pump/rp 进程
2.@ddl_disable.sql
3.@ddl_remove.sql
4.@marker_remove.sql
5.@marker_setup.sql
6.@ddl_setup.sql
7.@role_setup.sql
8.GRANT GGS_GGSUSER_ROLE TO goldengate;
9.@ddl_enable.sql
10.@$ORACLE_HOME/rdbms/admin/dbmspool.sql
11.@ddl_pin.sql goldengate
12.运行所有的OGG ex/pump/rp 进程

2.2 加密

1.生成加密的KEY

[oracle@orcl:/u01/ggs]$keygen 256 1
0x1673D6197E05DA1009B1451420A73134BDF868246D6AC878FBFC69193231C355

2.将KEY存到本地指定文件

[oracle@orcl:/u01/ggs]$cat ENCKEYS 
KYLE_KEY 0xE9E07156ACC2411F97E78D117B55C444E935E27A034CBD389CF2F432F8B5F417

3.设置登录用的的KEY


GGSCI (orcl) 109> ENCRYPT PASSWORD goldengate AES256 ENCRYPTKEY KYLE_KEY
Encrypted password:  AADAAAAAAAAAAAKABIXBCASDGBHCGFNGMHJIFFMDXFGBRCLHTGJGVIECPCGEUAJEXDSEFBUCGATGKGUGBGCCVFKBBENDYBSAPDZBACQCQILIRHYH
Algorithm used:  AES256

添加KEY到相应文件

extract eitpux01
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA,REPORT
userid goldengate,password AADAAAAAAAAAAAKACAHHWIRDTBQDNBUJYFUCXCGEVBCDHBCEGFOIQFSEGFKCBGLDPGUGAIIHZDJATJSJYBPBSJSJUFSBJCUBYCXBFBPJPDSBVGBJ,AES256,ENCRYPTKEY KYLE_KEY
exttrail ./dirdat/eitpux01/ex
tranlogoptions excludeuser goldengate
tranlogoptions convertucs2clobs
warnlongtrans 12h,checkinterval 30m
discardfile ./dirrpt/eitpux01/eitpux01.dsc,append,megabytes 200
TABLE itpux.*;
--TABLE itpux01.*;
--TABLE itpux02.*;
--TABLE itpux03.*;