RAC Commands : 1 — Viewing Configuration

In 11gR2

Viewing the configuration of a RAC database

[root@node1 ~]# su - oracle
-sh-3.2$ srvctl config database -d RACDB
Database unique name: RACDB
Database name: RACDB
Oracle home: /u01/app/oracle/rdbms/11.2.0
Oracle user: oracle
Spfile: +DATA1/RACDB/spfileRACDB.ora
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RACSP
Database instances:
Disk Groups: DATA1,FRA,DATA2
Mount point paths:
Services: MY_RAC_SVC
Type: RAC
Database is policy managed

So, we see that :
a) The database name is RACDB
b) It is a Policy Managed database (not Administrator Managed)
c) It is dependent on 3 ASM Disk Groups DATA1, DATA2, FRA
d) There is one service called MY_RAC_SVC configured
e) The database is in the RACSP server pool
f) The database is configured to be Auto-started when Grid Infrastructure starts

Viewing the configuration of a RAC service

-sh-3.2$ srvctl config service -d RACDB -s MY_RAC_SVC
Service name: MY_RAC_SVC
Service is enabled
Server pool: RACSP
Cardinality: UNIFORM
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: NONE
Service is enabled on nodes:
Service is disabled on nodes:

So, we see that :
a) The service name is MY_RAC_SVC
b) The UNIFORM cardinality means that it is to run on all active nodes in the server pool
c) The server-side connection load balancing goal is LONG (for long running sessions)

Viewing the configuration of Server Pools

-sh-3.2$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Candidate server names:
Server pool name: RACSP
Importance: 0, Min: 0, Max: 2
Candidate server names:

So we see that :
a) The RACSP server pool is the only created (named) server pool
b) This server pool has a max of 2 nodes

Passed the 11g RAC and Grid Expert Exam

I passed the 11g RAC and Grid Expert Exam yesterday.
For those who are interested :

You must absolutely read the documentation on ASM, Grid Infrastructure and RAC.

I also recommend 3 books
1) Pro Oracle Database 11g RAC on Linux — by Steve Shaw and Martin Bach [Apress Publishing]
2) Oracle 11g R1/R2 Real Application Clusters Essentials — by Ben Prusinsky and Syed Jaffer Hussain [Packt Publishing]
2) Oracle 11g R1/R2 Real Application Clusters Handbook — by Ben Prusinsky, Guenad Jilveski and Syed Jaffer Husssain [Packt Publishing]
3) Oracle Database 11g Release 2 High Availability — by Scott Jesse, Bill Burton and Bryan Vongray [Oracle Press]

The 11gR2 Grid and RAC Accelerated training at Oracle University is also recommended but expensive.

Oracle startup and shutdown script: very famous (dbora) ;)

This is the most complete dbora script i came up with, i used linux subsystem and pid file for complete status workings… please have a look and n point out if there is anything to be done further in it…   #!/bin/sh # chkconfig: 345 55 25 # /etc/init.d/dbora # Description: Starts and stops the Oracle database and listener # Author: ConArtist # Date: 21/04/14 # source function library .

Which query takes the most CPU in oracle database?

Here is a simple sql query which will show which query is misbehaving and consuming the most CPU usage SELECT ss.username,        ss.OSUSER,        ss.TERMINAL,        se.SID,        VALUE / 100 cpu,        sq.SQL_TEXT   FROM v$session ss, v$sesstat se, v$statname sn, v$sql sq  WHERE se.STATISTIC# = sn.STATISTIC#    AND NAME LIKE '%CPU used by this session%'    AND se.SID = ss.SID    AND ss.STATUS = 'ACTIVE'    AND ss.username IS NOT NULL    AND ss.SQL_ID = sq.SQL_ID    AND VALUE / 100 >= 1  ORDER BY VALUE DESC; And when you see the top CPU using process, monitor its activity through this query: SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,        SID,          MACHINE,        REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,       ltrim(to_char(FLOOR(SES.LAST_CALL_ET/3600), '09')) || ':'        || ltrim(to_char(FLOOR(MOD(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'