Oracle技术专区 收藏本版 已有160 收藏本版 发新帖

dongxujian

高级会员

  1. 84 主题
  2. 168 评论
  3. 7 关注
  • 1今日版块发帖数
  • 11昨日版块发帖数
  • 1823昨日版块发帖数
  • 160板块关注人数

相关帖子

鸟哥的Linux私房菜服务器架设篇(第三版)
Linux6.5安装Oracle-11g-RAC(1)【视频教
Oracle.11g.从入门到精通
linux下安装oracle
Linux6.5安装Oracle-11g-RAC(2)【视频教

客服中心

010-63942882

服务时段:9:00-20:00

[分享] goldengate 12.2.0.1.1 for oracle logsource mode 测试

[复制链接]
发表于 2017-10-19 22:01:33 | 显示全部楼层 |阅读模式
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 oracleinstall /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/binORACLE_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>


回复

使用道具 举报

精彩评论 1

跳转到指定楼层
您需要登录后才可以回帖 登录 | 注册

本版积分规则

甲骨论是什么| 甲骨论   | ( 京ICP备12024223号 )

Copyright © 2017 Bosenrui Inc.   All Rights Reserved.

快速回复 返回顶部 返回列表