Source Extractors - SQL PLSQL - Information - Steps to follow when the instance name is not correctly modified and analysis is done

Impact of the Problem

To modify an instance name of a PL/SQL extraction, you must use the Database Renaming tool as described in the documentation : DMT - Oracle > Dealing with Oracle Schemas that move from one Server to another or from one Instance to another

If you have modified the instance name manually, run the analysis and faced issues related to the instance name in the dashboard, modules or Enlighten, you have to follow the steps described in this pages.

Observed in CAST AIP
Observed on RDBMS
Oracle Server (tick)
Microsoft SQL Server (tick)
CSS2 (tick)
Step by Step Scenario

Below is the step-by-step scenario leading to the problem:

  1. Deliver code.
  2. Change the database Instance.
  3. Perform and analysis and snapshot.
  4. Snapshot fails at the Generate Module step with error or Enlighten does not show the instance.
Action Plan
  1. The problem is due to an issue with using another database instance to extract files and the new extraction files were partially modified to remedy the situation and avoid added/deleted objects.
    To fix the problem, proceed as follows:

    1. Verify that all extracted files have the proper instance name in the deployment directory which is defined in CAST-MS.
      Especially verify that these are the same values.

      File DatabaseExtraction.uaxdirectory : line:  <UAXFile path="p_Instance.1.uax" name="<instance name>" type="CAST_Oracle_Instance">
      File ce.1.uax : line:  <CAST_Resolution_Identification resolutionName="<instance name>"/>
      File p_Schema.1.uax : line:  <CAST_Linker_ExportedSymbol isPublic="1" exportCollation="2" exportedCategory="138013" exportedPath="<instance name>" exportName="<export name>" exportExtension=""/>
    2. Remove the references to the incorrect project from the database.

  2. First, determine the associated projects in the KB:

    select * from <local database>.keys where objtyp = 138000;

    which gives results like (normally the lower project number is the one to keep)

    Query result example

    1301003;'DB_Oracle_20100';'';'XXXXXX';-1;36010;0;138000;'???';'1970-01-01 00:00:00';'';0;''

    1373451;'DB_Oracle_24976';'';'XXXXXX';-1;36010;0;138000;'???';'1970-01-01 00:00:00';'';0;''

  3. Then get the container id from this query:

    select ccre.Container_Id, *
      from CMS_ORA_Analysis coa
      left join CMS_Oracle_Project cop
        on cop.Object_Id = coa.Project_Id
      left join CMS_Code_Repo_OracleExtract ccre
        on ccre.Object_Id = coa.Resource_Id
      left join CMS_Code_Cont_DBExtract cccd
        on cccd.Object_Id = ccre.Container_Id

     which gives results like (35015 is the container_id in the first column in this case): 

    Query result example
    35015;19140;'2016-10-24 11:46:41';'<extraction name>';13539;19106;'dmtid:91436491-8bf2-4cc7-b21e-776c19501bdd:<extraction name>';'<extraction name>_435cba19';'';1;35016;'';;;;'ExecutionSuccessStatus';'2016-10-24 10:43:05';'246d3ff5d1d87748026c1d163a2bdd37';'0';'dmtid:7e6444e5-f21a-4185-b505-4c81c5ddc623';'<Log directory>\DB_Oracle_24976-20161024103938.castlog';'';'';35016;'2016-10-19 13:59:22';'<extraction name>';'<extraction name>';19106;0;0;0;19106;'2016-10-19 13:59:22';'DB_Oracle';13390;35015;'<deployment folder>';1;0;35015;'2016-10-19 13:59:22';'DB_Oracle';'<deployment folder>\DatabaseExtraction.uaxdirectory' 
  4. Now having that information, you can clean up the KB by first deleting the project that should not have been added:

    set search_path=<local database>;
    select project_delete(1373451);
  5. Then update the KB with the proper links (using the container ID and the project name) 

    set search_path=<local database>;
    update keys
      set keynam = 'DB_Oracle_35015'
    where idkey = 1301003;
    update objects 
    set idnam = 'DB_Oracle_35015' 
    where idkey = 1301003;
    update objects
    set idshortnam = 'DB_Oracle_35015' 
    where idkey = 1301003;
  6.  Finally re-launch the snapshot without skipping the analysis.


Ticket # 6950

Related Pages