OGG配置

在数据库中创建ogg表空间及用户
1.2.设置环境变量
[oracle@localhost /]$ cd home
[oracle@localhost home]$ cd oracle
[oracle@localhost ~]$ vim .bash_profile
export GGATE=/oracle/goldengate
[oracle@localhost ~]$ source .bash_profile
2. 初始化源端数据库(54)
2.12.1.开启归档模式
[oracle@localhost ~]$ sqlplus /nolog；
SQL> conn /as sysdba;
Connected.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Current log sequence 14
SQL> show parameter log_archive_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 2095704 bytes
Variable Size 138413480 bytes
Database Buffers 138412032 bytes
Redo Buffers 6291456 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
2.22.2.查看归档日志目录
SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
2.3.修改归档目录和大小
修改归档大小
SQL> alter system set db_recovery_file_dest_size=4g;
修改归档路径
[oracle@localhost oracle]$ mkdir arch_log;
[oracle@localhost oracle]$ ls -l
总计28
drwxr-x--- 3 oracle oinstall 4096 09-26 19:12 admin
drwxr-xr-x 2 oracle oinstall 4096 10-25 10:15 arch_log
drwxr-x--- 3 oracle oinstall 4096 09-26 19:14 flash_recovery_area
drwxr-xr-x 5 oracle oinstall 4096 10-25 09:54 goldengate
drwxr-x--- 4 oracle oinstall 4096 09-27 09:36 oradata
drwxrwx--- 6 oracle oinstall 4096 09-27 09:15 oraInventory
drwxrwx--- 3 oracle oinstall 4096 09-26 19:01 product
[oracle@localhost oracle]$ sqlplus /nolog;
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Oct 25 10:20:29 2011
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
SQL> conn /as sysdba;
Connected.
更改oracl 系统，禁止往闪回恢复区放归档日志
SQL> alter system set db_recovery_file_dest='';
System altered.
更改数据库，归档日志的存档终点。
SQLSQL> alter system set log_archive_dest='/oracle/arch_log';
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/arch_log
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
2.4.开启附加日志，补充日志？
SQL> alter database add supplemental log data;
Database altered.
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
2.5.开启强制日志模式(支持DDL 复制)
如果ogg 用户不存在可以先执行下一步
SQL> alter database force logging;
Database altered.
SQL> alter system set recyclebin=off scope=both;
System altered.
SQL> conn /as sysdba;
Connected.
SQL> @marker_setup

配置支持DDL复制
conn / as sysdba
@$GGATE_HOME/marker_setup.sql;
@$GGATE_HOME/ddl_setup.sql;
@$GGATE_HOME/role_setup.sql;
grant GGS_GGSUSER_ROLE to 用户名;
@$GGATE_HOME/ddl_enable.sql;
@?/rdbms/admin/dbmspool;
@$GGATE_HOME/ddl_pin.sql;

源端：
--------创建子目录
create subdirs

--------创建管理进程
edit param mgr
port 7809
DYNAMICPORTLIST 7840-7849
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 7
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 14
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

edit params GLOBALS
GGSCHEMA ogg

-----创建主抓取组进程
ADD EXTRACT ext1, TRANLOG,BEGIN NOW
-----定义本地跟踪队列
ADD EXTTRAIL /u01/soft/ogg/dirdat/ma, EXTRACT ext1, MEGABYTES 50

-----创建主抓取组配置文件
EXTRACT ext1
dynamicresolution
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg@primary, PASSWORD ogg
EXTTRAIL /u01/soft/ogg/dirdat/ma
TRANLOGOPTIONS EXCLUDEUSER ogg
DDL INCLUDE ALL
TABLE scott.*;

--------创建抽取进程
ADD EXTRACT pxt1, EXTTRAILSOURCE /u01/soft/ogg/dirdat/ma

-------添加源端跟踪队列
add rmttrail /u01/soft/ogg/dirdat/pa,megabytes 100,extract pxt1

--------创建抽取进程配置文件
EXTRACT PXT1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID ogg@primary, PASSWORD ogg
RMTHOST 192.168.100.222,MGRPORT 7809
RMTTRAIL /u01/soft/ogg/dirdat/pa
table scott.*;

目标端
-------创建管理进程
edit param mgr
port 7809
DYNAMICPORTLIST 7840-7849
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 7
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 14
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

edit params GLOBALS
GGSCHEMA ogg

------创建检查点表
add checkpointtable ogg.checkpoint

------创建同步进程
add replicat rep1,exttrail /u01/soft/ogg/dirdat/pa,begin now,checkpointtable ogg.checkpoint

------创建同步进程配置文件
REPLICAT rep1
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
ASSUMETARGETDEFS
APPLYNOOPUPDATES
allownoopupdates
USERID ogg@standby PASSWORD ogg
DISCARDFILE /u01/soft/ogg/dirrpt/RINIaa.dsc,PURGE
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLERROR DEFAULT DISCARD
DDLERROR DEFAULT IGNORE RETRYOP
MAP scott.* , TARGET scott.*;