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:

Automatic Statistics Collection (Doc ID 1233203.1)

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).

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)