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
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
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.
Last Update: May 4, 2017