Oracle DBA interview questions and answers:
What is the difference between Oracle DBA role and an Oracle Developer role in an organization? Are there any similarities between both?
An Oracle developer is mainly responsible for developing back-end applications. They do data modelling and develop the program according to business requirement. They design tables, create indexes ,views,procedure,function and other type of constraints. They are expected to know SQL and PL/SQL programming develop the program . The developer using this languages to coordinate and communicate with database, the Oracle developers are not expected to administer the database software itself.
On the other side, an Oracle DBA’s main responsibilities is to administer the database which involves like doing maintenance to keep the databases up and running 24/7, taking backups, enforcing security policies, ,manage data security ,data and database recovery etc. DBAs are not primarily assigned to develop code. DBA are supposed to have a good knowledge of SQL and PL/SQL like a developer as these are also required for administering the database.
According to the structure of the organization, DBAs might also be assigned development tasks or at least assist the developers and application team where necessary.
The main responsibility of an Oracle DBA is to keep the Oracle Databases of the organization up and running. This may involve installing and configuring a database from scratch.On a running system, the DBA will be the only privileged person who can start up and shutdown the database when ever required with proper approval.
The DBA will create new users and manage the privileges of each user as per user request and requirement.
He will take regular backups to ensure that data is safe. In case of a disaster, he will be responsible of restoring the database from existing backups. He will have to do monitor the space usage and do capacity planning for the database.
He will have to monitor database activities. He will have to tune the database so that it works at an acceptable performance.
He is expected to follow the latest security patches, Bug fix patch and apply them when applicable. DBA is the responsible to upgrade the databases as per industry and oracle upgrade.
What is a database?
Database is a structured set of data held in a computer, especially one that is accessible in various ways.Database is 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..
What are the different types of storage systems available and which one is used for Oracle databases-
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.
What is different 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 logs files which physically exist on a file system or ASM disk, whereas instance is a combination of oracle background process like SMON, PMON, DBWR, LGWR and memory structure (SGA and PGA).
Oracle background processes regular running on a computer and share same memory area.
An instance can mount and open only a single database.
A database may be mounted and opened by one or more instances (using Real Application Cluster RAC).
The system global area is a shared memory, which means it can be accessed by multiple processes. The background processes are operating system processes and each process has a specific responsibility in the instance.
The System Global Area and background processes are created when the instance is started. When the instance is shut down the processes are killed and the shared memory region is released back to operating system.
What is difference between SGA and PGA?
*SGA is called System Global Area, SGA 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 Pfile (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.