Interview Questions
1. What are Procedure, functions and Packages?
- Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
- Procedures do not return values while Functions return one Value.
- Packages provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents
2.What is a ROWID and Why does it needed?
ROWID is the physical address (location) of the row on the disk. This is the fastest way to access a row in a table.
3.How many types of SQL Statements are there in Oracle?
There are 6 types of SQL statements.
Data Definition Language (DDL): The DDL statements define and maintain objects and drop objects.
Data Manipulation Language (DML): The DML statements manipulate database data.
Transaction Control Statements: Manage change by DML.
Session Control: Used to control the properties of current session enabling and disabling roles.
System Control Statements: Change Properties of Oracle Instance.
Embedded SQL: Incorporate DDL, DML and T.C.S in Programming Language.
4.How many Integrity Rules are there and what are they?
There are Three Integrity Rules as follows:
Entity Integrity Rule: The Entity Integrity Rule enforces that the Primary key cannot be Null.
Foreign Key Integrity Rule: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced. When there is data in Child Tables the Master tables cannot be deleted.
Business Integrity Rules: The Third Integrity rule is about the complex business processes which cannot be implemented by the above 2 rules.
5.How to implement the If statement in the Select Statement?
We can implement the if statement in the select statement by using the Decode statement.
e.g select DECODE (EMP_CAT,’1′,’First’,’2′,’Second’Null);
Here, the Null is the else statement where null is done .
6.How do you use the same LOV for 2 columns?
- We can use the same LOV for 2 columns by passing the return values in global values and using the global values in the code.
7.What are snap shots and views?
- Snapshots are mirror or replicas of tables.
- Views are built using the columns from one or more tables.
- The Single Table View can be updated but the view with multi table cannot be updated.
8.What is the difference between candidate key, unique key and primary key?
- Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows.
- Unique key is also useful for identifying the distinct rows in the table.
9.What is Row Chaining?
- The data of a row in a table may not be able to fit the same data block.
- Data for row is stored in a chain of data blocks.
10.What is the difference between deleting and truncating of tables?
- Deleting a table will not remove the rows from the table but entry is there in the database dictionary and it can be retrieved.
- While truncating a table deletes it completely and it cannot be retrieved.
11.What is the Difference between a post query and a pre query?
- A post query will fire for every row that is fetched but the pre query will fire only once.
12.How to Delete the Duplicate rows in the table?
We can delete the duplicate rows in the table by using the Rowid
Example: DELETE FROM table_name a Where rowid>(select min(rowid) from table_name b where a.table_no=b.table_no);
13.Can we disable database trigger? How?
Yes we can disable database triggers through following issuing statement
ALTER TABLE TABLE [DISABLE all_trigger ]
14.Is space acquired in blocks or extents?
- Space is acquired in extents.
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?
|
Control files |
Flashback logs | Data Pump dump sets |
| Data files | DB SPFILE | Data Guard configuration |
| Temporary data files | RMAN backup sets | Change tracking bitmaps |
| Online redo logs | RMAN data file copies | OCR files |
| Archive logs | Transport data files |
ASM SPFILE |
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?
| Process |
Description |
| RBAL | Opens all device files as part of discovery and coordinates the rebalance activity |
| ARBn | One or more slave processes that do the rebalance activity |
| GMON | Responsible for managing the disk-level activities such as drop or offline and advancing the ASM disk group compatibility |
| MARK | Marks ASM allocation units as stale when needed |
| Onnn | One or more ASM slave processes forming a pool of connections to the ASM instance for exchanging messages |
| PZ9n | One or more parallel slave processes used in fetching data on clustered ASM installation from GV$ views |
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?
| Disk Group Type | Supported MirroringLevels | Default Mirroring Level |
| External redundancy | Unprotected (None) | Unprotected (None) |
| Normal redundancy | Two-wayThree-way
Unprotected (None) |
Two-way |
| High redundancy | Three-way | Three-way |
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):
- External redundancy maximum file size is 140 PB.
- Normal redundancy maximum file size is 42 PB.
- 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/
1.What is dynamic remastering? When will the dynamic remastering happens?
- Dynamic Remastering is ability to move the ownership of resource from one instance to another instance in RAC.
- Dynamic Resource remastering is used to implement for resource affinity for increased performance.
- Resource affinity optimized the system in situation where update transactions are being executed in one instance.
- When activity shift to another instance the resource affinity correspondingly move to another instance.
- If activity is not localized then resource ownership is hashed to the instance.
2.How you check the health of Your RAC Database?
‘crsctl’ command from Root or Oracle user can be used to check the clusterware health ,while for starting or stopping we have to use Root user or any privilege user.
$ crsctl check crs
3.If there is some issue with virtual IP how will you troubleshoot it?How will you change virtual ip?
To change the VIP (virtual IP) on a RAC node, use the command:
$ srvctl modify nodeapps -A new_address
4. Give Details on ACMS?
ACMS stands for Atomic Controlfile Memory Service.
In an Oracle RAC environment ACMS is an agent that ensures a distributed SGA memory update, (i.e.) SGA updates are globally committed on success or globally aborted in event of a failure.
5.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 is 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.
- GC BUFFER BUSY is the time remote instance locally spends accessing the requested data block.
6. Give details on GTX0-j?
- This process provides transparent support for XA global transactions in a RAC environment.
- The database auto-tunes the number of these processes based on the workload of XA global transactions.
7.Give details on LMON?
- This process monitors global enques and resources across the cluster and performs global enqueue recovery operations.
- This is called as Global Enqueue Service Monitor.
8. Give details on LMD?
- This process is called as global enqueue service daemon.
- This process manages incoming remote resource requests within each instance.
9. Give details on LMS?
- This process is called as Global Cache service process.
- This process maintains statuses of datafiles and each cahed block by recording information in a Global Resource Dectory(GRD).
- This process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances.
- This processing is a part of cache fusion feature.
10. Give details on LCK0?
- This process is called as Instance enqueue process.
- This process manages non-cache fusion resource requests such as library and row cache requests.
11. Give details on RMSn?
- This process is called as Oracle RAC management process.
- These processes perform manageability tasks for Oracle RAC.
- Tasks include creation of resources related Oracle RAC when new instances are added to the cluster.
12. Give details on RSMN?
- This process is called as Remote Slave Monitor.
- This process manages background slave process creation and communication on remote instances.
- This is a background slave process.
- This process performs tasks on behalf of a co-ordinating process running in another instance.
13. Can we use crossover cables with Oracle Clusterware interconnects?
No, crossover cables are not supported with Oracle Clusterware interconnects.
14.How do users connect to database in an Oracle RAC environment?
- Users can access a RAC database using a client/server configuration or through one or more middle tiers, with or without connection pooling.
- Users can use oracle services feature to connect to database.
15. Why should we have separate homes for ASM instance?
- It is a good practice to have ASM home separate from the database home(ORACLE_HOME).
- This helps in upgrading and patching ASM and the Oracle database software independent of each other.
- Also,we can deinstall the Oracle database software independent of the ASM instance.
16. What is the advantage of using ASM?
- Having ASM is the Oracle recommended storage option for RAC databases as the ASM maximizes performance by managing the storage configuration across the disks.
- ASM does this by distributing the database file across all of the available storage within our cluster database environment.
17.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.
18. Can rolling upgrade be used to upgrade from 10g to 11g database?
No,it can be used only for Oracle database 11g releases(from 11.1).
19. 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