Solved

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

Posted on 2004-09-29
10
700 Views
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

Regards

Dan
0
Comment
Question by:Dan_Morley
10 Comments
 
LVL 12

Expert Comment

by:geotiger
Comment Utility

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

Expert Comment

by:SDutta
Comment Utility
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.

0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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.
or:
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.
0
 

Author Comment

by:Dan_Morley
Comment Utility
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?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Dan_Morley
Comment Utility
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?
0
 
LVL 10

Expert Comment

by:SDutta
Comment Utility
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.

SDutta
0
 

Author Comment

by:Dan_Morley
Comment Utility
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!!

Dan

ps..Think I'll go out and buy Oracle 9i for dummies this w'end ;  )
0
 
LVL 10

Accepted Solution

by:
SDutta earned 500 total points
Comment Utility
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.
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96519.pdf

and the 8i version if you are using that
http://download-east.oracle.com/docs/cd/A87860_01/doc/index.htm
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

763 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now