Oracle DBA Interview Questions

1. The SGA is a distributed memory that several programs can use. This section contains data that the instance needs to function.
2. System Global Area (SGA), a distributed memory space on RAM, and applications running make up an instance.
3. The running processes are OS processes, and every program in the instance has a specific role to play.
4. When an instance is "started," it creates the GAS and runs processes.
5. The processes are destroyed and the common memory space is returned to the operating system when the instance status is "shut off."

A trace file can be created for each server and backend process. When a process or user process detects an operational fault, it spills data about the bugs to its trace. This information can be used to fine-tune the database.

The items themselves can be saved in the database. The DBMS language can be used with an OOPs language. The language used in the program may even be the same, removing the need for the developer to have two versions of his objects.

At the very beginning, it validates the syntax and semantics in the cache of the library before creating a blueprint.

Data will be automatically returned to the client if it is already present in the buffer.

If the data is not present in the database, it will go to the data files and store it in the database buffer cache, then transmit it to the server, which will then deliver it to the user.

The physical files that hold the data and information that compose up a database are referred to as database files. The control files, data files, and redo log files are among them.

Whereas, The storage that is distributed and accessible by all threads and background processes is referred to as an instance.

1. Redo Log Files.
2. Parameter files.
3. Control files.
4. Data files.
5. Password files.

The Oracle developer is mostly in charge of creating backend applications. The model data in accordance with business rules. They generate tables, indexes, and other types of restrictions. They are required to be familiar with SQL and PL/SQL. These languages are used to create processes. Oracle developers, on the other hand, are not required to manage the database software.

But, an Oracle DBA's primary responsibility is to administer the database, which covers areas such as doing maintenance to keep the databases operational, taking backups, maintaining privacy standards, and so on. DBAs are not typically tasked with writing code. DBAs, like developers, are expected to have a strong understanding of SQL and PL/SQL, as they are also essential for database administration. DBAs may be allocated development responsibilities or, at the very least, support developers when needed, depending on the company's structure.

Bind variables are crucial because most database suppliers provide an execution plan cache, which stores recently processed SQL statements and planning processes for again use.

It is the process of structuring a relational database's fields and tables to reduce complexity and dependence. It guarantees that our data is consistent by saving the memory.

1. First normal form - Only single (atomic) valued attributes or columns should be used. A column's values must all belong to a certain domain.
2. Second normal form - It must be written in First Normal. It should also not be partially dependent.
3. Third normal form - It's in the shape of the Second Normal. It also lacks transitive dependencies.
4. Boyce and Codd Normal Form - Boyce and Codd is a more advanced variation of the Third Normal Form. This form is used to deal with anomalies that aren't addressed by 3NF. BCNF refers to a 3NF table that does not contain several identical candidate keys.

The following are the elements of a logical data model:

1. Entity - An entity is a type of object that we utilize to store data. It comes with its own table.
2. Attribute - This reflects the information about the entity we're looking at. It is recorded as a table column with a particular data type connected to it.
3. Record - A record is a group of all the features associated with an object for a single condition, expressed in a table as a row.
4. Domain - A domain is the collection of all possibilities for a certain property.
5. Relationship - A relationship between two entities is represented by this object.

Oracle's grid design combines a huge database server, memory, and connections to create a customized, as per requirement computing resource for business computing demands. The grid computing architecture constantly evaluates the consumption of resources and changes supply accordingly.

You might, for example, run several programs on a grid of numerous connected database servers. If monthly reports are pending, the database administrator might automatically supply a dedicated server to that program to manage the growing demands.

Grid computing employs advanced workload management, enabling programs to work cooperatively across several servers. As per requirement data processing ability may be added or deleted, and resources within a region can be provided flexibly. Web services enable the rapid integration of apps to establish a business.

• Normal: New connections are not permitted in this option, and the database is terminated when all sessions quit.
• Immediate: New connections are not permitted, and all ongoing operations are returned.
• Transactional: New connections are not permitted, and Oracle will hold until all current processes have been finished.
• Abort: This occurs instantly, although the database is not completely shut down. When the database is restarted, it will have to conduct a quick restoration. We can't utilize this in everyday situations.

• A data block is a database object's basic logical storage unit. As objects expand in size, they require extra storage in the form of continuous data blocks.
• Extents are collections of consecutive data chunks.
• When all of an object's extents are gathered together, the segment is considered.

• Striping with an additional drive for parity is what RAID 5 is all about. If we damage a disk, we may rebuild it from the parity disk.
• RAID 10 mirror pairs of drives before striping over those mirrors.
• RAID 5 was developed at a time when drives were costly. Its goal was to deliver RAID on a budget. If a disk stops working, the IO module will function VERY slowly while it is being rebuilt. Furthermore, with all of the increased weight of the rebuild, your likelihood of failure increases considerably during this period. RAID 5 is sluggish for everything except reading even when it is running properly. Given that, plus the fact that databases (particularly Oracle's redo logs) continue to suffer write activity at all times, we should avoid RAID5 in all but the odd database that has primarily read activity. Redo logs should not be placed on RAID5.
• RAID10 is just all-around awesome. If you lose a single disk in a set of ten, for example, you might lose any of the remaining eight disks and be OK. Furthermore, because you're only creating a mirror duplicate, rebuilding has no influence on productivity. Finally, RAID10 performs admirably in all sorts of databases.

Transaction recordkeeping is more significant than transaction processing in general. Assume we have repeated entries on disk and a power interruption occurs.  The transactions can then be recovered by retrieving the redo logs on the disc. This method makes redo logs persistent first, and then temporary blocks are sent to storage.

Connections that surpass the given time are noted as clipped. When the client exceeds the normal time limit, the Oracle-level operations are removed, but the OS-level processes remain.

There are various parameters that may be changed flexibly while using spfile. That is the most significant advantage of spfile over pfile. Every value that you change when the database is operating can have one of three scope values: spfile, memory, or sometimes both. S cope of the Spfile will alter after the next reboot, storage range will change instantly but rollback after the reboot, and both will alter quickly.

RMAN uses a database structure called a recovery catalog to hold information from Oracle databases. The catalog is usually kept in a separate database. A recovery catalog adds repetition to the RMAN library contained within every destination database's control file. It acts as a backup metadata store. Even if the destination control file and all duplicates are deleted, the RMAN information remains in the recovery catalog. A recovery catalog adds repetition to the RMAN library contained within every destination database's control file. It acts as a backup metadata store. Even if the destination control file and all duplicates are deleted, the RMAN information remains.

Deadlock occurs when two operations are trying to change rows of a table that are restricted by the other operation. This is frequently caused by failure to issue adequate row lock requests. This condition can be caused by a poorly designed UI application, and the server's productivity will suffer as a result. When a commit/rollback action is conducted, or either of these programs is stopped outside, these restrictions will be freed immediately.

1. It is used to get statistics on the optimizer's objects and record these in the database.
2. It is used to remove information from the database about the information used by the database.
3. It is used to ensure that the object's structure is correct.
4. It is used to get the table or cluster's imported and linked rows.

1. The database instance should be shut down.
2. Make a backup of the database.
3. Perform any actions that are particular to your operating system.
4. Start a new instance and load the database, but don't open it yet.
5. Change the archiving mode of the database.

Any specified object in a database that would be used to keep or dataset is referred to as a database object. Views, clusters, tables, sequences, indexes, and synonyms have come under database objects.

1. It's a two-part file that contains data in the database's physical form.
2. You can remove the probability of crashing with the assistance of the control file.
3. It contains information about the database name, checkpoint, duration, and the presence of multiple log files.
4. The CONTROL FILE argument, which stores information about the control file, makes it simple to locate it.
5. To avoid the problem related to a corrupted file, it is preferable to make numerous copies of the control file.

1. Grid architecture can be built by using the famous technology known as clustering. The cluster provides static resources dedicated to certain applications and owners. Grids, which can be made up of numerous clusters, are resource provisioning pools that can be used by a wide range of applications and users. A grid does not presume that all of its servers are performing the same set of apps. Applications can be planned and transferred among grid servers.
2. Grid computing is, at its most basic level, processing as a service. If we talk in simple terms, it doesn't matter where your data is stored or your request is handled by which machine. You must be able to fetch data or computations and have them provided anytime and as much as you desire. This is similar to how power systems function in that you don't know where the generator is or how the grid is wired, all you have to do is ask for power and it will be provided. The objective is to turn computers into a utility, a commodity, and something that everyone has access to. As a result, the moniker 'The Grid' was coined. This is, of course, a "client-side" approach to utility computing.
3. The grid is all about the distribution of the resources, data exchange, and reliability from the "server-side" or the backend. Resource allocation guarantees that people who require or seek resources receive them and that assets are not unused while demands go unmet. Information sharing ensures that users and applications have access to the information they require, and when they require it. High availability characteristics ensure that all data and computations are always available, similar to how a utility provider ensures that electricity is always available.

The credentials of the database users are saved in the database's data dictionary. When the user tries to request access to the database, it compares the user's account and passcode to the data contained in the database. Once The user is only allowed database access if the account and passcode match. We can access the data dictionary as long as the database is open.  This dictionary also contains the credentials for admins. You can't access the dictionary once the database is shut down. Because starting up a closed database is one of the executive's jobs, there has to be a means for admins to login into the database even when the server is down. 

A password file is a distinct OS file that is kept on a disc and is not connected to the server. It stores the login details for SYSDBA and SYSOPER clients. While the database is offline, the password file is used to verify admins with those rights.

People with straight software engineering experience are more likely to transition into this type of administrative function. Very strong knowledge is essential for this, as this type of position is not cracked by everyone. 

You must understand and have absolute trust in SQL and PL/SQL, which is standard program code accessible for Oracle databases. Moreover, the applicant must understand how the table was constructed within the system, the definition of tablespace, how to recreate and address certain extremely crucial query tuning issues, correct indexing, and how to preserve the required consistency for a whole database. 

As a database administrator license is generally granted depending on any edition, the applicant should always update himself with any latest iteration of the database released by Oracle.

Conclusion

One of the most prominent database management systems is Oracle Database. Oracle is one of the top critical databases in the industry, used by the majority of organizations. As a result, being one of the administrators in that Oracle database is always a professional achievement for that individual.

There is nothing to question that a person with Oracle certification would never be unemployed in today's market.

Interview Resources

• https://www.interviewbit.com/technical-interview-questions/
• https://www.interviewbit.com/coding-interview-questions/
• https://www.interviewbit.com/blog/

Without even a formal qualification, an Oracle database administrator can earn any income. A student with a valid Oracle credential is not guaranteed a high package compensation. There are different sorts of packages in the contemporary business, one is provided directly by Oracle, while the other is provided by IT organizations that require this type of administrative assistance for their own platform. Pay often ranges from $90000 to $180000, depending on the variety of certifications supplied by the oracle. Oracle qualifications for professionals, masters, and specialists will be developed for various types of pay in their future positions. However, a candidate should be more worried about the variety of updates and being recognized in advanced training. Based on that salary will be very attractive.

Database administration, particularly in Oracle databases, is really a major and critical component of the work. According to today's market boom, the administrative position in the database, especially in Oracle, has already climbed 11%, which is much greater than that of other accessible occupations. The professional applicant is never able to consider their profession since they are so eager to state that they can simply claim their industry worth from IT Companies.  Mostly all IT businesses place a high value on those individuals who can keep their own systems running well.

• Oracle software installation
• Oracle database creation
• Upgrading the software and database to the current version levels
• Turn off and on the database
• Handling the storage architecture of the database
• User management and protection
• Taking care of schema objects like tables, indexes, and views
• It helps to backup data and, if necessary, execute database restoration
• Analyzing the database's quality on a regular basis and taking preventative or remedial action when needed
• Performance tracking and tweaking
• You could be the only individual doing these duties in a small to medium database environment. The position is frequently shared among numerous DBAs in big business systems, each with their particular area of expertise, such as database security analyst or database performance specialist.