Fixing the ORA-01017 Error: Credential Store Issues in Oracle GoldenGate

 Hey there, DBAs! Have you ever faced the dreaded ORA-01017 error while working with Oracle GoldenGate?  I recently tackled this issue, and I thought it would be helpful to share my experience. Let's break down what happened and how to fix it, step by step.

The Scenario

While performing a routine refresh of our environments, I encountered an error with the GoldenGate administrator credentials. Even though the credentials were correct, I kept seeing this:

GGSCI (oracledba.localhst.com) 2> start *

Sending START request to MANAGER ...
EXTRACT PUMP01 starting

Sending START request to MANAGER ...
EXTRACT EXTR1 starting

GGSCI (oracledba.localhst.com) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                         
EXTRACT     STOPPED     PUMP01      00:00:00      64:19:39 
EXTRACT     STOPPED     EXTR1       00:00:00      64:19:40

Troubleshooting Steps

First, I viewed the report for the EXTR1 process:

GGSCI (oracledba.localhst.com) 12> view report extr1
...
2024-05-21 13:53:27  ERROR   OGG-00664  OCI Error beginning session (status = 1017-ORA-01017: invalid username/password; logon denied).
2024-05-21 13:53:27  ERROR   OGG-01668  PROCESS ABENDING.

Clearly, the issue was with the credentials. Here's how I fixed it:

Step 1: Check the Credential Store

GGSCI (oracledb.localhst.com) 13> info credentialstore

Reading from ./dircrds/:
Default domain: OracleGoldenGate

  Alias: ggadmin
  Userid: ggadmin

  Alias: gguser
  Userid: gguser

Step 2: Delete and Recreate the Credential Store

Since the credential store seemed to be the issue, I deleted and recreated it:

GGSCI (oracledba.localhst.com) 14> delete credentialstore
Credential store deleted from ./dircrds/.

GGSCI (oracledba.localhst.com) 15> info credentialstore
ERROR: Unable to open credential store in ./dircrds/.

Next, I added the user again:

GGSCI (oracledba.localhst.com) 20> add credentialstore
Credential store created in ./dircrd/.

GGSCI (oracledba.localhst.com) 21> ALTER CREDENTIALSTORE ADD USER ggadmin alias gguser
Password: *****
Credential store in ./dircrd/ altered.

Step 3: Update Parameter Files

Update the ext1 parameter file with the new alias:

GGSCI (oracledba.localhst.com) 30> view params extr1

EXTRACT extr1
USERIDALIAS ogguser

EXTTRAIL /u01/app/oracle/product/18.1.2/ggbase/dirdat/lt
TABLE SCOTT.TABLE1;
TABLE SCOTT.TABLE2;

Step 4: Restart the Processes
Finally, restart the processes to apply the changes:

GGSCI (oracledba.localhst.com) 26> start *

Sending START request to MANAGER ...
EXTRACT PUMP01 starting

Sending START request to MANAGER ...
EXTRACT EXTR1 starting

GGSCI (oracledba.localhst.com) 27> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                         
EXTRACT     RUNNING     PUMP01      00:00:00      62:22:41 
EXTRACT     RUNNING     EXTR1       63:04:39      00:00:01

And just like that, everything was back up and running smoothly. The key takeaway here is to ensure your credential store is properly configured and to refresh the credentials when necessary.

Conclusion
Dealing with credential issues in Oracle GoldenGate can be a bit tricky, but with these steps, you should be able to troubleshoot and resolve the ORA-01017 error efficiently. Remember, always double-check your credential store and keep your parameter files up to date.

Feel free to share your experiences or ask any questions in the comments below. Let’s keep those databases running smoothly!

Happy troubleshooting!