Oracle 11g dba interview questions and answers - PL SQL Interview Question

1) What is a database?

• Database offer a single point of mechanism for storing and retrieving information with the help of tables.
• Table is made up of columns and rows where each column stores specific attribute and each row displays a value for the corresponding attribute.
• It is a structure that stores information about the attributes of the entities and relationships among them.
• It also stores data types for attributes and indexes.
• Well known DBMS include Oracle, ibm db2, Microsoft sql server, Microsoft access, mysql and sqlLite.

2) What are the different types of storage systems available and which one is used by Oracle?

Two types of storage systems are available
• Relational Database Management System (RDBMS) and Hierarchical Storage Management System (HSM)

• Most databases use RDBMS model, Oracle also uses RDBMS model.

• Hierarchical Storage Management System (HSM)
• Information Management System (IMS) from IBM.
• Integrated Database Management System (IDMS) from CA.

3) Explain some examples of join methods

• Join methods are of mainly 3 types
• Merge Join – Sorting both the tables using join key and then merge the rows which are sorted.
• Nested loop join – It gets a result set after applying filter conditions based on the outer table.
• Then it joins the inner table with the respective result set.
• Hash join - It uses hash algorithm first on smaller table and then on the other table to produce joined columns. After that matching rows are returned.

4) What are the components of logical data model and list some differences between logical and physical data model?

Components of logical data model are
• Entity – Entity refers to an object that we use to store information. It has its own table.
• Attribute – It represents the information of the entity that we are interested in. It is stored as a column of the table and has specific datatype associated with it.
• Record – It refers to a collection of all the properties associated with an entity for one specific condition, represented as row in a table.
• Domain – It is the set of all the possible values for a particular attribute.
• Relation – Represents a relation between two entities.
Difference between Logical and Physical data model

• Logical data model represents database in terms of logical objects, such as entities and relationships.
• Physical data model represents database in terms of physical objects, such as tables and constraints.

5) What is normalization? What are the different forms of normalization?

• Normalization is a process of organizing the fields and tables of a relational database to minimize redundancy and dependency.
• It saves storage space and ensures consistency of our data.

There are six different normal forms

• First Normal Form – If all underlying domains contain atomic values only.
• Second Normal Form – If it is in first normal form and every non key attribute is fully functionally dependent on primary key.
• Third Normal Form - If it is in 2nd normal form and every non key attribute is non transitively dependent on the primary key.
• Boyce Codd Normal Form - A relation R is in BCNF if and only every determinant is a candidate key.

• Fourth Normal Form
• Fifth Normal Form

6) Differentiate between a database and Instance and explain relation between them?

• Database is a collection of three important files which include data files, control files and redo log files which physically exist on a disk
• Whereas instance is a combination of oracle background process (SMON, PMON, DBWR, LGWR) and memory structure (SGA, PGA).
• Oracle background processes running on a computer share same memory area.
• An instance can mount and open only a single database, ever.
• A database may be mounted and opened by one or more instances (using RAC).

7) What are the components of SGA?

• SGA is used to store shared information across all database users.
• It mainly includes Library cache, Data Dictionary cache, Database Buffer Cache, Redo log Buffer cache, Shared Pool.
• Library cache – It is used to store Oracle statements.
• Data Dictionary Cache – It contains the definition of Database objects and privileges granted to users.
• Data Base buffer cache – It holds copies of data blocks which are frequently accessed, so that they can be retrieved faster for any future requests.
• Redo log buffer cache – It records all changes made to the data files.

8) Difference between SGA and PGA.

• SGA (System Global Area) is a memory area allocated during an instance start up.
• SGA is allocated as 40% of RAM size by default.
• SGA size is controlled by DB_CACHE_SIZE parameter defined in initialization parameter file (init.ora file or SPFILE).

• PGA (Program or Process Global Area) is a memory area that stores a user session specific information.

• PGA is allocated as 10% of RAM size by default.

9) What are the disk components in Oracle?

These are the physical components which gets stored in the disk.
• Data files
• Redo Log files
• Control files
• Password files
• Parameter files

10) What is System Change Number (SCN)?

• SCN is a unique ID that Oracle generates for every committed transaction.
• It is recorded for every change in the redo entry.
• SCN is also generated for every checkpoint (CKPT) occurred.
• It is an ever increasing number which is updated for every 3 seconds
• You can get the SCN number by querying select SCN from v$database from SQLPLUS.

11) What is Database Writer (DBWR) and when does DBWR write to the data file?

• DBWR is a background process that writes data blocks information from Database buffer cache to data files.
There are 4 important situations when DBWR writes to data file
• Every 3 seconds
• Whenever checkpoint occurs
• When server process needs free space in database buffer cache to read new blocks.
• Whenever number of changed blocks reaches a maximum value.

12) What is Log Writer and when does LGWR writes to log file?

• LGWR writes redo or changed information from redo log buffer cache to redo log files in database.
• It is responsible for moving redo buffer information to online redo log files, when you commit and a log switch also occurs.
• LGWR writes to redo files when the redo log buffer is 1/3 rd full.
• It also writes for every 3 seconds.

• Before DBWR writes modified blocks to the datafiles, LGWR writes to the
log file

13) Which Table spaces are created automatically when you create a database?

• SYSTEM tablespace is created automatically during database creation.
• It will be always online when the database is open.
Other Tablespaces include
• SYSAUX tablespace
• UNDO tablespace
• TEMP tablespace
• UNDO & TEMP tablespace are optional when you create a database.

14) Which file is accessed first when Oracle database is started and What is the difference between SPFILE and PFILE?

• Init<SID>.ora parameter file or SPFILE is accessed first .( SID is instance name)
• Settings required for starting a database are stored as parameters in this file.

• SPFILE is by default created during database creation whereas PFILE should be created from SPFILE.
• PFILE is client side text file whereas SPFILE is server side binary file.
• SPFILE is a binary file (it can’t be opened) whereas PFILE is a text file we can edit it and set parameter values.
• Changes made in SPFILE are dynamically effected with running database whereas PFILE changes are effected after bouncing the database.
• We can backup SPFILE using RMAN.

15) What are advantages of using SPFILE over PFILE?

• SPFILE is available from Oracle 9i and above.
• Parameters in SPFILE are changed dynamically.
• You can’t make any changes to PFILE when the database is up.
• RMAN cant backup PFILE, It can backup SPFILE.
• SPFILE parameters changes are checked before they are accepted as it is maintained by Oracle server thereby reducing the human typo errors.

16) How can you find out if the database is using PFILE or SPFILE?

• You can query Dynamic performance view (v$parameter) to know your database is using PFILE or SPFILE.

• SQL> select value from V$parameter where name= ‘SPFILE’;
• A non-null value indicates the database is using SPFILE.
• Null value indicates database is using PFILE.
• You can force a database to use a PFILE by issuing a startup command as
• SQL> startup PFILE = ‘full path of Pfile location’;

17) 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 specific file we can append it at the startup command as
SQL > startup PFILE = ‘full path of parameter file ‘;

• You can create PFILE from SPFILE as create PFILE from SPFILE;
• All the parameter values are now updated with SPFILE.
• Similarly, create SPFILE from PFILE; command creates SPFILE from PFILE.

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

19) What is the purpose of configuring more than one Database Writer Processes? How many should be used? (On UNIX)

• DBWn process writes modified buffers in Database Buffer Cache to data files, so that user process can always find free buffers.

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

• We can configure additional 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 upto a maximum number of 20.

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

20) 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 dump.
• 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.
• Source the Environment file to the respective bash profile and now run Oracle Universal Installer.

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

• Editing the /etc/oratab file on a server gives the list of oracle instances running on your server.

• Editing /etc/sysctl.conf file with vi editor 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.