I have been asked this question quite often in this forum regarding how do we connect to GoldenGate if there are a number of databases running on the source server or on the target server. Or there could be a case where we have installed GoldenGate for Oracle 10g and we have both 10g as well as 11g Oracle Homes on the same machine and we want to connect to the Oracle 10g environment in particular.
Very valid question and a very simple answer for that.
1) Either set the right environment using .oraenv
or
2) Specify the right TNS alias in the Manager, Extract or Replicat parameter file where we have used the USERID keyword
For example, just to illustrate the same, I wrongly set the ORACLE_SID variable to a nonexistent value. The manager process will not start since it will try to connect with the username and password used in the manager parameter file to the LOCAL database for which the environment has been now set up and since the ORACLE_SID is wrong, there is no database running on the server with that SID.
$ export ORACLE_SID=xyz
GGSCI (vixen) 2> start manager
Manager started.
GGSCI (vixen) 3> status manager
Manager is DOWN!
This is what we will see in the GoldenGate logs:
2010-08-19 11:04:37 GGS ERROR 182 OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory).
2010-08-19 11:04:37 GGS ERROR 190 PROCESS ABENDING.
To use a TNS alias, make sure that we are able to do a tnsping as well as connect via SQL*PLUS using that alias before we launch GGSCI.
In the relevant parameter file (in this example an extract parameter file) we just add the TNS alias to the USERID as shown below:
USERID ggs_owner@levengr2, PASSWORD ggs_owner
If the TNS connection is not defined properly, we can expect to see an error like this in the log file:
2010-08-19 11:09:46 GGS ERROR 182 OCI Error during OCIServerAttach (status = 12154-ORA-12154: TNS:could not resolve the connect identifier specified)
.
2010-08-19 11:09:46 GGS ERROR 190 PROCESS ABENDING.
We can also use the same TNS alias method when we use DBLOGIN keyword to establish a database connection via GoldenGate
GGSCI (devastator) 2> dblogin userid ggs_owner@levengr2, password ggs_owner
Successfully logged into database.
I am trying the Oracle GG tutorial posted on this site. It is a very lucid explanation of how to get GG going. Thank you very much for this wonderful tutorial!. The set up has been pretty smooth. However, when I run the extract process for the initial load, the process abends. The ggerr.log reports the following error:
2011-03-07 17:41:56 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, LOAD1.prm: EXTRACT LOAD1 started.
2011-03-07 17:42:02 WARNING OGG-01194 Oracle GoldenGate Capture for Oracle, LOAD1.prm: EXTRACT task LOAD2 abended : OCI Error during OCIServerAttach (status = 12557-Error while trying to retrieve text for error ORA-12557).
2011-03-07 17:42:02 ERROR OGG-01203 Oracle GoldenGate Capture for Oracle, LOAD1.prm: EXTRACT abending.
2011-03-07 17:42:02 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, LOAD1.prm: PROCESS ABENDING.
GGSCI reports the following:
GGSCI (MY-XP) 1> ADD EXTRACT load1, SOURCEISTABLE
EXTRACT added.
GGSCI (MY-XP) 2> edit PARAMS load1
GGSCI (MY-XP) 3> START EXTRACT load1
Sending START request to MANAGER (‘EAST’) …
EXTRACT LOAD1 starting
GGSCI (MY-XP) 4> info extract load1
EXTRACT LOAD1 Initialized 2011-03-07 17:14 Status STOPPED
Checkpoint Lag Not Available
Log Read Checkpoint Not Available
First Record Record 0
Task SOURCEISTABLE
I have the source as:
Windows XP as VMWare instance in Mac
Oracle 10g Express Edition
target as:
Windows XP
Oracle 10g Express Edition
On both source and target, I can login as ggs_owner with ggsci tool. That would, I assume, indicate it is not an issue with ORACLE_HOME or ORACLE_SID. Any help appreciated. Thanks!
I just wanted to say thank you so much for your GoldenGate tutorials. I’m working on setting up a GoldenGate environment for the first time, and while the official documentation has lots of detailed information, I have found it to be poorly organized and inconsistent. I’ve been relying heavily on your tutorials for clear step-by-step instructions. And just now, I needed an answer to this very question of multiple databases replicating on the same system: your site was the first place I looked. Thank you!