Can you restore oracle using only hot <online, noarchive mode!> backups?

Using Arcserve, our Oracle system <win2k server> is backed up nightly. The database is never closed during this time but the CA BAOF agent is active on that server. The database is running in noarchive mode. For piece of mind I decided to put the backup tapes into practice and perform a restore on a test win2k server box. Bloody good job Ive done this exercise because I cant manage to get Oracle to function correctly using the backed up datafiles.

After installing Oracle 8i and restoring the backed up datafiles into place oracle was able to mount without an issue but when attempting to fully open, it displays:
ORA-01122 Failed Validation check
ORA-01110 on QTEMP1.DBF
ORA-01207 File is more recent than controlfile - old controlfile

After viewing many FAQ pages I performed a recover database until cancel using backup of controlfiles and it completed but displaying this:
ORA-01194 File 1 needs to be more consistent
ORA-01110 Data file 1 /oracle/oradata/system01.DBF
Then I attempted to startup again using alter database open resetlogs now I continue to get the same message as above!

I then added _allow_resetlog_corruption = true in my Pfile and tried to startup and it fails with:
ORA-00603 Fatal Error

I used the DBV tool and checked my datafiles and it comes up with:
"Blocksize 2048 differs from actual 8192" on all my DBF/CTL files

I then removed the new line out of my Pfile and tried again and miraculously the database opened and I am able to view the instance, schema, datafiles, tablespace etc apart from the SGA parameters. At this point I attempted to connect via sqlplus but it comes up with:
ORA-01033 Initialisation or shutdown in progress and it wont permit the login

Is what Im trying to achieve still feasible or do I need to re-think by backup strategy to include archivemode and full exports of the database?

Any advise particularly on my technical issue but also on backup strategy would be greatly appreciated


Who is Participating?
SDuttaConnect With a Mentor Commented:
Reading up is always good, testing it is even better.

Here is the link for the "Oracle9i Backup and Recovery Concepts, Release 2 (9.2)" manual.

and the 8i version if you are using that

When you create your new database, you need to make sure it has the exactly the same block size.  since your database is in no archive mode, you need to shut down your database and do full backup (cold backup).
You cannot create a valid backup of a database in NOARCHIVE mode without shutting it down first, it cannot be completely recovered otherwise.

To do a hot backup (while database is open) it must be in ARCHIVELOG mode and you have to use one or more of the archivelogs along with the backup to recover it completely.

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Mark GeerlingsDatabase AdministratorCommented:
SDutta is correct, you cannot do a hot backup of an Oracle database that is in NOARCHIVE mode.  (To be more precise, you actually can sometimes do a backup that way as you confirmed - you just can't ever use that backup for anything!)

Your options are:
1. Run in archivelog mode *AND* use backup software that is Oracle-aware to make sure that it puts each tablespace in backup mode as it is being backed up.
2. Do an orderly (not "shutdown abort") shutdown of Oracle, then do the backup, then restart Oracle.

We use option 2, since we don't need our database to be up all day on Sundays.  We also have enough disk space so that when we shut Oracle down, we can do a quick disk-to-disk copy of the Oracle data, log and control files, then restart the database, then do the backup later using the Oracle files from the alternate disk location, not the actual database files that are in use when the backup happens.
Dan_MorleyAuthor Commented:
Thanks for the advise on future backup strategy and best practice. However, I am still unable to bring the database into open status. Am I wasting my time with this exercise based on the fact I have these useless backups or is there a way using RMAN/SVRMGRL to rollback the system to a stable date?
Dan_MorleyAuthor Commented:
Just to clarify I now receive the below message when starting up the database using svrmgrl using startup open:

ORA-01194 File 1 needs to be more consistent
ORA-01110 Data file 1 /oracle/oradata/system01.DBF

Is it possible to perform a TSPITR on this datafile to resolve?
Hi Dan,

With a database in NOARCHIVE mode there is no recovery possible. Either it comes up ok (if the backup is good) or not. I think it is futile to spend any more time on a backup that was not done as per Oracle's recommended methods.

I hope this was just an exercise in backup/recovery and your original database and data is still intact.

Dan_MorleyAuthor Commented:
Yes Fortunately this was an exercise :) but a good one at that. I will implement a cold backup strategy and TEST!!! immediately : ) Thanks guys for all the advise!!


ps..Think I'll go out and buy Oracle 9i for dummies this w'end ;  )
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.