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> r
1* select current_Scn from v$database
CURRENT_SCN
--------------------
188313744261
| 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.
$ ps -ef|grep ocfs
[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.
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.
[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.
[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.
[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.
[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
|
