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