CENTOS6.5一步一步安装OGG实现DML DDL SEQUENCE序列单向复制及服务器故障恢复

来源：互联网发布：搜客户软件编辑：程序博客网时间：2024/06/05 22:54

一、环境描述

操作系统版本: 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 root@192.168.81.211:/root
$scp ./go2.sh ./McTestInit root@192.168.81.212:/root

go1/2.sh:#!/bin/bash#CentOS 6.5 64bit #Oracle 11g2r ogg#Libin 2016-3-15 chengduif [ $USER != "root" ] thenecho "Not ROOT ! Please login as root !";elsebash ./McTestInit 1 ogg1 |tee -a /home/SetEnvLog_`date +%Y-%m-%d`.logfiMcTestInit:#!/bin/bash#CentOS 6.5 64bit #Oracle 11g2r ogg#Libin 2016-3-15 chengdud=`date +%Y-%m-%d`;dateiptest="^[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 !"  exitfi#init env#stop iptablesservice iptables stopchkconfig iptables off#disable selinuxcp /etc/selinux/config /etc/selinux/config_`date +%Y-%m-%d`.bakecho '# This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: ' > /etc/selinux/configecho '#enforcing - SELinux security policy is enforced.' >> /etc/selinux/configecho '#permissive - SELinux prints warnings instead of enforcing.' >> /etc/selinux/configecho '#disabled - No SELinux policy is loaded.' >> /etc/selinux/configecho 'SELINUX=disabled' >> /etc/selinux/configecho '# SELINUXTYPE= can take one of these two values:' >> /etc/selinux/configecho '#targeted - Targeted processes are protected,' >> /etc/selinux/configecho '#mls - Multi Level Security protection.' >> /etc/selinux/configecho 'SELINUXTYPE=targeted' >> /etc/selinux/config#modify namecp /etc/sysconfig/network /etc/sysconfig/network_`date +%Y-%m-%d`.bakecho 'NETWORKING=yes' > /etc/sysconfig/networkecho "HOSTNAME=HTogg${1}" >> /etc/sysconfig/networkecho 'NOZEROCONF=yes' >> /etc/sysconfig/network#modify hostscp /etc/hosts /etc/hosts_`date +%Y-%m-%d`.bakwhile [[ ! $rac1 =~ $iptest  ]]; do read -p "Enter HTogg1 IP : " rac1;done while [[ ! $rac2 =~ $iptest  ]]; do read -p "Enter HTogg2 IP : " rac2;donewhile [[ ! $NetMask =~ $iptest  ]]; do read -p "Enter NETMASK IP : " NetMask;donewhile [[ ! $GateWay =~ $iptest  ]]; do read -p "Enter GATEWAY IP : " GateWay;donenmip[1]=$rac1nmip[2]=$rac2echo '127.0.0.1localhost localhost.localdomain localhost4 localhost4.localdomain4' > /etc/hostsecho '::1localhost localhost.localdomain localhost6 localhost6.localdomain6' >> /etc/hostsecho "$rac1HTogg1" >> /etc/hostsecho "$rac2HTogg2" >> /etc/hosts#modify net ifcfg-eth0cp /etc/sysconfig/network-scripts/ifcfg-eth0 /etc/sysconfig/network-scripts/ifcfg-eth0_`date +%Y-%m-%d`.bakecho "DEVICE=eth0" > /etc/sysconfig/network-scripts/ifcfg-eth0echo "TYPE=Ethernet" >> /etc/sysconfig/network-scripts/ifcfg-eth0echo "ONBOOT=yes" >> /etc/sysconfig/network-scripts/ifcfg-eth0echo "NM_CONTROLLED=yes" >> /etc/sysconfig/network-scripts/ifcfg-eth0echo "BOOTPROTO=none" >> /etc/sysconfig/network-scripts/ifcfg-eth0echo "IPADDR="${nmip[${1}]}"" >> /etc/sysconfig/network-scripts/ifcfg-eth0echo "NETMASK=$NetMask" >> /etc/sysconfig/network-scripts/ifcfg-eth0echo "GATEWAY=$GateWay" >> /etc/sysconfig/network-scripts/ifcfg-eth0echo "IPV6INIT=no" >> /etc/sysconfig/network-scripts/ifcfg-eth0echo "USERCTL=no" >> /etc/sysconfig/network-scripts/ifcfg-eth0#modify sysctrlcp /etc/sysctl.conf /etc/sysctl.conf_`date +%Y-%m-%d`.bakecho "kernel.shmmax = 1073741823" >> /etc/sysctl.confecho "kernel.shmmni = 4096" >> /etc/sysctl.confecho "kernel.shmall = 4194304" >> /etc/sysctl.confecho "kernel.sem = 250 32000 100 128" >> /etc/sysctl.confecho "fs.file-max = 65536" >> /etc/sysctl.confecho "fs.aio-max-nr = 1048576" >> /etc/sysctl.confecho "net.ipv4.ip_local_port_range = 9000 65500" >> /etc/sysctl.confecho "net.core.rmem_default = 4194304" >> /etc/sysctl.confecho "net.core.rmem_max = 4194304" >> /etc/sysctl.confecho "net.core.wmem_default = 262144" >> /etc/sysctl.confecho "net.core.wmem_max = 262144" >> /etc/sysctl.confecho "kernel.panic = 60" >> /etc/sysctl.confsysctl -pgroupadd -g 54321 oinstallgroupadd -g 54322 dbauserdel oraclerm -rf /home/oracle/useradd -m -u 1101 -g oinstall -G dba -d /home/oracle -s /bin/bash oracleecho "enter oracle password: "passwd oracle#mkdirmkdir -p /u01 chown -R oracle:oinstall /u01 chmod -R 775 /u01 #modify limitscp /etc/security/limits.conf /etc/security/limits.conf_`date +%Y-%m-%d`.bakecho "oracle soft nproc 2047" >> /etc/security/limits.confecho "oracle hard nproc 16384" >> /etc/security/limits.confecho "oracle soft nofile 1024" >> /etc/security/limits.confecho "oracle hard nofile 65536" >> /etc/security/limits.conf#modify logincp /etc/pam.d/login /etc/pam.d/login_`date +%Y-%m-%d`.bakecho "session required pam_limits.so" >>/etc/pam.d/login#modify profilecp /etc/profile /etc/profile_`date +%Y-%m-%d`.bakecho 'if [ /$USER = "oracle" ]; then' >>/etc/profileecho 'if [ /$SHELL = "/bin/ksh" ]; then' >>/etc/profileecho 'ulimit -p 16384' >>/etc/profileecho 'ulimit -n 65536' >>/etc/profileecho 'else' >>/etc/profileecho 'ulimit -u 16384 -n 65536' >>/etc/profileecho 'fi' >>/etc/profileecho 'umask 022' >>/etc/profileecho 'fi' >>/etc/profile#modify oracle.bash_profile 1.2.cp /home/oracle/.bash_profile /home/oracle/.bash_profile_`date +%Y-%m-%d`.bakecho "ORACLE_SID=${2}; export ORACLE_SID" >>/home/oracle/.bash_profileecho 'ORACLE_UNQNAME=prod; export ORACLE_UNQNAME' >>/home/oracle/.bash_profileecho 'JAVA_HOME=/usr/local/java; export JAVA_HOME' >>/home/oracle/.bash_profileecho 'ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE' >>/home/oracle/.bash_profileecho 'ORACLE_HOME=$ORACLE_BASE/11.2.0/dbhome; export ORACLE_HOME' >>/home/oracle/.bash_profileecho 'ORACLE_TERM=xterm; export ORACLE_TERM' >>/home/oracle/.bash_profileecho 'NLS_DATE_FORMAT="YYYY:MM:DD HH24:MI:SS"; export NLS_DATE_FORMAT' >>/home/oracle/.bash_profileecho 'NLS_LANG=american_america.ZHS16GBK; export NLS_LANG' >>/home/oracle/.bash_profileecho 'TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN' >>/home/oracle/.bash_profileecho 'ORA_NLS11=$ORACLE_HOME/nls/data; export ORA_NLS11' >>/home/oracle/.bash_profileecho 'PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME' >>/home/oracle/.bash_profile/binecho 'PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin' >>/home/oracle/.bash_profileecho 'export PATH' >>/home/oracle/.bash_profileecho 'LD_LIBRARY_PATH=$ORACLE_HOME/lib' >>/home/oracle/.bash_profileecho 'LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib' >>/home/oracle/.bash_profileecho 'LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib' >>/home/oracle/.bash_profileecho 'export LD_LIBRARY_PATH' >>/home/oracle/.bash_profileecho 'CLASSPATH=$ORACLE_HOME/JRE' >>/home/oracle/.bash_profileecho 'CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib' >>/home/oracle/.bash_profileecho 'CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib' >>/home/oracle/.bash_profileecho 'CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib' >>/home/oracle/.bash_profileecho 'export CLASSPATH' >>/home/oracle/.bash_profileecho 'THREADS_FLAG=native; export THREADS_FLAG' >>/home/oracle/.bash_profileecho 'export TEMP=/tmp' >>/home/oracle/.bash_profileecho 'export TMPDIR=/tmp' >>/home/oracle/.bash_profileecho '# ---------------------------------------------------' >>/home/oracle/.bash_profileecho '# UMASK ' >>/home/oracle/.bash_profileecho '# ---------------------------------------------------' >>/home/oracle/.bash_profileecho '# Set the default file mode creation mask ' >>/home/oracle/.bash_profileecho '# (umask) to 022 to ensure that the user performing # the Oracle software installation creates files ' >>/home/oracle/.bash_profileecho '# with 644 permissions. ' >>/home/oracle/.bash_profileecho '# ---------------------------------------------------' >>/home/oracle/.bash_profileecho 'umask 022' >>/home/oracle/.bash_profile#del NTPif read -p "Enter Your NTP IP or press return: " ntpIP; then if [ ! -z $ntpIP ]; then  if [[ $ntpIP =~ $iptest  ]]; then    ntpdate  $ntpIP    hwclock -w   fi fifiservice ntpd stop service ntpd status chkconfig ntpd off chkconfig ntpd --list/etc/init.d/libvirtd stopchkconfig libvirtd offecho "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 root@192.168.81.211/212:/root

dbSet.sh:#!/bin/bash#CentOS 6.5 64bit #Oracle 11g2r ogg#Libin 2016-3-15 chengdud=`date +%Y-%m-%d`;if [ $USER != "root" ] thenecho "Not ROOT ! Please login as root !";elsecp /etc/oratab /etc/oratab_`date +%Y-%m-%d`.bak echo 'ogg1:/u01/app/oracle/11.2.0/dbhome:Y' >/etc/oratabcp ./oracle /etc/init.d/chmod 755 /etc/init.d/oraclechkconfig --level 35 oracle onln -s /etc/init.d/oracle /etc/rc0.d/K01oracleln -s /etc/init.d/oracle /etc/rc6.d/K01oraclefioracle:#!/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/dbhomeORA_OWNER=oraclecase "$1" in'start')# Start the Oracle databases:echo "Starting Oracle Databases ... "echo "-------------------------------------------------" >> /var/log/oracledate +" %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracleecho "-------------------------------------------------" >> /var/log/oraclesu - $ORA_OWNER -c "$ORACLE_HOME/bin/dbstart" >>/var/log/oracleecho "Done"# Start the Listener:echo "Starting Oracle Listeners ... "echo "-------------------------------------------------" >> /var/log/oracledate +" %T %a %D : Starting Oracle Listeners as part of system up." >> /var/log/oracleecho "-------------------------------------------------" >> /var/log/oraclesu - $ORA_OWNER -c "$ORACLE_HOME/bin/lsnrctl start" >>/var/log/oracleecho "Done."echo "-------------------------------------------------" >> /var/log/oracledate +" %T %a %D : Finished." >> /var/log/oracleecho "-------------------------------------------------" >> /var/log/oracletouch /var/lock/subsys/oracle;;'stop')# Stop the Oracle Listener:echo "Stoping Oracle Listeners ... "echo "-------------------------------------------------" >> /var/log/oracledate +" %T %a %D : Stoping Oracle Listener as part of system down." >> /var/log/oracleecho "-------------------------------------------------" >> /var/log/oraclesu - $ORA_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop" >>/var/log/oracleecho "Done."rm -f /var/lock/subsys/oracle# Stop the Oracle Database:echo "Stoping Oracle Databases ... "echo "-------------------------------------------------" >> /var/log/oracledate +" %T %a %D : Stoping Oracle Databases as part of system down." >> /var/log/oracleecho "-------------------------------------------------" >> /var/log/oraclesu - $ORA_OWNER -c "$ORACLE_HOME/bin/dbshut" >>/var/log/oracleecho "Done."echo ""echo "-------------------------------------------------" >> /var/log/oracledate +" %T %a %D : Finished." >> /var/log/oracleecho "-------------------------------------------------" >> /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 root@192.168.81.211:/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 ... "