Author: learnersreference

We are offering rman interview questions for beginners, rman interview questions for middle level DBA, rman interview questions for experienced. Clear your interview, grab next job
1) What are all the new rman features supported in oracle 12c?
Oracle database 12c supports RMAN the Recovery manager tool to perform backup,restore and recovery. In Oracle database 12c RMAN is enhanced to support many new features that are really interesting
1) Fine-grained recovery is supported – This involves recovery of individual tables from backup. This feature is available in addition to flashback database features
recover table – Command to recover tables/partitions from RMAN backup
This is a great improvement as this provides enhanced point-in-time recovery of tables as opposed to tablespaces in earlier versions. This is primarily useful over FLASHBACK TABLE in cases wherein there is insufficient UNDO space
2) Migration of databases across different platforms is simplified
3) Cloning has been improved which enhances faster duplication of databases using fast ACTIVE duplicate mechanism
4) Multitenant database feature starting 12c can be backed up and restored using RMAN – This is a brand new implementation in 12c. Backup , restore and recovery of both pluggbale databases and container databases is fully supported
5) Faster synchronization of standby databases is supported
2) What is RMAN?
RMAN the oracle recovery manager is a tool from Oracle Corporation
RMAN helps us backup the database files including datafiles, archived redo log files, control files,spfile from within the database with the help of database server itself. This is a utility that comes for FREE as part of oracle database software
3) What files can be backed up using RMAN?
RMAN can make backup of the following files – backups of datafiles, data file image copies, control files and control file image copies, archived redo logs, the SPFILE, RMAN backup pieces
4) What is Proxy copy in RMAN?
RMAN can perform a special kind of backup called as proxy copy
Media manager is given control of copying process
Proxy copies can’t be used with disks and can be used only with tape backups
RMAN>BACKUP DEVICE TYPE sbt PROXY DATAFILE 10(datafile_number);
It is to be noted that media manager will have to determine if a PROXY COPY can be performed as data transfer control is given to media manager
5) What happens if media manager can not perform PROXY COPY?
If a PROXY is specified as part of BACKUP command and media manager determines that data transfer can’t be performed, RMAN will query the media manager. If there is no provision, RMAN will perform backup same as the case without PROXY option being specified
6) What happens when PROXY ONLY option is used with RMAN?
RMAN will query the media manager to determine is proxy copy can be performed. IF media manager fails to do so, RMAN backup fails
7) Why is rman preferred tool for performing oracle recovery process?
RMAN is the preferred tool for performing Oracle recovery process. RMAN selects and applies the necessary data and log files during recovery
RMAN can perform recovery at the data block level with the block media recovery feature, which dramatically reduces recovery time
RMAN provides restore optimization – a great timesaving feature that enables us to bypass data files that are ok during the recovery process
RMAN provides DUPLICATE command which lets us create copies of production database easily. RMAN DUPLICATE command is the preferred method of database duplication in a dataguard standby creation process. The duplicate command makes use of control file, data file backups and the archived logs from primary database to instantiate a standby database
8) What is the use of recovery_catalog_owner role?
Oracle RMAN(Recovery Manager) RECOVERY_CATALOG_OWNER is a predefined system role which includes the following system privileges:
1) ALTER SESSION
2) CREATE CLUSTER
3) CREATE DATABASE LINK
4) CREATE PROCEDURE
5) CREATE SEQUENCE
6) CREATE SESSION
7) CREATE SYNONYM
8) CREATE TABLE
9) CREATE TRIGGER
10) CREATE TYPE
11) CREATE VIEW
RECOVERY_CATALOG_OWNER is the role granted to rman user. rman user is the default user used for creating and accessing the recovery catalog of an oracle database.
9) Give details on backup tag format in RMAN?
RMAN lets us specify tag for every backup so we can easily identify the backup.We can specify the tags when we perform are store or recovery.
RMAN>BACKUP TAG ‘tag_name’ DATABASE;
Using backup tag when we perform a restore or recovery operation , we can speify the tag to identify the backup to use.Tags are very useful in identifying various backups,especially those created using incremental backup strategies.A simple example showing how we can tag a full database backup is given below:
RMAN> BACKUP TAG ‘weekly_full_database_backup’ DATABASE;
We can use the format option with backup commands to specify a location and name for backup pieces and copies. We use substitution variables to generate unique filenames. A example showing how to specify a file format , as well as location,using the FORMAT option is given below:
RMAN> BACKUP FORMAT=’AL_%d%t%s%p’ ARCHIVELOG LIKE ‘%arc_dest%’;
To make the RMAN backup job a non-interfering one we can issue DURATION option with RMAN BACKUP command. This specifies the time oracle should take to perform a backup.
We can use the FORMAT option with backup commands to specify a location and name for backup pieces and copies.We can use substitution variables – RMAN> BACKUP FORMAT ‘NAME_substitution_variables’ ARCHIVELOG LIKE ‘name’;
10) Give details on restore validate command in RMAN:-
RESTORE VALIDATE – check whether a certain object of interest is among the RMAN’s backup set.
RMAN> RESTORE TABLESPACE tablespace-name VALIDATE; – this command asks RMAN to confirm whether it can restore the tablespace from its backup sets
Success Message – “Finished Restore”
Give details on restore preview command in RMAN:-
RESTORE PREVIEW – lets us identify all the backup files necessary for a specific restore operation
RMAN>RESTORE DATABASE/TABLESPACE/DATAFILE tsname/datafilename PREVIEW;
The command provides detailed report of all the backups needed for the RESTORE command to succeed
11) How to PRINT script in RMAN?
RMAN scripts can be printed using PRINT SCRIPT Command
RMAN>PRINT SCRIPT script_name;
To print a global RMAN Script issue:
RMAN>PRINT GLOBAL SCRIPT script_name;
12) What is oracle backup aka Oracle Secure Backup?
Oracle Backup is a media manager from Oracle Corporation used for tape backups.It simplifies and automates backup and recovery operations. In Oracle 10.2 this tool has been named as Oracle Secure Backup
13) How to create recovery catalog?
To create a recovery catalog we must connect to the database in which we want to create the recovery catalog. Create a new recovery catalog owner schema usually named rman (create user rman).
Grant necessary privileges to it, then create recovery catalog-
SQL> grant connect, resource to rman;
SQL> grant recovery_catalog_owner to rman;
Connect to catalog as shown previously
RMAN>CREATE CATALOG ;- creates RMAN recovery catalog in the tablespace assigned as default tablespace for user rman
DROP catalog; – drop catalog
14) How does user-managed recovery work?
User-managed techniques can be used to restore and recover a database
Decide if we can allow users to access the database during the time of recovery
Use commands RECOVER DATABASE/TABLESPACE/DATAFILE
If database is not open it will be in mount state. Now open with ALTER DATABASE OPEN command
RMAN has provision to automate all of the above mentioned user-managed restore and recovery procedures
15) How to Backup Flash Recovery Area in oracle?
RMAN>show all;
………………….
CONFIGURE BACKUP OPTIMIZATION OFF;#default
………………………………
RMAN>configure backup optimization on;
RMAN>backup recovery area;
RMAN-06747: at least 1 channel of tertiary storage must be allocated to execute this command
Ensure that tape drive is properly connected.
The steps mentioned above are used to backup flash recovery area.
16) How to backup flashback recovery area using RMAN?
We can backup flash recovery area with RMAN backup commands. We have to run CONFIGURE BACKUP OPTIMIZATION ON. We can backup the flash recovery area to the tape device using the backup commands
RMAN>BACKUP RECOVERY AREA – Allows us to backup every file in the flash recovery area. This will only backup the files that haven’t been backed up to the tape before
RMAN>BACKUP RECOVERY FILES – Backup every file, including files on the file system
RMAN> BACKUP RECOVERY FILE DESTINATION – Move disk backup to tape
17) What are all the tools needed for oracle database backup?
RMAN – Recovery Manager. It is a tool from Oracle corporation. Can be used in command-line mode and as GUI from OEM
Operating system utilities – cp and dd. Used to perform user-managed backups. We use a combination of OS backup commands and SQL*PLUS commands to backup the database files
18) How to connect to rman?
Simply type $rman at OS prompt. This will lead us to RMAN> prompt
RMAN commands can also be used in batch mode
We need SYSDBA privilege to connect to target databases
For connecting to recovery catalog we need special RMAN username/password. SYSDBA privilege is not needed
$rman target / – OS authentication
Recovery catalog – rman target target-database-name catalog username/password@recoverycatalog_SID
19) How to check the syntax in RMAN?
RMAN commands can be issued as individual commands in RMAN> prompt. It is also possbile to script the sequence of commands in a RMAN script.We can check the syntax of a RMAN script and a RMAN command using the parameter CHECKSYNTAX
#rman CHECKSYNTAX @/abc/rmancheck
The above command :
1) Shows message on Recovery Manager version
2) Lists the RMAN commands in the rmancheck file
3) Displays the message – ” The cmdfile has no syntax errors Recovery Manager complete” – If there are no errors
4) Displays an error message if there is an error
20) Is there a way it skip previously backed up files in RMAN?
By default backup optimization is off
We can configure backup optimization to on and skip the previously backed up files
Configure backup optimization on
21) Is there a way to override backup optimization?
Yes. We can make use of FORCE option as part of backup commands – backup database force, backup archivelog all force overrides backup optimization setting , creates backup of database files even though it happens to be identical to previous version of the file
22) What is the main advantage of fast recovery area?
IT is to be noted that flash recovery area introduced in 10g has been renamed to fast recovery area starting 11g. To keep it simple this is a location configured using certain initialization parameters like db_recovery_file_Dest, db_recovery_file_Dest_size instead of log_Archive_DEst, log_Archive_DEst_n, log_Archive_duplex_Dest etc that the RMAN is aware of. This is a disk based location created as part of disk group, ASM etc. Disk based backup and recovery is considered fastest form of recovery and this mechanism comes handy
23) Who can configure fast recovery area?
USer who is owner of oracle software, users logged in as sysdba have the privilege to configure fast recovery area
24) What are all the steps needed to configure fast recovery area?
Here are the simple steps that must be executed in sequence
1) Disable archived redo log destinations. All the destinations must be disabled
alter system set log_Archive_Dest=”;
alter system set log_Archive_Dest_!=”;
2) Log into system as sysdba
sqlplus / as sysdba
sqlplus sys/password as sysdba
3) Configure fast recovery area as follows:
alter system set db_recovery_file_Dest=’location’;
alter system set db_recovery_file_DEst_size=’value’;
The location used for fast recovery area is typically a directory in file system, ASM disk group location etc
25) How is space managed in fast recovery area?
RMAN does utilize the redundancy and retention policies, deletes unwanted obsolete backup files, image copies, archived redo logs etc from fast recovery area. Again delete obsolete command is used for this purpose
26) What is common oracle error while making use of fast recovery area?
It is to be noted that fast recovery area is totally disk based. Henceforth, there is a emergency situation associated with running out of disk space. Here is a common oracle error ORA-38760: This database instance failed to turn on flashback database
To fix this issue do one of the following:
Increase the size of fast recovery area
alter system set db_Recovery_file_dest_Size=’newvalue’;
Manually run rman obsolete, remove unnecessary files
delete obsolete;
Above command does remove the unnecessary files. Files needed for recovery are retained and not touched. In that case we need to increase the fast recovery area size
Get details on restore point, remove some permanent restore points to conserve space
select * from v$restore_point;
drop restore point restore_point_name;
Above steps will help us startup database without error
27) How to delete archived logs based on log sequence number?
delete archivelog from logseq=starting_log until logseq=ending_log
28) How does rman treat user-made backups?
RMAN generally treats user made backups as image copies
29) What are all the simple steps to recover from loss of controlfile usign rman?
We can use RMAN to recover from the loss of control file. Details on recovering from loss of control file using RMAN is given below:
1) SHUTDOWN IMMEDIATE
2) At this step we delete the control files.If we try to start the database using STARTUP option we get the following error :
RMAN – 03002: failure of startup command at 06/02/2016 12:30
ORA – 00205 : error in identifying controlfile, check alert log for more info
We have to follow the steps below to recover from the loss of control file.
3) STARTUP NOMOUNT
4) RECOVER CONTROLFILE
5) ALTER DATABASE MOUNT
6) RECOVER DATABASE
7) ALTER DATABASE OPEN RESETLOGS
30) What are all the various options for creating multiple copies of RMAN backups of database,tablespace,datafile?
RMAN> BACKUP DEVICE TYPE DISK COPIES 4 DATAFILE 1 FORMAT ‘format’;
After making the image copies of the datafile, tablespace, database we can backup the image copies of the backups;
RMAN> BACKUP AS COPY DATABASE;
Copy the previous image copy of the database – RMAN>BACKUP AS COPY COPY OF DATABASE;
Image copy of a single tablespace – RMAN> BACKUP AS COPY TABLESPACE tablespace-name;
Create a backup set from tablespace image copy – RMAN>BACKUP AS BACKUPSET COPY OF TABLESPACE tablespace-name;
For a datafile – RMAN>BACKUP AS COPY DATAFILE 3; RMAN>BACKUP AS COPY COPY OF DATAFILE 3;

31) How to run RMAN Scripts From Operating System File ?
We can incorporate RMAN script in an Operating system file. This is called as command file
$rman TARGET/CATALOG username/password@catalog_database CMDFILE file.rcv LOG outlog.txt – OS file is used directly to run the RMAN commands and show the results in an output logfile
32) Why is RMAN needed for ASM?
RMAN is critical to ASM and is responsible for tracking ASM filenames and for deleting obsolete ASM files. ASM files can’t be copied through the OS (operating system) commands, RMAN is the preferred means of copying ASM Files
RMAN is the only method of performing backup of the database files containing ASM files
33) Give details on obtool:-
We can invoke oracle secure backup from command line interface using the command obtool
C:>obtool
obtool: Error: this host’s identity and membership within a Oracle Secure Backup
obtool: administrative domain have not yet been established
34) What is the preferred medium to store backups infrastructure professional choice?
DBA’s frequently use tape devices for database backup.Tape devices – convenient, easy to archive, safely kept
Media Management Layer is needed if we want to use tape devices with RMAN
Oracle Corporation now offers its own media management tool called as Oracle Secure Backup , free with Oracle Server
35) How does user privilege impact RMAN backup mechanism?
In an oracle database environment privileged user is someone who has access to privileged OS account or who has been granted privileges at database level. At os level the user will be part of administrative groups, ora_dba group in case of windows environment etc. At the database level users are offered privilege with proper grants, being part of password authentication file as privileged user offers an user option to connect to database with higher privilege access. This same privilege is used by RMAN to establish privileged database session via rman utility. It is this utility command-line interface or GUI in oracle enterprise manager that allows us to perform backup, restore, recovery operations
36) Give details on backupdba OS group?
The backupdba operating system group is new in oracle database 12c. User who is part of backupdba OS group is given database privilege sysbackup. This privilege is new in oracle database 12c and allows an user to startup database, shutdown database, perform all backup and recovery operations at the oracle database level
37) How do you specify a location to write archived redo logs in an oracle database environment?
All the production, mission critical oracle databases needs to be run in archived redo log mode. Once the archiving is enabled, archived redologs are created and stored in archival destination. By default in UNIX/Linux environment $ORACLE_HOME/dbs is the default archival destination unless otherwise specified. In case of windows environment the location is going to be %ORACLE_HOME%\database folder. This can be modified by properly specifying flash recovery area (or) logarchive_Dest_n destination. This location needs to be designed and properly specified while designing the database
38) How to make backups without using RMAN?
We can make completely valid backups ourselves without use of RMAN
We can use OS commands like cp and dd
In this approach, we must keep track of all the backups and check their validity
This helps us determine the backups that we need during a recovery session
This method is referred to as user-managed backups
If the database load is simple and not onerous, we may go for user-managed backups
39) What is hot backup?
hot backup is also called as online backup.It is taken while database is open,datafiles are online.Tablespace/database are put into backup mode in case of user-managed backup.In case of RMAN there is no need to switch to backup mode.
How do you put tablespace and database in backup mode during hot/online bakup?
alter tablespace begin backup
alter database begin backup
40) How do you put tablespace/database back into normal mode from backup mode?
alter tablespace end backup
alter database end backup
41) How to create multiple copies of RMAN backup?
RMAN lets us make multiple copies of backup sets with a BACKUP command. We can send produce upto four copies of each backup piece in a backup set within a single BACKUP command
We can’t produce multiple copies of RMAN image copies
Multiple copies of BACKUP sets can be produced as follows : Use the CONFIGURE..BACKUP COPIES option, Use SET BACKUP COPIES in a RUN block, Use COPIES option in the BACKUP Command
42) What is cold backup?
Backup of one or more datafiles made while database is closed is cold backup.It can be consistent or inconsistent depending on if few or all datafiles are taken offline.It is also called as whole database backup.
43) Give details on oracle recovery filetypes:-
Transient recovery files can be automatically deleted
Full datafile backups, incremental backups, datafile copies, control file (autobackup), archived redo logs, block change tracking files, flashback logs
Oracle places mirrored copies of certain database files in the flash recovery area
Mirrored copies of online redo log files, mirrored copies of current control files are permanent files and should never be deleted
44) What is a fuzzy file?
When data files are in backup mode chances are that there can be a datafile whose SCN(system change number) is greater than or equal to the checkpoint SCN.This file needs recovery.
45) How to switch from init.ora file to spfile?
create spfile from pfile;/create spfile=[spfile_name] from pfile=[pfile_name];
46) How do you create pfile from spfile?
create pfile from spfile;/create pfile=[pfile_name] from spfile=[spfile_name];
47) What is a log switch?
The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.
48) What is Testing Oracle Database Backups – Avoid Recovery Disaster?
Defective backup strategies lead to frustration during recovery of a production database.It is due to inadequate and in some cases non-existent planning and testing of the backup and recovery strategy
To avoid this, DBA must have a well established and tested backup and disaster recovery plan
Validate the backups – make sure that they are all readable
Check for corrupted-blocks in backed-up files, to avoid recovery problems due to bad files
We have features in user-managed backup and RMAN to avoid this problem
RMAN comes as an integral part of Oracle and doesnt need seperate installation,RMAN can be easily invoked using command-line client rman
RMAN in Oracle database 12c can perform backup of following files
1) Datafiles – These are physical files containing database data
2) Controlfiles – The heartbeat file that stores details on database structures, structural changes, backup details in case of nocatalog, transactions details to name a few
3) Archived redo logs – All the archives created in archive folder since last database startup can be backedup
4) SPFILE – The binary file needed for Oracle instance startup and interaction of instance with database. This is extermely crucial as its loss will bring entire system down
49) Basic Database Configuration Needed For Database Backup:
Whenever there is a question from customer/client what is the basic database backup tool from Oracle corporation and what is DB level configuration it is easy to mention that basic requirement
Oracle database must be in archivelog mode to make use of Oracle database backup tool RMAN
To determine if database is in archivelog mode issue the following command
archive log all
50) RMAN-00554 initialization of internal recovery manager package failed. What to do?
When we try connecting to the rman executable using the rman target / we might get this error.To fix this error invoke rman as rman target /@ORACLE_SID
51) Give details on RMAN special terminologies:-
RMAN uses some special terminology. To use RMAN effectively, we need a good understanding of the terms.
Backup Piece :
A backup piece is an operating system file containing the backup of a data file.,control file, or archived redo log file. This backup information is stored in an RMAN-specific format.
Backup Set :
A backup set is a logical set that consists of one or more RMAN backup pieces(the default is one backup piece per backup set). We can create a backup set on a disk or tape.If we backup a database, data file, tablespace,or archive log ,RMAN groups the complete set of relevant backup pieces into one backup set.
When we issue the backup command, RMAN creates the backup set to hold the output.
By default RMAN creates a backup set when we use a backup command, whether we are copying to disk or tape (using a media manager).
Note : Backup set is a file or a set of files in proprietor format that only RMAN can understand. So, only RMAN is able to use the backup sets to recover the database.
52) What is a user-managed recovery and how to perform one?
There are circumstances that demand the restore and recovery of the oracle database as a whole or portion of it say a datafile. Such procedures and practices are called the user-managed recovery procedures. Here are the simple steps needed to perform user-managed recovery
1) Identify the database files that need to be restored
2) Place database in appropriate mode – mount or open
3) Restore database files from media (tape or disk) to appropriate location
4) Recover database using RECOVER command
5) RECOVER command applies archived redo logs to the database files – if needed
53) What to do when we encounter ORA-19550: cannot use backup/restore functions while using dispatcher?
To fix this issue create a new tns alias in tnsnames.ora file. This should be created with SERVER=DEDICATED option. This makes sure that rman connectivity does not make use of dispatchers. The error will be fixed
54) Give details on RMAN validate command :-
VALIDATE BACKUPSET command – validate backup sets before we use them from a recovery
If there is an error – RMAN:06159 – error while looking up backup set while issuing
RMAN>validate backupset 2; – indicates that backup can’t be found by RMAN
CROSSCHECK – Make sure that backup is present and usable
55) How to determine the location of RMAN backups at OS level?
Actual location on the disk where the backup is stored can be determined in one of the following ways
We can use FORMAT parameter, this location will override any location specified for the flash recovery area – RMAN>BACKUP DATABASE FORMAT ‘format’;
If we don’t specify the FORMAT parameter, flash recovery area is the location chosen – RMAN>BACKUP DATABASE;
If we haven’t configured flash recovery area as well as don’t specify FORMAT command, RMAN will store backup in an OS directory
56) How does oracle RMAN perform Recovery?
RMAN restores data files from backups and applies the necessary archived redo logs to bring the database up to date
RMAN looks into its recovery catalog and knows which files it needs and thus simplifies the error that may occur during user-managed recovery.RMAN can check if the backups are valid before performing a recovery
57) What are oracle database recovery scenarios?
Database can be unavailable due to system crash, network failure, media failure, natural disaster
Solidly tested backups and regular recovery drills are keys to successful recovery.
Practicing recovery techniques is essential for successful recovery.
Latest technique – Flashback recovery
58) You are restoring controlfile from backup. You are not restoring datafile as part of this process. How will you open the database?
The database should be opened with alter database open resetlogs command. Inspite of restore of datafiles this process is considered a media recovery because the backup controlfile will have SCN that does not synchronize with datafile SCN and online redo logfile SCN. Henceforth, resetlogs option should be used
59) How will you determine if a controlfile is a normal one or the one from backup?
This can be easily determine by querying v$database controlfile_type column

60) How will you determine if backup of controlfile is enabled?
Log into RMAN prompt and issue command show all. This provides information on whether controlfile autobackup is enabled (or) disabled
61) What is the easiest way to determine if flash recovery area is enabled or disabled?
Check if db_recovery_file_dest parameter is set from within sqlprompt
62) You have enabled flash recovery area. You have enabled autobackup of controlfile. Can you get information on DBID from the controlfile piece in FRA?
Nope. It is not possible to determine DBID value. It is because if controlfile autobackup happens at FRA the file is created based on oracle managed file naming format
63) What is the way to determine DBID value while database is up and running?
While database is up and running the database unique identifier commonly called as DBID from v$database view
64) You forgot to record details on DBID. Your database has crashed now. You dont have FRA. You are trying to restore controlfile from autoback and get the error RMAN-06495 : must explicitly specify DBID with SET DBID command. What will you do?
As FRA is not set DBID value can be determined from controlfile naming convention which defaults to C-xxxxxxxxxx-yyyymmdd-qq format. The 10x’s map to value of DBID
IF the format is explicitly specified the value may not exist. In this case look at alert.log in case you have earlier recorded using dbms_System package. IF you have logfile showing rman output earlier the value will be present there
IF none of the above options work there is a way to dump dbid value from any of the existing datafile, archived redolog file, online redolog file in nomount mode. Only requirement is that these files must be present physically
startup nomount;
alter system dump datafile ‘filepath’ block min 1 block max 10;
From the above command it is evident that DBID is a 10 digit value that appears in first 10 blocks of datafile
If you choose to dump the DBID from online redolog file (or) archived redolog file use command:
alter system dump logfile ‘path’ ;
In both of the above mentioned scenarios DBID is recorded in tracefiles that are generated in diagnostic tracefile destination
65) A backup is taking too long time. How will you decide if the backup is progressing fine (or) has been terminated?
As a DBA the data dictionary views come handy while monitoring long jobs like background batch jobs, RMAN backup jobs, datapump jobs etc. For RMAN backup make use of v$session_longops view
66) You have a RMAN recovery catalog in place. The recovery catalog database is in a separate database. Can you shutdown recovery catalog database using RMAN prompt rather than logging into that database the way we do for TARGET databases?
For performing startup and shutdown of recovery catalog databases either log into them directly perform shutdown , from RMAN connect to recovery catalog databases specified as target database and then issue the shutdown and startup commands
67) You want to know if you your RMAN command is free of syntax errors. How will you accomplish this?
By making use of RMAN checksyntax followed by rman commands we can verify and validate RMAN commands
For set of rman commands specified from within command files this checksyntax can be used
68) You have configured recovery catalog to store the backup metadata information in RMAN environment. IF control file useless in this case?
Nope. Inspite of recovery catalog configuration in place controlfile still records details of RMAN backups
69) The recovery catalog database was down and was not noticed. Suddenly DBA notices this and fixes the recovery catalog database. Is the RMAN backup information lost in this case?
Nope. Inspite of recovery catalog configuration in-place backup information is still stored in controlfile of target database by default. Once the recovery catalog is up and running connect to recovery catalog database and target database. PErform resynchronization by using the command:
resync catalog
After this process completes we see a message that says full resync complete
70) How will you know what files are needed for media recovery?
Look at v$recover_file view to see the list of files that are needed for media recovery
71) You have configured controlfile autobackup to on in your environment. What is the benefit of this configuration?
Once the controlfile autobackup is set to on at the end of every RMAN backup command, the controlfile is backed up automatically. This backup controlfile gets created at trace folder in location where alert_sid.log file in located
72) From which view can you get details on the messages reported by RMAN?
The v$rman_output view an in-memory view that is transient upon database restart isused to get details on messages reported by RMAN
73) How to convert oracle rman stored script to textfile script
RMAN offers two kinds of scripts – stored scripts kept in recovery catalog, text scripts kept in regular text files
Stored scripts have an advantage – any user who logs into RMAN can access them easily
We can change scripts from stored scripts to textfile and viceversa
RMAN> PRINT script script_name to file ‘filename’; – RMAN command can send the contents of a stored script to a text file
74) Give some details on substitution variable usage in RMAN:
RMAN comes with powerful scripting lanuage which can be used in addition to manual commands
Common backup tasks can be encapsulated easily using scripting
We can store scripts in recovery catalog or as text files
When we need large number of configuration parameters for a particular backup, we do it easily using scripting.RMAN scripting has been enhanced and it is now possible to create a RMAN script that is more dynamic. The substitution variable & can be made use of and values are passed through shell scripting
Lets say we create a shell script rman.sh and assign value to these variables.
Now it is possible to pass these values to this rman script as follows from within the shell script
rman @rmanscript using $variable1 $variable2
75) Give some details on whole and partial database backups:-
We can backup entire database, or a part of it such as a tablespace or a datafile
Whole Database backup – Most commonly performed backup. It consists of all the datafiles, control file. Without the control file Oracle will not open the database, so we need the latest backup of the control file along with all the datafile backups for recovery. We can perform whole database backup in archivelog or noarchivelog mode
Partial Database backup – It can only be done if database is run in archivelog mode. Is the database is in noarchivelog mode, tablespaces and files must be in read-only mode
76) What is the purpose of restore validate command?
RESTORE..VALIDATE Command is used to check whether a certain job of interest is among RMAN backup sets.
RMAN>RESTORE TABLESPACE tablespace_name VALIDATE;
This command will ask for confirmation as if it can restore the tablespace specified.On completion of restore we get “Finished Restore” message.
77) How to restart rman backup?
If the RMAN backup fails before it completes we can resume it from the point of failure., without needing to redo the entire backup. Let us say we perform a daily backup and the last backup failed midway. We can issue the following command after the backup failure:
RMAN> BACKUP DATABASE NOT BACKED UP SINCE TIME ‘SYSDATE-1’;
BACKUP DATABASE NOT BACKED UP SINCE TIME command will backup only those files that haven’t been backed up before.
78) How to register a database in oracle RMAN?
For RMAN to perform its job, we need to register the target database we want to backup or recover.Database is enrolled in the recovery catalog
RMAN>REGISTER DATABASE;
To make sure that datafiles of target database are registered to recovery catalog use
RMAN>REPORT SCHEMA;
79) What are different types of database failure?
DBA’s important function is to safeguard enterprise data and enable users to access it with few disruptions.System Failures – Hardware-related failures. Disk drive controller may fail, disk head could be defective, system peripherals can fail, CPU, memory, power-failure may have problem, OS errors. RAC is a solution
Data Centre Disasters – Range from tornado to fire to a terrorist attack. Good solution is Dataguard. During disaster recovery session, turn the standby database into primary database with no disruption and no data loss
Human Error – People may make mistakes. DBA and system administrators can make critical mistakes
Media Failures – Damage to disks that prevent them from being read and written. Two factors are critical when disaster occurs – amount of data that becomes unavailable and needs to be replaced from backups, amount of time it might take to replace data
80) How to collect trace from RMAN backup job?
At times it becomes mandatory to understand the reason behind an RMAN issue. This includes RMAN performance slowness, indefinite backup, backups taking too long etc. AS RMAN is a product from Oracle corporation, it is developed using PL/SQL and hence RMAN trace files will be of great help as is case with sql traces
Follow simple steps to collect trace output from RMAN
rman target / log rman.log trace rman.trc debug
sql “alter session ”10046 set name context forever, level 12””;
Say backup validate datafile 1;
exit;
The above command will create trace output under diag_dest in case of 11g and can be analysed using tkprof
81) Give details on RMAN duplication types?
It becomes often mandatory to make a copy of the existing production database to create a test system for which we can make use of traditional cold backup wherein we shutdown the Oracle instance, copy the database onto new machine.
As this traditional process involves downtime, this has not been the best method always. As a good alternative Oracle has come up with its unique methods that makes use of popular Oracle backup solution RMAN
RMAN has an inherent DUPLICATE feature that makes this copy and DB recreation process easy and simple without any downtime
There are two ways to perform DUPLICATE
1) Active Database Duplication – Involving duplication of database from active production database
2) Backup-based Duplication – Involves copying backup onto destination host(server also called auxillary host) and recreating duplicate database there – Note that auxillary (or) duplicate database can be created in same host that will make use of auxillary instance
82) What is the meaning of RMAN-06171: not connected to target database?
User connects to a Catalog database and tries backing up database by issuing backup database command from catalog database.This issues the following error:
C:Documents and SettingsDBA>rman catalog rman/rman
Recovery Manager: Release 10.1.0.2.0 – Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> backup database ;
Starting backup at=======RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of backup command at 09/05/2008 11:29:09RMAN-06171: not connected to target database
RMAN>
To fix it use rman catalog rman/rman@oracle_sid
83) Give details on executing sql commands in RMAN?
We can execute few sql statements from RMAN prompt.We need to specify sql commands withinsingle quotes.
We cannot use select command :
RMAN> sql “create table rman_test_double(no int)”;sql “create table rman_test_double(no int)”;sql statement: create table rman_test_double(no int)
SQL> select distinct table_name from dba_tables where table_name like ‘RMAN%TE%’;
TABLE_NAME——————————RMAN_TESTRMAN_TEST_DOUBLE
RMAN> sql “select sysdate from dual”;
sql “select sysdate from dual”;
sql statement: select sysdate from dual
Note : We can use single as well as double quotes.
We can create database objects from within RMAN prompts. SELECT commands don’t fetch the results when used from RMAN prompt
84) What are the most salient features of RMAN backup, restore and recovery solution?
Following are some of the salient features:
Commands are easy, flexible, and very simple. Using these set of commands we can easily perform backup, restore and recovery
With RMAN the backup process is well organized and information log is maintained either in controlfile of target database or in recovery catalog database if one is in place
Optimal backup performance utilizing parallelism feature of RMAN
Backups of different types including full database backup, incremental backups performing delta change backups is possible with RMAN
Disaster recovery is possible with RMAN backups that can be restored and recovered
RMAN duplicate option enabled setting up advanced business continuity solutions like dataguard
85) What is a backup pieve file in RMAN?
The binary backup files created by RMAN are backup files
Based on format configured more than one file is created in physical disk drive that has been often configured as backup destination
20) Why is a backup piece datafile smaller in sie than original datafiles?
In RMAN backups only used blocks in datafiles are backed up. Henceforth, even in case of full backup only used data blocks are backed up and the size of file is often smaller than original database datafile size
86) What are the uses of flash recovery area and how is it related to RMAN?
IT is an optional disk area that can be used to store RMAN backups, multiplexed copies of online redo log files, control files
87) Is v$datafile same as v$datafile_header view?
Nope. V$dtafile_header view uses information from physical datafile on disk to get details on problems. Information from error and recover columns report potential problems
v$datafile view uses information from control files
88) You are looking at v$datafile_header view and see value of yes as well as null in recover colum. What does this mean?
Error columns reports description of problems. Recover column shows value yes or null and this is an indication of problem with associated datafile that can be located from datafile number that is obtained from file# column in this v$datafile_header view
89) How to fix RMAN-05020: cannot specify AUXILIARY DESTINATION option for normal recovery?
I tried performing tablespace point in time recovery popularly called as TSPITR today using oracle RMAN (recovery manager) in Oracle database. I tried issuing command without until time ‘timestamp’ and found the following error
RMAN> recover tablespace pointintime auxiliary destination ‘destination_location’;
Starting recover at
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at
RMAN-05020: cannot specify AUXILIARY DESTINATION option for normal recovery
This is an error in syntax. It needs to be UNTIL TIME ‘timestamp’
RMAN> recover tablespace pitr until time ‘mm-dd-yy hh24:mi:ss’ auxiliary destination ‘destination_location’;
This will fix the issue.
90) How will you Change Value of RMAN Compression Algorithm in an oracle database?
Oracle RMAN offers to compress the backups created by them. Compression algorithms like bzip2, zlib are being used by rman to perform compression of backups

91) How to look for compression algorithm being made use of?
It is possible to look at v$rman_compression_algorithm view and determine which algorithm RMAN uses for compressing backup. By default it is bzip2It is possible to configure a different compression algorithm as follows :
configure compression algorithm ‘zlib’;
92) How does degree of parallelism work with channel configuration in RMAN?
Oracle rman utility offers provision to configure channels before initiating database backup. Channel is a mount point onto which files are written to as part of database backup. RMAN offers configure command to configure channels. PARALLELISM is a configuration possible with RMAN utility
Here is the simple command to configure parallelism for device type disks in rman
configure device type disk parallelism 2;
The many different types of devices can be disk, sbt that are tapes used for performing backups etc
Next, we can configure two channels as follows:
configure channel 1 device type disk format ‘location1’;
configure channel 2 device type disk format ‘location2’;
In the above command the location1 and location2 correspond to two different mount points respectively
It should be noted that always degree of parallelism should match the channels configured
93) What happens when they are different?
Say the degree of parallelism is 3 and only two channels are configured. In this case 3 channels are opened by RMAN. The third channel location is going to be flash recovery area and other two are configured mount points as mentioned above. In this case you have sufficient disk space available in flash recovery area
94) How to protect rman from exposing password?
Database backup is a routine task that does happen on hourly, daily, weekly, monthly basis in the form of incremental backups, differential backups, full backups etc. As such this is a potential task that can be hacked to get details on sys user password that is crucial for business. A person with password detail can easily log onto database system and steal the data. This is a heavy security threat in businesses dealing with customer sensitive information like healthcare data, financial data, banking services, credit card services etc. This is an audit item to be kept under consideration while designing, implementing and managing databases
Here is a simple measure one can take to avoid password exposure while performing RMAN commands including backups, restore, recovery etc
1) Log into rman prompt with username only
rman target sys@oracle_sid
The above command will prompt for password. Once we enter this information this is not exposed to the reader. Make use of this approach while designing automation scripts used for RMAN backups
2) When an rman command is being run, ps -ef unix command will pull details on password. In addition to this when we run a rman script password detail is evident. To avoid this we can run the rman script with @ suffix
rman @script.rman
3) Make use of connect target inside rman script. Rather than specifying password command-line, use connect target username/password@ORACLE_SID as part of script. Say, script.rman is the script to backup database. Use the commands within script
connect target username/password@ORACLE_SID
backup database;
The above script is called as run as rman @script.rman. This will replace username/password with simple asterisk *
Make sure RMAN is used safe and password is unexposed
95) What is use of parameter DBMS_BLOCK_CHECKING?
DBMS_BLOCK_CHECKING parameter when turned on at different levels say low, medium,full offers user tablespace information check to make sure data blocks are logically self-consistent
96) You have RMAN cataog database configured. From trace files you see some corrupt blocks in RMAN catalog database. What is the way to recover corrupt blocks in a RMAN catalog database?
The best method is to make use of block media recovery feature of RMAN as there are few blocks recorded in trace file and alert.log file that has details on error ORA-01578. Make note of datafile number, block number to perform block media recovery as follows :
BLOCKRECOVER DATAFILE datafile_number BLOCK block_number_1 DATAFILE datafile_number BLOCK block_number_n;