Pages

Tuesday 8 January 2013

Golden Gate extract process fails to start with error OGG-00529


Golden Gate extract process fails to start with error as below

GGSCI (slc01jbc) 3> start lsrcet

Sending START request to MANAGER ...
EXTRACT LSRCET starting


GGSCI (slc01jbc) 4> start extract e2_dpmp

Sending START request to MANAGER ...
EXTRACT E2_DPMP starting


GGSCI (slc01jbc) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     E2_DPMP     00:00:00      00:50:26
EXTRACT     STOPPED     LSRCET      00:00:00      00:57:01


error in the log is

2013-01-08 06:55:51  INFO    OGG-01815  Oracle GoldenGate Capture for Oracle, lsrcet.prm:  Virtual Memory Facilities for: COM
    anon alloc: mmap(MAP_ANON)  anon free: munmap
    file alloc: mmap(MAP_SHARED)  file free: munmap
    target directories:
    /orasw/GG/dirtmp.
2013-01-08 06:55:51  ERROR   OGG-00529  Oracle GoldenGate Capture for Oracle, lsrcet.prm:  DDL Replication is enabled but table ggs_owner.GGS_DDL_HIST is not found. Please check DDL installation in the database.
2013-01-08 06:55:51  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, lsrcet.prm:  PROCESS ABENDING.
2013-01-08 06:56:11  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (oracle): start extract e2_dpmp.
2013-01-08 06:56:11  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host slc01jbc.us.oracle.com (START EXTRACT E2_DPMP ).
2013-01-08 06:56:11  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT E2_DPMP starting.



Solution:

DDL support scripts should be run in source database:

#> sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jan 8 06:07:40 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:ggs_owner
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to GG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL> @ddl_setup.sql
GoldenGate DDL Replication setup script
Verifying that current user has privileges to install DDL Replication...
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:ggs_owner
You will be prompted for the mode of installation.
To install or reinstall DDL replication, enter INITIALSETUP
To upgrade DDL replication, enter NORMAL
Enter mode of installation:INITIALSETUP
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...
Check complete.
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the role GGS_GGSUSER_ROLE
To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)
You will be prompted for the name of a schema for the GoldenGate database objects.
NOTE: The schema must be created prior to running this script.
NOTE: Stop all DDL replication before starting this installation.
Enter GoldenGate schema name:ggs_owner
Wrote file role_setup_set.txt
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO
where is the user assigned to the GoldenGate processes.
SQL> GRANT GGS_GGSUSER_ROLE TO ggs_owner;
Grant succeeded.
SQL> @ddl_enable
Trigger altered.

Now Extract process can be started.

GGSCI (slc01jbc) 3> start extract LSRCET

Sending START request to MANAGER ...
EXTRACT LSRCET starting


GGSCI (slc01jbc) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     STOPPED     E2_DPMP     00:00:00      01:24:26
EXTRACT     RUNNING     LSRCET      01:30:59      00:00:02



Thursday 3 January 2013

ORA-12519: TNS:no appropriate service handler found while connecting to database.




New database connection attempts to Oracle database fail with following Oracle error





Reason:

One of the most common reasons for TNS-12519 Oracle erroris the configured maximum number of PROCESSES and/or SESSIONS limitation being reached. When this occurs, the service handlers for the TNS Listener become “Blocked” and no new connections can be made. Once the TNS Listener receives an update from the PMON process associated with the database instance telling that thresholds are below the configured limit, the database listener will start to accept new connections

Solution:


Check if a database instance reaches the maximum number of processes using below command.

select * from v$resource_limit where resource_name = 'processes';

eg:

SQL> col LIMIT_VALUE format a20;
col RESOURCE_NAME format a20;
col INITIAL_ALLOCATION format a15;
select * from v$resource_limit where resource_name = 'processes';SQL> SQL> SQL>

RESOURCE_NAME        CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCAT LIMIT_VALUE
-------------------- ------------------- --------------- --------------- --------------------
processes                            299             300        300             300

1 row selected.


If this is the case, to fix this Oracle error, increase the PROCESSES parameter value to allow the Oracle database Listener to spawn more server process as required in your environment or Kill some inactive and unwanted sessions using alter system kill session ‘SID, SERAL#’;