ORACLE RAC 11204 到ORACLE 单机 ogg安装 dml/ddl

目录

 

1、准备tnsnames并正常连接源，目标，ASM 

2、解压ogg软件

3、配置源与目标的环境变量

4、在源与目标创建所需求的目录

 5、配置mgr配置文件

6、在源配置执行所需要的用户与包环境 

8、在源端配置抽取与投送配置文件

9、在目标库配置所需要的用户与包环境

11、在目标配置接收进程配置文件

12、启动复制

13、测试

14、日志文件

---

1、准备tnsnames并正常连接源，目标，ASM 

[oracle@rac1 admin]$ sqlplus sys/Oracle123@ASM as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 14 20:56:50 2020

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

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
+ASM1

SQL> 
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
[oracle@rac1 admin]$ sqlplus sys/Oracle123@orcl as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 14 20:56:56 2020

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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl1

2、解压ogg软件

3、配置源与目标的环境变量

[oracle@rac1 ~]$
cat>>/home/oracle/.bash_profile<<EOF
export OGG_HOME=/u01/ogg
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib:\$ORACLE_HOME/rdbms/lib
export PATH=\$PATH:\$ORACLE_HOME/bin:\$OGG_HOME
EOF

[oracle@oracle11g ~]$ cat>>/home/oracle/.bash_profile<<EOF
> export OGG_HOME=/u01/ogg
> export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib:\$ORACLE_HOME/rdbms/lib
> export PATH=\$PATH:\$ORACLE_HOME/bin:\$OGG_HOME
> EOF

4、在源与目标创建所需求的目录

 mkdir -p /u01/ogg/dirrpt/
 mkdir -p /u01/ogg/dirpcs/
 mkdir -p /u01/ogg/dirtmp
 mkdir -p /u01/ogg/dirdat/
 mkdir -p /u01/ogg/dirchk/

 5、配置mgr配置文件

/u01/ogg/dirprm/mgr.prm

PORT 7808
autostart er * 
autorestart er *

[grid@rac1 ogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.33 22986205 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_160502.0200_FBO
Linux, x64, 64bit (optimized), Oracle 11g on May  2 2016 06:30:42

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



GGSCI (rac1) 1> edit params mgr
PORT 7808
autostart er * 
autorestart er *

GGSCI (rac1) 4> view params mgr

PORT 7808
autostart er * 
autorestart er *



[grid@rac1 ogg]$ cat /u01/ogg/dirprm/mgr.prm
PORT 7808
autostart er * 
autorestart er *

[oracle@oracle11g ogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.33 22986205 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_160502.0200_FBO
Linux, x64, 64bit (optimized), Oracle 11g on May  2 2016 06:30:42

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



GGSCI (oracle11g) 1>  edit params mgr
PORT 7808
autostart er *
autorestart er *


GGSCI (oracle11g) 2> view params mgr

PORT 7808
autostart er * 
autorestart er *


GGSCI (oracle11g) 3> exit
[oracle@oracle11g ogg]$ cat /u01/ogg/dirprm/mgr.prm
PORT 7808
autostart er * 
autorestart er *
[oracle@oracle11g ogg]$ 

6、在源配置执行所需要的用户与包环境 

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 14 20:33:19 2020

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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create user ogg identified by ogg;

User created.

SQL> grant connect,resource,unlimited tablespace to ogg;
Grant succeeded.



SQL> create tablespace ogg datafile '+datadg' size 1g autoextend off;

Tablespace created.

SQL> SQL> alter user ogg default tablespace ogg;
User altered.

SQL> select username,default_tablespace from dba_users where username='OGG';
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
OGG                            OGG

SQL> 

SQL>  @sequence.sql
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.

SQL> alter system archive log current;

System altered.

SQL> grant execute on utl_file to ogg;

Grant succeeded.

SQL> @marker_setup.sql

SQL> @ddl_setup .sql

SQL> @role_setup.sql

SQL> grant ggs_ggsuser_role to ogg;

Grant succeeded.
 
SQL> @ddl_enable.sql

Trigger altered.
 
SQL> @ddl_pin ogg

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system set enable_goldengate_replication=true scope=both;

System altered.

8、在源端配置抽取与投送配置文件

抽取

cat /u01/ogg/dirprm/testext.prm 
EXTRACT testext
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
USERID ogg@rac, PASSWORD ogg
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD Oracle123
EXTTRAIL ./dirdat/et
DYNAMICRESOLUTION
DDL INCLUDE ALL
TABLE test.*;

[oracle@rac1 ogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.33 22986205 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_160502.0200_FBO
Linux, x64, 64bit (optimized), Oracle 11g on May  2 2016 06:30:42

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



GGSCI (rac1) 1> dblogin userid ogg password ogg
Successfully logged into database.



GGSCI (rac1) 3> add extract testext,tranlog,begin now,threads 2
EXTRACT added.


GGSCI (rac1) 4> add exttrail ./dirdat/et,extract testext
EXTTRAIL added.


GGSCI (rac1) 5> edit params testext
EXTRACT testext
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
USERID ogg@rac, PASSWORD ogg
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD Oracle123
EXTTRAIL ./dirdat/et
DYNAMICRESOLUTION
DDL INCLUDE ALL
TABLE test.*;

GGSCI (rac1) 6> view params testext

EXTRACT testext
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
USERID ogg@rac, PASSWORD ogg
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD Oracle123
EXTTRAIL ./dirdat/et
DYNAMICRESOLUTION
DDL INCLUDE ALL
TABLE test.*;

[oracle@rac1 dirprm]$ cat /u01/ogg/dirprm/testext.prm 
EXTRACT testext
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
USERID ogg@rac, PASSWORD ogg
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD Oracle123
EXTTRAIL ./dirdat/et
DYNAMICRESOLUTION
DDL INCLUDE ALL
TABLE test.*;

投送

cat /u01/ogg/dirprm/testpump.prm 
EXTRACT testpump
rmthost 192.168.33.15, mgrport 7808
rmttrail ./dirdat/rt
passthru
table test.*;

 

GGSCI (rac1) 7> add extract testpump,exttrailsource ./dirdat/et,begin now
EXTRACT added.


GGSCI (rac1) 8> add rmttrail ./dirdat/rt,extract testpump
RMTTRAIL added.


GGSCI (rac1) 9> edit params testpump
EXTRACT testpump
rmthost 192.168.33.15, mgrport 7808
rmttrail ./dirdat/rt
passthru
table test.*;

[oracle@rac1 dirprm]$ cat /u01/ogg/dirprm/testpump.prm 
EXTRACT testpump
rmthost 192.168.33.15, mgrport 7808
rmttrail ./dirdat/rt
passthru
table test.*;

 

GGSCI (rac1) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           
EXTRACT     STOPPED     TESTEXT     00:00:00      00:07:47    
EXTRACT     STOPPED     TESTPUMP    00:00:00      00:01:35    

9、在目标库配置所需要的用户与包环境

 

[oracle@oracle11g ogg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 14 21:02:09 2020

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 Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user ogg identified by ogg;

User created.

SQL> grant connect,resource,dba to ogg;

Grant succeeded.

SQL> create user test identified by test;

User created.

SQL> grant connect ,resource to test;

Grant succeeded.

SQL> alter system set enable_goldengate_replication=true scope=both;

System altered.

SQL> 

11、在目标配置接收进程配置文件

 cat /u01/ogg/dirprm/testrpt.prm
REPLICAT testrpt
setenv (ORACLE_SID=orcl)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2/db")
USERID ogg@orcl,PASSWORD ogg
ASSUMETARGETDEFS
HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE ./repsz.dsc,append,megabytes 100
MAP test.*, TARGET test.*; 

[oracle@oracle11g ogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.33 22986205 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_160502.0200_FBO
Linux, x64, 64bit (optimized), Oracle 11g on May  2 2016 06:30:42

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



GGSCI (oracle11g) 1> dblogin userid ogg password ogg
Successfully logged into database.

GGSCI (oracle11g) 2> add replicat testrpt,exttrail ./dirdat/rt,nodbcheckpoint
ERROR: Could not create checkpoint file /u01/ogg/dirchk/TESTRPT.cpr (error 2, No such file or directory).


GGSCI (oracle11g) 3> ^Cadd replicat testrpt,exttrail ./dirdat/rt,nodbcheckpoint
REPLICAT added.


GGSCI (oracle11g) 4> edit params testrpt
REPLICAT testrpt
setenv (ORACLE_SID=orcl)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2/db")
USERID ogg@orcl,PASSWORD ogg
ASSUMETARGETDEFS
HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE ./repsz.dsc,append,megabytes 100
MAP test.*, TARGET test.*; 

~
"dirprm/testrpt.prm" [New] 11L, 306C written

GGSCI (oracle11g) 6> view params testrpt

REPLICAT testrpt
setenv (ORACLE_SID=orcl)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2/db")
USERID ogg@orcl,PASSWORD ogg
ASSUMETARGETDEFS
HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE ./repsz.dsc,append,megabytes 100
MAP test.*, TARGET test.*; 



[oracle@oracle11g admin]$ cat /u01/ogg/dirprm/testrpt.prm
REPLICAT testrpt
setenv (ORACLE_SID=orcl)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2/db")
USERID ogg@orcl,PASSWORD ogg
ASSUMETARGETDEFS
HANDLECOLLISIONS
REPERROR (DEFAULT, DISCARD)
DDLERROR DEFAULT DISCARD
DDLOPTIONS REPORT
DISCARDFILE ./repsz.dsc,append,megabytes 100
MAP test.*, TARGET test.*; 




GGSCI (oracle11g) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           
REPLICAT    STOPPED     TESTRPT     00:00:00      00:03:11    


GGSCI (oracle11g) 6> 

 

12、启动复制

启动源与目标的mgr->启动ext->启动pump

 

GGSCI (rac1) 25> start mgr
GGSCI (rac1) 35> start TESTEXT 
GGSCI (rac1) 36> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     TESTEXT     00:22:12      00:00:09    
EXTRACT     STOPPED     TESTPUMP    00:00:00      00:16:45 



GGSCI (oracle11g) 11> start mgr
GGSCI (oracle11g) 13> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     TESTRPT     00:00:00      00:00:03 



GGSCI (rac1) 37> start TESTPUMP
GGSCI (rac1) 38> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     TESTEXT     00:00:00      00:00:08    
EXTRACT     RUNNING     TESTPUMP    00:00:00      00:00:02    

13、测试

源对test用户表与数据

[oracle@rac1 ogg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 14 21:20:11 2020

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 Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> 
SQL> 
SQL>  create table test.test(id int,sdate char(20));

Table created.

SQL> 
SQL> 
SQL> begin
  2  for i in 1 .. 100
  3  loop
  4  insert into test.test values (i,  to_char(current_timestamp at time zone dbtimezone,'dd-mon-rr hh:mi:ss'));
  5  commit;
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

目标查询

[root@oracle11g ~]# su - oracle
[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 14 21:21:39 2020

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 Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from test.test;

  COUNT(*)
----------
       100

SQL> 

14、日志文件

[oracle@rac1 ogg]$ tail -f ggserr.log 
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /u01/ogg/dirtmp.
2020-04-14 21:18:17  WARNING OGG-01015  Oracle GoldenGate Capture for Oracle, testpump.prm:  Positioning with begin time: Apr 14, 2020 9:00:03 PM, waiting for data: at extseqno 0, extrba 0.
2020-04-14 21:18:17  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, testpump.prm:  EXTRACT TESTPUMP started.
2020-04-14 21:18:22  INFO    OGG-01226  Oracle GoldenGate Capture for Oracle, testpump.prm:  Socket buffer size set to 27985 (flush size 27985).
2020-04-14 21:18:22  INFO    OGG-01052  Oracle GoldenGate Capture for Oracle, testpump.prm:  No recovery is required for target file ./dirdat/rt000000, at RBA 0 (file not opened).
2020-04-14 21:18:22  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, testpump.prm:  Output file ./dirdat/rt is using format RELEASE 11.2.
2020-04-14 21:18:24  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.
2020-04-14 21:21:20  WARNING OGG-00869  Oracle GoldenGate Capture for Oracle, testext.prm:  No unique key is defined for table 'TEST'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.


[oracle@oracle11g ogg]$ tail -f ggserr.log 
2020-04-14 21:18:29  INFO    OGG-01229  Oracle GoldenGate Collector for Oracle:  Connected to :63983.
2020-04-14 21:18:29  INFO    OGG-01669  Oracle GoldenGate Collector for Oracle:  Opening ./dirdat/rt000000 (byte -1, current EOF 0).
2020-04-14 21:21:01  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, testrpt.prm:  DDL found, operation [ create table test.test(id int,sdate char(20))  (size 47)].
2020-04-14 21:21:01  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, testrpt.prm:  DDL is of mapped scope, after mapping new operation [ create table test."TEST"(id int,sdate char(20))  (size 49)].
2020-04-14 21:21:01  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, testrpt.prm:  Setting current schema for DDL operation to [SYS].
2020-04-14 21:21:01  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, testrpt.prm:  Executing DDL operation.
2020-04-14 21:21:01  INFO    OGG-00483  Oracle GoldenGate Delivery for Oracle, testrpt.prm:  DDL operation successful.
2020-04-14 21:21:01  INFO    OGG-01408  Oracle GoldenGate Delivery for Oracle, testrpt.prm:  Restoring current schema for DDL operation to [ogg].
2020-04-14 21:21:31  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, testrpt.prm:  No unique key is defined for table 'TEST'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
2020-04-14 21:21:31  WARNING OGG-03504  Oracle GoldenGate Delivery for Oracle, testrpt.prm:  NLS_LANG character set AL32UTF8 on the target is different from the source database character set ZHS16GBK. Replication may not be valid if the source data has an incompatible character for the target NLS_LANG character set.

 

报错：

[grid@rac1 ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.33 22986205 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_160502.0200_FBO
Linux, x64, 64bit (optimized), Oracle 11g on May  2 2016 06:30:42

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



GGSCI (rac1) 1> edit params mgr
Cannot load ICU resource bundle 'ggMessage', error code 2 - No such file or directory
Cannot load ICU resource bundle 'ggMessage', error code 2 - No such file or directory
Aborted (core dumped)


必须进入目录

[grid@rac1 ~]$ cd /u01/ogg/
[grid@rac1 ogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.33 22986205 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_160502.0200_FBO
Linux, x64, 64bit (optimized), Oracle 11g on May  2 2016 06:30:42

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