Oracle Interview Questions – Part 17
Q.What is Ove?
rloading 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
Q.What is the Oracle Intelligent Agent? (for DBA)
The Oracle Intelligent Agent (OIA) is an autonomous process that needs to run on a remote node in the network to make the node OEM manageable. The Oracle Intelligent Agent is responsible for:
. Discovering targets that can be managed (Database Servers, Net8 Listeners, etc.);
. Monitoring of events registered in Enterprise Manager; and
. Executing tasks associated with jobs submitted to Enterprise Manager.
Q.How does one start the Oracle Intelligent Agent? (for DBA)
One needs to start an OIA (Oracle Intelligent Agent) process on all machines that will to be managed via OEM.
For OEM 9i and above:
agentctl start agent
agentctl stop agent
For OEM 2.1 and below:
On Windows NT, start the “OracleAgent” Service.
If the agent doesn’t want to start, ensure your environment variables are set correctly and delete the following files before trying again:
1) In $ORACLE_HOME/network/admin: snmp_ro.ora and snmp_rw.ora.
2) Also delete ALL files in $ORACLE_HOME/network/agent/.
Q.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.
Q.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.
Q.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
Q.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
Q.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.
Q.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
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
Q.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
The advantage of uing Image copy is its not in RMAN proprietary 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
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 has been made very simple in 9i .
It is just
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
.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.
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.
Q.Explain UNION,MINUS,UNION ALL, INTERSECT ?
INTERSECT returns all distinct rows selected by both queries.MINUS – returns all distinct rows selected by the first query but not by the second.UNION – returns all distinct rows selected by either queryUNION ALL – returns all rows selected by either query, including all duplicates.
Q.Can a repeating frame be created without a data group as a base?
Q.Is it possible to set a filter condition in a cross product group in matrix reports?
Q.Should the OEM Console be displayed at all times (when there are scheduled jobs)? (for DBA)
When a job is submitted the agent will confirm the status of the job. When the status shows up as scheduled, you can close down the OEM console. The processing of the job is managed by the OIA (Oracle Intelligent Agent). The OIA maintains a .jou file in the agent’s subdirectory. When the console is launched communication with the Agent is established and the contents of the .jou file (binary) are reported to the console job subsystem. Note that OEM will not be able to send e-mail and paging notifications when the Console is not started.
Q.Difference between SUBSTR and INSTR?
INSTR (String1,String2(n,(m)),INSTR returns the position of the mth occurrence of the string 2 instring1. The search begins from nth position of string1.SUBSTR (String1 n,m)SUBSTR returns a character string of size m in string1, starting from nth position of string1.
Q.What kind of jobs can one schedule with OEM? (for DBA)
OEM comes with pre-defined jobs like Export, Import, run OS commands, run sql scripts, SQL*Plus commands etc. It also gives you the flexibility of scheduling custom jobs written with the TCL language.
Q.What are the pre requisites?
I. to modify data type of a column ii. to add a column with NOT NULL constraint To Modify the datatype of a column the column must be empty. to add a column with NOT NULL constrain, the table must be empty.
Q.How does one backout events and jobs during maintenance slots? (for DBA)
Managemnet and data collection activity can be suspended by imposing a blackout. Look at these examples:
agentctl start blackout # Blackout the entrire agent
agentctl stop blackout # Resume normal monitoring and management
agentctl start blackout ORCL # Blackout database ORCL
agentctl stop blackout ORCL # Resume normal monitoring and management
agentctl start blackout -s jobs -d 00:20 # Blackout jobs for 20 minutes
Q.What are the two parts of a procedure ?
Procedure Specification and Procedure Body.
Q.What are the types of SQL Statement?
Data Definition Language :
CREATE,ALTER,DROP,TRUNCATE,REVOKE,NO AUDIT & COMMIT.
Data Manipulation Language:
TABLE,EXPLAIN PLAN & SELECT.Transactional Control:
COMMIT & ROLLBACKSession Control: ALTERSESSION & SET
ROLESystem Control :