Oracle DBA Certification Training

Oracle DBA Interview Questions
--------------------------------------------------

1) What is oracle database?

Oracle Database is a relational database management system (RDBMS) which is used to store and retrieve the large amounts of data. Oracle Database had physical and logical structures. Logical structures and physical structures are separated from each other.

2) What is the difference between Oracle database and Oracle instance?

Oracle database is the collection of datafiles, redologs and control files while Oracle instance is the SGA ,processes in the Memory.

3) What is a Tablespace?

Oracle use Tablespace for logical data Storage. Physically, data will get stored in Datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles. A tablespace can have objects from different schema’s and a schema can have multiple tablespace. Database creates “SYSTEM tablespace” by default during database creation. It contains read only data dictionary tables which contains the information about the database.

4) What are Datafiles?

The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database.

5) What is Control Files?

Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database such as Database name and the Names and locations of datafiles and redo log files.

6) What is Redo Log Files?

The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.

7) What is Archive Log Files?

Oracle automatically archives log files when the database is in ARCHIVELOG mode. This prevents oracle from overwriting the redo log files before they have been safely archived to another location.

8) What is Parameter Files (initSID.ora)?

Parameter files contain a list of configuration parameters for that instance and database.

9) What is schema?

A user account and its associated data including tables, views, indexes, clusters, sequences,procedures, functions, triggers,packages and database links is known as Oracle schema. System, SCOTT etc are default schema’s. We can create a new Schema/User. But we can’t drop default database schemas.

10) What is data blocks?

Data Blocks are the base unit of logical database space. Each data block represents a specific number of bytes of database space on a disk.The data blocks can be 4 K,8 K size depending on the requirement.

11) What is an Extent?

Extent is a collection of Continuous data blocks, which is used for storing a specific type of information.

12) What is a Segment?

A segment is a collection of extents which is used for storing a specific data structure and resides in the same tablespace.

13) What is Rollback Segment?

Database contain one or more Rollback Segments to roll back transactions and data recovery.

14) What are the different type of Segments?

Data Segment(for storing User Data), Index Segment (for storing index), Rollback Segment and Temporary Segment.

15) What is archive-log and No archive log mode?

We all know that redo logs stored the redo information and redo log files are in circular fashion.Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The process of turning redo log files into archived redo log files is called archiving.

The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind. No archive log means archive log are not generated and redo are overwritten.

16) What all things are present in the shared pool?

The shared pool portion of the SGA contains three major areas:
library cache(contains parsed sql statements,cursor information,execution plans)
dictionary cache (contains cache -user account information,privileges information,datafile,segment and extent information)
buffers for parallel execution messages
control structure.

17) What is sqlnet.ora?

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

18) what are the different components of physical and logical database structure?

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

19) What is Database Writer (DBWR)?

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

20) What is a listener process?

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

21) What is the difference between SPFILE and PFILE?

• 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.

22) What is the purpose of collecting statistics?

Collecting statistics helps optimizer to generate best execution plan

23) What is an Oracle index?

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

24) If the table is fragmented, how would you rebuild it?

Alter table tablename move, We can analyze the table.

25) What are the different tools that are provided by Oracle to assist performance monitoring?

AWR,ADDM,Trace,TKPROF,OEM

26) What is an explain plan and how does it help in optimizing the SQL Query?

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.

27) How to analyze AWR report and ADDM report.

• 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

28) If performance issue will occur, What are the actions need to take ?

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.

29) What are the different types of backups that are available in Oracle?

• RMAN
• Datapump,Exp/IMP
• Cold backup

30) RMAN full backup was failed due to archivelogs error, What we will do?

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

31) How do you identify which sessions are locking the objects? What is the way to clear them?

By combining V$locked_object and v$session

32) Till yesterday a query is giving output in 1 min. today it is taking 5 min. how you  will troubleshoot this problem?

• 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

33) How to import table with different schema and different table and different tablespace?

remap_schema,remap_table,remap_tablespace

34) How to check production database and standby database sync?

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

35) Our standby database not in sync with production 10000 archivelogs gap will be there how to sync with production?

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

36) How to open standby database read write mode and again how to rollback, What are options we have to use it.

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

37) What are Database Components?

• 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;

38) What makes you mean by Database Normalization?

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

39) How can we identified the resources for which the sessions are waiting?

We can find out using v$session_waits and v$ system _waits.

40) Define table partitioning?

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.

41) Brief the Oracle DBA Characteristics. 

a) Structures Logical and physical 

b) Grid architecture 

c) Fully compliant 

d) Data stored as Data blocks, extents, segments, and tablespaces
 

42) Define Oracle? 

Oracle refers a company. Oracle is also known as database server, which manages data in a structured way. It enables users to store and retrieve related data in a multi-user environment so that the users can concurrently access the same data.
 

43) Define Oracle Database? 

Oracle offers software solution to create and manage the Oracle Database. The database comprises physical and logical structures in which system, user, and other information are stored. The software that deals with the database is called the Oracle Database server.
 

44) List out the roles of DBA? 

A DBA has access to create new users, remove the existing users, or change the environment variables or set permission assigned to other users. 

a) Manage database storage 

b) Administer users and security 

c) Manage schema objects 

d) Monitor database performance 

e) Perform system backup and recovery 

f) Schedule and automate jobs
 

45) Mention the different Oracle Database objects? 

a) Tables
b) Views
c) Indexes
d) Synonyms
e) Sequences
f) Tablespaces
 

46) What are the advantages of ORDBMS? 

In ORDBMS, the objects can be stored as it is. The DBMS language is integrated with an object-oriented programming language. The language  does not urge the programmer to have two representations of an object.
 

47) Define Oracle index? 

An index is referred as an optional structure related with a table to have direct access to rows that can be created to improve the performance of data retrieval. An index can be created in one or more columns of a table.
 

48) What are the common tasks performed in Oracle DBA? 

The Oracle DBA carry out the following tasks: 

a) Install Oracle software
b) Create Oracle databases
c) Perform upgrades of the database and software to new release versions
d) Starting up and turning down the database
e) Manage the database storage structures
f) Manage user permission and security
g) Handle schema objects, such as tables, indexes, and views
h) Taking database backups and perform recovery when needed
i) Monitor the database and take preventive actions
j) Monitor and tune performance

---------------------------------------------------------------------------------------------------------------