oracle导入测试脚本,ORACLE的高达上goldengate完整测试脚本

一、源端操作

操作系统操作

1、创建gg操作系统用户

useradd -g oinstall -G dba ggs

passwd ggs

2、配置gg用户环境变量，可使用oracle环境变量，追加以下变量

export GG_HOME=/ggs

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME

数据库操作

1、规划归档目录，ASM为例

export ORACLE_SID=+ASM1

asmcmd

cd dg1/yuanqk/

mkdir archive

cd archive

mkdir arch1

mkdir arch2

2、修改数据库参数归档路径

alter system set log_archive_dest_1='location=+dg1/yuanqk/archive/arch1' scope=spfile sid='yuanqk1';

alter system set log_archive_dest_1='location=+dg1/yuanqk/archive/arch2' scope=spfile sid='yuanqk2';

3、修改数据库为归档模式

shutdown immediate

startup mount

alter database archivelog;

alter database open;

3、修改日志必要参数

select supplemental_log_data_min,force_logging from v$database;

alter database add supplemental log data;

alter database force logging;

4、创建gg数据库用户

create user ggs identified by ggs;

grant dba to ggs;

5、为gg连接asm配置listener和tnsnames文件(各节点都要执行)，非ASM可以不配置

cp listener.ora listener.ora.bak

cp tnsnames.ora tnsnames.ora.bak

vi listener.ora

-----原文可能如下----------

LISTENER_RAC1 =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.200)(PORT = 1521)(IP = FIRST))

)

)

SID_LIST_LISTENER_RAC1 =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

)

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

---------需要在SID_LIST_LISTENER_XXXX上增加如下信息-----------

SID_LIST_LISTENER_RAC1 =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = oradb)

(ORACLE_HOME = /oracle/product/10.2.0/db_1)

(SID_NAME = oradb1)

)

(SID_DESC =

(GLOBAL_DBNAME = +ASM)

(ORACLE_HOME = /oracle/product/10.2.0/db_1)

(SID_NAME = +ASM1)

)

)

vi tnsnames.ora增加以下服务名

GGASM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1521))

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = +ASM)

(UR=A)

)

)

6、测试使用新建的服务名连接ASM

sqlplus sys/system@ggasm as sysdba

show parameter instance

数据导出导入，数据初始化

1、查询源端数据库的SCN

col CURRENT_SCN for 9999999999999999

select CURRENT_SCN from v$database ;

2、使用expdp导出数据

create directory dump_dir as '/oracle';

grant all on directory dump_dir to public ;

expdp \'/ as sysdba\' schemas=TEST DIRECTORY=dump_dir  CONTENT=DATA_ONLY FLASHBACK_SCN=392079 DUMPFILE=test.dmp logfile=test.log

impdp \'/ as sysdba\' schemas=TEST DIRECTORY=dump_dir DUMPFILE=test.dmp logfile=test.log

nohup expdp parfile=exp_gg_szfs_20120613.par > exp_gg_szfs_20120613.par.out &

userid='/ as sysdba'

schemas=TEST

directory=orabak2

dumpfile=exp_gg_szfs_20120613_%U.dmp

logfile=exp_gg_szfs_20120613.log

FLASHBACK_SCN=428888780

parallel=4

EXCLUDE=statistics,grant,sequence

GoldenGate操作

1、安装GoldenGate软件

unzip V32406-01_20363.zip

tar -xvf fbo_ggs_Linux_x86_ora10g_32bit.tar

1.1校验gg需要的库文件是否齐全

ldd ggsci

1.2安装gg软件

./ggsci

create subdirs

2、配置manager进程

edit params mgr

DYNAMICPORTLIST 7840-7914

PORT 7809

PURGEOLDEXTRACTS /ggs/dirdat/wl*, USECHECKPOINTS, MINKEEPFILES 10

AUTORESTART ER *, RETRIES 3, WAITMINUTES 5

PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

3、为需要复制的数据表添加trandata

3.1生成所有要复制表的增加附加日志的脚本

sqlplus / as sysdba

spool trandata.txt

set head off

set linesize 1000

set pagesize 1000

select 'add trandata '|| owner || '.' || table_name from dba_tables where owner='TEST';

spool off

3.2执行trandata.txt脚本，为要复制的表增加附加日志

./ggsci

dblogin userid ggs,password ggs

obey ./dirsql/trandata.txt

3.3检查表级附加日志是否已全部打开

select owner,table_name from dba_tables  where owner in ('TEST')

minus

select owner,TABLE_NAME from dba_log_groups;

4、配置extract进程

add extract wlmext,tranlog,threads 2,begin now

edit params wlmext

EXTRACT wlmext

USERID ggs, PASSWORD ggs

EXTTRAIL /ggs/dirdat/wl

DISCARDFILE exterr,APPEND,MEGABYTES 5

TRANLOGOPTIONS ALTARCHIVELOGDEST instance yuanqk1 +DG1/yuanqk/archive/arch1,altarchivelogdest instance yuanqk2 +DG1/yuanqk/archive/arch2

TRANLOGOPTIONS ASMUSER SYS@GGASM,ASMPASSWORD SYSTEM

FETCHOPTIONS, USESNAPSHOT, NOUSELATESTVERSION, MISSINGROW REPORT

STATOPTIONS REPORTFETCH

WARNLONGTRANS 1H, CHECKINTERVAL 5M

TABLE test.*;

--SEQUENCE test.*;

添加该extract对应的trail文件，用来存储抽取的数据。单个文件大小设置为10MB

add exttrail /ggs/dirdat/wl,extract wlmext, MEGABYTES 10

5、配置data pump进程

add extract wlmdump, exttrailsource /ggs/dirdat/wl, begin now

edit params wlmdump

EXTRACT wlmdump

USERID ggs, PASSWORD ggs

RMTHOST 192.168.1.150, MGRPORT 7809

RMTTRAIL /ggs/dirdat/wl

DISCARDFILE wlmdumperr,APPEND,MEGABYTES 5

TABLE test.*;

添加该data pump对应的trail文件，用来传输抽取的数据。

add rmttrail /ggs/dirdat/wl,extract wlmdump, megabytes 10

6、配置checkpoint表

EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE ggs.ggschkpt

dblogin userid ggs,password ggs

ADD CHECKPOINTTABLE ggs.ggschkpt

7、启动相关进程并进行检查

start mgr

start wlmext

start wlmdump

info all

view report wlmext

view report wlmdump

====================至此源端操作完毕========================

二、目标端配置

操作系统操作

1、创建gg操作系统用户

useradd -g oinstall -G dba ggs

passwd ggs

2、配置gg用户环境变量，可使用oracle环境变量，追加以下变量

export GG_HOME=/ggs

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$GG_HOME

GoldenGate操作

1、安装GoldenGate软件

unzip V32406-01_20363.zip

tar -xvf fbo_ggs_Linux_x86_ora10g_32bit.tar

1.1校验gg需要的库文件是否齐全

ldd ggsci

1.2安装gg软件

./ggsci

create subdirs

2、配置manager进程

edit params mgr

DYNAMICPORTLIST 7840-7914

PORT 7809

PURGEOLDEXTRACTS /ggs/dirdat/wl*, USECHECKPOINTS, MINKEEPFILES 10

AUTORESTART ER *, RETRIES 3, WAITMINUTES 5

PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 5, FREQUENCYMINUTES 30

3、配置checkpoint表

EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE ggs.ggschkpt

dblogin userid ggs,password ggs

ADD CHECKPOINTTABLE ggs.ggschkpt

4、配置replicat进程

add replicat wlmrep,exttrail /ggs/dirdat/wl checkpointtable ggs.ggschkpt

edit params wlmrep

REPLICAT wlmrep

ASSUMETARGETDEFS

USERID ggs, PASSWORD ggs

DISCARDFILE wlmreperr,APPEND,MEGABYTES 5

HANDLECOLLISIONS

REPERROR DEFAULT, DISCARD --定义出错以后replicat的响应，一般可以定义为两种：

--Abend，即一旦出现错误即停止复制，此为缺省配置；

--Discard，出现错误后继续复制，只把错误的数据放到discard文件中。

DISCARDFILE  /ggs/dirdat/LOCALREP.dsc, PURGE

DYNAMICRESOLUTION

MAP test.* , TARGET test.* ;

5、启动replicat进程，可以使用scn

start replicat wlmrep

start replicat wlmrep, aftercsn 392079