ASM Report Disk Group

SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off

COLUMN disk_group_name FORMAT a20 HEAD ‘Disk Group Name’
COLUMN disk_file_path FORMAT a17 HEAD ‘Path’
COLUMN disk_file_name FORMAT a20 HEAD ‘File Name’
COLUMN disk_file_fail_group FORMAT a20 HEAD ‘Fail Group’
COLUMN total_mb FORMAT 999,999,999 HEAD ‘File Size (MB)’
COLUMN used_mb FORMAT 999,999,999 HEAD ‘Used Size (MB)’
COLUMN pct_used FORMAT 999.99 HEAD ‘Pct. Used’

break on report on disk_group_name skip 1

compute sum label “” of total_mb used_mb on disk_group_name
compute sum label “Grand Total: ” of total_mb used_mb on report

SELECT
NVL(a.name, ‘[CANDIDATE]‘) disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, 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
/

Using ASM Command line

From the OS command prompt, type asmcmd, which brings up the ASM command-line prompt:

 

export ORACLE_HOME=$ASM_HOME ;  export ORACLE_SID=+ASM1

[oracle@wygora01 asm]$ asmcmd
ASMCMD> du .
Used_MB      Mirror_used_MB
5151                5151

ASMCMD> asmcmd -p
commands:
——–
cd
du
find
help
ls
lsct
lsdg
mkalias
mkdir
pwd
rm
rmalias

ASMCMD> ls -l
State    Type    Rebal  Unbal  Name
MOUNTED  EXTERN  N      N      BACKUP/
MOUNTED  EXTERN  N      N      DATA/

ASMCMD> cd BACKUP/
ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
Y    WYGPROD/

ASMCMD> cd WYGPROD/
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
N    1_10_647361523.dbf => +BACKUP/WYGPROD/ARCHIVELOG/2008_02_25/thread_1_seq_10.288.647631003
N    1_11_647361523.dbf => +BACKUP/WYGPROD/ARCHIVELOG/2008_02_26/thread_1_seq_11.292.647666319

……………….

Y    ARCHIVELOG/
Y    BACKUPSET/
Y    CONTROLFILE/
Y    DATAFILE/
Y    ONLINELOG/
Y    PARAMETERFILE/
N    spfilewygprod.ora => +BACKUP/WYGPROD/PARAMETERFILE/spfile.261.647361669

ASM Views

V$ASM_ALIAS Shows every alias for every disk group mounted by the ASM instance
V$ASM_CLIENT Shows which database instance(s) are using any ASM disk groups that are being mounted by this ASM instance
V$ASM_DISK Lists each disk discovered by the ASM instance, including disks that are not part of any ASM disk group
V$ASM_DISKGROUP Describes information about ASM disk groups mounted by the ASM instance
V$ASM_FILE Lists each ASM file in every ASM disk group mounted by the ASM instance
V$ASM_OPERATION Like its counterpart, V$SESSION_LONGOPS, it shows each long-running ASM operation in the ASM instance
V$ASM_TEMPLATE Lists each template present in every ASM disk group mounted by the ASM instance
V$ASM_DISKGROUP Shows one row per each ASM disk group that’s mounted by the local ASM instance
V$ASM_DISK Displays one row per each disk in each ASM disk group that are in use by the database instance
V$ASM_CLIENT Lists one row per each ASM instance for which the database instance has any open ASM files

ASM File Names: Example ASM1DG1.ORCL.DATAFILE.TBSASM.257.1
A fully-qualified file name comprises the following five components, and always ends with a special number pair:<group>. The name of the ASM disk group.
<dbname>. The database that owns this ASM file.
<file type>. The Oracle file type (e.g. DATAFILE, ARCHIVELOG, CONTROLFILE).
<tag>. Type-specific information about the file. In this case, it is the corresponding tablespace name for the datafile.
<file>.<incarnation>. A special number pair that ASM generates to insure uniqueness.

Maintain CRS

[oracle@wygora01 ~]$ uptime
11:26:20 up 3 days, 18:40, 6 users, load average: 1.20, 1.07, 1.01

[oracle@wygora01 ~]$ showcrs
HA Resource                                   Target     State
-----------                                   ------     -----
ora.wygora01.ASM1.asm                         ONLINE     ONLINE on wygora01
ora.wygora01.LISTENER_WYGORA01.lsnr           ONLINE     ONLINE on wygora01
ora.wygora01.gsd                              ONLINE     UNKNOWN on wygora01
ora.wygora01.ons                              ONLINE     UNKNOWN on wygora01
ora.wygora01.vip                              ONLINE     ONLINE on wygora01
ora.wygora02.ASM2.asm                         ONLINE     UNKNOWN on wygora02
ora.wygora02.LISTENER_WYGORA02.lsnr           ONLINE     UNKNOWN on wygora02
ora.wygora02.gsd                              ONLINE     UNKNOWN on wygora02
ora.wygora02.ons                              ONLINE     UNKNOWN on wygora02
ora.wygora02.vip                              ONLINE     ONLINE on wygora02
ora.wygprod.db                                ONLINE     ONLINE on wygora01
ora.wygprod.wygprod.cs                        OFFLINE    OFFLINE
ora.wygprod.wygprod.wygprod1.srv              ONLINE     ONLINE on wygora01
ora.wygprod.wygprod.wygprod2.srv              ONLINE     UNKNOWN on wygora02
ora.wygprod.wygprod1.inst                     ONLINE     ONLINE on wygora01
ora.wygprod.wygprod2.inst                     ONLINE     OFFLINE

Oracle support asked me to stop & start nodeapps on wygora01(not wygora02 which restarted)-

“srvctl stop nodeapps -n wygora01″

[oracle@wygora01 ~]$ srvctl stop nodeapps -n wygora01

- export SRVM_TRACE=true

- srvctl start nodeapps -n wygora01

It produced huge

nodeapps.txt

Solution: I was using dbora.sh for staring up oracle instances after a reboot. All services are started up by oracle clusterware so you don’t need dbora.sh at startup. This pulled Listener & ASM services out of “unknown” state but ons,gsd are still reported unknown after a restart and need manual correction.

CRS_STAT Services in UNKNOWN/OFFLINE Status

[oracle@wygora02 ~]$ crs_start ora.wygprod.wygprod2.inst

[oracle@wygora02 ~]$ showcrs
HA Resource                                   Target     State
-----------                                   ------     -----
ora.wygora01.ASM1.asm                         ONLINE     ONLINE on wygora01
ora.wygora01.LISTENER_WYGORA01.lsnr           ONLINE     ONLINE on wygora01
ora.wygora01.gsd                              ONLINE     ONLINE on wygora01
ora.wygora01.ons                              ONLINE     ONLINE on wygora01
ora.wygora01.vip                              ONLINE     ONLINE on wygora01
ora.wygora02.ASM2.asm                         ONLINE     UNKNOWN on wygora02
ora.wygora02.LISTENER_WYGORA02.lsnr           ONLINE     ONLINE on wygora02
ora.wygora02.gsd                              ONLINE     ONLINE on wygora02
ora.wygora02.ons                              ONLINE     ONLINE on wygora02
ora.wygora02.vip                              ONLINE     ONLINE on wygora02
ora.wygprod.db                                ONLINE     UNKNOWN on wygora02
ora.wygprod.wygprod.cs                        ONLINE     UNKNOWN on wygora02
ora.wygprod.wygprod.wygprod1.srv              ONLINE     UNKNOWN on wygora01
ora.wygprod.wygprod.wygprod2.srv              ONLINE     OFFLINE
ora.wygprod.wygprod1.inst                     ONLINE     OFFLINE
ora.wygprod.wygprod2.inst                     ONLINE     OFFLINE

[oracle@wygora02 ~]$ crs_start ora.wygprod.wygprod2.inst
CRS-1028: Dependency analysis failed because of:
‘Resource in UNKNOWN state: ora.wygora02.ASM2.asm’

CRS-0223: Resource ‘ora.wygprod.wygprod2.inst’ has placement error.

[oracle@wygora02 ~]$ crs_stop crs_start ora.wygprod.wygprod2.inst -f
Target set to OFFLINE for `ora.wygprod.wygprod2.inst`
CRS-0210: Could not find resource ‘crs_start’.

[oracle@wygora02 ~]$ crs_stop ora.wygprod.wygprod2.inst -f
`ora.wygprod.wygprod2.inst` is already OFFLINE.
CRS-0216: Could not stop resource ‘ora.wygprod.wygprod2.inst’.

[oracle@wygora02 ~]$ crs_start ora.wygprod.wygprod2.inst
CRS-1028: Dependency analysis failed because of:
‘Resource in UNKNOWN state: ora.wygora02.ASM2.asm’

CRS-0223: Resource ‘ora.wygprod.wygprod2.inst’ has placement error.

[oracle@wygora02 ~]$ crs_start ora.wygora02.ASM2.asm
CRS-1028: Dependency analysis failed because of:
‘Resource in UNKNOWN state: ora.wygora02.ASM2.asm’

CRS-0223: Resource ‘ora.wygora02.ASM2.asm’ has placement error.

[oracle@wygora02 ~]$ crs_stop ora.wygora02.ASM2.asm -f
Attempting to stop `ora.wygora02.ASM2.asm` on member `wygora02`
Stop of `ora.wygora02.ASM2.asm` on member `wygora02` succeeded.
[oracle@wygora02 ~]$ crs_start ora.wygora02.ASM2.asm
Attempting to start `ora.wygora02.ASM2.asm` on member `wygora02`
Start of `ora.wygora02.ASM2.asm` on member `wygora02` succeeded.

[oracle@wygora02 ~]$ showcrs
HA Resource                                   Target     State
-----------                                   ------     -----
ora.wygora01.ASM1.asm                         ONLINE     ONLINE on wygora01
ora.wygora01.LISTENER_WYGORA01.lsnr           ONLINE     ONLINE on wygora01
ora.wygora01.gsd                              ONLINE     ONLINE on wygora01
ora.wygora01.ons                              ONLINE     ONLINE on wygora01
ora.wygora01.vip                              ONLINE     ONLINE on wygora01
ora.wygora02.ASM2.asm                         ONLINE     ONLINE on wygora02
ora.wygora02.LISTENER_WYGORA02.lsnr           ONLINE     ONLINE on wygora02
ora.wygora02.gsd                              ONLINE     ONLINE on wygora02
ora.wygora02.ons                              ONLINE     ONLINE on wygora02
ora.wygora02.vip                              ONLINE     ONLINE on wygora02
ora.wygprod.db                                ONLINE     ONLINE on wygora02
ora.wygprod.wygprod.cs                        ONLINE     UNKNOWN on wygora02
ora.wygprod.wygprod.wygprod1.srv              ONLINE     UNKNOWN on wygora01
ora.wygprod.wygprod.wygprod2.srv              ONLINE     OFFLINE
ora.wygprod.wygprod1.inst                     ONLINE     ONLINE on wygora01
ora.wygprod.wygprod2.inst                     OFFLINE    OFFLINE

[oracle@wygora02 ~]$ crs_start ora.wygprod.wygprod2.inst
Attempting to start `ora.wygprod.wygprod2.inst` on member `wygora02`
Start of `ora.wygprod.wygprod2.inst` on member `wygora02` succeeded.
[oracle@wygora02 ~]$ crs_start ora.wygprod.wygprod.wygprod1.srv
CRS-1028: Dependency analysis failed because of:
‘Resource in UNKNOWN state: ora.wygprod.wygprod.wygprod1.srv’

CRS-0223: Resource ‘ora.wygprod.wygprod.wygprod1.srv’ has placement error.

[oracle@wygora02 ~]$ crs_stop ora.wygprod.wygprod.wygprod1.srv -f
Attempting to stop `ora.wygprod.wygprod.wygprod1.srv` on member `wygora01`
Stop of `ora.wygprod.wygprod.wygprod1.srv` on member `wygora01` succeeded.
[oracle@wygora02 ~]$ crs_start ora.wygprod.wygprod.wygprod1.srv
Attempting to start `ora.wygprod.wygprod.wygprod1.srv` on member `wygora01`
Start of `ora.wygprod.wygprod.wygprod1.srv` on member `wygora01` succeeded.

[oracle@wygora02 ~]$ showcrs
HA Resource                                   Target     State
-----------                                   ------     -----
ora.wygora01.ASM1.asm                         ONLINE     ONLINE on wygora01
ora.wygora01.LISTENER_WYGORA01.lsnr           ONLINE     ONLINE on wygora01
ora.wygora01.gsd                              ONLINE     ONLINE on wygora01
ora.wygora01.ons                              ONLINE     ONLINE on wygora01
ora.wygora01.vip                              ONLINE     ONLINE on wygora01
ora.wygora02.ASM2.asm                         ONLINE     ONLINE on wygora02
ora.wygora02.LISTENER_WYGORA02.lsnr           ONLINE     ONLINE on wygora02
ora.wygora02.gsd                              ONLINE     ONLINE on wygora02
ora.wygora02.ons                              ONLINE     ONLINE on wygora02
ora.wygora02.vip                              ONLINE     ONLINE on wygora02
ora.wygprod.db                                ONLINE     ONLINE on wygora02
ora.wygprod.wygprod.cs                        ONLINE     ONLINE on wygora02
ora.wygprod.wygprod.wygprod1.srv              ONLINE     ONLINE on wygora01
ora.wygprod.wygprod.wygprod2.srv              ONLINE     ONLINE on wygora02
ora.wygprod.wygprod1.inst                     ONLINE     ONLINE on wygora01
ora.wygprod.wygprod2.inst                     ONLINE     ONLINE on wygora02

Manage backup and recovery Voting Disk

Backing up Voting Disks
dd if=voting_disk_name of=backup_file_name

Recovering Voting Disks
dd if=backup_file_name of=voting_disk_name

Changing Voting Disk Configuration after Installing
crsctl add css votedisk path
crsctl delete css votedisk path

Managing Backup and Recovering OCR

Backing up Oracle Cluster Registry
$ocrconfig -showbackup
Normally Cluster registry is backed up at $CRS_Home/cdata/cluster_name

Change backup location using following syntax:
$ocrconfig -backuploc directory name

The Oracle Clusterware automatically creates OCR backups every four hours. At any one time, Oracle always retains the last three backup copies of the OCR. The CRSD process that creates the backups also creates and retains an OCR backup for each full day and at the end of each week.

Restoring the Oracle Cluster Registry on UNIX-Based Systems
- Identify the OCR backups using the “ocrconfig -showbackup”
- Stop Oracle Clusterware on all of the nodes using “init.crs stop”
- Perform the restore ocrconfig -restore file_name(from ocrconfig -showbackup)
- Restart Oracle Clusterware by running “init.crs start”
- verify OCR integrity using “cluvfy comp ocr -n all [-verbose]“

Import/Export Oracle Cluster Registry
“In addition to using the automatically created OCR backup files, you should also export the OCR contents before and after making significant configuration changes, such as adding or deleting nodes from your environment, modifying Oracle Clusterware resources, or creating a database.”
- Locate OCR export file created using “ocrconfig -export file_name”
- Stop Oracle Clusterware on all of the nodes using “init.crs stop”
- Perform the import using “ocrconfig -import file_name”
- Restart Oracle Clusterware by running “init.crs start”
- verify OCR integrity using “cluvfy comp ocr -n all [-verbose]“

CRS didn’t start after reboot

RAC database created & a quick reboot at NODE 2 won’t bring CRS back. ASM & RAC DB will fail with CRS process error message.

 

I noticed CSS and other services were not coming up after a reboot so I have to put dbora  in place to start up these services after restart.

[oracle@wygora02 ~]$ srvctl status nodeapps -n wygora02
VIP is running on node: wygora02
GSD is not running on node: wygora02
Listener is not running on node: wygora02
ONS daemon is not running on node: wygora02

[root@wygora02 ~]# cd /u01/crs/oracle/product/10/crs/bin
[root@wygora02 bin]# ./crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly

[root@wygora02 bin]# ps -ef | grep crs
root      5307 21002  0 16:24 pts/2    00:00:00 grep crs
root      9423     1  0 16:15 ?        00:00:00 /u01/crs/oracle/product/10/crs/bin/crsd.bin reboot
oracle   11791  9333  0 16:16 ?        00:00:00 /u01/crs/oracle/product/10/crs/bin/evmd.bin

[oracle@wygora02 ~]$ $ORACLE_HOME/bin/dbstart
Processing ASM instance “+ASM2″: log file /u01/app/oracle/product/10.2.0/asm/startup.log
Processing Database instance “wygprod2″: log file /u01/app/oracle/product/10.2.0/db_1/startup.log

Stopping crsctl stop crs / crsctl start crs
Disabling crsctl disable crs
This disables CRS / Clusterware services from being started in subsequent reboots.
Enabling crsctl enable crs
Use this to enable CRS / Clusterware to be started in subsequent reboots

Oracle Clusterware Service command

stop the crs stack crsctl stop crs
start the crs stack crsctl start crs

Usage: crsctl check crs – checks the viability of the CRS stack
crsctl check cssd – checks the viability of CSS
crsctl check crsd – checks the viability of CRS
crsctl check evmd – checks the viability of EVM
crsctl set css <parameter> <value> – sets a parameter override
crsctl get css <parameter> – gets the value of a CSS parameter
crsctl unset css <parameter> – sets CSS parameter to its default
crsctl query css votedisk – lists the voting disks used by CSS
crsctl add css votedisk <path> – adds a new voting disk
crsctl delete css votedisk <path> – removes a voting disk
crsctl enable crs – enables startup for all CRS daemons
crsctl disable crs – disables startup for all CRS daemons
crsctl start crs – starts all CRS daemons.
crsctl stop crs – stops all CRS daemons. Stops CRS resources in case of cluster.
crsctl start resources – starts CRS resources.
crsctl stop resources – stops CRS resources.
crsctl debug statedump evm – dumps state info for evm objects
crsctl debug statedump crs – dumps state info for crs objects
crsctl debug statedump css – dumps state info for css objects
crsctl debug log css [module:level]{,module:level} …
- Turns on debugging for CSS
crsctl debug trace css – dumps CSS in-memory tracing cache
crsctl debug log crs [module:level]{,module:level} …
- Turns on debugging for CRS
crsctl debug trace crs – dumps CRS in-memory tracing cache
crsctl debug log evm [module:level]{,module:level} …
- Turns on debugging for EVM
crsctl debug trace evm – dumps EVM in-memory tracing cache
crsctl debug log res <resname:level> turns on debugging for resources
crsctl query crs softwareversion [<nodename>] – lists the version of CRS software installed
crsctl query crs activeversion – lists the CRS software operating version
crsctl lsmodules css – lists the CSS modules that can be used for debugging
crsctl lsmodules crs – lists the CRS modules that can be used for debugging
crsctl lsmodules evm – lists the EVM modules that can be used for debugging

Oracle Clusterware Administration Quick Reference

Sequence of events to bring cluster database back..
1.    Start all services using “start -nodeapps”
2.    Start ASM instnace using “srvctl start asm -n (node)”
3.    Start RAC instances using “srvctl start instance -d (database) -I (instance)”
4.    Finish up by bringing our load balanced/TAF service online “srvctl start service -d orcl -s RAC”

List of nodes and other information for all nodes participating in the cluster:
[oracle@oradb4 oracle]$ olsnodes
oradb4 oradb3 oradb2 oradb1

List all nodes participating in the cluster with their assigned node numbers:
[oracle@oradb4 tmp]$ olsnodes -n
oradb4 1 oradb3 2 oradb2 3 oradb1 4

List all nodes participating in the cluster with the private interconnect assigned to each node:
[oracle@oradb4 tmp]$ olsnodes -p
oradb4 oradb4-priv oradb3 oradb3-priv oradb2 oradb2-priv oradb1 oradb1-pr

Check the health of the Oracle Clusterware daemon processes:
[oracle@oradb4 oracle]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

Query and administer css vote disks :
[root@oradb4 root]# crsctl add css votedisk /u03/oradata/ CssVoteDisk.dbf
Now formatting voting disk: /u03/oradata/CssVoteDisk.dbf Read -1 bytes of 512 at offset 0 in voting device (CssVoteDisk.dbf) successful addition of votedisk /u03/oradata/CssVoteDisk.dbf

For dynamic state dump of the CRS:
[root@oradb4 root]# crsctl debug statedump crs
dumping State for crs objects Dynamic state dump information is appended to the crsd log file located in the $ORA_CRS_HOME/log/oradb4/crsd directory.

Verify the Oracle Clusterware version:
[oracle@oradb4 log]$ crsctl query crs softwareversion
CRS software version on node [oradb4] is [10.2.0.0.0]

Verify the current version of Oracle Clusterware being used:
[oracle@oradb4 log]$ crsctl query crs activeversion
CRS active version on the cluster is [10.2.0.0.0]