Linux教程網 >> Linux基礎 >> 關於Linux >> CENTOS6.5一步一步安裝OGG實現DML DDL單向復制及服務器故障恢復

CENTOS6.5一步一步安裝OGG實現DML DDL單向復制及服務器故障恢復

日期：2017/3/1 12:04:28 编辑：關於Linux

一、環境描述

操作系統版本: CentOS-6.5-x86_64

系統分區：

sda:40G

sda1:500M /boot EXT4

sda2: 39.5G /為LVM,包括:lv_swap 4G,lv_root 35.6G

源端[A]系統數據庫 IP: 192.168.81.211

目標端[B]系統數據庫 IP: 192.168.81.212

Vip:192.168.81.210（規劃中，暫未使用）

數據庫版本: oracle_112030_Linux-x86-64

Oracle Base:/u01/app/oracle

Software location: /u01/app/oracle/11.2.0/dbhome

Inventory directory:/u01/app/oraInventory

Clobal database name: orcl

SID:ogg1

sys/system/sysman/dbsnmp:123456

數據文件存放路徑:/u01/oradata/

OGG 版本: ogg112101_fbo_ggs_Linux_x64_ora11g_64bit

說明：

# 提示符表示用戶root環境下， $提示符表示用戶oracle環境下。

源服務器=通俗的主服務器、目標服務器=通俗的備用服務器

建議：

系統、數據庫軟件在一組物理盤，存儲的數據在另一組物理盤。

二、AB Linux安裝

【A、B端】Linux安裝：
Disc Found -> Skip -> next -> next -> next
Basic storage devices -> next -> yes,discard any data -> next
time zone: Chongqing -> next
password: 123456 -> next -> Use Anyway
Replace existing Linux system -> next -> Write changes to disk
選 Database server 點 Customize now -> next
Databases: 勾掉 2 Mysql 和 2 PostgreSQL
development:除了eclipse外全選
desktops:desktop
HA
Languages:chinese support
Load Balancer
-> next
reboot
create user: NO

三、AB DB安裝

【A、B端】DB安裝：
root登陸系統
啟動網卡：[用光標、TAB鍵切換選擇項、回車鍵確認]以A為例，B只要修改為192.168.81.212，其他一樣：
#setup->network configuration->Run Tool->Device configuration->eth0 (eth0) -...->
│ Name eth0________________ │
│ Device eth0________________ │
│ Use DHCP [ ] │ <- 去掉“*”
│ Static IP 192.168.81.211______ │ <- IP
│ Netmask 255.255.255.0_______ │ <- 掩碼
│ Default gateway IP 192.168.81.1________ │ <- 默認網關
->ok->save->save&Quit->Quit
#ifdown eth0
#ifup eth0
#ifconfig
eth0 Link encap:Ethernet HWaddr 00:0C:29:94:5A:29
inet addr:192.168.81.211 Bcast:192.168.81.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fe94:5a29/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:270 errors:0 dropped:0 overruns:0 frame:0
TX packets:203 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:32463 (31.7 KiB) TX bytes:65227 (63.6 KiB)
..............
拷貝ORACLE環境設置ORACLE環境腳本到各服務器【此為linux筆記本】：
$scp ./go1.sh ./McTestInit [email protected]:/root
$scp ./go2.sh ./McTestInit [email protected]:/root

go1/2.sh:
#!/bin/bash
#CentOS 6.5 64bit 
#Oracle 11g2r ogg
#Libin 2016-3-15 chengdu
if [ $USER != "root" ] 
then
	echo "Not ROOT ! Please login as root !";
else
	bash ./McTestInit 1 ogg1 |tee -a /home/SetEnvLog_`date +%Y-%m-%d`.log
fi

McTestInit:
#!/bin/bash
#CentOS 6.5 64bit 
#Oracle 11g2r ogg
#Libin 2016-3-15 chengdu
d=`date +%Y-%m-%d`;
date
iptest="^[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}$"
if [[ -z ${1} || -z ${2} ]]; then
  echo "Use: McTestInit 1/2 SID1/SID2"
  echo "     SID must Uppercase !"
  exit
fi
#init env
#stop iptables
service iptables stop
chkconfig iptables off
#disable selinux
cp /etc/selinux/config /etc/selinux/config_`date +%Y-%m-%d`.bak
echo '# This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: ' > /etc/selinux/config
echo '#	enforcing - SELinux security policy is enforced.' >> /etc/selinux/config
echo '#	permissive - SELinux prints warnings instead of enforcing.' >> /etc/selinux/config
echo '#	disabled - No SELinux policy is loaded.' >> /etc/selinux/config
echo 'SELINUX=disabled' >> /etc/selinux/config
echo '# SELINUXTYPE= can take one of these two values:' >> /etc/selinux/config
echo '#	targeted - Targeted processes are protected,' >> /etc/selinux/config
echo '#	mls - Multi Level Security protection.' >> /etc/selinux/config
echo 'SELINUXTYPE=targeted' >> /etc/selinux/config
#modify name
cp /etc/sysconfig/network /etc/sysconfig/network_`date +%Y-%m-%d`.bak
echo 'NETWORKING=yes' > /etc/sysconfig/network
echo "HOSTNAME=HTogg${1}" >> /etc/sysconfig/network
echo 'NOZEROCONF=yes' >> /etc/sysconfig/network
#modify hosts
cp /etc/hosts /etc/hosts_`date +%Y-%m-%d`.bak
while [[ ! $rac1 =~ $iptest  ]]; do read -p "Enter HTogg1 IP : " rac1;done 
while [[ ! $rac2 =~ $iptest  ]]; do read -p "Enter HTogg2 IP : " rac2;done
while [[ ! $NetMask =~ $iptest  ]]; do read -p "Enter NETMASK IP : " NetMask;done
while [[ ! $GateWay =~ $iptest  ]]; do read -p "Enter GATEWAY IP : " GateWay;done
nmip[1]=$rac1
nmip[2]=$rac2
echo '127.0.0.1	localhost localhost.localdomain localhost4 localhost4.localdomain4' > /etc/hosts
echo '::1	localhost localhost.localdomain localhost6 localhost6.localdomain6' >> /etc/hosts
echo "$rac1	HTogg1" >> /etc/hosts
echo "$rac2	HTogg2" >> /etc/hosts
#modify net ifcfg-eth0
cp /etc/sysconfig/network-scripts/ifcfg-eth0 /etc/sysconfig/network-scripts/ifcfg-eth0_`date +%Y-%m-%d`.bak
echo "DEVICE=eth0" > /etc/sysconfig/network-scripts/ifcfg-eth0
echo "TYPE=Ethernet" >> /etc/sysconfig/network-scripts/ifcfg-eth0
echo "ONBOOT=yes" >> /etc/sysconfig/network-scripts/ifcfg-eth0
echo "NM_CONTROLLED=yes" >> /etc/sysconfig/network-scripts/ifcfg-eth0
echo "BOOTPROTO=none" >> /etc/sysconfig/network-scripts/ifcfg-eth0
echo "IPADDR="${nmip[${1}]}"" >> /etc/sysconfig/network-scripts/ifcfg-eth0
echo "NETMASK=$NetMask" >> /etc/sysconfig/network-scripts/ifcfg-eth0
echo "GATEWAY=$GateWay" >> /etc/sysconfig/network-scripts/ifcfg-eth0
echo "IPV6INIT=no" >> /etc/sysconfig/network-scripts/ifcfg-eth0
echo "USERCTL=no" >> /etc/sysconfig/network-scripts/ifcfg-eth0
#modify sysctrl
cp /etc/sysctl.conf /etc/sysctl.conf_`date +%Y-%m-%d`.bak
echo "kernel.shmmax = 1073741823" >> /etc/sysctl.conf
echo "kernel.shmmni = 4096" >> /etc/sysctl.conf
echo "kernel.shmall = 4194304" >> /etc/sysctl.conf
echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf
echo "fs.file-max = 65536" >> /etc/sysctl.conf
echo "fs.aio-max-nr = 1048576" >> /etc/sysctl.conf
echo "net.ipv4.ip_local_port_range = 9000 65500" >> /etc/sysctl.conf
echo "net.core.rmem_default = 4194304" >> /etc/sysctl.conf
echo "net.core.rmem_max = 4194304" >> /etc/sysctl.conf
echo "net.core.wmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.wmem_max = 262144" >> /etc/sysctl.conf
echo "kernel.panic = 60" >> /etc/sysctl.conf
sysctl -p
groupadd -g 54321 oinstall
groupadd -g 54322 dba
userdel oracle
rm -rf /home/oracle/
useradd -m -u 1101 -g oinstall -G dba -d /home/oracle -s /bin/bash oracle
echo "enter oracle password: "
passwd oracle
#mkdir
mkdir -p /u01 
chown -R oracle:oinstall /u01 
chmod -R 775 /u01 
#modify limits
cp /etc/security/limits.conf /etc/security/limits.conf_`date +%Y-%m-%d`.bak
echo "oracle soft nproc 2047" >> /etc/security/limits.conf
echo "oracle hard nproc 16384" >> /etc/security/limits.conf
echo "oracle soft nofile 1024" >> /etc/security/limits.conf
echo "oracle hard nofile 65536" >> /etc/security/limits.conf
#modify login
cp /etc/pam.d/login /etc/pam.d/login_`date +%Y-%m-%d`.bak
echo "session required pam_limits.so" >>/etc/pam.d/login
#modify profile
cp /etc/profile /etc/profile_`date +%Y-%m-%d`.bak
echo 'if [ /$USER = "oracle" ]; then' >>/etc/profile
echo '	if [ /$SHELL = "/bin/ksh" ]; then' >>/etc/profile
echo '		ulimit -p 16384' >>/etc/profile
echo '		ulimit -n 65536' >>/etc/profile
echo '	else' >>/etc/profile
echo '		ulimit -u 16384 -n 65536' >>/etc/profile
echo '	fi' >>/etc/profile
echo '	umask 022' >>/etc/profile
echo 'fi' >>/etc/profile
#modify oracle.bash_profile 1.2.
cp /home/oracle/.bash_profile /home/oracle/.bash_profile_`date +%Y-%m-%d`.bak
echo "ORACLE_SID=${2}; export ORACLE_SID" >>/home/oracle/.bash_profile
echo 'ORACLE_UNQNAME=prod; export ORACLE_UNQNAME' >>/home/oracle/.bash_profile
echo 'JAVA_HOME=/usr/local/java; export JAVA_HOME' >>/home/oracle/.bash_profile
echo 'ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE' >>/home/oracle/.bash_profile
echo 'ORACLE_HOME=$ORACLE_BASE/11.2.0/dbhome; export ORACLE_HOME' >>/home/oracle/.bash_profile
echo 'ORACLE_TERM=xterm; export ORACLE_TERM' >>/home/oracle/.bash_profile
echo 'NLS_DATE_FORMAT="YYYY:MM:DD HH24:MI:SS"; export NLS_DATE_FORMAT' >>/home/oracle/.bash_profile
echo 'NLS_LANG=american_america.ZHS16GBK; export NLS_LANG' >>/home/oracle/.bash_profile
echo 'TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN' >>/home/oracle/.bash_profile
echo 'ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11' >>/home/oracle/.bash_profile
echo 'PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME' >>/home/oracle/.bash_profile/bin
echo 'PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin' >>/home/oracle/.bash_profile
echo 'export PATH' >>/home/oracle/.bash_profile
echo 'LD_LIBRARY_PATH=$ORACLE_HOME/lib' >>/home/oracle/.bash_profile
echo 'LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib' >>/home/oracle/.bash_profile
echo 'LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib' >>/home/oracle/.bash_profile
echo 'export LD_LIBRARY_PATH' >>/home/oracle/.bash_profile
echo 'CLASSPATH=$ORACLE_HOME/JRE' >>/home/oracle/.bash_profile
echo 'CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib' >>/home/oracle/.bash_profile
echo 'CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib' >>/home/oracle/.bash_profile
echo 'CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib' >>/home/oracle/.bash_profile
echo 'export CLASSPATH' >>/home/oracle/.bash_profile
echo 'THREADS_FLAG=native; export THREADS_FLAG' >>/home/oracle/.bash_profile
echo 'export TEMP=/tmp' >>/home/oracle/.bash_profile
echo 'export TMPDIR=/tmp' >>/home/oracle/.bash_profile
echo '# ---------------------------------------------------' >>/home/oracle/.bash_profile
echo '# UMASK ' >>/home/oracle/.bash_profile
echo '# ---------------------------------------------------' >>/home/oracle/.bash_profile
echo '# Set the default file mode creation mask ' >>/home/oracle/.bash_profile
echo '# (umask) to 022 to ensure that the user performing # the Oracle software installation creates files ' >>/home/oracle/.bash_profile
echo '# with 644 permissions. ' >>/home/oracle/.bash_profile
echo '# ---------------------------------------------------' >>/home/oracle/.bash_profile
echo 'umask 022' >>/home/oracle/.bash_profile
#del NTP
if read -p "Enter Your NTP IP or press return: " ntpIP; then
 if [ ! -z $ntpIP ]; then
  if [[ $ntpIP =~ $iptest  ]]; then
    ntpdate  $ntpIP
    hwclock -w 
  fi
 fi
fi
service ntpd stop 
service ntpd status 
chkconfig ntpd off 
chkconfig ntpd --list
/etc/init.d/libvirtd stop
chkconfig libvirtd off

echo "set env finished."
date

A:
#bash ./go1.sh
B:
#bash ./go2.sh
【如果用非原版db.iso則必須對database目錄
#chown -R oracle.oinstall ./database
#chmod 755 -R ./database
】

在安裝目錄下執行./runinstaller，如果出現安裝提示must be configured to display at least 256 colors Failed，就執行如下
root 下先執行
#xhost +
#export DISPLAY=:0.0

#su - oracle
#./runinstaller -> y
勾掉：i wish to receive security updates... next -> yes
勾：Skip software updates -> next
Create and configure a database -> next
server class ->
Single istance database installation ->
Advanced install -> next -> next
Enterprise edtion(4.5GB) -> next
Oracle Base:/u01/app/oracle
Software location: /u01/app/oracle/11.2.0/dbhome
Inventory directory:/u01/app/oraInventory -> next
General Purpose / Transaction Processing -> next
Clobal database name: orcl
SID:ogg1 -> next
Memory(sga,pga) 勾：Enable Automatic Memory Management
Character sets:Traditional Chinese ZHT32EUC -> next -> next
Specify database file localtion: /u01/app/oracle/oradata -> next
Do not enable automated backups -> next
Use the same password all accounts:123456 -> next -> yes
Database operator group:oinstall -> next
換centOS6.5光盤:
#cd /media/cdrom/Pa...
#rpm -ivh libaio-devel-0.3.107-10.el6.x86_64.rpm
#rpm -ivh compat-libstdc++-33-3.2.3-69.el6.x86_64.rpm
checks: pdksh-5.2.14 -> ignore all -> next -> yes -> install
Global Database Name:orcl
System Identifier(SID):ogg1
Parameter filename:/u01/app/oracle/11.2.0/dbhome/dbs/spfileogg1.ora
Enterprise Manager database Control URL:
https://HTogg1:1158/em
https://HTogg2:1158/em
#/u01/app/oraInventory/orainstRoot.sh
#u01/app/oracle/11.2.0/dbhome/root.sh 回2次車
-> ok -> close
install session at:
/u01/app/oraInventory/logs/installActions2016-...

本地設置ORACLE為自動隨系統啟動關閉：
$scp ./oracle ./dbSet.sh [email protected]/212:/root

dbSet.sh:
#!/bin/bash
#CentOS 6.5 64bit 
#Oracle 11g2r ogg
#Libin 2016-3-15 chengdu
d=`date +%Y-%m-%d`;
if [ $USER != "root" ] 
then
	echo "Not ROOT ! Please login as root !";
else
	cp /etc/oratab /etc/oratab_`date +%Y-%m-%d`.bak 
	echo 'ogg1:/u01/app/oracle/11.2.0/dbhome:Y' >/etc/oratab
	cp ./oracle /etc/init.d/
	chmod 755 /etc/init.d/oracle
	chkconfig --level 35 oracle on
	ln -s /etc/init.d/oracle /etc/rc0.d/K01oracle
	ln -s /etc/init.d/oracle /etc/rc6.d/K01oracle
fi

oracle:
#!/bin/sh
# chkconfig: 35 80 10
# description: Oracle auto start-stop script.
#
# Set ORACLE_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORACLE_HOME.
ORACLE_HOME=/u01/app/oracle/11.2.0/dbhome
ORA_OWNER=oracle

case "$1" in
'start')
# Start the Oracle databases:
echo "Starting Oracle Databases ... "
echo "-------------------------------------------------" >> /var/log/oracle
date +" %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle
echo "-------------------------------------------------" >> /var/log/oracle
su - $ORA_OWNER -c "$ORACLE_HOME/bin/dbstart" >>/var/log/oracle
echo "Done"
# Start the Listener:
echo "Starting Oracle Listeners ... "
echo "-------------------------------------------------" >> /var/log/oracle
date +" %T %a %D : Starting Oracle Listeners as part of system up." >> /var/log/oracle
echo "-------------------------------------------------" >> /var/log/oracle
su - $ORA_OWNER -c "$ORACLE_HOME/bin/lsnrctl start" >>/var/log/oracle
echo "Done."
echo "-------------------------------------------------" >> /var/log/oracle
date +" %T %a %D : Finished." >> /var/log/oracle
echo "-------------------------------------------------" >> /var/log/oracle
touch /var/lock/subsys/oracle
;;

'stop')
# Stop the Oracle Listener:
echo "Stoping Oracle Listeners ... "
echo "-------------------------------------------------" >> /var/log/oracle
date +" %T %a %D : Stoping Oracle Listener as part of system down." >> /var/log/oracle
echo "-------------------------------------------------" >> /var/log/oracle
su - $ORA_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop" >>/var/log/oracle
echo "Done."
rm -f /var/lock/subsys/oracle
# Stop the Oracle Database:
echo "Stoping Oracle Databases ... "
echo "-------------------------------------------------" >> /var/log/oracle
date +" %T %a %D : Stoping Oracle Databases as part of system down." >> /var/log/oracle
echo "-------------------------------------------------" >> /var/log/oracle
su - $ORA_OWNER -c "$ORACLE_HOME/bin/dbshut" >>/var/log/oracle
echo "Done."
echo ""
echo "-------------------------------------------------" >> /var/log/oracle
date +" %T %a %D : Finished." >> /var/log/oracle
echo "-------------------------------------------------" >> /var/log/oracle
;;

'restart')
$0 stop
$0 start
;;

*)
$0 start
;;
esac

A/B：
#bash ./dbSet.sh

【A、B端】測試DB：
$cd $ORACLE_HOME/bin/
$./lsnrctl status
$./sqlplus /nolog
SQL>conn / as sysdba;
SQL>startup;
SQL>desc tab;
SQL>
with a as
(select 101 - rownum n from dual connect by rownum <102),
max_one as
(select max(n) max1 from a),
max_two as
(select /*+leading(p2,p1) use_nl(p1) */ p2.n max2,p1.n max1
from a p1,a p2
where p1.n+p2.n=100
and p1.n=(select max1 from max_one)
and rownum=1)
select * from max_two;
MAX2 MAX1
------ -----
0 100
SQL>quit

四、DML單向復制

【DML單向A->B復制】參考資料“手把手教你安裝和配置OGG，並實現單向DML復制技術.html完全OK”
唯一，重新啟動系統後，要手動啟動ggsci中的各服務start mgr、start *
A:
sql:
create tablespace goldengate datafile '/u01/ogg/goldengate01.dbf' size 100m autoextend on;
create user goldengate identified by goldengate default tablespace goldengate temporary tablespace temp;
grant connect to goldengate;
grant alter any table to goldengate;
grant alter session to goldengate;
grant create session to goldengate;
grant flashback any table to goldengate;
grant select any dictionary to goldengate;
grant select any table to goldengate;
grant resource to goldengate;
grant select any transaction to goldengate;
archive log list
select supplemental_log_data_min from v$database;
alter system switch logfile;
其實以上grant 可以簡潔為1句：GRANT CONNECT,RESOURCE,DBA TO goldengate;
ggsci:
create subdirs
edit params mgr

port 7839
DYNAMICPORTLIST 7840-7850
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

start mgr
info all
GGSCI (HTogg1) 5> info mgr
Manager is running (IP port HTogg1.7839).

A:
select owner||'.'||table_name table_name,logging from dba_tables where owner='ITS';
TABLE_NAME
--------------------------------------------------------------------------------
LOGGIN
------
ITS.BBB
YES

ITS.AAA
YES

ggsci:
dblogin userid goldengate, password goldengate
info trandata its.*
add trandata hr.*
add extract ext_01, tranlog, begin now, threads 1
add EXTTRAIL ./dirdat/r1, extract ext_01,megabytes 100
edit param ext_01

EXTRACT ext_01
setenv (ORACLE_SID=ogg1)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid goldengate,password goldengate
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/ext_01.dsc,APPEND,MEGABYTES 1000
DISCARDROLLOVER AT 3:00
exttrail ./dirdat/r1,megabytes 100
dynamicresolution
TRANLOGOPTIONS EXCLUDEUSER goldengate
TRANLOGOPTIONS convertucs2clobs
TABLE ITS.*;

start ext_01

add extract dpe_01, exttrailsource ./dirdat/r1
add rmttrail ./dirdat/t1,EXTRACT dpe_01,MEGABYTES 100
edit param dpe_01

extract dpe_01
dynamicresolution
passthru
rmthost 192.168.81.212, mgrport 7839, compress
rmttrail ./dirdat/t1
numfiles 5000
TABLE ITS.*;

start dpe_01

B:
sql:
create tablespace goldengate datafile '/u01/ogg/goldengate01.dbf' size 100m autoextend on;
create user goldengate identified by goldengate default tablespace goldengate temporary tablespace temp;
grant connect to goldengate;
grant alter any table to goldengate;
grant alter session to goldengate;
grant create session to goldengate;
grant flashback any table to goldengate;
grant select any dictionary to goldengate;
grant select any table to goldengate;
grant resource to goldengate;
grant insert any table to goldengate;
grant update any table to goldengate;
grant delete any table to goldengate;
grant create any index to goldengate;
grant select any transaction to goldengate;
其實以上grant 可以簡潔為1句：GRANT CONNECT,RESOURCE,DBA TO goldengate;
B：
[oracle@HTogg2 ogg]$ ./ggsci
GGSCI (HTogg2) 2> create subdirs
GGSCI (HTogg2) 3> edit params mgr
port 7839
DYNAMICPORTLIST 7840-7850
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

start mgr
info all
info mgr
Manager is running (IP port HTogg2.7839).

dblogin userid goldengate,password goldengate
add checkpointtable goldengate.rep_01_ckpt
add replicat rep_01,exttrail ./dirdat/t1,checkpointtable goldengate.rep_01_ckpt
edit param rep_01

REPLICAT rep_01
SETENV (ORACLE_SID=ogg1)
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
USERID goldengate,PASSWORD goldengate
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/rep_01.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
MAP ITS.*, TARGET ITS.*;

start rep_01

測試：insert delete update OK!!
小心由於AB數據不一致，導致ogg B replicat 進程 abended
所以，最好先停業務、停OGG服務，導A數據到B，然後再啟動Bogg、Aogg、業務

五、DDL單向復制

【DDL單向A->B復制】參考“為已經運行的GoldenGate開啟DDL選項”
1. 源端支持DDL復制運行腳本
2. 修改源端extract進程的params文件
3. 修改目標端replicat進程的params文件
4. 測試

1.分別停止source、target端的OGG進程
stop *
stop mgr

2.source端抽取進程參數添加DDL選項
GGSCI (node3) 43> edit params ext_01
...
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA, REPORT
...

3.target端復制進程添加DDL選項
GGSCI (single) 19> edit params rep_01
...
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
...
4. 為OGG用戶顯示添加權限
grant dba to goldengate;--可省
grant create table,create sequence to goldengate;--可省
@role_setup.sql
GRANT GGS_GGSUSER_ROLE TO goldengate;

@marker_setup.sql
name:goldengate
@ddl_setup.sql
name:goldengate
@ddl_enable.sql

--@ddl_disable.sql
--@ddl_remove.sql
--@marker_remove.sql

分別開啟source、target端各個進程
start mgr
start *

測試：
source
create table tt as select * from aaa;
target
desc tt;
source
ALTER TABLE tt ADD cc varchar2(4) default 'zz';
target
desc tt;
source
ALTER TABLE aaa ADD cc varchar2(4) default 'zz';
target
select * from aaa;

OK!!

？？alter system set recyclebin=off; 10G?
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
？？target
edit params rt1
ddlerror default ignore retryop maxretries 3 retrydelay 5
??@ddl_pin.sql
??@/rdbms/admin/dbmspool.sql

測試權限：GRANT CONNECT,RESOURCE,DBA TO OGG;
revoke
B:
sql:
revoke alter any table from goldengate;
revoke alter session from goldengate;
revoke create session from goldengate;
revoke flashback any table from goldengate;
revoke select any dictionary from goldengate;
revoke select any table from goldengate;
revoke insert any table from goldengate;
revoke update any table from goldengate;
revoke delete any table from goldengate;
revoke create any index from goldengate;
revoke select any transaction from goldengate;
revoke create table,create sequence from goldengate;
grant connect to goldengate;
grant resource to goldengate;
grant dba to goldengate;
A:
sql:
revoke alter any table from goldengate;
revoke alter session from goldengate;
revoke create session from goldengate;
revoke flashback any table from goldengate;
revoke select any dictionary from goldengate;
revoke select any table from goldengate;
revoke select any transaction from goldengate;
revoke create table,create sequence from goldengate;
grant connect to goldengate;
grant resource to goldengate;
grant dba to goldengate;

source
create table tt2 as select * from aaa;
insert into tt2 values('t2','t2','t2');
target
desc tt2;

ok!!
source
create table tt3 as select * from aaa;
insert into tt3 values('t3','t3','t3');
commit;
target
desc tt2;
select * from tt3;
source
delete from tt3 where cc='t3';
commit;
update tt3 set cc='c3';
commit;
alter table tt3 add xx varchar2(2) default 'x4';
alter table tt3 rename column xx to dd;
alter table tt3 modify dd varchar2(4) default 'x4';
target
select * from tt3;

ok!!

六、問題場景描述

源服務器A出問題[如系統崩潰、數據庫崩潰、磁盤壞等]切換操作：

把問題服務器A修復後，設置為目標服務器B，把之前的目標服務器B設置為源服務器A。AB的IP等系統設置不變。

目標服務器B出問題，直接按照新裝目標服務器搭建即可。

假設A服務器192.168.81.211崩潰的恢復【恢復後當目標機B用】步驟：

1.恢復A.211後當目標B.211機用

2.設置原來的目標B.212機為現在的源端A.212機用

3.依據SCN從212導數據到211，或者直接從A完全導數據到B

4.啟動211、212的OGG服務

注意：

A、B數據不同，容易導致OGG服務掛起！！！

在212數據導出之前，必須關閉數據對外服務，待數據導出開始後，啟動源端OGG的各服務後，才能啟動源端數據的對外服務；在目的端211，必須等數據導入後，才能啟動OGG各服務。否則會因為中間產生無法同步的數據，導致生產中OGG DOWN機各服務掛起。

或者：斷掉數據服務，導數據A->B，開A、B的OGG各個服務，提供數據服務。

七、恢復A為目標服務器

192.168.81.211原來的源端A.211機設置為目標【B.211】端：

1、按“myDB20160417”文檔安裝系統、數據庫【IP、SID等不變，還為A.211的】

$scp ./oracle ./dbSet.sh [email protected]:/root

#bash ./dbSet.sh

具體腳本：

dbSet.sh:

#!/bin/bash

#CentOS 6.5 64bit

#Oracle 11g2r ogg

#Libin 2016-3-15 chengdu

d=`date +%Y-%m-%d`;

if [ $USER != "root" ]

then

echo "Not ROOT ! Please login as root !";

else

cp /etc/oratab /etc/oratab_`date +%Y-%m-%d`.bak

echo 'ogg1:/u01/app/oracle/11.2.0/dbhome:Y' >/etc/oratab

cp ./oracle /etc/init.d/

chmod 755 /etc/init.d/oracle

chkconfig --level 35 oracle on

ln -s /etc/init.d/oracle /etc/rc0.d/K01oracle

ln -s /etc/init.d/oracle /etc/rc6.d/K01oracle

oracle:

#!/bin/sh

# chkconfig: 35 80 10

# description: Oracle auto start-stop script.

# Set ORACLE_HOME to be equivalent to the $ORACLE_HOME

# from which you wish to execute dbstart and dbshut;

# Set ORA_OWNER to the user id of the owner of the

# Oracle database in ORACLE_HOME.

ORACLE_HOME=/u01/app/oracle/11.2.0/dbhome

ORA_OWNER=oracle

case "$1" in

'start')

# Start the Oracle databases:

echo "Starting Oracle Databases ... "