RAC Troubleshooting

Some daily Oracle RAC commands
========================================
Doing quicks checks to check the livliness of the RAC
We have gone through this command several times. It resides under the ORACLE_HOME/CRS/bin directory and there are several commands to perform various tasks, such as displaying individual resource and starting and stopping those resources. Anyhow, do the crs --help to get a complete list of each command.
[oracle@vm02 bin]$ crs_stat -t
By checking the status of individual nodes and all the necessary applications, we can see that the VIP, GSD, Listener and the ONS daemons are alive.

[oracle@vm02 bin]$ srvctl status nodeapps -n vm01
VIP is running on node: vm01
GSD is running on node: vm01
Listener is running on node: vm01
ONS daemon is running on node: vm01
[oracle@vm02 bin]$ srvctl status nodeapps -n vm02
VIP is running on node: vm02
GSD is running on node: vm02
Listener is running on node: vm02
ONS daemon is running on node: vm02
Now checking the status of the ASM on both nodes...

[oracle@vm02 bin]$ srvctl status asm -n vm01
ASM instance +ASM1 is running on node vm01.
[oracle@vm02 bin]$ srvctl status asm -n vm02
ASM instance +ASM2 is running on node vm02.
What about the database status?

[oracle@vm02 bin]$ srvctl status database -d esxrac
Instance esxrac1 is running on node vm01
Instance esxrac2 is running on node vm02

What would be the status of the service that we created at the end of the database installation?
[oracle@vm02 bin]$ srvctl status service -d esxrac
Service fokeserv is running on instance(s) esxrac2, esxrac1

Cluster Status

[oracle@vm02 bin]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
[oracle@vm02 bin]$ ssh vm01

[oracle@vm01 ~]$ crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
[oracle@vm01 ~]$ exit

Here we quickly query the gv$instance cluster view to get the instances, hostname, and status of our node apps.

SQL> select instance_name, host_name, archiver, thread#, status
  2  from gv$instance
  3  /
select file_name, bytes/1024/1024
  2  from dba_data_files
  3  /

Getting the status of all the groups, type, membership (if any)...
SQL> select group#, type, member, is_recovery_dest_file
  2  from v$logfile
  3  order by group#
  4  /
Querying the v$asm_diskgroup view...
select group_number, name,allocation_unit_size alloc_unit_size,state,type,total_mb,usable_file_mb
from v$asm_diskgroup;
Querying v$asm_disk for our volumes (remember the ones we created first on OS level with the asmlib) :
select name, path, header_status, total_mb free_mb, trunc(bytes_read/1024/1024) read_mb, trunc(bytes_written/1024/1024) write_mb
from v$asm_disk;
All datafiles in one go:
SQL> select name from v$datafile
  2  union
  3  select name from v$controlfile
  4  union
  5  select name from v$tempfile
  6  union
  7  select member from v$logfile
  8  /
SQL> select tablespace_name, file_name
  2  from dba_data_files
  3  union
  4  select tablespace_name, file_name
  5  from dba_temp_files
  6  /
This script will give you information of the +ASM1 instance files:
SQL> select group_number, file_number, bytes/1024/1024/1024 GB, type, striped, modification_date
  2   from v$asm_file
  3  where TYPE != 'ARCHIVELOG'
  4  /
SQL> select group_number, file_number, compound_index, incarnation, block_size, bytes/1024/1024/1024 GB, type, striped,
  2  creation_date, modification_date
  3  from v$asm_file
  4  where TYPE != 'ARCHIVELOG'
  5  /