Oracle Interview Questions

Oracle DBA Interview Questions/FAQs Part1


1. What is an instance?
SGA + background processes.

2. What is SGA?
System/Shared Global Area.

3. What is PGA (or) what is pga_aggregate_target?
Programmable Global Area.

4. What are new memory parameters in Oracle 10g?
SGA_TARGET
PGA_TARGET

5. What are new memory parameters in Oracle 11g?
MEMORY_TARGET

6. What are the mandatory background processes?
DBWR LGWR SMON PMON CKPT RECO.

7. What are the optional background processes?
ARCH, MMAN, MMNL, MMON, CTWR, ASMB, RBAL, ARBx etc.

8. What are the new background processes in Oracle 10g?
MMAN MMON MMNL CTWR ASMB RBAL ARBx


10. What are the new features in Oracle 10g?

11. What are the new features in Oracle 11g?

12. What are the new features in Oracle 11g R2?

13. What are the new features in Oracle 12c?


14. What process will get data from datafiles to DB cache?
Server process

15. What background process will writes data to datafiles?
DBWR

16. What background process will write undo data?
DBWR

17. What are physical components of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files. Password file and parameter file also come under physical components.

18. What are logical components of Oracle database?
Blocks, Extents, Segments, Tablespaces.

19. What is segment space management?
LMTS and DMTS.

20. What is extent management?
Auto and Manual.

21. What are the differences between LMTS and DMTS?
Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.
21. What is a datafile?
Every Oracle database has one or more physical datafiles. 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.

22. What are the contents of control file?
Database name, SCN, LSN, datafile locations, redolog locations, archive mode, DB Creation Time, RMAN Backup & Recovery Details, Flashback mode.

23. What is the use of redo log files?

24. What are the uses of undo tablespace or redo segments?

25. How undo tablespace can guarantee retain of required undo data?
Alter tablespace undo_ts retention guarantee;

26. What is ORA-01555 - snapshot too old error and how do you avoid it?

27. What is the use/size of temporary tablespace?

28. What is the use of password file?

29. How to create password file?
$ orapwd file=orapwSID password=sys_password force=y nosysdba=y

30. How many types of indexes are there?
Clustered and Non-Clustered

1.B-Tree index
2.Bitmap index
3.Unique index
4.Function based index

Implicit index and explicit index.
Explicit indexes are again of many types like simple index, unique index, Bitmap index, Functional index, Organisational index, cluster index.

31. What is bitmap index & when it’ll be used?
Bitmap indexes are preferred in Data warehousing environment.
Preferred when cardinality is low.

32. What is B-tree index & when it’ll be used?
B-tree indexes are preferred in OLTP environment.
Preferred when cardinality is high.

33. How you will find out fragmentation of index?
AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented indexes/Tables.

analyze index validate structure;
This populates the table ‘index_stats’. It should be noted that this table contains only one row and therefore only one index can be analysed at a time.

An index should be considered for rebuilding under any of the following conditions:
* The percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3.
* If the ‘HEIGHT’ is greater than 4.
* If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large number of deletes, indicating that the index should be rebuilt.

34. What is the difference between delete and truncate?
Truncate will release the space. Delete won’t.
Delete can be used to delete some records. Truncate can’t.
Delete can be rollbacked.
Delete will generate undo (Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence data removed by Delete command can be rolled back but not the data removed by TRUNCATE).
Truncate is a DDL statement whereas DELETE is a DML statement.
Truncate is faster than delete.

35. What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where unique key creates a nonclustered index by default. Primary key doesn't allow NULLs, but unique key allows one NULL only.

36. What is the difference between schema and user?
Schema is collection of user’s objects.

37. What is the difference between SYSDBA, SYSOPER and SYSASM?
SYSOPER can’t create and drop database.
SYSOPER can’t do incomplete recovery.
SYSOPER can’t change character set.
SYSOPER can’t CREATE DISKGROUP, ADD/DROP/RESIZE DISK

SYSASM can do anything SYSDBA can do.

38. What is the difference between SYS and SYSTEM?
SYSTEM can’t shutdown the database.
SYSTEM can’t create another SYSTEM, but SYS can create another SYS or SYSTEM.

39. How to improve sqlldr (SQL*Loader) performance?

40. What is the difference between view and materialized view?
View is logical, will store only the query, and will always gets latest data.
Mview is physical, will store the data, and may not get latest data.


41. What are materialized view refresh types and which is default?
Complete, fast, force(default)

42. How fast refresh happens?

43. How to find out when was a materialized view refreshed?
Query dba_mviews or dba_mview_analysis or dba_mview_refresh_times
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mviews;
(or)
SQL> select NAME, to_char(LAST_REFRESH,'YYYY-MM-DD HH24:MI:SS') from dba_mview_refresh_times;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,'YYYY-MM-DD HH24:MI:SS') from dba_mview_analysis;

44. What is materialized view log (type)?

45. What is atomic refresh in mviews?
From Oracle 10g, complete refresh of single materialized view can do delete instead of truncate. To force the refresh to do truncate instead of delete, parameter ATOMIC_REFRESH must be set to false.

ATOMIC_REFRESH = FALSE, mview will be truncated and whole data will be inserted. The refresh will go faster, and no undo will be generated.
ATOMIC_REFRESH = TRUE (default), mview will be deleted and whole data will be inserted. Undo will be generated. We will have access at all times even while it is being refreshed.

SQL> EXEC DBMS_MVIEW.REFRESH('mv_emp', 'C', atomic_refresh=FALSE);

46. How to find out whether database/tablespace/datafile is in backup mode or not?
Query V$BACKUP view.

47. What is row chaining?
If the row is too large to fit into an empty data block in this case the oracle stores the data for the row in a chain of one or more data blocks. Can occur when the row is inserted.

48. What is row migration?
An update statement increases the amount of data in a row so that the row no longer fits in its data blocks. Now the oracle tries to find another free block with enough space to hold the entire row if such a block is available oracle moves entire row to new block.

49. What are different types of partitions?
With Oracle8, Range partitioning (on single column) was introduced.
With Oracle8i, Hash and Composite(Range-Hash) partitioning was introduced.
With Oracle9i, List partitioning and Composite(Range-List) partitioning was introduced.
With Oracle 11g, Interval partitioning, REFerence partitioning, Virtual column based partitioning, System partitioning and Composite partitioning [Range-Range, List-List, List-Range, List-Hash, Interval-Range, Interval-List, Interval-Interval] was introduced.

50. What is local partitioned index and global partitioned index?
A local index is an index on a partitioned table which is partitioned in the exact same manner as the underlying partitioned table. Each partition of a local index corresponds to one and only one partition of the underlying table.
A global partitioned index is an index on a partitioned or non partitioned tables which are partitioned using a different partitioning key from the table and can have different number of partitions. Global partitioned indexes can only be partitioned using range partitioning.

51. How you will recover if you lost one/all control file(s)?

52. Why more archivelogs are generated, when database is begin backup mode?
During begin backup mode datafile headers get freezed and as result row information cannot be retrieved as a result the entire block is copied to redo logs as a result more redo generated and more log switch and in turn more archive logs. Normally only deltas (change vectors) are logged to the redo logs. When in backup mode, Oracle will write complete changed blocks to the redo log files.


Mainly to overcome fractured blocks. Most of the cases Oracle block size is equal to or a multiple of the operating system block size.

e.g. Consider Oracle blocksize is 2k and OSBlocksize is 4k. so each OS Block is comprised of 2 Oracle Blocks. Now you are doing an update when your db is in backup mode. An Oracle Block is updating and at the same time backup is happening on the OS block which is having this particular DB block. Backup will not be consistent since the one part of the block is being updated and at the same time it is copied to the backup location. In this case we will have a fractured block, so as to avoid this Oracle will copy the whole OS block to redo logfile which can be used for recovery. Because of this redo generation is more.


53. What UNIX parameters you will set while Oracle installation?
shmmax, shmmni, shmall, sem,

54. What is the use of inittrans and maxtrans in table definition?

55. What are differences between dbms_job and dbms_schedular?
Through dbms_schedular we can schedule OS level jobs also.

56. What are differences between dbms_schedular and cron jobs?
Through dbms_schedular we can schedule database jobs, through cron we can’t set.

57. Difference between CPU & PSU patches?
CPU - Critical Patch Update - includes only Security related patches.
PSU - Patch Set Update - includes CPU + other patches deemed important enough to be released prior to a minor (or major) version release.

58. What you will do if (local) inventory corrupted [or] opatch lsinventory is giving error?

59. What are the entries/location of oraInst.loc?
/etc/oraInst.loc is pointer to central/local Oracle Inventory.

60. What is the difference between central/global inventory and local inventory?

61. How you will connect to database when the database is in hung state (Not even sysdba could not connect)
A. Use sqlplus "/ as sysdba" -prelim
     oradebug set myospid
     oradebug hanganalyze 3
62. What happens when undo datafile is lost when database is running?
      Instance Crashes.. need to restore and recover undo datafile from backup. Current going transactions are lost.
63. What happens when  temporary datafile is lost.
64. Explain PITR when a whole tablespace is lost.
65. How you'll find whether a table and Index are fragmented?
66. What is SCAN IP and SCAN Listener? What is remote listener?
67. What is the shutdown process of 11g RAC?

68. What is the use of root.sh & oraInstRoot.sh?
Ans:
Changes ownership & permissions of oraInventory
Creating oratab file in the /etc directory
In RAC, starts the clusterware stack

69. What is transportable tablespace (and across platforms)?

70. How can you transport tablespaces across platforms with different endian formats?
Ans:
RMAN

71. What is xtss (cross platform transportable tablespace)?

72. What is the difference between restore point & guaranteed restore point?

73. What is the difference between 10g/11g OEM Grid control and 12c Cloud control?

74. What are the components of Grid control?
Ans:
OMS (Oracle Management Server)
OMR (Oracle Management Repository)
OEM Agent

75. What are the new features of 12c Cloud control?


76. How to find if your Oracle database is 32 bit or 64 bit?
Ans:
execute the command "file $ORACLE_HOME/bin/oracle", you should see output like /u01/db/bin/oracle: ELF 64-bit MSB executable SPARCV9 Version 1

means you are on 64 bit oracle.

If your oracle is 32 bit you should see output like below
oracle: ELF 32-bit MSB executable SPARC Version 1


77. How to find opatch Version ?
Ans:
opatch is utility to apply database patch, In order to find opatch version execute"$ORACLE_HOME/OPatch/opatch version"

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

1)

What is the difference between pfile and spfile. Where these files are located.

what will you do if pfile and spfile file is deleted.

Can you start the database?

pfile or init.ora is a text file, hence setting any oracle init parameters in this file requires restarting database.

With spfile we can dynamically set certain oracle init parameters without restarting the instance.

Example: alter system set DB_CACHE_SIZE=2G scope=both; both means memory and spfile both.

location of pfile/spfile is $ORACLE_HOME/dbs

if init.ora/spfile is lost, we can manually create a pfile using any other database pfile. Edit the pfile as per the db_name, control_files etc.

And then start the database. Later on we can create spfile from pfile.

2)

What is the difference between Static and Dynamic init.ora/spfile parameters.

Changing Oracle Static parameters requires instance restart to make them effective.

Dynamic parameters are immediately effective in running Oracle Instance and does not require restart.

What is the complete syntax to set DB_CACHE_SIZE in memory and spfile.

alter system set DB_CACHE_SIZE=2G scope=both;

3) How do we configure multiple Buffer Cache in Oracle. Whats the benefit? Does setting multiple Cache requires Database Restart?

We can set multiple Buffer Cache by setting DB_NK_CACHE_SIZE dynamic parameter in pfile or spfile. NK can be (2K, 4K,8K,16,32K)

if db_block_size=8K then DB_8K_CACHE_SIZE is not allowed.

OLTP database has small transactions so they need small block size(2k,4k,8k) and hence 2k, 4k, 8k Cache Size.

Datawarehouse database work on big transaction that effect big tables hence we need bigger block size(8k,16k,32k)

If a database is mix having both OLPT and Datawarehouse needs we need to configure Multiple Block size and Also create Tablespace of different block size usingBLOCKSIZE syntax.

Multiple Buffer Cach parameters are Dynamic database restart is not needed.

4) What is Oracle Golden Gate

It a software used for Replicating data from one database to another. The source and target can be Microsoft Sql Server, Oracle , IBM DB2, Sybase, MYSQL running on any OS.

5) Can we create Tablespaces of multiple Block Sizes. If yes, what is the Syntax

YES it is possible. We need to set Buffer Caches of corresponding block size, and create the tablespace with BLOCKSIZE syntax.

For example if we need Tablespace of 32K size we will use following steps:

alter system set db_32k_cache_size=2G scope=both;

create tablespace hr_data datafile ‘/u01/app/oracle/oradata/hrprd/hr_data01.dbf’ size 1G

BLOCKSIZE 32K;

6) How do you calculate the size of oracle memory areas Buffer Cache, Log Buffer, Shared Pool, PGA etc.

We allocate70- 80% of Unix Server RAM to Oracle and then allocate

60-70% to Buffer Cache

20-30% to PGA and remaining to Shared Pool and Log Buffer

7) What is OMF? What spfile parameters are used to configure OMF. What is the benefit

OMF is oracle managed files and it is used to simplify the syntax for Datafile, Logfile, Tablespace and controlfile creation.

init.ora/spfile parameters to configure OMF are:

db_create_file_dest

db_create_online_log_dest_n (n=1 to 5)

8. ) What is Database Cloning? Why Cloning is needed? What are the steps to clone a databse?

Cloning is used to create dev and test database from production on a different machine. Refer to blog for complete steps.

9) What is Oracle Streams

Oracle Streams is used to Replicate/Transfer Data from one Oracle Database to another Oracle Database.

10) There are 2 control files for a database. What will happen when 1 control file is deleted and you try to start database.

How you will fix this problem.

If one Control file is missing out of 2, Oracle will complain when we start database. To fix this we need to modify CONTROL_FILES init.ora/spfile parameter and remove the entry for deleted control file. We can also copy control01.ctl to control02.ctl and then start the database and it will fix the error.

11) Tell me something about RAC

Using RAC technology we can run multiple instances of Oracle on multiple servers. RAC provides load balancing, fail over and scalability.

12) What is Dynamic performance view and What is Data Dictionary Views. Give some examples of each

During Database operation, Oracle maintains a set of virtual tables/view that record current database activity.

These are called dynamic performance views because they are continuously updated while a database is open and in use.

These are also called V$ views. GV$ views used in RAC are same a V$ views bu

Dynamic performance view ( v$datafile, v$controlfile, v$sql, v$transaction )

Data Dictionary Views ( dba_users, dba_tablespaces, dba_sys_privs )

13) You are working in database that does lot of Sorting , i.e SELECT queries use a lot of ORDER BY and GROUP BY.

What Oracle memory area and Physical File/Tablespace you need to tune and How.

We need bigger PGA and TEMP tablespace space to support excessive Sorting.

14) Why we upgrade a database. What are the steps to upgrade database. Any errors you got during upgrade.

Every few years an Oracle Database Version gets desupported by Oracle so we need to upgrade to newer Oracle version. Currently Oracle 9i is not supported by oracle. Also we need to upgrade to newer versions to use the new features/tools provided by newer Oracle version like 11gr1/11gr2.

We should use utlu112i.sql , utlu112s.sql and DBUA/catupgrd.sql to upgrade a database to 11gr2.

15) What is MEMORY_TARGET not supported error. How do you fix it.

This error occurs when linux shared memory or swap space is defined less. Increase its size to fix the error.

Refer to blog for further details.

16) What are the steps to manually create a database?

Create init.ora/spfile

startup nomount

Run Create Database command to manually create database.

Refer to blog for exact steps.

17) A DBA ran a delete statement to delete all records on a table. The table has 50 Million rows.

While Delete is running his SQLPLUS session terminate abnormally.

What oracle will do internally.

Answer: When the session terminates PMON Process will rollback this transation.

Next question- Which query/view you will use to monitor the Rollback/Undo that Oracle is doing

Answer: V$TRANSACTION columns used_ublk and used_urec

18) What is Oracle Dataguard

Dataguard is use to configure a Standby Database at a Remote location. Dataguard provides database protection in case of natural disaster(Earthquake, Flood) when complete Datacenter is lost and database is damaged. Business will using Standby database present at remote location.

19) Can we change the DB_BLOCK_SIZE? if Yes. What are the steps.

we can not change the db_block_size using any oracle commands. If it is required here are steps

export data from old database using Datapump(expdp) into a .dmp file.

Create a new databsae with db_block_size to any of the values (2k,4k,8k,16k,32K) as per your requirement.

Import data into new database using the using the .dmp file

20) Explain the Oracle Architecture

Oracle consists of Instance and Physical Database.

Instance has SGA, PGA and Background Process.

Physical Database consists of Datafiles, Control files, Log files and Archive log files

21) What happens internally in Oracle when a User Connects and run a SELECT Query.

What SGA areas and background processes are involved?

22) How do you create a tablespace, undo tablespace and temp tablespace. What are the Syntax

Tablespace -> create tablespace …

Undo Tablespace -> create undo tablespace..

Temp Tablespace -> create temporary tablesapce…

23) As a HR user you logged in and Creating a EMP_BIG Table and inserting 10 lac rows.

While inserting 10 lac rows you got error ORA-01688: unable to extend table EMP_BIG by 512 in tablespace HR_DATA

What are the two ways to fix this Tablespace error??

Answer:

1)Resize the existing tablespace datafile to add more space

2)Add new datafile to tablespace to add more space

24) What are the steps to rename a databse?

Shutdown Immediate.

Startup mount

Then use the NID command to rename a database.

Refer to blog for exact steps.

25) What is the syntax to Create a user and roles?

create user username identified by pass1 default tablespace hr_data temporary tablespace temp;

create role hr_read_role;

26) What are the 3 init.ora parameter to manage UNDO. What are their usage.

UNDO_TABLESPACE

UNOD_MANAGEMENT=AUTO/MANAUAL

UNDO_RETENTION

27) what is Snapshot too old error. How do you fix it

Snapshot too old error occurs when a long running queries tries to read data that from Undo Tablespace which is already overwritten by some new Transactions.

To fix this error, We need to create proper size Undo Tablespace. Query the v$UNDOSTAT for undo tablespace size recommendation.

Also we can set RETENTION GURANTEE for a tablespace, But it is not recommended.

28) What is Undo Retention Gurantee. How do we set it. What is the Proc and Cons of setting it

When Retention Gurantee is set for Undo Tablespace, committed transactions are not overwritten for UNDO_RETENTION period.

If we set this the new Transactions will fail if there is less space in Undo Tablespace and hence its very Risky and not recommended.

29) What are System Privileges and Object Privileges. Give some examples.

What Data Dictionary view we use to check both.

System privileges are generic database privileges e.g CREATE TABLE, CREATE VIEW, CREATE SESSION

To see System privileges query : SELECT * FROM DBA_SYS_PRIVS

Object privileges are on specific database object/table e.g SELECT ON EMPLOYEE, DELETE ON EMPLOYEE

To see Object privileges query : SELECT * FROM DBA_TAB_PRIVS

30) What is PGA?. What information is stored in PGA?. What is PGA Tuning?

PGA is process global are used to store sorting data, bind variable etc. PGA tuning is setting the proper size of PGA_AGGREGATE_TARGET init.ora/spfile parameter for better performance.

31) What are the steps to identify a slow running SQL and tune it.

a) Monitor sessions to find slow running sql.

b) Generate Explain Plan/SQL plan to find the root cause of slowness.

c) Tune the sqls by Creating indexes or Using SQL Hints or by Rewriting a Bad sql

32) What are all the preparation work a DBA need to do before installing Oracle?

Set linux kernel parameters.

Install Oracle recommended Linux packages.

For all steps Refer to Oracle Installation blog.

Any error that you got during Oracle Installation and how did you fix it?

Example of Oracle errors/warnings are: Kernel parameters not set, Linux packages missing, In sufficient Memory for Oracle.

33)  What is default tablespace and temporary tablespace

Default Tablespace  : Place where a user creates objects if the user does not specify some other tablespace. Note that having a default tablespace does not imply that the user has the privilege of creating objects in that tablespace, nor does the user have a quota of space in that tablespace in which to create objects. Both of these are granted separately.

Temporary tablespace: This is a place where temporary objects, such as sorts and temporary tables, are created on behalf of the user by the instance. No quota is applied to temporary tablespaces.

34)  What do you know about privileges?

A privilege is a right to execute a particular type of SQL statement or to access another user’s object

Privileges are divided into two categories:

System privileges: Each system privilege allows a user to perform a particular database operation or class of database operations. For example, the privilege to create tablespaces is a system privilege.

Object privileges: Object privileges allow a user to perform a particular action on a specific object, such as a table, view, sequence, procedure, function, or package. Without specific permission, users can access only their own objects.

35)  Which privelege allows you to select from tables owned by other users?

The SELECT ANY TABLE privilege allows to select from tables owned by other users

36)  What command we use to revoke system privelege?

revoke select table from username
37)   How do we create a Role?
A role is a named group of related privileges that are granted to users or to other roles. A DBA manages privileges through roles.
To create a role:

CREATE ROLE role_name;

OR
1.    In Enterprise Manager Database Control, click the Server tab and then click Roles under the Security heading.
2.    Click the Create button

38)  Difference between Non-Deffered and deffered constraint?

Nondeferred constraints, also known as immediate constraints, are enforced at the end of every DML statement. A constraint violation causes the statement to be rolled back. If a constraint causes an action such as delete cascade, the action is taken as part of the statement that caused it. A constraint that is defined as nondeferrable cannot be changed to a deferrable constraint. For nondeferrable constraints, the primary key and unique key constraints need unique indexes; if the column or columns already have a non-unique index, constraint creation fails because those indexes cannot be used for a unique or primary key.

Deferred constraints are constraints that are checked only when a transaction is committed. If constraint violations are detected at commit time, the entire transaction is rolled back. These constraints are most useful when both the parent and child rows in a foreign key relationship are entered at the same time, as in the case of an order entry system in which the order and the items in the order are entered at the same time. For deferrable constraints, primary key and unique keys need non-unique indexes; if the column or columns already have a unique index on them, constraint creation fails because those indexes cannot be deferred.