加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

Oracle Database 复制解决方案：Oracle GoldenGate

发布时间：2020-12-12 13:27:22 所属栏目：百科 来源：网络整理
导读：源端数据库配置 [ [email?protected]?~]#?su?-?oracle [ [email?protected]?~]$?sqlplus?/?as?sysdba SQL?create?tablespace?goldengate?datafile?'/u01/app/oracle/oradata/king/ogg01.dbf'?size?1024M; Tablespace?created. SQL?create?user?ggs?identifie
源端数据库配置 [ [email?protected]?~]#?su?-?oracle [ [email?protected]?~]$?sqlplus?/?as?sysdba SQL>?create?tablespace?goldengate?datafile?'/u01/app/oracle/oradata/king/ogg01.dbf'?size?1024M; Tablespace?created. SQL>?create?user?ggs?identified?by?ggs?default?tablespace?goldengate; User?created. SQL>?grant?dba?to?ggs; Grant?succeeded. SQL>?select?log_mode?from?v$database; LOG_MODE ------------------------------------ ARCHIVELOG SQL>?alter?database?force?logging; Database?altered. SQL>?select?force_logging?from?v$database; FORCE_LOGGING ------------------------------ YES SQL>?alter?database?add?supplemental?log?data; Database?altered. SQL>?col?supplemental_log_data_min?for?a30 SQL>?select?supplemental_log_data_min?from?v$database; SUPPLEMENTAL_LOG_DATA_MIN ------------------------------ YES SQL>?alter?system?set?enable_goldengate_replication; SQL>?alter?system?archive?log?current; 


源端安装OGG
[[email?protected]?~]$?vi?.bash_profile
export?OGG_HOME=/u01/app/ogg
export?LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export?PATH=$OGG_HOME:$PATH
[[email?protected]?~]$?source?.bash_profile

[[email?protected]?~]$?mkdir?-p?/u01/app/ogg
[[email?protected]?~]$?unzip?fbo_ggs_Linux_x64_shiphome.zip
[[email?protected]?~]$?cd?fbo_ggs_Linux_x64_shiphome/Disk1
[[email?protected]?Disk1]$?./runInstaller


[[email?protected]?~]$?cd?/u01/app/ogg
[[email?protected]?ogg]$?./ggsci
Oracle?GoldenGate?Command?Interpreter?for?Oracle
Version?12.2.0.1.1?OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux,?x64,?64bit?(optimized),?Oracle?11g?on?Dec?12?2015?00:54:38
Operating?system?character?set?identified?as?UTF-8.
Copyright?(C)?1995,?2015,?Oracle?and/or?its?affiliates.?All?rights?reserved.

GGSCI?(king01)?1>?create?subdirs
Creating?subdirectories?under?current?directory?/u01/app/ogg
Parameter?files????????????????/u01/app/ogg/dirprm:?created
Report?files???????????????????/u01/app/ogg/dirrpt:?created
Checkpoint?files???????????????/u01/app/ogg/dirchk:?created
Process?status?files???????????/u01/app/ogg/dirpcs:?created
SQL?script?files???????????????/u01/app/ogg/dirsql:?created
Database?definitions?files?????/u01/app/ogg/dirdef:?created
Extract?data?files?????????????/u01/app/ogg/dirdat:?created
Temporary?files????????????????/u01/app/ogg/dirtmp:?created
Credential?store?files?????????/u01/app/ogg/dircrd:?created
Masterkey?wallet?files?????????/u01/app/ogg/dirwlt:?created
Dump?files?????????????????????/u01/app/ogg/dirdmp:?created

源端MANAGER进程组
GGSCI?(king01)?2>?edit?params?mgr
PORT?7839
DYNAMICPORTLIST?7840-7939
AUTORESTART?EXTRACT?*,RETRIES?5,WAITMINUTES?3
PURGEOLDEXTRACTS?./dirdat/*,USECHECKPOINTS,?MINKEEPDAYS?7

GGSCI?(king01)?3>?start?mgr
Manager?started.

GGSCI?(king01)?4>?info?mgr
Manager?is?running?(IP?port?king01.7839,?Process?ID?3243).


源端EXTRACT进程组
GGSCI?(king01)?5>?dblogin?userid?ggs,password?ggs
Successfully?logged?into?database.

GGSCI?(king01?as?[email?protected])?6>?add?schematrandata?soe
2018-08-04?13:18:03??INFO????OGG-01788??SCHEMATRANDATA?has?been?added?on?schema?soe.
2018-08-04?13:18:03??INFO????OGG-01976??SCHEMATRANDATA?for?scheduling?columns?has?been?added?on?schema?soe.

GGSCI?(king01?as?[email?protected])?7>?add?extract?ext_soe,?tranlog,?begin?now
EXTRACT?added.

GGSCI?(king01?as?[email?protected])?8>?add?exttrail?./dirdat/st,extract?ext_soe,megabytes?100
EXTTRAIL?added.

GGSCI?(king01?as?[email?protected])?9>?edit?params?ext_soe
EXTRACT?ext_soe
dynamicresolution
USERID?ggs,PASSWORD?ggs
EXTTRAIL?./dirdat/st
TABLEEXCLUDE?SOE.ORDERENTRY_METADATA
TABLE?SOE.*;

GGSCI?(king01?as?[email?protected])?10>?start?ext_soe
Sending?START?request?to?MANAGER?...
EXTRACT?EXT_SOE?starting

GGSCI?(king01?as?[email?protected])?11>?info?ext_soe

EXTRACT????EXT_SOE???Last?Started?2017-08-30?13:47???Status?RUNNING
Checkpoint?Lag???????00:00:00?(updated?00:00:07?ago)
Process?ID???????????4234
Log?Read?Checkpoint??Oracle?Redo?Logs
?????????????????????2017-08-30?16:58:33??Seqno?59,?RBA?211786752
?????????????????????SCN?0.2361582?(2361582)
?????????????????????
源端PUMP进程组?????????????????????
GGSCI?(king01?as?[email?protected])?12>?add?extract?pmp_soe,?exttrailsource?./dirdat/st
EXTRACT?added.

GGSCI?(king01?as?[email?protected])?13>?add?rmttrail?./dirdat/rt,EXTRACT?pmp_soe,megabytes?100
RMTTRAIL?added.

GGSCI?(king01?as?[email?protected])?14>?edit?params?pmp_soe
EXTRACT?pmp_soe
PASSTHRU
RMTHOST?192.168.1.202,?MGRPORT?7839,?COMPRESS
RMTTRAIL?./dirdat/rt
TABLE?SOE.*;

GGSCI?(king01?as?[email?protected])?15>?start?pmp_soe
Sending?START?request?to?MANAGER?...
EXTRACT?PMP_SOE?starting

GGSCI?(king01?as?[email?protected])?16>??info?pmp_soe
EXTRACT????PMP_SOE???Last?Started?2017-08-30?15:22???Status?RUNNING
Checkpoint?Lag???????00:00:00?(updated?00:00:08?ago)
Process?ID???????????4844
Log?Read?Checkpoint??File?./dirdat/st000000000
?????????????????????2017-08-30?16:20:08.000000??RBA?47753236

GGSCI?(king01?as?[email?protected])?17>?info?all

Program?????Status??????Group???????Lag?at?Chkpt??Time?Since?Chkpt

MANAGER?????RUNNING???????????????????????????????????????????
EXTRACT?????RUNNING?????EXT_SOE?????00:00:00??????00:00:01????
EXTRACT?????RUNNING?????PMP_SOE?????00:00:00??????00:00:06


源端备份数据库
[[email?protected]?~]$?mkdir?/u01/app/oracle/backup
[[email?protected]?~]$?rman?target?/
RMAN>?CONFIGURE?CONTROLFILE?AUTOBACKUP?ON;
RMAN>?CONFIGURE?CONTROLFILE?AUTOBACKUP?FORMAT?FOR?DEVICE?TYPE?DISK?TO?'/u01/app/oracle/backup/%F';
RMAN>?RUN?{
BACKUP?INCREMENTAL?LEVEL=0?TAG?'FULL_BACKUP'?DATABASE
FORMAT?'/u01/app/oracle/backup//soe_full_incr_%s_%p_%T'
PLUS?ARCHIVELOG
FORMAT?'/u01/app/oracle/backup/soe_arch_%s_%p_%T'?delete?all?input;
DELETE?NOPROMPT?OBSOLETE;
}

[[email?protected]?~]$?sqlplus?/?as?sysdba
SQL>?select?to_char(current_scn)?from?v$database;
TO_CHAR(CURRENT_SCN)
----------------------------------------
1032191

SQL>?alter?system?archive?log?current?;
System?altered.
SQL>?alter?system?archive?log?current?;
System?altered.
SQL>?alter?system?archive?log?current?;
System?altered.

[[email?protected]?~]$?rman?target?/
RMAN>?backup?archivelog?all?FORMAT?'/u01/app/oracle/backup/soe_arch_%s_%p_%T';
[[email?protected]?~]$?cd?/u01/oracle/backup
[[email?protected]?backup]$??scp?*?192.168.1.202:/u01/oracle/backup/
[[email?protected]?~]$?cd?$ORACLE_HOME/dbs
[[email?protected]?dbs]$?scp?orapwking?192.168.1.202:/u01/app/database/11.2.0.4/product/dbs


目标端恢复数据库
[[email?protected]?~]$?rman?target?/
RMAN>?startup?nomount
startup?failed:?ORA-01078:?failure?in?processing?system?parameters
LRM-00109:?could?not?open?parameter?file?'/u01/app/oracle/product/11.2.0/db_1/dbs/initking.ora'
starting?Oracle?instance?without?parameter?file?for?retrieval?of?spfile
Oracle?instance?started
Total?System?Global?Area????1068937216?bytes
Fixed?Size?????????????????????2260088?bytes
Variable?Size????????????????281019272?bytes
Database?Buffers?????????????780140544?bytes
Redo?Buffers???????????????????5517312?bytes

RMAN>?set?DBID=1085678857
RMAN>?run{
?set?CONTROLFILE?AUTOBACKUP?FORMAT?FOR?DEVICE?TYPE?DISK?TO?'/u01/app/oracle/backup/%F';
?restore?spfile?from?autobackup;
?}
?
RMAN>?shutdown?immediate
RMAN>?startup?nomount
Total?System?Global?Area????1068937216?bytes
Fixed?Size?????????????????????2260088?bytes
Variable?Size????????????????322962312?bytes
Database?Buffers?????????????687865856?bytes
Redo?Buffers??????????????????55848960?bytes

RMAN>?set?DBID=1085678857
executing?command:?SET?DBID
RMAN>?run{
2>?set?CONTROLFILE?AUTOBACKUP?FORMAT?FOR?DEVICE?TYPE?DISK?TO?'/u01/app/oracle/backup/%F';
3>?restore?controlfile?from?autobackup;
4>?}

RMAN>?alter?database?mount;

RMAN>?run{
?set?until?scn?1032191;
?restore?database;
?recover?database;
?}
?
RMAN>?alter?database?open?resetlogs;


目标端安装OGG
[[email?protected]?~]$?vi?.bash_profile
export?OGG_HOME=/u01/app/ogg
export?LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export?PATH=$OGG_HOME:$PATH
[[email?protected]g02?~]$?source?.bash_profile

[[email?protected]?~]$?mkdir?-p?/u01/app/ogg
[[email?protected]?~]$?unzip?fbo_ggs_Linux_x64_shiphome.zip
[[email?protected]?~]$?cd?fbo_ggs_Linux_x64_shiphome/Disk1
[[email?protected]?Disk1]$?./runInstaller?

[[email?protected]?Disk1]$?cd?/u01/app/ogg
[[email?protected]?ogg]$?./ggsci
GGSCI?(king02)?1>?create?subdirs
Creating?subdirectories?under?current?directory?/u01/app/ogg
Parameter?files????????????????/u01/app/ogg/dirprm:?created
Report?files???????????????????/u01/app/ogg/dirrpt:?created
Checkpoint?files???????????????/u01/app/ogg/dirchk:?created
Process?status?files???????????/u01/app/ogg/dirpcs:?created
SQL?script?files???????????????/u01/app/ogg/dirsql:?created
Database?definitions?files?????/u01/app/ogg/dirdef:?created
Extract?data?files?????????????/u01/app/ogg/dirdat:?created
Temporary?files????????????????/u01/app/ogg/dirtmp:?created
Credential?store?files?????????/u01/app/ogg/dircrd:?created
Masterkey?wallet?files?????????/u01/app/ogg/dirwlt:?created
Dump?files?????????????????????/u01/app/ogg/dirdmp:?created
目标端数据库设置
SQL>?select?'alter?table?'||owner||'.'||table_name||'?disable?constraint?'||constraint_name||';'
?from?dba_constraints
?where?constraint_type?in?('R')?and
?owner?in('SOE')
?order?by?status,owner;
'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------
alter?table?SOE.ADDRESSES?disable?constraint?ADD_CUST_FK;
alter?table?SOE.ORDERS?disable?constraint?ORDERS_CUSTOMER_ID_FK;
alter?table?SOE.ORDER_ITEMS?disable?constraint?ORDER_ITEMS_PRODUCT_ID_FK;
alter?table?SOE.INVENTORIES?disable?constraint?INVENTORIES_PRODUCT_ID_FK;
alter?table?SOE.ORDER_ITEMS?disable?constraint?ORDER_ITEMS_ORDER_ID_FK;
alter?table?SOE.INVENTORIES?disable?constraint?INVENTORIES_WAREHOUSES_FK;
6?rows?selected.

SQL>?select?'alter?trigger?'||owner||'.'||trigger_name||'?disable;'
?from?dba_triggers
?where?owner?in('SOE')
?order?by?status,owner;
no?rows?selected

SQL>??alter?system?set?job_queue_processes=0?scope=both;
System?altered.

SQL>?drop?user?ggs?cascade;
User?dropped.

SQL>?create?user?ggt?identified?by?ggt?default?tablespace?goldengate;
User?created.

SQL>?grant?dba?to?ggt;
Grant?succeeded.

目标端REPLICAT进程组
[[email?protected]?~]$?cd?/u01/app/ogg/
[[email?protected]?ogg]$?./ggsci

GGSCI?(king02)?1>?edit?params?./GLOBALS
checkpointtable?ggt.chkpt

GGSCI?(king02)?2>?dblogin?userid?ggt,password?ggt
Successfully?logged?into?database.

GGSCI?(king02?as?[email?protected])?3>?add?checkpointtable

No?checkpoint?table?specified.?Using?GLOBALS?specification?(ggt.chkpt)...

Successfully?created?checkpoint?table?ggt.chkpt.

GGSCI?(king02?as?[email?protected])?4>?add?replicat?rep_soe,exttrail?./dirdat/rt
REPLICAT?added.

GGSCI?(king02?as?[email?protected])?5>?edit?param?rep_soe?
REPLICAT?rep_soe
USERID?ggt,?PASSWORD?ggt
DISCARDFILE?./dirrpt/rep_soe.dsc,APPEND,MEGABYTES?1024
REPERROR?DEFAULT,?ABEND
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
MAP?SOE.*,?TARGET?SOE.*;

GGSCI?(king02?as?[email?protected])?6>?start?replicat?rep_soe,?aftercsn?1032191

Sending?START?request?to?MANAGER?...
REPLICAT?REP_SOE?starting

GGSCI?(king02?as?[email?protected])?7>?info?rep_soe

REPLICAT???REP_SOE???Last?Started?2018-08-04?14:40???Status?RUNNING
Checkpoint?Lag???????00:00:00?(updated?00:00:00?ago)
Process?ID???????????13902
Log?Read?Checkpoint??File?./dirdat/rt000000000
?????????????????????2018-08-04?14:43:08.676383??RBA?42207


GGSCI?(king02?as?[email?protected])?8>?info?all

Program?????Status??????Group???????Lag?at?Chkpt??Time?Since?Chkpt

MANAGER?????RUNNING???????????????????????????????????????????
REPLICAT????RUNNING?????REP_SOE?????00:00:00??????00:00:04

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同