DBA Oracle Interview Question

1. What is SGA?
The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.

2. What is a shared pool?
The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL statements among concurrent users.

3. What is mean by Program Global Area (PGA)?
It is area in memory that is used by a single Oracle user process.

4. What is a data segment?
Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

5. What are the factors causing the reparsing of SQL statements in SGA?
Due to insufficient shared pool size.
Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE.

6. What are clusters?
Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

7. What is cluster key?
The related columns of the tables in a cluster are called the cluster key.

8.Do a view contain data?
Views do not contain or store data.

9. What is user Account in Oracle database?
A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.

10. How will you enforce security using stored procedures?
Don’t grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

11. What are the dictionary tables used to monitor a database space?

12. How does one do off-line database backups?
Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database.

13. Do the following queries to get a list of all files that needs to be backed up:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;
Sometimes Oracle takes forever to shutdown with the “immediate” option. As workaround to this problem, shutdown using these commands:
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate
Note that if you database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.

14.How does one do on-line database backups?
Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes). Look at this simple example.
! cp xyfFile1 /backupDir/
It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files. Look at this example:
ALTER SYSTEM SWITCH LOGFILE; — Force log switch to update control file headers
NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.

15. How does one backup a database using RMAN?
The biggest advantage of RMAN is that it only backup used space in the database. Rman doesn’t put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database blocks until it gets a consistent image of it. Look at this simple backup example.
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
format ‘/app/oracle/db_backup/%d_t%t_s%s_p%p’
( database );
release channel t1;
Example RMAN restore:
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
# set until time ‘Aug 07 2000 :51’;
restore tablespace users;
recover tablespace users;
release channel t1;
The examples above are extremely simplistic and only useful for illustrating basic concepts. By default Oracle uses the database controlfiles to store information about backups. Normally one would rather setup a RMAN catalog database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.
Note: RMAN cannot write image copies directly to tape. One needs to use a third-party media manager that integrates with RMAN to backup directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.

16. How does one put a database into ARCHIVELOG mode?
The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups. To enable ARCHIVELOG mode, simply change your database startup command script, and bounce the database:
SQLPLUS> connect sys as sysdba
SQLPLUS> startup mount exclusive;
SQLPLUS> alter database archivelog;
SQLPLUS> archive log start;
SQLPLUS> alter database open;
NOTE1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.
NOTE2: ARCHIVELOG mode was introduced with Oracle V6, and is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backups.
NOTE3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE, log_archive_dest=… and log_archive_format=…
NOTE4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO ‘directory’; statement. This statement is often used to switch archiving between a set of directories.
NOTE5: When running Oracle Real Application Server (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode.

17. What is the basic data structure that is required for creating an LOV?
Record Group.

18. How does one backup archived log files?
One can backup archived log files using RMAN or any operating system backup utility. Remember to delete files after backing them up to prevent the archive log directory from filling up. If the archive log directory becomes full, your database will hang! Look at this simple RMAN backup script:
RMAN> run {
2> allocate channel dev1 type disk;
3> backup
4> format ‘/app/oracle/arch_backup/log_t%t_s%s_p%p’
5> (archivelog all delete input);
6> release channel dev1;
7> }

19. Does Oracle write to data files in begin/hot backup mode?
Oracle will stop updating file headers, but will continue to write data to the database files even if a tablespace is in backup mode.
In backup mode, Oracle will write out complete changed blocks to the redo log files. Normally only deltas (changes) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups.

20.What is the Maximum allowed length of Record group Column?
Record group column names cannot exceed 30 characters.

21. Which parameter can be used to set read level consistency across multiple queries?
Read only

22. What is an administrative (privileged) user?
Oracle DBAs and operators typically use administrative accounts to manage the database and database instance. An administrative account is a user that is granted SYSOPER or SYSDBA privileges. SYSDBA and SYSOPER allow access to a database instance even if it is not running. Control of these privileges is managed outside of the database via password files and special operating system groups. This password file is created with the orapwd utility.
23. How does one connect to an administrative user?
If an administrative user belongs to the “dba” group on Unix, or the “ORA_DBA” (ORA_sid_DBA) group on NT, he/she can connect like this:
connect / as sysdba
No password is required. This is equivalent to the desupported “connect internal” method.
A password is required for “non-secure” administrative access. These passwords are stored in password files. Remote connections via Net8 are classified as non-secure. Look at this example:
connect sys/password as sysdba

24. How does one create a password file?
The Oracle Password File ($ORACLE_HOME/dbs/orapw or orapwSID) stores passwords for users with administrative privileges. One needs to create a password files before remote administrators (like OEM) will be allowed to connect.
Follow this procedure to create a new password file:
. Log in as the Oracle software owner
. Runcommand: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd
. Shutdown the database (SQLPLUS> SHUTDOWN IMMEDIATE)
. Edit the INIT.ORA file and ensure REMOTE_LOGIN_PASSWORDFILE=exclusive is set.
. Startup the database (SQLPLUS> STARTUP)
NOTE: The orapwd utility presents a security risk in that it receives a password from the command line. This password is visible in the process table of many systems. Administrators needs to be aware of this!

25. How does one add users to a password file?
One can select from the SYS.V_$PWFILE_USERS view to see which users are listed in the password file. New users can be added to the password file by granting them SYSDBA or SYSOPER privileges, or by using the orapwd utility. GRANT SYSDBA TO scott;

26. Can one import/export between different versions of Oracle?
Different versions of the import utility is upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. This is quite an effective way of upgrading a database from one release of Oracle to the next.

Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility). For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.

27. Can one export to multiple files?/ Can one beat the Unix 2 Gig limit?

From Oracle8i, the export utility supports multiple output files. This feature enables large exports to be divided into files whose sizes will not exceed any operating system limits (FILESIZE= parameter). When importing from multi-file export you must provide the same filenames in the same sequence in the FILE= parameter. Look at this example:

exp SCOTT/TIGER FILE=D:\F1.dmp,E:\F2.dmp FILESIZE=10m LOG=scott.log

Use the following technique if you use an Oracle version prior to 8i:

Create a compressed export on the fly. Depending on the type of data, you probably can export up to 10 gigabytes to a single file. This example uses gzip. It offers the best compression I know of, but you can also substitute it with zip, compress or whatever.

# create a named pipe

mknod exp.pipe p

# read the pipe – output to zip file in the background

gzip scott.exp.gz &

# feed the pipe

exp userid=scott/tiger file=exp.pipe …

28. What is bind reference and how can it be created?

Bind reference are used to replace the single value in sql, pl/sql statements a bind reference can be created using a (:) before a column or a parameter name.

29. How can one improve Import/ Export performance?


. Set the BUFFER parameter to a high value (e.g. 2M)

. Set the RECORDLENGTH parameter to a high value (e.g. 64K)

. Stop unnecessary applications to free-up resources for your job.

. If you run multiple export sessions, ensure they write to different physical disks.

. DO NOT export to an NFS mounted filesystem. It will take forever.


Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.

. Place the file to be imported on a separate physical disk from the oracle data files

. Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file

. Set the LOG_BUFFER to a big value and restart oracle.

. Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)

. Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)

. Use COMMIT=N in the import parameter file if you can afford it

. Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements

. Remember to run the indexfile previously created

30. What are the common Import/ Export problems?

ORA-00001: Unique constraint (…) violated – You are importing duplicate rows. Use IGNORE=NO to skip tables that already exist (imp will give an error if the object is re-created).

ORA-01555: Snapshot too old – Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO

ORA-01562: Failed to extend rollback segment – Create bigger rollback segments or set parameter COMMIT=Y while importing

IMP-00015: Statement failed … object already exists… – Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.

31. Where is the external query executed at the client or the server?
At the server.

32. Where is a procedure return in an external pl/sql library executed at the client or at the server?

At the client.

My database was terminated while in BACKUP MODE, do I need to recover?

If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required when you try to restart the database. The DBA is then required to recover the database and apply all archived logs to the database. However, from Oracle7.2, you can simply take the individual datafiles out of backup mode and restart the database.
One can select from V$BACKUP to see which datafiles are in backup mode. This normally saves a significant amount of database down time.
Thiru Vadivelu contributed the following:
From Oracle9i onwards, the following command can be used to take all of the datafiles out of hot backup mode:
The above commands need to be issued when the database is mounted.

33. My database is down and I cannot restore. What now?

Recovery without any backup is normally not supported, however, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL (Disk UnLoad). This utility reads data in the data files and unloads it into SQL*Loader or export dump files. DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!!!

34. I’ve lost my REDOLOG files, how can I get my DB back?

The following INIT.ORA parameter may be required if your current redo logs are corrupted or blown away. Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it. _allow_resetlogs_corruption = true

35. I’ve lost some Rollback Segments, how can I get my DB back?

Re-start your database with the following INIT.ORA parameter if one of your rollback segments is corrupted. You can then drop the corrupted rollback segments and create it from scratch.
Caution is advised when enabling this parameter, as uncommitted transactions will be marked as committed. One can very well end up with lost or inconsistent data!!! Please contact Oracle Support before using it. _Corrupted_rollback_segments = (rbs01, rbs01, rbs03, rbs04)

36. What are the differences between EBU and RMAN?

Enterprise Backup Utility (EBU) is a functionally rich, high performance interface for backing up Oracle7 databases. It is sometimes referred to as OEBU for Oracle Enterprise Backup Utility. The Oracle Recovery Manager (RMAN) utility that ships with Oracle8 and above is similar to Oracle7’s EBU utility. However, there is no direct upgrade path from EBU to RMAN.

37. How does one create a RMAN recovery catalog?

Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:
sqlplus sys
SQL>create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script. rman catalog rman/rman
RMAN>create catalog tablespace tools;
RMAN> exit;
You can now continue by registering your databases in the catalog. Look at this example:
rman catalog rman/rman target backdba/backdba
RMAN> register database;

38. What is the use of FILE option in IMP command ?

The name of the file from which import should be performed.

39. What is a Shared SQL pool?

The data dictionary cache is stored in an area in SGA called the Shared SQL Pool. This will allow sharing of parsed SQL statements among concurrent users.

40. What is hot backup and how it can be taken?

Taking backup of archive log files when database is open. For this the ARCHIVELOG mode should be enabled. The following files need to be backed up. All data files. All Archive log, redo log files. All control files.

41. List the Optional Flexible Architecture (OFA) of Oracle database? or How can we organize the tablespaces in Oracle database to have maximum performance ?

SYSTEM – Data dictionary tables.
DATA – Standard operational tables.
DATA2- Static tables used for standard operations
INDEXES – Indexes for Standard operational tables.
INDEXES1 – Indexes of static tables used for standard operations.
TOOLS – Tools table.
TOOLS1 – Indexes for tools table.
RBS – Standard Operations Rollback Segments,
RBS1,RBS2 – Additional/Special Rollback segments.
TEMP – Temporary purpose tablespace
TEMP_USER – Temporary tablespace for users.
USERS – User tablespace.

42. How to implement the multiple control files for an existing database ?

Shutdown the database Copy one of the existing control file to new location Edit Config ora file by adding new control file. name Restart the database.

43. What is advantage of having disk shadowing/ Mirroring ?

Shadow set of disks save as a backup in the event of disk failure. In most Operating System if any disk failure occurs it automatically switchover to place of failed disk. Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.

44. How will you force database to use particular rollback segment ?


45. Why query fails sometimes ?

Rollback segment dynamically extent to handle larger transactions entry loads. A single transaction may wipeout all available free space in the Rollback Segment Tablespace. This prevents other user using Rollback segments.

46. How will you monitor rollback segment status ?

Querying the DBA_ROLLBACK_SEGS view
IN USE – Rollback Segment is on-line.
AVAILABLE – Rollback Segment available but not on-line.
OFF-LINE – Rollback Segment off-line
INVALID – Rollback Segment Dropped.
NEEDS RECOVERY – Contains data but need recovery or corupted.
PARTLY AVAILABLE – Contains data from an unresolved transaction involving a distributed database.

47. What is meant by Redo Log file mirroring ? How it can be achieved?

Process of having a copy of redo log files is called mirroring. This can be achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group. It degrades performance.

48. Which parameter in Storage clause will reduce no. of rows per block?

PCTFREE parameter
Row size also reduces no of rows per block.

49. What is meant by recursive hints ?

Number of times processes repeatedly query the dictionary table is called recursive hints. It is due to the data dictionary cache is too small. By increasing the SHARED_POOL_SIZE parameter we can optimize the size of Data Dictionary Cache.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s