Database Interview Questions Part 3

1.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 (SPFILE or PFILE).

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.

Full or COLD backup can be performed by using copy command in unix.

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

There are 2 types of backup’s

1. COLD backup(User managed & RMAN)
2. 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.

You can begin backup by using the following command

SQL> alter database begin backup;

– End backup by

SQL> alter database end backup;

3.How to recover database if we lost the control file and we do not have a backup and what is RMAN?

We can recover our database at any point of time, when we have backup of our control files in different mount points.

Also check whether the control file is available in trace file located in USERDUMP or the alert log to recover the database.

RMAN called as Recovery manager tool supplied by oracle that can be used to manage backup and recovery activities.

You can perform both offline (Cold) and online (Hot) backup’s using RMAN.

We need to configure Flash_Recovery_Area of database to use RMAN.

RMAN maintains the repository of backup information in control file.

4.What is a recovery catalog?

 Recovery catalog is an inventory of backup taken by RMAN for the database.

The size of the recovery catalog schema depends upon the number of databases monitored by the catalog.

It is used to restore 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.

List some advantages of using RMAN.

•  Table spaces are not put in the backup mode ,therefore there is no extra redo log file during online backups.
•  Incremental backups that only copy data blocks, which have changed since last backup.
•  Detection of corrupt blocks.
•  Built in reporting and listing commands.
•  Parallelization of I/O operations.

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

– You should change your init.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;
•  sql>alter database open;

– Make sure you backup your database before switching to ARCHIVELOG mode.

6.What are the different stages of database startup?

Database undergoes different stages before making itself available to end users

Following stages are involved in the startup of database

1. NoMount
2. Mount
3. Open

NoMount – Oracle Instance is available based on the parameters defined in SPFile.

Mount – Based on the Information from parameter control files location in spfile, it opens and reads them and available to next stage.

Open – Datafiles, redo log files are available to the end users.

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

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

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.

9.What is a user process trace file?

• It is an optional file which is produced by user session.
• 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.
• If it set at instance level, trace file will be created for all connected sessions.
• If it is set at session level, trace file will be generated only for specified session.
• The location of user process trace file is specified in the USER_DUMP_DEST parameter.

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

TX Lock – Acquired once for every transaction. It isa row transaction lock.

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

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

12.What is a latch and explain its significance?

• Latch is an on/off switch in oracle that a process must access in order to perform certain type of activities.
• They enforce serial access to the resources and limit the amount of time for which a single process can use a resource.
• A latch is acquired for a very short amount of time to ensure that the resource is allocated.
• We may face performance issues which may be due to either of the two following reasons
• Lack of availability of resource.
• Poor application programming resulting in high number of requests for resource.
• Latch information is available in the v$LATCH and v$LATCHHOLDER dynamic performance views.

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 large object in oracle? Explain its purposes.

Large objects (LOB’s) are exclusively used to hold large amounts of data. It can hold data in tetra bytes. Different types of LOBs include internal, external, persistent and temporary. Binary LOB’s are typically used to store graphics, video, or audio data.

Purpose:

• Enables you to access and manipulate the data efficiently in your application.
• Is optimized for large amounts of data.
• Provides a uniform way of accessing data stored within the database or outside the database.

Internal LOBs

BLOBs, CLOBs, and NCLOBs are Internal LOBs stored inside database tablespaces to optimize space and efficient access. Changes to internal LOBs can be committed or rolled back.

They use copy semantics and participate in the transactional model of the server and can be recovered on media failure.

The ACIDFoot 1 properties are applicable to internal LOBs too.

BLOB

The BLOB data type stores binary large objects. BLOB can store up to 4 gigabytes of binary data.

CLOB

The CLOB data type stores character large objects. CLOB can store up to 4 gigabytes of character data.

NCLOB

The NCLOB data type stores character large objects in multibyte national character set. NCLOB can store up to 4 gigabytes of character data.

External LOBs:

BFILES

BFILES are External LOBs stored in operating system files outside database tablespaces. These files use reference semantics.

The BFILE datatype allows read-only byte stream access to large files on the file system of the database server. The maximum file size supported is 4 gigabytes.

16. Name some of the important dynamic performance views used in Oracle?

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

17.What are the different methods we can shutdown our database?

SHUTDOWN (or) SHUTDOWN NORMAL

No new connections are accepted and wait for the user to close the session.

SHUTDOWN TRANSACTIONAL

No new connections are accepted and wait for the existing transactions to commit and logouts the session without the permission of user.

SHUTDOWN IMMEDIATE

No new connections are accepted and all committed transactions are reflected in database and all the transactions are about to commit are rolled back to previous value.

SHUTDOWN ABORT

It’s just like an immediate power off for a database, it doesn’t mind what are the transactions running it just stops entire activity -(even committed transactions are not reflected in database) and make database unavailable. SMON process takes responsibility for recovery during next startup of database.

SHUTDOWN NORMAL, TRANSACTIONAL, IMMEDIATE are clean shutdown methods as database maintains its consistency.

SHUTDOWN ABORT leaves our database in an inconsistent state,data integrity is lost.

18.Name the architectural components of RMAN?

• RMAN executable
• Server process
• Channels
• Target database
• Recovery catalog database
• Media management layer
• Backup sets and backup pieces