Oracle DBA Interview Questions And Answers

@ : Home > Interview> Oracle DBA Interview Questions And Answers

1. Where are parameter files stored and how can you start a database using a specific parameter file?

In UNIX they are stored in the location $ORACLE_HOME/dbs and ORACLE_HOME/database for Windows directory.

Oracle by default starts with SPFILE located in $ORACLE_HOME/dbs.

If you want to start the database with particular file, we can append it at the startup command as
SQL > startup PFILE = ‘full path of parameter file ‘;

You can create PFILE from SPFILE'
SQL>create PFILE from SPFILE;
update parameter in PFILE then run the following command
SQL>Create SFILE from PFILE;

2. What is PGA_AGGREGATE_TARGET parameter?

  • PGA_AGGREGATE TARGET parameter specifies target aggregate PGA memory available to all server process attached to an instance.
  • Oracle sets its value to 20% of SGA.
  • It is used to set overall size of work-area required by various components.
  • Its value can be known by querying v$pgastat dynamic performance view.
  • From sqlplus it can be known by using SQL> show parameter pga.

3. What is the purpose of configuring more than one Database Writer Processes and How many should be used?

- DBWn process records modified buffers in Database Buffer Cache to data files so that user process can always find free buffers.

- To efficiently release the buffer cache to make it available to user processes, you can use multiple DBWn processes.

- We can configure new processes (DBW1 through DBW9 and DBWa through DBWj) to improve write performance if our system modifies data heavily.

- The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes up to a maximum number of 20.

- If the Unix system being used is fitted of asynchronous input/output processing, then only one DBWn process is enough if not the case the whole DBWn processes required will be twice the number of disks used by oracle, and this can be set with DB_WRITER_PROCESSES initialization parameter.

4. List out the major installation steps of oracle software on UNIX in brief?

- Set up disk and make sure you have Installation file (run Installer) in your folder.
- Check the swap and TEMP space.
- Export the following environment variables
        1. ORACLE_BASE
        2. ORACLE_HOME
        3. PATH
        4. LD_LIBRARY_PATH
        5. TNS_ADMIN

- Set up the kernel parameters and file maximum descriptors.
- Set up limt.conf parameters.
- Source the Environment file to the respective bash profile and now run Oracle Universal Installer.

5. Can we check number of instances running on Oracle server and how to set kernel parameters in Linux?

  • view the /etc/oratab file on a server gives the list of oracle instances running on your server.
  • view /etc/sysctl.conf file with 'cat' command will open a text file listing out kernel level parameters.
  • We can make changes to kernel parameters as required for our environment only as a root user.
  • To make the changes affected permanently to kernel run the command /sbin/sysctl –p.
  • We must also set file maximum descriptors during oracle installation which can be done by editing /etc/security/limits.conf as a root user.