Oracle DBA Interview Questions & Answer
Oracle DBA Interview Questions
Oracle DBA Interview Questions
What are the versions you are working on
Database
RAC database
Dataguard
What is the recent patch you applied?
What happens when you run Root.sh during installation
RAC Architechture?
What is Node Eviction? In what scenarios the node be evicted? Which background process is responsible for node eviction and fixing evicted node and bringing into cluster? How do you troubleshoot Node Eviction?
What is cache fusion? What is responsible for cache fusion?
How will you take VD backup in 11gR1 and 11gR2?
How to check VD backup location
How to check OCR backup location
Starup Restrict vs Startup Upgrade
Difference between 11g and 10g ?
Difference between sga_target and memory_target?
Difference between 11gR2,11gR1,10gR2 and 10gR1 RAC ?
Materialized views and its types
Database upgrade takes 1hr normally and client want to upgrade without down time.
How can you achieve that in a RAC and Standalone.How can we upgrade with only 5 min downtime instead of 1 hour, as per client requirement?
Can exp be faster than expdp? If yes? In which scenario it happens?
Exp vs Datapump
Datapump vs RMAN
If RMAN backs up only changed blocks, what happens if the database has some read only ts and I have taken level 0 backup. Will the readonly ts backs up or not as there are no changed blocks in them?
What is Dataguard?
Logical vs Physical Standby databases?
Protection modes in Dataguard? What Protection mode do you have in your project?
DB_LINK? Syntax?
DB_LINK VS NETWORK_LINK in Datapump
what happens internally when you export or import in Datapump
How to check whether DATAPUMP dump File is corrupted or not?
How to see the DDL contents of an export dump file in Data Pump import and Traditional import?
Datapump Import failed after running for long time and even resumable time also execeeded? What action will you take?
Datapump Import hanged after running for long time? What action will you take?
How will you stop and start an import in datapump.
From which view you will find out datapump jobs?
Tell me some issues you faced with Datapump and how did you fix them?
Tell me the toughest issue you faced with Dataguard and how did you fix it?
Tell me the toughest issue you faced with RAC and how did you fix it?
Tell me the toughest issue you faced in Performance Tuning and how did you fix it?
CPU VS PSU Patching? What patching you are implementing in your project?
What is the full form of Opatch
opatch apply vs napply
Pre requirement for upgrade to 11g from 10g.
Steps for database cloning using RMAN
Steps for DR rebuild
-----------------------------------------------------------------------
Temp ts is 100% full,adding space still filling up, we cant add more space to it, what fix will you apply?
How to make RMAN backups run faster?
What is block change tracking? How does it work?
Apps team complained database is performing slow? How will you confirm the slowness being a DBA ? What actions will you perform?
Apps team complained database performence is low compared to last week? What actions will you take?
Which columns will you check in active_session_history
Write query for populating Active sessions excluding background processes?
Fast failover in DG? Is Fast Failover there in your environment?
What is a Scan listener? How it works?
What are the versions you are working on
Database
RAC database
Dataguard
What is the recent patch you applied?
What happens when you run Root.sh during installation
RAC Architechture?
What is Node Eviction? In what scenarios the node be evicted? Which background process is responsible for node eviction and fixing evicted node and bringing into cluster? How do you troubleshoot Node Eviction?
What is cache fusion? What is responsible for cache fusion?
How will you take VD backup in 11gR1 and 11gR2?
How to check VD backup location
How to check OCR backup location
Starup Restrict vs Startup Upgrade
Difference between 11g and 10g ?
Difference between sga_target and memory_target?
Difference between 11gR2,11gR1,10gR2 and 10gR1 RAC ?
Materialized views and its types
Database upgrade takes 1hr normally and client want to upgrade without down time.
How can you achieve that in a RAC and Standalone.How can we upgrade with only 5 min downtime instead of 1 hour, as per client requirement?
Can exp be faster than expdp? If yes? In which scenario it happens?
Exp vs Datapump
Datapump vs RMAN
If RMAN backs up only changed blocks, what happens if the database has some read only ts and I have taken level 0 backup. Will the readonly ts backs up or not as there are no changed blocks in them?
What is Dataguard?
Logical vs Physical Standby databases?
Protection modes in Dataguard? What Protection mode do you have in your project?
DB_LINK? Syntax?
DB_LINK VS NETWORK_LINK in Datapump
what happens internally when you export or import in Datapump
How to check whether DATAPUMP dump File is corrupted or not?
How to see the DDL contents of an export dump file in Data Pump import and Traditional import?
Datapump Import failed after running for long time and even resumable time also execeeded? What action will you take?
Datapump Import hanged after running for long time? What action will you take?
How will you stop and start an import in datapump.
From which view you will find out datapump jobs?
Tell me some issues you faced with Datapump and how did you fix them?
Tell me the toughest issue you faced with Dataguard and how did you fix it?
Tell me the toughest issue you faced with RAC and how did you fix it?
Tell me the toughest issue you faced in Performance Tuning and how did you fix it?
CPU VS PSU Patching? What patching you are implementing in your project?
What is the full form of Opatch
opatch apply vs napply
Pre requirement for upgrade to 11g from 10g.
Steps for database cloning using RMAN
Steps for DR rebuild
-----------------------------------------------------------------------
Temp ts is 100% full,adding space still filling up, we cant add more space to it, what fix will you apply?
How to make RMAN backups run faster?
What is block change tracking? How does it work?
Apps team complained database is performing slow? How will you confirm the slowness being a DBA ? What actions will you perform?
Apps team complained database performence is low compared to last week? What actions will you take?
Which columns will you check in active_session_history
Write query for populating Active sessions excluding background processes?
Fast failover in DG? Is Fast Failover there in your environment?
What is a Scan listener? How it works?
LOAD BALANCING in RAC:-
The Oracle RAC system can distribute the load over many nodes this feature called as load balancing.
There are two methods of load balancing
1.Client load balancing
2.Server load balancing
1.Client Load Balancing
Client Load Balancing distributes new connections among Oracle RAC nodes so that no one server is overloaded with connection requests and it is configured at net service name level by providing multiple descriptions in a description list or multiple addresses in an address list. For example, if connection fails over to another node in case of failure, the client load balancing ensures that the redirected connections are distributed among the other nodes in the RAC.
Configure Client-side connect-time load balancing by setting LOAD_BALANCE=ON in the corresponding client side TNS entry.
TESTRAC =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2-VIP)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = testdb.selectstarfrom.com))
)
2.Server Load Balancing
Server Load Balancing distributes processing workload among Oracle RAC nodes. It divides the connection load evenly between all available listeners and distributes new user session connection requests to the least loaded listener(s) based on the total number of sessions which are already connected. Each listener communicates with the other listener(s) via each database instance’s PMON process.
Configure Server-side connect-time load balancing feature by setting REMOTE_LISTENERS initialization parameter of each instance to a TNS name that describes list of all available listeners.
TESTRAC_LISTENERS =
(DESCRIPTION =
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1)(PORT = 1521)))
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2)(PORT = 1521))))
)
Set *.remote_listener= TESTRAC_LISTENERS’ initialization parameter in the database’s shared SPFILE and add TESTRAC_LISTENERS’ entry to the TNSNAMES.ORA file in the Oracle Home of each node in the cluster.
Once you configure Server-side connect-time load balancing, each database’s PMON process will automatically register the database with the database’s local listener as well as cross-register the database with the listeners on all other nodes in the cluster. Now the nodes themselves decide which node is least busy, and then will connect the client to that node.
----------------------------------------------------------------------------------------------------------------------------------------------
FAILOVER in RAC:-
The Oracle RAC system can protect against failures caused by O/S or server crashes or hardware failures. When a node failure occurs in RAC system, the connection attempts can fail over to other surviving nodes in the cluster this feature called as Failover.
There are two methods of failover
1. Connection Failover
2. Transparent Application Failover (TAF)
1. Connection Failover
If a connection failure occurs at connect time, the application failover the connection to another active node in the cluster. This feature enables client to connect to another listener if the initial connection to the first listener fails.
Enable client-side connect-time Failover by setting FAILOVER=ON in the corresponding client side TNS entry.
TESTRAC =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2-VIP)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = testdb.selectstarfrom.com))
)
If LOAD_BALANCE is set to on then clients randomly attempt connections to any nodes. If client made connection attempt to a down node, the client needs to wait until it receives the information that the node is not accessible before trying alternate address in ADDRESS_LIST.
2. Transparent Application Failover (TAF)
If connection failure occurs after a connection is established, the connection fails over to other surviving nodes. Any uncommitted transactions are rolled back and server side program variables and session properties will be lost. In some case the select statements automatically re-executed on the new connection with the cursor positioned on the row on which it was positioned prior to the failover.
TESTRAC =
(DESCRIPTION =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = testdb.selectstarfrom.com)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))
)
)
The Oracle RAC system can distribute the load over many nodes this feature called as load balancing.
There are two methods of load balancing
1.Client load balancing
2.Server load balancing
1.Client Load Balancing
Client Load Balancing distributes new connections among Oracle RAC nodes so that no one server is overloaded with connection requests and it is configured at net service name level by providing multiple descriptions in a description list or multiple addresses in an address list. For example, if connection fails over to another node in case of failure, the client load balancing ensures that the redirected connections are distributed among the other nodes in the RAC.
Configure Client-side connect-time load balancing by setting LOAD_BALANCE=ON in the corresponding client side TNS entry.
TESTRAC =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2-VIP)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = testdb.selectstarfrom.com))
)
2.Server Load Balancing
Server Load Balancing distributes processing workload among Oracle RAC nodes. It divides the connection load evenly between all available listeners and distributes new user session connection requests to the least loaded listener(s) based on the total number of sessions which are already connected. Each listener communicates with the other listener(s) via each database instance’s PMON process.
Configure Server-side connect-time load balancing feature by setting REMOTE_LISTENERS initialization parameter of each instance to a TNS name that describes list of all available listeners.
TESTRAC_LISTENERS =
(DESCRIPTION =
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1)(PORT = 1521)))
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2)(PORT = 1521))))
)
Set *.remote_listener= TESTRAC_LISTENERS’ initialization parameter in the database’s shared SPFILE and add TESTRAC_LISTENERS’ entry to the TNSNAMES.ORA file in the Oracle Home of each node in the cluster.
Once you configure Server-side connect-time load balancing, each database’s PMON process will automatically register the database with the database’s local listener as well as cross-register the database with the listeners on all other nodes in the cluster. Now the nodes themselves decide which node is least busy, and then will connect the client to that node.
----------------------------------------------------------------------------------------------------------------------------------------------
FAILOVER in RAC:-
The Oracle RAC system can protect against failures caused by O/S or server crashes or hardware failures. When a node failure occurs in RAC system, the connection attempts can fail over to other surviving nodes in the cluster this feature called as Failover.
There are two methods of failover
1. Connection Failover
2. Transparent Application Failover (TAF)
1. Connection Failover
If a connection failure occurs at connect time, the application failover the connection to another active node in the cluster. This feature enables client to connect to another listener if the initial connection to the first listener fails.
Enable client-side connect-time Failover by setting FAILOVER=ON in the corresponding client side TNS entry.
TESTRAC =
(DESCRIPTION =
(ADDRESS_LIST=
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC2-VIP)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = testdb.selectstarfrom.com))
)
If LOAD_BALANCE is set to on then clients randomly attempt connections to any nodes. If client made connection attempt to a down node, the client needs to wait until it receives the information that the node is not accessible before trying alternate address in ADDRESS_LIST.
2. Transparent Application Failover (TAF)
If connection failure occurs after a connection is established, the connection fails over to other surviving nodes. Any uncommitted transactions are rolled back and server side program variables and session properties will be lost. In some case the select statements automatically re-executed on the new connection with the cursor positioned on the row on which it was positioned prior to the failover.
TESTRAC =
(DESCRIPTION =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTRAC1-VIP)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = testdb.selectstarfrom.com)
(FAILOVER_MODE = (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5))
)
)
Oracle DBA Interview Questions and Answers - RAC -
2
Oracle RAC Interview Questions
and Answers
1. Where are the Clusterware files stored on a RAC environment?
The Clusterware is installed on each node (on an Oracle Home) and on the shared disks (the voting disks and the CSR file)
2. Where are the database software files stored on a RAC environment?
The base software is installed on each node of the cluster and the
database storage on the shared disks.
3. What kind of storage we can use for the shared Clusterware files?
- OCFS (Release 1 or 2)
- raw devices
- third party cluster file system such as GPFS or Veritas
4. What kind of storage we can use for the RAC database storage?
- OCFS (Release 1 or 2)
- ASM
- raw devices
- third party cluster file system such as GPFS or Veritas
5. What is a CFS?
A cluster File System (CFS) is a file system that may be accessed (read and write) by all members in a cluster at the same time. This implies that all members of a cluster have the same view.
6. What is an OCFS2?
The OCFS2 is the Oracle (version 2) Cluster File System which can be used for the Oracle Real Application Cluster.
7. Which files can be placed on an Oracle Cluster File System?
- Oracle Software installation (Windows only)
- Oracle files (controlfiles, datafiles, redologs, files described by the bfile datatype)
- Shared configuration files (spfile)
- OCR and voting disk
- Files created by Oracle during runtime
Note: There are some platform specific limitations.
8. Do you know another Cluster Vendor?
HP Tru64 Unix, Veritas, Microsoft
9. How is possible to install a RAC if we don’t have a CFS?
This is possible by using a raw device.
10. What is a raw device?
A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks.
11. What is a raw partition?
A raw partition is a portion of a physical disk that is accessed at the lowest possible level. A raw partition is created when an extended partition is created and logical partitions are assigned to it without any formatting. Once formatting is complete, it is called cooked partition.
12. When to use CFS over raw?
A CFS offers:
- Simpler management
- Use of Oracle Managed Files with RAC
- Single Oracle Software installation
- Autoextend enabled on Oracle datafiles
- Uniform accessibility to archive logs in case of physical node failure
- With Oracle_Home on CFS, when you apply Oracle patches CFS guarantees that the updated Oracle_Home is visible to all nodes in the cluster.
Note: This option is very dependent on the availability of a CFS on your platform.
13. When to use raw over CFS?
- Always when CFS is not available or not supported by Oracle.
- The performance is very, very important: Raw devices offer best performance without any intermediate layer between Oracle and the disk.
Note: Autoextend fails on raw devices if the space is exhausted. However the space could be added online if needed.
14. What CRS is?
Oracle RAC 10g Release 1 introduced Oracle Cluster Ready Services (CRS), a platform-independent set of system services for cluster environments. In Release 2, Oracle has renamed this product to Oracle Clusterware.
15. What is VIP IP used for?
It returns a dead connection IMMIDIATELY, when its primary node fails. Without using VIP IP, the clients have to wait around 10 minutes to receive ORA-3113: “end of file on communications channel”. However, using Transparent Application Failover (TAF) could avoid ORA-3113.
16. Why we need to have configured SSH or RSH on the RAC nodes?
SSH (Secure Shell,10g+) or RSH (Remote Shell, 9i+) allows “oracle” UNIX account connecting to another RAC node and copy/ run commands as the local “oracle” UNIX account.
17. Is the SSH, RSH needed for normal RAC operations?
No. SSH or RSH are needed only for RAC, patch set installation and clustered database creation.
18. Do we have to have Oracle RDBMS on all nodes?
Each node of a cluster that is being used for a clustered database will typically have the RDBMS and RAC software loaded on it, but not actual data files (these need to be available via shared disk).
19. What are the restrictions on the SID with a RAC database? Is it limited to 5 characters?
The SID prefix in 10g Release 1 and prior versions was restricted to five characters by install/ config tools so that an ORACLE_SID of up to max of 5+3=8 characters can be supported in a RAC environment. The SID prefix is relaxed up to 8 characters in 10g Release 2, see bug 4024251 for more information.
20. Does Real Application Clusters support heterogeneous platforms?
The Real Application Clusters do not support heterogeneous platforms in the same cluster.
21. Are there any issues for the interconnect when sharing the same switch as the public network by using VLAN to separate the network?
RAC and Clusterware deployment best practices suggests that the interconnect (private connection) be deployed on a stand-alone, physically separate, dedicated switch. On big network the connections could be instable.
22. What is the Load Balancing Advisory?
To assist in the balancing of application workload across designated resources, Oracle Database 10g Release 2 provides the Load Balancing Advisory. This Advisory monitors the current workload activity across the cluster and for each instance where a service is active; it provides a percentage value of how much of the total workload should be sent to this instance as well as service quality flag.
23. How many nodes are supported in a RAC Database?
With 10g Release 2, we support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database. Currently DBCA has a bug where it will not go beyond 63 instances. There is also a documentation bug for the max-instances parameter. With 10g Release 1 the Maximum is 63.
24. What is the Cluster Verification Utiltiy (cluvfy)?
The Cluster Verification Utility (CVU) is a validation tool that you can use to check all the important components that need to be verified at different stages of deployment in a RAC environment.
25. What versions of the database can I use the cluster verification utility (cluvfy) with?
The cluster verification utility is release with Oracle Database 10g Release 2 but can also be used with Oracle Database 10g Release 1.
26. If I am using Vendor Clusterware such as Veritas, IBM, Sun or HP, do I still need Oracle Clusterware to run Oracle RAC 10g?
Yes. When certified, you can use Vendor Clusterware however you must still install and use Oracle Clusterware for RAC. Best Practice is to leave Oracle Clusterware to manage RAC. For details see Metalink Note 332257.1 and for Veritas SFRAC see 397460.1.
27. Is RAC on VMW are supported?
Yes.
28. What is hangcheck timer used for ?
The hangcheck timer checks regularly the health of the system. If the system hangs or stop the node will be restarted automatically.
There are 2 key parameters for this module:
-> hangcheck-tick: this parameter defines the period of time between checks of system health. The default value is 60 seconds; Oracle recommends setting it to 30seconds.
-> hangcheck-margin: this defines the maximum hang delay that should be tolerated before hangcheck-timer resets the RAC node.
29. Is the hangcheck timer still needed with Oracle RAC 10g?
Yes.
30. What files can I put on Linux OCFS2?
For optimal performance, you should only put the following files on Linux OCFS2:
- Datafiles
- Control Files
- Redo Logs
- Archive Logs
- Shared Configuration File (OCR)
- Voting File
- SPFILE
31. Is it possible to use ASM for the OCR and voting disk?
No, the OCR and voting disk must be on raw or CFS (cluster file system).
32. Can I change the name of my cluster after I have created it when I am using Oracle Clusterware?
No, you must properly uninstall Oracle Clusterware and then re-install.
33. What the O2CB is?
The O2CB is the OCFS2 cluster stack. OCFS2 includes some services. These services must be started before using OCFS2 (mount/ format the file systems).
34. What the OCR file is used for?
OCR is a file that manages the cluster and RAC configuration.
35. What the Voting Disk file is used for?
The voting disk is nothing but a file that contains and manages information of all the node memberships.
36. What is the recommended method to make backups of a RAC environment?
RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file.
37. What command would you use to check the availability of the RAC system?
crs_stat -t -v (-t -v are optional)
38. What is the minimum number of instances you need to have in order to create a RAC?
You can create a RAC with just one server.
39. Name two specific RAC background processes
RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG.
40.Can you have many database versions in the same RAC?
Yes, but Clusterware version must be greater than the greater database version.
41. What was RAC previous name before it was called RAC?
OPS: Oracle Parallel Server
42. What RAC component is used for communication between instances?
Private Interconnect.
43. What is the difference between normal views and RAC views?
A RAC view has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION
44. Which command will we use to manage (stop, start) RAC services in command-line mode?
srvctl
45. How many alert logs exist in a RAC environment?
One for each instance.
46. 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
47. How do you backup voting disk
#dd if=voting_disk_name of=backup_file_name
48. How do I identify the voting disk location
#crsctl query css votedisk
49. How do I identify the OCR file location
check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck
50. 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
51. 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.
52. 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.
53. 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.
54. 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.
55. 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.
56. 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.
57. 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.
58. 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.
59. 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.
60. 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.
61. 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.
1. Where are the Clusterware files stored on a RAC environment?
The Clusterware is installed on each node (on an Oracle Home) and on the shared disks (the voting disks and the CSR file)
2. Where are the database software files stored on a RAC environment?
The base software is installed on each node of the cluster and the
database storage on the shared disks.
3. What kind of storage we can use for the shared Clusterware files?
- OCFS (Release 1 or 2)
- raw devices
- third party cluster file system such as GPFS or Veritas
4. What kind of storage we can use for the RAC database storage?
- OCFS (Release 1 or 2)
- ASM
- raw devices
- third party cluster file system such as GPFS or Veritas
5. What is a CFS?
A cluster File System (CFS) is a file system that may be accessed (read and write) by all members in a cluster at the same time. This implies that all members of a cluster have the same view.
6. What is an OCFS2?
The OCFS2 is the Oracle (version 2) Cluster File System which can be used for the Oracle Real Application Cluster.
7. Which files can be placed on an Oracle Cluster File System?
- Oracle Software installation (Windows only)
- Oracle files (controlfiles, datafiles, redologs, files described by the bfile datatype)
- Shared configuration files (spfile)
- OCR and voting disk
- Files created by Oracle during runtime
Note: There are some platform specific limitations.
8. Do you know another Cluster Vendor?
HP Tru64 Unix, Veritas, Microsoft
9. How is possible to install a RAC if we don’t have a CFS?
This is possible by using a raw device.
10. What is a raw device?
A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks.
11. What is a raw partition?
A raw partition is a portion of a physical disk that is accessed at the lowest possible level. A raw partition is created when an extended partition is created and logical partitions are assigned to it without any formatting. Once formatting is complete, it is called cooked partition.
12. When to use CFS over raw?
A CFS offers:
- Simpler management
- Use of Oracle Managed Files with RAC
- Single Oracle Software installation
- Autoextend enabled on Oracle datafiles
- Uniform accessibility to archive logs in case of physical node failure
- With Oracle_Home on CFS, when you apply Oracle patches CFS guarantees that the updated Oracle_Home is visible to all nodes in the cluster.
Note: This option is very dependent on the availability of a CFS on your platform.
13. When to use raw over CFS?
- Always when CFS is not available or not supported by Oracle.
- The performance is very, very important: Raw devices offer best performance without any intermediate layer between Oracle and the disk.
Note: Autoextend fails on raw devices if the space is exhausted. However the space could be added online if needed.
14. What CRS is?
Oracle RAC 10g Release 1 introduced Oracle Cluster Ready Services (CRS), a platform-independent set of system services for cluster environments. In Release 2, Oracle has renamed this product to Oracle Clusterware.
15. What is VIP IP used for?
It returns a dead connection IMMIDIATELY, when its primary node fails. Without using VIP IP, the clients have to wait around 10 minutes to receive ORA-3113: “end of file on communications channel”. However, using Transparent Application Failover (TAF) could avoid ORA-3113.
16. Why we need to have configured SSH or RSH on the RAC nodes?
SSH (Secure Shell,10g+) or RSH (Remote Shell, 9i+) allows “oracle” UNIX account connecting to another RAC node and copy/ run commands as the local “oracle” UNIX account.
17. Is the SSH, RSH needed for normal RAC operations?
No. SSH or RSH are needed only for RAC, patch set installation and clustered database creation.
18. Do we have to have Oracle RDBMS on all nodes?
Each node of a cluster that is being used for a clustered database will typically have the RDBMS and RAC software loaded on it, but not actual data files (these need to be available via shared disk).
19. What are the restrictions on the SID with a RAC database? Is it limited to 5 characters?
The SID prefix in 10g Release 1 and prior versions was restricted to five characters by install/ config tools so that an ORACLE_SID of up to max of 5+3=8 characters can be supported in a RAC environment. The SID prefix is relaxed up to 8 characters in 10g Release 2, see bug 4024251 for more information.
20. Does Real Application Clusters support heterogeneous platforms?
The Real Application Clusters do not support heterogeneous platforms in the same cluster.
21. Are there any issues for the interconnect when sharing the same switch as the public network by using VLAN to separate the network?
RAC and Clusterware deployment best practices suggests that the interconnect (private connection) be deployed on a stand-alone, physically separate, dedicated switch. On big network the connections could be instable.
22. What is the Load Balancing Advisory?
To assist in the balancing of application workload across designated resources, Oracle Database 10g Release 2 provides the Load Balancing Advisory. This Advisory monitors the current workload activity across the cluster and for each instance where a service is active; it provides a percentage value of how much of the total workload should be sent to this instance as well as service quality flag.
23. How many nodes are supported in a RAC Database?
With 10g Release 2, we support 100 nodes in a cluster using Oracle Clusterware, and 100 instances in a RAC database. Currently DBCA has a bug where it will not go beyond 63 instances. There is also a documentation bug for the max-instances parameter. With 10g Release 1 the Maximum is 63.
24. What is the Cluster Verification Utiltiy (cluvfy)?
The Cluster Verification Utility (CVU) is a validation tool that you can use to check all the important components that need to be verified at different stages of deployment in a RAC environment.
25. What versions of the database can I use the cluster verification utility (cluvfy) with?
The cluster verification utility is release with Oracle Database 10g Release 2 but can also be used with Oracle Database 10g Release 1.
26. If I am using Vendor Clusterware such as Veritas, IBM, Sun or HP, do I still need Oracle Clusterware to run Oracle RAC 10g?
Yes. When certified, you can use Vendor Clusterware however you must still install and use Oracle Clusterware for RAC. Best Practice is to leave Oracle Clusterware to manage RAC. For details see Metalink Note 332257.1 and for Veritas SFRAC see 397460.1.
27. Is RAC on VMW are supported?
Yes.
28. What is hangcheck timer used for ?
The hangcheck timer checks regularly the health of the system. If the system hangs or stop the node will be restarted automatically.
There are 2 key parameters for this module:
-> hangcheck-tick: this parameter defines the period of time between checks of system health. The default value is 60 seconds; Oracle recommends setting it to 30seconds.
-> hangcheck-margin: this defines the maximum hang delay that should be tolerated before hangcheck-timer resets the RAC node.
29. Is the hangcheck timer still needed with Oracle RAC 10g?
Yes.
30. What files can I put on Linux OCFS2?
For optimal performance, you should only put the following files on Linux OCFS2:
- Datafiles
- Control Files
- Redo Logs
- Archive Logs
- Shared Configuration File (OCR)
- Voting File
- SPFILE
31. Is it possible to use ASM for the OCR and voting disk?
No, the OCR and voting disk must be on raw or CFS (cluster file system).
32. Can I change the name of my cluster after I have created it when I am using Oracle Clusterware?
No, you must properly uninstall Oracle Clusterware and then re-install.
33. What the O2CB is?
The O2CB is the OCFS2 cluster stack. OCFS2 includes some services. These services must be started before using OCFS2 (mount/ format the file systems).
34. What the OCR file is used for?
OCR is a file that manages the cluster and RAC configuration.
35. What the Voting Disk file is used for?
The voting disk is nothing but a file that contains and manages information of all the node memberships.
36. What is the recommended method to make backups of a RAC environment?
RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file.
37. What command would you use to check the availability of the RAC system?
crs_stat -t -v (-t -v are optional)
38. What is the minimum number of instances you need to have in order to create a RAC?
You can create a RAC with just one server.
39. Name two specific RAC background processes
RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG.
40.Can you have many database versions in the same RAC?
Yes, but Clusterware version must be greater than the greater database version.
41. What was RAC previous name before it was called RAC?
OPS: Oracle Parallel Server
42. What RAC component is used for communication between instances?
Private Interconnect.
43. What is the difference between normal views and RAC views?
A RAC view has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION
44. Which command will we use to manage (stop, start) RAC services in command-line mode?
srvctl
45. How many alert logs exist in a RAC environment?
One for each instance.
46. 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
47. How do you backup voting disk
#dd if=voting_disk_name of=backup_file_name
48. How do I identify the voting disk location
#crsctl query css votedisk
49. How do I identify the OCR file location
check /var/opt/oracle/ocr.loc or /etc/ocr.loc ( depends upon platform)
or
#ocrcheck
50. 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
51. 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.
52. 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.
53. 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.
54. 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.
55. 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.
56. 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.
57. 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.
58. 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.
59. 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.
60. 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.
61. 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.
Oracle DBA Interview Questions and Answers - Oracle
11g Database New Features
Oracle 11g Database New Features Interview
Questions and Answers
1. Database Replay
2. The SQL Performance Analyzer
3. Online Patching in Oracle Database Control
4. Automatic Diagnostic Repository (ADR)
5. Data Recovery Advisor
6. Automatic Memory Management
7. Invisible Indexes
8. Read-Only Tables
9. Shrinking Temporary Tablespaces and Tempfiles
10. Server Result Cache
11. SQL Tuning Automation
12. SQL Plan Management
13. Database ADDM
14. New SYSASM Privilege for ASM Administration
15. Enhanced Block Media Recovery
16. VALIDATE Command
17. Configuring an Archived Redo Log Deletion Policy
18. Active Database Duplication
19. Virtual Private Catalogs
20. ASM Restricted Mode
21. Checking Diskgroup
22. The FORCE option with Drop Diskgroup Command
23. Active Data Guard is a new option for Oracle Database 11g Enterprise Edition
ASM Rolling Upgrades
Database Replay
Database Replay (sometimes named as Workload Replay) feature in Oracle11g allows you to reproduce the
production database conditions in a testing environment.In other words, with this feature you can capture the
actual workload on a production system and replay it in a test system. This way, you can analyze the condition of the production database without working on the actual production database.
This feature enables you to test the impact of applying changes on a production database. These changes could be database upgrades, switching to RAC, application upgrades, operating system upgrades or storage system changes.
The SQL Performance Analyzer
The SQL Performance Analyzer (SPA) aims at measuring the impact of applying any change on the database on the performance of the SQL statements execution. If it finds out performance degradation in one or more SQL statements, it provides you recommendations on how to improve their performance.
This is very useful for a DBA to analyze how a change on the database (including database upgrade) may affect the execution efficiency of SQL statements. Using this tool is explained here because you may consider using it to study the effect of upgrading an Oracle database 10g release 2 to 11g.
Note:
If you plan to use SPA on a test database, it is highly recommended to make the test database resemble the production database as closely as possible. You can use the RMAN duplicate command for this purpose.
Online Patching in Oracle Database Control
Patching through Database Control is enhanced in Oracle 11g. With Oracle 11g online patching (or called hot patching),you can apply or roll back a database patch while the instance is running. Also it can detect conflicts between two online patches.On the other hand, online patching consumes more memory than the conventional method.
In UNIX systems, you use the script $ORACLE_HOME/OPatch/opatch to invoke the online patching.
Automatic Diagnostic Repository (ADR)
The Automatic Diagnostic Repository (ADR) is a file system repository to store diagnostic data source such as alter log, trace files, user and background dump files, and also new types of troubleshooting files such as Health Monitor reports, Incident packages, SQL test cases and Data repair records.
In Oracle 11g, there is a new framework (named as fault diagnosability infrastructure) consisting of many tools for diagnosing and repairing the errors in the database. All those tools refer to the ADR in their operation.
ADR is developed to provide the following advantages:
1.Diagnosis data, because it is stored in file system, is available even when the database is down.
2.It is easier to provide Oracle support with diagnosis data when a problem occurs in the database.
3.ADR has diagnosis data not only for the database instance. It has troubleshooting data for other Oracle components such as ASM and CRS.
Note:
For each database instance two alert log files are generated: one as text file and one with xml
format. Contents of the xml-formatted file can be examined using adrci tool.
Also the xml-formatted alert log is saved in the ADR and specifically in the directory
$ORACLE_BASE/diag/rdbms/$INSTANCE_NAME/$ORACLE_SID/alert
Data Recovery Advisor
Data Recovery Advisor is an Oracle Database 11g tool that automatically diagnoses data failures, determines and presents appropriate repair options,and executes repairs at the user's request. Data Recovery Advisor can diagnose failures such as the following:
1. Inaccessible components like datafiles and control files.
2. Physical corruptions such as block checksum failures and invalid block header
3. Field values
4. Inconsistent datafiles (online and offline)
5. I/O failures
The advisor however doe not recover from failures on standby databases or RAC environment. This advisor can be used through RMAN or the Enterprise Manager.
Automatic Memory Management
In Oracle 11g, a new parameter named as MEMORY_TARGET is added to automate memory allocation for both the SGA and PGA. When this parameter is set, the SGA and the PGA memory sizes are automatically determined by the instance based on the database workload.
This parameter is dynamic and can be alter using the ALTER SYSTEM command as shown below:
ALTER SYSTEM SET MEMORY_TARGET = 1024M ;
However, if the database is not configured to use this parameter and you want to use it, you must restart the
database after setting the parameter.
Invisible Indexes
Invisible index is an index that is not considered by the optimizer when creating the execution plans. This can be used to test the effect of adding an index to a table on a query (using index hint) without actually being used by the other queries.
When using invisible indexes, consider the following:
- If you rebuild an invisible index, the resulting operation will make the index visible.
- If you want the optimizer to consider the invisible indexes in its operation, you can set the new initialization
parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE (the default is FALSE). You can set the parameter in the system and session levels.
Read-Only Tables
In Oracle 11g, you can set a table to be read only,i.e. users can only query from the table but no DML
statement is allowed on the table.
Shrinking Temporary Tablespaces and Tempfiles
In Oracle 11g, you can shrink temporary tablespaces and tempfiles.
Server Result Cache
In Oracle 11g, there is a new SGA component called result cache, which is used cache SQL query and PL/SQL function results. The database serves the results for the executed SQL queries and PL/SQL functions from the cache instead of re-executing the actual query. Of course,the target is to obtain high response time. The cached results stored become invalid when data in the dependent database objects is modified.
As clear from its concept, result cache is mostly useful in for frequently executed queries with rare changes on the retrieved data.
SQL Tuning Automation
The SQL Tuning Advisor is run by default every night during the automated maintenance window. Basically, the advisor catches the SQL statements from AWR that are candidate for tuning (they are called buckets) during four different time periods.It then automatically creates SQL profile for any poor SQL statement, if that helps. Tuned plans are automatically added to the SQL plan baselines by the automatic SQL tuning task.
The advisor also may recommend actions like creating new indexes, refreshing statistics or re-writing the statement. These actions, however, are not automatically implemented by the advisor.
On Oracle 11g Release 2 (11.2.0.2), a new package named as DBMS_AUTO_SQLTUNE should be used instead of the DBMS_SQLTUNE package. The new package provides more restrictive access to the Automatic SQL Tuning feature.
To use the DBMS_AUTO_SQLTUNE package, you must have the DBA role, or have EXECUTE privileges granted by an administrator. The only exception is the EXECUTE_AUTO_TUNING_TASK procedure, which can only be run by SYS.
SQL Plan Management
SQL plan management (SPM), is a new feature in Oracle 11g that prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information.Changes to the execution plan may be resulted from database upgrades,system and data changes, application upgrade or bug fixes.
When SPM is enabled, the system maintains a plan history that contains all plans generated by the optimizer and store them in a component called plan baseline. Among the plan history in the plan baseline, plans that are verified not to cause performance regression are marked as acceptable. The plan baseline is used by the optimizer to decide on the best plan to use when compiling a SQL statement.
Repository stored in data dictionary of plan baselines and statement log maintained by the optimizer is called
SQL management base(SMB).
SQL Plan management is implemented by undertaking the following phases:
1.Capturing SQL Plan Baselines: this can be done automatically or manually.
2.Selecting SQL Plan Baselines by the optimizer
3.Evolving SQL Plan Baselines
Database ADDM
Oracle Database 11g has added a new layer of analysis to ADDM called Database ADDM. The mode ADDM was working in Oracle 10g is now called instance ADDM. The main target of database ADDM is to analyze and report on RAC environment. To enable Database ADDM, you set the parameter INSTANCES in DBMS_ADVISOR.
New SYSASM Privilege for ASM Administration
SYSASM is a new privilege introduced in Oracle 11g. Users who are granted this privilege can perform ASM administration tasks. The idea behind this privilege is to separate database management and the storage management responsibilities.
Backup and Recovery New Features:-
Enhanced Block Media Recovery
In Oracle Database 11g, there is a new command to perform block media recovery, named the recover ...
blockcommand replacing the old blockrecover command. The new command is more efficient since because it searches the flashback logs for older uncorrupted versions of the corrupt blocks. This requires the database to work in archivelog mode and has the Database Flashback enabled.
While the block media recovery is going on, any attempt by users to access data in the corrupt blocks will result in an error message, telling the user that the data block is corrupt.
VALIDATE Command
You can use the new command VALIDATE to manually check for physical and logical corruptions in datafiles,backup sets, and even individual data blocks. The comma nd by default checks for physical corruption. You can optionally specify CHECK LOGICAL . Corrupted blocks are reported in V$DATABASE_BLOCK_CORRUPTION.
Configuring an Archived Redo Log Deletion Policy
You can use RMAN to create a persistent configuration that controls when archived redo logs are eligible for
deletion from disk or tape. This deletion policy applies to all archiving destinations, including the flash recovery area. When the policy is configured, it applies on the automatic deletion of the logs in the flash recovery area and the manual deletion by the BACKUP ... DELETE and DELETE ... ARCHIVELOG commands.
To enable an archived redo log deletion policy, run the CONFIGURE ARCHIVELOG DELETION POLICY BACKED UP n TIMES command with the desired options.
Active Database Duplication
In Oracle Database 11g, you can directly duplicate a data base over the network without having to back up and provide the source database files. This direct database duplication is called active database duplication. It can be done either with Database Control or through RMAN. Instance that runs the duplicated database is called auxiliary instance.
Virtual Private Catalogs
In Oracle Database 11g, you can restrict access to the recovery catalog by granting access to only a subset of the metadata in the recovery catalog. The subset that a user has read/write access to is termed as virtual private catalog, or just virtual catalog. The central or source recovery catalog is now called the base recovery catalog.
ASM Restricted Mode
In Oracle 11g, you can start the ASM instance in restricted mode. When in restricted mode, databases will not be permitted to access the ASM instance. Also, individual diskgroup can be set in restricted mode.
Checking Diskgroup
Starting from Oracle Database 11g, you can validate the internal consistency of ASM diskgroup metadata using the ALTER DISKGROUP ... CHECK command. Summary of errors is logged in the ASM alert log file.
The FORCE option with Drop Diskgroup Command
If a disk is destroyed beyond repair, you want to drop it. But because the disk is practically damaged, you cannot mount it and thus you cannot issue the DROP DISKGROUP command against it. In such a condition, Oracle 11g provides the FORCE INCLUDING CONTENTS option to drop the diskgroup even if it is not mounted.
SQL>DROP DISKGROUP <DISKGROUP_NAME> FORCE INCLUDING CONTENTS;
ASM Rolling Upgrades
ASM rolling upgrades enable you to independently upgrade or patch clustered ASM nodes without affecting
database availability, thus providing greater uptime. Rolling upgrade means that all of the features of a clustered ASM environment function when one or more of the nodes in the cluster use different software versions.
Active Data Guard is a new option for Oracle Database 11g Enterprise Edition
An Active Data Guard standby database is an exact copy of the primary that is open read-only while it continuously applies changes transmitted by the primary database. An active standby can offload ad-hoc queries, reporting, and fast incremental backups from the primary database, improving performance and scalability while preventing data loss or downtime due to data corruptions, database and site failures, human error, or natural disaster. Oracle Active Data Guard enables read-only access to a physical standby database.
With Oracle Active Data Guard, a physical standby database can be used for real-time reporting, with minimal latency between reporting and production data. Compared with traditional replication methods, Active Data Guard is very simple to use, transparently supports all datatypes, and offers very high performance. Oracle Active Data Guard also allows backup operations to be off-loaded to the standby database, and be done very fast using intelligent incremental backups.
Active Dataguard Features:
1. Physical Standby with Real-time Query
2. Fast Incremental Backup on Physical Standby.
3. Automatic Block Repair.
http://ahmedbaraka.com/computer/docs/oracle_11g_nf_1.0.pdf
http://pocketoracle.blogspot.com/search/label/10g%20New%20Features
http://pocketoracle.blogspot.com/search/label/11g%20New%20Features
1. Database Replay
2. The SQL Performance Analyzer
3. Online Patching in Oracle Database Control
4. Automatic Diagnostic Repository (ADR)
5. Data Recovery Advisor
6. Automatic Memory Management
7. Invisible Indexes
8. Read-Only Tables
9. Shrinking Temporary Tablespaces and Tempfiles
10. Server Result Cache
11. SQL Tuning Automation
12. SQL Plan Management
13. Database ADDM
14. New SYSASM Privilege for ASM Administration
15. Enhanced Block Media Recovery
16. VALIDATE Command
17. Configuring an Archived Redo Log Deletion Policy
18. Active Database Duplication
19. Virtual Private Catalogs
20. ASM Restricted Mode
21. Checking Diskgroup
22. The FORCE option with Drop Diskgroup Command
23. Active Data Guard is a new option for Oracle Database 11g Enterprise Edition
ASM Rolling Upgrades
Database Replay
Database Replay (sometimes named as Workload Replay) feature in Oracle11g allows you to reproduce the
production database conditions in a testing environment.In other words, with this feature you can capture the
actual workload on a production system and replay it in a test system. This way, you can analyze the condition of the production database without working on the actual production database.
This feature enables you to test the impact of applying changes on a production database. These changes could be database upgrades, switching to RAC, application upgrades, operating system upgrades or storage system changes.
The SQL Performance Analyzer
The SQL Performance Analyzer (SPA) aims at measuring the impact of applying any change on the database on the performance of the SQL statements execution. If it finds out performance degradation in one or more SQL statements, it provides you recommendations on how to improve their performance.
This is very useful for a DBA to analyze how a change on the database (including database upgrade) may affect the execution efficiency of SQL statements. Using this tool is explained here because you may consider using it to study the effect of upgrading an Oracle database 10g release 2 to 11g.
Note:
If you plan to use SPA on a test database, it is highly recommended to make the test database resemble the production database as closely as possible. You can use the RMAN duplicate command for this purpose.
Online Patching in Oracle Database Control
Patching through Database Control is enhanced in Oracle 11g. With Oracle 11g online patching (or called hot patching),you can apply or roll back a database patch while the instance is running. Also it can detect conflicts between two online patches.On the other hand, online patching consumes more memory than the conventional method.
In UNIX systems, you use the script $ORACLE_HOME/OPatch/opatch to invoke the online patching.
Automatic Diagnostic Repository (ADR)
The Automatic Diagnostic Repository (ADR) is a file system repository to store diagnostic data source such as alter log, trace files, user and background dump files, and also new types of troubleshooting files such as Health Monitor reports, Incident packages, SQL test cases and Data repair records.
In Oracle 11g, there is a new framework (named as fault diagnosability infrastructure) consisting of many tools for diagnosing and repairing the errors in the database. All those tools refer to the ADR in their operation.
ADR is developed to provide the following advantages:
1.Diagnosis data, because it is stored in file system, is available even when the database is down.
2.It is easier to provide Oracle support with diagnosis data when a problem occurs in the database.
3.ADR has diagnosis data not only for the database instance. It has troubleshooting data for other Oracle components such as ASM and CRS.
Note:
For each database instance two alert log files are generated: one as text file and one with xml
format. Contents of the xml-formatted file can be examined using adrci tool.
Also the xml-formatted alert log is saved in the ADR and specifically in the directory
$ORACLE_BASE/diag/rdbms/$INSTANCE_NAME/$ORACLE_SID/alert
Data Recovery Advisor
Data Recovery Advisor is an Oracle Database 11g tool that automatically diagnoses data failures, determines and presents appropriate repair options,and executes repairs at the user's request. Data Recovery Advisor can diagnose failures such as the following:
1. Inaccessible components like datafiles and control files.
2. Physical corruptions such as block checksum failures and invalid block header
3. Field values
4. Inconsistent datafiles (online and offline)
5. I/O failures
The advisor however doe not recover from failures on standby databases or RAC environment. This advisor can be used through RMAN or the Enterprise Manager.
Automatic Memory Management
In Oracle 11g, a new parameter named as MEMORY_TARGET is added to automate memory allocation for both the SGA and PGA. When this parameter is set, the SGA and the PGA memory sizes are automatically determined by the instance based on the database workload.
This parameter is dynamic and can be alter using the ALTER SYSTEM command as shown below:
ALTER SYSTEM SET MEMORY_TARGET = 1024M ;
However, if the database is not configured to use this parameter and you want to use it, you must restart the
database after setting the parameter.
Invisible Indexes
Invisible index is an index that is not considered by the optimizer when creating the execution plans. This can be used to test the effect of adding an index to a table on a query (using index hint) without actually being used by the other queries.
When using invisible indexes, consider the following:
- If you rebuild an invisible index, the resulting operation will make the index visible.
- If you want the optimizer to consider the invisible indexes in its operation, you can set the new initialization
parameter OPTIMIZER_USE_INVISIBLE_INDEXES to TRUE (the default is FALSE). You can set the parameter in the system and session levels.
Read-Only Tables
In Oracle 11g, you can set a table to be read only,i.e. users can only query from the table but no DML
statement is allowed on the table.
Shrinking Temporary Tablespaces and Tempfiles
In Oracle 11g, you can shrink temporary tablespaces and tempfiles.
Server Result Cache
In Oracle 11g, there is a new SGA component called result cache, which is used cache SQL query and PL/SQL function results. The database serves the results for the executed SQL queries and PL/SQL functions from the cache instead of re-executing the actual query. Of course,the target is to obtain high response time. The cached results stored become invalid when data in the dependent database objects is modified.
As clear from its concept, result cache is mostly useful in for frequently executed queries with rare changes on the retrieved data.
SQL Tuning Automation
The SQL Tuning Advisor is run by default every night during the automated maintenance window. Basically, the advisor catches the SQL statements from AWR that are candidate for tuning (they are called buckets) during four different time periods.It then automatically creates SQL profile for any poor SQL statement, if that helps. Tuned plans are automatically added to the SQL plan baselines by the automatic SQL tuning task.
The advisor also may recommend actions like creating new indexes, refreshing statistics or re-writing the statement. These actions, however, are not automatically implemented by the advisor.
On Oracle 11g Release 2 (11.2.0.2), a new package named as DBMS_AUTO_SQLTUNE should be used instead of the DBMS_SQLTUNE package. The new package provides more restrictive access to the Automatic SQL Tuning feature.
To use the DBMS_AUTO_SQLTUNE package, you must have the DBA role, or have EXECUTE privileges granted by an administrator. The only exception is the EXECUTE_AUTO_TUNING_TASK procedure, which can only be run by SYS.
SQL Plan Management
SQL plan management (SPM), is a new feature in Oracle 11g that prevents performance regressions resulting from sudden changes to the execution plan of a SQL statement by providing components for capturing, selecting, and evolving SQL plan information.Changes to the execution plan may be resulted from database upgrades,system and data changes, application upgrade or bug fixes.
When SPM is enabled, the system maintains a plan history that contains all plans generated by the optimizer and store them in a component called plan baseline. Among the plan history in the plan baseline, plans that are verified not to cause performance regression are marked as acceptable. The plan baseline is used by the optimizer to decide on the best plan to use when compiling a SQL statement.
Repository stored in data dictionary of plan baselines and statement log maintained by the optimizer is called
SQL management base(SMB).
SQL Plan management is implemented by undertaking the following phases:
1.Capturing SQL Plan Baselines: this can be done automatically or manually.
2.Selecting SQL Plan Baselines by the optimizer
3.Evolving SQL Plan Baselines
Database ADDM
Oracle Database 11g has added a new layer of analysis to ADDM called Database ADDM. The mode ADDM was working in Oracle 10g is now called instance ADDM. The main target of database ADDM is to analyze and report on RAC environment. To enable Database ADDM, you set the parameter INSTANCES in DBMS_ADVISOR.
New SYSASM Privilege for ASM Administration
SYSASM is a new privilege introduced in Oracle 11g. Users who are granted this privilege can perform ASM administration tasks. The idea behind this privilege is to separate database management and the storage management responsibilities.
Backup and Recovery New Features:-
Enhanced Block Media Recovery
In Oracle Database 11g, there is a new command to perform block media recovery, named the recover ...
blockcommand replacing the old blockrecover command. The new command is more efficient since because it searches the flashback logs for older uncorrupted versions of the corrupt blocks. This requires the database to work in archivelog mode and has the Database Flashback enabled.
While the block media recovery is going on, any attempt by users to access data in the corrupt blocks will result in an error message, telling the user that the data block is corrupt.
VALIDATE Command
You can use the new command VALIDATE to manually check for physical and logical corruptions in datafiles,backup sets, and even individual data blocks. The comma nd by default checks for physical corruption. You can optionally specify CHECK LOGICAL . Corrupted blocks are reported in V$DATABASE_BLOCK_CORRUPTION.
Configuring an Archived Redo Log Deletion Policy
You can use RMAN to create a persistent configuration that controls when archived redo logs are eligible for
deletion from disk or tape. This deletion policy applies to all archiving destinations, including the flash recovery area. When the policy is configured, it applies on the automatic deletion of the logs in the flash recovery area and the manual deletion by the BACKUP ... DELETE and DELETE ... ARCHIVELOG commands.
To enable an archived redo log deletion policy, run the CONFIGURE ARCHIVELOG DELETION POLICY BACKED UP n TIMES command with the desired options.
Active Database Duplication
In Oracle Database 11g, you can directly duplicate a data base over the network without having to back up and provide the source database files. This direct database duplication is called active database duplication. It can be done either with Database Control or through RMAN. Instance that runs the duplicated database is called auxiliary instance.
Virtual Private Catalogs
In Oracle Database 11g, you can restrict access to the recovery catalog by granting access to only a subset of the metadata in the recovery catalog. The subset that a user has read/write access to is termed as virtual private catalog, or just virtual catalog. The central or source recovery catalog is now called the base recovery catalog.
ASM Restricted Mode
In Oracle 11g, you can start the ASM instance in restricted mode. When in restricted mode, databases will not be permitted to access the ASM instance. Also, individual diskgroup can be set in restricted mode.
Checking Diskgroup
Starting from Oracle Database 11g, you can validate the internal consistency of ASM diskgroup metadata using the ALTER DISKGROUP ... CHECK command. Summary of errors is logged in the ASM alert log file.
The FORCE option with Drop Diskgroup Command
If a disk is destroyed beyond repair, you want to drop it. But because the disk is practically damaged, you cannot mount it and thus you cannot issue the DROP DISKGROUP command against it. In such a condition, Oracle 11g provides the FORCE INCLUDING CONTENTS option to drop the diskgroup even if it is not mounted.
SQL>DROP DISKGROUP <DISKGROUP_NAME> FORCE INCLUDING CONTENTS;
ASM Rolling Upgrades
ASM rolling upgrades enable you to independently upgrade or patch clustered ASM nodes without affecting
database availability, thus providing greater uptime. Rolling upgrade means that all of the features of a clustered ASM environment function when one or more of the nodes in the cluster use different software versions.
Active Data Guard is a new option for Oracle Database 11g Enterprise Edition
An Active Data Guard standby database is an exact copy of the primary that is open read-only while it continuously applies changes transmitted by the primary database. An active standby can offload ad-hoc queries, reporting, and fast incremental backups from the primary database, improving performance and scalability while preventing data loss or downtime due to data corruptions, database and site failures, human error, or natural disaster. Oracle Active Data Guard enables read-only access to a physical standby database.
With Oracle Active Data Guard, a physical standby database can be used for real-time reporting, with minimal latency between reporting and production data. Compared with traditional replication methods, Active Data Guard is very simple to use, transparently supports all datatypes, and offers very high performance. Oracle Active Data Guard also allows backup operations to be off-loaded to the standby database, and be done very fast using intelligent incremental backups.
Active Dataguard Features:
1. Physical Standby with Real-time Query
2. Fast Incremental Backup on Physical Standby.
3. Automatic Block Repair.
http://ahmedbaraka.com/computer/docs/oracle_11g_nf_1.0.pdf
http://pocketoracle.blogspot.com/search/label/10g%20New%20Features
http://pocketoracle.blogspot.com/search/label/11g%20New%20Features
Posted by Dharmendra
Chalasani at Wednesday, September 11, 2013
July 24, 2013
Oracle ASM Interview Questions and Answers
What is ASM in Oracle?
Oracle ASM is Oracle’s volume manager specially designed for Oracle database data. It is available since Oracle database version 10g and many improvements have been made in versions 11g release 1 and 2.
ASM offers support for Oracle RAC clusters without the requirement to install 3rd party software, such as cluster aware volume managers or filesystems.
ASM is shipped as part of the database server software (Enterprise and Standard editions) and does not cost extra money to run.
ASM simplifies administration of Oracle related files by allowing the administrator to reference disk groups
rather than individual disks and files, which are managed by ASM.
The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.
Advantages of ASM in Oracle?
Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
Uses redundancy features available in intelligent storage arrays
The storage system can store all types of database files
Using disk group makes configuration easier, as files are placed into disk groups
ASM provides stripping and mirroring (fine and coarse gain - see below)
ASM and non-ASM oracle files can coexist
Striping—ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.
For example, if there are six disks in a disk group, pieces of each ASM file are written to all six disks. These pieces come in 1 MB chunks known as extents. When a database file is created, it is striped (divided into extents and distributed) across the six disks, and allocated disk space on all six disks grows evenly. When reading the file, file extents are read from all six disks in parallel, greatly increasing performance.
Mirroring—ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extent of the file, to help avoid data loss caused by disk failures. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group.
ASM supports 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring, where each file extent gets two mirrored copies.
Online storage reconfiguration and dynamic rebalancing—ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances by evenly relocating all file extents from the disk being removed to the other disks in the disk group.
Managed file creation and deletion—ASM further reduces administration tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns filenames when files are created, and automatically deletes files when they are no longer needed.
What is ASM instance in Oracle?
The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight.
Characteristics of Oracle ASM instance
--------------------------------------
1. do not have controlfile and datafiles, do not have online redo logs
2. do have init.ora and a passwordfile
3. for connecting remotely, create passwordfile and set following in init.ora
remote_login_passwordfile=exclusive
create a password file:
$ORACLE_HOME/bin/orapwd file=orapw+ASM1 password=yourpw entries=10
4. ASM instance can not be in open status as there are not datafiles. Can be in mount (although
there is no controlfile) and nomount status. When in mount status, database can use the
diskgroup. The mount status actually means mount disk groups.
What are ASM Background Processes in Oracle?Both an Oracle ASM instance and an Oracle Database instance are built on the same technology. Like a database instance, an Oracle ASM instance has memory structures (System Global Area) and background processes. Besides, Oracle ASM has a minimal performance impact on a server. Rather than mounting a database, Oracle ASM instances mount disk groups to make Oracle ASM files available to database instances.
There are at least two new background processes added for an ASM instance:
ASM Instance Background Processes:
---------------------------------
ARBx (ASM) Rebalance working processARBn performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these processes running at a time, named ARB0, ARB1, and so on.These processes are managed by the RBAL process. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.
RBAL (Re-balancer) RBAL runs in both database and ASM instances. In the database instance, it does a global open of ASM disks. In an ASM instance, it also coordinates rebalance activity for disk groups.RBAL, which coordinates rebalance activities
for disk resources controlled by ASM.
Database Instance ASM Background Processes:
------------------------------------------In the database instances, there are three background process to support ASM, namely:
ASMB, this process contact CSS using the group name and acquires the associated ASM connect string. The connect string is subsequently used to connect to the ASM instance.
RBAL, which performs global opens on all disks in the disk group.A global open means that more than one database instance can be accessing the ASM disks at a time.
O00x, a group slave processes, with a numeric sequence starting at 000.
What are the components of components of ASM are disk groups?
The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files. The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.
Failure groups are defined within a disk group to support the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations.
What are ASM instance initialization parameters?
INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.
Advantages of ASM in Oracle?
Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
Uses redundancy features available in intelligent storage arrays
The storage system can store all types of database files
Using disk group makes configuration easier, as files are placed into disk groups
ASM provides stripping and mirroring (fine and coarse gain - see below)
ASM and non-ASM oracle files can coexist
Striping—ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.
For example, if there are six disks in a disk group, pieces of each ASM file are written to all six disks. These pieces come in 1 MB chunks known as extents. When a database file is created, it is striped (divided into extents and distributed) across the six disks, and allocated disk space on all six disks grows evenly. When reading the file, file extents are read from all six disks in parallel, greatly increasing performance.
Mirroring—ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extent of the file, to help avoid data loss caused by disk failures. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group.
ASM supports 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring, where each file extent gets two mirrored copies.
Online storage reconfiguration and dynamic rebalancing—ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances by evenly relocating all file extents from the disk being removed to the other disks in the disk group.
Managed file creation and deletion—ASM further reduces administration tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns filenames when files are created, and automatically deletes files when they are no longer needed.
Why should we use separate ASM home?
ASM should be installed separately from the database software in its own ORACLE_HOME directory. This will allow you the flexibility to patch and upgrade ASM and the database software independently.
How many ASM instances should one have?
Several databases can share a single ASM instance. So, although one can create multiple ASM instances on a single system, normal configurations should have one and only one ASM instance per system.
For clustered systems, create one ASM instance per node (called +ASM1, +ASM2, etc).
How many diskgroups should one have?
Generally speaking one should have only one disk group for all database files - and, optionally a second for recovery files (see FRA).
Data with different storage characteristics should be stored in different disk groups. Each disk group can have different redundancy (mirroring) settings (high, normal and external), different fail-groups, etc. However, it is generally not necessary to create many disk groups with the same storage characteristics (i.e. +DATA1, +DATA2, etc. all on the same type of disks).
To get started, create 2 disk groups - one for data and one for recovery files. Here is an example:
CREATE DISKGROUP data EXTERNAL REDUNDANCY DISK '/dev/d1', '/dev/d2', '/dev/d3', ....;
CREATE DISKGROUP recover EXTERNAL REDUNDANCY DISK '/dev/d10', '/dev/d11', '/dev/d12', ....;
Here is an example how you can enable automatic file management with such a setup:
ALTER SYSTEM SET db_create_file_dest = '+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER' SCOPE=SPFILE;
You may also decide to introduce additional disk groups - for example, if you decide to put historic data on low cost disks, or if you want ASM to mirror critical data across 2 storage cabinets.
What is ASM Rebalancing?
The rebalancing speed is controlled by the ASM_POWER_LIMIT initialization parameter. Setting it to 0 will disable disk rebalancing.
ALTER DISKGROUP data REBALANCE POWER 11;
What happens when an Oracle ASM diskgroup is created?
When an ASM diskgroup is created, a hierarchialfilesystem structure is created.
How does this filesystem structure appear?
Oracle ASM diskgroup'sfilesystem structure is similar to UNIX filesystem hierarchy or Windows filesystem hierarchy.
Where are the Oracle ASM files stored?
Oracle ASM files are stored within the Oracle ASM diskgroup. If we dig into internals, oracle ASM files are stored within the Oracle ASM filesystem structures.
How are the Oracle ASM files stored within the Oracle ASM filesystem structure?
Oralce ASM files are stored within the Oracle ASM filesystem structures as objects that RDBMS instances/Oracle database instance access. RDBMS/Oracle instance treats the Oracle ASM files as standard filesystem files.
What are the Oracle ASM files that are stored within the Oracle ASM file hierarchy?
Files stored in Oracle ASM diskgroup/Oracl ASM filestructures include:
1) Datafile
2) Controlfiles
3) Server Parameter Files(SPFILE)
4) Redo Log files
What happens when you create a file/database file in ASM?What commands do you use to create database files?
Some common commands used for creating database files are :
1) Create tabespace
2) Add Datafile
3) Add Logfile
For example,
SQL> CREATE TABLESPACE TS1 DATAFILE '+DATA1' SIZE 10GB;
Above command creates a datafile in DATA1 diskgroup
How can you access a databasefile in ASM diskgroup under RDBMS?
Once the ASM file is created in ASM diskgroup, a filename is generated. This file is now visible to the user via the standard RDBMS view V$DATAFILE.
What will be the syntax of ASM filenames?
ASM filename syntax is as follows:
+diskgroup_name/database_name/database_file_type/tag_name.file_number.incarnation
where,
+diskgroup_name - Name of the diskgroup that contains this file
database_name - Name of the database that contains this file
datafile - Can be one among 20 different ASM file types
tag_name - corresponds to tablespace name for datafiles, groupnumber for redo log files
file_number - file_number in ASM instance is used to correlate filenames in database instance
incarnation_number - It is derived from the timestamp. IT is used to provide uniqueness
What is an incarnation number?
An incarnation number is a part of ASM filename syntax. It is derived from the timestamp. Once the file is created, its incarnation number doesnot change.
What is the use of an incarnation number in Oracle ASM filename?
Incarnation number distinguishes between a new file that has been created using the same file number and another file that has been deleted
ASM's SPFile will be residing inside ASM itself. This could be found out in number of ways, looking at the alert log of ASM when ASM starts
Machine: x86_64
Using parameter settings in server-side spfile +DATA/asm/asmparameterfile/registry.253.766260991
System parameters with non-default values:
large_pool_size = 12M
instance_type = "asm"
remote_login_passwordfile= "EXCLUSIVE"
asm_diskgroups = "FLASH"
asm_diskgroups = "DATA"
asm_power_limit = 1
diagnostic_dest = "/opt/app/oracle"
Or using the asmcmd's spget command which shows the spfile location registered with GnP profile
ASMCMD> spget
+DATA/asm/asmparameterfile/registry.253.766260991
What is ASM in Oracle?
Oracle ASM is Oracle’s volume manager specially designed for Oracle database data. It is available since Oracle database version 10g and many improvements have been made in versions 11g release 1 and 2.
ASM offers support for Oracle RAC clusters without the requirement to install 3rd party software, such as cluster aware volume managers or filesystems.
ASM is shipped as part of the database server software (Enterprise and Standard editions) and does not cost extra money to run.
ASM simplifies administration of Oracle related files by allowing the administrator to reference disk groups
rather than individual disks and files, which are managed by ASM.
The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.
Advantages of ASM in Oracle?
Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
Uses redundancy features available in intelligent storage arrays
The storage system can store all types of database files
Using disk group makes configuration easier, as files are placed into disk groups
ASM provides stripping and mirroring (fine and coarse gain - see below)
ASM and non-ASM oracle files can coexist
Striping—ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.
For example, if there are six disks in a disk group, pieces of each ASM file are written to all six disks. These pieces come in 1 MB chunks known as extents. When a database file is created, it is striped (divided into extents and distributed) across the six disks, and allocated disk space on all six disks grows evenly. When reading the file, file extents are read from all six disks in parallel, greatly increasing performance.
Mirroring—ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extent of the file, to help avoid data loss caused by disk failures. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group.
ASM supports 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring, where each file extent gets two mirrored copies.
Online storage reconfiguration and dynamic rebalancing—ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances by evenly relocating all file extents from the disk being removed to the other disks in the disk group.
Managed file creation and deletion—ASM further reduces administration tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns filenames when files are created, and automatically deletes files when they are no longer needed.
What is ASM instance in Oracle?
The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight.
Characteristics of Oracle ASM instance
--------------------------------------
1. do not have controlfile and datafiles, do not have online redo logs
2. do have init.ora and a passwordfile
3. for connecting remotely, create passwordfile and set following in init.ora
remote_login_passwordfile=exclusive
create a password file:
$ORACLE_HOME/bin/orapwd file=orapw+ASM1 password=yourpw entries=10
4. ASM instance can not be in open status as there are not datafiles. Can be in mount (although
there is no controlfile) and nomount status. When in mount status, database can use the
diskgroup. The mount status actually means mount disk groups.
What are ASM Background Processes in Oracle?Both an Oracle ASM instance and an Oracle Database instance are built on the same technology. Like a database instance, an Oracle ASM instance has memory structures (System Global Area) and background processes. Besides, Oracle ASM has a minimal performance impact on a server. Rather than mounting a database, Oracle ASM instances mount disk groups to make Oracle ASM files available to database instances.
There are at least two new background processes added for an ASM instance:
ASM Instance Background Processes:
---------------------------------
ARBx (ASM) Rebalance working processARBn performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these processes running at a time, named ARB0, ARB1, and so on.These processes are managed by the RBAL process. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.
RBAL (Re-balancer) RBAL runs in both database and ASM instances. In the database instance, it does a global open of ASM disks. In an ASM instance, it also coordinates rebalance activity for disk groups.RBAL, which coordinates rebalance activities
for disk resources controlled by ASM.
Database Instance ASM Background Processes:
------------------------------------------In the database instances, there are three background process to support ASM, namely:
ASMB, this process contact CSS using the group name and acquires the associated ASM connect string. The connect string is subsequently used to connect to the ASM instance.
RBAL, which performs global opens on all disks in the disk group.A global open means that more than one database instance can be accessing the ASM disks at a time.
O00x, a group slave processes, with a numeric sequence starting at 000.
What are the components of components of ASM are disk groups?
The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files. The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.
Failure groups are defined within a disk group to support the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations.
What are ASM instance initialization parameters?
INSTANCE_TYPE - Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
DB_UNIQUE_NAME - Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
ASM_DISKGROUPS - The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
ASM_DISKSTRING - Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.
Advantages of ASM in Oracle?
Provides automatic load balancing over all the available disks, thus reducing hot spots in the file system
Prevents fragmentation of disks, so you don't need to manually relocate data to tune I/O performance
Adding disks is straight forward - ASM automatically performs online disk reorganization when you add or remove storage
Uses redundancy features available in intelligent storage arrays
The storage system can store all types of database files
Using disk group makes configuration easier, as files are placed into disk groups
ASM provides stripping and mirroring (fine and coarse gain - see below)
ASM and non-ASM oracle files can coexist
Striping—ASM spreads data evenly across all disks in a disk group to optimize performance and utilization. This even distribution of database files eliminates the need for regular monitoring and I/O performance tuning.
For example, if there are six disks in a disk group, pieces of each ASM file are written to all six disks. These pieces come in 1 MB chunks known as extents. When a database file is created, it is striped (divided into extents and distributed) across the six disks, and allocated disk space on all six disks grows evenly. When reading the file, file extents are read from all six disks in parallel, greatly increasing performance.
Mirroring—ASM can increase availability by optionally mirroring any file. ASM mirrors at the file level, unlike operating system mirroring, which mirrors at the disk level. Mirroring means keeping redundant copies, or mirrored copies, of each extent of the file, to help avoid data loss caused by disk failures. The mirrored copy of each file extent is always kept on a different disk from the original copy. If a disk fails, ASM can continue to access affected files by accessing mirrored copies on the surviving disks in the disk group.
ASM supports 2-way mirroring, where each file extent gets one mirrored copy, and 3-way mirroring, where each file extent gets two mirrored copies.
Online storage reconfiguration and dynamic rebalancing—ASM permits you to add or remove disks from your disk storage system while the database is operating. When you add a disk, ASM automatically redistributes the data so that it is evenly spread across all disks in the disk group, including the new disk. This redistribution is known as rebalancing. It is done in the background and with minimal impact to database performance. When you request to remove a disk, ASM first rebalances by evenly relocating all file extents from the disk being removed to the other disks in the disk group.
Managed file creation and deletion—ASM further reduces administration tasks by enabling files stored in ASM disk groups to be Oracle-managed files. ASM automatically assigns filenames when files are created, and automatically deletes files when they are no longer needed.
Why should we use separate ASM home?
ASM should be installed separately from the database software in its own ORACLE_HOME directory. This will allow you the flexibility to patch and upgrade ASM and the database software independently.
How many ASM instances should one have?
Several databases can share a single ASM instance. So, although one can create multiple ASM instances on a single system, normal configurations should have one and only one ASM instance per system.
For clustered systems, create one ASM instance per node (called +ASM1, +ASM2, etc).
How many diskgroups should one have?
Generally speaking one should have only one disk group for all database files - and, optionally a second for recovery files (see FRA).
Data with different storage characteristics should be stored in different disk groups. Each disk group can have different redundancy (mirroring) settings (high, normal and external), different fail-groups, etc. However, it is generally not necessary to create many disk groups with the same storage characteristics (i.e. +DATA1, +DATA2, etc. all on the same type of disks).
To get started, create 2 disk groups - one for data and one for recovery files. Here is an example:
CREATE DISKGROUP data EXTERNAL REDUNDANCY DISK '/dev/d1', '/dev/d2', '/dev/d3', ....;
CREATE DISKGROUP recover EXTERNAL REDUNDANCY DISK '/dev/d10', '/dev/d11', '/dev/d12', ....;
Here is an example how you can enable automatic file management with such a setup:
ALTER SYSTEM SET db_create_file_dest = '+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET db_recovery_file_dest = '+RECOVER' SCOPE=SPFILE;
You may also decide to introduce additional disk groups - for example, if you decide to put historic data on low cost disks, or if you want ASM to mirror critical data across 2 storage cabinets.
What is ASM Rebalancing?
The rebalancing speed is controlled by the ASM_POWER_LIMIT initialization parameter. Setting it to 0 will disable disk rebalancing.
ALTER DISKGROUP data REBALANCE POWER 11;
What happens when an Oracle ASM diskgroup is created?
When an ASM diskgroup is created, a hierarchialfilesystem structure is created.
How does this filesystem structure appear?
Oracle ASM diskgroup'sfilesystem structure is similar to UNIX filesystem hierarchy or Windows filesystem hierarchy.
Where are the Oracle ASM files stored?
Oracle ASM files are stored within the Oracle ASM diskgroup. If we dig into internals, oracle ASM files are stored within the Oracle ASM filesystem structures.
How are the Oracle ASM files stored within the Oracle ASM filesystem structure?
Oralce ASM files are stored within the Oracle ASM filesystem structures as objects that RDBMS instances/Oracle database instance access. RDBMS/Oracle instance treats the Oracle ASM files as standard filesystem files.
What are the Oracle ASM files that are stored within the Oracle ASM file hierarchy?
Files stored in Oracle ASM diskgroup/Oracl ASM filestructures include:
1) Datafile
2) Controlfiles
3) Server Parameter Files(SPFILE)
4) Redo Log files
What happens when you create a file/database file in ASM?What commands do you use to create database files?
Some common commands used for creating database files are :
1) Create tabespace
2) Add Datafile
3) Add Logfile
For example,
SQL> CREATE TABLESPACE TS1 DATAFILE '+DATA1' SIZE 10GB;
Above command creates a datafile in DATA1 diskgroup
How can you access a databasefile in ASM diskgroup under RDBMS?
Once the ASM file is created in ASM diskgroup, a filename is generated. This file is now visible to the user via the standard RDBMS view V$DATAFILE.
What will be the syntax of ASM filenames?
ASM filename syntax is as follows:
+diskgroup_name/database_name/database_file_type/tag_name.file_number.incarnation
where,
+diskgroup_name - Name of the diskgroup that contains this file
database_name - Name of the database that contains this file
datafile - Can be one among 20 different ASM file types
tag_name - corresponds to tablespace name for datafiles, groupnumber for redo log files
file_number - file_number in ASM instance is used to correlate filenames in database instance
incarnation_number - It is derived from the timestamp. IT is used to provide uniqueness
What is an incarnation number?
An incarnation number is a part of ASM filename syntax. It is derived from the timestamp. Once the file is created, its incarnation number doesnot change.
What is the use of an incarnation number in Oracle ASM filename?
Incarnation number distinguishes between a new file that has been created using the same file number and another file that has been deleted
ASM's SPFile will be residing inside ASM itself. This could be found out in number of ways, looking at the alert log of ASM when ASM starts
Machine: x86_64
Using parameter settings in server-side spfile +DATA/asm/asmparameterfile/registry.253.766260991
System parameters with non-default values:
large_pool_size = 12M
instance_type = "asm"
remote_login_passwordfile= "EXCLUSIVE"
asm_diskgroups = "FLASH"
asm_diskgroups = "DATA"
asm_power_limit = 1
diagnostic_dest = "/opt/app/oracle"
Or using the asmcmd's spget command which shows the spfile location registered with GnP profile
ASMCMD> spget
+DATA/asm/asmparameterfile/registry.253.766260991
Oracle Patching,Cloning and Upgrade Interview
Questions and Answers
When you moved oracle binary files from one ORACLE_HOME server to another server then which oracle utility will be used to make this new ORACLE_HOME usable?
Relink all.
In which months oracle release CPU patches?
JAN, APR, JUL, OCT
When we applying single Patch, can you use opatch utility?
Yes, you can use Opatch incase of single patch. The only type of patch that cannot be used with OPatch is a patchset.
Is it possible to apply OPATCH without downtime?
As you know for apply patch your database and listener must be down. When you apply OPTACH it will update your current ORACLE_HOME. Thus coming to your question to the point in fact it is not possible without or zero downtime in case of single instance but in RAC you can Apply Opatch without downtime as there will be more separate ORACLE_HOME and more separate instances (running once instance on each ORACLE_HOME).
You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from it?
With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatch util NApply –help. It will give you clear picture.
For Example:
opatch util napply <patch_location> -id 9 -skip_subset -skip_duplicate
This will apply only the patch id 9 from the patch location and will skip duplicate and subset of patch installed in your ORACLE_HOME.
If both CPU and PSU are available for given version which one, you will prefer to apply?
From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1
PSU is superset of CPU then why someone choose to apply a CPU rather than a PSU?
CPUs are smaller and more focused than PSU and mostly deal with security issues. It seems to be theoretically more consecutive approach and can cause less trouble than PSU as it has less code changing in it. Thus anyone who is concerned only with security fixes and not functionality fixes, CPU may be good approach.
How to Download Patches, Patchset or Opatch from metalink?
If you are using latest support.oracle.com then after login to metalink Dashboard
- Click on "Patches & Updates" tab
- On the left sidebar click on "Latest Patchsets" under "Oracle Server/Tools".
- A new window will appear.
- Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page.
- Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that.
- You will go the download page. From the download page you can also change your platform and patchset version.
REFERENCES:
http://docs.oracle.com/cd/E11857_01/em.111/e12255/e_oui_appendix.htm
Oracle® Universal Installer and OPatch User's Guide
11g Release 2 (11.2) for Windows and UNIX
Part Number E12255-11
What is the recent Patch applied?
What is OPatch?
How to Apply Opatch in Oracle?
1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.
2.Make sure you have a good backup of database.
3. Make a note of all Invalid objects in the database prior to the patch.
4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
5. You MUST Backup your oracle Home and Inventory
tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz
6. Unzip the patch in $ORACLE_HOME/patches
7. cd to the patch direcory and do opatch -apply to apply the patch.
8. Read the output/log file to make sure there were no errors.
Patching Oracle Software with OPatch ?
opatch napply <patch_location> -skip_subset -skip_duplicate
OPatch skips duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the Oracle home).
What is Opactch in Oracle?
OPATCH Utility (Oracle RDBMS Patching)
1. Download the required Patch from Metalink based on OS Bit Version and DB Version.
2. Need to down the database before applying patch.
3. Unzip and Apply the Patch using ”opatch apply” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatch lsinventory” command .
4. Each patch has a unique ID, the command to rollback a patch is “opatch rollback -id <patch no.>” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatch lsinventory” command .
5. Patch file format will be like, “p<patch no.>_<db version>_<os>.zip”
6. We can check the opatch version using “opatch -version” command.
7. Generally, takes 2 minutes to apply a patch.
8. To get latest Opatch version download “patch 6880880 - latest opatch tool”, it contains OPatch directory.
9. Contents of downloaded patches will be like “etc,files directories and a README file”
10. Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch
11. OPatch also maintains an index of the commands executed with OPatch and the log files associated with it in the history.txt file located in the <ORACLE_HOME>/cfgtoollogs/opatch directory.
12. Starting with the 11.2.0.2 patch set, Oracle Database patch sets are full installations of the Oracle Database software. This means that you do not need to install Oracle Database 11g Release 2 (11.2.0.1) before installing Oracle Database 11g Release 2 (11.2.0.2).
13. Direct upgrade to Oracle 10g is only supported if your database is running one of the following releases: 8.0.6, 8.1.7, 9.0.1, or 9.2.0. If not, you will have to upgrade the database to one of these releases or use a different upgrade option (like export/ import).
14.Direct upgrades to 11g are possible from existing databases with versions 9.2.0.4+, 10.1.0.2+ or 10.2.0.1+. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.
When you moved oracle binary files from one ORACLE_HOME server to another server then which oracle utility will be used to make this new ORACLE_HOME usable?
Relink all.
In which months oracle release CPU patches?
JAN, APR, JUL, OCT
When we applying single Patch, can you use opatch utility?
Yes, you can use Opatch incase of single patch. The only type of patch that cannot be used with OPatch is a patchset.
Is it possible to apply OPATCH without downtime?
As you know for apply patch your database and listener must be down. When you apply OPTACH it will update your current ORACLE_HOME. Thus coming to your question to the point in fact it is not possible without or zero downtime in case of single instance but in RAC you can Apply Opatch without downtime as there will be more separate ORACLE_HOME and more separate instances (running once instance on each ORACLE_HOME).
You have collection of patch (nearly 100 patches) or patchset. How can you apply only one patch from it?
With Napply itself (by providing patch location and specific patch id) you can apply only one patch from a collection of extracted patch. For more information check the opatch util NApply –help. It will give you clear picture.
For Example:
opatch util napply <patch_location> -id 9 -skip_subset -skip_duplicate
This will apply only the patch id 9 from the patch location and will skip duplicate and subset of patch installed in your ORACLE_HOME.
If both CPU and PSU are available for given version which one, you will prefer to apply?
From the above discussion it is clear once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1
PSU is superset of CPU then why someone choose to apply a CPU rather than a PSU?
CPUs are smaller and more focused than PSU and mostly deal with security issues. It seems to be theoretically more consecutive approach and can cause less trouble than PSU as it has less code changing in it. Thus anyone who is concerned only with security fixes and not functionality fixes, CPU may be good approach.
How to Download Patches, Patchset or Opatch from metalink?
If you are using latest support.oracle.com then after login to metalink Dashboard
- Click on "Patches & Updates" tab
- On the left sidebar click on "Latest Patchsets" under "Oracle Server/Tools".
- A new window will appear.
- Just mouseover on your product in the "Latest Oracle Server/Tools Patchsets" page.
- Corresponding oracle platform version will appear. Then simply choose the patchset version and click on that.
- You will go the download page. From the download page you can also change your platform and patchset version.
REFERENCES:
http://docs.oracle.com/cd/E11857_01/em.111/e12255/e_oui_appendix.htm
Oracle® Universal Installer and OPatch User's Guide
11g Release 2 (11.2) for Windows and UNIX
Part Number E12255-11
What is the recent Patch applied?
What is OPatch?
How to Apply Opatch in Oracle?
1. You MUST read the Readme.txt file included in opatch file, look for any prereq. steps/ post installation steps or and DB related changes. Also, make sure that you have the correct opatch version required by this patch.
2.Make sure you have a good backup of database.
3. Make a note of all Invalid objects in the database prior to the patch.
4. Shutdown All the Oracle Processes running from that Oracle Home , including the Listener and Database instance, Management agent etc.
5. You MUST Backup your oracle Home and Inventory
tar -cvf $ORACLE_HOME $ORACLE_HOME/oraInventory | gzip > Backup_Software_Version.tar.gz
6. Unzip the patch in $ORACLE_HOME/patches
7. cd to the patch direcory and do opatch -apply to apply the patch.
8. Read the output/log file to make sure there were no errors.
Patching Oracle Software with OPatch ?
opatch napply <patch_location> -skip_subset -skip_duplicate
OPatch skips duplicate patches and subset patches (patches under <patch_location> that are subsets of patches installed in the Oracle home).
What is Opactch in Oracle?
OPATCH Utility (Oracle RDBMS Patching)
1. Download the required Patch from Metalink based on OS Bit Version and DB Version.
2. Need to down the database before applying patch.
3. Unzip and Apply the Patch using ”opatch apply” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatch lsinventory” command .
4. Each patch has a unique ID, the command to rollback a patch is “opatch rollback -id <patch no.>” command.On successfully applied of patch you will see successful message “OPatch succeeded.“, Crosscheck your patch is applied by using “opatch lsinventory” command .
5. Patch file format will be like, “p<patch no.>_<db version>_<os>.zip”
6. We can check the opatch version using “opatch -version” command.
7. Generally, takes 2 minutes to apply a patch.
8. To get latest Opatch version download “patch 6880880 - latest opatch tool”, it contains OPatch directory.
9. Contents of downloaded patches will be like “etc,files directories and a README file”
10. Log file for Opatch utility can be found at $ORACLE_HOME/cfgtoollogs/opatch
11. OPatch also maintains an index of the commands executed with OPatch and the log files associated with it in the history.txt file located in the <ORACLE_HOME>/cfgtoollogs/opatch directory.
12. Starting with the 11.2.0.2 patch set, Oracle Database patch sets are full installations of the Oracle Database software. This means that you do not need to install Oracle Database 11g Release 2 (11.2.0.1) before installing Oracle Database 11g Release 2 (11.2.0.2).
13. Direct upgrade to Oracle 10g is only supported if your database is running one of the following releases: 8.0.6, 8.1.7, 9.0.1, or 9.2.0. If not, you will have to upgrade the database to one of these releases or use a different upgrade option (like export/ import).
14.Direct upgrades to 11g are possible from existing databases with versions 9.2.0.4+, 10.1.0.2+ or 10.2.0.1+. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.
http://avdeo.com/2008/08/19/opatch-utility-oracle-rdbms-patching/
Oracle version 10.2.0.4.0 what does each number refers to?
Oracle version number refers:
10 – Major database release number
2 – Database Maintenance release number
0 – Application server release number
4 – Component Specific release number
0 – Platform specific release number
Types of Patches?
How to rollback a patch?
What is PSU?
What is Rolling Patch?
How to check installed Patches?
How much time will it take for Patching?
Common issues faced in Patching?
REFERENCES:
OPATCH Utility (Oracle RDBMS Patching)
http://avdeo.com/2008/08/19/opatch-utility-oracle-rdbms-patching/
How to apply Database Patches
http://rafioracledba.blogspot.in/search/label/Database%20Patches
Critical Patch Updates, Security Alerts and Third Party Bulletin
http://www.oracle.com/technetwork/topics/security/alerts-086861.html
Oracle: Quick Guide to Opatch - (Oracle Database Patching utility)
http://www.dbalifeline.com/content/oracle-quick-guide-opatch-oracle-database-patching-utility
How to Design an Effective Patch Management Process
http://www.computing.net/howtos/show/how-to-design-an-effective-patch-management-process/744.html
Oracle Database 11.2.0.2 Patch Set (English)
http://www.dbacomp.com.br/blog/?p=69
Apply Oracle CPUApr2010 – 9352191 for Oracle10.2.0.4 in Aix5L
http://hendrydasan.com/2010/05/21/apply-oracle-cpuapr2010-9352191-for-oracle10-2-0-4-in-aix5l/
Cloning
=======
What is Cloning?
How to do take RMAN Cloning? Explain Steps?
Upgrade
=======
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.Rolling upgrade can be used only for Oracle database 11g releases(from 11.1).
Steps to Upgrade in Oracle ?
Manual upgrade which involves the following steps:
1.Backup the database.
2.In UNIX/Linux environments, set the $ORACLE_HOME and $PATH variables to point to the new 11g Oracle home.
3.Analyze the existing instance using the "$ORACLE_HOME/rdbms/admin/utlu111i.sql" script.
4.Start the original database using the STARTUP UPGRADE command and proceed with the upgrade by running the "$ORACLE_HOME/rdbms/admin/catupgrd.sql" script.
5.Recompile invalid objects.
6.Restart the database.
7.Run the "$ORACLE_HOME/rdbms/admin/utlu111s.sql" script and check the result of the upgrade.
8.Troubleshoot any issues or abort the upgrade.
What happens when you give "STARTUP UPGRADE"?
$sqlplus "/as sysdba"
SQL> STARTUP UPGRADE
Note:
----
The UPGRADE keyword enables you to open a database based on an earlier Oracle Database release. It also restricts logons to AS SYSDBAsessions, disables system triggers, and performs additional operations that prepare the environment for the upgrade.
You might be required to use the PFILE option to specify the location of your initialization parameter file.
Once the database is started in upgrade mode, only queries on fixed views execute without errors until after the catupgrd.sql script is run. Before running catupgrd.sql, queries on any other view or the use of PL/SQL returns an error.
What is the difference between startup Upgrade and Migrate ?
startup migrate:
---------------
Used to upgrade a database till 9i.
Startup Upgrade
---------------
From 10G we are using startup upgrade to upgrade database.
What happens internally when you use startup upgrade/migrate?
It will adjust few database (init) parameters (irrespective of what you have defined) automatically to certain values in order to run upgrade scripts smoothely.
in other way..it will issue few alter statements to set certain parameters which are required to complete the upgrade scripts without any issues.
REFERENCE:
---------
Oracle® Database Upgrade Guide 11g Release 2 (11.2)
http://docs.oracle.com/cd/E11882_01/server.112/e23633/upgrade.htm
Common issues faced in Upgrade?
Error is related to timezone file
Started database in upgrade mode and fired catupgrd.sql :
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 6413680640 bytes
Fixed Size 2160112 bytes
Variable Size 1946159632 bytes
Database Buffers 4429185024 bytes
Redo Buffers 36175872 bytes
Database mounted.
Database opened.
SQL> @catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
DOC>
DOC> ORA-00001: unique constraint () violated
DOC>#
FROM registry$database
*
ERROR at line 2:
ORA-00942: table or view does not exist
This error is related to timezone file which must be version 4 for Oracle version 11g.If timezone is not version 4 than patch needs to be applied.
Query to check timezone file is:
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 4
SQL> select * from v$timezone_file;
FILENAME VERSION
———— ———-
timezlrg.dat 4
So I had correct version.I remember applying patch before upgrade.I got lucky because patch existed for version 10.2.0.3.
If there is no patch for your Oracle versions than patch can be download for similar version and applied manually.
Instructions are below:
1. Download the identified patch.
2. Unzip the patch, and locate the 2 files timezone.dat and timezlrg.dat in the “files/oracore/zoneinfo” directory of the uncompressed patch (or from the relevant .jar file of a patchset). If there is also a readme.txt in this location then make a note of this as well.
3. Backup your existing files in $ORACLE_HOME/oracore/zoneinfo – THIS CAN BE VITAL, DO NOT SKIP.
Note:
Before going on with step 4, make sure the current files are not in use.
On Windows the files will simply refuse to be removed when the are in use.
On Unix replacing the files whilst they are in use can cause the files to become corrupt. Use the fuser command before replacing the files to make sure they are not in use.
4. Copy the 2 .dat files and possibly the readme.txt file that were found in step 2 into the $ORACLE_HOME/oracore/zoneinfo directory.
5. Restart the database (in case of installation on a database), or restart the client applications (in case of client install). Note that the database did not need to be down before the time zone files were applied, but it does need to be restarted afterwards.
http://indiandba.blogspot.in/2012/01/error-is-related-to-timezone-file.html
Oracle DBA Interview Questions and Answers - RAC
Oracle RAC Interview Questions and Answers
How does OCSSD starts first 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.
What is gsdctl in RAC? list gsdctl commands in Oracle RAC?
GSDCTL stands for Global Service Daemon Control, we can use gsdctl commands to start, stop, and obtain the status of the GSD service on any platform.
The options for gsdctl are:-
$ gsdctl start -- To start the GSD service
$ gsdctl stop -- To stop the GSD service
$ gsdctl stat -- To obtain the status of the GSD service
Log file location for gsdctl:
$ ORACLE_HOME/srvm/log/gsdaemon_node_name.log
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.
Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide a highly scalable and available database solution for all the business applications.
Oracle RAC provides the foundation for enterprise grid computing.
What is Oracle RAC One Node?
Oracle RAC one Node is a single instance running on one node of the cluster while the 2nd node is in cold standby mode. If the instance fails for some reason then RAC one node detect it and restart the instance on the same node or the instance is relocate to the 2nd node incase there is failure or fault in 1st node. The benefit of this feature is that it provides a cold failover solution and it automates the instance relocation without any downtime and does not need a manual intervention. Oracle introduced this feature with the release of 11gR2 (available with Enterprise Edition).
What is RAC and how is it different from non RAC databases?
Oracle Real Application clusters allows multiple instances to access a single database, the instances will be running on multiple nodes.
In Real Application Clusters environments, all nodes concurrently execute transactions against the same database.
Real Application Clusters coordinates each node's access to the shared data to provide consistency and integrity.
What are the advantages of RAC (Real Application Clusters)?
Reliability - if one node fails, the database won't fail
Availability - nodes can be added or replaced without having to shutdown the database
Scalability - more nodes can be added to the cluster as the workload increases
What is Oracle RAC One Node?
Oracle RAC one Node is a single instance running on one node of the cluster while the 2nd node is in cold standby mode. If the instance fails for some reason then RAC one node detect it and restart the instance on the same node or the instance is relocate to the 2nd node incase there is failure or fault in 1st node. The benefit of this feature is that it provides a cold failover solution and it automates the instance relocation without any downtime and does not need a manual intervention. Oracle introduced this feature with the release of 11gR2 (available with Enterprise Edition).
What is Cache Fusion?
Oracle RAC is composed of two or more instances. 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. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service (GES) monitors and Instance enqueue process manages the cache fusion.
What command would you use to check the availability of the RAC system?
crs_stat -t -v (-t -v are optional)
How do we verify that RAC instances are running?
SQL>select * from V$ACTIVE_INSTANCES;
The query gives the instance number under INST_NUMBER column,host_:instancename under INST_NAME column.
How can you connect to a specific node in a RAC environment?
tnsnames.ora ensure that you have INSTANCE_NAME specified in it.
Which is the "MASTER NODE" in RAC?
The node with the lowest node number will become master node and dynamic remastering of the resources will take place.
To find out the master node for particular resource, you can query v$ges_resource for MASTER_NODE column.
To find out which is the master node, you can see ocssd.log file and search for "master node number".
when the first master node fails in the cluster the lowest node number will become master node.
What components in RAC must reside in shared storage?
All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.
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).
What are Oracle Cluster Components?
1.Cluster Interconnect (HAIP)
2.Shared Storage (OCR/Voting Disk)
3.Clusterware software
4.Oracle Kernel Components
What are Oracle RAC Components?
VIP, Node apps etc.
What are Oracle Kernel Components?
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.
How 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
Disk architechture in RAC?
SAN (Storage Area Networks) - generally using fibre to connect to the SAN
NAS (Network Attached Storage) - generally using a network to connect to the NAS using either NFS, ISCSI
What is Oracle Clusterware?
The Clusterware software allows nodes to communicate with each other and forms the cluster that makes the nodes work as a single logical server.
The software is run by the Cluster Ready Services (CRS) using the Oracle Cluster Registry (OCR) that records and maintains the cluster and node membership information and the voting disk which acts as a tiebreaker during communication failures. Consistent heartbeat information travels across the interconnect to the voting disk when the cluster is running.
Real Application Clusters
Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide a highly scalable and available database solution for all your business applications. Oracle RAC provides the foundation for enterprise grid computing.
Oracle’s Real Application Clusters (RAC) option supports the transparent deployment of a single database across a cluster of servers, providing fault tolerance from hardware failures or planned outages. Oracle RAC running on clusters provides Oracle’s highest level of capability in terms of availability, scalability, and low-cost computing.
One DB opened by multipe instances so the the db ll be Highly Available if an instance crashes.
Cluster Software. Oracles Clusterware or products like Veritas Volume Manager are required to provide the cluster support and allow each node to know which nodes belong to the cluster and are available and with Oracle Cluterware to know which nodes have failed and to eject then from the cluster, so that errors on that node can be cleared.
Oracle Clusterware has two key components Cluster Registry OCR and Voting Disk.
The cluster registry holds all information about nodes, instances, services and ASM storage if used, it also contains state information ie they are available and up or similar.
The voting disk is used to determine if a node has failed, i.e. become separated from the majority. If a node is deemed to no longer belong to the majority then it is forcibly rebooted and will after the reboot add itself again the the surviving cluster nodes.
What are the Oracle Clusterware key components?
Oracle Clusterware has two key components Cluster Registry OCR and Voting Disk.
What is Voting Disk and OCR?
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.
A node must be able to access more than half of the voting disks at any time.
For example, if you have 3 voting disks configured, then a node must be able to access at least two of the voting disks at any time. If a node cannot access the minimum required number of voting disks it is evicted, or removed, from the cluster.
Oracle Cluster Registry (OCR)
The cluster registry holds all information about nodes, instances, services and ASM storage if used, it also contains state information ie they are available and up or similar.
The OCR must reside on shared disk that is accessible by all of the nodes in your cluster.
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
Can you add voting disk online? Do you need voting disk backup?
Yes, as per documentation, if you have multiple voting disk you can add online, but if you have only one voting disk , by that cluster will be down as its lost you just need to start crs in exclusive mode and add the votedisk using
crsctl add votedisk <path>
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.
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:
copy voting_disk_name backup_file_name
How do we verify an existing current backup of OCR?
We can verify the current backup of OCR using the following command : ocrconfig -showbackup
You have lost OCR disk, what is your next step?
The cluster stack will be down due to the fact that cssd is unable to maintain the integrity, this is true in 10g, From 11gR2 onwards, the crsd stack will be down, the hasd still up and running. You can add the ocr back by restoring the automatic backup or import the manual backup,
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.
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.
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.
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.
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.
What are Oracle database background processes specific to RAC?
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
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.
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.
What is 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.
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.
SCAN IP can be disabled if not required. However SCAN IP is mandatory during the RAC installation. Enabling/disabling SCAN IP is mostly used in oracle apps environment by the concurrent manager (kind of job scheduler in oracle apps).
Steps to disable the SCAN IP,
i. Do not use SCAN IP at the client end.
ii. Stop scan listener
srvctl stop scan_listener
iii.Stop scan
srvctl stop scan (this will stop the scan vip's)
iv. Disable scan and disable scan listener
srvctl disable scan
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
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.
What is the use of cluster interconnect?
Cluster interconnect is used by the Cache fusion for inter instance communication.
How can we configure the cluster interconnect?
· Configure User Datagram Protocol (UDP) on Gigabit Ethernet for cluster interconnects.
· 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.
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.
What is a virtual IP address or VIP?
A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP 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.
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.
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.
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.
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.
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.
What are the characteristics controlled by Oracle services feature?
The characteristics include a unique name, workload balancing, failover options, and high availability.
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.
What are the types of connection load-balancing?
Connection Workload management is one of the key aspects when you have RAC instances as you want to distribute the connections to specific nodes/instance or those have less load.
There are two types of connection load-balancing:
1.Client Side load balancing (also called as connect time load balancing)
2.Server side load balancing (also called as Listener connection load balancing)
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.
Client Side load balancing:- Oracle client side load balancing feature enables clients to randomize the connection requests among all the available listeners based on their load.
An tns entry that contains all nodes entries and use load_balance=on (default its on) will use the connect time load balancing or client side load balancing.
Sample Client Side TNS Entry:-
finance =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myrac2-vip)(PORT = 2042))
(ADDRESS = (PROTOCOL = TCP)(HOST = myrac1-vip)(PORT = 2042))
(ADDRESS = (PROTOCOL = TCP)(HOST = myrac3-vip)(PORT = 2042))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FINANCE) (FAILOVER=ON)
(FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))
)
)
Server side load balancing:- This improves the connection performance by balancing the number of active connections among multiple instances and dispatchers. In a single instance environment (shared servers), the listener selects the least dispatcher to handle the incoming client requests. In a rac environments, PMON is aware of all instances load and dispatchers , and depending on the load information PMON redirects the connection to the least loaded node.
In a RAC environment, *.remote_listener parameter which is a tns entry containing all nodes addresses need to set to enable the load balance advisory updates to PMON.
Sample Tns entry should be in an instances of RAC cluster,
local_listener=LISTENER_MYRAC1
remote_listener = LISTENERS_MYRACDB
What are the administrative tools used for Oracle RAC environments?
Oracle RAC cluster can be administered as a single image using the below
· OEM (Enterprise Manager),
· SQL*PLUS,
· Server control (SRVCTL),
· Cluster Verification Utility (CLUVFY),
· DBCA,
· NETCA
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.
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 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
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
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).
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 the major difference between 10g and 11g RAC?
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
Databases
Instances
Applications
Node Monitoring
Event Services
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 is hangcheck timer?
The hangcheck timer checks regularly the health of the system. If the system hangs or stop the node will be restarted automatically.
There are 2 key parameters for this module:
-> hangcheck-tick: this parameter defines the period of time between checks of system health. The default value is 60 seconds; Oracle recommends setting it to 30seconds.
-> hangcheck-margin: this defines the maximum hang delay that should be tolerated before hangcheck-timer resets the RAC node.
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
---------------------------------------------------------------------------------------------------------------
What is RAC? What is the benefit of RAC over single instance database?
In Real Application Clusters environments, all nodes concurrently execute transactions against the same database. Real Application Clusters coordinates each node's access to the shared data to provide consistency and integrity.
Benefits:
Improve response time
Improve throughput
High availability
Transparency
Advantages of RAC (Real Application Clusters)
Reliability - if one node fails, the database won't fail
Availability - nodes can be added or replaced without having to shutdown the database
Scalability - more nodes can be added to the cluster as the workload increases
What is a virtual IP address or VIP?
A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP 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.
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.
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.
Using virtual IP we can save our TCP/IP timeout problem because Oracle notification service maintains communication between each nodes and listeners.
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.
What is voting disk?
Voting Disk is a file that sits in the shared storage area and must be accessible by all nodes in the cluster. All nodes in the cluster registers their heart-beat information in the voting disk, so as to confirm that they are all operational. If heart-beat information of any node in the voting disk is not available that node will be evicted from the cluster. The CSS (Cluster Synchronization Service) daemon in the clusterware maintains the heart beat of all nodes to the voting disk. When any node is not able to send heartbeat to voting disk, then it will reboot itself, thus help avoiding the split-brain syndrome.
For high availability, Oracle recommends that you have a minimum of three or odd number (3 or greater) of votingdisks.
Voting Disk - is file that resides on shared storage and Manages cluster members. Voting disk reassigns cluster ownership between the nodes in case of failure.
The Voting Disk Files are used by Oracle Clusterware to determine which nodes are currently members of the cluster. The voting disk files are also used in concert with other Cluster components such as CRS to maintain the clusters integrity.
Oracle Database 11g Release 2 provides the ability to store the voting disks in ASM along with the OCR. Oracle Clusterware can access the OCR and the voting disks present in ASM even if the ASM instance is down. As a result CSS can continue to maintain the Oracle cluster even if the ASM instance has failed.
How many voting disks are you maintaining ?
http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/RACR2ARC6/Default.aspx
By default Oracle will create 3 voting disk files in ASM.
Oracle expects that you will configure at least 3 voting disks for redundancy purposes. You should always configure an odd number of voting disks >= 3. This is because loss of more than half your voting disks will cause the entire cluster to fail.
You should plan on allocating 280MB for each voting disk file. For example, if you are using ASM and external redundancy then you will need to allocate 280MB of disk for the voting disk. If you are using ASM and normal redundancy you will need 560MB.
Why we need to keep odd number of voting disks ?
Oracle expects that you will configure at least 3 voting disks for redundancy purposes. You should always configure an odd number of voting disks >= 3. This is because loss of more than half your voting disks will cause the entire cluster to fail.
What are 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
What are Oracle Clusterware processes for 10g ?
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.
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
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.
What is Cache Fusion?
Transfor of data across instances through private interconnect is called cachefusion.Oracle RAC is composed of two or more instances. 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 insatnce which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance enqueue process manages the cahce fusion
What is SCAN? (11gR2 feature)
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.
SCAN provides a single domain name via (DNS), allowing and-users to address a RAC cluster as-if it were a single IP address. SCAN works by replacing a hostname or IP list with virtual IP addresses (VIP).
Single client access name (SCAN) is meant to facilitate single name for all Oracle clients to connect to the cluster database, irrespective of number of nodes and node location. Until now, we have to keep adding multiple address records in all clients tnsnames.ora, when a new node gets added to or deleted from the cluster.
Single Client Access Name (SCAN) eliminates the need to change TNSNAMES entry when nodes are added to or removed from the Cluster. RAC instances register to SCAN listeners as remote listeners. Oracle recommends assigning 3 addresses to SCAN, which will create 3 SCAN listeners, though the cluster has got dozens of nodes.. SCAN is a domain name registered to at least one and up to three IP addresses, either in DNS (Domain Name Service) or GNS (Grid Naming Service). The SCAN must resolve to at least one address on the public network. For high availability and scalability, Oracle recommends configuring the SCAN to resolve to three addresses.
http://www.freeoraclehelp.com/2011/12/scan-setup-for-oracle-11g-release211gr2.html
What are SCAN components in a cluster?
1.SCAN Name
2.SCAN IPs (3)
3.SCAN Listeners (3)
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.
What is TAF?
TAF (Transparent Application Failover) is a configuration that allows session fail-over between different nodes of a RAC database cluster.
Transparent Application Failover (TAF). If a communication link failure occurs after a connection is established, the connection fails over to another active node. Any disrupted transactions are rolled back, and session properties and server-side program variables are lost. In some cases, if the statement executing at the time of the failover is a Select statement, that statement may be automatically re-executed on the new connection with the cursor positioned on the row on which it was positioned prior to the 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.
TAF architecture offers the ability to restart transactions at either the transaction (SELECT) or session level.
How does OCSSD starts first 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.
What is gsdctl in RAC? list gsdctl commands in Oracle RAC?
GSDCTL stands for Global Service Daemon Control, we can use gsdctl commands to start, stop, and obtain the status of the GSD service on any platform.
The options for gsdctl are:-
$ gsdctl start -- To start the GSD service
$ gsdctl stop -- To stop the GSD service
$ gsdctl stat -- To obtain the status of the GSD service
Log file location for gsdctl:
$ ORACLE_HOME/srvm/log/gsdaemon_node_name.log
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.
Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide a highly scalable and available database solution for all the business applications.
Oracle RAC provides the foundation for enterprise grid computing.
What is Oracle RAC One Node?
Oracle RAC one Node is a single instance running on one node of the cluster while the 2nd node is in cold standby mode. If the instance fails for some reason then RAC one node detect it and restart the instance on the same node or the instance is relocate to the 2nd node incase there is failure or fault in 1st node. The benefit of this feature is that it provides a cold failover solution and it automates the instance relocation without any downtime and does not need a manual intervention. Oracle introduced this feature with the release of 11gR2 (available with Enterprise Edition).
What is RAC and how is it different from non RAC databases?
Oracle Real Application clusters allows multiple instances to access a single database, the instances will be running on multiple nodes.
In Real Application Clusters environments, all nodes concurrently execute transactions against the same database.
Real Application Clusters coordinates each node's access to the shared data to provide consistency and integrity.
What are the advantages of RAC (Real Application Clusters)?
Reliability - if one node fails, the database won't fail
Availability - nodes can be added or replaced without having to shutdown the database
Scalability - more nodes can be added to the cluster as the workload increases
What is Oracle RAC One Node?
Oracle RAC one Node is a single instance running on one node of the cluster while the 2nd node is in cold standby mode. If the instance fails for some reason then RAC one node detect it and restart the instance on the same node or the instance is relocate to the 2nd node incase there is failure or fault in 1st node. The benefit of this feature is that it provides a cold failover solution and it automates the instance relocation without any downtime and does not need a manual intervention. Oracle introduced this feature with the release of 11gR2 (available with Enterprise Edition).
What is Cache Fusion?
Oracle RAC is composed of two or more instances. 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. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service (GES) monitors and Instance enqueue process manages the cache fusion.
What command would you use to check the availability of the RAC system?
crs_stat -t -v (-t -v are optional)
How do we verify that RAC instances are running?
SQL>select * from V$ACTIVE_INSTANCES;
The query gives the instance number under INST_NUMBER column,host_:instancename under INST_NAME column.
How can you connect to a specific node in a RAC environment?
tnsnames.ora ensure that you have INSTANCE_NAME specified in it.
Which is the "MASTER NODE" in RAC?
The node with the lowest node number will become master node and dynamic remastering of the resources will take place.
To find out the master node for particular resource, you can query v$ges_resource for MASTER_NODE column.
To find out which is the master node, you can see ocssd.log file and search for "master node number".
when the first master node fails in the cluster the lowest node number will become master node.
What components in RAC must reside in shared storage?
All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.
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).
What are Oracle Cluster Components?
1.Cluster Interconnect (HAIP)
2.Shared Storage (OCR/Voting Disk)
3.Clusterware software
4.Oracle Kernel Components
What are Oracle RAC Components?
VIP, Node apps etc.
What are Oracle Kernel Components?
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.
How 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
Disk architechture in RAC?
SAN (Storage Area Networks) - generally using fibre to connect to the SAN
NAS (Network Attached Storage) - generally using a network to connect to the NAS using either NFS, ISCSI
What is Oracle Clusterware?
The Clusterware software allows nodes to communicate with each other and forms the cluster that makes the nodes work as a single logical server.
The software is run by the Cluster Ready Services (CRS) using the Oracle Cluster Registry (OCR) that records and maintains the cluster and node membership information and the voting disk which acts as a tiebreaker during communication failures. Consistent heartbeat information travels across the interconnect to the voting disk when the cluster is running.
Real Application Clusters
Oracle RAC is a cluster database with a shared cache architecture that overcomes the limitations of traditional shared-nothing and shared-disk approaches to provide a highly scalable and available database solution for all your business applications. Oracle RAC provides the foundation for enterprise grid computing.
Oracle’s Real Application Clusters (RAC) option supports the transparent deployment of a single database across a cluster of servers, providing fault tolerance from hardware failures or planned outages. Oracle RAC running on clusters provides Oracle’s highest level of capability in terms of availability, scalability, and low-cost computing.
One DB opened by multipe instances so the the db ll be Highly Available if an instance crashes.
Cluster Software. Oracles Clusterware or products like Veritas Volume Manager are required to provide the cluster support and allow each node to know which nodes belong to the cluster and are available and with Oracle Cluterware to know which nodes have failed and to eject then from the cluster, so that errors on that node can be cleared.
Oracle Clusterware has two key components Cluster Registry OCR and Voting Disk.
The cluster registry holds all information about nodes, instances, services and ASM storage if used, it also contains state information ie they are available and up or similar.
The voting disk is used to determine if a node has failed, i.e. become separated from the majority. If a node is deemed to no longer belong to the majority then it is forcibly rebooted and will after the reboot add itself again the the surviving cluster nodes.
What are the Oracle Clusterware key components?
Oracle Clusterware has two key components Cluster Registry OCR and Voting Disk.
What is Voting Disk and OCR?
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.
A node must be able to access more than half of the voting disks at any time.
For example, if you have 3 voting disks configured, then a node must be able to access at least two of the voting disks at any time. If a node cannot access the minimum required number of voting disks it is evicted, or removed, from the cluster.
Oracle Cluster Registry (OCR)
The cluster registry holds all information about nodes, instances, services and ASM storage if used, it also contains state information ie they are available and up or similar.
The OCR must reside on shared disk that is accessible by all of the nodes in your cluster.
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
Can you add voting disk online? Do you need voting disk backup?
Yes, as per documentation, if you have multiple voting disk you can add online, but if you have only one voting disk , by that cluster will be down as its lost you just need to start crs in exclusive mode and add the votedisk using
crsctl add votedisk <path>
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.
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:
copy voting_disk_name backup_file_name
How do we verify an existing current backup of OCR?
We can verify the current backup of OCR using the following command : ocrconfig -showbackup
You have lost OCR disk, what is your next step?
The cluster stack will be down due to the fact that cssd is unable to maintain the integrity, this is true in 10g, From 11gR2 onwards, the crsd stack will be down, the hasd still up and running. You can add the ocr back by restoring the automatic backup or import the manual backup,
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.
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.
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.
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.
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.
What are Oracle database background processes specific to RAC?
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
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.
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.
What is 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.
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.
SCAN IP can be disabled if not required. However SCAN IP is mandatory during the RAC installation. Enabling/disabling SCAN IP is mostly used in oracle apps environment by the concurrent manager (kind of job scheduler in oracle apps).
Steps to disable the SCAN IP,
i. Do not use SCAN IP at the client end.
ii. Stop scan listener
srvctl stop scan_listener
iii.Stop scan
srvctl stop scan (this will stop the scan vip's)
iv. Disable scan and disable scan listener
srvctl disable scan
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
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.
What is the use of cluster interconnect?
Cluster interconnect is used by the Cache fusion for inter instance communication.
How can we configure the cluster interconnect?
· Configure User Datagram Protocol (UDP) on Gigabit Ethernet for cluster interconnects.
· 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.
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.
What is a virtual IP address or VIP?
A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP 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.
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.
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.
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.
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.
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.
What are the characteristics controlled by Oracle services feature?
The characteristics include a unique name, workload balancing, failover options, and high availability.
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.
What are the types of connection load-balancing?
Connection Workload management is one of the key aspects when you have RAC instances as you want to distribute the connections to specific nodes/instance or those have less load.
There are two types of connection load-balancing:
1.Client Side load balancing (also called as connect time load balancing)
2.Server side load balancing (also called as Listener connection load balancing)
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.
Client Side load balancing:- Oracle client side load balancing feature enables clients to randomize the connection requests among all the available listeners based on their load.
An tns entry that contains all nodes entries and use load_balance=on (default its on) will use the connect time load balancing or client side load balancing.
Sample Client Side TNS Entry:-
finance =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myrac2-vip)(PORT = 2042))
(ADDRESS = (PROTOCOL = TCP)(HOST = myrac1-vip)(PORT = 2042))
(ADDRESS = (PROTOCOL = TCP)(HOST = myrac3-vip)(PORT = 2042))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FINANCE) (FAILOVER=ON)
(FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5))
)
)
Server side load balancing:- This improves the connection performance by balancing the number of active connections among multiple instances and dispatchers. In a single instance environment (shared servers), the listener selects the least dispatcher to handle the incoming client requests. In a rac environments, PMON is aware of all instances load and dispatchers , and depending on the load information PMON redirects the connection to the least loaded node.
In a RAC environment, *.remote_listener parameter which is a tns entry containing all nodes addresses need to set to enable the load balance advisory updates to PMON.
Sample Tns entry should be in an instances of RAC cluster,
local_listener=LISTENER_MYRAC1
remote_listener = LISTENERS_MYRACDB
What are the administrative tools used for Oracle RAC environments?
Oracle RAC cluster can be administered as a single image using the below
· OEM (Enterprise Manager),
· SQL*PLUS,
· Server control (SRVCTL),
· Cluster Verification Utility (CLUVFY),
· DBCA,
· NETCA
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.
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 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
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
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).
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 the major difference between 10g and 11g RAC?
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
Databases
Instances
Applications
Node Monitoring
Event Services
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 is hangcheck timer?
The hangcheck timer checks regularly the health of the system. If the system hangs or stop the node will be restarted automatically.
There are 2 key parameters for this module:
-> hangcheck-tick: this parameter defines the period of time between checks of system health. The default value is 60 seconds; Oracle recommends setting it to 30seconds.
-> hangcheck-margin: this defines the maximum hang delay that should be tolerated before hangcheck-timer resets the RAC node.
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
---------------------------------------------------------------------------------------------------------------
What is RAC? What is the benefit of RAC over single instance database?
In Real Application Clusters environments, all nodes concurrently execute transactions against the same database. Real Application Clusters coordinates each node's access to the shared data to provide consistency and integrity.
Benefits:
Improve response time
Improve throughput
High availability
Transparency
Advantages of RAC (Real Application Clusters)
Reliability - if one node fails, the database won't fail
Availability - nodes can be added or replaced without having to shutdown the database
Scalability - more nodes can be added to the cluster as the workload increases
What is a virtual IP address or VIP?
A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP 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.
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.
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.
Using virtual IP we can save our TCP/IP timeout problem because Oracle notification service maintains communication between each nodes and listeners.
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.
What is voting disk?
Voting Disk is a file that sits in the shared storage area and must be accessible by all nodes in the cluster. All nodes in the cluster registers their heart-beat information in the voting disk, so as to confirm that they are all operational. If heart-beat information of any node in the voting disk is not available that node will be evicted from the cluster. The CSS (Cluster Synchronization Service) daemon in the clusterware maintains the heart beat of all nodes to the voting disk. When any node is not able to send heartbeat to voting disk, then it will reboot itself, thus help avoiding the split-brain syndrome.
For high availability, Oracle recommends that you have a minimum of three or odd number (3 or greater) of votingdisks.
Voting Disk - is file that resides on shared storage and Manages cluster members. Voting disk reassigns cluster ownership between the nodes in case of failure.
The Voting Disk Files are used by Oracle Clusterware to determine which nodes are currently members of the cluster. The voting disk files are also used in concert with other Cluster components such as CRS to maintain the clusters integrity.
Oracle Database 11g Release 2 provides the ability to store the voting disks in ASM along with the OCR. Oracle Clusterware can access the OCR and the voting disks present in ASM even if the ASM instance is down. As a result CSS can continue to maintain the Oracle cluster even if the ASM instance has failed.
How many voting disks are you maintaining ?
http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/RACR2ARC6/Default.aspx
By default Oracle will create 3 voting disk files in ASM.
Oracle expects that you will configure at least 3 voting disks for redundancy purposes. You should always configure an odd number of voting disks >= 3. This is because loss of more than half your voting disks will cause the entire cluster to fail.
You should plan on allocating 280MB for each voting disk file. For example, if you are using ASM and external redundancy then you will need to allocate 280MB of disk for the voting disk. If you are using ASM and normal redundancy you will need 560MB.
Why we need to keep odd number of voting disks ?
Oracle expects that you will configure at least 3 voting disks for redundancy purposes. You should always configure an odd number of voting disks >= 3. This is because loss of more than half your voting disks will cause the entire cluster to fail.
What are 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
What are Oracle Clusterware processes for 10g ?
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.
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
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.
What is Cache Fusion?
Transfor of data across instances through private interconnect is called cachefusion.Oracle RAC is composed of two or more instances. 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 insatnce which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance enqueue process manages the cahce fusion
What is SCAN? (11gR2 feature)
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.
SCAN provides a single domain name via (DNS), allowing and-users to address a RAC cluster as-if it were a single IP address. SCAN works by replacing a hostname or IP list with virtual IP addresses (VIP).
Single client access name (SCAN) is meant to facilitate single name for all Oracle clients to connect to the cluster database, irrespective of number of nodes and node location. Until now, we have to keep adding multiple address records in all clients tnsnames.ora, when a new node gets added to or deleted from the cluster.
Single Client Access Name (SCAN) eliminates the need to change TNSNAMES entry when nodes are added to or removed from the Cluster. RAC instances register to SCAN listeners as remote listeners. Oracle recommends assigning 3 addresses to SCAN, which will create 3 SCAN listeners, though the cluster has got dozens of nodes.. SCAN is a domain name registered to at least one and up to three IP addresses, either in DNS (Domain Name Service) or GNS (Grid Naming Service). The SCAN must resolve to at least one address on the public network. For high availability and scalability, Oracle recommends configuring the SCAN to resolve to three addresses.
http://www.freeoraclehelp.com/2011/12/scan-setup-for-oracle-11g-release211gr2.html
What are SCAN components in a cluster?
1.SCAN Name
2.SCAN IPs (3)
3.SCAN Listeners (3)
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.
What is TAF?
TAF (Transparent Application Failover) is a configuration that allows session fail-over between different nodes of a RAC database cluster.
Transparent Application Failover (TAF). If a communication link failure occurs after a connection is established, the connection fails over to another active node. Any disrupted transactions are rolled back, and session properties and server-side program variables are lost. In some cases, if the statement executing at the time of the failover is a Select statement, that statement may be automatically re-executed on the new connection with the cursor positioned on the row on which it was positioned prior to the 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.
TAF architecture offers the ability to restart transactions at either the transaction (SELECT) or session level.
What are the requirements for
Oracle Clusterware?
1. External Shared Disk to store Oracle Cluster
ware file (Voting Disk and Oracle Cluster Registry - OCR)
2. Two netwrok cards on each cluster ware node (and
three set of IP address) -
Network Card 1 (with IP address set 1) for public
network
Network Card 2 (with IP address set 2) for private
network (for inter node communication between rac nodes used by clusterware and
rac database)
IP address set 3 for Virtual IP (VIP) (used as
Virtual IP address for client connection and for connection failover)
3. Storage Option for OCR and Voting Disk - RAW,
OCFS2 (Oracle Cluster File System), NFS, …..
Which enable 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.
How to find location of OCR file when CRS is down?
If you need to find the location of OCR (Oracle Cluster Registry) but your CRS is down.
When the CRS is down:
Look into “ocr.loc” file, location of this file changes depending on the OS:
On Linux: /etc/oracle/ocr.loc
On Solaris: /var/opt/oracle/ocr.loc
When CRS is UP:
Set ASM environment or CRS environment then run the below command:
ocrcheck
In 2 node RAC, how many NIC’s are r using ?
2 network cards on each clusterware node
Network Card 1 (with IP address set 1) for public
network
Network Card 2 (with IP address set 2) for private
network (for inter node communication between rac nodes used by clusterware and
rac database)
In 2 node RAC, how many IP’s are r using ?
6 - 3 set of IP address
## eth1-Public: 2
## eth0-Private: 2
## VIP: 2
How to find IP’s information in RAC ?
Edit the /etc/hosts file as shown below:
# Do not remove the following line, or various
programs
# that requires network functionality will fail.
127.0.0.1
localhost.localdomain localhost
## Public Node names
192.168.10.11
node1-pub.hingu.net node1-pub
192.168.10.22
node2-pub.hingu.net node2-pub
## Private Network (Interconnect)
192.168.0.11
node1-prv
node1-prv
192.168.0.22
node2-prv node2-prv
## Private Network (Network Area storage)
192.168.1.11
node1-nas
node1-nas
192.168.1.22
node2-nas node2-nas
192.168.1.33
nas-server nas-server
## Virtual IPs
192.168.10.111
node1-vip.hingu.net node1-vip
192.168.10.222
node2-vip.hingu.net node2-vip
What is difference between RAC ip addresses ?
Public IP adress is the normal IP address typically
used by DBA and SA to manage storage, system and database. Public IP addresses
are reserved for the Internet.
Private IP address is used only for internal
clustering processing (Cache Fusion) (aka as interconnect). Private IP
addresses are reserved for private networks.
VIP is used by database applications to enable fail
over when one cluster node fails. The purpose for having VIP is so client
connection can be failover to surviving nodes in case there is failure
Can application developer
access the private ip ?
No. private IP address is used only for internal
clustering processing (Cache Fusion) (aka as interconnect)
http://rajeevjhaoracle.wordpress.com/2012/01/02/oracle-rac-interview-question-answer/
--------------------------------------------------------------------------------------------------------------
What is RAC?
RAC Architechture?
What is a SCAN Listener?
What is VIP?
What is TAF?
What is FAN?
What is LMON?
What is CACHE FUSION?
What is SPLIT BRAIN in RAC?
How Load balancing works in ORACLE RAC?
What is Voting Disk?
How many backups are there for Voting Disk?
WHY ODD NUMBER OF VOTING DISK IS USED?
How to take Voting Disk backup? Do you take Voting Disk backup?
If you lost Voting Disk, how do you Restore?
What is OCR?
How many backups are there for OCR?
How to take OCR backup? Do you take OCR backup?
If you lost OCR, how do you Restore?
What is a Master Node?
How do you know which is the Master Node without connecting to Database?
Can we have Node 1 in SOLARIS OS and Node 2 in LINUX OS?
Commomnly used commands in RAC?
How to convert standalone database to RAC?
--------------------------------------------------------------------------------------------------------------
What is RAC?
RAC Architechture?
What is a SCAN Listener?
What is VIP?
What is TAF?
What is FAN?
What is LMON?
What is CACHE FUSION?
What is SPLIT BRAIN in RAC?
How Load balancing works in ORACLE RAC?
What is Voting Disk?
How many backups are there for Voting Disk?
WHY ODD NUMBER OF VOTING DISK IS USED?
How to take Voting Disk backup? Do you take Voting Disk backup?
If you lost Voting Disk, how do you Restore?
What is OCR?
How many backups are there for OCR?
How to take OCR backup? Do you take OCR backup?
If you lost OCR, how do you Restore?
What is a Master Node?
How do you know which is the Master Node without connecting to Database?
Can we have Node 1 in SOLARIS OS and Node 2 in LINUX OS?
Commomnly used commands in RAC?
How to convert standalone database to RAC?
Hi, Thank you for this informative blog, I have just started to learn Oracle DBA & RAC online Training and Certification and this blog is definitely very helpful for me. Thank you for this informative blog.
ReplyDeleteBEST POST.
ReplyDeleteoracle rac online training
oracle rac training
Hi, 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