Real Application Cluster(RAC) Interview Questions Part 6

1.What is Partition Pruning ?

• Oracle optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access by those SQL statements.
• In other words, partition pruning is the skipping of unnecessary index and data partitions or subpartitions in a query.

 

 

2.What is fencing?

• I/O fencing prevents updates by failed instances, and detecting failure and preventing split brain in cluster.
• When a cluster node fails, the failed node needs to be fenced off from all the shared disk devices or diskgroups.
• This methodology is called I/O Fencing, sometimes called Disk Fencing or failure fencing.

 

 

3.What is Grid Plug and Play (GPnP)?

• Grid Plug and Play (GPnP) eliminates per-node configuration data and the need for explicit add and delete node steps.
• This allows a system administrator to take a template system image and run it on a new node with no further configuration.
• This removes many manual operations, reduces the opportunity for errors, and encourages configurations that can be changed easily.
• Removal of the per-node configuration makes the nodes easier to replace, because they do not need to contain individually-managed state.
• Grid Plug and Play reduces the cost of installing, configuring, and managing database nodes by making their per-node state disposable.
• It allows nodes to be easily replaced with regenerated state.

 

 

4.Can I use Fast Connection Failover (FCF) and Transparent Application Failover (TAF) together?

No, Only one of them should be used at a time.

 

 

5.What is the status of Fast Connection Failover (FCF) with Universal Connection Pool (UCP)?

FCF is now deprecated along with the Implicit Connection Caching in favor of using the Universal Connection Pool (UCP) for JDBC.

 

 

6.What is the Service Management Policy?

• When you use automatic services in an administrator-managed database, during planned database startup, services may start on the first instances to start rather than their preferred instances.
• Prior to Oracle RAC 11 g release 2 (11.2), all services worked as though they were defined with a manual management policy.

 

 

7.Why does my user appear across all nodes when querying GV$SESSION when my service does not span all nodes?

• The problem is you are querying GV$SESSION as the ABC user and this results in the “strange” behaviour.
• If you select gv$session, 2 parallel servers are spawned to query the v$session on each node.
• This happens as the same user. Hence when you query gv$session as ABC you are seeing 3 (one real and 2 parallel slaves querying v$session on each instance).
• The reason you are seeing 1 on one node and 3 on the other is the order in which the parallel processes query the v$session.
• Take the sys (or any other) user to query the session of ABC and you will not see this problem.

 

 

8.How do I determine the “Master” node?

• For the cluster synchronization service (CSS), the master can be found by searching $GI_HOME/log/cssd/ocssd.log.
• For master of an enqueue resource with Oracle RAC, you can select from v$ges_resource.
• There should be a master_node column.

 

 

9.What are the different types of failover mechanisms available?

• JDBC-THIN driver supports Fast Connection Failover (FCF)
• JDBC-OCI driver supports Transparent Application Failover (TAF)
• JDBC-THIN 11gR2 supports Single Client Access Name (SCAN)

 

 

10.What is the guideline on how to auto-extend data files?

• When configuring a file to auto extend, the size of the extension should cover all disks in the ASM disk group to optimize balance.
• For example, with a 4 MB AU size and 128 disks, the size of the extension should be a multiple of 512MB (4*128).

 

 

11.What init.ora parameters does a user need to configure for ASM instances?

The default parameter settings work perfectly for ASM. The only parameters needed for 11g ASM:

• PROCESSES*
• ASM_DISKSTRING*
• ASM_DISKGROUPS
• INSTANCE_TYPE

 

 

12.How does the database interact with the ASM instance and how do I make ASM go faster?

• ASM is not in the I/O path so ASM does not impede the database file access.
• Since the RDBMS instance is performing raw I/O, the I/O is as fast as possible.

 

 

13.Do I need to define the RDBMS FILESYSTEMIO_OPTIONS parameter when I use ASM?

No, RDBMS does I/O directly to the raw disk devices, the FILESYSTEMIO_OPTIONS  parameter is only for filesystems.

 

 

14.Why Oracle recommends two diskgroups?

Oracle recommends two diskgroups to provide a balance of manageability, utilization, and performance.

 

 

15.We have a 16 TB database. I’m curious about the number of disk groups we should use; e.g. 1 large disk group, a couple of disk groups, or otherwise?

• For VLDBs you will probably end up with different storage tiers.
• For Example: Some of our large customers they have Tier1 (RAID10 FC), Tier2 (RAID5 FC), Tier3 (SATA), etc.
• Each one of these is mapped to a diskgroup.

 

 

16.We have a new app and don’t know our access pattern, but assuming mostly sequential access, what size would be a good AU fit?

• For 11g ASM/RDBMS it is recommended to use 4MB ASM AU for disk groups.
• See Metalink Note 810484.1

 

 

17.Would it be better to use BIGFILE tablespaces, or standard tablespaces for ASM?

• The use of Bigfile tablespaces has no bearing on ASM (or vice versa).
• In fact most database object related decisions are transparent to ASM.

 

 

18.What is the best LUN size for ASM?

In most cases the storage team will dictate to you based on their standardized LUN size. The ASM administrator merely has to communicate the ASM Best Practices and application  characteristics to storage:

• Need equally sized / performance LUNs
• Minimum of 4 LUNs
• The capacity requirement
• The workload characteristic (random r/w, sequential r/w) & any response time SLA
• Using this info , and their standards, the storage folks should build a nice LUN group set for you.