Database Administrator

1) There are three major structures in Oracle Database server architecture: memory structures, process structures, and storage structures. A basic Oracle database system consists of an Oracle database and a database instance.

2) The database consists of both physical structures and logical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting access to logical storage structures.

3) The instance consists of memory structures and background processes associated with that instance. 

4) Every time an instance is started, a shared memory area called the System Global Area (SGA) is allocated and the background processes are started. Processes are jobs that work in the memory of computers.

5) A process is defined as a “thread of control” or a mechanism in an operating system that can run a series of steps. After starting a database instance, the Oracle software associates the instance with a specific physical database. This is called mounting the database. 

6) The database is then ready to be opened, which makes it accessible to authorized users.

1) Oracle's Data Guard is a well test and supported solution but it does not come cheap but it is a solution that is practically the same across all companies and one that all DBA's should be able to grasp very quickly when starting at a new company.

2) Data Guard is basically a ship redo and then apply redo, as you know redo is the information needed to recover a database transaction. A production database referred to as a primary database transmits redo to one or more independent replicas referred to as standby databases.

3) Standby databases are in a continuous state of recovery, validating and applying redo to maintain synchronization with the primary database. A standby database will also automatically resynchronize if it becomes temporary disconnected to the primary due to power outages, network problems, etc.

4) Firstly the redo transport services transmits redo data from the primary to the standby as it is generated.

5)Secondly services apply the redo data and update the standby database files.

6) Thirdly independently of Data Guard the database writer process updates the primary database files.

7) Lastly Data Guard will automatically resynchronize the standby database following power or network outages using redo data that has been archived at the primary.

1) Dynamic Management Views are functions built into Microsoft SQL Server that allow for troubleshooting, diagnostics and server health monitoring.

2) The default port for MySQL is 3306, and can be changed in Windows.

3) The default port for Oracle is 1521, and can be changed .Depending on your security stance, changing the port that your database server uses can be a good way to lower your profile and reduce the amount of unauthorized access attempts against the server.

I was asked to mention some important points that we need to ensure while analysing the AWR( Automatic Workload Repository ) Report . I answered accordingly : 

1) To Collect Multiple AWR Reports: It’s beneficial to have two AWR Reports, one for the good time and other when performance is poor or we can also create three reports (Before/Meantime/After reports) during the time frame problem was experienced and compare it with the time frame before and after.

2) To Stick to Particular Time: A specific time is chosen when Database is slow so that we can choose a shorter timeframe to get a more precise report.

3) To Split Large AWR Report into Smaller Reports: Instead of having one report for long time like one report for 3 hrs. it is better to have three reports each for one hour. This will help to isolate the problem

4) FOR RAC, taking each instance’s individual report: For RAC environment, it is better to do it separately of all the instances in the RAC to see if all the instances are balanced the way they should be.

5) Using ASH also : Using AWR to identify the troublesome areas and then using ASH to confirm those areas reduced the pressure and is proved to be quite useful.

6) Increasing the retention period : Some instances where we get more performance issues we should increase the retention time so that we can have historical data to compare.

This discussion went for over 30 mins but the interviewer was quite satisfied with my answer.

The advantages of using a view in the table are : 
1) It is a subset of the data in table
2) It store complex queries
3) It can simplify multiple tables into one
4) It occupies very little space
5) It presents the data from different perspectives

The main four types of backups in Oracle are:
1) COLD Backup
2) HOT Backup
3) Import – Export Backup
4) RMAN Backup

Follow Up Question : Explain Hot Backup and Cold Backup.

Hot backup is also recognized as an Online Backup because here the backup is taken while the database is active and running. And when the backup can only happen while the database is in shut down mode then it is called Cold Backup which can also be identified as Offline Backup.

General Tip : Before an interview for any company , have a breif insight about the company , what it does , when was it founded and so on . All these info can be easily acquired from the Company Website itself .

Tip 1 : The cross questioning can go intense some time, think before you speak.
Tip 2 : Be open minded and answer whatever you are thinking, in these rounds I feel it is important to have opinion.
Tip 3 : Context of questions can be switched, pay attention to the details. It is okay to ask questions in these round, like what are the projects currently the company is investing, which team you are mentoring. How all is the work environment etc.
Tip 4 : Since everybody in the interview panel is from tech background, here too you can expect some technical questions. No coding in most of the cases but some discussions over the design can surely happen.