Monday, April 11, 2011

RMAN Question & Answers


  •   RMAN is an Oracle Database client 
  •   It performs backup and recovery tasks on your databases and automates administration of your backup strategies 
  •   It greatly simplifies the dba jobs by managing the production database's backing up, restoring, and recovering database files 
  •   This tool integrates with sessions running on an Oracle database to perform a range of backup and recovery activities, including maintaining an RMAN repository of historical data about backups 
  •  There is no additional installation required for this tool 
  •   It is by default get installed with the oracle database installation 
  •   The RMAN environment consists of the utilities and databases that play a role in acking up your data 
  •   We can access RMAN through the command line or through Oracle Enterprise Manager

2) Why to use RMAN?
     RMAN gives you access to several backup and recovery techniques and features not available with user-managed backup and recovery. The most noteworthy are the following:

Automatic specification of files to include in a backup
Establishes the name and locations of all files to be backed up

Maintain backup repository
  •    Backups are recorded in the control file, which is the main repository of RMAN metadata 
  •   Additionally, you can store this metadata in a recovery catalog

Incremental backups 
  • Incremental backup stores only blocks changed since a previous backup
  • Thus, they provide more compact backups and faster recovery, thereby reducing the need to apply redo during datafile media recovery
Unused block compression: 
  In unused block compression, RMAN can skip data blocks that have never been used

Block media recovery
 We can repair a datafile with only a small number of corrupt data blocks without taking it offline or restoring it from backup

Binary compression
A binary compression mechanism integrated into Oracle Database reduces the size of backups

Encrypted backups
  RMAN uses backup encryption capabilities integrated into Oracle Database to store backup sets in an encrypted format

Corrupt block detection
RMAN checks for the block corruption before taking its backup

  •  RMAN backup and recovery operation for a target database are managed by RMAN client 
  •  RMAN uses the target database control file to gather metadata about the target database and to store information about its own operations 
  •   The RMAN client itself does not perform backup, restore, or recovery operations 
  •   When you connect the RMAN client to a target database, RMAN allocates server sessions on the target instance and directs them to perform the operations 
  •   The work of backup and recovery is performed by server sessions running on the  target database 
  •   A channel establishes a connection from the RMAN client to a target or auxiliary database instance by starting a server session on the instance 
  •   The channel reads data into memory, processes it, and writes it to the output device 
  •   When you take a database backup using RMAN, you need to connect to the target database using RMAN Client 
  •   The RMAN client can use Oracle Net to connect to a target database, so it can be located on any host that is connected to the target host through Oracle Net 
  •   For backup you need to allocate explicit or implicit channel to the target database 
  • An RMAN channel represents one stream of data to a device, and corresponds to one database server session. 
  •  This session dynamically collect information of the files from the target database control file before taking the backup or while restoring 
  •   For example if you give ' Backup database ' from RMAN, it will first get all the datafiles information from the controlfile 
  •   Then it will divide all the datafiles among the allocated channels. (Roughly equal size of work as per the datafile size) 
  •   Then it takes the backup in 2 steps

Step1:
The channel will read all the Blocks of the entire datafile to find out all the formatted blocks to backup

Note:
  RMAN do not take backup of the unformatted blocks

Step2:
  In the second step it takes back up of the formatted blocks

Example:
  • This is the best advantage of using RMAN as it only takes back up of the required blocks 
  •   Lets say in a datafile of 100 MB size, there may be only 10 MB of use full data and rest 90 MB is free then RMAN will only take backup of those 10 MB

  •   RMAN always connects to the target or auxiliary database using the SYSDBA privilege 
  •   RMAN always connects to the target or auxiliary database using the SYSDBA privilege 
  •   Its connections to a database are specified and authenticated in the same way as SQL*Plus connections to a database 
  •   The O/S user should be part of the DBA group 
  •   For remote connection it needs the password file Authentication 
  •   Target database should have the initialization parameter REMOTE_LOGIN_PASSWORDFILE set to EXCLUSIVE or SHARED

5) RMAN terminology:

A target database:
  •   An Oracle database to which RMAN is connected with the TARGET keyword 
  •   A target database is a database on which RMAN is performing backup and recovery operations 
  •   RMAN always maintains metadata about its operations on a database in the control file of the database

A recovery Catalog:
  •   A separate database schema used to record RMAN activity against one or more target databases 
  •   A recovery catalog preserves RMAN repository metadata if the control file is lost, making it much easier to restore and recover following the loss of the control file 
  •   The database may overwrite older records in the control file, but RMAN maintains records forever in the catalog unless deleted by the user

Backup sets:
  • RMAN can store backup data in a logical structure called a backup set, which is the smallest unit of an RMAN backup 
  •   One backup set contains one or more datafiles a section of datafile or archivelogs

Backup Piece:
  •  A backup set contains one or more binary files in an RMAN-specific format 
  •   This file is known as a backup piece 
  •   Each backup piece is a single output file 
  •   The size of a backup piece can be restricted; if the size is not restricted, the backup set will comprise one backup piece 
  •   Backup piece size should be restricted to no larger than the maximum file size that your filesystem will support

Image copies:
  • An image copy is a copy of a single file (datafile, archivelog, or controlfile) 
  •   It is very similar to an O/S copy of the file 
  •   It is not a backupset or a backup piece 
  •   No compression is performed

Snapshot Controlfile:
  • When RMAN needs to resynchronize from a read-consistent version of the control file, it creates a temporary snapshot control file 
  •   The default name for the snapshot control file is port-specific

Database Incarnation:
  • Whenever you perform incomplete recovery or perform recovery using a backup control file, you must reset the online redo logs when you open the database 
  •   The new version of the reset database is called a new incarnation 
  •   The reset database command directs RMAN to create a new database incarnation record in the recovery catalog 
  •   This new incarnation record indicates the current incarnation
  

6) What is RMAN Configuration and how to configure it?

  • The RMAN backup and recovery environment is preconfigured for each target database 
  •   The configuration is persistent and applies to all subsequent operations on this target database, even if you exit and restart RMAN 
  •   RMAN configured settings can specify backup devices, configure a connection to a backup device , policies affecting backup strategy, encryption algorithm, snap shot controlfile loaion  and others 
  •   By default there are few default configuration are set when you login to RMAN 
  •   You can customize them as per your requirement 
  •   Any time you can check the current setting by using the "Show all” command 
  •   CONFIGURE command is used to create persistent settings in the RMAN environment, which apply to all subsequent operations, even if you exit and restart RMAN

7) How to check RMAN configuration?
RMAN>Show all;

8) How to reset to default configuration?
To reset the default configuration setting use Connect to the target database from sqlplus and run
SQL> connect <sys/passwd as sysdba>@target_database; 
SQL> execute dbms_backup_restore.resetConfig

RMAN Catalog Database

9) What is Catalog database and How to configure it?
 This is a separate database which contains catalog schema
  You can use the same target database as the catalog database but it’s not at all recommended

10) How Many catalog database I can have?

  • You can have multiple catalog databases for the same target database 
  •   But at a time you can connect to only 1 catalog database via RMAN. Its not recommended to have multiple catalog database

11) Is this mandatory to use catalog database?
       No! It’s an optional one



12) What is the advantage of catalog database?
  •  Catalog database is a secondary storage of backup metadata 
  •   It’s very useful in case you lost the current controlfile, as all the backup information are there in the catalog schema 
  •   Secondly from contolfile the older backup information are aged out depending upon the control_file_record_keep_time 
  •   RMAN catalog database mainten the history of data

13) What is the difference between catalog database & catalog schema?
Catalog database is like any other database which contains the RMAN catalog user's schema


  • Since catalog database is an optional there is no direct effect of loss of catalog database 
  •   Create a new catalog database and register the target database with the newly created catalog one All the backup information from the target database current controlfile will be updated to the catalog schema 
  •   If any backup information which is aged out from the target database then you need to manually catalog those backup pieces

RMAN backup:

15)  What are the database file's that RMAN can backup?
 RMAN can backup Controlfile, Datafiles, Archive logs, standby database controfile, Spfile

16) What are the database file's that RMAN cannot backup?
RMAN can not take backup of the pfile, Redo logs, network configuration files, password files, external tables and the contents of the Oracle home files

17) Can I have archivelogs and datafile backup in a single backupset?

    No.  We can not put datafiles and archive logs in the same backupset

  •  Yes 
  •   If the controlfile autobackup is not ON then RMAN takes backup of controlfile along with the datafile 1, whenever you take backup of the database or System tablespace

19) Can I regulate the size of backup piece and backup set?
  •  Yes! 
  •   You can set max size of the backupset as well as the backup piece 
  •   By default one RMAN channel creates a single backupset with one backup piece in it 
  •   You can use the MAXPIECESIZE channel parameter to set limits on the size of backup pieces 
  •   You can also use the MAXSETSIZE parameter on the BACKUP and CONFIGURE commands to set a limit for the size of backup sets

20) What is the difference between backup set backup and Image copy backup?

  •  A backup set is an RMAN-specific proprietary format, whereas an image copy is a bit-for-bit copy of a file 
  •   By default, RMAN creates backup sets

21) What is RMAN consistent backup and inconsistent backup?

  •  A consistent backup occurs when the database is in a consistent state 
  •   That means backup of the database taken after a shutdown immediate, shutdown normal or shutdown transactional 
  •   If the database is shutdown with abort option then its not a consistent backup 
  • A backup when the database is up and running is called an inconsistent backup 
  •   When a database is restored from an inconsistent backup, Oracle must perform media recovery before the database can be opened, applying any pending changes from the redo logs 
  •   You can not take inconsistent backup when the database is in Noarchivelog mode

22)  Can I take RMAN backup when the database is down?

   No!
   You can take RMAN backup only when the target database is Open or in Mount stage
   It’s because RMAN keep the backup metadata in controfile
   Only in open or mount mode controlfile is accessible

23)  Do I need to place the database in begin backup mode while taking RMAN inconsistent backup?

       RMAN does not require extra logging or backup mode because it knows the format of data blocks
       RMAN is guaranteed not to back up fractured blocks
       No extra redo is generated during RMAN backup


24) Can I compress RMAN backups?

ü  RMAN supports binary compression of backup sets
ü  The supported algorithms are BZIP2 (default) and ZLIB 
ü  It’s not recommended to compress the RMAN backup using any other OS or third party utility

Note:
ü  RMAN compressed backup with BZIP2 provides great compression but is CPU intensive
ü  Using ZLIB compression requires the Oracle Database 11g Advanced Compression Option and is only supported with an 11g database
ü  The feature is not backward compatible with 10g databases

25) Can I encrypt RMAN backup?

ü  RMAN supports backup encryption for backup sets
ü  You can use wallet-based transparent encryption, password-based encryption, or both
ü  You can use the CONFIGURE ENCRYPTION command to configure persistent transparent encryption
ü  Use the SET ENCRYPTION, command at the RMAN session level to specify password-based encryption

26)  Can RMAN take backup to Tape?

ü  Yes!
ü  We can use RMAN for the tape backup
ü  But RMAN can not able to write directly to tape
ü  You need to have third party Media Management Software installed
ü  Oracle has published an API specification which Media Management Vendor's who are members of Oracle's Backup Solutions Partner program have access to
ü  Media Management Vendors (MMVs) then write an interface library which the Oracle server uses to write and read to and from tape
Starting from oracle 10g R2 oracle has its Own Media management software for the database backup to tape called OSB


27) How RMAN Interact with Media manager?

ü  Before performing backup or restore to a media manager, you must allocate one or more channels or configure default channels for use with the media manager to handle the communication with the media manager
ü  RMAN does not issue specific commands to load, label, or unload tapes
ü  When backing up, RMAN gives the media manager a stream of bytes and associates a unique name with this stream
ü  When RMAN needs to restore the backup, it asks the media manager to retrieve the byte stream
ü  All details of how and where that stream is stored are handled entirely by the media manager

28) What is Proxy copy backup to tape?

ü  Proxy copy is functionality, supported by few media manager in which they handle the entire data movement between datafiles and the backup devices
ü  Such products may use technologies such as high-speed connections between storage and media subsystems to reduce load on the primary database server
ü  RMAN provides a list of files requiring backup or restore to the media manager, which in turn makes all decisions regarding how and when to move the data

29) What is Oracle Secure backup?

ü  Oracle Secure Backup is a media manager provided by oracle that provides reliable and secure data protection through file system backup to tape
ü  All major tape drives and tape libraries in SAN, Gigabit Ethernet, and SCSI environments are supported

30) Can I restore or duplicate my previous version database using a later version of Oracle?
For example, is it possible to restore a 9i backup while using the 10g executables?

It is possible to use the 10.2 RMAN executable to restore a 9.2 database (same for 11.2 to 11.1 or 11.1 to 10.2, etc) even if the restored datafiles will be stored in ASM
RMAN is configured so that a higher release is able to restore a lower release, but it is strongly suggested you use only the same version

31) Can I restore or duplicate between two different patchset levels?

ü  As you can restore between different Oracle versions, you can also do so between two different patchset levels
Alter database open resetlogs upgrade;
OR
alter database open resetlogs downgrade;

32) Can I restore or duplicate between two different versions of the same operating system?

For example, can I restore my 9.2.0.1.0 RMAN backup taken against a host running Solaris 9 to a different machine where 9.2.0.1.0 is installed but where that host is running Solaris 10?

 If the same Oracle Server installation CDs (media pack) can be used to install 9.2.0.1.0 on Solaris 9 and Solaris 10, this type of restore is supportable

 

33) Is it possible to restore or duplicate when the bit level (32 bit or 64 bit) of Oracle does not match? 

For example, is it possible to restore or duplicate my 9.2. 64-bit database to a 9.2.32-bit installation?
  •  It is preferable to keep the same bit version when performing a restore/recovery 
  •   However, excluding the use of duplicate command, the use of the same operating system platform should allow for a restore/recovery between bit levels (32 bit or 64 bit) of Oracle 
  •   Note, this may be specific to the particular operating system and any problems with this should be reported to Oracle Support 
  •   If you will be running the 64-bit database against the 32-bit binary files or vice versa, after the recovery has ended the database bit version must be converted using utlirp.sql
  • If you do not run utlirp.sql you will see errors including but not limited to:
ORA-06553: PLS-801: INTERNAL ERROR [56319]

 

34) Can I restore or duplicate my RMAN backup between two different platforms such as Solaris to Linux?

In general, you cannot restore or duplicate between two different platforms

35) What are the corruption types?

ü  Datafile Block Corruption - Physical/Logical
ü  Table/Index Inconsistency
ü  Extents Inconsistencies
ü  Data Dictionary Inconsistencies


Scenarios:

Goal: How to identify all the corrupted segments in the database reported by RMAN?

Solution:

Step 1: Identify the corrupt blocks (Datafile Block Corruption - Intra block corruption)

RMAN> backup validate check logical database;

To make it faster, it can be configured to use PARALLELISM with multiple channels:

RMAN> run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
backup validate check logical database;
}

Step2:  Using the view v$database_block_corruption:
SQL> select * from v$database_block_corruption;

          FILE#          BLOCK#          BLOCKS CORRUPTION_CHANGE# CORRUPTIO
------------------------------------------------------------------------------------------------------------
              6              10                          1          8183236781662                      LOGICAL
              6              42                          1                  0                                      FRACTURED
              6              34                          2                  0                                      CHECKSUM
              6              50                          1      8183236781952                          LOGICAL
              6              26                          4                  0                                      FRACTURED

5 rows selected.


Datafile Block Corruption - Intra block corruption

  • It refers to intra block corruptions that may cause different errors like ORA-1578, ORA-8103, ORA-1410, ORA-600 etc. 
  •   Oracle classifies the corruptions as Physical and Logical
ü  To identify both Physical and Logical Block Corruptions use the "CHECK LOGICAL" option
ü  It checks the complete database for both corruptions without actually doing a backup

Solution1:


$ rman target /
RMAN> backup check logical validate database;

$ rman target /
RMAN> backup check logical database;

Solution2:

ü  Chek the view V$DATABASE_BLOCK_CORRUPTION to identify the block corruptions detected by RMAN

Solution3: DBVerify - Identify Datafile Block Corruptions

ü  DBVERIFY identify Physical and Logical Intra Block Corruptions by default
ü  Dbverify cannot be run for the whole database in a single command
ü  It does not need a database connection either

dbv file=<datafile name> blocksize=<datafile Block size>

RMAN Vs DBVerify - Datafile Intra Block Corruption

When the logical option is used by RMAN, it does exactly the same checks as DBV does for intra block corruption.
RMAN can be run with PARALLELISM using multiple channels making it faster than DBV which can not be run in parallel in a single command
DBV checks for empty blocks. In 10g RMAN may not check blocks in free extents when Locally Managed Tablespaces are used. In 11g RMAN checks for both free and used extents.
  • Both DBV and RMAN (11g) can check for a range of blocks. RMAN: VALIDATE DATAFILE 1 BLOCK 10 to 100;.  DBV: start=10 end=100
  • RMAN keeps corruption information in the control file (v$database_block_corruption, v$backup_corruption). DBV does not. 
  • RMAN may not report the corruption details like what is exactly corrupted in a block reported as a LOGICAL corrupted block. DBV reports the corruption details in the screen or in a log file.
  • DBV can scan blocks with a higher SCN than a given SCN.
  • DBV does not need a connection to the database.

dentify TABLE / INDEX Inconsistency
Table / Index inconsistencies is when an entry in the Table does not exist in the Index or vice versa. The common errors are ORA-8102, ORA-600 [kdsgrp1], ORA-1499 by "analyze validate structure cascade".
  • The tool to identify TABLE / INDEX inconsistencies is the ANALYZE command:
analyze table <table name> validate structure cascade;

When an inconsistency is identified, the above analyze command will produce error ORA-1499 and a trace file.



35) What Happens When A Tablespace/Database Is Kept In Begin Backup Mode?

ü  One danger in making online backups is the possibility of inconsistent data within a block
ü  For example, assume that you are backing up block 100 in datafile users.dbf
ü  Also, assume that the copy utility reads the entire block while DBWR is in the middle of updating the block
ü  In this case, the copy utility may read the old data in the top half of the block and the new data in the bottom top half of the block
ü  The result is called a fractured block, meaning that the data contained in this block is not consistent at a given SCN

Therefore oracle internally manages the consistency as below : 
The first time a block is changed in a datafile that is in hot backup mode, the entire block is written to the redo log files, not just the changed bytes
Normally only the changed bytes (a redo vector) is written
In hot backup mode, the entire block is logged the first time
This is because you can get into a situation where the process copying the datafile and DBWR are working on the same block simultaneously
Lets say they are and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time). The backup program goes to read an 8k Oracle block. The OS gives it 4k. Meanwhile -- DBWR has asked to rewrite this block. the OS schedules the DBWR write to occur right now. The entire 8k block is rewritten. The backup program starts running again (multi-tasking OS here) and reads the last 4k of the block. The backup program has now gotten an fractured block -- the head and tail are from two points in time.
We cannot deal with that during recovery. Hence, we log the entire block image so that during recovery, this block is totally rewritten from redo and is consistent with itself atleast. We can recover it from there.

2.  The datafile headers which contain the SCN of the last completed checkpoint are not updated while a file is in hot backup mode. This lets the recovery process understand what archive redo log files might be needed to fully recover this file.

To limit the effect of this additional logging, you should ensure you only place one tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon as you have backed it up. This will reduce the number of blocks that may have to be logged to the minimum possible. 

Try to take the hot/online backups when there is less / no load on the database, so that less redo will be generated.

NOTE: The above information is gathered for my understanding,preparation and for my reference only through some web sources  



23 comments:

Unknown said...

Thanks for posting such a interesting blog and it is really informative. If you are interested in taking java as a professional carrier visit this website.JAVA Training in Chennai

Unknown said...

Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

iOS Training in Chennai

yasar said...

very nice and informative blog
big data projects chennai
mobile computing projects chennai
cloud computing projects chennai
secure computing projects chennai

Unknown said...

This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information&its very useful to me...
Android training in chennai
Ios training in chennai

vignesjoseph said...

we sharing now interview question for Oracle data Base. Oracle is relation management system it's product by Oracle Corporation.Many of the Corporate company database for storing and managing their data's and programs.If you want to be learning from Hadoop Developer reached and visit there for below web page.Hadoop Training in Chennai
Hadoop Training

Sujitkumar said...

Nice blog. Really helpful information about Oracle …. Please keep update some more…………

Luckperson said...

Hi, You have shared really very great post. Thanks for your great effort to shared interview question and answer with explanation.

DBA course | Oracle dba course

kevingeorge said...

I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site.
Best Java Training Institute Chennai



Java Training Institute Bangalore

Unknown said...

I believe there are many more pleasurable opportunities ahead for individuals that looked at your site.

Hadoop Training institute in Bangalore

Unknown said...

Thanks a lot very much for the high quality and results-oriented help. I won’t think twice to endorse your blog post to anybody who wants and needs support about this area.
mean-stack-training-institute-in-chennai

priyanka said...

Thank you a lot for providing individuals with a very spectacular possibility to read critical reviews from this site.
Best selenium training Institute in chennai

Unknown said...

it is really amazing...thanks for sharing....provide more useful information...

Embedded training in chennai | Embedded training centre in chennai | Embedded system training in chennai | PLC Training institute in chennai | IEEE final year projects in chennai | VLSI training institute in chennai

Unknown said...

At this time, it seems like WordPress is the preferred blogging platform available right now. (from what I’ve read) Is that what you’re using on your blog? Great post, however, I was wondering if you could write a little more on this subject?

DevOps Training in Chennai

Unknown said...

Some us know all relating to the compelling medium you present powerful steps on this blog and therefore strongly encourage contribution from other ones on this subject while our own child is truly discovering a great deal. Have fun with the remaining portion of the year.

Digital Marketing Training in Chennai

Unknown said...

I am really happy with your blog because your article is very unique and powerful for new reader.
Click here:
Selenium Training in Chennai | Selenium Training in Bangalore | Selenium Training in Pune

DeepikaOrange said...

Marvelous and fascinating article. Incredible things you've generally imparted to us. Much obliged. Simply keep making this kind out of the post.

Oracle DBA Training
Oracle PLSQL Training
Oracle Performance Tunning Training

gowthunan said...

I enjoy what you guys are usually up too. This sort of clever work and coverage! Keep up the wonderful works guys I’ve added you guys to my blog roll.
nebosh course in chennai

Unknown said...

Those guidelines additionally worked to become a good way to recognize that other people online have the identical fervor like mine to grasp great deal more around this condition.
python training in chennai
java training in chennai

gowthunan said...

I wish to indicate because of you only to bail me out of this specific trouble. As a consequence of checking through the net and meeting systems that were not beneficial, I thought my life was finished.
iosh course in chennai

gowthunan said...

I accept there are numerous more pleasurable open doors ahead for people that took a gander at your site.
iosh safety course in chennai

gowthunan said...

I just needed to record a speedy word to express profound gratitude to you for those magnificent tips and clues you are appearing on this site.
iosh safety course in chennai

Shanthi Cabs said...

Amazing article. Your blog helped me to improve myself in many ways thanks for sharing this kind of wonderful informative blogs in live. I have bookmarked more article from this website. Such a nice blog you are providing ! Kindly Visit Us @ Best Travels in Madurai | Tours and Travels in Madurai | Madurai Travels

akalya said...

nice..

internship for bba students

internship certificate for cse students

internship training in chennai

internships in hyderabad for cse students 2020

r training in chennai

robotics course

internship for ece students in core companies

internship for aeronautical engineering students in bangalore

internship for cse students in bangalore 2019

industrial visits for college students in chennai

Post a Comment

Auto Scroll Stop Scroll