WHY USE RMAN ? (for DBA
ORACLE Interview Questions and Answers (Part 5)
(Continued from previous question...)
850. 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
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
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
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
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.
(Continued on next question...)
Other Interview Questions
- Is it possible to split the print reviewer into more than one region?
- Is it possible to center an object horizontally in a repeating frame that has a variable horizontal size?
- For a field in a repeating frame, can the source come from the column which does not exist in the data group which forms the base for the frame?
- Can a field be used in a report without it appearing in any data group?
- The join defined by the default data link is an outer join yes or no?
- Can a formula column referred to columns in higher group?
- Can a formula column be obtained through a select statement?
- Is it possible to insert comments into sql statements return in the data model editor?
- Is it possible to disable the parameter from while running the report?
- When a form is invoked with call_form, Does oracle forms issues a save point?
- Explain the difference between a hot backup and a cold backup and the benefits associated with each.
- You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?
- How do you switch from an init.ora file to a spfile?
- Explain the difference between a data block, an extent and a segment.
- Give two examples of how you might determine the structure of the table DEPT.
- Where would you look for errors from the database engine?
- Compare and contrast TRUNCATE and DELETE for a table.
- Give the reasoning behind using an index.
- Give the two types of tables involved in producing a star schema and the type of data they hold.
- What type of index should you use on a fact table?
- Give two examples of referential integrity constraints.
- A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
- Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
- What command would you use to create a backup control file?
- Give the stages of instance startup to a usable state where normal users may access it.
- What column differentiates the V$ views to the GV$ views and how?
- How would you go about generating an EXPLAIN plan?
- How would you go about increasing the buffer cache hit ratio?
- Explain an ORA-01555
- Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
- How would you determine the time zone under which a database was operating?
- Explain the use of setting GLOBAL_NAMES equal to TRUE.
- What command would you use to encrypt a PL/SQL application?
- Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
- Explain the use of table functions.
- Name three advisory statistics you can collect.
- Where in the Oracle directory tree structure are audit traces placed?
- Explain materialized views and how they are used.
- When a user process fails, what background process cleans up after it?
- What background process refreshes materialized views?
- How would you determine what sessions are connected and what resources they are waiting for?
- Describe what redo logs are.
- How would you force a log switch?
- Give two methods you could use to determine what DDL changes have been made.
- What does coalescing a tablespace do?
- What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?
- Name a tablespace automatically created when you create a database.
- When creating a user, what permissions must you grant to allow them to connect to the database?
- How do you add a data file to a tablespace
- How do you resize a data file?
- What view would you use to look at the size of a data file?
- What view would you use to determine free space in a tablespace?
- How would you determine who has added a row to a table?
- How can you rebuild an index?
- Explain what partitioning is and what its benefit is.
- You have just compiled a PL/SQL package but got errors, how would you view the errors?
- How can you gather statistics on a table?
- How can you enable a trace for a session?
- What is the difference between the SQL*Loader and IMPORT utilities?
- Name two files used for network connection to a database.
- What is the function of Optimizer ?
- What is Execution Plan ?
- Can one resize tablespaces and data files? (for DBA)
- What is SAVE POINT ?
- What are the values that can be specified for OPTIMIZER MODE Parameter ?
- Can one rename a tablespace? (for DBA)
- What is RULE-based approach to optimization ?
- What are the values that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command ? )
- How does one create a standby database? (for DBA)
- How does one give developers access to trace files (required as input to tkprof)? (for DBA)
- What are the responsibilities of a Database Administrator ?
- What is a trace file and how is it created ?
- What are the roles and user accounts created automatically with the database?
- What are the minimum parameters should exist in the parameter file (init.ora) ?
- Why and when should I backup my database? (for DBA
- What strategies are available for backing-up an Oracle database? (for DBA
- What is the difference between online and offline backups? (for DBA
- What is the difference between restoring and recovering? (for DBA
- How does one backup a database using the export utility? (for DBA
- What are the built_ins used the display the LOV?
- How do you call other Oracle Products from Oracle Forms?
- What is the main diff. bet. Reports 2.0 & Reports 2.5?
- What are the Built-ins to display the user-named editor?
- How many number of columns a record group can have?
- What is a Query Record Group?
- What does the term panel refer to with regard to pages?
- What is a master detail relationship?
- What is a library?
- What is an anchoring object and what is its use? What are the various sub events a mouse double click event involves?
- Use the add_group_column function to add a column to record group that was created at a design time?
- What are the various sub events a mouse double click event involves? What are the various sub events a mouse double click event involves?
- What is the use of break group? What are the various sub events a mouse double click event involves?
- What tuning indicators can one use? (for DBA
- 813. What tools/utilities does Oracle provide to assist with performance tuning? (for DBA
- What is STATSPACK and how does one use it? (for DBA
- What are the common RMAN errors (with solutions)? (for DBA
- How can you execute the user defined triggers in forms 3.0 ?
- What ERASE package procedure does ?
- What is the difference between NAME_IN and COPY ?
- What package procedure is used for calling another form ?
- 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 ?
- The value recorded in system.last_record variable is of type a. Number b. Boolean c. Character. ?
- What is mean by Program Global Area (PGA) ?
- What is hit ratio ?
- How do you implement the If statement in the Select Statement
- How many types of Exceptions are there
- What are the inline and the precompiler directives
- How do you use the same lov for 2 columns
- How many minimum groups are required for a matrix report
- What is the difference between static and dynamic lov
- How does one manage Oracle database users? (for DBA
- 831. How does one tune Oracle Wait events? (for DBA
- What is the difference between DBFile Sequential and Scattered Reads?(for DBA
- What is the use of PARFILE option in EXP command ?
- What is the use of TABLES option in EXP command ?
- What is the OPTIMAL parameter?
- How does one use ORADEBUG from Server Manager/ SQL*Plus? (for DBA
- Are there any undocumented commands in Oracle? (for DBA
- If the maximum record retrieved property of the query is set to 10 then a summary value will be calculated?
- What are the different objects that you cannot copy or reference in object groups?
- What is an OLE?
- Can a repeating frame be created without a data group as a base?
- Is it possible to set a filter condition in a cross product group in matrix reports?
- What is Overloading of procedures ?
- What are the return values of functions SQLCODE and SQLERRM ? What is Pragma EXECPTION_INIT ? Explain the usage ?
- What are the datatypes a available in PL/SQL ?
- What are the two parts of a procedure ?
- What is the basic structure of PL/SQL ?
- What is PL/SQL table ?
- WHAT IS RMAN ? (for DBA
- WHY USE RMAN ? (for DBA
- Explain UNION,MINUS,UNION ALL, INTERSECT ?
- Should the OEM Console be displayed at all times (when there are scheduled jobs)? (for DBA
- Difference between SUBSTR and INSTR ?
- What kind of jobs can one schedule with OEM? (for DBA
- What are the pre requisites ?
- How does one backout events and jobs during maintenance slots? (for DBA
- What are the types of SQL Statement ?
- What is the Oracle Intelligent Agent? (for DBA
- How does one start the Oracle Intelligent Agent? (for DBA
- Where can one get more information about TCL? (for DBA
- Are there any troubleshooting tips for OEM? (for DBA
- What is import/export and why does one need it? (for DBA
- what is a display item?
- How does one use the import/export utilities? (for DBA
- What are the types of visual attribute settings?
- Can one export a subset of a table? (for DBA
- What are the two ways to incorporate images into a oracle forms application?
- Can one monitor how fast a table is imported? (for DBA
- Can one import tables to a different tablespace? (for DBA
- What do you mean by a block in forms4.0?
- How is possible to restrict the user to a list of values while entering values for parameters?
- What is SQL*Loader and what is it used for? (for DBA
- How does one use the SQL*Loader utility? (for DBA
- How can a cross product be created?
- Is there a SQL*Unloader to download data to a flat file? (for DBA
- Can one load variable and fix length data records? (for DBA
- Can one modify data as it loads into the database? (for DBA
- Can one load data into multiple tables at once? (for DBA
- What is the difference between boiler plat images and image items?
- What are the triggers available in the reports?
- Why is a Where clause faster than a group filter or a format trigger?
- Can one selectively load only the records that one need? (for DBA
- Can one skip certain columns while loading data? (for DBA
- How does one load multi-line records? (for DBA
- How can get SQL*Loader to COMMIT only at the end of the load file? (for DBA
- Can one improve the performance of SQL*Loader? (for DBA
- How does one use SQL*Loader to load images, sound clips and documents? (for DBA
- What is the difference between the conventional and direct path loader? (for DBA
- What are the various types of Exceptions ?
- Can we define exceptions twice in same block ?
- What is the difference between a procedure and a function ?
- Can you have two functions with the same name in a PL/SQL block ?
- Can you have two stored functions with the same name ?
- Can you call a stored function in the constraint of a table ?
- What are the various types of parameter modes in a procedure ?
- What is Over Loading and what are its restrictions ?
- Can functions be overloaded ?
- Can 2 functions have same name & input parameters but differ only by return datatype
- What are the constructs of a procedure, function or a package ?
- Why Create or Replace and not Drop and recreate procedures ?
- Can you pass parameters in packages ? How ?
- What are the parts of a database trigger ?
- What are the various types of database triggers ?
- What is the advantage of a stored procedure over a database trigger ?
- What is the maximum no. of statements that can be specified in a trigger statement ?
- Can views be specified in a trigger statement ?
- What are the values of :new and :old in Insert/Delete/Update Triggers ?
- What are cascading triggers? What is the maximum no of cascading triggers at a time?
- What are mutating triggers ?
- What are constraining triggers ?
- Describe Oracle database's physical and logical structure ?
- Can you increase the size of a tablespace ? How ?
- What is the use of Control files ?
- What is the use of Data Dictionary ?
- What are the advantages of clusters ?
- What are the disadvantages of clusters ?
- Can Long/Long RAW be clustered ?
- Can null keys be entered in cluster index, normal index ?
- Can Check constraint be used for self referential integrity ? How ?
- What are the min. extents allocated to a rollback extent ?
- What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
- What is the difference between unique key and primary key ?
- An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
- Can you define multiple savepoints ?
- Can you Rollback to any savepoint ?
- What is the maximum no. of columns a table can have ?
- What is the significance of the & and && operators in PL SQL ?
- Can you pass a parameter to a cursor ?
- What are the various types of RollBack Segments ?
- Can you use %RowCount as a parameter to a cursor ?
- Is the query below allowed : Select sal, ename Into x From emp Where ename = 'KING' (Where x is a record of Number(4) and Char(15))
- Is the assignment given below allowed : ABC = PQR (Where ABC and PQR are records)
- Is this for loop allowed : For x in &Start..&End Loop
- How many rows will the following SQL return : Select * from emp Where rownum
- How many rows will the following SQL return : Select * from emp Where rownum = 10;
- Which symbol preceeds the path to the table in the remote database ?
- Are views automatically updated when base tables are updated ?
- Can a trigger written for a view ?
- If all the values from a cursor have been fetched and another fetch is issued, the output will be : error, last record or first record ?
- A table has the following data : [[5, Null, 10]]. What will the average function return ?
- Is Sysdate a system variable or a system function?
- Consider a sequence whose currval is 1 and gets incremented by 1 ...
- Definition of relational DataBase by Dr. Codd (IBM)?
- What is Multi Threaded Server (MTA) ?
- Which are initial RDBMS, Hierarchical & N/w database ?
- What is Functional Dependency
- What is Auditing ?
- While designing in client/server what are the 2 imp. things to be considered ?
- When to create indexes ?
- How can you avoid indexes ?
- What is the result of the following SQL ...
- Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
- Can you alter synonym of view or view ?
- Can you create index on view
- What is the difference between a view and a synonym ?
- What is the difference between alias and synonym ?
- What is the effect of synonym and table name used in same Select statement ?
- What's the length of SQL integer ?
- What is the difference between foreign key and reference key ?
- Can dual table be deleted, dropped or altered or updated or inserted ?
- If content of dual is updated to some value computation takes place or not ?
- If any other table same as dual is created would it act similar to dual?
- For which relational operators in where clause, index is not used ?
- Assume that there are multiple databases running on one machine. How can you switch from one to another ?
- What are the advantages of Oracle ?
- What is a forward declaration ? What is its use ?
- What are actual and formal parameters ?
- What are the types of Notation ?
- What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size ?
- If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ?
- What are various types of joins ?
- What is a package cursor ?
- If you insert a row in a table, then create another table and then say Rollback. In this case will the row be inserted ?
- What are the various types of queries ??
- What is a transaction ?
- What is implicit cursor and how is it used by Oracle ?
- Which of the following is not a schema object : Indexes, tables, public synonyms, triggers and packages ?
- What is PL/SQL?
- Is there a PL/SQL Engine in SQL*Plus?
- Is there a limit on the size of a PL/SQL block?
- Can one read/write files from PL/SQL?
- How can I protect my PL/SQL source code?
- ORACLE Interview Questions and Answers (Part 5)
- Oracel Interview Question Only (1)
- Oracel Interview Question Only (2)