OGG常见初始化方案

数据初始化

RMAN初始化方案

本方案在初始化过程中,不需要源数据库停机。

步骤如下:

生产端===============================================

1)        源端和目标端安装OGG软件,并启动manager

2)        源端配置OGG的Extract及DataPump

3)      源端启动Extract进程,并且人工记录抽取进程启动的时间点,例如:“2011-05-03 11:20:55” ,将此时间点作为在下一步中查找长事务的一个判断条件;可以在数据库中查询该时间点:”select to_char(sysdate,’yyyy-mm-dd hh24:mi:ss’) from dual”

4)        长事务处理:在V$TRANSACTION中查找当前正在运行的事务(RAC环境下,查看gv$transaction),并找出相应的长事务。在执行rman备份之前,确保这些长事务已经完成,或者被kill掉(需要得到dba或者相关管理人员的确认)。

Select  start_time  from gv$transaction where to_date(start_time, ‘yyyy-mm-dd hh24:mi:ss’)<to_date(‘2011-05-03 11:20:55’, ‘yyyy-mm-dd hh24:mi:ss’);

注:通过上面的SQL语句查找到比第3歩中记录下的时间点早的事务,需要等到该事务结束,然后执行rman的备份;

 

 

5)        使用oracle用户登录,执行rman备份

–备份数据库:

$rman target /

run {

allocate channel ch00 type disk maxpiecesize 20g;

allocate channel ch01 type disk maxpiecesize 20g;

crosscheck backupset;

delete noprompt expired backupset;

sql ‘alter system archive log current’;

backup as backupset skip inaccessible tag hot_db_bk_level0

full database

format ‘/u01/backup/bk_%s_%p_%t’;

release channel ch00;

release channel ch01;

}

–备份归档和控制文件:(如果有三个日志文件组,可以按照以下方式进行切换,如果更多,则对应增加切换的次数即可);

run {

ALLOCATE CHANNEL ch00 TYPE DISK MAXPIECESIZE 20G;

ALLOCATE CHANNEL ch01 TYPE DISK MAXPIECESIZE 20G;

sql ‘alter system switch logfile’;

sql ‘alter system switch logfile’;

sql ‘alter system switch logfile’;

sql ‘alter system archive log current’;

BACKUP ARCHIVELOG ALL FORMAT ‘/u01/backup/ARCH_%U’;

BACKUP CURRENT CONTROLFILE FORMAT ‘/u01/backup/bk_controlfile’;

RELEASE CHANNEL ch00;

RELEASE CHANNEL ch01;

}

在源机执行

SQL>set linesize 200

SQL>select * from Gv$log;

GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM

———- ———- ———- ———- ———- — —————- ————- ———

         1          1         23  104857600          1 NO  CURRENT                 218512 01-DEC-10

         2          1         22  104857600          1 YES ACTIVE                  218422 01-DEC-10

         3          2         17  104857600          1 NO  CURRENT                 218503 01-DEC-10

         4          2         16  104857600          1 YES INACTIVE                218412 01-DEC-10 (*)

 

获取不活动的已归档日志的最后一个SCN号,如果有多个已归档的INACTIVE的组,取最大的FIRST_CHANGE#,这里取218412

记录该SCN号(在“第10歩”目标端恢复数据库时使用),将备份文件,FTP到目标机

========================================

hpux2:/u01/oracle$ ftp 192.168.1.27

Connected to 192.168.1.27.

220 hpux_dr FTP server (Revision 4.0 Version wuftpd-2.6.1 Wed Jun 18 07:11:14 GMT 2008) ready.

Name (192.168.1.27:oracle): oracle

331 Password required for oracle.

Password:

230 User oracle logged in.

Remote system type is UNIX.

Using binary mode to transfer files.

ftp> cd /u01/oracle/rmanbk

250 CWD command successful.

ftp> lcd /u01/oracle/rmanbk

Local directory now /u01/oracle/rmanbk

ftp> bin

200 Type set to I.

ftp> prompt

Interactive mode off.

ftp> mput hp*

 

容灾端===============================================

6)        使用oracle用户登录目标机执行恢复

ü  在目标机建立相同的目录结构,并正确设置:ORACLE_BASE,ORACLE_HOME,ORACLE_SID环境变量。

ü  拷贝源机init{ORACLE_SID}.ora文件到目标机

ü  cp $ORACLE_HOME/dbs/initorcl.ora  目标机对应目录

ü  生成口令文件或者copy $ORACLE_HOME/dbs/PWD{ORACLE_SID}

 

7)        启动数据库到nomount状态

sqlplus ‘/ as sysdba’

SQL>startup nomount

 

8)        启动RMAN恢复控制文件

rman target /

RMAN>restore controlfile from ‘/u01/oracle/rmanbk/hpdb_ctlfile_757366280381.dbf’;

 

9)        将数据库更改为mount状态

sqlplus ‘/ as sysdba’

SQL>alter database mount;

 

10)     启动RMAN恢复数据库

rman target /

RMAN>restore database;

 

RMAN>

run

{

   set archivelog destination to ‘/u01/arch’;

   SET UNTIL SCN 218412;     –在第5歩源端记录下的scn号

   RECOVER DATABASE;

}

 

11)     使用以下SQL语句查找目标端数据库的SCN号:(得到该SCN号之后,在2.5.8章节中启动复制进程时,使用该SCN号。)

SQL> SELECT CHECKPOINT_CHANGE#,CHECKPOINT_TIME FROM V$DATAFILE_HEADER;

 

CHECKPOINT_CHANGE# CHECKPOIN

—————— ———

            218412 28-MAY-11

            218412 28-MAY-11

            218412 28-MAY-11

            218412 28-MAY-11

            218412 28-MAY-11

注:

a)  如果目标端数据库在“第10歩”操作之后,又额外追加了源数据库rman备份后的归档日志,导致目标端数据库的SCN号大于“第10歩”中SCN号。所以必须以目标端数据库当前的SCN为主,从而避免数据重复。

b)  如果目标端数据库在“第10歩”操作之后,没有额外追加源数据库rman备份后的归档日志,则“第11歩”中得到的SCN号应该与“第10歩”中的SCN号相等;

12)     (备选)如果源端为RAC,目标端为单机,需将目标机改为单机模式

删除多余的redolog group

SQL> select THREAD#, STATUS, ENABLED from v$thread;

 THREAD# STATUS ENABLED

———- —— ——–

1 OPEN   PUBLIC

2 CLOSED PRIVATE

 

SQL> select group# from v$log where THREAD#=2;

GROUP#

———-

4

5

6

 

SQL> alter database disable thread 2;

Database altered.

SQL> alter database drop logfile group 4;

alter database drop logfile group 4

ERROR at line 1:

ORA-00350: log 4 of instance racdb2 (thread 2) needs to be archived

ORA-00312: online log 4 thread 2: ‘/u01/oracle/oradata/ractest/log/redo04.log’

SQL> alter database clear unarchived logfile group 4;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

 

SQL> alter database drop logfile group 6;

 

Database altered.

SQL> select THREAD#, STATUS, ENABLED from v$thread;

 

THREAD# STATUS ENABLED

———- —— ——–

1 OPEN   PUBLIC

 

 

删除多余UNDO表空间

SQL> show parameter undo;

NAME                                 TYPE        VALUE

———————————— ———– ——————————

undo_management                   string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

 

SQL> select tablespace_name from dba_tablespaces where contents=’UNDO’;

TABLESPACE_NAME

——————————

UNDOTBS1

UNDOTBS2

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.

 

如果源数据库为Oracle9i,因为RMAN时不备份TEMP表空间,需重新创建TEMP表空间

SQL> create temporary tablespace TEMP  tempfile ‘/u01/temp.dbf’ size 500M;

Tablespace created.

SQL> alter database default temporary tablespace TEMP;

Database altered.

13)     目标端打开数据库

alter database open resetlogs;

注意:由于此时源端和目标端一样,所以要先删除DDL那几个脚本,删除replicat进程再 重新添加,最后才能 start replicat repea,aftercsn <>

Oracle EXPDP/IMPDP初始化方案

在数据初始化的过程中,不需要源数据库停机。

1)        源端和目标端安装OGG软件,并启动manager

2)        源端配置OGG的Extract及DataPump

3)        源端启动Extract进程以及DataPump进程,并且人工记录抽取进程启动的时间点,例如:“2011-05-03 11:20:55” ,将此时间点作为在下一步中查找长事务的一个判断条件;

4)        长事务处理:在V$TRANSACTION中查找当前正在运行的事务(RAC环境下,查看gv$transaction),并找出相应的长事务。在执行rman备份之前,确保这些长事务已经完成,或者被kill掉(需要得到dba或者相关管理人员的确认)。

Select  start_time  from gv$transaction where to_date(start_time, ‘yyyy-mm-dd hh24:mi:ss’)<to_date(‘2011-05-03 11:20:55’, ‘yyyy-mm-dd hh24:mi:ss’);

注:通过上面的SQL语句查找到比第3歩中记录下的时间点早的事务,需要等到该事务结束,然后执行rman的备份;

5)        创建directory用于执行数据泵操作

–directory path for store dump file

CREATE OR REPLACE DIRECTORY DATA_PUMP AS ‘E:\OGG\data’;

grant read ,write on DIRECTORY DATA_PUMP to ggs ;

–users to execute expdp

grant read ,write on DIRECTORY DATA_PUMP to maclean ;

6)        源端获取数据库当前的SCN

SQL> select dbms_flashback.get_system_change_number from dual;

 

GET_SYSTEM_CHANGE_NUMBER

————————

                 3079975

7)        源端数据导出

在源端OS系统中执行数据导出,导出用户名、dmp文件名自行修改,导出完成后ftp至目标端datapump目录

expdp maclean/maclean directory=DATA_PUMP dumpfile=<dp_maclean.dmp> flashback_scn=<scn>

 

–以下操作在目标端执行

8)        创建directory用于执行数据泵操作,同时给ggs以及导入用户赋权

–directory path for store dump file

CREATE OR REPLACE DIRECTORY DATA_PUMP AS ‘/goldengate/data’;

grant read ,write on DIRECTORY DATA_PUMP to ggs ;

grant read ,write on DIRECTORY DATA_PUMP to maclean;

9)        目标端导入数据

— 以下操作在OS中执行,具体用户名与导入dmp文件名视具体情况而定

impdp maclean/maclean DUMPFILE=DATA_PUMP:DP_maclean.DMP

 

 

Goldengate实现在线数据迁移

Goldengate的一大卖点就是可以实现在线的数据迁移,这意味着在整个过程中我们无需关闭数据库,甚至于无需停止应用。在<How to create a GoldenGate uni-directional target database in a production database zero downtime>中,大致介绍了实现零下线时间数据迁移所涉及的步骤,包括:

  1. 在source与target主机上完成OGG的初始化配置
  2. 创建extract及exttrail
  3. 创建pump
  4. 启动本地extract和pump
  5. 创建带有HANDLECOLLISIONS参数的replicat,但不启动它
  6. 完成数据的initial load即数据初始化
  7. 启动replicat
  8. 当replicat追上extract后取消replicat的HANDLECOLLISIONS参数

接下来我们通过以下实例来实践OGG的在线数据迁移:

SQL> conn clinic/clinic
Connected.

/* 以clinic为应用用户 */

SQL> create table tv (t1 int primary key,t2 int,t3 varchar2(30));
Table created.

/* 为别在source和target创建该测试用表,在这里我们先不考虑同步DDL*/

SQL> create sequence seqt1 start with 1 increment by 1;
Sequence created.

declare
  rnd number(9,2);
begin
   for i in 1..100000 loop
     insert into tv values(seqt1.nextval,i*dbms_random.value,'MACLEAN IS TESTING');
     commit;
   end loop;
end;
PL/SQL procedure successfully completed.

/* 在源端随机生成10w行数据 */

begin
   loop
    delete from tv where rownum=1;
     commit;
     insert into tv values(seqt1.nextval,200000*dbms_random.value,'MACLEAN IS UPDATING');
     commit;
	 insert into tv values(seqt1.nextval,300000*dbms_random.value,'MACLEAN IS UPDATING');
	 commit;
	update tv set t2=t2+10 where rownum=1;
	commit;
     dbms_lock.sleep(1);
     end loop;
end;

/* 以上匿名块可以造成简易的数据变化,它会在每1秒钟删除/更新/插入TV表的部分数据
    我们打开多个窗口执行以上匿名块,以模拟在线业务
*/

以上为测试前提工作,接下来我们正式配置OGG FOR ZERO DOWNTIME MIGRATION:

GGSCI (rh2.oracle.com) 3> dblogin userid maclean
Password:
Successfully logged into database.

GGSCI (rh2.oracle.com) 5> add trandata clinic.tv
Logging of supplemental redo data enabled for table CLINIC.TV.

/* 通过add trandata命令为tv表加上追加日志 */

GGSCI (rh2.oracle.com) 6> add extract load2,tranlog,begin now
EXTRACT added.

GGSCI (rh2.oracle.com) 7> add rmttrail /s01/new/ze,megabytes 200,extract load2
RMTTRAIL added.

GGSCI (rh2.oracle.com) 10> view params load2

extract load2
userid maclean, password AACAAAAAAAAAAAHANCUEFHPAVCYDNJVD,encryptkey default
RMTHOST rh3.oracle.com, MGRPORT 7809
RMTTRAIL /s01/new/ze
Table clinic.tv;

/* 为了让整个过程更简洁,我们在这里不适用pump */

GGSCI (rh2.oracle.com) 9> start load2
Sending START request to MANAGER ...
EXTRACT LOAD2 starting

GGGSCI (rh2.oracle.com) 11> stats load2
Sending STATS request to EXTRACT LOAD2 ...
Start of Statistics at 2010-12-11 20:00:44.
Output to /s01/new/ze:
Extracting from CLINIC.TV to CLINIC.TV:
*** Total statistics since 2010-12-11 20:00:32 ***
        Total inserts                              370.00
        Total updates                              185.00
        Total deletes                              185.00
        Total discards                               0.00
        Total operations                           740.00
*** Daily statistics since 2010-12-11 20:00:32 ***
        Total inserts                              370.00
        Total updates                              185.00
        Total deletes                              185.00
        Total discards                               0.00
        Total operations                           740.00
*** Hourly statistics since 2010-12-11 20:00:32 ***
        Total inserts                              370.00
        Total updates                              185.00
        Total deletes                              185.00
        Total discards                               0.00
        Total operations                           740.00
*** Latest statistics since 2010-12-11 20:00:32 ***
        Total inserts                              370.00
        Total updates                              185.00
        Total deletes                              185.00
        Total discards                               0.00
        Total operations                           740.00
End of Statistics.

/* 以上完成了extract的配置和启动,接下来我们配置target上的replicat */

GGSCI (rh3.oracle.com) 39> add replicat rep2,begin now,exttrail /s01/new/ze,checkpointtable maclean.checkpoint
REPLICAT added.

GGSCI (rh3.oracle.com) 42> view params rep2
replicat rep2
userid maclean,password maclean
ASSUMETARGETDEFS
discardfile /s01/discard/clinic.log,append,megabytes 10
HANDLECOLLISIONS
map clinic.tv, target clinic.tv;

/* 注意为replicat加上HANDLECOLLISIONS参数,
    用以处理可能的deplicate records和missed records;
    该参数的具体定义见Reference文档
*/ 

/* 注意在配置完replicat后,不要去启动它;直到完成initial load才能启动*/

/* 接下来要配置initial load使用的extract和replicat */

GGSCI (rh2.oracle.com) 14> add extract einit,sourceistable
EXTRACT added.

/* 以sourceistable选项创建extract */

GGSCI (rh2.oracle.com) 17> view params einit

extract einit
SOURCEISTABLE
userid maclean, password maclean
RMTHOST rh3.oracle.com, MGRPORT 7809
RMTFILE /s01/new/clinictv
Table clinic.tv;

/* 之后我们需要在命令行界面下调用该extract */
[maclean@rh2 gg]$ pwd
/home/maclean/gg

[maclean@rh2 gg]$ extract paramfile dirprm/einit.prm
2010-12-11 20:03:06  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
***********************************************************************
                 Oracle GoldenGate Capture for Oracle
                     Version 11.1.1.0.0 Build 078
   Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 15:39:00
 Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
                    Starting at 2010-12-11 20:03:06
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Mon Mar 29 22:10:29 EDT 2010, Release 2.6.18-194.el5
Node: rh2.oracle.com
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited
Process id: 21399
Description:
***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
extract einit
SOURCEISTABLE

2010-12-11 20:03:06  INFO    OGG-01017  Wildcard resolution set to IMMEDIATE because SOURCEISTABLE is used.
userid maclean, password *******
RMTHOST rh3.oracle.com, MGRPORT 7809
RMTFILE /s01/new/clinictv
Table clinic.tv;
Using the following key columns for source table CLINIC.TV: T1.
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE:                         64K
CACHESIZE:                                8G
CACHEBUFFERSIZE (soft max):               4M
CACHEPAGEOUTSIZE (normal):                4M
PROCESS VM AVAIL FROM OS (min):          16G
CACHESIZEMAX (strict force to disk):  13.99G

Database Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Database Language and Character Set:
NLS_LANG environment variable specified has invalid format, default value will be used.
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "WE8ISO8859P1"
Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.
2010-12-11 20:03:06  INFO    OGG-01478  Output file /s01/new/clinictv is using format RELEASE 10.4/11.1.
2010-12-11 20:03:11  INFO    OGG-01226  Socket buffer size set to 27985 (flush size 27985).
Processing table CLINIC.TV
***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************
Report at 2010-12-11 20:03:12 (activity since 2010-12-11 20:03:06)
Output to /s01/new/clinictv:
From Table CLINIC.TV:
       #                   inserts:    100780
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0

/* 以上extract操作在target上RMTFILE指定的路径生成了导出文件*/

[maclean@rh3 new]$ pwd
/s01/new

[maclean@rh3 new]$ file clinictv
clinictv: data

[maclean@rh3 new]$ strings clinictv |head -20
uri:rh2:oracle:com:6
/s01/new/clinictv7
Linux1
rh2.oracle.com2
2.6.18-194.el53
##1 SMP Mon Mar 29 22:10:29 EDT 20104
x86_642
CLINICA2
clinica3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
EINIT1
Version 11.1.1.0.0 Build 0784
CLINIC.TV
MACLEAN IS TESTINGZ
CLINIC.TV
MACLEAN IS TESTINGZ

/* 接下来我们要在target上配置initial load使用的replicat,以导入之前的EXTFILE */

GGSCI (rh3.oracle.com) 12> add replicat rinit,specialrun
REPLICAT added.

GGSCI (rh3.oracle.com) 17> view params rinit

replicat rinit
SPECIALRUN
userid maclean,password maclean
EXTFILE /s01/new/clinictv
ASSUMETARGETDEFS
map clinic.tv, target clinic.tv;

/ *之后我们在命令行上调用该replicat */

[maclean@rh3 gg]$ replicat paramfile dirprm/rinit.prm 

***********************************************************************
                 Oracle GoldenGate Delivery for Oracle
                     Version 11.1.1.0.0 Build 078
   Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 15:58:11

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2010-12-11 20:06:14
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Mon Mar 29 22:22:00 EDT 2010, Release 2.6.18-194.el5xen
Node: rh3.oracle.com
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 18554

Description: 

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
replicat rinit
SPECIALRUN
userid maclean,password *******
EXTFILE /s01/new/clinictv
ASSUMETARGETDEFS
map clinic.tv, target clinic.tv;

CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE:                         64K
CACHESIZE:                              512M
CACHEBUFFERSIZE (soft max):               4M
CACHEPAGEOUTSIZE (normal):                4M
PROCESS VM AVAIL FROM OS (min):           1G
CACHESIZEMAX (strict force to disk):    881M

Database Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

Database Language and Character Set:
NLS_LANG environment variable specified has invalid format, default value will be used.
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
NLS_LANGUAGE     = "AMERICAN"
NLS_TERRITORY    = "AMERICA"
NLS_CHARACTERSET = "WE8ISO8859P1" 

Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.

***********************************************************************
**                     Run Time Messages                             **
***********************************************************************

Opened trail file /s01/new/clinictv at 2010-12-11 20:06:14

MAP resolved (entry CLINIC.TV):
  map CLINIC.TV, target clinic.tv;
Using following columns in default map by name:
  T1, T2, T3

Using the following key columns for target table CLINIC.TV: T1.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************

Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name :  /s01/new/clinictv
Hdr-Ind    :     E  (x45)     Partition  :     .  (x04)
UndoFlag   :     .  (x00)     BeforeAfter:     A  (x41)
RecLength  :    52 (x0034)    IO Time    : 2010-12-11 20:03:10.969955
IOType     :     5  (x05)     OrigNode   :   255  (xff)
TransInd   :     .  (x03)     FormatType :     R  (x52)
SyskeyLen  :     0  (x00)     Incomplete :     .  (x00)
AuditRBA   :          0       AuditPos   : 0
Continued  :     N  (x00)     RecCount   :     1  (x01)

2010-12-11 20:03:10.969955 Insert             Len    52 RBA 11244940
Name: CLINIC.TV
___________________________________________________________________

Reading /s01/new/clinictv, current RBA 11245052, 100780 records

Report at 2010-12-11 20:06:54 (activity since 2010-12-11 20:06:14)

From Table CLINIC.TV to CLINIC.TV:
       #                   inserts:    100780
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0

Last log location read:
     FILE:      /s01/new/clinictv
     RBA:       11245052
     TIMESTAMP: 2010-12-11 20:03:10.969955
     EOF:       NO
     READERR:   400

SQL> select count(*) from tv;
  COUNT(*)
----------
    100780

/* 以上完成了initial load所要求的工作,接着我们打开同步使用的replicat*/
GGSCI (rh3.oracle.com) 3> start rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting

GGSCI (rh3.oracle.com) 7> lag rep2
Sending GETLAG request to REPLICAT REP2 ...
Last record lag: 4 seconds.
At EOF, no more records to process.

GGSCI (rh3.oracle.com) 9> stats rep2

Sending STATS request to REPLICAT REP2 ...

Start of Statistics at 2010-12-11 20:10:15.

Replicating from CLINIC.TV to CLINIC.TV:

*** Total statistics since 2010-12-11 20:09:54 ***
        Total inserts                              204.00
        Total updates                              102.00
        Total deletes                              102.00
        Total discards                               0.00
        Total operations                           408.00

*** Daily statistics since 2010-12-11 20:09:54 ***
        Total inserts                              204.00
        Total updates                              102.00
        Total deletes                              102.00
        Total discards                               0.00
        Total operations                           408.00

*** Hourly statistics since 2010-12-11 20:09:54 ***
        Total inserts                              204.00
        Total updates                              102.00
        Total deletes                              102.00
        Total discards                               0.00
        Total operations                           408.00

*** Latest statistics since 2010-12-11 20:09:54 ***
        Total inserts                              204.00
        Total updates                              102.00
        Total deletes                              102.00
        Total discards                               0.00
        Total operations                           408.00
End of Statistics.

/* 当replicat追上extract后,需要取消HANDLECOLLISIONS参数 */

GGSCI (rh3.oracle.com) 11> view params rep2
replicat rep2
userid maclean,password maclean
ASSUMETARGETDEFS
discardfile /s01/discard/clinic.log,append,megabytes 10
map clinic.tv, target clinic.tv;

GGSCI (rh3.oracle.com) 12> stop rep2
Sending STOP request to REPLICAT REP2 ...
Request processed.

GGSCI (rh3.oracle.com) 13> start rep2
Sending START request to MANAGER ...
REPLICAT REP2 starting

接下来停止模拟的在线业务,进行数据对比。首先使用Goldengate veridata创建group及compare pair等配置:


接着生成job,并执行:

Job运行的结果界面:

对比发现同步正常的报告:

***********************************************************************
                  Oracle GoldenGate Veridata Server
                           Version 3.0.0.6
                              Build 002

Copyright (C) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

                   Starting at 2010-12-11 20:15:58

***********************************************************************
Unexpected Windows major version
Process id: 6340, Thread id : 6392
Group: clinic (1) Compare Pairs
Performing 1 comparisons ...
        Compare pair: TV=TV
2010-12-11 20:15:58. Submitted (1 of 1) Compare Pair (TV=TV) RunId (runid=(1007, 1, 1)).
2010-12-11 20:16:00. Completed in-sync. Compare Pair (TV=TV). (0) Remaining

               Compare Pairs with Errors: 0
          Compare Pairs with Out-Of-Sync: 0
       Compare Pairs without Out-Of-Sync: 1
                 Compare Pairs Cancelled: 0

Group terminated normally.

接着我们在target端执行一些人工修改数据的SQL,以达到不同步的效果:

begin
   loop
    delete from tv where rownum=1;
     commit;
     insert into tv values(seqt1.nextval,200000*dbms_random.value,'MACLEAN IS UPDATING');
     commit;
	 insert into tv values(seqt1.nextval,300000*dbms_random.value,'MACLEAN IS UPDATING');
	 commit;
	update tv set t2=t2+10 where rownum=1;
	commit;
     dbms_lock.sleep(1);
     end loop;
end;
/

/* 在源端打开模拟的应用程序 */

/* 在target插入一条额外的纪录 */

SQL> insert into tv values(99993242,1213,'OUT OF SYNC');
1 row created.

SQL> commit;
Commit complete.

再次运行Goldengate veridata中相关的job,可以在job相关的日志中看到详细的out-of-sync纪录信息:

Profile: newp

General:
***********************************************************************
     Out-Of-Sync Output Format: binary
          Maximum Size of Each
  Out-Of-Sync XML Chunk (Rows): 500
    Output in-sync rows to OOS
                          file: false
Output in-sync after in-flight
              rows to OOS file: true
 Report in-sync rows to report
                          file: false
Report in-sync after in-flight
           rows to report file: false
          Sorting Method:
               Sort Data Using: database
     Maximum Memory Usage (MB): 50
  Number Of Concurrent Threads: 4
   Temporary Storage Directory
               for Source Data:
   Temporary Storage Directory
               for Target Data:
      NSort Memory Retry Limit: 0
NSort Memory Retry Wait Interval: 0

Initial Compare (General):
***********************************************************************
     Max Concurrent Comparison
                       Threads: 4
Terminate when Maximum Records
                   Out-Of-Sync: 100000
     Output Out-Of-Sync Record
        Details to Report File: false
      Update Report file Every
                     (seconds): 0
      Update Report file Every
                       Threads: 0
Terminate when Maximum Records
                   Out-Of-Sync: 100000
     Output Out-Of-Sync Record
        Details to Report File: false
      Update Report file Every
                     (seconds): 0
      Update Report file Every
                     (records): 0
    Limit Number of Input Rows: 0
     Delta processsing enabled: false

Initial Compare (Event Reporting):
***********************************************************************
             Generate Messages: both
 Generate Warning Messages For
        Out-Of-Sync Rows After
                 (differences): 9999

Initial Compare (Agent):
***********************************************************************
 Use Static Listening Port For
     For Agent During Row Hash
                     On Source: 0
 Use Static Listening Port For
     For Agent During Row Hash
                     On Target: 0

Initial Compare (NonStop Process):
***********************************************************************
  Source Process Name Starting
                          With:
     Source Process CPU Number: -1
       Source Process Priority: 0
  Target Process Name Starting
                          With:
     Target Process CPU Number: -1
       Target Process Priority: 0

Confirm-Out-Of-Sync (General):
***********************************************************************
   Perform Confirm Out-Of-Sync
                          Step: true
         Run Concurrently With
               Initial Compare: true
     Delay Confirm-Out-Of-Sync
                  By (seconds): 0
Terminate when Maximum Records
                   Out-Of-Sync: 100000
     Output Out-Of-Sync Record
        Details to Report File: true
            Update Report file
               Every (seconds): 0
            Update Report file
               Every (records): 0

Confirm-Out-Of-Sync (Event Reporting):
***********************************************************************
             Generate Messages: both
 Generate Warning Messages For
          For Out-Of-Sync Rows
           After (differences): 9999

Confirm-Out-Of-Sync (Agent):
***********************************************************************
 Use Static Listening Port For
         Agent During Row Hash
                     On Source: 0
 Use Static Listening Port For
         Agent During Row Hash
                     On Target: 0

Confirm-Out-Of-Sync (NonStop Process):
***********************************************************************
  Source Process Name Starting
                          With:
     Source Process CPU Number: -1
       Source Process Priority: 0
  Target Process Name Starting
                          With:
     Target Process CPU Number: -1
       Target Process Priority: 0

Starting  Veriagt session for source rowhash at 2010-12-11 20:32:25.

Initial Compare Source Agent Information:
                       ORACLE_HOME : /s01/10gdb
       NLS_LANG (from environment) :
                          ORA_SDTZ :
              NLS_LANG (from DBMS) : AMERICAN_AMERICA.WE8ISO8859P1
              NLS_LENGTH_SEMANTICS : BYTE
                   SESSIONTIMEZONE : -05:00
                        Process ID : 21473
                  Operating System : Linux
                      Architecture : x86_64
                       Report File : /home/maclean/veridata/agent/dirver/report/maclean_PC_4150_000010110000000100000001___source_rh.rpt
                        Trace File : /home/maclean/veridata/agent/dirver/trace/maclean_PC_4150_000010110000000100000001___source_rh.trc

Starting  Veriagt session for target rowhash at 2010-12-11 20:32:26.

Initial Compare Target Agent Information:
                       ORACLE_HOME : /s01/db_1
       NLS_LANG (from environment) :
                          ORA_SDTZ :
              NLS_LANG (from DBMS) : AMERICAN_AMERICA.WE8ISO8859P1
              NLS_LENGTH_SEMANTICS : BYTE
                   SESSIONTIMEZONE : +08:00
                        Process ID : 18639
                  Operating System : Linux
                      Architecture : x86_64
                       Report File : /home/maclean/veridata/agent/dirver/report/maclean_PC_4150_000010110000000100000001___target_rh.rpt
                        Trace File : /home/maclean/veridata/agent/dirver/trace/maclean_PC_4150_000010110000000100000001___target_rh.trc

Processing first rowhash block from source at 2010-12-11 20:32:26.
Processing first rowhash block from target at 2010-12-11 20:32:27.
Comparing first row at 2010-12-11 20:32:27.
Starting  Veriagt session for source COOS at 2010-12-11 20:32:27.

Performance Statistics for source Rowhash at 2010-12-11 20:32:27.

                     rows: 103959
          duration (secs): 00:00:02
                 rows/sec: 51979.50
                row bytes: 4345147
            row bytes/sec: 2172573
                bytes/row: 41
             rh bytes/row: 23
             rows skipped: 0
           blocks skipped: 0
           hash comp rate: 0.55
          total comp rate: 0.23
        pct time fetching: 15.44
         pct time waiting: 5.91
     time until first row: 00:00:00
                 ipc msgs: 12
                ipc bytes: 2809974
                bytes/msg: 234164
     compressed bytes/msg: 99262
                bytes/sec: 1404987
     compressed bytes/sec: 595572
    msg compression ratio: 0.42

*** Summarizing Initial Row Comparison Step ***

                          Time: 2010-12-11 20:32:27.
                  Elapsed Time: 00:00:00
         Comparisons performed: 103961
               Rows per second: 169593
              Rows out-of-sync: 11
                       inserts: 9
                       updates: 0
                       deletes: 2

Performance Statistics for target Rowhash at 2010-12-11 20:32:27.

                     rows: 103952
          duration (secs): 00:00:01
                 rows/sec: 103952.00
                row bytes: 4344826
            row bytes/sec: 4344826
                bytes/row: 41
             rh bytes/row: 23
             rows skipped: 0
           blocks skipped: 0
           hash comp rate: 0.55
          total comp rate: 0.23
        pct time fetching: 20.51
         pct time waiting: 17.15
     time until first row: 00:00:00
                 ipc msgs: 12
                ipc bytes: 2809779
                bytes/msg: 234148
     compressed bytes/msg: 99256
                bytes/sec: 2809779
     compressed bytes/sec: 1191076
    msg compression ratio: 0.42

COOS source Agent Information:
                       ORACLE_HOME : /s01/10gdb
       NLS_LANG (from environment) :
                          ORA_SDTZ :
              NLS_LANG (from DBMS) : AMERICAN_AMERICA.WE8ISO8859P1
              NLS_LENGTH_SEMANTICS : BYTE
                   SESSIONTIMEZONE : -05:00
                        Process ID : 21475
                  Operating System : Linux
                      Architecture : x86_64
                       Report File : /home/maclean/veridata/agent/dirver/report/maclean_PC_4150_000010110000000100000001___source_coos.rpt
                        Trace File : /home/maclean/veridata/agent/dirver/trace/maclean_PC_4150_000010110000000100000001___source_coos.trc

Starting  Veriagt session for target COOS at 2010-12-11 20:32:28.

COOS target Agent Information:
                       ORACLE_HOME : /s01/db_1
       NLS_LANG (from environment) :
                          ORA_SDTZ :
              NLS_LANG (from DBMS) : AMERICAN_AMERICA.WE8ISO8859P1
              NLS_LENGTH_SEMANTICS : BYTE
                   SESSIONTIMEZONE : +08:00
                        Process ID : 18641
                  Operating System : Linux
                      Architecture : x86_64
                       Report File : /home/maclean/veridata/agent/dirver/report/maclean_PC_4150_000010110000000100000001___target_coos.rpt
                        Trace File : /home/maclean/veridata/agent/dirver/trace/maclean_PC_4150_000010110000000100000001___target_coos.trc

Persistent out of sync row (optype: insert)

  source row values:

                  T1  K     0     6  107913
           GGROWHASH        1     8  0x0E8563AC22AA9C0F
                  T2  H     2     5  55512
                  T3  H     3    19  MACLEAN IS UPDATING

Persistent out of sync row (optype: insert)

  source row values:

                  T1  K     0     6  107914
           GGROWHASH        1     8  0xAAB002BF5C1B3233
                  T2  H     2     6  255834
                  T3  H     3    19  MACLEAN IS UPDATING

Persistent out of sync row (optype: insert)

  source row values:

                  T1  K     0     6  107917
           GGROWHASH        1     8  0xC0723BE324DC032D
                  T2  H     2     6  197710
                  T3  H     3    19  MACLEAN IS UPDATING

Persistent out of sync row (optype: insert)

  source row values:

                  T1  K     0     6  107918
           GGROWHASH        1     8  0x8A63DA5F06C3A12C
                  T2  H     2     6  194628
                  T3  H     3    19  MACLEAN IS UPDATING

Persistent out of sync row (optype: delete)

  target row values:

                  T1  K     0     8  99993242
           GGROWHASH        1     8  0x958B6304CEC4BAD3
                  T2  H     2     4  1213
                  T3  H     3    11  OUT OF SYNC

*** Summarizing Confirm Out-of-Sync Step ***

                          Time: 2010-12-11 20:32:29.
                  Elapsed Time: 00:00:01
                Rows confirmed: 11
               Rows per second: 11

*** Summarizing Persistently Out-of-Sync Rows ***

              Rows out-of-sync: 5
                       inserts: 4
                       updates: 0
                       deletes: 1

*** Summarizing In-Sync-after-In-Flight Rows ***

  Rows in-sync-after-in-flight: 6
                       inserts: 5
                       updates: 0
                       deletes: 1

*** Summarizing Still-Changing Rows ***

                Rows in-flight: 0
                       inserts: 0
                       updates: 0
                       deletes: 0

Comparison terminated normally.

Goldengate一个令人郁闷的小细节

晚上有兴趣测试了下Goldengate的initial load功能,通过initial load+change sync可以很容易做到数据迁移data migration;不过发现一个令人很郁闷的细节,就是replicat的params中定义map参数时TARGET到前面一个逗号间要留一个空格space:

MAP table spec, TARGET table spec     
/*TARGET到前面一个逗号间要留一个空格space*/
[, DEF ]
[, TARGETDEF ]
[, COLMAP ()]
[, EVENTACTIONS ()]
[, EXCEPTIONSONLY]
[, EXITPARAM “”]
[, FILTER ()]
[, HANDLECOLLISIONS | NOHANDLECOLLISIONS]
[, INSERTALLRECORDS]
[, INSERTAPPEND | NOINSERTAPPEND]
[, KEYCOLS ()]
[, REPERROR ( , )]
[, SQLEXEC ()]
[, TRIMSPACES | NOTRIMSPACES]
[, WHERE ()]
;

如果map参数中target和前面的逗号间没有空格会出现invaild option for map错误:

GGSCI (rh2.oracle.com) 32> view params init1
extract init1
userid ggate,password ggate
RMTHOST rh3.oracle.com,MGRPORT 7809
RMTTASK replicat,GROUP  init3
table sh.products;

GGSCI (rh3.oracle.com) 40> view params init3

replicat init3
userid ggate,password ggate
ASSUMETARGETDEFS
map sh.products,target sh.products;

GGSCI (rh2.oracle.com) 33> start extract init1

Sending START request to MANAGER ...
EXTRACT INIT1 starting

GGSCI (rh3.oracle.com) 41> view report init3
..............
MAP resolved (entry SH.PRODUCTS):
  map SH.PRODUCTS, sh.products;

Source Context :
  SourceModule            : [er.main]
  SourceID                : [/home/ecloud/workspace/Build_FBO_OpenSys_r11.1.1.0.0_078_[34093]/perforce/src/app/er/rep.c]
  SourceFunction          : [get_map_entry]
  SourceLine              : [8573]
  ThreadBacktrace         : [11] elements
                          : [./replicat(CMessageContext::AddThreadContext()+0x26) [0x5d9516]]
                          : [./replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0x5cffb2]]
                          : [./replicat(_MSG_ERR_STARTUP_PARAMERROR_INVALID_OPTION_VALUE
(CSourceContext*, char const*, char const*, CMessageFactory::MessageDisposition)
+0x9b) [0x59086b]]
                          : [./replicat(get_map_entry(char*, int, __wc*, int)+0x284d) [0x4ee66d]]
                          : [./replicat [0x5357d5]]
                          : [./replicat(WILDCARD_check_table(char const*, char const*, int, 
unsigned int*, int, unsigned int, DBString<777>*, int)+0x15e) [0x536fce]]
                          : [./replicat(REP_find_source_file_wc(char const*, unsigned int, DBString<777>*, int)+0x64f) [0x8a1caf]]
                          : [./replicat [0x8a9b04]]
                          : [./replicat(main+0x227a) [0x4f50da]]
                          : [/lib64/libc.so.6(__libc_start_main+0xf4) [0x340d01d994]]
                          : [./replicat(__gxx_personality_v0+0x1e2) [0x4d86ba]]

2010-12-01 08:58:30  ERROR   OGG-00212  Invalid option for MAP: sh.products.

/*我们来加上空格*/

GGSCI (rh3.oracle.com) 43> view params init3
replicat init3
userid ggate,password ggate
ASSUMETARGETDEFS
map sh.products, target sh.products;

GGSCI (rh3.oracle.com) 43> view report init3
MAP resolved (entry SH.PRODUCTS):
  map SH.PRODUCTS, target sh.products;

2010-12-01 09:00:30  WARNING OGG-00869  No unique key is defined for table PRODUCTS. 
All viable columns will be used to represent the key, but may not guarantee uniqueness.  
KEYCOLS may be used to define the key.
Using following columns in default map by name:
  PROD_ID, PROD_NAME, PROD_DESC, PROD_SUBCATEGORY, PROD_SUBCATEGORY_ID, 
  PROD_SUBCATEGORY_DESC, PROD_CATEGORY, PROD_CATEGORY_ID, 
  PROD_CATEGORY_DESC, PROD_WEIGHT_CLASS, PROD_UNIT_OF_MEASURE, 
  PROD_PACK_SIZE, SUPPLIER_ID, PROD_STATUS, PROD_LIST_PRICE, 
  PROD_MIN_PRICE, PROD_TOTAL, PROD_TOTAL_ID, PROD_SRC_ID, 
  PROD_EFF_FROM, PROD_EFF_TO, PROD_VALID

/* 加上空格后初始化成功*/

沪ICP备14014813号-2

沪公网安备 31010802001379号