If you database is on ASM and running on below version.
Environment used in Below exercise.
- Oracle 10.2.0.5 or later 10g R2 versions
- Oracle 11.2.0.2 or later 11g R2 versions
Environment used in Below exercise.
| SOURCE | TARGET | |
| DATABASE | DEVDB (2 NODE RAC) | TEST |
| DATABASE VERSION | 11.2.0.1 | 11.2.0.1 |
| PLATFORM | Linux, x64, 64bit | Linux, x64, 64bit |
| STORAGE | ASM | File-system |
1. Add remote listener for ASM.
2. EDIT ASM entry in Listener.ora in $GRID_HOME/network/admin for All node respectively & Reload Listener.
3. Add TNS entry for ASM on All Nodes
4. Test ASM connection from sqlplus as sys user.
5. Network configuration on Target.
6. Configure Goldengate extract & Datapump for Source.
7. Start extract & Datapump.
8. Configure Goldengate Replicate for Target.
9. Start replicate on target.
10. Confirm Goldengate connection on Source DB & ASM and Target DB.
11. Test Replication.
1) Add Remote listener for ASM.
ALTER SYSTEM SET remote_listener='RACD-SCAN:1521' SCOPE=BOTH SID='*';
2) Static registration of ASM in LISTNER.ORA IN $GRID_HOME/network/admin for All node respectively.
Edit below entry into Listener.ora intO $GRID_HOME/network/admin/listener.ora and Reload listener.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u00/app/11.2.0/grid)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = /u00/app/11.2.0/grid)
(SID_NAME = +ASM1)
)
)
---Reload listener.ora on All Nodes.
RACD1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $lsnrctl reload LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-JUN-2012 16:09:01 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) The command completed successfully
--Grid Infrastructure Network configuration on Source.
RACD1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $ll
total 36
-rw-r--r-- 1 oracle oinstall 187 May 7 2007 shrept.lst
drwxr-xr-x 2 oracle oinstall 4096 Jun 7 11:25 samples
drwxr-xr-x 11 oracle oinstall 4096 Jun 7 11:28 ..
-rw-r--r-- 1 oracle oinstall 552 Jun 7 11:44 listener.ora.bak.racd1
-rw-r--r-- 1 oracle oinstall 552 Jun 7 12:13 listener12060712PM1317.bak
-rw-r--r-- 1 oracle oinstall 216 Jun 7 12:13 sqlnet.ora
-rw-r--r-- 1 oracle oinstall 186 Jun 13 11:12 endpoints_listener.ora
-rw-r--r-- 1 oracle oinstall 948 Jun 17 16:07 listener.ora
drwxr-xr-x 3 oracle oinstall 4096 Jun 17 16:07 .
RACD1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $cat listener.ora
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent
LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent
LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u00/app/11.2.0/grid)
(PROGRAM = extproc)
)
(SID_DESC =
(ORACLE_HOME = /u00/app/11.2.0/grid)
(SID_NAME = +ASM1)
)
)
RACD1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $cat endpoints_listener.ora
LISTENER_RACD1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=RACD1-VIP)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.100.124)(PORT=1521)(IP=FIRST)))) # line added by Agent
RACD1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $
---Listener status after changes.
--Listener status
RACD1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-JUN-2012 18:25:38
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 17-JUN-2012 15:59:02
Uptime 0 days 2 hr. 26 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u00/app/11.2.0/grid/network/admin/listener.ora
Listener Log File /u00/app/oracle/diag/tnslsnr/RACD1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.124)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.134)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM1" has 1 instance(s).
Instance "+ASM1", status UNKNOWN, has 1 handler(s) for this service...
Service "DEVDB" has 1 instance(s).
Instance "DEVDB1", status READY, has 1 handler(s) for this service...
Service "DEVDBXDB" has 1 instance(s).
Instance "DEVDB1", status READY, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
--Listener services
RACD1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $lsnrctl services
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-JUN-2012 18:27:07
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "+ASM1" has 1 instance(s).
Instance "+ASM1", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "DEVDB" has 1 instance(s).
Instance "DEVDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER
Service "DEVDBXDB" has 1 instance(s).
Instance "DEVDB1", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=RACD1.localdomain.com)(PORT=31649))
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
The command completed successfully
3) ADDED TNS NAMES TO $ORACLE_HOME/network/admin/tnsnames.ora on All nodes.
+ASM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = RACD-SCAN)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = +ASM)
)
)
4) Test +ASM tnsenry from $ORACLE_HOME
-----From Node1 RACD1@:/u00/app/11.2.0/grid/network/admin :+ASM1 $. oraenv ORACLE_SID = [+ASM1] ? DEVDB1 The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u00/app/oracle RACD1@:/u00/app/11.2.0/grid/network/admin :DEVDB1 $sqlplus sys/Summer69@+ASM as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 17 18:12:29 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options 18:12:29 @+ASM-> ------From Node 2 RACD2@:/u00/app/11.2.0/grid/network/admin :+ASM2 $. oraenv ORACLE_SID = [+ASM2] ? DEVDB2 The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u00/app/oracle RACD2@:/u00/app/11.2.0/grid/network/admin :DEVDB2 $sqlplus sys/Summer69@+ASM as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 17 18:13:26 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options 18:13:15 @+ASM->
5) Network configuration on Target.
-- listener.ora on TARGET.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u00/app/oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.101 )(PORT = 1521))
)
)
-- Tns entry on TARGET
TEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.101 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST)
)
)
6) Configure Goldengate Extract & Datapump on Source
DBLOGIN is specially required for 10.2,for 11.1 DBLOGIN is not required but I did.As if you want to delete EXTRACT you have to first DBLOGIN
if you need help on syntax here it is.
GGSCI (RACD1.localdomain.com) > help add extract
--CONFIGURE EXTRACT RACDE
RACD1@:/u02/gghome :DEVDB1 $./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 11g on Oct 4 2011 23:49:46 Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved. GGSCI (RACD1.localdomain.com) 1> DBLOGIN USERID GGSUSER Password: Successfully logged into database. GGSCI (RACD1.localdomain.com) 3>ADD EXTRACT RACDE TRANLOG,BEGIN NOW,THREADS 2 GGSCI (RACD1.localdomain.com) 8>ADD EXTTRAIL ./dirdat/DE, EXTRACT RACDE EXTTRAIL added. GGSCI (RACD1.localdomain.com) 9> EDIT PARAMS RACDE EXTRACT RACDE ---ORACLE ENVIRONMET SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1") SETENV (ORACLE_SID = "DEVDB1") USERID ggsuser, PASSWORD Summer2011 TRANLOGOPTIONS ASMUSER sys@+ASM, ASMPASSWORD Summer69 DYNAMICRESOLUTION DISCARDFILE ./dirrpt/racde.dsc,PURGE, MEGABYTES 100 EXTTRAIL ./dirdat/DE --DDL REPLICATION DDL INCLUDE MAPPED OBJNAME TEST.* --DML replication for SCHEMA level. TABLE TEST.*;
--CONFIGURE DATAPUMP RACDP
GGSCI (RACD1.localdomain.com) 10> ADD EXTRACT RACDP, EXTTRAILSOURCE ./dirdat/DE,begin now EXTRACT added. GGSCI (RACD1.localdomain.com) 12> ADD RMTTRAIL ./dirdat/DP, EXTRACT RACDP, MEGABYTES 100 RMTTRAIL added. GGSCI (RACD1.localdomain.com) 13> edit params RACDP EXTRACT RACDP SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/db_1") SETENV (ORACLE_SID = "DEVDB1") USERID ggsuser, PASSWORD Summer2011 PASSTHRU RMTHOST 192.168.100.101, MGRPORT 7809 RMTTRAIL ./dirdat/DP TABLE TEST.*; GGSCI (RACD1.localdomain.com) 14> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED RACDE 00:00:00 00:04:44 EXTRACT STOPPED RACDP 00:00:00 00:01:14
7) START EXTRACT & DATAPUMP
GGSCI (RACD1.localdomain.com) 15> start racde Sending START request to MANAGER ... EXTRACT RACDE starting ----Output from tail -f ggserr.log at time of starting extract. 2012-06-17 17:01:06 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): edit params RACDP. 2012-06-17 17:01:49 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start racde. 2012-06-17 17:01:49 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.100.124 (START EXTRACT RACDE ). 2012-06-17 17:01:50 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT RACDE starting. 2012-06-17 17:01:50 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, racde.prm: EXTRACT RACDE starting. 2012-06-17 17:01:51 INFO OGG-01635 Oracle GoldenGate Capture for Oracle, racde.prm: BOUNDED RECOVERY: reset to initial or altered checkpoint. 2012-06-17 17:01:56 INFO OGG-00546 Oracle GoldenGate Capture for Oracle, racde.prm: Default thread stack size: 10485760. 2012-06-17 17:01:56 INFO OGG-01515 Oracle GoldenGate Capture for Oracle, racde.prm: Positioning to begin time Jun 17, 2012 4:57:01 PM. 2012-06-17 17:02:01 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, racde.prm: Positioned to (Thread 1) Sequence 13, RBA 2676224, SCN 0.0, Jun 17, 2012 4:57:01 PM. 2012-06-17 17:02:01 INFO OGG-01515 Oracle GoldenGate Capture for Oracle, racde.prm: Positioning to begin time Jun 17, 2012 4:57:01 PM. 2012-06-17 17:02:01 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, racde.prm: Positioned to (Thread 2) Sequence 17, RBA 2571264, SCN 0.0, Jun 17, 2012 4:57:01 PM. 2012-06-17 17:02:01 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, racde.prm: EXTRACT RACDE started. 2012-06-17 17:02:01 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, racde.prm: Position of first record processed for Thread 1, Sequence 13, RBA 2681872, SCN 0.1079716, Jun 17, 2012 4:57:12 PM. 2012-06-17 17:02:01 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, racde.prm: No recovery is required for target file ./dirdat/DE000000, at RBA 0 (file not opened). 2012-06-17 17:02:01 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, racde.prm: Output file ./dirdat/DE is using format RELEASE 10.4/11.1. 2012-06-17 17:02:02 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, racde.prm: Position of first record processed for Thread 2, Sequence 17, RBA 2630160, SCN 0.1079872, Jun 17, 2012 4:58:57 PM. GGSCI (RACD1.localdomain.com) 16> START racdp Sending START request to MANAGER ... EXTRACT RACDP starting ----Output from tail -f ggserr.log at time of starting Pump. 2012-06-17 17:07:03 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.100.124 (START EXTRACT RACDP ). 2012-06-17 17:07:03 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT RACDP starting. 2012-06-17 17:07:03 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, racdp.prm: EXTRACT RACDP starting. 2012-06-17 17:07:03 WARNING OGG-01015 Oracle GoldenGate Capture for Oracle, racdp.prm: Positioning with begin time: Jun 17, 2012 5:00:31 PM, waiting for data: at extseqno 0, extrba 0. 2012-06-17 17:07:03 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, racdp.prm: EXTRACT RACDP started. 2012-06-17 17:07:08 INFO OGG-01226 Oracle GoldenGate Capture for Oracle, racdp.prm: Socket buffer size set to 27985 (flush size 27985). 2012-06-17 17:07:08 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, racdp.prm: No recovery is required for target file ./dirdat/DP000000, at RBA 0 (file not opened). 2012-06-17 17:07:08 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, racdp.prm: Output file ./dirdat/DP is using format RELEASE 10.4/11.1. GGSCI (RACD1.localdomain.com) 4> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING RACDE 00:00:00 00:00:03 EXTRACT RUNNING RACDP 00:00:00 00:00:05
8) Configure Goldengate for Replicate on Target.
--CONFIGURE REPLICATE RACDR
TEST:/u00/app/gghome :TEST$./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 11g on Oct 4 2011 23:49:46 Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved. GGSCI (TEST.localdomain.com) 1> ADD REPLICAT RACDR, EXTTRAIL ./dirdat/DP,checkpointtable ggsuser.ckpt REPLICAT added. GGSCI (TEST.localdomain.com) 2> edit params RACDR REPLICAT RACDR SETENV (ORACLE_HOME = "/u00/app/oracle/product/11.2.0/db_1") SETENV (ORACLE_SID = "TEST") --Assume DDL of Source. ASSUMETARGETDEFS USERID ggsuser, PASSWORD Summer2011 DISCARDFILE ./dirrpt/RACDR.dsc, append, megabytes 100 --DLL replication. DDL INCLUDE ALL --DML replication from TEST schema to TEST schema. MAP TEST.*, TARGET TEST.*;
9). Start replicate on target.
GGSCI (TEST.localdomain.com) 3> start racdr Sending START request to MANAGER ... REPLICAT RACDR starting ----Output from tail -f ggserr.log at time of starting Replicate 2012-06-17 14:31:17 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start racdr. 2012-06-17 14:31:17 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.100.101 (START REPLICAT RACDR ). 2012-06-17 14:31:17 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: REPLICAT RACDR starting. 2012-06-17 14:31:17 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle, racdr.prm: REPLICAT RACDR starting. 2012-06-17 14:31:17 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, racdr.prm: REPLICAT RACDR started. GGSCI (TEST.localdomain.com) 4> info all Program Status Group Lag Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RACDR 00:00:00 00:00:01 GGSCI (TEST.localdomain.com) 5> exit
10). Confirm Goldengate connection on Source DB & ASM and Target DB.
-----Goldengate Connection on Source DB
RACD2@:/u01/app/oracle/product/11.2.0/db_1/network/admin :DEVDB2$sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 17 17:36:53 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options set lines 170 col machine for a25 COL USERNAME FOR A10 SELECT USERNAME,INST_ID,PROGRAM,machine,process from gv$session WHERE PROGRAM LIKE 'extract%'; USERNAME INST_ID PROGRAM MACHINE PROCESS ---------- ---------- ------------------------------------------------ ------------------------- ------------------------ GGSUSER 1 extract@RACD1.localdomain.com (TNS V1-V3) RACD1.localdomain.com 7650 GGSUSER 1 extract@RACD1.localdomain.com (TNS V1-V3) RACD1.localdomain.com 7650 GGSUSER 1 extract@RACD1.localdomain.com (TNS V1-V3) RACD1.localdomain.com 7889 GGSUSER 1 extract@RACD1.localdomain.com (TNS V1-V3) RACD1.localdomain.com 7650
-----Goldengate Connection on Source ASM
RACD2@:/u01/app/oracle/product/11.2.0/db_1/network/admin :+ASM2 $orasql SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 17 19:03:26 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Real Application Clusters and Automatic Storage Management options set lines 170 col machine for a25 COL USERNAME FOR A10 SELECT USERNAME,INST_ID,PROGRAM,machine,process from gv$session WHERE PROGRAM LIKE 'extract%'; USERNAME INST_ID PROGRAM MACHINE PROCESS ---------- ---------- ------------------------------------------------ ------------------------- ------------------------ SYS 2 extract@RACD1.localdomain.com (TNS V1-V3) RACD1.localdomain.com 7650 SYS 2 extract@RACD1.localdomain.com (TNS V1-V3) RACD1.localdomain.com 7650
----Goldengate connection on Target DB
SELECT USERNAME,INST_ID,PROGRAM,machine,process from gv$session WHERE PROGRAM LIKE 'replicat%'; USERNAME INST_ID PROGRAM MACHINE PROCESS ---------- -------------------- ------------------------------------------------ ------------------------- ------------------------ GGSUSER 1 replicat@TEST.localdomain.com (TNS V1-V3) TEST.localdomain.com 3875
11. Test Replication.
////////////////////////////////////////////////////////////////
DDL+DML replication Test
////////////////////////////////////////////////////////////////
ON NODE2. Create Table & Insert few records.
RACD2@:/u01/app/oracle/product/11.2.0/db_1/network/admin :DEVDB2 $sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Jun 17 17:36:53 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
create table TEST.INFORMATION(
ID VARCHAR2(4 BYTE) NOT NULL,
First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
Start_Date DATE,
End_Date DATE,
Salary Number(8,2),
City VARCHAR2(10 BYTE),
Description VARCHAR2(15 BYTE)
)
/
insert into TEST.INFORMATION(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
/
insert into TEST.INFORMATION(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
/
insert into TEST.INFORMATION(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
/
insert into TEST.INFORMATION(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
/
insert into TEST.INFORMATION(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
/
insert into TEST.INFORMATION(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester')
/
insert into TEST.INFORMATION(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager')
/
insert into TEST.INFORMATION(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
COMMIT;
SELECT * FROM TEST.INFORMATION
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
---Output from Source ggserr.log
2012-06-17 17:42:28 WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, racde.prm: No unique key is defined for table INFORMATION. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
---Confirm on Target side.
SELECT * FROM TEST.INFORMATION ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- --------- --------- -------------------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester ---Output from Target ggserr.log 2012-06-17 14:42:24 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, racdr.prm: Setting current schema for DDL operation to [SYS]. 2012-06-17 14:42:29 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, racdr.prm: Restoring current schema for DDL operation to [GGSUSER]. 2012-06-17 14:42:39 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, racdr.prm: No unique key is defined for table INFORMATION. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
3 comments:
Excellent post... This is I have been looking for a while. However, I have got one questions here, Did you installed GG on both nodes (locally) or on shared drive?
Thanks for sharing....
Hardik
http://handsonoracle.blogspot.in
GG is on shared FS (OCFS2).
I really appreciate information shared above. It’s of great help. If someone want to learn Online (Virtual) instructor lead live training in Oracle Golden Gate.kindly contact us http://www.maxmunus.com/contact
MaxMunus Offer World Class Virtual Instructor led training on Oracle Golden Gate. We have industry expert trainer. We provide Training Material and Software Support. MaxMunus has successfully conducted 100000+ trainings in India, USA, UK, Australlia, Switzerland, Qatar, Saudi Arabia, Bangladesh, Bahrain and UAE etc.
For Free Demo Contact us:
Name : Arunkumar U
Email : arun@maxmunus.com
Skype id: training_maxmunus
Contact No.-+91-9738507310
Company Website –http://www.maxmunus.com
Post a Comment
Note: Only a member of this blog may post a comment.