goldengate 12.2.0.1.1 for oracle logsource mode 测试
1.主机规划：cat /etc/hosts
127.0.0.1 localhost
192.168.1.87 dg1
192.168.1.88 dg2
dg1 上安装有基于文件系统的单实例数据库，归档模式
dg2 上安装有基于文件系统的单实例数据库，ALO模式可以只安装客户端即可，goldengate 软件安装在dg2上完成数据的抽取
2.首先配置dg1上的nfs服务,dg2上挂载dg1的online redo和archive log的目录
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 11 08:10:35 2017
Copyright (c) 1982, 2013, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 4182M
SQL>
$ mkdir arch
$ cd arch
$ pwd
/u01/app/oracle/arch
$ ls -l | grep arch
drwxr-xr-x.2 oracle oinstall 4096 Oct 11 08:08 arch
ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/oracle/arch/' SCOPE=spfile;
show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
shutdonw immediate;
startup
archvie log list;
dg1上配置：
root:
vi /etc/exports
/u01/app/oracle * (rw,no_root_squash,no_all_squash,sync)
save and exit
exportfs -r
linux 6.X
service nfs start
service rpcbind start
dg2:
root:
# mkdir -p /u02/app/oracle
# chown -R oracle:oinstall /u02/
# chmod -R 775 /u02/
# mount -t nfs dg1:/u01/app/oracle /u02/app/oracle/
3.配置dg2到dg1的tnsname解析：
dg2: netcaorcl1
$ cd $ORACLE_HOME/network/admin
$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl1)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
4.dg2安装goldengate 12.2.0.1 for oracle on linux 平台软件：
调整Oracle用户环境变量
# pwd
/root
# su - oracle
$ cat .bash_profile
# ---------------------------------------------------
# OS User: oracle
# Application:Oracle Database Software Owner
# Version: Oracle 11g release 2
# ---------------------------------------------------
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
ORACLE_SID=orcl1; export ORACLE_SID #another is dgh2
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"; export NLS_DATE_FORMAT
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/common/oracle/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH
THREADS_FLAG=native; export THREADS_FLAG
export TEMP=/tmp
export TMPDIR=/tmp
# ---------------------------------------------------
# UMASK
# ---------------------------------------------------
# Set the default file mode creation mask
# (umask) to 022 to ensure that the user performing
# the Oracle software installation creates files
# with 644 permissions.
# ---------------------------------------------------
umask 022
创建goldengate 安装目录，并调整权限
$ mkdir -p /u01/app/ogg
$ #### upload goldengate 12.2.0.1.1 soft V100692-01.zip to /u01/app/ogg directory
$
上传goldengate软件并安装截图参考同级截图文件夹
$ cd /u01/app/ogg
$ ls -l
total 464468
-rw-r--r--. 1 root root 475611228 Feb32016 V100692-01.zip
$ unzip V100692-01.zip
Archive:V100692-01.zip
creating: fbo_ggs_Linux_x64_shiphome/
creating: fbo_ggs_Linux_x64_shiphome/Disk1/
inflating: fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller
creating: fbo_ggs_Linux_x64_shiphome/Disk1/stage/
......中间内容略...........
inflating: OGG-12.2.0.1-README.txt
inflating: OGG-12.2.0.1.1-ReleaseNotes.pdf
$ ls -l
total 464752
drwxr-xr-x. 3 oracle oinstall 4096 Dec 122015 fbo_ggs_Linux_x64_shiphome
-rw-r--r--. 1 oracle oinstall 282294 Jan 182016 OGG-12.2.0.1.1-ReleaseNotes.pdf
-rw-r--r--. 1 oracle oinstall 1559 Jan 182016 OGG-12.2.0.1-README.txt
-rw-r--r--. 1 root root 475611228 Feb32016 V100692-01.zip
$ pwd
/u01/app/ogg
$ pwd
/u01/app/ogg
$ cd
$ vi .bash_profile
# ---------------------------------------------------
# OS User: oracle
# Application:Oracle Database Software Owner
# Version: Oracle 11g release 2
# ---------------------------------------------------
# Get the aliases and functions
. ~/.bashrc
fi
ORACLE_SID=orcl1; export ORACLE_SID #another is dgh2
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"; export NLS_DATE_FORMAT
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/common/oracle/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH
THREADS_FLAG=native; export THREADS_FLAG
export TEMP=/tmp
export TMPDIR=/tmp
# ---------------------------------------------------
# UMASK
# ---------------------------------------------------
# Set the default file mode creation mask
# (umask) to 022 to ensure that the user performing
# the Oracle software installation creates files
# with 644 permissions.
# ---------------------------------------------------
umask 022
~
".bash_profile" 43L, 1602C written
$ cat .bash_profile
# ---------------------------------------------------
# OS User: oracle
# Application:Oracle Database Software Owner
# Version: Oracle 11g release 2
# ---------------------------------------------------
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
export GGHOME=/u01/app/ogg
ORACLE_SID=orcl1; export ORACLE_SID #another is dgh2
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_TERM=xterm; export ORACLE_TERM
NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"; export NLS_DATE_FORMAT
TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN
PATH=.:${JAVA_HOME}/bin:${PATH}:$HOME/bin:$ORACLE_HOME/bin
PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
PATH=${PATH}:/u01/app/common/oracle/bin
export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:$ORACLE_HOME/oracm/lib
LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export CLASSPATH
THREADS_FLAG=native; export THREADS_FLAG
export TEMP=/tmp
export TMPDIR=/tmp
# ---------------------------------------------------
# UMASK
# ---------------------------------------------------
# Set the default file mode creation mask
# (umask) to 022 to ensure that the user performing
# the Oracle software installation creates files
# with 644 permissions.
# ---------------------------------------------------
umask 022
$ source .bash_profile
$ echo $GGHOME
/u01/app/ogg
$ exit
logout
# export DISPLAY=192.168.1.1:0.0
# xhost +
access control disabled, clients can connect from any host
# su - oracle
$ xhost +
access control disabled, clients can connect from any host
$
$
$
$
$
$
$ cd $GGHOME
$ ls
fbo_ggs_Linux_x64_shiphomeOGG-12.2.0.1.1-ReleaseNotes.pdfOGG-12.2.0.1-README.txtV100692-01.zip
$ cd fbo_ggs_Linux_x64_shiphome/
$ pwd
/u01/app/ogg/fbo_ggs_Linux_x64_shiphome
$ ls -l
total 4
drwxr-xr-x. 5 oracle oinstall 4096 Dec 122015 Disk1
$ cd Disk1/
$ pwd
/u01/app/ogg/fbo_ggs_Linux_x64_shiphome/Disk1
$ ls -l
total 16
drwxr-xr-x.4 oracle oinstall 4096 Dec 122015 install
drwxrwxr-x.2 oracle oinstall 4096 Dec 122015 response
-rwxr-xr-x.1 oracle oinstall918 Dec 122015 runInstaller
drwxr-xr-x. 11 oracle oinstall 4096 Dec 122015 stage
$ ./runInstaller
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 23871 MB Passed
Checking swap space: must be greater than 150 MB. Actual 1999 MB Passed
Checking monitor: must be configured to display at least 256 colors
>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<
Some requirement checks failed. You must fulfill these requirements before
continuing with the installation,
Continue? (y/n) y
>>> Ignoring required pre-requisite failures. Continuing...
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-10-11_09-15-48AM. Please wait ...$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2017-10-11_09-15-48AM.log
$
$
$
$ #OUI install capture picture reference another file
$
$
5. 登录ggsci确认mgr进程状态及参数并创建相关目录
$ 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 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (dg2) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (dg2) 2> view params mgr
PORT 7809
GGSCI (dg2) 3> exit
$ ls -l | grep dir*
$ 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 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (dg2) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (dg2) 2> create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter files /u01/app/ogg/dirprm: already exists
Report files /u01/app/ogg/dirrpt: already exists
Checkpoint files /u01/app/ogg/dirchk: already exists
Process status files /u01/app/ogg/dirpcs: already exists
SQL script files /u01/app/ogg/dirsql: already exists
Database definitions files /u01/app/ogg/dirdef: already exists
Extract data files /u01/app/ogg/dirdat: already exists
Temporary files /u01/app/ogg/dirtmp: already exists
Credential store files /u01/app/ogg/dircrd: already exists
Masterkey wallet files /u01/app/ogg/dirwlt: already exists
Dump files /u01/app/ogg/dirdmp: already exists
GGSCI (dg2) 4> exit
6.调整位于dg1上的数据库实例orcl配置使其满足goldengate 软件需求，这里为sqlplus 连接串连接至源端数据库：
$
$
$ ######source database orcl modify ,sqlplus remote connect to orcl
$ tnsping orcl
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 11-OCT-2017 09:23:02
Copyright (c) 1997, 2013, Oracle.All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))
OK (0 msec)
$
$
$
$
$ sqlplus system/oracle@orcl
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 11 09:23:18 2017
Copyright (c) 1982, 2013, Oracle.All rights reserved.
ERROR:
ORA-28002: the password will expire within 7 days
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>
SQL>
SQL> archive log list;
ORA-01031: insufficient privileges
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ sqlplus sys/oracle@orcl as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 11 09:23:45 2017
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> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch/
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
SQL>
SQL>
SQL> desc v$instance;
Name Null? Type
----------------------------------------- -------- ----------------------------
INSTANCE_NUMBER NUMBER
INSTANCE_NAME VARCHAR2(16)
HOST_NAME VARCHAR2(64)
VERSION VARCHAR2(17)
STARTUP_TIME DATE
STATUS VARCHAR2(12)
PARALLEL VARCHAR2(3)
THREAD# NUMBER
ARCHIVER VARCHAR2(7)
LOG_SWITCH_WAIT VARCHAR2(15)
LOGINS VARCHAR2(10)
SHUTDOWN_PENDING VARCHAR2(3)
DATABASE_STATUS VARCHAR2(17)
INSTANCE_ROLE VARCHAR2(18)
ACTIVE_STATE VARCHAR2(9)
BLOCKED VARCHAR2(3)
1* select host_name,instance_name from v$instance
SQL> /
HOST_NAME INSTANCE_NAME
-------------------- ----------------
dg1 orcl
SQL>
SQL>
SQL>
SQL> select username from dba_users;
USERNAME
------------------------------
HR
SQL>
SQL>
SQL>
SQL> 这里选择样例用户hr为复制测试用户
SQL>
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
SQL> alter database add supplemental log data;
Database altered.
SQL> select log_mode,force_logging from v$database;
LOG_MODE FOR
------------ ---
ARCHIVELOG YES
SQL> alter system switch logfile;
System altered.
SQL> show parameter goldengate
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
SQL> alter system set enable_goldengate_replication= true;
System altered.
SQL> show parameter enable_goldengate_replication
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
SQL>
SQL>
SQL>
7.源端数据库创建goldengate管理用户ogg:
SQL> create user ogg identified by oracle account unlock;
User created.
SQL> grant resource,dba,connect to ogg identified by oracle;
Grant succeeded.
SQL> exec dbms_streams_auth.grant_admin_privilege('ogg');
PL/SQL procedure successfully completed.
SQL> grant become user to ogg;
Grant succeeded.
SQL> exit
8.源端goldengate 复制用户hr用户下的表开启表级别附加日志：
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ hostname
dg2
$ pwd
/u01/app/ogg
$ 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 11g on Dec 12 2015 00:54:38
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
$ 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 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (dg2) 1> dblogin userid ogg@orcl,password oracle
注意这里连接的数据库为源端数据库
Successfully logged into database.
GGSCI (dg2 as ogg@orcl) 2> add schematrandata hr
2017-10-11 09:31:23INFO OGG-01788SCHEMATRANDATA has been added on schema hr.
2017-10-11 09:31:23INFO OGG-01976SCHEMATRANDATA for scheduling columns has been added on schema hr.
GGSCI (dg2 as ogg@orcl) 3> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (dg2 as ogg@orcl) 4>
9.配置抽取进程:
GGSCI (dg2 as ogg@orcl) 5> view params ext1
EXTRACT ext1
EXTTRAIL ./dirdat/er
userid ogg@orcl, password oracle
WILDCARDRESOLVE DYNAMIC
DYNAMICRESOLUTION
TRANLOGOPTIONS LOGSOURCE LINUX, PATHMAP /u01/app/oracle/oradata/orcl /u02/app/oracle/oradata/orcl
--TRANLOGOPTIONS LOGSOURCE LINUX, PATHMAP /u01/app/oracle/oradata/orcl /u02/app/oracle/oradata/orcl1 PATHMAP /u01/app/oracle/arch /u02/app/oracle/arch
TRANLOGOPTIONS ALTARCHIVELOGDEST /u02/app/oracle/arch
table hr.*;
GGSCI (dg2 as ogg@orcl) 6> dblogin userid ogg@orcl, password oracle
Successfully logged into database.
GGSCI (dg2 as ogg@orcl) 7> ADD EXTRACT ext1, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (dg2 as ogg@orcl) 8> ADD EXTTRAIL ./dirdat/er, EXTRACT ext1, MEGABYTES 10
EXTTRAIL added.
GGSCI (dg2 as ogg@orcl) 9> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:00:12
GGSCI (dg2) 8> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:00:01
10.启动抽取进程
GGSCI (dg2) 9> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (dg2) 10> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:08
11.由于源端数据库未执行事务操作并提交所以未有事务抽取：
GGSCI (dg2) 11> stats ext1
Sending STATS request to EXTRACT EXT1 ...
No active extraction maps.
12.登录源端数据库执行DML操作后提交以测试事务是否被正常抽取：
GGSCI (dg2) 12> exit
$ sqlplus system/oracle@orcl
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 11 09:47:34 2017
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> alter user hr identified by hr account unlock;
User altered.
SQL> exit
$ sqlplus hr/hr@orcl
SQL*Plus: Release 11.2.0.4.0 Production on Wed Oct 11 09:48:30 2017
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 tname from tab;
TNAME
------------------------------
COUNTRIES
DEPARTMENTS
EMPLOYEES
EMP_DETAILS_VIEW
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
8 rows selected.
SQL> desc employees;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
SQL> update employees set salary=2*salary;
107 rows updated.
SQL> commit;
Commit complete.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
13.登录ggsci查询goldengate是否正常抽取相关事务：
$ 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 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (dg2) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:02
GGSCI (dg2) 2> stats ext1
Sending STATS request to EXTRACT EXT1 ...
Start of Statistics at 2017-10-11 09:49:35.
Output to ./dirdat/er:
Extracting from HR.EMPLOYEES to HR.EMPLOYEES:
*** Total statistics since 2017-10-11 09:49:27 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
*** Daily statistics since 2017-10-11 09:49:27 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
*** Hourly statistics since 2017-10-11 09:49:27 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
*** Latest statistics since 2017-10-11 09:49:27 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
End of Statistics.
事务正常抽取
GGSCI (dg2) 3> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:07
14. info ext1 detail 命令查看发现事务读取的为源端数据库挂载过来的在线日志文件，（和归档日志文件）
GGSCI (dg2) 4> info ext1 detail
EXTRACT EXT1 Last Started 2017-10-11 09:44 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Process ID 9129
Log Read CheckpointOracle Redo Logs
2017-10-11 09:51:16Seqno 10, RBA 1351680
SCN 0.1009416 (1009416)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
./dirdat/er 1 17511 10 EXTTRAIL
Extract Source Begin End
/u02/app/oracle/oradata/orcl/redo01.log2017-10-11 09:442017-10-11 09:51
/u02/app/oracle/oradata/orcl/redo01.log* Initialized * 2017-10-11 09:44
/u02/app/oracle/oradata/orcl/redo01.log* Initialized * 2017-10-11 09:44
/u02/app/oracle/oradata/orcl/redo01.log2017-10-11 09:332017-10-11 09:33
Not Available * Initialized * 2017-10-11 09:33
Not Available * Initialized * 2017-10-11 09:33
Not Available * Initialized * 2017-10-11 09:33
Current directory /u01/app/ogg
Report file /u01/app/ogg/dirrpt/EXT1.rpt
Parameter file /u01/app/ogg/dirprm/ext1.prm
Checkpoint file /u01/app/ogg/dirchk/EXT1.cpe
Process file /u01/app/ogg/dirpcs/EXT1.pce
Error log /u01/app/ogg/ggserr.log
GGSCI (dg2) 5> shell tail -40 ggserr.log
2017-10-11 09:44:57INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start ext1.
2017-10-11 09:44:57INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host :34959 (START EXTRACT EXT1 ).
2017-10-11 09:44:57INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #5).
2017-10-11 09:44:57INFO OGG-00975Oracle GoldenGate Manager for Oracle, mgr.prm:EXTRACT EXT1 starting.
2017-10-11 09:44:57INFO OGG-00992Oracle GoldenGate Capture for Oracle, ext1.prm:EXTRACT EXT1 starting.
2017-10-11 09:44:57INFO OGG-03059Oracle GoldenGate Capture for Oracle, ext1.prm:Operating system character set identified as US-ASCII.
2017-10-11 09:44:57INFO OGG-02695Oracle GoldenGate Capture for Oracle, ext1.prm:ANSI SQL parameter syntax is used for parameter parsing.
2017-10-11 09:44:57WARNING OGG-10173Oracle GoldenGate Capture for Oracle, ext1.prm:(ext1.prm) line 5: Parsing error, is deprecated.
2017-10-11 09:44:57WARNING OGG-10173Oracle GoldenGate Capture for Oracle, ext1.prm:(ext1.prm) line 4: Parsing error, is deprecated.
2017-10-11 09:44:57INFO OGG-03522Oracle GoldenGate Capture for Oracle, ext1.prm:Setting session time zone to source database time zone 'GMT'.
2017-10-11 09:44:58INFO OGG-01635Oracle GoldenGate Capture for Oracle, ext1.prm:BOUNDED RECOVERY: reset to initial or altered checkpoint.
2017-10-11 09:44:58INFO OGG-01815Oracle GoldenGate Capture for Oracle, ext1.prm:Virtual Memory Facilities for: BR
anon alloc: mmap(MAP_ANON)anon free: munmap
file alloc: mmap(MAP_SHARED)file free: munmap
target directories:
/u01/app/ogg/BR/EXT1.
2017-10-11 09:44:58INFO OGG-01851Oracle GoldenGate Capture for Oracle, ext1.prm:filecaching started: thread ID: 140603485894400.
2017-10-11 09:44:58INFO OGG-01815Oracle GoldenGate Capture for Oracle, ext1.prm:Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON)anon free: munmap
file alloc: mmap(MAP_SHARED)file free: munmap
target directories:
/u01/app/ogg/dirtmp.
2017-10-11 09:44:58INFO OGG-02089Oracle GoldenGate Capture for Oracle, ext1.prm:Source redo compatibility version is: 11.2.0.4.0.
2017-10-11 09:44:58INFO OGG-01515Oracle GoldenGate Capture for Oracle, ext1.prm:Positioning to begin time Oct 11, 2017 9:44:51 AM.
2017-10-11 09:44:58INFO OGG-01516Oracle GoldenGate Capture for Oracle, ext1.prm:Positioned to Sequence 10, RBA 909840, SCN 0.0 (0), Oct 11, 2017 9:44:51 AM.
2017-10-11 09:44:58INFO OGG-00993Oracle GoldenGate Capture for Oracle, ext1.prm:EXTRACT EXT1 started.
2017-10-11 09:44:58INFO OGG-01055Oracle GoldenGate Capture for Oracle, ext1.prm:Recovery initialization completed for target file ./dirdat/er000000000, at RBA 1366.
2017-10-11 09:44:58INFO OGG-01478Oracle GoldenGate Capture for Oracle, ext1.prm:Output file ./dirdat/er is using format RELEASE 12.2.
2017-10-11 09:44:58INFO OGG-01026Oracle GoldenGate Capture for Oracle, ext1.prm:Rolling over remote file ./dirdat/er000000000.
2017-10-11 09:44:58INFO OGG-01053Oracle GoldenGate Capture for Oracle, ext1.prm:Recovery completed for target file ./dirdat/er000000001, at RBA 1425.
2017-10-11 09:44:58INFO OGG-01057Oracle GoldenGate Capture for Oracle, ext1.prm:Recovery completed for all targets.
2017-10-11 09:44:58INFO OGG-01517Oracle GoldenGate Capture for Oracle, ext1.prm:Position of first record processed Sequence 10, RBA 909840, SCN 0.1008794 (1008794), Oct 11, 2017 9:44:57 AM.
2017-10-11 09:44:59INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2017-10-11 09:47:10INFO OGG-01021Oracle GoldenGate Capture for Oracle, ext1.prm:Command received from GGSCI: STATS.
2017-10-11 09:49:26INFO OGG-06508Oracle GoldenGate Capture for Oracle, ext1.prm:Wildcard MAP (TABLE) resolved (entry hr.*): table "HR"."EMPLOYEES".
2017-10-11 09:49:27INFO OGG-06509Oracle GoldenGate Capture for Oracle, ext1.prm:Using the following key columns for source table HR.EMPLOYEES: EMPLOYEE_ID.
2017-10-11 09:49:30INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2017-10-11 09:49:35INFO OGG-01021Oracle GoldenGate Capture for Oracle, ext1.prm:Command received from GGSCI: STATS.
2017-10-11 09:51:44INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2017-10-11 09:52:01INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info ext1 detail.
GGSCI (dg2) 6> view params ext1
EXTRACT ext1
EXTTRAIL ./dirdat/er
userid ogg@orcl, password oracle
WILDCARDRESOLVE DYNAMIC
DYNAMICRESOLUTION
TRANLOGOPTIONS LOGSOURCE LINUX, PATHMAP /u01/app/oracle/oradata/orcl /u02/app/oracle/oradata/orcl
--TRANLOGOPTIONS LOGSOURCE LINUX, PATHMAP /u01/app/oracle/oradata/orcl /u02/app/oracle/oradata/orcl PATHMAP /u01/app/oracle/arch /u02/app/oracle/arch
TRANLOGOPTIONS ALTARCHIVELOGDEST /u02/app/oracle/arch
table hr.*;
GGSCI (dg2) 7> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:04
GGSCI (dg2) 8>
GGSCI (dg2) 8>
GGSCI (dg2) 8>
GGSCI (dg2) 8>
GGSCI (dg2) 8>
GGSCI (dg2) 8>
GGSCI (dg2) 8>
GGSCI (dg2) 8>
GGSCI (dg2) 8>
页:
[1]