using RMAN BLOCK RECOVER command
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
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.
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
How RMAN improves backup time?
RMAN backup time consumption is very less than compared to regular online backup as RMAN copies only modified blocks
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.
List the encryption options available with RMAN?
RMAN offers three encryption modes: transparent mode password mode and dual mode
What are the steps required to perform in $ORACLE_HOME for enabling the RMAN backups with netbackup or TSM tape library software?
I can expalin 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
TCPPort 1500
passwordacess prompt
nodename backup
enablelanfree yes
TCPSERVERADDRESS
5.Create dsm.opt file add an entry
SErvername
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;
}
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.
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.
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.
How do you identify what are the all the target databases that are being backed-up with RMAN database?
You dont have any view to identifiy 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 timining.
What is the difference between cumulative incremental and differential incremental backups?
Differencial 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 cahnged after the most recent backup at level n-1 or lower.
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
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;
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.
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."
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
What is db_recovery_file_dest ? When do you need to set this value?
If Database Flashback option is on then use this option.
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
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;
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
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".
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
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.
What is hot backup and what is cold backup?
Hot backup when the database is online cold backup is taken during shut down period
What are RTO, MTBF, and MTTR?
What are the various tape backup solutions available in the market?
How do you generate the begin backup script?
How do you enable the encryption for RMAN backups?
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?
Outline the steps for recovery of missing data file?
Outline the steps for recovery with missing online redo logs?
Outline steps for recovery with missing archived redo logs?
What is auxiliary channel in RMAN? When do you need this?
What is Channel? How do you enable the parallel backups with RMAN?
How do you enable the encryption for RMAN backups?
What is backup set?
What is FRA ? When do you use this ?
How do you generate the begin backup script?
How do I go about backing up my online redo logs?
You don’t. Online redo logs should never, ever be included in a backup, regardless of whether that backup is performed hot or cold. The reasons for this are two-fold. First, you physically cannot backup a hot online redo log, and second there is precisely zero need to do so in the first place because an archive redo log is, by definition, a backup copy of a formerly on-line log. There is, however, a more practical reason: backing up the online logs yourself increases the risk that you will lose committed data.
In short, it’s not necessary, it’s not possible, and it’s dangerous to even try.
Can you explain why it’s not possible a bit more? I can, of course, issue ‘copy *.log’ commands at the command prompt whenever it suits me, so it would seemt o be very possible for me.
The golden rule in Oracle backups is: you cannot ever copy anything hot without the resulting copy being, internally, complete garbage. That’s because it takes a finite amount of time to copy a file, and during that time the contents of the original may change. The copy will therefore end up with bits of itself at one time, and bits at other times.
Now, Oracle provides a mechanism to patch up that sort of mess when it encounters it inside a copied-then-restored data file. The mechanism is called “recovery”, and it works by applying redo to the internal bits of the data file so that the oldest bits get rolled forward till they ‘catch up’ to the youngest bits. Eventually, the entire file gets to one, consistent, point of time and can be rolled forward from there. In other words, redo makes internally inconsistent data files internally consistent and usable.
But you only apply redo to data files. A recovery does not apply redo to control files. Instead, Oracle provides a different mechanism to permit hot backups of the control file: alter database backup controlfile to ‘c:\somewhere’. This is a SQL command that generates a read-consistent image of the control file. It’s guaranteed by Oracle to be internally consistent in the first place, so it needs no redo applied to it to make it usable.
In both of these cases, therefore, Oracle has provided a mechanism to make it possible to take hot copies of data files and control files -one to prevent internal inconsistencies in the first place, and one to sort them out after the are enountered. But here’s the punch line: neither of those mechanisms applies to online redo logs. Since there is no ‘repair inconsistencies’ or ‘avoid inconsistencies’ mechanism for online redo logs, it follows that they cannot be copied hot without the contents of the copies being immediately rendered 100% useless.
Right. I can understand not copying them if I’m doing hot backups. I can see that now. But actually, I’m doing cold backups. Presumably these considerations don’t apply, and I could take a copy of the files if I wanted to?
You could, it’s true. A cold file can be copied at your leisure: by definition, its contents aren’t going to change whilst you’re copying it, so there is no risk of internal corruption or inconsistency in the copy. But you still have to face the “it’s pointless” argument.
For a start, you’re presumably in archive log mode, and ARCn has been busy taking copies of every one of your online logs for you. You shutting down the database and taking a fresh copy of one or two of them doesn’t exactly bring much that’s new or beneficial to the party. What’s more, the second you open up your database after taking the new backup, the contents of your backup are out of date.
Third, and most important, you never need to restore online redo logs to perform any database recovery -so what is the point of having a backup of something you will never actually need to use?
But it’s not actually true, is it, that ARCn has copied every log already -because it doesn’t copy the CURRENT online log, until after it ceases to be the current one, does it?
Quite right. There is always one log (and only one log, incidentally) which definitely hasn’t been archived by ARCn yet, and that’s the one which is currently being written to by LGWR (and hence has a status of CURRENT in the V$LOG dynamic performance view). But try and think logically: if that log is truly ‘CURRENT’, it must be in use and is therefore hot... and you can’t copy it because the copy will be internally inconsistent and unusable. If the database is shut down, then the current log isn’t truly ‘current’ (because there’s no LGWR to write to it!). It’s cold, and could be copied -but you never need to copy it, because the copy would be instantly out of date once you restart the instance, and in any case you never need to restore online redo logs under any circumstances. Either way you look at it, you either can’t copy the current log, or there’s no point in doing so.
What you are really saying, of course, is that you are worried that ARCn has not yet copied the CURRENT log, and are feeling a bit nervous about that. I totally agree that you should be nervous about this: the current redo log is definitely the weakest point in the entire Oracle srchitecture, and its loss would indeed result in committed data being lost.
But those are not grounds to copy the current log. Those are grounds to make sure you never lose the current log in the first place -and to do that, you should be employing hardware mirroring and Oracle multiplexing (making each log group consist of multiple members).
OK, I accept that it is pointless and unnecessary. But it won’t exactly do any harm if I do back them up (cold!) will it?
Yes it will, actually. Or, rather, yes it could.
Imagine a production environment in which you have, despite all advice to the contrary, taken backups of your online redo logs (hot or cold, it makes no difference). Suppose you lose a small and fairly unimportant data file from that system. Recovery should be a piece of cake: restore the damaged data file, recover datafile X, alter tablespace X online. All committed data back, no sweat. Now imagine that in the heat of the moment, you were to restore *.dbf, instead of just x.dbf... well, recovery takes a lot longer, and the database is down for the duration, completely unnecessarily, because SYSTEM has to be recovered. But you still get all your committed data back, no worries. A totally successful recovery that just wasn’t performed as efficiently as it might have been.
Now take that scernario one stage further: if the restore had been of *.*, rather than *.dbf. At that point, you have just overwritten your existing online redo logs with old versions that were backed up last night. Now the loss of some of those up-to-date logs are not a problem, because you’ve got archives of them. But the loss of the previously current log, and its replacement by an out-of-date backup is terminal: there’s no way to replace the redo that log contained, and hence you have just lost committed data.
Now you might laugh at that, and say you would never be so stupid. But it’s happened. I’ve seen it happen. Oh, and confession-time: I’ve done it myself. In short, the mere presence of the online logs in a backup set is a risk. One over-eager restore operation later, and committed data will have been lost that shouldn’t have been.
I don’t mind doing unnecessary things when ‘there’s no harm’ in doing it. But in the case of backing up your online redo logs, it is not only unnecessary but it can definitely do harm.
I have a note here in Oracle’s own documentation: ‘In cases where the entire database needs to be restored, the process is simplified if the online redo logs have been backed up’. So Oracle itself says it’s OK to do it.
That is because Oracle documentation describes what is technically possible, not what is pragmatically the safest or best thing to do. If you have closed your database down cleanly, then you can make a copy of the ‘online’ logs which will not be internally inconsistent. I said as much earlier. But there is no *need* to do so.
It is again true that the recovery process in the event of complete loss of the database with backups of redo logs available would be something along the lines of:
copy c:\backup\*.* c:\oracle\ora92
startup
But the equivalent without the redo logs present would be:
copy c:\backup\*.dbf c:\oracle\ora92
copy c:\backup\*.ctl c:\oracle\ora92
startup mount;
recover database until cancel using backup controlfile;
cancel;
alter database open resetlogs;
Which is definitely a bit more complex and a fair bit more typing. Is it so outrageously more complex, however, that it is worth risking the loss of committed data to avoid having to do it this way? I certainly don’t think so. If care and concern for your organisation’s data doesn’t move you to a similar viewpoint, just consider: a DBA that manages to lose committed data usually loses his or her job shortly thereafter. Self-interest and self-preservation alone should cause you to avoid taking unnecesary risks when the risk-minimised approach is not exactly rocket science.
So yes, the Oracle documentation is correct: cleanly shutdown databases technically can have their redo logs backed up. But I’m right too: common sense, safety, caution and pragmatism dictates that you never, ever back them up.
And if you follow my advice, it will always be correct, whether your database is hot, cold, in archivelog mode or noarchivelog mode. If you follow Oracle’s advice, however, you have to remember that it only applies to noarchivelog databases which have been shutdown cleanly. And, personally, I like laws of nature which are always true, not working shortcuts whose precise applicability depends on a variety of factors.
Finally, take a look at RMAN, the backup utility written and supplied by Oracle. It is syntactically impossible to get that tool to backup online redo logs. That ought to tell you something: Oracle’s own best practice is not to back them up. So follow their lead: leave your online logs out of your backups!
No comments:
Post a Comment