Oracle DBA Interview Questions and Answers

bookmark

Categories

Oracle DBA


Oracle DBA Interview Questions and Answers Online Training

Enroll Today

Top Oracle DBA Interview Questions and Answers - 2021 [UPDATED]

Are you looking for Oracle DBA Interview Questions and Answers? Than you are at the Right Place. Browse through Popular and Most Asked Interview Questions for Oracle DBA.  There is a Huge Demand for Oracle DBA Professionals in the Market. These Questions are suitable for both Freshers and Experienced Professionals and are based on Trending Topics and as per Current Industry Requirements.

Master These Topics and Increase your Chances in cracking Oracle DBA Interviews like a pro and Secure your Dream Job Today.

Oracle DBA Interview Questions and Answers

  • 55 Questions

./opatch lsinventory 100 GB of database size while applying opatch it will take 30 mins, so 500 GB of database size how much time it will take to apply opatch. Same 30 Mins, We will apply the opatch for oracle binary only not for data.

It is used to security of our database, Privileged IP address only able to access the database.

physical: Datafile,Controlfiles, redologfiles. Logical: Database buffer cache, Redo log buffer cache,Shared pool,large pool

It is responsible to writing data from database buffer cache into datafiles.

It is network connectivity, Application and sql developer tool to access database. Lietener.ora and Tnsnames.ora files are used.

Spfile: Server parameter file of binary file, It contains all the information for setting the database initialization parameters. SPFILE permits dynamic changes without requiring you to restart that instance. Pfile: It is text parameter file, information for setting the database initialization parameters.

It will help optimizer to generate best execution plan

It is an database object used to increase the performance for retrieval data.

Alter table tablename move, We can analyze the table.

We will check if query is using proper indexes are not. If not we will either create new indexes or can use hints to specify which indexes to use.

AWR: AWR report mainly we have to analyze query elapsed time and executions. ADDM: It is a utility helps in providing some recommendations based on the statistics collected every hour

We need to take AWR report and check query elapsed time and how many executions, Query using proper index or not, or elase we have to analyze table and indexes.Check alertlog any issue occurred

We need to crosscheck the archivelogs. crosscheck archivelog all; delete expired archivelog all;

We will start looking at the network issues Check if the query is changed from yesterday to today Check table statistics are upto date Generate explain plan and see where is the problem

select max(sequence#) from V$archived_log; select max(sequence#) from V$log_history;

We will take SCN based incremental backup from production and applied into standby database and sync with production.

Using flashback and restore point option we can open and rollback the standby database.

set line 200; set pagesize 9999; col COMP_ID format a15; col COMP_NAME format a50; select COMP_ID,COMP_NAME,STATUS from dba_registry;

Normalization technique is a set of rules that exist used to build this relational database to prevent data redundancy also dependency. Once original database objects remain classified, normalization benefits in identifying specific relationships between schema objects. First Normal Form Second Normal Form Third Normal Form Boyce-Codd Normal Form Fourth Normal Form Fifth Normal Form

We can find out using v$session_waits and v$ system _waits. Slove : Hope the above set would have provided you a good knowledge of any of the advanced ideas of Oracle. Just go through those questions before attending the interview also success will be yours.

It is a process of sharing a table within smaller chunks so as to obtain the data retrieval simple and quick. Each piece order is known as a partition also can occur accessed separately.

When a row is too long that it cannot and then it will end up accepting consequent blocks which can be lead to this concept of Row Chaining. It can signify avoided by refreshing this storage parameter over an appropriate value.

Due to various insert & delete actions, that file gets fragmented & unstructured data, through creating that relationship slowly. To get data inside these indexes, rebuilding is completed.

TKPROF – (Transient kernel profiler) STATSPACK – (Stands for Statistics Package) ADDM – (Automated Database Diagnostics Monitor) AWR – (Automatic Workload Repository) OEM – (Oracle Enterprise Manager)

Database Design. Memory Allocation. Disk I/Os. Database Contention. OS level (CPU).

DBA_FREE_SPACE DBA_SEGMENTS DBA_DATA_FILES

Recovery Catalog is a means by database schema that holds by the metadata managed by RMAN for restoration data and recovery data processes.

DBWR is a background process that writes changed data blocks of defense amounts over this data file. This changed information blocks move called dirty blocks.

Data files Redo Logs Control files Password files and Parameter files

PGA is a memory distance used by Oracle database. It is expected to save session specific information.

When checkpoint occurs When number from infected blocks provides a threshold Every three moments due to timeout When the server method demands free space in buffer cache to read new blocks.

SCN is an ID that Oracle makes to every transaction. It is recorded including those corresponding variety into a redo approach.

The Oracle Certified Professional (OCA/OCP) Database Administrator (DBA) accreditation is intended to reflect capability in Oracle database engineering, establishment, organization, execution tuning, and investigating.

An Oracle database is an accumulation of information treated as a unit. The motivation behind a database is to store and recover related data. … Oracle Database is the primary database intended for big business framework figuring, the most adaptable and practical approach to oversee data and applications.

You can start up a database with three devices. SQL*Plus: This is the most generally utilized choice. You initially associate with an inactive case with SQL*Plus and after that startup the case with "startup" direction. Prophet Enterprise Manager: This is another method for beginning up a database. You can sign in to Oracle Enterprise Manager regardless of whether the database is halted. OEM will recognize the status of the down database and will introduce you " Startup" catch. You can start up the database by clicking this catch. RMAN: This is somewhat a less utilized instrument for beginning up a database yet it is conceivable to fire up a database from the Recovery Manager direction line.

Prophet utilizes Tablespace for consistent information Storage. Physically, the information will get put away in Data documents. Information records will be associated with the tablespace. Table space can have numerous information documents. A tablespace can have objects from various outline's and construction can have numerous tablespace's. The database makes " Framework tablespace" as a matter of course amid database creation. It contains read-just information word reference tables which contain data about the database.

A checkpoint plays out the accompanying three tasks: Every filthy square in the cushion reserve is kept in touch with the information records. That is, it synchronizes the information hinders in the support reserve with the datafiles on circle. It's the DBWR that composes all changed database hinders back to the datafiles.

An occurrence is comprised of a mutual memory locale on RAM called System Global Area (SGA) and foundation forms. The framework worldwide region is a common memory, which implies it very well may be gotten to by various procedures. This holds information which is required by the case to work. The foundation forms are working framework forms and each procedure has a particular obligation in the case. The System Global Area and foundation forms are made when the case is "began" When the occasion is " shut down" the procedures are murdered and the common memory the district is "discharged" back to the working framework.

Prophet home stock or neighborhood stock is available inside every Oracle home. It just contains data significant to a specific Oracle home. This document is situated in the accompanying area: $ORACLE_HOME/stock It contains the accompanying records and organizers: Components File Home Properties File Other Folders

A database object is any characterized item in a database that is utilized to store or reference information. A few instances of database objects incorporate tables, sees, bunches, arrangements, files, and equivalent words. The table is this present hour's center since it is the essential and most straightforward type of information stockpiling in a social database.

The undertakings of Oracle DBA as pursues: Installing Oracle programming Creating Oracle databases Performing updates of the database and programming to new discharge levels Starting up and closing down the database Managing the database's stockpiling structures Managing clients and security Managing diagram objects, for example, tables, files, and perspectives Making database reinforcements and performing recuperation when fundamental Proactively checking the database's wellbeing and making the preventive or remedial move as required Monitoring and tuning execution a little to a fair size database condition, you may be the sole individual playing out these undertakings. In enormous venture conditions, the activity is regularly isolated among a few DBAs, each with their territory of claim to fame, for example, the database security director or database tuning master.

The items accordingly can be put away in the database. The language of the DBMS can be coordinated with an item arranged programming language. The language may even be equivalent to that utilized in the application, which does not constrain the software engineer to have two portrayals of his items.

These are the physical parts which get put away in the circle. Data documents Redo Log documents Control documents Password documents Parameter documents

An information square is the littlest unit of intelligent stockpiling for a database object. As items develop they take pieces of extra stockpiling that are made out of touching information squares. These groupings of coterminous information squares are called degrees. Every one of the degrees that an article takes when gathered are viewed as the portion of the database object.

When you are running committed server at that point procedure data put away inside the procedure worldwide region (PGA) and when you are utilizing shared server then the procedure data put away inside the client worldwide region (UGA).

Two kinds of capacity frameworks are accessible: Relational Database Management System (RDBMS) and Hierarchical Storage Management System (HSM) Most databases utilize the RDBMS model, Oracle likewise utilizes the RDBMS model. Hierarchical Storage Management System (HSM) Information Management System (IMS) from IBM. Integrated Database Management System (IDMS) from CA.

Set up the circle and ensure you have Installation document (run Installer) in your dump. Check the swap and TEMP space. Fare the accompanying condition factors ORACLE_BASE ORACLE_HOME Way LD_LIBRARY_PATH TNS_ADMIN Set up the part parameters and document most extreme descriptors. Source the Environment record to the separate slam profile and now run Oracle Universal Installer.

Ordinary: In this mode, no new associations are permitted and the database is shut after every one of the sessions detaches themselves. Quick: No new associations are permitted and the current dynamic exchanges are moved back. Changes made by a functioning exchange are lost in this alternative. Value-based: No new associations are permitted and Oracle holds up until every single dynamic exchange are finished. Prematurely end: This happens quickly, notwithstanding, the database isn't closed down neatly. The database should perform case recuperation next time it is begun. This choice ought not be utilized in standard exercises.

LGWR is the background method that writes redo data from redo log buffers before the log files.

Oracle database is used to store and retrieve information. It contains physical and logical structures. Physical structures are controlled file, datafile and redo log file. The logical structure is a database, tablespace, segment, extent, and blocks.

All the tables are stored in data files. The data file has all the data stored.