RMAN Interview Questions & Answers
Can you explain what is current control file? if we are using multiplexed control files, then which is current control file (CONTROL01.CTL,CONTROL02.CTL or CONTROL03.CTL)? RMAN take which controlfile backup?
Ans) CONTROL01.CTL, CONTROL02.CTL and CONTROL03.CTL are mirrors of each other. Logically, it is one control file with 3 physical mirrors. This logical 1 controlfile is the CURRENT CONTROLFILE. Oracle may copy any one of three mirrors.
What is SCN?
Ans) The SCN is an Oracle server–assigned number that indicates a committed version of the database. It’s quite possible that different datafiles in the database might have a different SCN at any given point in time.
At checkpoint, the server will makes all database file SCNs and control file SCN consistent with respect to an identical SCN.The datafiles will not contain any database changes beyond that common SCN. This synchronization of the SCNs will make sure we have a consistent backup of database.
When you are doing hot backup, you may end up with backups of the various datafiles at various time points and different SCNs and you can not open a database without synchronizing the SCN on all data files, so you will have to apply archive logs to make the data current and synchronize the SCNs across the datafiles.
What is the significance of fast_start_mttr_target parameter?
Ans) You use the Oracle initialization parameter fast_start_mttr_target to specify the number of seconds you want the crash recovery to take. Oracle will try to recover the
instance as close as possible to the time that you specify for the fast_start_mttr_target parameter. The maximum value of this parameter is 3600 seconds (1 hour).
During instance recovery, in the first roll forward operation, the database server must apply all transactions between the last checkpoint and the end of the redo log to the datafiles. Thus, in order to tune instance recovery, you control the gap between the checkpoint position and the end of the redo log. This is called Mean Time to Recover (MTTR).
There was a media failure. How can you find which files you must recover?
Ans) By querying the V$RECOVER_FILE view, which lists all files that need media recovery.
What are the benefits of RMAN over user-managed backup-recovery process?
Ans)
– powerful Data Recovery Advisor feature
– simpler backup and recovery commands
– automatically manages the backup files without DBA intervention.
– automatically deletes unnecessary backup datafiles and archived redo log files both from disk and tape.
– provides you with detailed reporting of backup actions
– Easy to duplicate a database or create standby database.
– Without actually restoring data, you can test whether you will be able to do it or not
– Incremental backup! only RMAN can do that.
– Active Duplication! (11g new feature), you can perform database duplication without backups by using the network enabled database duplication feature
– automatically detects corrupt data blocks during backups
– Data block level recovery possible in case only few DB blocks are corrupt
– Encrypted backups possible
– Use RMAN with a variety of third-party storage systems easily
With so many benefits, RMAN shold be used as primary backup-recovery tool.
How important is Database Redundancy Set and where you should plan to keep it?
Ans) Database Redundancy Set is essential set of recovery-related files. As a DBA, you need to be well prepared for any kind of contingency situation.
It should contain below:
– Recent backups of all datafiles & control file (duplex or triplex the control file at database level)
– All archived redo logs made after the last backup
– Current control files and online redo file copies (duplex online redo files at database level)
– Oracle database-related configuration file copies (spfile, password file, tnsnames.ora and listener.ora files etc)
Operating system mirroring is good, but you should do database level mirroring wherever possible.
If you using ASM, try to have atleast Normal redundancy.
When setting up production systems, use at least two disk drives(one for the redundancy set and the other for the datafiles). They should be completely separated by using different volumes, file systems, disk controllers, and RAID devices to hold the two sets of files
You can set up FRA for keeping the redundancy set. Oracle recommends the flash recovery area as a logical candidate to keep a copy of all the files belonging to the redundancy set (which includes the most recent database backup) on disk.
What is the benefit of making automatic control file backup to ON?
Ans) Remember that control file is absolutely necessary during a recovery.
Below command can be used to have automatic control file backup to be taken. This is highly recommended.
RMAN> configure controlfile autobackup on
Now at the end of every RMAN backup command, RMAN automatically backs up the control file.
Even when you make some changes via SQL*Plus( say creating a new tablespace or adding or renaming a datafile or an online redo log member), the control file is automatically backed up.
Also, you can restore RMAN’s backup and recovery information (called RMAN’s repository), when you lose all your control files and aren’t using the optional recovery catalog.
What is the significance of RMAN view V$RMAN_OUTPUT?
Ans) V$RMAN_OUTPUT displays messages reported by RMAN. This is an in-memory view (means will not persist thru a database restart)and is not recorded in the controlfile. This information straight out of the database by using SQL queries.
Sometimes RMAN log files may have already been overwritten by the next backup or simply just deleted. This is an alternative way of accessing the RMAN output information
For Example:
Review the last two days worth of rman output:
select output from v$rman_output where session_recid in (select session_recid from v$rman_status
where start_time > sysdate-2) order by recid ;
OUTPUT
———————————————————————————————————————————-
connected to target database: BRIJ (DBID=3142459675)
using target database control file instead of recovery catalog
echo set on
backup archivelog all not backed up delete all input;
Starting backup at 09-FEB-14
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=124 instance=BRIJ devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=126 instance=BRIJ devtype=DISK
….
…
What all you can store in Flash Recovery Area(FRA)? Can one FRA directory be used for more than one database?
Ans) Oracle can store different kind of files under FRA:
– backupset: for RMAN regular backups.
– datafile: for RMAN image copies.
– autobackup: for control file autobackups.
– flashback: If your database runs in flashback mode, you will see flashback logs in this subdirectory.
– archivelog: for Archived redo logs
– controlfile: The control file, if configured to go to the flash recovery area.
– onlinelog: Online redo logs can also be made to go to the flash recovery area
You can use the same flash recovery area for as many databases as you want. The RMAN backup process will create a subdirectory called <SID_NAME>, the same name as the database you are backing up.
Which views can be used for Checking Space Usage in the FRA?
Ans) Check Below..
SQL> select NAME,SPACE_LIMIT/1024/1024/1024 TOTAL_GB,SPACE_USED/1024/1024/1024 USED_GB,SPACE_RECLAIMABLE,NUMBER_OF_FILES from v$recovery_file_dest;
NAME TOTAL_GB USED_GB SPACE_RECLAIMABLE NUMBER_OF_FILES
————————————————– ———- ———- —————– —————
/u01/oracle/DB11G/fast_recovery_area 16 1.13197899 0 3
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
——————– —————— ————————- —————
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .12 0 1
BACKUP PIECE 6.95 0 2
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
7 rows selected.
Is putting control file and online redo logs in Flash Recovery Area (FRA) advisable?
Ans) Control file is very important file for the database operation. Loosing a single control file will make the database unstable and will lead to interruption in service.
So we will always try to put control file in a safe and stable place.
Similarly online logs are equally important and loosing them can also cause database to crash, incomplete recovery and possible data loss.
CASE 1:
Usually the flash recovery area and the main database disks are located in such a way that the probability of both going down at the same time is very slim.
And If your flash recovery area is in a storage location as reliable as the main database storage, then you should put one control file and one redo member/log group there.
It will surely help you in quick and complete recovery.
CASE 2:
If your flash recovery area is NOT as reliable as the main database storage, the chance of failure in the flash recovery area is greater compared to the main database disks. If the flash recovery area fails, then you lose one of the control files and the online redo log. You will be able to start database easily by removing that control file from the control file parameter in the initialization parameter file (copying online log from the secondary Non-FRA location) and restarting it but you will have an interruption of production service, which is very undesirable.
Scenario A)
Besides FRA, we have multiplexed Control files to two other separate location, so risk of loosing control file (and fear of not able to do complete recovery) is minimized
We won’t be putting even a single control file in the FRA.
Scenario B)
Besides FRA, we have multiplexed Control files to only one other separate location, so risk of loosing control file and (and fear of not able to do complete recovery) is more. Complete recovery of database is of primary importance to you than the database interruption.
Here we can go and put the control file in FRA.
How can you make sure that only one of the redo log member is created in FRA?
Ans) If you want only one member of the group in the flash recovery area and the other one in the regular database file location, you should define two parameters—the flash recovery area and db_create_file_dest.
SQL> Show parameter db_recovery_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/oracle/DB11G/fast_recovery_area
SQL> alter system set db_create_file_dest =’/u01/oracle/DB11G/oradata';
System altered.
SQL> alter database add logfile group 4;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
———- ——- ——- ————————————————– ———————–
3 ONLINE /u01/oracle/DB11G/oradata/brij/redo03.log NO
2 ONLINE /u01/oracle/DB11G/oradata/brij/redo02.log NO
1 ONLINE /u01/oracle/DB11G/oradata/brij/redo01.log NO
4 ONLINE /u01/oracle/DB11G/oradata/brij/BRIJ/onlinelog/o1_mf_5_9hl8zjmt_.log NO
4 ONLINE /u01/oracle/DB11G/fast_recovery_area/BRIJ/onlinelog/o1_mf_5_9hl8zjsz_.log YES
How can you create log of your RMAN activity?
Ans) Two ways to do it
A) WHILE STARTING RMAN
$ rman log=’tmp/rman_log.log’ OR $ rman log /tmp/rman_log.log append
(use with append clause if you don’t wanna overwrite existing file)
Also If you are running RMAN interactively and you want to see output on your terminal screen as well as have it written to a log file, use tee command:
$ rman | tee /tmp/rman.log
B) WHILE INSIDE RMAN
$rman
RMAN> spool log to ‘/tmp/rman_log.log';
(use with append clause if you don’t wanna overwrite existing file “spool log to ‘/tmp/rman_log.log’ append.)
..
RMAN> spool log off;
** you can use any writeable directory and log file name.
Can Recovery Catalog database also be shutdown from RMAN prompt like TARGET Database can be done?
Ans) All the shutdown and startup commands applies only to the target database. You can’t start and stop the recovery catalog instance from RMAN. The only way to start up and shut down the recovery catalog instance is by connecting to the recovery catalog database as the target database and by issuing the relevant commands to start or stop the instance.
How to check the syntax of RMAN commands?
Ans) Start the RMAN client with the operating system command-line argument checksyntax.
$ rman checksyntax
Recovery Manager: Release 11.2.0.4.0 – Production on Wed Feb 12 14:36:22 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
The command has no syntax errors
RMAN> backup database;
The command has no syntax errors
You can also use the checksyntax argument to check the syntax of RMAN commands that are part of a command file.
Example:
$ rman checksyntax @/tmp/rmancmdfile
Does using recovery catalog means RMAN won’t use the control file to store information?
Ans) No. Even when you choose to use a recovery catalog, backup information will continue to be stored in the control file as well by default.
What is the benefit of using Recovery Catalog?
Ans)
– provides larger storage capacity, thus enabling access to a longer history of backups
– you can create and store RMAN scripts in the recovery catalog and Any client that can connect to the recovery catalog and a target database can use these stored scripts
– Can service many target databases
– you can use ‘KEEP FOREVER’ clause of RMAN backup command.
– Allows you to list the data files and tablespaces that are or were in the target database at a given time
What is the significance of ‘resync catalog’ RMAN command?
Ans) The resync catalog command is used in order to update or resynchronize a recovery catalog from the target database control file. Since sync is done from Target database control file, so you must connect to the recovery catalog as well as to the target database in order to perform the resynchronization.
RMAN> resync catalog;
starting full resync of recovery catalog
full resync complete
When above command is executed, RMAN will first create a snapshot control file. It’ll then compare the contents of the recovery catalog to the contents of the snapshot control file and update the recovery catalog by adding the missing information and modifying the changed backup and schema related records.
It means that we will keep parameter control_file_record_keep_time longer than our backup interval, so that the data in the control file gets transferred to the recovery catalog before that data is overwritten.
When you issue certain RMAN commands such as the backup command, RMAN automatically performs a resynchronization.
You want to move your recovery catalog from one database to another. How you can do it?
Ans) Moving Recovery catalog to another database is simple process
a) Create a new recovery catalog in the target database. You don’t need to register any database to it
b) Use the import catalog command in RMAN after connecting to the target database:
$ rman
RMAN> connect catalog rman_new/rman@target_database
RMAN> import catalog rman_old/rman@source_database;
The import catalog command will import the source recovery catalog contents into the target recovery catalog.
What are the most important recovery catalog views?
Ans) Below are some of the frequently used views:
RC_STORED_SCRIPT: Information about RMAN scripts stored in the recovery catalog.
RC_UNUSABLE_BACKUPFILE_DETAILS: Lists the unusable backup files recorded in the recovery catalog.
RC_RMAN_STATUS: Similar to V$RMAN_STATUS view and shows the status of all RMAN operations. This view doesn’t contain information about any operations that are currently executing.
RC_RMAN_CONFIGURATION: Information about persistent configuration settings.
RC_DATAFILE: Shows all datafiles registered in the recovery catalog.
RC_DATABASE: Shows the databases registered in the recovery catalog.
RC_ARCHIVED_LOG: Provides historical information on both archived as well as unarchived redo logs.
How to check the version of your recovery catalog?
Ans) You can easily check the version of your recovery catalog by issuing the following command from SQL*Plus after logging in as the recovery catalog owner:
RMAN@rmandb > select * from rcver;
VERSION
————
11.02.00.04
What all files can NOT be backed up by RMAN?
Ans)
1) Oracle home-related files
2) External files
3) Network configuration files
4) Password files
What is RMAN and How to configure it?
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
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
How RMAN works?
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
What O/S and oracle user privilege required using RMAN?
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
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
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
How to check RMAN configuration?
RMAN>Show all;
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
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
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
Is this mandatory to use catalog database?
No! It’s an optional one
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
What is the difference between catalog database & catalog schema?
Catalog database is like any other database which contains the RMAN catalog user's schema
What happen if catalog database lost?
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:
What are the database file's that RMAN can backup?
RMAN can backup Controlfile, Datafiles, Archive logs, standby database controfile, Spfile
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
Can I have archivelogs and datafile backup in a single backupset?
No. We can not put datafiles and archive logs in the same backupset
Can I have datafiles and contolfile backup in a single backup set?
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
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
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
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
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
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
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
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
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
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
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
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
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
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;
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
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]
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
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.
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.
Can you explain what is current control file? if we are using multiplexed control files, then which is current control file (CONTROL01.CTL,CONTROL02.CTL or CONTROL03.CTL)? RMAN take which controlfile backup?
Ans) CONTROL01.CTL, CONTROL02.CTL and CONTROL03.CTL are mirrors of each other. Logically, it is one control file with 3 physical mirrors. This logical 1 controlfile is the CURRENT CONTROLFILE. Oracle may copy any one of three mirrors.
What is SCN?
Ans) The SCN is an Oracle server–assigned number that indicates a committed version of the database. It’s quite possible that different datafiles in the database might have a different SCN at any given point in time.
At checkpoint, the server will makes all database file SCNs and control file SCN consistent with respect to an identical SCN.The datafiles will not contain any database changes beyond that common SCN. This synchronization of the SCNs will make sure we have a consistent backup of database.
When you are doing hot backup, you may end up with backups of the various datafiles at various time points and different SCNs and you can not open a database without synchronizing the SCN on all data files, so you will have to apply archive logs to make the data current and synchronize the SCNs across the datafiles.
What is the significance of fast_start_mttr_target parameter?
Ans) You use the Oracle initialization parameter fast_start_mttr_target to specify the number of seconds you want the crash recovery to take. Oracle will try to recover the
instance as close as possible to the time that you specify for the fast_start_mttr_target parameter. The maximum value of this parameter is 3600 seconds (1 hour).
During instance recovery, in the first roll forward operation, the database server must apply all transactions between the last checkpoint and the end of the redo log to the datafiles. Thus, in order to tune instance recovery, you control the gap between the checkpoint position and the end of the redo log. This is called Mean Time to Recover (MTTR).
There was a media failure. How can you find which files you must recover?
Ans) By querying the V$RECOVER_FILE view, which lists all files that need media recovery.
What are the benefits of RMAN over user-managed backup-recovery process?
Ans)
– powerful Data Recovery Advisor feature
– simpler backup and recovery commands
– automatically manages the backup files without DBA intervention.
– automatically deletes unnecessary backup datafiles and archived redo log files both from disk and tape.
– provides you with detailed reporting of backup actions
– Easy to duplicate a database or create standby database.
– Without actually restoring data, you can test whether you will be able to do it or not
– Incremental backup! only RMAN can do that.
– Active Duplication! (11g new feature), you can perform database duplication without backups by using the network enabled database duplication feature
– automatically detects corrupt data blocks during backups
– Data block level recovery possible in case only few DB blocks are corrupt
– Encrypted backups possible
– Use RMAN with a variety of third-party storage systems easily
With so many benefits, RMAN shold be used as primary backup-recovery tool.
How important is Database Redundancy Set and where you should plan to keep it?
Ans) Database Redundancy Set is essential set of recovery-related files. As a DBA, you need to be well prepared for any kind of contingency situation.
It should contain below:
– Recent backups of all datafiles & control file (duplex or triplex the control file at database level)
– All archived redo logs made after the last backup
– Current control files and online redo file copies (duplex online redo files at database level)
– Oracle database-related configuration file copies (spfile, password file, tnsnames.ora and listener.ora files etc)
Operating system mirroring is good, but you should do database level mirroring wherever possible.
If you using ASM, try to have atleast Normal redundancy.
When setting up production systems, use at least two disk drives(one for the redundancy set and the other for the datafiles). They should be completely separated by using different volumes, file systems, disk controllers, and RAID devices to hold the two sets of files
You can set up FRA for keeping the redundancy set. Oracle recommends the flash recovery area as a logical candidate to keep a copy of all the files belonging to the redundancy set (which includes the most recent database backup) on disk.
What is the benefit of making automatic control file backup to ON?
Ans) Remember that control file is absolutely necessary during a recovery.
Below command can be used to have automatic control file backup to be taken. This is highly recommended.
RMAN> configure controlfile autobackup on
Now at the end of every RMAN backup command, RMAN automatically backs up the control file.
Even when you make some changes via SQL*Plus( say creating a new tablespace or adding or renaming a datafile or an online redo log member), the control file is automatically backed up.
Also, you can restore RMAN’s backup and recovery information (called RMAN’s repository), when you lose all your control files and aren’t using the optional recovery catalog.
What is the significance of RMAN view V$RMAN_OUTPUT?
Ans) V$RMAN_OUTPUT displays messages reported by RMAN. This is an in-memory view (means will not persist thru a database restart)and is not recorded in the controlfile. This information straight out of the database by using SQL queries.
Sometimes RMAN log files may have already been overwritten by the next backup or simply just deleted. This is an alternative way of accessing the RMAN output information
For Example:
Review the last two days worth of rman output:
select output from v$rman_output where session_recid in (select session_recid from v$rman_status
where start_time > sysdate-2) order by recid ;
OUTPUT
———————————————————————————————————————————-
connected to target database: BRIJ (DBID=3142459675)
using target database control file instead of recovery catalog
echo set on
backup archivelog all not backed up delete all input;
Starting backup at 09-FEB-14
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=124 instance=BRIJ devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=126 instance=BRIJ devtype=DISK
….
…
What all you can store in Flash Recovery Area(FRA)? Can one FRA directory be used for more than one database?
Ans) Oracle can store different kind of files under FRA:
– backupset: for RMAN regular backups.
– datafile: for RMAN image copies.
– autobackup: for control file autobackups.
– flashback: If your database runs in flashback mode, you will see flashback logs in this subdirectory.
– archivelog: for Archived redo logs
– controlfile: The control file, if configured to go to the flash recovery area.
– onlinelog: Online redo logs can also be made to go to the flash recovery area
You can use the same flash recovery area for as many databases as you want. The RMAN backup process will create a subdirectory called <SID_NAME>, the same name as the database you are backing up.
Which views can be used for Checking Space Usage in the FRA?
Ans) Check Below..
SQL> select NAME,SPACE_LIMIT/1024/1024/1024 TOTAL_GB,SPACE_USED/1024/1024/1024 USED_GB,SPACE_RECLAIMABLE,NUMBER_OF_FILES from v$recovery_file_dest;
NAME TOTAL_GB USED_GB SPACE_RECLAIMABLE NUMBER_OF_FILES
————————————————– ———- ———- —————– —————
/u01/oracle/DB11G/fast_recovery_area 16 1.13197899 0 3
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
——————– —————— ————————- —————
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG .12 0 1
BACKUP PIECE 6.95 0 2
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0
7 rows selected.
Is putting control file and online redo logs in Flash Recovery Area (FRA) advisable?
Ans) Control file is very important file for the database operation. Loosing a single control file will make the database unstable and will lead to interruption in service.
So we will always try to put control file in a safe and stable place.
Similarly online logs are equally important and loosing them can also cause database to crash, incomplete recovery and possible data loss.
CASE 1:
Usually the flash recovery area and the main database disks are located in such a way that the probability of both going down at the same time is very slim.
And If your flash recovery area is in a storage location as reliable as the main database storage, then you should put one control file and one redo member/log group there.
It will surely help you in quick and complete recovery.
CASE 2:
If your flash recovery area is NOT as reliable as the main database storage, the chance of failure in the flash recovery area is greater compared to the main database disks. If the flash recovery area fails, then you lose one of the control files and the online redo log. You will be able to start database easily by removing that control file from the control file parameter in the initialization parameter file (copying online log from the secondary Non-FRA location) and restarting it but you will have an interruption of production service, which is very undesirable.
Scenario A)
Besides FRA, we have multiplexed Control files to two other separate location, so risk of loosing control file (and fear of not able to do complete recovery) is minimized
We won’t be putting even a single control file in the FRA.
Scenario B)
Besides FRA, we have multiplexed Control files to only one other separate location, so risk of loosing control file and (and fear of not able to do complete recovery) is more. Complete recovery of database is of primary importance to you than the database interruption.
Here we can go and put the control file in FRA.
How can you make sure that only one of the redo log member is created in FRA?
Ans) If you want only one member of the group in the flash recovery area and the other one in the regular database file location, you should define two parameters—the flash recovery area and db_create_file_dest.
SQL> Show parameter db_recovery_file_dest
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string /u01/oracle/DB11G/fast_recovery_area
SQL> alter system set db_create_file_dest =’/u01/oracle/DB11G/oradata';
System altered.
SQL> alter database add logfile group 4;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
———- ——- ——- ————————————————– ———————–
3 ONLINE /u01/oracle/DB11G/oradata/brij/redo03.log NO
2 ONLINE /u01/oracle/DB11G/oradata/brij/redo02.log NO
1 ONLINE /u01/oracle/DB11G/oradata/brij/redo01.log NO
4 ONLINE /u01/oracle/DB11G/oradata/brij/BRIJ/onlinelog/o1_mf_5_9hl8zjmt_.log NO
4 ONLINE /u01/oracle/DB11G/fast_recovery_area/BRIJ/onlinelog/o1_mf_5_9hl8zjsz_.log YES
How can you create log of your RMAN activity?
Ans) Two ways to do it
A) WHILE STARTING RMAN
$ rman log=’tmp/rman_log.log’ OR $ rman log /tmp/rman_log.log append
(use with append clause if you don’t wanna overwrite existing file)
Also If you are running RMAN interactively and you want to see output on your terminal screen as well as have it written to a log file, use tee command:
$ rman | tee /tmp/rman.log
B) WHILE INSIDE RMAN
$rman
RMAN> spool log to ‘/tmp/rman_log.log';
(use with append clause if you don’t wanna overwrite existing file “spool log to ‘/tmp/rman_log.log’ append.)
..
RMAN> spool log off;
** you can use any writeable directory and log file name.
Can Recovery Catalog database also be shutdown from RMAN prompt like TARGET Database can be done?
Ans) All the shutdown and startup commands applies only to the target database. You can’t start and stop the recovery catalog instance from RMAN. The only way to start up and shut down the recovery catalog instance is by connecting to the recovery catalog database as the target database and by issuing the relevant commands to start or stop the instance.
How to check the syntax of RMAN commands?
Ans) Start the RMAN client with the operating system command-line argument checksyntax.
$ rman checksyntax
Recovery Manager: Release 11.2.0.4.0 – Production on Wed Feb 12 14:36:22 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
The command has no syntax errors
RMAN> backup database;
The command has no syntax errors
You can also use the checksyntax argument to check the syntax of RMAN commands that are part of a command file.
Example:
$ rman checksyntax @/tmp/rmancmdfile
Does using recovery catalog means RMAN won’t use the control file to store information?
Ans) No. Even when you choose to use a recovery catalog, backup information will continue to be stored in the control file as well by default.
What is the benefit of using Recovery Catalog?
Ans)
– provides larger storage capacity, thus enabling access to a longer history of backups
– you can create and store RMAN scripts in the recovery catalog and Any client that can connect to the recovery catalog and a target database can use these stored scripts
– Can service many target databases
– you can use ‘KEEP FOREVER’ clause of RMAN backup command.
– Allows you to list the data files and tablespaces that are or were in the target database at a given time
What is the significance of ‘resync catalog’ RMAN command?
Ans) The resync catalog command is used in order to update or resynchronize a recovery catalog from the target database control file. Since sync is done from Target database control file, so you must connect to the recovery catalog as well as to the target database in order to perform the resynchronization.
RMAN> resync catalog;
starting full resync of recovery catalog
full resync complete
When above command is executed, RMAN will first create a snapshot control file. It’ll then compare the contents of the recovery catalog to the contents of the snapshot control file and update the recovery catalog by adding the missing information and modifying the changed backup and schema related records.
It means that we will keep parameter control_file_record_keep_time longer than our backup interval, so that the data in the control file gets transferred to the recovery catalog before that data is overwritten.
When you issue certain RMAN commands such as the backup command, RMAN automatically performs a resynchronization.
You want to move your recovery catalog from one database to another. How you can do it?
Ans) Moving Recovery catalog to another database is simple process
a) Create a new recovery catalog in the target database. You don’t need to register any database to it
b) Use the import catalog command in RMAN after connecting to the target database:
$ rman
RMAN> connect catalog rman_new/rman@target_database
RMAN> import catalog rman_old/rman@source_database;
The import catalog command will import the source recovery catalog contents into the target recovery catalog.
What are the most important recovery catalog views?
Ans) Below are some of the frequently used views:
RC_STORED_SCRIPT: Information about RMAN scripts stored in the recovery catalog.
RC_UNUSABLE_BACKUPFILE_DETAILS: Lists the unusable backup files recorded in the recovery catalog.
RC_RMAN_STATUS: Similar to V$RMAN_STATUS view and shows the status of all RMAN operations. This view doesn’t contain information about any operations that are currently executing.
RC_RMAN_CONFIGURATION: Information about persistent configuration settings.
RC_DATAFILE: Shows all datafiles registered in the recovery catalog.
RC_DATABASE: Shows the databases registered in the recovery catalog.
RC_ARCHIVED_LOG: Provides historical information on both archived as well as unarchived redo logs.
How to check the version of your recovery catalog?
Ans) You can easily check the version of your recovery catalog by issuing the following command from SQL*Plus after logging in as the recovery catalog owner:
RMAN@rmandb > select * from rcver;
VERSION
————
11.02.00.04
What all files can NOT be backed up by RMAN?
Ans)
1) Oracle home-related files
2) External files
3) Network configuration files
4) Password files
What is RMAN and How to configure it?
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
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
How RMAN works?
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
What O/S and oracle user privilege required using RMAN?
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
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
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
How to check RMAN configuration?
RMAN>Show all;
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
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
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
Is this mandatory to use catalog database?
No! It’s an optional one
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
What is the difference between catalog database & catalog schema?
Catalog database is like any other database which contains the RMAN catalog user's schema
What happen if catalog database lost?
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:
What are the database file's that RMAN can backup?
RMAN can backup Controlfile, Datafiles, Archive logs, standby database controfile, Spfile
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
Can I have archivelogs and datafile backup in a single backupset?
No. We can not put datafiles and archive logs in the same backupset
Can I have datafiles and contolfile backup in a single backup set?
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
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
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
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
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
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
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
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
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
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
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
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
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
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;
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
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]
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
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.
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.
No comments:
Post a Comment