Rem drv: <migrate type="data_upgrade" version="10.2.0.1" condition="EM_REPOS_MODE=CENTRAL"/>
Rem
Rem $Header: upgrade_reports.sql 14-aug-2006.12:06:35 bmallipe Exp $
Rem
Rem upgrade_reports.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      upgrade_reports.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    bmallipe    08/14/06 - adding the CENTRAL condition
Rem    rreilly     08/04/05 - bug 4534249 move set define on 
Rem    nachen      07/29/05 - upgrade database overview report 
Rem    rreilly     07/17/05 - rreilly_bug-4494270
Rem    rreilly     07/15/05 - Created
Rem

SET DEFINE OFF

-- Upgrade from BETA to GC requires the following steps: 
-- Drop BETA reports to remove all old reports, elements and SQL
-- Then, call our create report scripts which are copies from latest GC
-- so that we get all the up-to-date changes. 
--
-- NOTE: After the 10.2GC release the report upgrade scripts should be 
--       changed to be more like the policy upgrade scripts, only 
--       containing changes new reports, dropped reports, and modifications
--       to existing reports. In the future this approach would cause users
--       to loose sql they may rely on. This is not an issue for BETA though.

-- ------------------------------------------------
-- Drop all BETA db_outofbox_storage Reports
-- ------------------------------------------------

BEGIN

  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN',
    report_title_in => 'DBMSG_DATABASE_GROUP_SPACE_USAGE',
    check_security  => 0 );
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.DBGroupCurrDBSpcUsage');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.DBGroupHistStorageChartSql');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.DBGroupTbspAllocUsage');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.CurrDBGroupTbspFullAlertSummary');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.DBGroupFullAlertCount');
  COMMIT;

  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN',
    report_title_in => 'DBMSG_DATABASE_SPACE_USAGE',
    check_security  => 0 );
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.CurrentDBStorageSql');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.DBHistTbspUsageChartSql');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.DBDatafileSpcUsage');
  COMMIT;

  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN',
    report_title_in => 'DBMSG_TBLSPC_MONTHLY_SPACE_USAGE',
    check_security  => 0 );
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.TbspAllocMetricTimestampSql');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.TbspUsageMonthly');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.TbspUsageMonthlySplit');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.TbspUsageMonthlyChartSql');
  COMMIT;

END;
/

-- ------------------------------------------------
-- Drop all BETA db_outofbox_storage_issues Reports
-- ------------------------------------------------
BEGIN

  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN',
    report_title_in => 'DBMSG_TABLESPACE_FULL_ALERT_BY_PERCENT',
    check_security  => 0 );
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.TbspFullSpaceUsedPctOpenAlerts');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.HistoricTbspSpaceUsedPct');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.HistoricAvgTbspSpaceUsedPct');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.AlertHistoryTbspSpaceUsedPct');
  COMMIT;

  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN',
    report_title_in => 'DBMSG_TABLESPACE_FULL_ALERT_BY_MB',
    check_security  => 0 );
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.TbspFullFreeMBOpenAlerts');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.HistoricTbspFreeMB');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.HistoricAvgTbspFreeMB');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.AlertHistoryTbspFreeMB');
  COMMIT;

  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN',
    report_title_in => 'DBMSG_TABLESPACE_PROBLEM_SEGMENTS',
    check_security  => 0 );
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.TbspSegsApproachingMaxExtents');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.storage.reports.TbspSegsNotAbleToExtend');
  COMMIT;

  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN',
    report_title_in => 'DBMSG_TABLESPACE_REC_SPACE',
    check_security  => 0 );
  COMMIT;

  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN',
    report_title_in => 'DBMSG_SEGMENTS_ROW_CHAINING',
    check_security  => 0 );
  COMMIT;
END;
/

-- ------------------------------------------------
-- Drop all BETA db_outofbox_audit Reports
-- ------------------------------------------------
BEGIN

  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN',
    report_title_in => 'DBMSG_DATABASE_FAILED_LOGINS',
    check_security  => 0 );
  COMMIT;

  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN',
    report_title_in => 'DBMSG_SUCCESSFUL_LOGINS',
    check_security  => 0 );
  COMMIT;

  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN',
    report_title_in => 'DBMSG_SYS_USER_OPERATIONS',
    check_security  => 0 );
  COMMIT;

  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN',
    report_title_in => 'DBMSG_DATABASE_GROUP_FAILED_LOGIN',
    check_security  => 0 );
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.security.audit.reports.groupTotalFailedLoginCount');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.security.audit.reports.groupDailyFailedLoginCount');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.security.audit.reports.groupDailyFailedLoginCountForChart');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.security.audit.reports.topDBFailedLoginCount');
  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.security.audit.reports.topDBFailedLoginCountByDay');
  COMMIT;
END;
/

BEGIN
-- clean up Database Instance Overview Report
  mgmt_ip.delete_report_by_title(
    report_owner_in => 'SYSMAN', 
    report_title_in => 'DB_DATABASE_INSTANCE_OVERVIEW',
    check_security => 0);

  mgmt_ip.deregister_sql_statement(
    p_name => 'oracle.sysman.db.overview.report.DB_INSTANCE_INFO_SQL');
  mgmt_ip.deregister_sql_statement( 
    p_name => 'oracle.sysman.db.overview.report.SGA_INFO_SQL');
  mgmt_ip.deregister_sql_statement (
    p_name => 'oracle.sysman.db.overview.report.NON_DEFAULT_INIT_PARAMS_SQL'); 
  COMMIT; 
END;
/

SET DEFINE ON

-- Run our scripts
@&EM_SQL_ROOT/db/v102020/reports/db_outofbox_elements.sql
@&EM_SQL_ROOT/db/v102020/reports/db_outofbox_storage.sql
@&EM_SQL_ROOT/db/v102020/reports/db_outofbox_storage_issues.sql
@&EM_SQL_ROOT/db/v102020/reports/db_outofbox_audit.sql
@&EM_SQL_ROOT/db/v102020/reports/overview_outofbox.sql