OGG-00730 No minimum supplemental logging is enabled.

My GG version: 11.2.1.0.x

OS version:  RHEL 5.x

Oracle version: 11.2.0.2

 Extract ABENDS with below error

ERROR   OGG-00730  No minimum supplemental logging is enabled. This may cause extract process to handle key update incorrectly if key column is not in first row piece.

This error occurs when we haven't yet enabled supplemental logging on the database level but when I check my database it has supplemental logging enabled

SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;

FOR SUPPLEME

--- --------

YES YES

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     5

Next log sequence to archive   6

Current log sequence           6

Solution:        This is a BUG, [1571665.1]

           Added undocumented parameter “TRANLOGOPTIONS DISABLESUPPLOGCHECK” parameter to extract and once started then removed it from paramfile and everything worked fine

Generate GOLDENGATE Encryption Keys using Keygen

My GG version: 11.2.1.0.x

OS version:  RHEL 5.x

Oracle version: 11.2.0.2

In order to generate keys, run the keygen command from the GoldenGate software installation home

KEYGEN   key length   n

Where:

(key length) is the encryption key length, up to 128 bits.

(n) represents the number of keys to generate.

oracle@oradev01:/opt/oracle/GGATE  $ ./keygen  128 4

0x022A972B7CF6EF537DBCF35792BEH321

0x03CC8167B516426D9CA3A70B5B1FDW12

0x066E6C23EF369406BA8A5B3F2580DF54

0x0A10575F2857E61FD8710F73EFE08H11

Save the file as the name ENCKEYS in all upper case letters, without an extension, in the Oracle GoldenGate installation directory.

oracle@oradev01:/opt/oracle/GGATE $  vi ENCKEYS

oracle@oradev01:/opt/oracle/GGATE $ cat ENCKEYS

key1 0x022A972B7CF6EF537DBCF35792BEH321

key2 0x03CC8167B516426D9CA3A70B5B1FDW12

key3 0x066E6C23EF369406BA8A5B3F2580DF54

key4 0x0A10575F2857E61FD8710F73EFE08H11

Copy this ENCKEYS file to all the target servers in the GG home location. The key names and values in all of the ENCKEYS files must be identical, or else the data exchange will fail

oracle@oradev01:/opt/oracle/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 (oradev01) 1> encrypt password gguser123

Expecting either 'ENCRYPTKEY' or an encryption algorithm

we need to give encrypt key which is specified in ENCKEYS file

GGSCI (oradev01) 2> encrypt password gguser123 ENCRYPTKEY key1

Encrypted password:  AADAAAAAAAAAAAKAVHWAGJIGWBGHDBRAGJMIPEQEXBYEMDYIRBICFHSCTDHHEJHANCKAUDDGQJPBHRDT

Algorithm used:  AES128

Check whether you can able to connect with this encrypted password

GGSCI (oradev01) 3> dblogin USERID gguser@MYDB, PASSWORD AADAAAAAAAAAAAKAVHWAGJIGWBGHDBRAGJMIPEQEXBYEMDYIRBICFHSCTDHHEJHANCKAUDDGQJPBHRDT, encryptkey key1

Successfully logged into database.

GGSCI (oradev01) 3> dblogin USERID gguser@MYDB, PASSWORD gguser123

Successfully logged into database.

So you can use this encrypted password in your extract param file

GGSCI (oradev01) 4> edit params ext

GGSCI (oradev01) 6> view params ext

EXTRACT ext

USERID gguser@MYDB, PASSWORD AADAAAAAAAAAAAKAVHWAGJIGWBGHDBRAGJMIPEQEXBYEMDYIRBICFHSCTDHHEJHANCKAUDDGQJPBHRDT, encryptkey  key1

EXTTRAIL /opt/oracle/GGATE/dirdat/lt

………

…..

………….

SETENV NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1"

TABLE schema.table_name

NOTE:  The only way to protect this ENCKEYS file from other is through o/s privileges (Doc ID 1575452.1)

Install Grid Agent from 11g OEM

To install Agent on the oracle linux server

Go to Deployments TAB from 11g Grid control page

Click on Install Agent, then the below screen will be appeared

Click on the links which you want to perform, here I click on Fresh install

NOTE: If we want to upgrade agent from 10g to 11gr1 then simply click on Upgrade agent, so no need to install 11g agent on the server it will automatically install 11g and upgrade from 10g to 11g.

Fill all the fields according to the environment.

Here you need to know the Management Server Registration Password  (OMS password) to continue.

Once done verify from the server : ./emctl status agent

Verify from grid control, it should show new oracle linux HOST

Run the .../agent11g/root.sh script (as root) as soon after installation is complete (or) you can check the "run root.sh" box if you have root access

Useful Standby database commands

To see if the MRP is running or not

 SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

How to Check if Active Data Guard is Already Enabled :

SELECT 'Using Active Data Guard' ADG FROM V$MANAGED_STANDBY M, V$DATABASE D WHERE M.PROCESS LIKE 'MRP%' AND D.OPEN_MODE='READ ONLY';

ADG

-----------------------                                               (if ENABLED)

Using Active Data Guard                     

no rows selected.                                                              (if NOT ENABLED)

To start Redo Apply, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

To stop recovery of a standby database:

SQL>   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

On primary:

SQL >  SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"

FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#) ORDER BY 1;

On Standby:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Check Archive gap on Standby:

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Check the max log sequence on Primary DB

 SELECT Max(sequence#) FROM   v$log_history;

# check the last log applied on STANDBY
SELECT thread#,   Max(sequence#) "Last Standby Seq Applied"
FROM   v$archived_log   WHERE  applied = 'YES'  GROUP  BY thread#   ORDER  BY 1; 

SQL> SELECT * FROM V$DATAGUARD_STATS WHERE NAME=’transport lag’;

SQL> SELECT * FROM V$DATAGUARD_STATS WHERE NAME=’apply lag’;

On primary:  (Where dblink_stby à dblink on the primary that points to the standby database )

SQL> select scn_to_timestamp((select current_scn from v$database))-scn_to_timestamp((select current_scn from v$database@dblink_stby)) from dual;

The value returned from the query indicates the number of seconds that data on the standby lags behind the current position of the primary database.

Find Redo GAP: (on STANDBY)

SQL> select * from v$archive_gap;

thread#    low_sequence#    high_sequence#
---------------------------------------------------------------------------
2              222                   222
3              341                   342

Identify missing archive logs from above output:

SQL> select name from v$archived_log where thread# = 2 and dest_id = 2 and sequence# = 222;