DBA Interview Questions -II

 See my earlier post for interview question Interview Questions for Jr.DBA Oracle

1) What are the phases of application design?

a) Develop   b) Design   c) Code   d) Test   e) Launch & support

2) What is Development life cycle?

Planning --> analysis  -->  design --> Implementation -->  testing -->  evaluation -->  release --> support

3) what are the Methods of application development?

a) Waterfall Method:  is a sequential design process in which progress is seen as flowing steadily downwards (like a waterfall) through the phases of Planning à analysis à design à Implementation à testing à evaluation à release à support.

The Waterfall approach works great if the process is going to be predictable and have requirements that will not change over the course of the development.

b) Agile Method: is a software development methods based on iterative and incremental development. An Agile approach to Software development is the same, this category is more adaptable and gives developers time to work while requirements are changing. The Agile approach is illustrated by the following image; where after each phase, developers have the opportunity to cycle back and check their work.

4) What you know about Raid?

 RAID (Redundant array of Independent Disks) is a way of storing the same data in different places (redundantly) on multiple hard disks. By which I/O (input/output) operations can overlap in a balanced way, improving performance but also increases the mean time between failures (MTBF), storing data redundantly also increases.

A RAID appears to the operating system as a single logical hard disk. RAID employs the technique of disk striping, which involves partitioning each drive's storage space into units ranging from a sector (512 bytes) up to several megabytes. The stripes of all the disks are interleaved and addressed in order.

There are at least nine types of RAID plus a non-redundant array (RAID-0).

              

RAID 0

RAID 0 splits or stripes the data across all the drives in the RAID group. The benefit of RAID 0 is higher data throughput. The downside is that no redundancy makes performance good, but any disk fault or failure results in complete data loss.

You should choose RAID 0 when increased storage performance is very important, budgets are highly constrained and potential data loss isn't an issue. Some good application data examples include temporary photo editing or video editing files.

RAID 1 

RAID 1 synchronously mirrors all the data from each drive to an exact duplicate drive. No data is lost if either drive has a fault or failure. The advantage of using RAID 1 is improved multi-user read performance, because either disk can be read at the same time. The disadvantage is that the storage drive cost per usable byte of storage is twice as much because two drives are needed to store the same data.

Choose RAID 1 for an application requiring a belt-and-suspenders approach (i.e., you can't afford the possibility of application data loss or disruption), plus high-performance random reads. A good example is a read-only database in a retail brick-and-mortar store. RAID 1 is also a good choice for entry-level systems where only two drives are available, such as in a small file server.

RAID 10 (i.e., RAID 1+0 and RAID 0+1)

RAID 10 is the combination of RAID 0 and RAID 1. The advantage of using RAID 10 is the redundancy of RAID 1 with the performance of RAID 0. System performance during a drive rebuild is also noticeably higher than parity-based RAID levels (RAID 5 and RAID 6). This is because data doesn't require regeneration from parity information because it's just copied from the other mirrored drive. The disadvantage is the much higher cost --usually 60% to 80% more than parity RAID levels.

There are two variations of RAID 10. The first is RAID 0+1, which stripes the data across multiple disks, and then the striped disk sets are mirrored to an identical group of disks. The second is RAID level 1+0, which mirrors the data where the mirrors are striped.

You should choose RAID 10 for applications requiring the higher performance of RAID 0 and the unparalleled data protection of RAID 1. Online transactional databases often fit that bill.

RAID 5 

RAID 5 is designed to provide RAID 0 performances with more economical redundancy, and is the most common RAID level in the majority of organizations. It does this by striping block data across several drives and distributing parity among the drives. No single disk is devoted to parity. The benefits of using RAID 5 are overlapped read and write operations (more efficient use of disk drives) speeding up small writes in a multiprocessor system, providing greater usable storage than RAID 1 or 10 (the redundancy storage penalty is approximately 20% instead of 50%). Data protection comes from parity information that's used to reconstruct data if a drive in the RAID group has a fault or fails. The downsides include a minimum of three -- and usually five -- disks per RAID group, significantly lower performance of the storage system while a drive is being rebuilt and the potential of total RAID group data loss if a second drive fails while the first is being rebuilt. Additionally, read performance tends to be lower than other RAID types because parity data is distributed on each drive.

You should choose RAID 5 for the vast majority of applications as long as the disk drives aren't high-capacity SATA drives. SATA drives have lower duty cycles than SAS or Fibre Channel drives and have shorter MTBF rates. And because SATA drives are very high capacity (500 GB to 1000 GB), rebuild times are very long, with controller performance degraded as well. High-capacity SATA drives also increase the probability of a second disk fault or failure, resulting in complete data loss.

5)  What have you done to secure your Oracle Database?

• Drop or disable the sample schemas
• Lock expired and default user accounts.
• Don’t use same password for all admin accounts such as sys, system, dbsnmp.
• Enable data dictionary protection.
• Enable authenticate clients: Oracle recommends verifying that the database initialization parameter REMOTE_OS_AUTHENT is set to FALSE. Restrict operating system access limit the number of users with operating system access on the Oracle Database host. Oracle recommends restricting the ability to modify the default file and directory permissions for the Oracle Database home (installation) directory or its contents. Even privileged operating system users and the Oracle owner should not modify these permissions, unless instructed otherwise by Oracle.
• Restrict operating system access: Limit the number of users with operating system access on the Oracle Database host. Oracle recommends restricting the ability to modify the default file and directory permissions for the Oracle Database home (installation) directory or its contents. Even privileged operating system users and the Oracle owner should not modify these permissions, unless instructed otherwise by Oracle.
•  Implement Password authentication function, Create roles and Grant necessary privileges only, restrict the CREATE ANY JOB, EXP_FULL_DATABASE, and IMP_FULL_DATABASE privileges, Restrict library-related privileges to trusted users only, Restrict synonym-related privileges to trusted users only, Do not allow non-administrative users access to objects owned by the SYS schema,  Grant privileges only to roles.  Discourage users from using the NOLOGGING clause in SQL statements

                                                                                           

6) How to implement Application Security Policy

An application security policy is a list of application security requirements and rules that regulate user access to database objects. An each database application should have one or more database roles that provide different levels of security when executing the application. Where possible you should build applications in which application users are database users. In this way, you can leverage the intrinsic security mechanisms of the database.

For many commercial packaged applications, application users are not database users. For these applications, multiple users authenticate themselves to the application, and the application then connects to the database as a single, highly-privileged user. This is called the One Big Application User model.

Drawbacks for one Big Application user model:

• Auditing:   If One Big Application User performs all actions in the database, then database auditing cannot hold individual users accountable for their actions. The application must implement its own auditing mechanisms to capture individual user actions.
• Roles: If application users are not database users, then the usefulness of roles is diminished. Applications must then craft their own mechanisms to distinguish between the privileges which various application users need to access data within the application.

7) How to keep your Oracle Database secure?

• Always apply all relevant security patches for both the operating system on which Oracle Database resides and Oracle Database itself, and for all installed Oracle Database options and components. Periodically check the security site on Oracle Technology Network for details about security alerts released by Oracle.
•  Practice the principle of least privileges (See 5.g)
• Securing a Database Installation and Configuration  
•  Ensure that the umask value is 022 for the oracle owner account.
• By default, Oracle allows operating system-authenticated logins only over secure connections, which precludes using Oracle Net and a shared server configuration. This default restriction prevents a remote user from impersonating another operating system user over a network connection. You should not alter the default setting of the REMOTE_OS_AUTHENT initialization parameter, which is FALSE. Setting this parameter to FALSE does not mean that users cannot connect remotely. It means that the database will not trust that the client has already authenticated, and will therefore apply its standard authentication processes.
•  Auditing Sensitive Information: Be aware that sensitive data, such as credit card numbers, appear in the fine-grained audit trail if you collect SQL text. For standard auditing, setting the AUDIT_TRAIL initialization parameter to DB, EXTENDED or XML, EXTENDED enables the collection of SQL text. After you collect the required information, archive the audit records of interest and then purge the audit trail of this information.

8)  You have just moved Oracle Binary files from an ORACLE_HOME on one server to another server. What is the oracle utility command you would use to make this new ORACLE_HOME usable?

From the “$ORACLE_HOME/clone/bin” directory run the clone.pl file for the unzipped Oracle home. Use the following syntax:

$ORACLE_HOME/perl/bin/perl clone.pl ORACLE_BASE="target_oracle_base" ORACLE_HOME="target_oracle_home"   OSDBA_GROUP=OSDBA_privileged_group OSOPER_GROUP=OSOPER_privileged_group  -defaultHomeName

9) Tell me why just copying the oracle datafiles from HP-UX to LINUX RHEL and starting the database would not work?

Because for the different endian format the files are written to get the endian file format from database run the following sql:

SELECT PLATFORM_ID, PLATFORM_NAME, ENDIAN_FORMAT

FROM V$TRANSPORTABLE_PLATFORM

10) You need to shutdown the oracle database but it will not shutdown with SHUTDOWN IMMEDIATE. How will you shutdown the database?

• Force the check point    
• Switch log files   
• Force shutdown by abort   
• Startup restrict                     
• Shutdown immediate     
• Startup

11) How would you determine that SCATTERED READ where?

Look in the views “v$session_wait “   or   “v$session_wait_history”.

12) Three different ways you see that oracle is running?

• sqlplus  /as sysdba    
•  ps –ef | grep pmon  “or”  ps –ef | grep smon   
•  ipcs

13) what is ORADEBUG utility is used for?

The oradebug utility provides useful functions for debugging and tracing Oracle database errors and can also be quite handy for tracing SQL statements to output to the tkprof utility for analysis by Oracle technicians.

The most useful features of the oradebug utility are for hang and crash analysis for Oracle database environments. If there is a core dump or bug that must be resolved with the help of Oracle support, the Oracle DBA can generate the crash dump analysis and work through the crisis with confidence and less delay by dumping the memory contents of the SGA to the trace file and handing this off to the advanced Oracle internal support engineer for quick analysis and problem resolution.

 Another use for oradebug is for performance analysis and tuning. SQL statements can be traced at the kernel level with oradebug which aids in the tuning of SQL for the Oracle database environment.

Sql > oradebug SETMYPID    

Sql> oradebug SESSION_EVENT

Sql> oradebug  tracefile_name

14) What are the different ways an oracle session can be traced?

a) Alter session set sql_trace=TRUE;

b) EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);

c) EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);

d) ORADEBUG SETMYPID;                (Debug current session)

The different tracing levels the following are valid values:

• 0 - No trace. Like switching sql_trace off.
• 2 - The equivalent of regular sql_trace.
• 4 - The same as 2, but with the addition of bind variable values.
• 8 - The same as 2, but with the addition of wait events.
• 12 - The same as 2, but with both bind variable values and wait events.

15) Step to check the long running sqls?

Check whether the execution plan of various sql statements have been changed which may    happen due to Bulk inserts and updates.
Try to gather statistics on regular basis.
Check the server side resources using top, vmstat, iostat utilities.
Check the storage side df –h  
 Check the network side

16) What errors did you hit during your most recent database recovery?

17) What is a mutating table error and how can you get around it?

This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.

Better Answer: This is a bug in the code process. Review and correct the code so you no longer have to concurrently read and update a given row.

18) What is a “Snapshot too old” exception and how do you avoid, or prevent, them?

The error occurs when the redo required for a read consistent view of the data is no longer available. Avoid or prevent by increasing the number and size of redo segments so that the longest running transaction can be contained in one segment. – or – Increase the undo retention period to a value >= the longest running transaction in the database and make sure you have sufficient disk to support that much redo.

19) You are using hot backup without being in archivelog mode, can you recover in the event of a failure? Why or why not?

You can’t use hot backup without being in archivelog mode. So no, you couldn’t recover.

20) A developer is trying to create a view and the database won’t let him. He has the “DEVELOPER” role which has the “CREATE VIEW” system privilege and SELECT grants on the tables he is using, what is the problem?

You need to verify the developer has direct grants on all tables used in the view. You can’t create a stored object with grants given through roles.

21) When looking at v$sysstat you see that sorts (disk) is high. Is this bad or good? If bad -How do you correct it?

If you get excessive disk sorts this is bad. This indicates you need to tune the sort area parameters in the initialization files. The major sort parameter is the SORT_AREA_SIZE parameter.

22) Tell me how backups work, how recovery works. Walk through various scenarios and tell me what would happen. For example, we are in archivelog mode -- hot backup every Sunday. It is Wednesday and we lost the disk with the users tablespace on it. What do you do now?

A full backup and archivelog work hand-in-hand for complete recovery i.e to the last bytes of data restore. An archivelog holds the chronological information of all transactions that takes place with database. Which helps in restoring database up to any time in past.

23) What is the difference between process and procedures?

 Process is a series of actions/tasks that obtains desired business outcome and procedure are detailed step for how to undertake the process.

24) What is your relationship with application developers?

After proper design and development are completed, the developer and DBA must meet again to review the final database code and functional testing. Review of the resulting SQL and expected and tested execution path further reduces the risk of performance problems and eliminates surprises once the code is placed into production by the DBA.

Once code is placed into production, it must have the proper administration and change management. Although the DBA performs the database object maintenance, he must also communicate changes to developers so the original model and design are updated. This eliminates the risk of erasing changes made in production when new application updates or patches are deployed.

Performance tuning is a shared responsibility between developers and administrators. It's reliant upon the application design, transaction execution and orchestration across a complex infrastructure of application servers, networks, databases and servers.

There shouldn’t be any barriers to communicate with developers for a smooth workflow from database design to production and focus more on preventing poor-quality code from being deployed. People, processes and technology must integrate to support business.

25) Role of a Database administrator?

The roles of a DBA vary from organization to organization. But the following list shows the primary activities that many DBAs are charged with.

     The most important every day task for a DB admin is to make sure all instances are up and running, and foresee any potential risk to the database apart from the regular tasks such as

• Back up and recover the database.
•  Install and configure Oracle software.
• Create new databases.
•  Design the database schema and create any necessary database objects.
• Formulate optimal application SQL.
• Ensure database security is implemented to safeguard the data.
•  Apply patches or upgrades to the database as needed.
• Work closely with application developers and system administrators to ensure all        database needs are being met.

DBAs (Database Administrators) perform many different tasks on a regular basis. The following lists some of the most common tasks performed by the average DBA, from A to Z.

• Auditing
• Application Integration
• Backup and Recovery
• Business Intelligence/Data Warehousing
• Capacity Planning
• Change Management
• Database Application Development
• Data Modeling and Database Design
• Developing and Maintaining Best Practices
• High Availability
• Installing, Configuring, Patching and Upgrading SQL Server Software
• Installing and Configuring Hardware
• Load Balancing
• Maintaining Documentation
• Managing Managers
• Managing People
• Managing SQL Server-based Applications
• Maintaining Servers and Databases
• Managing Test Environments
• Monitoring
• Needs/Requirements Analysis
• Negotiating Service Level Agreements
• Performance Tuning
• Project Management
• Protector of the Data
• Replicating Data
• Report Writing
• Running Jobs
• Security
• Scripting
• SSIS/ETL
• Testing
• Training Users
• Troubleshooting
• Vendor Relations
• Working with Teammates