Disclaimer

Sunday 27 February 2022

RMAN Interview Questions

 

RMAN Interview Questions


RMAN Frequently Asked Questions
1. What is RMAN ?
Recovery Manager (RMAN) is a utility that can manage your entire Oracle backup and recovery activities.
Which Files must be backed up?
Database Files (with RMAN)
Control Files (with RMAN)
Offline Redolog Files (with RMAN)
INIT.ORA (manually)
Password Files (manually)

2.   When you take a hot backup putting Tablespace in begin backup mode, Oracle records SCN # from header of a database file.  What happens when you issue hot backup database in RMAN at block level backup? How does RMAN mark the record that the block has been backed up ?  How does RMAN know what blocks were backed up so that it doesn't have to scan them again?
In 11g, there is Oracle Block Change Tracking feature.  Once enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN's performance as it does not have to scan whole datafiles to detect changed blocks.
Logging of changed blocks is performed by the CTRW process which is also responsible for writing data to the block change tracking file. RMAN uses SCNs on the block level and the archived redo logs to resolve any inconsistencies in the datafiles from a hot backup. What RMAN does not require is to put the tablespace in BACKUP mode, thus freezing the SCN in the header. Rather, RMAN keeps this information in either your control files or in the RMAN repository (i.e., Recovery Catalog).  

3.  What are the Architectural components of RMAN?
1.RMAN executable
2.Server processes
3.Channels
4.Target database
5.Recovery catalog database (optional)
6.Media management layer (optional)
7.Backups, backup sets, and backup pieces

4.  What are Channels?
A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files. It is through the allocation of channels that you govern I/O characteristics such as:
  • Type of I/O device being read or written to, either a disk or an sbt_tape
  • Number of processes simultaneously accessing an I/O device
  • Maximum size of files created on I/O devices
  • Maximum rate at which database files are read
  • Maximum number of files open at a time

5.  Why is the catalog optional?
Because RMAN manages backup and recovery operations, it requires a place to store necessary information about the database. RMAN always stores this information in the target database control file. You can also store RMAN metadata in a recovery catalog schema contained in a separate database. The recovery catalog
schema must be stored in a database other than the target database
.
6.  What does complete RMAN backup consist of ?
A backup of all or part of your database. This results from issuing an RMAN backup command. A backup consists of one or more backup sets.

7.  What is a Backup set?
A logical grouping of backup files -- the backup pieces -- that are created when you issue an RMAN backup command. A backup set is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

8.  What is a Backup piece?
A physical binary file created by RMAN during a backup. Backup pieces are written to your backup medium, whether to disk or tape. They contain blocks from the target database's datafiles, archived redo log files, and control files. When RMAN constructs a backup piece from datafiles, there are a several rules that it follows:
  A datafile cannot span backup sets
  A datafile can span backup pieces as long as it stays within one backup set
  Datafiles and control files can coexist in the same backup sets
  Archived redo log files are never in the same backup set as datafiles or control files RMAN is the only tool that can operate on backup pieces. If you need to restore a file from an RMAN backup, you must use RMAN to do it. There's no way for you to manually reconstruct database files from the backup pieces. You must use RMAN to restore files from a backup piece.

9.  What are the benefits of using RMAN?
1. Incremental backups that only copy data blocks that have changed since the last backup.
2. Tablespaces are not put in backup mode, thus there is noextra redo log generation during online backups.
3. Detection of corrupt blocks during backups.
4. Parallelization of I/O operations.
5. Automatic logging of all backup and recovery operations.
6. Built-in reporting and listing commands.

10. RMAN Restore Preview
The PREVIEW option of the RESTORE command allows you to identify the backups required to complete a specific restore operation. The output generated by the command is in the same format as the LIST command. In addition the PREVIEW SUMMARY command can be used to produce a summary report with the same format as the LIST SUMMARY command. The following examples show how these commands are used:
# Spool output to a log file
SPOOL LOG TO c:\oracle\rmancmd\restorepreview.lst;
# Show what files will be used to restore the SYSTEM tablespace’s datafile
RESTORE DATAFILE 2 PREVIEW;
# Show what files will be used to restore a specific tablespace
RESTORE TABLESPACE users PREVIEW;
# Show a summary for a full database restore
RESTORE DATABASE PREVIEW SUMMARY;
# Close the log file
SPOOL LOG OFF;


11. Where should the catalog be created?
The recovery catalog to be used by rman should be created in a separate database other than the target database. The reason been that the target database will be shutdown while datafiles are restored.

12. How many times does oracle ask before dropping a catalog?
The default is two times one for the actual command, the other for confirmation.

13. How to view the current defaults for the database.
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u02/app/oracle/product/10.1.0/db_1/dbs/snapcf_test.f’; # default


14. Backup the database.
run
{
backup incremental level $level ${level_keyword}
tag INC${target_db}_$level database include current controlfile;
backup archivelog all not backed up 1 times delete input;
}

15. How to resolve the ora-19804 error
Basically this error is because of flash recovery area been full. One way to solve is to increase the space available for flashback database.
sql>ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G; –It can be set to K,M or G.
rman>backup database;
……………….
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 04-JUL-05
channel ORA_DISK_1: finished piece 1 at 04-JUL-05
piece handle=/u02/app/oracle/flash_recovery_area/TEST/backupset/2005_07_04/o1_mf_ncsnf_TAG20050704T205840_1dmy15cr_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 04-JUL-05
Oracle Flashback
After taking a back up resync the database.
Restoring the whole database.
run {
shutdown immediate;
startup mount;
restore database;
recover database;
alter database open;
}

16. What are the various reports available with RMAN
rman>list backup;
rman> list archive;

17. What does backup incremental level=0 database do?
Backup database level=0 is a full backup of the database. rman>>backup incremental level=0 database;
You can also use backup full database; which means the same thing as level=0;

18. What is the difference between DELETE INPUT and DELETE ALL command in backup?
Generally speaking LOG_ARCHIVE_DEST_n points to two disk drive locations where we archive the files, when a command is issued through rman to backup archivelogs it uses one of the location to backup the data. When we specify delete input the location which was backedup will get deleted, if we specify delete all all log_archive_dest_n will get deleted.
DELETE all applies only to archived logs. delete expired archivelog all;

19. How do I backup archive log?
In order to backup archivelog we have to do the following:-
run
{
allocate channel t1 type 'SBT_TAPE';
delete noprompt archivelog until time = 'sysdate-3/24';
delete noprompt obsolete;
release channel t1;
}

20. How do I do a incremental backup after a base backup?
run
{
backup incremental level $level ${level_keyword}
tag INC${target_db}_$level database include current controlfile;
backup archivelog all not backed up 1 times delete input;
}


21. In catalog database, if some of the blocks are corrupted due to system crash, How will you recover?
using RMAN BLOCK RECOVER command

22. You have taken a manual backup of a datafile using o/s. How RMAN will know about it?
You have to catalog that manual backup in RMAN's repository by command
RMAN> catalog datafilecopy '/DB01/BACKUP/users01.dbf';
restrictions:
> Accessible on disk
> A complete image copy of a single file

23. Where RMAN keeps information of backups if you are using RMAN without Catalog?
RMAN keeps information of backups in the control file.
CATALOG vs NOCATALOG
the difference is only who maintains the backup records like when is the last successful backup incremental differential etc.
In CATALOG mode another database (TARGET database) stores all the information.
In NOCATALOG mode controlfile of Target database is responsible.

24. How do you see information about backups in RMAN?
RMAN> List Backup;
Use this SQL to check
SQL> SELECT sid totalwork sofar FROM v$session_longops WHERE sid 153;
Here give SID when back start it will show SID

25. How RMAN improves backup time?
RMAN backup time consumption is very less than compared to regular online backup as RMAN copies only modified blocks

26. What is the advantage of RMAN utility?
Central Repository
Incremental Backup
Corruption Detection
Advantage over tradition backup system:
1). copies only the filled blocks i.e. even if 1000 blocks is allocated to datafile but 500 are filled with data then RMAN will only create a backup for that 500 filled blocks.
2). incremental and accumulative backup.
3). catalog and no catalog option.
4). detection of corrupted blocks during backup;
5). can create and store the backup and recover scripts.
6). increase performance through automatic parallelization( allocating channels) less redo generation.

27. List the encryption options available with RMAN?
RMAN offers three encryption modes: transparent mode, password mode and dual mode.

28. What are the steps required to perform in $ORACLE_HOME for enabling the RMAN backups with netbackup or TSM tape library software?
I can explain what are all the steps to take a rman backup with TSM tape library as follows
1.Install TDPO (default path /usr/tivoli/tsm/client/oracle/)
2.Once u installed the TDPO automatically one link is created from TDPO directory to /usr/lib.Now we need to Create soft link between OS to ORACLE_HOME
ln -s /usr/lib/libiobk64.a $ORACLE_HOME/lib/libobk.a(very imporatant)
3.Uncomment and Modify tdpo.opt file which in
/usr/tivoli/tsm/client/oracle/bin/tdpo.opt as follows
DSMI_ORC_CONFIG /usr/Tivoli/tsm/client/oracle/bin64/dsm.opt
DSMI_LOG /home/tmp/oracle
TDPO_NODE backup
TDPO_PSWDPATH /usr/tivoli/tsm/client/oracle/bin64
4.create dsm.sys file in same path and add the entries
SErvername <Server name >
TCPPort 1500
passwordacess prompt
nodename backup
enablelanfree yes
TCPSERVERADDRESS <Server Address>
5.Create dsm.opt file add an entry
SErvername <Server name >
6.Then take backup
RMAN>run
{
allocate channel t1 type 'sbt_tape' parms
'ENV (TDPO_OPTFILE /usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
backup database include current controlfile;
release channel t1;
}

29. What is the significance of incarnation and DBID in the RMAN backups?
When you have multiple databases you have to set your DBID (Database Id) which is unique to each database. You have to set this before you do any restore operation from RMAN.
There is possibility that incarnation may be different of your database. So it is advised to reset to match with the current incarnation. If you run the RMAN command ALTER DATABASE OPEN RESETLOGS then RMAN resets the
target database automatically so that you do not have to run RESET DATABASE. By resetting the database RMAN considers the new incarnation as the current incarnation of the database.

30. List at least 6 advantages of RMAN backups compare to traditional hot backups?
RMAN has the following advantages over Traditional backups:
1. Ability to perform INCREMENTAL backups
2. Ability to Recover one block of datafile
3. Ability to automatically backup CONTROLFILE and SPFILE
4. Ability to delete the older ARCHIVE REDOLOG files, with the new one's automatically.
5. Ability to perform backup and restore with parallelism.
6. Ability to report the files needed for the backup.
7. Ability to RESTART the failed backup, without starting from beginning.
8. Much faster when compared to other TRADITIONAL backup strategies.

31. How do you enable the autobackup for the controlfile using RMAN?
issue command at rman prompt.....
RMAN> configure controlfile autobackup on;
also we can configure controlfile backup format......
RMAN> configure controlfile autobackup format for device type disk to
2> '$HOME/BACKUP/RMAN/ F.bkp';
$HOME/BACKUP/RMAN/ this can be any desired location.

32. How do you identify what are the all the target databases that are being backed-up with RMAN database?
You don’t have any view to identify whether it is backed up or not . The only option is connect to the target database and give list backup this will give you the backup information with date and timing.

33. What is the difference between cumulative incremental and differential incremental backups?
Differential backup: This is the default type of incremental backup which backs up all blocks changed after the most recent backup at level n or lower.
Cumulative backup: Backup all blocks changed after the most recent backup at level n-1 or lower.

34. How do you identify the block corruption in RMAN database? How do you fix it?
using v$block_corruption view u can find which blocks corrupted.
Rman>> block recover datafile <fileid> block <blockid>;
Using the above statement u recover the corrupted blocks.
First check whether the block is corrupted or not by using this command
sql>select file# block# from v$database_block_corruption;
file# block
2 507
the above block is corrupted...
conn to Rman
To recover the block use this command...
Rman>blockrecover dataile 2 block 507;
the above command recover the block 507
Now just verify it.....
Rman>blockrecover corruption list;

35. How do you clone the database using RMAN software? Give brief steps? When do you use crosscheck command?
Check whether backup pieces proxy copies or disk copies still exist.
Two commands available in RMAN to clone database:
1) Duplicate
2) Restore.

36. What is the difference between obsolete RMAN backups and expired RMAN backups?
The term obsolete does not mean the same as expired. In short obsolete means "not needed " whereas expired means "not found."

37. List some of the RMAN catalog view names which contain the catalog information?
RC_DATABASE_INCARNATION RC_BACKUP_COPY_DETAILS
RC_BACKUP_CORRUPTION
RC_BACKUP-DATAFILE_SUMMARY to name a few

38. What is db_recovery_file_dest ? When do you need to set this value?
If Database Flashback option is on then use this option.

39. How do you setup the RMAN tape backups?
RMAN Target /
run
{
Allocate channel ch1 device type sbt_tape maxpiecesize 4g
Format' D_ U_ T_ t';
sql 'alter system switch logfile';
Backup database;
backup archivelog from time 'sysdate-7';
Backup Format ' D_CTLFILE_P_ U_ T_ t' Current controlfile;
release channel ch1;
}
This is backup script for Tivoli Backup Server

40. How do you install the RMAN recovery catalog?
Steps to be followed:
1) Create connection string at catalog database.
2) At catalog database create one new user or use existing user and give that user a recovery_catalog_owner privilege.
3)Login into RMAN with connection string
a) export ORACLE_SID           
b) rman target catalog @connection string
4) rman> create catalog;
5) register database;

41. When do you recommend hot backup? What are the pre-reqs?
Database must be Archivelog Mode
Archive Destination must be set and LOG_ARCHIVE_START TRUE (EARLIER VERSION BEFORE 10G)
If you go through RMAN then
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/oracle/autobackup/ F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/u01/app/oracle/product/10.2.0/db_2/dbs/snapcf_dba.f'; # default

42. What is the difference between physical and logical backups?
In Oracle Logical Backup is "which is taken using either Traditional Export/Import or Latest Data Pump". Where as Physical backup is known "when you take Physical O/s Database related Files as Backup".

43. What is RAID? What is RAID0? What is RAID1? What is RAID 10?
RAID: It is a redundant array of independent disk
RAID0: Concatenation and stripping
RAID1: Mirroring

44. What are things which play major role in designing the backup strategy?
I Believe in designing a good backup strategy it will not only be simply backup but also a contingency plan. In this case you should consider the following:
1. How long is the allowable down time during recovery? - If short you could consider using dataguard.
2. How long is the backup period? - If short I would advise to use RMAN instead of user managed backup.
3. If limited disk space for backup never use user managed backup.
4. If the database is large you could consider doing full rman backups on a weekend and do a incremental backup on a weekday.
5. Schedule your backup on the time where there is least database activity this is to avoid resource huggling.
6. Backup script should always be automized via scheduled jobs. This way operators would never miss a backup period.
7. Retention period should also be considered. Try keeping atleast 2 full backups. (current and previous backup).
Cold backup: shutdown the database and copy the datafiles with the help of
O.S. command. this is simply copying of datafiles just like any other text file copy.
Hot backup: backup process starts even though database in running. The process to take a hot backup is
1) sql> alter database begin backup;
2) copy the datafiles.
3) after copying
sql> alter database end backup;
Begin backup clause will generate the timestamp. it'll be used in backup consistency i.e. when begin backup pressed it'll generate the timestamp. During restore database will restore the data from backup till that timestamp and remaining backup will be recovered from archive log.

45. What is hot backup and what is cold backup?
Hot backup when the database is online cold backup is taken during shut down period

46. How do you test that your recovery was successful?
SQL> SELECT count(*) FROM flashback_table;

47. How do you backup the Flash Recovery Area?
A:RMAN> BACKUP RECOVERY FILES;
The files on disk that have not previously been backed up will be backed up. They are full and incremental backup sets, control file auto-backups, archive logs and datafile copies.

48. How to enable Fast Incremental Backup to backup only those data blocks that have changed?
A:SQL> ALTER DATABASE enable BLOCK CHANGE TRACKING;

49. How do you set the flash recovery area?
A:SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 100G;SQL> ALTER SYSTEM SET db_recovery_file_dest = ‘/u10/oradata/school’;

50. How can you use the CURRENT_SCN column in the V$DATABASE view to obtain the currentSCN?
A:SQL> SELECT current_scn FROM v$database;

51. You have taken a manual backup of a datafile using o/s. How RMAN will know about it?
You have to catalog that manual backup in RMAN's repository by command
RMAN> catalogdatafilecopy '/DB01/BACKUP/users01.dbf';
restrictions:> Accessible on disk> A complete image copyof a single file

52. In catalog database, if some of the blocks are corrupted due to system crash, How will you recover?
using RMAN BLOCK RECOVER command

53. List advantages of RMAN backups compare to traditional hot backups?
RMAN has the following advantages over Traditional backups:
1. Ability to perform INCREMENTALbackups
2. Ability to Recover one block of datafile
3. Ability to automatically backup CONTROLFILEand SPFILE
4. Ability to delete the older ARCHIVE REDOLOG files

54. How do you identify the expired, active, obsolete backups? Which RMAN command you use?
Use command:
Rman > crosscheck backup;
Rman > crosscheck archivelog all;
Rman > listbackup;
Rman > list archive logall

55. How do you enable the autobackup for the controlfile using RMAN?
RMAN> configure controlfile autobackup on;
also we can configurecontrolfile backup format......
RMAN> configure control file auto backup format for device type disk

56. How do you identify what are the all the target databases that are being backed-up with RMAN database?
You don’t have any view to identify whether it is backed up or not . The only option is connect to the target database and give list backup, this will give you the backup information with date and timing

57. What is the difference between cumulative incremental and differential incremental backups?
Differential backup: This is the default type of incremental backup which backs up all blocks changed after the most recent backup at level n or lower.
Cumulative backup: Backup all blocks changed after the most recent backup at level n-1 or lower
                    
58. Explain how to setup the physical stand by database with RMAN?
$ Export ORACLE_SID=TEST $ rman target /
 RMAN> show all;
Using target database controlfile instead of recovery catalog RMAN configuration parameters are:
 CONFIGURE RETENTIONPOLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE BACKUP OPTIMIZATION

59. What is auxiliary channel in RMAN? When do you need this?
An auxiliary channel is a link to auxiliary instance. If you do not have automatic channels configured, then before issuing the DUPLICATE command, manually allocate at least one auxiliary channel with in the same RUN command.

60. What is backup set?
RMAN can also store its backups in an RMAN-exclusive format which is called backup set. A backupset is a collection of backup pieces, each of which may contain one or more datafile backups

61. What is RMAN and how does one use it?
Recovery Manager (or RMAN) is an Oracle provided utility for backing-up, restoring and recoveringOracle Databases. RMAN ships with the database server and doesn't require a separate installation. TheRMAN executable is located in your ORACLE_HOME/bin directory.

62. What kind of backup are supported by RMAN?
Backup SetsDatafiles CopiesOS BackupWhat is the Flash Recovery Area?
A: It is a unified storage location for all recovery-related files and activities in an Oracle Database. Itincludes Control File, Archived Log Files, Flashback Logs, Control File Autobackups, Data Files, andRMAN files.

63. How do you define a Flash Recovery Area?
A: To define a Flash Recovery Area set the following Oracle Initialization Parameters.
SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 100G;
SQL> ALTER SYSTEM SET db_recovery_file_dest = ‘/u10/oradata/school’;

64. How do you use the V$RECOVERY_FILE_DEST view to display information regarding the flashrecovery area?
A:SQL> SELECT name, space_limit, space_used,space_reclaimable, number_of_filesFROM v$recovery_file_dest;

65. How can you display warning messages?
A:SQL> SELECT object_type, message_type,message_level, reason, suggested_actionFROM dba_outstanding_alerts;

66. How to use the best practice to use Oracle Managed File (OMF) to let Oracle database to create andmanage the underlying operating system files of a database?
A:SQL> ALTER SYSTEM SETdb_create_file_dest = ‘/u03/oradata/school’;
SQL> ALTER SYSTEM SETdb_create_online_dest_1 = ‘/u04/oradata/school’;

67. How to enable Fast Incremental Backup to backup only those data blocks that have changed?
A:SQL> ALTER DATABASE enable BLOCK CHANGE TRACKING;

68. How do you monitor block change tracking?
A:SQL> SELECT filename, status, bytes FROM v$block_change_tracking;
It shows where the block change-tracking file is located, the status of it and the size.

69. How do you use the V$BACKUP_DATAFILE view to display how effective the block change trackingis in minimizing the incremental backup I/O?
A:SQL> SELECT file#, AVG(datafile_blocks), AVG(blocks_read),AVG (blocks_read/datafile_blocks), AVG(blocks)FROM v$backup_datafileWHERE used_change_tracking = ‘YES’ AND incremental_level > 0GROUP BY file#;If the AVG (blocks_read/datafile_blocks) column is high then you may have to decrease the timebetween the incremental backups.

70. How do you backup the entire database?
A:RMAN> BACKUP DATABASE;

71. How do you backup an individual tablespaces?
A:RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> BACKUP TABLESPACE system;

72. How do you backup datafiles and control files?
A:RMAN> BACKUP DATAFILE 3;
RMAN> BACKUP CURRENT CONTROLFILE;
Use a fast recovery without restoring all backups from their backup location to the location specified inthe controlfile.
A:RMAN> SWITCH DATABASE TO COPY;

73. RMAN will adjust the control file so that the data files point to the backup file location and then starts recovery.Why use Rman ?
A. 1. No Extra Costs.. It is available free.
2.RMAN introduced in Oracle 8 it has become simpler with new version and easier that user managed backups.
3.Proper Security
4.You are 100% sure your database has been backed up .
5.It contains details of backup taken in the central repository
6.Facility of Testing validity of backups also command like cross check to checkthe status of backup.
7.Oracle 10g has got further optimized incremental backups with has resulted inimprovement of performance during backup
8.and recovery time
9.Parrallel operation are supported
10.Better Querying facility for knowing different details of backup.
11.No Extra redo generated when backup is taken. compared to online backup
12.Without rman.which results in saving of space in hard disk.
13.RMAN is an intelligent tool
14.Maintains repository of backup metadata.
15.Remembers backup locations
16.Knows what needs backup set locations
17.Knows what needs to be backed up
18.Knows what is required for recovery
19.Know what backups are redundant
20.It handles database corruptions

74. Oracle Enhancement for Rman in 10g
A. 1.Flash Recovery Area
2.Incrementally Updated Backups
3.Faster Incremental Backups
4.SWITCH DATABASE COMMAND.
5.Binary Compression
6.Global Scripting
7.Duration Clause
8.Configure This
9.Oracle Enhancement for Rman in 10g
10.Automatic Channel Failover
11.Compress Backup Sets
12.Recovery Through Reset Logs
13.Cross Backup Sets

75. Global Scripting
A.RMAN> print script full_backup to file 'my_script_file.txt'
Oracle Database 10g provides a new concept of global scripts, which you can executeagainst any database registered in the recovery catalog, as long as your RMAN client isconnected to the recovery catalog and a target database simultaneously.CPISOLUTION.COM
RMAN> create global script global_full_backup


76. Outline the steps for recovery of missing data file?
Losing Datafiles Whenever you are in NoArchivelog Mode:
###################################################
If you are in noarchivelog mode and you loss any datafile then whether it is temporary or permanent media failure, the database will automatically shut down. If failure is temporary then correct the underline hardware and start the database. Usually crash recovery will perform recovery of the committed transaction of the database from online redo log files. If you have permanent media failure then restore a whole database from a good backup. How to restore a database is as follows:

If a media failure damages datafiles in a NOARCHIVELOG database, then the only option for recovery is usually to restore a consistent whole database backup. As you are in noarchivelog mode so you have to understand that changes after taken backup is lost.

If you logical backup that is export file you can import that also.

In order to recover database in noarchivelog mode you have to follow the following procedure.

1)If the database is open shutdown it.

SQL>SHUTDOWN IMMEDIATE;

2)If possible, correct the media problem so that the backup database files can be restored to their original locations.

3)Copy all of the backup control files, datafiles to the default location if you corrected media failure. However you can restore to another location. Remember that all of the files not only the damaged files.

4)Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must have to do incomplete recovery:

RECOVER DATABASE UNTIL CANCEL
CANCEL

5)Open the database in RESETLOGS mode:
ALTER DATABASE OPEN RESETLOGS;

In order to rename your control files or in case of media damage you can copy it to another location and then by setting (if spfile)

STARTUP NOMOUNT
alter system set control_files='+ORQ/orq1/controlfile/control01.ctl','+ORQ/orq1/controlfile/control02.ctl' scope=spfile;
STARTUP FORCE MOUNT;

In order to rename data files or online redo log files first copy it to new location and then point control file to new location by,

ALTER DATABASE RENAME FILE '+ORQ/orq1/datafile/system01.dbf';'
TO '+ORQ/orq1/datafile/system02.dbf';

Losing Datafiles Whenever you are in Archivelog Mode:
###################################################
If the datafile that is lost is under SYSTEM tablespace or if it is a datafile contain active undo segments then database shuts down. If the failure is temporary then correct the underline hardware and start the database. Usually crash recovery will perform recovery of the committed transaction of the database from online redo log files.

If the datafile that is lost in not under SYSTEM tablespace and not contain active undo segments then the affected datafile is gone to offline. The database remains open. In order to fix the problem take the affected tablespace offline and then recover the tablespace. 

77. Outline the steps for recovery with missing online redo logs?
 Redo log is CURRENT (DB was shut down cleanly)
If the CURRENT redo log is lost and if the DB is closed consistently, OPEN RESETLOGS can be issued directly without any transaction loss. It is advisable to take a full backup of DB immediately after the STARTUP.
 Redo log is CURRENT (DB was not shut down cleanly)
When a current redo log is lost, the transactions in the log file are also lost before making to archived logs. Since a DB startup can no more perform a crash recovery (since all the now-available online log files are not sufficient to startup the DB in consistent state), an incomplete media recovery is the only option. We will need to restore the DB from a previous backup and restore to the point just before the lost redo log file. The DB will need to be opened in RESETLOGS mode. There is some transaction loss in this scenario.
RMAN> RESTORE CONTROLFILE FROM '<backup tag location>';
RMAN> ALTER DATABASE MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE UNTIL TIME "to_date('MAR 05 2009 19:00:00','MON DD YYYY HH24:MI:SS')";
RMAN> ALTER DATABASE OPEN RESETLOGS;

78. Outline steps for recovery with missing archived redo logs?
If a redo log file is already archived, its loss can safely be ignored. Since all the changes in the DB are now archived and the online log file is only waiting for its turn to be re-written by LGWR (redo log files are written circularly) the loss of the redo log file doesnt matter much. It may be re-created using the command
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CLEAR LOGFILE GROUP <group#>;
This will re-create all groups and no transactions are lost. The database can be opened normally after this.

79. What is FRA ? When do you use this ?
Flash recovery area where you can store not only the traditional components  found in a backup strategy such as control files, archived log files, and Recovery Manager (RMAN) datafile copies but also a number of other file
components such as flashback logs. The flash recovery area simplifies backup operations, and it increases the availability of the database because many backup and recovery operations using the flash recovery area can be performed when the database is open and available to users.

Because the space in the flash recovery area is limited by the initialization parameter DB_ RECOVERY_FILE_DEST_SIZE , the Oracle database keeps track of which files are no longer needed on disk so that they can be deleted when there is not enough free space for new files. Each time a file is deleted from the flash recovery area, a message is written to the alert log.

A message is written to the alert log in other circumstances. If no files can be deleted, and the recovery area used space is at 85 percent, a warning message is issued. When the space used is at 97 percent, a critical warning is
issued. These warnings are recorded in the alert log file, are viewable in the data dictionary view DBA_OUTSTANDING_ALERTS , and are available to you on the main page of the EM Database Control

80. What is Channel? How do you enable the parallel backups with RMAN?
Channel is a link that RMAN requires to link to target database. This link is required when backup and recovery operations are performed and recorded. This channel can be allocated manually or can be preconfigured by using
automatic channel allocation.

The number of allocated channels determines the maximum degree of parallelism that is used during backup, restore or recovery. For example, if you allocate 4 channels for a backup operation, 4 background processes for the operation can run concurrently.

Parallelization of backup sets allocates multiple channels and assigns files to specific channels. You can configure parallel backups by setting a PARALLELISM option of the CONFIGURE command to a value greater than 1 or by
manually allocating multiple channels.

RMAN> CONFIGURE DEVICE TYPE PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;

81. What are RTO, MTBF, and MTTR?
RTO: Recovery Time objective-is the maximum amount of time that the database can be unavailable  and still stasfy SLA's
MTBF (Meant tiem Between Failure)-
MTTR (Mean tie to recover)-  fast recovery solutions

82. How do you enable the encryption for RMAN backups?
If you wish to modify your existing backup environment so that all RMAN backups are encrypted, perform the following steps:
· Set up the Oracle Encryption Wallet
· Issue the following RMAN command:

RMAN> CONFIGURE ENCRYPTION ALGORITHM 'AES256'; -- use 256 bit encryption
RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON; -- encrypt backups

83. What is the difference between restoring and recovering?
Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.
Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.
SQL> connect SYS as SYSDBA
SQL> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;
RMAN> run {
  set until time to_date('04-Aug-2004 00:00:00', 'DD-MON-YYYY HH24:MI:SS');
  restore database;
  recover database;
}

What are the various tape backup solutions available in the market?
How do you generate the begin backup script?
Outline the steps for recovering the full database from cold backup?
Explain the steps to perform the point in time recovery with a backup which is taken before the resetlogs of the db?
Outline the steps involved in TIME based recovery from the full database from hot backup?
Is it possible to take Catalog Database Backup using RMAN? If Yes, How?
Can a schema be restored in oracle 9i RMAN when the schema having numerous table spaces?
Outline the steps for changing the DBID in a cloned environment?
How do you identify the expired, active, obsolete backups? Which RMAN command you use?
Explain how to setup the physical stand by database with RMAN?
List the steps required to enable the RMAN backup for a target database?
How do you verify the integrity of the image copy in RMAN environment?
Outline the steps involved in SCN based recovery from the full database from hot backup?
Outline the steps involved in CANCEL based recovery from the full database from hot backup?
Outline the steps involved in TIME based recovery from the full database from hot backup?
Is it possible to specific tables when using RMAN DUPLICATE feature? If yes, how?
Explain the steps to perform the point in time recovery with a backup which is taken before the resetlogs of the db?
Outline the steps for recovering the full database from cold backup?
How do you generate the begin backup script?


RMAN Question & Answers


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

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

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

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

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

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

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

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

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

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

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

Note:
  RMAN do not take backup of the unformatted blocks

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

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

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

5) RMAN terminology:

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

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

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

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

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

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

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

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

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

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

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

RMAN Catalog Database

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

10) How Many catalog database I can have?

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

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



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

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


14)  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:

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

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

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

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

18)  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

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

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

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

21) What is RMAN consistent backup and inconsistent backup?

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

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

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

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

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


24) Can I compress RMAN backups?

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

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

25) Can I encrypt RMAN backup?

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

26)  Can RMAN take backup to Tape?

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


27) How RMAN Interact with Media manager?

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

28) What is Proxy copy backup to tape?

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

29) What is Oracle Secure backup?

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

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

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

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

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

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

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

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

 

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

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

 

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

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

35) What are the corruption types?

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


Scenarios:

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

Solution:

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

RMAN> backup validate check logical database;

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

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

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

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

5 rows selected.


Datafile Block Corruption - Intra block corruption

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

Solution1:


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

$ rman target /
RMAN> backup check logical database;

Solution2:

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

Solution3: DBVerify - Identify Datafile Block Corruptions

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

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

RMAN Vs DBVerify - Datafile Intra Block Corruption

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

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

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



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

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

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

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

To limit the effect of this additional logging, you should ensure you only place one tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon as you have backed it up. This will reduce the number of blocks that may have to be logged to the minimum possible. 
Try to take the hot/online backups when there is less / no load on the database, so that less redo will be generated.

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




at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

100 Oracle DBA Interview Questions and Answers

  Here are 100 tricky interview questions tailored for a Senior Oracle DBA role. These questions span a wide range of topics, including perf...