Archive

Archive for August, 2010

Useful notes for oracle CRS

August 25, 2010 Leave a comment

To find out cluster and daemon status:
=========================================


$GRID_HOME/bin/crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

To find out clusterware processes state:
===========================================


$GRID_HOME/bin/crsctl stat res -t -init

----------------------------------------------------------------------
NAME           TARGET  STATE        SERVER              STATE_DETAILS      
----------------------------------------------------------------------
Cluster Resources
----------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       rac1                 Started            
ora.crsd
      1        ONLINE  ONLINE       rac1                                        
ora.cssd
      1        ONLINE  ONLINE       rac1                                        
ora.cssdmonitor
      1        ONLINE  ONLINE       rac1                                        
ora.ctssd
      1        ONLINE  ONLINE       rac1                 ACTIVE:0           
ora.diskmon
      1        ONLINE  ONLINE       rac1                                        
ora.drivers.acfs
      1        ONLINE  ONLINE       rac1                                        
ora.evmd
      1        ONLINE  ONLINE       rac1                                        
ora.gipcd
      1        ONLINE  ONLINE       rac1                                        
ora.gpnpd
      1        ONLINE  ONLINE       rac1                                        
ora.mdnsd
      1        ONLINE  ONLINE       rac1                                        

Use the following command to query the resource state of all user resources:
============================================================================


[oracle@rac1 bin]$ ./crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
      1        ONLINE  ONLINE       rac1                     Started            
ora.crsd
      1        ONLINE  ONLINE       rac1                                        
ora.cssd
      1        ONLINE  ONLINE       rac1                                        
ora.cssdmonitor
      1        ONLINE  ONLINE       rac1                                        
ora.ctssd
      1        ONLINE  ONLINE       rac1                     ACTIVE:0           
ora.diskmon
      1        ONLINE  ONLINE       rac1                                        
ora.drivers.acfs
      1        ONLINE  ONLINE       rac1                                        
ora.evmd
      1        ONLINE  ONLINE       rac1                                        
ora.gipcd
      1        ONLINE  ONLINE       rac1                                        
ora.gpnpd
      1        ONLINE  ONLINE       rac1                                        
ora.mdnsd
      1        ONLINE  ONLINE       rac1                                        

 
[oracle@rac1 bin]$ ./crsctl stat res -t

-----------------------------------------------------------------
NAME           TARGET  STATE        SERVER       STATE_DETAILS      
------------------------------------------------------------------
Local Resources
------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       rac1                                        
               ONLINE  ONLINE       rac2                                        
ora.FLASH.dg
               ONLINE  ONLINE       rac1                                        
               ONLINE  ONLINE       rac2                                        
ora.LISTENER.lsnr
               ONLINE  ONLINE       rac1                                        
               ONLINE  ONLINE       rac2                                        
ora.asm
               ONLINE  ONLINE       rac1           Started            
               ONLINE  ONLINE       rac2           Started            
ora.eons
               ONLINE  ONLINE       rac1                                        
               ONLINE  ONLINE       rac2                                        
ora.gsd
               OFFLINE OFFLINE      rac1                                        
               OFFLINE OFFLINE      rac2                                        
ora.net1.network
               ONLINE  ONLINE       rac1                                        
               ONLINE  ONLINE       rac2                                        
ora.ons
               ONLINE  ONLINE       rac1                                        
               ONLINE  ONLINE       rac2                                        
ora.registry.acfs
               ONLINE  ONLINE       rac1                                        
               ONLINE  ONLINE       rac2                                        
---------------------------------------------------------------
Cluster Resources
---------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       rac1                                        
ora.oc4j
      1        OFFLINE OFFLINE                                                  
ora.rac1.vip
      1        ONLINE  ONLINE       rac1                                        
ora.rac2.vip
      1        ONLINE  ONLINE       rac2                                        
ora.scan1.vip
      1        ONLINE  ONLINE       rac1                                        
ora.testrac.db
      1        ONLINE  ONLINE       rac1            Open               
      2        ONLINE  ONLINE       rac2            Open               

 

To start an offline daemon – if ora.crsd is OFFLINE:
$GRID_HOME/bin/crsctl start res ora.crsd -init
If this fails, try with “-f” option:
$GRID_HOME/bin/crsctl stop res ora.crsd -init -f

If a critical clusterware process fails to start and there’s no obvious reason,
the next action is to restart clusterware on local node:
$GRID_HOME/bin/crsctl stop crs -f
If above command fails, you may kill all clusterware processes by executing:
ps -ef | grep keyword | grep -v grep | awk ‘{print $2}’ | xargs kill -9
As a last resort, you can take out local node by rebooting it.

OHASD.BIN
==========

spawns 4 agent monitors to start level resource


oraagent: responsible for ora.asm, ora.evmd, ora.gipcd,
ora.gpnpd, ora.mdnsd etc
orarootagent: responsible for ora.crsd, ora.ctssd,
ora.diskmon, ora.drivers.acfs etc
cssdagent / cssdmonitor: responsible for ora.cssd(for ocssd.bin) and ora.cssdmonitor(for cssdmonitor itself)


Useful Documentation from oracle for RAC 
==========================================
How to Troubleshoot Grid Infrastructure Startup Issues [ID 1050908.1]
What to Do if 11gR2 Clusterware is Unhealthy [ID 1068835.1]
How to Proceed from Failed 11gR2 Grid Infrastructure (CRS) Installation [ID 942166.1]
11gR2 Clusterware and Grid Home - What You Need to Know [ID 1053147.1]
How to recreate OCR/Voting disk accidentally deleted [ID 399482.1]
How to Proceed from Failed 11gR2 Grid Infrastructure (CRS) Installation [ID 942166.1]
Troubleshooting 11.2 Grid Infastructure Installation Root.sh Issues [ID 1053970.1]
How to Troubleshoot Connectivity Issue with 11gR2 SCAN Name [ID 975457.1]

Advertisements
Categories: Uncategorized

11gR2 Grid Infrastructure Offline Targets

August 20, 2010 Leave a comment

Installation of Oracle 11gR2 Grid Infrastructure (formally clusterware) installation, will notice that serveral resources are offline.

expected behavior refer to metalink 1068835.1.

ora.oc4j refers to Database Workload Management(DBWLM) which is unavailable

ora.gsd and ora.hpc1.gsd refers to 9i instances which are none running

Categories: Oracle Rac

Oracle Enterprise Manager Grid Control is reporting a Service Staus alert of “Service is Down.”.

August 18, 2010 2 comments

The problem is being caused by the service being registered into table sys.service$ on target database but not marked as deleted afterwards. To get rid of it just run command:

SQL> execute dbms_service.delete_service('service_name');
Categories: Oracle Rac

validate database and Rman Backups

August 15, 2010 Leave a comment

To  validate all datafiles and control files (and the server parameter file if one is in use),execute the following command at the RMAN prompt:

RMAN> VALIDATE DATABASE

For example, you can validate that all database files and archived logs
can be backed up by running a command as shown in the following example.
This command checks for physical corruptions only.

rman> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

To check for logical corruptions in addition to physical corruptions,
run the following variation of the preceding command:

rman> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

Categories: Oracle

Improvements in the wait model 10g

August 11, 2010 Leave a comment
  • V$EVENT_NAME
  • V$SESSION
  • V$SESSION_WAIT

The new views include:

  • V$ACTIVE_SESSION_HISTORY
  • V$SESSION_WAIT_HISTORY
  • V$SESS_TIME_MODEL
  • V$SYS_TIME_MODEL
  • V$SYSTEM_WAIT_CLASS
  • V$SESSION_WAIT_CLASS
  • V$EVENT_HISTOGRAM
  • V$FILE_HISTOGRAM
  • V$TEMP_HISTOGRAM

Examples

Categories: Oracle

Gathering statistics with DBMS_STATS procedures

August 11, 2010 Leave a comment

Gather stats using the DBMS_STATS package.  The package can also be used to modify , view , export , import and delete statistics

Collect statistics on Table level


exec dbms_stats.gather_table_stats ( -
     ownname          => 'SCOTT', -
     tabname          => 'EMP', -
     estimate_percent => dbms_stats.auto_sample_size, -
     method_opt       => 'for all columns size auto', -
     cascade          => true, -
     degree           => 5 -
)
<strong>/
</strong><span style="color: #000000;">PL/SQL procedure successfully completed.</span>

<span style="color: #000000;">

 

Collect Statsitics on Schema Level

 

exec dbms_stats.gather_schema_stats ( -
     ownname          => 'SCOTT', -
     options          => 'GATHER', -
     estimate_percent => dbms_stats.auto_sample_size, -
     method_opt       => 'for all columns size auto', -
     cascade          => true, -
     degree           => 5 -
)
<strong>/
</strong><span style="color: #000000;">PL/SQL procedure successfully completed.</span>

<span style="color: #000000;">

 

options

gather Reanalyzes the whole schema
gather empty Only analyzes tables that have no existing statistics
gather stale Only reanalyzes tables with more than 10% modifications (inserts, updates, deletes).
gather auto Reanalyzes objects which currently have no statistics and objects with stale statistics (Using gather auto is like combining gather stale and gather empty.)

useful Views

  • USER_TAB_COL_STATISTICS
  • USER_PART_COL_STATISTICS
  • USER_SUBPART_COL_STATISTICS
  • 
    select
    COLUMN_NAME, NUM_DISTINCT, HISTOGRAM, NUM_BUCKETS,
    to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
    from user_tab_col_statistics
    where table_name='SKEWED_DATA'
    
    

    if you need to know more about method_opt good site to look at is

    http://structureddata.org/2008/10/14/dbms_stats-method_opt-and-for-all-indexed-columns/

    Categories: Oracle

    Real Application Clusters (RAC) Pocket Reference Guide

    August 10, 2010 Leave a comment
    Categories: Oracle Rac