Rem
Rem NAME
Rem CATSNMP.SQL
Rem FUNCTION
Rem Creates an SNMPAgent role to access the v$ tables
Rem NOTES
Rem MODIFIED
Rem ychan 06/17/09 - Fix bug 8607966
Rem dsemler 01/14/08 - Add QOS user creation
Rem amahalin 06/21/07 - to grant SELECT_CATALOG_ROLE to OEM_MONITOR
Rem jsoule 05/07/07 - grant dbms_management_packs access to dbsnmp
Rem ychan 02/13/07 - Add db_feature table and procedure
Rem jsoule 07/20/06 - restore bsln schema creation
Rem jsoule 07/13/06 - remove bsln dependency on EMDW
Rem lburgess 03/27/06 - user lowercase for DBSNMP password
Rem nachen 02/02/05 - add OEM_ADVISOR role
Rem ychan 11/11/04 - Fix bug 3926476
Rem zsyed 10/29/04 - Removing addition of dbsnmp to SYS_USER group
Rem jsoule 08/06/04 - add dbsnmp baseline schema objects
Rem shigupta 07/16/04 - add dbsnmp to sys_group consumer group
Rem hxlin 06/28/04 - Put SQL Response back
Rem hxlin 06/18/04 - Temporarily remove response.plb
Rem shigupta 06/07/03 - cleanup
Rem ychan 11/05/03 - Fix bug 3234502
Rem jochen 10/02/03 - Add ANALYZE ANY DICTIONARY to OEM_MONITOR
Rem vchao 07/09/03 - Fix bug 3027355. Modify alert_que privileges
Rem ychan 06/27/03 - Add aq priv
Rem ychan 06/26/03 - Add aq priv
Rem lhan 06/27/03 - change key of gtt
Rem lhan 06/13/03 - remove hard tab
Rem lhan 06/06/03 - Add GTTs definition
Rem ychan 05/14/03 - Remove reuse
Rem ychan 05/11/03 - Grant dbms_lock
Rem ychan 04/28/03 - Add response sql
Rem ychan 03/26/03 - Fix connect dbsnmp
Rem ychan 03/24/03 - Fix bug 2657279
Rem jaysmith 08/21/02 - remove create user error-handling
Rem jaysmith 08/18/02 - suppress error from create user
Rem jaysmith 08/16/02 - do not recreate DBSNMP user each time run
Rem xxu 02/01/02 - add function OemGetNextExtend
Rem glavash 01/03/02 - remove superfluos privs
Rem rburns 10/28/01 - wrap drop role statement
Rem gviswana 05/24/01 - CREATE OR REPLACE SYNONYM
Rem jaysmith 04/11/01 - restore views, oemagent roles
Rem jaysmith 03/08/01 - pull back grants for dbsnmp user
Rem glavash 02/16/01 - add select access to sys.obj$ user and ts
Rem glavash 10/23/00 - add statspack views
Rem dholail 04/12/99 - Adding Events role OEM_MONITOR
Rem cluo 07/15/96 -
Rem dnakos removed creation of backup script tables
Rem dnakos removed creation of obsolete history tables
Rem dnakos removed references to obsolete DBA_LOCKS
Rem
Rem OWNER
Rem ebosco
Rem
BEGIN
EXECUTE IMMEDIATE 'drop role SNMPAGENT';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -1919 THEN NULL;
ELSE RAISE;
END IF;
END;
/
REM
REM -- OEM_ADVISOR role is needed so that users with this
REM -- role could manage advisor tasks.
REM
create role OEM_ADVISOR;
REM -- Privileges to run Advisor Tasks as Scheduler Jobs
grant ADVISOR to OEM_ADVISOR;
grant CREATE JOB to OEM_ADVISOR;
REM -- Privileges to run SQL Tuning Advisor Tasks
grant ADMINISTER SQL TUNING SET to OEM_ADVISOR;
REM
REM -- OEM_MONTOR role is needed so that users of EM Diagnostics Pack can grant this
REM -- role to another user who they want to use to monitor database using EM Agent.
REM -- DBSNMP is the out-of-box default user used by EM Agent to monitor the database.
REM
create role OEM_MONITOR;
REM -- Ability to create a session and read dictionary views
grant CREATE SESSION to OEM_MONITOR;
grant SELECT ANY DICTIONARY to OEM_MONITOR;
REM -- Privileges to enable/disable SQL Tracing and Statistics Aggregation
grant execute on SYS.DBMS_SYSTEM to OEM_MONITOR;
grant execute on SYS.DBMS_MONITOR to OEM_MONITOR;
REM -- Privileges to run AWR report
grant execute on dbms_workload_repository to OEM_MONITOR;
REM -- Privileges to run Advisor Tasks as Scheduler Jobs
grant ADVISOR to OEM_MONITOR;
grant CREATE JOB to OEM_MONITOR;
REM -- HA related package
grant execute on SYS.DBMS_DRS to OEM_MONITOR;
REM -- Privileges needed to use DBMS_SPACE package. This package
REM -- is used by Space metrics
REM
grant analyze any to OEM_MONITOR;
grant analyze any dictionary to OEM_MONITOR;
REM -- Privileges for alerts queue
grant EXECUTE on DBMS_AQADM to OEM_MONITOR;
grant EXECUTE on DBMS_AQ to OEM_MONITOR;
grant EXECUTE on DBMS_SERVER_ALERT to OEM_MONITOR;
REM -- Privileges for OLS Data Dictionary Views
grant SELECT_CATALOG_ROLE to OEM_MONITOR;
begin
dbms_aqadm.grant_system_privilege('manage_any', 'OEM_MONITOR', false);
dbms_aqadm.grant_queue_privilege('dequeue', 'alert_que', 'OEM_MONITOR', false);
end;
/
create user DBSNMP identified by dbsnmp DEFAULT TABLESPACE SYSAUX PASSWORD EXPIRE ACCOUNT LOCK;
grant select any dictionary to DBSNMP;
grant OEM_MONITOR to DBSNMP;
REM - These privileges are needed for SQL Response metric. These need to be
REM - granted only to DBSNMP since DBSNMP schema owns these objects.
REM - The response.plb file (see below) creates the tables and procedures
REM - under DBSNMP. Such creation will fail without these privileges.
REM
grant create table to DBSNMP;
grant create procedure to DBSNMP;
grant unlimited tablespace to DBSNMP;
REM - DBSNMP user should get preferential resource allocation on a system
REM - experiencing contention. This is to ensure that DBA is able to use
REM - EM to diagnose the problem. If resource manager is enabled then sys_group
REM - gets a high allocation since this is what sys and system belong to.
REM - Therefore, add dbsnmp to sys_group.
REM Grant these directly to the owner of mgmt_bsln_internal.
GRANT EXECUTE ON DBMS_SERVER_ALERT TO DBSNMP;
GRANT EXECUTE ON DBMS_MANAGEMENT_PACKS TO DBSNMP;
ALTER SESSION SET CURRENT_SCHEMA = DBSNMP;
REM Load the SQL Response Metric
@@response.plb
REM -- Privilege to execute MGMT_RESPONSE package (for SQL Response Metric)
grant EXECUTE on dbsnmp.mgmt_response to OEM_MONITOR;
REM -- Privileges to query SQL Response tables
grant select on dbsnmp.mgmt_baseline to OEM_MONITOR;
grant select on dbsnmp.mgmt_baseline_sql to OEM_MONITOR;
grant select on dbsnmp.mgmt_latest to OEM_MONITOR;
grant select on dbsnmp.mgmt_latest_sql to OEM_MONITOR;
grant select on dbsnmp.mgmt_history to OEM_MONITOR;
grant select on dbsnmp.mgmt_history_sql to OEM_MONITOR;
REM Create the BSLN schema objects
@@catbsln
REM Create GTTs for Tablespaces Full metric under DBSNMP schema
create global temporary table mgmt_db_file_gtt (
tablespace_name varchar2(30),
meg number,
max_meg number,
file_name varchar2(513),
file_id number,
ts# number,
blocksize number,
flag number,
constraint mgmt_db_file_gtt_pk primary key (tablespace_name,file_id)
)
on commit delete rows;
create global temporary table mgmt_db_size_gtt (
tablespace_name varchar2(30),
sz number,
constraint mgmt_db_size_gtt_pk primary key (tablespace_name)
)
on commit delete rows;
REM ******************************
REM Start: Tracking em db feature
REM ******************************
CREATE TABLE mgmt_db_feature_log (
source varchar2(30) NOT NULL CONSTRAINT mgmt_db_feature_log_pk PRIMARY KEY,
last_update_date timestamp with time zone);
CREATE OR REPLACE PROCEDURE mgmt_update_db_feature_log(src IN VARCHAR2)
AS
l_last_update_date mgmt_db_feature_log.last_update_date%TYPE;
current_date mgmt_db_feature_log.last_update_date%TYPE;
diff interval day to second;
diff_min NUMBER;
BEGIN
current_date := SYSTIMESTAMP;
SELECT last_update_date
INTO l_last_update_date
FROM mgmt_db_feature_log
WHERE source = src;
diff := current_date - l_last_update_date;
diff_min := EXTRACT(DAY FROM diff)*24*60+EXTRACT(HOUR FROM diff)*60+EXTRACT(MINUTE FROM diff);
-- 2 hours 2x60=120
IF (diff_min > 120) THEN
UPDATE mgmt_db_feature_log set last_update_date = current_date WHERE source = src;
commit;
END IF;
EXCEPTION
when NO_DATA_FOUND then
BEGIN
INSERT INTO mgmt_db_feature_log VALUES (src, systimestamp);
commit;
END;
END;
/
GRANT EXECUTE ON dbsnmp.mgmt_update_db_feature_log TO OEM_MONITOR;
REM ******************************
REM END: Tracking em db feature
REM ******************************
ALTER SESSION SET CURRENT_SCHEMA = SYS;
REM This must be called after the DBSNMP user is known to be created as
REM it grants permissions to DBSNMP
@@catqos