Oracle GoldenGate 11.2

OGG 11.2 offers  integrated capture mode. Extract integrates with database log mining server to receive change data in LCR format .
Oracle is utilizing Streams more and more ..

Oracle database must be Oracle 11.2.0.3 with the patch for bug 13560925.

Here is example how to change extract more to integrated and back to classic mode.

You can check current extract mode using:
info extract <ext> , it will show following:

for IE mode: Log Read Checkpoint Oracle Integrated Redo Logs

for classic: Log Read Checkpoint Oracle Redo Logs

[oracle@test gg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.0 OGGCORE_11.2.1.0.0_PLATFORMS_120131.1910_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Feb  1 2012 00:55:59

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

GGSCI (test) 1> dblogin  userid srvegate, password test
Successfully logged into database.

GGSCI (test) 2> alter extract testext, upgrade integrated tranlog
ERROR: REGISTER EXTRACT testext DATABASE must be performed before upgrading to integrated capture..
GGSCI (test) 4> REGISTER EXTRACT testext DATABASE

2012-03-26 16:42:07  WARNING OGG-02064  Oracle compatibility version 11.2.0.0.0 has limited datatype support for integra                          ted capture. Version 11.2.0.3 required for full support.
2012-03-26 16:42:50  INFO    OGG-02003  Extract testext successfully registered with database at SCN 18279580332.
GGSCI (test) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     testext      00:27:39      00:00:01
EXTRACT     RUNNING     testpump     00:00:00      00:00:07
GGSCI (test) 3> ALTER EXTRACT testext,  UPGRADE INTEGRATED TRANLOG
ERROR: EXTRACT testext is running and cannot be altered (1,2,No such file or directory).
GGSCI (test) 4> stop testext

Sending STOP request to EXTRACT testext …
Request processed.

GGSCI (test) 6> dblogin userid srvegate, password test
Successfully logged into database.

GGSCI (test) 7>  ALTER EXTRACT testext,  UPGRADE INTEGRATED TRANLOG
Extract testext successfully upgraded to integrated capture.
GGSCI (test) 8> start testext

Sending START request to MANAGER …
EXTRACT testext starting

GGSCI (test) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     testext      00:20:00      00:00:01
EXTRACT     RUNNING     testpump     00:00:00      00:00:03

 
GGSCI (test) 1> dblogin  userid srvegate, password test
Successfully logged into database.

GGSCI (test) 2> alter extract testext, DOWNGRADE INTEGRATED TRANLOG

ERROR: Found checkpoint values from mulitiple threads. Need to use the THREADS option with 2 or greater..
GGSCI (test) 3> alter extract testext, DOWNGRADE INTEGRATED TRANLOG threads 2
Extract testext successfully downgraded from integrated capture.
GGSCI (test) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     testext      00:00:00      00:02:35
EXTRACT     RUNNING     testpump     00:00:00      00:00:04
GGSCI (test) 5> start testext

Sending START request to MANAGER …
EXTRACT testext starting
GGSCI (test) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     testext      00:00:03      00:00:00
EXTRACT     RUNNING     testpump     00:00:00      00:00:01

Oracle GoldenGate and Encrypted Tablespaces (TDE) in 11.2

To make GG work with encrypted tablespaces  you have to apply database patch:

Patch  10395645 for Oracle 11.2.0.2.

mkstore -wrl ./ -createEntry ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG

Supply shared secret for GG ( not wallet password).Last step is to supply wallet password.Copy ewallet.p12 file to other nodes ( if running RAC but not sharing wallet location ).
Do not forget standby databases if you have them.
If you like to see shared secret for GG you can run:

 mkstore -wrl  /u01/app/oracle/wallet -viewEntry ORACLE.SECURITY.CL.ENCRYPTION.ORACLEGG
Create package  dbms_internal_clkm.Package code get created by running script prvtclkm.plb  located under GG home .Once package is created grant execute privileget to GG user:

grant execute on  on thedbms_internal_clkm to gguser;

This package has only one procedure  called GET_KEY with following parameters:

SQL> desc dbms_internal_clkm
PROCEDURE GET_KEY
 Argument Name                  Type                    In/Out Default?
 —————————— ———————– —— ——–
 CLIENT                         VARCHAR2                IN
 MASTER_KEY_ID                  VARCHAR2                IN
 WRAPPED_KEY                    VARCHAR2                IN
 FLAGS                          BINARY_INTEGER          IN
 KEY                            VARCHAR2                OUT
Encrypt the Shared secret key
GGSCI> ENCRYPT PASSWORD “shared key”
Add an entry to the Extract parameter file to decrpt the new shared password

DBOPTIONS  DECRYPTPASSWORD “SHARED KEY”
Close and open wallet to clear caches.If you have wallet enabled with auto login ( file cwallet.sso ) than disable it temporary to close and open wallet.

Oracle GoldenGate version 11.1.1.1 released

After long wait Oracle finaly relesed new Oracle GoldenGate version 11.1.1.1 which supports encrypted tablespaces.

To download it click on the link below:

https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=13730870

Also there is new documentation for this release:

http://download.oracle.com/docs/cd/E22355_01/index.htm

About  Replicating TDE-encrypted data

  Oracle GoldenGate supports the Transparent Data Encryption (TDE) at the column and tablespace level.

● Column-level encryption is supported for all versions of 10.2.0.5, 11.1, and 11.2.

● Tablespace-level encryption is supported for all versions of 10.2.0.5 and 11.1.0.2.

 Required database patches

  To support TDE, one of the following Oracle patches must be applied to the database,

depending on the version.

 ● Patch 10628966 for 10.2.0.5.2PSU

● Patch 10628963 for 11.1.0.7.6PSU

● Patch 10628961 for 11.2.0.2

Role-Based Database Service

Starting with 11.2 role based services are managed by Clusterware.
For previous releases we had to use after db_role_change trigger.That is  no longer required.

So in the past :

/* Create Service */

exec DBMS_SERVICE.CREATE_SERVICE(service_name =>’taf’,network_name =>’taf’,aq_ha_notifications
=>true,failover_method =>’BASIC’,failover_type =>’SELECT’,failover_retries =>180,failover_delay => 5);

and have database trigger to start service in case database role switch happens:

CREATE OR REPLACE TRIGGER set_rc_svc AFTER DB_ROLE_CHANGE ON DATABASE DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = ‘PRIMARY’ THEN
DBMS_SERVICE.START_SERVICE(‘taf’);
ELSE
DBMS_SERVICE.STOP_SERVICE(‘taf’);
END IF;
END;
/

In 11.2  we can create database service which does not require this trigger.Clusterware will handle role change:

Oracle Notification Service ( ONS) and ONS for Enterprise Manager (ONS) must be enabled

So command to create role-based service looks something like this:

srvctl add service -d test -s taf -l PRIMARY -e session -m basic -q TRUE

It will enabled when database is serving as primary database.

Similar service can be created for STANDBY database:

srvctl add service -d test -s tafstandby -l PHYSICAL_STANDBY -e session -m basic -q TRUE

Manual Switchover involving RAC 11g using SQL*Plus

1.    Verify that it is possible to perform a switchover operation. On the primary query the switchover_status column of v$database to verify that switchover to standby is possible.

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
——————
TO STANDBY ( or SESSIONS ACTIVE is also ok)


SESSIONS ACTIVE – Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted. Query the V$SESSION view to identify the specific processes that need to be terminated.

In order to perform a switchover all sessions to the database need to be disconnected.Nothing to worry because ( with session shutdown) will take care of it.

Shutdown ALL RAC instances BUT ONE before attempt a switchover!

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

select thread#, max(sequence#) “Last Primary Seq Generated”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

SQL> alter database commit to switchover to standby with session shutdown;

2.  Shutdown the former primary and mount as a standby database:

SQL> shutdown immediate
SQL> startup mount

3.  Defer the remote archive destination on the old primary( new standby):

SQL> alter system set log_archive_dest_state_2=defer;

4.  Verify that the physical standby can be converted to the new primary:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
——————
TO PRIMARY

Note that if the status returns SESSIONS ACTIVE then you should append the with session shutdown clause.

5.  Convert the physical standby to the new primary: