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

Posted on 2004-09-29
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

724 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