Recovery Question
Paul
I am using Oracle 10.2.0.2.0 on Windows Server 2003.
We had a machine die on us whilst the Oracle instance was not running.
We have no backups (the data isn't really critical, and it is customer
data so we cannot back it up anyway).
Anyway, the machine died. It has been rebuilt with different drive
letters. We have all of the data files, control files etc and so I
think it is probably possible to sort this out. I tried recreating the
instance, backing up control file to trace, starting it in NOMOUNT
mode, then editing the create control file statement to include the
data files etc.
I kept getting errors about sizes not being correct, and it appeared
that for a particular size of 7GB, it just would not work!
What I want to know is how people would approach this problem in order
to recover. The instance was not running, I have all the files, so it
should be relatively easy. But I have never done this before, and I
cannot really find any info out about it.If anybody can suggest
anything or point me to an article on how to do this then that would
be appreciated.
Please don't remind me of the importance of backups - the data is a
customer database with confidential data so we cannot back it up.
Many Thanks in advance!
Paul
fitzj...@cox.net
I expect that since Windows Server 2003 is a 32-bit 'operating system'
you will have problems with files greater than 2 GB in size. Possibly
you won't have this restriction with raw partitions.
Was this database ever running?
David Fitzjarrell
sybrandb
Seems we are entering another prize draw, as your request calls for
clairvoyance and crystal balls, none of which are usually available.
Maybe Catweazle could do something about it (just saw a fragment on
Dutch Television yesterday: very funny: Catweazle went completely
bonkers when the boy switched the light on).
That said one would usually indeed recreate instance and the
controlfile, making sure the original controlfiles are there, and
*they are identical* (just overwriting them with the most recent one
is usually OK).
As you don't specify what happened, I can not provide an explanation.
As to the 7 Gb oddity: without exact specifications about whether
Oracle and O/S are both 32-bit, or are both 64-bit, or you are running
the wrong edition of Oracle (very easy as each 64-bit CPU has it's own
port): who can tell?
As to the person who decided not to back up a database because the
data is confidential, I would recommend an appearance in Tom Kyte's
upcoming 'Oracle worst practices' web seminar. What use is
maintaining the database by a 3rd party, if the 3rd party isn't
allowed to make a back up? Doesn't signing up an SLA implies something
tiny as *confidence*?
--
Sybrand Bakker
Senior Oracle DBA
Paul
God I love helpful people - do you want me to spend an hour explaining
why we can't back up the data or do you just assume that in every
single situation you are right and the poster is an idiot? I stated
the data is not backed up for a reason, I even said don't bother
mentioning it.
We do not 'maintain the data' for anybody. We supply software to
customers. They maintain their own databases. Every now and again they
have a problem, either with the data, or with performance. As some of
our customers have very large databases, these are the best databases
for us to use for either problem investigation, or for performance
tuning. So we use them. They are not backed up as quite frankly we do
not give a stuff what happens to this data - we do not need to recover
it. Their databases contain confidential data, and part of the
agreement is that we have to either anonymise their database prior to
them shipping it, or we cannot back it up. Is that clear?
Now that I have wasted the time explaining that, back to the original
point.
The original database was running 10.2.0.2.0, with all data files
stored in d:\oracle\oradata\<Instance>. The instance was not running
when it died. It is the 32-bit version of WIndows and Oracle.
The machine has been rebuilt,but I now need all of the data to be in f:
\oracle\oradata\<instance>
I cannot just recreate the instance, then overwrite all of the files,
because as far as the control files are concerned the data files all
live in drive d: When I tried copying the original control files
across it gave the error "could not identifiy control file".
I do actually have an export of the data prior to the point at which
the machine died - all that actually happened by the way is that the
disk with the Oracle Home on it died completely.
I could quite easily recreate the instance and import the data, but I
hoped that I could find somebody helpful to allow me to expand my
experience.
I do not see that this would be a different process whether using 32-
bit or 64-bit,and I do not really see what ports have to do with
anything either. I realise that people here have vastly greater
experience than myself, which is why I was hoping for a bit of help,
maybe others would rather just put people trying to learn down rather
than coming up with any helpful suggestions.
fitzj...@cox.net
32-bit operating systems can access no more than 4 GB of memory,
period, and as such usually inflict a file size restriction of 2 GB;
this puts your 7 GB file 'out of the ball park' as far as
accessibility and 'recognizability' to the software. With 64-bit
releases the number of available memory addresses increases almost
exponentially, and with that the availability of single files larger
than 2 GB.
I can't understand how you
1) built a datafile larger than 2 GB with this software
2) had a database running so you could load data with that
configuration
Your best bet in this instance is to simply recreate the database,
with files sized to 2 GB or less, then import your recent export and
go from there.
David Fitzjarrell
Cristian Cudizio
I'm not sure to have completely understand: if you have your origina
control files you don't have to recreate them,
where controlfiles are is written into spfile (you can use textual
pfile to start with), then you have to start database in mount mode,
make
alter database rename datafile ...
then you can open database.
To increase your experience use with i've written and what is written
on manuals (exact sintax)
If windows 2003 , it is 32 bit or 64 has not a size limit of 2GB if
you use NTFS that limit is of fat32 and i hope you have not a server
with fat32 filesystem.
Bye
Cristian Cudizio
http://oracledb.wordpress.com
http://cristiancudizio.wordpress.com
Cristian Cudizio
wrote:
P.S.
on my PC, with pentium D (i think 32 bit) and Windows XP i've files of
20GB.
bye
fitzj...@cox.net
>
> - Show quoted text -
XP is not Server 2003.
David Fitzjarrell
StefanKapitza
The max Filesize depends on the Filesystem and the API a Program
is using.
regards
s.kapitza
Niall Litchfield
> 32-bit operating systems can access no more than 4 GB of memory,
> period, and as such usually inflict a file size restriction of 2 GB;
> this puts your 7 GB file 'out of the ball park' as far as
> accessibility and 'recognizability' to the software.
The memory section is sort of true, though it oversimplifies somewhat
as people using 32bit linux servers with > 4gb ram can testify. The
file size assertion is just wrong. NTFS file sizes are limited to
2^^44 (-64k) bytes. (http://www.microsoft.com/technet/prodtechnol/
windows2000serv/reskit/core/fncc_fil_tvjq.mspx?mfr=true) though in
fact, depending on oracle database version datafile size may be more
limited than that. In fact you'll see that back in the 8i/NT4 days
11gb was just fine (http://tinyurl.com/3x2zhz) it was 4gb that was a
problem :(. I believe certain *nix filesystems were limited to 2gb in
those days.
Niall Litchfield
Oracle DBA
DA Morgan
> God I love helpful people - do you want me to spend an hour explaining
> why we can't back up the data or do you just assume that in every
> single situation you are right and the poster is an idiot? I stated
> the data is not backed up for a reason, I even said don't bother
> mentioning it.
In answer to your statement above you may not be an idiot but you've
put yourself into a situation that is idiotic.
Very simply: Anyone using an Oracle database under restrictions that the
data can not be backed up is playing Russian roulette. There is no
question a disaster will strike. The issues is when ... not if. And it
will happen again and again and again.
Does the no backup rule also restrict you from using RAID or just common
sense?
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Paul
Thanks to everybody who has replied sensibly and tried to help.
I tried to explain why backups were not used - there is no point in
wasting time implementing RAID when this is merely throw away data - I
just wanted to experiment and see what I could actually do instead of
having to recreate the schemas and re-import the data.
There are some people who clearly believe that helping anybody
properly is beneath them - I feel sorry for those people, maybe you
are just having a bad day as there are 2 of you ( and I am sure you
know who you are ) who I see helping people very frequently - I think
you always give good advice and I believe that you have helped me in
the past so I am not sure why sarcasm/pretentiousness got the better
of you today.
Daniel - I cannot see how your post is an attempt to help in the
slightest - if you have time to waste lecturing people on good
practice, or trying to make them look stupid then please feel free -
this is not how you usually respond but clearly my post annoyed you.
Reading books will always help but sometimes there are things that
need to be asked of people with the experience - if all they can do is
criticise then I wish they would not bother as this merely impedes on
the learning process.
I don't particularly want to make enemies in here, as most people are
very helpful most of the time!
Anyway, if anybody ever needs to do this then this is what I have done
in order to sort this out:
1) Create a new instance with the same name as the old instance
2) Copy all of the files from the old instance over the top of the
newly created files
3) Startup the instance in MOUNT mode
4) Use the ALTER DATABASE... RENAME FILE command, to update the
control files to point to the new locations
5) Open the database
This worked for me, I expect only because the DB was closed at the
time!
Thanks again to everyone for their information!
Paul
sybr...@hccnet.nl
wrote:
>There are some people who clearly believe that helping anybody
>properly is beneath them - I feel sorry for those people, maybe you
>are just having a bad day as there are 2 of you ( and I am sure you
>know who you are ) who I see helping people very frequently - I think
>you always give good advice and I believe that you have helped me in
>the past so I am not sure why sarcasm/pretentiousness got the better
>of you today.
I don't appreciate you carefully suppressed I *did* provide helpful
advice. But then you didn't provide any clues at all to outline the
exact situation, requiring for crystal balls and clairvoyance. I admit
providing error messages to people who are trying to help you out *for
free* was probably asked too much.
I would recommend you direct similar questions in the future to Oracle
Technical Support. At least *they* are getting paid to deal with posts
without any details.
hjr.p...@gmail.com
wrote:
Yikes! So much misunderstanding in one post!
A 32-bit operating system has no problem whatsoever with files bigger
than 2GB. It would be odd maths indeed if that was the case, since
2^32 implies a 4GB limit, not a 2GB one!
As it is, on 32-bit Windows using NTFS, you are allowed files up to
about 16TB in size. (See, for example, http://www.pcguide.com/ref/hdd/file/ntfs/files_Files.htm
and/or http://en.wikipedia.org/wiki/NTFS)
In Oracle itself, you have a problem of not being able to address more
than 2^22 database blocks (because that's the number of bits used to
store unique block numbers within a file), which means 4 million
blocks for a 32 bit installation -which, at 8K per block, would imply
an Oracle file size limit of 32GB (the maths changes if you use
bigfile tablespaces).
Paul
>What I want to know is how people would approach this problem in order
>to recover. The instance was not running, I have all the files, so it
>should be relatively easy. But I have never done this before, and I
>cannot really find any info out about it.If anybody can suggest
>anything or point me to an article on how to do this then that would
>be appreciated.
This is the part of my post in which I asked for help. I asked how
people would approach it. I did not ask anybody to use a crystal ball
and decipher random error messages. I laid out quite clearly what had
happened and merely asked people to suggest how they would approach
such a problem.
Anyway, as I said it was rather simple to solve in the end. In fact it
took a lot less time than reading these posts.
hjr - thanks for fully explaining the file size thing - I did think it
rather strange that somebody thought this but didn't have time to
start digging!
DA Morgan
> Anyway, as I said it was rather simple to solve in the end. In fact it
> took a lot less time than reading these posts.
> hjr - thanks for fully explaining the file size thing - I did think it
> rather strange that somebody thought this but didn't have time to
> start digging!
Howard is one of the best in the business but your comment at the end is
totally out of line. We are not paid and it is not our responsibility to
dig or research or do anything on your behalf. For you to criticize
someone for trying to help based on their best understanding is
inappropriate.
Noons
wrote:
>
> > P.S.
> > on my PC, with pentium D (i think 32 bit) and Windows XP i've files of
> > 20GB.
>
> > bye
>
> > Cristian Cudizio
>
>
> > - Show quoted text -
>
> XP is not Server 2003.
Agreed, but something is amiss here:
we run server2003-sp2 and haven't had a problem
with db files in the 10GB size range. IIRC,
NTFS files go all the way to some huge number,
16TB or something? Doesn't mean Oracle can address
all that without the bigfiles thing, but it certainly can go
over 2 and 4GB file sizes. Nothing to do with the
32-bit limit, that is mostly for process space and that
can be sort of bypassed - a-la "expanded memory" - up
to about 64GB with the AWE switch.
Paul
Paul wrote
> Anyway, as I said it was rather simple to solve in the end. In fact it
> took a lot less time than reading these posts.
> hjr - thanks for fully explaining the file size thing - I did think it
> rather strange that somebody thought this but didn't have time to
> start digging!
>Howard is one of the best in the business but your comment at the end is
>totally out of line. We are not paid and it is not our responsibility to
>dig or research or do anything on your behalf. For you to criticize
>someone for trying to help based on their best understanding is
>inappropriate.
Daniel - you misunderstood what I meant here - I meant that I (me
personally) didn't have time to start digging i.e. researching further
based on the comments made by Howard. This all started because I did
not like the tone that was taken with me at the very beginning.
Believe me, I fully appreciate the time that people spend helping me,
as many times I find this help invaluable. I fully acknowledge that
nobody has to do anything to help me in the slightest. This is why I
generally use this ng as a last resort after I have looked at the
documentation and at metalink. I meant no offence to anybody and you
certainly took what I meant out of context.
In fact if I had meant that Howard should be doing the digging then to
be honest I would have expected a far harsher response! If you look at
my original post I was merely asking for pointers to sites/books to
aid me, this is generally how I work as otherwise you never learn
anything!
How about getting back on to what the topic has now become - the file
size discussion?
You took what I wrote completely differently to how it was meant -
next time I will try to be clearer!
Paul
DA Morgan
> Daniel wrote
> Paul wrote
>> Anyway, as I said it was rather simple to solve in the end. In fact it
>> took a lot less time than reading these posts.
>> hjr - thanks for fully explaining the file size thing - I did think it
>> rather strange that somebody thought this but didn't have time to
>> start digging!
>
>> Howard is one of the best in the business but your comment at the end is
>> totally out of line. We are not paid and it is not our responsibility to
>> dig or research or do anything on your behalf. For you to criticize
>> someone for trying to help based on their best understanding is
>> inappropriate.
>
> Daniel - you misunderstood what I meant here - I meant that I (me
> personally) didn't have time to start digging i.e. researching further
> based on the comments made by Howard.
Understand. Thanks for the clarification.