Thursday, March 3, 2016

ERROR OGG-01705 Input checkpoint position for input trail file is greater than the size of the file

Pump, Extract or Replicat may get stuck on a trail file even if there are more trail files available in machine for the reader object to process. This issue occurs when the pump extract / Replicat read checkpoint RBA is larger than the local trail file size that it reads.
Here my Replicat got abended with the following error message …….

ERROR   OGG-01705  Input checkpoint position 33144618 for input trail file './dirdat/c4001256' is greater than the size of the file (33079717).  Please consult Oracle Knowledge Management Doc ID 1138409.1. for instructions.


GGSCI (oracledev01) 9> info REPDEV

REPLICAT   REPDEV  Last Started 2016-03-01 19:10   Status ABENDED
Checkpoint Lag       00:00:00 (updated 240:44:11 ago)
Log Read Checkpoint  File ./dirdat/c4001256
                     2016-02-20 19:19:49.000207  RBA 33144618

Now let’s check the trail file size…………

 [oracle@oracledev01 GG11]$ ls -l dirdat/c4001256
-rw-r----- 1 oracle oinstall 33079717 Feb 22 11:48 dirdat/c4001256

From above we can see that RBA is greater than the trail file size

FILTERDUPTRANSACTIONS
When using this option to start the replicat, it will use the information already stored in the checkpoint table and filter out transactions already applied.
This option is valid only for Replicat in nonintegrated mode. Integrated mode handles duplicate transactions transparently.

[oracle@oracledev01 GG11]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.14 17547423 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131022.0605_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 22 2013 11:03:39
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (oracledev01) 1> start replicat REPDEV FILTERDUPTRANSACTIONS
Sending START request to MANAGER ...
REPLICAT REPDEV starting

GGSCI (oracledev01) 2> info REPDEV
REPLICAT   REPDEV  Last Started 2016-03-01 20:06   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint  File ./dirdat/c4001257
                                         First Record  RBA 0


GGSCI (oracledev01) 3> info REPDEV
REPLICAT   REPDEV  Last Started 2016-03-01 20:06   Status RUNNING
Checkpoint Lag       240:46:27 (updated 00:00:03 ago)
Log Read Checkpoint  File ./dirdat/c4001257
                   2016-02-20 19:19:57.964167  RBA 276602

From the above we can see my replicat has moved on to the next trail file “c4001257” and it is up and running fine J

Refer:

OGG Extract / Replicat Checkpoint RBA Is Larger than Local Trail Size (Doc ID 1138409.1)

Wednesday, November 11, 2015

ORA-39070: Unable to open the log file during expdp in RAC ASM


DB version: 11.2.0.4 RAC 2 node
Os version: RHEL 6

SQL> create directory EXP_DIR as '+FRADG/EXP';

Directory created.

SQL> grant read,write on directory EXP_DIR to sys;

Grant succeeded.

[oracle@oraclehost]$ expdp directory=EXP_DIR dumpfile=STDBY_FULL_metadata.dmp logfile=exp_STDBY_FULL_metadata.log full=y content=metadata_only

Export: Release 11.2.0.4.0 - Production on Sat Oct 10 23:39:03 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation


Solution:

You cannot put logfiles in asm directory so specify another directory for logfile file or use nologfile=y

Ex:
 logfile= EXP: exp_STDBY_FULL_metadata.log    à where ‘EXP’ is directory in my local
 (or)
 Nologfile=y     à This does not create any directory at all

Ref:
DataPump Export (EXPDP) To ASM Directory Fails With Errors ORA-6512 At SYS.UTL_FILE (Doc ID 1464897.1)


ORA-00245: control file backup operation failed during RMAN backup

DB: 11.2.0.4 2 node RAC
OS:  RHEL5

When I’m trying to take FULL Db backup my backup failed and I see the below error message in the logfile


[oracle@oracledev]~/CB> nohup ./bkup.ksh &

released channel: ch2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 08/21/2015 18:02:18

RMAN-03009: failure of backup command on ch2 channel at 08/21/2015 18:00:42
ORA-00245: control file backup operation failed

Recovery Manager complete.

This is the Error Specific to the RAC database, you don’t see this in NON-RAC database.
From 11.2.0.2, RMAN fails on RAC when doing controlfile autobackup which is set to DEFAULT (local server location)


Solution:


RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/DBhome1R2/dbs/snapcf_TEST.f'; # default

From the above output we see my snapshot control file  set to the default location, so we need to change to common directory for all the nodes, here I’m using ASM so I’ll set this location to ASM disk group
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/snapcf_TEST.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DATA/snapcf_TEST.f';
new RMAN configuration parameters are successfully stored


Reference:

ORA-245: In RAC environment from 11.2 onwards Backup Or Snapshot controlfile needs to be in shared location (Doc ID 1472171.1)


Thursday, October 22, 2015

ORA-39070: Unable to open the log file during EXPDP in RAC ASM

DB version: 11.2.0.4 RAC 2 node
Os version: RHEL 6

SQL> create directory EXP_DIR as '+FRADG/EXP';

Directory created.

SQL> grant read,write on directory EXP_DIR to sys;

Grant succeeded.

[oracle@oraclehost]$ expdp directory=EXP_DIR dumpfile=STDBY_FULL_metadata.dmp logfile=exp_STDBY_FULL_metadata.log full=y content=metadata_only

Export: Release 11.2.0.4.0 - Production on Sat Oct 10 23:39:03 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation


Solution:

You cannot put logfiles in asm directory so specify another directory for logfile file or use nologfile=y

Ex:
 logfile= EXP: exp_STDBY_FULL_metadata.log    à where ‘EXP’ is the directory in my local filesystem
 (or)
 Nologfile=y     à This does not create any Logfile at all

Ref:
DataPump Export (EXPDP) To ASM Directory Fails With Errors ORA-6512 At SYS.UTL_FILE (Doc ID 1464897.1)

ORA-01466: unable to read data - table definition has changed


DB version: 11.2.0.4
OS: RHEL 6

My export command:

expdp directory=EXP_DIR dumpfile=SBX_STDBY _%U.dmp logfile=EXP:exp_SBX_STDBY.log schemas=SCHEMA_NAME flashback_scn=5424088950 parallel=4 reuse_dumpfiles=y cluster=N

 ORA-31693: Table data object "SCHEMA"."TABLE_NAME" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data - table definition has changed


Reason:

There is a DDL operation performed on this table causing the error.
Here i gave SCN number for an object before its LST_DDL_TIME 

Ex:
SQL> select object_name,to_timestamp(last_ddl_time) from dba_objects where owner=’SCHEMA_NAME’  and object_name=’TABLE_NAME’;

OBJECT_NAME
--------------------------------------------------------------------------------
TO_TIMESTAMP(LAST_DDL_TIME)
---------------------------------------------------------------------------
TDFM_EVT
15-OCT-15 12.00.00 AM


SQL> select SCN_TO_TIMESTAMP(5424088950) from dual;

SCN_TO_TIMESTAMP(5424088950)
---------------------------------------------------------------------------
15-OCT-15 08.44.33.000000000 PM


Solution:

Remove FLASHBACK_SCN  parameter and rerun  (or)
Specify SCN after the time “LST_DDL_TIME” and start again …..


Ref:

EXPDP - How to Solve "ORA-01466: unable to read data - table definition has changed" (Doc ID 1902481.1)


Saturday, September 5, 2015

OGG-01496 Failed to open target trail file


My pump process is down for so many days and when I tried to start the process it is getting ABENDED because it is trying to write in the remote trail file which is MISSING



GGSCI (oracleqa011.domain.com) 7> start PUMPQA

Sending START request to MANAGER ...
EXTRACT PUMPQA starting


GGSCI (oracleqa011.domain.com) 8> info PUMPQA

EXTRACT    PUMPQA  Last Started 2015-08-20 19:59   Status ABENDED
Checkpoint Lag       00:00:00 (updated 00:00:28 ago)
Log Read Checkpoint  File ./dirdat/cb000011  --> This is  source trail file
                     2015-08-20 19:59:30.000000  RBA 36679


GGSCI (oracleqa011.domain.com) 9> view report PUMPQA


2015-08-20 19:59:44  ERROR   OGG-01496  Failed to open target trail file /u01/oracle/TARGET/cb000008, at RBA 8448365.

2015-08-20 19:59:44  ERROR   OGG-01668  PROCESS ABENDING.


Here pump process is trying to write to the file “cb000008” where is has left before, since it’s been long ago this file doesn’t exist in the target location


Tried doing begin now command, but doesn’t work

GGSCI (oracleqa011.domain.com) 10> alter PUMPQA begin now
EXTRACT altered.

Still “ABENDED”    -->  because it will begin now but try to write to the same trail file


Solution:

Do “ETROLLOVER” for pump process, this will roll over to the next trail file in the sequence (here cb000009)

GGSCI (oracleqa011.domain.com) 10> ALTER EXTRACT PUMPQA ETROLLOVER

2015-08-20 20:02:22  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 (oracleqa011.domain.com) 11> start PUMPQA

Sending START request to MANAGER ...
EXTRACT PUMPQA starting


GGSCI (oracleqa011.domain.com) 12> info PUMPQA

EXTRACT    PUMPQA  Last Started 2015-08-20 20:02   Status RUNNING
Checkpoint Lag       00:03:09 (updated 00:00:08 ago)
Log Read Checkpoint  File ./dirdat/cb000011
                     2015-08-20 19:59:30.000000  RBA 36679



In the target server I see “cb000009” has been created and writing into it.


ERROR OGG-01224 Address already in use.


DB version: 11.2.0.4 2-Node RAC
OS:  RHEL 6
GG version :  11.2.1.0.3

Today when i'm trying to start my manager process it is not starting 

GGSCI (oracledev01) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
JAGENT      STOPPED
EXTRACT     ABENDED     EOECBP      291:56:23     27:02:50

GGSCI (oracledev01) 2> start mgr

Manager started.

But when I do info mgr it says manager is DOWN…..

GGSCI (oracledev01) 3> info mgr

Manager is DOWN!


GGSCI (oracledev01) 4> view params mgr

PORT 7809
DYNAMICPORTLIST 7840-9860


GGSCI (oracledev01) 5> view report mgr


***********************************************************************
                 Oracle GoldenGate Manager for Oracle
 Version 11.2.1.0.14 17547423 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131022.0605
   Linux, x64, 64bit (optimized), Oracle 11g on Oct 22 2013 07:38:49

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


                    Starting at 2015-08-11 19:56:22
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Fri May 29 10:16:43 EDT 2015, Release 2.6.32-504.23.4.el6.x86_64
Node: sl73orcdbdbq005
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 36788

Parameters...

PORT 7809
DYNAMICPORTLIST 7840-9860


***********************************************************************
**                     Run Time Messages                             **
***********************************************************************


Source Context :
  SourceModule            : [mgr.main]
  SourceID                : [/scratch/aime1/adestore/views/aime1_adc4150267/oggcore/OpenSys/src/app/mgr/mgr.c]
  SourceFunction          : [init_functions]
  SourceLine              : [3390]
  ThreadBacktrace         : [8] elements
                          : [/gg/GG11/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7ff4595509fe]]
                          : [/gg/GG11/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2cc) [0x7ff45954974c]]
                          : [/gg/GG11/libgglog.so(_MSG_ERR_TCP_GENERIC(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x31) [0x7ff4595318a5]]
                          : [./mgr(init_functions(int, char**)+0x7f5) [0x4511c5]]
                          : [./mgr(main_loop(int, char**)+0x4c) [0x454aec]]
                          : [./mgr(main+0xf2) [0x455362]]
                          : [/lib64/libc.so.6(__libc_start_main+0xfd) [0x3d9361ed5d]]
                          : [./mgr(__gxx_personality_v0+0x142) [0x43efca]]

2015-08-11 19:56:22  ERROR   OGG-01224  Address already in use.

2015-08-11 19:56:22  ERROR   OGG-01668  PROCESS ABENDING.


Error in ggserr.log:

2015-08-11 19:56:10  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info mgr.
2015-08-11 19:56:19  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.
2015-08-11 19:56:22  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start mgr.
2015-08-11 19:56:22  ERROR   OGG-01224  Oracle GoldenGate Manager for Oracle, mgr.prm:  Address already in use.
2015-08-11 19:56:22  ERROR   OGG-01668  Oracle GoldenGate Manager for Oracle, mgr.prm:  PROCESS ABENDING.


CAUSE:

Previous mgr process is still running, which used port 7809 according to parameter file.


SOLUTION:

Use other port and start
OR
To release the port perform below steps


As a root or Goldengate owner check the port

[root@oracledev01~]#  netstat -nap | grep 7809
tcp        0      0 0.0.0.0:7809                0.0.0.0:*                   LISTEN      44402/./mgr

From the above output we see mgr is already running on port 7809 so kill this old process and start again

[root@oracledev01~]# kill -9 44402
[root@oracledev01~]# netstat -nap | grep 7809
Nothing displays

[oracle@sl73orcdbdbq005 GG11]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.14 17547423 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131022.0605_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 22 2013 11:03:39

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


GGSCI (oracledev01) 1> start mgr

Manager started.


GGSCI (oracledev01) 2> info mgr


Manager is running (IP port oracledev01.7809).


Friday, August 14, 2015

ENABLE automatic statistics collection in 11g.


SQL> SHO PARAMETER STATISTICS_LEVEL

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

SQL> select client_name, status,attributes,service_name from dba_autotask_client;

CLIENT_NAME                              STATUS          ATTRIBUTES                                                   SERVICE_NAME
---------------------------------------- -------- ------------------------------------------------------------ ----------------------------
auto optimizer stats collection          DISABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor                       ENABLED  ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor                       ENABLED  ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL


SQL> select client_name,status from DBA_AUTOTASK_TASK;

CLIENT_NAME                              STATUS
---------------------------------------- --------
sql tuning advisor                       ENABLED
auto space advisor                       ENABLED


SQL> SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS'),sql_tune_advisor, optimizer_stats, segment_advisor FROM DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME                    TO_CHAR(WINDOW_NEXT_TIME,'D   SQL_TUNE   OPTIMIZE   SEGMENT_
------------------------------ --------------------------- -------- -------- --------
MONDAY_WINDOW                  17-AUG-15 22:00:00          ENABLED  DISABLED ENABLED
TUESDAY_WINDOW                 18-AUG-15 22:00:00          ENABLED  DISABLED ENABLED
WEDNESDAY_WINDOW               19-AUG-15 22:00:00          ENABLED  DISABLED ENABLED
THURSDAY_WINDOW                13-AUG-15 22:00:00          ENABLED  DISABLED ENABLED
FRIDAY_WINDOW                  14-AUG-15 22:00:00          ENABLED  DISABLED ENABLED
SATURDAY_WINDOW                15-AUG-15 06:00:00          ENABLED  DISABLED ENABLED
SUNDAY_WINDOW                  16-AUG-15 06:00:00          ENABLED  DISABLED ENABLED

7 rows selected.

SQL>
SQL> SELECT  ENABLED FROM DBA_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME = 'GATHER_STATS_PROG';

ENABL
-----
TRUE

Check if this table has any pending stats

SQL> select LAST_ANALYZED,NUM_ROWS from dba_TAB_PENDING_STATS where TABLE_NAME='TEST_TAB';

no rows selected

Enable optimizer stats :

SQL>  BEGIN
     DBMS_AUTO_TASK_ADMIN.ENABLE(
     client_name => 'auto optimizer stats collection',
     operation => NULL,
     window_name => NULL);
     END;
     / 

PL/SQL procedure successfully completed.


SQL> SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS'),sql_tune_advisor, optimizer_stats, segment_advisor FROM DBA_AUTOTASK_WINDOW_CLIENTS;

WINDOW_NAME                TO_CHAR(WINDOW_NEXT_TIME,'D   SQL_TUNE   OPTIMIZE       SEGMENT_
------------------------------        ---------------------------         --------          --------         --------
MONDAY_WINDOW                17-AUG-15 22:00:00          ENABLED  ENABLED  ENABLED
TUESDAY_WINDOW               18-AUG-15 22:00:00           ENABLED  ENABLED  ENABLED
WEDNESDAY_WINDOW           19-AUG-15 22:00:00          ENABLED  ENABLED  ENABLED
THURSDAY_WINDOW                13-AUG-15 22:00:00          ENABLED  ENABLED  ENABLED
FRIDAY_WINDOW                   14-AUG-15 22:00:00          ENABLED  ENABLED  ENABLED
SATURDAY_WINDOW              15-AUG-15 06:00:00          ENABLED  ENABLED  ENABLED
SUNDAY_WINDOW                   16-AUG-15 06:00:00          ENABLED  ENABLED  ENABLED

7 rows selected.


Read more:



OGG-01224 Oracle GoldenGate Manager for Oracle, mgr.prm: Address already in use.



DB version: 11.2.0.4 2-Node RAC
OS:  RHEL 6
GG version :  11.2.1.0.3

GGSCI (oracledev01) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
JAGENT      STOPPED
EXTRACT     ABENDED     EOECBP      291:56:23     27:02:50

GGSCI (oracledev01) 2> start mgr

Manager started.

But when I do info mgr it says manager is DOWN…..

GGSCI (oracledev01) 3> info mgr

Manager is DOWN!


GGSCI (oracledev01) 4> view params mgr

PORT 7809
DYNAMICPORTLIST 7840-9860


GGSCI (oracledev01) 5> view report mgr


***********************************************************************
                 Oracle GoldenGate Manager for Oracle
 Version 11.2.1.0.14 17547423 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131022.0605
   Linux, x64, 64bit (optimized), Oracle 11g on Oct 22 2013 07:38:49

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


                    Starting at 2015-08-11 19:56:22
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Fri May 29 10:16:43 EDT 2015, Release 2.6.32-504.23.4.el6.x86_64
Node: sl73orcdbdbq005
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 36788

Parameters...

PORT 7809
DYNAMICPORTLIST 7840-9860


***********************************************************************
**                     Run Time Messages                             **
***********************************************************************


Source Context :
  SourceModule            : [mgr.main]
  SourceID                : [/scratch/aime1/adestore/views/aime1_adc4150267/oggcore/OpenSys/src/app/mgr/mgr.c]
  SourceFunction          : [init_functions]
  SourceLine              : [3390]
  ThreadBacktrace         : [8] elements
                          : [/gg/GG11/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7ff4595509fe]]
                          : [/gg/GG11/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x2cc) [0x7ff45954974c]]
                          : [/gg/GG11/libgglog.so(_MSG_ERR_TCP_GENERIC(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x31) [0x7ff4595318a5]]
                          : [./mgr(init_functions(int, char**)+0x7f5) [0x4511c5]]
                          : [./mgr(main_loop(int, char**)+0x4c) [0x454aec]]
                          : [./mgr(main+0xf2) [0x455362]]
                          : [/lib64/libc.so.6(__libc_start_main+0xfd) [0x3d9361ed5d]]
                          : [./mgr(__gxx_personality_v0+0x142) [0x43efca]]

2015-08-11 19:56:22  ERROR   OGG-01224  Address already in use.

2015-08-11 19:56:22  ERROR   OGG-01668  PROCESS ABENDING.


Error in ggserr.log:

2015-08-11 19:56:10  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info mgr.
2015-08-11 19:56:19  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): info all.
2015-08-11 19:56:22  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start mgr.
2015-08-11 19:56:22  ERROR   OGG-01224  Oracle GoldenGate Manager for Oracle, mgr.prm:  Address already in use.
2015-08-11 19:56:22  ERROR   OGG-01668  Oracle GoldenGate Manager for Oracle, mgr.prm:  PROCESS ABENDING.


CAUSE:

Previous mgr process is still running, which used port 7809 according to parameter file.


SOLUTION:

Use other port and start
OR
To release the port perform below steps


As a root or Goldengate owner check the port

[root@oracledev01~]#  netstat -nap | grep 7809
tcp        0      0 0.0.0.0:7809                0.0.0.0:*                   LISTEN      44402/./mgr

From the above output we see mgr is running on port 7809 so kill this old process and start again

[root@oracledev01~]# kill -9 44402
[root@oracledev01~]# netstat -nap | grep 7809

[oracle@sl73orcdbdbq005 GG11]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.14 17547423 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131022.0605_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Oct 22 2013 11:03:39

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


GGSCI (oracledev01) 1> start mgr

Manager started.


GGSCI (oracledev01) 2> info mgr

Manager is running (IP port oracledev01.7809).



Wednesday, August 5, 2015

ORA-04045: errors during recompilation/revalidation of GG_USER.DDLREPLICATION


DB version: 11.2.0.4
OS:  RHEL 6
GG version :  11.2.1.0.3

For any DDL operation performed in the database I’m hitting the below error


EX:
SQL> ALTER TABLE USER.TEST MODIFY (emp_id VARCHAR2(100) );
ALTER TABLE USER.TEST MODIFY (emp_id VARCHAR2(100) )
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04045: errors during recompilation/revalidation of GGS_ADMIN.DDLREPLICATION
ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGS_ADMIN.DDLREPLICATION"
ORA-06512: at line 1100
ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGS_ADMIN.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGS_ADMIN.DDLREPLICATION"
ORA-06512: at line 977
ORA-04045: errors during recompilation/revalidation of GGS_ADMIN.DDLREPLICATION
ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGS_ADMIN.DDLREPLICATION"
ORA-06512: at line 1100
ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGS_ADMIN.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGS_ADMIN.DDLREPLICATION"
ORA-04045: errors during recompilation/revalidation of GGS_ADMIN.DDLREPLICATION
ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGS_ADMIN.DDLREPLICATION"
ORA-06512: at line 1100
ORA-04067: not executed, package body "GGS_ADMIN.DDLREPLICATION" does not exist
ORA-06508: PL/SQL: could not find program unit being called:
"GGS_ADMIN.DDLREPLICATION"
ORA-06508: PL/SQL: could not find program unit being called:
"GGS_ADMIN.DDLREPLICATION"

And I see there are few invalid objects in the database in which I see goldengate package too

SQL> select count(*) from  dba_objects where status='INVALID';
COUNT(*)
----------
        35

SQL> select owner,object_name,object_type,status from dba_objects where object_name='DDLREPLICATION';

OWNER                                 OBJECT_NAME            OBJECT_TYPE                                               STATUS
--------------------------------- ----------------------    -----------------------------------------------         ----------------------
GGS_ADMIN                         DDLREPLICATION         PACKAGE BODY                                         INVALID
GGS_ADMIN                         DDLREPLICATION         PACKAGE                                                     VALID


Tried to run the @?/rdbms/admin/utlrp.sql” package but same error .

Solution:

DISABLE “GGS_DDL_TRIGGER_BEFORE” trigger and run the utlrp script to make objects validate and then enable back the DDL trigger

SQL> select trigger_name,status,action_type from dba_triggers where owner='SYS' and trigger_name='GGS_DDL_TRIGGER_BEFORE';

TRIGGER_NAME                      STATUS                   ACTION_TYPE
--------------------------------- ------------------------ ---------------------------------
GGS_DDL_TRIGGER_BEFORE            ENABLED                  PL/SQL

SQL> alter trigger sys.GGS_DDL_TRIGGER_BEFORE disable ;
Trigger altered.


SQL> select trigger_name,status,action_type from dba_triggers where owner='SYS' and trigger_name='GGS_DDL_TRIGGER_BEFORE';

TRIGGER_NAME                      STATUS                   ACTION_TYPE
--------------------------------- ------------------------ ---------------------------------
GGS_DDL_TRIGGER_BEFORE            DISABLED      PL/SQL



SQL> @?/rdbms/admin/utlrp.sql

SQL> select owner,object_name,object_type,status from dba_objects where object_name='DDLREPLICATION';

OWNER                             OBJECT_NAME            OBJECT_TYPE                                               STATUS
--------------------------------- ---------------------- ------------------------------------------------   -----------------------------
GGS_ADMIN                         DDLREPLICATION         PACKAGE BODY                                          VALID
GGS_ADMIN                         DDLREPLICATION         PACKAGE                                                   VALID


SQL> alter trigger sys.GGS_DDL_TRIGGER_BEFORE enable ;

Trigger altered.

SQL> select trigger_name,status,action_type from dba_triggers where owner='SYS' and trigger_name='GGS_DDL_TRIGGER_BEFORE';

TRIGGER_NAME                      STATUS                   ACTION_TYPE
--------------------------------- ------------------------ ---------------------------------
GGS_DDL_TRIGGER_BEFORE            ENABLED                  PL/SQL


Now I can do all my DDL operations. J
If you still see the same error then again disable the TRIGGER and reinstall the DDL replication package

NOTE:  same thing applies when we do any patching work or running any scripts (catupgrd,catproc,catuppst,utlrp,etc.,)


Refer:
  Do I Need To Disable The GoldenGate DDL Trigger Before An Oracle DB Upgrade or PSU patching? (Doc ID 971222.1)