|
Oracle Interview Questions and Answers
If the maximum record retrieved property of the query
is set to 10 then a summary value will be calculated?
Only for 10 records.
What are the different objects that you cannot copy or
reference in object groups?
Objects of different modules
Another object groups
Individual block dependent items
Program units.
What is an OLE?
Object Linking & Embedding provides you with the
capability to integrate objects from many Ms-Windows
applications into a single compound document creating
integrated applications enables you to use the features
form .
Can a repeating frame be created without a data group as
a base?
No
Is it possible to set a filter condition in a cross
product group in matrix reports?
No
What is Overloading of procedures ?
The Same procedure name is repeated with parameters of
different datatypes and parameters in different
positions, varying number of parameters is called
overloading of procedures. e.g. DBMS_OUTPUT put_line
What are the return values of functions SQLCODE and
SQLERRM ? What is Pragma EXECPTION_INIT ? Explain the
usage ?
SQLCODE returns the latest code of the error that has
occurred.
SQLERRM returns the relevant error message of the
SQLCODE.
What are the datatypes a available in PL/SQL ?
Some scalar data types such as NUMBER, VARCHAR2, DATE,
CHAR, LONG, BOOLEAN. Some composite data types such as
RECORD & TABLE.
What are the two parts of a procedure ?
Procedure Specification and Procedure Body.
What is the basic structure of PL/SQL ?
PL/SQL uses block structure as its basic structure.
Anonymous blocks or nested blocks can be used in PL/SQL
What is PL/SQL table ?
Objects of type TABLE are called "PL/SQL tables", which
are modeled as (but not the same as) database tables,
PL/SQL tables use a primary PL/SQL tables can have one
column and a primary key. Cursors
WHAT IS RMAN ? (for DBA)
Recovery Manager is a tool that: manages the process of
creating backups and also manages the process of
restoring and recovering from them.
WHY USE RMAN ? (for DBA)
No extra costs …Its available free
?RMAN introduced in Oracle 8 it has become simpler with
newer versions and easier than user managed backups
?Proper security
?You are 100% sure your database has been backed up.
?Its contains detail of the backups taken etc in its
central repository
Facility for testing validity of backups also commands
like crosscheck to check the status of backup.
Faster backups and restores compared to backups without
RMAN
RMAN is the only backup tool which supports incremental
backups.
Oracle 10g has got further optimized incremental backup
which has resulted in improvement of performance during
backup and recovery time
Parallel operations are supported
Better querying facility for knowing different details
of backup
No extra redo generated when backup is taken..compared
to online
backup without RMAN which results in saving of space in
hard disk
RMAN an intelligent tool
Maintains repository of backup metadata
Remembers backup set location
Knows what need to backed up
Knows what is required for recovery
Knows what backups are redundant
UNDERSTANDING THE RMAN ARCHITECTURE
An oracle RMAN comprises of
RMAN EXECUTABLE This could be present and fired even
through client side
TARGET DATABASE This is the database which needs to be
backed up .
RECOVERY CATALOG Recovery catalog is optional otherwise
backup details are stored in target database controlfile
.
It is a repository of information queried and updated by
Recovery Manager
It is a schema or user stored in Oracle database. One
schema can support many databases
It contains information about physical schema of target
database datafile and archive log ,backup sets and
pieces Recovery catalog is a must in following scenarios
. In order to store scripts
. For tablespace point in time recovery
Media Management Software
Media Management software is a must if you are using
RMAN for storing backup in tape drive directly.
Backups in RMAN
Oracle backups in RMAN are of the following type
RMAN complete backup OR RMAN incremental backup
These backups are of RMAN proprietary nature
IMAGE COPY
The advantage of uing Image copy is its not in RMAN
proprietary format..
Backup Format
RMAN backup is not in oracle format but in RMAN format.
Oracle backup comprises of backup sets and it consists
of backup pieces. Backup sets are logical entity In
oracle 9i it gets stored in a default location There are
two type of backup sets Datafile backup sets, Archivelog
backup sets One more important point of data file backup
sets is it do not include empty blocks. A backup set
would contain many backup pieces.
A single backup piece consists of physical files which
are in RMAN proprietary format.
Example of taking backup using RMAN
Taking RMAN Backup
In non archive mode in dos prompt type
RMAN
You get the RMAN prompt
RMAN > Connect Target
Connect to target database : Magic
using target database controlfile instead of recovery
catalog
Lets take a simple backup of database in non archive
mode
shutdown immediate ; - - Shutdowns the database
startup mount
backup database ;- its start backing the database
alter database open;
We can fire the same command in archive log mode
And whole of datafiles will be backed
Backup database plus archivelog;
Restoring database
Restoring database has been made very simple in 9i .
It is just
Restore database..
RMAN has become intelligent to identify which datafiles
has to be restored
and the location of backuped up file.
Oracle Enhancement for RMAN in 10 G
Flash Recovery Area
Right now the price of hard disk is falling. Many dba
are taking oracle database backup inside the hard disk
itself since it results in lesser mean time between
recoverability.
The new parameter introduced is
DB_RECOVERY_FILE_DEST = /oracle/flash_recovery_area
By configuring the RMAN RETENTION POLICY the flash
recovery area will automatically delete obsolete backups
and archive logs that are no longer required based on
that configuration Oracle has introduced new features in
incremental backup
Change Tracking File
Oracle 10g has the facility to deliver faster
incrementals with the implementation of changed tracking
file feature.This will results in faster backups lesser
space consumption and also reduces the time needed for
daily backups
Incrementally Updated Backups
Oracle database 10g Incrementally Updates Backup
features merges the image copy of a datafile with RMAN
incremental backup. The resulting image copy is now
updated with block changes captured by incremental
backups.The merging of the image copy and incremental
backup is initiated with RMAN recover command. This
results in faster recovery.
Binary compression technique reduces backup space usage
by 50-75%.
With the new DURATION option for the RMAN BACKUP
command, DBAs can weigh backup performance against
system service level requirements. By specifying a
duration, RMAN will automatically calculate the
appropriate backup rate; in addition, DBAs can
optionally specify whether backups should minimize time
or system load.
New Features in Oem to identify RMAN related backup like
backup pieces, backup sets and image copy
Oracle 9i New features Persistent RMAN Configuration
A new configure command has been introduced in Oracle 9i
, that lets you configure various features including
automatic channels, parallelism ,backup options, etc.
These automatic allocations and options can be
overridden by commands in a RMAN command file.
Controlfile Auto backups
Through this new feature RMAN will automatically perform
a controlfile auto backup. after every backup or copy
command.
Block Media Recovery
If we can restore a few blocks rather than an entire
file we only need few blocks.
We even dont need to bring the data file offline.
Syntax for it as follows
Block Recover datafile 8 block 22;
Configure Backup Optimization
Prior to 9i whenever we backed up database using RMAN
our backup also used take backup of read only table
spaces which had already been backed up and also the
same with archive log too.
Now with 9i backup optimization parameter we can prevent
repeat backup of read only tablespace and archive log.
The command for this is as follows Configure backup
optimization on
Archive Log failover
If RMAN cannot read a block in an archived log from a
destination. RMAN automatically attempts to read from an
alternate location this is called as archive log
failover
There are additional commands like
backup database not backed up since time '31-jan-2002
14:00:00'
Do not backup previously backed up files
(say a previous backup failed and you want to restart
from where it left off).
Similar syntax is supported for restores
backup device sbt backup set all Copy a disk backup to
tape
(backing up a backup
Additionally it supports
. Backup of server parameter file
. Parallel operation supported
. Extensive reporting available
. Scripting
. Duplex backup sets
. Corrupt block detection
. Backup archive logs
Pitfalls of using RMAN
Previous to version Oracle 9i backups were not that easy
which means you had to allocate a channel compulsorily
to take backup You had to give a run etc . The syntax
was a bit complex …RMAN has now become very simple and
easy to use..
If you changed the location of backup set it is
compulsory for you to register it using RMAN or while
you are trying to restore backup It resulted in hanging
situations
There is no method to know whether during recovery
database restore is going to fail because of missing
archive log file.
Compulsory Media Management only if using tape backup
Incremental backups though used to consume less space
used to be slower since it used to read the entire
database to find the changed blocks and also They have
difficult time streaming the tape device. .
Considerable improvement has been made in 10g to
optimize the algorithm to handle changed block.
Observation
Introduced in Oracle 8 it has become more powerful and
simpler with newer version of Oracle 9 and 10 g.
So if you really don't want to miss something critical
please start using RMAN.
Page Numbers :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 35
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Job Interview Questions
for more Interview Questions with Answers
|
