Now, when you go to start the extract/replicat, you may get the following error:
ERROR OGG-00551 Database operation failed: Couldn’t connect to [ dns ]. ODBC error: SQLSTATE 37000 native database error 4060. [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database “db_name” requested by the login. The login failed.
The error message is a little bit misleading. It tells you that the process cannot connect to the database which you were able to connect to from the GGSCI command prompt with no issue.
Why is this?
The issue lies in the fact that the manager (MGR) process is running as a service and does not have the correct permissions to access the database from the service.
In searching MOS for this error, I was found Note ID: 1633138.1.
In this note, notice that this issue is known as of Oracle GoldenGate version 12.1.2.x.x. The note also provides you a fix to this issue. In simple terms, since the manager process is running as a service; additional permissions have to be granted to manger.
To grant the SYSADMIN privilege for the manager process follow the below sequence of steps (on windows after all):
1. Manager is installed as service:
Open SQL Server Management studio -> Security ->login>select NT AUTHORITY\SYSTEM ->Right Click -> Properties–>Server Role –>Enable sysadmin role
2. ggsci>stop mgr
3. ggsci>start mgr
4. ggsci>start extract <extract-name>
After granting the sysadmin role, the extract will start.
ERROR OGG-00551 Database operation failed: Couldn’t connect to [ dns ]. ODBC error: SQLSTATE 37000 native database error 4060. [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database “db_name” requested by the login. The login failed.
The error message is a little bit misleading. It tells you that the process cannot connect to the database which you were able to connect to from the GGSCI command prompt with no issue.
Why is this?
The issue lies in the fact that the manager (MGR) process is running as a service and does not have the correct permissions to access the database from the service.
In searching MOS for this error, I was found Note ID: 1633138.1.
In this note, notice that this issue is known as of Oracle GoldenGate version 12.1.2.x.x. The note also provides you a fix to this issue. In simple terms, since the manager process is running as a service; additional permissions have to be granted to manger.
To grant the SYSADMIN privilege for the manager process follow the below sequence of steps (on windows after all):
1. Manager is installed as service:
Open SQL Server Management studio -> Security ->login>select NT AUTHORITY\SYSTEM ->Right Click -> Properties–>Server Role –>Enable sysadmin role
2. ggsci>stop mgr
3. ggsci>start mgr
4. ggsci>start extract <extract-name>
After granting the sysadmin role, the extract will start.