操作系統版本: 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環境下。
源服務器=通俗的主服務器、目標服務器=通俗的備用服務器
建議:
系統、數據庫軟件在一組物理盤,存儲的數據在另一組物理盤。
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." dateA:
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 ;; esacA/B:
【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、業務
源服務器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各個服務,提供數據服務。
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
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
2、ogg安裝:
$scp -r ./ogg* [email protected]:/u01
#cd /u01
#bash oggSet.sh
具體腳本:
oggSet.sh:
#!/bin/bash
#CentOS 6.5 64bit
#Oracle 11g2r ogg
#Libin 2016-3-15 chengdu
oh="/u01/app/oracle/11.2.0/dbhome"
oup="sys/123456"
oggup="ogg/ogg"
cdn="orcl"
#d="/home/oracle/ogg";
d2="/u01/ogg";
d3="/u01/archivelog";
d4="/u01/oradata";
if [ $USER != "root" ]
then
echo "Not ROOT ! Please login as root !";
else
mkdir $d2 $d3 $d4 # $d
tar zxvf ./ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.tar.gz -C $d2
chown -R oracle:oinstall $d2 $d3 $d4 /home/oracle #$d
chmod -R 775 $d2
su - oracle -c "$oh/bin/sqlplus $oup as sysdba @$(pwd)/oggAchiveSet.sql"
su - oracle -c "$oh/bin/sqlplus $oggup @$(pwd)/oggchk.sql"
fi
oggAchiveSet.sql:
--archivelog
shutdown immediate
startup mount;
--alter system set recyclebin=off scope=both; 無法修改,應該是10G的設置
alter system set db_recovery_file_dest='' scope=both;
alter system set log_archive_dest_1='location=/u01/archivelog' scope = both;
alter database archivelog;
alter database open;
shutdown immediate
startup
--log
alter database force logging;
alter database add supplemental log data;
alter database add supplemental log data (primary key,unique index,foreign key) columns;
--ogg set
CREATE TABLESPACE OGG_ITS DATAFILE '/u01/oradata/ogg_its01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 32767M;
CREATE USER ogg IDENTIFIED BY ogg DEFAULT TABLESPACE OGG_ITS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
GRANT CONNECT,RESOURCE,DBA TO OGG;
quit
oggchk.sql:
@@/u01/ogg/chkpt_ora_create.sql
quit
目標【B】端 OGG配置:
$ cd /u01/ogg
$ ./ggsci
步驟:
GGSCI (HTogg1) 1> create subdirs
GGSCI (HTogg1) 2> edit param mgr <-具體參數見下面
GGSCI (HTogg1) 3> edit param GLOBALS <-具體參數見下面
GGSCI (HTogg1) 4> add replicat repl01,checkpointtable ogg.ggs_checkpoint,exttrail ./dirdat/ht
GGSCI (HTogg1) 5>edit param repl01 <-具體參數見下面
具體的:【
GGSCI (HTogg1) 1> create subdirs
Creating subdirectories under current directory /u01/ogg
Parameter files /u01/ogg/dirprm: already exists
Report files /u01/ogg/dirrpt:created
Checkpoint files /u01/ogg/dirchk: created
Process status files /u01/ogg/dirpcs: created
SQL script files /u01/ogg/dirsql: created
Database definitions files /u01/ogg/dirdef: created
Extract data files /u01/ogg/dirdat: created
Temporary files /u01/ogg/dirtmp: created
Stdout files /u01/ogg/dirout: created
GGSCI (HTogg1) 2>edit param mgr
PORT 7909
DYNAMICPORTLIST 7940-7950
AUTOSTART REPLICAT *
AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 7
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
GGSCI (HTogg1) 3>edit param GLOBALS
GGSCHEMA ogg
CHECKPOINTTABLE ogg.ggs_checkpoint
GGSCI (HTogg1) 4> add replicat repl01,checkpointtable ogg.ggs_checkpoint,exttrail ./dirdat/ht
REPLICAT added.
GGSCI (HTogg1) 5>edit param repl01
REPLICAT repl01
SETENV (NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8")
USERID ogg,PASSWORD ogg
REPORT AT 01:50
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORTROLLOVER AT 02:00
REPERROR DEFAULT, ABEND
NUMFILES 1000
GROUPTRANSOPS 3000
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/repl01.dsc, APPEND, MEGABYTES 200
DISCARDROLLOVER AT 02:00
GETTRUNCATES
ALLOWNOOPUPDATES
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5
DDLOPTIONS REPORT
MAPEXCLUDE ITS.BAYMONRECSTAT
MAP ITS.*,TARGET ITS.*;
GGSCI (HTogg1) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
REPLICAT STOPPED REPL01 00:00:00 00:01:01 】
192.168.81.212原來的目標B.212機設置為現在的源端A.212機【A】端:
1、刪除REPLICAT配置參數
2、重新設置源端A.212機
$ cd /u01/ogg
$ ./ggsci
GGSCI () 1> dblogin userid ogg,password ogg
GGSCI () 2> delete replicat repl01
【
GGSCI (HTogg2) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
REPLICAT ABENDED REPL01 00:00:00 170:45:41
GGSCI (HTogg2) 5> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (HTogg2) 6> delete replicat repl01
Deleted REPLICAT REPL01.
GGSCI (HTogg2) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
】
$ cd /u01/ogg
$rm ./dir開頭的目錄下的除了jar外的所有文件
$rm *.dmp
$rm *.txt
$rm *.log
$ cd /u01/ogg
$ $ORACLE_HOME/bin/sqlplus / as sysdba
SQL> @marker_setup.sql
出現Enter Oracle GoldenGate schema name:輸入ogg
SQL> @ddl_setup.sql
出現Enter Oracle GoldenGate schema name:輸入ogg
SQL> @role_setup.sql
出現Enter GoldenGate schema name:輸入ogg
SQL> GRANT GGS_GGSUSER_ROLE TO ogg;
SQL> @ddl_enable.sql
SQL> quit
$ cd /u01/ogg
$ ./ggsci
GGSCI (HTogg2) 1> create subdirs
GGSCI (HTogg2) 2> edit param mgr
GGSCI (HTogg2) 3> edit param GLOBALS
GGSCI (HTogg2) 4> edit param extr01
GGSCI (HTogg2) 5> edit param pump01
GGSCI (HTogg2) 6> add extract extr01,tranlog,begin now
GGSCI (HTogg2) 7> add exttrail ./dirdat/ht,extract extr01,megabytes 50
GGSCI (HTogg2) 8> add extract pump01,exttrailsource ./dirdat/ht
GGSCI (HTogg2) 9> add rmttrail ./dirdat/ht,extract pump01,megabytes 50
GGSCI (HTogg2) 10> dblogin userid ogg, password ogg
GGSCI (HTogg2) 11> add trandata its.*
GGSCI (HTogg2) 12> info all
【具體配置:
GGSCI (HTogg2) 1> create subdirs
Creating subdirectories under current directory /u01/ogg
Parameter files /u01/ogg/dirprm: already exists
Report files /u01/ogg/dirrpt: already exists
Checkpoint files /u01/ogg/dirchk: already exists
Process status files /u01/ogg/dirpcs: already exists
SQL script files /u01/ogg/dirsql: already exists
Database definitions files /u01/ogg/dirdef: already exists
Extract data files /u01/ogg/dirdat: already exists
Temporary files /u01/ogg/dirtmp: already exists
Stdout files /u01/ogg/dirout: already exists
GGSCI (HTogg2) 2> edit param mgr
PORT 7909
DYNAMICPORTLIST 7940-7950
AUTOSTART ER *
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 7
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
GGSCI (HTogg2) 3> edit param GLOBALS
GGSCHEMA ogg
CHECKPOINTTABLE ogg.ggs_checkpoint
GGSCI (HTogg2) 4> edit param extr01
EXTRACT extr01
SETENV (NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8")
USERID ogg, PASSWORD ogg
GETTRUNCATES
REPORTCOUNT EVERY 30 MINUTES, RATE
NUMFILES 1000
DISCARDFILE ./dirrpt/extr01.dsc, APPEND, MEGABYTES 200
DISCARDROLLOVER AT 3:00
WARNLONGTRANS 2h, CHECKINTERVAL 3m
EXTTRAIL ./dirdat/ht, MEGABYTES 200
DYNAMICRESOLUTION
DDL INCLUDE ALL
TRANLOGOPTIONS EXCLUDEUSER ogg
TABLE its.*;
GGSCI (HTogg2) 5> edit param pump01
EXTRACT pump01
RMTHOST 192.168.81.211, MGRPORT 7909
PASSTHRU
NUMFILES 1000
RMTTRAIL ./dirdat/ht
DYNAMICRESOLUTION
TABLE its.*;
GGSCI (HTogg2) 6> add extract extr01,tranlog,begin now
EXTRACT added.
GGSCI (HTogg2) 7> add exttrail ./dirdat/ht,extract extr01,megabytes 50
EXTTRAIL added.
GGSCI (HTogg2) 8> add extract pump01,exttrailsource ./dirdat/ht
EXTRACT added.
GGSCI (HTogg2) 9> add rmttrail ./dirdat/ht,extract pump01,megabytes 50
RMTTRAIL added.
GGSCI (HTogg2) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXTR01 00:00:00 00:00:29
EXTRACT STOPPED PUMP01 00:00:00 00:00:11
GGSCI (HTogg2) 11> dblogin userid ogg, password ogg
Successfully logged into database.
GGSCI (HTogg2) 12> add trandata its.*
2016-04-26 08:30:59 WARNING OGG-00869 No unique key is defined for table 'AAA'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table ITS.AAA.
Logging of supplemental redo data enabled for table ITS.BBB.
】
依據SCN從212導數據到211:
建議先停業務,停2服務器數據對外服務,防止數據不同!然後停止OGG服務stop * stop mgr...
A211生產數據環境:
$ $ORACLE_HOME/bin/sqlplus / as sysdba
SQL>CREATE TABLESPACE ITS DATAFILE '/u01/oradata/its01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 32767M;
SQL>CREATE USER its IDENTIFIED BY its DEFAULT TABLESPACE ITS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
SQL>GRANT CONNECT,RESOURCE,DBA TO its;
A212、B211設置導數據環境:
$ $ORACLE_HOME/bin/sqlplus / as sysdba
SQL>create directory dump_dir as '/home/oracle';
SQL>Grant read,write on directory dump_dir to its;
A212:
SQL>select current_scn from v$database;
CURRENT_SCN
-----------
1168863
$ $ORACLE_HOME/bin/expdp its/its@orcl schemas=its dumpfile=db01.dmp logfile=bakBegin.log flashback_scn=1168863 DIRECTORY=dump_dir
【[oracle@HTogg2 ~]$ $ORACLE_HOME/bin/expdp its/its@orcl schemas=its dumpfile=db01.dmp logfile=bakBegin.log flashback_scn=1168863 DIRECTORY=dump_dir
Export: Release 11.2.0.3.0 - Production on Tue Apr 26 09:42:37 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "ITS"."SYS_EXPORT_SCHEMA_01": its/********@orcl schemas=its dumpfile=db01.dmp logfile=bakBegin.log flashback_scn=1168863 DIRECTORY=dump_dir
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "ITS"."AAA" 5.789 KB 1 rows
. . exported "ITS"."BB2" 5.414 KB 3 rows
. . exported "ITS"."BBB" 5.828 KB 4 rows
. . exported "ITS"."CCC" 5.398 KB 1 rows
Master table "ITS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ITS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/db01.dmp
Job "ITS"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:43:20
】
[oracle@HTogg2 ogg]$ scp /home/oracle/db01.dmp [email protected]:/home/oracle/db01.dmp
The authenticity of host '192.168.81.211 (192.168.81.211)' can't be established.
RSA key fingerprint is 65:5f:f6:63:03:cb:02:21:93:35:7c:77:5c:d6:32:75.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.81.211' (RSA) to the list of known hosts.
[email protected]'s password:
db01.dmp 100% 208KB 208.0KB/s 00:00
B211:
$ $ORACLE_HOME/bin/impdp its/its@orcl schemas=its DIRECTORY=dump_dir dumpfile=db01.dmp
[本地如果監聽沒有起來,則:$ $ORACLE_HOME/bin/lsnrctl start]
【$ORACLE_HOME/bin/impdp its/its@orcl schemas=its DIRECTORY=dump_dir dumpfile=db01.dmp
Import: Release 11.2.0.3.0 - Production on Wed Apr 27 01:04:44 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ITS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "ITS"."SYS_IMPORT_SCHEMA_01": its/********@orcl schemas=its DIRECTORY=dump_dir dumpfile=db01.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ITS" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ITS"."AAA" 5.789 KB 1 rows
. . imported "ITS"."BB2" 5.414 KB 3 rows
. . imported "ITS"."BBB" 5.828 KB 4 rows
. . imported "ITS"."CCC" 5.398 KB 1 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "ITS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 01:04:52
】
啟動211、212的OGG服務:
211.B:
$ cd /u01/ogg
[oracle@HTogg1 ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (HTogg1) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
REPLICAT STOPPED REPL01 00:00:00 01:28:38
GGSCI (HTogg1) 2> start mgr
Manager started.
GGSCI (HTogg1) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPL01 00:00:00 00:00:01
212.A:
$ cd /u01/ogg
[oracle@HTogg2 ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (HTogg2) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXTR01 00:00:00 00:42:14
EXTRACT STOPPED PUMP01 00:00:00 00:41:57
GGSCI (HTogg2) 2> start mgr
Manager started.
GGSCI (HTogg2) 3> info all
Program Status Group Lag at Chkpt TimeSince Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTR01 00:42:25 00:00:01
EXTRACT RUNNING PUMP01 00:00:00 00:42:09
測試導入數據:
211:
$ $ORACLE_HOME/bin/sqlplus its/its
select count(*) from aaa;
select count(*) from bbb;
212:
$ $ORACLE_HOME/bin/sqlplus its/its
select count(*) from aaa;
select count(*) from bbb;
比較一樣OK
在測試環境以下測試都通過,生產環境請忽略以下測試!!:
A212:
create table ccc(a varchar(2),n varchar(2));
insert into ccc values('aa','bb');
create table bb2(a varchar(2),n varchar(2), PRIMARY KEY ( a ));
insert into bb2 values('aa','bb');
insert into bb2 values('a1','b1');
insert into bb2 values('a2','b2');
insert into bb2 values('a3','b3');
insert into bb2 values('a4','b4');
commit;
ALTER TABLE aaa ADD cc varchar2(4) default 'zz';
ALTER TABLE bbb ADD cc varchar2(4) default 'zz';
alter table aaa rename column cc to dd;
alter table bbb modify cc varchar2(3) default 'x4';
insert into bbb values('aa','bb','xx');
commit;
delete from bbb where cc='zz';
commit;
update bbb set a='az';
commit;
insert into bbb values('a1','bb','xx');
insert into bbb values('a2','bb','xx');
insert into bbb values('a3','bb','xx');
commit;
truncate table bbb;
insert into bbb values('a1','bb','xx');
insert into bbb values('a2','bb','xx');
commit;
drop table bbb;
create table bbb(a varchar(2),n varchar(2),cc varchar2(3), PRIMARY KEY ( a ));
insert into bbb values('a1','bb','xx');
insert into bbb values('a2','bb','xx');
commit;
CREATE INDEX nIndex ON bbb (n,cc desc);
drop index nIndex;
select INDEX_NAME, TABLE_NAME from user_indexes;