Month: April 2017

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.

1.What is a diskgroup in ASM?

• A disk group consists of multiple disks and is the fundamental object that ASM manages.
• Each disk group contains the metadata that is required for the management of space in the disk group.
• The ASM instance manages the metadata about the files in a Disk Group in the same way that a file system manages metadata about its files.
• However, the vast majority of I/O operations do not pass through the ASM instance.

2.Can my disks in a diskgroup can be varied size? For example one disk is of 100GB and another disk is of 50GB. If so how does ASM manage the extents?

Yes, disk sizes can be varied, Oracle ASM will manage data efficiently and intelligent by placing the extents proportional to the size of the disk in the disk group, bigger diskgroups have more extents than lesser ones.

3.What is the major difference between 10g and 11g RAC?

Well, there is not much difference between 10g and 11g (Release-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 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

4.What is Grid Naming Service(GNS)?

Grid Naming service (GNS) is alternative service to Domain Naming Service (DNS) , which will act as a sub domain in your DNS but managed by Oracle, with GNS the connection is routed to the cluster IP and manages internally.

5.What are the file types that ASM support and keep in disk groups?

6.List Key benefits of ASM?

• Stripes files rather than logical volumes
• Provides redundancy on a file basis
• Enables online disk reconfiguration and dynamic rebalancing
• Reduces the time significantly to resynchronize a transient failure by tracking changes while disk is offline
• Provides adjustable rebalancing speed
• Is cluster-aware
• Supports reading from mirrored copy instead of primary copy for extended clusters
• Is automatically installed as part of the Grid Infrastructure

7.List some of the background process that used in ASM?

8.What is node listener?

In 11gr2 the listeners will run from Grid Infrastructure software home

• The node listener is a process that helps establish network connections from ASM clients to the ASM instance.
• Runs by default from the Grid $ORACLE_HOME/bin directory
• Listens on port 1521 by default
• Is the same as a database instance listener
• Is capable of listening for all database instances on the same machine in addition to the ASM instance
• Can run concurrently with separate database listeners or be replaced by a separate database listener
• Is named tnslsnr on the Linux platform

9.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

10.How to find Voting Disk location?

To determine the location of the voting disk write the following command on Linux terminal as Root User:

# crsctl query css votedisk

11.What are types of ASM Mirroring?

12.What is ASM Striping?

ASM can use variable size data extents to support larger files, reduce memory requirements, and improve performance.

Each data extent resides on an individual disk.

Data extents consist of one or more allocation units.

The data extent size is:

• Equal to AU for the first 20,000 extents (0–19999)
• Equal to 4 × AU for the next 20,000 extents (20000–39999)
• Equal to 16 × AU for extents above 40,000

ASM stripes files using extents with a coarse method for load balancing or a fine method to reduce latency.

• Coarse-grained striping is always equal to the effective AU size.
• Fine-grained striping is always equal to 128 KB.

13.How many ASM Diskgroups can be created under one ASM Instance?

ASM imposes the following limits:

• 63 disk groups in a storage system
• 10,000 ASM disks in a storage system
• Two-terabyte maximum storage for each ASM disk (non-Exadata)
• Four-petabyte maximum storage for each ASM disk (Exadata)
• 40-exabyte maximum storage for each storage system
• 1 million files for each disk group
• ASM file size limits (database limit is 128 TB):

1. External redundancy maximum file size is 140 PB.
2. Normal redundancy maximum file size is 42 PB.
3. High redundancy maximum file size is 15 PB.

14.How to find the cluster network settings?

To determine the list of interfaces available to the cluster:

$ oifcfg iflist –p -n

To determine the public and private interfaces that have been configured:

$ oifcfg getif

To determine the Virtual IP (VIP) host name, VIP address, VIP subnet mask, and VIP interface name:

$ srvctl config nodeapps -a

15.Managing or Modifying SCAN in Oracle RAC?

To add a SCAN VIP resource:

$ srvctl add scan -n cluster01-scan

To remove Clusterware resources from SCAN VIPs:

$ srvctl remove scan [-f]

To add a SCAN listener resource:

$ srvctl add scan_listener

$ srvctl add scan_listener -p 1521

To remove Clusterware resources from all SCAN listeners:

$ srvctl remove scan_listener [-f]

16.I’m going to do add disks to my ASM diskgroup, how long will this rebalance take?

Rebalance time is heavily driven by the three items:

• Amount of data currently in the diskgroup
• IO bandwidth available on the server
• ASM_POWER_LIMIT or Rebalance Power Level

17.We are migrating to a new storage array. How do I move my ASM database from storage A to storage B?

Given that the new and old storage are both visible to ASM, simply add the new disks to the ASM disk group and drop the old disks. ASM rebalance will migrate data online.

Note 428681.1 covers how to move OCR/Voting disks to the new storage array

18.Is it possible to unplug an ASM disk group from one platform and plug into a server on another platform (for example, from Solaris to Linux)?

No, Cross-platform disk group migration not supported. To move datafiles between endian-ness platforms, you need to use XTTS, Datapump or Streams.

19.How does ASM work with multipathing software?

It works great! Multipathing software is at a layer lower than ASM, and thus is transparent.
You may need to adjust ASM_DISKSTRING to specify only the path to the multipathing pseudo devices.

http://dev.oraclesolutions.pk/real-application-clusterrac-interview-questions-part-4/

http://dev.oraclesolutions.pk/real-application-clusterrac-interview-questions-part-3/

http://dev.oraclesolutions.pk/real-application-clusterrac-interview-questions-part-2/

http://dev.oraclesolutions.pk/real-application-clusterrac-interview-questions-part-2/

http://dev.oraclesolutions.pk/real-application-clusterrac-interview-questions-part-1/