Oracle : Prerequisite check "CheckActiveFilesAndExecutables" failed ; OPatch failed with error code 73

While patching the oracle database, the patch apply failed with below error

Following executables are active :
/opt/app/oracle_db/oracle/product/oracle_db/lib/libclntsh.so.11.1
UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: /opt/app/oracle_db/oracle/product/oracle_db/cfgtoollogs/opatch/opatch2016-02-25_11-13-41AM_1.log

OPatch failed with error code 73

Cause :
Something is using the library , we need to find what is using it and kill it.

find the culprit using fuser

Solution :

oracle_db@sev2(174) oracle_db /opt/app/patches
$ fuser /opt/app/oracle_db/oracle/product/oracle_db/lib/libclntsh.so.11.1
/opt/app/oracle_db/oracle/product/oracle_db/lib/libclntsh.so.11.1: 118942m
oracle_db@sev2(175) oracle_db /opt/app/patches
$ ps -ef | grep 118942
oracle_db  95662  57918  0 11:15 pts/0    00:00:00 grep 118942
oracle_db 118942 118378  0 Jan21 ?        00:00:00 sqlplus -s              @/opt/app/oracle_db/backup/rman/common/info.sql  oracle_db
oracle_db@sev2(176) oracle_db /opt/app/patches
$ kill -9 118942

And then start the patch apply. Hope this resolves your problem

Oracle : How to add comments to Alert Log

To add comments to Alert log of Oracle Database, you can run the below command

SQL> execute sys.dbms_system.ksdwrt(2,'Hi, Hello World');

PL/SQL procedure successfully completed.

Note that the first parameter MUST be a 2.

ERROR OGG-01028 Formatting error on: table name + Error converting timestamp with timezone from Oracle to ASCII format for column

2015-11-16 17:51:18  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  EXTRACT EXTRACT1 started.
2015-11-16 17:51:18  INFO    OGG-01055  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Recovery initialization completed for target file /usr/app/datab1/ggs/trails/a1000007, at RBA 1144.
2015-11-16 17:51:18  INFO    OGG-01478  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Output file /usr/app/datab1/ggs/trails/a1 is using format RELEASE 11.2.
2015-11-16 17:51:18  INFO    OGG-01026  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Rolling over remote file /usr/app/datab1/ggs/trails/a1000007.
2015-11-16 17:51:18  INFO    OGG-01053  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Recovery completed for target file /usr/app/datab1/ggs/trails/a1000008, at RBA 1144.
2015-11-16 17:51:18  INFO    OGG-01057  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Recovery completed for all targets.
2015-11-16 17:51:18  INFO    OGG-01517  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Position of first record processed Sequence 17507, RBA 21008, SCN 3332.2582756465, Nov 16, 2015 5:32:16 PM.
2015-11-16 17:51:19  INFO    OGG-01020  Oracle GoldenGate Delivery for Oracle, rccp004a.prm:  Processed extract process RESTART_ABEND record at seq 8, rba 1081 (aborted 0 records).
2015-11-16 17:51:21  ERROR   OGG-01028  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  Formatting error on: table name CSI_CAET.ERROR_HIT, rowid AAAYnTAAGAAACM/AAA, XID 1.1.2642, position (Seqno 17507, RBA 21616). Error converting timestamp with timezone from Oracle to ASCII format for column LAST_UPDATE_DATE.
2015-11-16 17:51:21  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, EXTRACT1.prm:  PROCESS ABENDING.


GGSCI (Sever2) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     ABENDED     EXTRACT1    00:14:10      00:58:53    


GGSCI (Sever2) 2> edit params EXTRACT1

Solution :
Oracle reported bug 14053498 for the above error {GoldenGate Extract Abends: OGG-01028 Formatting Error on: table name xxxx.xxxx rowid , XID nnnn.nn.nnnn, Position (Seqno n, RBA n) (Doc ID 1558791.1)}

Another Solution

Check the database characterset

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY


Add below two parameters to the extract prm file

SETENV (NLS_LANG="AMERICAN_AMERICA.WE8ISO8859P1") -- Please make sure you have the correct characterset from the above output
TRANLOGOPTIONS INCLUDEREGIONID

GGSCI (Sever2) 4> start EXTRACT1

Sending START request to MANAGER ...
EXTRACT EXTRACT1 starting


GGSCI (Sever2) 5> info EXTRACT1

EXTRACT    EXTRACT1  Last Started 2015-11-16 18:50   Status RUNNING
Checkpoint Lag       01:13:36 (updated 00:00:01 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2015-11-16 17:37:08  Seqno 17507, RBA 176876032
                     SCN 3332.2582759675 (14313413789947)

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