Oracle 11g OGG单实例RAC同步

ip

数据库版本

SID

源端

10.32.50.88

11g

orcl

目标端

10.32.68.112

11g

orcl

注意：RAC所有节点都要配置(grid)

cat listener.ora

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))# line added by Agent

LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))# line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON# line added by Agent

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON# line added by Agent

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = +ASM)

(ORACLE_HOME=/u01/gridsoft/11.2.0)

(SID_NAME = +ASM1)

)

)

--reload监控，使配置生效(grid)

lsnrctl reload

--测试连接是否正常(oracle)

sqlplus sys/oracle@192.168.1.56:1521/+ASM as sysdba

配置TNS, 使用每个节点本地IP地址(oracle)

--节点1

cd $ORACLE_HOME/network/admin

cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

RAC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.56)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.58)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

+ASM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.56)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = +ASM)

)

)

--节点2

注意：要用oracle用户去配置

[oracle@rac2 admin]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin

[oracle@rac2 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

RAC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.56)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.58)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

)

+ASM =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.58)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = +ASM)

)

)

修改归档路径参数

alter system set log_archive_dest_1='location=+DATA/orcl/archivelog' scope=spfile sid='*';

关闭数据库，全部节点都要关闭

srvctl stop database -d orcl

节点2 数据库启动到mount状态

srvctl start instance -d orcl -i orcl2 -o mount

修改归档并启动数据库

alter database archivelog;

alter database open;

查看归档信息

archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination +DATA/orcl/archivelog

Oldest online log sequence 3

Next log sequence to archive 4

Current log sequence 4

启动节点1

alter database open;

oracle用户下添加OGG环境变量

export OGG_HOME=/oracle/ogg/install

export INVENTORY_LOCATION=/oracle/oraInventory

export PATH=$OGG_HOME:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME:/lib:/usr/lib

oracle用户下创建ogg目录

mkdir -p /oracle/ogg/install/

mkdir -p /oracle/archivelog/

chown -R oracle:oinstall /oracle

使用共享磁盘划分一个10G共享裸设备

fdisk /dev/sde

格式化磁盘区

mkfs -t ext3 /dev/sde1

授权及挂载

chown -R oracle:oinstall /oracle

mount /dev/sde1 /oracle

vi /etc/fstab

/dev/sde1 /goldengate ext3 defaults 1 2

采用在现有ASM磁盘上创建一个ASM卷并挂载(grid)

sqlplus / as sysasm

alter diskgroup DATA add volume acfsload size 1G;

ll /dev/asm*

brwxrwx--- 1 root dba 252, 107521 May 19 15:44 acfsload-210

创建acfs挂载目录，每个节点都要创建相同目录(root)

mkdir -p /u01/app/acfsmounts/acfsload-210

用mkfs创建文件系统(root)

mkfs -t acfs -n acfs01 /dev/asm/acfsload-210

shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory

mkfs.acfs: version = 11.2.0.4.0

mkfs.acfs: on-disk version = 39.0

mkfs.acfs: volume = /dev/asm/acfsload-210

mkfs.acfs: volume size = 1073741824

mkfs.acfs: Format complete.

用acfsuit命令注册文件系统(root)

acfsutil registry -a -f /dev/asm/acfsload-210 /u01/app/acfsmounts/acfsload-210

acfsutil registry: mount point /u01/app/acfsmounts/acfsload-210 successfully added to Oracle Registry

挂在目录及授权(root)

mount.acfs -o all

chown oracle:oinstall /u01/app/acfsmounts/acfsload-210

unzip 191004_fbo_ggs_Linux_x64_shiphome.zip

cd fbo_ggs_Linux_x64_shiphome/Disk1

ls

install response runInstaller stage

./runInstaller

create tablespace ogg datafile '/u01/app/oracle/oradata/orcl/ogg.dbf' size 1g autoextend on;

create tablespace test datafile '/u01/app/oracle/oradata/orcl/data.dbf' size 1g autoextend on;

CREATE USER ogg IDENTIFIED BY ogg DEFAULT TABLESPACE ogg;

授权ogg用户，如果对用户权限要求不严格可以直接授予dba权限，否则授予如下权限

grant connect,resource,dba to ogg;

创建数据库测试数据用户

create user test identified by test default tablespace test;

grant connect,resource to test;

打开最小附加日志

alter database add supplemental log data;

alter database force logging;

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

如果要让 GoldenGate 支持DDL操作，还需要额外运行几个脚本，这些脚本是OGG带的而不是 Oracle 带的，在 GoldenGate 的安装目录都可以找到，源端与目标端都需要运行，如下：

@marker_setup.sql --该脚本用于创建DDL的marker表GGS_MARKER，用于存储DDL信息，该表只进行insert操作

@ddl_setup.sql --该脚本创建了进行DDL复制抽取和复制所需的对象

@role_setup.sql --创建DDL复制所需的对象

@ddl_enable.sql -- 启用DDL复制，实质上是创建触发器，用以想MARKER和HISTORY表插入DDL信息

ggsci

创建ogg子目录

create subdirs

默认mgr进程可以添加如下参数：

view params mgr

PORT 7809 (指定服务监听端口；默认端口为7809)

DYNAMICPORTLIST 7820-7830, 7833, 7835 (动态端口：可以制定最大256个可用端口的动态列表，当指定的端口不可用时，管理进程将会从列表中选择一个可用的端口，源端和目标段的Collector、Replicat、GGSCI进程通信也会使用这些端口)

autostart er * (本处设置表示每2分钟尝试重新启动所有EXTRACT进程；以后5分钟清零。)

autorestart er *, retries 5, waitminutes 3 （自动启动所有参数，会尝试5次，每次等待3分钟）

purgeoldextracts ./dirdat/*,usecheckpoints, minkeepdays 7 (自动清理过期的trail文件，会保留7天)

启动mgr进程

start mgr

dblogin userid ogg, password ogg

add trandata test.*

exta为进程名，一般为ext开头表示是extract进程，后面可以加1-2位字符标识；begin可以是now也可以是指定的时间

add ext exta, tranlog, begin now

为exta进程配置本地队列，本地的trail文件以la开头

add exttrail ./dirdat/lc, ext exta, MEGABYTES 20

添加投递进程pmpa，将本地的./dirdat/la文件传送到目标端

add extract pmpa, EXTTRAILSOURCE ./dirdat/lc

为pmpa进程配置远程队列，队列的位置是源端的主机目录,传送到目标断后命名以rc开头。

add rmttrail ./dirdat/rc, ext dpea, MEGABYTES 20

add RMTTRAIL ./dirdat/rc, EXTRACT PMPA

view params EXTA

EXTRACT exta

setenv ( NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK" )

setenv (ORACLE_SID = orcl)

--SETENV (ORACLE_HOME = "/u01/app/oracle/product/db11g")

USERID ogg, PASSWORD ogg

--TRANLOGOPTIONS ASMUSER sys@+ASM,ASMPASSWORD oracle (RAC中ASM配置)

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000

EXTTRAIL ./dirdat/lc

DYNAMICRESOLUTION

DDL INCLUDE ALL (DDL复制参数)

DDLOPTIONS ADDTRANDATA,REPORT (DDL复制参数)

table test.*;

附加参数, 可忽略

--GETUPDATEBEFORES

--DDL INCLUDE ALL (DDL复制参数)

--DDLOPTIONS ADDTRANDATA NOCROSSRENAME REPORT (ddl复制参数)

--NOCOMPRESSDELETES

启动进程

start EXTA

view params PMPA

extract pmpa

setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )

rmthost 10.32.68.112, mgrport 7809, compress

PASSTHRU

--REPORT AT 01:59

--reportrollover at 02:00

RMTTRAIL ./dirdat/rc

DYNAMICRESOLUTION

table test.*;

启动进程

start PMPA

add rep rep1a, exttrail ./dirdat/rc, nodbcheckpoint

view params REP1A

replicat rep1a

setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )

setenv (ORACLE_SID = orcl)

userid ogg, password ogg

reperror default,abend

discardfile ./dirrpt/rep1a.dsc,append, megabytes 10

DDL INCLUDE MAPPED (DDL复制参数)

DDLOPTIONS REPORT (DDL复制参数)

map test.*, target test.*;

附加参数, 可忽略

--assumetargetdefs

--allownoopupdates

--dynamicresolution

--DDL include all

--ddloptions report

--DDLERROR DEFAULT DISCARD

--DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

--ASSUMETARGETDEFS

--INSERTALLRECORDS

启动进程

start REP1A

查看ogg环境

show

查看进程信息

info all

查看配置/编辑文件信息

view/edit params filename

启动/停止进程

start/stop name

查看进程报错日志

view report name

查看ogg错误日志

view GGSEVT Contents

强制停止进程

killi extract name

查看历史命令

history

参数文件存放位置

Storing parameter filename

删除进程配置文件

delete name

delete name force

delete replicat name!

查看trail

info exttrail *

查看oracle thread和gg thread的对应关系

select distinct(thread#) from V$log；

info extract ext1 ,showch

查看extract的lag (info all 中lag一列是unknow时用)

send ext1 getlag

查看延迟

lag name