OGG-00717 : Found unsupported in-memory undo record + Minimum supplemental logging must be enabled to prevent data loss

2015-11-09 16:23:54  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  EXTRACT EXTRACT3 started.
2015-11-09 16:23:54  INFO    OGG-01052  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  No recovery is required for target file /usr/app/db1/ggs/trails/x1000000, at RBA 0 (file not opened).
2015-11-09 16:23:54  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  Output file /usr/app/db1/ggs/trails/x1 is using format RELEASE 11.2.
2015-11-09 16:23:54  ERROR   OGG-00717  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  Found unsupported in-memory undo record in sequence 11566, at RBA 1473040, with SCN 3330.2812655651 (14305053751331) ... Minimum supplemental logging must be enabled to prevent data loss.
2015-11-09 16:23:54  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, EXTRACT3.prm:  PROCESS ABENDING.


Possible Cause:
Supplemental logging is not enabled on the database level or the extract is positioned to a point where the supplemental logging has not been enabled i.e an attempt was made to start the extract when the supplemental logging was not enabled on the database.

Solution:

Check if Supplemental logging is enabled

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;

SUPPLEME
--------
NO

Enable Supplemental Logging

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Database altered.

Switch Logfile

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

And attempt to start the extract with begin now option

GGSCI (sev274) 2> alter extract EXTRACT3, begin now
EXTRACT altered.


GGSCI (sev274) 3> start EXTRACT3

Sending START request to MANAGER ...
EXTRACT EXTRACT3 starting


GGSCI (sev274) 4> info EXTRACT3

EXTRACT    EXTRACT3  Last Started 2015-11-09 16:29   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-09 16:29:20  Seqno 0, RBA 0
                     SCN 0.0 (0)


GGSCI (sev274) 5> !
info EXTRACT3

EXTRACT    EXTRACT3  Last Started 2015-11-09 16:29   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:09 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-09 16:29:20  Seqno 0, RBA 0
                     SCN 0.0 (0)


GGSCI (sev274) 6> !
info EXTRACT3

EXTRACT    EXTRACT3  Last Started 2015-11-09 16:29   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-09 16:55:07  Seqno 11567, RBA 281600
                     SCN 3330.2812657068 (14305053752748)

OGG-01004 + Database error 26945 (OCI Error ORA-26945: unsupported hint RESTRICT_ALL_REF_CONS

While using delete cascade on the database, the replicat needs below parameter set in the prm file

DBOPTIONS DEFERREFCONST

After setting this, the replicat was getting abended with the below error

2015-10-30 09:21:34  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, rapi8602.prm:  OCI Error ORA-26945: unsupported hint RESTRICT_ALL_REF_CONS (status = 26945). DELETE /*+ RESTRICT_ALL_REF_CONS */ FROM "user5"."transactions"  WHERE "ID" = :b0.
2015-10-30 09:21:34  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, rapi8602.prm:  Aborted grouped transaction on 'user5.transactions', Database error 26945 (OCI Error ORA-26945: unsupported hint RESTRICT_ALL_REF_CONS (status = 26945). DELETE /*+ RESTRICT_ALL_REF_CONS */ FROM "user5"."transactions"  WHERE "ID" = :b0).
2015-10-30 09:21:34  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, rapi8602.prm:  Repositioning to rba 1679 in seqno 8.
2015-10-30 09:21:34  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rapi8602.prm:  SQL error 26945 mapping user5.transactions to user5.transactions OCI Error ORA-26945: unsupported hint RESTRICT_ALL_REF_CONS (status = 26945). DELETE /*+ RESTRICT_ALL_REF_CONS */ FROM "user5"."transactions"  WHERE "ID" = :b0.

Solution :

exec dbms_goldengate_auth.grant_admin_privilege('<GGSUSER>');
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE = BOTH;

Oracle : Recycle Bin ORA-02096

10g

On Database level

11:15:22 DB2 @ sev1 > show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

11:15:45 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = OFF ;

System altered.

11:17:45 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = ON ;

System altered.

11g

We need to specify DEFFERED to turn off recyclebin on 11g databases, else you will see the below error. The change will take place from the new sessions

11:15:26 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = OFF ;        
ALTER SYSTEM SET recyclebin = OFF
                                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

Fix :

11:15:45 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = OFF DEFERRED;

System altered.

11:15:58 DB2 @ sev1 > ALTER SYSTEM SET recyclebin = ON DEFERRED;

System altered.

Turning off recyclebin on session level

11:18:23 DB2 @ sev1 > ALTER SESSION SET recyclebin = OFF ;

11:19:17 DB2 @ sev1 > ALTER SESSION SET recyclebin = ON ;

ERROR OGG-00446 Oracle GoldenGate Delivery for Oracle, replicat3.prm: Unable to lock file "/usr/app/db1/ggs/11.2.1.0.28/dirchk/REPLICAT3.cpr" (error 11, Resource temporarily unavailable). Lock currently held by process id (PID) 68732

ERROR   OGG-00446  Oracle GoldenGate Delivery for Oracle, replicat3.prm:  Unable to lock file "/usr/app/db1/ggs/11.2.1.0.28/dirchk/REPLICAT3.cpr" (error 11, Resource temporarily unavailable).  Lock currently held by process id (PID) 68732

Replicat was getting abended with above error, checked to see what the process PID 68732 is, but nothing was showing up with

ps -ef | grep 68732

and no processes showed as running with the replicat REPLICAT3 name.

Solution :

Copied the original  REPLICAT3.cpr file as REPLICAT3.cpr.tmp , removed the original file REPLICAT3.cpr and renamed REPLICAT3.cpr.tmp as REPLICAT3.cpr.

This released the lock and replicat process started running

Steps :
cp REPLICAT3.cpr REPLICAT3.cpr.tmp
rm REPLICAT3.cpr
mv REPLICAT3.cpr.tmp REPLICAT3.cpr

ERROR OGG-01032 There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. Length is 3 - 000000: 46 45 00 |FE. |.

After the Server 2 crashed and brought up the pumps on Server 1 sending trails to Server2 was abending with below error

ERROR   OGG-01032  There is a problem in network communication, a remote file problem, encryption keys for target and source do not match (if using ENCRYPT) or an unknown error. Length is 3 - 000000: 46 45 00                                        |FE.             |.

Cause

Trail file system on target goes out of space causing the pump  to hang. The issue is ultimately due to an  interrupted rmt trail writing caused by connection issues because the target file system cannot be written to.

Note that this is a single instance of a broader issue. Anytime the collector server fails for any reason, it will look like a TCP or network issue to the pump. This can be space or security or path issues or anything else.

Solution

Fix any target space issue.

1) Go the source server and login into ggsci

- Execute the below command
alter <extract pump> etrollover

start <extract pump>

(ETROLLOVER will force the pump to write on a fresh trail)

3) Go the target server and check if the new trail files are being generated.

4) Verify if the first record in the new trail file is indeed the next record in the local extract trail. (It can be done by looking at the RBA value through the logdump utility)

5) Alter the replicat to the new trail file
alter <replicat> extseqno <new trail file> extrba 0

6) Start the Replicat process

7) Verify if all the processes are running fine. 

Oracle : Determine Backups/Restore Time

col OPNAME for a30

select OPNAME,SOFAR/TOTALWORK*100 PERCENT_SOFAR, trunc(TIME_REMAINING/60) TIME_REMAINING,

trunc(ELAPSED_SECONDS/60) TIME_CONSUMED

from v$session_longops where TOTALWORK>0 and OPNAME like '%RMAN%';

Output for above query

OPNAME                         PERCENT_SOFAR TIME_REMAINING TIME_CONSUMED

------------------------------ ------------- -------------- -------------

RMAN: incremental datafile bac           100              0             0

kup

RMAN: full datafile backup               100              0             0

RMAN: incremental datafile bac           100              0             0

kup

RMAN: full datafile backup               100              0             0

RMAN: full datafile backup               100              0             0

RMAN: full datafile backup               100              0             0

RMAN: archived log backup                100              0             0

RMAN: archived log backup                100              0             0

RMAN: archived log backup                100              0             0

RMAN: archived log backup                100              0             0

Understanding the Output

About v$session_longops : Please click here 

OPNAME : Shows the Brief description of the operation

PERCENT_SOFAR : Percentage completed sofar

TIME_REMAINING : Estimated remaining time to complete the job

TIME_CONSUMED : Elapsed Time for the job

You can alter the above query to find the time remaining for 'restore' work as well by substituting 'RMAN' with 'RESTORE'

Oracle : ORA-06570: shared pool object does not exist, cannot be pinned/purged

SQL> select ADDRESS, HASH_VALUE from GV$SQLAREA where SQL_ID like '8xzc%';

ADDRESS HASH_VALUE
---------------- ----------
00000067B31EB568  1105770

Running from first node

SQL> execute dbms_shared_pool.purge('00000067B31EB568,1105770','C');
BEGIN dbms_shared_pool.purge('00000067B31EB568,1105770','C'); END;

*
ERROR at line 1:
ORA-06570: shared pool object does not exist, cannot be pinned/purged
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 48
ORA-06512: at "SYS.DBMS_SHARED_POOL", line 115
ORA-06512: at line 1

As the details were obtained from GV$SQLAREA and it is RAC database, the purge should be done on another node as it might not be on the library cache on the instance you are connected.

Now, running it from another node

SQL> execute dbms_shared_pool.purge('00000067B31EB568,1105770','C');

PL/SQL procedure successfully completed.