goldengate 12.2 oracle 12.1 隐藏列复制测试实践操作
环境配置：Installing and Configuring Oracle GoldenGate for Oracle Database
12c (12.2.0.1)
1.7.4 Non-supported Objects and Operations in Oracle DML
The following are additional Oracle objects or operations that are not supported by
Extract in either classic or integrated capture mode:
? REF
? Sequence values in an active-active bi-directional configuration
? Database Replay
? Tables created as EXTERNAL
? Invisible columns are not supported by classic Extract #############
$ exit
logout
# uname -a
Linux node1 2.6.32-358.el6.x86_64 #1 SMP Tue Jan 29 11:47:41 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
# arch
x86_64
# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.4 (Santiago)
#
#
#
#
$ cd $GGHOME
$ ggsci -v
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
$ exit
logout
# sqlplsu /nolog
-bash: sqlplsu: command not found
# sqlplus /nolog
-bash: sqlplus: command not found
# su - oracle
$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 09:56:53 2017
Copyright (c) 1982, 2014, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
oracle用户环境变量：
# su - oracle
$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export GGHOME=/u01/ogg12c
export PATH=/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:.
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.1.0
export ORACLE_SID=dev12c
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$GGHOME:$ORACLE_HOME/bin:$PATH
$
$
检查goldengate状态：
$ cd $GGHOME
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT1 00:00:11 4249:05:05
REPLICAT STOPPED REP1 00:00:00 4249:05:04
GGSCI (node1) 2> view params ext1
EXTRACT ext1
USERID c##ggadmin, PASSWORD oracle
EXTTRAIL ./dirdat/ex
SOURCECATALOG pdb1
TABLE TEST.*;
GGSCI (node1) 3> view params rep1
REPLICAT rep1
USERID c##ggadmin@pdb2, password oracle
ASSUMETARGETDEFS
MAP pdb1.test.*, TARGET pdb2.test.*;
GGSCI (node1) 4>
GGSCI (node1) 4>
GGSCI (node1) 4>
GGSCI (node1) 4>
GGSCI (node1) 4>
GGSCI (node1) 4>
GGSCI (node1) 4>
GGSCI (node1) 4> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED EXT1 00:00:11 4249:05:34
REPLICAT STOPPED REP1 00:00:00 4249:05:33
GGSCI (node1) 5> start mgr
Manager started.
GGSCI (node1) 6> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:11 4249:05:37
REPLICAT STOPPED REP1 00:00:00 4249:05:36
GGSCI (node1) 7> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (node1) 8> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT STARTING EXT1 00:00:11 4249:05:42
REPLICAT STOPPED REP1 00:00:00 4249:05:42
GGSCI (node1) 9> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (node1) 10> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT STARTING EXT1 00:00:11 4249:05:47
REPLICAT STARTING REP1 00:00:00 4249:05:47
GGSCI (node1) 11> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:02 00:00:06
REPLICAT STOPPED REP1 00:00:00 4249:06:47
GGSCI (node1) 12> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:09 00:00:03
REPLICAT STOPPED REP1 00:00:00 4249:07:44
GGSCI (node1) 13> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (node1) 14> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:09 00:00:00
REPLICAT STARTING REP1 00:00:00 4249:07:51
GGSCI (node1) 15> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:09 00:00:03
REPLICAT RUNNING REP1 00:00:00 00:00:00
GGSCI (node1) 16> exit
$
$
$
$
$
停止复制关系，添加还有隐藏列新表：
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:07 00:00:09
REPLICAT RUNNING REP1 00:00:00 00:00:07
GGSCI (node1) 2> stop ext1
Sending STOP request to EXTRACT EXT1 ...
Request processed.
GGSCI (node1) 3> stop rep1
Sending STOP request to REPLICAT REP1 ...
Request processed.
GGSCI (node1) 4> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:04 00:00:06
REPLICAT STOPPED REP1 00:00:00 00:00:01
GGSCI (node1) 5> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:04 00:00:08
REPLICAT STOPPED REP1 00:00:00 00:00:03
GGSCI (node1) 6> exit
$
$
$
$
$
$
$ cd $ORACLE_HOME/network/admin
$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DEV12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dev12c)
)
)
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)
PDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb2)
)
)
$ sqlplus test/oracle@pdb1
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 09:47:02 2017
Copyright (c) 1982, 2014, Oracle.All rights reserved.
Last Successful login time: Fri Oct 07 2016 07:48:23 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select tname from tab;
TNAME
--------------------------------------------------------------------------------
SOURCE
SQL> create table t1 (empid number, salary number, commission number INVISIBLE generated always as (salary * .05) VIRTUAL );
Table created.
SQL> alter table t1 add constraint pk_test_ogg primary key (empid);
Table altered.
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NOT NULL NUMBER
SALARY NUMBER
SQL> SET COLINVISIBLE ON;
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NOT NULL NUMBER
SALARY NUMBER
COMMISSION (INVISIBLE) NUMBER
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$ cd $GGHOME
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> dblogin USERID c##ggadmin, PASSWORD oracle
Successfully logged into database CDB$ROOT.
GGSCI (node1 as c##ggadmin@dev12c/CDB$ROOT) 2> add trandata pdb1.test.t1
Logging of supplemental redo data enabled for table PDB1.TEST.T1.
TRANDATA for scheduling columns has been added on table 'PDB1.TEST.T1'.
TRANDATA for instantiation CSN has been added on table 'PDB1.TEST.T1'.
GGSCI (node1 as c##ggadmin@dev12c/CDB$ROOT) 3>
GGSCI (node1 as c##ggadmin@dev12c/CDB$ROOT) 3>
GGSCI (node1 as c##ggadmin@dev12c/CDB$ROOT) 3> exit
$
$
$
$
$ sqlplus test/oracle@pdb2
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 09:51:13 2017
Copyright (c) 1982, 2014, Oracle.All rights reserved.
Last Successful login time: Fri Oct 07 2016 07:49:43 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select tname from tab;
TNAME
--------------------------------------------------------------------------------
SOURCE
SQL> create table t1 (empid number, salary number, commission number INVISIBLE generated always as (salary * .05) VIRTUAL );
Table created.
SQL> alter table t1 add constraint pk_test_ogg primary key (empid);
Table altered.
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NOT NULL NUMBER
SALARY NUMBER
SQL> SET COLINVISIBLE ON;
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NOT NULL NUMBER
SALARY NUMBER
COMMISSION (INVISIBLE) NUMBER
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$
$
$
$
$
$ cd $GGHOME
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> dblogin USERID c##ggadmin@pdb2, password oracle
Successfully logged into database PDB2.
GGSCI (node1 as c##ggadmin@dev12c/PDB2) 2> add trandata pdb2.test.t1
Logging of supplemental redo data enabled for table PDB2.TEST.T1.
TRANDATA for scheduling columns has been added on table 'PDB2.TEST.T1'.
TRANDATA for instantiation CSN has been added on table 'PDB2.TEST.T1'.
GGSCI (node1 as c##ggadmin@dev12c/PDB2) 3>
GGSCI (node1 as c##ggadmin@dev12c/PDB2) 3>
GGSCI (node1 as c##ggadmin@dev12c/PDB2) 3>
GGSCI (node1 as c##ggadmin@dev12c/PDB2) 3>
GGSCI (node1 as c##ggadmin@dev12c/PDB2) 3>
GGSCI (node1 as c##ggadmin@dev12c/PDB2) 3>
GGSCI (node1 as c##ggadmin@dev12c/PDB2) 3>
GGSCI (node1 as c##ggadmin@dev12c/PDB2) 3> exit
$
$
$
$
$
$
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:04 00:07:18
REPLICAT STOPPED REP1 00:00:00 00:07:14
GGSCI (node1) 2> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (node1) 3> start ext2
ERROR: Invalid command.
GGSCI (node1) 4> start rep1
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (node1) 5> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:07:29 00:00:05
REPLICAT RUNNING REP1 00:00:00 00:00:01
GGSCI (node1) 6> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:07:29 00:00:07
REPLICAT RUNNING REP1 00:00:00 00:00:04
GGSCI (node1) 7> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:07:29 00:00:09
REPLICAT RUNNING REP1 00:00:00 00:00:05
GGSCI (node1) 8> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:02 00:00:01
REPLICAT RUNNING REP1 00:00:00 00:00:07
GGSCI (node1) 9> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:02 00:00:04
REPLICAT RUNNING REP1 00:00:00 00:00:00
GGSCI (node1) 10>
GGSCI (node1) 10>
GGSCI (node1) 10>
GGSCI (node1) 10>
GGSCI (node1) 10> exit
源端插入数据验证：
$ sqlplus test/oracle@pdb1
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 09:53:38 2017
Copyright (c) 1982, 2014, Oracle.All rights reserved.
Last Successful login time: Sun Apr 02 2017 09:47:02 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select count(*) from t1;
COUNT(*)
----------
0
SQL> insert into t1 values (1001, 10000);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from t1;
COUNT(*)
----------
1
SQL> select empid,salary,commission from t1;
EMPID SALARY COMMISSION
---------- ---------- ----------
1001 10000 500
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$
$
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> shell tail -200f ggserr.log
2
2017-04-02 09:53:16INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2017-04-02 09:53:19INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2017-04-02 09:53:20INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2017-04-02 09:53:22INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2017-04-02 09:53:25INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2017-04-02 09:54:33INFO OGG-06508Oracle GoldenGate Capture for Oracle, ext1.prm:Wildcard MAP (TABLE) resolved (entry pdb1.TEST.*): TABLE "PDB1"."TEST"."T1".
2017-04-02 09:54:33INFO OGG-06509Oracle GoldenGate Capture for Oracle, ext1.prm:Using the following key columns for source table PDB1.TEST.T1: EMPID.
2017-04-02 09:54:35INFO OGG-06506Oracle GoldenGate Delivery for Oracle, rep1.prm:Wildcard MAP resolved (entry pdb1.test.*): MAP "PDB1"."TEST"."T1", TARGET pdb2.test."T1".
2017-04-02 09:54:37INFO OGG-02756Oracle GoldenGate Delivery for Oracle, rep1.prm:The definition for table PDB1.TEST.T1 is obtained from the trail file.
2017-04-02 09:54:37INFO OGG-06511Oracle GoldenGate Delivery for Oracle, rep1.prm:Using following columns in default map by name: EMPID, SALARY.
2017-04-02 09:54:37INFO OGG-06510Oracle GoldenGate Delivery for Oracle, rep1.prm:Using the following key columns for target table PDB2.TEST.T1: EMPID.
^C
GGSCI (node1) 2>
GGSCI (node1) 2>
GGSCI (node1) 2>
GGSCI (node1) 2> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:02 00:00:00
REPLICAT RUNNING REP1 00:00:00 00:00:04
检查抽取复制进程统计量：
GGSCI (node1) 3> stats ext1
Sending STATS request to EXTRACT EXT1 ...
Start of Statistics at 2017-04-02 09:55:28.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 2.00
Output to ./dirdat/ex:
Extracting from PDB1.TEST.T1 to PDB1.TEST.T1:
*** Total statistics since 2017-04-02 09:54:33 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2017-04-02 09:54:33 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2017-04-02 09:54:33 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2017-04-02 09:54:33 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (node1) 4> exit
$
$
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:01 00:00:03
REPLICAT RUNNING REP1 00:00:00 00:00:08
GGSCI (node1) 2> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2017-04-02 09:55:40.
Replicating from PDB1.TEST.T1 to PDB2.TEST.T1:
*** Total statistics since 2017-04-02 09:54:37 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2017-04-02 09:54:37 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2017-04-02 09:54:37 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2017-04-02 09:54:37 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (node1) 3> exit
目标端验证：
$ sqlplus test/oracle@pdb2
SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 09:55:49 2017
Copyright (c) 1982, 2014, Oracle.All rights reserved.
Last Successful login time: Sun Apr 02 2017 09:51:13 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select tname from tab;
TNAME
--------------------------------------------------------------------------------
SOURCE
T1
SQL> select count(*) from t1;
COUNT(*)
----------
1
SQL> select empid,salary,commission from t1;
EMPID SALARY COMMISSION
---------- ---------- ----------
1001 10000 500
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
$
$
$
页:
[1]