Oracle 9i to Oracle 12c goldengate 单向复制non-cdb模式实践操作记...
2016/10/28 10:06:55$ hostname
shost
$ sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Fri Oct 28 10:08:51 2016
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area320306616 bytes
Fixed Size 740792 bytes
Variable Size 285212672 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
SQL> !lsnrctl status
LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 28-OCT-2016 10:09:40
Copyright (c) 1991, 2006, Oracle Corporation.All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shost)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
!TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
SQL> !lsnrctl start
/bin/bash: !lsnrctl: command not found
SQL> !lsnrctl start
LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 28-OCT-2016 10:09:51
Copyright (c) 1991, 2006, Oracle Corporation.All rights reserved.
Starting /u01/app/oracle/product/9.2.0/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 9.2.0.8.0 - Production
System parameter file is /u01/app/oracle/product/9.2.0/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/9.2.0/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 9.2.0.8.0 - Production
Start Date 28-OCT-2016 10:09:51
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /u01/app/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
SQL> alter system register;
System altered.
SQL> !lsnrctl status
LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 28-OCT-2016 10:10:04
Copyright (c) 1991, 2006, Oracle Corporation.All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 9.2.0.8.0 - Production
Start Date 28-OCT-2016 10:09:51
Uptime 0 days 0 hr. 0 min. 13 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /u01/app/oracle/product/9.2.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/9.2.0/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shost)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 2 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
SQL> select * from v$banner;
select * from v$banner
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select banner from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
$ cd $GGHOME
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 9i on Oct4 2011 23:49:32
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (shost) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (shost) 2> start mgr
Manager started.
GGSCI (shost) 3> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
GGSCI (shost) 4> create user ggs identified by ggs account unlock;
ERROR: Invalid command.
GGSCI (shost) 5> exit
$ sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Fri Oct 28 10:11:44 2016
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> create user ggs identified by ggs account unlock;
User created.
如果对权限有要求，如没要求，一般就给dba权限，详细权限为：
GRANT create table to ggs;
GRANT CONNECT TO ggs;
GRANT ALTER ANY TABLE TO ggs;
GRANT ALTER SESSION TO ggs;
GRANT CREATE SESSION TO ggs;
GRANT FLASHBACK ANY TABLE TO ggs;
GRANT SELECT ANY DICTIONARY TO ggs;
GRANT SELECT ANY TABLE TO ggs;
GRANT RESOURCE” TO ggs;
GRANT DELETE ANY TABLE TO ggs;
GRANT INSERT ANY TABLE TO ggs;
GRANT UPDATE ANY TABLE TO ggs;
GRANT RESTRICTED SESSION TO ggs;
SQL> grant dba to ggs;
Grant succeeded.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/orcl/archive
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
SQL> alter system set recyclebin=off scope=spfile;
alter system set recyclebin=off scope=spfile
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM
SQL> select supplemental_log_data_min from v$database;
SUP
---
NO
SQL> alter database add supplemental log data;
Database altered.
SQL> select supplemental_log_data_min from v$database;
SUP
---
YES
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='HR';
USERNAME DEFAULT_TABLESPACETEMPORARY_TABLESPACE
----------- ------------------------------
HR EXAMPLE TEMP
SQL> alter user hr identified by hr account unlock;
User altered.
SQL> connect hr/hr
Connected.
SQL> select tname from tab;
TNAME
------------------------------
COUNTRIES
DEPARTMENTS
EMPLOYEES
EMP_DETAILS_VIEW
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
15 rows selected.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
$
$
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 9i on Oct4 2011 23:49:32
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (shost) 1> dblogin userid ggs,password ggs
Successfully logged into database.
GGSCI (shost) 2> add trandata sh.*
2016-10-28 10:17:12INFO OGG-01451Unique key I_SNAP$_CAL_MONTH_SALES_MV cannot be used due to the inclusion of function-based index column.
2016-10-28 10:17:12WARNING OGG-00869No viable unique key is defined for table CAL_MONTH_SALES_MV. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SH.CAL_MONTH_SALES_MV.
Logging of supplemental redo data enabled for table SH.CHANNELS.
2016-10-28 10:17:12WARNING OGG-00869No unique key is defined for table COSTS. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SH.COSTS.
Logging of supplemental redo data enabled for table SH.COUNTRIES.
Logging of supplemental redo data enabled for table SH.CUSTOMERS.
2016-10-28 10:17:12INFO OGG-01451Unique key I_SNAP$_FWEEK_PSCAT_SALES_ cannot be used due to the inclusion of function-based index column.
2016-10-28 10:17:12WARNING OGG-00869No viable unique key is defined for table FWEEK_PSCAT_SALES_MV. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SH.FWEEK_PSCAT_SALES_MV.
2016-10-28 10:17:12WARNING OGG-00869No unique key is defined for table MVIEW$_EXCEPTIONS. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SH.MVIEW$_EXCEPTIONS.
2016-10-28 10:17:12WARNING OGG-00869No unique key is defined for table MV_CAPABILITIES_TABLE. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SH.MV_CAPABILITIES_TABLE.
2016-10-28 10:17:12WARNING OGG-00869No unique key is defined for table PLAN_TABLE. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SH.PLAN_TABLE.
Logging of supplemental redo data enabled for table SH.PRODUCTS.
Logging of supplemental redo data enabled for table SH.PROMOTIONS.
2016-10-28 10:17:13WARNING OGG-00869No unique key is defined for table REWRITE_TABLE. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SH.REWRITE_TABLE.
2016-10-28 10:17:13WARNING OGG-00869No unique key is defined for table SALES. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SH.SALES.
ERROR: OCI Error retrieving bind info for query (status = 100), SQL <SELECT * FROM "SH"."SALES_TRANSACTIONS_EXT">.
GGSCI (shost) 5> add trandata oe.*
2016-10-28 10:23:24WARNING OGG-01455Object table OE.CATEGORIES_TAB is not supported for this database version.
Logging of supplemental redo data enabled for table OE.CUSTOMERS.
Logging of supplemental redo data enabled for table OE.INVENTORIES.
Logging of supplemental redo data enabled for table OE.ORDERS.
Logging of supplemental redo data enabled for table OE.ORDER_ITEMS.
Logging of supplemental redo data enabled for table OE.PRODUCT_DESCRIPTIONS.
Logging of supplemental redo data enabled for table OE.PRODUCT_INFORMATION.
Logging of supplemental redo data enabled for table OE.WAREHOUSES.
GGSCI (shost) 6> add trandata hr.*
Logging of supplemental redo data enabled for table HR.COUNTRIES.
Logging of supplemental redo data enabled for table HR.DEPARTMENTS.
Logging of supplemental redo data enabled for table HR.EMPLOYEES.
Logging of supplemental redo data enabled for table HR.JOBS.
Logging of supplemental redo data enabled for table HR.JOB_HISTORY.
Logging of supplemental redo data enabled for table HR.LOCATIONS.
Logging of supplemental redo data enabled for table HR.REGIONS.
GGSCI (shost) 7>
GGSCI (shost) 7>
GGSCI (shost) 7>
GGSCI (shost) 7> info trandata hr.*
Logging of supplemental redo log data is enabled for table HR.COUNTRIES
Logging of supplemental redo log data is enabled for table HR.DEPARTMENTS
Logging of supplemental redo log data is enabled for table HR.EMPLOYEES
Logging of supplemental redo log data is enabled for table HR.JOBS
Logging of supplemental redo log data is enabled for table HR.JOB_HISTORY
Logging of supplemental redo log data is enabled for table HR.LOCATIONS
Logging of supplemental redo log data is enabled for table HR.REGIONS
GGSCI (shost) 8> info trandata oe.*
2016-10-28 10:25:48WARNING OGG-01455Object table OE.CATEGORIES_TAB is not supported for this database version.
Logging of supplemental redo log data is enabled for table OE.CUSTOMERS
Logging of supplemental redo log data is enabled for table OE.INVENTORIES
Logging of supplemental redo log data is enabled for table OE.ORDERS
Logging of supplemental redo log data is enabled for table OE.ORDER_ITEMS
Logging of supplemental redo log data is enabled for table OE.PRODUCT_DESCRIPTIONS
Logging of supplemental redo log data is enabled for table OE.PRODUCT_INFORMATION
Logging of supplemental redo log data is enabled for table OE.WAREHOUSES
GGSCI (shost) 9> info trandata sh.*
Logging of supplemental redo log data is enabled for table SH.CAL_MONTH_SALES_MV
Logging of supplemental redo log data is enabled for table SH.CHANNELS
Logging of supplemental redo log data is enabled for table SH.COSTS
Logging of supplemental redo log data is enabled for table SH.COUNTRIES
Logging of supplemental redo log data is enabled for table SH.CUSTOMERS
Logging of supplemental redo log data is enabled for table SH.FWEEK_PSCAT_SALES_MV
Logging of supplemental redo log data is enabled for table SH.MVIEW$_EXCEPTIONS
Logging of supplemental redo log data is enabled for table SH.MV_CAPABILITIES_TABLE
Logging of supplemental redo log data is enabled for table SH.PLAN_TABLE
Logging of supplemental redo log data is enabled for table SH.PRODUCTS
Logging of supplemental redo log data is enabled for table SH.PROMOTIONS
Logging of supplemental redo log data is enabled for table SH.REWRITE_TABLE
Logging of supplemental redo log data is enabled for table SH.SALES
Logging of supplemental redo log data is disabled for table SH.SALES_TRANSACTIONS_EXT.
Logging of supplemental redo log data is disabled for table SH.TIMES.
GGSCI (shost) 10> edit params ext_db
GGSCI (shost) 4> view params ext_db
extract ext_db
dynamicresolution
userid ggs,password ggs
exttrail ./dirdat/aa
table hr.*;
GGSCI (shost) 5> add extract ext_db,tranlog,begin now
EXTRACT added.
GGSCI (shost) 7> add exttrail ./dirdat/aa,extract ext_db
EXTTRAIL added.
GGSCI (shost) 8> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT_DB 00:00:00 00:00:39
GGSCI (shost) 9> edit params pm_db
GGSCI (shost) 10> view params pm_db
extract pm_db
passthru
dynamicresolution
userid ggs,password ggs
rmthost 192.168.1.123,mgrport 7809
rmttrail ./dirdat/bb
table hr.*;
GGSCI (shost) 11> add extract pm_db,exttrailsource ./dirdat/aa
EXTRACT added.
GGSCI (shost) 12> add rmttrail ./dirdat/bb,extract pm_db
RMTTRAIL added.
GGSCI (shost) 13> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT_DB 00:00:00 00:02:44
EXTRACT STOPPED PM_DB 00:00:00 00:00:20
GGSCI (shost) 32> start ext_db
Sending START request to MANAGER ...
EXTRACT EXT_DB starting
GGSCI (shost) 33> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_DB 00:00:00 00:04:19
EXTRACT STOPPED PM_DB 00:00:00 00:10:03
GGSCI (shost) 34> start pm_db
Sending START request to MANAGER ...
EXTRACT PM_DB starting
GGSCI (shost) 35> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_DB 00:04:19 00:00:10
EXTRACT RUNNING PM_DB 00:00:00 00:10:13
GGSCI (shost) 41> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_DB 00:00:00 00:00:06
EXTRACT ABENDED PM_DB 00:00:00 00:12:25
GGSCI (shost) 42> view ggsevt
2016-10-26 19:26:13INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit params mgr.
2016-10-26 19:26:49INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit params mgr.
2016-10-26 19:30:46INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit params mgr.
2016-10-26 19:31:00INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start mgr.
2016-10-26 19:31:01INFO OGG-00983Oracle GoldenGate Manager for Oracle, mgr.prm:Manager started (port 7809).
2016-10-26 20:32:04INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): stop mgr.
2016-10-26 20:32:05INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host 192.168.
1.88 (STOP).
2016-10-26 20:32:05WARNING OGG-00938Oracle GoldenGate Manager for Oracle, mgr.prm:Manager is stopping at user request.
2016-10-28 10:11:08INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start mgr.
2016-10-28 10:11:08INFO OGG-00983Oracle GoldenGate Manager for Oracle, mgr.prm:Manager started (port 7809).
2016-10-28 10:17:11INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add trandata sh.*
.
2016-10-28 10:17:12INFO OGG-01451Oracle GoldenGate Command Interpreter for Oracle:Unique key I_SNAP$_CAL_MONTH_SALES_MV can
not be used due to the inclusion of function-based index column.
2016-10-28 10:17:12WARNING OGG-00869Oracle GoldenGate Command Interpreter for Oracle:No viable unique key is defined for table
CAL_MONTH_SALES_MV. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to
define the key.
2016-10-28 10:17:12WARNING OGG-00869Oracle GoldenGate Command Interpreter for Oracle:No unique key is defined for table COSTS.
All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define the key.
2016-10-28 10:17:12INFO OGG-01451Oracle GoldenGate Command Interpreter for Oracle:Unique key I_SNAP$_FWEEK_PSCAT_SALES_ can
not be used due to the inclusion of function-based index column.
2016-10-28 10:17:12WARNING OGG-00869Oracle GoldenGate Command Interpreter for Oracle:No viable unique key is defined for table
FWEEK_PSCAT_SALES_MV. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used
to define the key.
2016-10-28 10:17:12WARNING OGG-00869Oracle GoldenGate Command Interpreter for Oracle:No unique key is defined for table MVIEW$
_EXCEPTIONS. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define
the key.
2016-10-28 10:17:12WARNING OGG-00869Oracle GoldenGate Command Interpreter for Oracle:No unique key is defined for table MV_CAP
ABILITIES_TABLE. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to def
ine the key.
2016-10-28 10:17:12WARNING OGG-00869Oracle GoldenGate Command Interpreter for Oracle:No unique key is defined for table PLAN_T
ABLE. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define the key
.
2016-10-28 10:17:13WARNING OGG-00869Oracle GoldenGate Command Interpreter for Oracle:No unique key is defined for table REWRIT
E_TABLE. All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define the
key.
2016-10-28 10:17:13WARNING OGG-00869Oracle GoldenGate Command Interpreter for Oracle:No unique key is defined for table SALES.
All viable columns will be used to represent the key, but may not guarantee uniqueness.KEYCOLS may be used to define the key.
2016-10-28 10:23:07INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add trandata scot
t.*.
2016-10-28 10:23:24INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add trandata oe.*
.
2016-10-28 10:23:24WARNING OGG-01455Oracle GoldenGate Command Interpreter for Oracle:Object table OE.CATEGORIES_TAB is not sup
ported for this database version.
2016-10-28 10:25:08INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add trandata hr.*
.
2016-10-28 10:25:48WARNING OGG-01455Oracle GoldenGate Command Interpreter for Oracle:Object table OE.CATEGORIES_TAB is not sup
ported for this database version.
2016-10-28 10:26:43INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit params ext_c
db.
2016-10-28 10:29:06INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit params ext_d
b.
2016-10-28 10:29:49INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add extract ext_d
b tranlog,begin now.
2016-10-28 10:30:06INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add exttrail ./di
rdat/aa extrace ext_db.
2016-10-28 10:30:26INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add exttrail ./di
rdat/aa extract ext_db.
2016-10-28 10:30:52INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit params pm_db
.
2016-10-28 10:32:13INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add extract pm_db
exttrailsource ./dirdat/aa.
2016-10-28 10:32:32INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add rmttrail ./di
rdat/bb extract pm_db.
2016-10-28 10:33:39INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start ext_db.
2016-10-28 10:33:39INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host 192.168.
1.88 (START EXTRACT EXT_DB ).
2016-10-28 10:33:39INFO OGG-00975Oracle GoldenGate Manager for Oracle, mgr.prm:EXTRACT EXT_DB starting.
2016-10-28 10:33:39INFO OGG-00992Oracle GoldenGate Capture for Oracle, ext_db.prm:EXTRACT EXT_DB starting.
2016-10-28 10:33:39ERROR OGG-00446Oracle GoldenGate Capture for Oracle, ext_db.prm:Could not open checkpoint file /u01/ogg11
/dirchk/EXT_CDB.cpe, mode 1 (error 2, No such file or directory).
2016-10-28 10:33:39ERROR OGG-01668Oracle GoldenGate Capture for Oracle, ext_db.prm:PROCESS ABENDING.
2016-10-28 10:33:49INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start ext_db.
2016-10-28 10:33:49INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host 192.168.
1.88 (START EXTRACT EXT_DB ).
2016-10-28 10:33:49INFO OGG-00975Oracle GoldenGate Manager for Oracle, mgr.prm:EXTRACT EXT_DB starting.
2016-10-28 10:33:49INFO OGG-00992Oracle GoldenGate Capture for Oracle, ext_db.prm:EXTRACT EXT_DB starting.
2016-10-28 10:33:49ERROR OGG-00446Oracle GoldenGate Capture for Oracle, ext_db.prm:Could not open checkpoint file /u01/ogg11
/dirchk/EXT_CDB.cpe, mode 1 (error 2, No such file or directory).
2016-10-28 10:33:49ERROR OGG-01668Oracle GoldenGate Capture for Oracle, ext_db.prm:PROCESS ABENDING.
2016-10-28 10:37:30INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): delete ext_cdb.
2016-10-28 10:37:58INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add extract ext_d
b tranlog,begin now.
2016-10-28 10:38:06INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add exttrail ./di
rdat/aa extrace ext_db.
2016-10-28 10:38:20INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add exttrail ./di
rdat/aa extract ext_db.
2016-10-28 10:38:39INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start ext_db.
2016-10-28 10:38:39INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host 192.168.
1.88 (START EXTRACT EXT_DB ).
2016-10-28 10:38:39INFO OGG-00975Oracle GoldenGate Manager for Oracle, mgr.prm:EXTRACT EXT_DB starting.
2016-10-28 10:38:39INFO OGG-00992Oracle GoldenGate Capture for Oracle, ext_db.prm:EXTRACT EXT_DB starting.
2016-10-28 10:38:39ERROR OGG-00446Oracle GoldenGate Capture for Oracle, ext_db.prm:Could not open checkpoint file /u01/ogg11
/dirchk/EXT_CDB.cpe, mode 1 (error 2, No such file or directory).
2016-10-28 10:38:39ERROR OGG-01668Oracle GoldenGate Capture for Oracle, ext_db.prm:PROCESS ABENDING.
2016-10-28 10:42:02INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit params ext_d
b.
2016-10-28 10:42:15INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start ext_db.
2016-10-28 10:42:15INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host 192.168.
1.88 (START EXTRACT EXT_DB ).
2016-10-28 10:42:15INFO OGG-00975Oracle GoldenGate Manager for Oracle, mgr.prm:EXTRACT EXT_DB starting.
2016-10-28 10:42:15INFO OGG-00992Oracle GoldenGate Capture for Oracle, ext_db.prm:EXTRACT EXT_DB starting.
2016-10-28 10:42:15INFO OGG-01635Oracle GoldenGate Capture for Oracle, ext_db.prm:BOUNDED RECOVERY: reset to initial or alt
ered checkpoint.
2016-10-28 10:42:16INFO OGG-01515Oracle GoldenGate Capture for Oracle, ext_db.prm:Positioning to begin time Oct 28, 2016 10
:37:58 AM.
2016-10-28 10:42:17INFO OGG-01516Oracle GoldenGate Capture for Oracle, ext_db.prm:Positioned to Sequence 11, RBA 59398672,
Oct 28, 2016 10:37:58 AM.
2016-10-28 10:42:17INFO OGG-00993Oracle GoldenGate Capture for Oracle, ext_db.prm:EXTRACT EXT_DB started.
2016-10-28 10:42:17INFO OGG-01052Oracle GoldenGate Capture for Oracle, ext_db.prm:No recovery is required for target file .
/dirdat/aa000000, at RBA 0 (file not opened).
2016-10-28 10:42:17INFO OGG-01478Oracle GoldenGate Capture for Oracle, ext_db.prm:Output file ./dirdat/aa is using format R
ELEASE 10.4/11.1.
2016-10-28 10:42:17INFO OGG-01517Oracle GoldenGate Capture for Oracle, ext_db.prm:Position of first record processed Sequen
ce 11, RBA 59398672, SCN 0.259079, Oct 28, 2016 10:37:59 AM.
2016-10-28 10:42:26INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start pm_db.
2016-10-28 10:42:26INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host 192.168.
1.88 (START EXTRACT PM_DB ).
2016-10-28 10:42:26INFO OGG-00975Oracle GoldenGate Manager for Oracle, mgr.prm:EXTRACT PM_DB starting.
2016-10-28 10:42:26INFO OGG-00992Oracle GoldenGate Capture for Oracle, pm_db.prm:EXTRACT PM_DB starting.
2016-10-28 10:42:26INFO OGG-00993Oracle GoldenGate Capture for Oracle, pm_db.prm:EXTRACT PM_DB started.
2016-10-28 10:42:26WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:42:36WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:42:46WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:42:56WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:43:06WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:43:16WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:43:26WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:43:36WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:43:46WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:43:56WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:44:06WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:44:16ERROR OGG-01224Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused); ret
ries exceeded.
2016-10-28 10:44:16ERROR OGG-01668Oracle GoldenGate Capture for Oracle, pm_db.prm:PROCESS ABENDING.
启动 target 端 mgr:
GGSCI (shost) 43> exit
$ ssh 192.168.1.123
oracle@192.168.1.123's password:
Last login: Thu Oct6 09:55:01 2016 from 192.168.1.25
$ cd $GGHOME
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER STOPPED
GGSCI (node1) 2> start mgr
Manager started.
GGSCI (node1) 3> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (node1) 4> exit
$ exit
logout
Connection to 192.168.1.123 closed.
重新启动源端pm_db:
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 9i on Oct4 2011 23:49:32
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (shost) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_DB 00:00:00 00:00:09
EXTRACT ABENDED PM_DB 00:00:00 00:13:21
GGSCI (shost) 2> start pm_db
Sending START request to MANAGER ...
EXTRACT PM_DB starting
GGSCI (shost) 3> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_DB 00:00:00 00:00:10
EXTRACT RUNNING PM_DB 00:00:00 00:13:32
GGSCI (shost) 4> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_DB 00:00:00 00:00:05
EXTRACT RUNNING PM_DB 00:00:00 00:00:04
GGSCI (shost) 5> view ggsevt
2016-10-28 10:42:15INFO OGG-00975Oracle GoldenGate Manager for Oracle, mgr.prm:EXTRACT EXT_DB starting.
2016-10-28 10:42:15INFO OGG-00992Oracle GoldenGate Capture for Oracle, ext_db.prm:EXTRACT EXT_DB starting.
2016-10-28 10:42:15INFO OGG-01635Oracle GoldenGate Capture for Oracle, ext_db.prm:BOUNDED RECOVERY: reset to initial or alt
ered checkpoint.
2016-10-28 10:42:16INFO OGG-01515Oracle GoldenGate Capture for Oracle, ext_db.prm:Positioning to begin time Oct 28, 2016 10
:37:58 AM.
2016-10-28 10:42:17INFO OGG-01516Oracle GoldenGate Capture for Oracle, ext_db.prm:Positioned to Sequence 11, RBA 59398672,
Oct 28, 2016 10:37:58 AM.
2016-10-28 10:42:17INFO OGG-00993Oracle GoldenGate Capture for Oracle, ext_db.prm:EXTRACT EXT_DB started.
2016-10-28 10:42:17INFO OGG-01052Oracle GoldenGate Capture for Oracle, ext_db.prm:No recovery is required for target file .
/dirdat/aa000000, at RBA 0 (file not opened).
2016-10-28 10:42:17INFO OGG-01478Oracle GoldenGate Capture for Oracle, ext_db.prm:Output file ./dirdat/aa is using format R
ELEASE 10.4/11.1.
2016-10-28 10:42:17INFO OGG-01517Oracle GoldenGate Capture for Oracle, ext_db.prm:Position of first record processed Sequen
ce 11, RBA 59398672, SCN 0.259079, Oct 28, 2016 10:37:59 AM.
2016-10-28 10:42:26INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start pm_db.
2016-10-28 10:42:26INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host 192.168.
1.88 (START EXTRACT PM_DB ).
2016-10-28 10:42:26INFO OGG-00975Oracle GoldenGate Manager for Oracle, mgr.prm:EXTRACT PM_DB starting.
2016-10-28 10:42:26INFO OGG-00992Oracle GoldenGate Capture for Oracle, pm_db.prm:EXTRACT PM_DB starting.
2016-10-28 10:42:26INFO OGG-00993Oracle GoldenGate Capture for Oracle, pm_db.prm:EXTRACT PM_DB started.
2016-10-28 10:42:26WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:42:36WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:42:46WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:42:56WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:43:06WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:43:16WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:43:26WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:43:36WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:43:46WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:43:56WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:44:06WARNING OGG-01223Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused).
2016-10-28 10:44:16ERROR OGG-01224Oracle GoldenGate Capture for Oracle, pm_db.prm:TCP/IP error 111 (Connection refused); ret
ries exceeded.
2016-10-28 10:44:16ERROR OGG-01668Oracle GoldenGate Capture for Oracle, pm_db.prm:PROCESS ABENDING.
2016-10-28 10:45:41INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start pm_db.
2016-10-28 10:45:41INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host 192.168.
1.88 (START EXTRACT PM_DB ).
2016-10-28 10:45:41INFO OGG-00975Oracle GoldenGate Manager for Oracle, mgr.prm:EXTRACT PM_DB starting.
2016-10-28 10:45:41INFO OGG-00992Oracle GoldenGate Capture for Oracle, pm_db.prm:EXTRACT PM_DB starting.
2016-10-28 10:45:41INFO OGG-00993Oracle GoldenGate Capture for Oracle, pm_db.prm:EXTRACT PM_DB started.
2016-10-28 10:45:47INFO OGG-01226Oracle GoldenGate Capture for Oracle, pm_db.prm:Socket buffer size set to 27985 (flush siz
e 27985).
2016-10-28 10:45:47INFO OGG-01052Oracle GoldenGate Capture for Oracle, pm_db.prm:No recovery is required for target file ./
dirdat/bb000000, at RBA 0 (file not opened).
2016-10-28 10:45:47INFO OGG-01478Oracle GoldenGate Capture for Oracle, pm_db.prm:Output file ./dirdat/bb is using format RE
LEASE 10.4/11.1.
GGSCI (shost) 6> exit
查看目标端生成trail文件：
$ ssh 192.168.1.123
oracle@192.168.1.123's password:
Last login: Thu Oct 27 22:44:14 2016 from 192.168.1.88
$ cd $GGHOME
$ cd dirdat
$ ls -l
total 0
-rw-r-----. 1 oracle oinstall 0 Oct 27 22:44 bb000000
$ exit
logout
Connection to 192.168.1.123 closed.
$ exit
logout
执行数据首次实例化：
# su - oracle
$ cd $GGHOME
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 9i on Oct4 2011 23:49:32
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (shost) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_DB 00:00:00 00:00:10
EXTRACT RUNNING PM_DB 00:00:00 00:00:01
确认extrace,data pump 启动后：
GGSCI (shost) 2> exit
查看源端数据库当前scn:
$ sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Fri Oct 28 10:47:06 2016
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
260562
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
exp 一致性导出源端数据：
$ cd
$ pwd
/home/oracle
$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
$ exp system/oracle file=/u01/app/oracle/exp_hr.dmp owner=hr flashback_scn=190646 log= exp_hr.log
Export: Release 9.2.0.8.0 - Production on Fri Oct 28 10:49:29 2016
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified users ...
EXP-00008: ORACLE error 1466 encountered
ORA-01466: unable to read data - table definition has changed
EXP-00000: Export terminated unsuccessfully
上面指定的scn有误，调整为正确的scn导出数据正常：
$ exp system/oracle file=/u01/app/oracle/exp_hr.dmp owner=hr flashback_scn=260562 log= exp_hr.log
Export: Release 9.2.0.8.0 - Production on Fri Oct 28 10:49:46 2016
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HR
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HR
About to export HR's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HR's tables via Conventional Path ...
. . exporting table COUNTRIES 25 rows exported
. . exporting table DEPARTMENTS 27 rows exported
. . exporting table EMPLOYEES 107 rows exported
. . exporting table JOBS 19 rows exported
. . exporting table JOB_HISTORY 10 rows exported
. . exporting table LOCATIONS 23 rows exported
. . exporting table REGIONS 4 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
$
$
$
再次确认源端用户表空间相关信息：
$ sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Fri Oct 28 10:50:19 2016
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='HR';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEMPORARY_TABLESPACE
------------------------------
HR EXAMPLE
TEMP
查看源端hr用户的数据表：
SQL> alter user hr identified by hr account unlock;
SP2-0640: Not connected
SQL> connect /as sysdba
Connected.
SQL> alter user hr identified by hr account unlock;
User altered.
SQL> connect hr/hr
Connected.
SQL> select tname from tab;
TNAME
------------------------------
COUNTRIES
DEPARTMENTS
EMPLOYEES
EMP_DETAILS_VIEW
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
8 rows selected.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
连接到目标端，创建相应的表空间及用户：
$ ssh 192.168.1.123
oracle@192.168.1.123's password:
Last login: Thu Oct 27 22:45:31 2016 from 192.168.1.88
$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 27 22:51:13 2016
Copyright (c) 1982, 2014, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected to an idle instance.
SQL> exit
Disconnected
$ export ORACLE_SID=orcl
$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 27 22:51:49 2016
Copyright (c) 1982, 2014, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> create tablespace example datafile size 2G;
Tablespace created.
SQL> l
1create user hr identified by hr
2account unlock
3default tablespace example
4temporary tablespace temp
5* quota unlimited on example
SQL> /
User created.
赋予用户相应的权限：
SQL> grant dba to hr;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
源端拷贝dmp文件至目标端导入：
$ echo $ORACLE_SID
orcl
$ exit
logout
Connection to 192.168.1.123 closed.
$ cd /u01/app/oracle
$ ls -l
total 187472
drwxr-xr-x 3 oracle oinstall 4096 Oct 26 14:48 admin
drwxrwxr-x 7 oracle oinstall 4096 Oct 26 13:41 doc
-rw-r--r-- 1 oracle oinstall 114032640 Oct 27 10:48 exp_full.dmp
-rw-r--r-- 1 oracle oinstall 57344 Oct 28 10:49 exp_hr.dmp
-rw-r--r-- 1 oracle oinstall77651968 Oct 27 10:46 exp_sh.dmp
drwxrwxr-x 3 oracle oinstall 4096 Oct 26 13:41 jre
drwxr-xr-x 3 oracle oinstall 4096 Oct 26 14:48 oradata
drwxrwxr-x13 oracle oinstall 4096 Oct 26 14:42 oraInventory
drwxr-xr-x 3 oracle oinstall 4096 Oct 26 13:18 product
$ scp -pr exp_hr.dmp 192.168.1.123:/u01/app/oracle/
oracle@192.168.1.123's password:
exp_hr.dmp 100% 56KB56.0KB/s 00:00
登录至目标端导入数据：
$ ssh 192.168.1.123
oracle@192.168.1.123's password:
Last login: Thu Oct 27 22:51:08 2016 from 192.168.1.88
$ cd /u01/app/oracle
$ ls -l
total 84
drwxrwx---.4 oracle oinstall4096 Oct 27 00:29 admin
drwxrwx---.4 oracle oinstall4096 Oct 26 23:22 audit
drwxrwx---.3 oracle oinstall4096 Jun6 01:43 cfgtoollogs
drwxrwxr-x.2 oracle oinstall4096 Oct6 08:58 checkpoints
drwxrwxr-x. 19 oracle oinstall4096 Jun6 01:42 diag
drwxr-xr-x.2 oracle oinstall4096 Oct 27 02:20 dir1
-rw-r--r--.1 oracle oinstall 57344 Oct 282016 exp_hr.dmp
drwxrwxr-x.3 oracle oinstall4096 Jun6 01:38 product
$ pwd
/u01/app/oracle
$imp userid=system/oracle@orclfromuser=hr touser=hr file=exp_hr.dmp log=imp_hr.log
Import: Release 12.1.0.2.0 - Production on Thu Oct 27 22:57:06 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing HR's objects into HR
. . importing table "COUNTRIES" 25 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "COUNTRIES" TO "OE""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'OE' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT REFERENCES ON "COUNTRIES" TO "OE""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'OE' does not exist
. . importing table "DEPARTMENTS" 27 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "DEPARTMENTS" TO "OE""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'OE' does not exist
. . importing table "EMPLOYEES" 107 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "EMPLOYEES" TO "OE""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'OE' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT REFERENCES ON "EMPLOYEES" TO "OE""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'OE' does not exist
. . importing table "JOBS" 19 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "JOBS" TO "OE""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'OE' does not exist
. . importing table "JOB_HISTORY" 10 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "JOB_HISTORY" TO "OE""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'OE' does not exist
. . importing table "LOCATIONS" 23 rows imported
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT SELECT ON "LOCATIONS" TO "OE""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'OE' does not exist
IMP-00017: following statement failed with ORACLE error 1917:
"GRANT REFERENCES ON "LOCATIONS" TO "OE""
IMP-00003: ORACLE error 1917 encountered
ORA-01917: user or role 'OE' does not exist
. . importing table "REGIONS" 4 rows imported
About to enable constraints...
Import terminated successfully with warnings.
忽略报错
确认目标端导入的表：
$ sqlplus hr/hr@orcl
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 27 22:58:27 2016
Copyright (c) 1982, 2014, Oracle.All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
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> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
目标端准备配置replicat:
$ cd $GGHOME
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (node1) 2> exit
目标端建有orcc,orcl两个数据库实例，确认数据库实例为non-cdb实例：
$ echo $ORACLE_SID
orcc
$ export ORACLE_SID=orcl
目标端创建goldengate管理用户ggs:
$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 27 22:59:44 2016
Copyright (c) 1982, 2014, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> create user ggs identified by ggs account unlock;
User created.
SQL> grant dba to ggs;
Grant succeeded.
SQL> connect ggs/ggs
Connected.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
配置目标端replicat进程：
$ echo $ORACLE_SID
orcl
$ cd $GGHOME
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
GGSCI (node1) 2> dblogin userid ggs,password ggs
Successfully logged into database.
配置replicat进程参数：
GGSCI (node1 as ggs@orcl) 3> edit params rep_db
GGSCI (node1 as ggs@orcl) 4> view params rep_db
replicat rep_db
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV (ORACLE_SID="orcl")
USERID ggs, PASSWORD ggs
handlecollisions
assumetargetdefs
map hr.*, target hr.*;
配置检查点表：
GGSCI (node1 as ggs@orcl) 6> edit params ./GLOBALS
添加检查点表：
GGSCI (node1 as ggs@orcl) 9> add checkpointtable ggs.chkt
Successfully created checkpoint table ggs.chkt.
添加replicat进程：
GGSCI (node1 as ggs@orcl) 10> add replicat rep_db,exttrail ./dirdat/bb,checkpointtable ggs.chkt
REPLICAT added.
GGSCI (node1 as ggs@orcl) 11> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP_DB 00:00:00 00:00:03
启动replicat进程，指定scn:
GGSCI (node1 as ggs@orcl) 12> start rep_db,aftercsn 260562
Sending START request to MANAGER ...
REPLICAT REP_DB starting
GGSCI (node1 as ggs@orcl) 13> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REP_DB 00:00:00 00:01:09
检查日志，发现报错如下：
GGSCI (node1 as ggs@orcl) 14> view ggsevt
2016-10-26 08:18:00INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-26 08:18:09INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): stop mgr.
2016-10-26 08:18:10INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host [192.168
.1.123]:15094 (STOP).
2016-10-26 08:18:10INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #6).
2016-10-26 08:18:10WARNING OGG-00938Oracle GoldenGate Manager for Oracle, mgr.prm:Manager is stopping at user request.
2016-10-26 08:18:15INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start mgr.
2016-10-26 08:18:15WARNING OGG-01877Oracle GoldenGate Manager for Oracle, mgr.prm:Missing explicit accessrule for server colle
ctor.
2016-10-26 08:18:15INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #8).
2016-10-26 08:18:15INFO OGG-00983Oracle GoldenGate Manager for Oracle, mgr.prm:Manager started (port 7809).
2016-10-26 08:18:17INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-26 08:31:59INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-26 08:32:01INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): stop mgr.
2016-10-26 08:32:01INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host [192.168
.1.123]:15109 (STOP).
2016-10-26 08:32:01INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #6).
2016-10-26 08:32:01WARNING OGG-00938Oracle GoldenGate Manager for Oracle, mgr.prm:Manager is stopping at user request.
2016-10-26 08:32:06INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 01:20:14INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 01:32:15INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 22:44:24INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 22:44:27INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start mgr.
2016-10-27 22:44:27WARNING OGG-01877Oracle GoldenGate Manager for Oracle, mgr.prm:Missing explicit accessrule for server colle
ctor.
2016-10-27 22:44:27INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #8).
2016-10-27 22:44:27INFO OGG-00983Oracle GoldenGate Manager for Oracle, mgr.prm:Manager started (port 7809).
2016-10-27 22:44:29INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 22:44:48INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from EXTRACT on host [192.1
68.1.88]:32798 (START SERVER CPU -1 PRI -1TIMEOUT 300 PARAMS ).
2016-10-27 22:44:48INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #5).
2016-10-27 22:44:48INFO OGG-01677Oracle GoldenGate Collector for Oracle:Waiting for connection (started dynamically).
2016-10-27 22:44:48INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from SERVER on host [::1]:4
2886 (REPORT 3990 7819).
2016-10-27 22:44:48INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #1).
2016-10-27 22:44:48INFO OGG-00974Oracle GoldenGate Manager for Oracle, mgr.prm:Manager started collector process (Port 7819
).
2016-10-27 22:44:48INFO OGG-01228Oracle GoldenGate Collector for Oracle:Timeout in 300 seconds.
2016-10-27 22:44:53INFO OGG-01229Oracle GoldenGate Collector for Oracle:Connected to :32799.
2016-10-27 22:44:53INFO OGG-01669Oracle GoldenGate Collector for Oracle:Opening ./dirdat/bb000000 (byte -1, current EOF 0).
2016-10-27 22:59:06INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 23:00:42INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 23:00:51INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): dblogin userid gg
s password ***.
2016-10-27 23:01:11INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit params rep_d
b.
2016-10-27 23:04:27INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit ./GLOBALS.
2016-10-27 23:04:42INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit params ./GLO
BALS.
2016-10-27 23:05:05INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add checkpointtab
le.
2016-10-27 23:05:14INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add checkpointtab
le ogg.chkt.
2016-10-27 23:05:28INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add checkpointtab
le ggs.chkt.
2016-10-27 23:06:20INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add replicat rep_
db exttrail ./dirdat/bb,checkpointtable ggs.chkt.
2016-10-27 23:06:24INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 23:07:25INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start rep_db afte
rcsn 260562.
2016-10-27 23:07:25INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host [192.168
.1.123]:34307 (START REPLICAT REP_DB AFTERCSN 260562).
2016-10-27 23:07:25INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #6).
2016-10-27 23:07:25INFO OGG-00975Oracle GoldenGate Manager for Oracle, mgr.prm:REPLICAT REP_DB starting.
2016-10-27 23:07:26INFO OGG-00995Oracle GoldenGate Delivery for Oracle, rep_db.prm:REPLICAT REP_DB starting.
2016-10-27 23:07:26INFO OGG-03059Oracle GoldenGate Delivery for Oracle, rep_db.prm:Operating system character set identifie
d as UTF-8.
2016-10-27 23:07:26INFO OGG-02695Oracle GoldenGate Delivery for Oracle, rep_db.prm:ANSI SQL parameter syntax is used for pa
rameter parsing.
2016-10-27 23:07:26INFO OGG-02095Oracle GoldenGate Delivery for Oracle, rep_db.prm:Successfully set environment variable OR
ACLE_SID=orcl.
2016-10-27 23:07:27INFO OGG-01971Oracle GoldenGate Delivery for Oracle, rep_db.prm:The previous message, 'INFO OGG-02095',
repeated 1 times.
2016-10-27 23:07:27WARNING OGG-06471Oracle GoldenGate Delivery for Oracle, rep_db.prm:Unable to disable trigger firing.
2016-10-27 23:07:27INFO OGG-01815Oracle GoldenGate Delivery for Oracle, rep_db.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/ogg12c/dirtmp.
2016-10-27 23:07:28ERROR OGG-02091Oracle GoldenGate Delivery for Oracle, rep_db.prm:Operation not supported because enable_g
oldengate_replication is not set to true.
2016-10-27 23:07:28ERROR OGG-01668Oracle GoldenGate Delivery for Oracle, rep_db.prm:PROCESS ABENDING.
2016-10-27 23:07:29INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
目标端设置 enable_goldengate_replication 为TRUE:
GGSCI (node1 as ggs@orcl) 15> exit
$ echo $ORACLE_SID
orcl
$ sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 27 23:08:34 2016
Copyright (c) 1982, 2014, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> show parameter goldengate_replicat
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 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
目标端重新启动replicat:
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REP_DB 00:00:00 00:02:59
GGSCI (node1) 2> start rep_db,aftercsn 260562
Sending START request to MANAGER ...
REPLICAT REP_DB starting
GGSCI (node1) 3> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_DB 00:00:00 00:00:01
GGSCI (node1) 4> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_DB 00:00:00 00:00:03
GGSCI (node1) 5> view ggsevt
2016-10-26 08:18:00INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-26 08:18:09INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): stop mgr.
2016-10-26 08:18:10INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host [192.168
.1.123]:15094 (STOP).
2016-10-26 08:18:10INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #6).
2016-10-26 08:18:10WARNING OGG-00938Oracle GoldenGate Manager for Oracle, mgr.prm:Manager is stopping at user request.
2016-10-26 08:18:15INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start mgr.
2016-10-26 08:18:15WARNING OGG-01877Oracle GoldenGate Manager for Oracle, mgr.prm:Missing explicit accessrule for server colle
ctor.
2016-10-26 08:18:15INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #8).
2016-10-26 08:18:15INFO OGG-00983Oracle GoldenGate Manager for Oracle, mgr.prm:Manager started (port 7809).
2016-10-26 08:18:17INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-26 08:31:59INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-26 08:32:01INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): stop mgr.
2016-10-26 08:32:01INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host [192.168
.1.123]:15109 (STOP).
2016-10-26 08:32:01INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #6).
2016-10-26 08:32:01WARNING OGG-00938Oracle GoldenGate Manager for Oracle, mgr.prm:Manager is stopping at user request.
2016-10-26 08:32:06INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 01:20:14INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 01:32:15INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 22:44:24INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 22:44:27INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start mgr.
2016-10-27 22:44:27WARNING OGG-01877Oracle GoldenGate Manager for Oracle, mgr.prm:Missing explicit accessrule for server colle
ctor.
2016-10-27 22:44:27INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #8).
2016-10-27 22:44:27INFO OGG-00983Oracle GoldenGate Manager for Oracle, mgr.prm:Manager started (port 7809).
2016-10-27 22:44:29INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 22:44:48INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from EXTRACT on host [192.1
68.1.88]:32798 (START SERVER CPU -1 PRI -1TIMEOUT 300 PARAMS ).
2016-10-27 22:44:48INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #5).
2016-10-27 22:44:48INFO OGG-01677Oracle GoldenGate Collector for Oracle:Waiting for connection (started dynamically).
2016-10-27 22:44:48INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from SERVER on host [::1]:4
2886 (REPORT 3990 7819).
2016-10-27 22:44:48INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #1).
2016-10-27 22:44:48INFO OGG-00974Oracle GoldenGate Manager for Oracle, mgr.prm:Manager started collector process (Port 7819
).
2016-10-27 22:44:48INFO OGG-01228Oracle GoldenGate Collector for Oracle:Timeout in 300 seconds.
2016-10-27 22:44:53INFO OGG-01229Oracle GoldenGate Collector for Oracle:Connected to :32799.
2016-10-27 22:44:53INFO OGG-01669Oracle GoldenGate Collector for Oracle:Opening ./dirdat/bb000000 (byte -1, current EOF 0).
2016-10-27 22:59:06INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 23:00:42INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 23:00:51INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): dblogin userid gg
s password ***.
2016-10-27 23:01:11INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit params rep_d
b.
2016-10-27 23:04:27INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit ./GLOBALS.
2016-10-27 23:04:42INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): edit params ./GLO
BALS.
2016-10-27 23:05:05INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add checkpointtab
le.
2016-10-27 23:05:14INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add checkpointtab
le ogg.chkt.
2016-10-27 23:05:28INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add checkpointtab
le ggs.chkt.
2016-10-27 23:06:20INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): add replicat rep_
db exttrail ./dirdat/bb,checkpointtable ggs.chkt.
2016-10-27 23:06:24INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 23:07:25INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start rep_db afte
rcsn 260562.
2016-10-27 23:07:25INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host [192.168
.1.123]:34307 (START REPLICAT REP_DB AFTERCSN 260562).
2016-10-27 23:07:25INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #6).
2016-10-27 23:07:25INFO OGG-00975Oracle GoldenGate Manager for Oracle, mgr.prm:REPLICAT REP_DB starting.
2016-10-27 23:07:26INFO OGG-00995Oracle GoldenGate Delivery for Oracle, rep_db.prm:REPLICAT REP_DB starting.
2016-10-27 23:07:26INFO OGG-03059Oracle GoldenGate Delivery for Oracle, rep_db.prm:Operating system character set identifie
d as UTF-8.
2016-10-27 23:07:26INFO OGG-02695Oracle GoldenGate Delivery for Oracle, rep_db.prm:ANSI SQL parameter syntax is used for pa
rameter parsing.
2016-10-27 23:07:26INFO OGG-02095Oracle GoldenGate Delivery for Oracle, rep_db.prm:Successfully set environment variable OR
ACLE_SID=orcl.
2016-10-27 23:07:27INFO OGG-01971Oracle GoldenGate Delivery for Oracle, rep_db.prm:The previous message, 'INFO OGG-02095',
repeated 1 times.
2016-10-27 23:07:27WARNING OGG-06471Oracle GoldenGate Delivery for Oracle, rep_db.prm:Unable to disable trigger firing.
2016-10-27 23:07:27INFO OGG-01815Oracle GoldenGate Delivery for Oracle, rep_db.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/ogg12c/dirtmp.
2016-10-27 23:07:28ERROR OGG-02091Oracle GoldenGate Delivery for Oracle, rep_db.prm:Operation not supported because enable_g
oldengate_replication is not set to true.
2016-10-27 23:07:28ERROR OGG-01668Oracle GoldenGate Delivery for Oracle, rep_db.prm:PROCESS ABENDING.
2016-10-27 23:07:29INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 23:09:19INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 23:09:36INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): start rep_db afte
rcsn 260562.
2016-10-27 23:09:36INFO OGG-00963Oracle GoldenGate Manager for Oracle, mgr.prm:Command received from GGSCI on host [192.168
.1.123]:34310 (START REPLICAT REP_DB AFTERCSN 260562).
2016-10-27 23:09:36INFO OGG-00960Oracle GoldenGate Manager for Oracle, mgr.prm:Access granted (rule #6).
2016-10-27 23:09:36INFO OGG-00975Oracle GoldenGate Manager for Oracle, mgr.prm:REPLICAT REP_DB starting.
2016-10-27 23:09:36INFO OGG-00995Oracle GoldenGate Delivery for Oracle, rep_db.prm:REPLICAT REP_DB starting.
2016-10-27 23:09:36INFO OGG-03059Oracle GoldenGate Delivery for Oracle, rep_db.prm:Operating system character set identifie
d as UTF-8.
2016-10-27 23:09:36INFO OGG-02695Oracle GoldenGate Delivery for Oracle, rep_db.prm:ANSI SQL parameter syntax is used for pa
rameter parsing.
2016-10-27 23:09:36INFO OGG-02095Oracle GoldenGate Delivery for Oracle, rep_db.prm:Successfully set environment variable OR
ACLE_SID=orcl.
2016-10-27 23:09:37INFO OGG-01971Oracle GoldenGate Delivery for Oracle, rep_db.prm:The previous message, 'INFO OGG-02095',
repeated 1 times.
2016-10-27 23:09:37INFO OGG-06451Oracle GoldenGate Delivery for Oracle, rep_db.prm:Triggers will be suppressed by default.
2016-10-27 23:09:37INFO OGG-01815Oracle GoldenGate Delivery for Oracle, rep_db.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/ogg12c/dirtmp.
2016-10-27 23:09:37INFO OGG-00996Oracle GoldenGate Delivery for Oracle, rep_db.prm:REPLICAT REP_DB started.
2016-10-27 23:09:37INFO OGG-02243Oracle GoldenGate Delivery for Oracle, rep_db.prm:Opened trail file ./dirdat/bb000000 at 2
016-10-27 23:09:37.714785.
2016-10-27 23:09:38INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
2016-10-27 23:09:41INFO OGG-00987Oracle GoldenGate Command Interpreter for Oracle:GGSCI command (oracle): info all.
GGSCI (node1) 6> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_DB 00:00:00 00:00:08
GGSCI (node1) 7> lag rep_db
2016-10-27 23:10:01INFO OGG-14054Lag from heartbeat table requires DBLOGIN.
Sending GETLAG request to REPLICAT REP_DB ...
No records yet processed.
At EOF, no more records to process.
GGSCI (node1) 8> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_DB 00:00:00 00:00:07
GGSCI (node1) 9> exit
$ exit
logout
Connection to 192.168.1.123 closed.
确认源端lag:
$ cd $GGHOME
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 9i on Oct4 2011 23:49:32
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (shost) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_DB 00:00:00 00:00:02
EXTRACT RUNNING PM_DB 00:00:00 00:00:07
GGSCI (shost) 2> lag ext_db
Sending GETLAG request to EXTRACT EXT_DB ...
No records yet processed.
At EOF, no more records to process.
GGSCI (shost) 3> lag pm_db
Sending GETLAG request to EXTRACT PM_DB ...
No records yet processed.
At EOF, no more records to process.
GGSCI (shost) 4> exit
登录目标端查询 lag:
$ ssh 192.168.1.123
oracle@192.168.1.123's password:
Last login: Thu Oct 27 22:55:58 2016 from 192.168.1.88
$ cd $GGHOME
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (node1) 1> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_DB 00:00:00 00:00:01
GGSCI (node1) 2> view params rep_db
replicat rep_db
SETENV (ORACLE_SID="orcl")
USERID ggs, PASSWORD ggs
handlecollisions
assumetargetdefs
map hr.*, target hr.*;
会话级别取消handlecollisions:
GGSCI (node1) 3> send replicat rep_db nohandlecollisions
Sending NOHANDLECOLLISIONS request to REPLICAT REP_DB ...
REP_DB No tables found matching * to set NOHANDLECOLLISIONS.
GGSCI (node1) 4> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_DB 00:00:00 00:00:02
GGSCI (node1) 5> edit params rep_db
GGSCI (node1) 6> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_DB 00:00:00 00:00:02
GGSCI (node1) 7> exit
$ exit
logout
Connection to 192.168.1.123 closed.
测试：
$ sqlplus hr/hr
SQL*Plus: Release 9.2.0.8.0 - Production on Fri Oct 28 11:12:46 2016
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
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> select sum(salary) from employees;
SUM(SALARY)
-----------
691400
SQL> update employees set salary=salary+1000;
107 rows updated.
SQL> commit;
Commit complete.
SQL> select sum(salary) from employees;
SUM(SALARY)
-----------
798400
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100
Linux, x64, 64bit (optimized), Oracle 9i on Oct4 2011 23:49:32
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (shost) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_DB 00:00:00 00:00:06
EXTRACT RUNNING PM_DB 00:00:00 00:00:09
GGSCI (shost) 2> stats ext_db
Sending STATS request to EXTRACT EXT_DB ...
Start of Statistics at 2016-10-28 11:14:38.
Output to ./dirdat/aa:
Extracting from HR.EMPLOYEES to HR.EMPLOYEES:
*** Total statistics since 2016-10-28 11:14:05 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
*** Daily statistics since 2016-10-28 11:14:05 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
*** Hourly statistics since 2016-10-28 11:14:05 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
*** Latest statistics since 2016-10-28 11:14:05 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
End of Statistics.
GGSCI (shost) 3> stats pm_db
Sending STATS request to EXTRACT PM_DB ...
Start of Statistics at 2016-10-28 11:14:50.
Output to ./dirdat/bb:
Extracting from HR.EMPLOYEES to HR.EMPLOYEES:
*** Total statistics since 2016-10-28 11:14:06 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
*** Daily statistics since 2016-10-28 11:14:06 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
*** Hourly statistics since 2016-10-28 11:14:06 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
*** Latest statistics since 2016-10-28 11:14:06 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
End of Statistics.
GGSCI (shost) 4> exit
$ ssh 192.168.1.123
oracle@192.168.1.123's password:
Last login: Thu Oct 27 23:10:43 2016 from 192.168.1.88
$ cd $GGHOME
GGSCI (node1) 26> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_DB 00:00:00 00:00:02
GGSCI (node1) 27> stats rep_db
Sending STATS request to REPLICAT REP_DB ...
Start of Statistics at 2016-10-27 23:21:06.
Replicating from HR.EMPLOYEES to HR.EMPLOYEES:
*** Total statistics since 2016-10-27 23:20:57 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
*** Daily statistics since 2016-10-27 23:20:57 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
*** Hourly statistics since 2016-10-27 23:20:57 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
*** Latest statistics since 2016-10-27 23:20:57 ***
Total inserts 0.00
Total updates 107.00
Total deletes 0.00
Total discards 0.00
Total operations 107.00
End of Statistics.
GGSCI (node1) 28> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_DB 00:00:00 00:00:06
GGSCI (node1) 29> view report rep_db
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 03:29:37
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
Starting at 2016-10-27 23:20:51
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Jan 29 11:47:41 EST 2013, Release 2.6.32-358.el6.x86_64
Node: node1
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: 4724
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2016-10-27 23:20:51INFO OGG-03059Operating system character set identified as UTF-8.
2016-10-27 23:20:51INFO OGG-02695ANSI SQL parameter syntax is used for parameter parsing.
2016-10-27 23:20:51INFO OGG-02095Successfully set environment variable NLS_LANG=AMERICAN_AMERICA.ZHS16GBK.
2016-10-27 23:20:51INFO OGG-02095Successfully set environment variable ORACLE_SID=orcl.
replicat rep_db
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
2016-10-27 23:20:51INFO OGG-02095Successfully set environment variable NLS_LANG=AMERICAN_AMERICA.ZHS16GBK.
SETENV (ORACLE_SID="orcl")
2016-10-27 23:20:51INFO OGG-02095Successfully set environment variable ORACLE_SID=orcl.
USERID ggs, PASSWORD ***
assumetargetdefs
map hr.*, target hr.*;
2016-10-27 23:20:51INFO OGG-06451Triggers will be suppressed by default.
2016-10-27 23:20:51INFO OGG-01815Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON)anon free: munmap
file alloc: mmap(MAP_SHARED)file free: munmap
target directories:
/u01/ogg12c/dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHEPAGEOUTSIZE (default): 4M
PROCESS VM AVAIL FROM OS (min): 4G
CACHESIZEMAX (strict force to disk): 3.41G
Database Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Database Language and Character Set:
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"
***********************************************************************
** Run Time Messages **
***********************************************************************
2016-10-27 23:20:51INFO OGG-02243Opened trail file ./dirdat/bb000000 at 2016-10-27 23:20:51.615848.
2016-10-27 23:20:51WARNING OGG-03509Using NLS_LANG character set zhs16gbk as the source database character set.
2016-10-27 23:20:51INFO OGG-06506Wildcard MAP resolved (entry hr.*): map "HR"."EMPLOYEES", target hr."EMPLOYEES".
2016-10-27 23:20:57INFO OGG-02759The definition for table HR.EMPLOYEES is obtained from the target database.
2016-10-27 23:20:57INFO OGG-06511Using following columns in default map by name: EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, P
HONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID.
2016-10-27 23:20:57INFO OGG-06510Using the following key columns for target table HR.EMPLOYEES: EMPLOYEE_ID.
2016-10-27 23:21:06INFO OGG-01021Command received from GGSCI: STATS.
GGSCI (node1) 30> info all
Program Status Group Lag at ChkptTime Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_DB 00:00:00 00:00:00
GGSCI (node1) 31> exit
$ echo $ORACLE_SID
orcc
$ export ORACLE_SID=orcl
[
$ sqlplus hr/hr
SQL*Plus: Release 12.1.0.2.0 Production on Thu Oct 27 23:22:32 2016
Copyright (c) 1982, 2014, Oracle.All rights reserved.
Last Successful login time: Thu Oct 27 2016 22:58:27 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select sum(salary) from employees;
SUM(SALARY)
-----------
798400
2016/10/28 11:23:18
添加复制表测试(帖子字数限制删除）：
页:
[1]