How to upgrade Goldengate 11.1 to 12.1.2.1.10

We use GoldenGate for replicating data between 2 RAC databases bidirectionally. Replications are schema based. Our current version is  11.1.1.1.

We want to upgrade GoldenGate to 12C to use integrated capture. Integrated capture supports more data types and more importantly supports compression.

Our system is

2 X  RAC database 11.2.0.3.2 with 2 nodes running on Linux Redhat 5.5 

This upgrade consist of 4 main steps:

1. Apply patches on Note 1411356.1

Applying patches 11.2.0.3.15 (Patch no: 20760997) and OGG RDBMS on top of 11.2.0.3.15  (Patch:21373865)



2. Linux upgrade 5.5 to 5.6. (We have to upgrade it because of minimum requirements)

After linux upgrade, we have to upgrade oracleasmlib.


for 5.5  -> oracleasm-2.6.18-194.el5-2.0.5-1.el5 

for 5.6   -> oracleasm-2.6.18-238.el5-2.0.5-1.el5


3. GoldenGate upgrade steps:

• Stop all applications that cause transaction on database.

• Note archivelog and SCN number:

GGSCI (dev1.com) 5> INFO EXTRACT XSSDOZG,showch

*****

 Recovery Checkpoint (position of oldest unprocessed transaction in the data source):

    Thread #: 1

    Sequence #: 11181

    RBA: 220041744

    Timestamp: 2015-10-13 09:39:23.000000

    SCN: 43.3481210601 (188164804329)

    Redo File: +DGLOG/dev/onlinelog/group_5.261.836222981

• Note database SCN  number on source

SQL> col current_scn format 9999999999999999999
SQL> r
  1* select current_Scn from v$database
         CURRENT_SCN
--------------------
        188313744261

Run below command until no redo to capture on source:

GGSCI (dev1.com) 1> send extract XSSDOZG logend Sending LOGEND request to EXTRACT XSSDOZG ... NO. GGSCI (dev1.com) 2> send extract XSSDOZG logend Sending LOGEND request to EXTRACT XSSDOZG ... YES. Stop extract and pump on source: GGSCI (dev1.com) 1> send extract XSSDOZG logend Sending LOGEND request to EXTRACT XSSDOZG ... NO. GGSCI (dev1.com) 2> send extract XSSDOZG logend Sending LOGEND request to EXTRACT XSSDOZG ... YES. Uninstall marker_remove, ddl_disable, ddl_remove on source: [oradev@dev1 ocfsmnt]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 13 10:21:38 2015 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @ddl_disable Trigger altered. SQL> ddl_remove SP2-0042: unknown command "ddl_remove" - rest of line ignored. SQL> @ddl_remove DDL replication removal script. WARNING: this script removes all DDL replication objects and data. You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. Enter GoldenGate schema name:gg Working, please wait ... Spooling to file ddl_remove_spool.txt Script complete. SQL> @marker_remove Marker removal script. WARNING: this script removes all marker objects and data. You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. Enter GoldenGate schema name:gg PL/SQL procedure successfully completed. Sequence dropped. Table dropped. Script complete. Run below comand on target until see output "At EOF": GGSCI (dev1.com) 5> send replicat ROZG status Sending STATUS request to REPLICAT ROZG ...   Current status: At EOF   Sequence #: 7   RBA: 1384   0 records in current transaction Stop Replicats on target GGSCI (dev1.com) 6> stop replicat * Sending STOP request to REPLICAT ROZG ... Request processed. Stop all managers: GGSCI (dev1.com) 5> stop mgr Manager process is required by other GGS processes. Are you sure you want to stop it (y/n)? y Sending STOP request to MANAGER ... Request processed. Manager stopped. Check if there is a  process of goldengate: We are using OCFS as shared file system so: $ ps -ef|grep ocfs  Give permission to gg directory: [oradev@dev1 ocfsmnt]$ chmod -R u+w gg Run ./runinstaller with GUI for OGG 12.1.2 Don't forget to uncheck "start manager" OGG12.1.2.1.10 PSU should be applied at this point. On target replicats: [oradev@dev1 ocfsmnt]$ 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. GGSCI (dev1.com) 1> dblogin userid gg Password: Successfully logged into database. GGSCI (dev1 as gg@dev1) 2> upgrade checkpointtable No checkpoint table specified. Using GLOBALS specification (GG.CHKPTAB)... Successfully upgraded checkpoint table GG.CHKPTAB. Run below commands on sounce databases: [oradev@dev1 ocfsmnt_script_calissin_diye]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 14 11:11:59 2015 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @ulg Oracle GoldenGate supplemental log groups upgrade script. Please do not execute any DDL while this script is running. Press ENTER to continue. PL/SQL procedure successfully completed. Run command ETROLLOVER: Here is the tricky part. You should write seqno of extract to pump and you should write seqno of pump to replicat ON SOURCE: GGSCI (dev1.com) 48> alter extract XOZGSSD etrollover 2015-10-13 16:16:31  INFO    OGG-01520  Rollover performed.  For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file;  it will not happen automatically. EXTRACT altered. GGSCI (dev1.com) 50> alter extract POZGSSD etrollover 2015-10-13 16:17:28  INFO    OGG-01520  Rollover performed.  For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file;  it will not happen automatically. EXTRACT altered. GGSCI (dev1.com) 52> info XOZGSSD detail EXTRACT    XOZGSSD   Last Started 2015-10-13 16:17   Status RUNNING Checkpoint Lag       00:00:00 (updated 00:00:00 ago) Process ID           18447 Log Read Checkpoint  Oracle Redo Logs                      2015-10-13 16:18:53  Thread 1, Seqno 11184, RBA 711680                      SCN 43.3527051310 (188210645038) Log Read Checkpoint  Oracle Redo Logs                      2015-10-13 16:18:52  Thread 2, Seqno 16664, RBA 9732624                      SCN 43.3527050895 (188210644623)   Target Extract Trails:   Trail Name                                       Seqno        RBA     Max MB Trail Type   ./dirdat/sd                                         19       1403         50 EXTTRAIL   GGSCI (dev1.com) 55> info POZGSSD detail EXTRACT    POZGSSD   Last Started 2015-10-13 16:19   Status RUNNING Checkpoint Lag       00:00:00 (updated 00:00:00 ago) Process ID           18661 Log Read Checkpoint  File ./dirdat/sd000019                      First Record  RBA 1403   Target Extract Trails:   Trail Name                                       Seqno        RBA     Max MB Trail Type   ./dirdat/sd                                         18          0         50 RMTTRAIL   GGSCI (dev1.com) 53> ALTER EXTRACT POZGSSD , EXTSEQNO 19, EXTRBA 0 EXTRACT altered. GGSCI (tst1.com) 24> alter replicat ROZGSSD,EXTSEQNO 18,  EXTRBA 0 REPLICAT altered. ON TARGET: GGSCI (tst1.com) 24> alter replicat ROZGSSD,EXTSEQNO 18,  EXTRBA 0 REPLICAT altered. Run marker_setup and review set text files [oradev@dev1 ocfsmnt_script_calissin_diye]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 14 11:31:09 2015 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @marker_setup Marker setup script You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter Oracle GoldenGate schema name:gg Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GG MARKER TABLE ------------------------------- OK MARKER SEQUENCE ------------------------------- OK Script complete. Run ddl_setup. [oradev@dev1 ocfsmnt_script_calissin_diye]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 14 11:32:19 2015 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @ddl_setup Oracle GoldenGate DDL Replication setup script Verifying that current user has privileges to install DDL Replication... You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter Oracle GoldenGate schema name:gg Working, please wait ... Spooling to file ddl_setup_spool.txt Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ... Check complete. Using GG as a Oracle GoldenGate schema name. Working, please wait ... DDL replication setup script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GG CLEAR_TRACE STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors CREATE_TRACE STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors TRACE_PUT_LINE STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors INITIAL_SETUP STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors DDLVERSIONSPECIFIC PACKAGE STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors DDLREPLICATION PACKAGE STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors DDLREPLICATION PACKAGE BODY STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors DDL IGNORE TABLE ----------------------------------- OK DDL IGNORE LOG TABLE ----------------------------------- OK DDLAUX  PACKAGE STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors DDLAUX PACKAGE BODY STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors SYS.DDLCTXINFO  PACKAGE STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors SYS.DDLCTXINFO  PACKAGE BODY STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors DDL HISTORY TABLE ----------------------------------- OK DDL HISTORY TABLE(1) ----------------------------------- OK DDL DUMP TABLES ----------------------------------- OK DDL DUMP COLUMNS ----------------------------------- OK DDL DUMP LOG GROUPS ----------------------------------- OK DDL DUMP PARTITIONS ----------------------------------- OK DDL DUMP PRIMARY KEYS ----------------------------------- OK DDL SEQUENCE ----------------------------------- OK GGS_TEMP_COLS ----------------------------------- OK GGS_TEMP_UK ----------------------------------- OK DDL TRIGGER CODE STATUS: Line/pos   Error ---------- ----------------------------------------------------------------- No errors  No errors DDL TRIGGER INSTALL STATUS ----------------------------------- OK DDL TRIGGER RUNNING STATUS ----------------------------------- ENABLED STAYMETADATA IN TRIGGER ----------------------------------- OFF DDL TRIGGER SQL TRACING ----------------------------------- 0 DDL TRIGGER TRACE LEVEL ----------------------------------- 0 LOCATION OF DDL TRACE FILE ------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/diag/rdbms/dev/dev1/trace/ggs_ddl_trace.log Analyzing installation status... VERSION OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1 STATUS OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ SUCCESSFUL installation of DDL Replication software components Script complete. Run role_setup [oradev@dev1 gg]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 14 11:33:34 2015 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> @role_setup GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.) You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:gg Wrote file role_setup_set.txt PL/SQL procedure successfully completed. Role setup script complete Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command: GRANT GGS_GGSUSER_ROLE TO <loggedUser> where <loggedUser> is the user assigned to the GoldenGate processes. SQL> GRANT GGS_GGSUSER_ROLE TO gg   2  ; Grant succeeded. Run ddl_enable: SQL> @ddl_enable Trigger altered. Start manager and other processes: [oradev@dev1 gg]$ 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. GGSCI (dev1.com) 1> start manager Manager started. GGSCI (dev1.com) 2> info all Program     Status      Group       Lag at Chkpt  Time Since Chkpt MANAGER     RUNNING                                            EXTRACT     STOPPED     PSSDOZG     00:00:00      00:13:15     EXTRACT     STOPPED     XSSDOZG     00:24:58      00:14:38     REPLICAT    STOPPED     RSCSOZG     00:00:00      00:14:58     GGSCI (dev1.com) 3> start * Sending START request to MANAGER ... EXTRACT PSSDOZG starting Sending START request to MANAGER ... EXTRACT XSSDOZG starting Sending START request to MANAGER ... REPLICAT RSCSOZG starting 4. Convert all processes from classic mode to integrated mode according to Note 1484313.1