Interview Questions and Answers of Oracle DBA

There are some important question and answer for Oracle DBA.

1. What are the components of SGA?

SGA is used to store shared information across all database users. It mainly includes Library cache, Data Dictionary cache, Database Buffer Cache, Redo log Buffer cache, Shared Pool.

2. Difference between SGA and PGA.

SGA (System Global Area) is a memory area allocated during an instance start up. SGA is allocated as 40% of RAM size by default. SGA size is controlled by DB_CACHE_SIZE parameter defined in initialization parameter file (init.ora file or SPFILE).

 PGA (Program or Process Global Area) is a memory area that stores a user session specific information. PGA is allocated as 10% of RAM size by default.

3. What is System Change Number (SCN)?

 SCN is a unique ID that Oracle generates for every committed transaction.  It is recorded for every change in the redo entry.

4. What is Database Writer (DBWR) and when does DBWR write to the data file?

-DBWR is a background process that writes data blocks information from Database buffer cache to data files. There are 4 important situations when DBWR writes to data file.

 Every 3 seconds

Whenever checkpoint occurs.

When server process needs free space in database buffer cache to read new blocks.

Whenever number of changed blocks reaches a maximum value.

5. What is Log Writer and when does LGWR writes to log file?

 LGWR writes redo or changed information from redo log buffer cache to redo log files in database.

 It is responsible for moving redo buffer information to online redo log files, when you commit and a log switch also occurs.

 LGWR writes to redo files when the redo log buffer is 1/3 rd full.

 It also writes for every 3 seconds.

 Before DBWR writes modified blocks to the datafiles, LGWR writes to the log file

6. Which Table spaces are created automatically when you create a database?

SYSTEM tablespace is created automatically during database creation.

 It will be always online when the database is open.

Other Tablespaces include

·         SYSAUX tablespace

·         UNDO tablespace

·         TEMP tablespace

·         UNDO & TEMP tablespace are optional when you create a database.

6. What is a control file?

Control file is a binary file which records the physical structure of a database. It includes number of log files and their respective location, Database name and timestamp when database is created, checkpoint information.  We find CONTROL_FILE parameter in initialization parameter file which stores information about control file location.  We can multiplex control files, store in different locations to make control files available even if one is corrupted.

8. How do you resize a data file and tablespace?

you can resize a datafile by using ALTER DATABASE DATAFILE <file_name> RESIZE;

- Resizing Table space includes creation of new data file or resizing existing data file.

- ALTER TABLESPACE <tablespacename> ADD DATAFILE ‘<datafile name> ‘ size M; creates a new datafile.

9. What is archive log file?

In archive log mode, the database will makes archive of all redo log files that are filled, called as archived redo logs or archive log files.  By default your database runs in NO ARCHIVE LOG mode, so we can’t perform online backup’s (HOT backup).

10. What do you mean by database backup and which files must be backed up?

- Database stores most crucial data of business, so it’s important to keep the data safe and this can be achieved by backup.  Database files, Control files, Archived log files,  Parameter files (spfile and pfile) and Password file must be backup.

11. What are the different types of indexes available in Oracle?

Oracle provides several Indexing schemas

B-tree index - Retrieves a small amount of information from a large table.

Global and Local index - Relates to partitioned tables and indexes.

Reverse Key Index - It Is most useful for oracle real application clusters applications.

Domain Index - Refers to an application

Hash cluster Index - Refers to the index that is defined specifically for a hash cluster.

12. What do db_file_sequential_read and db_file_scattered_read events define?

- Db_file_sequential_read event generally indicates index usage.

- It shows an access by row id.

- While the db_file-scattered_read event indicates full table scan.

- Db_file_sequential_read event reads a single block at one time.

- Whereas db_file_scattered_read event reads multiple blocks.

13. Explain the architecture of data guard?

Data guard architecture includes the following components

Primary database - Refers to the production database.

Standby Database - Refers to a copy of primary or production database.It may have more than one standby database.

Log transport service - Manages transfer of archive log files primary to standby database.

Network configuration - Refers to the network connection between primary and standby database.

Applies archived logs to the standby database.

Role management services - Manages the role change between primary and standby database.

Data guard broker - Manages data guard creation process and monitors the dataguard.

14. What is role transition and when does it happen?

- Database operates in one of the following mutually exclusive roles

1. Primary

2. Standby

- Role transition is the change of role between primary and standby databases.

- Data guard enables you to change this roles dynamically by issuing the sql statements.

- Transition happens in between primary and standby databases in the following way

- Switchover, where primary database is switched to standby database and standby to primary database.

- Failover, where a standby database can be used as a disaster recovery solution in case of a failure in primary database.

- DRM allows you to create resource plans, which specify resource allocation to various consumer groups.

- DRM offers an easy-to-use and flexible system by defining distinct independent components.

- Enables you to limit the length of time a user session can stay idle and automatically terminates long-running SQL statement and users sessions.

- Sets the initial login priorities for various consumer groups.

- DRM will automatically queue all the subsequent requests until the currently running sessions complete.

15. WHAT IS RAC ?

Real Application Clusters (RAC) introduced in oracle 9i, is a technology that enables a single database to work on multiple instances simultaneously on different nodes.

A RAC database requires three components:

•             cluster nodes

•             Shared storage

•             Oracle Clusterware

To identify RAC instance in a database the following can be used:

•             Use the DBMS_UTILITY.IS_CLUSTER_DATABASE function

•             Show parameter CLUSTER_DATABASE

16. WHAT IS FAILOVER ?

1)  If a node in the shared disk cluster fails, the system dynamically redistributes the workload among the surviving cluster nodes.

2)  RAC checks to detect node and network failures. A disk-based heartbeat mechanism uses the control file to monitor node membership and the cluster interconnect is regularly checked to determine correct operation.

3)  Enhanced failover reliability in 10g with the use of Virtual IP addresses (VIPs)

4)  If one node or instance fails, node detecting failure does the following.

•  Read redo log of failed instance from last checkpoint

•  Apply redo to datafiles including undo segments (roll forward)

•  Rollback uncommitted transactions

•  Cluster is frozen during part of this process

FAST APPLICATION NOTIFICATION (FAN)

1)  FAN is a method introduced in Oracle 10.1, by which applicationscan be informed of changes in cluster status for –

•  Fast node failure detection

•  Workload balancing

2)  Advantageous by preventing applications from –

•  Waiting for TCP/IP timeouts when a node fails

•  Trying to connect to currently down database service

•  Processing data received from failed node

3)  Can be notified using –

•  Server side callouts

•  Fast Connection Failover (FCF)

•  ONS API

ORACLE NOTIFICATION SERVICE (ONS)

1)  ONS, introduced in Oracle 10.1 is a subscribe serviceused by Oracle Clusterware to propagate messages to :

•  Nodes in cluster

•  Middle-tier application servers

•  Clients

2)  ONS is automatically launched for RAC on each node as part of the Oracle Clusterware installation process. However, it can also be configured to run on nodes hosting client or mid-tier applications.

3)  It is an underlying mechanism for Fast Application Notification (FAN).

4)  ONSruns independent of Transparent Application Failover (TAF).

TRANSPARENT APPLICATION FAILOVER (TAF)

1)  TAF is a client-side feature that allows for clients to reconnect to surviving databases in the event of a failure of a database instance.

2)  Masks failures to end users; they don’t need to log back into the system

3)  Applications and users are transparently reconnected to another node

4)  Applications and queries continue uninterrupted

5)  Transactions can failover and replay

6)  Login context maintained

7)  DML transactions are rolled back

8)  Requires configuration in TNSNAMES.ORA

 17. What is the use of a service in Oracle RAC environemnt?

Applications should use the services feature to connect to the Oracle database.Services enable us to define rules and characteristics to control how users and applications connect to database instances.

18. Which enable the load balancing of applications in RAC?

Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database.

19. What is the use of VIP?

If a node fails, then the node’s VIP address fails over to another node on which the VIP address can accept TCP connections but it cannot accept Oracle connections.

20. What is the significance of VIP address failover?

When a VIP address failover happens, Clients that attempt to connect to the VIP address receive a rapid connection refused error.They don’t have to wait for TCP connection timeout messages.

21. What are the administrative tools used for Oracle RAC environments?

Oracle RAC cluster can be administered as a single image using OEM (Enterprise Manager), SQL*PLUS, Servercontrol(SRVCTL), clusterverificationutility(cvu), DBCA, NETCA.

22. How do we verify that RAC instances are running?

Issue the following query from any one node connecting through SQL*PLUS.

$connect sys/sys as sysdba

SQL>select * from V$ACTIVE_INSTANCES;

The query gives the instance number under INST_NUMBER column, host_: instancename under  INST_NAME column.

23. What is FAN?

Fast application Notification as it abbreviates to FAN relates to the events related to instances, services and nodes. This is a notification mechanism that Oracle RAC uses to notify other processes about the configuration and service level information that includes service status changes such as, UP or DOWN events. Applications can respond to FAN events and take immediate action.

24. Why should we have seperate homes for ASm instance?

It is a good practice to have ASM home seperate from the database home(ORACLE_HOME).This helps in upgrading and patching ASM and the Oracle database software independent of each other.Also,we can deinstall the Oracle database software independent of the ASM instance.

25. Can rolling upgrade be used to upgrade from 10g to 11g database?

No, it can be used only for Oracle database 11g releases(from 11.1).

26. What two parameters must be set at the time of starting up an ASM instance in a RAC  environment?

The parameters CLUSTER_DATABASE and INSTANCE_TYPE must be set.

27. Mention the components of Oracle clusterware.

Oracle clusterware is made up of components like voting disk and Oracle Cluster Registry(OCR).

28. What is a CRS resource?

Oracle clusterware is used to manage high-availability operations in a cluster. Anything that Oracle Clusterware manages is known as a CRS resource.Some examples of CRS resources are database,an instance,a service,a listener,a VIP address,an application process etc.

29. What is the use of OCR?

Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).

30. Name some Oracle clusterware tools and their uses?

OIFCFG – allocating and deallocating network interfaces

OCRCONFIG – Command-line tool for managing Oracle Cluster Registry

OCRDUMP – Identify the interconnect being used

CVU – Cluster verification utility to get status of CRS resources

31. How do we verify an existing current backup of OCR?

We can verify the current backup of OCR using the following command :

Ocrconfig –showbackup

32. What are the types of connection load-balancing?

There are two types of connection load-balancing:server-side load balancing and clientside load balancing.

33. How can you find out how many users are currently logged into the database? How can you find their operating system id?

To look at the v$session or v$process views and check the current_logins parameter in the v$sysstat view. If you are on UNIX is to do a ps -ef|grep oracle|wc -l? Command, but this only works against a single instance installation.

34. What is tkprof and how is it used?

The tkprof tool is a tuning tool used to determine CPU and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.

What is Explain plan and how is it used?

The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.

35. Where can you get a list of all initialization parameters for your instance? How about an indication if they are default settings or have been changed?

You can look in the init.ora file for an indication of manually set parameters. For all parameters, their value and whether or not the current value is the default value, look in the v$parameter view.

36. Discuss row chaining, how does it happen? How can you reduce it? How do you correct it?

Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the old value and would not fit in the remaining block space. This results in the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import of the effected table.

37. You are getting busy buffer waits. Is this bad? How can you find what is causing it?

Buffer busy waits could indicate contention in redo, rollback or data blocks. You need to check the v$waitstat view to see what areas are causing the problem. The value of the "count" column tells where the problem is, the "class" column tells you with what. UNDO is rollback segments, DATA is data base buffers.

38. How can you tell if a tablespace has excessive fragmentation?

If a select against the dba_free_space table shows that the count of tablespaces extents is greater than the count of its data files, then it is fragmented.

39. You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem?

A large number of wraps indicates that your extent size for your rollback segments are probably too small. Increase the size of your extents to reduce the number of wraps. You can look at the average transaction size in the same view to get the information on transaction size.

40. How many redo logs should you have and how should they be configured for maximum recoverability?

You should have at least 3 groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided.

41. Can we take incremental Backup with out taking complete Backup?

No, First full backup is needed

42. What kind of backup you take Physical / Logical? Which one is better and Why?

Logical backup means backing up the individual database objects such as tables, views , indexes using the utility called EXPORT, provided by Oracle. The objects exported in this way can be imported into either same database or into any other database. The backed-up copy of information is stored in a dumpfile, and this file can be read only using another utility called IMPORT. There is no other way you can use this file. In this backup Oracle Export utility stores data in Binary file at OS level.

Physical backups rely on the Operating System to make a copy of the physical files like data files, log files, control files that comprise the database. In this backup physically CRD (datafile, controlfile, redolog file) files are copied from one location to another (disk or tape)

We don't preferred logical backup. It is very slow and recoveries are almost not possible.

43. What is Partial Backup?

A   Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

A partial backup is an operating system backup of part of a database. The backup of an individual table space’s data files or the backup of a control file are examples of partial backups. Partial backups are useful only when the database is in ARCHIVELOG ...

44. How to check the size of SGA?

SQL> show SGA

Total System Global Area 167772160 bytes

Fixed Size 1247900 bytes

Variable Size 58721636 bytes

Database Buffers 104857600 bytes

Redo Buffers 2945024 bytes

45. How to define data block size

The primary block size is defined by the Initialization parameter DB_BLOCK_SIZE.

46 .How can we determine the size of the log files.

SQL>Select sum(bytes)/(1024*1024) “size_in_mb” from v$log;

47. What is the frequency of log Updated..?

Whenever commit, checkpoint or redolog buffer is 1/3rd full, Time out occurs (3 sec.), 1 MB of redo log buffer

48. In your database some blocks of particular datafile are corrupted. What statement will you issue to know how many blocks are corrupted?

You can check the " Select * from V$DATABASE_BLOCK_CORRUPTION; " view to determine the corrupted blocks.

49. How can the problem be resolved if a SYSDBA, forgets his password for logging into enterprise manager?

There are two ways to do that:

1. Login as SYSTEM and change the SYS password by using ALTER USER.

2. Recreate the password file using orapwd and set remote_password_file exclusive and then restart the instance.

3. Also you can enter as ‘/ as sysdba’ and then after change the password “Alter user sys identified by xxx”;

50. What is Data Pumping?

Data Pumping is a data movement utility. This is a replacement to imp/exp utilities. The earlier imp/exp utilities are also data movement utilities, but they work within the local servers only. Where as, impdp/expdp (Data pumping) are very fast and perform data movements from one database to another database on same as well as different host. In other words, it provides secure transports.

51. How to know which query is taking long time?

By testing with the help of these tools tkprof or using explain plan. tkprof is available to DBA Only where as explain plan can run programmer as well as DBA also. As well as tkprof generates complexilty after sucessful execution only where as explain plan can show Oracle internal plan & other details. Even though they are not alternatives for one to another. But both are designed for one purpose only. They are two different tools they are engaged in different useful situations also you can use STATSPACK to take Snaps while running those queries and get the report with details of SQL taking more time to respond otherwise, you can search Top ten sql with the following views:

SQL>SELECT * FROM V$SQL;                    

SQL>SELECT * FROM V$SQLAREA;

SQL>SELECT * FROM (SELECT rownum Substr(a.sql_text 1 200) sql_text Trunc(a.disk_reads/Decode(a.executions 0 1 a.executions)) reads_per_execution a.buffer_gets a.disk_reads a.executions a.sorts a.address FROM v$sqlarea a ORDER BY 3 DESC)WHERE rownum < 10;

How can you check which user has which Role.

Sql>Select * from DBA_ROLE_PRIVS order by grantee;

52. What would you use to improve performance on an insert statement that places millions of rows into that table?

Drop the indexes and recreate after insert.

DML Triggers to be DISABLED and then ENABLED once the insert completed.

DISABLE the Clustered Index and then ENABLED once the insert completed.

53. If Monday take full backup and Tuesday it was cumulative backup and Wednesday we taken incremental backup, Thursday some disaster happen then what type of recovery and how it will take?

Restore the Monday full backup + Tuesday cumulative backup + Wednesday Incremental backup. Becausecumulative and incremental clears the archives every backup

54. How do you delete duplicate rows in a table?

DELETE FROM emp A WHERE ROWID >

(SELECT min(rowid) FROM emp B

WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID);

-----------------OR----------------------

delete from emp t1

where  exists (select 'x' from emp t2

where t2.EMPLOYEE_ID = t1.EMPLOYEE_ID

and t2.EMPLOYEE_ID = t1.EMPLOYEE_ID

and t2.rowid      > t1.rowid);

-----------------OR----------------------

delete from emp where rowid not in

( select min(rowid)

from emp group by EMPLOYEE_ID);

55. What is an Index Organized Table?

An index-organized table (IOT) is a type of table that stores data in a B*Tree index structure. Normal relational tables, called heap-organized tables, store rows in any order (unsorted).

CREATE TABLE my_iot (id INTEGER PRIMARY KEY, value VARCHAR2 (50)) ORGANIZATION INDEX;

56. What are a Global Index and Local Index?

When you create a partitioned table, you should create an index on the table. The index may be partitioned according to the same range values that were used to partition the table. Local keyword in the index partition tells oracle to create a separate index for each partition of the table. TheGlobal clause in create index command allows you to create a non-partitioned index or to specify ranges for the index values that are different from the ranges for the table partitions. Local indexes may be easier to manage than global indexes however, global indexes may perform uniqueness checks faster than local (portioned) indexes perform them.