Wednesday, July 31, 2013

11gR2 Clusterware and Grid Home - What You Need to Know

1050908.1 - troubleshoot grid startup issue
http://oracledbazone.wordpress.com/2012/07/17/useful-11gr2-rac-commands/
11gR2 Clusterware and Grid Home - What You Need to Know (Doc ID 1053147.1)


-- check  cluster name
   export GRID_HOME=/ora01/grid/11.2.0.2/grid
   export DB_HOME=/ora01/oracle/product/11.2.0.2/db
 
   $GRID_HOME/bin/cemutlo -n
 
--  Check Clusterware Resources status
   ps -ef |egrep "crsd.bin|ocssd.bin|evmd.bin|oprocd"
   ps -ef|grep d.bin
   ps -ef | egrep 'init|d.bin'
 
   cluvfy comp -list
 
   crsctl check crs
   $GRID_HOME/bin/crsctl stat res -t                 #all
   $GRID_HOME/bin/crsctl stat res -t -init           #only initialize
   $GRID_HOME/bin/crsctl stat res -p                 #long format
   $GRID_HOME/bin/crsctl query css votedisk
   $GRID_HOME/bin/ocrcheck
   $GRID_HOME/bin/oifcfg getif
   $GRID_HOME/bin/crsctl check crs
   crsctl check has
   crsctl query crs activeversion
   crsctl check cluster -all
   crs_stat -t -v


--  To find out config  information
     srvctl config network
     srvctl config nodeapps
     srvctl config asm
     srvctl config listener
     srvctl config scan
     srvctl config scan_listener
     $GRID_HOME/bin/srvctl config nodeapps -a
     $GRID_HOME/bin/srvctl config scan
     $GRID_HOME/bin/srvctl config network
     $GRID_HOME/bin/srvctl config asm -a
     $GRID_HOME/bin/srvctl config listener -l <listener-name> -a
     $DB_HOME/bin/srvctl config database -d <dbname> -a
     $DB_HOME/bin/srvctl config service -d <dbname> -s <service-name> -v

--  To find out non-grid status
     srvctl status nodeapps
     srvctl status asm
     srvctl status listener
     srvctl status scan
     srvctl status scan_listener
crs_stat -t -v
 
-- to start all resource (CRS, ASM , Listener and Databases)
   cd /ora01/grid/11.2.0.2/grid/bin
   ./crsctl start resource -all
   ./crsctl stop has ( will stop on node1 and move vip to node2)
   ./crs_stop -all ( will stop on both nodes)
   ./crsctl stop cluster -n node_name ( will stop asm, crs)
   ./crsctl stop crs ( will stop asm, crs)
 
   srvctl start cvu
   srvctl start oc4j
   srvctl status cvu
   alternate command
   crsctl start resource ora.cvu
   crsctl status resource ora.cvu



 -- start / stop non-rac
   # appsdbaworkshop.blogspot.com

    crsctl 11.2.0.2 to start/stop/check on standalone node

    crsctl config has
    crsctl stop has
    crsctl start has
    crsctl status resource
    crs_stop -all
    crs_start -all
 
   -- start resource manually
   crsctl start resource ora.cvu -n <nodename>
   crsctl start res ora.crsd -init
 
-- check network

  1. Find out Subnet ID from oifcfg from grid

    cd /ora01/grid/11.2.0.2/grid/bin
   
    oifcfg iflist -p -n
    1st col - network adapter name
    2nd col - subnet id
    3rd col - public/private
    4th col - netmask

  2. from root
     # ifconfig -a

  3. Subnet Info in Oracle Clusterware - OCR
     cd /ora01/grid/11.2.0.2/grid/bin
     oifcfg getif

-- check cluster nodes
   olsnodes -n
   olsnodes -c
   olsnodes -i  -s -n

-- OCR
   ocrcheck

-- Check voting disk
   crsctl query css votedisk

- check VIP
   srvctl status nodeapps
   srvctl start nodeapps
   crsctl stat res -t

-- check SCAN
   cluvfy comp scan -verbose
   srvctl config scan
   srvctl status scan

 
   srvctl config scan_listener
   srvctl status scan_listener
   lsnrctl status LISTENER_SCAN1
   lsnrctl service LISTENER_SCAN
   show parameter remote_listener
 
   crsctl stat res -w "TYPE = ora.scan_listener.type"
 
-- check listener
   srvctl status listener

-- check HAIP
   crsctl stat res -init -w "TYPE = ora.haip.type"
   oifcfg iflist -p -n
     will show   "bond1  169.254.0.0  UNKNOWN  255.255.0.0" for HAIP
 
   col host_name format a10
   sqlplus
     SELECT a.host_name, a.instance_name, b.name, b.ip_address
     FROM gv$cluster_interconnects b, gv$instance a
     WHERE a.inst_id=b.inst_id
     ORDER BY 1,2,3;
 
-- collect config
$GRID_HOME/bin/crsctl stat res -t
$GRID_HOME/bin/crsctl stat res -p
$GRID_HOME/bin/crsctl query css votedisk
$GRID_HOME/bin/ocrcheck
$GRID_HOME/bin/oifcfg getif
$GRID_HOME/bin/srvctl config nodeapps -a
$GRID_HOME/bin/srvctl config scan
$GRID_HOME/bin/srvctl config asm -a
$GRID_HOME/bin/srvctl config listener -l <listener-name> -a
$DB_HOME/bin/srvctl config database -d <dbname> -a
$DB_HOME/bin/srvctl config service -d <dbname> -s <service-name> -v

 
-- steps to  stop ( asm, instance, nodeapps, in 11gr2)
   $ srvctl stop instance -d RACDB -n racnode1
   $ srvctl stop vip -n racnode1 -f

--  check if the CRS/OHAS & services are enabled to autostart
   # $GRID_HOME/bin/crsctl config crs
   if not then enable
   # $GRID_HOME/bin/crsctl enable crs

--  shutdown crs services
   # $GRID_HOME/bin/crsctl stop crs
 
-- verify
   $GRID_HOME/bin/crsctl status resource -t

-- Change IP Interconnect address

-- Change VIP


-- get database name
   $ORACLE_HOME/bin/srvctl status home -o $ORACLE_HOME -s /tmp/oracle_home.stat

-- start databases using new command
   $ORACLE_HOME/bin/srvctl start home -o $ORACLE_HOME -s /tmp/oracle_home.stat

-- stop databases
   $ORACLE_HOME/bin/srvctl stop home -o $ORACLE_HOME -s /tmp/oracle_home.stat


  # http://surachartopun.com/2009/10/how-to-create-oracle-service-on-11gr2.html

  lnx20012@mzmd:/ora01/oracle $ cat /opt/oracle/admin/scripts/gridstat
     #!/usr/bin/ksh
     #
     # Check 11G RAC database Up/Down Status script
     #
     # Description:
     #    - Returns formatted version of crs_stat -t, in tabular
     #      format, with the complete rsc names and filtering keywords
     #   - The argument, $RSC_KEY, is optional and if passed to the script, will
     #     limit the output to HA resources whose names match $RSC_KEY.
     # Requirements:
     #   - $ORA_CRS_HOME should be set in your environment
     # Modifications
     #  RLM 5/2010 - use oraenv to set the environment, grid must be in oratab
     #             - check if search argument is null and substitute .* to create a valid syntax for Solaris
     ORACLE_SID=grid;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES
     # If search argument is null, substitute .*, meaning match anything
     if [[ -z $1 ]]; then
       RSC_KEY='.*'
     else
       RSC_KEY=$1
     fi
     #QSTAT=-u
     QSTAT=-l
     AWK=/bin/awk
     $ORACLE_HOME/bin/crs_stat $QSTAT | $AWK \
       'BEGIN {printf "%-45s %-10s %-18s\n", "HA Resource", "Target", "State";
               printf "%-45s %-10s %-18s\n", "-----------", "------", "-----";
               FS="="; state = 0 }
       $1~/NAME/ && $2~/'$RSC_KEY'/ { appname=$2; state=1}
       state == 0 {next}
       $1~/TARGET/ && state == 1 {apptarget = $2; state=2}
       $1~/STATE/ && state == 2 {appstate = $2; state=3}
       state == 3 {printf "%-45s %-10s %-18s\n", appname, apptarget, appstate; state=0}'

Check the status of the cluster

Check the status of the cluster

[oracle@rac1 ~]$ 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

[oracle@rac1 ~]$ crsctl check cluster
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

[oracle@rac1 ~]$ crsctl check cluster -all
**************************************************************
rac1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

Check the status of the services on the cluster

[oracle@rac1 ~]$ crsctl status resource -t
——————————————————————————–
NAME TARGET STATE SERVER STATE_DETAILS
——————————————————————————–
Local Resources
——————————————————————————–
ora.DATA.dg
ONLINE ONLINE rac1
ora.LISTENER.lsnr
ONLINE ONLINE rac1
ora.asm
ONLINE ONLINE rac1
ora.gsd
OFFLINE OFFLINE rac1
ora.net1.network
ONLINE ONLINE rac1
ora.ons
ONLINE ONLINE rac1
ora.registry.acfs
ONLINE ONLINE rac1
——————————————————————————–
Cluster Resources
——————————————————————————–
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE rac1
ora.cvu
1 ONLINE ONLINE rac1
ora.oc4j
1 ONLINE ONLINE rac1
ora.rac1.vip
1 ONLINE ONLINE rac1
ora.rac2.vip
1 OFFLINE OFFLINE
ora.scan1.vip
1 ONLINE ONLINE rac1
ora.testrac.db
1 OFFLINE OFFLINE Instance Shutdown

Stop crs on all nodes (only one node up in this example)

[root@rac1 bin]# ./crsctl stop cluster -all
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rac1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘rac1'
CRS-2673: Attempting to stop ‘ora.registry.acfs’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.cvu’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.oc4j’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘rac1'
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.rac1.vip’ on ‘rac1'
CRS-2677: Stop of ‘ora.LISTENER_SCAN1.lsnr’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.scan1.vip’ on ‘rac1'
CRS-2677: Stop of ‘ora.scan1.vip’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.rac1.vip’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.cvu’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.registry.acfs’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.oc4j’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘rac1'
CRS-2677: Stop of ‘ora.asm’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘rac1'
CRS-2677: Stop of ‘ora.ons’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘rac1'
CRS-2677: Stop of ‘ora.net1.network’ on ‘rac1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘rac1' has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.asm’ on ‘rac1'
CRS-2677: Stop of ‘ora.asm’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘rac1'
CRS-2677: Stop of ‘ora.evmd’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘rac1'
CRS-2677: Stop of ‘ora.cssd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘rac1'
CRS-2677: Stop of ‘ora.diskmon’ on ‘rac1' succeeded

Stop crs on specifc node

[root@rac1 bin]# ./crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on ‘rac1'
CRS-2673: Attempting to stop ‘ora.crsd’ on ‘rac1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on ‘rac1'
CRS-2673: Attempting to stop ‘ora.cvu’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.LISTENER.lsnr’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.registry.acfs’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.DATA.dg’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.LISTENER_SCAN1.lsnr’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.oc4j’ on ‘rac1'
CRS-2677: Stop of ‘ora.LISTENER.lsnr’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.rac1.vip’ on ‘rac1'
CRS-2677: Stop of ‘ora.rac1.vip’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.LISTENER_SCAN1.lsnr’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.scan1.vip’ on ‘rac1'
CRS-2677: Stop of ‘ora.scan1.vip’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.cvu’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.oc4j’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.registry.acfs’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.DATA.dg’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.asm’ on ‘rac1'
CRS-2677: Stop of ‘ora.asm’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.ons’ on ‘rac1'
CRS-2677: Stop of ‘ora.ons’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.net1.network’ on ‘rac1'
CRS-2677: Stop of ‘ora.net1.network’ on ‘rac1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on ‘rac1' has completed
CRS-2677: Stop of ‘ora.crsd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.drivers.acfs’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.mdnsd’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.crf’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.ctssd’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.evmd’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.asm’ on ‘rac1'
CRS-2677: Stop of ‘ora.asm’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.cluster_interconnect.haip’ on ‘rac1'
CRS-2677: Stop of ‘ora.evmd’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.crf’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.mdnsd’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.cluster_interconnect.haip’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.drivers.acfs’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.ctssd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.cssd’ on ‘rac1'
CRS-2677: Stop of ‘ora.cssd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.gipcd’ on ‘rac1'
CRS-2673: Attempting to stop ‘ora.diskmon’ on ‘rac1'
CRS-2677: Stop of ‘ora.gipcd’ on ‘rac1' succeeded
CRS-2673: Attempting to stop ‘ora.gpnpd’ on ‘rac1'
CRS-2677: Stop of ‘ora.diskmon’ on ‘rac1' succeeded
CRS-2677: Stop of ‘ora.gpnpd’ on ‘rac1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on ‘rac1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

Disable CRS from starting on reboot

[root@rac1 bin]# ./crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.

Enable CRS to start on reboot

[root@rac1 bin]# ./crsctl enable has
CRS-4622: Oracle High Availability Services autostart is enabled.

Start crs on specifc node

[root@rac1 bin]# ./crsctl start has
CRS-4123: Oracle High Availability Services has been started.

Check database status

[oracle@rac1 ~]$ srvctl status database -d RACDB
Instance RACDB1 is running on node rac1
Instance RACDB2 is running on node rac2

Stop an instance

[oracle@rac1 ~]$ srvctl stop instance -i RACDB1 -d RACDB
[oracle@rac1 ~]$ srvctl status database -d RACDB
Instance RACDB1 is not running on node rac1
Instance RACDB2 is running on node rac2

Start an instance

[oracle@rac1 ~]$ srvctl start instance -i RACDB1 -d RACDB
[oracle@rac1 ~]$ srvctl status database -d RACDB
Instance RACDB1 is running on node rac1
Instance RACDB2 is running on node rac2

Stop the database

[oracle@rac1 ~]$ srvctl stop database -d RACDB
[oracle@rac1 ~]$ srvctl status database -d RACDB
Instance RACDB1 is not running on node rac1
Instance RACDB2 is not running on node rac2

Start the database

[oracle@rac1 ~]$ srvctl start database -d RACDB
[oracle@rac1 ~]$ srvctl status database -d RACDB
Instance RACDB1 is running on node rac1
Instance RACDB2 is running on node rac2

Stop the listener on a Node

[oracle@rac1 ~]$ srvctl status listener -n rac1
Listener LISTENER is enabled on node(s): rac1
Listener LISTENER is not running on node(s): rac1

Start the listener on a Node

[oracle@rac1 ~]$ srvctl status listener -n rac1
Listener LISTENER is enabled on node(s): rac1
Listener LISTENER is running on node(s): rac1



Check the voting disk

[oracle@rac1 ~]$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
– —– —————– ——— ———
1. ONLINE 91b18024f9674f83bf5117d3c0996a05 (ORCL:VOL1) [DATA]
Located 1 voting disk(s).

Query the network addresses

[oracle@rac1 ~]$ oifcfg getif
eth0 192.168.245.0 global public
eth1 192.168.126.0 global cluster_interconnect

Show OCR backup – the voting file (disk) is also backed up with the OCR backup

[root@rac1 bin]# ./ocrconfig -showbackup

rac1 2012/07/17 07:47:17 /u01/app/11.2.0/grid/cdata/rac-cluster/backup00.ocr

rac1 2012/07/17 03:47:15 /u01/app/11.2.0/grid/cdata/rac-cluster/backup01.ocr

rac1 2012/07/16 23:47:13 /u01/app/11.2.0/grid/cdata/rac-cluster/backup02.ocr

rac1 2012/07/16 19:47:11 /u01/app/11.2.0/grid/cdata/rac-cluster/day.ocr

rac1 2012/07/16 19:47:11 /u01/app/11.2.0/grid/cdata/rac-cluster/week.ocr

rac1 2012/03/23 11:30:19 /u01/app/11.2.0/grid/cdata/rac-cluster/backup_20120323_113019.ocr

Take a manual backup of the OCR

[root@rac1 bin]# ./ocrconfig -manualbackup

rac1 2012/07/17 10:08:28 /u01/app/11.2.0/grid/cdata/rac-cluster/backup_20120717_100828.ocr

rac1 2012/07/17 10:07:39 /u01/app/11.2.0/grid/cdata/rac-cluster/backup_20120717_100739.ocr

rac1 2012/03/23 11:30:19 /u01/app/11.2.0/grid/cdata/rac-cluster/backup_20120323_113019.ocr

Monday, July 01, 2013

ASM Useful commands

-- LUN size

SELECT
      NVL(a.name, '[CANDIDATE]')                       disk_group_name
    , b.path                                           disk_file_path
    , b.total_mb                                       total_mb
    , (b.total_mb - b.free_mb)                         used_mb
    , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used
  FROM
      v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
  ORDER BY
     a.name;

-- will show provisioned dasd too


col DISK_FILE_PATH format a20    
SELECT
      NVL(a.name, '[CANDIDATE]')                       disk_group_name
    , b.path                                           disk_file_path
    , b.total_mb                                       total_mb
    , (b.total_mb - b.free_mb)                         used_mb
  FROM
      v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
  ORDER BY
     a.name;

-- check provisioned dasd using asmcmd

asmcmd >  lsdsk --candidate -p

   
-- ToTal Utlizatization size

 SELECT
      name                                     group_name
    , type                                     type
    , total_mb                                 total_mb
    , (total_mb - free_mb)                     used_mb
    , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
  FROM
      v$asm_diskgroup
  ORDER BY
     name;

-- find flash area usage

select database_name "Database",
         sum(space)/1024/1024 "Size in MB" FROM (
  SELECT
      CONNECT_BY_ROOT db_name as database_name, space
  FROM
      ( SELECT
            a.parent_index       pindex
          , a.name               db_name
          , a.reference_index    rindex
          , f.bytes              bytes
          , f.space              space
          , f.type               type
        FROM
            v$asm_file f RIGHT OUTER JOIN v$asm_alias a
                         USING (group_number, file_number) where GROUP_NUMBER=2
      )
  WHERE type IS NOT NULL
  START WITH (MOD(pindex, POWER(2, 24))) = 0
      CONNECT BY PRIOR rindex = pindex)
group by rollup(database_name)
order by database_name
/