数据库安装+OGG数据同步

最新推荐文章于 2024-08-11 11:29:21 发布

c.a.p

最新推荐文章于 2024-08-11 11:29:21 发布

阅读量7.2k

点赞数 2

文章标签： OGG

本文链接：https://blog.csdn.net/renyanjie123/article/details/80951865

版权

数据库：Oracle 11g

操作系统：linux 7.2

数据库安装（单机） ---> ogg搭建 --->数据同步

一、数据库安装

查看操作系统版本
[oracle@standbbk /]$ cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7.2 (Maipo)

或者 uname -a

[oracle@standbbk ~]$ uname -a
Linux standbbk 3.10.0-327.el7.x86_64 #1 SMP Thu Oct 29 17:29:29 EDT 2015 x86_64 x86_64 x86_64 GNU/Linux

1、改主机名 ip
hostname standbbk
vim /etc/sysconfig/network
vi /etc/hosts

192.168.1.120 standbbk

2、依赖包

yum -y install binutils-* compat-libstdc++-* compat-libstdc++-*.i686 elfutils-libelf* elfutils-libelf-devel* gcc-* gcc-c++-* glibc-* glibc-*.i686 glibc-common* glibc-devel* glibc-devel*.i686 glibc-headers* ksh* libaio* libaio*.i686 libaio* libaio*.i686 libgcc* libgcc*.i686 libstdc++* libstdc++*.i686 libstdc++-devel* make* sysstat* unixODBC* unixODBC*.i686 unixODBC-devel* unixODBC-devel*

检查
rpm -q gcc make binutils openmotif setarch compat-db compat-gcc \

compat-gcc-c++ compat-libstdc++ compat-libstdc++-devel

3、用户、组
/usr/sbin/groupadd -g 1007 oinstall
/usr/sbin/groupadd -g 1008 dba
/usr/sbin/useradd -u 1008 -g oinstall -G dba oracle
passwd oracle

4、目录挂载
mkdir -p /app/oraInventory
chown -R oracle:oinstall /app/oraInventory

chmod -R 770 /app/oraInventory

mkdir -p /app/oracle
mkdir -p app/oracle/cfgtoollogs
chown -R oracle:oinstall /app/oracle
chmod -R 775 /app/oracle

vim /etc/oraInst.loc
inventory_loc=/app/oraInventory

inst_group=oinstall

5、oracle用户环境变量
vi .bash_profile
export PATH
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=standbbk
export ORACLE_TERM=xterm
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export PATH=$ORACLE_HOME/bin:$PATH;

6、安装
chown -R oracle:oinstall database

chmod -R 755 database

dbca

----------------------报错--------------

Linux 7.2 单实例安装 86%报错

解决方式：
在makefile中添加链接libnnz11库的参数
修改$ORACLE_HOME/sysman/lib/ins_emagent.mk，将

$(MK_EMAGENT_NMECTL)修改为：$(MK_EMAGENT_NMECTL) -lnnz11 （第一个是字母l 后面两个是数字1）

----------------------------------------------------

7、建库
密码都为oracle（oracle用户，实例）
mkdir -p /data/oradata
chown -R oracle:oinstall /data/oradata

chmod -R 775 /data/oradata

------检查一些配置------

--查看是否开闪回：select flashback_on from v$database;

--查看sga的大小：select sum(value)/1024/1024 MB from v$sga;

------修改一些参数配置-------

--修改进程process 和session：
show parameter process;
show parameter session;
alter system set processes=5000 scope=spfile;
alter system set sessions=7752 scope=spfile;
--关闭审计：
show parameter audit;
alter system set audit_trail=none scope=spfile;
--更改内存：
show parameter memory
alter system set memory_max_target=64000M scope=spfile;

alter system set memory_target=64000M scope=spfile;

--更改db_file：
show parameter db_file;

alter system set db_files=2000 scope=spfile;

--更改redo：
1.新增
ALTER DATABASE ADD LOGFILE GROUP 4 ('/app/oradata/standbbk/redo04a.log ','/app/oradata/standbbk/redo04b.log ') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/app/oradata/standbbk/redo05a.log ','/app/oradata/standbbk/redo05b.log ') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/app/oradata/standbbk/redo06a.log ','/app/oradata/standbbk/redo06b.log ') SIZE 2048M;
2.状态
SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
ALTER SYSTEM SWITCH LOGFILE;
SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
3.检查点
ALTER SYSTEM CHECKPOINT;
SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;
4、删除状态为inactive的日志
ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
5、重建
ALTER DATABASE ADD LOGFILE GROUP 1 ('/app/oradata/standbbk/redo01a.log ','/app/oradata/standbbk/redo01b.log ') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 2 ('/app/oradata/standbbk/redo02a.log ','/app/oradata/standbbk/redo02b.log ') SIZE 2048M;
ALTER DATABASE ADD LOGFILE GROUP 3 ('/app/oradata/standbbk/redo03a.log ','/app/oradata/standbbk/redo03b.log ') SIZE 2048M;
6、查看
ALTER SYSTEM SWITCH LOGFILE;

SELECT group#, members, bytes/1024/1024 byte_mb, status FROM v$log;

--设置表空间大小

select file#,name from v$datafile;

FILE# NAME
---------- -----------------------------------------
1 /app/oradata/standbbk/system01.dbf
2 /app/oradata/standbbk/sysaux01.dbf
3 /app/oradata/standbbk/undotbs01.dbf
4 /app/oradata/standbbk/users01.dbf

alter database datafile 2 resize 24576M; sysaux
alter database datafile 1 resize 4096M; system
alter database datafile 4 resize 10240M; users
alter database datafile 3 resize 30720M; undo
alter tablespace UNDOTBS1 add datafile '/app/oradata/standbbk/undotbs02.dbf' size 30720m;
create tablespace GOLDENGATE datafile '/app/oradata/standbbk/goldengate.dbf' size 500m;

-创建STANDATA表空间（文件以.dbf结尾） --这里只有一部分
create tablespace STANDATA datafile '/app/oradata/standbbk/standata_001.dbf' size 32767M;
alter tablespace STANDATA add datafile '/app/oradata/standbbk/standata_002.dbf' size 32767M;
alter tablespace STANDATA add datafile '/app/oradata/standbbk/standata_003.dbf' size 32767M;
alter tablespace STANDATA add datafile '/app/oradata/standbbk/standata_004.dbf' size 32767M;

alter tablespace STANDATA add datafile '/app/oradata/standbbk/standata_005.dbf' size 32767M;

--开归档:

archive log list

shutdown immediate

startup mount

show parameter dest

alter system set log_archive_dest_1='location=/data/arch';
alter database archivelog;

alter database open;

--开监听:
lsnrctl status
lsnrctl start

二、OGG安装---(环境：rhel-7.2 and Oracle 11.2.0.4 )

-----源端和目标端-----

--ogg用户

SQL>CREATE USER ogg IDENTIFIED BY oracle DEFAULT TABLESPACE ogg;

SQL> grant dba to ogg;

--ogg目录

[oracle@standbbk data]$ mkdir /data/ogg

[oracle@standbbk data]$ chown -R oracle:oinstall /data/ogg

--环境变量

### Linux、HP-UX 在源端：
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
### AIX

export LIBPATH=$ORACLE_HOME/lib:/lib:/usr/lib

1、开启数据库级别日志补充

ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA(PRIMARY KEY, UNIQUE,FOREIGN KEY) COLUMNS;
ALTER SYSTEM ARCHIVE LOG CURRENT;

SELECT
SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI,
SUPPLEMENTAL_LOG_DATA_FK,
SUPPLEMENTAL_LOG_DATA_ALL
FROM v$database;

SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES YES YES YES NO

Oracle11.2.0.4版本所需参数

SQL > alter system set enable_goldengate_erplication = true scope=both;

2、添加角色和一些能力
安装sequence支持
cd $OGG_HOME
sqlplus / as sysdba
@sequence.sql
GRANT EXECUTE ON ogg.updateSequence TO ogg;
ALTER TABLE sys.seq$ ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
安装ddl支持
@marker_setup.sql
@ddl_setup.sql
添加角色
@role_setup.sql
GRANT GGS_GGSUSER_ROLE TO ogg;
@ddl_enable.sql

@marker_status.sql

3、设置全局参数
[oracle@standbbk data]$cd $OGG_HOME
[oracle@standbbk data]$./ggsci
GGSCI (standbbk) 1>EDIT PARAMS ./GLOBALS

GGSCHEMA ogg

4、创建管理（manager）进程

GGSCI (standbbk) 2>edit params mgr
Port 7839
DYNAMICPORTLIST 7840-7900
AUTOSTART REPLICAT *
PURGEOLDEXTRACTS ./dirdat/* , USECHECKPOINTS, MINKEEPDAYS 2
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

***PORT：指定服务监听端口；默认端口为7809
***DYNAMICPORTLIST：动态端口：可以制定最大256个可用端口的动态列表，当指定的端口不可用时，管理进程将会从列表中选择一个可用的端口，源端和目标段的Collector、Replicat、GGSCI进程通信也会使用这些端口；
***COMMENT：注释行，也可以用--来代替;
***AUTOSTART：指定在管理进程启动时自动启动哪些进程； --AUTOSTART ER *
***AUTORESTART：自动重启参数设置：本处设置表示每3分钟尝试重新启动所有EXTRACT进程，共尝试5次；--AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
***PURGEOLDEXTRACTS：定期清理trail文件设置：本处设置表示对于超过7天的trail文件进行删除。
***LAGREPORT、LAGINFO、LAGCRITICAL：
***LAGREPORTHOURS 1
***LAGINFOMINUTES 30
***LAGCRITICALMINUTES 45
***定义数据延迟的预警机制：本处设置表示MGR进程每隔1小时检查EXTRACT的延迟情况，如果超过了30分钟就把延迟作为信息记录到错误日志中，如果延迟超过了45分钟，则把它作为警告写到错误日志中。

GGSCI (standbbk) 3> info all ----查询所有进程的状态
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (standbbk) 4> start mgr
Manager started.
GGSCI (standbbk) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

------源端-----

5、添加表级的附加日志（transdata）
GGSCI (emdb1) 1>dblogin userid ogg,password oracle （在OGG下使用ogg用户登录到数据库）
GGSCI (emdb1) 2>add trandata scott.* （将SCOTT用户下所有的表都添加附加日志）

6、创建源端的捕获（extract）进程
GGSCI (emdb1) 3>add extract EXTEM , tranlog, begin now, threads 1

GGSCI (emdb1) 4>add EXTTRAIL ./dirdat/em, extract EXTEM ,MEGABYTES 100（指定队列的大小）

--捕获参数

GGSCI (emdb1) 4> view params ext_ntt

EXTRACT ext_ntt
SETENV (NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK")
setenv (ORACLE_SID=emdb1)
userid ggs, password ggs
GETTRUNCATES
BR BROFF
TRANLOGOPTIONS BUFSIZE 4096000
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS DBLOGREADERBUFSIZE 1048576
REPORTCOUNT EVERY 1 MINUTES, RATE
DBOPTIONS ALLOWUNUSEDCOLUMN
EXTTRAIL ./dirdat/em, format RELEASE 11.2
TRANLOGOPTIONS CONVERTUCS2CLOBS
DYNAMICRESOLUTION
numfiles 1500
tranlogoptions altarchivelogdest instance emdb1 /emdbarch,altarchivelogdest instance emdb2 /emdbarch
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf
FETCHOPTIONS FETCHPKUPDATECOLS
table UCR_MALL.TF_B_ORDER;
table UCR_MALL.TF_B_ORDER_GOODSINS;
table UCR_MALL.TF_B_ORDER_GOODSINS_ATVAL;

GGSCI (emdb1) 5>start ext_ntt

7、创建投递（pump）进程 ----
GGSCI (emdb1) 6>add extract DPEMSTBK, exttrailsource ./dirdat/st
GGSCI (emdb1) 7>add rmttrail ./dirdat/st, extract DPEMSTBK, MEGABYTES 100

投递参数
GGSCI (emdb1) 8> view params DPEMSTBK
extract dpemstbk
dynamicresolution
passthru
rmthost 192.168.1.120, mgrport 7839, compress
rmttrail ./dirdat/st
numfiles 5000
table UCR_MALL.TF_B_ORDER;

table UCR_MALL.TF_B_ORDER_GOODSINS;

GGSCI (emdb1) 9>start DPEMSTBK

---目标端---

8、复制（replicate）进程
ogg 目标端配置
GGSCI (standbbk) 6> dblogin userid ogg,password oracle
GGSCI (standbbk) 7> add checkpointtable ogg.REP1EM_ckpt
GGSCI (standbbk) 8> dd replicat REP1EM,exttrail ./dirdat/st,checkpointtable ogg.REP1EM_ckpt

进程参数

GGSCI (standbbk) 9> view params REP1EM

REPLICAT rep1em
SETENV (NLS_LANG = "American_America.ZHS16GBK")
USERID ggs,PASSWORD ggs
REPORTCOUNT EVERY 30 MINUTES, RATE
--REPERROR DEFAULT, ABEND
REPERROR (1,DISCARD)
REPERROR (1403,DISCARD)
numfiles 5000
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/rep1em.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
map UCR_CCS.TF_B_ORDER_O2O_LOG, target UCR_CCS.TF_B_ORDER_O2O_LOG;
map UCR_CCS.TL_M_QR, target UCR_CCS.TL_M_QR;
map UCR_CCS.TF_M_GROUP_MANAGER, target UCR_CCS.TF_M_GROUP_MANAGER;

GGSCI (standbbk) 10> view params REP2EM
REPLICAT rep2em
SETENV (NLS_LANG = "American_America.ZHS16GBK")
USERID ggs,PASSWORD ggs
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR (1403,DISCARD), ABEND
numfiles 5000
HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/rep2em.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
--map UCR_MALL.TL_B_ORDER_DEAL, target UCR_MALL.TL_B_ORDER_DEAL;

map UCR_MALL.TF_B_ORDER_FEE, target UCR_MALL.TF_B_ORDER_FEE;

GGSCI (standb) 11> view params REP3EM

REPLICAT rep3em
SETENV (NLS_LANG = "American_America.ZHS16GBK")
USERID ggs,PASSWORD ggs
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR (1403,DISCARD)
REPERROR (1,DISCARD), ABEND
numfiles 5000
HANDLECOLLISIONS
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/rep3em.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
--map UCR_MALL.TF_B_ORDER, target UCR_MALL.TF_B_ORDER;
--map UCR_MALL.TF_B_LGTS_STATE, target UCR_MALL.TF_B_LGTS_STATE;
map UCR_MALL.TL_B_ORDER_ESSFEE, target UCR_MALL.TL_B_ORDER_ESSFEE;

map UCR_MALL.TF_F_VOUCHER_DETAIL, target UCR_MALL.TF_F_VOUCHER_DETAI

三、ogg数据同步---官档说按进程同步比较快，源端新的投递（pump）进程开启，就会将新的数据投递到目标端，当数据按SCN号导出在导入到目标端中，复制（replicate）进程按SCN好恢复，就可实现数据同步。

1.获取源端当前SCN
col current_scn for 9999999999999999
select current_scn from v$database;

2.源端指定scn导出相关表
vi /emdbdata5/dump/expdp_120_REP4_20180416.par
tables=UCR_MALL.TF_B_ORDER_POST,
UCR_MALL.TF_B_IDCARDUPLOAD,
UCR_MALL.TF_B_ORDER_RECOMMENT,
UCR_MALL.TF_B_ORDERPAY,
UCR_MALL.TF_B_ALIPAY_KINGCARD,
UCR_MALL.TF_B_ORDER_EVALUATE_ATTR,
UCR_MALL.TF_B_NET_UNION,

UCR_MALL.TF_B_ORDER_WM

....部分表

3.导出目录

nohup expdp system/\"密码\" PARFILE=expdp_120_REP4_20180416.par dumpfile=expdp_120_REP4_%U_20180416.dmp DIRECTORY=stbk LOGFILE=expdp_120_REP4_20180416.log FLASHBACK_SCN=XXXXXX COMPRESSION=all CLUSTER=n parallel=8 &
4.从源端scp到目标端
scp /emdbdata5/dump/expdp_120_REP4_*.dmp oracle@192.168.1.120:/data/expdp_impdp
5.导入到目标端
cd /data/expdp_impdp
cat /data/expdp_impdp/impdp_120_REP.par
指定parfile，将所有表remap到standata表空间
REMAP_TABLESPACE=TBS_ACHK_HDATA:STANDATA,
TBS_ACHK_INDEX:STANDATA,
TBS_BASE:STANDATA,
TBS_BROADBANDLOG_DATA:STANDATA,
TBS_BROADBAND_DATA:STANDATA,
TBS_BROADBAND_INDEX:STANDATA,
......

nohup impdp system/oracle PARFILE=impdp_120_REP.par dumpfile=expdp_120_REP4_%U_20180416.dmp DIRECTORY=stbk_dump LOGFILE=impdp_120_REP4_20180416.log parallel=8 &

6.导入完成后查看是否有ora报错
cat impdp_120_REP4_20180416.log|grep ORA-|cut -c 1-90|sort -u

7.确认没有报错，开启应用进程rep4em

配置各个replicat进程
添加replicat进程

ADD checkpointtable ggs.ckptable
GGSCI (standbbk) 12>ADD REPLICAT REP4EM EXTTRAIL ./dirdat/st,checkpointtable ggs.ckptable

配置replicat进程
GGSCI (standbbk) 13>edit params REP4EM

REPLICAT rep4em
SETENV (NLS_LANG = "American_America.ZHS16GBK")
USERID ggs,PASSWORD ggs
REPORTCOUNT EVERY 30 MINUTES, RATE
--REPERROR (1403,DISCARD)
REPERROR DEFAULT, ABEND
numfiles 5000
--HANDLECOLLISIONS
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/rep4em.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
map UCR_MALL.TF_B_ORDER_POST, target UCR_MALL.TF_B_ORDER_POST;
map UCR_MALL.TF_B_IDCARDUPLOAD, target UCR_MALL.TF_B_IDCARDUPLOAD;
map UCR_MALL.TF_B_ORDER_RECOMMENT, target UCR_MALL.TF_B_ORDER_RECOMMENT;
map UCR_MALL.TF_B_ORDERPAY, target UCR_MALL.TF_B_ORDERPAY;
map UCR_MALL.TF_B_ALIPAY_KINGCARD, target UCR_MALL.TF_B_ALIPAY_KINGCARD;
map UCR_MALL.TF_B_ORDER_EVALUATE_ATTR, target UCR_MALL.TF_B_ORDER_EVALUATE_ATTR;
map UCR_MALL.TF_B_NET_UNION, target UCR_MALL.TF_B_NET_UNION;
map UCR_MALL.TF_B_ORDER_WM, target UCR_MALL.TF_B_ORDER_WM;
map UCR_MALL.TF_B_BESPEAK_INFO, target UCR_MALL.TF_B_BESPEAK_INFO;
map UCR_MALL.TF_F_RESULT, target UCR_MALL.TF_F_RESULT;
map UCR_MALL.TF_B_ORDER_SELFFETCH_RELE, target UCR_MALL.TF_B_ORDER_SELFFETCH_RELE;
map UCR_MALL.TF_B_ORDER_SALETURN, target UCR_MALL.TF_B_ORDER_SALETURN;
map UCR_MALL.TF_B_ORDER_AIR, target UCR_MALL.TF_B_ORDER_AIR;
map UCR_MALL.TD_P_ACTIVITY, target UCR_MALL.TD_P_ACTIVITY;
map UCR_MALL.TF_B_ORDER_SITE_ACT, target UCR_MALL.TF_B_ORDER_SITE_ACT;
map UCR_MALL.TF_B_ORDER_IMPRESSION, target UCR_MALL.TF_B_ORDER_IMPRESSION;
map UCR_MALL.TF_B_BESPEAK_4G, target UCR_MALL.TF_B_BESPEAK_4G;
map UCR_MALL.TF_B_ORDER_EVALUATE, target UCR_MALL.TF_B_ORDER_EVALUATE;

GGSCI (standbbk) 14>view params REP4EM
GGSCI (standbbk) 15>start REP4EM,AFTERCSN XXXXXX

8.确认应用进程是否正常
GGSCI (standbbk) 16>info REP4EM