PREVIOUS NEXT
Oracle Interview Questions And Answers
1. What tools/utilities does Oracle provide to assist with performance tuning? (for DBA)
Oracle provide the following tools/ utilities to assist with performance monitoring and tuning:
. TKProf
. UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats monitoring
. Statspack
. Oracle Enterprise Manager - Tuning Pack
2. What is STATSPACK and how does one use it? (for DBA)
Statspack is a set of performance monitoring and reporting utilities provided by Oracle from Oracle8i and above. Statspack provides improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available. For more information about STATSPACK, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.
Install Statspack:
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- Install Statspack -
sqlplus "/ as sysdba" @spcreate.sql-- Enter tablespace names when prompted
Use Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; -- Take a performance snapshots
exec statspack.snap;
o Get a list of snapshots
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql -- Enter two snapshot id's for difference report
Other Statspack Scripts:
. sppurge.sql - Purge a range of Snapshot Id's between the specified begin and end Snap Id's
. spauto.sql - Schedule a dbms_job to automate the collection of STATPACK statistics
. spcreate.sql - Installs the STATSPACK user, tables and package on a database (Run as SYS).
. spdrop.sql - Deinstall STATSPACK from database (Run as SYS)
. sppurge.sql - Delete a range of Snapshot Id's from the database
. spreport.sql - Report on differences between values recorded in two snapshots
. sptrunc.sql - Truncates all data in Statspack tables
3. What are the common RMAN errors (with solutions)? (for DBA)
Some of the common RMAN errors are:
RMAN-20242: Specification does not match any archivelog in the recovery catalog.
Add to RMAN script: sql 'alter system archive log current';
RMAN-06089: archived log xyz not found or out of sync with catalog
Execute from RMAN: change archivelog all validate;
4. How can you execute the user defined triggers in forms 3.0 ?
Execute Trigger (trigger-name)
5. What ERASE package procedure does ?
Erase removes an indicated global variable.
6. What is the difference between NAME_IN and COPY ?
Copy is package procedure and writes values into a field.
Name in is a package function and returns the contents of the variable to which you apply.
7. What package procedure is used for calling another form ?
Call (E.g. Call(formname)
8. When the form is running in DEBUG mode, If you want to examine the values of global variables and other form variables, What package procedure command you would use in your trigger text ?
Break.
SYSTEM VARIABLES
9. The value recorded in system.last_record variable is of type
a. Number
b. Boolean
c. Character. ?
b. Boolean.
10. What is mean by Program Global Area (PGA) ?
It is area in memory that is used by a Single Oracle User Process.
11. What is hit ratio ?
It is a measure of well the data cache buffer is handling requests for data. Hit Ratio = (Logical Reads - Physical Reads - Hits Misses)/ Logical Reads.
12. How do u implement the If statement in the Select Statement
We can implement the if statement in the select statement by using the Decode statement. e.g. select DECODE (EMP_CAT,'1','First','2','Second'Null); Here the Null is the else statement where null is done .
13. How many types of Exceptions are there
There are 2 types of exceptions. They are
a) System Exceptions
e.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
e.g. My_exception exception
When My_exception then
14. What are the inline and the precompiler directives
The inline and precompiler directives detect the values directly
15. How do you use the same lov for 2 columns
We can use the same lov for 2 columns by passing the return values in global values and using the global values in the code
16. How many minimum groups are required for a matrix report
The minimum number of groups in matrix report are 4
17. What is the difference between static and dynamic lov
The static lov contains the predetermined values while the dynamic lov contains values that come at run time
18. How does one manage Oracle database users? (for DBA)
Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the DB Configuration Assistant (dbca). DBA's must unlock these accounts to make them available to users.
Look at these examples:
ALTER USER scott ACCOUNT LOCK -- lock a user account
ALTER USER scott ACCOUNT UNLOCK; -- unlocks a locked users account
ALTER USER scott PASSWORD EXPIRE; -- Force user to choose a new password
19. What is the difference between DBFile Sequential and Scattered Reads?(for DBA)
Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Time is reported in 100's of a second for Oracle 8i releases and below, and 1000's of a second for Oracle 9i and above. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache.
db file sequential read:
A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the control file and reading data file headers (P2=1). In general, this event is indicative of disk contention on index reads.
db file scattered read:
Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.
The following query shows average wait time for sequential versus scattered reads:
prompt "AVERAGE WAIT TIME FOR READ REQUESTS"
select a.average_wait "SEQ READ", b.average_wait "SCAT READ"
from sys.v_$system_event a, sys.v_$system_event b
where a.event = 'db file sequential read'
and b.event = 'db file scattered read';
20. What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.
21. What is the use of TABLES option in EXP command ?
List of tables should be exported.ze)
22. What is the OPTIMAL parameter?
It is used to set the optimal length of a rollback segment.
23. How does one use ORADEBUG from Server Manager/ SQL*Plus? (for DBA)
Execute the "ORADEBUG HELP" command from svrmgrl or sqlplus to obtain a list of valid ORADEBUG commands. Look at these examples:
SQLPLUS> REM Trace SQL statements with bind variables
SQLPLUS> oradebug setospid 10121
Oracle pid: 91, Unix process pid: 10121, image: oracleorcl
SQLPLUS> oradebug EVENT 10046 trace name context forever, level 12
Statement processed.
SQLPLUS> ! vi /app/oracle/admin/orcl/bdump/ora_10121.trc
SQLPLUS> REM Trace Process Statistics
SQLPLUS> oradebug setorapid 2
Unix process pid: 1436, image: ora_pmon_orcl
SQLPLUS> oradebug procstat
Statement processed.
SQLPLUS>> oradebug TRACEFILE_NAME
/app/oracle/admin/orcl/bdump/pmon_1436.trc
SQLPLUS> REM List semaphores and shared memory segments in use
SQLPLUS> oradebug ipc
SQLPLUS> REM Dump Error Stack
SQLPLUS> oradebug setospid <pid>
SQLPLUS> oradebug event immediate trace name errorstack level 3
SQLPLUS> REM Dump Parallel Server DLM locks
SQLPLUS> oradebug lkdebug -a convlock
SQLPLUS> oradebug lkdebug -a convres
SQLPLUS> oradebug lkdebug -r <resource handle> (i.e 0x8066d338 from convres dump)
24. Are there any undocumented commands in Oracle? (for DBA)
Sure there are, but it is hard to find them. Look at these examples:
From Server Manager (Oracle7.3 and above): ORADEBUG HELP
It looks like one can change memory locations with the ORADEBUG POKE command. Anyone brave enough to test this one for us? Previously this functionality was available with ORADBX (ls -l $ORACLE_HOME/rdbms/lib/oradbx.o; make -f oracle.mk oradbx) SQL*Plus: ALTER SESSION SET CURRENT_SCHEMA = SYS
25. If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated?
Only for 10 records.
26. 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.
27. 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 .
28. Can a repeating frame be created without a data group as a base?
No
29. Is it possible to set a filter condition in a cross product group in matrix reports?
No
30. 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
31. 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.
32. 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.
33. What are the two parts of a procedure ?
Procedure Specification and Procedure Body.
34. 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
35. 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
36. 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.
37. 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.
38. 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.
39. 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.
40. 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.
41. 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.
42. 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.
43. 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
44. What are the types of SQL Statement ?
Data Definition Language :
CREATE,ALTER,DROP,TRUNCATE,REVOKE,NO AUDIT & COMMIT.
Data Manipulation Language:
INSERT,UPDATE,DELETE,LOCK
TABLE,EXPLAIN PLAN & SELECT.Transactional Control:
COMMIT & ROLLBACKSession Control: ALTERSESSION & SET
ROLESystem Control :
ALTER SYSTEM.
Oracle provide the following tools/ utilities to assist with performance monitoring and tuning:
. TKProf
. UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats monitoring
. Statspack
. Oracle Enterprise Manager - Tuning Pack
2. What is STATSPACK and how does one use it? (for DBA)
Statspack is a set of performance monitoring and reporting utilities provided by Oracle from Oracle8i and above. Statspack provides improved BSTAT/ESTAT functionality, though the old BSTAT/ESTAT scripts are still available. For more information about STATSPACK, read the documentation in file $ORACLE_HOME/rdbms/admin/spdoc.txt.
Install Statspack:
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql -- Install Statspack -
sqlplus "/ as sysdba" @spcreate.sql-- Enter tablespace names when prompted
Use Statspack:
sqlplus perfstat/perfstat
exec statspack.snap; -- Take a performance snapshots
exec statspack.snap;
o Get a list of snapshots
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;
@spreport.sql -- Enter two snapshot id's for difference report
Other Statspack Scripts:
. sppurge.sql - Purge a range of Snapshot Id's between the specified begin and end Snap Id's
. spauto.sql - Schedule a dbms_job to automate the collection of STATPACK statistics
. spcreate.sql - Installs the STATSPACK user, tables and package on a database (Run as SYS).
. spdrop.sql - Deinstall STATSPACK from database (Run as SYS)
. sppurge.sql - Delete a range of Snapshot Id's from the database
. spreport.sql - Report on differences between values recorded in two snapshots
. sptrunc.sql - Truncates all data in Statspack tables
3. What are the common RMAN errors (with solutions)? (for DBA)
Some of the common RMAN errors are:
RMAN-20242: Specification does not match any archivelog in the recovery catalog.
Add to RMAN script: sql 'alter system archive log current';
RMAN-06089: archived log xyz not found or out of sync with catalog
Execute from RMAN: change archivelog all validate;
4. How can you execute the user defined triggers in forms 3.0 ?
Execute Trigger (trigger-name)
5. What ERASE package procedure does ?
Erase removes an indicated global variable.
6. What is the difference between NAME_IN and COPY ?
Copy is package procedure and writes values into a field.
Name in is a package function and returns the contents of the variable to which you apply.
7. What package procedure is used for calling another form ?
Call (E.g. Call(formname)
8. When the form is running in DEBUG mode, If you want to examine the values of global variables and other form variables, What package procedure command you would use in your trigger text ?
Break.
SYSTEM VARIABLES
9. The value recorded in system.last_record variable is of type
a. Number
b. Boolean
c. Character. ?
b. Boolean.
10. What is mean by Program Global Area (PGA) ?
It is area in memory that is used by a Single Oracle User Process.
11. What is hit ratio ?
It is a measure of well the data cache buffer is handling requests for data. Hit Ratio = (Logical Reads - Physical Reads - Hits Misses)/ Logical Reads.
12. How do u implement the If statement in the Select Statement
We can implement the if statement in the select statement by using the Decode statement. e.g. select DECODE (EMP_CAT,'1','First','2','Second'Null); Here the Null is the else statement where null is done .
13. How many types of Exceptions are there
There are 2 types of exceptions. They are
a) System Exceptions
e.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
e.g. My_exception exception
When My_exception then
14. What are the inline and the precompiler directives
The inline and precompiler directives detect the values directly
15. How do you use the same lov for 2 columns
We can use the same lov for 2 columns by passing the return values in global values and using the global values in the code
16. How many minimum groups are required for a matrix report
The minimum number of groups in matrix report are 4
17. What is the difference between static and dynamic lov
The static lov contains the predetermined values while the dynamic lov contains values that come at run time
18. How does one manage Oracle database users? (for DBA)
Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the DB Configuration Assistant (dbca). DBA's must unlock these accounts to make them available to users.
Look at these examples:
ALTER USER scott ACCOUNT LOCK -- lock a user account
ALTER USER scott ACCOUNT UNLOCK; -- unlocks a locked users account
ALTER USER scott PASSWORD EXPIRE; -- Force user to choose a new password
19. What is the difference between DBFile Sequential and Scattered Reads?(for DBA)
Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Time is reported in 100's of a second for Oracle 8i releases and below, and 1000's of a second for Oracle 9i and above. Most people confuse these events with each other as they think of how data is read from disk. Instead they should think of how data is read into the SGA buffer cache.
db file sequential read:
A sequential read operation reads data into contiguous memory (usually a single-block read with p3=1, but can be multiple blocks). Single block I/Os are usually the result of using indexes. This event is also used for rebuilding the control file and reading data file headers (P2=1). In general, this event is indicative of disk contention on index reads.
db file scattered read:
Similar to db file sequential reads, except that the session is reading multiple data blocks and scatters them into different discontinuous buffers in the SGA. This statistic is NORMALLY indicating disk contention on full table scans. Rarely, data from full table scans could be fitted into a contiguous buffer area, these waits would then show up as sequential reads instead of scattered reads.
The following query shows average wait time for sequential versus scattered reads:
prompt "AVERAGE WAIT TIME FOR READ REQUESTS"
select a.average_wait "SEQ READ", b.average_wait "SCAT READ"
from sys.v_$system_event a, sys.v_$system_event b
where a.event = 'db file sequential read'
and b.event = 'db file scattered read';
20. What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.
21. What is the use of TABLES option in EXP command ?
List of tables should be exported.ze)
22. What is the OPTIMAL parameter?
It is used to set the optimal length of a rollback segment.
23. How does one use ORADEBUG from Server Manager/ SQL*Plus? (for DBA)
Execute the "ORADEBUG HELP" command from svrmgrl or sqlplus to obtain a list of valid ORADEBUG commands. Look at these examples:
SQLPLUS> REM Trace SQL statements with bind variables
SQLPLUS> oradebug setospid 10121
Oracle pid: 91, Unix process pid: 10121, image: oracleorcl
SQLPLUS> oradebug EVENT 10046 trace name context forever, level 12
Statement processed.
SQLPLUS> ! vi /app/oracle/admin/orcl/bdump/ora_10121.trc
SQLPLUS> REM Trace Process Statistics
SQLPLUS> oradebug setorapid 2
Unix process pid: 1436, image: ora_pmon_orcl
SQLPLUS> oradebug procstat
Statement processed.
SQLPLUS>> oradebug TRACEFILE_NAME
/app/oracle/admin/orcl/bdump/pmon_1436.trc
SQLPLUS> REM List semaphores and shared memory segments in use
SQLPLUS> oradebug ipc
SQLPLUS> REM Dump Error Stack
SQLPLUS> oradebug setospid <pid>
SQLPLUS> oradebug event immediate trace name errorstack level 3
SQLPLUS> REM Dump Parallel Server DLM locks
SQLPLUS> oradebug lkdebug -a convlock
SQLPLUS> oradebug lkdebug -a convres
SQLPLUS> oradebug lkdebug -r <resource handle> (i.e 0x8066d338 from convres dump)
24. Are there any undocumented commands in Oracle? (for DBA)
Sure there are, but it is hard to find them. Look at these examples:
From Server Manager (Oracle7.3 and above): ORADEBUG HELP
It looks like one can change memory locations with the ORADEBUG POKE command. Anyone brave enough to test this one for us? Previously this functionality was available with ORADBX (ls -l $ORACLE_HOME/rdbms/lib/oradbx.o; make -f oracle.mk oradbx) SQL*Plus: ALTER SESSION SET CURRENT_SCHEMA = SYS
25. If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated?
Only for 10 records.
26. 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.
27. 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 .
28. Can a repeating frame be created without a data group as a base?
No
29. Is it possible to set a filter condition in a cross product group in matrix reports?
No
30. 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
31. 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.
32. 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.
33. What are the two parts of a procedure ?
Procedure Specification and Procedure Body.
34. 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
35. 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
36. 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.
37. 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.
38. 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.
39. 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.
40. 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.
41. 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.
42. 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.
43. 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
44. What are the types of SQL Statement ?
Data Definition Language :
CREATE,ALTER,DROP,TRUNCATE,REVOKE,NO AUDIT & COMMIT.
Data Manipulation Language:
INSERT,UPDATE,DELETE,LOCK
TABLE,EXPLAIN PLAN & SELECT.Transactional Control:
COMMIT & ROLLBACKSession Control: ALTERSESSION & SET
ROLESystem Control :
ALTER SYSTEM.
PREVIOUS NEXT
No comments:
Post a Comment