Submit a ticketCall us

Don’t fall victim to a ransomware attack
Backups are helpful, but sometimes that’s not enough to protect your business against ransomware. At our live webcast we will discuss how to protect against ransomware attacks with SolarWinds® Patch Manager and how to leverage log data to detect ransomware. Register now for our live webcast.

Home > Success Center > Database Performance Analyzer (DPA) > Oracle database is unable to unregister in DPA

Oracle database is unable to unregister in DPA

Table of contents
Created by Robert Holtam, last modified by MindTouch on Jun 23, 2016

Views: 22 Votes: 0 Revisions: 4

Overview

This article describes the issue when you are unable to unregister an Oracle DB in DPA.

Environment

DPA version 10.0.352 and later

Steps

Run this query in the DPA repository database:

set serveroutput on
set pages 999
column name format a60
select id, name from cond order by name;
prompt Database ID:
accept DBID

declare
   sSQL     VARCHAR2(1000);
   nDBID     INTEGER := &DBID;
   sDBName     VARCHAR2(50);
begin
   -- loop through all tables that end in this ID
   for t in (select table_name from user_tables
             where table_name like 'CON%\_'||nDBID escape '\') loop
      -- drop each table for this database
      sSQL := 'DROP TABLE ' || t.table_name;
      DBMS_OUTPUT.PUT_LINE(sSQL);
      EXECUTE IMMEDIATE sSQL;
   end loop;

   -- loop through all sequences
   for s in (select sequence_name from user_sequences
             where sequence_name like 'CON%\_'||nDBID escape '\') loop
      -- drop each sequence for this database
      sSQL := 'DROP SEQUENCE ' || s.sequence_name;
      DBMS_OUTPUT.PUT_LINE(sSQL);
      EXECUTE IMMEDIATE sSQL;
   end loop;

   -- remove all parameters
   DELETE condprm WHERE id = nDBID;
   DBMS_OUTPUT.PUT_LINE('Purged ' || SQL%ROWCOUNT || ' rows from CONDPRM');
   COMMIT;

   -- remove all alert history
   -- get the DBname so we can remove things
   SELECT name INTO sDBName FROM cond WHERE id = nDBID;
   DELETE con_alert_history_results
   WHERE HISTORYID IN (
      SELECT HISTORYID
      FROM CON_ALERT_HISTORY
      WHERE DBNAME = sDBName);
   DBMS_OUTPUT.PUT_LINE('Purged '||SQL%ROWCOUNT || ' rows from con_alert_history_results');
   COMMIT;
   DELETE CON_ALERT_HISTORY WHERE DBNAME = sDBName;
   DBMS_OUTPUT.PUT_LINE('Purged '||SQL%ROWCOUNT || ' rows from con_alert_history');
   COMMIT;

   DELETE con_alert_db_status_history WHERE DBID = nDBID;
   DBMS_OUTPUT.PUT_LINE('Purged '||SQL%ROWCOUNT || ' rows from con_alert_db_status_history');
   DELETE con_alert_db_results WHERE DBID = nDBID;
   DBMS_OUTPUT.PUT_LINE('Purged '||SQL%ROWCOUNT || ' rows from con_alert_db_results');
   DELETE con_dbparams_alert WHERE DBID = nDBID;
   DBMS_OUTPUT.PUT_LINE('Purged '||SQL%ROWCOUNT || ' rows from con_dbparams_alert');
   DELETE con_alert_db WHERE DBID = nDBID;
   DBMS_OUTPUT.PUT_LINE('Purged '||SQL%ROWCOUNT || ' rows from con_alert_db');
   COMMIT;

   -- remove the row from COND
   DELETE cond WHERE id = nDBID;
   DBMS_OUTPUT.PUT_LINE('Database has been removed from Ignite');
   COMMIT;
end;
/

 

Last modified
19:16, 22 Jun 2016

Tags

Classifications

Public