• phone icon +44 7459 302492 email message icon info@uplatz.com
  • Register
Job Meter = High

Oracle DBA

10 Hours
Self-paced Training (pre-recorded videos)
USD 17 (USD 140)
Save 88% Offer ends on 30-Nov-2023
Oracle DBA course and certification
460 Learners

About this Course
Oracle DBA or Database Administration is a database management system  developed by Oracle which is commonly used for data warehousing and mixed database workloads. To learn Oracle DBA, one should have a prior knowledge of Basic SQL and Linux. Some of the core responsibilities of a DBA working in an Oracle platform includes:

·         Installation of Oracle Software
·         User Management
·         Taking backups; either daily, weekly, or monthly.
·         Monitoring database instances.
·         Instance recovery
·         Creation of Data Guards

The course curriculum for the Oracle DBA Course at Uplatz includes:

Ø  Introduction to Linux and DBMS
Ø  Basic SQL
Ø  Oracle Database Architecture
Ø  Oracle Database Creation
Ø  Database Storage Management
Ø  Oracle Networking
Ø  User Administration and Security
Ø  User Managed Backup and Recovery
Ø  RMAN Backup and Recovery
Ø  Oracle RAC, etc.

Along with the following course contents, a student will be awarded a Certificate of Completion on successful completion of the course at Uplatz.

Oracle DBA

Course Details & Curriculum

ORACLE DBA – course curriculum

Introduction to Linux

·        History of Linux

·        Components of Linux

·        Linux Basic Commands

·        File permission to users

Introduction to DBMS

·         What is a database?

·         History of Oracle database

·         Role of a Database Administrator

Basic SQL

·         What are DML, DDL and DCL Statement

·         Examples on DML, DDL and DCL

·         Creation of Table, Views, Sequence, Synonyms & Index

Oracle Database Architecture

·         Detail explanation on components of an Oracle database architecture

·         Oracle database memory and process architecture

Installing Oracle database software (11g R2)

·         Pre-requisites for a successful Oracle software installation

·         Installation of oracle software using GUI and Manual Method

·         Installation of Oracle 11g R2 database software

Oracle Database Creation

·         Introduction

·         Planning for an Oracle database creation

·         Creating an Oracle database using the MANUAL method

·         Creating an Oracle database using the GUI Tool (DBCA)

·         Startup and Shutdown Methods

·         Best practices using OFA for database creation

Database Storage Management

·         Introduction

·         Tablespaces creation methods

·         Datafiles creation methods

·         How to resize/rename the tablespace/datafiles

·         How to Change the location of datafiles

·         Redolog file architecture and best practices

·         How to resize/rename the redolog file

·         Benefits of Archiving, how to enable/disable Archivelog mode for a database

·         Temporary tablespaces and temporary tablespace groups

Oracle Networking

·         Oracle listener & TNS configuration using NETMGR

·         Managing & Distributing by using DB-link concept

·         Oracle database connections from other systems

·         Oracle Shared server Dispatcher & Architecture

User Administration and Security

·         Create database users

·         Database resource management (User’s Profile and Role)

·         Locking/unlocking a user account

·         User’s password management

User Managed Backup and Recovery

·         Introduction: Backup and Recovery Strategy for user managed backup

·         How to backup an Oracle database using COLD BACKUP

·         How to backup an Oracle database using HOT BACKUP

·         Recovery scenarios using User managed backup

RMAN Backup and Recovery

·         RMAN backup of Database, controlfile,Archivelogfiles

·         Database backup using RMAN with normal and compressed mode

·         Database backup using catalog and no-catalog and virtual catalog

·         Querying the backup details using Catalog database

·         Benefits of Fast Recovery Area using RMAN backup for storage

·         How to backup incremental database

·         Recovery scenarios using RMAN

·         Tablespace Point in time recovery (TSPITR)

Oracle Datapump {Logical backup & Restore}

·         Introduction on Datapump(Expdp,Impdp) Utilities

·         Benefits of Logical backup & Physical backup

·         Exporting/Importing Tables, Schema Tablespace

·        database and Transportable tablespace

·         Backup methods using datapump

Flashback Feature

·         Types of flashback and their benefits

·         Flashback Query, Flashback Table to past time

·         Flash back of Recovering Drop tables

·         Flashback Archive

Oracle Enterprise Manager (grid Control)

·         Introduction

·         How to install and configure

·         Monitor databases and performance of the databases


·         Cloning database using RMAN from Active Database

·         Cloning database using same datafile storage locations and different datafile

·        storage locations

Logminer and SQL Loader

·         Reading/Extracting the content of Redologs (Online/Archived)

Oracle Dbase Patching/Patch Management

·         Downloading/Applying Interim & Patchset patches using Opatch

·         Applying cpu and psu patches

·        Oracle DBase Upgradation and Migration

·         Upgrating database from 11g to 12c version

Oracle Data Guard

·         Oracle Dataguard Architecture

·         Creation of physical standby database using RMAN and Manual Method

·         How to configure Active standby database and snapshot standby database

·         Data guard Modes and Switchover/Failover

·         Scenarios on restore and recovery

Oracle Automatic Storage Management (ASM)

·         Setup ASM by using Grid Infrastructure Utility

·         Database Migration from Non-ASM to ASM

Oracle Database Performance Tuning

·         Basic overview on PT and troubleshooting

·         Routine Day-to-Day Health Management tasks

·         Introduction on Proactive & reactive Tuning

·         Diagnostic tools such as Alert logfile, Trace file, TKPROF

·         AWR Report, ADDM Report and ASH Report

Oracle 12c New Features

·        Introduction, Architecture, Installation & Creation of DBase

·        Multi-Tenancy Database (PDB,CDBs)

·        SQL Statement & Table level recovery(TPITR) in RMAN

·        Creating a user, Tablesapce & In-memory Area

Oracle RAC

·         Basic Introduction & overview on RAC

Additional Support

·         Resume Preparation

·         Study Material

·         Interview Questions based on the topics

·         Mock interview

Oracle DBA

Career Path

Database Administrators
Oracle DBA Consultant

Job Prospects


Oracle DBA Interview Questions



1) How many memory layers are in the shared pool? 

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


2) How do you find out from the RMAN catalog if a particular archive log has been backed-up? 

Ans:  list archivelog all;


3) How can you tell how much space is left on a given file system and how much space each of the file system’s subdirectories take-up? 

Ans: df -kh and du-sh


4) Define the SGA and:

i)  How you would configure SGA for a mid-sized OLTP environment?

ii) What is involved in tuning the SGA? 

Ans: SGA: The System Global Area (SGA) is a group of shared memory areas that are dedicated to an Oracle “instance” (an instance is your database programs and RAM). All Oracle processes use the SGA to hold information. The SGA is used to store incoming data (the data buffers as defined by thedb_cache_size parameter), and internal control information that is needed by the database. You control the amount of memory to be allocated to the SGA by setting some of the Oracle “initialization parameters”.  These might include db_cache_size, shared_pool_size and log_buffer.

1. i) 40% of RAM can be used for sizing SGA rest is reserved for OS and others in 64 bit machine and in 32 bit machine max SGA configured can be 1.5GB only.

2. ii) Check the statspack report. Check hit ratio of Data buffer. If it is less than 90%, then we need to increase the Data buffer. Check hit ratio of Shared pool. If it is less than 95%, then we need to increase the Shared pool. Check log buffer. If redo buffer allocation retries/redo entries is greater than 1%, then we need to increase log_buffer.


5) What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it? 

Ans:  Buffer cache hit ratio: It calculates how often a requested block has been found in the buffer cache without requiring disk access. This ratio is computed using data selected from the dynamic performance view V$SYSSTAT. The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE.

sql> select name, value Fromv$sysstat Where name in (‘db block gets’, ‘consistent gets’, ‘physical reads’);

The cache-hit ratio can be calculated as follows: Hit ratio = 1 – (physical reads / (db block gets + consistent gets)) If the cache-hit ratio goes below 90% then:  increase the initialisation parameter DB_CACHE_SIZE.

Library cache hit ratio: It calculates how often the parsed representation of the statement can be reused. It also known as soft parse.

sql>  select namespace, pins, pinhits, reloads, invalidations from v$librarycache order by namespace;

Library Cache Hit Ratio = sum(pinhits) / sum(pins)

Dictionary cache hit ratio:It is a measure of the proportion of requests for information from the data dictionary, the collection of database tables and views containing reference information about the database, its structures, and its users. On instance startup, the data dictionary cache contains no data, so any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses should decrease. Eventually the database should reach a "steady state" in which the most frequently used dictionary data is in the cache.


6) Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database? 

Ans: Daily Monitoring activities and check different logs for any sort of errors.


7) How do you tell what your machine name is and what is its IP address?

Ans: hostname, uname -n and ifconfig


8) How would you go about verifying the network name that the local_listener is currently using?

Ans: lsnrctl stat or ps-eaf|greptns


9) You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance? 

Ans:  SQL>oradebugsetmypid



Also you can check the spfile. The parameters will start with instance_name. parameter_name naming.


10) What view(s) do you use to associate a user’s SQLPLUS session with his o/s process? 

Ans: v$process and v$session

sql> select a.spid from v$process a, v$session b where a.addr = b.addr and b.audsid=userenv(‘sessionid’); 


11) What is the recommended interval at which to run statspack snapshots, and why? 

Ans: Should be in minutes (15-20 mins approx)  because where the time between the two snapshots is measured in hours, the events that caused serious performance issues for 20 minutes during peak processing don’t look so bad when they’re spread out over an 8-hour window. It’s also true with STATSPACK that measuring things over too long of a period tends to level them off over time. Nothing will stand out and strike you as being wrong.


12) What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly? 



13) Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM. 

Ans: dbms_job.submit(:jobno,'statspack.snap;',trunc(sysdate)+9/24,'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);


14) How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM? 

Ans: 00 02 * * * /test/test.sh


15)In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?



16) How would you best determine why your MVIEW couldn’t FAST REFRESH? 

Ans: Possibly by checking the MVIEW LOG for errors.


17) How would you begin to troubleshoot an ORA-3113 error? 

Ans: End of File Communication Error. Check Alert Logfile. CheckNetwrok Latency. Check sqlnet.ora file has expire_time = 0, delete unwanted files and check the swap and temp spaces.


18) Which dictionary tables and/or views would you look at to diagnose a locking issue?

Ans:  v$lock, v$session, v$process


19) An automatic job running via DBMS_JOB has failed. Knowing only that “it’s failed”, how do you approach troubleshooting this issue? 

Ans:Check the log and possible reason for the JOB failed.


20) How would you extract DDL of a table without using a GUI tool? 

Ans: select dbms_metadata.get_ddl('OBJECT','OBJECT_NAME') from dual;


21) You’re getting high “busy buffer waits” - how can you find what’s causing it? 

Ans:  Buffer busy wait means that the queries are waiting for the blocks to be read into the dbcache.There could be the reason when the block may be busy in the cache and session is waiting for it. It could be undo, data block or segment header wait.

Run the following query to find out the p1,p2 and p3 of a session causing buffer busy wait

sql> select p1 "File #",p2 "Block #",p3 "Reason Code" from v$session_wait where event = 'buffer busy waits';

After that running the following query to find the  segment causing buffer busy wait:-

sql> select owner,segment_name,segment_type from dba_extents where file_id = &P1 and &P2 between block_id and block_id + blocks -1;


22) What query tells you how much space a tablespace named “test” is taking up, and how much space is remaining? 




rem column dummy noprintcolumn  pct_used format 999.9       heading "%|Used"

column  name    format a25      heading "Tablespace Name"

column  Kbytes   format 999,999,999    heading "MBytes"

column  used    format 999,999,999   heading "Used(MB)"

column  free    format 999,999,999  heading "Free(MB)"

column  largest    format 999,999,999  heading "Largest"

break   on report

compute sum of kbytes on report

compute sum of free on report

compute sum of used on report

set pagesize 100

select nvl(b.tablespace_name,

nvl(a.tablespace_name,'UNKOWN')) name,(kbytes_alloc/1024) kbytes,

((kbytes_alloc-nvl(kbytes_free,0))/1024) used,(nvl(kbytes_free,0)/1024) free,

((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100 "%used",

nvl(largest,0)/1024  largest

from ( select sum(bytes)/1024 Kbytes_free,

max(bytes)/1024 largest,              tablespace_name

from  sys.dba_free_space       group by tablespace_name ) a,

( select sum(bytes)/1024 Kbytes_alloc,              tablespace_name

from sys.dba_data_files       group by tablespace_name )b

where a.tablespace_name (+) = b.tablespace_name

order by 1



23) Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session is able to connect. 

Ans: Log into the system and find whether there are any deadlocks in the system using the following query.

select    'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking

from    v$lock l1, v$lock l2

where    l1.block =1 and l2.request > 0

and    l1.id1=l2.id1

and    l1.id2=l2.id2


If so kill the processes caught in deadlock

alter system kill session 'SID,SERIAL#' immediate;

Also find out which wait events exist in the system using following commands and go in detail as to what events are causing these waits and take appropriate actions.

select event,count(*) from v$session group by event


select u.sid,u.serial#, u.username,p.spid,to_char(u.logon_time,'DD-MON-YYYY:HH24:MI:SS')  from v$session u, v$sessionw,v$process p where u.sid = w.sid and w.event like '%&a%' and u.paddr = p.addr



24) Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle’s. What database recovery options are available? Database is in archive log mode.

Ans: First of all secure all the archives and all the backups you have on the tape or other system. Then run fschk to check the filesystem. If the corruption is detected at the filesystem level and is not recoverable by fschk format the file system and restore the database through  RMAN.


25) How do you increase the OS limitation for open files (LINUX and/or Solaris)?

Ans:  Set the file-max parameter is /etc/sysctl.conf to the number you want.Save the file and execute it by using command /etc/sysctl.conf-p


26) Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.

Ans:  export ORACLE_BASE=/oracle

export ORACLE_HOME=/oracle/ora10g

export ORACLE_SID=ora10g

export path=$ORACLE_HOME/lib

sqlplus sys as sysdba<< EOF



Now the contents of /oracle/date.sql

select SYSDATE from dual;

select to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss') from dual;

drop table tablename cascade constraints;

select to_char(SYSDATE,'dd-mon-yyyy hh24:mi:ss') from dual;


27) Explain how you would restore a database using RMAN to Point in Time?

Ans:   restore database

until time "to_date('Aug 27 2001 02:00:00','Mon DD YYYY HH24:MI:SS')";

recover database


28) How does Oracle guarantee data integrity of data changes?

Ans: Oracle exadata training Bangalore enables you to define and enforce data integrity constraints like PRIMARY KEY CONSTRAINTS, FOREIGN KEY CONSTRAINTS and UNIQUE CONSTRAINTS.


29) Which environment variables are absolutely critical in order to run the OUI?

Ans: ORACLE_BASE, ORACLE_HOME, ORACLE_SID,path and library path


30) What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?

Ans: select count(1) from v$session where USERNAME='username';


31) TABLESPACE is not specified for a user?





Answer: c


32) User SCOTT creates an index with this statement: CREATE INDEX emp_indx on employee (empno). In which tablespace would be the index created?

a. SYSTEM tablespace

b. SCOTTS default tablespace

c. Tablespace with rollback segments

d. Same tablespace as the EMPLOYEE table.

Answer: b


33) Which data dictionary view shows the available free space in a certain tablespace? 






Answer: c


34) Which method increase the size of a tablespace?

A. Add a datafile to a tablespace.

B. Use the ALTER TABLESPACE command to increase the MINEXTENTS for the tablespace.

C. Use the ALTER TABLESPACE command to increase the MAXEXTENTS for the tablespace.

D. Use the ALTER TABLESPACE command to increase the MINIMUM EXTENT for the tablespace.

Answer: a


35) What does the command ALTER DATABASE . . . RENAME DATAFILE do? (8-37) (Not proper description of the ans.)

A. It copies a data file.

B. It updates the control file.

C. It copies a data file and updates the control file.

D. It copies a data file, deletes the obsolete file, and updates the control file.

Answer: b


36) Can you drop objects from a read-only tablespace?

A. No

B. Yes

C. Only when using the DBA role

D. Only when the tablespace is online

Answer: b


37) SYSTEM TABLESPACE can be made off-line.

a) Yes

b) No

Answer: b


38) Datadictionary can span across multiple Tablespaces.



Answer: b


39) Multiple Tablespaces can share a single datafile.



Answer: b


40) All datafiles related to a Tablespace are removed when the Tablespace is dropped?



Answer: b


41) In which situation would you need to create a new control file for an existing database?

A. When all redo-log files are lost.

B. When MAXLOGMEMBERS needs to be changed.

C. When RECOVERY_PARALLELISM needs to be changed.

D. When the name of the parameter file needs to be changed

Answer: b


42) When configuring a database for ARCHIVELOG mode, you use an initialisation parameter to specify which action?

A. The size of archived log files.

B. How frequently log files will be archived.

C. That the database is in ARCHIVELOG mode.

d. To Store Archive log Files

Answer: d


43) Which command creates a text backup of the control file?





Answer: a


44) You are configuring a database for ARCHIVELOG mode. Which initialization parameter should you use?




Answer: c


45) How does a DBA specify multiple control files?

A. With the ADD CONTROLFILE command.

B. By using the files in the STARTUP command.

C. With the MULTIPLEX control file command.

D. By listing the files in the CONTROL_FILES parameter.

Answer: d


46) Which dynamic view should a DBA query to obtain information about the different sections of the control file? 





Answer: c


47) Which statements about online redo log members in a group are true?

A. All files in all groups are the same size.

B. All members in a group are the same size.

C. The rollback segment size determines the member size.

D. Differently size of transactions requires that the DBA should differently sized members.

Answer: b




Didn't find what you are looking for?  Contact Us