oracle ogg 单机环境单向复制搭建
OGG安装
fbo_ggs_Linux_x64_shiphome.zip---------------------同时支持11g和12c
添加用户
useradd -u 1003 -g oinstall -G dba ogg
配置环境变量
export ORACLE_BASE=/u01/app/oracle;
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1;
export ORACLE_SID=racdb1;
export OGG_HOME=$ORACLE_BASE/ogg
export PATH=$ORACLE_HOME/bin:$PATH:/home/oracle/bin:$OGG_HOME/;
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/ogg/:/lib:/usr/lib;
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
安装时注意目录
2、找不到ggMessage
Cannot load ICU resource bundle'ggMessage', error code 2 - No such file or directory
Aborted (core dumped)
解决方法：oracle goldengate的HOME目录下执行
GGSCI (oggtarget) 2> help
GGSCI Command Summary:
Object: Command:
SUBDIRS CREATE
DATASTORE ALTER, CREATE, DELETE, INFO, REPAIR
ER INFO, KILL, LAG, SEND, STATUS,START, STATS, STOP
EXTRACT ADD, ALTER, CLEANUP, DELETE, INFO,KILL,
LAG, REGISTER, SEND, START,STATS, STATUS, STOP
UNREGISTER
EXTTRAIL ADD, ALTER, DELETE, INFO
GGSEVT VIEW
JAGENT INFO, START, STATUS, STOP
MANAGER INFO, SEND, START, STOP, STATUS
MARKER INFO
PARAMETERS EDIT, VIEW, SET EDITOR, INFO,GETPARAMINFO
REPLICAT ADD, ALTER, CLEANUP, DELETE, INFO,KILL, LAG, REGISTER, SEND,
START, STATS, STATUS, STOP,SYNCHRONIZE, UNREGISTER
REPORT VIEW
RMTTRAIL ADD, ALTER, DELETE, INFO
TRACETABLE ADD, DELETE, INFO
TRANDATA ADD, DELETE, INFO
SCHEMATRANDATA ADD, DELETE, INFO
CHECKPOINTTABLE ADD, DELETE, CLEANUP, INFO, UPGRADE
WALLET CREATE, OPEN, PURGE
MASTERKEY ADD, INFO, RENEW, DELETE, UNDELETE
CREDENTIALSTORE ADD, ALTER, INFO, DELETE
HEARTBEATTABLE ADD, DELETE, ALTER, INFO
HEARTBEATENTRY DELETE
Commands without an object:
(Database) DBLOGIN, LIST TABLES, ENCRYPT PASSWORD,FLUSH SEQUENCE
MININGDBLOGIN, SET NAMECCSID
(DDL) DUMPDDL
(Miscellaneous) ! ,ALLOWNESTED | NOALLOWNESTED, CREATESUBDIRS,
DEFAULTJOURNAL, FC, HELP,HISTORY, INFO ALL, OBEY, SHELL,
SHOW, VERSIONS, VIEW GGSEVT,VIEW REPORT
(note: type the word COMMANDafter the ! to display the
! help topic, for example:GGSCI (sys1)> help ! command
OGG配置
实验规划
项目 | ||
操作系统 | ||
主机名 | ||
数据库版本 | ||
数据库字符集 | ||
Oracle版本 | ||
Ogg版本 | ||
Oracle sid | dbdream | stream |
主库进行全备
$ rman target /
run {
allocate channel d0 type disk;
allocate channel d1 type disk;
backup format'/u01/backup/full_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/u01/backup/arc_t%t_s%s_p%p'archivelog all;
release channel d0;
release channel d1;
}
2.3.1 主库备份
主库进行全备
$ rman target /
run {
allocate channel d0 type disk;
allocate channel d1 type disk;
backup format'/u01/backup/full_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/u01/backup/arc_t%t_s%s_p%p'archivelog all;
release channel d0;
release channel d1;
}
创建备用控制文件
RMAN> backup current controlfile forstandby format '/u01/backup/control01.ctl';
scp * 192.168.120.203:/u01/backup
恢复
[oracle@oggtarget ~]$ export ORACLE_SID=stream
[oracle@oggtarget ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 -Production on Sun Aug 27 09:54:43 2017
Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.
connected to target database (not started)
RMAN> set dbid=2496948349
RMAN> startup nomount
startup failed: ORA-01078: failure inprocessing system parameters
LRM-00109: could not open parameter file'/u01/app/oracle/product/11.2.0/db_1/dbs/initstream.ora'
starting Oracle instance without parameterfile for retrieval of spfile
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes
注意：在rman下即使没有参数文件，默认也会启动一个DUMMY实例，以便能够恢复参数文件。
1、恢复spfile
RMAN> restore spfile from'/u01/backup/full_t953113531_s4_p1';
RMAN> sql "create pfile from spfile"
修改pfile参数
dbdream.__java_pool_size=4194304
dbdream.__large_pool_size=8388608
dbdream.__oracle_base='/u01/app/oracle'#ORACLE_BASEset from environment
dbdream.__pga_aggregate_target=314572800
dbdream.__sga_target=465567744
dbdream.__shared_io_pool_size=0
dbdream.__shared_pool_size=117440512
dbdream.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stream/adump'-----------------------修改创建目录
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/stream/control01.ctl','/u01/app/oracle/fast_recovery_area/stream/control02.ctl'----修改
*.db_block_size=8192
*.db_domain=''
*.db_name='stream'-----修改
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=streamXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=780140544
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
~
RMAN> shutdown abort
用新修改的文件启动 nomount
RMAN> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstream.ora'
创建pfile
RMAN> sql "create spfile from pfile";
新spfile启动
RMAN> startup nomount;
RMAN> startup force nomount;
2、恢复控制文件
RMAN> restore controlfile from '/u01/backup/full_t953113527_s3_p1';
Starting restore at 27-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete,elapsed time: 00:00:01
output filename=/u01/app/oracle/oradata/stream/control01.ctl
output filename=/u01/app/oracle/fast_recovery_area/stream/control02.ctl
Finished restore at 27-AUG-17
3、启动数据库到加载状态
RMAN> alter database mount;
RMAN> catalog start with '/backup/';
RMAN> restore database;
RMAN> recover database;
RMAN>alter database open resetlogs 打开数据库
nid target=/as sysdba dbname=stream
[oracle@oggtarget dbs]$ nid target=/assysdba dbname=stream
DBNEWID: Release 11.2.0.4.0 - Production onSun Aug 27 12:31:08 2017
Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.
Password:
Connected to database DBDREAM (DBID=2496948349)
Connected to server version 11.2.0
Control Files in database:
/u01/app/oracle/oradata/stream/control01.ctl
/u01/app/oracle/fast_recovery_area/stream/control02.ctl
Change database ID and database nameDBDREAM to STREAM? (Y/[N]) => y
Proceeding with operation
Changing database ID from 2496948349 to1719130576
Changing database name from DBDREAM toSTREAM
Control File /u01/app/oracle/oradata/stream/control01.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/stream/control02.ctl -modified
Datafile /u01/app/oracle/oradata/dbdream/system01.db - dbid changed,wrote new name
Datafile /u01/app/oracle/oradata/dbdream/sysaux01.db - dbid changed,wrote new name
Datafile /u01/app/oracle/oradata/dbdream/undotbs01.db - dbid changed,wrote new name
Datafile /u01/app/oracle/oradata/dbdream/users01.db - dbid changed,wrote new name
Datafile /u01/app/oracle/oradata/dbdream/temp01.db - dbid changed, wrotenew name
Control File /u01/app/oracle/oradata/stream/control01.ctl - dbidchanged, wrote new name
Control File /u01/app/oracle/fast_recovery_area/stream/control02.ctl -dbid changed, wrote new name
Instance shut down
Database name changed to STREAM.
Modify parameter file and generate a newpassword file before restarting.
Database ID for database STREAM changed to1719130576.
All previous backups and archived redo logsfor this database are unusable.
Database is not aware of previous backupsand archived logs in Recovery Area.
Database has been shutdown, open databasewith RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
如果没有修改参数文件中的DB_NAME参数，那么在MOUNT的时候，会报ORA-01103错误。
修改DB_NAME参数，尝试直接打开数据库。
提示必须使用RESETLOGS的方式才能打开数据库。
默认情况下，db_unique_name 和service_names都会伴随着DB NAME一起改变，此时由于service_names发生了变化，正常情况下应用是无法连接数据库的(以SID连接方式除外)。
Oracle goldengate搭建ogg
主库
设置环境变量（oracle用户）
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin:/ggs
export LD_LIBRARY_PATH=/ggs:$ORACLE_HOME/lib
1.检查是否开启归档
SQL> select log_mode fromgv$database;
SQL> archive log list; ----注意归档路径需要是共享路径
2.检查是否开启force logging及补充日志
selectforce_logging,supplemental_log_data_min,supplemental_log_data_all,flashback_onfrom v$database;
开启：
alter database force logging;
alter database add supplemental logdata;
alter system archive log current;
3.对主库检查，ogg不允许：唯一索引的索引列的列定义允许为null的
select dic.table_owner,
dic.table_name,
dic.index_name,
di.uniqueness,
dic.column_name
from dba_ind_columns dic, dba_indexes di, dba_tab_columns dtc
where dic.table_owner = '自行添加用户' -----修改用户名
and dtc.OWNER = '自行添加用户' -----修改用户名
AND dic.table_owner = di.table_owner
and dic.TABLE_NAME = di.table_name
and dic.index_name = di.index_name
and di.uniqueness = 'UNIQUE'
and dtc.owner = di.table_owner
and dtc.TABLE_NAME = di.table_name
and dic.column_name = dtc.COLUMN_NAME
and dtc.nullable = ' Y '
anddic.TABLE_NAME = dtc.TABLE_NAME;
不应该返回行，如果返回了，修改：要么变为非唯一索引，要么在保留唯一索引的情况下，将列的定义置为 not null。
4.创建ogg用户，并授权
create user ogg identified by ogg default tablespaceusers;
grant dba to ogg;
ALTER SYSTEM SETENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
5.检查是否有nologing方式的表（ogg不支持nologing方式创建的表）
select owner,table_name,logging fromdba_tables where logging='NO' AND owner='用户名';
修改为logging的表的语法：alter table 表名 logging;
注意：在ext进程的参数文件里添加 dboptions allownologging可以让ext进程继续运行，但是会导致数据丢失。
6.源端数据库添加表的补充日志
进入ogg安装路径：
ggsci
dblogin userid ogg password ogg
GGSCI (oggsource) 1> dblogin userid ogg password ogg
Successfully logged into database.
GGSCI (oggsource as ogg@dbdream) 2>create subdirs
Creating subdirectories under current directory/u01/app/oracle/ogg
Parameter files /u01/app/oracle/ogg/dirprm:already exists
Report files /u01/app/oracle/ogg/dirrpt:already exists
Checkpoint files /u01/app/oracle/ogg/dirchk:already exists
Process status files /u01/app/oracle/ogg/dirpcs: alreadyexists
SQL script files /u01/app/oracle/ogg/dirsql:already exists
Database definitions files /u01/app/oracle/ogg/dirdef: already exists
Extract data files /u01/app/oracle/ogg/dirdat:already exists
Temporary files /u01/app/oracle/ogg/dirtmp:already exists
Credential store files /u01/app/oracle/ogg/dircrd: alreadyexists
Masterkey wallet files /u01/app/oracle/ogg/dirwlt: alreadyexists
Dump files /u01/app/oracle/ogg/dirdmp: already exists
GGSCI (oggsource as ogg@dbdream) 2>addtrandata lm.testogg
-------------------为表添加附加日志，以便goldengate进行redo的抽取以及应用。
7.配置DDL复制
使用ogg作为存储DDL objects的用户给ogg授权：
SQL> GRANT EXECUTE ON UTL_FILE TO ogg;
8.配置GLOBALS文件
ggsci
edit param ./GLOBALS中加入：
GGSCHEMA goldengate
如果是10g需要停用 recyclebin，11g就不需要了
9.数据库执行：
退出所有的oracle连接后执行：
cd /ggs
sqlplus / as sysdba
@marker_setup.sql
SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of aschema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
Enter Oracle GoldenGate schema name:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL>@ddl_setup.sql
Oracle GoldenGate DDL Replication setupscript
Verifying that current user has privilegesto install DDL Replication...
You will be prompted for the name of aschema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the systemrecycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
Enter Oracle GoldenGate schema name:ogg
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holdinglocks on Oracle Golden Gate metadata tables ...
Check complete.
Using OGG as a Oracle GoldenGate schemaname.
Working, please wait ...
DDL replication setup script complete,running verification script...
Please enter the name of a schema for theGoldenGate database objects:
Setting schema name to OGG
CLEAR_TRACE STATUS:
Line/pos Error
-------------------------------------------------------------------------------------
No errors No errors
CREATE_TRACE STATUS:
Line/pos Error
-------------------------------------------------------------------------------------
No errors No errors
TRACE_PUT_LINE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
INITIAL_SETUP STATUS:
Line/pos Error
-------------------------------------------------------------------------------------
No errors No errors
DDLVERSIONSPECIFIC PACKAGE STATUS:
Line/pos Error
-------------------------------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE STATUS:
Line/pos Error
-------------------------------------------------------------------------------------
No errors No errors
DDLREPLICATION PACKAGE BODY STATUS:
Line/pos Error
-------------------------------------------------------------------------------------
No errors No errors
DDL IGNORE TABLE
-----------------------------------
OK
DDL IGNORE LOG TABLE
-----------------------------------
OK
DDLAUX PACKAGE STATUS:
Line/pos Error
-------------------------------------------------------------------------------------
No errors No errors
DDLAUX PACKAGE BODY STATUS:
Line/pos Error
-------------------------------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE STATUS:
Line/pos Error
-------------------------------------------------------------------------------------
No errors No errors
SYS.DDLCTXINFO PACKAGE BODY STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL HISTORY TABLE
-----------------------------------
OK
DDL HISTORY TABLE(1)
-----------------------------------
OK
DDL DUMP TABLES
-----------------------------------
OK
DDL DUMP COLUMNS
-----------------------------------
OK
DDL DUMP LOG GROUPS
-----------------------------------
OK
DDL DUMP PARTITIONS
-----------------------------------
OK
DDL DUMP PRIMARY KEYS
-----------------------------------
OK
DDL SEQUENCE
-----------------------------------
OK
GGS_TEMP_COLS
-----------------------------------
OK
GGS_TEMP_UK
-----------------------------------
OK
DDL TRIGGER CODE STATUS:
Line/pos Error
-------------------- -----------------------------------------------------------------
No errors No errors
DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
DDL TRIGGER RUNNING STATUS
----------------------------------------------------------------------
ENABLED
STAYMETADATA IN TRIGGER
----------------------------------------------------------------------
OFF
DDL TRIGGER SQL TRACING
----------------------------------------------------------------------
0
DDL TRIGGER TRACE LEVEL
----------------------------------------------------------------------
NONE
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/dbdream/dbdream/trace/ggs_ddl_trace.log
Analyzing installation status...
VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replicationsoftware components
Script complete.
SQL>
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the roleGGS_GGSUSER_ROLE
To use a different role name, quit thisscript and then edit the params.sql script to change the gg_role parameter tothe preferred name. (Do not run the script.)
You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
Enter GoldenGate schema name:ogg
SP2-0606: Cannot create SPOOL file"role_setup_spool.txt"
SP2-0606: Cannot create STORE file"role_setup_set.txt"
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned tothe Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO<loggedUser>
where <loggedUser> is the userassigned to the GoldenGate processes.
SQL> grantggs_ggsuser_role to ogg;
SQL> @ddl_enable.sql
如果是有灾备演练的需求，需要配置sequence同步
cd /ggs --ogg安装目录
sqlplus / as sysdba
@sequence.sql
GRANT EXECUTE on goldengate.updateSequenceTO goldengate;
10.源端配置参数文件
su - grid
vi$ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = +ASM)
(ORACLE_HOME=/u01/app/11.2.0/grid)
(SID_NAME = +ASM1)
. )
.)
.
.
.su - oracle
.cd $ORACLE_HOME/network/admin
.vi tnsnames.ora
.ASM =
. (DESCRIPTION =
. (ADDRESS = (PROTOCOL = TCP)(HOST = 186.168.100.3)(PORT = 1521))
. (CONNECT_DATA =
. (SERVER = DEDICATED)
. (SERVICE_NAME = +ASM)
. (SID_NAME = +ASM1)
. )
. )
.
11、配置管理进程mgr：
GGSCI(NDSCDB1) 1> edit param mgr
port 7809
-- DYNAMICPORTLIST 7830-7835
autostart extract *
autorestart extract *, waitminutes 1,retries 60, RESETMINUTES 60
PURGEOLDEXTRACTS /ggs/dirdat/sd*,USECHECKPOINTS, MINKEEPHOURS 2
~ MANAGER进程参数配置说明：
PORT：指定服务监听端口；这里以7839为例，默认端口为7809
DYNAMICPORTLIST：动态端口：可以制定最大256个可用端口的动态列表，当指定的端口不可用时，管理进程将会从列表中选择一个可用的端口，源端和目标段的Collector、Replicat、GGSCI进程通信也会使用这些端口；
COMMENT：注释行，也可以用--来代替;
AUTOSTART：指定在管理进程启动时自动启动哪些进程；
AUTORESTART：自动重启参数设置：本处设置表示每3分钟尝试重新启动所有EXTRACT进程，共尝试5次；
PURGEOLDEXTRACTS：定期清理trail文件设置：本处设置表示对于超过3天的trail文件进行删除。
LAGREPORT、LAGINFO、LAGCRITICAL：
定义数据延迟的预警机制：本处设置表示MGR进程每隔1小时检查EXTRACT的延迟情况，如果超过了30分钟就把延迟作为信息记录到错误日志中，如果延迟超过了45分钟，则把它作为警告写到错误日志中。
12、配置extfull
add extract extfull, tranlog,begin now
edit param extfull
extract extfull
setenv ( NLS_LANG = " AMERICAN_AMERICA.ZHS16GBK " )------添加报错
TRANLOGOPTIONS ASMUSERSYS@ASM, ASMPASSWORD oracle
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY80000 IOLATENCY 160000
DBOPTIONS ALLOWUNUSEDCOLUMN
userid goldengate, password goldengate
ddl include mapped
ddloptions addtrandata RETRYOP MAXRETRIES1000 RETRYDELAY 10, REPORT
WARNLONGTRANS 1h, CHECKINTERVAL 5m
exttrail /ggs/dirdat/sd
gettruncates
dynamicresolution
NOCOMPRESSUPDATES
NOCOMPRESSDELETES
table LM.testogg;
add exttrail /u01/app/oracle/ogg/dirdat/sd, extractextfull, MEGABYTES 50
13.添加传输进程
添加传输进程
addextract dpfull exttrailsource /ggs/dirdat/sd
创建远程队列文件并将其指定给传输进程
addrmttrail /u01/app/oracle/ogg/td, extract dpfull, MEGABYTES 50
配置传输进程参数
editparam dpfull
==================================
extractdpfull
passthru
rmthost 186.168.100.22, mgrport 7809
rmttrail /ggs/dirdat/td
gettruncates
table LM.testogg;
12、启动管理进程：
dblogin userid ogg password ogg
GGSCI (oggsource as ogg@dbdream) 11>start mgr
Manager started.
查看进程状态可发现 MANAGER状态为 RUNNING：
GGSCI(NDSCDB1) 3> info all
/u01/app/oracle/ogg/dirrpt
13、配置抽取进程：
GGSCI (oggsource as ogg@dbdream) 12> addextract extnd,tranlog,begin now
EXTRACT added.
GGSCI (oggsource as ogg@dbdream) 13> addexttrail ./dirdat/nd,extract extnd,megabytes 100
EXTTRAIL added.
Megabytes:指定队列大小，本处设置表示100M。
添加传输进程，配置参数
GGSCI(NDSCDB1) 15> edit params extnd
EXTRACT extnd
setenv(NLS_LANG ="AMERICAN_AMERICA.ZHS16GBK")
SETENV(ORACLE_HOME ="/u01/app/oracle/product/11.2.0/db_1")
SETENV(ORACLE_SID ="dbdream")
USERID ogg, PASSWORD ogg
--GETTRUNCATES
REPORTCOUNTEVERY 1 MINUTES, RATE
DISCARDFILE./dirrpt/extnd.dsc,APPEND,MEGABYTES1024
--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENS60000
DBOPTIONS ALLOWUNUSEDCOLUMN
WARNLONGTRANS2h,CHECKINTERVAL 3m
EXTTRAIL./dirdat/nd
--TRANLOGOPTIONSEXCLUDEUSER USERNAME
FETCHOPTIONSNOUSESNAPSHOT
TRANLOGOPTIONS CONVERTUCS2CLOBS
TABLE olive.ol$_objects
GGSCI(NDSCDB1) 15>add extract dpend,exttrailsource ./dirdat/nd
EXTRACT added.
GGSCI(NDSCDB1) 15>add rmttrail /u01/app/oracle/ogg/nd, EXTRACT DPEND
RMTTRAIL added.
edit params dpend
EXTRACT dpend
SETENV(NLS_LANG ="AMERICAN_AMERICA.ZHS16GBK")
USERID ogg, PASSWORD ogg
PASSTHRU
RMTHOST10.122.0.113, MGRPORT 7839, compress
RMTTRAILF:/u01/app/oracle/ogg/dirdat/nd
TABLE olive.ol$_objects;
抽取进程和传输进程其实都是EXTRACT进程，也可以配置在一个进程完成这两个功能，但是当网络传输有问题时，这样抽取也就不能继续运行了，所以推荐分开配置为两个进程；
EXTRACT进程参数配置说明：
SETENV：配置系统环境变量
USERID/ PASSWORD：指定OGG连接数据库的用户名和密码，这里使用3.4部分中创建的数据库用户OGG;
COMMENT：注释行，也可以用--来代替;
TABLE：定义需复制的表，后面需以；结尾
TABLEEXCLUDE：定义需要排除的表，如果在TABLE参数中使用了通配符，可以使用该参数指定排除掉得表。
GETUPDATEAFTERS|IGNOREUPDATEAFTERS：
是否在队列中写入后影像，缺省复制
GETUPDATEBEFORES| IGNOREUPDATEBEFORES：
是否在队列中写入前影像，缺省不复制
GETUPDATES|IGNOREUPDATES：
是否复制UPDATE操作，缺省复制
GETDELETES|IGNOREDELETES：
是否复制DELETE操作，缺省复制
GETINSERTS|IGNOREINSERTS：
是否复制INSERT操作，缺省复制
GETTRUNCATES|IGNORETRUNDATES：
是否复制TRUNCATE操作，缺省不复制；
RMTHOST：指定目标系统及其GoldengateManager进程的端口号，还用于定义是否使用压缩进行传输，本例中的compress为压缩传输；
RMTTRAIL：指定写入到目标断的哪个队列；
EXTTRAIL：指定写入到本地的哪个队列；
SQLEXEC：在extract进程运行时首先运行一个SQL语句；
PASSTHRU：禁止extract进程与数据库交互，适用于DataPump传输进程；
REPORT：定义自动定时报告；
STATOPTIONS：定义每次使用stat时统计数字是否需要重置；
REPORTCOUNT：报告已经处理的记录条数统计数字；
TLTRACE：打开对于数据库日志的跟踪日志；
DISCARDFILE：定义discardfile文件位置，如果处理中油记录出错会写入到此文件中；
DBOPTIONS：指定对于某种特定数据库所需要的特殊参数；
TRANLOGOPTIONS：指定在解析数据库日志时所需要的特殊参数，例如：对于裸设备，可能需要加入以下参数 rawdeviceoggset 0
WARNLONGTRANS：指定对于超过一定时间的长交易可以在gsserr.log里面写入警告信息，本处配置为每隔3分钟检查一次场交易，对于超过2小时的进行警告；~
~
~
目标库
备库：
1.创建ogg用户并授权
create user goldengatet identified bygoldengatet default tablespace tbs_ogg;
grant dba to ogg;
execdbms_streams_auth.grant_admin_privilege(grantee => 'ogg',grant_privileges=> true);
ALTER SYSTEM SETENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
2.配置环境变量（oracle用户下）
export LD_LIBRARY_PATH
export PATH=
目标库创建GoldenGate数据库用户并授权：
GGSCI (oggtarget) 1> create subdirs
Creating subdirectories under currentdirectory /u01/app/oracle/ogg
Parameter files /u01/app/oracle/ogg/dirprm:already exists
Report files /u01/app/oracle/ogg/dirrpt:already exists
Checkpoint files /u01/app/oracle/ogg/dirchk:already exists
Process status files /u01/app/oracle/ogg/dirpcs: alreadyexists
SQL script files /u01/app/oracle/ogg/dirsql:already exists
Database definitions files /u01/app/oracle/ogg/dirdef: already exists
Extract data files /u01/app/oracle/ogg/dirdat:already exists
Temporary files /u01/app/oracle/ogg/dirtmp:already exists
Credential store files /u01/app/oracle/ogg/dircrd: alreadyexists
Masterkey wallet files /u01/app/oracle/ogg/dirwlt: alreadyexists
Dump files /u01/app/oracle/ogg/dirdmp: already exists
SQL> create tablespace ogg datafile'/u01/app/oracle/oradata/dbdream/ogg.dbf' size 50M autoextend on;
SQL> create user ogg identified by oggdefault tablespace ogg;
grant connect,resource,unlimited tablespaceto ogg;
grant execute on utl_file to ogg;
grant select any dictionary,select anytable to ogg;
grant alter any table to ogg;
grant flashback any table to ogg;
grant execute on DBMS_FLASHBACK to ogg;
grant insert any table to ogg;
grant delete any table to ogg;
grant update any table to ogg;
库配置检查
SQL> select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MINfrom v$database;
SQL> alter database force logging;
SQL> alter database add SUPPLEMENTAL log data;
配置MGR
配置参数文件
MGR：
edit param mgr
PORT 7839
autostart replicat *
autorestart replicat *, waitminutes 1,retries 60, RESETMINUTES 60
PURGEOLDEXTRACTS/u01/app/oracle/ogg/dirdat/td*, USECHECKPOINTS, MINKEEPHOURS 2
添加checkpoint表
dblogin userid goldengate,password goldengate
ADD CHECKPOINTTABLE goldengate.ckptfull
配置目标端进程组
add replicat repfull, exttrail/u01/app/oracle/ogg/dirdat/td, CHECKPOINTTABLE ogg.ckptfull
edit params repfull
replicat repfull
setenv ( NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK" )
assumetargetdefs
userid ogg, password ogg
DBOPTIONS DEFERREFCONST, SUPPRESSTRIGGERS
gettruncates
ALLOWNOOPUPDATES
ddl include mapped
discardfile ./dirrpt/repfull.dsc, append,megabytes 4000
map scott.t1, target scott.t1;
GGSCI (oggtarget as ogg@stream) 74>start mgr
GGSCI (oggtarget as ogg@stream) 75> infoall
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPFULL 00:00:00 00:00:02
参数介绍：
REPLICAT RINI_1：说明这是REPLICAT应用进程，名字叫RINI_1
SETENV：语言变量，同捕获进程EINI_1
ASSUMETARGETDEFS：告诉OGG目标端和源端需要同步的表的结构完全一致，不需要OGG去检查表的结构，包括表名、字段名、字段类型、字段长度等，如果目标端和源端同步的表的结构不一样，需要使用SOURCEDEFS参数，详见OGG官方文档。
USERID、PASSWORD：同捕获进程EINI_1参数介绍
DISCARDFILE：错误信息存放位置及命名规则
MAP：源端捕获的表的名字
TARGET：目标端同步的表的名字，可以不在同一SCHEMA。
测试检查
info all---------------------检查进程
info 进程名,detail
stats REPFULL-----------------------------检查数据传输状态
view report 进程名
数据库登录检查数据是否有变化
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/45.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
