Google+ Followers


Friday, July 27, 2012

compress all the tables inside a tablespace

1-calculate all the tablespace tables sizes before compression:

select  sum(bytes/1024/1024/1024),segment_name,owner from dba_segments where tablespace_name='TABLESPACE_NAME' and segment_type='TABLE' group by segment_name,owner order by 1;

2-generate a script to compress all the tables inside the tablespace given that the table was not compressed before:

 select 'Alter table '||owner||'.'|| table_name||' move compress;' from dba_tables where TABLESPACE_NAME='
TABLESPACE_NAME ' and compression='DISABLED';

3-rebuild all the indexes that was unusable due to the compression:

  select  sum(bytes/1024/1024/1024),segment_name,owner from dba_segments where tablespace_name='
TABLESPACE_NAME ' and segment_type='TABLE' group by segment_name,owner order by 1;

4-calculate the sizes of all the tables inside the tablespace after compression:

select  sum(bytes/1024/1024/1024),segment_name,owner from dba_segments where tablespace_name=' TABLESPACE_NAME ' and segment_type='TABLE' group by segment_name,owner order by 1;

Don't  forget to rebuild all unusable indexes after finishing this process:

select 'Alter index '||owner||'.'||index_name||' rebuild;' from dba_indexes where status='UNUSABLE'
pelase note that compression is useful when you have alot of redundant data available and will be so beneficial.

Manual steps to create Oracle Sample Schemas

The sample schemas can be installed using the database configuration assistant by checking the checkbox Example Schemas.

Manual method of creating oracle Sample Schemas.

When the sample schemas are installed manually, the driving script (mksample.sql) is invoked like so.
The first two arguments are the password for system and sys, respectively, followed by the six passwords for hr, oe, pm, ix, sh and bi, respectively, followed by the default tablspace and the default temp tablespace.

Connect as DBA and issue following command.
@?/demo/schema/mksample passwd_system passwd_sys pw_hr pw_oe pw_pm pw_ix pw_sh pw_bi users temp c:\temp\ora_schema_log

Known schemas in Oracle

This is a (most probably unfinished) list of users  that come with an Oracle installation or with installing the Oracle sample schemas For most of these users, the default password is the same as the username. Where this is not the case, I have noted it.
Used for HTTP access to XML DB.
This user is created with the oracle sample schemas. It owns the Business Intelligenceschema.
The owner of Oracle text (formerly: interMedia text).
Part of the Oracle intelligent agent.
The password is assigned during the installation.
This user is created (or re-created) with ORACLE_HOME/rdbms/admin/catsnmp.sql and dropped with $ORACLE_HOME/rdbms/admin/catnsnmp.sql.
Used by the Directory Integration Platform (DIP) which synchronizes changes in the Oracle Internet Directory with applications in the database.
This user is used for Data Mining.
This schema is used for expression filters.
This seems to be the user for APEX (Oracle Application Express) (on Oracle Express Edition???). XXXXXX seems to be the version number of APEX. So, APEX 2.1 hasflows_020100.
Installed with Oracle sample schemas. It owns the Human Resource schema.
Installed with Oracle sample schemas. It owns the Information Transport schema.
The administration account for Oracle Label Security.
Used by Oracle Spatial to store Geocoder and router data.
The owner of spatial (which is a part of interMedia).
Used for the Oracle Enterprise Manager Database Control.
Its password is generated randomly.
The Data mining schema. Since Oracle 10g, the name can be freely chosen.
Used for the data repository for data mining samples.
Default password: mtrpw.
Installed with Oracle sample schemas. It owns the Order Entry schema.
The schema that owns the OLAP catalogs.
Default password: manager.
Like mdsys: part of interMedia. Third party plugins (as well as Oracle plugins) for interMedia are installed into this schema.
The administration account for interMedia.
Used for the query plan stability feature.
outln owns ol$ol$hints and ol$nodes.
Installed with Oracle sample schemas. It owns the Product Media schema.
Used for Oracle examples.
Its default password is tiger.
This schema is created by executing $ORACLE_HOME/sqlplus/demo/demobld.sql(demodrop.sql drops scott.)
Installed with Oracle sample schemas. It owns the Sales History schema.
Used for SQL/MM Still Image Standard.
Used for database administration. See this link.
The password is set when the database is installed.
The default super user account used to set up and administer enterprise manager.
The password is set when the database is installed.
Used for database administration. See this link.
The password is set when the database is installed.
Used for Oracle trace. Not used anymore since Oracle 8.
Used for Microsoft transaction server support.
Used for OLAP.
Used for ultrasearch.
Default password: wksys.
Used for ultrasearch.
Default password: wmsys.
The owner for Workspace Manager.
Used for XML DB.

Monday, July 16, 2012

Renaming Database Objects

To rename a table:
·         RENAME oldname TO newname;

To rename an index
·         ALTER INDEX oldname RENAME TO newname;

In Oracle 9.2 and above to rename a column
·         ALTER TABLE t1 RENAME COLUMN oldname TO newname;

In Oracle 9.2 and above to rename a constraint
·         ALTER TABLE t1 RENAME CONSTRAINT oldname TO newname;

In Oracle 10.1 and above to rename a tablespace
·         ALTER TABLESPACE  oldname RENAME TO newname;

Friday, July 6, 2012

Oracle DBA General Interview Questions

1. How would you determine the time zone under which a database was operating?

select DBTIMEZONE from dual;

2. Explain the use of setting GLOBAL_NAMES equal to TRUE.
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.

3. What command would you use to encrypt a PL/SQL application?


4. Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

5. Explain the use of table functions.

Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

6. Name three advisory statistics you can collect.

Buffer Cache Advice, Segment Level Statistics, & Timed Statistics

7. Where in the Oracle directory tree structure are audit traces placed?

In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

8. Explain materialized views and how they are used.

Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

9. When a user process fails, what background process cleans up after it?


10. What background process refreshes materialized views?

The Job Queue Processes.

11. How would you determine what sessions are connected and what resources they are waiting for?


12. Describe what redo logs are.

Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.

13. How would you force a log switch?


14. Give two methods you could use to determine what DDL changes have been made.

You could use Logminer or Streams

15. What does coalescing a tablespace do?
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.

16. What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.

17. Name a tablespace automatically created when you create a database.

The SYSTEM tablespace.

18. When creating a user, what permissions must you grant to allow them to connect to the database?

Grant the CONNECT to the user.

19. How do you add a data file to a tablespace?


20. How do you resize a data file?


21. What view would you use to look at the size of a data file?


22. What view would you use to determine free space in a tablespace?


23. How would you determine who has added a row to a table?

Turn on fine grain auditing for the table.

24. How can you rebuild an index?


25. Explain what partitioning is and what its benefit is.

Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

26. You have just compiled a PL/SQL package but got errors, how would you view the errors?

27. How can you gather statistics on a table?
The ANALYZE command.

28. How can you enable a trace for a session?



29. What is the difference between the SQL*Loader and IMPORT utilities?

These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.

30. Name two files used for network connection to a database.

Technical - UNIX

Every DBA should know something about the operating system that the database will be running on. The questions here are related to UNIX but you should equally be able to answer questions related to common Windows environments.

31. How do you list the files in an UNIX directory while also showing hidden files?

ls -ltra

32. How do you execute a UNIX command in the background?
Use the "&"

33. What UNIX command will control the default file permissions when files are created?


34. Explain the read, write, and execute permissions on a UNIX directory.

Read allows you to see and list the directory contents.

Write allows you to create, edit and delete files and subdirectories in the directory.

Execute gives you the previous read/write permissions plus allows you to change into the directory and execute programs or shells from the directory.

35. What is the difference between a soft link and a hard link?

A symbolic (soft) linked file and the targeted file can be located on the same or different file system while for a hard link they must be located on the same file system.

36. Give the command to display space usage on the UNIX file system.

df -lk

37. Explain iostat, vmstat and netstat.

Iostat reports on terminal, disk and tape I/O activity.

Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity.

Netstat reports on the contents of network data structures.

38. How would you change all occurrences of a value using VI?
Use :%s///g

39. Give two UNIX kernel parameters that effect an Oracle install


40. Briefly, how do you install Oracle software on UNIX.

Basically, set up disks, kernel parameters, and run orainst.

41. Describe the Oracle Wait Interface, how it works, and what it provides. What are some limitations? What do the db_file_sequential_read and db_file_scattered_read events indicate?

The Oracle Wait Interface refers to Oracle's data dictionary for managing wait events. Selecting from tables such as v$system_event and v$session_event give you event totals through the life of the database (or session). The former are totals for the whole system, and latter on a per session basis. The event db_file_sequential_read refers to single block reads, and table accesses by rowid. db_file_scattered_read conversely refers to full table scans. It is so named because the blocks are read, and scattered into the buffer cache.

42. How do you return the top-N results of a query in Oracle? Why doesn't the obvious method work?

Most people think of using the ROWNUM pseudocolumn with ORDER BY. Unfortunately the ROWNUM is determined *before* the ORDER BY so you don't get the results you want. The answer is to use a subquery to do the ORDER BY first. For example to return the top-5 employees by salary:


43. What is a database link? What is the difference between a public and a private database link? What is a fixed user database link?

A database link allows you to make a connection with a remote database, Oracle or not, and query tables from it, even incorporating those accesses with joins to local tables.

A private database link only works for, and is accessible to the user/schema that owns it. A global one can be accessed by any user in the database.

A fixed user link specifies that you will connect to the remote db as one and only one user that is defined in the link. Alternatively, a current user database link will connect as the current user you are logged in as.

44. What are some advantages to using Oracle's CREATE DATABASE statement to create a new database manually?

* You can script the process to include it in a set of install scripts you deliver with a product.
* You can put your create database script in CVS for version control, so as you make changes or adjustments to it,
you can track them like you do changes to software code.
* You can log the output and review it for errors.
* You learn more about the process of database creation, such as what options are available and why.

Oracle RAC Interview Questions

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.
What is RAC and how is it different from non RAC databases?
RAC stands for Real Application Cluster, you have n number of instances running in their own separate nodes and based on the shared storage. Cluster is the key component and is a collection of servers operations as one unit. RAC is the best solution for high performance and high availably. Non RAC databases has single point of failure in case of hardware failure or server crash.

Give the usage of srvctl :
srvctl start instance -d db_name -i "inst_name_list" [-o start_options]
srvctl stop instance -d name -i "inst_name_list" [-o stop_options]
srvctl stop instance -d orcl -i "orcl3,orcl4" -o immediate
srvctl start database -d name [-o start_options]
srvctl stop database -d name [-o stop_options]
srvctl start database -d orcl -o mount
Mention the Oracle RAC software components :Oracle RAC is composed of two or more database instances. They are composed of Memory structures and background processes same as the single instance database.Oracle RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache Service) that enable cache fusion.Oracle RAC instances are composed of following background processes:
ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor

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

Give Details on ACMS:
ACMS stands for Atomic Controlfile Memory Service.In an Oracle RAC environment ACMS is an agent that ensures a distributed SGA memory update(ie)SGA updates are globally committed on success or globally aborted in event of a failure.
What is Cache Fusion?
Cache fusion is the mechanism to transfer the data block from memory to memory of one node to the other.If two nodes require the same block for query or update, the block must be transfered from the cache of one node to the other. RAC system must equipped with low-latency and high speed inter-connect to make it happen.
Give Details on 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 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.
Cache Fusion is essentially a memory-to-memory transfer of data between the nodes in the RAC environment. Before Cache Fusion, a node was required to write some of the data to disk before it could be transferred to the next node in the cluster. Cache Fusion does a straight memory-to-memory transfer. In addition, each node's SGA has a map of what data is contained in the other node's data caches.
The performance improvement is phenomenal. Oracle leverages the vendor's high speed interconnects between the nodes to achieve the cache-to-cache data transfers. Before Cache Fusion, when you added a node to the cluster to increase performance of the application, it didn't always provide you with the performance improvement that you hoped for. With Cache Fusion, you can easily cost justify the addition of another node into a RAC cluster to increase the performance of the application running on it. Oracle sales pitches describe it as 'near linear horizontal scalability'.
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.

Give details on GTX0-j :
The process provides transparent support for XA global transactions in a RAC environment.The database autotunes the number of these processes based on the workload of XA global transactions.

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.

Give details on LMD:
This process is called as global enqueue service daemon. This process manages incoming remote resource requests within each instance.

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.

Give details on LCK0:
This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as libry and row cache requests.

Give details on RMSn:
This process is called as Oracle RAC management process.These pocesses perform managability tasks for Oracle RAC.Tasks include creation of resources related Oracle RAC when new instances are added to the cluster.

Give details on RSMN:
This process is called as Remote Slave Monitor.This process manages background slave process creation andd 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.

What components in RAC must reside in shared storage?
All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.

What is the significance of using cluster-aware shared storage in an Oracle RAC environment?
All instances of an Oracle RAC can access all the datafiles,control files, SPFILE's, redolog files when these files are hosted out of cluster-aware shared storage which are group of shared disks.

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

How can we configure the cluster interconnect?
Configure User Datagram Protocol(UDP) on Gigabit ethernet for cluster interconnect.On unix and linux systems we use UDP and RDS(Reliable data socket) protocols to be used by Oracle Clusterware.Windows clusters use the TCP protocol.

Can we use crossover cables with Oracle Clusterware interconnects?No, crossover cables are not supported with Oracle Clusterware intercnects.

What is the use of cluster interconnect?Cluster interconnect is used by the Cache fusion for inter instance communication.

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.

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 charateristics include a unique name, workload balancing and failover options,and high availability characteristics.
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 is a virtual IP address or VIP?
A virtl IP address or VIP is an alternate IP address that the client connectins use instead of the standard public IP address. To configureVIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network.

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.

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 are the administrative tools used for Oracle RAC environments?
Oracle RAC cluster can be administered as a single image using OEM(Enterprise Manager),SQL*PLUS,Servercontrol(SRVCTL),clusterverificationutility(cvu),DBCA,NETCA

How do we verify that RAC instances are running?Issue the following query from any one node connecting through SQL*PLUS.
$connect sys/sys as sysdba
The query gives the instance number under INST_NUMBER column,host_:instancename under INST_NAME column.

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.

Where can we apply FAN UP and DOWN events?FAN UP and FAN DOWN events can be applied to instances,services and nodes.
State the use of FAN events in case of a cluster configuration change?
During times of cluster configuration changes,Oracle RAC high availability framework publishes a FAN event immediately when a state change occurs in the cluster.So applications can receive FAN events and react immediately.This prevents applications from polling database and detecting a problem after such a state change.

Why should we have seperate homes for ASm instance?
It is a good practice to have ASM home seperate from the database hom(ORACLE_HOME).This helps in upgrading and patching ASM and the Oracle database software independent of each other.Also,we can deinstall the Oracle database software independent of the ASM instance.

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.

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.

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

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:
What is ORA-00603: ORACLE server session terminated by fatal error or ORA-29702: error occurred in Cluster Group Service operation?
RAC node name was listed in the loopback address...

Can the DML_LOCKS and RESULT_CACHE_MAX_SIZE be identical on all instances?These parameters can be identical on all instances only if these parameter values are set to zero.
What two parameters must be set at the time of starting up an ASM instance in a RAC environment?The parameters CLUSTER_DATABASE and INSTANCE_TYPE must be set.

Mention the components of Oracle clusterware:
Oracle clusterware is made up of components like voting disk and Oracle Cluster Registry(OCR).
What is a CRS resource?
Oracle clusterware is used to manage high-availability operations in a cluster.Anything that Oracle Clusterware manages is known as a CRS resource.Some examples of CRS resources are database,an instance,a service,a listener,a VIP address,an application process etc.

What is the use of OCR?Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).

How does a Oracle Clusterware manage CRS resources?Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).

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

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

How do we verify an existing current backup of OCR?We can verify the current backup of OCR using the following command : ocrconfig -showbackup
What are the performance views in an Oracle RAC environment?
We have v$ views that are instance specific. In addition we have GV$ views called as global views that has an INST_ID column of numeric data type.GV$ views obtain information from individual V$ views.
What are the types of connection load-balancing?
There are two types of connection load-balancing:server-side load balancing and client-side load balancing.

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.

What are the three greatest benefits that RAC provides??
The three main benefits are availability, scalability, and the ability to use low cost commodity hardware. RAC allows an application to scale vertically, by adding CPU, disk and memory resources to an individual server. But RAC also provides horizontal scalability, which is achieved by adding new nodes into the cluster. RAC also allows an organization to bring these resources online as they are needed. This can save a small or midsize organization a lot of money in the early stages of a project.
In a RAC environment, if a node in the cluster fails, the application continues to run on the surviving nodes contained in the cluster. If your application is configured correctly, most users won't even know that the node they were running on became unavailable.

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 are the different network components in Oracle 10g RAC?We have public, private, and VIP components. Private interfaces is for intra node communication. VIP is all about availability of application. When a node fails then the VIP component will fail over to some other node, this is the reason that all applications should be based on VIP components.  This means that tns entries should have VIP entry in the host list.