安装ogg:
[oracle@node1 ogg]$ ggsci
GGSCI (node1) 1> create subdirs
Parameter files /oracle/app/oracle/ogg/dirprm: already exists
Report files /oracle/app/oracle/ogg/dirrpt: created
Checkpoint files /oracle/app/oracle/ogg/dirchk: created
Process status files /oracle/app/oracle/ogg/dirpcs: created
SQL script files /oracle/app/oracle/ogg/dirsql: created
Database definitions files /oracle/app/oracle/ogg/dirdef: created
Extract data files /oracle/app/oracle/ogg/dirdat: created
Temporary files /oracle/app/oracle/ogg/dirtmp: created
Stdout files /oracle/app/oracle/ogg/dirout: created
GGSCI (node1) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (node1) 6> edit params mgr
PORT 7809
GGSCI (node1) 7> start mgr
配置环境变量:
[oracle@node1 ogg]$ sqlplus / as sysdba
sys@RAC> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG NO YES
sys@RAC> alter database add supplemental log data;
Database altered.
sys@RAC> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG YES YES
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database add supplemental log data (primary key) columns;
Database altered.
SQL> alter database add supplemental log data (foreign key) columns;
Database altered.
SQL> alter database add supplemental log data (unique) columns;
Database altered.
export GGHOME=$ORACLE_BASE/ogg
export PATH=$ORACLE_BASE/ogg:$ORACLE_HOME/bin:/usr/bin/:$PATH
创建用户及权限
create user ogg identified by ogg default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to ogg;
grant connect ,resource,unlimited tablespace to ogg;
grant execute on utl_file to ogg;
grant select any dictionary,select any table to ogg;
grant alter any table to ogg;
grant flashback any table to ogg;
grant execute on dbms_flashback to ogg;
设置RAC的静态监听
#node1
10.3.4.13 node1.localdomain node1
10.3.4.14 node1-vip.localdomain node1-vip
192.168.56.13 node1-private.localdomain node1-private
#node2
10.3.4.15 node2.localdomain node2
10.3.4.16 node2-vip.localdomain node2-vip
192.168.56.15 node2-private.localdomain node2-private
su - grid
[grid@node1 admin]$ pwd
/oracle/app/11.2/grid/network/admin
[grid@node1 admin]$ 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=/oracle/app/11.2/grid)
(SID_NAME = +ASM1)
)
)
cat tnsname.ora
ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = node2 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
# (SID_NAME = +ASM1)
)
)
su - oracle
[oracle@node1 ~]$ sqlplus sys/oracle0101@asm as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 5 03:04:38 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
idle> show parameter name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name string +ASM
instance_name string +ASM1
lock_name_space string
service_names string +ASM
node2依此设置
设置源端的extract
GGSCI (node1) 10> dblogin userid ogg ,password ogg
Successfully logged into database.
ADD EXTRACT e1, TRANLOG, BEGIN NOW, THREADS 2
add exttrail /oracle/app/oracle/ogg/dirdat/e1, extract e1
edit params e1
extract e1
dynamicresolution
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle0101
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
exttrail /oracle/app/oracle/ogg/dirdat/e1
table qn.t;
add extract p1 ,exttrailsource /oracle/app/oracle/ogg/dirdat/e1
add rmttrail /opt/ogg/dirdat/e1,extract p1
edit params p1
extract p1
userid ogg, password ogg
rmthost 10.3.4.110, mgrport 7809
rmttrail /opt/ogg/dirdat/e1
table qn.t;
GGSCI (node1) 35> add trandata qn.t
2016-12-05 00:37:10 WARNING OGG-00869 No unique key is defined for table 'T'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo log data is already enabled for table QN.T.
设置目标端replicat
add replicat r1, exttrail ./dirdat/e1, checkpointtable ogg.ckpt
edit params r1
replicat r1
ASSUMETARGETDEFS
userid ogg,password ogg
reperror default,discard
discardfile ./dirrpt/e1.dsc, append, megabytes 5
map qn.t, target qn.t;
测试
qn@RAC> insert into t select * from t ;
6 rows created.
qn@RAC> commit ;
Commit complete.
qn@R2> select * from t ;
ID NAME
---------- -----------------------------------
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
9 rows selected.
配置rac+ogg注意事项:
1. archive log ,需要通过TRANLOGOPTIONS 设置,否则会报错,如下:
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle0101
2. 需要设置asm实例的静态监听与tnsname.ora
[grid@node1 admin]$ pwd
/oracle/app/11.2/grid/network/admin
[grid@node1 admin]$ 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=/oracle/app/11.2/grid)
(SID_NAME = +ASM1)
)
)
cat tnsname.ora
ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = node2 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
# (SID_NAME = +ASM1)
)
)
3. 配置exttract进程中,添加tranlog的时候,需要写thread 2
[oracle@node1 ogg]$ ggsci
GGSCI (node1) 1> create subdirs
Parameter files /oracle/app/oracle/ogg/dirprm: already exists
Report files /oracle/app/oracle/ogg/dirrpt: created
Checkpoint files /oracle/app/oracle/ogg/dirchk: created
Process status files /oracle/app/oracle/ogg/dirpcs: created
SQL script files /oracle/app/oracle/ogg/dirsql: created
Database definitions files /oracle/app/oracle/ogg/dirdef: created
Extract data files /oracle/app/oracle/ogg/dirdat: created
Temporary files /oracle/app/oracle/ogg/dirtmp: created
Stdout files /oracle/app/oracle/ogg/dirout: created
GGSCI (node1) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (node1) 6> edit params mgr
PORT 7809
GGSCI (node1) 7> start mgr
配置环境变量:
[oracle@node1 ogg]$ sqlplus / as sysdba
sys@RAC> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG NO YES
sys@RAC> alter database add supplemental log data;
Database altered.
sys@RAC> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG YES YES
SQL> alter database add supplemental log data;
Database altered.
SQL> alter database add supplemental log data (primary key) columns;
Database altered.
SQL> alter database add supplemental log data (foreign key) columns;
Database altered.
SQL> alter database add supplemental log data (unique) columns;
Database altered.
export GGHOME=$ORACLE_BASE/ogg
export PATH=$ORACLE_BASE/ogg:$ORACLE_HOME/bin:/usr/bin/:$PATH
创建用户及权限
create user ogg identified by ogg default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace to ogg;
grant connect ,resource,unlimited tablespace to ogg;
grant execute on utl_file to ogg;
grant select any dictionary,select any table to ogg;
grant alter any table to ogg;
grant flashback any table to ogg;
grant execute on dbms_flashback to ogg;
设置RAC的静态监听
#node1
10.3.4.13 node1.localdomain node1
10.3.4.14 node1-vip.localdomain node1-vip
192.168.56.13 node1-private.localdomain node1-private
#node2
10.3.4.15 node2.localdomain node2
10.3.4.16 node2-vip.localdomain node2-vip
192.168.56.15 node2-private.localdomain node2-private
su - grid
[grid@node1 admin]$ pwd
/oracle/app/11.2/grid/network/admin
[grid@node1 admin]$ 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=/oracle/app/11.2/grid)
(SID_NAME = +ASM1)
)
)
cat tnsname.ora
ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = node2 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
# (SID_NAME = +ASM1)
)
)
su - oracle
[oracle@node1 ~]$ sqlplus sys/oracle0101@asm as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 5 03:04:38 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
idle> show parameter name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name string +ASM
instance_name string +ASM1
lock_name_space string
service_names string +ASM
node2依此设置
设置源端的extract
GGSCI (node1) 10> dblogin userid ogg ,password ogg
Successfully logged into database.
ADD EXTRACT e1, TRANLOG, BEGIN NOW, THREADS 2
add exttrail /oracle/app/oracle/ogg/dirdat/e1, extract e1
edit params e1
extract e1
dynamicresolution
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle0101
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 20000
exttrail /oracle/app/oracle/ogg/dirdat/e1
table qn.t;
add extract p1 ,exttrailsource /oracle/app/oracle/ogg/dirdat/e1
add rmttrail /opt/ogg/dirdat/e1,extract p1
edit params p1
extract p1
userid ogg, password ogg
rmthost 10.3.4.110, mgrport 7809
rmttrail /opt/ogg/dirdat/e1
table qn.t;
GGSCI (node1) 35> add trandata qn.t
2016-12-05 00:37:10 WARNING OGG-00869 No unique key is defined for table 'T'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo log data is already enabled for table QN.T.
设置目标端replicat
add replicat r1, exttrail ./dirdat/e1, checkpointtable ogg.ckpt
edit params r1
replicat r1
ASSUMETARGETDEFS
userid ogg,password ogg
reperror default,discard
discardfile ./dirrpt/e1.dsc, append, megabytes 5
map qn.t, target qn.t;
测试
qn@RAC> insert into t select * from t ;
6 rows created.
qn@RAC> commit ;
Commit complete.
qn@R2> select * from t ;
ID NAME
---------- -----------------------------------
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
1 1
9 rows selected.
配置rac+ogg注意事项:
1. archive log ,需要通过TRANLOGOPTIONS 设置,否则会报错,如下:
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle0101
2. 需要设置asm实例的静态监听与tnsname.ora
[grid@node1 admin]$ pwd
/oracle/app/11.2/grid/network/admin
[grid@node1 admin]$ 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=/oracle/app/11.2/grid)
(SID_NAME = +ASM1)
)
)
cat tnsname.ora
ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
# (ADDRESS = (PROTOCOL = TCP)(HOST = node2 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
# (SID_NAME = +ASM1)
)
)
3. 配置exttract进程中,添加tranlog的时候,需要写thread 2