Rechercher dans ce blog

mercredi 13 août 2014

ASM diskgroup monitoring

ASM  diskgroup  monitoring

For FRA   diskgroup:
SELECT a.name disk_group_name,
    path,
           b.total_mb total_mb,
           (b.total_mb - b.free_mb) used_mb ,
           b.free_mb Free_MB,
           ROUND ( (1 - (b.free_mb / b.total_mb)) * 100, 2) pct_used,
           DECODE (TRUNC ( ( (1 - (b.free_mb / b.total_mb)) * 100 / 32)),
                   0, 'Disk Group OK',
                   1, 'Info Disk Group  entre 32%  et 64% ',
                   2, 'WArning entre  64% et 96% open ticket to DBA P1!',
                   3, 'Urgent   96%  et plus')
              Result
      FROM v$asm_diskgroup a, v$asm_disk b
     WHERE a.group_number = b.group_number AND b.header_status = 'MEMBER'  and
     a.name  in  (select substr(value,2) from  V$parameter  where  name='db_recovery_file_dest')
  ORDER BY ROUND ( (1 - (b.free_mb / b.total_mb)) * 100, 2);

For  diskgroup   in general :

CREATE OR REPLACE FORCE VIEW SYS.RAPPORT_DG
(
  "disk_group_name",
  "path",
  "total_mb",
  "use_MB",
  "Free_MB",
  "pct_used",
  "Result"
)
AS
    SELECT a.name disk_group_name,
    path,
           b.total_mb total_mb,
           (b.total_mb - b.free_mb) used_mb ,
           b.free_mb Free_MB,
           ROUND ( (1 - (b.free_mb / b.total_mb)) * 100, 2) pct_used,
           DECODE (TRUNC ( ( (1 - (b.free_mb / b.total_mb)) * 100 / 32)),
                   0, 'Disk Group OK',
                   1, 'Info Disk Group  entre 32%  et 64%',
                   2, 'WArning entre  64% et 96%',
                   3, 'Urgent   96%  et plus')
              Result
      FROM v$asm_diskgroup a, v$asm_disk b
     WHERE a.group_number = b.group_number AND b.header_status = 'MEMBER'
  ORDER BY ROUND ( (1 - (b.free_mb / b.total_mb)) * 100, 2);

select  *  from  SYS.RAPPORT_DG;
Publié par à Aucun commentaire:
Libellés : ,

dimanche 25 mai 2014

resource manager service job class

resource   manager service  job class


1         Des  etapes  preparatoires :

1.1        Création de  services :

Les  services  sont  reliés  a un  nœud du  cluster  en  particulier.
La  notion de   preconnect   implique que pour le  tns client  il y a une entré backup.
Le  fait  de  créer  un service  avec  l’option  preconnect   ca rajoute  un service  au  clusterware

srvctl   add  service -s  apexprdsvc         -d PRD  -r PRD2 -a PRD1  -e select -m  none -l PRIMARY  -q false -z 0 -w 0             
srvctl   add  service -s  webjobprdsvc      -d PRD  -r PRD2 -a PRD1 -P  PRECONNECT -e  none -m  none   -l PRIMARY  -q false  -z 0 -w
srvctl   add  service -s  airrmprdsvc       -d PRD  -r PRD2 -a PRD1 -P  PRECONNECT -e  none -m  none   -l PRIMARY  -q false  -z 0 -w
srvctl   add  service -s  ffscacheprdsvc     -d PRD  -r PRD2 -a PRD1 -P  PRECONNECT -e none -m  none   -l PRIMARY  -q false  -z 0 -w
srvctl   add  service -s  tsreportingprdsvc  -d PRD  -r PRD2 -a PRD1 -P  PRECONNECT -e none -m  none   -l PRIMARY  -q false  -z 0 -w
srvctl   add  service -s  loaddwprdsvc       -d PRD  -r PRD2 -a PRD1 -P  PRECONNECT -e none -m  none   -l PRIMARY  -q false  -z 0 -w
srvctl   add  service -s  ocrprdsvc          -d PRD  -r PRD2 -a PRD1 -P  PRECONNECT -e none -m  none   -l PRIMARY  -q false  -z 0 -w
srvctl   add  service -s  webprdsvc          -d PRD  -r PRD1 -a PRD2 -P  PRECONNECT -e none -m  none -l PRIMARY  -q false -z 0 -w 0 
srvctl   add  service -s  entprdsvc          -d PRD  -r PRD1 -a PRD2 -P  PRECONNECT -e none -m  none -l PRIMARY  -q false -z 0 -w 0 
srvctl   add  service -s  ffsprdsvc          -d PRD  -r PRD1 -a PRD2 -P  PRECONNECT -e none -m  none -l PRIMARY  -q false -z 0 -w 0 
srvctl   add  service -s  gdstaprdsvc        -d PRD  -r PRD1 -a PRD2 -P  PRECONNECT -e none -m  none -l PRIMARY  -q false -z 0 -w 0 
srvctl   add  service -s  gdstbprdsvc        -d PRD  -r PRD1 -a PRD2 -P  PRECONNECT -e none -m  none -l PRIMARY  -q false -z 0 -w 0 

crsctl stat res –t
…
ora.prd.airrmprdsvc.svc
      1        ONLINE  ONLINE       t105                                                                                                                                                                 
ora.prd.airrmprdsvc_preconnect.svc
      1        ONLINE  ONLINE       t104    
…

1.2       config   du service apexprdsvc :

Service name: apexprdsvc
Service is enabled
Server pool: PRD_ apexprdsvc
Cardinality: 1
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: NONE
Failover method: NONE
TAF failover retries: 0
TAF failover delay: 0
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: PRECONNECT
Edition:
Preferred instances: PRD2
Available instances: PRD1

1.3       demarrage des  services :

srvctl   start  service -s  apexprdsvc         -d PRD                   
srvctl   start  service -s  webjobprdsvc      -d PRD                    
srvctl   start  service -s  airrmprdsvc       -d PRD    
srvctl   start  service -s  ffscacheprdsvc     -d PRD             
srvctl   start  service -s  tsreportingprdsvc  -d PRD             
srvctl   start  service -s  loaddwprdsvc       -d PRD             
srvctl   start  service -s  ocrprdsvc          -d PRD                                                                    
srvctl   start  service -s  webprdsvc          -d PRD   
srvctl   start  service -s  entprdsvc          -d PRD             
srvctl   start  service -s  ffsprdsvc          -d PRD  
srvctl   start  service -s  gdstaprdsvc        -d PRD  
srvctl   start  service -s  gdstbprdsvc        -d PRD  

1.4       Exemple de tns client:

APEX =
(description =
(address = (protocol=tcp)(host=crs)(port=1521))
(connect_data=
(service_name= apexprdsvc)
(failover_mode=
(backup=apex_preconnect)
(type=select)
(method=preconnect)
)
)
)

apex_preconnect =
(description =
(address = (protocol=tcp)(host=crs)(port=1521))
(connect_data=(service_name= apexprdsvc_preconnect))
)

2         Resource  manager

Créations des groupes  resource  manager :
exec dbms_resource_manager.clear_pending_area();                                                                                                                                                   
exec dbms_resource_manager.create_pending_area();                                                                                                                                           
# on crée  les  groupes pour  l’instance 1                                                                                                                                                                      
exec dbms_resource_manager.create_consumer_group('IX-ENT-PRD-GRP','ENT apps 1' );                                                                                                         
exec dbms_resource_manager.create_consumer_group('IX-WEB-PRD-GRP','WEB apps 1');                                                                                                          
exec dbms_resource_manager.create_consumer_group('IX-FFS-PRD-GRP','FFS apps 1');                                                                                                         
exec dbms_resource_manager.create_consumer_group('IX-GDS-TA-PRD-GRP','GDS TA apps 1');                                                                                                     
exec dbms_resource_manager.create_consumer_group('IX-GDS-TB-PRd-GRP','GDS TB apps 1');                                                                                                     
# on crée  les  groupes pour  l’instance 2                                                                                                                                                                      
exec dbms_resource_manager.create_consumer_group('IX-AIRRM-PRD-GRP','AIRRM apps 2'); 
exec dbms_resource_manager.create_consumer_group('IX-APEX-PRD-GRP','APEX apps 2');                                                                                                        
exec dbms_resource_manager.create_consumer_group('IX-FFSCACHE-PRD-GRP','FFS_CACHE apps 2');                                                                                                      
exec dbms_resource_manager.create_consumer_group('IX-LOADDW-PRD-GRP','DW_JOBS apps 2');                                                                                                     
exec dbms_resource_manager.create_consumer_group('IX-OCR-PRD-GRP','OCR JOB apps 2');

2.1       Mapping de  services :

#mapping  service :                                                                                                                                                                         
exec dbms_resource_manager.set_consumer_group_mapping(attribute=>dbms_resource_manager.service_name,value=>'entprdsvc'  ,consumer_group=>'IX-ENT-PRD-GRP'    );    
exec dbms_resource_manager.set_consumer_group_mapping(attribute=>dbms_resource_manager.service_name,value=>'webprdsvc'  ,consumer_group=>'IX-WEB-PRD-GRP'    );    
exec dbms_resource_manager.set_consumer_group_mapping(attribute=>dbms_resource_manager.service_name,value=>'ffsprdsvc'  ,consumer_group=>'IX-FFS-PRD-GRP'    );    
exec dbms_resource_manager.set_consumer_group_mapping(attribute=>dbms_resource_manager.service_name,value=>'gdstaprdsvc',consumer_group=>'IX-GDS-TA-PRD-GRP' );    
exec dbms_resource_manager.set_consumer_group_mapping(attribute=>dbms_resource_manager.service_name,value=>'gdstbprdsvc',consumer_group=>'IX-GDS-TB-PRD-GRP' );    
exec dbms_resource_manager.set_consumer_group_mapping(attribute=>dbms_resource_manager.service_name,value=>'airrmprdsvc',consumer_group=>'IX-AIRRM-PRD-GRP'  );
exec dbms_resource_manager.set_consumer_group_mapping(attribute=>dbms_resource_manager.service_name,value=>'ffscacheprdsvc',consumer_group=>'IX-FFSCACHE-PRD-GRP'  );
exec dbms_resource_manager.set_consumer_group_mapping(attribute=>dbms_resource_manager.service_name,value=>'loaddwprdsvc',consumer_group=>'IX-LOADDW-PRD-GRP'  );
exec dbms_resource_manager.set_consumer_group_mapping(attribute=>dbms_resource_manager.service_name,value=>'apexprdsvc',consumer_group=>'IX-APEX-PRD-GRP'  );
exec dbms_resource_manager.set_consumer_group_mapping(attribute=>dbms_resource_manager.service_name,value=>'ocrprdsvc',consumer_group=>'IX-OCR-PRD-GRP'  );

2.2       Creation du plan et des  directives:

exec DBMS_RESOURCE_MANAGER.create_plan('IX_APPS_PLAN','PRD_ix_plan');
begin
dbms_resource_manager.create_plan_directive(PLAN=>'IX_APPS_PLAN',GROUP_OR_SUBPLAN=>'IX-APEX-PRD-GRP',Comment=>'percent of  CPU for IX-APEX-PRD-GRP',mgmt_p1=>10);
dbms_resource_manager.create_plan_directive(PLAN=>'IX_APPS_PLAN',GROUP_OR_SUBPLAN=>'IX-OCR-PRD-GRP' ,Comment=>'percent of  CPU for 'IX-OCR-PRD-GRP' ,mgmt_p1=>50,max_utilization_limit=>65);
dbms_resource_manager.create_plan_directive(PLAN=>'IX_APPS_PLAN',GROUP_OR_SUBPLAN=>'OTHER_GROUPS'      ,Comment=>'percent of  CPU for  OTHER_GROUPS',mgmt_p1=>40, mgmt_p2=>0,max_utilization_limit=>90);
end;
/
exec dbms_resource_manager.validate_pending_area();                                                                                                                                                                   
exec dbms_resource_manager.submit_pending_area();

3         Post  installation:

3.1       Directives  et  users

grant  EXECUTE ANY CLASS  to  AIR_PRD_FQ;
grant  EXECUTE ANY CLASS  to  APEX_PUBLIC_USER;
exec  dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'APEX_PUBLIC_USER',consumer_group=>'IX-APEX-PRD-GRP',grant_option=>FALSE); 
exec  dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'AIR_PRD_FQ',consumer_group=>'IX-OCR-PRD-GRP',grant_option=>FALSE);

3.2       Job class :

Le  but  est  de   relier  des job interne  oracle a  un service  et  resource   manager :
exec  SYS.DBMS_SCHEDULER.DROP_JOB_CLASS(job_class_name=>'ocr_class',force=> TRUE);
 exec DBMS_SCHEDULER.create_job_class(job_class_name=> 'ocr_class',service=> 'ocrprdsvc', logging_level=>DBMS_SCHEDULER.LOGGING_RUNS,comments=> 'run on OCR',RESOURCE_CONSUMER_GROUP=>'IX-OCR-PRD-GRP');

3.3       Modification init:

Alter  system  set resource_manager_plan= IX_APPS_PLAN  scope=both sid=’*’;

4         Annexe:

4.1       Note  et details

Metalink  docID1312749.1

Publié par à Aucun commentaire:
Libellés :
Inscription à : Articles (Atom)