[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


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

Posted on 2004-09-29
Medium Priority
Last Modified: 2011-09-20
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


Question by:Dan_Morley
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 12

Expert Comment

ID: 12180820

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).
LVL 10

Expert Comment

ID: 12181241
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.

LVL 35

Expert Comment

by:Mark Geerlings
ID: 12181962
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.
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments


Author Comment

ID: 12189662
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?

Author Comment

ID: 12189703
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?
LVL 10

Expert Comment

ID: 12190303
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.


Author Comment

ID: 12191635
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 ;  )
LVL 10

Accepted Solution

SDutta earned 2000 total points
ID: 12192410
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question