Oracle DBA Interview Questions and Answers

Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode.

A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode.

The benefit of taking a hot backup is that the database is still available

for use while the backup is occurring and you can recover the database to any point in time.

The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. 

In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

Explain the difference between a data block, an extent and a segment.

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks.

These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time.

NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time.

NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

Give the stages of instance startup to a usable state where normal users may access it.

STARTUP NOMOUNT - Instance startup

ALTER DATABASE MOUNT - The database is mounted

ALTER DATABASE OPEN - The database is opened

Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

Script to get ASM information

---------------------------------------------------------------------------------------------
spool asm_info.html
set pagesize 1000
set linesize 250
set feedback off
col bytes format 999,999,999,999
col space format 999,999,999,999
col gn format 999
col name format a20
col au format 99999999
col state format a12
col type format a12
col total_mb format 999,999,999
col free_mb format 999,999,999
col od format 999
col compatibility format a12
col dn format 999
col mount_status format a12
col header_status format a12
col mode_status format a12
col mode format a12
col failgroup format a20
col label format a12
col path format a45
col path1 format a40
col path2 format a40
col path3 format a40
col bytes_read format 999,999,999,999,999
col bytes_written format 999,999,999,999,999
col cold_bytes_read format 999,999,999,999,999
col cold_bytes_written format 999,999,999,999,999

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS' ;

select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS' ) current_time from dual;
select group_number gn, name, allocation_unit_size au, state, type, total_mb, free_mb, offline_disks od, compatibility from v$asm_diskgroup;
select group_number gn,disk_number dn, mount_status, header_status,mode_status,state, total_mb, free_mb,name, failgroup, label, path,create_date, mount_date from v$asm_disk order by group_number, disk_number;

break on g_n skip 1
break on failgroup skip 1
compute sum of t_mb f_mb on failgroup
compute count of failgroup on failgroup

select g.group_number g_n,g.disk_number d_n,g.name , g.path , g.total_mb t_mb,g.free_mb f_mb,g.failgroup from v$asm_disk g order by g_n, failgroup, d_n;

SET MARKUP HTML ON

How to change SQL prompt in Oracle?

Procedure to change SQL prompt in Oracle. 

Default SQL prompt:

Changing SQL promt to specific username

1. Go to ORACLE_HOME/sqlplus/admin.

Append below line in  glogin.sql file.

set sqlprompt '_USER>';


















2. Connect to the database. You can see the prompt as a user name in below snap.

How to find tables without PK constraint?

SELECT table_name FROM all_tables
WHERE owner = '&OWNER'
MINUS
SELECT table_name FROM all_constraints
WHERE owner = '&&OWNER' AND constraint_type = 'P';