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的目录
[oracle@dg1 ~]$ 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>
[oracle@dg1 oracle]$ mkdir arch
[oracle@dg1 oracle]$ cd arch
[oracle@dg1 arch]$ pwd
/u01/app/oracle/arch
[oracle@dg1 oracle]$ 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:
[root@dg2 ~]# mkdir -p /u02/app/oracle
[root@dg2 ~]# chown -R oracle install /u02/
[root@dg2 ~]# chmod -R 775 /u02/
[root@dg2 ~]# mount -t nfs dg1:/u01/app/oracle /u02/app/oracle/
3.配置dg2到dg1的tnsname解析:
dg2: netca orcl1
[oracle@dg2 ~]$ cd $ORACLE_HOME/network/admin
[oracle@dg2 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用户环境变量
[root@dg2 ~]# pwd
/root
[root@dg2 ~]# su - oracle
[oracle@dg2 ~]$ 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 安装目录,并调整权限
[oracle@dg2 ~]$ mkdir -p /u01/app/ogg
[oracle@dg2 ~]$ #### upload goldengate 12.2.0.1.1 soft V100692-01.zip to /u01/app/ogg directory
[oracle@dg2 ~]$
上传goldengate软件并安装截图参考同级截图文件夹
[oracle@dg2 ~]$ cd /u01/app/ogg
[oracle@dg2 ogg]$ ls -l
total 464468
-rw-r--r--. 1 root root 475611228 Feb 3 2016 V100692-01.zip
[oracle@dg2 ogg]$ 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
[oracle@dg2 ogg]$ ls -l
total 464752
drwxr-xr-x. 3 oracle oinstall 4096 Dec 12 2015 fbo_ggs_Linux_x64_shiphome
-rw-r--r--. 1 oracle oinstall 282294 Jan 18 2016 OGG-12.2.0.1.1-ReleaseNotes.pdf
-rw-r--r--. 1 oracle oinstall 1559 Jan 18 2016 OGG-12.2.0.1-README.txt
-rw-r--r--. 1 root root 475611228 Feb 3 2016 V100692-01.zip
[oracle@dg2 ogg]$ pwd
/u01/app/ogg
[oracle@dg2 ogg]$ pwd
/u01/app/ogg
[oracle@dg2 ogg]$ cd
[oracle@dg2 ~]$ 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
[oracle@dg2 ~]$ 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
[oracle@dg2 ~]$ source .bash_profile
[oracle@dg2 ~]$ echo $GGHOME
/u01/app/ogg
[oracle@dg2 ~]$ exit
logout
[root@dg2 ~]# export DISPLAY=192.168.1.1:0.0
[root@dg2 ~]# xhost +
access control disabled, clients can connect from any host
[root@dg2 ~]# su - oracle
[oracle@dg2 ~]$ xhost +
access control disabled, clients can connect from any host
[oracle@dg2 ~]$
[oracle@dg2 ~]$
[oracle@dg2 ~]$
[oracle@dg2 ~]$
[oracle@dg2 ~]$
[oracle@dg2 ~]$
[oracle@dg2 ~]$ cd $GGHOME
[oracle@dg2 ogg]$ ls
fbo_ggs_Linux_x64_shiphome OGG-12.2.0.1.1-ReleaseNotes.pdf OGG-12.2.0.1-README.txt V100692-01.zip
[oracle@dg2 ogg]$ cd fbo_ggs_Linux_x64_shiphome/
[oracle@dg2 fbo_ggs_Linux_x64_shiphome]$ pwd
/u01/app/ogg/fbo_ggs_Linux_x64_shiphome
[oracle@dg2 fbo_ggs_Linux_x64_shiphome]$ ls -l
total 4
drwxr-xr-x. 5 oracle oinstall 4096 Dec 12 2015 Disk1
[oracle@dg2 fbo_ggs_Linux_x64_shiphome]$ cd Disk1/
[oracle@dg2 Disk1]$ pwd
/u01/app/ogg/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@dg2 Disk1]$ ls -l
total 16
drwxr-xr-x. 4 oracle oinstall 4096 Dec 12 2015 install
drwxrwxr-x. 2 oracle oinstall 4096 Dec 12 2015 response
-rwxr-xr-x. 1 oracle oinstall 918 Dec 12 2015 runInstaller
drwxr-xr-x. 11 oracle oinstall 4096 Dec 12 2015 stage
[oracle@dg2 Disk1]$ ./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) [n] y
>>> Ignoring required pre-requisite failures. Continuing...
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-10-11_09-15-48AM. Please wait ...[oracle@dg2 Disk1]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2017-10-11_09-15-48AM.log
[oracle@dg2 Disk1]$
[oracle@dg2 Disk1]$
[oracle@dg2 Disk1]$
[oracle@dg2 Disk1]$ #OUI install capture picture reference another file
[oracle@dg2 Disk1]$
[oracle@dg2 Disk1]$
5. 登录ggsci确认mgr进程状态及参数并创建相关目录
[oracle@dg2 Disk1]$ cd $GGHOME
[oracle@dg2 ogg]$ 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 Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (dg2) 2> view params mgr
PORT 7809
GGSCI (dg2) 3> exit
[oracle@dg2 ogg]$ ls -l | grep dir*
[oracle@dg2 ogg]$ 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 Chkpt Time 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 连接串连接至源端数据库:
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$ ######source database orcl modify ,sqlplus remote connect to orcl
[oracle@dg2 ogg]$ 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)
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$ 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
[oracle@dg2 ogg]$ 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
[oracle@dg2 ogg]$ hostname
dg2
[oracle@dg2 ogg]$ pwd
/u01/app/ogg
[oracle@dg2 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.
[oracle@dg2 ogg]$ 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:23 INFO OGG-01788 SCHEMATRANDATA has been added on schema hr.
2017-10-11 09:31:23 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema hr.
GGSCI (dg2 as ogg@orcl) 3> info all
Program Status Group Lag at Chkpt Time 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 Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:00:12
GGSCI (dg2) 8> info all
Program Status Group Lag at Chkpt Time 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 Chkpt Time 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
[oracle@dg2 ogg]$ 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
[oracle@dg2 ogg]$ 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是否正常抽取相关事务:
[oracle@dg2 ogg]$ 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 Chkpt Time 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 Chkpt Time 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 Checkpoint Oracle Redo Logs
2017-10-11 09:51:16 Seqno 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.log 2017-10-11 09:44 2017-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.log 2017-10-11 09:33 2017-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:57 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start ext1.
2017-10-11 09:44:57 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host [192.168.1.88]:34959 (START EXTRACT EXT1 ).
2017-10-11 09:44:57 INFO OGG-00960 Oracle GoldenGate Manager for Oracle, mgr.prm: Access granted (rule #5).
2017-10-11 09:44:57 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EXT1 starting.
2017-10-11 09:44:57 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, ext1.prm: EXTRACT EXT1 starting.
2017-10-11 09:44:57 INFO OGG-03059 Oracle GoldenGate Capture for Oracle, ext1.prm: Operating system character set identified as US-ASCII.
2017-10-11 09:44:57 INFO OGG-02695 Oracle GoldenGate Capture for Oracle, ext1.prm: ANSI SQL parameter syntax is used for parameter parsing.
2017-10-11 09:44:57 WARNING OGG-10173 Oracle GoldenGate Capture for Oracle, ext1.prm: (ext1.prm) line 5: Parsing error, [DYNAMICRESOLUTION] is deprecated.
2017-10-11 09:44:57 WARNING OGG-10173 Oracle GoldenGate Capture for Oracle, ext1.prm: (ext1.prm) line 4: Parsing error, [WILDCARDRESOLVE] is deprecated.
2017-10-11 09:44:57 INFO OGG-03522 Oracle GoldenGate Capture for Oracle, ext1.prm: Setting session time zone to source database time zone 'GMT'.
2017-10-11 09:44:58 INFO OGG-01635 Oracle GoldenGate Capture for Oracle, ext1.prm: BOUNDED RECOVERY: reset to initial or altered checkpoint.
2017-10-11 09:44:58 INFO OGG-01815 Oracle 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:58 INFO OGG-01851 Oracle GoldenGate Capture for Oracle, ext1.prm: filecaching started: thread ID: 140603485894400.
2017-10-11 09:44:58 INFO OGG-01815 Oracle 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:58 INFO OGG-02089 Oracle GoldenGate Capture for Oracle, ext1.prm: Source redo compatibility version is: 11.2.0.4.0.
2017-10-11 09:44:58 INFO OGG-01515 Oracle GoldenGate Capture for Oracle, ext1.prm: Positioning to begin time Oct 11, 2017 9:44:51 AM.
2017-10-11 09:44:58 INFO OGG-01516 Oracle 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:58 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, ext1.prm: EXTRACT EXT1 started.
2017-10-11 09:44:58 INFO OGG-01055 Oracle GoldenGate Capture for Oracle, ext1.prm: Recovery initialization completed for target file ./dirdat/er000000000, at RBA 1366.
2017-10-11 09:44:58 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, ext1.prm: Output file ./dirdat/er is using format RELEASE 12.2.
2017-10-11 09:44:58 INFO OGG-01026 Oracle GoldenGate Capture for Oracle, ext1.prm: Rolling over remote file ./dirdat/er000000000.
2017-10-11 09:44:58 INFO OGG-01053 Oracle GoldenGate Capture for Oracle, ext1.prm: Recovery completed for target file ./dirdat/er000000001, at RBA 1425.
2017-10-11 09:44:58 INFO OGG-01057 Oracle GoldenGate Capture for Oracle, ext1.prm: Recovery completed for all targets.
2017-10-11 09:44:58 INFO OGG-01517 Oracle 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:59 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
2017-10-11 09:47:10 INFO OGG-01021 Oracle GoldenGate Capture for Oracle, ext1.prm: Command received from GGSCI: STATS.
2017-10-11 09:49:26 INFO OGG-06508 Oracle GoldenGate Capture for Oracle, ext1.prm: Wildcard MAP (TABLE) resolved (entry hr.*): table "HR"."EMPLOYEES".
2017-10-11 09:49:27 INFO OGG-06509 Oracle GoldenGate Capture for Oracle, ext1.prm: Using the following key columns for source table HR.EMPLOYEES: EMPLOYEE_ID.
2017-10-11 09:49:30 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
2017-10-11 09:49:35 INFO OGG-01021 Oracle GoldenGate Capture for Oracle, ext1.prm: Command received from GGSCI: STATS.
2017-10-11 09:51:44 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): info all.
2017-10-11 09:52:01 INFO OGG-00987 Oracle 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 Chkpt Time 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>
|
|