精华内容
下载资源
问答
  • 环境说明: linux为Linux 2.6.32-573.el6.x86_64 oracle为11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production oggogg112101_fbo_ggs_Linux_x64_ora11g_64bit ...

    环境说明:   linux为Linux 2.6.32-573.el6.x86_64     oracle为 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                     ogg为 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit

    技术背景:

     

     

     

    说明:  goldengate为ogg管理用户                 oggdemo为生产用户

    源库操作:

      1.  上传ogg安装包与安装ogg软件。

    [oracle@11g ~]$ mkdir ogg

    [oracle@11g ~]$ cd ogg
    [oracle@11g ogg]$ ls
    ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

    [oracle@11g ogg]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip 
    Archive: ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
    inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar 
    inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf 
    inflating: Oracle GoldenGate 11.2.1.0.1 README.txt 
    inflating: Oracle GoldenGate 11.2.1.0.1 README.doc 
    [oracle@11g ogg]$ tar vxf fbo_ggs_Linux_x64_ora11g_64bit.tar 
    UserExitExamples/
    UserExitExamples/ExitDemo_more_recs/
    UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
    UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS

    。。。。省略大量输出。。。。。

    ucharset.h
    ulg.sql
    usrdecs.h
    zlib.txt

    2.  数据库为ogg传递数据做环境调整。  (归档,数据库级最小附加日志)

    [oracle@11g ogg]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 9 03:03:20 2014

    SQL> archive log list;
    Database log mode Archive Mode
    Automatic archival Enabled
    Archive destination USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence 21
    Next log sequence to archive 23
    Current log sequence 23
    SQL> alter database add supplemental log data;

    Database altered.

    SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

    System altered.

    3. 创建专用的ogg表空间与ogg用户,ogg用户授权 (以后卸载ogg直接删除表空间即可)

    SQL> create tablespace goldgate datafile '/home/oracle/app/oradata/orcl/ogg01.dbf' size 2G AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;


    Tablespace created.

    SQL> CREATE USER goldengate IDENTIFIED BY goldengate DEFAULT TABLESPACE goldgate;

    User created.

    SQL> GRANT CONNECT TO goldengate;

    Grant succeeded.

    SQL> GRANT CREATE SESSION TO goldengate;

    Grant succeeded.

    SQL> GRANT ALTER SESSION TO goldengate;

    Grant succeeded.

    SQL> GRANT RESOURCE TO goldengate;

    Grant succeeded.

    SQL> GRANT SELECT ANY DICTIONARY TO goldengate;

    Grant succeeded.

    SQL> GRANT SELECT ANY TABLE TO goldengate;

    Grant succeeded.

    SQL> GRANT FLASHBACK ANY TABLE TO goldengate;

    Grant succeeded.

    SQL> GRANT ALTER ANY TABLE TO goldengate;

    Grant succeeded.

     

    说明:

    在goldengate目录下,执行 ./ggsci 进入命令行界面,左侧提示出现GGSCI (myhost) 1>,
    表示进入成功
    如果ggsci进入出错,设置一下环境变量:
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib

     

    [oracle@11g ~]$ cd ogg                  --验证是否可以进入ggsci命令界面
    [oracle@11g 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 (11g) 1>      -- 成功进入界面

    ——————————————————————————————————————————————————————————————————

    目标库操作:

     

    说明 : 目标库的操作与源库以上操作全部相同(请参考上面的部分),操作完以上部分后,目标库的附加操作如下>

    SQL> GRANT INSERT ANY TABLE TO goldengate;

    Grant succeeded.

    SQL> GRANT UPDATE ANY TABLE TO goldengate;

    Grant succeeded.

    SQL> GRANT DELETE ANY TABLE TO goldengate;

    Grant succeeded.

     ————————————————————————————————————————————————————————————————————

    4.  创建ogg子目录

    源库与目标库都执行如下:  

    GGSCI (11g) 3> create subdirs

    Creating subdirectories under current directory /home/oracle/ogg

    Parameter files /home/oracle/ogg/dirprm: already exists
    Report files /home/oracle/ogg/dirrpt: created
    Checkpoint files /home/oracle/ogg/dirchk: created
    Process status files /home/oracle/ogg/dirpcs: created
    SQL script files /home/oracle/ogg/dirsql: created
    Database definitions files /home/oracle/ogg/dirdef: created
    Extract data files /home/oracle/ogg/dirdat: created
    Temporary files /home/oracle/ogg/dirtmp: created
    Stdout files /home/oracle/ogg/dirout: created

    参数说明:

    该命令会在OGG安装目录下建立若干子目录,其中几个主要目录如下所示:
    dirchk:用于存放各个进程的检查点
    dirdat:用于存放数据队列文件
    dirprm:用于存放各进程参数文件
    dirrpt:用于存放各进程报告
    dirpcs:存放各个正在运行的进程信息

     

     5. 创建mgr进程

     

    GGSCI (11g) 4> edit param mgr    (--在mgr文件中加入  port 7809)

    GGSCI (11g) 8> view param mgr

    port 7809

    GGSCI (11g) 5> start mgr

    Manager started.

    GGSCI (11g) 6> info all

    Program Status Group Lag at Chkpt Time Since Chkpt

    MANAGER RUNNING

    ————————————————————————————————————————————————————

    源库操作:

    6. 创建oggdemo测试用户

    SQL> create user oggdemo identified by oggdemo;

    User created.

    SQL> grant dba to oggdemo;

    Grant succeeded.

    SQL> conn oggdemo;
    Enter password: 
    Connected.

    SQL> create table oggdemo(id int);

    Table created.

    7.   GGSCI命令行中登录数据库,为所有要复制的数据表添加trandata

     

    GGSCI (11g) 9> dblogin userid oggdemo , password oggdemo
    Successfully logged into database.

    GGSCI (11g) 10> add trandata oggdemo.oggdemo

    2014-11-09 04:12:37 WARNING OGG-00869 No unique key is defined for table 'OGGDEMO'. 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 OGGDEMO.OGGDEMO.

    ————————————————————————————————————————————————————————————————

    目标端操作:

    SQL> create user oggdemo identified by oggdemo;

    User created.

    SQL> grant dba to oggdemo;

    Grant succeeded.

    SQL> conn oggdemo;
    Enter password: 
    Connected.

    SQL> create table oggdemo(id int);

    Table created.

    ————————————————————————————————————————————————————————

    源库操作:

    8.   创建ext进程,tail文件,data_pump进程

    GGSCI (11g) 11> add ext exta, tranlog, begin now
    EXTRACT added.


    GGSCI (11g) 12> add exttrail /home/oracle/ogg/dirdat/la, ext exta, MEGABYTES 20      -- 创建源trail文件
    EXTTRAIL added.


    GGSCI (11g) 13> add extract dpea, EXTTRAILSOURCE /home/oracle/ogg/dirdat/la        -- 创建data_pump进程
    EXTRACT added.


    GGSCI (11g) 14> add rmttrail /home/oracle/ogg/dirdat/ra, ext dpea, MEGABYTES 20     --创建目标端trail文件
    RMTTRAIL added.


    GGSCI (11g) 15> info all

    Program Status Group Lag at Chkpt Time Since Chkpt

    MANAGER RUNNING 
    EXTRACT STOPPED DPEA 00:00:00 00:02:48 
    EXTRACT STOPPED EXTA 00:00:00 00:09:36

    GGSCI (11g) 17> edit param exta    --exta文件添加内容显示如下

    GGSCI (11g) 35> view param exta

    EXTRACT exta
    setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
    setenv (ORACLE_SID = orcl)
    USERID goldengate, PASSWORD goldengate
    EXTTRAIL /home/oracle/ogg/dirdat/la
    dynamicresolution
    table oggdemo.oggdemo;

    GGSCI (11g) 33> start exta

    Sending START request to MANAGER ...
    EXTRACT EXTA starting


    GGSCI (11g) 34> info all

    Program Status Group Lag at Chkpt Time Since Chkpt

    MANAGER RUNNING 
    EXTRACT STOPPED DPEA 00:00:00 01:40:39 
    EXTRACT RUNNING EXTA 01:47:26 00:00:01

    GGSCI (11g) 36> edit param dpea         --dpea文件添加内容显示如下

    GGSCI (11g) 39> view param dpea

    extract dpea
    setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
    passthru
    rmthost 10.100.25.14,mgrport 7809, compress
    rmttrail /home/oracle/ogg/dirdat/ra
    dynamicresolution
    table oggdemo.oggdemo;

     

    参数说明:

    passthru表示本进程是一个传输进程data pump,无需跟数据库交互,只需要搬运数据即可;
    因为data pump要传输数据到目标,所以需要配置rmthost和rmttrail指定目标主机和队列信息

     

    GGSCI (11g) 37> start dpea

    Sending START request to MANAGER ...
    EXTRACT DPEA starting


    GGSCI (11g) 38> info all

    Program Status Group Lag at Chkpt Time Since Chkpt

    MANAGER RUNNING 
    EXTRACT RUNNING DPEA 00:00:00 00:00:00 
    EXTRACT RUNNING EXTA 00:00:00 00:00:04

    ————————————————————————————————————————————————————————————

     

    目标数据库操作:

    9.     创建目标数据库的replicate进程

    GGSCI (11g) 6> add rep repa, exttrail /home/oracle/ogg/dirdat/ra, nodbcheckpoint
    REPLICAT added.

    参数说明: 

    repa为进程名,一般为rep开头表示是replicat进程,后面可以加1-2位字符标识,一般与ext进程对应;

    exttrail表示要抽取的数据队列,注意是目标端的队列位置;

    nodbcheckpoint表示不使用数据库检查点。

     

    GGSCI (11g) 17> edit param repa     -- repa文件添加内容显示如下

    GGSCI (11g) 20> view param repa

    replicat repa
    setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)
    setenv (ORACLE_SID = orcl)
    userid goldengate, password goldengate
    reperror default,abend
    discardfile /home/oracle/ogg/dirrpt/repa.dsc,append, megabytes 10
    assumetargetdefs
    dynamicresolution
    map oggdemo.oggdemo, target oggdemo.oggdemo;

     

    GGSCI (11g) 18> start repa

    Sending START request to MANAGER ...
    REPLICAT REPA starting


    GGSCI (11g) 19> info all

    Program Status Group Lag at Chkpt Time Since Chkpt

    MANAGER RUNNING 
    REPLICAT RUNNING REPA 00:00:00 00:00:01

     ————————————————————————————————————————————

    ok 一切正常结束。


    本文转自 张冲andy 博客园博客,原文链接:   http://www.cnblogs.com/andy6/p/6155488.html,如需转载请自行联系原作者



    展开全文
  • 1.long 字段的无法使用OGG 同步 2.clob字段的导入导出Bug , 生产使用network-link 导入导出太慢了,本地导入导出速度会快3到4倍 。但是测试环境的情况却相反 测试环境和生产环境测试结果如下: 最后还是以生产...

    1.long 字段的无法使用OGG 同步

     

    2.clob字段的导入导出Bug , 生产使用network-link 导入导出太慢了,本地导入导出速度会快3到4倍 。但是测试环境的情况却相反

    测试环境和生产环境测试结果如下: 最后还是以生产环境的数据为主。

    原因有2点,第一 : 测试环境的网络没有拥塞,所以感觉测试环境network-link I/O 速度可以接受。第二测试环境的I/O 太慢了,跟生产环境不是一个等量级,所以感觉生产环境 本地I/O 会快很多。

     

    数据局迁移遇到的Bug

     

    1.DRAFT_LOG 这个表有6G ,有clob 字段

     2._LOG 这张表因为有clob 字段,这张表是核心表,导出时间和导入时间会有比较长。大概有19G ,

    因为有clob 字段,导出时间会比较长。根据我们测试,

     

     

    expdp /impdp 索引分离迁移  http://blog.51cto.com/itbull/1078724

    数据泵 expdp导出

    nohup expdp  system/******** dumpfile=lysb_20121113_%U.dmp directory=dmp_dir schemas=scott logfile=expdp_20121113.log job_name=lysbexpdp &

    index导入时会占用大量时间,导入时先导入处index外的其他对象包括数据

    nohup impdp system/******** dumpfile=lysb_20121113_%U.dmp directory=dmp_dir schemas=scott logfile=impdp_20121113.log job_name=lysbexpdp TABLE_EXISTS_ACTION=REPLACE EXCLUDE=INDEX &

    index可以再重建或者单独导入

    nohup impdp system/******** dumpfile=lysb_20121113_%U.dmp directory=dmp_dir schemas=scott logfile=impdp_201211132.log job_name=lysbexpdp include=INDEX &

     

     

     

    测试1:(测试环境),不使用network_link 方式,

     

    EXPDP 这2张表时间会达到3个小时。

    IMPDP 这2张表时间也会超过3个小时。

     

     

    测试2:  (测试环境) ,使用network_link 方式,

     

    时间会缩短一倍,达到3个半小时。

     

     

    测试3:(生产环境),使用network_link 方式,

     

    从上午11点25发起,持续到16点,已经跑了4个半小时,还在导入。

     

    测试4:  (生产环境) ,使用本地导入导出方式,不使用network_link 方式,除去BAK_RGCT_DRAFT_LOG这个6G表。

     

    EXPDP 所有生产用户时间会达到1个小时10分钟。

    IMPDP 所有生产用户时间会达到1个小时10分钟。

     

    可以在2个半小时左右完成,

     

     

    1.

    检查项 "1.创建空库不需要初始化
    2.检查生产的FRA空间检查
    生产的在维护期间会创建一个回滚点并保留7天,所以要检查灾备FRA的卷空间是否能保留7天的日志量。不足需申请扩空间。"
    新建目标空库 "1,只包含system,sysaux,undotbs1,temp,users 表空间,不需要初始化。
    set line 200 pagesize 200
    select tablespace_name from dba_tablespaces a
    where (a.tablespace_name not in ('TEMP','SYSAUX','SYSTEM','USERS')
    and a.contents not in ('TEMPORARY','UNDO')) ;
    2,字符集、block size与源库保持一致
    select * from v$nls_parameters where parameter='NLS_CHARACTERSET';
    show parameter block_size;
    3,安装组件和源生产一致。
    select comp_name,version,status from dba_registry order by 1;
    4.源库和目标库具有相同的timeone;
    SELECT VERSION FROM V$TIMEZONE_FILE;

    8.db_cache_size
    设置为和源库相同
    show parameter db_cache_size
    9.注:9.1.目标库cpu_count 不要在spfile里进行设置,不需要和源库保持一致
    9.2.目标库的parallel_max_servers设置要和源库一致。
    10.job_queue_processes,aq_tm_processes 设置为0。
    11.修改新建的目标库为非归档模式
    12,11g数据库参数按标准新建库手册。
    13. 关闭Flashback。
    select flashback_on from v$database; --结果为NO
    15.awr配置为间隔15分钟,保留90天
    exec dbms_workload_repository.modify_snapshot_settings(interval=>15, retention=>90*24*60);
    col SNAP_INTERVAL format a20
    col RETENTION format a20
    select * from dba_hist_wr_control;"
    检查迁移对象是否是闭包的 "1:检查迁移用户引用非迁移用户的对象,如果引用的是系统表酌情处理;如果引用的是非系统表则要整改。
    select * from dba_dependencies dd where dd.owner in
    ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') --替换为实际用户
    and dd.referenced_owner not in
    ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE')
    2:检查非迁移用户引用迁移用户的对象,如果存在则与开发讨论处理方法。
    select * from dba_dependencies dd where dd.type<>'SYNONYM' and dd.owner not in
    ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') ----替换为实际用户
    and dd.referenced_owner in
    ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') ----替换为实际用户

    /"
    检查迁移schema的trigger是否是闭包的 "1:检查触发器的owner<>table_owner且owner不在迁移用户列表而table_owner在迁移用户列表的情况,这种触发器需要手工迁移
    select * from dba_triggers where owner<>table_owner and owner not in ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') and table_owner in ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') ;


    2:检查触发器的owner<>table_owner且owner在迁移用户列表而table_owner不在迁移用户列表的情况,这种触发器视情况决定是否迁移
    select * from dba_triggers where owner<>table_owner and owner not in ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') and table_owner in ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') ;


    3:检查触发器的triggering_eventowner'LOGON '且owner在迁移用户列表或table_owner在迁移用户列表的情况,这种触发器expdp不能导出需要手工迁移
    select * from dba_triggers where triggering_event='LOGON 'and (owner in ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE') or table_owner in ('BBSP','BBSPODS','BCDPETL','DBMONOPR','DBMGR','OVSEE'));

    "
    根据10g生产库中待迁移job清单信息,生成job迁移脚本并在测试环境验证通过。特别注意:脚本所有待迁移job其next_date都必须设为无限大。 t_update_status();
    检查10g生产库中是否有业务scheduler存在,这些scheduler也要进行迁移,如果存在有,本步要把迁移scheduler的脚本准备好 0

    "
    创建11g目标库到10g生产库的dblink" "create public database link dblink_migup connect to dbmgr identified by &pw
    using ' 生产库连接串 ';
    3:验证dblink是否通
    select * from dual@dblink_migup; --有结果返回表示通的否则不通需处理
    "
    "
    1. 创建要迁移的用户清单配置表(sys..t_user_cfg)

    " "create table sys.t_user_cfg as SELECT username, default_tablespace
    FROM dba_users@dblink_migup
    WHERE username not in
    ('SYS', 'SYSTEM', 'PUBLIC', 'OUTLN', 'WMSYS', 'ORDSYS', 'MDSYS',
    'CTXSYS', 'XDB', 'WKSYS', 'ANONYMOUS', 'ORDPLUGINS', 'EXFSYS',
    'LBACSYS', 'DIP', 'LBACSYS', 'ORACLE_OCM', 'TSMSYS', 'ADAMS',
    'TRACESVR', 'DBSNMP', 'PERFSTAT', 'DBSTATS', 'EXPFULL',
    'MDDATA', 'MGMT_VIEW', 'OLAPSYS', 'SI_INFORMTN_SCHEMA', 'OWBSYS',
    'SYSMAN', 'ORDDATA', 'OWBSYS_AUDIT', 'APEX_030200', 'APPQOSSYS',
    'MDSYS', 'FLOWS_FILES', 'SPATIAL_WFS_ADMIN_USR',
    'SPATIAL_CSW_ADMIN_USR', 'APEX_PUBLIC_USER', 'XS$NULL','DMSYS')
    minus
    select username, default_tablespace from dba_users;"
    目标端新增表空间 "1.查看源库表空间名称及大小,在目标库新建相同大小表空间
    set line 200 pagesize 200
    select tablespace_name from dba_tablespaces a
    where a.tablespace_name not in ('TEMP','SYSAUX','SYSTEM','USERS') ;

    --注意临时表空间是否有特殊的,CREATE TEMPORARY TABLESPACE RLMSTMP TEMPFILE '+DATA_HDG' SIZE 100M AUTOEXTEND ON;

    2.打开所有表空间的自动扩展
    select 'alter database datafile '''||file_name||''' autoextend on next 100M;' from dba_data_files ;
    select 'alter database tempfile '''||file_name||''' autoextend on next 100M;' from dba_temp_files ;
    执行上面生成的脚本
    复核:
    select df.tablespace_name,df.autoextensible from dba_temp_files df where df.autoextensible='NO';
    select df.tablespace_name,df.autoextensible from dba_data_files df where df.autoextensible='NO';"
    目标端创建role "SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000
    spool $HOME/01_create_role.sql
    select 'create role ' || role || ';'
    from (select role
    from dba_roles@dblink_migup
    minus
    select role from dba_roles);
    spool off;
    在目标库执行01_create_role.sql"
    创建密码函数 "执行附件脚本创建密码函数sys.VERIFY_FUNCTION_11G
    PS:这个密码函数如果老库的名称不一样,需要把老库的函数迁移过来。"
    创建profile "SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000
    spool $HOME/02_deal_profile.sql
    select (case
    when rn = 1 then
    ('create profile ' || profile ||' limit ' || resource_name ||' ' || limit || (case
    when rn = max_rn then
    ';'
    end)) else(resource_name || ' ' || limit || (case
    when rn = max_rn then
    ';'
    end)) end)
    from (select profile,
    resource_name,
    limit,
    row_number() over(partition by profile order by profile, resource_name) rn,
    count(*) over(partition by profile) max_rn
    from dba_profiles@dblink_migup
    where profile in
    (select distinct profile
    from dba_profiles@dblink_migup
    minus
    select distinct profile from dba_profiles)
    order by profile, resource_name)
    order by profile, resource_name;
    spool off;

    执行02_deal_profile.sql

    alter profile default limit PASSWORD_VERIFY_FUNCTION VERIFY_FUNCTION_11G;
    alter profile default limit FAILED_LOGIN_ATTEMPTS 10;
    复核:
    select * from dba_profiles minus select * from dba_profiles@dblink_migup;
    select * from dba_profiles@dblink_migup minus select * from dba_profiles;"
    创建公共同义词 "SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000
    spool $HOME/03_create_public_synonym.sql
    SELECT
    'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';'
    FROM dba_synonyms@dblink_migup
    WHERE owner = 'PUBLIC'
    AND table_owner IN ( select username from sys.t_user_cfg t) ;
    spool off;
    在目标库执行03_create_public_synonym.sql"
    移植cron "
    将原hp平台上的crontab移植到新linux主机上。
    "

     

     2.

    21:00 导出BBSP.RGCT_DRAFT_LOG "export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
    cd /db/db1/oradata/dba/exp_table
    vi expdp_rgct_log.par
    userid='/ as sysdba'
    directory=schema_exp
    dumpfile=expdp_rgct_log_%U.dmp
    logfile=expdp_rgct_draft_log.log
    parallel=4
    filesize=10240M
    tables=(BBSP.RGCT_DRAFT_LOG)
    content=all "
    22:00 scp传输到linux scp *.dmp cradmin@10.198.217.193:/db/db1/app/opdb1/db1_impdp
    22:00 新库创建用户 "create user bbsp identified by values 'DF01EB216CE6E6BE' default tablespace bbsp profile default;
    grant unlimited tablespace to bbsp;
    alter user bbsp quota unlimited on users;
    alter user bbsp quota unlimited on bbsp;
    "
    22:10 导入BBSP.RGCT_DRAFT_LOG "chown -R opdb1:dba /db/db1/app/opdb1/db1_impdp/*.dmp

    export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
    cd /db/db1/app/opdb1/db1_impdp
    vi impdp_rgct_log.par
    userid='/ as sysdba'
    directory=schema_imp
    dumpfile=expdp_rgct_log_%U.dmp
    logfile=impdp_rgct_draft_log.log
    parallel=4
    tables=(BBSP.RGCT_DRAFT_LOG)
    content=all

    nohup impdp parfile=impdp_rgct_log.par &

    检查导出导入日志是否有报错"
    T-0 "DBA检查DB中的long oper、
    rbs占用,
    并请运营确认和做相应处理" "sqlplus '/ as sysdba'
    alter system switch logfile ;
    alter system switch logfile ;
    alter system switch logfile ;
    alter system archive log current;

    1)检查long operation
    select sid, target,opname, sofar,totalwork,time_remaining
    from gv$session_longops where time_remaining>0;
    2)检查占用回滚段较多的事务:
    select xidusn,s.last_call_et, s.username, used_ublk,USED_UREC,space,
    RECURSIVE, NOUNDO, START_UBAFIL,start_ubablk, start_uext
    from gv$transaction t, gv$session s
    where t.ses_addr=s.saddr;
    "
    1:00 维护时间开始处理,通知应用停止应用 应用将库的所有相关应用系统和job、ETL任务等停止下来。
    T-0 检查卷,冻结源库双机软件 "冻结源库的双机,
    冻结目标库的双机"
    T-0 全备,磁带永久保留
    T-0 DBA停止报警,屏蔽cron "cd $HOME
    touch cron_null
    crontab cron_null
    crontab -l 返回值应该为空"
    T-0 修改端口 "lsnrctl stop db1
    修改监听端口为1537
    cd $ORACLE_HOME/network/admin
    vi listener.ora
    lsnrctl start db1"
    T-0 重建到旧库的db links。 "1:在目标库重建迁移用的临时dblink ---直接使用连接串
    drop public database link dblink_migup;
    create public database link dblink_migup connect to dbmgr identified by db1234DBA
    using ' (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.198.69.10)(PORT = 1537)) (CONNECT_DATA =(sid = db1)))';
    2:验证dblink是否通
    select * from dual@dblink_migup; --有结果返回表示通的否则不通需处理"
    T-0 修改job_queue_processes,aq_tm_processes为0,关闭audit "保留记录job_queue_processes及aq_tm_processes修改前的值。
    show parameter job_queue_processes --- 1000
    show parameter aq_tm_processes ---1
    alter system set job_queue_processes=0 scope=both;
    alter system set aq_tm_processes=0 scope=both;
    alter system set audit_trail=none scope=spfile;"
    T-0 "DBA检查DB中的long oper、
    rbs占用,
    并请运营确认和做相应处理" "sqlplus '/ as sysdba'
    alter system switch logfile ;
    alter system switch logfile ;
    alter system switch logfile ;
    alter system archive log current;

    1)检查long operation
    select sid, target,opname, sofar,totalwork,time_remaining
    from gv$session_longops where time_remaining>0;
    2)检查占用回滚段较多的事务:
    select xidusn,s.last_call_et, s.username, used_ublk,USED_UREC,space,
    RECURSIVE, NOUNDO, START_UBAFIL,start_ubablk, start_uext
    from gv$transaction t, gv$session s
    where t.ses_addr=s.saddr;
    "
    T-0 修改stream_pool_size alter system set streams_pool_size=128M scope=both;
    T-0 1.设置JOB的next_date为无限大 "备份原库的Job:create table dbmgr.dbajobs_old_bak as select * from dba_jobs;
    复核:SELECT COUNT(*) FROM dbmgr.dbajobs_old_bak;
    设置JOB的next_date为无限大:"
    T-0 原库创建用户清单表sys.t_user_list "create table sys.t_user_cfg as SELECT username, default_tablespace
    FROM dba_users
    WHERE username not in
    ('SYS', 'SYSTEM', 'PUBLIC', 'OUTLN', 'WMSYS', 'ORDSYS', 'MDSYS',
    'CTXSYS', 'XDB', 'WKSYS', 'ANONYMOUS', 'ORDPLUGINS', 'EXFSYS',
    'LBACSYS', 'DIP', 'LBACSYS', 'ORACLE_OCM', 'TSMSYS', 'ADAMS',
    'TRACESVR', 'DBSNMP', 'PERFSTAT', 'DBSTATS', 'EXPFULL',
    'MDDATA', 'MGMT_VIEW', 'OLAPSYS', 'SI_INFORMTN_SCHEMA', 'OWBSYS',
    'SYSMAN', 'ORDDATA', 'OWBSYS_AUDIT', 'APEX_030200', 'APPQOSSYS',
    'MDSYS', 'FLOWS_FILES', 'SPATIAL_WFS_ADMIN_USR',
    'SPATIAL_CSW_ADMIN_USR', 'APEX_PUBLIC_USER', 'XS$NULL','DMSYS');
    grant select on sys.t_user_cfg to public;"
    T-0 部署数据比对的procedure "grant select on dba_tab_columns to dbmgr;
    create table dbmgr.TAB_HASH
    (
    OWNER VARCHAR2(30),
    TABLE_NAME VARCHAR2(50),
    SQL_VALUE VARCHAR2(4000),
    HASH_VALUE NUMBER(20)
    ) ;

    CREATE OR REPLACE PROCEDURE DBMGR.GET_TAB_HASH8(V_OWNER VARCHAR2,
    V_TABLE_NAME VARCHAR2) AS
    v_sql_temp varchar2(4000);
    v_hash number(20);
    BEGIN
    FOR r in (SELECT COLUMN_NAME
    FROM DBA_TAB_COLUMNS
    WHERE OWNER = V_OWNER
    AND TABLE_NAME = V_TABLE_NAME
    AND DATA_TYPE NOT IN ('CLOB', 'BLOB')
    and OWNER IN (SELECT USERNAME FROM sys.t_user_cfg)
    order by 1 asc) loop
    v_sql_temp := v_sql_temp || '||' || R.COLUMN_NAME;
    end loop;
    v_sql_temp := 'select/*+ parallel(t 8) full(t)*/ sum(dbms_utility.get_hash_value(' ||
    substr(v_sql_temp, 3) || ', 0, 999999999)) from ' ||
    V_OWNER || '.' || V_TABLE_NAME || ' t';

    execute immediate v_sql_temp
    into v_hash;

    insert into tab_hash
    (owner, table_name, sql_value, hash_value)
    values
    (V_OWNER, V_TABLE_NAME, v_sql_temp || ';', v_hash);
    commit;
    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    END;
    /"
    T-0 为GG回滚库做的disable/enable trigger以及constraint脚本。 "set linesize 300

    SPOOL $HOME/disable_constraint_r.sql
    SELECT 'spool ./disable_constraint_r.log' FROM dual;
    SELECT 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'
    FROM dba_constraints
    WHERE owner IN (select username from sys.t_user_cfg)
    AND constraint_type = 'R'
    ORDER BY constraint_type desc, owner, table_name, constraint_name;
    SELECT 'spool off' FROM dual;
    SPOOL OFF

    SPOOL ./disable_trigger.sql
    SELECT 'spool ./disable_trigger.log' FROM dual;
    SELECT
    'alter trigger '||owner||'.'||trigger_name||' disable;'
    FROM dba_triggers
    WHERE owner IN (select username from sys.t_user_cfg)
    AND status <> 'DISABLED'
    ORDER BY owner, table_name, trigger_name;
    SELECT 'spool off' FROM dual;
    SPOOL OFF
    SPOOL ./enable_trigger.sql
    SELECT 'spool ./enable_trigger.log' FROM dual;
    SELECT
    'alter trigger '||owner||'.'||trigger_name||' enable;'
    FROM dba_triggers
    WHERE owner IN (select username from sys.t_user_cfg)
    AND status = 'ENABLED'
    ORDER BY owner, table_name, trigger_name;
    SELECT 'spool off' FROM dual;
    SPOOL OFF

    SPOOL ./enable_constraint_r_novalidate.sql
    SELECT 'spool ./enable_constraint_r_novalidate.log' FROM dual;
    SELECT
    'alter table '||owner||'.'||table_name||' modify constraint '||constraint_name||' enable novalidate;'
    FROM dba_constraints
    WHERE owner IN (select distinct username from sys.t_user_cfg)
    AND constraint_type = 'R'
    AND STATUS = 'ENABLED'
    ORDER BY constraint_type desc, owner, table_name, constraint_name;
    SELECT 'spool off' FROM dual;
    SPOOL OFF

    SPOOL ./enable_constraint_src_r.sql
    SELECT 'spool ./enable_constraint_src_r.log' FROM dual;
    SELECT
    'alter table '||owner||'.'||table_name||' modify constraint '||constraint_name||' enable novalidate;'
    FROM dba_constraints
    WHERE owner IN (select distinct username from sys.t_user_cfg)
    AND constraint_type = 'R'
    AND status = 'ENABLED'
    ORDER BY constraint_type desc, owner, table_name, constraint_name;
    SELECT 'spool off' FROM dual;
    SPOOL OFF"
    T-0 重启生产库 "
    shutdown immediate;
    startup
    "
    T-0 停止同城灾备db 没有灾备库
    T-0 清空回收站 Purge dba_recyclebin;
    T-0 expdp导出 "export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
    connect / as sysdba
    create or replace directory schema_exp as '/db/db1/oradata/expdp_db1';
    cd /db/db1/oradata/expdp_db1

    userid='/ as sysdba'
    directory=schema_exp
    dumpfile=expdp_schemas_%U.dmp
    logfile=expdp_schemas.log
    parallel=6
    filesize=10240M
    schemas=BBSP,BBSPODS,BCDPETL,DBMONOPR
    content=all
    EXCLUDE=TABLE:""in ('BAK_RGCT_DRAFT_LOG','RGCT_DRAFT_LOG')""

    nohup expdp parfile=expdp.par &"
    检查error more expdp_schemas.log |grep -i error
    T-0 impdp导入 "scp *.dmp cradmin@10.198.217.193:/db/db1/app/opdb1/db1_impdp
    chown -R opdb1:dba /db/db1/app/opdb1/db1_impdp/*.dmp
    export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

    userid='/ as sysdba'
    directory=schema_imp
    dumpfile=expdp_schemas_%U.dmp
    logfile=impdp_schemas.log
    parallel=6
    schemas=BBSP,BBSPODS,BCDPETL,DBMONOPR
    content=all

    nohup impdp parfile=impdp.par &"
    T-0 检查error more impdp_schemas.log |grep -i error
    T-0 grant GRANT SELECT ON "BBSP"."RGCT_DRAFT_LOG" TO "BCDPETL"
    T-0 查出时点ID记录数最大的做保存 select max(l.id) from bbsp.rgct_draft_log l
    T-0 用记录的最大id查出新增数据 select count(*) from bbsp.rgct_draft_log l where l.id>xxxxx; --替换为上一步查出的值
    T-0 补录新增数据到bbsp.rgct_draft_log "insert into bbsp.rgct_draft_log select * from bbsp.rgct_draft_log@dblink_migup where id>xxxxxxxx ;
    commit;"
    T-0 "数据核对
    并且点开lob字段查询是否有值" "select count(*) from bbsp.rgct_draft_log ;
    select * from bbsp.rgct_draft_log ;"
    T-0 清除impdp导到目标库sys用户下的job并复核 "select * from dba_jobs;
    清除impdp导到sys用户下的job
    DECLARE X NUMBER;
    BEGIN
    for cur in ( select * from dba_jobs j where j.log_user = 'SYS') loop
    SYS.DBMS_iJOB.remove(job => cur.job );
    COMMIT;
    end loop ;
    commit;
    END;
    /"
    T-1 创建公共同义词 "SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000
    spool $HOME/03_create_public_synonym.sql
    SELECT
    'create or replace public synonym '||synonym_name||' for '||table_owner||'.'||table_name||';'
    FROM dba_synonyms@dblink_migup
    WHERE owner = 'PUBLIC'
    AND table_owner IN ( select username from sys.t_user_cfg t) ;
    spool off;
    在目标库执行03_create_public_synonym.sql"
    T-0 生成用户审计信息 "SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000
    spool $HOME/04_audit_users.sql
    SELECT
    'audit connect by '||user_name||';'
    FROM dba_stmt_audit_opts@dblink_migup
    WHERE audit_option = 'CREATE SESSION'
    and user_name in ( select username from sys.t_user_cfg )
    and user_name is not null;
    SELECT
    'audit '||audit_option||' by '||user_name||' by session whenever successful;'
    FROM dba_stmt_audit_opts@dblink_migup
    WHERE audit_option <> 'CREATE SESSION'
    and user_name in ( select username from sys.t_user_cfg )
    and user_name is not null;
    SPOOL OFF;"
    T-0 添加用户审计信息 执行04_audit_user.sql
    T-0 给用户和角色授权 "1.执行附件脚本04_grant.sql
    2.可以看到生成的脚本$HOME/grant_role_priv.sql,$HOME/grant_sys_priv.sql,$HOME/grant_sys_tab_priv.sql
    3.检查没问题后执行上面生成的3个脚本"
    T-0 目标库编译失效对象 sqlplus> @?/rdbms/admin/utlrp.sql;
    T-0 对比源库和目标库确保无新增失效对象 "select owner ,object_name, status from dba_objects where status = 'INVALID'
    minus
    select owner ,object_name, status from dba_objects@dblink_migup where status = 'INVALID';"
    T-0 检查表和索引的统计信息是否被锁住 "select owner,table_name,partition_name,t.stattype_locked from dba_tab_statistics t where t.stattype_locked is not null and t.owner in (select username from sys.t_user_cfg);
    select owner,index_name,table_owner,table_name,partition_name,stattype_locked from dba_ind_statistics t where t.stattype_locked is not null and t.owner in (select username from sys.t_user_cfg);
    如果有结果,说明有表或索引统计信息被锁定

    执行下列拼出来的语句
    select case when partition_name is null then 'exec dbms_stats.unlock_table_stats(ownname =>''' ||owner || ''',tabname =>''' || table_name || ''');'
    else 'exec dbms_stats.unlock_table_stats(ownname =>''' ||owner || ''',tabname =>''' || table_name ||''',partname =>'''|| partition_name||''');' end
    from dba_tab_statistics t
    where t.stattype_locked is not null
    and t.owner in (select username from sys.t_user_cfg);

    select case when partition_name is null then 'exec dbms_stats.unlock_table_stats(ownname =>''' ||table_owner || ''',tabname =>''' || table_name || ''');'
    else 'exec dbms_stats.unlock_table_stats(ownname =>''' ||table_owner || ''',tabname =>''' || table_name ||''',partname =>'''|| partition_name||''');' end
    from dba_ind_statistics t where t.stattype_locked is not null and t.owner in (select username from sys.t_user_cfg);"
    T-0 "复核以下项在源和目标环境中是否相等
    " " 检查迁移用户系统权限
    检查迁移用户角色权限
    检查迁移用户对象权限
    检查迁移用户审计
    检查迁移用户对象同义词
    检查迁移用户db_link
    检查迁移用户context
    检查迁移用户的对象总数
    迁移用户的constraint
    检查迁移用户的index(含索引字段)
    检查迁移用户的表结构
    迁移用户的trigger
    检查迁移用户的表记录数
    检查迁移用户的seq数及last_number"
    T-0 核心表数据比对 "select count(*)登记中心数 from BBSP.rgct_bill_info
    union
    select count(*)登记中心历史数 from BBSP.rgct_bill_hist

    T-0 打开归档 "shutdown immediate;
    startup nomount;
    alter database mount;
    alter database archivelog;
    alter database open;"
    T-0 关闭undo和temp表空间的自动扩展 "select 'alter database datafile '''||file_name||''' autoextend off;' from dba_data_files where tablespace_name like 'UNDO%';
    select 'alter database tempfile '''||file_name||''' autoextend off;' from dba_temp_files ;
    执行产出脚本"
    T-0 复核job参数 "检查Job参数设置,设置为0
    SQL> show parameter job_queue_processes --0
    SQL> show parameter aq_tm_processes --0"
    force logging开启 alter database force logging;
    T-0 gg混滚链路处理 "alter table BBSP.CODE logging;
    alter table BBSP.PRODUCT logging;
    "
    T-0 gg链路处理 按gg回滚手册进行部署
    T-0 配置监听 "【新库恢复监听】
    lsnrctl stop db1
    并修改监听端口为1528

    cd $ORACLE_HOME/network/admin
    vi listener.ora

    lsnrctl start db1 --监听端口是1528"
    T-0 DNS切换,清理缓存 找应用值班同事执行
    T-0 验证连通性 sqlplus客户端验证
    T-0 启动中间件和相关应用
    T-0 通知开发、应用验证应用
    T-0 打开Job参数 "SQL> alter system set job_queue_processes = 1000 scope = both;
    SQL> alter system set aq_tm_processes=1 scope = both;;
    复核:
    SQL> show parameter job_queue_processes
    SQL> show parameter aq_tm_processes
    "
    T-0 恢复cron文件 无
    T-0 注册catalog "Rman注册:
    步骤如下(主库):
    shell>rman target / catalog rman11g/rman11g@cat11g
    RMAN> register database;
    RMAN> report schema;
    RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 90 DAYS;
    RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
    RMAN>show all;"

     

     

    2.1 set_job_nextdate.sql

    SET termout on feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000
    set SERVEROUTPUT on size 200000
    alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss' ;


    begin
    for cur in ( select * from dba_jobs j where j.BROKEN = 'N' ) loop
    sys.dbms_ijob.next_date(job => cur.job,next_date=>to_date('2999/9/9','yyyy/mm/dd')) ;
    end loop ;
    commit ;
    end;
    /

    select count(1) from dba_jobs j where j.next_date < to_date('2999-09-09','yyyy-mm-dd') and j.broken = 'N';

     

    2.2 04_grant.sql

    SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000
    spool $HOME/grant_role_priv.sql
    SELECT 'spool $HOME/grant_role_priv.log' FROM dual;
    SELECT 'grant ' || granted_role || ' to ' || grantee || CASE admin_option
    WHEN 'YES' THEN
    ' with admin option;'
    ELSE
    ';'
    END
    FROM dba_role_privs@dblink_migup
    WHERE grantee IN
    ( select username from sys.t_user_cfg
    union
    select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from sys.t_user_cfg)
    and granted_role like 'R\_%' escape '\'
    ) ;
    SELECT 'spool off;' FROM dual;
    SPOOL OFF;
    commit;
    SET termout on echo on


    --SET termout OFF feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

    --@$HOME/grant_role_priv.sql
    --commit;

     

    SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000
    spool $HOME/grant_sys_priv.sql
    SELECT 'spool $HOME/grant_sys_priv.log' FROM dual;
    SELECT
    'grant '||privilege||' to '||grantee
    ||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END
    FROM dba_sys_privs@dblink_migup
    WHERE grantee IN
    ( select username from sys.t_user_cfg
    union
    select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from sys.t_user_cfg)
    and granted_role like 'R\_%' escape '\'
    ) ;
    SELECT
    'grant '||privilege||' to '||grantee
    ||CASE admin_option WHEN 'YES' THEN ' with admin option;' ELSE ';' END
    FROM dba_sys_privs@dblink_migup
    WHERE grantee IN (SELECT role FROM dba_roles WHERE password_required <> 'NO' AND ROLE LIKE 'R\_%' ESCAPE '\');
    SELECT 'spool off;' FROM dual;
    SPOOL OFF;
    commit;
    SET termout on echo on

     

    --SET termout OFF feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

    --@$HOME/grant_sys_priv.sql
    --commit;


    SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000
    spool $HOME/grant_sys_tab_priv.sql
    SELECT 'spool $HOME/grant_sys_tab_priv.log' FROM dual;

    SELECT
    'grant '||privilege||' on '
    ||CASE WHEN substr(table_name,1,2)='V$' THEN 'V_$'||substr(table_name,3,length(table_name-2)) ELSE table_name END
    ||' to '||grantee||CASE grantable WHEN 'YES' THEN ' with grant option;' ELSE ';' END
    FROM dba_tab_privs@dblink_migup
    WHERE grantee IN
    ( select username from sys.t_user_cfg
    union
    select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from sys.t_user_cfg)
    and granted_role like 'R\_%' escape '\'
    )
    AND owner = 'SYS'
    and table_name NOT in('EXPDIR','V_$TEMP_HISTOGRAM','V_$STANDBY_APPLY_SNAPSHOT','GV_$TEMP_HISTOGRAM','GV_$STANDBY_APPLY_SNAPSHOT' ,'DBA_ADVISOR_DIRECTIVES','DBA_CACHEABLE_TABLES' , 'DBA_CACHEABLE_OBJECTS_BASE' , 'DBA_CACHEABLE_OBJECTS' ,'DBA_USERS2') -----opms Special
    AND privilege NOT IN ('READ','WRITE');
    SELECT
    'grant '||privilege||' on directory '||table_name||' to '||grantee
    ||CASE grantable WHEN 'YES' THEN ' with grant option;' ELSE ';' END
    FROM dba_tab_privs@dblink_migup
    WHERE grantee IN
    ( select username from sys.t_user_cfg
    union
    select distinct granted_role from dba_role_privs@dblink_migup WHERE grantee IN (select username from sys.t_user_cfg)
    and granted_role like 'R\_%' escape '\'
    )
    AND owner = 'SYS'
    AND privilege IN ('READ', 'WRITE')
    AND table_name not in ( 'EXPDIR' );
    SELECT 'spool off;' FROM dual;
    SPOOL OFF;
    commit;
    SET termout on echo on

    --SET termout OFF feedback on verify OFF echo on heading OFF trims ON escape ON pages 0 linesize 999 long 3000

    --@$HOME/grant_sys_tab_priv.sql
    --commit;

     

     

    2.3 check.sql

    ----check object -----------------
    --CHECK OBJECT


    1.准备工作 (11G RAC)
    step 0:
    alter user dbmgr identified by dbmgr1234;

    alter system set local_listener = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.197.1.203)(PORT=1528)))' sid='db1' scope = memory;

    step 1:

    drop public database link dblink_migup;

    create public database link dblink_migup connect to dbmgr identified by dbmgr1234 using ' (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.197.1.103)(PORT = 1528)) (CONNECT_DATA =(sid = db1)))';


    step 2:

    drop table sys.t_user_cfg;


    create table sys.t_user_cfg as SELECT username, default_tablespace
    FROM dba_users
    WHERE username not in
    ('SYS', 'SYSTEM', 'PUBLIC', 'OUTLN', 'WMSYS', 'ORDSYS', 'MDSYS',
    'CTXSYS', 'XDB', 'WKSYS', 'ANONYMOUS', 'ORDPLUGINS', 'EXFSYS',
    'LBACSYS', 'DIP', 'LBACSYS', 'ORACLE_OCM', 'TSMSYS', 'ADAMS',
    'TRACESVR', 'DBSNMP', 'PERFSTAT', 'DBSTATS', 'EXPFULL',
    'MDDATA', 'MGMT_VIEW', 'OLAPSYS', 'SI_INFORMTN_SCHEMA', 'OWBSYS',
    'SYSMAN', 'ORDDATA', 'OWBSYS_AUDIT', 'APEX_030200', 'APPQOSSYS',
    'MDSYS', 'FLOWS_FILES', 'SPATIAL_WFS_ADMIN_USR','SQLTXPLAIN','DBMONOPR','SCOTT',
    'SPATIAL_CSW_ADMIN_USR', 'APEX_PUBLIC_USER', 'XS$NULL','DMSYS','SQLTXADMIN','ZABBIX','DBMGR','OGG','OVSEE','FOGLIGHT','PAO');


    set pagesize 0
    select * from sys.t_user_cfg ;

     

    2:验证dblink是否通

    select * from dual@dblink_migup;

    --有结果返回表示通的否则不通需处理" 未完成


    3.开始使用连接PL/SQL developer 端连接数据库 RAC 11G
    用户名:dbmgr/dbmgr1234
    字符串:
    10.197.1.205:1528/db


    4.PL/SQL devoper 开一个SQL 窗口,执行以下语句,(重点关注 jobs dbmgr.save_ash('db') 在11g是否存在;)

     

    --CHECK jobs
    select log_user,schema_user,what,broken
    from dba_jobs@dblink_migup
    where schema_user in (select username from sys.t_user_cfg)
    and last_date is not null
    minus
    select log_user,schema_user,what,broken
    from dba_jobs
    where schema_user in (select username from sys.t_user_cfg)
    and last_date is not null;

    --CHECK scheduler_jobs
    select job_name from dba_scheduler_jobs@dblink_migup where owner !='SYS'
    minus
    select job_name from dba_scheduler_jobs where owner !='SYS';


    --CHECK OBJECT
    select owner,object_name,object_type,status
    from dba_objects@dblink_migup
    where owner in (select username from sys.t_user_cfg)
    and status <> 'INVALID'
    minus
    select owner,object_name,object_type,status
    from dba_objects
    where owner in (select username from sys.t_user_cfg)
    and status <> 'INVALID';


    ----check system priv-------------
    select grantee,privilege from dba_sys_privs@dblink_migup
    where grantee in ( select username from sys.t_user_cfg)
    minus
    select grantee,privilege from dba_sys_privs
    where grantee in ( select username from sys.t_user_cfg) ;

    ----check role priv-------------
    select grantee,granted_role,admin_option from dba_role_privs@dblink_migup
    where grantee in ( select username from sys.t_user_cfg)
    minus
    select grantee,granted_role,admin_option from dba_role_privs
    where grantee in ( select username from sys.t_user_cfg) ;

    ---check role----
    select role from dba_roles@dblink_migup
    minus
    select role from dba_roles;

    --CHECK SYS PRIVS
    select grantee,privilege,admin_option
    from dba_sys_privs@dblink_migup
    where grantee in (select username from sys.t_user_cfg)
    minus
    select grantee,privilege,admin_option
    from dba_sys_privs
    where grantee in (select username from sys.t_user_cfg) ;

    --CHECK ROLE PRIVS
    select grantee,granted_role,admin_option
    from dba_role_privs@dblink_migup
    where grantee in (select username from sys.t_user_cfg)
    minus
    select grantee,granted_role,admin_option
    from dba_role_privs
    where grantee in (select username from sys.t_user_cfg) ;

    ----check user object priv-------------
    select grantee,owner,table_name,grantor,privilege,grantable from dba_tab_privs@dblink_migup
    where grantee in ( select username from sys.t_user_cfg)
    minus
    select grantee,owner,table_name,grantor,privilege,grantable from dba_tab_privs
    where grantee in ( select username from sys.t_user_cfg) ;

    -----check quota---------------
    select username,max_bytes
    from dba_ts_quotas@dblink_migup
    where username in (select username from sys.t_user_cfg)
    minus
    select username,max_bytes
    from dba_ts_quotas
    where username in (select username from sys.t_user_cfg) ;
    --check audit-------------
    select user_name,audit_option,success,failure
    from dba_stmt_audit_opts@dblink_migup
    where user_name in ( select username from sys.t_user_cfg)
    minus
    select user_name,audit_option,success,failure
    from dba_stmt_audit_opts
    where user_name in ( select username from sys.t_user_cfg) ;

    --check synonym no rows selected
    select owner,synonym_name,table_owner,table_name
    from dba_synonyms@dblink_migup
    where table_owner in ( select username from sys.t_user_cfg)
    minus
    select owner,synonym_name,table_owner,table_name
    from dba_synonyms
    where table_owner in ( select username from sys.t_user_cfg) ;

    --check db links no rows selected
    select owner,db_link,username,host from dba_db_links@dblink_migup
    where owner in ( select username from sys.t_user_cfg)
    minus
    select owner,db_link,username,host from dba_db_links
    where owner in ( select username from sys.t_user_cfg) ;

    --check db context no rows selected
    select namespace,schema,package,type
    from dba_context@dblink_migup
    where schema in ( select username from sys.t_user_cfg)
    minus
    select namespace,schema,package,type
    from dba_context
    where schema in ( select username from sys.t_user_cfg) ;

    --CHECK OBJECT COUNT
    --select owner,object_type,status,count(*)
    -- from dba_objects@dblink_migup
    -- where owner in (select username from sys.t_user_cfg)

    -- And (owner,object_name) Not In (Select owner,segment_name From dba_lobs@dblink_migup
    -- Union All Select owner,index_name From dba_lobs@dblink_migup)
    -- group by owner,object_type,status
    --minus
    --select owner,object_type,status,count(*)
    -- from dba_objects
    -- where owner in (select username from sys.t_user_cfg)
    --- group by owner,object_type,status;


    --check objects
    --老库比新库,结果应为空
    select owner,object_name,object_type
    from dba_objects@dblink_migup
    where owner in (select username from sys.t_user_cfg)
    minus
    select owner,object_name,object_type
    from dba_objects
    where owner in (select username from sys.t_user_cfg) ;

    --新库比老库,结果显示出来的对象需要邓玉坤确认是否是新建对象
    select * from (
    select owner,object_name,object_type
    from dba_objects
    where owner in (select username from sys.t_user_cfg)
    minus
    select owner,object_name,object_type
    from dba_objects@dblink_migup
    where owner in (select username from sys.t_user_cfg) ) t where t.object_name not like 'SYS_IL000%' and t.object_name not like 'AQ$_%';


    --check table no rows selected
    select owner,table_name,partitioned,iot_type,temporary
    from dba_tables@dblink_migup
    where owner in ( select username from sys.t_user_cfg)
    minus
    select owner,table_name,partitioned,iot_type,temporary
    from dba_tables
    where owner in ( select username from sys.t_user_cfg) ;

    --check constraint no rows selected
    select owner, constraint_name, constraint_type, table_name, r_owner, status
    from dba_constraints@dblink_migup
    where owner in (select username from sys.t_user_cfg)

    and constraint_name not like 'SYS%' and constraint_name not like 'BIN$%'
    minus
    select owner, constraint_name, constraint_type, table_name, r_owner, status
    from dba_constraints
    where owner in (select username from sys.t_user_cfg)

    and constraint_name not like 'SYS%' and constraint_name not like 'BIN$%';

    ------check index---------------------
    select index_owner,index_name,table_owner,table_name,column_name,column_position
    from dba_ind_columns@dblink_migup
    where table_owner in (select username from sys.t_user_cfg)

    minus
    select index_owner,index_name,table_owner,table_name,column_name,column_position
    from dba_ind_columns
    where table_owner in (select username from sys.t_user_cfg)
    ;

    --check tab columns no rows selected
    select owner,table_name,column_name,data_type,data_length,nullable,column_id from dba_tab_columns@dblink_migup
    where owner in (select username from sys.t_user_cfg) and table_name not like 'AQ$%'
    and table_name not like 'BIN$%'
    minus
    select owner,table_name,column_name,data_type,data_length,nullable,column_id from dba_tab_columns
    where owner in (select username from sys.t_user_cfg) and table_name not like 'AQ$%'
    ;

    --check comments no rows selected
    select owner,table_name,column_name,comments from dba_col_comments@dblink_migup
    where owner in (select username from sys.t_user_cfg)
    and table_name not like 'BIN$%'
    minus
    select owner,table_name,column_name,comments from dba_col_comments
    where owner in (select username from sys.t_user_cfg)
    ;

    ------check profile
    select * from dba_profiles@dblink_migup
    minus
    select * from dba_profiles ;

    ------check mview
    select owner,mview_name,container_name,query_len,rewrite_enabled,refresh_mode,refresh_method from dba_mviews@dblink_migup
    where owner in (select username from sys.t_user_cfg)

    minus
    select owner,mview_name,container_name,query_len,rewrite_enabled,refresh_mode,refresh_method from dba_mviews
    where owner in (select username from sys.t_user_cfg)
    ;

    -----check user
    select a.username,a.password apwd,b.password bpwd,a.profile apf,b.profile bpf,
    a.account_status acs,b.account_status bcs
    From
    (
    select du.username,su.password,du.profile,du.account_status
    from dba_users@dblink_migup du,sys.user$ su
    where username in (select username from sys.t_user_cfg) and du.username = su.name
    ) a
    ,
    (
    select du.username,su.password,du.profile,du.account_status
    from dba_users du,sys.user$ su
    where du.username in (select username from sys.t_user_cfg)
    and du.username = su.name
    ) b
    Where a.username = b.username And (a.password <> b.password Or a.profile <> b.profile Or a.account_status <> b.account_status);


    --------check trigger--
    select owner,trigger_name,trigger_type,table_owner,table_name,column_name,status
    from dba_triggers@dblink_migup
    where owner in (select username from sys.t_user_cfg)

    minus
    select owner,trigger_name,trigger_type,table_owner,table_name,column_name,status from dba_triggers
    where owner in (select username from sys.t_user_cfg)
    ;

    --check table owner trigger no rows selected

    select owner,trigger_name,trigger_type,table_owner,table_name,column_name
    from dba_triggers@dblink_migup where owner in (select username from sys.t_user_cfg)
    minus
    select owner,trigger_name,trigger_type,table_owner,table_name,column_name
    from dba_triggers where owner in (select username from sys.t_user_cfg);


    ----------check sequence 结果为空
    select ds.sequence_owner,
    ds.sequence_name,
    ds.min_value,
    ds.max_value,
    ds.increment_by,
    ds.cycle_flag,
    ds.order_flag,
    ds.cache_size
    from dba_sequences@dblink_migup ds
    where sequence_owner in (select username from sys.t_user_cfg)
    minus
    select ds.sequence_owner,
    ds.sequence_name,
    ds.min_value,
    ds.max_value,
    ds.increment_by,
    ds.cycle_flag,
    ds.order_flag,
    ds.cache_size
    from dba_sequences ds
    where sequence_owner in (select username from sys.t_user_cfg);

    --比对sequence值,结果应为空,新库SEQUNCE值比老库大
    select *
    from (select sequence_owner,
    sequence_name,
    lastnum_10,
    lastnum_11,
    lastnum_11 - lastnum_10 delta_num
    from (select ds.sequence_owner sequence_owner,
    ds.sequence_name sequence_name,
    ds.last_number lastnum_10,
    dt.last_number lastnum_11
    from dba_sequences dt, dba_sequences@dblink_migup ds
    where dt.sequence_owner in
    (select username from sys.t_user_cfg)
    and dt.sequence_owner = ds.sequence_owner
    and dt.sequence_name = ds.sequence_name))
    where delta_num < 0;

    5.删除db_link
    drdrop public database link dblink_migup;op public database link dblink_migup;

     

     

    ###################################

    3.OGG 安装

     

    上传不同平台的goldengate软件 gg软件两个平台都要安装
    "goldengate临时装到data卷上
    1.cd /db/{ORACLE_SID}/data
    mkdir goldendate
    2.unzip goldengate软件
    " 请注意 goldengate 软件区分os平台和oracle版本 ,另外对于RAC,goldengate软件安装目录需要设置为共享存储
    "cd
    vi .profile
    ##追加以下内容
    GGS_HOME=/db/{ORACLE_SID}/data/goldengate
    export GGS_HOME
    LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GGS_HOME:$LD_LIBRARY_PATH
    export LD_LIBRARY_PATH
    PATH=$GGS_HOME:$PATH
    export PATH

    hp-unix:step:
    . ./profile_bcds

    " "1、请注意平台不同,profile文件配置也不同
    2、对于RAC,每个节点上的profile都需要配置;"
    "1.检查:
    select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
    2.如果是未有, 打开supplementary log:
    set timing on
    alter system checkpoint;
    alter system checkpoint;
    ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;" "1.linux 结果如下:
    SUPPLEME
    --------
    YES

    2.hp unix 结果如下:
    SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

    SUPPLEME
    --------
    YES

    "
    "create user ggmgr identified by ggmgr123 default tablespace users temporary tablespace temp;
    grant create session,alter session to ggmgr;
    grant alter system to ggmgr;
    grant resource to ggmgr;
    grant connect to ggmgr;
    grant select any dictionary to ggmgr;
    grant flashback any table to ggmgr;
    grant select any table to ggmgr;
    grant select any table to ggmgr;
    grant insert any table to ggmgr;
    grant update any table to ggmgr;
    grant delete any table to ggmgr;
    grant select on dba_clusters to ggmgr;
    grant execute on dbms_flashback to ggmgr;
    grant create table to ggmgr;
    grant create sequence to ggmgr;
    grant alter any table to ggmgr;
    grant dba to ggmgr;
    grant lock any table to ggmgr;" 1.linux/hp-unix done
    "cd $GGS_HOME
    mkdir direnv"
    ./ggsci
    "create subdirs
    exit"
    "cd $GGS_HOME
    ./ggsci
    GGSCI> EDIT PARAMS ./GLOBALS
    --输入如下内容
    CHECKPOINTTABLE ggmgr.gg_checkpoint_tab
    syslog none" "1.linux/hp-unix done
    当使用GG版本11.x时,需要在GG manager参数中配置syslog none,使gg日志不写入操作系统的syslog。"
    "GGSCI> DBLOGIN USERID ggmgr, PASSWORD ggmgr123
    GGSCI> ADD CHECKPOINTTABLE ggmgr.gg_checkpoint_tab
    GGSCI>exit" 1.linux/hp-unix done
    "cd $GGS_HOME/direnv
    1.vi db.oby
    SETENV (ORACLE_SID = ""bcds"")

    2.vi user.oby
    userid ggmgr, password ggmgr123
    " "1.linux/hp-unix done
    ORACLE_SID = ""bcds"" 注意大小写要和环境变量保持一致"
    "cd $GGS_HOME/dirprm
    vi mgr.prm
    port 7809
    dynamicportlist 7809-7819
    autostart er *
    autorestart er *, retries 24, waitminutes 3, resetminutes 1440
    lagreportminutes 10
    laginfoseconds 30
    LAGCRITICALMINUTES 45
    purgeoldextracts ./dirdat/*, usecheckpoints, minkeephours 168
    " "
    1.linux/hp-unix done
    删除PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10 "
    "cd $GGS_HOME
    ./ggsci
    start mgr"
    info mgr
    GGSCI>dblogin userid ggmgr, password ggmgr12

     

     

    2.迁移当天设置回滚环境

     创建2条链路,链路一(step 1)是非CLOB 表,   链路2 (step 2)是clob 表

    准备参数文件 准备e进程、d进程、r进程的参数文件
    为ggmgr用户临时授权 "grant insert any table to ggmgr;
    grant delete any table to ggmgr;
    grant update any table to ggmgr;
    "
    设置参数 alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;
    创建gg_send表 "CREATE TABLE ggmgr.GG_SEND(
    TESTER VARCHAR2(30) NOT NULL,
    SOURCE_DATABASE VARCHAR2(10) NOT NULL,
    TARGET_DATABASE VARCHAR2(10) NOT NULL,
    TEST_TIME DATE DEFAULT SYSDATE,
    Constraint PK_GG_SEND primary KEY (TESTER, SOURCE_DATABASE, TARGET_DATABASE, TEST_TIME) );

    COMMENT ON COLUMN ggmgr.GG_SEND.TESTER IS '测试人员UM账号';
    COMMENT ON COLUMN ggmgr.GG_SEND.SOURCE_DATABASE IS '源数据库SID';
    COMMENT ON COLUMN ggmgr.GG_SEND.TARGET_DATABASE IS '目标数据库SID';
    COMMENT ON COLUMN ggmgr.GG_SEND.TEST_TIME IS '测试时间';
    COMMENT ON TABLE ggmgr.GG_SEND is 'GoldenGate接收端连通性测试表';

     

    2. step 2 table 2:
    CREATE TABLE ggmgr.GG_SEND_2(
    TESTER VARCHAR2(30) NOT NULL,
    SOURCE_DATABASE VARCHAR2(10) NOT NULL,
    TARGET_DATABASE VARCHAR2(10) NOT NULL,
    TEST_TIME DATE DEFAULT SYSDATE,
    Constraint PK_GG_SEND_2 primary KEY (TESTER, SOURCE_DATABASE, TARGET_DATABASE, TEST_TIME) );

    COMMENT ON COLUMN ggmgr.GG_SEND_2.TESTER IS '测试人员UM账号';
    COMMENT ON COLUMN ggmgr.GG_SEND_2.SOURCE_DATABASE IS '源数据库SID';
    COMMENT ON COLUMN ggmgr.GG_SEND_2.TARGET_DATABASE IS '目标数据库SID';
    COMMENT ON COLUMN ggmgr.GG_SEND_2.TEST_TIME IS '测试时间';
    COMMENT ON TABLE ggmgr.GG_SEND_2 is 'GoldenGate接收端连通性测试表';


    "
    添加trandata "cd $GGS_HOME
    ./ggsci
    dblogin userid ggmgr password ggmgr123
    delete trandata BBSP.*
    delete trandata BBSPODS.*
    delete trandata BCDPETL.*

    add trandata BBSP.*
    add trandata BBSPODS.*
    add trandata BCDPETL.*
    "

    --WARNING OGG-00869  All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.  这个报错可以忽略

     

     


    对超过32列无主键的表添加trandata "
    执行/tmp/dba/add_trandata_long32.sql
    --报错WARNING OGG-01387需要执行"

     

    1.部署pkg(排除了clob表)
    2.执行查询
    SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000

    spool /tmp/dba/add_trandata_long32.sql
    select trim(sql_add_supp) from TABLE(
    select cast(gg_supplemental_32.sql_supplemental_32('AFA')
    as gg_supplemental_32_table_type )
    from dual
    );
    select trim(sql_add_supp) from TABLE(
    select cast(gg_supplemental_32.sql_supplemental_32('AUTEK')
    as gg_supplemental_32_table_type )
    from dual
    );

     


    检查trandata是否添加成功 "执行下面sql,返回空为成功。( --如果不为 空 ,add trandata BBSPODS.*  ,会报错error no data found retrieving导致漏掉一些表,所以需要手工add trandata BBSPODS.table_name) 
    select 'add trandata '||owner||'.'|| table_name from dba_tables where owner in ('BBSP',' BBSPODS','BCDPETL') and table_name not in (select distinct table_name from dba_log_group_columns where owner in ('BBSP',' BBSPODS','BCDPETL'));

    --排除掉一些临时备份表或者CLOB表或者名称为小写表,剩余的表就需要手工add   trandata BBSPODS.table_name
    --clob 表会自动创建唯一索引的

    --add trandata BBSPODS.*  会漏掉一些表,所以需要手工add trandata BBSPODS.table_name
    --同时在GGSCI 重新运行返回的结果SQL,

     

    "
    上传参数文件 "cd $GGS_HOME/dirprm

    cd $GGS_HOME/dirprm
    step 1:
    将e_rollbk.prm,d_rollbk.prm上传到此目录下
    step 2:
    将e_rollbk_2.prm,d_rollbk_2.prm上传到此目录下
    chown -R opDB:dba /crdb/DB/data/goldengate/dirprm

     

    chown -R opbcds:dba /ogg/bcds/dirprm

    "
    创建GG进程 "cd $GGS_HOME  (注意  OGG 的进程名总数不能超过8个 )
    ./ggsci

    cd $GGS_HOME
    ./ggsci
    dblogin userid ggmgr password ggmgr123

    step 1:
    add extract e_rollbk, tranlog, begin now
    add exttrail ./dirdat/e1, extract e_rollbk, megabytes 50

    add extract d_rollbk, exttrailsource ./dirdat/e1, params dirprm/d_rollbk.prm
    add rmttrail ./dirdat/dr, extract d_rollbk, megabytes 50

    step 2:
    add extract e_roll_2, tranlog, begin now
    add exttrail ./dirdat/e2, extract e_roll_2, megabytes 50

    add extract d_roll_2, exttrailsource ./dirdat/e2, params dirprm/d_roll_2.prm
    add rmttrail ./dirdat/d2, extract d_roll_2, megabytes 50

     

    "
    修改回滚链路r进程参数文件 "cd $GGS_HOME/dirprm

    cd $GGS_HOME/dirprm
    step 1:
    上传r_rollbk.prm
    step 2:
    上传r_rollbk_2.prm

    chown -R opUSER:dba /crdb/DB/oradata/goldengate/dirprm


    chown -R opbcds:dba /oggfs/ogg/dirprm "
    创建GG进程 "cd $GGS_HOME
    ./ggsci
    dblogin userid ggmgr password ggmgr123

    step 1:
    add replicat r_rollbk, exttrail ./dirdat/dr, params dirprm/r_rollbk.prm , CHECKPOINTTABLE ggmgr.gg_checkpoint_tab

    step 2:
    add replicat r_roll_2, exttrail ./dirdat/d2, params dirprm/r_roll_2.prm , CHECKPOINTTABLE ggmgr.gg_checkpoint_tab

     


    "
    创建目标端连通性测试表

    step 1:
    CREATE TABLE ggmgr.GG_RECEIVE_R_ROLLBK(
    TESTER VARCHAR2(30) NOT NULL,
    SOURCE_DATABASE VARCHAR2(10) NOT NULL,
    TARGET_DATABASE VARCHAR2(10) NOT NULL,
    TEST_TIME DATE DEFAULT SYSDATE,
    Constraint PK_GG_RECEIVE_R_ROLLBK primary KEY (TESTER, SOURCE_DATABASE, TARGET_DATABASE, TEST_TIME) );

    COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK.TESTER IS '测试人员UM账号';
    COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK.SOURCE_DATABASE IS '源数据库SID';
    COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK.TARGET_DATABASE IS '目标数据库SID';
    COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK.TEST_TIME IS '测试时间';
    COMMENT ON TABLE ggmgr.GG_RECEIVE_R_ROLLBK is 'GoldenGate接收端连通性测试表';

    step 2:
    CREATE TABLE ggmgr.GG_RECEIVE_R_ROLLBK_2(
    TESTER VARCHAR2(30) NOT NULL,
    SOURCE_DATABASE VARCHAR2(10) NOT NULL,
    TARGET_DATABASE VARCHAR2(10) NOT NULL,
    TEST_TIME DATE DEFAULT SYSDATE,
    Constraint PK_GG_RECEIVE_R_ROLLBK_2 primary KEY (TESTER, SOURCE_DATABASE, TARGET_DATABASE, TEST_TIME) );

    COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK_2.TESTER IS '测试人员UM账号';
    COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK_2.SOURCE_DATABASE IS '源数据库SID';
    COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK_2.TARGET_DATABASE IS '目标数据库SID';
    COMMENT ON COLUMN ggmgr.GG_RECEIVE_R_ROLLBK_2.TEST_TIME IS '测试时间';
    COMMENT ON TABLE ggmgr.GG_RECEIVE_R_ROLLBK_2 is 'GoldenGate接收端连通性测试表';

     

    ';

    "
    禁用级联删除和触发器 "SELECT 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';'
    FROM dba_constraints
    WHERE owner in ('BBSP',' BBSPODS','BCDPETL')
    AND constraint_type = 'R'
    ORDER BY constraint_type desc, owner, table_name, constraint_name;

    select 'alter trigger '||owner||'.'||trigger_name||' disable ;'
    from all_triggers
    where owner in ('BBSP',' BBSPODS','BCDPETL')
    order by owner,trigger_name;

    复核:
    select owner,table_name,constraint_name,status from dba_constraints where owner in ('BBSP',' BBSPODS','BCDPETL') and constraint_type = 'R' and status <> 'DISABLED'; --无结果产出

    select owner,trigger_name,table_owner,table_name,status from dba_triggers where owner in ('BBSP',' BBSPODS','BCDPETL') and status <> 'DISABLED'; --无结果产出
    "
    启动extract和dump进程 

    step 1:
    start e_rollbk
    start d_rollbk
    step 2:
    start e_roll_2
    start d_roll_2
    info all
    <进程都是RUNNING状态>

     

    启动同步到unix的回滚R进程

    cd $GGS_HOME

    ./ggsci
    step 1:
    start r_rollbk

    step 2:
    start r_roll_2

     


    插入测试信息 "sqlplus '/as sysdba'

    step 1
    sqlplus '/as sysdba'
    insert into ggmgr.GG_SEND values('测试','source_sid','target_sid',SYSDATE);
    commit;

    step 2:
    insert into ggmgr.GG_SEND_2 values('测试','source_sid','target_sid',SYSDATE);
    commit;


    检测测试信息是否同步过来 "sqlplus '/as sysdba'

    step 1:
    sqlplus '/as sysdba'
    select * from ggmgr.GG_RECEIVE_R_ROLLBK ;

    step 2:
    sqlplus '/as sysdba'
    select * from ggmgr.GG_RECEIVE_R_ROLLBK_2 ;


    for clob table:
    select count(*) from f_cus_reprintinfo;


    for no-clob table:

    select count(*) from t_ibps_paymentbook

    "

     

    3.3 OGG PKG

     

    查看32列以上 没有主键 的非clob表   (排除了clob表,如果有 clob 表,那么可能需要另外单独为clob表 单独创建一个链路,解释2  CLOB 表会自动 创建一个唯一性的索引。可以正常使用ogg 同步,所以下面pkg 不检测clob 表) :

    1.部署pkg
    2.执行查询
    SET termout OFF feedback OFF verify OFF echo OFF heading OFF trims ON escape ON pages 0 linesize 999 long 3000
    select trim(sql_add_supp) from TABLE(
    select cast(gg_supplemental_32.sql_supplemental_32('BBSP')
    as gg_supplemental_32_table_type )
    from dual
    );

     

     

    Pkg info:

    create or replace type gg_supplemental_32_row_type as object
    (
    owner varchar2(32),
    table_name varchar2(32),
    seq# number,
    sql_add_supp varchar2(2048)
    );
    /
    create or replace type gg_supplemental_32_table_type as table of gg_supplemental_32_row_type;
    /
    create or replace package gg_supplemental_32 AUTHID CURRENT_USER is
    function sql_supplemental_32(i_owner varchar2)
    return gg_supplemental_32_table_type;
    end gg_supplemental_32;
    /

    create or replace package body gg_supplemental_32 as
    function sql_supplemental_32(i_owner varchar2)
    return gg_supplemental_32_table_type as
    z_data gg_supplemental_32_table_type := gg_supplemental_32_table_type();
    n number;
    cursor c is
    select dt.owner || '.' || dt.table_name obj,
    dt.column_name,
    dt.table_name,
    dt.owner,
    Row_Number() over(partition by dt.owner || '.' || dt.table_name order by dt.column_name) rn,
    do.object_id
    from dba_tab_columns dt, dba_objects do
    where dt.owner = i_owner
    and dt.data_type not in
    ('CLOB', 'BLOB', 'NCLOB', 'LONG', 'LONG RAW', 'ANYDATA',
    'ALERT_TYPE', 'XMLTYPE', 'WM_PERIOD', 'SQL_PLAN_TABLE_TYPE',
    'BFILE')
    and dt.data_type not like '%$%'
    and dt.owner = do.owner
    and dt.table_name = do.object_name

    and do.OBJECT_TYPE='TABLE'
    and dt.table_name in
    (SELECT distinct (table_name) obj
    FROM dba_tab_columns
    WHERE owner = i_owner
    AND column_id > 32
    AND table_name in
    (SELECT table_name
    FROM dba_tables
    WHERE owner = i_owner
    MINUS (SELECT obj1.name
    FROM SYS.user$ user1,
    SYS.user$ user2,
    SYS.cdef$ cdef,
    SYS.con$ con1,
    SYS.con$ con2,
    SYS.obj$ obj1,
    SYS.obj$ obj2
    WHERE user1.name = i_owner
    AND cdef.type# = 2                                            
    AND con2.owner# = user2.user#(+)
    AND cdef.robj# = obj2.obj#(+)
    AND cdef.rcon# = con2.con#(+)
    AND obj1.owner# = user1.user#
    AND cdef.con# = con1.con#
    AND cdef.obj# = obj1.obj#
    UNION
    SELECT idx.table_name
    FROM dba_indexes idx
    WHERE idx.owner = i_owner
    AND idx.uniqueness = 'UNIQUE')));
    obj_prev varchar2(200);
    obj_flag number;
    sql_add_supp varchar2(4000);
    sql_post varchar2(2000);
    n_g number;
    pre_n_g number;
    z_owner varchar2(32);
    z_table_name varchar2(32);
    begin
    sql_post := ') always;';
    obj_flag := 0;
    for x in c loop
    n_g := trunc((x.rn - 1) / 32) + 1;
    n := mod(x.rn, 32);
    z_owner := x.owner;
    z_table_name := x.table_name;

    if obj_prev is null then
    sql_add_supp := 'ALTER TABLE ' || x.obj ||
    ' ADD SUPPLEMENTAL LOG GROUP GGS_' ||
    substr(x.table_name, 1, 16) || '_' || x.object_id || '_' || n_g || '(' ||
    x.column_name;
    obj_prev := x.obj;
    pre_n_g := n_g;
    elsif (obj_prev = x.obj) then
    if (n_g <> pre_n_g) then
    -- print out sql statement
    sql_add_supp := sql_add_supp || sql_post;
    z_data.extend;
    z_data(z_data.count) := gg_supplemental_32_row_type(z_owner,
    z_table_name,
    n_g,
    sql_add_supp);
    -- reset
    pre_n_g := n_g;
    sql_add_supp := 'ALTER TABLE ' || x.obj ||
    ' ADD SUPPLEMENTAL LOG GROUP GGS_' ||
    substr(x.table_name, 1, 16) || '_' || x.object_id || '_' || n_g || '(' ||
    x.column_name;
    else
    sql_add_supp := sql_add_supp || ' ,' || x.column_name;
    end if;
    else
    -- print out sql statement
    sql_add_supp := sql_add_supp || sql_post;
    z_data.extend;
    z_data(z_data.count) := gg_supplemental_32_row_type(z_owner,
    z_table_name,
    n_g,
    sql_add_supp);
    -- reset
    pre_n_g := n_g;
    obj_prev := x.obj;
    sql_add_supp := 'ALTER TABLE ' || x.obj ||
    ' ADD SUPPLEMENTAL LOG GROUP GGS_' ||
    substr(x.table_name, 1, 16) || '_' || x.object_id || '_' || n_g || '(' ||
    x.column_name;
    end if;
    end loop;
    -- print last sql if exist --
    sql_add_supp := sql_add_supp || sql_post;
    z_data.extend;
    z_data(z_data.count) := gg_supplemental_32_row_type(z_owner,
    z_table_name,
    n_g,
    sql_add_supp);

    return z_data;
    end sql_supplemental_32;
    end;
    /

     

     

    ###解释说明:

     

    解释1 非clob 表带有唯一主键的表。

    SELECT obj1.name
    FROM SYS.user$ user1,
    SYS.user$ user2,
    SYS.cdef$ cdef,
    SYS.con$ con1,
    SYS.con$ con2,
    SYS.obj$ obj1,
    SYS.obj$ obj2
    WHERE user1.name = i_owner
    AND cdef.type# = 2                                            
    AND con2.owner# = user2.user#(+)
    AND cdef.robj# = obj2.obj#(+)
    AND cdef.rcon# = con2.con#(+)
    AND obj1.owner# = user1.user#
    AND cdef.con# = con1.con#
    AND cdef.obj# = obj1.obj#

     

    ###以下是查  clob 表和非clob 表都带有唯一主键的表

    SELECT idx.table_name
    FROM dba_indexes idx
    WHERE idx.owner = i_owner
    AND idx.uniqueness = 'UNIQUE'

    解释2  CLOB 表会自动 创建一个唯一性的索引。所以OGG

    You are not allowed to operate directly on system-defined LOB index.

    Whenever you create a LOB column the database automatically creates two segments (LOB Data Segment, LOB index Segment).  You cannot drop/alter any of these segments separately, the only way to do so is to drop the LOB column itself and consequently the two associated segments will be dropped.

    TEST CASE
    =========
    SQL> create user user1 identified by user1;

    User created.

    SQL> grant dba to user1;

    Grant succeeded.

    SQL> connect user1/user1
    Connected.
    SQL> create table LOB_TAB(id number, name CLOB);

    Table created.

    SQL> insert into LOB_TAB values (1,'Ahmed');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> select index_name, index_type from dba_indexes where table_name like 
    'LOB_TAB' and owner like 'USER1';

    INDEX_NAME INDEX_TYPE
    ------------------------------ ---------------------------
    SYS_IL0000051347C00002$$ LOB

    SQL> drop index SYS_IL0000051347C00002$$;
    drop index SYS_IL0000051347C00002$$
    *
    ERROR at line 1:
    ORA-22864: cannot ALTER or DROP LOB indexes


    SQL> alter table LOB_TAB drop column name;

    Table altered.

    SQL>


    You can use the DBA_LOBS view to query for all the LOB objects related to a 
    certain user and then drop their columns one by one:

    SQL> col table_name format a20
    SQL> col column_name format a20
    SQL> select table_name, column_name from dba_lobs where owner like 'USER1';

    TABLE_NAME COLUMN_NAME
    -------------------- --------------------
    LOB_TAB NAME

    Note:  If you want to move them, see Note 130814.1 - How to move LOB Data to Another Tablespace
     
     
    ###解释 3
     
    target  :
    --db.oby

    SETENV (ORACLE_SID = "USER")

     

    --mgr.prm

    port 7809
    dynamicportlist 7809-7819
    autostart er *
    autorestart er *, retries 24, waitminutes 3, resetminutes 1440
    lagreportminutes 10
    laginfoseconds 30
    LAGCRITICALMINUTES 45
    purgeoldextracts ./dirdat/*, usecheckpoints, minkeephours 168

     

    ---r_roll_2.prm

    REPLICAT r_roll_2
    obey ./direnv/db.oby
    --If the NLS_CHARACTERSET of the Target DB is not the same as the Source DB, db_esb_lang.oby must be set in this file
    SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
    obey ./direnv/user.oby

    ASSUMETARGETDEFS

    -- control how often Extract and Replicat make their routine checkpoints
    CHECKPOINTSECS 30

    -- control the number of SQL operations that are contained in a Replicat transaction when
    GROUPTRANSOPS 20000

    -- Sets a global response to all errors except those for which explicit,
    -- Roll back the transaction and terminate processing abnormally
    REPERROR DEFAULT, ABEND

    -- generate a discard file to which GoldenGate can log records that it cannot process,
    -- Adds new content to existing content if the file already exists
    DISCARDFILE ./dirrpt/r_rollbk_2.dsc, append, MEGABYTES 2000

    -- set a schedule for aging discard files
    DISCARDROLLOVER AT 05:30 ON friday

    -- report a count of transaction records that Extract or Replicat processed since startup
    REPORTCOUNT EVERY 10000 RECORDS, RATE

    -- alter the rules for processing wildcarded table specifications in a TABLE, SEQUENCE, or MAP statement
    WILDCARDRESOLVE DYNAMIC

    -- control whether or not duplicate MAP statements for the same source and target objects are accepted in a parameter file
    ALLOWDUPTARGETMAP


    map ggmgr.gg_send_2 , target ggmgr.gg_receive_r_rollbk_2, &
    colmap (
    tester = tester ,
    source_database = source_database ,
    target_database = target_database ,
    test_time = test_time
    );

    MAP USER.*, TARGET USER.*;
    MAP AUTEK.*, TARGET AUTEK.*;

     

    --r_rollbk.prm

    REPLICAT r_rollbk
    obey ./direnv/db.oby
    --If the NLS_CHARACTERSET of the Target DB is not the same as the Source DB, db_esb_lang.oby must be set in this file
    SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
    obey ./direnv/user.oby

    ASSUMETARGETDEFS

    -- control how often Extract and Replicat make their routine checkpoints
    CHECKPOINTSECS 30

    -- control the number of SQL operations that are contained in a Replicat transaction when
    GROUPTRANSOPS 20000

    -- Sets a global response to all errors except those for which explicit,
    -- Roll back the transaction and terminate processing abnormally
    REPERROR DEFAULT, ABEND

    -- generate a discard file to which GoldenGate can log records that it cannot process,
    -- Adds new content to existing content if the file already exists
    DISCARDFILE ./dirrpt/r_rollbk.dsc, append, MEGABYTES 2000

    -- set a schedule for aging discard files
    DISCARDROLLOVER AT 05:30 ON friday

    -- report a count of transaction records that Extract or Replicat processed since startup
    REPORTCOUNT EVERY 10000 RECORDS, RATE

    -- alter the rules for processing wildcarded table specifications in a TABLE, SEQUENCE, or MAP statement
    WILDCARDRESOLVE DYNAMIC

    -- control whether or not duplicate MAP statements for the same source and target objects are accepted in a parameter file
    ALLOWDUPTARGETMAP


    map ggmgr.gg_send , target ggmgr.gg_receive_r_rollbk, &
    colmap (
    tester = tester ,
    source_database = source_database ,
    target_database = target_database ,
    test_time = test_time
    );

    MAP USER.*, TARGET USER.*;
    MAP AUTEK.*, TARGET AUTEK.*;

     

    ---user.oby

    userid ggmgr, password ggmgr123

     

     

    source :

    ---d_roll_2.prm

    --------------------------------------------------------------------------------------
    -- This prm file is used to set the todatapump parameters
    -- You need to replace all the '<>'
    -- Please input the parameter into GoldenGate management system before you replace it
    -- Please refer 'gg_wux_ref_v104.pdf' for the parameters's detail means
    --------------------------------------------------------------------------------------
    EXTRACT d_roll_2
    obey ./direnv/db.oby
    obey ./direnv/user.oby
    RMTHOST 10.198.67.14, MGRPORT 7809

    -- Specifies a trail to which extracted data is written on a remote system
    RMTTRAIL ./dirdat/d2

    --list the tables
    TABLE ggmgr.gg_send_2;

    table USER.E_CUS_REPRINTINFO;

     

    --d_rollbk.prm

    --------------------------------------------------------------------------------------
    -- This prm file is used to set the todatapump parameters
    -- You need to replace all the '<>'
    -- Please input the parameter into GoldenGate management system before you replace it
    -- Please refer 'gg_wux_ref_v104.pdf' for the parameters's detail means
    --------------------------------------------------------------------------------------
    EXTRACT d_rollbk
    obey ./direnv/db.oby
    obey ./direnv/user.oby
    RMTHOST 10.198.67.14, MGRPORT 7809

    -- Specifies a trail to which extracted data is written on a remote system
    RMTTRAIL ./dirdat/dr

    --list the tables
    TABLE ggmgr.gg_send;

    TABLE USER.*;
    TABLE AUTEK.*;

     

    --db.oby

    SETENV (ORACLE_SID = "USER")


    --e_roll_2.prm

    --------------------------------------------------------------------------------------
    -- This prm file is used to set the extract parameters
    -- You need to replace all the '<>'
    -- Please input the parameter into GoldenGate management system before you replace it
    -- Please refer 'gg_wux_ref_v104.pdf' for the parameters's means
    --------------------------------------------------------------------------------------
    EXTRACT e_roll_2
    obey ./direnv/db.oby
    obey ./direnv/user.oby
    --These two parameter must be added in OGG 11.1.1.1 (ȸö to elusion a bug. Do not add them in OGG 11.1.1 (}¸ö,for they are
    --not recognised before the version of OGG 11.1.1.X.
    TRANLOGOPTIONS _NOREADAHEAD ANY
    TRANLOGOPTIONS LOGRETENTION DISABLED
    TRANLOGOPTIONS DBLOGREADER

    --GoldenGate manager will monitor the long transections which is running upper than 4 hours, interval 30min
    WARNLONGTRANS 4h, CHECKINTERVAL 30m

    -- Specifies a trail to which extracted data is written on the local system
    EXTTRAIL ./dirdat/e2

    --list the tables
    TABLE ggmgr.gg_send_2;


    table USER.E_CUS_REPRINTINFO;


     

    --d_rollbk.prm

    --------------------------------------------------------------------------------------
    -- This prm file is used to set the todatapump parameters
    -- You need to replace all the '<>'
    -- Please input the parameter into GoldenGate management system before you replace it
    -- Please refer 'gg_wux_ref_v104.pdf' for the parameters's detail means
    --------------------------------------------------------------------------------------
    EXTRACT d_rollbk
    obey ./direnv/db.oby
    obey ./direnv/user.oby
    RMTHOST 10.198.67.14, MGRPORT 7809

    -- Specifies a trail to which extracted data is written on a remote system
    RMTTRAIL ./dirdat/dr

    --list the tables
    TABLE ggmgr.gg_send;

    TABLE USER.*;
    TABLE AUTEK.*;

     

     

    ---e_rollbk.prm

    --------------------------------------------------------------------------------------
    -- This prm file is used to set the extract parameters
    -- You need to replace all the '<>'
    -- Please input the parameter into GoldenGate management system before you replace it
    -- Please refer 'gg_wux_ref_v104.pdf' for the parameters's means
    --------------------------------------------------------------------------------------
    EXTRACT e_rollbk
    obey ./direnv/db.oby
    obey ./direnv/user.oby
    --These two parameter must be added in OGG 11.1.1.1 (ȸö to elusion a bug. Do not add them in OGG 11.1.1 (}¸ö,for they are
    --not recognised before the version of OGG 11.1.1.X.
    TRANLOGOPTIONS _NOREADAHEAD ANY
    TRANLOGOPTIONS LOGRETENTION DISABLED
    TRANLOGOPTIONS DBLOGREADER

    --GoldenGate manager will monitor the long transections which is running upper than 4 hours, interval 30min
    WARNLONGTRANS 4h, CHECKINTERVAL 30m

    -- Specifies a trail to which extracted data is written on the local system
    EXTTRAIL ./dirdat/e1

    --list the tables
    TABLE ggmgr.gg_send;

    TABLE USER.* ;
    TABLE AUTEK.* ;


    tableexclude USER.E_CUS_REPRINTINFO;

     

    --mgr.prm

    port 7809
    dynamicportlist 7809-7819
    autostart er *
    autorestart er *, retries 24, waitminutes 3, resetminutes 1440
    lagreportminutes 10
    laginfoseconds 30
    LAGCRITICALMINUTES 45
    purgeoldextracts ./dirdat/*, usecheckpoints, minkeephours 168

     

    --user.oby

    userid ggmgr, password ggmgr123

     

     

     

     

    转载于:https://www.cnblogs.com/feiyun8616/p/9286456.html

    展开全文
  • oracle ogg搭建

    2021-07-31 17:19:05
    --oogg搭建 -----(源,目标) -----1 创建ogg用户 ,在操作系统中,创建ogg用户。 useradd -g dba -G oinstall -d /home/ogg ogg -----2 设置环境变量 cp /home/oracle/.bashrc /home/ogg/ chown ogg:dba /home/ogg/....

    --oogg搭建
    -----(源,目标)
    -----1 创建ogg用户 ,在操作系统中,创建ogg用户。
    useradd -g dba -G oinstall -d /home/ogg ogg
    -----2 设置环境变量
    cp /home/oracle/.bashrc /home/ogg/
    chown ogg:dba /home/ogg/.bashrc
    vim /home/ogg/.bashrc
    --------------------
    export PATH=$ORACLE_BASE/ogg:$ORACLE_HOME/bin:$PATH
    export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
    export OGG_HOME=$ORACLE_BASE/ogg

    --------------------
    -----3 创建OGG安装目录
    ---------------------------
    mkdir  -p /u01/app/oracle/ogg  
    chown -R ogg:oinstall /u01/app/oracle/ogg    --OGG_HOME

    ---4 安装
    mkdir -p /home/ogg/insogg/
    unzip /home/oracle/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip -d /home/ogg/insogg/
    cd  /home/ogg/insogg/
    tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/app/oracle/ogg

    ---5 登录OGG测试(源)
    cd /u01/app/oracle/ogg
    ggsci

    GGSCI (localhost.localdomain) 1> create subdirs

    Creating subdirectories under current directory /u01/app/oracle/ogg

    Parameter files                /u01/app/oracle/ogg/dirprm: already exists
    Report files                   /u01/app/oracle/ogg/dirrpt: created
    Checkpoint files               /u01/app/oracle/ogg/dirchk: created
    Process status files           /u01/app/oracle/ogg/dirpcs: created
    SQL script files               /u01/app/oracle/ogg/dirsql: created
    Database definitions files     /u01/app/oracle/ogg/dirdef: created
    Extract data files             /u01/app/oracle/ogg/dirdat: created
    Temporary files                /u01/app/oracle/ogg/dirtmp: created
    Stdout files                   /u01/app/oracle/ogg/dirout: created


    文件路径 说明
    /u01/app/oracle/ogg/dirprm 存放OGG参数各的配置信息
    /u01/app/oracle/ogg/dirrpt 存放进程报告文件
    /u01/app/oracle/ogg/dirchk 存放检查点文件
    /u01/app/oracle/ogg/dirpcs 存放进程状态文件
    /u01/app/oracle/ogg/dirsql 存放SQL脚本文件
    /u01/app/oracle/ogg/dirdef 存放DEFGEN工具生成的数据定义
    ------6 oracle 配置 
    -----------创建表空间ogg 
    create tablespace ogg datafile '/u01/app/oracle/data/orcl/ogg01.dbf' size 50M autoextend on;
    ----------创建用户
    create user ogg identified by ogg default tablespace ogg;
    -----------为OGG用户授权
    --OGG用户需要以下权限,也有人为了省事,直接给OGG用户DBA权限。

    SQL> grant CONNECT, RESOURCE to ogg;
    SQL> grant SELECT ANY DICTIONARY,SELECT ANY TABLE to ogg;
    SQL> grant ALTER ANY TABLE to ogg;
    SQL> grant FLASHBACK ANY TABLE to ogg;
    SQL> grant EXECUTE on DBMS_FLASHBACK to ogg;
    SQL> grant insert any table to ogg;
    SQL> grant update any table to ogg;
    SQL> grant delete any table to ogg; 

    ----------- 打开源数据库的附加日志和force log
    SQL> select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

    SQL> alter database force logging;

    SQL> alter database add SUPPLEMENTAL log data;

    SQL> select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

    ---运行OGG支持DDL脚本
    --如果要让OGG支持DDL操作,还需要额外运行几个脚本,这些脚本是OGG带的而不是ORACLE带的,在OGG的安装目录都可以找到,如果在OGG的安装目录登录数据库,可以直接@加脚本的名字,源端与目标端都需要运行
    chown -R ogg:oinstall /u01/app/oracle/ogg
    sqlplus / as sysdba
    GRANT CREATE TABLE,CREATE SEQUENCE TO OGG;
    --权限配置以后.要重新执行
    SQL>@marker_setup.sql
    SQL>@ddl_setup
    SQL>@role_setup
    SQL> grant GGS_GGSUSER_ROLE to ogg;
    SQL>@ddl_enable
    --OGG原理及数据制造
    ----创建测试数据
    ----源数据库(源)
    conn scott/tiger
    @demo_ora_create.sql
    @demo_ora_insert.sql
    ----目标数据(目标)
    @demo_ora_create.sql


    ---------------OGG配置
    /*
    MGR管理进程(源,目标)
    检查点 (源,目标)
    捕获进程(源)
    投递进程(源)
    REPLICAT进程(目标)
    */

    ----源端添加表级TRANDATA(源)
    --添加表级的trandata可以理解为需要将哪些用户的哪些表和目标库同步,其实也是添加表级的supplemental log,但是只有上文打开的minimal supplemental log后,这个才生效。
    GGSCI (oggsource) 2>  dblogin userid  ogg,password ogg
    ---增加检查点
    GGSCI (oggsource) 2> add checkpointtable ogg.checkpoint
    GGSCI (dbdream) 3> add  trandata   scott.tcustmer  (---goldengate对符号比较敏感,在add trandata时不要用分号),成功添加表级TRANDATA后,可以通过INFO命令查看哪些表被添加了TRANDATA.
    GGSCI (dbdream) 3>INFO TRANDATA scott.*  

    ---目标和源端配置MGR管理进程 (源,目标)
    GGSCI (stream) 2> EDIT PARAMS MGR 加入以下两行内容
    PORT 7809
    PURGEOLDEXTRACTS /ogg/dirdat,USECHECKPOINTS
    参数说明:
    PORT 7809:OGG管理进程监控端口。
    PURGEOLDEXTRACTS:清除不需要的trail文件。
    /ogg/dirdat:trail文件存放位置。
    USECHECKPOINTS:使用检查点队列。
    GGSCI (localhost.localdomain) 11> start mgr
    GGSCI (oggtarget) 4> info  mgr
    文件存放路径$OGG_HOME/dirprm
    ---配置初始化数据进程(此步骤非必须,可以由其他方式完成)
    -------下面在源端配置捕获进程EINI_1。(源)  
    GGSCI (orcl) 11> ADD EXTRACT EINI_1, SOURCEISTABLE--------------源端加同步进程  删除进程 delete EXTRACT EIXI_1--对应的进程名称
    GGSCI (orcl) 12> INFO EXTRACT *, TASKS     
    GGSCI (orcl) 13> EDIT PARAMS EINI_1  
    ----------------------------
    EXTRACT EINI_1
    SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
    setenv (ORACLE_SID=orcl)
    userid  ogg, password ogg
    RMTHOST 192.168.137.131, MGRPORT 7809
    exttrail /u01/app/oracle/ogg/dirdat/ea
    --RMTTASK REPLICAT, GROUP RINI_1
    TABLE SCOTT.TCUSTMER;
    ------------------
    --源端配置捕获进程
    GGSCI (orcl) 13> EDIT PARAMS EINI_1  
    ----------------------------
    EXTRACT EINI_1
    SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
    setenv (ORACLE_SID=orcl)
    userid  ogg, password ogg
    RMTHOST 192.168.137.131, MGRPORT 7809  --端口号要和mgr一致
    --RMTTASK REPLICAT, GROUP RINI_1-----目标端名字一定要对应
    TABLE SCOTT.TCUSTMER;
    --TABLE SCOTT.DEPT_OGG;

    /*
    EXTRACT EINI_1:说明这是EXTRACT进程,名字是EINI_1 SETENV:
    环境变量,一定要设置和数据库字符集一样,否则可能会乱码
    USERID:数据库OGG用户 PASSWORD:数据库用户OGG的密码
    RMTHOST:目标端地址,如果在/etc/hosts文件里已经设置解析,可以写主机名 MGRPORT:
    目标端MGR管理进程监听的端口RMTTASK REPLICAT:
    目标端REPLICAT应用进程的组和名字
    TABLE:源端要初始化数据的表的名字
    */

    add extract EINI_1,tranlog,begin now   --添加抽取进程
    add exttrail /u01/app/oracle/ogg/dirdat/ea,extract EINI_1,megabytes 5   --意思是将EINI_1捕获的extract跟踪文件添加到./dirdat 目录,文件最大5M。
    start extract EINI_1
    --配置投递(传输跟踪文件)的pump进程(源)
    edit params pump1
    ---
    EXTRACT pump1
    RMTHOST 192.168.137.131, MGRPORT 7809
    RMTTRAIL ./dirdat/et
    PASSTHRU
    DYNAMICRESOLUTION
    TABLE scott.TCUSTMER;
    -----
    --如果添加错,下面命令删除
    --GGSCI (oracle1) 5> DELETE extract pump1、

    add extract pump1,exttrailsource /u01/app/oracle/ogg/dirdat/ea , begin now
    add rmttrail ./dirdat/et ,extract pump1
    info extract  pump1
    start extract pump1

    --datapump 进程参数
    /*
    EXTRACT <进程名>
    RMTHOST IP地址, MGRPORT 7809, COMPRESS  
    —目标端的ip地址,7809为目标端的管理端口,要求配置compress压缩参数,节省网络带宽,但会增加CPU开销
    PASSTHRU  
    —传输进程直接跟抽取进程交互,而不再和数据库进行交互,减少数据库资源的利用
    NUMFILES 5000  —最多处理多少个表
    RMTTRAIL ./dirdata/ya   —投递哪个队列,必须和抽取保持一致
    DYNAMICRESOLUTION     —动态解析表名
    TABLE schema.;    —需要投递的对象列表
    Sequence schema.;    —需要投递的sequence对象
    */


    ---源端和目标端配置OGG的检查点(源,目标)
    EDIT PARAMS   GLOBALS
    -----
    CHECKPOINTTABLE ogg.checkpoint
    --------------
    --这就告诉OGG检查点存放到OGG用户下的GGSCHKPT表中,但是还需要使用OGG用户登录数据库,创建检查点表,此时需要退出OGG,重新登录,否则可能会遇到下面的错误
    GGSCI (oggsource) 1> dblogin userid ogg,password ogg
    GGSCI (oggsource as ogg@dbdream) 2> ADD CHECKPOINTTABLE

    ----配置目标端REPLICAT进程 (特别留意名字为源端配置的名) (目标端)
    add replicat RINI_1,specialrun------目标端添加同步进程
    GGSCI (oracle2) 5> EDIT PARAMS RINI_1
    -----------
    REPLICAT RINI_1
    SETENV(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
    ASSUMETARGETDEFS
    USERID ogg, PASSWORD ogg
    DISCARDFILE  /u01/app/oracle/ogg/dirrpt/RINI_1.dsc, PURGE
    MAP scott.*, TARGET scott.*;
    ---------------------
    add replicat RINI_1,exttrail ./dirdat/et 
     info replicat RINI_1
     start replicat RINI_1
    /*
    REPLICAT RINI_1:说明这是REPLICAT应用进程,名字叫RINI_1
    SETENV:语言变量,同捕获进程EINI_1
    ASSUMETARGETDEFS:告诉OGG目标端和源端需要同步的表的结构完全一致,不需要OGG去检查表的结构,包括表名、字段名、字段类型、字段长度等,如果目标端和源端同步的表的结构不一样,需要使用SOURCEDEFS参数,
    USERID、PASSWORD:同捕获进程EINI_1参数介绍
    DISCARDFILE:错误信息存放位置及命名规则
    MAP:源端捕获的表的名字
    TARGET:目标端同步的表的名字,可以不在同一SCHEMA
    */
    --如果错误需要删除 可以命令 delete replicat RINI_1

    --------启动
    --配置好目标端的应用进程RINI_1后,就可以启动源端的捕获进程进行捕获数据了,而目标端的应用进程RINI_1不需要手动去启动,也就是说目标端RINI_1进程不需要管。
    --ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;(没找到对应得参数先略过,以后要改再说)

    --1.启动源端的管理进程(源,目标)
    start mgr
    --2.启动目标端的复制进程(目标)
     start replicat RINI_1
        --查看源端捕获进程状态:
         VIEW replicat RINI_1
    --3.启动源端的捕获进程:(源) 
    START EXTRACT EINI_1
        --查看源端捕获进程状态:
         VIEW REPORT EINI_1
    --4.启动源端的投递进程(源)
    start extract pump1
        --查看源端投递进程状态
         info extract  pump1

    info all  --可以查看所有进程
    --源端进程
    ---------------------------
    GGSCI (localhost.localdomain) 14> info all

    Program     Status      Group       Lag at Chkpt  Time Since Chkpt

    MANAGER     RUNNING                                           
    EXTRACT     RUNNING     EINI_1      00:00:00      00:00:08    
    EXTRACT     RUNNING     PUMP1       00:00:00      00:00:01    
    --目标端进程
    GGSCI (oracle2) 27> info all

    Program     Status      Group       Lag at Chkpt  Time Since Chkpt

    MANAGER     RUNNING                                           
    REPLICAT    RUNNING     RINI_1      00:00:00      00:00:03    
    -------------------------------------------

    总结:这个ogg实际难度还好,但是要细致,想一想好几年前我就没有配置正确,缺的课以后都是要补的,接下来先把新版的oracle安装学习一下,后面再学rac


    ---验证数据
    insert into scott.tcustmer select 'Ac','baikaishui','China','cc'  from dual;

    展开全文
  • OGG是一种基于日志的结构化数据复制软件,通过捕获源数据库online redo log (在线重做日志)或archive log(归档日志)获得数据变化,形成tail(队列文件 ),再将这些tail通过网络协议,传输到目标数据库,目标端通过...

    OGG原理

    OGG是一种基于日志的结构化数据复制软件,通过捕获源数据库online redo log (在线重做日志)或archive log(归档日志)获得数据变化,形成tail(队列文件 ),再将这些tail通过网络协议,传输到目标数据库,目标端通过解析,插入至目标端数据库,从而实现源端与目标端数据同步。

    OGG的特性:

    1.对生产系统影响小:实时读取交易日志,以低资源占用实现大交易量数据实时复制;

    2.以交易为单位复制,保证交易一致性:只同步已提交的数据;

    3.高性能,智能的交易重组和操作合并,使用数据库本地接口访问,并行处理体系,灵活的拓扑结构:支持一对一、一对多、多对一、多对多和双向复制等。

    环境准备

    1.两台服务器(或者虚拟机)(一个作为源端,一个作为目标端)

    2.源端Linux(CentOS7.5),目标端WindowsServer2012

    安装部署

    1.安装Oracle数据库
    在Linux系统安装Oracle11g

    2.Oracle参数调整

    ​ a)开启数据库归档日志(源端)

    --首先用sys登录数据库,查看oracle是否开启归档模式(su - oracle切换用户,然后sqlplus / as sysdba进入sql模式)
    archive log list;
    
    --非归档模式
    --Database log mode              No Archive Mode
    --Automatic archival             Disabled
    
    --归档模式
    --Database log mode              Archive Mode
    --Automatic archival             Enabled
    
    开启归档模式
    --先关闭数据库
    shutdown immediate;
    --启动数据库到mount状态
    startup mount;
    --启用归档模式
    alter database archivelog;
    --启动数据库
    alter database open;
    

    ​ b)开启数据库强制日志记录和最小附加日志(源端)

    --查看数据库是否强制日志(force_logging)和开启最小附加日志(supplemental_log_data_min)
    select force_logging, supplemental_log_data_min from v$database;
    --YES 开启;NO 未开启
    
    --开启强制日志
    alter database force logging;
    --开启最小附加日志
    alter database add supplemental log data;
    

    ​ c)Oracle版本在11.2.0.4.0之后需要调整enable_goldengate_replication(源端,目标端)

    ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
    

    ​ d)为ogg创建单独的表空间,临时表空间和用户,为用户授权DBA权限(源端,目标端)

    --创建表空间
    create tablespace OGG_DATA datafile '文件路径/ogg_data01.dbf' size 100M autoextend on maxsize 30G;
    --创建临时表空间
    create temporary tablespace OGG_TEMP tempfile '文件路径/ogg_temp01.dbf'  size 50m  autoextend on maxsize 10G;
    --创建用户
    create user ggs identified by "123456" default tablespace OGG_DATA temporary tablespace OGG_TEMP quota unlimited on OGG_DATA;
    --授予DBA权限
    grant dba to ggs;
    

    源端安装OGG软件

    1.图形化安装

    2.静默安装
    有些时候我们没有图形化界面得时候就,只能用命令行得方式去安装OGG这时候就需要用到静默安装,静默安装需要配置应答文件,我将ogg安装文件解压到了/u01/ogg目录下了,应答文件在OGG安装文件解压后的目录里(/u01/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp)

    应答文件(参考)

    
    ####################################################################
    ## Copyright(c) Oracle Corporation 2017. All rights reserved.     ##
    ##                                                                ##
    ## Specify values for the variables listed below to customize     ##
    ## your installation.                                             ##
    ##                                                                ##
    ## Each variable is associated with a comment. The comment        ##
    ## can help to populate the variables with the appropriate        ##
    ## values.                                                        ##
    ##                                                                ##
    ## IMPORTANT NOTE: This file should be secured to have read       ##
    ## permission only by the oracle user or an administrator who     ##
    ## own this installation to protect any sensitive input values.   ##
    ##                                                                ##
    ####################################################################
    
    #-------------------------------------------------------------------------------
    # Do not change the following system generated value. 
    #-------------------------------------------------------------------------------
    oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
    
    
    ################################################################################
    ##                                                                            ##
    ## Oracle GoldenGate installation option and details                          ##
    ##                                                                            ##
    ################################################################################
    
    #-------------------------------------------------------------------------------
    # Specify the installation option.
    # Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
    #         ORA11g for installing Oracle GoldenGate for Oracle Database 11g 
    #-------------------------------------------------------------------------------
    #安装OGG得版本  ORA11g是11g, ORA12c是12c
    INSTALL_OPTION=ORA11g
    
    #-------------------------------------------------------------------------------
    # Specify a location to install Oracle GoldenGate
    #-------------------------------------------------------------------------------
    #OGG安装路径
    SOFTWARE_LOCATION=/u01/ggs
    
    #-------------------------------------------------------------------------------
    # Specify true to start the manager after installation. 
    #-------------------------------------------------------------------------------
    #是否开启mgr进程 true开启 false 不开启
    START_MANAGER=true
    
    #-------------------------------------------------------------------------------
    # Specify a free port within the valid range for the manager process.
    # Required only if START_MANAGER is true.
    #-------------------------------------------------------------------------------
    #mgr进程得端口号
    MANAGER_PORT=7809
    
    #-------------------------------------------------------------------------------
    # Specify the location of the Oracle Database.
    # Required only if START_MANAGER is true.
    #-------------------------------------------------------------------------------
    #ORACLE_HOME的路径
    DATABASE_LOCATION=/u01/app/oracle/product/11.2.0.4/db_1
    
    
    ################################################################################
    ##                                                                            ##
    ## Specify details to Create inventory for Oracle installs                    ##
    ## Required only for the first Oracle product install on a system.            ##
    ##                                                                            ##
    ################################################################################
    
    #-------------------------------------------------------------------------------
    # Specify the location which holds the install inventory files.
    # This is an optional parameter if installing on
    # Windows based Operating System.
    #-------------------------------------------------------------------------------
    #Oracle产品目录地址oraInventory
    INVENTORY_LOCATION=/u01/app/oraInventory
    
    #-------------------------------------------------------------------------------
    # Unix group to be set for the inventory directory.  
    # This parameter is not applicable if installing on
    # Windows based Operating System.
    #-------------------------------------------------------------------------------
    #用户组 一般为Oracle的用户组
    UNIX_GROUP_NAME=oinstall
    
    

    配置好应答文件,执行命令开始静默安装ogg

    --进入OGG安装文件解压后的目录
    cd /u01/ogg/fbo_ggs_Linux_x64_shiphome/Disk1
    --静默安装命令格式
    ./runInstaller -silent -responseFile [应答文件全路径]
    --静默安装执行语句
    ./runInstaller -silent -responseFile /u01/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
    

    3.开启DDL

    --在OGG安装目录下进去sqlplus
    export ORACLE_SID=orcl
    sqlplus / as sysdba;
    SQL> @marker_setup
    SQL> @ddl_setup
    SQL> @role_setup
    SQL> GRANT GGS_GGSUSER_ROLE TO ggs;
    SQL> @ddl_enable
    

    4.mgr进程配置

    --进入ogg控制台(在OGG安装目录下执行)
    ./ggsci
    --编辑mgr配置
    edit params mgr
    --编辑mgr配置,保存退出,重启mgr进程生效
    stop mgr!
    start mgr
    

    a)mgr配置(参考)

    PORT 7909
    
    DYNAMICPORTLIST 7910-7930	
    PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 7	
    LAGREPORTHOURS 1
    LAGINFOMINUTES 30
    LAGCRITICALMINUTES 45
    AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 5
    

    5.EXTRACT(抽取)进程配置

    --配置抽取集成需要通过ogg登录到oracle
    dblogin userid ggs@orcl,password 123456
    --为需要同步的用户或表开启表级附加日志
    --同步某用户下的所有表
    add trandata 用户名.*
    --同步某张表
    add trandata 用户名.表名
    --编辑抽取进程参数
    edit params ext01(抽取进程名 不超过8个字符)
    --指定抽取进程从那个时间点抽取日志(前提数据库归档日志存在)
    --从当前时间点抽取
    --单实例
    add extract ext01,tranlog, begin now
    --RAC集群
    add extract ext01,tranlog, begin now,threads 2
    --从指定时间抽取
    --单实例
    add extract ext01,tranlog, begin 2020-12-20 00:00:00
    --RAC集群
    add extract ext01,tranlog, begin 2020-12-20 00:00:00,threads 2
    --绑定抽取进程抽取到本地的tail日志文件队列
    add exttrail ./dirdat/te,extract ext01 (te为tail日志文件名,只能是2个字符)
    --开启抽取进程
    start ext01
    

    a)EXTRACT抽取进程参数(参考)

    EXTRACT ext01
    SETENV (ORACLE_SID=orcl);
    SETENV (OGG_HOME=/u01/ggs);
    SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK);
    USERID ggs@orcl, PASSWORD AACAAAAAAAAAAAIAUEYFIIJCXEOHZDTB, ENCRYPTKEY default
    TRANLOGOPTIONS DBLOGREADER;
    REPORTCOUNT EVERY 5 HOURS,RATE;
    REPORT AT 01:05;
    EXTTRAIL ./dirdat/te;
    DISCARDFILE ./dirrpt/ext01.dsc,APPEND,MEGABYTES 4096;
    DYNAMICRESOLUTION;
    WARNLONGTRANS 12h,CHECKINTERVAL 30m;
    DDL INCLUDE OBJTYPE 'TABLE',INCLUDE OPTYPE 'ALTER';
    DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10;
    DDLOPTIONS REPORT;
    
    TABLE COMM.*;
    TABLE DICT.*;
    TABLE TEST.TAB1;
    

    6.投递进程配置

    --投递进程本质也是EXTRACT进程,只是分工不同
    --编辑投递进程
    edit params dpt01
    --指定投递进程要投递的源端tail日志文件
    add extract dpt01,exttrailsource ./dirdat/te
    add exttrail ./dirdat/te,extract dpt01
    --指定投递到远端(目标端)tail文件名
    add rmttrail ogg安装路径/dirdat/te,extract dpt01
    --启动投递进程(需要目标端的mgr进程启动)
    start dpt01
    

    a)投递进程配置(参考)

    EXTRACT dpt01
    PASSTHRU
    GETTRUNCATES
    RMTHOST 172.16.128.11,MGRPORT 7909,COMPRESS --目标端IP和OGG端口
    RMTTRAIL ./dirdat/te
    REPORTCOUNT EVERY 5 HOURS,RATE
    REPORT AT 01:05
    
    TABLE COMM.*;
    TABLE DICT.*;
    TABLE TEST.TAB1;
    

    7.数据备份

    --查询当前数据库scn号
    select current_scn from v$database;
    --创建导出数据的路径
    create or replace directory bak_dir as '/u01/bak';
    --按用户导出
    expdp 'userid="/ as sysdba"' directory=bak_dir DUMPFILE=TABLES_20210201_%U.dmp schemas=COMM,DICT LOGFILE=tables_backup0201.log PARALLEL=6  FILESIZE=30G flashback_scn=当前scn号 job_name=Tables_Export
    
    --按表导出
    expdp 'userid="/ as sysdba"' directory=bak_dir DUMPFILE=TABLES_20210201_%U.dmp tables=TEST.TAB1,tables2  LOGFILE=tables_backup0201.log PARALLEL=6 FILESIZE=30G flashback_scn=当前scn号 job_name=Tables_Export
    
    --PARALLEL=6  并行度 设置并行线程 提高导出速率 不要高于CPU线程数
    --FILESIZE=30G 设置导出文件大小
    

    目标端安装OGG软件(window)

    1.为ogg创建单独的表空间,临时表空间和用户,为用户授权DBA权限(源端,目标端)

    --创建表空间
    create tablespace OGG_DATA datafile '文件路径/ogg_data01.dbf' size 100M autoextend on maxsize 30G;
    --创建临时表空间
    create temporary tablespace OGG_TEMP tempfile '文件路径/ogg_temp01.dbf'  size 50m  autoextend on maxsize 10G;
    --创建用户
    create user ggs identified by "123456" default tablespace OGG_DATA temporary tablespace OGG_TEMP quota unlimited on OGG_DATA;
    --授予DBA权限
    grant dba to ggs;
    

    初始化备份数据

    --创建导出数据的路径
    create or replace directory dump as '/share/bak'
    --查看已经创建的导入导出目录
    select * from dba_directories;
    
    impdp system/szadmin@nanf directory=dump DUMPFILE=TABLES_20210201_%U.dmp LOGFILE=tables_import.log PARALLEL=6 table_exists_action=replace job_name=Tables_Import
    
    --table_exists_action=replace 表存在执行覆盖操作
    

    ogg图形化界面安装

    2.配置mgr进程

    --进入ogg控制台(在OGG安装目录下执行)
    ./ggsci
    --编辑./GLOBALS文件
    edit  param  ./GLOBALS
    CHECKPOINTTABLE ggs.checkpoint
    --编辑mgr配置
    edit params mgr
    --编辑mgr配置,保存退出,重启mgr进程生效
    stop mgr!
    start mgr
    --将进程注册到服务中(windows系统)
    install addservice
    

    a)mgr配置(参考)

    PORT 7909
    
    DYNAMICPORTLIST 7910-7930	
    PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 7	
    LAGREPORTHOURS 1
    LAGINFOMINUTES 30
    LAGCRITICALMINUTES 45
    AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 5
    

    3.配置REPLICAT(复制)进程

    --创建检查点
    checkpointtable ggs.checkpoint
    --编辑复制进程
    edit params rep01
    --指定复制进程要读取的tail日志文件
    add replicat rep01 exttrail ./dirdat/te,checkpointtable ggs.checkpoint
    --从指定SCN号开始同步数据,启动接收进程
    start replicat rep01,aftercsn 备份时使用的SCN号
    

    复制进程配置(参考)

    replicat  rep01
    SETENV (ORACLE_SID=orcl)
    SETENV (OGG_HOME=E:\ggs)
    userid ggs@orcl,PASSWORD 123456
    ASSUMETARGETDEFS
    ALLOWNOOPUPDATES
    GETTRUNCATES
    REPORT AT 1:59
    REPORTROLLOVER AT 2:00
    DISCARDFILE ./dirrpt/rep01.dsc,append,megabytes 4096
    DISCARDROLLOVER AT 05:00 
    DDL INCLUDE OBJTYPE 'TABLE',
    INCLUDE OPTYPE 'ALTER'
    ddl error default ignore retryop maxretries 3 retrydelay 5
    DBOPTIONS SUPPRESSTRIGGERS
    
    MAP TEST.TB1,TARGET TEST.TB1;
    
    展开全文
  • OGG搭建文档

    2016-03-30 10:01:00
    OGG搭建文档 一、OGG进程介绍 ogg一般来说有4个进程,Manager进程,Extract进程,Pump进程,Replicat进程 1.1 Manager进程 是GoldenGate的控制进程,它主要作用有以下几个方面:启动、监控、重启GoldenGate的...
  • OGG搭建步骤

    千次阅读 2017-12-06 19:40:51
    数据库级: SQL>alter database add supplemental log data; 表级: GGSCI (test4-154) > dblogin userid goldengate,password goldengate GGSCI (test4-154) > add trandata goldengate.TCU* 4,配置源端mgr...
  • OGG单向复制搭建

    千次阅读 2016-02-23 09:13:34
    OGG单向复制搭建 1架构规划 操作系统:oraclelinux 5 Oracle版本:11.2.0.1 服务器主机名 ogg_source ogg_target IP 192.168.127.100 192.168.127.101 Oracle sid ...
  • 在原ogg基础上重新搭建ogg 如果ogg的主从同步出现了问题,出现了无法修复的情况,我们就会准备重新搭建ogg,其实重新搭建的过程很简单,因为很多配置我们都做过了,所以只需要在原来OGG基础上按照以下几个步骤...
  • Oracle-----OGG搭建部署(goldengate)

    千次阅读 2020-05-18 14:33:25
    搭建环境:centos7.6、单节点Oracle数据库 一、安装goldengate软件 1.1创建ogg用户 在操作系统中,创建ogg用户。 useradd -g dba -G oinstall -d /home/ogg ogg 1.2设置环境变量 可以在原来Oracle数据库的环境变量...
  • 数据库安装+OGG数据同步

    千次阅读 2018-07-07 17:03:39
    数据库:Oracle 11g ... ogg搭建 --->数据同步 一、数据库安装 查看操作系统版本 [oracle@standbbk /]$ cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.2 (Maipo) 或者 uname -a ...
  • ogg搭建终版.docx

    2019-06-24 09:57:57
    该文档详细描述了在linux系统11gOracle数据库上安装Oracle goldengate的步骤,仅供参考
  • OGG:Oracle to MySQL异构复制环境搭建

    千次阅读 2017-08-23 23:44:31
    环境介绍source: Oracle RDBMS 11.2.0.4 OGG for oracle IP:192.168.5.221 ...OGG数据库用户:ogg4o OGG数据库表空间:oggtbs 数据库测试账户:lilitarget: MySQL 5.6.37 OGG for mysql IP:192.168.5.
  • PostgreSQL的OGG——Bucardo搭建手顺

    千次阅读 2020-03-06 10:49:20
    bucardo是PostgreSQL数据库中实现双向同步的软件,可以实现PostgreSQL数据库的双master的方案,不过bucardo中的同步都是异步的,它是通过触发器记录变化,程序是perl写的.bucardo可以实现postgresql的多主复制、主从...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 805
精华内容 322
关键字:

ogg数据库搭建