Silent Upgrade Oracle GoldenGate 12.1.2.0.0 to 12.1.2.1.2

Today I worked on silent upgrade of Oracle GoldenGate from 12.1.2.0.0 to 12.1.2.1.2, so thought to blog it which might help some of you. These are the steps I performed

Current GoldenGate Home –> /oracle/app/product/ogg12.1.2

1. Gather the details of GoldenGate Processes before stopping them

GGSCI> INFO EXTRACT EXTL, SHOWCH
INFO EXTRACT DPUMP, SHOWCH
GGSCI> SEND EXTRACT EXTL, SHOWTRANS
GGSCI> STOP EXTRACT DPUMP
GGSCI> STOP EXTRACT EXTL
GGSCI> SEND REPLICAT REPL STATUS
GGSCI> STOP REPLICAT REPL
GGSCI> STOP JAGENT
GGSCI> STOP MANAGER

2. Backup the existing binaries and associated files needed for your environment.

3. Edit the response file and run the runInstaller in silent mode

In the response file

#-------------------------------------------------------------------------------
# Specify the installation option.
# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
#         ORA11g for installing Oracle GoldenGate for Oracle Database 11g
#-------------------------------------------------------------------------------
INSTALL_OPTION=ORA11g

#-------------------------------------------------------------------------------
# Specify a location to install Oracle GoldenGate
#-------------------------------------------------------------------------------
SOFTWARE_LOCATION=/oracle2/app/product/ogg12.1.2

#-------------------------------------------------------------------------------
# Specify true to start the manager after installation.
#-------------------------------------------------------------------------------
START_MANAGER=false

#-------------------------------------------------------------------------------
# Specify a free port within the valid range for the manager process.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
MANAGER_PORT=

#-------------------------------------------------------------------------------
# Specify the location of the Oracle Database.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
DATABASE_LOCATION=

#-------------------------------------------------------------------------------
# Specify the location which holds the install inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=/oracle/oraInventory

#-------------------------------------------------------------------------------
# Unix group to be set for the inventory directory.
# This parameter is not applicable if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=oinstall

Run the runInstaller in Silent mode

[oracle@oracle gg]$ export ORACLE_HOME=/oracle/app/product/ogg12.1.2
[oracle@oracle gg]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
[oracle@oracle gg]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
[oracle@oracle Disk1]$ ./runInstaller -silent -showProgress -waitforcompletion -responseFile /opt/oracle/ogg_upg.rsp
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 181836 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 11999 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2015-04-21_12-23-03AM. Please wait ...You can find the log of this install session at:
 /oracle/oraInventory/logs/installActions2015-04-21_12-23-03AM.log

Prepare in progress.
..................................................   10% Done.

Prepare successful.

Copy files in progress.
..................................................   44% Done.
..................................................   50% Done.
..................................................   60% Done.
..................................................   65% Done.
..................................................   72% Done.
....................
Copy files successful.

Link binaries in progress.
..........
Link binaries successful.
..................................................   82% Done.

Setup files in progress.
..................................................   100% Done.

Setup files successful.
The installation of Oracle GoldenGate Core was successful.
Please check '/oracle/oraInventory/logs/silentInstall2015-04-21_12-23-03AM.log' for more details.
Successfully Setup Software.
[oracle@oracle1 Disk1]$

4. Confirm GoldenGate has been upgraded

[oracle@oracle Disk1]$ cd $ORACLE_HOME
[oracle@oracle ogg12.1.2]$
[oracle@oracle ogg12.1.2]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 09:14:25
Operating system character set identified as UTF-8.

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

5. Apply Patch 20265694: Oracle GoldenGate V12.1.2.1.2 for Oracle 11G

[oracle@oracle1 20265694]$ opatch apply
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/product/ogg12.1.2
Central Inventory : /oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 11.2.0.1.7
OUI version       : 11.2.0.3.0
Log file location : /oracle/app/product/ogg12.1.2/cfgtoollogs/opatch/opatch2015-04-21_00-24-15AM.log

Applying interim patch '20265694' to OH '/oracle/app/product/ogg12.1.2'
Verifying environment and performing prerequisite checks...

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/app/product/ogg12.1.2')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.oggcore.ora11g, 12.1.2.1.0...
Patch 20265694 successfully applied
Log file location: /oracle/app/product/ogg12.1.2/cfgtoollogs/opatch/opatch2015-04-21_00-24-15AM.log

OPatch succeeded.
[oracle@oracle1 20265694]$
[oracle@oracle1 20265694]$ opatch lsinv
Invoking OPatch 11.2.0.1.7

Oracle Interim Patch Installer version 11.2.0.1.7
Copyright (c) 2011, Oracle Corporation.  All rights reserved.
........................
........................
Patch  20265694     : applied on Tue Apr 21 00:24:27 EDT 2015
Unique Patch ID:  18593256
   Created on 3 Feb 2015, 15:03:21 hrs PST8PDT
   Bugs fixed:
     19818362, 19602692, 19241234, 17423191, 19781984, 19535319, 19724915
     19721652, 19516537, 19441114, 19132627, 19889991, 19681035

6. Confirm the GoldenGate version is now 12.1.2.1.2

[oracle@oracle ogg12.1.2]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.1.2.1.2 20133048 OGGCORE_12.1.2.1.0OGGBP_PLATFORMS_141228.0533_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 28 2014 13:19:44
Operating system character set identified as UTF-8.

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

7. Start the GoldenGate Processes


GGSCI (oracle.test.com) 1> dblogin userid ggate, Password "xxxxxxxx"
Successfully logged into database.

GGSCI> START MANAGER
GGSCI> START JAGENT
GGSCI> START EXTRACT EXTL
GGSCI> START EXTRACT DPUMP
GGSCI> START REPLICAT REPL
Advertisements

UPGRADE CHECKPOINTTABLE – Goldengate

We have a goldengate setup wherein 3 different GoldenGate clients connects and replicat to one target database.Below are the versions being currently used 11.1.1.1, 11.2.1.0.0 and 11.2.1.0.3.

The version 11.2.1.0.3 was recently added, and below are the steps performed

GGSCI (myhost) 2> obey ./dirprm/add_rep.oby

GGSCI (myhost) 3>

GGSCI (myhost) 3> DBLOGIN USERID ggate@test PASSWORD 'xxxxxxxx'

ERROR: Unable to connect to database using user ggate@test. Please check privileges.
ORA-12170: TNS:Connect timeout occurred.

GGSCI (myhost) 4>

GGSCI (myhost) 4> ADD REPLICAT rep, extTrail /app/trail/rep/rp, checkpointTable ggate.OGG_CHECKPOINT

REPLICAT added.

dblogin failed but then the replicat got added. Now what happens if we try to delete it

GGSCI (myhost) 5> delete replicat rep
ERROR: Could not delete DB checkpoint for REPLICAT rep (Database login required to delete database checkpoint).

GGSCI (host) 6> info all

Program     Status      Group   Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     ext     00:00:00      00:00:05
REPLICAT    STOPPED     rep     00:00:00      00:00:38

So, now what can be done to delete it. Its simple,

In GG_HOME/dirchk

TEST:/u01/app/oracle/product/ggate/dirchk->ls -lrt
total 20
-rw-rw-r-- 1 ggate dba 4096 Nov 17 01:51 EXT.cpb
-rw-rw-r-- 1 ggate dba 2048 Nov 17 03:39 REP.cpr
-rw-rw-r-- 1 ggate dba   52 Nov 17 03:59 EXT.cps
-rw-rw-r-- 1 ggate dba 8192 Nov 17 03:59 EXT.cpe

Remove "REP.cpr" file.

TEST:/u01/app/oracle/product/ggate/dirchk->rm REP.cpr


TEST:/u01/app/oracle/product/ggate->ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

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



GGSCI (host) 1> info all

Program     Status      Group  Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT     00:00:01      00:00:07

Once the port issue was resolved, added the replicat “rep” again successfully.

GGSCI (myhost) 2>  DBLOGIN USERID ggate@test PASSWORD "xxxxxxxx"
Successfully logged into database.

GGSCI (myhost) 3> ADD REPLICAT REP, extTrail /app/trail/rep/rp, checkpointTable ggate.OGG_CHECKPOINT
REPLICAT added.


GGSCI (myhost) 4> info all

Program     Status      Group   Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT     00:00:01      00:00:02
REPLICAT    STOPPED     REP     00:00:00      00:00:20


GGSCI (myhost) 2> start REP

Sending START request to MANAGER ...
REPLICAT REP starting


GGSCI (myhost) 3> info all

Program     Status      Group  Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT     00:00:00      00:00:06
REPLICAT    STOPPED     REP     00:00:00      00:04:15

Why is the status ‘STOPPED’? ggserr.log shows

2012-11-17 04:04:46  ERROR   OGG-00446  Oracle GoldenGate Delivery for Oracle, rep.prm:  Supplemental Checkpoint table does not exist.  Create a supplemental checkpoint table with the UPGRADE CHECKPOINTTABLE command in GGSCI if you have upgraded from release 11.2.1.0.0 or earlier.
2012-11-17 04:04:46  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rep.prm:  PROCESS ABENDING.

The target db is on 11.2.0.2.0 version. Checking the checkpoint table

SYS@test > desc ggate.ogg_checkpoint
 Name                                                                       Null?    Type
 -------------------------------------------------------------------------- -------- --------------------------------------------------
 GROUP_NAME                                                                 NOT NULL VARCHAR2(8)
 GROUP_KEY                                                                  NOT NULL NUMBER(19)
 SEQNO                                                                               NUMBER(10)
 RBA                                                                        NOT NULL NUMBER(19)
 AUDIT_TS                                                                            VARCHAR2(29)
 CREATE_TS                                                                  NOT NULL DATE
 LAST_UPDATE_TS                                                             NOT NULL DATE
 CURRENT_DIR                                                                NOT NULL VARCHAR2(255)

04:00:55 SYS@test1 > /

GROUP_NA  GROUP_KEY      SEQNO        RBA AUDIT_TS                      CREATE_TS                   LAST_UPDATE_TS
-------- ---------- ---------- ---------- ----------------------------- --------------------------- ---------------------------
CURRENT_DIR
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
RABC     2253238980          0  230112172 2012-11-17 00:18:32.000000    16-NOV-12-13:56:33          17-NOV-12-04:08:45
/u01/app/oracle/product/ggate

RDEF     451369050        174   13320072 2012-11-17 04:08:48.000000    08-OCT-12-22:02:16          17-NOV-12-04:08:51
/app/ggate

RGBAA    3979228817         85   30933007 2012-11-17 03:03:33.000000    26-OCT-12-10:06:50          17-NOV-12-04:04:09
/app/ggate

RDONE    3150503361        365  276978037 2012-11-17 04:05:33.000000    14-OCT-12-10:34:54          17-NOV-12-04:05:40
/app/ggate

Though we have added and started REP, we don’t see any row for it in checkpoint table. Lets try to run upgrade checkpointtable command

GGSCI (myhost) 2> DBLOGIN USERID ggate@test PASSWORD "xxxxxxx"
Successfully logged into database.

GGSCI (myhost) 3> UPGRADE CHECKPOINTTABLE ggate.OGG_CHECKPOINT

Successfully upgraded checkpoint table ggate.OGG_CHECKPOINT.

GGSCI (myhost) 4> start rep

Sending START request to MANAGER ...
REPLICAT REP starting


GGSCI (myhost) 5> info all

Program     Status      Group  Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     EXT     00:00:02      00:00:26
REPLICAT    RUNNING     REP     02:35:52      00:00:16

From db

SYS@test1 > desc ggate.ogg_checkpoint
 Name                                                                       Null?    Type
 -------------------------------------------------------------------------- -------- --------------------------------------------------
 GROUP_NAME                                                                 NOT NULL VARCHAR2(8)
 GROUP_KEY                                                                  NOT NULL NUMBER(19)
 SEQNO                                                                               NUMBER(10)
 RBA                                                                        NOT NULL NUMBER(19)
 AUDIT_TS                                                                            VARCHAR2(29)
 CREATE_TS                                                                  NOT NULL DATE
 LAST_UPDATE_TS                                                             NOT NULL DATE
 CURRENT_DIR                                                                NOT NULL VARCHAR2(255)
 LOG_CSN                                                                             VARCHAR2(129)
 LOG_XID                                                                             VARCHAR2(129)
 LOG_CMPLT_CSN                                                                       VARCHAR2(129)
 LOG_CMPLT_XIDS                                                                      VARCHAR2(2000)
 VERSION                                                                             NUMBER(3)

04:12:24 SYS@test1 > select * from ggate.ogg_checkpoint;

GROUP_NA  GROUP_KEY      SEQNO        RBA AUDIT_TS                      CREATE_TS                   LAST_UPDATE_TS
-------- ---------- ---------- ---------- ----------------------------- --------------------------- ---------------------------
CURRENT_DIR
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOG_CSN
---------------------------------------------------------------------------------------------------------------------------------
LOG_XID
---------------------------------------------------------------------------------------------------------------------------------
LOG_CMPLT_CSN
---------------------------------------------------------------------------------------------------------------------------------
LOG_CMPLT_XIDS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   VERSION
----------
RABC     2253238980          3  119752892 2012-11-20 02:12:30.000000    16-NOV-12-13:56:33          20-NOV-12-04:12:35
/u01/app/oracle/product/ggate






RDEF   451369050        183  229511885 2012-11-20 04:12:22.000000    08-OCT-12-22:02:16          20-NOV-12-04:12:24
/app/ggate






RGBAA   3979228817         97   80060759 2012-11-20 10:07:02.000000    26-OCT-12-10:06:50          20-NOV-12-04:10:09
/app/ggate






REP   2249640216         12  211547890 2012-11-20 04:12:32.000000    17-NOV-12-04:00:48          20-NOV-12-04:12:34
/u01/app/oracle/product/ggate
12957879573832
780.26.260408
12957879573832
780.26.260408
         1

RDONE    3150503361        404  290793846 2012-11-20 04:12:19.000000    14-OCT-12-10:34:54          20-NOV-12-04:12:23
/app/ggate


ORA-26723: user “XXXXX” requires the role “DV_GOLDENGATE_REDO_ACCESS”

While starting the extract on UAT DB env which had been recently moved to exadata we got the below error.As we had redo and archive logfiles on ASM, we used “TRANLOGOPTIONS DBLOGREADER” in extract parameter file.

2012-10-24 22:35:48  ERROR   OGG-00446  Oracle GoldenGate Capture for Oracle, emos_cc.prm:  Opening ASM file +RECO_UMO1/archivelog/2012_10_24/thread_1_seq_224.955.797517005 in DBLOGREADER mode: (26723) ORA-26723: user "GGATE" requires the role "DV_GOLDENGATE_REDO_ACCESS"

The first thing which we did is checked whether the role exists or not.


22:41:53 SYS@xxxxx1 > select role from dba_roles where role like 'DV_%';

no rows selected

Ahh, No roles starting with DV_ exists in the db. Then why is GOldenGate asking for this role.Doing some search on tahiti.oracle.com pointed to a document which mentioned

Grant the DV_GOLDENGATE_REDO_ACCESS role to any user who is responsible for using the Oracle GoldenGate TRANLOGOPTIONS DBLOGREADER method to access redo logs in an Oracle Database Vault environment. This enables the management of Oracle GoldenGate processes to be tightly controlled by Database Vault, but does not change or restrict the way an administrator would normally configure Oracle GoldenGate.

So, now we have a clue. Its something to do with Database Vault.The UAT env had recently been moved to exadata box, prior to which it on a normal server where the extract was running fine.

22:42:05 SYS@xxxxx1 > SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle Database Vault                                            TRUE

Above, shows Database Vault option enabled, but as the database was restored from the backup of the db on normal server, we didn’t had any DVSYS and DVF schemas.

Oracle Database Vault has the following schemas:

DVSYS Schema: Owns the Oracle Database Vault schema and related objects

DVF Schema: Owns the Oracle Database Vault functions that are created to retrieve factor identities

As, vault wasn’t required, we used CHOPT utility available from 11.2 for enabling/disabling database features.


After shutting down the db, ran chopt on all the nodes --

abcde0025: (abncu1) /u01/abncu/admin> chopt disable dv

Writing to /u01/app/oracle/product/11.2.0.3/dbhome_1/install/disable_dv.log...
/usr/bin/make -f /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/lib/ins_rdbms.mk dv_off ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1
/usr/bin/make -f /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/dbhome_1

abcde0025: (abncu1) /u01/abncu/admin>

Started the db and checked for the value which was disabled (FALSE) and GoldenGate extract started working.

SYS@xxxxx1 > SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

PARAMETER VALUE
----------------------------------------------------------------
----------------------------------------------------------------
Oracle Database Vault   FALSE

References
http://docs.oracle.com/cd/E11882_01/server.112/e23090/db_objects.htm#DVADM71151

http://docs.oracle.com/cd/E11882_01/install.112/e17214/postinst.htm#CHDBDCGE

Extract keeps ABENDING – No valid archive log dest for Thread N – OGG

Recently we refreshed one of the 2-node UAT database from 3-node production.The 2-node UAT acts as the source for the GoldenGate.After the refresh of source and the initial load on the target, we started the extract and replicat process.As, the source was 2-node RAC, extract was added with 2 Threads information

ADD EXTRACT ext, TRANLOG, THREADS 2, BEGIN NOW

Everything ran well for almost 2 weeks when all of a sudden extract kept abending and GG error log started showing

2011-11-14 03:44:47  INFO    OGG-01629  Oracle GoldenGate Capture for Oracle, ext.prm:  BOUNDED RECOVERY: PERSISTED OBJECTS RECOVERED: <>.
2011-11-14 03:44:51  WARNING OGG-01423  Oracle GoldenGate Capture for Oracle, ext.prm:  No valid default archive log destination directory found for thread 3.

Though the source was 2-node RAC the alert showed – No valid default archive log destination directory found for thread 3.

On looking at the source db more carefully showed thread 3 in v$thread.Someone had mistakenly added redo log groups with thread 3 and they were in UNUSED state.Once we “dropped” the LOG GROUPs for Thread 3 and restarted the Extract, everything is working fine.

Now,suppose you have source with 3 threads and you mistakenly added extract with 2 threads, you can get the same error in future, then –

1. Take the showch ouput of the existing extract
2. Consider the earliest Recovery checkpoint timestamp to begin the extract
3. ggsci> delete ext
4. ggsci> add extract ext TRANLOG, THREADS 3, BEGIN {Earliest Recovery Checkpiont timestamp taken in step2}
5. ggsci> add exttrail {path}, extract ext, megabytes XXX
6. ggsci> start ext

Incase, if you donot wish to capture the transactions from node N, edit the extract parameter file –

THREADOPTIONS PROCESSTHREADS EXCEPT N –> N is the thread number.