1 Reply Latest reply: Dec 23, 2013 4:29 AM by GG Lover RSS

    Unable to register one extract,but the other twos are ok.

    user1089277

      Enviroment:

      OS:Windows Server 2003 64bit

      GG:

      Oracle GoldenGate Command Interpreter for Oracle

      Version 11.2.1.0.6 16211226 OGGCORE_11.2.1.0.6_PLATFORMS_130418.1829

      Windows x64 (optimized), Oracle 11g on Apr 19 2013 17:38:40

       

      There are three extract on the same server.Now I'd like to register for the three extracts.The other twos are ok,but there is a one extract,meet the below error message:

      2013-12-22 21:43:50  ERROR   OGG-01755  Cannot register or unregister EXTRACT E_

      ZF_K because of the following SQL error: OCI Error ORA-00001: unique constraint

      (SYSTEM.LOGMNR_SESSION_UK1) violated

      ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 453

      ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 289

      ORA-06512: at line 1 (status = 1). See Extract user privileges in the Oracle Gol

      denGate for Oracle Installation and Setup Guide.

       

      What should I do?And I also want to know the detailed cause of this error.Thanks in acvance!

        • 1. Re: Unable to register one extract,but the other twos are ok.
          GG Lover

          Hi,

           

          Ongoing DDL operations on the Oracle database do not allow the PL/SQL PackageDBMS_CAPTURE_ADM.BUILD to build the LogMiner Data Dictionary, as part of the GGSCI REGISTER EXTRACT command.
          NOTE: The DBMS_CAPTURE_ADM.BUILD procedure is the same as the DBMS_LOGMNR_D.BUILD procedure.

           

          exec dbms_goldengate_auth.grant_admin_privilege('USER_NAME');

           

          1. Connect to the Oracle database as SYSDBA 

            # sqlplus /nologin
            SQL> connect / as sysdba

          2. In the SYSDBA session, determine that there are no EXCLUSIVE DDL sessions.

            (Summary) 

            SQL> select mode_held, count(*) from dba_ddl_locks group by mode_held;


            (In detail) 

            SQL> select mode_held, name, type from dba_ddl_locks where mode_held = 'Exclusive' order by mode_held;


            (To identify a specific user process) 

            select l.name, l.type, l.mode_held, s.sid, s.program, s.username, p.spid, p.pidfrom dba_ddl_locks l, v$session s, v$process p where l.mode_held = 'Exclusive' and l.session_id = s.sid and s.paddr = p.addr;

          3. When there are no EXCLUSIVE mode DDL locks, re-run the GGSCI REGISTER EXTRACT command.

          In case this is not working then try for following steps,

           

          1. turn on sql trace

          2. identify SQL and its bind variables:

          declare
          extract_name varchar2(100) := :1;
          source_global_name varchar2(4000) := :2;
          firstScn number := :3;
          outbound_server_name varchar2(30);
          outbound_capture_name varchar2(30);
          capture_queue_name varchar2(30);
          queue_table_name varchar2(30);
          outbound_comment varchar2(125);
          BEGIN
          dbms_xstream_gg_adm.wait_for_inflight_txns := 'n';
          dbms_xstream_gg_adm.synchronization := 'none';
          dbms_xstream_gg_adm.is_goldengate := true; /* Construct the queue table name */
          queue_table_name := SUBSTR('OGG$Q_TAB_' || extract_name, 1, 30); /* Construct the capture queue name */
          capture_queue_name := SUBSTR('OGG$Q_' || extract_name, 1, 30); /* create capture queue */
          dbms_streams_adm.set_up_queue(queue_table => queue_table_name, queue_name => capture_queue_name); /* Construct the outbound capture name */
          outbound_capture_name := SUBSTR('OGG$CAP_' || extract_name, 1, 30); /* create capture specifying the first scn */ DBMS_XSTREAM_GG.SET_GG_SESSION();
          dbms_capture_adm.create_capture(queue_name => capture_queue_name,
          capture_name => outbound_capture_name,
          first_scn => firstScn,
          source_database => source_global_name); /* Construct the outbound server name */
          outbound_server_name := SUBSTR('OGG$' || extract_name, 1, 30); /* Construct the comment assosciated with this outbound server */ outbound_comment := extract_name || ' GoldenGate Extract';
          DBMS_XSTREAM_GG_ADM.ADD_OUTBOUND(server_name => outbound_server_name,
          capture_name=> outbound_capture_name,
          source_database=> source_global_name,
          committed_data_only => FALSE,
          wait_for_inflight_txns => 'N',
          synchronization => 'NONE',
          start_scn => firstScn,
          comment => outbound_comment);
          DBMS_XSTREAM_GG.SET_GG_SESSION(FALSE);
          END;

          3. run the sql manually under sqlplus.
          it hits:

          ERROR at line 1:
          ORA-00604: error occurred at recursive SQL level 1
          ORA-01502: index 'SYS.I_WRI$_OPTSTAT_TAB_OBJ#_ST' or partition of such index is
          in unusable state
          ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 746
          ORA-06512: at line 16

          4.
          SQL> select owner,status from dba_indexes where index_name='I_WRI$_OPTSTAT_TAB_OBJ#_ST';
          OWNER STATUS
          ------------------------------ --------
          SYS UNUSABLE

          5.rebuild th index, but it did not help:
          alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild;

          6. drop and recreate the index. Then the extract can be registered

           

           

          Thanks,

          GG Lover