ADD CREDENTIALSTORE goldengate 12c

The ADD CREDENTIALSTORE is a new command in Oracle GoldenGate 12c and the default location of the credential store is “$GG_HOME/dircrd” directory of the GoldenGate software home. Before 12c to hide the passwords from param files we need to encrypt the passwords and place them in all param files

Check here for: How to encrypt passwords before 12c

Let’s add a user to credential store

GGSCI (oradev01) 3> DBLOGIN USERID GGADMIN, PASSWORD xxxxx

Successfully logged into database.

GGSCI (oradev01 as GGADMIN@testdb) 4> ADD CREDENTIALSTORE

Credential store created in ./dircrd/.

GGSCI (oradev01 as GGADMIN@testdb) 5> ALTER CREDENTIALSTORE ADD USER GGADMIN ALIAS ggadmin

Password:

Credential store in ./dircrd/ altered.

Now check the login with newly created alias name……..

GGSCI (oradev01 as GGADMIN@testdb) 6> dblogin useridalias ggadmin

Successfully logged into database.

oradev01:testdb:/opt/oracle/GG/home $ cd dircrd

oradev01:testdb:/opt/oracle/GG/home/dircrd $ ls -lrth

total 4.0K

-rw-r----- 1 oracle dba 517 Feb 22 22:55 cwallet.sso

From above we see that Auto Login wallet has been created

To verify:

GGSCI (oradev01) 1>  INFO CREDENTIALSTORE

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: ggadmin

  Userid: GGADMIN

GGSCI (oradev01 as GGADMIN@testdb) 1> edit params mgr
PORT 7809
USERIDALIAS ggadmin
PURGEOLDEXTRACTS /opt/oracle/GG/RT, USECHECKPOINTS

NOTE: "ggadmin" in param file is case sensitive

UPDATE Password:

In any company we will change the passwords on regular basis due to security and when you change the GGADMIN password then your Extract/pump/replicat will go ABENDED so when ever you change the password for ggadmin then you also need to update the credentialstore password too

if password modified then perform below steps to update:

GGSCI (oradev01) 1> dblogin useridalias ggadmin

ERROR: Unable to connect to database using user GGADMIN. Please check privileges.

Unable to initialize database connection because of error ORA-01017: invalid username/password; logon denied.

GGSCI (oradev01) 2> alter credentialstore replace user GGADMIN alias ggadmin

Password:

Credential store in ./dircrd/ altered.

GGSCI (oradev01) 3> info credentialstore

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: ggadmin

  Userid: GGADMIN

GGSCI (oradev01) 4> dblogin useridalias ggadmin

Successfully logged into database.

OGG-00369 Oracle GoldenGate Capture for Oracle: Error in token clause for TK_HOST.

GGSCI (sourceserver) 3>  view params

SETENV (ORACLE_SID=mydev)

userid ggadmin, password ****

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 256)

TRANLOGOPTIONS EXCLUDEUSER GGADMIN

EXTTRAIL /migrate/source/GG/home/dirdat/lt

DISCARDFILE  /migrate/source/GG/home/dirrpt/TEST.dsc, PURGE

--DDL INCLUDE MAPPED OBJNAME *.*;

LOGALLSUPCOLS

UPDATERECORDFORMAT COMPACT

TABLE  MYDEV.TEST,

TOKENS ( TK_HOST = @GETENV ("GGENVIRONMENT" , "HOSTNAME"),

TK_OSUSER = @GETENV ("GGENVIRONMENT" , "OSUSERNAME"),

TK_DBNAME = @GETENV("DBENVIRONMENT" , "DBNAME" ),

TK_GROUP = @GETENV ("GGENVIRONMENT", "GROUPNAME"),

TK_COMMIT_TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),

TK_POS = @GETENV ("GGHEADER", "LOGPOSITION"),

TK_RBA = @GETENV ("GGHEADER", "LOGRBA"),

TK_TABLE = @GETENV ("GGHEADER", "TABLENAME"),

TK_OPTYPE = @GETENV ("GGHEADER", "OPTYPE"),

TK_BA = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR"));

GGSCI (sourceserver) 23> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     ABENDED     EDEV      00:42:32      00:01:14

EXTRACT     RUNNING     PHRDEV      00:00:00      00:00:02

Error:

2017-02-09 16:48:53  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): edit params EDEV.

2017-02-09 16:51:14  INFO    OGG-06507  Oracle GoldenGate Capture for Oracle, edev.prm:  MAP (TABLE) resolved (entry MYDEV.TEST): TABLE  "MYDEV"."TEST", TOKENS ( TK_HOST = @GETENV("GGENVIRONMENT" , "HOSTNAME"), TK_OSUSER = @GETENV ("GGENVIRONMENT" , "OSUSERNAME"), TK_DBNAME = @GETENV("DBENVIRONMENT" , "DBNAME" ), TK_GROUP = @GETENV ("GGENVIRONMENT", "GROUPNAME"), TK_COMMIT_TS =@GETENV ("GGHEADER", "COMMITTIMESTAMP"), TK_POS = @GETENV ("GGHEADER", "LOGPOSITION"), TK_RBA = @GETENV ("GGHEADER", "LOGRBA"), TK_TABLE = @GETENV ("GGHEADER", "TABLENAME"), TK_OPTYPE = @GETENV ("GGHEADER", "OPTYPE"), TK_BA = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR")).

2017-02-09 16:51:14  WARNING OGG-06439  Oracle GoldenGate Capture for Oracle, edev.prm:  No unique key is defined for table TEST. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2017-02-09 16:51:14  INFO    OGG-06509  Oracle GoldenGate Capture for Oracle, edev.prm:  Using the following key columns for source table MYDEV.TEST: ID, NAME.

2017-02-09 16:51:14  INFO    OGG-01298  Oracle GoldenGate Capture for Oracle, edev.prm:  Column function diagnostic message: could not find column "GGENVIRONMENT".

2017-02-09 16:51:14  ERROR   OGG-00369  Oracle GoldenGate Capture for Oracle, edev.prm:  Error in token clause for TK_HOST.

2017-02-09 16:51:14  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, edev.prm:  PROCESS ABENDING.

Solution:

The code in the 12c version is now changed the text enclosed within double quotes is treated as column name and the text enclosed within single quotes is treated as literal text, which used to be double quoted text pre 12c versions.

Now, change Double quotes to Single and start extract

GGSCI (sourceserver) 3> view params

SETENV (ORACLE_SID=mydev)

userid ggadmin, password ****

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 256)

TRANLOGOPTIONS EXCLUDEUSER GGADMIN

EXTTRAIL /migrate/source/GG/home/dirdat/lt

DISCARDFILE  /migrate/source/GG/home/dirrpt/TEST.dsc, PURGE

--DDL INCLUDE MAPPED OBJNAME *.*;

LOGALLSUPCOLS

UPDATERECORDFORMAT COMPACT

TABLE  MYDEV.TEST,

TOKENS ( TK_HOST = @GETENV ('GGENVIRONMENT' , 'HOSTNAME'),

TK_OSUSER = @GETENV ('GGENVIRONMENT' , 'OSUSERNAME'),

TK_DBNAME = @GETENV('DBENVIRONMENT' , 'DBNAME' ),

TK_GROUP = @GETENV ('GGENVIRONMENT', 'GROUPNAME'),

TK_COMMIT_TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),

TK_POS = @GETENV ('GGHEADER', 'LOGPOSITION'),

TK_RBA = @GETENV ('GGHEADER', 'LOGRBA'),

TK_TABLE = @GETENV ('GGHEADER', 'TABLENAME'),

TK_OPTYPE = @GETENV ('GGHEADER', 'OPTYPE'),

TK_BA = @GETENV ('GGHEADER', 'BEFOREAFTERINDICATOR'));

Reference:

token with getenv bugs in 11.2.1 and 12.1 (Doc ID 1948440.1)

OGG-01031 There is a problem in network communication. Reply received is Output file is not in any allowed output directories

GG version:  12.2

Oracle DB:  12.1.0.2.0

2017-02-09 10:05:05  ERROR   OGG-01031  Oracle GoldenGate Capture for Oracle, edev.prm:  There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. (Reply received is Output file /migrate/target/GG/RT000000 is not in any allowed output directories.).

2017-02-09 10:05:05  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, edev.prm:  PROCESS ABENDING.

sourceserver:mydev:/migrate/source/GG/home $ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48

Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

GGSCI (sourceserver) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

EXTRACT     RUNNING     EDEV      00:00:02      00:00:07

EXTRACT     RUNNING     PDEV      00:00:00      00:00:05

GGSCI (sourceserver) 3> view params PDEV

EXTRACT PDEV

SETENV (ORACLE_SID='mydev')

USERID ggadmin, password ****

RMTHOST targetserver, MGRPORT 7809

RMTTRAIL  /migrate/target/GG/RT

PASSTHRU

TABLE   MYDEV.TEST ;

Solution:

In order to solve this problem, add the parameter ALLOWOUTPUTDIR in the target system GLOBALS file.

The ALLOWOUTPUTDIR parameter was introduced in Oracle GoldenGate 12.2

Go to TARGET side and add “ALLOWOUTPUTDIR /migrate/target/GG” to GLOBALS file because we are not using “dirdat” (12.2 new feature)

GGSCI (targetserver) 1> edit params ./GLOBALS

GGSCHEMA ggadmin

ALLOWOUTPUTDIR /migrate/target/GG

reference:

OGG-01031 There is a problem in network communication. Reply received is Output file is not in any allowed output directories. (Doc ID 2095284.1)

Failed to enable DBOPTIONS SUPPRESSTRIGGERS.

GG version:  12.2

Oracle DB:  12.1.0.2.0

GGSCI (targetserver) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

REPLICAT    STOPPED     RDEV      00:00:00      00:32:14

GGSCI (targetserver) 4> start RDEV

Sending START request to MANAGER ...

REPLICAT RDEV starting

Error:

2017-02-09 13:29:36  ERROR   OGG-06472  Oracle GoldenGate Delivery for Oracle, RDEV.prm:  Failed to enable DBOPTIONS SUPPRESSTRIGGERS.

2017-02-09 13:29:36  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RDEV.prm:  PROCESS ABENDING.

SOLUTION:

SQL> sho parameter spfile;

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- ------------------------------

spfile                               string                      /opt/oracle/product/12.1.0.2.64/dbs/spfileTDEV.ora

                                                                     

SQL>  sho parameter ENABLE_GOLDENGATE_REPLICATION

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- ------------------------------

enable_goldengate_replication        boolean                          FALSE

SQL> alter system set ENABLE_GOLDENGATE_REPLICATION = TRUE scope = both;

System altered.

SQL> sho parameter ENABLE_GOLDENGATE_REPLICATION

NAME                                 TYPE                             VALUE

------------------------------------ -------------------------------- ------------------------------

enable_goldengate_replication        boolean                          TRUE

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

targetserver:TDEV:/opt/oracle/product/12.1.0.2.64/dbs $ cd /opt/oracle/product/goldengate

targetserver:TDEV:/opt/oracle/product/goldengate $ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48

Operating system character set identified as UTF-8.

Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.

GGSCI (targetserver) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

REPLICAT    STOPPED     RDEV      00:00:00      00:40:07

GGSCI (targetserver) 2> start RDEV

Sending START request to MANAGER ...

REPLICAT RDEV starting

GGSCI (targetserver as ggadmin@TDEV) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

REPLICAT    RUNNING     RDEV      00:00:00      00:41:34

Refer:

GoldenGate Replicat Abends: ERROR OGG-6472 Failed to Enable DBOPTIONS SUPPRESSTRIGGERS (Doc ID 1614302.1)