Mir Sayeed Hassan – Oracle Blog

Oracle DBA Interview  Question & Answer

What is an Oracle Database?

Oracle Database is a database management system, where data are inserted and retrieved.

What is the Database Administrator task?

His duty is to take backup of the growing data, and install patches, and secure the data.

What are the databases available in the market?

Oracle, ibm db2, Microsoft SQL server, Microsoft Access, MySQL and SQLite, PostgreSQL, MariaDB

What is an instance in Oracle?

It is the medium to connect to the database. As soon as you start a database, the instance sit in RAM, and retrieves the data and update it through SGA. 40 percent of RAM can be used for sizing SGA rest is reserved for OS and others in 64-bit machine and in 32-bit machine, max SGA configured can be 1.5GB only.

What are the components of SGA?

Library cache, Database Buffer Cache, Data Dictionary cache, Redo log Buffer cache, Shared Pool.

Library cache is used to store Oracle statements.Data Dictionary Cache contains the definition of Database objects and privileges granted to users.

Data Base buffer cache holds copies of data blocks which are frequently accessed so that they can be retrieved faster for any future requests.

Redo log buffer cache records all changes made to the data files.

What are the physical components of the Oracle database.?

Data files, Control files , Redolog file Password files, Parameter files

What is System Change Number (SCN)?

SCN is a unique ID that Oracle generates automatically for every transaction which is committed., It is recorded for every change in the redo entry. Dba can get the SCN number by querying select SCN from a v$database from SQLPLUS.

What is PFILE?

The PFILE is read at instance startup time to get specific instance characteristics. The PFILE is text- based and can be edited in an editor like vi on UNIX or Notepad on Windows. Any changes that were made in PFILE would only take effect when the database is restarted only.

What is a control file?

The control file is a binary file which has the physical structure of a database.
It contains the number of log files and the respective location of the log file, Database name, and timestamp when the database is created, checkpoint information. It has the information when was the last database backup was taken, whether is failed or success. The last SCN number. The control file is the heart of the Database. That is the reason why without control file database cannot be up. And so This control file has to be multiplexed. So even though it corrupts or lost, from the backup the database can be restarted.

What are the two types of backup in Oracle?

COLD backup(User managed & RMAN) and HOT backup(User managed & RMAN)

What is a recovery catalog?

Recovery catalog contains the inventory of backups taken by RMAN from the database.
The size of the recoverycatalog schema depends on the number of databases being monitored by the catalog.
It is used for restoring a physical backup, reconstruct it, and make it available to the Oracle server. RMAN can be used without recovery catalog.
Recovery catalog also holds RMAN stored scripts.

What are the different stages of database startup?

1. NoMount 2. 2.Mount 3. 3.Open

What is hot backup?

When the database is active and running and during this time a backup is taken then it is hot backup.

How do you switch from an init.ora file to a spfile?

Create spfile from pfile;
and then shutdown instance and startup once again.

How will you backup control file from linux?

$cp *.ctl/u01/app/backup/db_name (This command will copy control file )

How can you obtain Information about control file?

The Control file information can be shown in the initialization parameter file. We can query v$controlfile to display the names of control files
From sql we can execute in SQL> show parameter control_files;

What is Redo Log Files?

The primary function of the redo log file is to record all changes made to data. When the instance crash happens and data are missed to write in the data file, then the changes can be obtained from the redo log, so work is never lost.

How will you backup all redo logfile from linux?

$cp *.log/u01/app/backup/db_name (This command will copy all redo logfile.

How will you backup all datafiles from linux?

$cp *.dbf/u01/app/backup/db_name (This command will copy all datafile.

How do we know which database instances are part of a RAC cluster?

You can query the V$ACTIVE_INSTANCES view to determine the member instances of the RAC cluster.

How will you check the database name from LINUX?

Cat/etc/oratab

How will you check the status of instance?

Select status from v$instance;

How will you check the name of instance?

Select name from v$instance;

How will you find the location of pfile?

Show parameter pfile;

How to kill the database session?

alter system kill session ‘SID,SERIAL#’

What is difference between startup mount and startup nomount?

startup mount -mount the control file startup nomount- does not mount the controlfile Q27)How to check the Oracle database version? Select * from v$version;

How will you recover a lost control file? Start the database in the nomount mode

1. Create the control file from the control file backup and place it in the correct location

Mount the database Recover the database Open the database

Suppose Some Blocks Are Damaged In A Datafile can you recover these blocks using ram?

Yes, the damaged blocks can be recovered.

Can you up the database when a control file is lost ?

You cannot up the database if control file is lost or damanged.

How will you find the database process running in linux?

$ps-ef | grep databasename

What are ORACLE DATABASE BACKUPS

Taking backups of everyday task, so that the datas are not lost . and tracking every single activity in the database.

What are the Types of Backup:

1. Logical backup (exp/imp –> 9i , expdp/impdp –> 10g)
2. Physical backup
Physical backup – User Managed backup( Cold and Hot backup) Physical backup – Server Manager backup( RMAN – Recovery Manager )

Logical backup (exp/imp)
We can take export(exp) in four levels.

1. Full database Level export/import
exp system/manager file=’/u01/app/oracle/ctsfull.dmp’ log=’/u01/app/oracle/ctsfull.log’ full=y imp system/manager file=’/u01/app/oracle/ctsfull.dmp’ log=’/u01/app/oracle/ctsfull_imp.log’ full=y 2. Schema(User) Level export/import

Schema user level export/import

exp system/manager file=’/u01/app/oracle/ctsuser.dmp’ log=’/u01/app/oracle/ctsuser.log’ owner=ram imp system/manager file=’/u01/app/oracle/ctsuser.dmp’ log=’/u01/app/oracle/ctsuser_imp.log’ fromuser=ram touser=ram
Note: RAM is a user in the database

3.Table Level export/import
exp system/manager file=’/u01/app/oracle/ctstab.dmp’ log=’/u01/app/oracle/ctstab.log’ tables=ram.emp

imp system/manager file=’/u01/app/oracle/ctstab.dmp’ log=’/u01/app/oracle/ctstab_imp.log’ fromuser=ram touser=ram tables=emp

4.Tablespace Level export/import

What happens when Db is started with nomount

 Init file(Pfile /spfile) is read  SGA is allocated
 Instance is started
 Alert/trace logs starts writing

What is DCL?

Data Control Language.i.e Grant,Revoke

Ways to reduce temp file size:

 Shrink temp files  Recreate temp files

What is Opatch Utility?

A java based utility that applies or rollback of patches to oracle software

What is an ASM?

It is a volume manager which holds datafiles in its diskgroups. contents are evenly distributed and stripped.

What are the background process in ASM?

ARBn(FOr rebalance) ASMb(Runs when copy command executes) GMON(disk membership)

Views to check RMAN status

v$rman_status v$session_longops

ORA -01555 error

Occurs due to snapshot too old
Modify UNDO_RETENTION or use larger rollback segments

What is a profile?

A profile is the resource limit(session per user,connect time,password expiry,password login atteps) for the current user.

Difference between sysdba and sysoper user roles.

sysdba: Can do all kinds of DBA works and also can create a DB sysoper: can do all kinds of DBa works but cant create a DB with this user.

What are the network files?

listener.ora tnsnames.ora sqlnet.ora

What is called as TNS?

TNS- Transaparent Network substrate.An alias for OCI which has connect string for the DB

Difference between hot and cold backup:

Hot Backup: Backup is taken when DB is up and running. Cold Backup: Backup is taken when DB is down and in mount stage

Types of Stanby Databases:

Physical -> Mount stage Active -> Read only mode Logical -> Read write mode

 Snapshot Q53)Modes of standby DG :

Maximum Protection Maximum Availability Maximum Performance

Views to get errors in DataGuard

v$dataguard_status v$archive_dest

Background processes in RAC

 LMON(GES)
 LMD(GES process)  LMS(GCS process)  DIAG

What is splitbrain syndrome

Nodes in a RAC cluster loses connectivity between each other and starts to function independantly.

What is the OCR Backup and OLR location

OCR bkup –> $CRS_HOME/cdata/server_name OLR –> $CRS_HOME/cdata/ **.olr

Functionality of running root.sh

Creating Oratab entry
copying oraenv/db_home to bin location

conditions of LGWR to write:

every 3sec
when 100MB is full 1/3 rd of the buffer is full

Uses of RMAN:

Block level backup can be taken  Block level recovery

Incremental backup can be taken  uses Large Pool

Skips unused blocks

Different status of redo log files:

Current –> LGWR currently writing ACTIVE –> Logfile used for recovery INACTIVE –>Logfile not used for recovery INVALID –>When LGWR cannot write or ACCESS

Background processes in DataGuard:

MRP(MAnaged Recovery Process)

What is a PSU patch

 LGWr(Log writer)
LNS (Lofile Network Server)

 Arch (Arch process) RFS(Remote File Server)

Patch Set Update : Collection of high impact,low risk and proven fixes for a specific product

Types of patching in RAC:

All node –> Patches done at all nodes at a time
Rolling –> Each node is patched one by one.
Minimum Downtime Patching –> some nodes are up.some nodes under patching

Types of Redundancy in ASM:

External –> No mirroring Normal –> Two way mirroring High –> Three way mirroring

What are the ASM parameters

v$asm_diskgroups v$asm_diskstring v$asm_power_limit

New features from oracle 11G

 Fast start failover
 Active DataGuard added

ACFS File system Introduced

What does PMON do

Cleansup unwanted process or ubnormally terminated process Release locks or other resources
restarting dead dispatchers

What are the different oracle database objects

Tables

Views Indexes Synonyms Sequences Tablespaces

What is the oracle DB logical structure

Datablock –> Extent –> Segment –> Tablespace –> Database

What is a synonym?

A synonym is also known as alias for a table,view,sequence or program unit

Wha are the services in DataGuard

STANDBY_FILE_MANAGEMENT

 Redo Transport service  Redo Apply service
 Role Transition

Mention some DG parameters

LOG_ARCHIVE_CONFIG  LOG_ARCH_DEST_n

LOG_ARCH_DEST_STATE_n  FAL_SERVER

 FAL_CLIENT ARCHIVE_LAG_TARGET

How many standby redo log files should be created?

FOr standalone, if n number of redo logfile in primary, then (n+1) standby redo logfile should be created for standby
For RAC,if n number of redo logfile in primary, then nuber of thread *(n+1) standby redo logfile should be created for standby

How to enable and check block_change_tracking?

alter database enable block change tracking using file ‘Location/blk_change.dbf’; select * from v$block_change_tracking;
What does Query Optimiser do?

 Generates a set of plans for SQls  Estimates cost for SQLs

Compares plan and choose lowest cost

How to take AWr report

 awrrpt.sql( for standalone) awrgrpt.sql(for RAC at Db evel)

awrrpti.sql(for RAC at instance level)

Name some wait events:

Db file sequential read DB file scaterred read Log file parallel write

Log file sync Buffer busy waits Free buffer waits

What does crsd do?

CRSd–> Cluster Ready Service Deamon.Mainly for managing HA(High Availability) It does OCR backup for every 3hrs

why catbundle.sql psu apply is ran?

To update the DB components with the new atch details and get validated. check in dba_registry_history

What is a control file?

A binary file that records physical straucture of the DB,needed to startup the DBand operate it It inludes DB name,datafiles,redofile names and its location,timestamp of DB creation,cureent log sequence number,checkpoint info etc..

How To check lag in Physical standby and if lag is found what will be your approach to troubleshoot.

I will check MRP status in Standby DB

Try to restart MRP and make sure it is not waiting for ‘WAIT_FOR_GAP’ message. If they are lagging, then we must restore the missing logs.

Also, we can check alert logs on both standby and primary database.
Start Stop MRP on standby DB:
Primary_to check error:
SQL> select status,error from gv$archive_dest where dest_id=2;
STATUS ERROR
ERROR ORA-00270: error creating archive log ERROR ORA-00270: error creating archive log ERROR

ORA-00270: error creating archive log Try to ENABLE and DEFER log shipping .

alter system set log_archive_dest_state_2=’ENABLE’ scope=both sid=’*’; alter system set log_archive_dest_state_2=’DEFER’ scope=both sid=’*’;

How you will check time on oracle

We can get current time by the query:
alter session set nls_date_format=”yyyy-mm-dd hh24:mi:ss”;SQL> Session altered. SQL> select sysdate from dual;
SYSDATE
SQL> !date
Sat Feb 8 11:05:52 EST 2014

Tell me by an example how you can copy file to different

scp rman_driver_10g.sh oracle@90.501.30.19:/GDSNP/oracle/admin/scripts

Suppose you have lot many inactive sessions and application team asks you to kill them for specific service, how will you do it quickly.

I will use Dynamic query to kill multiple sessions like.

select ‘alter system kill session ”’||sid||’,’||serial#||”’ immediate;’ from v$session where SERVICE_NAME =’Processing’ and status=’INACTIVE’ ;

Check now sessions should be gone.

select inst_id,sid,serial#,machine,username,OSUSER,status from gv$session where SERVICE_NAME =’Processing’ and status=’INACTIVE’;

Q91)How will you get datafile size and tablespace it belongs to

select tablespace_name,sum(bytes)/1024/1024/1024 as GB from dba_data_files group by tablespace_name;

Q92)How will you find out who is using sysaux

I will investigate V$SYSAUX_OCCUPANTS col OCCUPANT_NAME for a30 col SCHEMA_NAME for a20

select OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024 “Space (MB)”,SCHEMA_NAME fromV$SYSAUX_OCCUPANTS;

What is Flasback feature and what parameter necessary to set.

It is great feature, one of my favorites which enables DBA to flashback database back in time with minimal effort and time. To use this feature FLASHBACK_ON parameter needs to be set and flashback point should be created beforehand.

SQL> select flashback_on from v$database;

FLASHBACK_ON

A database is up and running fine however application user or end user complains that they are not able to connect to database. First thing what would you do?

I will check if database is reachable and do ping test

tnsping ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=alphaddbp001- vip)(PORT=1521))( CONNECT_DATA=(SERVICE_NAME=ORAPDB1)))’

How to determine how much space I can reclaim by shrinking

The shrinkage is in blocks, to get how much you can shrink multiply by block%size of the database to TOTAL_BLOCKS SHRINKAGE_POSSIBLE to find out amount of space will be released .

SQL> show parameter block_size

What do you know about DataGuard and how many types of standby databases are there ?

A Data Guard is to protect production data and continue the critical business applications in case of any disaster event. The Oracle DataGuard configuration consists of one production database and one or more standby databases irrespective of location (provided they are communicating to each other). Both primary and standby database can be either standalone or RAC Database. 11G also supports heterogeneous configurations in which the primary and standby systems may have different bit/processors or OS version. There are 3 types of standby database.

Physical Standby Database

It is a block to block replica of the production database and so with exact size of Production database. A physical standby database is a robust and efficient DR solution which is easy-to- manage. It reduces downtime for the application with the capabilities of fast role switch in case of planned or unplanned outages and can prevent data loss and corruption. It supports all data types and can reduces workload from primary database. The physical standby database is sync with production by Redo Apply.

Logical Standby Database

This type of standby database can be altered to have a different structure and so not always exact replica of production. The database is updated using SQL statements generated by mining the redo logs. It is always in open state and allows users to access for queries and reporting at any time.

Snapshot standby database

It allows user to use the standby database in read write mode (like standalone database) while still receiving the redo logs from primary. However, received redo logs from Primary will apply only

when snapshot is converted back to Physical standby. This is good feature in DR drill/Testing or bug fixing on production or in reporting purpose.

How many types of protection mode for standby database, tell me briefly .

There are 3 types of protection mode for standby database. Maximum Protection
Ø Highest level or protection and Zero data loss in case of any DR event.

Ø Use SYNC/AFFIRM I/O where Primary waits for acknowledgement that redo has been transmitted synchronously and written to disk. Primary database issue commits only when transaction gets applied to standby database.

Ø At least one standby database should get synchronized before the transaction commits at primary. If standby site is not available because of network or any reason, Primary database will be brought down to maintain level of data protection. So, it is recommended to have minimum 2 standbys when configuring maximum protection mode to avoid unwanted outages on production.

Ø Standby redo logs are required for this configuration Maximum Performance

Ø Highest level of performance and lowest degree of protection compare to other modes. Ø Data loss can happen in case of failover

Ø Uses ASYNC/NOAFFIRM I/O where primary will not wait for acknowledgement of redo transmission. Updates are first committed to production database before the logfile arrives on DR site

Maximum Availability

Ø It is a Mixture of Maximum Protection and Maximum Performance. It behaves like maximum protection however in case if standby site is not available it starts working in maximum performance.

Ø Can use either SYNC/AFFIRM (Primary waits for acknowledgement that redo has been transmitted synchronously and written to disk) or SYNC/NOAFFIRM (Primary waits only for
the acknowledgement that redo has been transmitted synchronously) based on what. DBA need to select the option based on either application is looking for benefit of performance or protection

Ø Standby redo logs are required for this configuration

Tell me few important views used in Oracle you have learned ?

Calculate the number of dump files getting generated with the help of filesize and estimate_only clause. If estimate goes beyond 99 dump files, keep additional directory.

How you feel RMAN will be useful for you?

Table spaces are not put in the backup mode, therefore there is no extra redo log file during online backups.
Incremental backups will take the backup data blocks only those got changed since last backup.

 Detection of corrupt blocks.
 Built in reporting and listing commands.  Parallelization of I/O operations.

How to bring a database from ARCHIVE LOG mode to NON ARCHIVE LOG MODE?

 You should change your init<SID>.ora file with the following information  log_archive_dest=’/u01/oradata/archlog’ (for example)

log_archive_format=’%t_%s.dbf’ log_archive_start=true (prior to 10g)

 sql>shutdown; sql>startup mount;

 sql> alter database archivelog;

V$Parameter V$Database V$Instance V$Datafiles V$controlfiles V$logfiles

What are the different methods we can shutdown our database?

SHUTDOWN (or) SHUTDOWN NORMAL

New connections will not be% accepted and db will not come down until existing % connections exit or close themselves

SHUTDOWN TRANSACTIONAL

New connections will not be% accepted and will not come down until %existing transactions to commit and exit or close themselves.

SHUTDOWN IMMEDIATE

New connections will not be% accepted and will not come down until all committed transactions %are written in the DB(Rolled_forward) and all uncommitted data is removed (Rolled_back)

SHUTDOWN ABORT
Not my recommendation, it is simply switching off. SHUTDOWN NORMAL, TRANSACTIONAL, IMMEDIATE are clean shutdown methods as database maintains its consistency.

How many different types of indexes are available you know?

 Oracle provides several Indexing schemas
B-tree index – Retrieves a small amount of information from a large table.

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

 Domain Index – Refers to an application
Q108)What is the use of ALERT log file? Where can you find the ALERT log file?

Alert log file is a log file that records database-wide events which is used for trouble shooting.  We can find the Log file in BACKGROUND_DUMP_DEST parameter.

TM Lock – Acquired once for each object, which is being changed. It is a DML lock. The ID1 column identifies the object being modified.

Following events are recorded in ALERT log file: Database shutdown and startup information.

 All non-default parameters.
 Oracle internal (ORA-600) errors.

Information about a modified control file.  Log switch change.

What is a user process trace file?

It is generated only if the value of SQL_TRACE parameter is set to true for a session.  SQL_TRACE parameter can be set at database, instance, or session level.

The location of user process trace file is specified in the USER_DUMP_DEST parameter.

What are different types of locks?

 There are different types of locks, which are given as follows:
 System locks – controlled by oracle and held for a very brief period of time.  User locks – Created and managed using dbms_lock package.
 Different types of user locks are given as follows
 UL Lock – Defined with dbms_lock package.

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.

How to tell the %age of connections on the current node of a DB?

select round ((I.instance/+zn.db)*100,2) as “%AGE_CONN” from (select count(*) as instance from v$session where username not like ‘%SYS%’) I, (select count(*) as db from gv$session where username not like ‘%SYS%’) zn;

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.

 The following files must be backed up
 Database files (Header of datafiles is freezed during backup)  Control files

 Parameter files (spfile and pfile)

Archived log files  Password file

What is a full backup and name some tools you use for full backup?

A full backup is a backup of all the control files, data_files, and parameter_file both SPFILE binary file and PFILE-static).

You must also backup your ORACLE_HOME binaries which are used for cloning.
A full backup can be performed when our database runs in NON ARCHIVE LOG mode. As a thumb rule, you must shutdown your database before you perform full backup.

What are the different types of backup’s available and also explain the difference between them?

 There are 2 types of backup’s
 COLD backup(User managed & RMAN)
 HOT backup(User managed & RMAN)
 Hot backup is taken when the database is still online and database should be in ARCHIVE  LOG MODE.
 Cold backup is taken when the database is in offline mode.
 Hot backup is inconsistent backup where as cold backup is consistent backup.

Name the architectural components of RMAN?

Recovery catalog database Media management layer Backup sets and backup pieces

RMAN executable Server process

 Channels Target database

Assume you work in an xyz company as senior DBA and on your absence your back up DBA has corrupted What do you do?

 As long as all data files are safe and on a successful completion of it is ok
 We can restore the control file by performing following commands CONNECT INTERNAL

STARTUP MOUNT and then
 you can TAKE ANY OFFLINE TABLESPACE (Read-only)

 after that you have to ALTER DATABASE DATAFILE (OFFLINE)  RECOVER DATABASE USING BACKUP CONTROL FILE

But we need to know all of the datafiles, logfiles, and settings of MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES for the database to use the command.

 ALTER DATABASE OPEN RESETLOGS
 BRING READ ONLY TABLE SPACE BACK ONLINE
 Shutdown and back up the system. Now you may start again the DB

Then give the command ALTER DATABSE BACKUP CONTROL FILE TO TRACE  This output can be used for control file recovery as well.

 If control file backup is not available, then the following will be required  CONNECT INTERNAL STARTUP NOMOUNT
 CREATE CONTROL FILE …..;

How data is being written into DB and by what process it is achieved and how frequent does it push data to db?

DBWR is a background process which writes data blocks info from DB buffer cache to data_files.

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

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

LGWR writes redo or changed information from redo log buffer cache in cyclic fashion to redo log files

 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

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

SYSTEM tablespace is created automatically during database creation and has to be online always.

SYSAUX tablespace UNDO tablespace TEMP tablespace

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

Which file is accessed first when Oracle database is started and What is the difference between SPFILE and PFILE?

 Init<SID>.ora parameter file or SPFILE is accessed first
 parameters in this file are necessary to start a DB.
 SPFILE is by default created during database creation whereas PFILE should be created from  SPFILE.
 PFILE is static text file whereas SPFILE is binary file

 you cannot edit binary file which is spfile but the static pfile file can be edited
 Changes made in SPFILE are dynamically effected with running database whereas PFILE

changes are effected after restarting the database.  We can backup SPFILE using RMAN.

=Hence this Oracle DBA Interview Question & Answer are created with the reference of Oracle documents=