4 Replies Latest reply: Apr 2, 2013 7:33 AM by 986917 RSS

    Not currently connected to Control Center Schema DBG-1032

    chillychin
      I think my brain is about fried

      I been trying to run the mapping debugger but keep hitting the following error

      program error debugger mapping.

      oracle.wh.service.sdk.mapping.debugger.WBMappingDebuggerException.

      DBG1032 Unable to connect to the location LOC.

      java.sql.SQLException.The Network Adapter could not estabilish the connection

      You are currently not connected to a Control Center schema. You must establish a connection to a control center schema before proceeding with the current debug session. If a connection is not established, the debug session will end

      Retry connection?

      I re-try, and test the conncetion successfully but still hit the same error.

      It seems I am hitting wall after wall of simple issues and I cant find an answer to them via google.

      Please any help would be appreciated

      I tried searching other threads but all I can find is "I solved it" without explaing why!
        • 1. Re: Not currently connected to Control Center Schema DBG-1032
          chillychin
          I hope this will help someone else in the future

          I went to Oracle support and got my issue resolved with them

          I had installed Oracle 11.2.0.3.0g Enterprise Edition on my local desktop along with OWB (Oracle Warehouse Builder)

          What was happening was that when I attempted to run the mapping "debugger" it would constantly say that it was unable to establish a connection. When I enter the credentials I needed and clicked "Test Connection" everything passes, but loops me back to an error message.

          After consulting with Oracle support apparently the issue had to do with my REPOSITORY BROWSER, LOCATION & CONTROL CENTER locations

          They all pointed to "localhost"

          I updated all the values to 127.0.0.1 (still localhost just using current IP)

          You need to update the "hostname" to anything BUT localhost

          I had to perform three steps

          (from note. 434272.1) to update any existence of host: localhost to the real hostname qualifier including domain name:
          - Update the Location Credentials stored in the Control Center
          - Update the Location Credentials stored in the Design Repository part of the Unified Repository
          - Update the Control Centers Credentials stored in the Design Repository part of the Unified Repository

          Cut and paste of support article as follows

          ------------------------------------------------------------------------------------------------------------------

          Update the Location Credentials stored in the Control Center

          Start the OWB Repository Browser Listener.

          For Warehouse Builder 10.2:

          For Windows : Start -> Programs -> Oracle OWB 10.2 -> Warehouse Builder -> Administration -> Start OWB Browser Listener
          For Linux : OWB_home/owb/bin/unix/startOwbbInst.sh
          For Warehouse Builder 11.x:

          See the steps in Note 469422.1 : How To Start The Warehouse Builder 11g Repository Browser Listener
          Start the Repository Browser.

          For Windows : Start -> Programs -> Oracle -> Warehouse Builder -> Repository Browser

          For Linux : OWB_home/owb/bin/unix/openRAB.sh

          Repository Browser can be started from the client for unsupported platform.
          Connect to the Repository Browser as the Repository Owner (10.2)/Workspace Owner (11.x).
          Select "Locations Report" under Reports - Deployments
          Select the Location you would like to update
          Check the box in the Select column in front of the Location Name
          Click on the link in the Validation column. (last column)
          In the "Connection Details" section of the Location Validation Report page, update the Host, Port and Service Name accordingly
          Click on the "Update Details" button
          Click on the "Test Location" button to check the location
          Repeat steps 5 to 9 for each location that requires changes
          Update the Control Center location "PlatformSchema" the same way as explained in steps 5 to 8
          The above will work for version till 10.2.0.4 and for version 11.1.0.x.
          For 10.2.0.5 and 11.2.0.x versions you will have to perform the update manually:

          Start SQL*Plus
          Connect as OWBSYS
          SQL> select CONNECT_SPEC from wb_rt_service_nodes;
          Check the returned values and update wb_rt_service_nodes
          SQL> update wb_rt_service_nodes set CONNECT_SPEC = 'HOST:PORT:SID';
          Note: If you need to update the location via a script then depending on the OWB version you are running the following note maybe of help: Note 1272726.1 - How To Update The Locations By Script.
          Warehouse Builder 11.2.0.3 provides a new tool to assist the failover process (update the locations).
          For more details please review Note 1362745.1 - How To Use The OWB 11.2.0.3 Java Tool oracle.wh.util.locationhelper.LocationTool for Failover.


          Update the Location Credentials stored in the Design Repository part of the Unified Repository

          Start SQL+
          Connect as Warehouse Builder Control Center Repository Owner
          For Warehouse Builder 11.x only, execute the following to set the Workspace:

          begin
          owbsys.wb_workspace_management.set_workspace('workspacename', 'workspaceowner');
          owbsys.start_enable_owb_roles;
          end;
          /
          Execute the script UpdateLocation.sql attached to this document for each location

          SQL>@UpdateLocation.sql LOCATION_NAME HOST PORT SERVICE_NAME


          The LOCATION_NAME needs to be in UPPERCASE otherwise the script will report location not found.


          Script UpdateLocation.sql is available here.


          A new version of UpdateLocation.sql which enable the update of SQL*Net location and File location is available in:

          Patch 12333081 for OWB 10.2.0.5
          Patch 10405383 for OWB 11.2.0.1
          Patch 12868049 for OWB 11.2.0.2 + Cumulative Patch 12874883 - OWB 11.2.0.2 MEGAPATCH V3 FOR GENERIC PLATFORM.
          Note: If you need to update the location via a script then depending on the OWB version you are running the following note maybe of help: Note 1272726.1 - How To Updade The Locations By Script.
          Warehouse Builder 11.2.0.3 provides a new tool to assist the failover process (update the locations).
          For more details please review Note 1362745.1 - How To Use The OWB 11.2.0.3 Java Tool oracle.wh.util.locationhelper.LocationTool for Failover.


          Update the Control Centers Credentials stored in the Design Repository part of the Unified Repository
          Start SQL+
          Connect as Warehouse Builder Control Center Repository Owner
          For Warehouse Builder 11.x only, execute the following to set the Workspace:

          begin
          owbsys.wb_workspace_management.set_workspace('workspacename', 'workspaceowner');
          owbsys.start_enable_owb_roles;
          end;
          /
          Execute the script UpdateControlCenter.sql attached to this document for each Control Center

          SQL>@UpdateControlCenter.sql CONTROL_CENTER_NAME HOST PORT SERVICE_NAME

          Script UpdateControlCenter.sql is available here.

          A new version of UpdateControlCenter.sql which enable the update of SQL*Net location is available in:

          Patch 12333081 for OWB 10.2.0.5
          Patch 10405383 for OWB 11.2.0.1
          Patch 12868049 for OWB 11.2.0.2 + Cumulative Patch 12874883 - OWB 11.2.0.2 MEGAPATCH V3 FOR GENERIC PLATFORM.

          ------------------------------------------------------------------------------------------------------------------

          Ensure that the UpdateControlCenter.sql & UpdateLocation.sql are both from the latest patch (Patch 12874883)

          Find below also the SQL scripts I just mentioned
          • 2. Re: Not currently connected to Control Center Schema DBG-1032
            chillychin
            UpdateControlCenter.Sql

            ---------------------------------------------------------------------------

            set serveroutput on;
            set verify off;
            prompt
            prompt This sql script updates the host:port:service, net service name parameters for a control center
            prompt in the OWB Client Repository. It must be run from user OWBSYS.
            prompt
            prompt The connection type must be host:port:service or SQL*Net Connection. The connection type cannot be changed
            prompt using this script.
            prompt
            prompt The default control center (DEFAULT_CONTROL_CENTER) cannot be changed
            prompt using this script.
            prompt
            prompt

            accept WORKSPACE_NAME CHAR PROMPT 'Enter Workspace Name: '
            accept USER_NAME CHAR PROMPT 'Enter Workspace User Name: '
            accept CONTROL_CENTER_NAME CHAR PROMPT 'Enter Control Center Name: '
            accept HOST CHAR PROMPT 'Host: '
            accept PORT CHAR PROMPT 'Port: '
            accept SERVICE CHAR PROMPT 'Service Name: '
            accept NETSERVICENAME CHAR PROMPT 'New Net Service Name: '

            declare

            v_elementid number(9);

            procedure updatestringprop (p_name varchar2, p_value varchar2) as

            begin
            update cmpstringpropertyvalue_v
            set value=p_value
            where logicalname = p_name
            and firstclassobject=v_elementid;
            end;


            procedure insertstringprop (p_name varchar2, p_value varchar2) as
            v_uoid varchar2(255);
            v_seq number(9);
            begin

            select sys_guid(), cwmseq.nextval into v_uoid, v_seq from dual;
            dbms_output.put_line( p_name || ' Not Found Inserting' );
            insert into cmpstringpropertyvalue_v(
            CLASSNAME,CREATEDBY,CREATIONTIMESTAMP, CUSTOMERDELETABLE,CUSTOMERRENAMABLE,
            EDITABLE,ELEMENTID,IMPORTED,
            LOGICALNAME,METADATASIGNATURE,NOTM,PERSISTENT,SEEDED,
            UOID,UPDATEDBY,UPDATETIMESTAMP,POSITION,PROPERTYOWNER,
            COMPLETED, DELETEINOVERRIDE, OVERRIDEATTRIBUTES, OVERRIDECHILDREN, OVERRIDEROLES,
            FIRSTCLASSOBJECT,VALUE)
            values ('CMPStringPropertyValue', USER, SYSDATE, '1', '0',
            '1', v_seq, '0',
            p_name, '9999999999', '0', '1', '0',
            v_uoid, USER, SYSDATE, '0', v_elementid,
            '0', '0', '0', '0', '0',
            v_elementid, p_value);

            end;

            procedure update_host_port_service as

            begin
            dbms_output.put_line('Updating CMPLocation_Host = ' || '&HOST');
            updatestringprop('CMPLocation_Host', '&HOST');
            if SQL%NOTFOUND THEN
            insertstringprop('CMPLocation_Host', '&HOST');
            END IF;

            dbms_output.put_line('Updating CMPLocation_Port = ' || '&PORT');
            updatestringprop('CMPLocation_Port', '&PORT');
            if SQL%NOTFOUND THEN
            insertstringprop('CMPLocation_Port', '&PORT');
            END IF;

            dbms_output.put_line('Updating CMPLocation_ServiceName = ' || '&SERVICE');
            updatestringprop('CMPLocation_ServiceName', '&SERVICE');
            if SQL%NOTFOUND THEN
            insertstringprop('CMPLocation_ServiceName', '&SERVICE');
            END IF;

            update CMPRuntimeLocation_v
            set host='&HOST' , port='&PORT'
            where name = '&CONTROL_CENTER_NAME';


            end;

            procedure update_sqlnet_loc_properties as
            v_netservice_name varchar2(500);
            begin
            if '&NETSERVICENAME' IS NOT NULL then
            dbms_output.put_line('Updating CMPLocation_NetServiceName = ' || '&NETSERVICENAME');
            updatestringprop('CMPLocation_NetServiceName', '&NETSERVICENAME');
            if SQL%NOTFOUND THEN
            insertstringprop('CMPLocation_NetServiceName', '&NETSERVICENAME');
            END IF;
            else
                 if '&HOST' IS NOT NULL AND '&PORT' IS NOT NULL AND '&SERVICE' IS NOT NULL then
                 v_netservice_name := '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ' || '&HOST'
                 || ')(PORT = ' || '&PORT'
                 || '))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ' || '&SERVICE' || ')))';
                 
                 dbms_output.put_line('Updating CMPLocation_NetServiceName = ' || v_netservice_name);
            updatestringprop('CMPLocation_NetServiceName', v_netservice_name);
            if SQL%NOTFOUND THEN
            insertstringprop('CMPLocation_NetServiceName', v_netservice_name);
            END IF;      
            END IF;
            END IF;
                 
            if '&NETSERVICENAME' IS NOT NULL then
            dbms_output.put_line('Updating CMPLocation_DataServerName = ' || '&NETSERVICENAME');
            updatestringprop('CMPLocation_DataServerName', '&NETSERVICENAME');
            if SQL%NOTFOUND THEN
            insertstringprop('CMPLocation_DataServerName', '&NETSERVICENAME');
            END IF;
            END IF;      

            end;


            function getworkspace return boolean as
            v_workspaceid number;
            begin
            select workspace_id into
            v_workspaceid from workspace_assignment
            where workspace_name = UPPER('&WORKSPACE_NAME') and user_name = UPPER('&USER_NAME');
            dbms_output.put_line('Select Workspace Id for workspace ' || '&WORKSPACE_NAME' || ' and user ' || '&USER_NAME');
            dbms_output.put_line('Workspace id = ' || v_workspaceid);

            owb_workspace_manager.set_workspace(v_workspaceid);
            return true;
            EXCEPTION
            WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Workspace id not found for workspace ' || '&WORKSPACE_NAME' || ' and user ' || '&USER_NAME');
            return false;
            WHEN OTHERS THEN
            dbms_output.put_line('Other Error');
            return false;
            end;


            function getruntimelocation return boolean as
            v_loctype varchar2(40);
            v_loctypeversion varchar2(40);
            begin
            select loctype, elementid into
            v_loctype, v_elementid from CMPRuntimeLocation_v where name = '&CONTROL_CENTER_NAME';
            dbms_output.put_line('Update location properties for ' || '&CONTROL_CENTER_NAME');
            dbms_output.put_line('Location Type = ' || v_loctype);
            return true;
            EXCEPTION
            WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Control Center ' || '&CONTROL_CENTER_NAME' || ' not found');
            return false;
            end;

            procedure updateruntimelocation as
            v_connectiontype varchar2(255);
            v_found boolean;
            v_workspace boolean;
            begin
            v_workspace := getworkspace;
            if v_workspace = true then
            v_found := getruntimelocation;
            if v_found = true then
            dbms_output.put_line('Control Center ' || '&CONTROL_CENTER_NAME' || ' Found');
            select value into v_connectiontype from cmpstringpropertyvalue_v where (logicalname = 'CMPLocation_ConnectionType' or logicalname = 'CMPLocation_J2EE_ConnectionType') and propertyowner = v_elementid;
            if v_connectiontype = 'HOST:PORT:SERVICE' then
            dbms_output.put_line('Connection Type = HOST:PORT:SERVICE Updating...');
            update_host_port_service;
            elsif v_connectiontype = 'SQL*NET Connection' then
            dbms_output.put_line('Connection Type = SQL*NET Connection Updating...');
            update_sqlnet_loc_properties;
            else
            dbms_output.put_line('Connection Type ' || v_connectiontype || ' cannot be updated');
            end if;
            end if;
            end if;

            EXCEPTION
            WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('Connection Type = Default HOST:PORT:SERVICE Updating...');
            update_host_port_service;
            end;

            begin
            updateruntimelocation;
            commit;
            end;
            /
            • 3. Re: Not currently connected to Control Center Schema DBG-1032
              chillychin
              UpdateLocation.sql

              -----------------------------------------------------------------------------------

              set serveroutput on;
              set verify off;
              prompt
              prompt This sql script sets the host:port:service, net service name and version parameters for a
              prompt location in the OWB Client Repository. It must be run from user OWBSYS.
              prompt
              prompt The connection type must be host:port:service or SQL*Net Connection and cannot be changed
              prompt using this script.
              prompt
              prompt The location may be registered or unregistered.
              prompt
              prompt Restart OWB client in order to see the new version in the UI.
              prompt

              accept WORKSPACE_NAME CHAR PROMPT 'Enter Workspace Name: '
              accept USER_NAME CHAR PROMPT 'Enter Workspace User Name: '
              accept LOCATION_NAME CHAR PROMPT 'Enter Location Name: '
              accept HOST CHAR PROMPT 'New Host: '
              accept PORT CHAR PROMPT 'New Port: '
              accept SERVICE CHAR PROMPT 'New Service Name: '
              accept NETSERVICENAME CHAR PROMPT 'New Net Service Name: '
              accept DBVERSION CHAR PROMPT 'New Version: '

              declare

              v_elementid number(9);

              procedure updatestringprop (p_name varchar2, p_value varchar2) as

              begin
              update cmpstringpropertyvalue_v
              set value=p_value
              where logicalname = p_name
              and firstclassobject=v_elementid;
              end;


              procedure insertstringprop (p_name varchar2, p_value varchar2) as
              v_uoid varchar2(255);
              v_seq number(9);
              begin

              select sys_guid(), cwmseq.nextval into v_uoid, v_seq from dual;
              dbms_output.put_line('Inserting' || p_name);
              insert into cmpstringpropertyvalue_v(
              CLASSNAME,CREATEDBY,CREATIONTIMESTAMP, CUSTOMERDELETABLE,CUSTOMERRENAMABLE,
              EDITABLE,ELEMENTID,IMPORTED,
              LOGICALNAME,METADATASIGNATURE,NOTM,PERSISTENT,SEEDED,
              UOID,UPDATEDBY,UPDATETIMESTAMP,POSITION,PROPERTYOWNER,
              COMPLETED, DELETEINOVERRIDE, OVERRIDEATTRIBUTES, OVERRIDECHILDREN, OVERRIDEROLES,
              FIRSTCLASSOBJECT,VALUE)
              values ('CMPStringPropertyValue', USER, SYSDATE, '1', '0',
              '1', v_seq, '0',
              p_name, '9999999999', '0', '1', '0',
              v_uoid, USER, SYSDATE, '0', v_elementid,
              '0', '0', '0', '0', '0',
              v_elementid, p_value);

              end;

              procedure update_properties as

              begin
              if '&HOST' IS NOT NULL then
              dbms_output.put_line('Updating CMPLocation_Host = ' || '&HOST');
              updatestringprop('CMPLocation_Host', '&HOST');
              if SQL%NOTFOUND THEN
              insertstringprop('CMPLocation_Host', '&HOST');
              END IF;
              END IF;

              if '&PORT' IS NOT NULL then
              dbms_output.put_line('Updating CMPLocation_Port = ' || '&PORT');
              updatestringprop('CMPLocation_Port', '&PORT');
              if SQL%NOTFOUND THEN
              insertstringprop('CMPLocation_Port', '&PORT');
              END IF;
              END IF;

              if '&SERVICE' IS NOT NULL then
              dbms_output.put_line('Updating CMPLocation_ServiceName = ' || '&SERVICE');
              updatestringprop('CMPLocation_ServiceName', '&SERVICE');
              if SQL%NOTFOUND THEN
              insertstringprop('CMPLocation_ServiceName', '&SERVICE');
              END IF;
              END IF;

              if '&DBVERSION' IS NOT NULL then
              dbms_output.put_line('Updating CMPLocation_Version = ' || '&DBVERSION');
              updatestringprop('CMPLocation_Version', '&DBVERSION');
              if SQL%NOTFOUND THEN
              insertstringprop('CMPLocation_Version', '&DBVERSION');
              END IF;
              END IF;

              end;

              procedure update_sqlnet_loc_properties as
              v_netservice_name varchar2(500);
              begin
              if '&NETSERVICENAME' IS NOT NULL then
              dbms_output.put_line('Updating CMPLocation_NetServiceName = ' || '&NETSERVICENAME');
              updatestringprop('CMPLocation_NetServiceName', '&NETSERVICENAME');
              if SQL%NOTFOUND THEN
              insertstringprop('CMPLocation_NetServiceName', '&NETSERVICENAME');
              END IF;
              else
              if '&HOST' IS NOT NULL AND '&PORT' IS NOT NULL AND '&SERVICE' IS NOT NULL then
              v_netservice_name := '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ' || '&HOST'
              || ')(PORT = ' || '&PORT'
              || '))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ' || '&SERVICE' || ')))';
                        
                   dbms_output.put_line('Updating CMPLocation_NetServiceName = ' || v_netservice_name);
                   updatestringprop('CMPLocation_NetServiceName', v_netservice_name);
                   if SQL%NOTFOUND THEN
                   insertstringprop('CMPLocation_NetServiceName', v_netservice_name);
              END IF;      
              END IF;
              END IF;
                   
              if '&NETSERVICENAME' IS NOT NULL then
              dbms_output.put_line('Updating CMPLocation_DataServerName = ' || '&NETSERVICENAME');
              updatestringprop('CMPLocation_DataServerName', '&NETSERVICENAME');
              if SQL%NOTFOUND THEN
              insertstringprop('CMPLocation_DataServerName', '&NETSERVICENAME');
              END IF;
              END IF;      
                   
              if '&DBVERSION' IS NOT NULL then
              dbms_output.put_line('Updating CMPLocation_Version = ' || '&DBVERSION');
              updatestringprop('CMPLocation_Version', '&DBVERSION');
              if SQL%NOTFOUND THEN
              insertstringprop('CMPLocation_Version', '&DBVERSION');
              END IF;
              END IF;

              end;

              function getworkspace return boolean as
              v_workspaceid number;
              begin
              select workspace_id into
              v_workspaceid from workspace_assignment
              where workspace_name = UPPER('&WORKSPACE_NAME') and user_name = UPPER('&USER_NAME');
              dbms_output.put_line('Select Workspace Id for workspace ' || '&WORKSPACE_NAME' || ' and user ' || '&USER_NAME');
              dbms_output.put_line('Workspace id = ' || v_workspaceid);

              owb_workspace_manager.set_workspace(v_workspaceid);
              return true;
              EXCEPTION
              WHEN NO_DATA_FOUND THEN
              dbms_output.put_line('Workspace id not found for workspace ' || '&WORKSPACE_NAME' || ' and user ' || '&USER_NAME');
              return false;
              WHEN OTHERS THEN
              dbms_output.put_line('Other Error');
              return false;
              end;

              function getlocation return boolean as
              v_loctype varchar2(40);
              v_loctypeversion varchar2(40);
              begin
              select loctype, elementid into
              v_loctype, v_elementid from CMPLogicalLocation_v where name = UPPER('&LOCATION_NAME');
              dbms_output.put_line('Update location properties for ' || '&LOCATION_NAME');
              dbms_output.put_line('Location Type = ' || v_loctype);
              return true;
              EXCEPTION
              WHEN NO_DATA_FOUND THEN
              dbms_output.put_line('Location ' || '&LOCATION_NAME' || ' not found');
              return false;
              end;

              procedure updatelocation as
              v_connectiontype varchar2(255);
              v_found boolean;
              v_workspace boolean;
              begin
              v_workspace := getworkspace;
              if v_workspace = true then
              v_found := getLocation;
              if v_found = true then
              dbms_output.put_line('Location ' || '&LOCATION_NAME' || ' Found');
              select value into v_connectiontype from cmpstringpropertyvalue_v where logicalname = 'CMPLocation_ConnectionType' and propertyowner = v_elementid;
              if v_connectiontype = 'HOST:PORT:SERVICE' then
              dbms_output.put_line('Connection Type = HOST:PORT:SERVICE Updating...');
              update_properties;
              elsif v_connectiontype = 'SQL*NET Connection' then
              dbms_output.put_line('Connection Type = SQL*NET Connection Updating...');
              update_sqlnet_loc_properties;
              else
              dbms_output.put_line('Connection Type ' || v_connectiontype || ' cannot be updated');
              end if;
              end if;
              end if;

              EXCEPTION
              WHEN NO_DATA_FOUND THEN
              dbms_output.put_line('Connection Type = Default HOST:PORT:SERVICE Updating...');
              update_properties;
              end;

              begin
              updatelocation;
              commit;
              end;
              /
              • 4. Re: Not currently connected to Control Center Schema DBG-1032
                986917
                Changing localhost to 127.0.0.1 worked on me thank you so much :)