ODI 12c - Upgrade Procedure ODI 12.2.1.2.6 To 12.2.1.4

Di seguito gli step necessari all'Upgrade del software ODI dalla versione 12.2.1.2.6 alla versione 12.2.1.4.0

STEP	NOTA
Creazione clone macchina DB
Riconfigurazione /etc/hots	1.      Configurazione del file /etc/hosts
Riconfigurazione network DB	1.      Modifica configurazione listener.ora con nuovo <hostname> 2.      Modifica configurazione tnsnames.ora con nuovo <hostname> 3.      Avvio listener
Startup DB	1.      Avvio istanza di database METAINTA
Creazione clone macchina ODI
Riconfigurazione /etc/hots	1)Configurazione del file /etc/hosts
Eseguire riconfigurazione Repository ODI - Connettersi al Master Repository   SUPERVISOR/Welcome1$	1.      Modifica URL ODI STUDIO Connection Login INFOBOARD_SVIL con nuovo IP 2.      modificare i puntamenti del workrepository sulla macchina del DB clonato 1.      (Modifica Topology\Repositories\Work Repositories\WORKREP\JDBC\JDBC URL con nuovo IP)  3.      modificare i puntamenti dell'agent alla nuova macchina ODI clonata 4.      disabilitare tutte le schedulazione dell'agent sulla tabella SNP_PLAN_AGENT 1.      Backup tabella SNP_PLAN_AGENT 2) UPDATE a D : UPDATE SNP_PLAN_AGENT SET  STAT_PLAN        = 'D';
Riconfigurazione Dominio WebLogic	Modificare nel file config.xml  il nome del vecchio Hostname col nuovo clonato Modificare nel file nodemanager.properties il nome del vecchio hostname con quello clonato Eseguire config.sh sostituendo il nome del vecchio hostname db con quello clonato ed il nome del vecchio hostname odi con quello clonato. Al termine delle operazioni di riconfigurazione deve apparire quanto segue:      Admin Server URL:         http://<hostname_odi_clone>:7001
Start weblogic	cd <ODIDOM>/bin/ nohup ./startNodeManager.sh >nmout.log  & nohup ./startWebLogic.sh >out.log  &
Connettersi alla console di Weblogic e test agent ODI	Collegarsi alla console : 1.      https://<hostname_odi_clone>:7001/console 0.      Verificare che l'Admin sia RUNNING 1.      Start Managed Server di ODI
Shutdown di tutti i processi Weblogic	1)Stop di tutti i processi weblogic chiudendoli dalla console: ·        https://<hostname_odi_clone>:7001/console 2)  cd <ODIDOM>/bin ·        ./stopNodeManager.sh 3)Verifica dell'assenza di processi java attivi e riferibili a weblogic: ·        ps -ef | grep java
Installazione software ODI 12.2.1.4.0	JDK FMW 12.2.1.4.0 ODI 12.2.1.4.0
Verifica se il Repository può essere upgradato e stop di tutti i processi	Start the Upgrade Assistant. (UNIX) ./ua -readiness
Verifica degli schema presenti	SET LINE 120 COLUMN MRC_NAME FORMAT A14 COLUMN COMP_ID FORMAT A20 COLUMN VERSION FORMAT A12 COLUMN STATUS FORMAT A9 COLUMN UPGRADED FORMAT A8 SELECT MRC_NAME, COMP_ID, OWNER, VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY ORDER BY MRC_NAME, COMP_ID ;
Inizio Upgrade ODI alla 12.2.1.4.0	Settare i path della versione ODI 12.2.1.4.0 quando ci si collega alla macchina CLONE nel .bash_profile
Upgrading Product Schemas Using the Upgrade Assistant	Start the Upgrade Assistant. (UNIX) ./ua   select Individually Selected Schemas.   Save Response File
Verifica degli schema presenti upgradati	SET LINE 120 COLUMN MRC_NAME FORMAT A14 COLUMN COMP_ID FORMAT A20 COLUMN VERSION FORMAT A12 COLUMN STATUS FORMAT A9 COLUMN UPGRADED FORMAT A8 SELECT MRC_NAME, COMP_ID, OWNER, VERSION, STATUS, UPGRADED FROM SCHEMA_VERSION_REGISTRY ORDER BY MRC_NAME, COMP_ID ;   OPPURE   SELECT MRC_NAME, COMP_ID, OWNER, VERSION, STATUS, UPGRADED       FROM SCHEMA_VERSION_REGISTRY ORDER BY UPGRADED desc ;
Backing Up the Domain	Domain Location: <ODIDOM>   (UNIX) cp -rf mydomain mydomain_backup cp -rf <ODIDOM> <ODIDOM_20210630>
Reconfiguration Wizard Domain	Start Reconfig Assistant (UNIX) ./reconfig.sh  -log=<inserire path e file di log> -log_priority=ALL   where -log=log_file is the absolute path of the log file you'd like to create for the domain reconfiguration session. This can be helpful if you need to troubleshoot the reconfiguration process. The parameter -log_priority=ALL ensures that logs are logged in fine mode.   The Reconfiguration Progress screen displays the progress of the reconfiguration process. During this process: • Domain information is extracted, saved, and updated. • Schemas, scripts, and other such files that support your Fusion Middleware products are updated. When the progress bar shows 100%, click Next.
Upgrading Domain Component Configurations	Start the Upgrade Assistant. (UNIX) ./ua   Select All Configurations Used By a Domain. The screen name changes to WebLogic Components.
Starting Servers and Processes	Domain Location:         <ODIDOM>   Step 1: Start the Administration Server: (UNIX) NEW_DOMAIN_HOME/bin/startWebLogic.sh Step 2: Start Node Manager (UNIX) NEW_DOMAIN_HOME/bin/startNodeManager.sh Step 3: Start eventuali ORACLE Components (UNIX) NEW_DOMAIN_HOME/bin/startComponent.sh component_name Step 4: Start the Managed Servers - se necessario (UNIX)NEW_DOMAIN_HOME/bin/startManagedWebLogic.sh managed_server_name admin_url   Eseguire step 1 e 2 e poi collegarsi alla console per verifica servizi, processi e start managed server ODI:   https://<hostname_odi_clone>:7001/console http://titappocm11lits:7001/console/login/LoginForm.jsp   LOG: /u01/data/domains/ODI12INF/INF-ODIDOM_SVIL/servers/ODI_INF_SVIL_1/logs/ODI_INF_SVIL_1.log /u01/data/domains/ODI12INF/INF-ODIDOM_SVIL/servers/ODI_INF_SVIL_1/logs/oracledi/odiagent.log
	CHECK domain with line command:   cd /u01/app/oracle/middleware/ODI12214INF/oracle_common/common/bin [oracle@titappocm11lits bin]$ ./wlst.sh   Initializing WebLogic Scripting Tool (WLST) ...   Welcome to WebLogic Server Administration Scripting Shell   Type help() for help on available commands   wls:/offline> readDomain('/u01/data/domains/ODI12INF/INF-ODIDOM_SVIL') wls:/offline/INF-ODIDOM_SVIL>ls () drw-   AppDeployment drw-   CoherenceClusterSystemResource drw-   Credential drw-   EmbeddedLDAP drw-   JDBCSystemResource drw-   Keystore drw-   Library drw-   Machine drw-   NMProperties drw-   Security drw-   SecurityConfiguration drw-   Server drw-   ServerTemplate drw-   ShutdownClass drw-   StartupClass drw-   StartupGroupConfig drw-   WLDFSystemResource   -rw-   Active                                        false -rw-   AdminServerName                               AdminODI_INF_SVIL -rw-   AdministrationMBeanAuditingEnabled            false -rw-   AdministrationPort                            9002 -rw-   AdministrationPortEnabled                     false -rw-   AdministrationProtocol                        t3s -rw-   ArchiveConfigurationCount                     0 -rw-   AutoDeployForSubmodulesEnabled                true -rw-   BatchJobsDataSourceJndiName                   null -rw-   BatchJobsExecutorServiceName                  null -rw-   ClusterConstraintsEnabled                     false -rw-   ConfigBackupEnabled                           false -rw-   ConfigurationAuditType                        null -rw-   ConfigurationVersion                          12.2.1.4.0 -rw-   ConsoleContextPath                            console -rw-   ConsoleEnabled                                true -rw-   ConsoleExtensionDirectory                     console-ext -rw-   DbPassiveMode                                 false -rw-   DbPassiveModeGracePeriodSeconds               30 -rw-   DiagnosticContextCompatibilityModeEnabled     true -rw-   DomainVersion                                 12.2.1.4.0 -rw-   EnableEeCompliantClassloadingForEmbeddedAdaptersfalse -rw-   ExalogicOptimizationsEnabled                  false -rw-   Id                                            0 -rw-   InternalAppsDeployOnDemandEnabled             true -rw-   JavaServiceConsoleEnabled                     false -rw-   JavaServiceEnabled                            false -rw-   LastModificationTime                          0 -rw-   LogFormatCompatibilityEnabled                 false -rw-   MaxConcurrentLongRunningRequests              50 -rw-   MaxConcurrentNewThreads                       50 -rw-   MsgIdPrefixCompatibilityEnabled               true -rw-   Name                                          INF-ODIDOM_SVIL -rw-   Notes                                         null -rw-   OCMEnabled                                    true -rw-   ParallelDeployApplicationModules              false -rw-   ParallelDeployApplications                    false -rw-   PartitionUriSpace                             /partitions -rw-   ProductionModeEnabled                         false -rw-   RootDirectory                                 null -rw-   ServerMigrationHistorySize                    100 -rw-   ServiceMigrationHistorySize                   100 -rw-   SiteName                                      null -rw-   Tag
CHECK UPGRADE	CHECK con connessione ODI tramite ODISTUDIO versione 12.2.1.4.0 1.      Check OdiAgent

ODI 12c - Procedure Purge Log 2

Di seguito la Purge Manuale dei LOG di ODI, da utilizzare solo dopo aver ricevuto l'OK dal supporto Oracle. Tale procedura se utilizzata senza consenso del supporto potrebbe portare alla cessazione della garanzia del prodotto in quanto prevede la cancellazione di tabelle del repository ODI, la cui manomissione comporta la cessazione della garanzia.

• STEP 1

Estrazione di tutte le tabelle del repository su cui effettuare una count prima della Purge dei dati, connettersi allo schema proprietario del repository ed effettuare la seguente query per estrarsi uno script di count su tutte le tabelle.:

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

COUNT ALL TABLE REPOSITORY 

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

select  

'select '''||table_name||''' as TABLE_NAME, count(*) as REC_NUM from '||table_name||' UNION '

from user_tables

order by table_name;

• STEP 2

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

----  VERIFICARE IL CONTENUTO DELLA TABELLA SNP_VAR_SESS -----------

----  SE DIFFERENTE DA 0 OCCORRE EFFETTUARE UN DELETE SULLA --------

----  TABELLA SNP_EXP_TXT_HEADER -----------------------------------

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

---------------- CLEANING SNP_EXP_TXT_HEADER -----------------------

    

    ALTER TABLE SNP_EXP_TXT_HEADER NOLOGGING;

   

    DELETE FROM SNP_EXP_TXT_HEADER 

        WHERE I_TXT IN ( SELECT I_TXT_VAR FROM SNP_VAR_SESS );

    

    ALTER TABLE SNP_EXP_TXT_HEADER LOGGING;

    

----------  DA FARE PRIMA DELLA TRUNCATE TABLE SNP_VAR_SESS ---------

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

• STEP 3

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

--- BACKUP TABELLE SNP_SESSION --- SNP_SESSION_REPORT ---- SNP_PLAN_AGENT 

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

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

- Purge ODI Logs (Procedura manuale con truncate table)

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

-- A,C

alter table SNP_PARAM_SESS disable constraint FK_PARAM_SESS;

alter table SNP_SESS_STEP disable constraint FK_SESS_STEP;

alter table SNP_SESS_TASK disable constraint FK_SESS_TASK3;

alter table SNP_VAR_SESS disable constraint FK_VAR_SESS;

alter table SNP_SCHEDULE_EXEC disable constraint FK_CURRENT_SESSION;

alter table SNP_SCHEDULE_EXEC disable constraint FK_SCHEDULE_ID;

alter table SNP_SCHEDULE_EXEC disable constraint FK_CURRENT_LP_RUN;

alter table SNP_SESS_TASK_LS disable constraint FK_SESS_TASK_LS;

alter table SNP_SESS_STEP_LV disable constraint FK_SESS_STEP_LV;

alter table SNP_SESS_TASK_LOG disable constraint FK_SESS_TASK_LOG;

alter table SNP_STEP_LOG disable constraint FK_STEP_LOG;

alter table SNP_SESSION disable constraint FK_SNP_SESSION_SB;

truncate table SNP_PARAM_SESS;

truncate table SNP_SESS_STEP;

truncate table SNP_SESS_TASK;

truncate table SNP_VAR_SESS;

truncate table SNP_SCHEDULE_EXEC;

truncate table SNP_SESS_TASK_LS;

truncate table SNP_SESS_STEP_LV;

truncate table SNP_SESS_TASK_LOG;

truncate table SNP_STEP_LOG;

truncate table SNP_SESSION;

alter table SNP_PARAM_SESS enable constraint FK_PARAM_SESS;

alter table SNP_SESS_STEP enable constraint FK_SESS_STEP;

alter table SNP_SESS_TASK enable constraint FK_SESS_TASK3;

alter table SNP_VAR_SESS enable constraint FK_VAR_SESS;

alter table SNP_SCHEDULE_EXEC enable constraint FK_CURRENT_SESSION;

alter table SNP_SCHEDULE_EXEC enable constraint FK_SCHEDULE_ID;

alter table SNP_SCHEDULE_EXEC enable constraint FK_CURRENT_LP_RUN;

alter table SNP_SESS_STEP_LV enable constraint FK_SESS_STEP_LV;

alter table SNP_SESS_TASK_LOG enable constraint FK_SESS_TASK_LOG;

alter table SNP_SESS_TASK_LS enable constraint FK_SESS_TASK_LS;

alter table SNP_STEP_LOG enable constraint FK_STEP_LOG;

alter table SNP_SESSION enable constraint FK_SNP_SESSION_SB;

• STEP 4 

alter table SNP_STEP_REPORT disable constraint FK_STEP_REPORT;

alter table SNP_SCEN_REPORT disable constraint FK_SCEN_REPORT;

truncate table SNP_STEP_REPORT;

truncate table SNP_SCEN_REPORT;

alter table SNP_STEP_REPORT enable constraint FK_STEP_REPORT;

alter table SNP_SCEN_REPORT enable constraint FK_SCEN_REPORT;

• STEP5

alter table SNP_LPI_EC_VAR_LOG disable constraint FK_LPI_EVLOG_E

alter table SNP_LPI_EC_VAR_LOG disable constraint FK_LPI_EVLOG_S

alter table SNP_LPI_EXC_LOG disable constraint FK_LPI_ELOG_SLOG;

alter table SNP_LPI_EXC_LOG disable constraint FK_LPI_ELOG_STEP;

alter table SNP_LPI_VAR_LOG disable constraint FK_LPI_VLOG_SLOG;

alter table SNP_LPI_VAR_LOG disable constraint FK_LPI_VLOG_SVAR;

alter table SNP_LPI_STEP_LOG disable constraint FK_LPI_SLOG_RUN;

alter table SNP_LPI_STEP_LOG disable constraint FK_LPI_SLOG_STEP

truncate table SNP_LPI_EC_VAR_LOG;

truncate table SNP_LPI_EXC_LOG;

truncate table SNP_LPI_VAR_LOG;

truncate table SNP_LPI_STEP_LOG;

alter table SNP_LPI_EC_VAR_LOG enable constraint FK_LPI_EVLOG_EL

alter table SNP_LPI_EC_VAR_LOG enable constraint FK_LPI_EVLOG_SV

alter table SNP_LPI_EXC_LOG enable constraint FK_LPI_ELOG_SLOG;

alter table SNP_LPI_EXC_LOG enable constraint FK_LPI_ELOG_STEP;

alter table SNP_LPI_VAR_LOG enable constraint FK_LPI_VLOG_SLOG;

alter table SNP_LPI_VAR_LOG enable constraint FK_LPI_VLOG_SVAR;

alter table SNP_LPI_STEP_LOG enable constraint FK_LPI_SLOG_RUN;

alter table SNP_LPI_STEP_LOG enable constraint FK_LPI_SLOG_STEP;

• STEP 6 

alter table SNP_PARAM_SESS disable constraint FK_PARAM_SESS;

alter table SNP_SESS_STEP disable constraint FK_SESS_STEP;

alter table SNP_SESS_TASK disable constraint FK_SESS_TASK3;

alter table SNP_VAR_SESS disable constraint FK_VAR_SESS;

alter table SNP_SCHEDULE_EXEC disable constraint FK_CURRENT_SESSION;

alter table SNP_SCHEDULE_EXEC disable constraint FK_SCHEDULE_ID;

alter table SNP_SCHEDULE_EXEC disable constraint FK_CURRENT_LP_RUN;

alter table SNP_SESS_TASK_LS disable constraint FK_SESS_TASK_LS;

alter table SNP_SESS_STEP_LV disable constraint FK_SESS_STEP_LV;

alter table SNP_SESS_TASK_LOG disable constraint FK_SESS_TASK_LOG;

alter table SNP_STEP_LOG disable constraint FK_STEP_LOG;

alter table SNP_SESSION disable constraint FK_SNP_SESSION_SB;

alter table SNP_STEP_REPORT disable constraint FK_STEP_REPORT;

alter table SNP_SCEN_REPORT disable constraint FK_SCEN_REPORT;

alter table SNP_LPI_EC_VAR_LOG disable constraint FK_LPI_EVLOG_ELOG;

alter table SNP_LPI_EC_VAR_LOG disable constraint FK_LPI_EVLOG_SVAR;

alter table SNP_LPI_EXC_LOG disable constraint FK_LPI_ELOG_SLOG;

alter table SNP_LPI_EXC_LOG disable constraint FK_LPI_ELOG_STEP;

alter table SNP_LPI_VAR_LOG disable constraint FK_LPI_VLOG_SLOG;

alter table SNP_LPI_VAR_LOG disable constraint FK_LPI_VLOG_SVAR;

alter table SNP_LPI_STEP_LOG disable constraint FK_LPI_SLOG_RUN;

alter table SNP_LPI_STEP_LOG disable constraint FK_LPI_SLOG_STEP;

Tabelle da Troncare

truncate table SNP_PARAM_SESS;

truncate table SNP_SESS_STEP;

truncate table SNP_SESS_TASK;

truncate table SNP_VAR_SESS;

truncate table SNP_SCHEDULE_EXEC;

truncate table SNP_SESS_TASK_LS;

truncate table SNP_SESS_STEP_LV;

truncate table SNP_SES S_TASK_LOG;

truncate table SNP_STEP_LOG;

truncate table SNP_SESSION;

truncate table SNP_STEP_REPORT;

truncate table SNP_SCEN_REPORT;

truncate table SNP_LPI_EC_VAR_LOG;

truncate table SNP_LPI_EXC_LOG;

truncate table SNP_LPI_VAR_LOG;

truncate table SNP_LPI_STEP_LOG;

alter table SNP_PARAM_SESS enable constraint FK_PARAM_SESS;

alter table SNP_SESS_STEP enable constraint FK_SESS_STEP;

alter table SNP_SESS_TASK enable constraint FK_SESS_TASK3;

alter table SNP_VAR_SESS enable constraint FK_VAR_SESS;

alter table SNP_SCHEDULE_EXEC enable constraint FK_CURRENT_SESSION;

alter table SNP_SCHEDULE_EXEC enable constraint FK_SCHEDULE_ID;

alter table SNP_SCHEDULE_EXEC enable constraint FK_CURRENT_LP_RUN;

alter table SNP_SESS_STEP_LV enable constraint FK_SESS_STEP_LV;

alter table SNP_SESS_TASK_LOG enable constraint FK_SESS_TASK_LOG;

alter table SNP_SESS_TASK_LS enable constraint FK_SESS_TASK_LS;

alter table SNP_STEP_LOG enable constraint FK_STEP_LOG;

alter table SNP_SESSION enable constraint FK_SNP_SESSION_SB;

alter table SNP_STEP_REPORT enable constraint FK_STEP_REPORT;

alter table SNP_SCEN_REPORT enable constraint FK_SCEN_REPORT;

alter table SNP_LPI_EC_VAR_LOG enable constraint FK_LPI_EVLOG_ELOG;

alter table SNP_LPI_EC_VAR_LOG enable constraint FK_LPI_EVLOG_SVAR;

alter table SNP_LPI_EXC_LOG enable constraint FK_LPI_ELOG_SLOG;

alter table SNP_LPI_EXC_LOG enable constraint FK_LPI_ELOG_STEP;

alter table SNP_LPI_VAR_LOG enable constraint FK_LPI_VLOG_SLOG;

alter table SNP_LPI_VAR_LOG enable constraint FK_LPI_VLOG_SVAR;

alter table SNP_LPI_STEP_LOG enable constraint FK_LPI_SLOG_RUN;

alter table SNP_LPI_STEP_LOG enable constraint FK_LPI_SLOG_STEP;

Al termine eseguire la count ottenuta allo STEP 1 per la verifica di quanto cancellato.

ODI 12C - Procedure Purge ODI_SESSIONS - Log 1

 Di seguito una procedura pl-sql per effettuare la purge dei log ODI. La procedura permette di effettuare la delete dei record all'interno di alcune tabelle del repository di ODI.

CREATE OR REPLACE PROCEDURE purge_odi_sessions AS
    a number;
    b number;
    type tableArray IS VARRAY(15) OF VARCHAR2(30);
    tableNames tableArray;
    tableCount integer;
    query_string varchar(2000);
    filter_cond varchar(2000);
    concatString varchar(3);
    TYPE cur_typ IS REF CURSOR;
    c cur_typ;
    tbl_name varchar(100);
    con_name varchar(100);
    
BEGIN    
   tableNames := tableArray('<Inserire il nome delle tabelle tra apici singoli>');
   tableCount := tableNames.count;
   filter_cond:=' ';
   concatString:='''';
    FOR i in 1 .. tableCount LOOP
      filter_cond := filter_cond || concatString ||  tableNames(i);
      concatString:=''',''';
    END LOOP;
   filter_cond:=filter_cond||'''';
    dbms_output.put_line('===========================================' );
dbms_output.put_line('=== DISABLING FORIEGN KEYS ================' );
    
query_string := 'select  UC.TABLE_NAME,UC.CONSTRAINT_NAME
             FROM USER_CONSTRAINTS  UC,
         USER_CONS_COLUMNS UCC
   WHERE UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
     AND uc.constraint_type = ''R'' and UCC.TABLE_NAME in ('||filter_cond||') 
   ORDER BY UC.TABLE_NAME,
            UC.R_CONSTRAINT_NAME,
            UCC.TABLE_NAME,
            UCC.COLUMN_NAME';
    OPEN c FOR query_string;
    LOOP
        FETCH c INTO tbl_name, con_name;
         EXIT WHEN c%NOTFOUND;
         DBMS_OUTPUT.put_line ('EXECUTING : ' || 'ALTER TABLE '||tbl_name||' DISABLE CONSTRAINT '||con_name||';');
         execute immediate 'ALTER TABLE '||tbl_name||' DISABLE CONSTRAINT '||con_name;
    END LOOP;
    CLOSE c;
         
    dbms_output.put_line('===========================================' );
dbms_output.put_line('==== CLEANING SNP_EXP_TXT_HEADER =========' );

execute immediate 'ALTER TABLE SNP_EXP_TXT_HEADER NOLOGGING';
dbms_output.put_line('EXECUTING : DELETE FROM SNP_EXP_TXT_HEADER WHERE I_TXT IN ( SELECT I_TXT_VAR FROM SNP_VAR_SESS ); WITH OUT LOGGING' );
    
DELETE FROM SNP_EXP_TXT_HEADER WHERE I_TXT IN ( SELECT I_TXT_VAR FROM SNP_VAR_SESS );
    
execute immediate 'ALTER TABLE SNP_EXP_TXT_HEADER LOGGING';
    
    dbms_output.put_line('===========================================' );
dbms_output.put_line('======== TRUNCATING THE DATA =============' );
    
    FOR i in 1 .. tableCount LOOP
      dbms_output.put_line('EXECUTING : ' || 'TRUNCATE TABLE '|| tableNames(i) );
      execute immediate 'TRUNCATE TABLE '|| tableNames(i);
    
    END LOOP;
 
    dbms_output.put_line('===========================================' );
dbms_output.put_line('========= ENABLING FORIEGN KEYS ===========' );
     
    OPEN c FOR query_string;
    LOOP
        FETCH c INTO tbl_name, con_name;
         EXIT WHEN c%NOTFOUND;
         DBMS_OUTPUT.put_line ('EXECUTING : ' || 'ALTER TABLE '||tbl_name||' ENABLE CONSTRAINT '||con_name||';');
         execute immediate 'ALTER TABLE '||tbl_name||' ENABLE CONSTRAINT '||con_name;
     END LOOP;
    CLOSE c;
   
   DBMS_OUTPUT.PUT_LINE('Process Completed ');
 END purge_odi_sessions;
/
-- Execute the stored proc using below commads
-- set serveroutput on;
-- exec purge_odi_sessions;

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

Funziona ma non fa altro che seguire quanto viene fatto nell'oditools utilizzato per fare la purge, quindi l'inconveniente maggiore è il tempo impiegato per cancellare i dati dalle tabelle.

ODI 12c - repository tables and their description

 In the post the description of tables present in the repository ODI 12c.

• DESIGN - MASTER - RUNTIME

ORDS - RDBMS : Retrieve a JWT Access Token Using the Auth REST Call pl-sql

Di seguito una funzione per leggere un Token JWT utilizzando una procedura pl-sql. Di seguito diamo una definizione di Token JWT:

Un JSON Web Token è un token di accesso standardizzato secondo RFC 7519 e consente lo scambio sicuro di dati tra due parti. Contiene tutte le informazioni importanti su un’entità, in modo che non sia necessaria alcuna interrogazione del database e che la sessione non debba essere memorizzata sul server.

Un token JWT si presenta nel seguente formato:

Token = base64Header + ‘.’ + base64Payload + ‘.’ + signature

eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWV9.dyt0CoTl4WoVjAHI9Q_CwSKhl6d_9rhM3NrXuJttkao

Per poter decodificare quanto presente nel seguente Token possiamo utilizzare la seguente funzione:

create or replace FUNCTION get_token_information (
         p_token VARCHAR2
    ) RETURN VARCHAR2 AS
        l_token apex_jwt.t_token;
        l_keys apex_t_varchar2;
        l_l_keys varchar2(4000);
        v_l_keys varchar2(4000);
        v_alg varchar2(20);
    BEGIN
     l_token := apex_jwt.decode (p_value => p_token);
    ------------------------------------------------- 
    ------Header 
    ------------------------------------------------- 
    sys.dbms_output.put_line('--- Header ---');
    apex_json.parse(l_token.header);
    l_keys := apex_json.get_members('.');
    for i in 1 .. l_keys.count loop
        l_l_keys:=l_keys(i)||'='||apex_json.get_varchar2(l_keys(i));
               v_alg:=apex_json.get_varchar2(l_keys(i));
        dbms_output.put_line(l_l_keys);
    end loop;
    sys.dbms_output.put_line('--- Payload ---');
    ------------------------------------------------- 
    ------ Payload  ---
    ------------------------------------------------- 
    apex_json.parse(l_token.payload);
    l_keys := apex_json.get_members('.'); 
     dbms_output.put_line('numero oggetti:' ||l_keys.count);
   for j in 1 .. l_keys.count loop 
        sys.dbms_output.put_line(l_keys(j)||'='||apex_json.get_varchar2(l_keys(j)));
    if j<l_keys.count then
    l_l_keys:=l_keys(j)||'='||apex_json.get_varchar2(l_keys(j));
  end if;
    end loop;
    return 'OK'
 END get_token_information; 

Eseguendo la funzione ottiene quanto segue:

DECLARE
  P_TOKEN VARCHAR2(200);
  v_Return VARCHAR2(200);
BEGIN
  P_TOKEN := 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWV9.dyt0CoTl4WoVjAHI9Q_CwSKhl6d_9rhM3NrXuJttkao';
  v_Return := GET_TOKEN_MAIL(
    P_TOKEN => P_TOKEN
  );
 dbms_output.put_line(v_Return);
END;

L'output sarà il seguente: