We are on Oracle 9i on Linux.
We take an export of our data daily on one of the client machines using Oracle export utility. This export is done when the operations are going on i.e. the operations are not shut down during the export. Recently, we have put the database in archivelog mode. My understanding was that we need to take a cold backup of our datafiles and if there is a crash, we need to apply the archive logs to this cold backup and recover. However, my DBA tells me that we need not take a cold backup. If there is a crash, we can take the export DMP file and apply the archive logs to the same. Is this correct ? If not, what should be the best strategy to insulate us from risk of loss of data ?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Also Pasword files and SPFILEs.
ATTENTION! After taking the database in ARCHIVELOG immediatelly 'shutdown' the DB and take a cold backup.
If this is not done your backup is not consistent. Because Export do not saves the SCNs of the datafiles.
If you do a new 'incarnation of the DB' - this means coldbackup and new flow of archive logs
then open the DB with resetlog option to begin the numeration of the archive logs from 1.
----> "If there is a crash, we can take the export DMP file and apply the archive logs to the same."
If this is really what your DBA said, I do not believe it is true.
The problem is that the DB is crashed totally.
You get another machine, install Oracle from scratch.
OK!
But how will you create the nonsystem tablespaces and nonsystem users?
What about the sequence of SCNs in the file headers?
Remember:
1. A total restore of the DB includes cold backup and all archive logs after this (reseting the archive sequence)
2. Export dump files can be used only to restore the database to the point of time of the Export.
No! You *CANNOT* use archived redo logs in combination with an export! Archived redo logs can *ONLY* be used for database recovery in combination with a backup. This backup can be either a cold backup (taken following a clean database shutdown) or it can be a hot backup taken with the database open.
Oracle exports certainly have value, and I like having them in addition to backups, but you cannot apply archived redo logs to a database that you restore based on an export. The reason is: archived redo logs contain changes at the physical block level. If you ever restore a database from an export, many records will likely end up in different blocks than they were in in the source database. One advantage of exports: it is much easier to recover an individual table from an export than from a backup. But a full database recovery from an export will take longer than from a backup. Also, a recovery from an export will only recover data up to the point in time when the export was taken. With a backup, plus archived redo logs, you can recover data added or changed after the backup up through the last archived redo log.
So you see that your DBA is wrong.
To fix the problem:
1. Shutdown the machine.
2. Do a cold backup
3. Startup the instance with 'resetlog' option
4. Drop all old archive logs, the new one will be only valid
5. Transfer in binary mode the files from the cold backup on another box or archiving device.
6. Do the same for all newly created archived logs files.
7. regularly check if you really can recreate the DB from all this stuff on a spare training box.
Business Accounts
Answer for Membership
by: ploubierPosted on 2006-03-08 at 00:12:28ID: 16130962
You can use an export (taken with option full=y of course) to recover your DB, but you'll have to create the database manually first.
In my opinion, the best strategy is a combination of :
- RMAN hot backups
- cold backups (as often as instance is shut down) using RMAN or your backup software
- user-level exports several times a day
- frequent backups of archived logs
And remember : cold backups must include all DB-related files (datafiles, control files, and redo log files).