打开DDL记录参数enable_ddl_logging. 6
修改enable_goldengater_replication参数... 6
- GoldenGate环境调研
按要求填写调研表，以便GoldenGate工程师收集全面的信息。
GoldenGate软件调研
|
|
|
| GoldenGate安装用户 |
|
| 安装目录(新建) |
|
| 数据库用户(新建) |
|
| 数据库表空间(新建) |
|
| 初始化方案 |
|
| 网络传输端口 |
|
| 源端OGG版本 |
|
| 目标端OGG版本 |
|
| 是否选择支持DDL操作 |
|
| 需要同步复制的用户或表 |
|
| 每天产生的日志量 |
|
| 复制用户 |
|
| 其他特殊要求 |
|
默认所有用户均对应名字相同。
无需调整
备份策略需要调整，以免删除GoldenGate进程所需的归档。
GoldenGate文件系统要求
根据现有数据库，需要GoldenGate文件系统/ogg大小为100GB， 归档目录100GB，
要求都放在共享磁盘上，使用双机管理。
无要求
以下均为TCP端口，要求如下：
开放1521、7809、7840-7850
在GoldenGate复制迁移过程中尽量不做DDL操作，比如表字段修改等等，否则GoldenGate复制报错，或者配置DDL复制
开启库级附加日志，一般情况下只需要打开最小附加日志。
alter database add supplemental log data ;
alter database add supplemental log data (primary key, unique,foreign key) columns;
alter system switch logfile;
开启force logging强制日志模式
ALTER DATABASE FORCE LOGGING;
SELECT FORCE_LOGGING FROM V$DATABASE;
ALTER SYSTEM SWITCH LOGFILE;
OGG要求数据库归档打开
alter system set log_archive_dest_1='LOCATION=/arch' scope=both sid='*';
shutdown immediate;
startup mount
alter database archivelog;
alter database open;
alter system set control_file_record_keep_time=30 scope=both sid='*';
配置自动清理归档日志脚本：
vi /home/oracle/rm_arch.sh
source /home/oracle/.bash_profile
export ORACLE_HOME=/ora_data/u01/app/oracle/product/11.2.0/db_1
export NLS_DATE_FORMAT='YYYYMMDD HH24:MI:SS';
rman target / <<EOF
delete force noprompt archivelog until time "sysdate-2";
exit;
EOF
crontab -e
0 2 * * * /home/oracle/rm_arch.sh
enable_ddl_logging用于记录所有DDL操作
alter system set enable_ddl_logging=true scope=both sid='*';
OGG软件安装
压缩介质，并安装在/ogg
cd /ogg
./ggsci
create subdirs
在生产库中执行：创建GoldenGate复制用户。
create tablespace ogg datafile '/ora_data/u01/app/oracle/oradata/aaa3/ogg01.dbf' size 100m ;
create user goldengate identified by "welcome1" default tablespace ogg;
grant resource, connect, dba to goldengate;
在目标库中执行：创建GoldenGate复制用户。
create tablespace ogg datafile '/ora_data/ogg01.dbf' size 100m ;
create user goldengate identified by "welcome1" default tablespace ogg;
grant resource, connect, dba to goldengate;
修改enable_goldengater_replication参数
启用GoldenGate参数，不管在classic或integrated capture模式下都要开启以下参数。
alter system set enable_goldengate_replication=true scope=both sid='*';
ALTER TABLE SYS.SEQ$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
在GoldenGate软件安装的操作系统用户下，默认可使用oracle用户，配置用户环境变量
源端：
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export GGS_HOME=/ora_data/ogg
export PATH=$GGS_HOME:$PATH
export LD_LIBRARY_PATH=$GGS_HOME:$LD_LIBRARY_PATH
alias ggsci="cd $GGS_HOME;./ggsci"
目标端：
export GGS_HOME=/ogg/ogg11
export PATH=$GGS_HOME:$PATH
export LD_LIBRARY_PATH=$GGS_HOME:$LD_LIBRARY_PATH
alias ggsci="cd $GGS_HOME;./ggsci"
清理原环境：
参照生产环境表空间实际使用大小，创建相应表空间：
SQL> select tablespace_name,sum(bytes/1024/1024/1024) from dba_segments group by tablespace_name order by 2;
根据现有数据库，需要GoldenGate文件系统/ogg大小为100GB，要求都放在共享磁盘上，使用双机管理。
OGG软件安装
安装目录为：/ogg
在生产库执行，在数据库上执行sequence.sql脚本
oracle@BJLT-HYWG-SZ-MDCC01:/ogg> sqlplus ' / as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on 星期三 5月 24 23:23:31 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
SQL> @sequence.sql
Please enter the name of a schema for the GoldenGate database objects:
goldengate
Setting schema name to GOLDENGATE
UPDATE_SEQUENCE STATUS:
Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errors
GETSEQFLUSH
Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errors
SEQTRACE
Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errors
REPLICATE_SEQUENCE STATUS:
Line/pos
--------------------
Error
-----------------------------------------------------------------
No errors
No errors
STATUS OF SEQUENCE SUPPORT
--------------------------------------------------------------
SUCCESSFUL installation of Oracle Sequence Replication support
以下操作在生产端执行：
Global参数
edit params ./GLOBALS
GGSCHEMA goldengate
Manager进程参数
view params mgr
PORT 7809
DYNAMICPORTLIST 7840-7850
AUTOSTART ER *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
userid goldengate, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 2
--PURGEDDLHISTORY MINKEEPDAYS 3,MAXKEEPDAYS 7
--PURGEMARKERHISTORY MINKEEPDAYS 3,MAXKEEPDAYS 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
Goldengate 用户密码生成：encrypt password welcome1,ENCRYPTKEY default;
Capture进程参数
view params exta
EXTRACT exta
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
setenv (ORACLE_HOME=/ora_data/u01/app/oracle/product/11.2.0/db_1)
setenv (ORACLE_SID=aaa3)
userid goldengate, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/exta.dsc,APPEND,MEGABYTES 1024
TRANLOGOPTIONS EXCLUDEUSER GOLDENGATE
TRANLOGOPTIONS DBLOGREADER
--TRANLOGOPTIONS CONVERTUCS2CLOBS
--TRANLOGOPTIONS RAWDEVICEOFFSET 0
--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000
DBOPTIONS ALLOWUNUSEDCOLUMN
FETCHOPTIONS USESNAPSHOT
FETCHOPTIONS fetchpkupdatecols
FETCHOPTIONS suppressduplicates
WARNLONGTRANS 2h,CHECKINTERVAL 300s
EXTTRAIL ./dirdat/ea
GETTRUNCATES
TABLE AAA3.*;
SEQUENCE AAA3.*;
Pump进程参数
EXTRACT dpa
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
setenv (ORACLE_HOME=/ora_data/u01/app/oracle/product/11.2.0/db_1)
setenv (ORACLE_SID=aaa3)
userid goldengate, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default
RMTHOST 10.2.36.106 , MGRPORT 7809, compress
PASSTHRU
numfiles 10000
RMTTRAIL ./dirdat/ea
TABLE AAA3.*;
SEQUENCE AAA3.*;
生产库中执行
dblogin userid goldengate,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default
add trandata AAA3.*
（在生产库中执行）
ADD extract exta, TRANLOG, THREADS 1, BEGIN now
add exttrail ./dirdat/ea,extract exta,megabytes 500
add extract dpa,exttrailsource ./dirdat/ea
add rmttrail ./dirdat/ea,extract dpa,megabytes 500
（在生产库中执行）
start mgr
start exta
info exta
send exta status
记录exta进程启动时间：
2018-04-02 23:52:23 INFO OGG-00546 Oracle GoldenGate Capture for Oracle, exta.prm: Default thread stack size: 10485760.
2018-04-02 23:52:23 INFO OGG-01515 Oracle GoldenGate Capture for Oracle, exta.prm: Positioning to begin time 2018-4-2 下午11:52:02.
2018-04-02 23:52:24 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, exta.prm: Positioned to (Thread 1) Sequence 7082, RBA 2268688, SCN 0.0, 2018-4-2 下午11:52:02.
2018-04-02 23:52:24 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, exta.prm: EXTRACT EXTA started.
2018-04-02 23:52:24 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, exta.prm: No recovery is required for target file ./dirdat/ea000000, at RBA 0 (file not opened).
2018-04-02 23:52:24 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, exta.prm: Output file ./dirdat/ea is using format RELEASE 11.2.
2018-04-02 23:52:24 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, exta.prm: Position of first record processed for Thread 1, Sequence 7082, RBA 2268688, SCN 0.59337746, 2018-4-2 下午11:52:04.
select START_TIME from v$transaction where start_time < '2017-04-16 14:29:22';
（在目标库192.168.40.38/39/40中执行）
按照生产的结构，按照表空间实际使用量，创建表空间，参考“准生产表空间创建”章节
对象权限脚本：
生成创建用户脚本及权限脚本：（略）
使用expdp + impdp方式逻辑导出导入方式初始化数据。
SQL> select owner,sum(bytes/1024/1024/1024) from dba_segments group by owner;
OWNER SUM(BYTES/1024/1024/1024)
------------------------------ -------------------------
MDSYS .072509766
OUTLN .000549316
CTXSYS .003540039
OLAPSYS .005004883
AAA3 5.69433594
SYSTEM .030090332
EXFSYS .003540039
APEX_030200 .082458496
SCOTT .000305176
DBSNMP .001159668
ORDSYS .000427246
OWNER SUM(BYTES/1024/1024/1024)
------------------------------ -------------------------
SYSMAN .065612793
XDB .123962402
ORDDATA .013244629
SYS 3.08581543
WMSYS .003417969
16 rows selected.
select owner,sum(bytes/1024/1024/1024) from dba_segments where owner in
('AAA3')
group by owner order by 2;
OWNER SUM(BYTES/1024/1024/1024)
------------------------------ -------------------------
AAA3 5.69433594
select owner,count(*) from dba_objects where owner in
('AAA3')
group by owner
order by 2;
OWNER COUNT(*)
------------------------------ ----------
AAA3 117
SQL> col current_scn for 99999999999999999
SQL> select current_scn from v$database;
CURRENT_SCN
------------------
99091348
nohup impdp parfile=imp1.par &
userid=goldengate/welcome1
NETWORK_LINK=aaa3
schemas=aaa3
FLASHBACK_SCN=99091348
parallel=2
cluster=N
exclude=TABLE_STATISTICS,INDEX_STATISTICS
收集用户及系统统计信息：
exec dbms_stats.gather_schema_stats(ownname=>'AAA3',estimate_percent=>30, method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO' , degree=>2, granularity=>'ALL' ,cascade=>TRUE,no_invalidate=>false);
--exec DBMS_STATS.DELETE_DICTIONARY_STATS;
--EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
--EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
（在灾备库中执行）
生成禁用触发器脚本：
set linesize 180 pagesize 0
spool disable_trigger.sql
select 'alter trigger '||owner||'.'||trigger_name||' disable;'
from dba_triggers
where table_owner in ('AAA3')
and status='ENABLED'
order by owner;
spool off
生成启用触发器脚本：
set linesize 180 pagesize 0
spool enable_trigger.sql
select 'alter trigger '||owner||'.'||trigger_name||' enable;'
from dba_triggers
where table_owner in ('AAA3')
and status='ENABLED'
order by owner;
spool off
（在灾备库中执行）
生成禁用外键的脚本：
set linesize 180 pagesize 0
spool disable_ref.sql
select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'
from dba_constraints
where constraint_type='R'
AND OWNER IN ('AAA3')
and status='ENABLED'
order by owner;
spool off
生成启用外键的脚本：
set linesize 180 pagesize 0
spool enable_ref.sql
select 'alter table '||owner||'.'||table_name||' enable constraint '||constraint_name||';'
from dba_constraints
where constraint_type='R'
AND OWNER IN ('AAA3')
and status='ENABLED'
order by owner;
spool off
停监听，锁定灾备数据库用户。
（在灾备库中执行）
Global参数
edit params ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.ckpt
Manager进程参数
view params mgr
PORT 7809
DYNAMICPORTLIST 7840-7850
--AUTOSTART ER *
--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
userid goldengate, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 2
--PURGEDDLHISTORY MINKEEPDAYS 3,MAXKEEPDAYS 7
--PURGEMARKERHISTORY MINKEEPDAYS 3,MAXKEEPDAYS 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
ACCESSRULE, PROG *, IPADDR *, ALLOW
2018-04-03 00:26:25 WARNING OGG-00936 Access denied (request from 10.2.36.102, rule #0).
Delivery进程参数
REPLICAT repa
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
setenv (ORACLE_HOME=/home/oracle11g/product/11g)
setenv (ORACLE_SID=ora10g)
userid goldengate, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default
DBOPTIONS DEFERREFCONST SUPPRESSTRIGGERS
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
GETTRUNCATES
BATCHSQL
--HANDLECOLLISIONS
NUMFILES 2000
--FETCHOPTIONS FETCHPKUPDATECOLS
REPORT AT 01:59
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
DISCARDFILE ./dirrpt/repa.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 02:30
--MAPEXCLUDE PLM80.PLAN_TABLE
MAP AAA3.*, target AAA3.*, filter ( @GETENV ("TRANSACTION","CSN") > 45386411 );
（在灾备库中执行）
dblogin userid goldengate,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default
delete CHECKPOINTTABLE goldengate.ckpt
ADD CHECKPOINTTABLE goldengate.ckpt
（在灾备库中执行）
./ggsci
dblogin userid goldengate,password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default
add replicat repa,exttrail ./dirdat/ea,checkpointtable goldengate.ckpt
（在灾备库中执行）
修改CSN值，启动repa进程
GGSCI (BJ-APN-1) 6> view params repa
REPLICAT repa
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
setenv (ORACLE_HOME=/home/oracle11g/product/11g)
setenv (ORACLE_SID=ora10g)
userid goldengate, password AACAAAAAAAAAAAIARIXFKCQBMFIGFARA, encryptkey default
DBOPTIONS DEFERREFCONST SUPPRESSTRIGGERS
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
GETTRUNCATES
BATCHSQL
--HANDLECOLLISIONS
NUMFILES 2000
--FETCHOPTIONS FETCHPKUPDATECOLS
REPORT AT 01:59
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
DISCARDFILE ./dirrpt/repa.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 02:30
--MAPEXCLUDE PLM80.PLAN_TABLE
MAP AAA3.*, target AAA3.*, filter ( @GETENV ("TRANSACTION","CSN") > 99091348 );
启动进程：
start repa
扫一扫
1002
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
