OGG部署

环境检查

1.检查是否开启归档

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /oracle/db/dbs/arch

Oldest online log sequence 810

Current log sequence 812

1. 查看日志的存档方式，并修改为存档模式

SQL> show parameter log_archive_start;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_start boolean FALSE

SQL> alter system set log_archive_start=true scope=spfile;

System altered.

1. 开启归档

先一致性关闭数据库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

启动数据库到mount

SQL> startup mount

ORACLE instance started.

Total System Global Area 9787547648 bytes

Fixed Size 2261568 bytes

Variable Size 3892317632 bytes

Database Buffers 5872025600 bytes

Redo Buffers 20942848 bytes

Database mounted.

开启归档

SQL> alter database archivelog;

Database altered.

启动数据库到open，并检查数据库归档是否开启

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /oracle/db/dbs/arch

Oldest online log sequence 810

Next log sequence to archive 812

Current log sequence 812

查看源端数据库是否开启附加日志

SQL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

• 缺省情况下Oracle只在日志中记录Update对应的rowid，在OGG复制的目标端同一行的rowid会与源端不同，无法实现记录的一一对应；
• OGG使用主键/唯一索引（或者全部列）作为行标识进行对应；
• 需要通过附加日志在数据库日志中加入这些额外的标识信息；

打开数据库的附加日志：

SQL> alter database add supplemental log data;

Database altered.

切换日志，使附加日志生效

SQL> Alter system switch logfile;

System altered.

上传OGG软件并解压

创建OGG目录

[SID:qdswzmk]@its-zmxt-predb[/oradata]$mkdir ogg

[SID:qdswzmk]@its-zmxt-predb[/oradata/ogg]$ls

fbo_ggs_Linux_x64_ora11g_64bit.tar

[SID:qdswzmk]@its-zmxt-predb[/oradata/ogg]$tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar

bcpfmt.tpl

bcrypt.txt

cachefiledump

cfg/

cfg/Config.properties

cfg/mpmetadata.xml

cfg/MPMetadataSchema.xsd

......

配置环境变量（.bash_profile）

Linux下执行vi /home/oracle/.bash_profile

AIX下执行vi /home/oracle/.profile

修改环境变量，添加PATH、LD_LIBARY_PATH

源端配置操作

创建ogg用户

SQL> create user ggs identified by grsds1234;

User created.

SQL> grant connect,resource,dba to ggs;

Grant succeeded.

创建目录

GGSCI (its-zmxt-predb) 5> create subdirs

Creating subdirectories under current directory /oradata/ogg

Parameter files /oradata/ogg/dirprm: already exists

Report files /oradata/ogg/dirrpt: created

Checkpoint files /oradata/ogg/dirchk: created

Process status files /oradata/ogg/dirpcs: created

SQL script files /oradata/ogg/dirsql: created

Database definitions files /oradata/ogg/dirdef: created

Extract data files /oradata/ogg/dirdat: created

Temporary files /oradata/ogg/dirtmp: created

Stdout files /oradata/ogg/dirout: created

GGSCI (its-zmxt-predb) 6> edit param ./GLOBALS

插入

GGSCHEMA ggsj1

CHECKPOINTTABLE ggs.checkpoint

添加checkpoint table

GGSCI (its-zmxt-predb) 7> ADD checkpointtable ggs.checkpoint

Successfully created checkpoint table ggs.checkpoint.

创建OGG进程

1. 创建mgr

GGSCI (its-zmxt-predb) 3> edit params mgr

port 7809

dynamicportlist 7810-8000

autorestart er *, retries 5, waitminutes 3

purgeoldextracts ./dirdat/*,usecheckpoints, minkeepdays 7

--purgeoldextracts ./dirdat/*,usecheckpoints, minkeepdays 7

LAGREPORTHOURS 5

LAGINFOMINUTES 10

LAGCRITICALMINUTES 15

1. 创建E进程（抽取进程）

如果是RAC模式

GGSCI> add extract ej1_kjgs, tranlog, threads 2, begin now

GGSCI> add exttrail ./dirdat/gc, extract ej1_kjgs, megabytes 1024

单机模式

GGSCI (its-zmxt-predb) 8> add extract ej1_kjgs, tranlog, begin now

EXTRACT added.

GGSCI (its-zmxt-predb) 9> add exttrail ./dirdat/gc, extract ej1_kjgs, megabytes 1024

EXTTRAIL added.

执行后查看

GGSCI (its-zmxt-predb) 13> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

EXTRACT STOPPED EJ1_KJGS 00:00:00 00:15:07

EXTRACT STOPPED PJ1_KJGS 00:00:00 00:04:54

GGSCI (its-zmxt-predb) 2> edit params ej1_kjgs

extract ej1_kjgs

SETENV (ORACLE_SID="qdswzmk")

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

Userid ggs,PASSWORD grsds1234

REPORT AT 01:59

REPORTROLLOVER AT 02:00

CACHEMGR, CACHESIZE 256MB

EXTTRAIL ./dirdat/gc

NUMFILES 3000

EOFDELAYCSECS 30

--GETTRUNCATES

TRANLOGOPTIONS DBLOGREADER

DYNAMICRESOLUTION

BR BRINTERVAL 2H , BRDIR BR

GETUPDATEBEFORES

NOCOMPRESSDELETES

WARNLONGTRANS 3H, CHECKINTERVAL 3M

table gszm_yw.ZMXT_SB_SBXX;

table gszm_yw.ZMXT_SB_JMFB_HS_SSXD;

table gszm_yw.ZMXT_SB_JMFB_HS_JMSX;

1. 创建P进程（投递进程）

GGSCI (its-zmxt-predb) 11> add extract pj1_kjgs ,exttrailsource ./dirdat/gc

EXTRACT added.

GGSCI (its-zmxt-predb) 12> add rmttrail ./dirdat/cg, extract pj1_kjgs ,megabytes 1024

RMTTRAIL added.

执行后查看

GGSCI (its-zmxt-predb) 13> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

EXTRACT STOPPED EJ1_KJGS 00:00:00 00:15:07

EXTRACT STOPPED PJ1_KJGS 00:00:00 00:04:54

GGSCI (its-zmxt-predb) 14> edit params PJ1_KJGS

extract PJ1_KJGS

SETENV (ORACLE_SID="qdswzmk")

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

Userid ggs,PASSWORD grsds1234

REPORT AT 01:59

REPORTROLLOVER AT 02:00

CACHEMGR, CACHESIZE 256MB

FLUSHCSECS 30

NUMFILES 3000

EOFDELAYCSECS 30

RMTHOST 76.27.75.168,MGRPORT 7829, TCPBUFSIZE 100000, TCPFLUSHBYTES 300000

RMTTRAIL ./dirdat/cg

GETTRUNCATES

PASSTHRU

DYNAMICRESOLUTION

GETUPDATEBEFORES

NOCOMPRESSDELETES

TABLEEXCLUDE *.DBMS_TABCOMP_TEMP*;

table gszm_yw.*;