oracle数据库配置goldengate同步

一、源端数据库配置：

1、在源库中创建goldengate表空间及goldengate用户：
$ export ORACLE_SID=sid
$ sqlplus / as sysdba;
SQL> create tablespace goldengate datafile '/cwdata/account/goldengate01.dbf' size 100M autoextend on;
SQL> create user goldengate identified by goldengate default tablespace goldengate temporary tablespace temp profile default;
SQL> grant dba to goldengate;

2、安装goldengate：
$ cd /ywogg
$ mkdir oggbj
通过ftp上传GoldenGate软件包到ogg_account安装目录，并解压缩
oracle@ywdb[/ywogg/oggbj]$ tar -xvf ggs_HPUX_ia64_ora11g_64bit.tar
$ ./ggsci
GGSCI> create subdirs
为oracle用户添加环境变量：
export GG_HOME=/ogg/ogg_lis
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

3、配置源端数据库：
$ export ORACLE_SID=lis
$ sqlplus / as sysdba;
检查数据库是否为归档模式，如果为非归档，将其改为归档
SQL> archive log list;
先查看
SQL> select supplemental_log_data_min,force_logging from v$database;

SUPPLEME FOR
-------- ---
NO       NO
SQL> alter database add supplemental log data;
SQL> alter database force logging;
SQL> select supplemental_log_data_min,force_logging from v$database;

SUPPLEME FOR
-------- ---
YES      YES


$ cd /ywogg/oggbj
$ ./ggsci
GGSCI> dblogin userid goldengate password goldengate
GGSCI> add trandata lis.*
GGSCI> info trandata lis.*
最好是记录日志，确保要同步的所有表都为enabled，在日志里面查找看有没有disabled


5、配置manager进程：
$ cd /ywogg/oggbj
$ ./ggsci
GGSCI> edit params mgr
内容如下：
port 7830
DYNAMICPORTLIST 7831-7839
--AUTORESTART ER *,RETRIES 5,WAITMINUTES 7
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45


6、配置抽取进程：
GGSCI> add extract extywbj, tranlog, begin now
GGSCI> add exttrail ./dirdat/ey extract extywbj, megabytes 100
GGSCI> edit params extlis
内容如下：
EXTRACT extywbj
SETENV (ORACLE_HOME="/oracle/product/11.2.0/dbhome_1")
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
setenv (ORACLE_SID="lis")
userid goldengate,password goldengate
GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/extywbj.dsc,APPEND,MEGABYTES 1000

WARNLONGTRANS 2h,CHECKINTERVAL 10m
EXTTRAIL ./dirdat/ey

TRANLOGOPTIONS  CONVERTUCS2CLOBS
TRANLOGOPTIONS EXCLUDEUSER goldengate
DBOPTIONS ALLOWUNUSEDCOLUMN
DYNAMICRESOLUTION

FETCHOPTIONS FETCHPKUPDATECOLS

--table
table LIS.ACCOUTNOINF;


7、配置datapump进程：
GGSCI> add extract dpeywbj, exttrailsource ./dirdat/ey
GGSCI> add rmttrail ./dirdat/ry, extract dpeywbj, megabytes 100
GGSCI> edit params dpeywbj
内容如下：
EXTRACT dpeywbj
RMTHOST 10.0.1.43, MGRPORT 7830, compress
PASSTHRU
RMTTRAIL ./dirdat/ry
DYNAMICRESOLUTION

--table
table LIS.ACCOUTNOINF;

8、sequence复制支持：
$ cd /ywogg/oggbj
$ export ORACLE_SID=lis
$ sqlplus / as sysdba;
SQL> @sequence.sql
SQL> GRANT EXECUTE on goldengate.updateSequence TO goldengate;
SQL> GRANT EXECUTE on goldengate.replicateSequence TO goldengate；
$ ./ggsci
GGSCI> start mgr
GGSCI> start extywbj
GGSCI> start dpeywbj
等启动抽取进程后，执行下列命令：
GGSCI> FLUSH SEQUENCE lis.*

二、备份恢复数据库：
1、源端备份：

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup as compressed backupset database include current controlfile format '/workdb/backup/fulllisbackup_%U';
}

或者

run {
# backup the database to disk
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
#backup the whole db
backup as compressed backupset database tag db_lis format '/workdb/backup/db_%t_%s_p%p';
# switch the current log file
SQL 'alter system archive log current';
#backup the archived logs
backup archivelog all tag arch_lis format '/workdb/backup/al_%t_%s_p%p';
# backup a copy of the control file
backup current controlfile tag ctl_lis format '/workdb/backup/cf_%t_%s_p%p';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

2、备份完成后，取SCN并归档当前日志，拷贝归档日志
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
  986903147

SQL> alter system archive log current;
将新归档的包含该SCN的日志也拷贝到目标数据库归档目录，在恢复时需要用到

3、将备份数据scp到目标服务器相同目录下
scp -r backup oracle@ip:/workdb/backup

4、恢复到目标数据库
$ export ORACLE_SID=lis
$ rman target /

RMAN> shutdown immediate;
RMAN> start nomount;
RMAN> restore controlfile from '/workdb/backup/cf_803231969_3712_p1';
RMAN> alter database mount;

RMAN> run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
restore database;
}

RMAN> recover database until scn 887839592;
RMAN> alter database open resetlogs;

5、重建临时表空间
SQL> create temporary tablespace test tempfile '/ywdata/lis/test01.dbf' size 200m;
SQL> alter database default temporary tablespace test;
SQL> drop tablespace temp including contents and datafiles;
SQL> create temporary tablespace temp tempfile '/ywdata/lis/temp01.dbf' size 2g reuse;
SQL> alter database default temporary tablespace temp;
SQL> drop tablespace test including contents and datafiles;
SQL> select file_name from dba_temp_files;


三、目标数据库配置：
目标数据库配置：
禁用触发器、外键约束、job及级联删除：
运行以下4个脚本：
SQL> @disable_triggers.sql
SQL> @disable_jobs.sql
SQL> @disable_fks.sql
SQL> @disable_cas_del.sql
SQL> alter system set job_queue_processes=0

1、安装goldengate：
$ cd /ogg
$ mkdir ogg_account
通过ftp上传GoldenGate软件包到ogg_account安装目录，并解压缩
$ tar -xvf ggs_HPUX_pa_ora11g_64bit.tar
$ ./ggsci
GGSCI> create subdirs
为oracle用户添加环境变量：
export GG_HOME=/ogg/ogg_account
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
注意：需要不需要创建goldengate表空间及同步的用户则按照实际情况考虑


2、目标端GLOBALS配置：
GGSCI> edit params ./GLOBALS
内容如下：
CHECKPOINTTABLE goldengate.oggchkpt
GGSCI> exit
Shell> ggsci
（重新登陆以激活GLOBALS参数）
GGSCI> dblogin userid goldengate,password goldengate
GGSCI> add checkpointtable


3、配置目标端manager进程：
GGSCI> edit params mgr
内容如下：
port 7830
DYNAMICPORTLIST 7831-7839
--AUTORESTART ER *,RETRIES 5,WAITMINUTES 7
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

4、配置目标端复制进程：
GGSCI> dblogin userid goldengate password goldengate
GGSCI> add replicat repywbj, exttrail ./dirdat/ry
GGSCI> edit params repywbj
内容如下：
REPLICAT repywbj
setenv (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV (ORACLE_HOME="/oracle/product/11.2.0/dbhome_1")
setenv (ORACLE_SID="lis")
USERID goldengate, PASSWORD goldengate
--SQLEXEC "ALTER SESSION SET CONSTRAINTS=DEFERRED"
REPORT AT 01:59
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
--numfiles 5000

--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/repywbj.dsc, APPEND, MEGABYTES 1000
GETTRUNCATES
ALLOWNOOPUPDATES

--table
map LIS.ACCOUTNOINF, target LIS.ACCOUTNOINF;

5、启动进程：
GGSCI> alter replicat repywbj extseqno 0, extrba 0
此处的extseqno后面的0代表trail文件的序号，需要到ogg根目录下的dirdat中检查，如果初始配置的话，就是0
GGSCI> start repywbj aftercsn 986903147

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26194851/viewspace-751855/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26194851/viewspace-751855/