1.环境介绍
类别源端目标端
数据库类型
单实例
单实例
数据库版本
11.2.0.4
11.2.0.4
Oracle_SID
cndba
cndba
DB_NAME
cndba
cndba
主机IP地址
192.168.1.85
192.168.1.86
OS版本
RedHat 6.7
RedHat 6.7
OGG版本
11.2.1.0.1 64位
11.2.1.0.1 64位
主机名
cndba
cndba
2.安装前的准备工作
2.1.源端创建GoldenGate用户表空间
create tablespace ogg_data datafile '/u01/app/oracle/oradata/cndba/ogg01.dbf' size 100m autoextend off;
2.2.源端创建GoldenGate用户
create user ogg identified by ogg default tablespace ogg_data;
grant connect,resource,dba,create table,create sequence to ogg;
2.3.目标端创建GoldenGate用户表空间
create tablespace ogg_data datafile '/u01/app/oracle/oradata/cndba/ogg01.dbf' size 100m autoextend off;
2.4.目标端创建GoldenGate用户表空间
create user ogg identified by ogg default tablespace ogg_data;
grant connect,resource,dba,create table,create sequence to ogg;
2.5.源端创建测试用户及测试数据
SQL> create user test identified by test;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table test (id number(10) primary key ,name varchar(8));
Table created.
SQL> insert into test values(1,'zhangsan');
1 row created.
SQL> insert into test values(2,'lisi');
1 row created.
SQL> commit;
2.6.目标端创建测试用户及测试数据
SQL> create user test identified by test;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
SQL> conn test/test
Connected.
SQL> create table test (id number(10) primary key ,name varchar(8));
Table created.
目标端不需要插入数据
2.7.源端开启归档模式、强制日志、附加日志
2.7.1.查看是否开启归档模式、强制日志、附加日志
SQL> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
LOG_MODE
SUPPLEME FOR
------------ -------- ---
NOARCHIVELOG NO
NO
2.7.2.开启归档
[root@ cndba]# mkdir -p /u01/archive
[root@ cndba]# chown -R oracle:oinstall /u01/archive/
SQL> archive log list
Database log mode
No Archive Mode
Automatic archival
Disabled
Archive destination
/u01/app/oracle/product/11.2.0/db_1/dbs/arch
Oldest online log sequence
5
Current log sequence
7
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1060585472 bytes
Fixed Size 2260000 bytes
Variable Size 905970656 bytes
Database Buffers 146800640 bytes
Redo Buffers 5554176 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter system set log_archive_dest_1='location=/u01/archive';
System altered.
SQL> archive log liset
SP2-0718: illegal ARCHIVE LOG option
SQL> archive log list
Database log mode
Archive Mode
Automatic archival
Enabled
Archive destination
/u01/archive
Oldest online log sequence
5
Next log sequence to archive 7
Current log sequence
7
2.7.3.开启强制日志
SQL> alter database force logging;
Database altered.
2.7.4.开启附加日志
SQL> alter database add supplemental log data;
Database altered.
2.7.5.查看是否开启归档模式、强制日志、附加日志
SQL> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
LOG_MODE
SUPPLEME FOR
------------
-------- ---
ARCHIVELOG
YES
YES
2.7.6.查看回收站是否关闭
SQL> show parameter recycle
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle
string
db_recycle_cache_size
big integer 0
recyclebin
string on
SQL> alter system set recyclebin=off scope=spfile;
System altered.
--重启数据库查看
SQL> show parameter recycle
NAME
TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle
string
db_recycle_cache_size
big integer 0
recyclebin
string OFF
3.GoldenGate安装
3.1.源端安装OGG
3.1.1.创建软件安装目录并赋权
[root@ software]# mkdir -p /u01/app/oracle/ogg
[root@ software]# chown -R oracle:oinstall /u01/app/oracle/ogg
3.1.2.配置oracle用户环境变量
[oracle@ ~]$ vi .bash_profile
设置Library 路径
假设OGG的安装目录是/u01/app/oracle/ogg，那么在/home/oracle/.bash_profile文件里添加如下内容：
export OGG_HOME=$ORACLE_BASE/ogg
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME:/lib:/usr/lib
--source 使修改生效：
[oracle@ ~]$ source .bash_profile
3.1.3.解压ogg文件
[root@ software]# cd /software/
[root@ software]# unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[root@ software]# tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/app/oracle/ogg
[root@ ogg]# chown -R oracle:oinstall /u01/app/oracle/ogg
注意：/u01/app/oracle/ogg 是$OGG_HOME
3.1.4.运行ogg并创建目录
[oracle@ ~]$ cd $OGG_HOME
[oracle@ 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 (cndba) 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
3.2.目标端安装OGG
3.2.1.创建软件安装目录并赋权
[root@ software]# mkdir -p /u01/app/oracle/ogg
[root@ software]# chown -R oracle:oinstall /u01/app/oracle/ogg
3.2.2.配置oracle用户环境变量
[oracle@ ~]$ vi .bash_profile
设置Library 路径
假设OGG的安装目录是/u01/app/oracle/ogg，那么在/home/oracle/.bash_profile文件里添加如下内容：
export OGG_HOME=$ORACLE_BASE/ogg
export PATH=$ORACLE_HOME/bin:$PATH:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME:/lib:/usr/lib
--source 使修改生效：
[oracle@ ~]$ source .bash_profile
3.2.3.解压ogg文件
[root@ software]# cd /software/
[root@ software]# unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
[root@ software]# tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/app/oracle/ogg
[root@ ogg]# chown -R oracle:oinstall /u01/app/oracle/ogg
注意：/u01/app/oracle/ogg 是$OGG_HOME
3.2.4.运行ogg并创建目录
[oracle@ ~]$ cd $OGG_HOME
[oracle@ 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 (cndba) 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
4.GoldenGate配置
4.1.OGG源端配置
4.1.1.配置mgr进程
GGSCI (cndba) 3> edit params mgr
GGSCI (cndba) 4> view params mgr
port 7809
GGSCI (cndba) 5> start mgr
Manager started.
GGSCI (cndba) 6> info all
Program
Status
Group
Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
GGSCI (cndba) 7> sh netstat -ntpl |grep 7809 --查看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
14176/./mgr
GGSCI (cndba) 8> sh ps -ef|grep mgr --查看mgr进程是否存在
root
14
2 0 13:24 ?
00:00:00 [async/mgr]
postfix 1867 1860 0 13:26 ?
00:00:00 qmgr -l -t fifo -u
oracle 14176 14114 0 15:43 ?
00:00:00 ./mgr PARAMFILE /u01/app/oracle/ogg/dirprm/mgr.p
oracle 14185 14114 0 15:44 pts/0 00:00:00 sh -c ps -ef|grep mgr
oracle 14187 14185 0 15:44 pts/0 00:00:00 grep mgr
4.1.2.添加表级transdata
GGSCI (cndba) 10> dblogin userid ogg,password ogg
Successfully logged into database.
查看是否开启
GGSCI (cndba) 11> info trandata test.test
GGSCI (cndba) 11> add trandata test.*
Logging of supplemental redo data enabled for table TEST.TEST.
注意：为了方便这里直接添加整个用户下表级transdata，如果只是同步部分表的，请批量
执行add trandata test.tablename
4.1.3.配置extract抽取进程
GGSCI (cndba) 13> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (cndba) 14> add extract ext1, tranlog, begin now, threads 1
EXTRACT added.
GGSCI (cndba) 15> add exttrail ./dirdat/et, extract ext1
EXTTRAIL added.
GGSCI (cndba) 16> edit params ext1
GGSCI (cndba) 17> view params ext1
EXTRACT ext1
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") --此处数据库字符集设为一致
--SETENV (ORACLE_SID = "cndba")
SETENV (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
USERID ogg, PASSWORD ogg
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
EXTTRAIL ./dirdat/et
DYNAMICRESOLUTION
--DDL INCLUDE ALL
TABLE test.*;
4.1.4.配置pump传输进程
GGSCI (cndba) 18> add extract pump1,exttrailsource ./dirdat/et,begin now
EXTRACT added.
GGSCI (cndba) 19> add rmttrail ./dirdat/et,extract pump1
RMTTRAIL added.
GGSCI (cndba) 20> edit params pump1
GGSCI (cndba) 21> view params pump1
EXTRACT pump1
RMTHOST 192.168.1.86, MGRPORT 7809
RMTTRAIL ./dirdat/et
PASSTHRU
DYNAMICRESOLUTION
TABLE test.*;
GGSCI (cndba) 22> info all
Program
Status
Group
Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
EXTRACT
STOPPED
EXT1
00:00:00
00:18:00
EXTRACT
STOPPED
PUMP1
00:00:00
00:04:07
4.2.OGG目标端配置
4.2.1.配置mgr进程
GGSCI (cndba) 3> edit params mgr
GGSCI (cndba) 4> view params mgr
port 7809
GGSCI (cndba) 5> start mgr
Manager started.
GGSCI (cndba) 6> info all
Program
Status
Group
Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
GGSCI (cndba) 7> sh netstat -ntpl |grep 7809 --查看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
14176/./mgr
GGSCI (cndba) 8> sh ps -ef|grep mgr --查看mgr进程是否存在
root
14
2 0 13:24 ?
00:00:00 [async/mgr]
postfix 1867 1860 0 13:26 ?
00:00:00 qmgr -l -t fifo -u
oracle 14176 14114 0 15:43 ?
00:00:00 ./mgr PARAMFILE /u01/app/oracle/ogg/dirprm/mgr.p
oracle 14185 14114 0 15:44 pts/0 00:00:00 sh -c ps -ef|grep mgr
oracle 14187 14185 0 15:44 pts/0 00:00:00 grep mgr
5.初始化数据-RMAN
5.1.启动生产端和容灾端的管理进程
--源端
[oracle@ ogg]$ cd /u01/app/oracle/ogg
[oracle@ ogg]$ ./ggsci
GGSCI (cndba) 1> start mgr
Manager started.
GGSCI (cndba) 1> info all
Program
Status
Group
Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
EXTRACT
STOPPED
EXT1
00:00:00
00:40:35
EXTRACT
STOPPED
PUMP1
00:00:00
00:26:42
--目标端
[oracle@host1 ~]$ cd /u01/app/oracle/ogg
[oracle@host1 ogg]$ ./ggsci
GGSCI (host1) 1> start mgr
Manager started.
GGSCI (cndba) 14> info all
Program
Status
Group
Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
5.2.启动源端的EXTRACT进程
GGSCI (cndba) 2> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (cndba) 4> start pump1
Sending START request to MANAGER ...
EXTRACT PUMP1 starting
GGSCI (cndba) 5> info all
Program
Status
Group
Lag at Chkpt Time Since Chkpt
MANAGER
RUNNING
EXTRACT
RUNNING
EXT1
00:00:00
00:00:03
EXTRACT
RUNNING
PUMP1
00:00:00
00:28:57
5.3.查看数据库中所有事务的开始时间
查看数据库中所有事务的开始时间，直到其大于抽取进程的启动时间再开始备份数
据库，因为GoldenGate 的只获取在Extract 启动以后的交易变化，在Extract 启动之
前开始而在Extract 启动以后才完成的交易GoldenGate 将会忽略这些交易，这些被忽
略的交易数据就会丢失。所以需要等数据库中所以的交易都在Extract 启动之后开始
的才能开始备份数据库。通过v$transaction 视图来查看数据库中的交易：
select min(start_time) from v$transaction;
这里是测试环境没有事物，可以进行后面的备份了。
5.4.RMAN 备份源端数据库