Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\ocmjb10.sql

Rem
Rem $Header: emll/admin/scripts/ocmjb10.sql /main/8 2008/09/10 13:26:09 glavash Exp $
Rem
Rem ocmjb10.sql
Rem
Rem Copyright (c) 2006, 2008, Oracle. All rights reserved.
Rem
Rem    NAME
Rem      ocmjb10.sql - OCM db config collection Job package Body for 10g onwards.
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    glavash     08/20/08 - remove assert
Rem    glavash     06/03/08 - XbranchMerge glavash_bug-7011400 from
Rem                           st_emll_10.3.0
Rem    glavash     06/02/08 - wrap l_ocm_dir_path bug 7011400
Rem    dkapoor     01/23/07 - create once a month job to coll stats
Rem    dkapoor     07/26/06 - do not use define 
Rem    dkapoor     07/21/06 - create package to re-create dir object 
Rem    dkapoor     06/13/06 - debug job already present error 
Rem    dkapoor     06/07/06 - run the job once 
Rem    dkapoor     06/02/06 - change ccr_user to ocm 
Rem    dkapoor     05/22/06 - Created
Rem

CREATE OR REPLACE PACKAGE body ORACLE_OCM.MGMT_CONFIG AS

JOB_NAME CONSTANT VARCHAR(40) := 'MGMT_CONFIG_JOB';
STATS_JOB_NAME CONSTANT VARCHAR(40) := 'MGMT_STATS_CONFIG_JOB';

/*
 Checks to see if the job already exists
*/
FUNCTION job_exists (job_name_in VARCHAR) RETURN BOOLEAN IS
  l_job_cnt NUMBER;
BEGIN
   select count(*) into l_job_cnt from 
	dba_scheduler_jobs WHERE job_name = job_name_in and owner ='ORACLE_OCM';
  if l_job_cnt = 0
  THEN
    return FALSE;
  ELSE
    return TRUE;
  END IF;
END job_exists;

/*
Submit a job to collect the configuration.
Basically, a job with what->collect_config
*/
procedure submit_job IS
BEGIN
  IF not job_exists(JOB_NAME) THEN  
      sys.dbms_scheduler.create_job(
        job_name => JOB_NAME,
        job_type => 'STORED_PROCEDURE',
        job_action => 'ORACLE_OCM.MGMT_CONFIG.collect_config',
 	schedule_name=> 'SYS.MAINTENANCE_WINDOW_GROUP',
        enabled => TRUE,
        auto_drop => FALSE,
        comments => 'Configuration collection job.');
     COMMIT;
  ELSE
      RAISE_APPLICATION_ERROR(-20000,'Cannot resubmit. A job '''|| JOB_NAME 
                       || '''already exists.');
  END IF;
  IF not job_exists(STATS_JOB_NAME) THEN  
      sys.dbms_scheduler.create_job(
        job_name => STATS_JOB_NAME,
        job_type => 'STORED_PROCEDURE',
        job_action => 'ORACLE_OCM.MGMT_CONFIG.collect_stats',
 	start_date=> SYSTIMESTAMP,
        repeat_interval => 'freq=monthly;interval=1;bymonthday=1;byhour=01;byminute=01;bysecond=01',
        end_date => NULL,
        enabled => TRUE,
        auto_drop => FALSE,
        comments => 'OCM Statistics collection job.');
     COMMIT;
  ELSE
      RAISE_APPLICATION_ERROR(-20001,'Cannot resubmit. A job '''|| STATS_JOB_NAME 
                       || '''already exists.');
  END IF;
END submit_job;

/*
Submit a job to collect the configuration.
Basically, a job with what->collect_config_metrics(<collection directory>
*/
procedure submit_job_for_inst(inst_id IN BINARY_INTEGER ,
        p_job_name IN VARCHAR2,p_job_action IN VARCHAR2) IS
 l_job NUMBER;
BEGIN
  BEGIN
  IF not job_exists(p_job_name || '_' || inst_id) THEN  
      sys.dbms_scheduler.create_job(
        job_name => p_job_name || '_' || inst_id,
        job_type => 'PLSQL_BLOCK',
        job_action => p_job_action ,
        start_date => NULL,
        repeat_interval => NULL,
        enabled => FALSE,
        auto_drop => TRUE,
        comments => 'OCM collection job run for an instance.');
       BEGIN
        -- Use the instance_id attribute.
        -- This may throw exception if not implemented in the version of
        -- the database. We would be ignoring the exception it that case.
        DBMS_SCHEDULER.SET_ATTRIBUTE (p_job_name || '_' || inst_id,'instance_id',inst_id);
       EXCEPTION
         WHEN OTHERS THEN NULL;
       END;

DBMS_SCHEDULER.ENABLE (p_job_name || '_' || inst_id);
     -- Run the job synchronously
     -- DBMS_SCHEDULER.RUN_JOB(p_job_name || '_' || inst_id,FALSE);
     COMMIT;
  END IF;
  EXCEPTION
    	WHEN OTHERS THEN
        -- Don't raise an exception otherwise it fills the alert/trace
        DBMS_OUTPUT.put_line('Do not raise an exception');
         -- RAISE_APPLICATION_ERROR(-20000,'SQLERRM: ' || SQLERRM || ' SQLCODE: '|| SQLCODE);
  END;
END submit_job_for_inst ;

/*
Runs the configuration collection job now.
*/
procedure run_now IS
BEGIN
   	DBMS_SCHEDULER.RUN_JOB(JOB_NAME);
   	DBMS_SCHEDULER.RUN_JOB(STATS_JOB_NAME);
  	COMMIT;
END run_now;

/*
Print the job details.
*/
procedure print_job_details IS
BEGIN
        dbms_output.put_line('Configuration collection job name: ' || JOB_NAME);
        dbms_output.put_line('Statistics collection job name: ' || STATS_JOB_NAME);
        dbms_output.put_line('Job Schedule Name: SYS.MAINTENANCE_WINDOW_GROUP');
END print_job_details;

/*
Stop the job.
*/
procedure stop_job IS
BEGIN
    DBMS_SCHEDULER.DROP_JOB (JOB_NAME);
    DBMS_SCHEDULER.DROP_JOB (STATS_JOB_NAME);
    COMMIT;
END stop_job;

/*
Config collection job
*/
procedure collect_config IS
  CURSOR l_res_cur IS select inst_id from gv$instance;
BEGIN
	FOR inst_id_row in l_res_cur LOOP
		submit_job_for_inst(inst_id_row.inst_id,
                 JOB_NAME, 'BEGIN ORACLE_OCM.MGMT_DB_LL_METRICS.collect_config_metrics(''ORACLE_OCM_CONFIG_DIR''); END;');
	END LOOP;
END collect_config;

/*
Statistics collection job
*/
procedure collect_stats IS
  CURSOR l_res_cur IS select inst_id from gv$instance;
BEGIN
	FOR inst_id_row in l_res_cur LOOP
		submit_job_for_inst(inst_id_row.inst_id,
                 STATS_JOB_NAME, 'BEGIN ORACLE_OCM.MGMT_DB_LL_METRICS.collect_stats_metrics(''ORACLE_OCM_CONFIG_DIR''); END;');
	END LOOP;
END collect_stats;

END MGMT_CONFIG;
/
show errors;
 
/*
     This package is executed with invoker's rights. This is needed so that
     ORACLE_OCM user does not need to be granted "execute" permission on "dbms_system" package.
*/
CREATE OR REPLACE PACKAGE body ORACLE_OCM.MGMT_CONFIG_UTL AS

PLATFORM_WINDOWS32    CONSTANT BINARY_INTEGER := 7;
PLATFORM_WINDOWS64    CONSTANT BINARY_INTEGER := 8;
PLATFORM_OPENVMS      CONSTANT BINARY_INTEGER := 15;

/*
Create or replace the directory object to recreate the path based on 
new ORACLE_HOME.
Note: 
  1. This procedure is executed with invoker's rights. This is needed so that
     ORACLE_OCM user does not need to be granted "execute" permission on "dbms_system" package.
     Only SYS would be able to run this procedure without error as it has the privilege to execute "dbms_system" and re-create
     the directory object ORACLE_OCM_CONFIG_DIR owned by it.
  2. This procedure is only supported from release 10.2 onwards that supports dbms_system.get_env.
*/
procedure create_replace_dir_obj IS
    -- local variables
  pfid        NUMBER;
  root        VARCHAR2(2000);
  l_ocm_dir_path VARCHAR2(4000);
BEGIN
    -- get the platform id
    SELECT platform_id INTO pfid FROM v$database;

IF pfid = PLATFORM_OPENVMS THEN
      -- ORA_ROOT is a VMS logical name
      l_ocm_dir_path := 'ORA_ROOT:[ccr.state]';
    ELSE
      -- Get ORACLE_HOME
      execute immediate 'BEGIN DBMS_SYSTEM.GET_ENV(''ORACLE_HOME'', :1); END;' using out root;
      -- Return platform-specific string
      IF pfid = PLATFORM_WINDOWS32 OR pfid = PLATFORM_WINDOWS64
      THEN
        l_ocm_dir_path := root || '\ccr\state';
      ELSE
        l_ocm_dir_path := root || '/ccr/state';
      END IF;
    END IF;
    execute immediate 'CREATE OR REPLACE DIRECTORY ORACLE_OCM_CONFIG_DIR AS ''' || l_ocm_dir_path || '''' ;
    COMMIT;
END create_replace_dir_obj;

END MGMT_CONFIG_UTL;
/
show errors;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de