Interview Tips for DBA Professionals

Sakshi Education

By,

Devi Vara Prasad Pirla, MCA, M.Tech. (CSE), (PhD)
Oracle Certified Specialist & OBIEE Trainer.

A Database Administrator (DBA) is the expert who is responsible for installation, configuration, upgradation, administration, monitoring and maintenance of databases in an organization. The role includes the development and design of database strategies, system monitoring and improving database performance and capacity, and planning for future expansion requirements. They may also plan, co-ordinate and implement security measures to safeguard the database.

List of skills required to become DBA are -

Communication skills
Knowledge of Database Theory
Knowledge of Database Design
Knowledge about the RDBMS itself, e.g. Oracle Database, IBM DB2, Microsoft SQL Server, Adaptive Server Enterprise, MaxDB, PostgreSQL
Knowledge of Structured Query Language (SQL)
General understanding of distributed computing architectures, e.g. Client/Server, Internet/Intranet and Enterprise
General understanding of the underlying operating system, e.g. Windows, Unix, Linux
General understanding of storage technologies, memory management, disk arrays, NAS/SAN, networking

Education
Employers prefer a bachelor's degree or higher qualifications in Computer Science and Engineering or related field to perform job duties as a database administrator.

Certification
The organisations prefer the database administrators who have a certification for the particular RDBMS that is being used in their organisation such as Oracle Database 11g Administrator Certified Professional and MCITP Database Administrator 2008.

Responsibilities of DBA
A database administrator's responsibilities would include the following tasks:

Installing and upgrading the database server and application tools
Allocating system storage and planning future storage requirements for the database system
Modifying the database structure, as necessary, from information given by application developers verbally
Enrolling users and maintaining system security
Ensuring compliance with database vendor license agreement
Controlling and monitoring user access to the database
Monitoring and optimizing the performance of the database
Planning for backup and recovery of database information
Maintaining archived data
Backing up and restoring databases
Contacting database vendor for technical support
Generating various reports by querying from database as per need

Job Titles DBAs are also known by the titles Database Coordinator or Database Programmer. However, a Database Programmer requires advanced skills in SQL programming than a DBA, however, he may not have the skills such database administration, backing up, restoring, monitoring or tuning, which are essential skills for Database Coordinators. The DBA role is closely related to the other jobs such as Database Analyst, Database Modeller, Programmer Analyst and Systems Manager.

Some organisations have a hierarchical level of database administrators, generally:

Data Analysts/Query Designers
Junior DBAs
Midlevel DBAs
Senior DBAs
DBA consultants
Manager/Director of Database Administration/Information Technology

General Interview Questions for DBA Professionals:
Here are the possible questions that would be asked in DBA interviews. The questions are divided into five levels, based on their difficulty.

Basic-level FAQs

(Every DBA should correctly answer the questions of this level. These are just basic level questions for those who have a 0-3 years experience as DBA.)

1.1 Which are the default passwords of SYSTEM/SYS?
Ans:MANAGER / CHANGE_ON_INSTALL

1.2 How can you execute a script file in SQLPLUS?
Ans: To execute a script file in SQLPlus, type @ and then the file name.

1.3 Where can you find official Oracle documentation?
Ans: tahiti.oracle.com

1.4 What is the address of the Official Oracle Support?
Ans:metalink.oracle.com or support.oracle.com

1.5 What file will you use to establish Oracle connections from a remote client?
Ans:tnsnames.ora

1.6 How can you check if the database is accepting connections?
Ans:lsnrctl status or lsnrctl services

1.7 Which log would you check if a database has a problem?
Ans: Alert log

1.8 Name three clients to connect with Oracle, for example, SQL Developer:
Ans:SQL Developer, SQL-Plus, TOAD, dbvisualizer, PL/SQL Developer… There are several, but an experienced dba should know at least three clients.

1.9 How can you check the structure of a table from sqlplus?
Ans:DESCRIBE or DESC

1.10 What command will you start to run the installation of Oracle software on Linux?
Ans: runInstaller
Moderate-level FAQs

(This is the standard knowledge that every DBA shall for his daily work. You could fail one or two questions, but not more.)

2.1 What should you do if you encounter an ORA-600?
Ans: Contact Oracle Support

2.2 Explain the differences between PFILE and SPFILE.
Ans:A PFILE is a Static, text file that initializes the database parameter in the moment that it’s started. If you want to modify parameters in PFILE, you have to restart the database.

A SPFILE is a dynamic, binary file that allows you to overwrite parameters while the database is already started (with some exceptions)

2.3 In which Oracle version was Data Pump introduced?
Ans: Oracle 10g

2.4 Give two examples of DML, two of DCL and two of DDL.
Ans:DML: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
DDL: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
DCL: GRANT, REVOKE

2.5 You want to save the output of an Oracle script from sqlplus. How would you do it?
Ans:spool script_name.txt
select * from your_oracle_operations;
spool off;

2.6 What is the most important requirement in order to use RMAN to make consistent hot backups?
Ans:Your database has to be in ARCHIVELOG mode.

2.7 Can you connect to a local database without a listener?
Ans: Yes, you can.

2.8 In which view you can find information about every view and table of oracle dictionary?
Ans: DICT or DICTIONARY

2.9 How can you view all the users account in the database?
Ans: SELECT USERNAME FROM DBA_USERS;

2.10 In Linux, how can we change which databases are started during a reboot?
Ans:Edit /etc/oratab
Advanced-level FAQs

(A 3+ year experienced DBA should have enough knowledge to answer these questions. However, depending on the work you have done, you could still fail up to 4 questions.)

3.1 When a user process fails, what Oracle background process will clean after it?
Ans:PMON

3.2 How can you reduce the space of TEMP datafile?
Ans:Prior to Oracle 11g, you had to recreate the datafile. In Oracle 11g, a new feature was introduced and you can shrink the TEMP tablespace.

3.3 How can you view all the current users connected in your database in this moment?
Ans: SELECT COUNT(*),USERNAME FROM V$SESSION GROUP BY USERNAME;

3.4 Explain the differences between SHUTDOWN, SHUTDOWN NORMAL, SHUTDOWN IMMEDIATE AND SHUTDOWN ABORT.
Ans:SHUTOWN NORMAL = SHUTDOWN: It waits for all sessions to end, without allowing new connections.
SHUTDOWN IMMEDIATE: Rollback current transactions and terminates every session.
SHUTDOWN ABORT: Aborts all the sessions, leaving the database in an inconsistent state. It is the fastest method, but can lead to database corruption.

3.5 Is it possible to backup your database without the use of an RMAN database to store the catalog?
Ans: Yes, but the catalog would be stored in the control file.

3.6 Which are the main components of Oracle Grid Control?
Ans: OMR (Oracle Management Repository), OMS (Oracle Management Server) and OMA (Oracle Management Agent).

3.7 What command will you use to navigate through ASM files?
Ans: asmcmd

3.8 What is the difference between a view and a materialized view?
Ans:A view is a select that is executed each time an user accesses to it. A materialized view stores the result of this query in memory for faster access purposes.

3.9 Which one is faster: DELETE or TRUNCATE?
Ans:TRUNCATE.

3.10 Are passwords in oracle case sensitive?
Ans:Only since Oracle 11g.
RAC FAQs

(Only intended for RAC-specific DBAs, difficulty levels of these questions vary.)

4.1 What is the recommended method to make backups of a RAC environment?
Ans:RMAN to make backups of the database, dd to backup your voting disk and hard copies of the OCR file.

4.2 What command would you use to check the availability of the RAC system?
Ans:crs_stat -t -v (-t -v are optional)

4.3 What is the minimum number of instances you need to have in order to create a RAC?
Ans: 1. You can create a RAC with just one server.

4.4 Name two specific RAC background processes.
Ans: RAC processes are: LMON, LMDx, LMSn, LKCx and DIAG.

4.5 Can you have many database versions in the same RAC?
Ans: Yes, but Clusterware version must be greater than the greater database version.

4.6 What was RAC previous name before it was called RAC?
Ans: OPS: Oracle Parallel Server

4.7 What RAC component is used for communication between instances?
Ans:Private Interconnect.

4.8 What is the difference between normal views and RAC views?
Ans:RAC views has the prefix ‘G’. For example, GV$SESSION instead of V$SESSION.

4.9 Which command will we use to manage (stop, start…) RAC services in command-line mode?
Ans:srvctl

4.10 How many alert logs exist in a RAC environment?
Ans:One for each instance.
Master-level FAQs

(A 3+ year experienced DBA would also probably fail these questions; they are very specific and especially difficult. Be glad if you are able to answer some of them.)

5.1 How can you differentiate a usual parameter from an undocumented parameter?
Ans:Undocumented parameters have the prefix ‘_’. For example, _allow_resetlogs_corruption

5.2 What is BBED?
Ans:An undocumented Oracle tool used for forensic purposes.

5.3 The result of the logical comparison (NULL = NULL) will be… And in the case of (NULL != NULL)
Ans:False in both cases.

5.4 Explain Oracle memory structure?
Ans:The Oracle RDBMS creates and uses storage on the computer hard disk and in random access memory (RAM). The portion in the computer s RAM is called memory structure. Oracle has two memory structures in the computer s RAM. The two structures are the Program Global Area (PGA) and the System Global Area (SGA).
The PGA contains data and control information for a single user process. The SGA is the memory segment that stores data that the user has retrieved from the database or data that the user wants to place into the database.

5.5 Will RMAN take backups of read-only tablespaces?
Ans: No.

5.6 Will a user be able to modify a table with SELECT only privilege?
Ans:He won’t be able to UPDATE/INSERT into that table, but for some reason, he will still be able to lock a certain table.

5.7 What Oracle tool will you use to transform data files into text files?
Ans:Trick question: you can’t do that, at least with any Oracle tool. A very experienced DBA should perfectly know this.

5.8 SQL> SELECT * FROM MY_SCHEMA.MY_TABLE;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
Why am I getting this error?
Ans:The table has a BLOB column.

5.9 What parameter will you use to force the starting of your database with a corrupted resetlog?
Ans: _ALLOW_RESETLOGS_CORRUPTION.

5.10 Name the seven types of Oracle tables.
Ans:Heap Organized Tables, Index Organized Tables, Index Clustered Tables, Hash Clustered Tables, Nested Tables, Global Temporary Tables, Object Tables.