Oracle RAC Interview questions..
1) What are Oracle
Clusterware processes for 10g on Unix and Linux?
Cluster Synchronization
Services (ocssd) —> Manages cluster
node membership and runs as the oracle user; failure of this process results in
cluster restart.
Cluster Ready Services (crsd)
—> The crs process manages cluster
resources (which could be a database, an instance, a service, a Listener, a
virtual IP (VIP) address, an
application process, and so
on) based on the resource's configuration information that is stored in the
OCR. This includes start, stop, monitor and failover operations. This
process runs as the root user
Event manager daemon (evmd) —
> A background process that publishes events that crs creates.
Process Monitor Daemon
(OPROCD) —> This process monitor the cluster and provide I/O fencing. OPROCD
performs its check, stops running, and if the wake up is beyond the expected
time, then OPROCD resets the
processor and reboots the node. An OPROCD failure results in Oracle Clusterware
restarting the node. OPROCD uses the hangcheck timer on Linux
platforms.
RACG (racgmain, racgimon)
—> Extends clusterware to support Oracle-specific requirements and complex
resources. Runs server callout scripts when FAN events occur.
2) What are Oracle database
background processes specific to RAC ?
•LMS—Global Cache Service
Process
•LMD—Global Enqueue Service
Daemon
•LMON—Global Enqueue Service Monitor
•LCK0—Instance Enqueue
Process
To ensure that each Oracle
RAC database instance obtains the block that it needs to satisfy a query or
transaction,
Oracle RAC instances use two
processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES).
The GCS and GES maintain
records of the statuses of each data file and each cached block using a Global
Resource Directory (GRD).
The GRD contents are
distributed across all of the active instances.
3) What are Oracle
Clusterware Components?
Voting Disk —> Oracle RAC
uses the voting disk to manage cluster membership by way of a health check and
arbitrates cluster ownership among the instances in case of network
failures. The voting disk
must reside on shared disk.
Oracle Cluster Registry (OCR)
—> Maintains cluster configuration information as well as configuration
information about any cluster database within the cluster. The OCR must
reside on shared disk that is
accessible by all of the nodes in your cluster
4) How do you troubleshoot node
reboot ?
Please check metalink ...
Note 265769.1 Troubleshooting
CRS Reboots
Note.559365.1 Using Diagwait
as a diagnostic to get more information for diagnosing Oracle Clusterware Node
evictions.
5) How do you backup the OCR
?
There is an automatic backup
mechanism for OCR. The default location is :
$ORA_CRS_HOME\cdata\"clustername"\
To display backups :
#ocrconfig -showbackup
To restore a backup :
#ocrconfig -restore
With Oracle RAC 10g Release 2
or later, you can also use the export command:
#ocrconfig -export -s online,
and use -import option to restore the contents back.
With Oracle RAC 11g Release
1, you can do a manaual backup of the OCR with the command:
# ocrconfig -manualbackup
6) How do you backup voting
disk ?
#dd if=voting_disk_name
of=backup_file_name
7) How do I identify the
voting disk location ?
#crsctl query css votedisk
8) How do I identify the OCR
file location ?
check /var/opt/oracle/ocr.loc
or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck
9) Is ssh required for normal
Oracle RAC operation ?
"ssh" are not
required for normal Oracle RAC operation. However "ssh" should be
enabled for Oracle RAC and patchset installation.
10) What is SCAN?
Single Client Access Name
(SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature
that provides a single name for clients to access an Oracle Database
running in a cluster. The
benefit is clients using SCAN do not need to change if you add or remove nodes
in the cluster.
11) What is the purpose of
Private Interconnect ?
Clusterware uses the private
interconnect for cluster synchronization (network heartbeat) and daemon
communication between the the clustered nodes. This communication is based on
the TCP protocol.
RAC uses the interconnect for
cache fusion (UDP) and inter-process communication (TCP). Cache Fusion is the
remote memory mapping of Oracle buffers, shared between the caches of
participating nodes in the
cluster.
12) Why do we have a Virtual
IP (VIP) in Oracle RAC?
Without using VIPs or FAN,
clients connected to a node that died will often wait for a TCP timeout period
(which can be up to 10 min) before getting an error. As a result, you
don't really have a good HA
solution without using VIPs.
When a node fails, the VIP
associated with it is automatically failed over to some other node and new node
re-arps the world indicating a new MAC address for the IP. Subsequent
packets sent to the VIP go to
the new node, which will send error RST packets back to the clients. This
results in the clients getting errors immediately.
13) What do you do if you see
GC CR BLOCK LOST in top 5 Timed Events in AWR Report?
This is most likely due to a
fault in interconnect network.
Check netstat -s
if you see "fragments
dropped" or "packet reassemblies failed" , Work with your system
administrator find the fault with network.
14) How many nodes are
supported in a RAC Database?
10g Release 2, support 100
nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC
database.
15 )Srvctl cannot start
instance, I get the following error PRKP-1001 CRS-0215, however sqlplus can
start it on both nodes? How do you identify the problem?
Set the environmental
variable SRVM_TRACE to true.. And start the instance with srvctl. Now you will
get detailed error stack.
16) what is the purpose of
the ONS daemon?
The Oracle Notification
Service (ONS) daemon is an daemon started by the CRS clusterware as part of the
nodeapps. There is one ons daemon started per clustered node.
The Oracle Notification
Service daemon receive a subset of published clusterware events via the local
evmd and racgimon clusterware daemons and forward those events to application
subscribers and to the local
listeners.
17) This in order to
facilitate:
a. the FAN or Fast
Application Notification feature or allowing applications to respond to
database state changes.
b. the 10gR2 Load Balancing
Advisory, the feature that permit load balancing accross different rac nodes
dependent of the load on the different nodes. The rdbms MMON is creating
an advisory for distribution
of work every 30seconds and forward it via racgimon and ONS to listeners and
applications.
18) What is the split-brain
scenario?
--> In Oracle RAC,
split-brain is the scenario when one or more nodes updates to the database
files w/o considering the integrity with other nodes. so in that scenario there
is
high possiblity of
compromissing of database integrity and introducing the corruption to the
database.
19) What is the role of
voting disk/file in RAC?
--> In Oracle RAC, voting
disk file is used to determine the state of each nodes in the cluster. Each
node should write heartbeat to the voting disk in predetermine interval i.e.
1 sec, so other nodes in the
the cluster know that the node is alive. If node could not register the
heartbeat to voting disk in stipulated time frame then it should be fence out
from cluster to avoid
split-brain scenario, which might introduce corruption to the database. Oracle
Cluster Synchronization Service Daemon(OCSSD) is responsible to maintain
synchronization of the
cluster using voting disk.
20) 12.What command is used
to find the status of Oracle 10g Clusterware (CRS) and the various components it
manages
(ONS, VIP, listener,
instances, etc.)?
---> $ocrcheck
21) How would you find the
interconnect IP address from any node within an Oracle 10g RAC configuration?
using oifcfg command.
22) What is the Purpose of
the voting disk in Oracle 10g Clusterware?
Voting disk record node
membership information. Oracle Clusterware uses the voting disk to determine
which instances are members of a cluster. The voting disk must reside on a
shared disk. For high
availability, Oracle recommends that you have a minimum of three voting disks.
If you configure a single voting disk, then you should use external mirroring
to provide redundancy. You
can have up to 32 voting disks in your cluster.
23) How many OCR and voting
disks should one have?
For redundancy, one should
have at least two OCR disks and three voting disks (raw disk partitions).
These disk partitions should
be spread across different physical disks.
24) What is TAF? (Transparent
Application Failover)
After an Oracle RAC node
crashes—usually from a hardware failure—all new application transactions are
automatically rerouted to a specified backup node.
The challenge in rerouting is
to not lose transactions that were "in flight" at the exact moment of
the crash.
One of the requirements of
continuous availability is the ability to restart in-flight application
transactions,
allowing a failed node to
resume processing on another server without interruption.
Oracle's answer to application
failover is a new Oracle Net mechanism dubbed Transparent Application Failover.
TAF allows the DBA to
configure the type and method of failover for each Oracle Net client.
25) What is FAN and FCF?
The Fast Connection Failover
(FCF) feature is an Oracle RAC.
Fast Application Notification
(FAN) client implemented through the connection pool.
The feature requires the use
of an Oracle JDBC driver and an Oracle RAC database.
26)
Top 20 Oracle 10g RAC
interview questions
1)What is RAC? How is it
different from standalone database?
2)Benifits of RAC?
3)What is OCR and VOting
Disk?
4)How many OCR and Voting
disk required?
5)What is CRS and what are
the processes and daemons of CRS and their use?
6)Which CRS process starts
first?
7)What is VIP? Why do we use
VIP?
8)What is TAF?
9)What is FAN and FCF?
10)What are the ways to
configure TAF and Load Balancing?
11)When to use -repair
parameter of ocrconfig command?
12)What is the command to
abort the database using srvctl command?
13)What is the meaning of
TARGET and STATUS column in crs_stat command output?
14)What is service? How to
use services to gain maximum use of RAC?
15)What is split Brain
Syndrome? How Oracle Clusterware handles it?
16)What is STONIH algorithm?
17)What is cache fusion?
Which Database background process facilitate it?
18)What is GRD? Where does it
resides?
19)What is the purpose of
voting disk and how does it help?
20)How to check backup
location of OCR disk? How to change it?
RAC/ASM/VOTING DISK Interview
Questions & Answer
Q What is SCAN?
Single Client Access Name
(SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature
that provides a single name for clients to access an Oracle Database
running in a cluster. The
benefit is clients using SCAN do not need to change if you add or remove nodes
in the cluster.
Q what is dynamic remastering
? When will the dynamic remastering happens?
dynamic remastering is
ability to move the ownership of resource from one instance to another instance
in RAC.
dynamic resource remastering
is used to implement for resource affinity for increased performance.
resource affinity optimized
the system in situation where update transactions are being executed in one
instance.
when activity shift to
another instance the resource affinity correspondingly move to another
instance.
If activity is not localized
then resource ownership is hashed to the instance.
In 10g dynamic remastering
happens in file+object level.
the process of remastering is
very stringent.
For one instance should touch
more than 50 times than the other instance in particular period(say 10 mints).
this touch ratio and time can
be tuned by gc_affinity_limit and _gc_affinity_time parameter.
Q why we required to maintain
odd number of voting disks?
Odd number of disk are to
avoid split brain,
When Nodes in cluster can't
talk to each other they run to lock the Voting disk and whoever lock the more
disk will survive,
if disk number are even there
are chances that node might lock 50% of disk (2 out of 4) then how to decide
which node to evict.
whereas when number is odd,
one will be higher than other and each for cluster to evict the node with less
number.
Q How you check the health of
Your RAC Database?
'crsctl' command from root or oracle user can
be used to check the clusterware health But for starting or stopping we have to
use root user or any privilege user.
[oracle@TEST_NODE1 ~]$ crsctl
check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
Q How you check the services
in RAC Node?
We can check the service or start the services
with 'srvctl' command.load balanced/TAF service named RAC online.
[oracle@TEST_NODE1 ~]$ srvctl
start service -d orcl -s RAC
[oracle@TEST_NODE1 ~]$
crsstat
Q If there is some issue with
virtual IP how will you troubleshoot it?How will you change virtual ip?
To change the VIP (virtual IP) on a RAC node,
use the command
[oracle@testnode oracle]$
srvctl modify nodeapps -A new_address
Q How you will backup your
RAC Database?
Backup strategy of RAC Database:
An RAC Database consists of
1)OCR
2)Voting disk
3)Database files,
controlfiles, redolog files & Archive log files
Q Do you have any idea of
load balancing in application?How load balancing is done?
http://practicalappsdba.wordpress.com/category/for-master-apps-dbas/
Q What is RAC?
RAC stands for Real
Application cluster.
It is a clustering solution
from Oracle Corporation that ensures high availability of databases by
providing instance failover, media
failover features.
Q What is RAC and how is it
different from non RAC databases?
RAC stands for Real
Application Cluster,
you have n number of
instances running in their own separate nodes and based on the shared storage.
Cluster is the key component
and is a collection of servers operations as one unit.
RAC is the best solution for
high performance and high availably.
Non RAC databases has single
point of failure in case of hardware failure or server crash.
Q Give the usage of srvctl ?
srvctl start instance -d
db_name -i "inst_name_list" [-o start_options]
srvctl stop instance -d name
-i "inst_name_list" [-o stop_options]
srvctl stop instance -d orcl
-i "orcl3,orcl4" -o immediate
srvctl start database -d name
[-o start_options]
srvctl stop database -d name
[-o stop_options]
srvctl start database -d orcl
-o mount
Q Mention the Oracle RAC
software components ?
Oracle RAC is composed of two
or more database instances.
They are composed of Memory
structures and background processes same as the single instance database.Oracle
RAC instances
use two processes GES(Global
Enqueue Service), GCS(Global Cache Service) that enable cache fusion.Oracle RAC
instances are composed of following background processes:
ACMS—Atomic Controlfile to
Memory Service (ACMS)
GTX0-j—Global Transaction
Process
LMON—Global Enqueue Service
Monitor
LMD—Global Enqueue Service
Daemon
LMS—Global Cache Service
Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management
Processes (RMSn)
RSMN—Remote Slave Monitor
Q What is GRD?
GRD stands for Global
Resource Directory.
The GES and GCS maintains
records of the statuses of each datafile and each cahed block using global
resource directory.This process is
referred to as cache fusion
and helps in data integrity.
Q What are the different
network components are in 10g RAC?
public, private, and vip
components
Private interfaces is for
intra node communication. VIP is all about availability of application.
When a node fails then the
VIP component fail over to some other node, this is
the reason that all
applications should based on vip components means tns entries should have vip
entry in the host list
Q Give Details on ACMS:
ACMS stands for Atomic Controlfile
Memory Service.
In an Oracle RAC environment
ACMS is an agent that ensures a distributed SGA memory update(ie)SGA updates
are globally committed
on success or globally
aborted in event of a failure.
Q What are the major RAC wait
events?
In a RAC environment the
buffer cache is global across all instances in the cluster and hence the
processing differs.
The most common wait events
related to this are gc cr request
and gc buffer busy
GC CR request :the time it
takes to retrieve the data from the remote cache
Reason: RAC Traffic Using
Slow Connection or Inefficient queries (poorly tuned queries will increase the
amount of data blocks requested by an Oracle session.
The more blocks requested
typically means the more often a block will need to be read from a remote
instance via the interconnect.)
GC BUFFER BUSY: It is the
time the remote instance locally spends accessing the requested data block.
Q Give details on GTX0-j
The process provides
transparent support for XA global transactions in a RAC environment.
The database autotunes the
number of these processes based on the workload of XA global
transactions.
Q Give details on LMON
This process monitors global
enques and resources across the cluster and performs global enqueue recovery
operations.
This is called as Global
Enqueue Service Monitor.
Q Give details on LMD
This process is called as
global enqueue service daemon.
This process manages incoming
remote resource requests within each instance.
Q Give details on LMS
This process is called as
Global Cache service process.
This process maintains
statuses of datafiles and each cahed block by recording information in a Global
Resource Dectory
(GRD).This process also
controls the flow of messages to remote instances and manages global data block
access and transmits block images between the buffer caches of different
instances.This processing is
a part of cache fusion feature.
Q Give details on LCK0
This process is called as
Instance enqueue process.
This process manages
non-cache fusion resource requests such as libry and row cache requests.
Q Give details on RMSn
This process is called as
Oracle RAC management process.
These pocesses perform managability
tasks for Oracle RAC.
Tasks include creation of
resources related Oracle RAC when new
instances are added to the
cluster.
Q How to export and import
crs resources while migrating Oracle RAC to new server.
Below script generate svrctl
add script for database, instance, service and 11G listeners from OCR from
current RAC.
Save the result of the script
and run it at new RAC.
for DBNAME in $(srvctl config
database)
do
# Generate DB resource
srvctl config database -d
$DBNAME -a | awk -v dbname="$DBNAME" \
'BEGIN { FS=":" }
$1~/Oracle home/ ||
$1~/ORACLE_HOME/ {dbhome = "-o" $2}
$1~/Spfile/ || $1~/SPFILE/
{spfile = "-p" $2}
$1~/Disk Groups/ {dg =
"-a" $2}
END { if (avail == "-a
") {avail = ""}; printf "%s %s %s %s %s\n",
"srvctl add database -d ", dbname, dbhome, spfile, dg }'
# Generate Instance resource
srvctl status database -d
$DBNAME | awk -v dbname="$DBNAME" \
'$4~/running/ { printf
"%s %s %s %s %s %s\n", "srvctl add instance -d ",dbname,
" -i ", $2 ," -n ", $7 }
$5~/running/ { printf "%s
%s %s %s %s %s \n", "srvctl add instance -d ",dbname, " -i
", $2 ," -n ", $8 }'
# Modify instance for 10G -
ASM dependency
if [ $(echo $ORACLE_HOME |
grep "1020" | wc -l ) -eq 1 ]
then
srvctl status database -d
$DBNAME | awk -v dbname="$DBNAME" \
'$2~/1$/ { printf "%s %s
%s %s %s \n", "srvctl modify instance -d ",dbname, " -i
", $2 ," -s +ASM1" }
$2~/2$/ { printf "%s %s
%s %s %s \n", "srvctl modify instance -d ",dbname, " -i
", $2 ," -s +ASM2" }
$2~/3$/ { printf "%s %s
%s %s %s \n", "srvctl modify instance -d ",dbname, " -i
", $2 ," -s +ASM3" }
$2~/4$/ { printf "%s %s
%s %s %s \n", "srvctl modify instance -d ",dbname, " -i
", $2 ," -s +ASM4" }'
fi
echo "srvctl start
database -d $DBNAME"
# Generate Service resource
snamelist=$(srvctl status
service -d $DBNAME | awk '{print $2}')
for sname in $snamelist
do
srvctl config service -d
$DBNAME -s $sname| awk -v dbname="$DBNAME" -v sname=$sname \
'BEGIN { FS=":"}
$1~/Preferred instances/
{pref = "-r" $2}
$1~/PREF/ {pref =
"-r" $2; sub(/AVAIL/, "", pref) }
$1~/Available instances/
{avail = "-a" $2}
$2~/AVAIL/ {avail =
"-a" $3}
$1~/Failover type/ {ft =
"-e" $2}
$1~/Failover method/ {fm =
"-m" $2}
$1~/Runtime Load Balancing
Goal/ {g = "-B" $2}
END { if (avail == "-a
") {avail = ""}; printf "%s %s %s %s %s %s %s %s %s
%s\n", "srvctl add service -d ",dbname, "-s ", sname,
pref, avail ,ft, fm,g, "-P BASIC"}'
echo "srvctl start
service -d $DBNAME -s $sname"
done
done
# Listener at 11G Home. 10G
listener can't ba added with srvctl.
srvctl config listener | awk
\
'BEGIN { FS=":";
state = 0; }
$1~/Name/ {lname =
"-l" $2; state=1};
$1~/Home/ && state ==
1 {ohome = "-o" $2; state=2;}
$1~/End points/ &&
state == 2 {lport = "-p " $3; state=3;}
state == 3 {if (ohome !=
"-o ") {printf "%s %s %s %s\n", "srvctl add listener
", lname, ohome, lport;} state=0;}'
Q Give details on RSMN
This process is called as
Remote Slave Monitor.
This process manages
background slave process creation and communication on remote instances. This
is a background slave
process.This process performs
tasks on behalf of a co-ordinating process running in another instance.
Q What components in RAC must
reside in shared storage?
All datafiles, controlfiles,
SPFIles, redo log files must reside on cluster-aware shred storage.
Q What is the significance of
using cluster-aware shared storage in an Oracle RAC environment?
All instances of an Oracle
RAC can access all the datafiles,control files, SPFILE's, redolog files when
these files are hosted out of cluster-aware shared storage which are group
of shared disks.
Q Give few examples for
solutions that support cluster storage
ASM(automatic storage
management),raw disk devices,network file system(NFS), OCFS2 and OCFS(Oracle
Cluster Fie systems).
Q What is an interconnect network?
An interconnect network is a
private network that connects all of the servers in a cluster. The interconnect
network uses a switch/multiple switches that only the nodes in the
cluster can access.
Q How can we configure the
cluster interconnect?
Configure User Datagram
Protocol(UDP) on Gigabit ethernet for cluster interconnect.
On unix and linux systems we
use UDP and RDS(Reliable data socket) protocols to be used by
Oracle Clusterware.Windows
clusters use the TCP protocol.
Q Can we use crossover cables
with Oracle Clusterware interconnects?
No, crossover cables are not
supported with Oracle Clusterware intercnects.
Q What is the use of cluster
interconnect?
Cluster interconnect is used
by the Cache fusion for inter instance communication.
Q How do users connect to
database in an Oracle RAC environment?
Users can access a RAC
database using a client/server configuration or through one or more middle
tiers ,
with or without connection
pooling.Users can use oracle services feature
to connect to database.
Q What is the use of a
service in Oracle RAC environment?
Applications should use the
services feature to connect to the Oracle database.Services enable us to define
rules and characteristics to control how users and applications connect
to database instances.
Q What are the
characteristics controlled by Oracle services feature?
The charateristics include a
unique name, workload balancing and failover options,and high availability
characteristics.
Q What enables the load
balancing of applications in RAC?
Oracle Net Services enable
the load balancing of application connections across all of the instances in an
Oracle RAC database.
Q What is a virtual IP
address or VIP?
A virtl IP address or VIP is
an alternate IP address that the client connectins use instead of the standard
public IP address. To configureVIP address, we need to reserve a spare
IP address for each node, and
the IP addresses must use the same subnet as the public network.
Q What is the use of VIP?
If a node fails, then the
node's VIP address fails over to another node on which the VIP address can
accept TCP connections but it cannot accept Oracle connections.
Q Give situations under which
VIP address failover happens
VIP addresses failover
happens when the node on which the VIP address runs fails, all interfaces for
the VIP address fails, all interfaces for the VIP address are disconnected
from the network.
Q What is the significance of
VIP address failover?
When a VIP address failover
happens, Clients that attempt to connect to the VIP address receive a rapid
connection refused error .They don't have to wait for TCP connection
timeout messages.
Q What are the administrative
tools used for Oracle RAC environments?
Oracle RAC cluster can be
administered as a single image using OEM(Enterprise
Manager),SQL*PLUS,Servercontrol(SRVCTL),clusterverificationutility(cvu),DBCA,NETCA
Q How do we verify that RAC
instances are running?
Issue the following query
from any one node connecting through SQL*PLUS.
$connect sys/sys as sysdba
SQL>select * from
V$ACTIVE_INSTANCES;
The query gives the instance
number under INST_NUMBER column,host_:instancename under INST_NAME column.
Q What is FAN?
Fast application Notification
as it abbreviates to FAN relates to the events related to instances,services
and nodes.This is a notification mechanism that Oracle RAc uses to
notify other processes about
the configuration and service level information that includes service status
changes such as,UP or DOWN events.Applications can respond to FAN events
and take immediate action.
Q Where can we apply FAN UP
and DOWN events?
FAN UP and FAN DOWN events
can be applied to instances,services and nodes.
State the use of FAN events
in case of a cluster configuration change?
During times of cluster
configuration changes,Oracle RAC high availability framework publishes a FAN
event immediately when a state change occurs in the cluster.So applications
can receive FAN events and
react immediately.This prevents applications from polling database and
detecting a problem after such a state change.
Q Why should we have seperate
homes for ASm instance?
It is a good practice to have
ASM home seperate from the database hom(ORACLE_HOME).This helps in upgrading
and patching ASM and the Oracle database software independent of each
other.Also,we can deinstall
the Oracle database software independent of the ASM instance.
Q What is the advantage of
using ASM?
Having ASM is the Oracle
recommended storage option for RAC databases as the ASM maximizes performance
by managing the storage configuration across the disks.ASM does this by
distributing the database
file across all of the available storage within our cluster database
environment.
Q What is rolling upgrade?
It is a new ASM feature from
Database 11g.ASM instances in Oracle database 11g release(from 11.1) can be
upgraded or patched using rolling upgrade feature. This enables us to
patch or upgrade ASM nodes in
a clustered environment without affecting database availability.During a
rolling upgrade we can maintain a functional cluster while one or more of
the nodes in the cluster are
running in different software versions.
Q Can rolling upgrade be used
to upgrade from 10g to 11g database?
No,it can be used only for
Oracle database 11g releases(from 11.1).
Q State the initialization
parameters that must have same value for every instance in an Oracle RAC
database
Some initialization
parameters are critical at the database creation time and must have same
values.Their value must be specified in SPFILE or PFILE for every instance.The
list of
parameters that must be
identical on every instance are given below:
ACTIVE_INSTANCE_COUNT
ARCHIVE_LAG_TARGET
COMPATIBLE
CLUSTER_DATABASE
CLUSTER_DATABASE_INSTANCE
CONTROL_FILES
DB_BLOCK_SIZE
DB_DOMAIN
DB_FILES
DB_NAME
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_UNIQUE_NAME
INSTANCE_TYPE (RDBMS or ASM)
PARALLEL_MAX_SERVERS
REMOTE_LOGIN_passWORD_FILE
UNDO_MANAGEMENT
Q What is ORA-00603: ORACLE
server session terminated by fatal error or ORA-29702: error occurred in
Cluster Group Service operation?
RAC node name was listed in
the loopback address...
Q Can the DML_LOCKS and
RESULT_CACHE_MAX_SIZE be identical on all instances?
These parameters can be
identical on all instances only if these parameter values are set to zero.
What two parameters must be
set at the time of starting up an ASM instance in a RAC environment?The
parameters CLUSTER_DATABASE and INSTANCE_TYPE must be set.
Q Mention the components of
Oracle clusterware
Oracle clusterware is made up
of components like voting disk and Oracle Cluster Registry(OCR).
Q What is a CRS resource?
Oracle clusterware is used to
manage high-availability operations in a cluster.Anything that Oracle
Clusterware manages is known as a CRS resource.Some examples of CRS resources
are database,an instance,a
service,a listener,a VIP address,an application process etc.
Q What is the use of OCR?
Oracle clusterware manages
CRS resources based on the configuration information of CRS resources stored in
OCR(Oracle Cluster Registry).
Q How does a Oracle
Clusterware manage CRS resources?
Oracle clusterware manages
CRS resources based on the configuration information of CRS resources stored in
OCR(Oracle Cluster Registry).
Q Name some Oracle
clusterware tools and their uses?
OIFCFG - allocating and
deallocating network interfaces
OCRCONFIG - Command-line tool
for managing Oracle Cluster Registry
OCRDUMP - Identify the
interconnect being used
CVU - Cluster verification
utility to get status of CRS resources
Q What are the modes of
deleting instances from ORacle Real Application cluster Databases?
We can delete instances using
silent mode or interactive mode using DBCA(Database Configuration Assistant).
Q How do we remove ASM from a
Oracle RAC environment?
We need to stop and delete
the instance in the node first in interactive or silent mode.After that asm can
be removed using srvctl tool as follows:
srvctl stop asm -n node_name
srvctl remove asm -n
node_name
We can verify if ASM has been
removed by issuing the following command:
srvctl config asm -n
node_name
Q How do we verify that an
instance has been removed from OCR after deleting an instance?
Issue the following srvctl
command:
srvctl config database -d
database_name
cd CRS_HOME/bin
./crs_stat
Q How do we verify an
existing current backup of OCR?
We can verify the current
backup of OCR using the following command : ocrconfig -showbackup
Q What are the performance
views in an Oracle RAC environment?
We have v$ views that are
instance specific. In addition we have GV$ views called as global views that
has an INST_ID column of numeric data type.GV$ views obtain information from
individual V$ views.
Q What are the types of
connection load-balancing?
There are two types of
connection load-balancing:server-side load balancing and client-side load
balancing.
Q What is the difference
between server-side and client-side connection load balancing?
Client-side balancing happens
at client side where load balancing is done using listener.In case of
server-side load balancing listener uses a load-balancing advisory to redirect
connections to the instance
providing best service.
Q What are the three greatest
benefits that RAC provides??
The three main benefits are
availability, scalability, and the ability to use low cost commodity hardware.
RAC allows an application to scale vertically, by adding CPU, disk and
memory resources to an
individual server. But RAC also provides horizontal scalability, which is
achieved by adding new nodes into the cluster. RAC also allows an organization
to
bring these resources online
as they are needed. This can save a small or midsize organization a lot of
money in the early stages of a project.
In a RAC environment, if a
node in the cluster fails, the application continues to run on the surviving
nodes contained in the cluster. If your application is configured
correctly, most users won't
even know that the node they were running on became unavailable.
Q What are the major RAC wait
events?
In a RAC environment the
buffer cache is global across all instances in the cluster and hence the
processing differs.The most common wait events related to this are gc cr
request
and gc buffer busy
GC CR request: the time it
takes to retrieve the data from the remote cache
Reason: RAC Traffic Using
Slow Connection or Inefficient queries (poorly tuned queries will increase the
amount of data blocks
requested by an Oracle
session. The more blocks requested typically means the more often a block will
need to be read from a remote instance via the interconnect.)
GC BUFFER BUSY: It is the
time the remote instance locally spends accessing the requested data block.
Q What are the different
network components in Oracle 10g RAC?
We have public, private, and
VIP components. Private interfaces is for intra node communication. VIP is all
about availability of application. When a node fails then the VIP
component will fail over to
some other node, this is the reason that all applications should be based on
VIP components. This means that tns
entries should have VIP entry in the
host list.
Q Tune the following RAC
DATABASE (DBNAME=PROD) which is 3 node RAC.
PROD1 PROD2 PROD3
CPU 8 CPU 15 CPU 8
32 GB RAM 12 GB RAM 16 GB RAM
What are you looking for
here? What tuning information do you expect?
It is a 3 node cluster with
different hardware configuration running RAC.
I would put 20% of the memory
for Oracle in each node. So that would mean that the SGA is different in each
of the nodes.
Also since the CPU's are
different PROD2 can have more number of max number of processes as compared to
the rest of them.
But as I said this is just configuration,
this is not tuning. Question is not clear.
Q Write a sample script for
RMAN for the recovery if all the instance are down.(First explain the procedure
how you will restore)
Bring all nodes down.
Start one Node
Restore all datafiles and archive
logs.
Recover 1 Node.
Open the database.
bring other nodes up.
Confirm that all nodes are
operational.
Q. Clients are performing
some operation and suddenly one of the datafile is experiencing problem what do
you do? The cluster is a two node one.
A. Bring the datafile offline
recover the datafile.
Q. How can you connect to a
specific node in a RAC environment?
A. tnsnames.ora ensure that
you have INSTANCE_NAME specified in it.
Q How to move OCR and Voting
disk to new storage device?
Moving OCR
==========
You must be logged in as the
root user, because root owns the OCR files.
Also an ocrmirror must be in place before
trying to replace the OCR device.
Make sure there is a recent
backup of the OCR file before making any changes:
ocrconfig –showbackup
If there is not a recent
backup copy of the OCR file, an export can be taken for the current OCR file.
Use the following command to generate an export of the online OCR file:
In 10.2
# ocrconfig –export -s online
In 11g
# ocrconfig -manualbackup
The new OCR disk must be
owned by root, must be in the oinstall group, and must have permissions set to
640. Provide at least 100 MB disk space for the OCR.
On one node as root run:
# ocrconfig -replace ocr
# ocrconfig -replace
ocrmirror
Now run ocrcheck to verify if
the OCR is pointing to the new file
Moving Voting Disk
==================
Note: crsctl votedisk
commands must be run as root
Shutdown the Oracle
Clusterware (crsctl stop crs as root) on all nodes before making any
modification to the voting disk. Determine the current voting disk location
using:
crsctl query css votedisk
Take a backup of all voting
disk:
dd if=voting_disk_name
of=backup_file_name
To move a Voting Disk,
provide the full path including file name:
crsctl delete css votedisk
–force
crsctl add css votedisk
–force
After modifying the voting
disk, start the Oracle Clusterware stack on all nodes
# crsctl start crs
Verify the voting disk
location using
crsctl query css votedisk
Q What is runfixup.sh script
in Oracle Clusterware 11g release 2 installation
With Oracle Clusterware 11g
release 2, Oracle Universal Installer (OUI) detects when the minimum
requirements for an installation are not met, and creates shell scripts, called
fixup scripts, to finish
incomplete system configuration steps. If OUI detects an incomplete task, then
it generates fixup scripts (runfixup.sh). You can run the fixup script
after you click the Fix and
Check Again Button.
The Fixup script does the
following:
¦ If necessary sets kernel parameters
to values required for successful installation,
including:
– Shared memory parameters.
– Open file descriptor and
UDP send/receive parameters.
¦ Sets permissions on the
Oracle Inventory (central inventory) directory.
¦ Reconfigures primary and
secondary group memberships for the installation
owner, if necessary, for the
Oracle Inventory directory and the operating system
privileges groups.
¦ Sets shell limits if
necessary to required values.
Q When exactly during the
installation process are clusterware components created?
After fulfilling the
pre-installation requirements, the basic installation steps to follow are:
1. Invoke the Oracle
Universal Installer (OUI)
2. Enter the different
information for some components like:
- name of the cluster
- public and private node
names
- location for OCR and Voting
Disks
- network interfaces used for
RAC instances
-etc.
3. After the Summary screen,
OUI will start copying under the $CRS_HOME (this is the $ORACLE_HOME for Oracle
Clusterware) in the local node the libraries and executables.
- here we will have the
daemons and scripts init.* created and configured properly.
Oracle Clusterware is formed
of several daemons, each one of which have a special function inside the stack.
Daemons are executed via the init.* scripts (init.cssd, init.crsd and
init.evmd).
- note that for CRS only some
client libraries are recreated, but not all the executables (as for the RDBMS).
4. Later the software is
propagated to the rest of the nodes in the cluster and the oraInventory is
updated.
5. The installer will ask to
execute root.sh on each node. Until this step the software for Oracle
Clusterware is inside the $CRS_HOME.
Running root.sh will create
several components outside the $CRS_HOME:
- OCR and VD will be
formated.
- control files (or SCLS_SRC
files ) will be created with the correct contents to start Oracle Clusterware.
These files are used to
control some aspects of Oracle Clusterware like:
- enable/disable processes
from the CSSD family (Eg. oprocd, oslsvmon)
- stop the daemons
(ocssd.bin, crsd.bin, etc).
- prevent Oracle Clusterware
from being started when the machine boots.
- etc.
- /etc/inittab will be
updated and the init process is notified.
In order to start the Oracle
Clusterware daemons, the init.* scripts first need to be run. These scripts are
executed by the daemon init. To accomplish this some entries must be
created in the file
/etc/inittab.
- the different processes
init.* (init.cssd, init.crsd, etc) will start the daemons (ocssd.bin, crsd.bin,
etc). When all the daemons are running then we can say that the
installation was successful
- On 10.2 and later, running
root.sh on the last node in the cluster also will create the nodeapps (VIP, GSD
and ONS). On 10.1, VIPCA is executed as part of the RAC installation.
6. After running root.sh on
each node, we need to continue with the OUI session. After pressing the 'OK'
button OUI will include the information for the public and
cluster_interconnect
interfaces. Also CVU (Cluster Verification Utility) will be executed.
Q What are Oracle Clusterware
processes for 10g on Unix and Linux
Cluster Synchronization
Services (ocssd) — Manages cluster node membership and runs as the oracle user;
failure of this process results in cluster restart.
Cluster Ready Services (crsd)
— The crs process manages cluster resources (which could be a database, an
instance, a service, a Listener, a virtual IP (VIP) address, an
application process, and so
on) based on the resource's configuration information that is stored in the
OCR. This includes start, stop, monitor and failover operations. This
process runs as the root user
Event manager daemon (evmd)
—A background process that publishes events that crs creates.
Process Monitor Daemon
(OPROCD) —This process monitor the cluster and provide I/O fencing. OPROCD
performs its check, stops running, and if the wake up is beyond the expected
time, then OPROCD resets the
processor and reboots the node. An OPROCD failure results in Oracle Clusterware
restarting the node. OPROCD uses the hangcheck timer on Linux
platforms.
RACG (racgmain, racgimon)
—Extends clusterware to support Oracle-specific requirements and complex
resources. Runs server callout scripts when FAN events occur.
Q What are Oracle database
background processes specific to RAC
•LMS—Global Cache Service
Process
•LMD—Global Enqueue Service
Daemon
•LMON—Global Enqueue Service
Monitor
•LCK0—Instance Enqueue
Process
To ensure that each Oracle
RAC database instance obtains the block that it needs to satisfy a query or
transaction, Oracle RAC instances use two processes, the Global Cache
Service (GCS) and the Global
Enqueue Service (GES). The GCS and GES maintain records of the statuses of each
data file and each cached block using a Global Resource Directory
(GRD). The GRD contents are
distributed across all of the active instances.
Q What are Oracle Clusterware
Components
Voting Disk — Oracle RAC uses
the voting disk to manage cluster membership by way of a health check and
arbitrates cluster ownership among the instances in case of network
failures. The voting disk
must reside on shared disk.
Oracle Cluster Registry (OCR)
— Maintains cluster configuration information as well as configuration
information about any cluster database within the cluster. The OCR must reside
on shared disk that is
accessible by all of the nodes in your cluster
Q How do you troubleshoot
node reboot
Please check metalink ...
Note 265769.1 Troubleshooting
CRS Reboots
Note.559365.1 Using Diagwait
as a diagnostic to get more information for diagnosing Oracle Clusterware Node
evictions.
Q How do you backup the OCR
There is an automatic backup
mechanism for OCR. The default location is :
$ORA_CRS_HOME\cdata\"clustername"\
To display backups :
#ocrconfig -showbackup
To restore a backup :
#ocrconfig -restore
With Oracle RAC 10g Release 2
or later, you can also use the export command:
#ocrconfig -export -s online,
and use -import option to restore the contents back.
With Oracle RAC 11g Release
1, you can do a manaual backup of the OCR with the command:
# ocrconfig -manualbackup
Q How do you backup voting
disk
#dd if=voting_disk_name of=backup_file_name
Q How do I identify the
voting disk location
#crsctl query css votedisk
Q How do I identify the OCR
file location
check /var/opt/oracle/ocr.loc
or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck
Q Is ssh required for normal
Oracle RAC operation ?
"ssh" are not
required for normal Oracle RAC operation. However "ssh" should be
enabled for Oracle RAC and patchset installation.
Q What is SCAN?
Single Client Access Name
(SCAN) is s a new Oracle Real Application Clusters (RAC) 11g Release 2 feature
that provides a single name for clients to access an Oracle Database
running in a cluster. The
benefit is clients using SCAN do not need to change if you add or remove nodes
in the cluster.
Q What is the purpose of
Private Interconnect ?
Clusterware uses the private
interconnect for cluster synchronization (network heartbeat) and daemon
communication between the the clustered nodes.
This communication is based
on
the TCP protocol.
RAC uses the interconnect for
cache fusion (UDP) and inter-process communication (TCP). Cache Fusion is the
remote memory mapping of Oracle buffers, shared between the caches of
participating nodes in the
cluster.
Q Why do we have a Virtual IP
(VIP) in Oracle RAC?
Without using VIPs or FAN,
clients connected to a node that died will often wait for a TCP timeout period
(which can be up to 10 min) before getting an error. As a result, you
don't really have a good HA
solution without using VIPs.
When a node fails, the VIP
associated with it is automatically failed over to some other node and new node
re-arps the world indicating a new MAC address for the IP. Subsequent
packets sent to the VIP go to
the new node, which will send error RST packets back to the clients. This results
in the clients getting errors immediately
Q What do you do if you see
GC CR BLOCK LOST in top 5 Timed Events in AWR Report?
This is most likely due to a
fault in interconnect network.
Check netstat -s
if you see "fragments
dropped" or "packet reassemblies failed" , Work with your system
administrator find the fault with network.
Q How many nodes are
supported in a RAC Database?
10g Release 2, support 100
nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC
database.
Q Srvctl cannot start
instance, I get the following error PRKP-1001 CRS-0215, however sqlplus can
start it on both nodes? How do you identify the problem?
Set the environmental
variable SRVM_TRACE to true..
And start the instance with
srvctl.
Now you will get detailed
error stack.
Q what is the purpose of the
ONS daemon?
The Oracle Notification
Service (ONS) daemon is an daemon started by the CRS clusterware as part of the
nodeapps.
There is one ons daemon
started per clustered node.
The Oracle Notification
Service daemon receive a subset of published clusterware events via the local
evmd and racgimon clusterware daemons and forward those events
to application subscribers
and to the local listeners.
This in order to facilitate:
a. the FAN or Fast
Application Notification feature or allowing applications to respond to
database state changes.
b. the 10gR2 Load Balancing
Advisory, the feature that permit load balancing accross different rac nodes
dependent of the load on the different nodes. The rdbms MMON is creating
an advisory for distribution
of work every 30seconds and forward it via racgimon and ONS to listeners and
applications.
Q How do users connect to
database in an Oracle RAC environment?
Users can access a RAC
database using a client/server configuration or through one or more middle
tiers,
with or without connection
pooling. Users can use oracle services feature
to connect to database.
Q What is the use of a
service in Oracle RAC environment?
Applications should use the
services feature to connect to the Oracle database.
Services enable us to define
rules and characteristics to control how users and applications
connect to database
instances.
Q What are the
characteristics controlled by Oracle services feature?
The characteristics include a
unique name,
workload balancing and
failover options, and high availability characteristics.
Q What is a voting disk?
A voting disk is a file that
manages information about node membership.
Q What are the administrative
tasks involved with voting disk?
Following administrative
tasks are performed with the voting disk :
1) Backing up voting disks
2) Recovering Voting disks
3) Adding voting disks
4) Deleting voting disks
5) Moving voting disks
Q How do we backup voting disks?
1) Oracle recommends that you
back up your voting disk after the initial cluster creation and after we
complete any node addition or deletion procedures.
2) First, as root user, stop
Oracle Clusterware (with the crsctl stop crs command) on all nodes. Then,
determine the current voting disk by issuing the following command:
crsctl query votedisk css
3) Then, issue the dd or
ocopy command to back up a voting disk, as appropriate.
Give the syntax of backing up
voting disks:-
On Linux or UNIX systems:
dd if=voting_disk_name
of=backup_file_name
where,
voting_disk_name is the name
of the active voting disk
backup_file_name is the name
of the file to which we want to back up the voting disk contents
On Windows systems, use the
ocopy command:
ocopy voting_disk_name
backup_file_name
Q What is the Oracle
Recommendation for backing up voting disk?
Oracle recommends us to use
the dd command to backup the voting disk with a minimum block size of 4KB.
Q How do you restore a voting
disk?
To restore the backup of your
voting disk, issue the dd or ocopy command for Linux and UNIX systems or ocopy
for Windows systems respectively.
On Linux or UNIX systems:
dd if=backup_file_name
of=voting_disk_name
On Windows systems, use the
ocopy command:
ocopy backup_file_name
voting_disk_name
where,
backup_file_name is the name
of the voting disk backup file
voting_disk_name is the name
of the active voting disk
Q How can we add and remove
multiple voting disks?
If we have multiple voting
disks,
then we can remove the voting
disks and add them back into our environment using the following commands,
where path is the complete
path of the location where the voting disk resides:
crsctl delete css votedisk
path
crsctl add css votedisk path
Q How do we stop Oracle
Clusterware?When do we stop it?
Before making any
modification to the voting disk, as root user,
stop Oracle Clusterware using
the crsctl stop crs command on all nodes.
Q How do we add voting disk?
To add a voting disk, issue
the following command as the root user,
replacing the path variable
with the fully qualified path name for the voting disk we want to add:
crsctl add css votedisk path
-force
Q How do we move voting
disks?
To move a voting disk, issue
the following commands as the root user,
replacing the path variable
with the fully qualified path name for the voting disk we want to move:
crsctl delete css votedisk
path -force
crsctl add css votedisk path
-force
Q How do we remove voting
disks?
To remove a voting disk,
issue the following command as the root user,
replacing the path variable
with the fully qualified path name for the voting disk we want to remove:
crsctl delete css votedisk
path -force
Q What should we do after
modifying voting disks?
After modifying the voting
disk,
restart Oracle Clusterware
using the crsctl start crs command on all nodes,
and verify the voting disk
location using the following command:
crsctl query css votedisk
Q When can we use -force
option?
If our cluster is down, then
we can include the -force option to modify the voting disk configuration,
without interacting with
active Oracle Clusterware daemons. However, using
the -force option while any
cluster node is active may corrupt our configuration.
36) What is split brain
What is split brain ?
In RAC environment, server
nodes communicate with each other using High speed private interconnects
network. A split brain
situation happens when all the links of the private interconnect fail to
respond to
each other but instances are
still up and running. So each instance thinks that the other nodes/instances
are
dead and that it should take
over the ownership.
In split brain situation,
instances independtly access the data and modify the same blocks and the
database
will end up with changed
database overwritten which could lead to data corruption. To avoid this,
various
algorithm are implemented to
handle split brain scenario.
In RAC, the IMR (Instance
Membership Recovery) service is one of the one of the efficient algorithm
used to detect & resolve
the split-brain syndrome. When one instance fails to communicate with other
instances or when one
instance becomes inactive due to any reason and is unable to issue the control
file
heartbeat, the split brain is
detected and the detecting instance will evict the failed instance from the
database.This process is
called node eviction.
37) What does the #!bin/ksh
at the beginning of a shell script do? Why should it be there?
Ans: On the first line of an
interpreter script, the "#!", is the name of a program which should
be used to interpret the contents of the file.
For instance, if the first
line contains "#! /bin/ksh", then the contents of the file are
executed as a korn shell
38) What command is used to
find the status of Oracle 10g Clusterware (CRS) and the various components it
manages
(ONS, VIP, listener,
instances, etc.)?
Ans: $ocrcheck
39) How would you find the
interconnect IP address from any node within an Oracle 10g RAC configuration?
using oifcfg command.
se the oifcfg -help command
to display online help for OIFCFG. The elements of OIFCFG commands, some of
which are
optional depending on the
command, are:
*nodename—Name of the Oracle
Clusterware node as listed in the output from the olsnodes command
*if_name—Name by which the
interface is configured in the system
*subnet—Subnet address of the
interface
*if_type—Type of interface:
public or cluster_interconnect
40) 15.What is the Purpose of
the voting disk in Oracle 10g Clusterware?
Voting disk record node
membership information.
Oracle Clusterware uses the
voting disk to determine which instances are members of a cluster.
The voting disk must reside
on a shared disk. For high availability, Oracle recommends that you have a
minimum of three voting disks.
If you configure a single
voting disk, then you should use external mirroring to provide redundancy.
You can have up to 32 voting
disks in your cluster.
41) Data Guard Protection
Modes :
In some situations, a
business cannot afford to lose data at any cost.
In other situations, some
applications require maximum database performance and can tolerate a potential
loss of data.
Data Guard provides three distinct modes of
data protection to satisfy these varied requirements:
*Maximum Protection—> This
mode offers the highest level of data protection.
Data is synchronously
transmitted to the standby database from the primary database and transactions
are not committed on the primary database unless the redo data is available on
at least one standby database configured in this mode.
If the last standby database configured in
this mode becomes unavailable, processing stops on the primary database.
This mode ensures
no-data-loss, even in the event of multiple failures.
*Maximum Availability—>
This mode is similar to the maximum protection mode, including zero data loss.
However, if a standby
database becomes unavailable (for example, because of network connectivity
problems),
processing continues on the
primary database.
When the fault is corrected,
the standby database is automatically resynchronized with the primary database.
This mode achieves
no-data-loss in the event of a single failure (e.g. network failure, primary
site failure . . .)
*Maximum Performance—>
This mode offers slightly less data protection on the primary database, but
higher performance than maximum availability mode.
In this mode, as the primary database
processes transactions, redo data is asynchronously shipped to the standby
database.
The commit operation of the
primary database does not wait for the standby database to acknowledge receipt
of redo data before completing write operations on the primary database.
If any standby destination
becomes unavailable, processing continues on the primary database and there is
little effect on primary database performance.
42) Connection hanging? what
are the possibilities?
possibilities for Oracle
hanging include:
External issues - The network
being down, Kerberos security issues, SSO or a firewall issue can cause an
Oracle connection to hang.
One way to test this is to
set sqlnet.authentication_services=(none) in your sqlnet.ora file and retry
connecting.
Listener is not running -
Start by checking the listener (check lsnrctl stat). Also, see my notes on
diagnosing Oracle network connectivity issues.
No RAM - Over allocation of
server resources, usually RAM, whereby there is not enough RAM to spawn another
connection to Oracle.
Contention - It is not
uncommon for an end-user session to “hang” when they are trying to grab a
shared data resource that is held by another end-user.
The end-user often calls the
help desk trying to understand why they cannot complete their transaction, and
the Oracle professional must quickly identify the source of the
contention."
43) What is Partition Pruning
?
Partition Pruning: Oracle
optimizes SQL statements to mark the partitions or subpartitions that need to
be accessed and eliminates (prunes) unnecessary partitions or subpartitions
from access by those SQL statements. In other words, partition pruning is the
skipping of unnecessary index and data partitions or subpartitions in a query.
44) FAN in RAC.
With Oracle RAC in place,
database client applications can leverage a number of high availability
features including:
Fast Connection Failover
(FCF): Allows a client application to be immediately notified of a planned or
unplanned database service outage by subscribing to Fast Application
Notification (FAN) events.
Run-time Connection Load-Balancing:
Uses the Oracle RAC Load Balancing Advisory events to distribute work
appropriately across the cluster nodes and to quickly react to changes in
cluster configuration, overworked nodes, or hangs.
Connection Affinity (11g
recommended/required): Routes connections to the same database instance based
on previous connections to an instance to limit performance impacts of
switching between instances.
RAC supports web session and
transaction-based affinity for different client scenarios.
45) Why extra standby redo
log group?
Determine the appropriate
number of standby redo log file groups.
Minimally, the configuration
should have one more standby redo log file group
than the number of online
redo log file groups on the primary database....
(maximum number of logfiles
for each thread + 1) * maximum number of threads
Using this equation reduces
the likelihood that the primary instance's log
writer (LGWR) process will be
blocked because a standby redo log file cannot be
allocated on the standby
database. For example, if the primary database has 2
log files for each thread and
2 threads, then 6 standby redo log file groups
are needed on the standby
database."
I think it says that if you
have groups #1 and #2 on primary and #1, #2 on
standby, and if LGWR on
primary just finished #1, switched to #2, and now it
needs to switch to #1 again
because #2 just became full, the standby must catch
up, otherwise the primary
LGWR cannot reuse #1 because the standby is still
archiving the standby's #1.
Now, if you have the extra #3 on standby, the
standby in this case can
start to use #3 while its #1 is being archived. That
way, the primary can reuse
the primary's #1 without delay.
46) how to take voting disk
backup ?
On 10gR2 RAC used "Can
be done online" for backup voting disk but in 11g you cannot use DD online
(use oracle command to do it).
First, as root user, stop
Oracle Clusterware (with the crsctl stop crs command) on all nodes if you want
to add/restore voting disk.
Then, determine the current
voting disk by issuing the following command:
crsctl query votedisk css
issue the dd or ocopy command
to back up a voting disk, as appropriate.
Give the syntax of backing up
voting disks:-
On Linux or UNIX systems:
dd if=voting_disk_name
of=backup_file_name
where,
voting_disk_name is the name
of the active voting disk
backup_file_name is the name
of the file to which we want to back up the voting disk contents
On Windows systems, use the
ocopy command:
ocopy voting_disk_name
backup_file_name
47) What is the Oracle
Recommendation for backing up voting disk?
Oracle recommends us to use
the dd command to backup the voting disk with aminimum block size of 4KB.
48) How do you restore a
voting disk?
To restore the backup of your
voting disk, issue the dd or ocopy command for Linux and UNIX systems or ocopy
for Windows systems respectively.
On Linux or UNIX systems:
dd if=backup_file_name
of=voting_disk_name
On Windows systems, use the
ocopy command:
ocopy backup_file_name
voting_disk_name
where,
backup_file_name is the name
of the voting disk backup file
voting_disk_name is the name
of the active voting disk
49) How can we add and remove
multiple voting disks?
If we have multiple voting
disks, then we can remove the voting disks and add them back into our
environment using the following commands,
where path is the complete
path of the location where the voting disk resides:
crsctl delete css votedisk
path
crsctl add css votedisk path
50) How do we stop Oracle
Clusterware?When do we stop it?
Before making any
modification to the voting disk, as root user,
stop Oracle Clusterware using
the crsctl stop crs command on all nodes.
51) How do we add voting
disk?
To add a voting disk, issue
the following command as the root user,
replacing the path variable
with the fully qualified path name for the voting disk we want to add:
crsctl add css votedisk path
-force
52) How do we move voting
disks?
To move a voting disk, issue
the following commands as the root user,
replacing the path variable with the fully
qualified path name for the voting disk we want to move:
crsctl delete css votedisk
path -force
crsctl add css votedisk path
-force
53) How do we remove voting
disks?
To remove a voting disk,
issue the following command
as the root user, replacing the path variable with the fully qualified path
name for the voting disk we want to remove:
crsctl delete css votedisk
path -force
54) What should we do after modifying
voting disks?
After modifying the voting
disk,
restart Oracle Clusterware
using the crsctl start crs command on all nodes, and verify the voting disk
location using the following command:
crsctl query css votedisk
55) When can we use -force option?
If our cluster is down, then
we can include the -force option to modify the voting disk configuration,
without interacting with
active Oracle Clusterware daemons.
However, using the -force
option while any cluster node is active may corrupt our configuration.
56) How to find Cluster
Interconnect IP address from Oracle Database ?
Hello, The easiest way to
find the cluster interconnect is to view the “hosts” file. The “hosts” file is
located under: UNIX .......... /etc
Windows ...... C:\WINDOWS\system32\drivers\etc
Following are the ways to
find the cluster interconnect through Oracle database:
1) Query X$KSXPIA
The following query provides
the interconnect IP address registered with Oracle database:
view plaincopy to
clipboardprint?
SQL> select IP_KSXPIA from
x$ksxpia where PUB_KSXPIA = 'N';
IP_KSXPIA
----------------
192.168.10.11
This query should be run on
all instances to find the private interconnect IP address used on their
respective nodes.
2) Query
GV$CLUSTER_INTERCONNECTS view
Querying GV$CLUSTER_INTERCONNECTS
view lists the interconnect used by all the participating instances of the RAC
database.
view plaincopy to
clipboardprint?
SQL> select INST_ID,
IP_ADDRESS from GV$CLUSTER_INTERCONNECTS;
INST_ID IP_ADDRESS
---------- ----------------
1 192.168.10.11
2 192.168.10.12
57) How to Identify master node in RAC ?
Grep crsd log file
# /u1/app/../crsd>grep
MASTER crsd.log | tail -1
(or)
cssd >grep -i "master node" ocssd.log | tail -1
OR You can also use
V$GES_RESOURCE view to identify the master node.
58) how to monitor block
transfer interconnects nodes in rac ?
The v$cache_transfer and v$file_cache_transfer views are used to examine RAC statistics.
The types of blocks that use
the cluster interconnects in a RAC environment are monitored with the v$ cache
transfer series of views:
v$cache_transfer: This view
shows the types and classes of blocks that Oracle transfers over the cluster
interconnect on a per-object basis.
The forced_reads and forced_writes columns can
be used to determine the types of objects the RAC instances are sharing.
Values in the forced_writes
column show how often a certain block type is transferred out of a local buffer
cache due to the current version being requested by another instance.
59) what is global cache
service monitoring?
Global Cache Services (GCS)
Monitoring
The use of the GCS relative
to the number of buffer cache reads, or logical reads can be estimated
by dividing the sum of GCS
requests (global cache gets + global
cache converts + global cache cr blocks
received + global cache current blocks
received )
by the number of logical
reads (consistent gets + db block gets
) for a given statistics collection interval.
A global cache service
request is made in Oracle when a user attempts to access a buffer cache to read
or modify a data block and the block is not in the local cache.
A remote cache read, disk
read or change access privileges is the inevitable result.
These are logical read
related. Logical reads form a superset of the global cache service operations.
Oracle RAC Interview
Questions/FAQs Part1 :
----------------------------------------------
1) What is the use of RAC
ANS:
Oracle RAC allows multiple
computers to run Oracle RDBMS software simultaneously while accessing a single
database, thus providing clustering.
2) What are the prerequisites
for RAC setup ?
3) What are Oracle
Clusterware/Daemon processes and what they do?
Ans:
ocssd, crsd, evmd, oprocd,
racgmain, racgimon
4) What are the special
background processes for RAC (or) what is difference in stand-alone database
& RAC database background processes?
ANS:
DIAG, LCKn, LMD, LMSn, LMON
5) What are structural
changes in 11g R2 RAC?
Ans:
http://satya-racdba.blogspot.com/2010/07/new-features-in-9i-10g-11g-rac.html
Grid & ASM are on one
home,
Voting disk & ocrfile can
be on the ASM,
SCAN,
By using srvctl, we can mange
diskgroups, home, ons, eons, filesystem, srvpool, server, scan, scan_listener,
gns, vip, oc4j,GSD
6) What is cache fusion?
Ans:
Transferring of data between
RAC instances by using private network.
Cache Fusion is the remote
memory mapping of Oracle buffers,
shared between the caches of
participating nodes in the cluster.
When a block of data is read
from datafile by an instance within the cluster and another instance is in need
of the same block,
it is easy to get the block
image from the instance which has the block in its SGA rather than reading from
the disk.
7) What is the purpose of
Private Interconnect?
Ans:
Clusterware uses the private
interconnect for cluster synchronization (network heartbeat) and daemon
communication between the clustered nodes. This communication is based on the
TCP protocol.
RAC uses the interconnect for
cache fusion (UDP) and inter-process communication (TCP).
8) What are the Clusterware
components?
Ans:
Voting Disk - Oracle RAC uses
the voting disk to manage cluster membership by way of a health check and
arbitrates cluster ownership among the instances in case of network failures.
The voting disk must reside on shared disk.
Oracle Cluster Registry (OCR)
- Maintains cluster configuration information as well as configuration
information about any cluster database within the cluster. The OCR must reside
on shared disk that is accessible by all of the nodes in your cluster.
The daemon OCSSd manages the
configuration info in OCR and maintains the changes to cluster in the registry.
Virtual IP (VIP) - When a
node fails, the VIP associated with it is automatically failed over to some
other node
and new node re-arps the
world indicating a new MAC address for the IP.
Subsequent packets sent to
the VIP go to the new node, which will send error RST packets back to the
clients.This results in the clients
getting errors immediately.
crsd – Cluster Resource
Services Daemon
cssd – Cluster
Synchronization Services Daemon
evmd – Event Manager Daemon
oprocd / hangcheck_timer –
Node hang detector
9) What is OCR file?
Ans:
RAC configuration information
repository that manages information about the cluster node list and
instance-to-node mapping information.
The OCR also manages
information about Oracle Clusterware resource profiles for customized
applications.
Maintains cluster
configuration information as well as configuration information about any
cluster database within the cluster.
The OCR must reside on shared
disk that is accessible by all of the nodes in your cluster.
The daemon OCSSd manages the
configuration info in OCR and maintains the changes to cluster in the registry.
10) What is Voting file/disk
and how many files should be there?
Ans:
Voting Disk File is a file on
the shared cluster system or a shared raw device file.
Oracle Clusterware uses the
voting disk to determine which instances are members of a cluster.
Voting disk is akin to the
quorum disk, which helps to avoid the split-brain syndrome.
Oracle RAC uses the voting
disk to manage cluster membership by way of a health check and arbitrates
cluster ownership among the instances
in case of network failures.
The voting disk must reside on shared disk.
11) How to take backup of OCR
file?
Ans:
#ocrconfig -manualbackup
#ocrconfig -export
file_name.dmp
#ocrdump -backupfile my_file
$cp -p -R /u01/app/crs/cdata
/u02/crs_backup/ocrbackup/RAC1
12) How to recover OCR file?
Ans:
#ocrconfig -restore
backup_file.ocr
#ocrconfig -import
file_name.dmp
13) What is local OCR?
Ans:
/etc/oracle/local.ocr
/var/opt/oracle/local.ocr
14) How to check backup of
OCR files?
Ans:
#ocrconfig –showbackup
15) How to take backup of
voting file?
Ans:
dd
if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/vd/VDFile_0
crsctl backup css
votedisk -- from 11g R2
16) How do I identify the voting disk location?
Ans:
# crsctl query css votedisk
17) How do I identify the OCR
file location?
check /var/opt/oracle/ocr.loc
or /etc/ocr.loc
Ans:
# ocrcheck
18) If voting disk/OCR file
got corrupted and don’t have backups, how to get them?
Ans:
We have to install
Clusterware.
19) Who will manage OCR
files?
Ans:
cssd will manage OCR.
20) Who will take backup of OCR files?
Ans:
crsd will take backup.
21) What is split brain
syndrome?
Ans:
Will arise when two or more
instances attempt to control a cluster database.
In a two-node environment,
one instance attempts to manage updates simultaneously while the other instance
attempts to manage updates.
22) What are various IPs used
in RAC? Or How may IPs we need in RAC?
Ans:
Public IP, Private IP,
Virtual IP, SCAN IP
23) What is the use of
virtual IP?
Ans:
When a node fails,
the VIP associated with it is
automatically failed over to some other node and new node re-arps the world
indicating a new MAC address for the IP.
Subsequent packets sent to
the VIP go to the new node, which will send error RST packets back to the
clients.
This results in the clients
getting errors immediately.
Without using VIPs or FAN,
clients connected to a node that died will often wait for a TCP timeout period
(which can be up to 10 min) before getting an error.
As a result, you don't really
have a good HA solution without using VIPs.
24) What is the use of SCAN
IP (SCAN name) and will it provide load balancing?
Ans:
Single Client Access Name
(SCAN) is a new Oracle Real Application Clusters (RAC) 11g Release 2,
feature that provides a
single name for clients to access an Oracle Database running in a cluster.
The benefit is clients using
SCAN do not need to change if you add or remove nodes in the cluster.
25) How many SCAN listeners will be running?
Ans:
Three SCAN listeners only.
26) What is FAN?
Ans:
Applications can use Fast
Application Notification (FAN) to enable rapid failure detection, balancing of
connection pools after failures,
and re-balancing of
connection pools when failed components are repaired.
The FAN process uses system
events that Oracle publishes when cluster servers become unreachable or if
network interfaces fail.
27) What is FCF?
Ans:
Fast Connection Failover
provides high availability to FAN integrated clients, such as clients that use
JDBC, OCI, or ODP.NET.
If you configure the client
to use fast connection failover, then the client automatically subscribes to
FAN events and can react to database UP and DOWN events.
In response, Oracle gives the
client a connection to an active instance that provides the requested database
service.
30) What is TAF and TAF
policies?
Ans:
Transparent Application
Failover (TAF) - A runtime failover for high availability environments,
such as Real Application
Clusters and Oracle Real Application Clusters Guard, TAF refers to the failover
and re-establishment of application-to-service connections.
It enables client
applications to automatically reconnect to the database if the connection
fails, and optionally resume a SELECT statement that was in progress.
This reconnect happens automatically
from within the Oracle Call Interface (OCI) library.
31) What are nodeapps?
Ans:
VIP, listener, ONS, GSD
32) What is gsd (Global
Service Daemon)? [
http://www.datadisk.co.uk/html_docs/rac/rac_cs.htm ]
runs on each node with one
GSD process per node.
The GSD coordinates with the
cluster manager to receive requests from clients such as the DBCA, EM, and the
SRVCTL utility to execute administrative job tasks such as instance startup or
shutdown.
The GSD is not an Oracle
instance background process and is therefore not started with the Oracle
instance
33) How to do load balancing
in RAC?
Client Side Connect-Time Load
Balance:
---------------------------------------
The client load balancing
feature enables clients to randomize connection requests among the listeners.
This is done by client
Tnsnames Parameter: LOAD_BALANCE.
The (load_balance=yes)
instructs SQLNet to progress through the list of listener addresses in the
address_list section of the net service name in a random sequence. When set to
OFF, instructs SQLNet to try the addresses sequentially until one succeeds.
Client Side Connect-Time
failover
-------------------------------------
This is done by client
Tnsnames Parameter: FAILOVER
The (failover=on) enables
clients to connect to another listener if the initial connection to the first
listener fails. Without connect-time failover, Oracle Net attempts a connection
with only one listener.
Server Side Listener
Connection Load Balancing.
-------------------------------------------------
With server-side load
balancing, the listener directs a connection request to the best instance
currently providing the service.
Init parameter
remote_listener should be set. When set, each instance registers with the TNS
listeners running on all nodes within the cluster.
There are two types of
server-side load balancing:
--------------------------------------------------
Load Based — Server side load
balancing redirects connections by default depending on node load. This id
default.
Session Based — Session based
load balancing takes into account the number of sessions connected to each node
and then distributes the connections to balance the number of sessions across
the different nodes.
From 10g release 2 the
service can be setup to use load balancing advisory. This mean connections can
be routed using SERVICE TIME and THROUGHPUT. Connection load balancing means
the goal of a service can be changed, to reflect the type of connections using
the service.
Transparent Application
Failover (TAF) :
----------------------------------------------
Transparent Application
Failover (TAF) is a feature of the Oracle Call Interface (OCI) driver at client
side. It enables the application to automatically reconnect to a database, if
the database instance to which the connection is made fails. In this case, the
active transactions roll back.
Tnsnames Parameter:
FAILOVER_MODE
e.g
(failover_mode=(type=select)(method=basic))
Failover Mode Type can be
Either SESSION or SELECT.
Session failover will have
just the session to failed over to the next available node. With SELECT, the
select query will be resumed.
TAF can be configured with
just server side service settings by using dbms_service package.
Fast Connection Failover
(FCF):
-----------------------------------
Fast Connection Failover is a
feature of Oracle clients that have integrated with FAN HA Events.
Oracle JDBC Implicit
Connection Cache, Oracle Call Interface (OCI), and Oracle Data Provider for
.Net (ODP.Net) include fast connection failover.
With fast connection
failover, when a down event is received, cached connections affected by the
down event are immediately marked invalid and cleaned up.
34) What are the uses of
services? How to find out the services in cluster?
Ans:
Applications should use the
services to connect to the Oracle database.
Services define rules and
characteristics (unique name, workload balancing, failover options, and high
availability) to control how users and applications connect to database
instances.
35) How to find out the nodes
in cluster (or) how to find out the master node?
Ans:
# olsnodes -- Which ever displayed first, is the master
node of the cluster.
select MASTER_NODE from
v$ges_resource;
To find out which is the
master node, you can see ocssd.log file and search for "master node
number".
36) How to know the public
IPs, private IPs, VIPs in RAC?
Ans:
# olsnodes -n -p -i
node1-pub 1
node1-prv node1-vip
node2-pub 2
node2-prv node2-vip
37) What utility is used to
start DB/instance?
Ans:
srvctl start database –d
database_name
srvctl start instance –d
database_name –i instance_name
38) How can you shutdown
single instance?
Ans:
Change cluster_database=false
srvctl stop instance –d
database_name –i instance_name
39) What is HAS (High
Availability Service) and the commands?
Ans:
HAS includes ASM &
database instance and listeners.
crsctl check has
crsctl config has
crsctl disable has
crsctl enable has
crsctl query has
releaseversion
crsctl query has
softwareversion
crsctl start has
crsctl stop has [-f]
40) How many nodes are
supported in a RAC Database?
Ans:
10g Release 2, support 100
nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC
database.
41) What is fencing?
Ans:
I/O fencing prevents updates
by failed instances, and detecting failure and preventing split brain in
cluster.
When a cluster node fails,
the failed node needs to be fenced off from all the shared disk devices or
diskgroups.
This methodology is called
I/O Fencing, sometimes called Disk Fencing or failure fencing.
42) Why Clusterware installed
in root (why not oracle)?
Oracle Clusterware works
closely with the operating system, system administrator access is required for
some of the installation tasks.
In addition, some of the
Oracle Clusterware processes must run as the special operating system user,
root.
43) What are the wait events
in RAC?
Ans:
http://satya-racdba.blogspot.com/2012/10/wait-events-in-oracle-rac-wait-events.html
http://orainternals.wordpress.com/2009/12/23/rac-performance-tuning-understanding-global-cache-performance/
gc buffer busy
gc buffer busy acquire
gc current request
gc cr request
gc cr failure
gc current block lost
gc cr block lost
gc current block corrupt
gc cr block corrupt
gc current block busy
gc cr block busy
gc current block congested
gc cr block congested.
gc current block 2-way
gc cr block 2-way
gc current block 3-way
gc cr block 3-way
(gc current/cr block n-way, n
is number of nodes)
gc current grant 2-way
gc cr grant 2-way
gc current grant busy
gc current grant congested
gc cr grant congested
gc cr multi block read
gc current multi block
request
gc cr multi block request
gc cr block build time
gc current block flush time
gc cr block flush time
gc current block send time
gc cr block send time
gc current block pin time
gc domain validation
gc current retry
ges inquiry response
gcs log flush sync
44) What are the
initialization parameters that must have same value for every instance in an
Oracle RAC database?
Ans:
http://satya-racdba.blogspot.com/2012/09/init-parameters-in-oracle-rac.html
ACTIVE_INSTANCE_COUNT
ARCHIVE_LAG_TARGET
COMPATIBLE
CLUSTER_DATABASE
CLUSTER_DATABASE_INSTANCE
CONTROL_FILES
DB_BLOCK_SIZE
DB_DOMAIN
DB_FILES
DB_NAME
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_UNIQUE_NAME
INSTANCE_TYPE
PARALLEL_MAX_SERVERS
REMOTE_LOGIN_PASSWORD_FILE
UNDO_MANAGEMENT
45) What is the difference
between cr block and cur (current) block?
46) New features in Oracle
Clusterware 12c ?
Oracle Flex ASM - This
feature of Oracle Clusterware 12c claims to reduce per-node overhead of using
ASM instance.
Now the instances can use
remote node ASM for any planned/unplanned downtime. ASM metadata requests can
be converted by non-local instance of ASM.
ASM Disk Scrubbing - From RAC
12c, ASM comes with disk scrubbing feature so that logical corruptions can be
discovered.
Also Oracle 12c ASM can
automatically correct this in normal or high redundancy diskgroups.
Oracle ASM Disk Resync and Rebalance enhancements.
Commands Databases Supporting
To the application Gameing Game What is raid
Application Continuity (AC) -
is transparent to the application and in-case the database or the
infrastructure is unavailable, this new features which work on JDBC drivers,
masks recoverable outages.
This recovers database
session beneath the application so that the outage actually appears to be
delayed connectivity or execution.
Transaction guard
(improvements of Fast Application Notification).
IPv6 Support - Oracle RAC 12c
now supports IPv6 for Client connectivity, Interconnect is still on IPv4.
Per Subnet multiple SCAN -
RAC 12c, per-Subnet multiple SCAN can be configured per cluster.
Each RAC instance opens the
Container Database (CDB) as a whole so that versions would be same for CDB as
well as for all of the Pluggable Databases (PDBs). PDBs are also fully
compatible with RAC.
Oracle installer will run
root.sh script across nodes. We don't have to run the scripts manually on all
RAC nodes.
new "ghctl" command
for patching.
47) New features in Oracle
9i/10g/11g RAC ? [
http://satya-racdba.blogspot.in/2010/07/new-features-in-9i-10g-11g-rac.html ]
Oracle Real Application
Clusters New features
Oracle 9i RAC:
---------------------
OPS (Oracle Parallel Server)
was renamed as RAC
CFS (Cluster File System) was
supported
OCFS (Oracle Cluster File
System) for Linux and Windows
watchdog timer replaced by
hangcheck timer
Oracle 10g R1 RAC :
-------------------
Cluster Manager replaced by
CRS
ASM introduced
Concept of Services expanded
ocrcheck introduced
ocrdump introduced
AWR was instance specific
Oracle 10g R2 RAC :
-------------------
CRS was renamed as
Clusterware
asmcmd introduced
CLUVFY introduced
OCR and Voting disks can be
mirrored
Can use FAN/FCF with TAF for
OCI and ODP.NET
The Waiting The Wait Latest
News Resource Manager Installing Music Downloads
Oracle 11g R1 RAC :
---------------------
--> Oracle 11g RAC
parallel upgrades - Oracle 11g have rolling upgrade features whereby RAC
database can be upgraded without any downtime.
-->Hot patching - Zero
downtime patch application.
-->Oracle RAC load
balancing advisor - Starting from 10g R2 we have RAC load balancing advisor
utility.
11g RAC load balancing
advisor is only available with clients who use .NET, ODBC, or the Oracle Call
Interface (OCI).
-->ADDM for RAC - Oracle
has incorporated RAC into the automatic database diagnostic monitor, for
cross-node advisories.
The script addmrpt.sql run
give report for single instance, will not report all instances in RAC, this is
known as instance ADDM.
But using the new package
DBMS_ADDM, we can generate report for all instances of RAC, this known as
database ADDM.
--> Optimized RAC cache fusion
protocols - moves on from the general cache fusion protocols in 10g to deal
with specific scenarios where the protocols could be further optimized.
--> Oracle 11g RAC Grid
provisioning - The Oracle grid control provisioning pack allows us to
"blow-out" a RAC node without the time-consuming install, using a
pre-installed "footprint".
Oracle 11g R2 RAC :
-----------------------
--> We can store
everything on the ASM. We can store OCR & voting files also on the ASM.
--> ASMCA
--> Single Client Access
Name (SCAN) - eliminates the need to change tns entry when nodes are added to
or removed from the Cluster.
RAC instances register to
SCAN listeners as remote listeners. SCAN is fully qualified name.
Oracle recommends assigning 3
addresses to SCAN, which create three SCAN listeners.
--> Clusterware
components: crfmond, crflogd, GIPCD.
--> AWR is consolidated
for the database.
--> 11g Release 2 Real
Application Cluster (RAC) has server pooling technologies so it’s easier to
provision and manage database grids.
This update is geared toward
dynamically adjusting servers as corporations manage the ebb and flow between
data requirements for datawarehousing and applications.By default, LOAD_BALANCE
is ON.
--> GSD (Global Service
Deamon), gsdctl introduced.
--> GPnP profile.
--> Cluster information in
an XML profile.
--> Oracle RAC OneNode is
a new option that makes it easier to consolidate databases that aren’t mission
critical, but need redundancy.
--> raconeinit - to
convert database to RacOneNode.
--> raconefix - to fix
RacOneNode database in case of failure.
--> racone2rac - to
convert RacOneNode back to RAC.
--> Oracle Restart - the
feature of Oracle Grid Infrastructure's High Availability Services (HAS) to
manage associated listeners, ASM instances and Oracle instances.
--> Oracle Omotion -
Oracle 11g release2 RAC introduces new feature called Oracle Omotion, an online
migration utility.
This Omotion utility will
relocate the instance from one node to another, whenever instance failure
happens.
Omotion utility uses Database
Area Network (DAN) to move Oracle instances.
Database Area Network (DAN)
technology helps seamless database relocation without losing transactions.
--> Cluster Time
Synchronization Service (CTSS) is a new feature in Oracle 11g R2 RAC, which is
used to synchronize time across the nodes of the cluster. --> CTSS will be
replacement of NTP protocol.
--> Grid Naming Service
(GNS) is a new service introduced in Oracle RAC 11g R2. With GNS, Oracle
Clusterware (CRS) can manage Dynamic Host Configuration Protocol --> (DHCP)
and DNS services for the dynamic node registration and configuration.
--> Cluster interconnect:
Used for data blocks, locks, messages, and SCN numbers.
--> Oracle Local Registry
(OLR) - From Oracle 11gR2 "Oracle Local Registry (OLR)" something new
as part of Oracle Clusterware. OLR is node’s local repository, --> similar
to OCR (but local) and is managed by OHASD. It pertains data of local node only
and is not shared among other nodes.
--> Multicasting is
introduced in 11gR2 for private interconnect traffic.
--> I/O fencing prevents
updates by failed instances, and detecting failure and preventing split brain
in cluster. When a cluster node fails, the failed node needs to be fenced off
from all the shared disk devices or diskgroups. This methodology is called I/O
Fencing, sometimes called Disk Fencing or failure fencing.
--> Re-bootless node
fencing (restart)? - instead of fast re-booting the node, a graceful shutdown
of the stack is attempted.
--> Clusterware log
directories: acfs*
--> HAIP (IC VIP).
--> Redundant
interconnects: NIC bonding, HAIP.
--> RAC background
processes: DBRM – Database Resource Manager, PING – Response time agent.
--> Virtual Oracle 11g RAC
cluster - Oracle 11g RAC supports virtualization.
48)
*************************************************************************************************************************************************************
Oracle GoldenGate Interview
Questions/FAQs :
**********************************************
1) What are
processes/components in GoldenGate?
Ans:
Manager, Extract, Replicat,
Data Pump
2) What is Data Pump process
in GoldenGate ?
he Data Pump (not to be
confused with the Oracle Export Import Data Pump) is an optional secondary
Extract group that is created on the source system. When Data Pump is not used,
the Extract process writes to a remote trail that is located on the target
system using TCP/IP. When Data Pump is configured, the Extract process writes
to a local trail and from here Data Pump will read the trail and write the data
over the network to the remote trail located on the target system.
The advantages of this can be
seen as it protects against a network failure as in the absence of a storage
device on the local system, the Extract process writes data into memory before
the same is sent over the network. Any failures in the network could then cause
the Extract process to abort (abend). Also if we are doing any complex data
transformation or filtering, the same can be performed by the Data Pump. It
will also be useful when we are consolidating data from several sources into
one central target where data pump on each individual source system can write
to one common trail file on the target.
3) What is the command line
utility in GoldenGate (or) what is ggsci?
ANS: Golden Gate Command Line
Interface essential commands – GGSCI
GGSCI -- (Oracle) GoldenGate Software Command
Interpreter
4) What is the default port
for GoldenGate Manager process?
ANS:
7809
5) What are important files
GoldenGate?
GLOBALS, ggserr.log, dirprm,
etc ...
6) What is checkpoint table?
ANS:
Create the GoldenGate
Checkpoint table
GoldenGate maintains its own
Checkpoints which is a known position in the trail file from where the Replicat
process will start processing after any kind of error or shutdown.
This ensures data integrity
and a record of these checkpoints is either maintained in files stored on disk
or table in the database which is the preferred option.
7) How can you see GoldenGate
errors?
ANS:
ggsci> VIEW GGSEVT
ggserr.log file
RAC 11gR2 Interview Question
Can I change a node’s
hostname?
Yes, however, the node must
be removed and added back to the cluster with the new name.
How do I define a service for
a Policy-Managed Database?
When you define services for
a policy-managed database, you define the service to a server pool where the
database is running. You can define the service as either UNIFORM (running on
all instances in the server pool) or SINGLETON (running on only one instance in
the server pool). For SINGLETON services, Oracle RAC chooses on which instance
in the server pool the service is active. If that instance fails, then the
service fails over to another instance in the server pool. A service can only
run in one server pool.
Services for
administrator-managed databases continue to be defined by the PREFERRED and
AVAILABLE definitions.
How do I convert from a
Policy-Managed Database to Administrator-Managed Database?
You cannot directly convert a
policy-managed database to an administrator-managed database. Instead, you can
remove the policy-managed configuration using the 'srvctl remove database' and
'srvctl remove service' commands, and then create a new administrator-managed
database with the 'srvctl add database' command.
What is Grid Plug and Play
(GPnP)?
Grid Plug and Play (GPnP)
eliminates per-node configuration data and the need for explicit add and delete
node steps. This allows a system administrator to take a template system image
and run it on a new node with no further configuration. This removes many
manual operations, reduces the opportunity for errors, and encourages
configurations that can be changed easily. Removal of the per-node
configuration makes the nodes easier to replace, because they do not need to
contain individually-managed state.
Grid Plug and Play reduces
the cost of installing, configuring, and managing database nodes by making
their per-node state disposable. It allows nodes to be easily replaced with
regenerated state.
What is a Server Pool?
Server pools enable the
cluster administrator to create a policy which defines how Oracle Clusterware
allocates resources. An Oracle RAC policy-managed database runs in a server
pool. Oracle Clusterware attempts to keep the required number of servers in the
server pool and, therefore, the required number of instances of the Oracle RAC
database. A server can be in only one server pool at any time. However, a
database can run in multiple server pools. Cluster-managed services run in a
server pool where they are defined as either UNIFORM (active on all instances
in the server pool) or SINGLETON (active on only one instance in the server
pool).
You should create redo log
groups only if you are using administrator-managed databases. For
policy-managed databases, increase the cardinality and when the instance
starts, if you are using Oracle Managed Files and Oracle ASM, then Oracle
automatically allocates the thread, redo, and undo.
If you remove an instance
from your Oracle RAC database, then you should disable the instance’s thread of
redo so that Oracle does not have to check the thread during database recovery.
For policy-managed databases,
Oracle automatically allocates the undo tablespace when the instance starts if
you have OMF enabled.
What is Run-Time Connection
Load Balancing?
The run-time connection load
balancing feature enables routing of work requests to an instance that offers
the best performance, minimizing the need to relocate work. To enable and use
run-time connection load balancing, the connection goal must be set to SHORT
and either of the following service-level goals must be set:
· SERVICE_TIME—The Load
Balancing Advisory attempts to direct work requests to instances according to
their response time. Load Balancing Advisory data is based on the elapsed time
for work done by connections using the service, as well as available bandwidth
to the service. This goal is best suited for workloads that require varying
lengths of time to complete, for example, an internet shopping system.
· THROUGHPUT—The Load
Balancing Advisory measures the percentage of the total response time that the
CPU consumes for the service. This measures the efficiency of an instance,
rather than the response time. This goal is best suited for workloads where
each work request completes in a similar amount of time, for example, a trading
system.
Client-side load balancing
balances the connection requests across the listeners by setting the parameter
‘LOAD_BALANCE=ON’ directive. When you set this parameter to ON, Oracle Database
randomly selects an address in the address list, and connects to that node's
listener. This balances client connections across the available SCAN listeners
in the cluster. When clients connect using SCAN, Oracle Net automatically load
balances client connection requests across the three IP addresses you defined
for the SCAN, unless you are using EZConnect.
What are the different types
of Server-Side Connection Load Balancing?
With server-side load
balancing, the SCAN listener directs a connection request to the best instance
currently providing the service by using the load balancing advisory. The two
types of connection load balancing are:
· SHORT—Connections are
distributed across instances based on the amount of time that the service is
used. Use the SHORT connection load balancing goal for applications that have
connections of brief duration. When using connection pools that are integrated
with FAN, set the connection load balancing goal to SHORT. SHORT tells the listener
to use CPU-based statistics.
· LONG—Connections are
distributed across instances based on the number of sessions in each instance,
for each instance that supports the service. Use the LONG connection load
balancing goal for applications that have connections of long duration. This is
typical for connection pools and SQL*Forms sessions. LONG is the default
connection load balancing goal, and tells the listener to use session-based
statistics.
How do I enable the Load
Balancing Advisory (LBA)?
To enable the load balancing
advisory, use the ‘-B’ option when creating or modifying the service using the
‘srvctl’ command.
How does the database
register with the Listener?
When a listener starts after
the Oracle instance starts, and the listener is listed for service
registration, registration does not occur until the next time the Oracle
Database process monitor (PMON) discovery routine starts. By default, PMON
discovery occurs every 60 seconds.
To override the 60-second
delay, use the SQL ‘ALTER SYSTEM REGISTER’ statement. This statement forces the
PMON process to register the service immediately.
If you run this statement
while the listener is up and the instance is already registered, or while the
listener is down, then the statement has no effect.
Can I configure both failure
notifications with Universal Connection Pool (UCP)?
Connection failure
notification is redundant with Fast Connection Failover (FCF) as implemented by
the UCP. You should not configure both within the same application.
Should I configure
Transparent Application Failure (TAF) in my service definition if using Fast
Connection Failure (FCF)?
Do not configure Transparent
Application Failover (TAF) with Fast Connection Failover (FCF) for JDBC clients
as TAF processing will interfere with FAN ONS processing.
Can I use Fast Connection
Failover (FCF) and Transparent Application Failover (TAF) together?
No. Only one of them should
be used at a time.
What is the status of Fast
Connection Failover (FCF) with Universal Connection Pool (UCP)?
FCF is now deprecated along
with the Implicit Connection Caching in favor of using the Universal Connection
Pool (UCP) for JDBC.
Does Fast Connection Failover
(FCF) support planned outages?
FCF does not support planned
outages like service relocation (reference Doc ID: 1076130.1). It is designed
to work for unplanned outages, where a RAC service is preferred on all the
nodes in the cluster and one of the nodes goes down unexpectedly. When a
planned outage like a service relocation is done from one node to the other,
FCF does not work as expected and the result is unpredictable. There is no
solution at present for this. Enhancement request 9495973 has been raised to
address this limitation.
Should I user JDBC Thin
driver or JDBC OCI driver?
Oracle thin JDBC driver is
usually preferred by application developers because it is cross platform and
has no external dependencies. However some applications require the
high-performance, native C-language based Oracle Call Interface (OCI) driver.
This driver is compatible with FCF and can alternatively use Transparent
Application Failover (TAF) which operates at a lower level than FCF and can
automatically resubmit SELECT queries in the event of a node failure. However
for most applications, the ease of deployment of the thin driver with full FCF
support will outweigh any benefits offered by the OCI driver.
How do I subscribe to HA
Events?
If you are using a client
that uses Oracle Streams Advanced Queuing, such as OCI and ODP.NET clients, to
receive FAN events, you must enable the service used by that client to access
the alert notification queue by using the ‘-q’ option via the ‘srvctl’ command.
FAN events are published
using ONS and Oracle Streams Advanced Queuing. The service metrics received
from the Oracle RAC load balancing advisory through FAN events for the service
are automatically placed in the Oracle Streams AQ queue table, ALERT_QUEUE.
Use the following query
against the internal queue table for load balancing advisory FAN events to
monitor load balancing advisory events generated for an instance:-
SET PAGES 60 COLSEP '|' LINES
132 NUM 8 VERIFY OFF FEEDBACK OFF
COLUMN user_data HEADING
"AQ Service Metrics" FORMAT A60 WRAP
BREAK ON service_name SKIP 1
SELECT
TO_CHAR(enq_time, 'HH:MI:SS')
Enq_time, user_data
FROM
sys.sys$service_metrics_tab
ORDER BY 1 ;
What is Connection Affinity?
Connection affinity is a
performance feature that allows a connection pool to select connections that
are directed at a specific Oracle RAC instance. The pool uses run-time
connection load balancing (if configured) to select an Oracle RAC instance to
create the first connection and then subsequent connections are created with an
affinity to the same instance.
What types of affinity does
Universal Connection Pool (UCP) support?
UCP JDBC connection pools
support two types of connection affinity: transaction-based affinity and Web
session affinity.
What is Transaction-Based
Affinity?
Transaction-based affinity is
an affinity to an Oracle RAC instance that can be released by either the client
application or a failure event. Applications typically use this type of
affinity when long-lived affinity to an Oracle RAC instance is desired or when
the cost (in terms of performance) of being redirected to a new Oracle RAC
instance is high. Distributed transactions are a good example of
transaction-based affinity. XA connections that are enlisted in a distributed
transaction keep an affinity to the Oracle RAC instance for the duration of the
transaction. In this case, an application would incur a significant performance
cost if a connection is redirect to a different Oracle RAC instance during the
distributed transaction.
Transaction-based affinity is
strictly scoped between the application/middle-tier and UCP for JDBC;
therefore, transaction-based affinity only requires that the
setFastConnectionFailoverEnabled property be set to true and does not require
complete FCF configuration. In addition, transaction-based affinity does not
technically require run-time connection load balancing. However, it can help
with performance and is usually enabled regardless. If run-time connection load
balancing is not enabled, the connection pool randomly picks connections.
What is Web Session Affinity?
Web session affinity is an
affinity to an Oracle RAC instance that can be released by either the instance,
a client application, or a failure event. The Oracle RAC instance uses a hint
to communicate to a connection pool whether affinity has been enabled or
disabled on the instance. An Oracle RAC instance may disable affinity based on
many factors, such as performance or load. If an Oracle RAC instance can no
longer support affinity, the connections in the pool are refreshed to use a new
instance and affinity is established once again.
Applications typically use
this type of affinity when short-lived affinity to an Oracle RAC instance is
expected or if the cost (in terms of performance) of being redirected to a new
Oracle RAC instance is minimal. For example, a mail client session might use
Web session affinity to an Oracle RAC instance to increase performance and is
relatively unaffected if a connection is redirected to a different instance.
What is recommended for
WebLogic Server?
Oracle recommends using
WebLogic JDBC multi data sources to handle failover instead. While connect-time
failover does not provide the ability to pre-create connections to alternate
Oracle RAC nodes, multi data sources have multiple connections available at all
times to handle failover.
Transparent Application
Failover (TAF) is not supported for any WLS data source. TAF, as delivered via
JDBC is currently not transparent. It is documented to affect some ongoing
query results and PreparedStatements in unpredictable and unrecoverable ways. TAF
JDBC requires specific recovery code at the application level and affects the
integrity of statements that WebLogic might be caching.
Do I still need to backup my
Oracle Cluster Registry (OCR) and Voting Disks?
You no longer have to back up
the voting disk. The voting disk data is automatically backed up in OCR as part
of any configuration change and is automatically restored to any voting disk
added. If all voting disks are corrupted, however, you can restore.
Oracle Clusterware
automatically creates OCR backups every four hours. At any one time, Oracle
Database always retains the last three backup copies of 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. You cannot customize the backup frequencies or
the number of files that Oracle Database retains.
How is DBMS_JOB functionality
affected by RAC?
DBMS jobs can be set to run
either on database (i.e. any active instance), or a specific instance.
What is PARELLEL_FORCE_LOCAL?
By default, the parallel
server processes selected to execute a SQL statement can operate on any or all
Oracle RAC nodes in the cluster. By setting PARALLEL_FORCE_LOCAL to TRUE, the
parallel server processes are restricted to just one node, the node where the
query coordinator resides (the node on which the SQL statement was executed).
However, in 11.2.0.1 when this parameter is set to TRUE the parallel degree
calculations are not being adjusted correctly to only consider the CPU_COUNT
for a single node. The parallel degree will be calculated based on the RAC-wide
CPU_COUNT and not the single node CPU_COUNT. Due to this bug 9671271 it is not
recommended that you set PARALLEL_FORCE_LOCAL to TRUE in 11.2.0.1, instead you
should setup a RAC service to limit where parallel statements can execute.
What is the Service
Management Policy?
When you use automatic
services in an administrator-managed database, during planned database startup,
services may start on the first instances to start rather than their preferred
instances. Prior to Oracle RAC 11 g release 2 (11.2), all services worked as
though they were defined with a manual management policy.
Why does my user appear
across all nodes when querying GV$SESSION when my service does not span all
nodes?
The problem is you are
querying GV$SESSION as the ABC user and this results in the "strange"
behaviour. If you select gv$session, 2 parallel servers are spawned to query
the v$session on each node. This happens as the same user. Hence when you query
gv$session as ABC you are seeing 3 (one real and 2 parallel slaves querying
v$session on each instance). The reason you are seeing 1 on one node and 3 on
the other is the order in which the parallel processes query the v$session.
Take the sys (or any other) user to query the session of ABC and you will not
see this problem.
How does Clustereare startup
with OCR and Voting Disk in ASM?
The startup sequence has been
changed/replaced, now being 2-phased, optimized approach:
Phase I
· OHASD will startup
"local" resources first.
· CSSD uses GPnP profile
which stores location of voting disk so no need to access ASM (voting disk is
stored different within ASM than other files so location is known).
Simultaneously,
· ORAAGENT starts up and ASM
instance is started (subset of information in OCR is stored in OLR, enough to
startup local resources), and ORAROOTAGENT starts CRSD.
So the 1st phase of
Clusterware startup is to essentially start up local resources.
Phase II
· At this point ASM and full
OCR information is available and the node is "joined" to cluster.
What is the Oracle Database
Quality of Service Management?
Oracle Database QoS
Management is an automated, policy-based product that monitors the workload
requests for an entire system. Oracle Database QoS Management manages the
resources that are shared across applications and adjusts the system
configuration to keep the applications running at the performance levels needed
by your business. Oracle Database QoS Management responds gracefully to changes
in system configuration and demand, thus avoiding additional oscillations in
the performance levels of your applications. If you use Oracle Database Quality
of Service Management (Oracle Database QoS Management), then you cannot have
SINGLETON services in a server pool, unless the maximum size of that server
pool is one.
Is a re-link required for the
Clusterware home after an OS upgrade?
In 11.2, there are some
executables in the GRID home that can and should be re-linked after an OS
upgrade. The procedure to do this is:
#> cd GI_HOME/crs/install
#> perl rootcrs.pl -unlock
As the grid infrastructure
for a cluster owner:
$> export
ORACLE_HOME=Grid_home
$> $GI_HOME/bin/relink
As root again:
#> cd GI_HOME/crs/insta
How do I determine the
“Master” node?
For the cluster synchronization
service (CSS), the master can be found by searching
$GI_HOME/log/cssd/ocssd.log. For master of an enqueue resource with Oracle RAC,
you can select from v$ges_resource. There should be a master_node column.
What are the different types
of failover mechanisms available?
· JDBC-THIN driver supports
Fast Connection Failover (FCF)
· JDBC-OCI driver supports
Transparent Application Failover (TAF)
· JDBC-THIN 11gR2 supports
Single Client Access Name (SCAN)
What is recommendation on
type of tablespaces?
You should use locally
managed, auto-allocate tablespaces. With auto-allocate Oracle automatically
grows the size of the extent depending on segment size, available free space in
the tablespace and other factors. The extent size of a segment starts at 64 KB
and grows to 1 MB when the segment grows past 1 MB, and 8 MB once the segment
size exceeds 64 MB. So for a large table, the extent size will automatically
grow to be large. The use of uniform extents is strongly discouraged for two
reasons; space wastage and the impact that wasted space has on scan
performance.
For large partitioned objects
you should use multiple big file tablespaces to avoid file header block
contention during parallel load operations. File header block contention
appears as the ‘gc buffer busy’ enqueue wait event in an AWR report. Checking
the buffer wait statistic will indicate if it is the file header block that is
being contended for.
To evenly distribute a
partitioned table among multiple big file tablespaces use the STORE IN clause.
What is the recommendation on
column statistics?
Prior to loading any data it
is advisable to run all queries against the empty tables to populate or seed
the column usage statistics. Column usage statistics are used during optimizer
statistics gathering to automatically determine which columns require
histograms and the number of buckets that will be used. A column is a candidate
for a histogram if it has been seen in a where clause predicate e.g. an
equality, range, LIKE, etc. and if there is data skew in that column.
How do I size hash
partitions?
Oracle uses a linear hashing
algorithm to create sub-partitions. In order to ensure that the data gets
evenly distributed among the hash partitions the number of hash partitions
should be a power of 2 (i.e. 2 * # of CPU). However, each hash partition should
be at least 16MB in size. Any smaller and they will not have efficient scan
rates with parallel query. If the subpartitions are too small (from the 2 * #
of CPU) considering using a smaller number of partitions (still an even number
of partitions).
è What should be my block
size?
8 KB is the default block
size and is the block size used during all of Oracle's testing. Typically this
is good enough for a data warehouse and transactional systems (good compromise
or sweet spot). By doubling the default block size you can increase the chances
of getting a good compression rate as Oracle applies data compression at the
block level. The more rows in the block the greater the chance Oracle will find
duplicate values within a block. (Reference: Oracle Sun Database Machine
Application Best Practices for Data Warehousing, Doc ID 1094934.1)
What is the guideline on how
to auto-extend data files?
When configuring a file to
auto extend, the size of the extension should cover all disks in the ASM disk
group to optimize balance. For example, with a 4 MB AU size and 128 disks, the
size of the extension should be a multiple of 512MB (4*128).
RAC/ASM/VOTING_DISK interview Q&Answer
What
is ASM?
In
Oracle Database 10g/11g there are two types of instances: database and ASM
instances. The ASM instance, which is generally named +ASM, is started with the
INSTANCE_TYPE=ASM init.ora parameter. This parameter, when set, signals the
Oracle initialization routine to start an ASM instance and not a standard
database instance. Unlike the standard database instance, the ASM instance
contains no physical files; such as logfiles, controlfiles or datafiles, and
only requires a few init.ora parameters for startup.
Upon
startup, an ASM instance will spawn all the basic background processes, plus
some new ones that are specific to the operation of ASM. The STARTUP clauses
for ASM instances are similar to those for database instances. For example,
RESTRICT prevents database instances from connecting to this ASM instance.
NOMOUNT starts up an ASM instance without mounting any disk group. MOUNT option
simply mounts all defined diskgroups
For
RAC configurations, the ASM SID is +ASMx instance, where x represents the
instance number.
What
are the key benefits of ASM?
ASM
provides filesystem and volume manager capabilities built into the Oracle
database kernel. Withthis capability, ASM simplifies storage management tasks,
such as creating/laying out databases and disk space management. Since ASM
allows disk management to be done using familiar create/alter/drop SQL
statements, DBAs do not need to learn a new skill set or make crucial decisions
on provisioning.
The
following are some key benefits of ASM:
· ASM spreads I/O evenly across all available disk
drives to prevent hot spots and maximize performance.
· ASM eliminates the need for over provisioning and
maximizes storage resource utilization facilitating database consolidation.
· Inherent large file support.
· Performs automatic online redistribution after the
incremental addition or removal of storage capacity.
· Maintains redundant copies of data to provide high
availability, or leverages 3rd party RAID functionality.
· Supports Oracle Database as well as Oracle Real
Application Clusters (RAC).
· Capable of leveraging 3rd party multipathing
technologies.
· For simplicity and easier migration to ASM, an Oracle
database can contain ASM and non-ASM files.
· Any new files can be created as ASM files whilst
existing files can also be migrated to ASM.
· RMAN commands enable non-ASM managed files to be
relocated to an ASM disk group.
· Enterprise Manager Database Control or Grid Control
can be used to manage ASM disk and file activities.
Describe
about ASM architecture.
Automatic
Storage Management (ASM) instance
Instance
that manages the diskgroup metadata
Disk
Groups
Logcal
grouping of disks
Determines file mirroring options
ASM Disks
LUNs presented to ASM
ASM Files
Determines file mirroring options
ASM Disks
LUNs presented to ASM
ASM Files
Files
that are stored in ASM disk groups are called ASM files, this includes database
files
Notes:
Many
databases can connect as clients to single ASM instances
ASM
instance name should only be +ASM only
One
diskgroup can serve many databases
How
does database connects to ASM Instance?
The
database communicates with ASM instance using the ASMB (umblicus process)
process. Once the database obtains the necessary extents from extent map, all
database IO going forward is processed through by the database processes,
bypassing ASM. Thus we say ASM is not really in the IO path. So, the question
how do we make ASM go faster…..you don’t have to.
What
init.ora parameters does a user need to configure for ASM instances?
The
default parameter settings work perfectly for ASM. The only parameters needed
for 11g ASM:
• PROCESSES*
• ASM_DISKSTRING*
• ASM_DISKGROUPS
• INSTANCE_TYPE
• PROCESSES*
• ASM_DISKSTRING*
• ASM_DISKGROUPS
• INSTANCE_TYPE
How
does the database interact with the ASM instance and how do I make ASM go
faster?
ASM is
not in the I/O path so ASM does not impede the database file access. Since the
RDBMS instance is performing raw I/O, the I/O is as fast as possible.
Do I
need to define the RDBMS FILESYSTEMIO_OPTIONS parameter when I use ASM?
No.
The RDBMS does I/O directly to the raw disk devices, the
FILESYSTEMIO_OPTIONS parameter is only for filesystems.
Why
Oracle recommends two diskgroups?
Oracle
recommends two diskgroups to provide a balance of manageability, utilization,
and performance.
We
have a 16 TB database. I’m curious about the number of disk groups we should
use; e.g. 1 large disk group, a couple of disk groups, or otherwise?
For
VLDBs you will probably end up with different storage tiers; e.g with some of
our large customers they have Tier1 (RAID10 FC), Tier2 (RAID5 FC), Tier3 (SATA),
etc. Each one of these is mapped to a diskgroup.
We
have a new app and don’t know our access pattern, but assuming mostly
sequential access, what size would be a good AU fit?
For
11g ASM/RDBMS it is recommended to use 4MB ASM AU for disk groups. See Metalink
Note 810484.1
Would
it be better to use BIGFILE tablespaces, or standard tablespaces for ASM?
The
use of Bigfile tablespaces has no bearing on ASM (or vice versa). In fact most
database object related decisions are transparent to ASM.
What
is the best LUN size for ASM?
There
is no best size! In most cases the storage team will dictate to you based on
their standardized LUN size. The ASM administrator merely has to communicate
the ASM Best Practices and application characteristics to storage folks :
• Need equally sized / performance LUNs
• Minimum of 4 LUNs
• The capacity requirement
• The workload characteristic (random r/w, sequential r/w) & any response time SLA
Using this info , and their standards, the storage folks should build a nice LUN group set for you.
• Need equally sized / performance LUNs
• Minimum of 4 LUNs
• The capacity requirement
• The workload characteristic (random r/w, sequential r/w) & any response time SLA
Using this info , and their standards, the storage folks should build a nice LUN group set for you.
In 11g
RAC we want to separate ASM admins from DBAs and create different users and
groups. How do we set this up?
For
clarification
• Separate Oracle Home for ASM and RDBMS.
• RDBMS instance connects to ASM using OSDBA group of the ASM instance.
Thus, software owner for each RDBMS instance connecting to ASM must be
a member of ASM’s OSDBA group.
• Choose a different OSDBA group for ASM instance (asmdba) than for
RDBMS instance (dba)
• In 11g, ASM administrator has to be member of a separate SYSASM group to
separate ASM Admin and DBAs.
• Separate Oracle Home for ASM and RDBMS.
• RDBMS instance connects to ASM using OSDBA group of the ASM instance.
Thus, software owner for each RDBMS instance connecting to ASM must be
a member of ASM’s OSDBA group.
• Choose a different OSDBA group for ASM instance (asmdba) than for
RDBMS instance (dba)
• In 11g, ASM administrator has to be member of a separate SYSASM group to
separate ASM Admin and DBAs.
Can my
RDBMS and ASM instances run different versions?
Yes.
ASM can be at a higher version or at lower version than its client databases.
There’s two
components of compatiblity:
Software compatibility
Diskgroup compatibility attributes:
compatible.asm
compatible.rdbms
components of compatiblity:
Software compatibility
Diskgroup compatibility attributes:
compatible.asm
compatible.rdbms
Where
do I run my database listener from; i.e., ASM HOME or DB HOME?
It is
recommended to run the listener from the ASM HOME. This is particularly
important for RAC env, since the listener is a node-level resource. In this
config, you can create additional [user] listeners from the database homes as
needed.
How do
I backup my ASM instance?
Not
applicable! ASM has no files to backup, as its does not contain
controlfile,redo logs etc.
When should
I use RMAN and when should I use ASMCMD copy?
· RMAN is the recommended and most complete and flexible
method to backup and transport database files in ASM.
ASMCMD copy is good for copying single files
• Supports all Oracle file types
• Can be used to instantiate a Data Guard environment
• Does not update the controlfile
• Does not create OMF files
ASMCMD copy is good for copying single files
• Supports all Oracle file types
• Can be used to instantiate a Data Guard environment
• Does not update the controlfile
• Does not create OMF files
I’m
going to do add disks to my ASM diskgroup, how long will this rebalance take?
· Rebalance time is heavily driven by the three items:
1) Amount of data currently in the diskgroup
2) IO bandwidth available on the server
3) ASM_POWER_LIMIT or Rebalance Power Level
1) Amount of data currently in the diskgroup
2) IO bandwidth available on the server
3) ASM_POWER_LIMIT or Rebalance Power Level
We are
migrating to a new storage array. How do I move my ASM database from storage A
to storage B?
Given
that the new and old storage are both visible to ASM, simply add the new disks
to the ASM disk group and drop the old disks. ASM rebalance will migrate data
online.
Note
428681.1 covers how to move OCR/Voting disks to the new storage array
Is it
possible to unplug an ASM disk group from one platform and plug into a server
on another platform (for example, from Solaris to Linux)?
No.
Cross-platform disk group migration not supported. To move datafiles between
endian-ness platforms, you need to use XTTS, Datapump or Streams.
How
does ASM work with multipathing software?
It
works great! Multipathing software is at a layer lower than ASM, and thus is
transparent.
You may need to adjust ASM_DISKSTRING to specify only the path to the multipathing pseudo devices.
You may need to adjust ASM_DISKSTRING to specify only the path to the multipathing pseudo devices.
Is ASM
constantly rebalancing to manage “hot spots”?
No…No…Nope!!
ASM provides even distribution of extents across all disks in a disk group.
Since each disk will equal number of extents, no single disk will be hotter
than another. Thus the answer NO, ASM does not dynamically move hot spots,
because hot spots simply do not
occur in ASM configurations. Rebalance only occurs on storage configuration changes (e.g. add, drop, or resize disks).
occur in ASM configurations. Rebalance only occurs on storage configuration changes (e.g. add, drop, or resize disks).
What
are the file types that ASM support and keep in disk groups?
Control
files
Flashback logs
Data Pump dump sets
Flashback logs
Data Pump dump sets
Data
files
DB SPFILE
Data Guard configuration
DB SPFILE
Data Guard configuration
Temporary
data files
RMAN backup sets
Change tracking bitmaps
RMAN backup sets
Change tracking bitmaps
Online
redo logs
RMAN data file copies
OCR files
RMAN data file copies
OCR files
Archive
logs
Transport data files
ASM SPFILE
Transport data files
ASM SPFILE
List
Key benefits of ASM?
· Stripes files rather than logical volumes
· Provides redundancy on a file basis
· Enables online disk reconfiguration and dynamic
rebalancing
· Reduces the time significantly to resynchronize a
transient failure by tracking changes while disk is offline
· Provides adjustable rebalancing speed
· Is cluster-aware
· Supports reading from mirrored copy instead of primary
copy for extended clusters
· Is automatically installed as part of the Grid
Infrastructure
What
is ASM Striping?
ASM
can use variable size data extents to support larger files, reduce memory
requirements, and improve performance.
Each
data extent resides on an individual disk.
Data
extents consist of one or more allocation units.
The
data extent size is:
· Equal to AU for the first 20,000 extents (0–19999)
· Equal to 4 × AU for the next 20,000 extents
(20000–39999)
· Equal to 16 × AU for extents above 40,000
ASM stripes
files using extents with a coarse method for load balancing or a fine method to
reduce latency.
· Coarse-grained striping is always equal to the
effective AU size.
· Fine-grained striping is always equal to 128 KB.
How
many ASM Diskgroups can be created under one ASM Instance?
ASM
imposes the following limits:
· 63 disk groups in a storage system
· 10,000 ASM disks in a storage system
· Two-terabyte maximum storage for each ASM disk
(non-Exadata)
· Four-petabyte maximum storage for each ASM disk
(Exadata)
· 40-exabyte maximum storage for each storage system
· 1 million files for each disk group
· ASM file size limits (database limit is 128 TB):
1. External redundancy maximum file size is 140 PB.
2. Normal redundancy maximum file size is 42 PB.
3. High redundancy maximum file size is 15 PB.
What
is a diskgroup?
A disk
group consists of multiple disks and is the fundamental object that ASM
manages. Each disk group contains the metadata that is required for the
management of space in the disk group. The ASM instance manages the metadata
about the files in a Disk Group in the same way that a file system manages
metadata about its files. However, the vast majority of I/O operations do not
pass through the ASM instance. In a moment we will look at how file
I/O works with respect to the ASM instance.
I/O works with respect to the ASM instance.
Diagram
that how database interacts with ASM when a request is to read or open a
datafile.
1A. Database issues open of a database file
1B. ASM sends the extent map for the file to database instance. Starting with 11g, the RDBMS only receives first 60 extents the remaining extents in the extent map are paged in on demand, providing a faster open
2A/2B. Database now reads directly from disk
3A.RDBMS foreground initiates a create tablespace for example
3B. ASM does the allocation for its essentially reserving the allocation units
for the file creation
3C. Once allocation phase is done, the extent map is sent to the RDBMS
3D. The RDBMS initialization phase kicks in. In this phase the initializes all
the reserved AUs
3E. If file creation is successful, then the RDBMS commits the file creation
1B. ASM sends the extent map for the file to database instance. Starting with 11g, the RDBMS only receives first 60 extents the remaining extents in the extent map are paged in on demand, providing a faster open
2A/2B. Database now reads directly from disk
3A.RDBMS foreground initiates a create tablespace for example
3B. ASM does the allocation for its essentially reserving the allocation units
for the file creation
3C. Once allocation phase is done, the extent map is sent to the RDBMS
3D. The RDBMS initialization phase kicks in. In this phase the initializes all
the reserved AUs
3E. If file creation is successful, then the RDBMS commits the file creation
Going
forward all I/Os are done by the RDBMS directly.
Can my
disks in a diskgroup can be varied size? For example one disk is of 100GB and
another disk is of 50GB. If so how does ASM manage the extents?
Yes,
disk sizes can be varied, Oracle ASM will manage data efficiently and
intelligent by placing the extents proportional to the size of the disk in the
disk group, bigger diskgroups have more extents than lesser ones.
31)
What is Intelligent Data Placement?
32)
What is ASM preferred Mirror read? How does it useful?
33)
What is ACFS?
34)
What is ADVM?
What
is the major difference between 10g and 11g RAC?
Well,
there is not much difference between 10g and 11gR (1) RAC.
But
there is a significant difference in 11gR2.
Prior
to 11gR1(10g) RAC, the following were managed by Oracle CRS
o Databases
o Instances
o Applications
o Node Monitoring
o Event Services
o High Availability
From
11gR2(onwards) its completed HA stack managing and providing the following
resources as like the other cluster software like VCS etc.
· Databases
· Instances
· Applications
· Cluster Management
· Node Management
· Event Services
· High Availability
· Network Management (provides DNS/GNS/MDNSD services on
behalf of other traditional services) and SCAN – Single Access Client Naming
method, HAIP
· Storage Management (with help of ASM and other new
ACFS filesystem)
· Time synchronization (rather depending upon
traditional NTP)
· Removed OS dependent hang checker etc, manages with
own additional monitor process
What
are Oracle Cluster Components?
Cluster
Interconnect (HAIP)
Shared
Storage (OCR/Voting Disk)
Clusterware
software
What
are Oracle RAC Components?
VIP,
Node apps etc.
What
are Oracle Kernel Components (nothing but how does Oracle RAC database differs
than Normal single instance database in terms of Binaries and process)
Basically
Oracle kernel need to switched on with RAC On option when you convert to RAC,
that is the difference as it facilitates few RAC bg process like
LMON,LCK,LMD,LMS etc.
To
turn on RAC
# link the oracle libraries
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
# rebuild oracle
$ cd $ORACLE_HOME/bin
$ relink oracle
# link the oracle libraries
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
# rebuild oracle
$ cd $ORACLE_HOME/bin
$ relink oracle
Oracle
RAC is composed of two or more database instances. They are composed of Memory
structures and background processes same as the single instance database.Oracle
RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache
Service) that enable cache fusion.Oracle RAC instances are composed of
following background processes:
ACMS—Atomic
Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor
What
is Clusterware?
Software
that provides various interfaces and services for a cluster. Typically, this
includes capabilities that:
· Allow the cluster to be managed as a whole
· Protect the integrity of the cluster
· Maintain a registry of resources across the cluster
· Deal with changes to the cluster
· Provide a common view of resources
What
are the background process that exists in 11gr2 and functionality?
Process Name
|
Functionality
|
crsd
|
•The CRS daemon (crsd) manages cluster resources
based on configuration information that is stored in Oracle Cluster Registry
(OCR) for each resource. This includes start, stop, monitor, and failover
operations. The crsd process generates events when the status of a resource changes.
|
cssd
|
•Cluster Synchronization Service (CSS): Manages the
cluster configuration by controlling which nodes are members of the cluster
and by notifying members when a node joins or leaves the cluster. If you are
using certified third-party clusterware, then CSS processes interfaces with
your clusterware to manage node membership information. CSS has three
separate processes: the CSS daemon (ocssd), the CSS Agent (cssdagent), and
the CSS Monitor (cssdmonitor). The cssdagent process monitors the cluster and
provides input/output fencing. This service formerly was provided by Oracle
Process Monitor daemon (oprocd), also known as OraFenceService on Windows. A
cssdagent failure results in Oracle Clusterware restarting the node.
|
diskmon
|
•Disk Monitor daemon (diskmon): Monitors and
performs input/output fencing for Oracle Exadata Storage Server. As Exadata
storage can be added to any Oracle RAC node at any point in time, the diskmon
daemon is always started when ocssd is started.
|
evmd
|
•Event Manager (EVM): Is a background process that
publishes Oracle Clusterware events
|
mdnsd
|
•Multicast domain name service (mDNS): Allows DNS
requests. The mDNS process is a background process on Linux and UNIX, and a
service on Windows.
|
gnsd
|
•Oracle Grid Naming Service (GNS): Is a gateway
between the cluster mDNS and external DNS servers. The GNS process performs
name resolution within the cluster.
|
ons
|
•Oracle Notification Service (ONS): Is a
publish-and-subscribe service for communicating Fast Application Notification
(FAN) events
|
oraagent
|
•oraagent: Extends clusterware to support
Oracle-specific requirements and complex resources. It runs server callout
scripts when FAN events occur. This process was known as RACG in Oracle
Clusterware 11g Release 1 (11.1).
|
orarootagent
|
•Oracle root agent (orarootagent): Is a specialized
oraagent process that helps CRSD manage resources owned by root, such as the
network, and the Grid virtual IP address
|
oclskd
|
•Cluster kill daemon (oclskd): Handles instance/node
evictions requests that have been escalated to CSS
|
gipcd
|
•Grid IPC daemon (gipcd): Is a helper daemon for the
communications infrastructure
|
ctssd
|
•Cluster time synchronisation daemon(ctssd) to
manage the time syncrhonization between nodes, rather depending on NTP
|
Under
which user or owner the process will start?
Component
|
Name of the Process
|
Owner
|
Oracle High Availability Service
|
ohasd
|
init, root
|
Cluster Ready Service (CRS)
|
Cluster Ready Services
|
root
|
Cluster Synchronization Service (CSS)
|
ocssd,cssd monitor, cssdagent
|
grid owner
|
Event Manager (EVM)
|
evmd, evmlogger
|
grid owner
|
Cluster Time Synchronization Service (CTSS)
|
octssd
|
root
|
Oracle Notification Service (ONS)
|
ons, eons
|
grid owner
|
Oracle Agent
|
oragent
|
grid owner
|
Oracle Root Agent
|
orarootagent
|
root
|
Grid Naming Service (GNS)
|
gnsd
|
root
|
Grid Plug and Play (GPnP)
|
gpnpd
|
grid owner
|
Multicast domain name service (mDNS)
|
mdnsd
|
grid owner
|
What
is startup sequence in Oracle 11g RAC? 11g RAC startup sequence?
This is about to understand the startup sequence of
Grid Infrastructure daemons and its resources in 11gR2 RAC.
In 11g RAC aka Grid Infrastructure we all know there
are additional background daemons and agents, and the Oracle documentation is
not so clear nor the other blog.
For example:- I have found below two diagram follow
any one of these.
explanation from diagram
OHASD Phase:-
·
OHASD (Oracle High Availability Server Daemon) starts Firsts and it will
start
OHASD Agent Phase:-
·
OHASD Agent starts and in turn this will start
gipcd
|
Grid interprocess communication daemon, used for
monitoring cluster interconnect
|
mdnsd
|
Multicast DNS service It resolves DNS requests on
behalf of GNS
|
gns
|
The Grid Naming Service (GNS), a gateway between DNS
and mdnsd, resolves DNS requests
|
gpnpd
|
Grid Plug and Play Daemon, Basically a profile
similar like OCR contents stored in XML format in
$GI_HOME/gpnp/profiles/
|
evmd/
evmlogger
|
Evm service will be provided by evmd daemon, which
is a information about events happening in cluster, stop node,start node,
start instance etc.
|
·
cssdagent (cluster synchronization service
agent), in turn starts
ocssd
|
Cluster synchronization service daemon which manages
node membership in the cluster
|
If cssd found that ocssd is down, it will reboot the
node to protect the data integrity.
·
cssdmonitor (cluster synchronization service monitor),
replaces oprocd and provides I/O fencing
·
OHASD orarootagent starts and in turn starts
crsd.bin
|
Cluster ready services, which manages high
availability of cluster resources , like stopping , starting, failing over
etc.
|
diskmon.bin
|
disk monitor (diskdaemon
monitor) provides I/O fencing for exadata storage
|
octssd.bin
|
Cluster synchronization time services , provides Network time protocol services but manages its own
rather depending on OS
|
CRSD Agent Phase:- crsd.bin starts two more agents
crsd orarootagent(Oracle root agent) starts and in turn this will
start
gns
|
Grid interprocess communication daemon, used for
monitoring cluster interconnect
|
gns vip
|
Multicast DNS service It resolves DNS requests on
behalf of GNS
|
Network
|
Monitor the additional networks to provide HAIP to
cluster interconnects
|
Scan vip
|
Monitor the scan vip, if found fail or unreachable
failed to other node
|
Node vip
|
Monitor the node vip, if found fail or unreachable
failed to other node
|
crsd oraagent(Oracle Agent) starts and in turn it will start
(the same functionality in 11gr1 and 10g managed by racgmain and racgimon
background process) which is now managed by crs Oracle agent itself.
·
ASM & disk groups
|
Start & monitor local asm instance
|
ONS
|
FAN feature, provides notification to interested
client
|
eONS
|
FAN feature, provides notification to interested
client
|
SCAN Listener
|
Start & Monitor scan listener
|
Node Listener
|
Start & monitor the node listener (rdbms?)
|
As
you said Voting & OCR Disk resides in ASM Diskgroups, but as per startup
sequence OCSSD starts first before than ASM, how is it possible?
How
does OCSSD starts if voting disk & OCR resides in ASM Diskgroups?
You
might wonder how CSSD, which is required to start the clustered ASM instance,
can be started if voting disks are stored in ASM? This sounds like a
chicken-and-egg problem: without access to the voting disks there is no CSS,
hence the node cannot join the cluster. But without being part of the cluster,
CSSD cannot start the ASM instance. To solve this problem the ASM disk headers
have new metadata in 11.2: you can use kfed to read the header of an ASM disk
containing a voting disk. The kfdhdb.vfstart and kfdhdb.vfend fields tell CSS
where to find the voting file. This does not require the ASM instance to be up.
Once the voting disks are located, CSS can access them and joins the cluster.
How
does SCAN works?
1. Client Connected through SCAN name of the cluster
(remember all three IP addresses round robin resolves to same Host name (SCAN
Name), here in this case our scan name is cluster01-scan.cluster01.example.com
2. The request reaches to DNS server in your corp and
then resolves to one of the node out of three. a. If GNS (Grid Naming
service or domain is configured) that is a subdomain configured in the
DNS entry for to resolve cluster address the request will be handover to GNS
(gnsd)
3. Here in our case assume there is no GNS, now the with
the help of SCAN listeners where end points are configured to database
listener.
4. Database Listeners listen the request and then process
further.
5. In case of node addition, Listener 4, client need not
to know or need not change any thing from their tns entry (address of 4th node/instance)
as they just using scan IP.
6. Same case even in the node deletion.
What
is GNS?
Grid
Naming service is alternative service to DNS , which will act as a sub domain
in your DNS but managed by Oracle, with GNS the connection is routed to the
cluster IP and manages internally.
What
is GPNP?
Grid
Plug and Play along with GNS provide dynamic
In previous
releases, adding or removing servers in a cluster required extensive manual
preparation.
In
Oracle Database 11g Release 2, GPnP allows each node to perform the
following tasks dynamically:
o Negotiating appropriate network
identities for itself
o Acquiring additional information from a
configuration profile
o Configuring or reconfiguring itself
using profile data, making host names and addresses resolvable on the network
For
example a domain should contain
· –Cluster name: cluster01
· –Network domain: example.com
· –GPnP domain: cluster01.example.com
To add
a node, simply connect the server to the cluster and allow the cluster to
configure the node.
To
make it happen, Oracle uses the profile located in $GI_HOME/gpnp/profiles/peer/profile.xml
which contains the cluster resources, for example disk locations of ASM. etc.
So
this profile will be read local or from the remote machine when plugged into
cluster and dynamically added to cluster.
What
are the file types that ASM support and keep in disk groups?
Control files
|
Flashback logs
|
Data Pump dump sets
|
Data files
|
DB SPFILE
|
Data Guard configuration
|
Temporary data files
|
RMAN backup sets
|
Change tracking bitmaps
|
Online redo logs
|
RMAN data file copies
|
OCR files
|
Archive logs
|
Transport data files
|
ASM SPFILE
|
List
Key benefits of ASM?
· Stripes files rather than logical volumes
· Provides redundancy on a file basis
· Enables online disk reconfiguration and dynamic
rebalancing
· Reduces the time significantly to resynchronize a
transient failure by tracking changes while disk is offline
· Provides adjustable rebalancing speed
· Is cluster-aware
· Supports reading from mirrored copy instead of primary
copy for extended clusters
· Is automatically installed as part of the Grid
Infrastructure
List
some of the background process that used in ASM?
Process
|
Description
|
RBAL
|
Opens all device files as part of discovery and
coordinates the rebalance activity
|
ARBn
|
One or more slave processes that do the rebalance
activity
|
GMON
|
Responsible for managing the disk-level activities
such as drop or offline and advancing the ASM disk group compatibility
|
MARK
|
Marks ASM allocation units as stale when needed
|
Onnn
|
One or more ASM slave processes forming a pool of
connections to the ASM instance for exchanging messages
|
PZ9n
|
One or more parallel slave processes used in
fetching data on clustered ASM installation from GV$ views
|
What
is node listener?
In
11gr2 the listeners will run from Grid Infrastructure software home
· The node listener is a process that helps establish
network connections from ASM clients to the ASM instance.
· Runs by default from the Grid $ORACLE_HOME/bin
directory
· Listens on port 1521 by default
· Is the same as a database instance listener
· Is capable of listening for all database instances on
the same machine in addition to the ASM instance
· Can run concurrently with separate database listeners
or be replaced by a separate database listener
· Is named tnslsnr on the Linux platform
What
is SCAN listener?
A scan
listener is something that additional to node listener which listens the
incoming db connection requests from the client which got through the scan IP,
it got end points configured to node listener where it routes the db connection
requests to particular node listener.
What
is the difference between CRSCTL and SRVCTL?
crsctl
manages clusterware-related operations:
· Starting and stopping Oracle Clusterware
· Enabling and disabling Oracle Clusterware daemons
· Registering cluster resources
srvctl
manages Oracle resource–related operations:
· Starting and stopping database instances and services
· Also from 11gR2 manages the cluster resources like
network,vip,disks etc
How to
control Oracle Clusterware?
To
start or stop Oracle Clusterware on a specific node:
#
crsctl stop crs
#
crsctl start crs
To
enable or disable Oracle Clusterware on a specific node:
#
crsctl enable crs
#
crsctl disable crs
How to
check the cluster (all nodes) status?
To
check the viability of Cluster Synchronization Services (CSS) across nodes:
$
crsctl check cluster
CRS-4537:
Cluster Ready Services is online
CRS-4529:
Cluster Synchronization Services is online
CRS-4533:
Event Manager is online
How to
check the cluster (one node) status?
$
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
How to
find Voting Disk location?
•To
determine the location of the voting disk:
#
crsctl query css votedisk
##
STATE File Universal Id File Name Disk group
– —–
—————– ———- ———-
1.
ONLINE 8c2e45d734c64f8abf9f136990f3daf8 (ASMDISK01) [DATA]
2.
ONLINE 99bc153df3b84fb4bf071d916089fd4a (ASMDISK02) [DATA]
3.
ONLINE 0b090b6b19154fc1bf5913bc70340921 (ASMDISK03) [DATA]
Located
3 voting disk(s).
How to
find Location of OCR?
· cat /etc/oracle/ocr.loc
ocrconfig_loc=+DATA
local_only=FALSE
· #OCRCHECK (also about OCR integrity)
What
are types of ASM Mirroring?
Disk Group Type
|
Supported MirroringLevels
|
Default Mirroring Level
|
External redundancy
|
Unprotected (None)
|
Unprotected (None)
|
Normal redundancy
|
Two-wayThree-way
Unprotected (None)
|
Two-way
|
High redundancy
|
Three-way
|
Three-way
|
What
is ASM Striping?
ASM
can use variable size data extents to support larger files, reduce memory
requirements, and improve performance.
Each data
extent resides on an individual disk.
Data
extents consist of one or more allocation units.
The
data extent size is:
· Equal to AU for the first 20,000 extents (0–19999)
· Equal to 4 × AU for the next 20,000 extents
(20000–39999)
· Equal to 16 × AU for extents above 40,000
ASM
stripes files using extents with a coarse method for load balancing or a fine
method to reduce latency.
· Coarse-grained striping is always equal to the
effective AU size.
· Fine-grained striping is always equal to 128 KB.
How
many ASM Diskgroups can be created under one ASM Instance?
ASM
imposes the following limits:
· 63 disk groups in a storage system
· 10,000 ASM disks in a storage system
· Two-terabyte maximum storage for each ASM disk
(non-Exadata)
· Four-petabyte maximum storage for each ASM disk
(Exadata)
· 40-exabyte maximum storage for each storage system
· 1 million files for each disk group
· ASM file size limits (database limit is 128 TB):
1. External redundancy maximum file size is 140 PB.
2. Normal redundancy maximum file size is 42 PB.
3. High redundancy maximum file size is 15 PB.
How to
find the cluster network settings?
To
determine the list of interfaces available to the cluster:
$
oifcfg iflist –p -n
To
determine the public and private interfaces that have been configured:
$
oifcfg getif
eth0
192.0.2.0 global public
eth1
192.168.1.0 global cluster_interconnect
To
determine the Virtual IP (VIP) host name, VIP address, VIP subnet mask, and VIP
interface name:
$
srvctl config nodeapps -a
VIP
exists.:host01
VIP
exists.: /192.0.2.247/192.0.2.247/255.255.255.0/eth0
…
How to
change Cluster interconnect in RAC?
On a
single node in the cluster, add the new global interface specification:
$
oifcfg setif -global eth2/192.0.2.0:cluster_interconnect
Verify
the changes with oifcfg getif and then stop Clusterware on all nodes by running
the following command as root on each node:
#
oifcfg getif
#
crsctl stop crs
Assign
the network address to the new network adapters on all nodes using ifconfig:
#ifconfig
eth2 192.0.2.15 netmask 255.255.255.0 \ broadcast 192.0.2.255
Remove
the former adapter/subnet specification and restart Clusterware:
$
oifcfgdelif -global eth1/192.168.1.0
#
crsctl start crs
Managing
or Modifying SCAN in Oracle RAC?
To add
a SCAN VIP resource:
$
srvctl add scan -n cluster01-scan
To
remove Clusterware resources from SCAN VIPs:
$
srvctl remove scan [-f]
To add
a SCAN listener resource:
$
srvctl add scan_listener
$
srvctl add scan_listener -p 1521
To
remove Clusterware resources from all SCAN listeners:
$
srvctl remove scan_listener [-f]
How to
check the node connectivity in Oracle Grid Infrastructure?
$
cluvfy comp nodecon -n all –verbose
Can I
stop all nodes in one command? Meaning that stopping whole cluster ?
In 10g
its not possible, where in 11g it is possible
[root@pic1]#
crsctl start cluster -all
[root@pic2]# crsctl stop cluster –all
[root@pic2]# crsctl stop cluster –all
What
is OLR? Which of the following statements regarding the Oracle Local Registry
(OLR) is true?
1.Each
cluster node has a local registry for node-specific resources.
2.The
OLR should be manually created after installing Grid Infrastructure on each
node in the cluster.
3.One
of its functions is to facilitate Clusterware startup in situations where the
ASM stores the OCR and voting disks.
4.You
can check the status of the OLR using ocrcheck.
What
is runfixup.sh script in Oracle Clusterware 11g release 2 installation
With
Oracle Clusterware 11g release 2, Oracle Universal Installer (OUI) detects when
the minimum requirements for an installation are not met, and creates shell
scripts, called fixup scripts, to finish incomplete system configuration steps.
If OUI detects an incomplete task, then it generates fixup scripts
(runfixup.sh). You can run the fixup script after you click the Fix and Check
Again Button.
The
Fixup script does the following:
If
necessary sets kernel parameters to values required for successful
installation, including:
· Shared memory parameters.
· Open file descriptor and UDP send/receive parameters.
Sets
permissions on the Oracle Inventory (central inventory) directory. Reconfigures
primary and secondary group memberships for the installation owner, if
necessary, for the Oracle Inventory directory and the operating system
privileges groups.
· Sets shell limits if necessary to required values
No matter what database platform you’re running, dbaDIRECT is your answer for 24×7 monitoring and expert skill, at a lower cost than what’s possible with internal administration. We offer each of our core remote management services for all major database platforms, including Oracle, Sybase, MySQL, SQLServer, and IBM DB2. Our team of DBAs is here ’round the clock for your database needs, capable of servicing any size organization at any time of the day. Period.
ReplyDeleteRemote dba services support 24x7 of below mentioned applications - more… Online Training- Corporate Training- IT Support U Can Reach Us On +917386622889 - +919000444287
http://www.21cssindia.com/support.html
This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me. We also Provide some Interview Questions on ORACLE RAC.
ReplyDeleteThanks for sharing your information, nice blog.
ReplyDeleteOracle RAC Training in Hyderabad
Appreciate you sharing, great article.Much thanks again. Really Cool.
ReplyDeletejava training
salesforce training
hadoop training
mulesoft training
linux training
oracle sql plsql online courses
ReplyDeletego langaunage online courses
azure online courses
This comment has been removed by the author.
ReplyDeleteHi, I read your whole blog. This is very nice. Good to know about the career in qa automation is broad in future. We are also providing various Oracle DBA & RAC Training, anyone interested can Oracle DBA & RAC Training for making their career in this field .
ReplyDelete