Oracle dba interview questions and answers

What is main purpose of CHECKPOINT?
Answer:
A Checkpoint is a database event, which synchronizes the data blocks in memory with the datafiles on disk.
A checkpoint has two purposes:
    1. to establish data consistency
    2. Enable faster database recovery

The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.
LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hour
LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.


How do you backup and restore using Transportable Tablespaces
Answer:
   
1. Run DBMS_TTS against the tablespace to see if tablespace is self contained or not.
2. Make tablespace Read Only.
3. Export Metadata (EXP TRANSPORT_TABLESPACES=Y TABLESPACE=ts1)
4. Copy data file to target host
5. Copy export dump to target
6. Import the Metadata (IMP TRANSPORT_TABLESPACES=Y DATAFILES (file1,file2) )
7. Bring Tablespace ONLINE and enable SOURCE tablespace to READ WRITE

What does RESETLOGS option do?

1. Creates a new incarnation of the database, putting a new SCN in all data file headers.

2. Reset Log Sequence number to 1

3. Reformats ONLINE REDO LOGFILES if they exists

What are the options available to refresh snapshots?

Answer:

COMPLETE - Tables are completely regenerated using the snapshots query and the master tables

every time the snapshot referenced.

FAST - If simple snapshot used then a snapshot log can be used to send the changes to the snapshot

tables.

FORCE - Default value. If possible it performs a FAST refresh; otherwise it will perform a complete

refresh.

What is snapshot log?

Answer:

It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the

same database as master table and is only available for simple snapshots. It should be created before

creating snapshots.

Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

Answer:

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is

where the oracle products reside

Explain Different values supported by CURSOR_SHARING parameter and its

explanation

Answer:

Below values are supported by CURSOR_SHARING init.ora parameter:

FORCE - Literals will be replaced by system generated bind variables where possible

SIMILAR - Oracle determines which literals are "safe" for substitution with bind variables. This will result

in some SQL not being shared in an attempt to provide a more efficient execution plan.

EXACT - Only allows statements with identical text to share the same cursor

Background processes:

Started when an Oracle Instance is started.

Background Processes Maintains and enforces relationships between physical and memory structures

There are two types of database processes:

1. Mandatory background processes

2. Optional background processes

Mandatory background processes:

– DBWn, PMON, CKPT, LGWR, SMON

Optional background processes:

– ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn

DBWn writes when:

• Checkpoint occurs

• Dirty buffers reach threshold

• There are no free buffers

• Timeout occurs

• RAC ping request is made

• Tablespace OFFLINE

• Tablespace READ ONLY

• Table DROP or TRUNCATE

• Tablespace BEGIN BACKUP

Log Writer (LGWR) writes:

• At commit

• When 1/3rd full

• When there is 1 MB of redo

• Every 3 seconds

• Before DBWn writes

System Monitor (SMON) Responsibilities:

• Instance recovery

– Rolls forward changes in redo logs

– Opens database for user access

– Rolls back uncommitted transactions

• Coalesces free space

• Deallocates temporary segments.

Process Monitor (PMON) Cleans up after failed processes by:

• Rolling back the transaction

• Releasing locks

• Releasing other resources

• Restarting dead dispatchers

Checkpoint (CKPT) Responsible for:

• Signaling DBWn at checkpoints

• Updating datafile headers with checkpoint information

• Updating control files with checkpoint information

Archiver (ARCn)

• Optional background process

• Automatically archives online redo logs when ARCHIVELOG mode is set

• Preserves the record of all changes made to the database

What is Global Inventory ?

Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory Please note if you have multiple global Inventory on machine check all oraInventory directories)

You will see entry like

HOME NAME=”ORA10g_HOME” LOC=”/u01/oracle/10.2.0/db” TYPE=”O” IDX=”1?/

What is Local Inventory ?

Inventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only.

What is Oracle Home Inventory?

Oracle home inventory or local inventory is present inside each Oracle home. It only contains information relevant to a particular Oracle home. This file is located in the following location:

$ORACLE_HOME/inventory

It contains the following files and folders:

· Components File

· Home Properties File

· Other Folders

What to do if my Global Inventory is corrupted ?

No need to worry if your global Inventory is corrupted, you can recreate global Inventory on machine using Universal Installer and attach already Installed oracle home by option

-attachHome

./runInstaller -silent -attachHome -invPtrLoc $location_to_oraInst.loc

ORACLE_HOME=”Oracle_Home_Location” ORACLE_HOME_NAME=”Oracle_Home_Name”

CLUSTER_NODES=”{}”

If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted ?

Background processes are started automatically when the instance is started.

Mandatory background processes are DBWn, LGWR, CKPT, SMON, PMON, and RECO. All other processes are optional, will be invoked if that particular feature is activated.

If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted.

Any issues related to backgroud processes should be monitored and analyzed from the trace files generated and the alert log.

In what scenarios open resetlogs required ?

An ALTER DATABASE OPEN RESETLOGS statement is required,

1.after incomplete recovery (Point in Time Recovery) or

2.recovery with a backup control file.

3. recovery with a control file recreated with the reset logs option.

If both CPU and PSU are available for given version which one, you will prefer to apply?

Once you apply the PSU then the recommended way is to apply the next PSU only. In fact, no need to apply CPU on the top of PSU as PSU contain CPU (If you apply CPU over PSU will considered you are trying to rollback the PSU and will require more effort in fact). So if you have not decided or applied any of the patches then, I will suggest you to go to use PSU patches. For more details refer: Oracle Products [ID 1430923.1], ID 1446582.1

PSU is superset of CPU and CPU mostly deals with security issues where as PSU fixes security issues as well as functionality changes.
if you are only concerned about security issues then CPU may be the good approach.
Oracle release both the patch sets every quarter.

•  Starting with the 11.2.0.2 patch set, Oracle Database patch sets are full installations of the Oracle Database software. This means that you do not need to install Oracle Database 11g Release 2 (11.2.0.1) before installing Oracle Database 11g Release 2 (11.2.0.2).

•  Direct upgrade to Oracle 10g is only supported if your database is running one of the following releases: 8.0.6, 8.1.7, 9.0.1, or 9.2.0. If not, you will have to upgrade the database to one of these releases or use a different upgrade option (like export/ import).

• Direct upgrades to 11g are possible from existing databases with versions 9.2.0.4+, 10.1.0.2+ or 10.2.0.1+. Upgrades from other versions are supported only via intermediate upgrades to a supported upgrade version.

Oracle version 11.2.0.4.0 what does each number refers to?
Oracle version number refers:
11 – Major database release number
2 – Database Maintenance release number
0 – Application server release number
4 – Component Specific release number
0 – Platform specific release number



How to get the latest version of OPatch?

You can download the latest version of OPatch from metalink using following URL.

http://updates.oracle.com/download/6880880.html



Explain the difference between a FUNCTION, PROCEDURE and PACKAGE ?

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

What is different between TRUNCATE and DELETE?

The 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.