Q. 1. Different between PFILE &
SPFILE.?
When oracle instance start up, it
search for the spfile by default, if no spfile file is found, it is search for
pfile.if spfile is in the search directory ($ORACLE_HOME/dba( Linux)
$ORACLE_HOME/database ( win)), but you want to use the pfile instead. you must
use.
startup pfile='full path of the pfile'
PFILE is the client-side text file that
must be updated with a standard text editor like "notepad" or
"vi". This file normally reside on the server, however, you need a
local copy if you want to start Oracle from a remote machine. It’s readable
file. If we Change in PFILE it require to restart the database to make changes
effect.
SPFILE is written in binary we cannot read
easily. An SPFILE can be backed-up with RMAN. SPFILE support changes online not
require to restart database for changes effect. Can modify with “Alter system”
command.
Q. 2. What is the difference in hot
backup and cold backup.?
Hot backup: - When databases must remain
operational 24 x 7, or have become so large that a cold backup would take too
long .
When database is online (read write mode) and
we take an online backup no need to shutdown the instance called hot backup. To
perform a hot backup, the database must be in ARCHIVELOG mode. We can take a
hot backup by RMAN tool.
Cold backup: A cold backup of the database is an image copy
of the database at a point in time. The database is consistent and restorable.
This image copy can be used to move the database to another computer provided
the same operating system is being used. If the database is in ARCHIVELOG mode,
the cold backup would be the starting point for a point-in-time recovery. All
archive logfiles necessary would be applied to the database once it is restored
from the cold backup.
When our database is
offline (Instance is shutdown) and we take a backup of all datafile, control
file and online redo log file(optional) & init.ora file (can be created
manually) called cold backup. In UNIX we can use cpio, tar, dd,
fbackup or some third party utility to take cold backup.
Q. 3. What are logical backup and its
advantages?
Logical Backup:- Logical backup is possible by data
pump utility (import/export) to a dump file. A logical backup is called when we
take database objects backup logically, like
table, index, view, producer and exports the database objects independent of the physical in a
.dmp file.
Advantages: Logical backups are very useful when
changing database servers. Logical backups can be used to detect block
corruptions. If a corrupt block is detected during an export, the export will
fail. A logical backup does not require ARCHIVELOG mode. Logical backup is
Free, and easy to transfer the database objects one database to another.
We can take
database objects backup with the help of IMPDP, EXPDP & IMP and EXP tools.
Q. 4. What is index and types of indexes?
Indexes are optional structures associated with tables and clusters. We can create indexes on one or more columns of a table to direct access on a row. Index increase performance of the sql query. We can create or drop an index at any time without affecting the base tables or other indexes.
Types of Index: There is basically two types index
BTree Index & BitMap Index.
Unique and
Nonunique Indexes.
Composite Index.
Visible and
Invisible Indexes.
Indexes and Keys.
Function Based
Index.
Q. 5. What is instance?
When database start oracle software allocates some memory (SGA) system global area and start some background process. The combinations of system global area and background process called instance.
There is some background process are mandatory like SMON, PMON,
DBWR, LGWR, CKPT. And some GSA components (segment) are mandatory like
(database buffer cache, log buffer, shared pool) some are optional.
Q. 6. What are database sequence and its uses?
Sequence is database objects from which multiple users can generate sequence integer. The sequence generator generates sequential numbers, which helps to create unique primary keys automatically.
Q. 7. What is the difference between view and materialized view?
View: - In View query result is not stored on the disk or database but materialized view store query result on the disk or table.
Rowid of
view is same as original table but in case of Materialized
view rowid is different.
In case of View
we always get latest data but in case of Materialized view we need to refresh
the view for getting latest data.
Performance of
View is less than Materialized view.
View always provides the latest data from the table. In case of
Materialized view we need extra trigger or some automatic method so that we can
keep MV refreshed; to fetch latest data from the database.
Q. 8. What is materialized view?
A materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary based on aggregations of a table's data.
The two refresh execution modes
are ON COMMIT and ON DEMAND.
Q. 9. What are constrains and types of constraints?
Constraints apply specific business rules to data, ensuring the data conforms to the requirements defined or restrict invalid data to enter in a column.
In oracle there are
5 type of constraint is available:
1. Check (The CHECK constraint is used to
limit the value range that can be placed in a column.)
2. Not
null (No any row can
be null in that column)
3. Unique (Unique constraints are like
alternative primary key constraints. A unique constraint defines a column, or
series of columns, that must be unique in value. )
4. Primary
Key (Primary key
constraints define a column or series of columns that uniquely identify a given
row in a table.)
5. Foreigne
key(A foreign key
constraint is used to enforce a relationship between two tables)
Q. 10. Required SGA Components:
1) Shared Pool: Caches the most recently used SQL statements that have been issued by database users.
2) Database Buffer Cache: Caches the data that has been most recently accessed by database users.
3) Redo Log Buffer: Stores transaction information for recovery purposes
Optional SGA Components:
1) Java Pool: Caches the most recently used Java objects and application code when Oracle’s JVM option is used.
2) Large Pool: Caches data for large operations such as Recovery Manager (RMAN) backup and restore activities and Shared Server components.
3) Streams Pool: Caches the data associated with queued message requests when Oracle’s Advanced Queuing option is used.
Q. 11. What are mandatory and optional background processes of instance?
*SMON (System MONitor) is an Oracle background process created when you start a database instance. The SMON process performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents.
* PMON (Process MONitor) is an Oracle background process created when you start a database instance. The PMON process will free up resources if a user process fails (eg. release database locks).
PMON normally wakes up
every 3 seconds to perform its housekeeping activities.
* DBWR - Database Writer or Dirty Buffer Writer process is responsible for writing dirty buffers from the database block cache to the database data files. Generally, DBWR only writes blocks back to the data files on commit, or when the cache is full and space has to be made for more blocks. The possible multiple DBWR processes in RAC must be coordinated through the locking and global cache processes to ensure efficient processing is accomplished.
* LGWR - Log Writer process is responsible for writing the log buffers out to the redo logs. In RAC, each RAC instance has its own LGWR process that maintains that instance?s thread of redo logs.
Q. 12. What is the use of "Undo" table space in database?
Oracle Database creates and manages information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.
When the instance starts, the database automatically
selects the first available undo tablespace. If no undo tablespace is
available, the instance starts without an undo tablespace and stores undo
records in the SYSTEM tablespace.
Q. 13. What is the use of Temporary tablespace in database?
Temporary tablespaces are used to manage space for database query operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialization parameter), space will be allocated in a temporary tablespace for doing the sort operation.
Q. 14. What is the advantage of putting database in archive log mode?
1. Online database backup possible only in archive log mode.
2. If
database crashes, we can only recover to the point of time of last backup, But
archive log allow recovering it to the point of the crash time.
Q.
15. What are the types of recovery in Oracle?
·
Complete Recovery
·
Incomplete Recovery (point-in-time
recovery)
·
Data file Media Recovery
·
Block Media Recovery
Q.
16. How many ways we can take database backup? i
means what tools can be used for taking database backup?
Cp command on OS level (Alter set database begin backup mode)
RMAN
EXPDP, IMPDP.
Q. 17. What is database dictionary?
Data base dictionary is the read only set of tables that provide the information about the database like.
The definition of the all database objects (Index, Tables, Views, SP, cluster, sequence etc.)
How much space has been allocated and is currently used by the schema objects.
Default value for the columns.
Integrity constant information’s.
The name of the oracle users.
Privileges’ and roles each user has been granted.
Other general database information.
The data dictionary is structured in tables and views, just like other database data. All the data dictionary tables and views for a given database are stored in that database's SYSTEM tablespace.
Auditing information like who has access or update various schima objects.
Q. 18. What will be the contents of System and Sysaux tablespace?
System tablespace contain data dictionary tables. To take advantage of the benefits of locally managed tablespaces, you can create a locally managed SYSTEM tablespace, or you can migrate an existing dictionary managed SYSTEM tablespace to a locally managed format.
SYSAUX tablespace is an auxiliary tablespace to the SYSTEM tablespace. The SYSAUX tablespace stores many database components, and it must be online for the correct functioning of all database components.
Q. 19. What tools are available in database for taking logical backup?
EXPDP data pump.
Q. 20. What is listener and its uses in database?
The listener is a separate process that runs on the database server computer. It receives incoming client connection requests and manages the traffic of these requests to the database server.
Q. 21. What is the use of tnsnames.ora and sqlnet.ora files?
Tnsnames.ora: > This is a configuration file that contains net service names mapped to listener protocol addresses, or net service names mapped to connect descriptors for the local naming method.
Sqlnet.ora: > This file is the profile configuration file. It resides on the client machines and the database server. Profiles are stored and implemented using this file. The database server can be configured with access control parameters in the sqlnet.ora file. These parameters specify whether clients are allowed or denied access based on the protocol.
Q. 22. Which process register database service with listener?
PMON process registered. SQL> ALTER SYSTEM REGESTER (to register manually)
Q. 23. Which process read data from database?
SMON (server process)
Q. 24. Explain complete steps of the user connection? I mean what all steps involved while connecting to the database?
When a
client connects to the database listener receives the request and then listener
creates a dedicated process for the request after all dedicated process
directly connects the client to database.
Q. 25. What is normalization? And types of normal forms (normalization)?
Normalization is a technique to organize the data in the database. Normalization is a series of steps followed to obtain a database design that allows for efficient access and storage of data. These steps reduce data redundancy and the chances of data becoming inconsistent
First
normal forms: First Normal Form eliminates repeating groups by
putting each into a separate table and connecting them with a one-to-many
relationship
§ Each table has a primary key made
of one or several fields and uniquely identifying each record
§ Each field is atomic, it does not contain more than
one value
Five are normal forms.
Q. 26. What are ACID properties of the database?
The ACID
properties of a DBMS allow safe sharing of data.
§ Atomicity (atomicity is maintained in the
presence of deadlocks, database
software failures, application software failures, CPU failures, disk failures,
can be turn off the system and session level)
§ Isolation(Transactions are serializable when
the effect on the database is the same whether the transactions are executed in
serial order or in an interleaved fashion.)
§ Durability(recovery to the most recent
successful commit after a database software failure, an
application software failure, a CPU failure, a disk failure, a data disk
failure)
Q. 27. What is schema?
The collection of logical structures of data or schema objects called schima like all (tables, index, S.P., view, materialize view, sequence, etc.) A schema is owned by a database user and has the same name as that user
Q. 28. Explain Oracle database architecture?
Each running Oracle database is associated with an Oracle instance. When a database is started
on a database server, the Oracle software allocates a shared
memory area called the System
Global Area (SGA) and starts several Oracle background processes.
This combination of the
SGA and the Oracle processes are called an Oracle instance.
After starting an instance, the Oracle software associates the
instance with a specific database.
This is called mounting the database. The database is then ready
to be opened, which makes it
Accessible to authorized users.
Multiple instances can execute concurrently on the same computer, each
accessing its own physical database.
Q. 29. Explain these query.
ANALYZE TABLE <table_name> COMPUTE STATISTICS;
Its work for whole table (scan every row in the table)
*******************
ANALYZE TABLE <table_name> estimate STATISTICS;
Computes Statistics on sample of rows over the table.
By default, its 1064 rows, we can define in the percentage like 10%, 20%
*******
ANALYZE TABLE <table_name> VALIDATE STATISTICS;
The
ANALYZE command can be used to verify each
data block in the analyzed object. If any corruption is detected rows are added
to the INVALID_ROWS table.Q. 30. How to calculate the Reload ratio and Hit ratio.?
The following query uses the V$LIBRARYCACHE view to examine the reload ratio in the library cache.
This next query uses the
V$LIBRARYCACHE view to examine the library caches hit ratio in detail:
Q. 31. How to check that the system is ready for installation or not?
Using “./rda.sh -T hcve” command in Linux.
RDA-Remote Diagnostic Agent (RDA)
-T for installation
HCVE-
Health Check
Q.32. What
is the DDL DML?
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
Q.33. What is TKPROF.?
TKProf is an Oracle database utility used to format SQL Trace output into human readable format. The TKProf executable is located in the ORACLE HOME/bin directory.
Q.34. what is PCTFREE & PCTUSED?
The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement.
PCTFREE =
20;
This
states that 20% of each data block in this table's data segment be kept free
and available for possible updates to the existing rows already within each
block. New rows can be added to the row data area, and corresponding information
can be added to the variable portions of the overhead area, until the row data
and overhead total 80% of the total block size
The PCTUSED parameter sets the minimum
percentage of a block that can be used for row data plus overhead before new
rows are added to the block. After a data block is filled to the limit
determined by PCTFREE, Oracle considers the block
unavailable for the insertion of new rows until the percentage of that block
falls beneath the parameter PCTUSED. Until this value is
achieved, Oracle uses the free space of the data block only for updates to rows
already contained in the data block. For example, assume that you specify the
following parameter in a CREATE TABLE statement
PCTUSED =
40
In this
case, a data block used for this table's data segment is considered unavailable
for the insertion of any new rows until the amount of used space in the block
falls to 39% or less (assuming that the block's used space has previously
reached PCTFREE).
Q. 35. What is locally
managed and Dictionary Managed Tablespaces?
Locally managed tablespaces track
all extent information in the tablespace itself by using bitmaps, resulting in
the following benefits:
Dictionary managed
tablespace Oracle use the data
dictionary (tables in the SYS schema) to track allocated and free extents for
tablespaces that is in "dictionary managed" mode. Free space is
recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever
space is required in one of these tablespaces, the ST (space transaction) enqueue
latch must be obtained to do inserts and deletes agianst these tables. As only
one process can acquire the ST enque at a given time, this often lead to
contention.
SQL> CREATE TABLESPACE ts1 DATAFILE '/oradata/ts1_01.dbf' SIZE
50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 50K NEXT 50K
MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);
- CREATE - to create objects in the database
- ALTER - alters the structure of the database
- DROP - delete objects from the database
- TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
- COMMENT - add comments to the data dictionary
- RENAME - rename an object
- SELECT - retrieve data from the a database
- INSERT - insert data into a table
- UPDATE - updates existing data within a table
- DELETE - deletes all records from a table, the space for the records remain
- MERGE - UPSERT operation (insert or update)
- CALL - call a PL/SQL or Java subprogram
- EXPLAIN PLAN - explain access path to data
