1.操作系统信息:
[root@dg2 ~]#
[root@dg2 ~]#
[root@dg2 ~]#
操作系统版本:
[root@dg2 ~]# uname -a
Linux dg2 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
操作系统架构:
[root@dg2 ~]# arch
x86_64
[root@dg2 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.4 (Santiago)
[root@dg2 ~]#
[root@dg2 ~]#
[root@dg2 ~]#
[root@dg2 ~]#
测试环境IP支持规划(物理dataguard dg1 to dg2)
[root@dg2 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.1.88 dg2
192.168.1.87 dg1
192.168.1.89 node1
[root@dg2 ~]#
[root@dg2 ~]#
[root@dg2 ~]#
[root@dg2 ~]#
[root@dg2 ~]#
[root@dg2 ~]#
[root@dg2 ~]#
2. oracle 软件tnsnames.ora 文件及监听配置,并检测网络连通性:
[root@dg2 ~]# su - oracle
[oracle@dg2 ~]$ cd $ORACLE_HOME/network/admin
[oracle@dg2 admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@dg2 admin]$ more 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 = dg1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl1)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl2)
)
)
[oracle@dg2 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl2)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dg2 admin]$
[oracle@dg2 admin]$
[oracle@dg2 admin]$
[oracle@dg2 admin]$
[oracle@dg2 admin]$ tnsping orcl2
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 13-OCT-2017 09:11:26
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 = dg2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl2)))
OK (40 msec)
[oracle@dg2 admin]$ tnsping orcl1
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 13-OCT-2017 09:11:41
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 = orcl1)))
OK (0 msec)
3.物理备库数据库版本,数据库角色,当前打开模式:
[oracle@dg2 admin]$
[oracle@dg2 admin]$
[oracle@dg2 admin]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 13 09:11:46 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> connect /as sysDBA
Connected.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> select database_role,protection_mode,log_mode from v$database;
DATABASE_ROLE PROTECTION_MODE LOG_MODE
---------------- -------------------- ------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE ARCHIVELOG
SQL> show parameter unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl2
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
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
4.物理主库数据库版本,数据库角色,当前打开模式:
[oracle@dg2 admin]$
[oracle@dg2 admin]$ ssh dg1
The authenticity of host 'dg1 (192.168.1.87)' can't be established.
RSA key fingerprint is 28:5d:76:19:60:ac:26:ca:3e:84:83:5e:fe:7e:51:3d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'dg1,192.168.1.87' (RSA) to the list of known hosts.
oracle@dg1's password:
[oracle@dg1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 13 09:14:04 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> select open_mode,database_role,log_mode,force_logging from v$database;
OPEN_MODE DATABASE_ROLE LOG_MODE FOR
-------------------- ---------------- ------------ ---
READ WRITE PRIMARY ARCHIVELOG YES
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@dg1 ~]$ cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
192.168.1.88 dg2
192.168.1.87 dg1
192.168.1.89 node1
[oracle@dg1 ~]$
[oracle@dg1 ~]$
[oracle@dg1 ~]$
[oracle@dg1 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.4 (Santiago)
[oracle@dg1 ~]$
[oracle@dg1 ~]$
[oracle@dg1 ~]$ uname -a
Linux dg1 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
[oracle@dg1 ~]$
[oracle@dg1 ~]$
[oracle@dg1 ~]$
[oracle@dg1 ~]$
5.主库tnsnames.ora 及监听配置文件:
[oracle@dg1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@dg1 admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@dg1 admin]$ more 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 = dg1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl1)
)
)
ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl2)
)
)
[oracle@dg1 admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@dg1 admin]$
[oracle@dg1 admin]$
[oracle@dg1 admin]$
[oracle@dg1 admin]$
[oracle@dg1 admin]$
[oracle@dg1 admin]$
[oracle@dg1 admin]$
[oracle@dg1 admin]$
[oracle@dg1 admin]$
[oracle@dg1 admin]$
[oracle@dg1 admin]$
[oracle@dg1 admin]$
[oracle@dg1 admin]$ exit
logout
Connection to dg1 closed.
[oracle@dg2 admin]$
[oracle@dg2 admin]$
[oracle@dg2 admin]$
[oracle@dg2 admin]$
[oracle@dg2 admin]$
[oracle@dg2 admin]$
[oracle@dg2 admin]$
6.物理备库所在主机dg2 上准备goldengate 12.2.0.1.1 for oracle 软件安装目录:
[oracle@dg2 admin]$
[oracle@dg2 admin]$ #######prepare to install goldengate 12.2.0.1.1 for oracle
[oracle@dg2 admin]$
[oracle@dg2 admin]$
[oracle@dg2 admin]$
[oracle@dg2 admin]$ echo $ORACLE_BASE
/u01/app/oracle
[oracle@dg2 admin]$ mkdir -p /u01/ogg/
[oracle@dg2 admin]$ cd /u01/ogg
[oracle@dg2 ogg]$ ls -l
total 0
7.dg2 上传goldengte 软件,解压并安装软件:
[oracle@dg2 ogg]$ #####upload goldengate software current directory
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$ ls -l
total 464472
-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/
clusterparam.ini
creating: fbo_ggs_Linux_x64_shiphome/Disk1/response/
inflating: fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
inflating: OGG-12.2.0.1-README.txt
inflating: OGG-12.2.0.1.1-ReleaseNotes.pdf
[oracle@dg2 ogg]$ ls -l
total 464756
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]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$ cd
[oracle@dg2 ~]$ vi .bash_profile
添加如下行:
export GGHOME=/u01/ogg
检查如下行:
$ORACLE_HOME/lib 存在于环境变量$LD_LIBRARY_PATH之中
[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/ogg;
ORACLE_SID=orcl2; 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 ~]$
[oracle@dg2 ~]$
[oracle@dg2 ~]$
[oracle@dg2 ~]$ source .bash_profile
[oracle@dg2 ~]$ echo $GGHOME
/u01/ogg
[oracle@dg2 ~]$
[oracle@dg2 ~]$
[oracle@dg2 ~]$ ######OUI install goldengate capture picture
[oracle@dg2 ~]$
[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 ~]$ cd /u01/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]$ 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 19922 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-13_09-20-25AM. Please wait ...[oracle@dg2 Disk1]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2017-10-13_09-20-25AM.log
[oracle@dg2 Disk1]$
8.登录GGSCI 查看管理进程状态并创建 goldnegate 工作目录:
[oracle@dg2 Disk1]$ cd
[oracle@dg2 ~]$ 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> create subdirs
Creating subdirectories under current directory /u01/ogg
Parameter files /u01/ogg/dirprm: already exists
Report files /u01/ogg/dirrpt: already exists
Checkpoint files /u01/ogg/dirchk: already exists
Process status files /u01/ogg/dirpcs: already exists
SQL script files /u01/ogg/dirsql: already exists
Database definitions files /u01/ogg/dirdef: already exists
Extract data files /u01/ogg/dirdat: already exists
Temporary files /u01/ogg/dirtmp: already exists
Credential store files /u01/ogg/dircrd: already exists
Masterkey wallet files /u01/ogg/dirwlt: already exists
Dump files /u01/ogg/dirdmp: already exists
GGSCI (dg2) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (dg2) 5> exit
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
9.登录物理主库检查并调整数据库配置及参数以满足goldengate配置需求:
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$ #######modify primary database configuration
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$ sqlplus sys/oracle@orcl1 as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 13 09:25:20 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 database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> select force_logging from v$database;
FOR
---
YES
SQL> desc v$database;
Name Null? Type
----------------------------------------- -------- ----------------------------
DBID NUMBER
NAME VARCHAR2(9)
CREATED DATE
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
PRIOR_RESETLOGS_CHANGE# NUMBER
PRIOR_RESETLOGS_TIME DATE
LOG_MODE VARCHAR2(12)
CHECKPOINT_CHANGE# NUMBER
ARCHIVE_CHANGE# NUMBER
CONTROLFILE_TYPE VARCHAR2(7)
CONTROLFILE_CREATED DATE
CONTROLFILE_SEQUENCE# NUMBER
CONTROLFILE_CHANGE# NUMBER
CONTROLFILE_TIME DATE
OPEN_RESETLOGS VARCHAR2(11)
VERSION_TIME DATE
OPEN_MODE VARCHAR2(20)
PROTECTION_MODE VARCHAR2(20)
PROTECTION_LEVEL VARCHAR2(20)
REMOTE_ARCHIVE VARCHAR2(8)
ACTIVATION# NUMBER
SWITCHOVER# NUMBER
DATABASE_ROLE VARCHAR2(16)
ARCHIVELOG_CHANGE# NUMBER
ARCHIVELOG_COMPRESSION VARCHAR2(8)
SWITCHOVER_STATUS VARCHAR2(20)
DATAGUARD_BROKER VARCHAR2(8)
GUARD_STATUS VARCHAR2(7)
SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
FORCE_LOGGING VARCHAR2(3)
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
RECOVERY_TARGET_INCARNATION# NUMBER
LAST_OPEN_INCARNATION# NUMBER
CURRENT_SCN NUMBER
FLASHBACK_ON VARCHAR2(18)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
STANDBY_BECAME_PRIMARY_SCN NUMBER
FS_FAILOVER_STATUS VARCHAR2(22)
FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)
FS_FAILOVER_THRESHOLD NUMBER
FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)
FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)
CONTROLFILE_CONVERTED VARCHAR2(3)
PRIMARY_DB_UNIQUE_NAME VARCHAR2(30)
SUPPLEMENTAL_LOG_DATA_PL VARCHAR2(3)
MIN_REQUIRED_CAPTURE_CHANGE# NUMBER
SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
SUPPLEME
--------
YES
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 goldengate
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
SQL>
SQL>
SQL>
SQL>
10.物理主库创建goldengate 管理用户并赋权;解锁测试用户scott:
SQL> create user ogg identified by ogg 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>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> alter user scott identified by oracle account unlock;
User altered.
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 scott/oracle@orcl1
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 13 09:29:13 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
------------------------------
BONUS
DEPT
EMP
SALGRADE
TEST
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
11.登录物理主库开启测试用户scott下的表的表级别附加日志:
[oracle@dg2 ogg]$ cd $GGHOME
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]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[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> dblogin userid ogg@orcl1,password oracle
Successfully logged into database.
GGSCI (dg2 as ogg@orcl) 3> add schematrandata scott
2017-10-13 09:31:53 INFO OGG-01788 SCHEMATRANDATA has been added on schema scott.
2017-10-13 09:31:53 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema scott.
12.添加ADG 模式抽取进程
GGSCI (dg2) 3> edit params ext1
extract ext1
exttrail ./dirdat/lr
"dirprm/ext1.prm" 7L, 151C written
GGSCI (dg2) 4> view params ext1
extract ext1
exttrail ./dirdat/lr
userid ogg, password oracle
tranlogoptions minefromactivedg
table scott.*;
GGSCI (dg2 as ogg@orcl) 16> ADD EXTRACT ext1,TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (dg2 as ogg@orcl) 18> ADD EXTTRAIL ./dirdat/lr, EXTRACT ext1, MEGABYTES 10
EXTTRAIL added.
13.启动抽取进程
GGSCI (dg2) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:16:02
GGSCI (dg2) 6> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (dg2) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:16:06
GGSCI (dg2) 8> view report ext1
***********************************************************************
Oracle GoldenGate Capture 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 01:10:08
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
Starting at 2017-10-13 09:48:45
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Jan 29 11:47:41 EST 2013, Release 2.6.32-358.el6.x86_64
Node: dg2
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 9803
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2017-10-13 09:48:45 INFO OGG-03059 Operating system character set identified as US-ASCII.
2017-10-13 09:48:45 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
2017-10-13 09:48:45 WARNING OGG-10173 (ext1.prm) line 6: Parsing error, [dynamicresolution] is deprecated.
2017-10-13 09:48:45 WARNING OGG-10173 (ext1.prm) line 5: Parsing error, [wildcardresolve] is deprecated.
extract ext1
exttrail ./dirdat/lr
userid ogg, password ***
2017-10-13 09:48:46 INFO OGG-03522 Setting session time zone to source database time zone 'GMT'.
tranlogoptions minefromactivedg
wildcardresolve dynamic
dynamicresolution
table scott.*;
2017-10-13 09:48:46 INFO OGG-01635 BOUNDED RECOVERY: reset to initial or altered checkpoint.
2017-10-13 09:48:46 INFO OGG-01815 Virtual Memory Facilities for: BR
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/u01/ogg/BR/EXT1.
Bounded Recovery Parameter:
BRINTERVAL = 4HOURS
BRDIR = /u01/ogg
2017-10-13 09:48:46 INFO OGG-01851 filecaching started: thread ID: 139815505479424.
2017-10-13 09:48:46 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/u01/ogg/dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHEPAGEOUTSIZE (default): 8M
PROCESS VM AVAIL FROM OS (min): 128G
CACHESIZEMAX (strict force to disk): 96G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Database Language and Character Set:
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"
Source Context :
SourceModule : [er.redo.ora]
SourceID : [/scratch/aime/adestore/views/aime_adc4150408/oggcore/OpenSys/src/app/er/redo/oracle/redoora.c]
SourceFunction : [REDO_validate_config]
SourceLine : [6156]
ThreadBacktrace : [12] elements
: [/u01/ogg/libgglog.so(CMessageContext::AddThreadContext()+0x1b) [0x7f295f081f1b]]
: [/u01/ogg/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x134) [0x7f295f
07c0a4]]
: [/u01/ogg/libgglog.so(_MSG_ERR_NO_DB_EVENT_SET(CSourceContext*, CMessageFactory::MessageDisposition)+0x29
) [0x7f295f052220]]
: [/u01/ogg/extract(REDO_validate_config(bool, unsigned int*, bool&)+0x129c) [0x76935c]]
: [/u01/ogg/extract(ggs::er::OraTranLogDataSource::setup()+0x6c) [0x75bd2c]]
: [/u01/ogg/extract() [0x5f3c56]]
: [/u01/ogg/extract(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x60) [0x6d05b0]]
: [/u01/ogg/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::Threa
dArgs*)+0x14d) [0x6d179d]]
: [/u01/ogg/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0xb1) [0x6d1881]]
: [/u01/ogg/extract(main+0x3b) [0x5f50eb]]
: [/lib64/libc.so.6(__libc_start_main+0xfd) [0x33e281ecdd]]
: [/u01/ogg/extract() [0x563159]]
2017-10-13 09:48:46 ERROR OGG-02091 Operation not supported because enable_goldengate_replication is not set to true.
2017-10-13 09:48:46 ERROR OGG-01668 PROCESS ABENDING.
物理备库此参数也需要调整,前期只是调整了主库的此参数,登录备库调整
GGSCI (dg2) 9> exit
[oracle@dg2 ogg]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 13 09:49:11 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> show parameter replicate
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 goldengate
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
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]$ 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 STOPPED EXT1 00:00:00 00:17:15
GGSCI (dg2) 2> view params ext1
extract ext1
exttrail ./dirdat/lr
userid ogg, password oracle
tranlogoptions minefromactivedg
table scott.*;
再次尝试启动抽取进程:
GGSCI (dg2) 3> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (dg2) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:17:24
GGSCI (dg2) 5> view report ext1
***********************************************************************
Oracle GoldenGate Capture 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 01:10:08
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
Starting at 2017-10-13 09:50:01
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Jan 29 11:47:41 EST 2013, Release 2.6.32-358.el6.x86_64
Node: dg2
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 9888
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2017-10-13 09:50:01 INFO OGG-03059 Operating system character set identified as US-ASCII.
2017-10-13 09:50:01 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
2017-10-13 09:50:01 WARNING OGG-10173 (ext1.prm) line 6: Parsing error, [dynamicresolution] is deprecated.
2017-10-13 09:50:01 WARNING OGG-10173 (ext1.prm) line 5: Parsing error, [wildcardresolve] is deprecated.
extract ext1
exttrail ./dirdat/lr
userid ogg, password ***
2017-10-13 09:50:02 INFO OGG-03522 Setting session time zone to source database time zone 'GMT'.
tranlogoptions minefromactivedg
wildcardresolve dynamic
dynamicresolution
table scott.*;
2017-10-13 09:50:02 INFO OGG-01635 BOUNDED RECOVERY: reset to initial or altered checkpoint.
2017-10-13 09:50:02 INFO OGG-01815 Virtual Memory Facilities for: BR
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/u01/ogg/BR/EXT1.
Bounded Recovery Parameter:
BRINTERVAL = 4HOURS
BRDIR = /u01/ogg
2017-10-13 09:50:02 INFO OGG-01851 filecaching started: thread ID: 140608222148352.
2017-10-13 09:50:02 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/u01/ogg/dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHEPAGEOUTSIZE (default): 8M
PROCESS VM AVAIL FROM OS (min): 128G
CACHESIZEMAX (strict force to disk): 96G
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Database Language and Character Set:
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"
2017-10-13 09:50:02 WARNING OGG-02810 A relative timestamp, such as NOW, was used as starting position for Extract on an Oracle Act
ive Data Guard standby database.
Source Context :
SourceModule : [er.redo.ora]
SourceID : [/scratch/aime/adestore/views/aime_adc4150408/oggcore/OpenSys/src/app/er/redo/oracle/redoora.c]
SourceFunction : [ce_validate_config]
SourceLine : [6078]
ThreadBacktrace : [12] elements
: [/u01/ogg/libgglog.so(CMessageContext::AddThreadContext()+0x1b) [0x7fe1fca34f1b]]
: [/u01/ogg/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x134) [0x7fe1fc
a2f0a4]]
: [/u01/ogg/libgglog.so(_MSG_ERR_DB_GENERIC_FAILURE(CSourceContext*, char const*, CMessageFactory::MessageD
isposition)+0x30) [0x7fe1fca16bbe]]
: [/u01/ogg/extract(REDO_validate_config(bool, unsigned int*, bool&)+0x1548) [0x769608]]
: [/u01/ogg/extract(ggs::er::OraTranLogDataSource::setup()+0x6c) [0x75bd2c]]
: [/u01/ogg/extract() [0x5f3c56]]
: [/u01/ogg/extract(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x60) [0x6d05b0]]
: [/u01/ogg/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::Threa
dArgs*)+0x14d) [0x6d179d]]
: [/u01/ogg/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0xb1) [0x6d1881]]
: [/u01/ogg/extract(main+0x3b) [0x5f50eb]]
: [/lib64/libc.so.6(__libc_start_main+0xfd) [0x33e281ecdd]]
: [/u01/ogg/extract() [0x563159]]
2017-10-13 09:50:02 ERROR OGG-00868 The number of Oracle redo threads (2) is not the same as the number of checkpoint threads (1)
. EXTRACT groups on RAC systems should be created with the THREADS parameter (e.g., ADD EXT <group name>, TRANLOG, THREADS 2, BEGIN..
.).
2017-10-13 09:50:02 ERROR OGG-01668 PROCESS ABENDING.
出现上面的报错,删除抽取进程后重新添加抽取进程:
GGSCI (dg2) 6>
GGSCI (dg2) 6>
GGSCI (dg2) 6>
GGSCI (dg2) 6>
GGSCI (dg2) 6>
GGSCI (dg2) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:19:12
GGSCI (dg2) 7> dblogin userid ogg@orcl1,password oracle
Successfully logged into database.
GGSCI (dg2 as ogg@orcl) 8> delete extract ext1
Deleted EXTRACT EXT1.
GGSCI (dg2 as ogg@orcl) 9>
GGSCI (dg2 as ogg@orcl) 11> view params ext1
extract ext1
exttrail ./dirdat/lr
userid ogg, password oracle
tranlogoptions minefromactivedg
table scott.*;
GGSCI (dg2 as ogg@orcl) 12>
GGSCI (dg2 as ogg@orcl) 12>
GGSCI (dg2 as ogg@orcl) 12>
GGSCI (dg2 as ogg@orcl) 12>
GGSCI (dg2 as ogg@orcl) 17> ADD EXTRACT ext1,threads 2, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (dg2 as ogg@orcl) 18> ADD EXTTRAIL ./dirdat/lr, EXTRACT ext1, MEGABYTES 10
EXTTRAIL added.
GGSCI (dg2 as ogg@orcl) 19> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 00:00:18
重新启动抽取进程,进程状态正常:
GGSCI (dg2 as ogg@orcl) 20> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
GGSCI (dg2 as ogg@orcl) 21> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:22 00:00:02
GGSCI (dg2 as ogg@orcl) 22> info ext1
EXTRACT EXT1 Last Started 2017-10-13 09:56 Status RUNNING
Checkpoint Lag 00:00:22 (updated 00:00:08 ago)
Process ID 10049
Log Read Checkpoint Oracle Redo Logs
2017-10-13 09:56:23 Thread 1, Seqno 41, RBA 499216
SCN 0.1055147 (1055147)
Log Read Checkpoint Oracle Redo Logs
2017-10-13 09:56:23 Thread 0, Seqno 0, RBA 0
SCN 0.0 (0)
GGSCI (dg2 as ogg@orcl) 23> info ext1
EXTRACT EXT1 Last Started 2017-10-13 09:56 Status RUNNING
Checkpoint Lag 00:00:05 (updated 00:00:02 ago)
Process ID 10049
Log Read Checkpoint Oracle Redo Logs
2017-10-13 09:57:00 Thread 1, Seqno 41, RBA 522240
SCN 0.1055193 (1055193)
Log Read Checkpoint Oracle Redo Logs
2017-10-13 09:56:23 Thread 0, Seqno 0, RBA 0
SCN 0.0 (0)
GGSCI (dg2 as ogg@orcl) 24> stats ext1
Sending STATS request to EXTRACT EXT1 ...
No active extraction maps.
GGSCI (dg2 as ogg@orcl) 25> info ext1
EXTRACT EXT1 Last Started 2017-10-13 09:56 Status RUNNING
Checkpoint Lag 00:00:06 (updated 00:00:02 ago)
Process ID 10049
Log Read Checkpoint Oracle Redo Logs
2017-10-13 09:57:09 Thread 1, Seqno 41, RBA 529408
SCN 0.1055206 (1055206)
Log Read Checkpoint Oracle Redo Logs
2017-10-13 09:56:23 Thread 0, Seqno 0, RBA 0
SCN 0.0 (0)
GGSCI (dg2 as ogg@orcl) 26> info ext1 detail
EXTRACT EXT1 Last Started 2017-10-13 09:56 Status RUNNING
Checkpoint Lag 00:00:04 (updated 00:00:03 ago)
Process ID 10049
Log Read Checkpoint Oracle Redo Logs
2017-10-13 09:57:21 Thread 1, Seqno 41, RBA 537600
SCN 0.1055221 (1055221)
Log Read Checkpoint Oracle Redo Logs
2017-10-13 09:56:23 Thread 0, Seqno 0, RBA 0
SCN 0.0 (0)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
./dirdat/lr 0 1363 10 EXTTRAIL
Extract Source Begin End
Not Available 2017-10-13 09:56 2017-10-13 09:57
Not Available * Initialized * 2017-10-13 09:56
Not Available * Initialized * 2017-10-13 09:56
Not Available * Initialized * 2017-10-13 09:56
Current directory /u01/ogg
Report file /u01/ogg/dirrpt/EXT1.rpt
Parameter file /u01/ogg/dirprm/ext1.prm
Checkpoint file /u01/ogg/dirchk/EXT1.cpe
Process file /u01/ogg/dirpcs/EXT1.pce
Error log /u01/ogg/ggserr.log
GGSCI (dg2 as ogg@orcl) 27> exit
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[oracle@dg2 ogg]$
[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:06 00:00:10
GGSCI (dg2) 3> exit
登录物理主库,执行DML操作测试抽取进程是否正常:
[oracle@dg2 ogg]$ sqlplus scott/oracle@orcl1
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 13 09:58:16 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
------------------------------
BONUS
DEPT
EMP
SALGRADE
TEST
SQL> select count(*) from test;
COUNT(*)
----------
0
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL> insert into test select * from dba_objects;
86958 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from test;
COUNT(*)
----------
86958
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]$ 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:04 00:00:00
GGSCI (dg2) 2> info ext1 detail
EXTRACT EXT1 Last Started 2017-10-13 09:56 Status RUNNING
Checkpoint Lag 00:00:04 (updated 00:00:07 ago)
Process ID 10049
Log Read Checkpoint Oracle Redo Logs
2017-10-13 09:59:13 Thread 1, Seqno 41, RBA 19196416
SCN 0.1056156 (1056156)
Log Read Checkpoint Oracle Redo Logs
2017-10-13 09:56:23 Thread 0, Seqno 0, RBA 0
SCN 0.0 (0)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
./dirdat/lr 2 6414189 10 EXTTRAIL
Extract Source Begin End
Not Available 2017-10-13 09:56 2017-10-13 09:59
Not Available * Initialized * 2017-10-13 09:56
Not Available * Initialized * 2017-10-13 09:56
Not Available * Initialized * 2017-10-13 09:56
Current directory /u01/ogg
Report file /u01/ogg/dirrpt/EXT1.rpt
Parameter file /u01/ogg/dirprm/ext1.prm
Checkpoint file /u01/ogg/dirchk/EXT1.cpe
Process file /u01/ogg/dirpcs/EXT1.pce
Error log /u01/ogg/ggserr.log
GGSCI (dg2) 3> stats ext1
Sending STATS request to EXTRACT EXT1 ...
Start of Statistics at 2017-10-13 09:59:33.
Output to ./dirdat/lr:
Extracting from SCOTT.TEST to SCOTT.TEST:
*** Total statistics since 2017-10-13 09:58:54 ***
Total inserts 86958.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 86958.00
*** Daily statistics since 2017-10-13 09:58:54 ***
Total inserts 86958.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 86958.00
*** Hourly statistics since 2017-10-13 09:58:54 ***
Total inserts 86958.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 86958.00
*** Latest statistics since 2017-10-13 09:58:54 ***
Total inserts 86958.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 86958.00
End of Statistics.
GGSCI (dg2) 4>
GGSCI (dg2) 4>
GGSCI (dg2) 4>
GGSCI (dg2) 4>
GGSCI (dg2) 4>
GGSCI (dg2) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:04 00:00:00
GGSCI (dg2) 5> info ext1 detail
EXTRACT EXT1 Last Started 2017-10-13 09:56 Status RUNNING
Checkpoint Lag 00:00:04 (updated 00:00:06 ago)
Process ID 10049
Log Read Checkpoint Oracle Redo Logs
2017-10-13 10:03:25 Thread 1, Seqno 41, RBA 20904960
SCN 0.1056588 (1056588)
Log Read Checkpoint Oracle Redo Logs
2017-10-13 09:56:23 Thread 0, Seqno 0, RBA 0
SCN 0.0 (0)
Target Extract Trails:
Trail Name Seqno RBA Max MB Trail Type
./dirdat/lr 2 6414189 10 EXTTRAIL
Extract Source Begin End
Not Available 2017-10-13 09:56 2017-10-13 10:03
Not Available * Initialized * 2017-10-13 09:56
Not Available * Initialized * 2017-10-13 09:56
Not Available * Initialized * 2017-10-13 09:56
Current directory /u01/ogg
Report file /u01/ogg/dirrpt/EXT1.rpt
Parameter file /u01/ogg/dirprm/ext1.prm
Checkpoint file /u01/ogg/dirchk/EXT1.cpe
Process file /u01/ogg/dirpcs/EXT1.pce
Error log /u01/ogg/ggserr.log
GGSCI (dg2) 6>
GGSCI (dg2) 6>
GGSCI (dg2) 6>
GGSCI (dg2) 6>
GGSCI (dg2) 6>
GGSCI (dg2) 6>
GGSCI (dg2) 6> exit
[oracle@dg2 ogg]$ sqlplus scott/oracle@orcl1
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 13 10:04:01 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> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL> update test set object_name='AAA';
86958 rows updated.
SQL> commit;
Commit complete.
SQL> delete from test;
86958 rows deleted.
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
[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:08
GGSCI (dg2) 2> info ext1
EXTRACT EXT1 Last Started 2017-10-13 09:56 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:10 ago)
Process ID 10049
Log Read Checkpoint Oracle Redo Logs
2017-10-13 10:04:25 Thread 1, Seqno 41, RBA 49847808
SCN 0.1056707 (1056707)
Log Read Checkpoint Oracle Redo Logs
2017-10-13 09:56:23 Thread 0, Seqno 0, RBA 0
SCN 0.0 (0)
GGSCI (dg2) 3> stats ext1
Sending STATS request to EXTRACT EXT1 ...
Start of Statistics at 2017-10-13 10:04:48.
Output to ./dirdat/lr:
Extracting from SCOTT.TEST to SCOTT.TEST:
*** Total statistics since 2017-10-13 09:58:54 ***
Total inserts 86958.00
Total updates 86958.00
Total deletes 86958.00
Total discards 0.00
Total operations 260874.00
*** Daily statistics since 2017-10-13 09:58:54 ***
Total inserts 86958.00
Total updates 86958.00
Total deletes 86958.00
Total discards 0.00
Total operations 260874.00
*** Hourly statistics since 2017-10-13 10:00:00 ***
Total inserts 0.00
Total updates 86958.00
Total deletes 86958.00
Total discards 0.00
Total operations 173916.00
*** Latest statistics since 2017-10-13 09:58:54 ***
Total inserts 86958.00
Total updates 86958.00
Total deletes 86958.00
Total discards 0.00
Total operations 260874.00
End of Statistics.
GGSCI (dg2) 4> exit
[oracle@dg2 ogg]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 13 10:08:08 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
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
物理主库所执行复制相关表的dml事务操作均正常抽取,测试成功。
备注:
上面涉及的这个报错:
2017-10-13 09:50:02 ERROR OGG-00868 The number of Oracle redo threads (2) is not the same as the number of checkpoint threads (1)
. EXTRACT groups on RAC systems should be created with the THREADS parameter (e.g., ADD EXT <group name>, TRANLOG, THREADS 2, BEGIN..
.).
2017-10-13 09:50:02 ERROR OGG-01668 PROCESS ABENDING.
这里采用的是添加进程是指定 threads 2 来解决的,这个错误的根本原因是主备库上的standby redo log 日志组的thread# 与在线日志的thread# 不同导致的
正确的处理方法参考文件夹 针对threads 2 相关报错的处理调整记录及官方文档 下的《问题处理.txt》. |
|