当前位置 博文首页 > LuciferLiu_DBA:OGG(11G)单实例-->单实例 搭建--同构同版本

    LuciferLiu_DBA:OGG(11G)单实例-->单实例 搭建--同构同版本

    作者:[db:作者] 时间:2021-06-16 18:15

    目录

    一、环境介绍

    二、GoldenGate安装(源端+目标端)

    1、创建OGG安装目录

    2、修改OGG操作系统用户环境变量

    4、上传OGG安装介质并解压

    5、建立OGG表空间及用户,给用户授权

    6、配置TNS

    7、配置数据库参数

    附加日志

    强制日志

    检查是否开启

    开启归档日志模式

    关闭回收站

    8、GGSCI初始化目录

    三、GoldenGate配置

    1、配置mgr进程(源端+目标端)

    2、添加表级trandata(源端)

    3、配置extract抽取进程(源端)

    4、配置pump传输进程(源端)

    四、RMAN初始化数据

    1、源端启动extract进程

    2、查询源端是否有长事务运行

    3、RMAN备份源端数据库

    4、目标端RMAN恢复

    备份文件拷贝至目标端

    目标端将数据库启动到nomount状态

    目标端恢复控制文件

    目标端启动到mount

    目标端rman恢复

    查看目标端的SCN号,记录该SCN号(得到该SCN号之后,启动复制进程时,使?该SCN号)

    以resetlogs方式开启目标端数据库(目标端)

    5、添加GLOBALS参数文件,创建检查点表(目标端)

    6、配置replicate复制进程(目标端)

    7、用SCN启动Replicat(目标端)

    8、检查是否同步

    DML测试

    五、开启DDL

    1、添加参数(源端)

    2、在源端执?与DDL同步相关的SQL脚本

    3、源端extract 配置

    4、重启extract进程

    5、目标端replicat配置

    6、重启replicat进程

    7、DDL测试

    查看scott下的表(源端+目标端)

    源端创建一张表

    查看目标端已经同步


    一、环境介绍

    项目源库目标库
    操作系统RHEL6.8RHEL6.8
    主机名s11gt11g
    IP地址192.168.56.20192.168.56.21
    数据库及版本ORACLE11.2.0.4ORACLE11.2.0.4
    数据库字符集AL32UTF8AL32UTF8
    ORACLE SIDorclorcl
    Goldengate用户oggogg
    Goldengate版本112103(Linux x86-64)112103(Linux x86-64)

    二、GoldenGate安装(源端+目标端)

    1、创建OGG安装目录

    mkdir -p /ogg
    chown -R oracle:oinstall /ogg
    chmod 775 /ogg

    2、修改OGG操作系统用户环境变量

    cat<<EOF >> /home/oracle/.bash_profile
    ################ogg profile add#########################
    export OGG_HOME=/ogg
    export PATH=\$ORACLE_HOME/bin:\$PATH:\$OGG_HOME
    export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:\$OGG_HOME:/lib:/usr/lib
    alias ggsci='rlwrap ggsci'
    EOF

    4、上传OGG安装介质并解压

    chown -R oracle:oinstall /soft/
    su - oracle -c "tar -xvf /soft/fbo_ggs_Linux_x64_ora11g_64bit.tar -C /ogg"

    5、建立OGG表空间及用户,给用户授权

    create tablespace ogg_data datafile '/oradata/orcl/ogg01.dbf' size 1024M;
    create user ogg identified by ogg default tablespace ogg_data;
    grant connect,resource,dba,create table,create sequence to ogg;

    6、配置TNS

    --源端
    cat <<EOF >> /etc/hosts
    192.168.56.21 t11g
    EOF
    
    --目标端
    cat <<EOF >> /etc/hosts
    192.168.56.20 s11g
    EOF
    
    --源端和目标端
    cat >> $ORACLE_HOME/network/admin/tnsnames.ora <<EOF
    SORCL =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = s11g)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
        )
      )
    
    TORCL =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = t11g)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = orcl)
        )
      )
    EOF

    7、配置数据库参数

    附加日志

    alter database add SUPPLEMENTAL log data;
    

    强制日志

    alter database force logging;
    

    检查是否开启

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

    开启归档日志模式

    mkdir /archivelog
    chown -R oracle:oinstall /archivelog/
    
    su - oracle
    sqlplus / as sysdba
    alter system set log_archive_dest_1='location=/archivelog';
    shutdown immediate
    startup mount
    alter database archivelog;
    alter database open;
    archive log list;
    alter system switch logfile;

    关闭回收站

    alter system set recyclebin=off scope=spfile;

    8、GGSCI初始化目录

    cd /ogg
    ggsci
    
    GGSCI (s11g) 1> create subdirs

    三、GoldenGate配置

    1、配置mgr进程(源端+目标端)

    GGSCI (s11g) 1> edit params mgr
    
    GGSCI (s11g) 2> view params mgr
    port 7809
    
    GGSCI (s11g) 3> start mgr
    Manager started.
    
    GGSCI (s11g) 4> info all
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    MANAGER     RUNNING 
    
    GGSCI (s11g) 6> sh netstat -ntpl|grep 7809
    (Not all processes could be identified, non-owned process info
     will not be shown, you would have to be root to see it all.)
    tcp        0      0 :::7809                     :::*                        LISTEN      5760/./mgr          
    
    GGSCI (s11g) 7> sh ps -ef|grep mgr
    
    root        14     2  0 09:59 ?        00:00:00 [async/mgr]
    postfix   1683  1676  0 09:59 ?        00:00:00 qmgr -l -t fifo -u
    oracle    5760  5752  0 11:52 ?        00:00:00 ./mgr PARAMFILE /ogg/dirprm/mgr.prm REPORTFILE /ogg/dirrpt/MGR.rpt PROCESSID MGR PORT 7809
    oracle    5852  5752  0 12:15 pts/0    00:00:00 sh -c ps -ef|grep mgr
    oracle    5854  5852  0 12:15 pts/0    00:00:00 grep mgr

    2、添加表级trandata(源端)

    GGSCI (s11g) 1> dblogin userid ogg,password ogg
    Successfully logged into database.
    
    GGSCI (s11g) 4> info trandata scott.emp
    Logging of supplemental redo log data is disabled for table SCOTT.EMP.
    
    GGSCI (s11g) 5> add trandata scott.emp
    Logging of supplemental redo data enabled for table SCOTT.EMP.
    

    3、配置extract抽取进程(源端)

    GGSCI (s11g) 6> dblogin userid ogg,password ogg
    Successfully logged into database.
    
    GGSCI (s11g) 7> add extract ext1,tranlog,begin now,threads 1
    EXTRACT added.
    
    GGSCI (s11g) 8> add exttrail ./dirdat/et,extract ext1
    EXTTRAIL added.
    
    GGSCI (s11g) 9> edit params ext1
    
    GGSCI (s11g) 10> view params ext1
    EXTRACT ext1
    SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") 
    --SETENV (ORACLE_SID = "orcl")
    SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
    USERID ogg, PASSWORD ogg
    THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
    EXTTRAIL ./dirdat/et
    DYNAMICRESOLUTION
    --DDL INCLUDE ALL
    --TABLE scott.emp;
    TABLE scott.*;

    4、配置pump传输进程(源端)

    GGSCI (s11g) 12> add extract pump1,exttrailsource ./dirdat/et,begin now
    EXTRACT added.
    
    GGSCI (s11g) 13> add rmttrail ./dirdat/et,extract pump1
    RMTTRAIL added.
    
    GGSCI (s11g) 14> edit params pump1
    
    GGSCI (s11g) 15> view params pump1
    EXTRACT pump1
    RMTHOST 192.168.56.21, MGRPORT 7809
    RMTTRAIL ./dirdat/et
    PASSTHRU
    DYNAMICRESOLUTION
    --TABLE scott.emp;
    TABLE scott.*;
    
    GGSCI (s11g) 16> info all
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    MANAGER     RUNNING                                           
    EXTRACT     STOPPED     EXT1        00:00:00      00:06:34    
    EXTRACT     STOPPED     PUMP1       00:00:00      00:01:43 

    四、RMAN初始化数据

    1、源端启动extract进程

    GGSCI (s11g) 4> start ext1
    Sending START request to MANAGER ...
    EXTRACT EXT1 starting
    
    GGSCI (s11g) 5> start pump1
    Sending START request to MANAGER ...
    EXTRACT PUMP1 starting
    
    GGSCI (s11g) 6> info all
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    MANAGER     RUNNING                                           
    EXTRACT     RUNNING     EXT1        00:12:12      00:00:10    
    EXTRACT     RUNNING     PUMP1       00:00:00      00:14:56 

    2、查询源端是否有长事务运行

    select min(start_time) from gv$transaction;

    3、RMAN备份源端数据库

    注意:备份过程保证抽取进程状态?直正常。

    --源端和目标端都创建目录
    mkdir /backup
    chown -R oracle:oinstall /backup
    
    --源端备份全库
    su - oracle
    rman target /
    
    run {
    allocate channel ch00 type disk maxpiecesize 10g;
    allocate channel ch01 type disk maxpiecesize 10g;
    sql 'alter system switch logfile';
    sql 'alter system switch logfile';
    sql 'alter system switch logfile';
    sql 'alter system switch logfile';
    crosscheck backupset;
    delete noprompt expired backupset;
    backup database format '/backup/bk_%s_%p_%t';
    sql 'alter system archive log current';
    BACKUP ARCHIVELOG ALL FORMAT '/backup/ARCH_%U';
    BACKUP CURRENT CONTROLFILE FORMAT '/backup/bk_controlfile';
    release channel ch00;
    release channel ch01;
    }

    4、目标端RMAN恢复

    备份文件拷贝至目标端

    scp * 192.168.56.21:/backup

    目标端将数据库启动到nomount状态

    sqlplus / as sysdba
    shutdown immediate
    startup nomount

    目标端恢复控制文件

    su - oracle
    rman target /
    restore controlfile from '/backup/bk_controlfile';

    目标端启动到mount

    alter database mount;

    目标端rman恢复

    restore database;
    
    --recover之前需要将redo log都mv或者rm掉,再进行recover
    recover database;
    
    
    否则会报错:
    RMAN-03002: failure of recover command at 03/24/2021 13:07:31
    ORA-19698: /oradata/orcl/redo01.log is from different database: id=1595737004, db_name=ORCL

    查看目标端的SCN号,记录该SCN号(得到该SCN号之后,启动复制进程时,使?该SCN号)

    SELECT CHECKPOINT_CHANGE#,CHECKPOINT_TIME FROM V$DATAFILE_HEADER;
    1042454

    以resetlogs方式开启目标端数据库(目标端)

    alter database open resetlogs;

    5、添加GLOBALS参数文件,创建检查点表(目标端)

    GGSCI (t11g) 10> edit params ./GLOBALS
    
    GGSCI (t11g) 11> view params ./GLOBALS
    GGSCHEMA ogg
    checkpointtable ogg.checkpoint
    
    GGSCI (t11g) 12> dblogin userid ogg,password ogg
    Successfully logged into database.
    
    GGSCI (t11g) 13> add checkpointtable ogg.checkpoint
    Successfully created checkpoint table ogg.checkpoint.
    
    

    6、配置replicate复制进程(目标端)

    GGSCI (t11g) 14> add replicat rep1, exttrail ./dirdat/et, checkpointtable ogg.checkpoint
    REPLICAT added.
    
    GGSCI (t11g) 15> edit params rep1
    
    GGSCI (t11g) 16> view params rep1
    REPLICAT rep1
    setenv (ORACLE_SID=orcl)
    SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db")
    USERID ogg,PASSWORD ogg
    ASSUMETARGETDEFS
    HANDLECOLLISIONS
    REPERROR (DEFAULT, DISCARD)
    DDLERROR DEFAULT DISCARD
    DDLOPTIONS REPORT
    DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100
    MAP scott.*, TARGET scott.*;
    

    7、用SCN启动Replicat(目标端)

    GGSCI (t11g) 17> start rep1,aftercsn 1042454
    Sending START request to MANAGER ...
    REPLICAT REP1 starting
    
    GGSCI (t11g) 1> info all
    
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    
    MANAGER     RUNNING                                           
    REPLICAT    STOPPED     REP1        00:00:00      00:07:29    
    
    --这边启动报错:
    2021-03-24 13:30:22  ERROR   OGG-00664  OCI Error creating temporary LOB to retrieve default LOB chunk size (status = 1187-ORA-01187: cannot read from fi
    le  because it failed verification tests
    ORA-01110: data file 201: '/oradata/orcl/temp01.dbf').
    
    2021-03-24 13:30:22  ERROR   OGG-01668  PROCESS ABENDING.
    
    --解决方案:
    目标端temp临时表空间drop重建
    alter database tempfile '/oradata/orcl/temp01.dbf' drop;
    alter tablespace temp add tempfile '/oradata/orcl/temp01.dbf' size 19M  reuse;
    
    --重新启动replicat进程
    GGSCI (t11g) 18> info all
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    MANAGER     RUNNING                                           
    REPLICAT    RUNNING     REP1        00:00:00      00:02:13  

    8、检查是否同步

    DML测试

    查看两端scott.emp数据,数据一致

    SYS@orcl>set line222
    SYS@orcl>set pagesize100
    SYS@orcl>select * from scott.emp;
    
         EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
          7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
          7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
          7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
          7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
          7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
          7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
          7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20
          7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
          7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
          7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20
          7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30
          7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
          7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10
    
    14 rows selected.

    源端插入一条数据

    insert into emp(empno,ename,job) values (9874,'LUCIFER','DBA');
    commit;

    查询目标端发现已经同步

    SYS@orcl>select * from scott.emp;
    
         EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
    ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
          9874 LUCIFER    DBA
          7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20
          7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30
          7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30
          7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20
          7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30
          7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30
          7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10
          7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20
          7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10
          7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30
          7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20
          7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30
          7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20
          7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10
    
    15 rows selected.

    五、开启DDL

    1、添加参数(源端)

    GGSCI (s11g) 2> edit params ./GLOBALS
    
    GGSCI (s11g) 3> view params ./GLOBALS
    GGSCHEMA ogg
    

    2、在源端执?与DDL同步相关的SQL脚本

    su - oracle
    --切记@marker_setup.sql ?定要在cd $OGG_HOME?录下执?否则会卡主。执?脚本时要输?ogg管理?户,本实例是ogg。
    cd /ogg
    sqlplus / as sysdba
    grant execute on utl_file to ogg;
    @marker_setup.sql
    @ddl_setup.sql
    @role_setup.sql
    grant GGS_GGSUSER_ROLE to ogg;
    @ddl_enable.sql
    @ddl_pin ogg
    @marker_status

    3、源端extract 配置

    GGSCI (s11g) 10> edit params ext1
    
    GGSCI (s11g) 11> view params ext1
    EXTRACT ext1
    SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") 
    --SETENV (ORACLE_SID = "orcl")
    SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
    USERID ogg, PASSWORD ogg
    THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
    EXTTRAIL ./dirdat/et
    DYNAMICRESOLUTION
    --增加如下ddl参数
    DDL INCLUDE ALL
    TABLE scott.emp;

    4、重启extract进程

    GGSCI (s11g) 13> stop ext1
    Sending STOP request to EXTRACT EXT1 ...
    Request processed.
    
    GGSCI (s11g) 14> start ext1
    Sending START request to MANAGER ...
    EXTRACT EXT1 starting
    
    GGSCI (s11g) 15> info all
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    MANAGER     RUNNING                                           
    EXTRACT     RUNNING     EXT1        00:00:00      00:00:00    
    EXTRACT     RUNNING     PUMP1       00:00:00      00:00:01 

    5、目标端replicat配置

    GGSCI (t11g) 3> edit params rep1
    
    GGSCI (t11g) 4> view params rep1
    REPLICAT rep1
    setenv (ORACLE_SID=orcl)
    SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db")
    USERID ogg,PASSWORD ogg
    ASSUMETARGETDEFS
    HANDLECOLLISIONS
    REPERROR (DEFAULT, DISCARD)
    DDLERROR DEFAULT DISCARD
    DDLOPTIONS REPORT
    DISCARDFILE ./dirrpt/repr1.dsc,append,megabytes 100
    MAP scott.*, TARGET scott.*;
    --添加如下ddl参数
    ddl include all
    ddlerror default ignore retryop maxretries 3 retrydelay 5
    
    

    6、重启replicat进程

    GGSCI (t11g) 5> stop rep1
    Sending STOP request to REPLICAT REP1 ...
    Request processed.
    
    GGSCI (t11g) 6> start rep1
    Sending START request to MANAGER ...
    REPLICAT REP1 starting
    
    GGSCI (t11g) 7> info all
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    MANAGER     RUNNING                                           
    REPLICAT    RUNNING     REP1        00:00:00      00:00:06    
    
    

    7、DDL测试

    查看scott下的表(源端+目标端)

    SCOTT@orcl>select table_name from user_tables;
    
    TABLE_NAME
    ------------------------------
    SALGRADE
    BONUS
    EMP
    DEPT
    
    4 rows selected.

    源端创建一张表

    SCOTT@orcl>create table lucifer(id number not null,name varchar2(20));
    
    Table created.
    
    SCOTT@orcl>insert into lucifer(id,name) values (1,'lucifer');
    
    1 row created.
    
    SCOTT@orcl>insert into lucifer(id,name) values (2,'lpc');
    
    1 row created.
    
    SCOTT@orcl>insert into lucifer(id,name) values (3,'hsx');
    
    1 row created.
    
    SCOTT@orcl>commit;
    
    Commit complete.
    
    SCOTT@orcl>select table_name from user_tables;
    
    TABLE_NAME
    ------------------------------
    LUCIFER
    SALGRADE
    BONUS
    EMP
    DEPT
    
    5 rows selected.
    
    SCOTT@orcl>select * from lucifer;
    
    	ID NAME
    ---------- --------------------
    	 1 lucifer
    	 2 lpc
    	 3 hsx
    
    3 rows selected.
    
    

    查看目标端已经同步

    SYS@orcl>conn scott/scott
    Connected.
    SCOTT@orcl>show user
    USER is "SCOTT"
    SCOTT@orcl>select table_name from user_tables;
    
    TABLE_NAME
    ------------------------------
    LUCIFER
    SALGRADE
    BONUS
    EMP
    DEPT
    
    SCOTT@orcl>select * from lucifer;
    
    	ID NAME
    ---------- --------------------
    	 1 lucifer
    	 2 lpc
    	 3 hsx
    

    ?