Home » RDBMS Server » Backup & Recovery » Trying to recover database after disk failure (Oracle 81)
Trying to recover database after disk failure [message #505631] Wed, 04 May 2011 06:09 Go to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
Hi,

I am a Oracle newbie.

I have a situation in that our server died , multiple disk failure, the database was open at the time.

We have managed to recover the data from the disk and have put it on another disk.

We have got a new server with oracle 9 running.

A blank database has been created.

I have copied the control files and redo files onto the new server and left the datafiles , (.dbf,.ora) on a seperate external disk F drive (this is because the original database resided on F drive)

I can mount the database , however when I try to open it I get the following

ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'F:\ORACLE\ORADATA\LCMPRO\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile

Any help would be appreciated

Daryl


Re: Trying to recover database after disk failure [message #505636 is a reply to message #505631] Wed, 04 May 2011 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What have you from the previous database?
* spfile (if used) or init.ora
* control files
* data files
* log files
* archived log files?

Which backup(s) have you (of each above category)? And which kind (RMAN, cp...)?

Regards
Michel


Re: Trying to recover database after disk failure [message #505638 is a reply to message #505636] Wed, 04 May 2011 06:49 Go to previous messageGo to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
Hi Michael,

Unfortunately we have no back of any kind

init.ora
all the control files
all the datafiles
all the redo.lof files
no archived log files (there was no folder recovered)

Thanks for your response.

Daryl
Re: Trying to recover database after disk failure [message #505653 is a reply to message #505638] Wed, 04 May 2011 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
init.ora
all the control files
all the datafiles
all the redo.lof files

Is this the list of the files you have from before the failure? Or is this the list of the files you lost?
Are you in archivelog or noarchivelog mode?

Regards
Michel

[Updated on: Wed, 04 May 2011 07:04]

Report message to a moderator

Re: Trying to recover database after disk failure [message #505656 is a reply to message #505653] Wed, 04 May 2011 07:09 Go to previous messageGo to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
this is the list that we have

i think it was in noarchivelog mode

Daryl
Re: Trying to recover database after disk failure [message #505661 is a reply to message #505656] Wed, 04 May 2011 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why have you this message "file is more recent than controlfile - old controlfile"?
Did you take an old control file or is this the current one?
What exactly failed?
What was on what has failed?

Regards
Michel
Re: Trying to recover database after disk failure [message #505798 is a reply to message #505661] Thu, 05 May 2011 01:23 Go to previous messageGo to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
Hi Michel,

These are the ones that were recovered from the failed disk.
2 disks out of raid 5 system
Databases and contorl files on the failed raid array.

Daryl
Re: Trying to recover database after disk failure [message #505803 is a reply to message #505798] Thu, 05 May 2011 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
These are the ones that were recovered from the failed disk

How did you recover them?
Actually I am unable to understand the picture you have.
Describe step by step what happened and what you did.

Regards
Michel
Re: Trying to recover database after disk failure [message #505810 is a reply to message #505803] Thu, 05 May 2011 02:14 Go to previous messageGo to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
We had a company recover the files for us.

The server hard disks failed .

Sent the disks to a company to recover the data, received the recovered data.

Constructed a new server and installed oracle 9 , created a new blank database structure.This a inventory tracking database.

Ensured the new database worked. (new database same structure).

Deleted all files from the oradata folder and copied the control and redo files that were recovered .

All the other database related files reside on another disk labeled F , as they were originally on a F drive.

Tried to mount the database and had a problem with 1 of the control files of blocksize mismatch ,did some googling and found that if i deleted the control file (03) that was causing the problem and copy one of the other ones (02) and rename it . This worked and I was able to mount the database.

I can mount the database , however when I try to open it I get the following

ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'F:\ORACLE\ORADATA\LCMPRO\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile


Daryl





Re: Trying to recover database after disk failure [message #505816 is a reply to message #505810] Thu, 05 May 2011 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove all what you did, drop the new database and service.

Copy the files you got from the restore (that is from your previous post, control files, data files and redo log files). Have you the old init.ora or spfile? If yes copy it to %ORACLE_HOME%\database, if not recreate the init.ora from either previous alert.log if you have it or from memory.

Recreate the Oracle service using ORADIM.

Startup the database and post what you have.

Regards
Michel

Re: Trying to recover database after disk failure [message #505820 is a reply to message #505816] Thu, 05 May 2011 03:01 Go to previous messageGo to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
Quote:
Remove all what you did, drop the new database and service.What do you mean drop database (delete contents of LCMPRO in oradata ?)

Copy the files you got from the restore (that is from your previous post, control files, data files and redo log files). Have you the old init.ora or spfile? If yes copy it to %ORACLE_HOME%\database, if not recreate the init.ora from either previous alert.log if you have it or from memory.

Copy files to where ? What about the fact the original files where on the F drive ?


Recreate the Oracle service using ORADIM.

Is this a SQl command ?
Sorry about the questions as this is my first adventure in Oracle



Daryl

Re: Trying to recover database after disk failure [message #505823 is a reply to message #505820] Thu, 05 May 2011 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) Yes drop ALL what you did.
2) Copy to their original location (if you can't then tell me but you said you already did it, so redo it)
3)
C:\>oradim
ORADIM: <command> [options].  Refer to manual.
Enter one of the following command:
Create an instance by specifying the following options:
     -NEW -SID sid | -SRVC srvc | -ASMSID sid | -ASMSRVC srvc [-SYSPWD pass]
 [-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]
 [-SHUTMODE normal|immediate|abort] [-TIMEOUT secs] [-RUNAS osusr/ospass]
Edit an instance by specifying the following options:
     -EDIT -SID sid | -ASMSID sid [-SYSPWD pass]
 [-STARTMODE auto|manual] [-SRVCSTART system|demand] [-PFILE file | -SPFILE]
 [-SHUTMODE normal|immediate|abort] [-SHUTTYPE srvc|inst] [-RUNAS osusr/ospass]
Delete instances by specifying the following options:
     -DELETE -SID sid | -ASMSID sid | -SRVC srvc | -ASMSRVC srvc
Startup services and instance by specifying the following options:
     -STARTUP -SID sid | -ASMSID sid [-SYSPWD pass]
 [-STARTTYPE srvc|inst|srvc,inst] [-PFILE filename | -SPFILE]
Shutdown service and instance by specifying the following options:
     -SHUTDOWN -SID sid | -ASMSID sid [-SYSPWD pass]
 [-SHUTTYPE srvc|inst|srvc,inst] [-SHUTMODE normal|immediate|abort]
 Query for help by specifying the following parameters: -? | -h | -help

So "oradim -new <sid> ..."

Regards
Michel

[Updated on: Thu, 05 May 2011 03:27]

Report message to a moderator

Re: Trying to recover database after disk failure [message #505844 is a reply to message #505823] Thu, 05 May 2011 04:25 Go to previous messageGo to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
Hi Michel

Here is the contents of the alert log

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
shared_pool_size = 159383552
large_pool_size = 8388608
java_pool_size = 0
control_files = F:\oracle\oradata\lcmpro\control01.ctl, F:\oracle\oradata\lcmpro\control02.ctl, F:\oracle\oradata\lcmpro\control03.ctl
db_block_buffers = 55461
db_block_size = 8192
compatible = 8.1.0
log_buffer = 32768
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
db_files = 1024
db_file_multiblock_read_count= 8
max_enabled_roles = 30
remote_login_passwordfile= EXCLUSIVE
db_domain = world
global_names = FALSE
instance_name = lcmpro
service_names = lcmpro.world
utl_file_dir = *
job_queue_processes = 15
parallel_max_servers = 5
background_dump_dest = f:\oracle\admin\lcmpro\bdump
user_dump_dest = f:\oracle\admin\lcmpro\udump
max_dump_file_size = 10240
oracle_trace_collection_name=
sort_area_size = 65536
sort_area_retained_size = 65536
db_name = lcmpro
open_cursors = 300
os_authent_prefix =
Obsolete system parameters with specified values:
distributed_transactions
End of obsolete system parameter listing
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
Thu May 05 19:07:19 2011
/* OracleOEM */ ALTER DATABASE MOUNT
Thu May 05 19:07:19 2011
ORA-00218: block size 729227280 of controlfile does not match DB_BLOCK_SIZE (8192)
ORA-00202: controlfile: 'F:\oracle\oradata\lcmpro\control03.ctl'

Thu May 05 19:07:19 2011
ORA-209 signalled during: /* OracleOEM */ ALTER DATABASE MOUNT...
Re: Trying to recover database after disk failure [message #505848 is a reply to message #505844] Thu, 05 May 2011 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This will give you the parameters you have to put in your init.ora file if you didn't recover it.

Is this is the alert.log you get after executing the steps I mentioned or is this the original one?

Regards
Michel
Re: Trying to recover database after disk failure [message #505850 is a reply to message #505848] Thu, 05 May 2011 04:57 Go to previous messageGo to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
is the alert log after doing the steps you mentioned

ORA-00218: block size 729227280 of controlfile does not match DB_BLOCK_SIZE (8192)
ORA-00202: controlfile: 'F:\oracle\oradata\lcmpro\control03.ctl'

the above is the error on the screen after trying to open the database using enterprise manager console in standalone mode and using the original init.ora file

Daryl
Re: Trying to recover database after disk failure [message #505851 is a reply to message #505850] Thu, 05 May 2011 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So maybe control file control03.dbf is corrupted, remove it from the init.ora list and retry.

Please copy and paste ALL what you do otherwise I can't know what you really have and so can't help efficiently.

Regards
Michel

[Updated on: Thu, 05 May 2011 05:00]

Report message to a moderator

Re: Trying to recover database after disk failure [message #505853 is a reply to message #505851] Thu, 05 May 2011 05:12 Go to previous messageGo to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
Hi Michel,

ORA - 01122:database file 1 failed verification check
ORA - 01110:data file :'f:\oracle\oradata\lcmpro\system01.dbf'
ORA - 01207:file is more recent than controlfile - old controlfile

in the alert was the following

/* OracleOEM */ ALTER DATABASE MOUNT
***
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Fractured block found during control file header read
Data in bad block -
type: 21 format: 2 rdba: 0x00000001
last change scn: 0xffff.0030026e seq: 0x1 flg: 0x04
consistency value in tail: 0xf4df1501
check value in block header: 0x3671, computed block checksum: 0xf6b1
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Informational message:
Controlfile 0 has seq# 3146350, lowest 3146350 file 1, cksret 1
***
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Fractured block found during control file header read
Data in bad block -
type: 21 format: 2 rdba: 0x00000001
last change scn: 0xffff.0030026e seq: 0x1 flg: 0x04
consistency value in tail: 0xf4df1501
check value in block header: 0x3671, computed block checksum: 0xf6b1
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Informational message:
Controlfile 0 has seq# 3146350, lowest 3146350 file 1, cksret 1
***
Corrupt block relative dba: 0x00000001 (file 0, block 1)
Fractured block found during control file header read
Data in bad block -
type: 21 format: 2 rdba: 0x00000001
last change scn: 0xffff.0030026e seq: 0x1 flg: 0x04
consistency value in tail: 0xf4df1501
check value in block header: 0x3671, computed block checksum: 0xf6b1
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Informational message:
Controlfile 0 has seq# 3146350, lowest 3146350 file 1, cksret 1
Thu May 05 20:00:50 2011
kccrsz: expanded controlfile section 1 from 1 to 4 records
requested to grow by 3 record(s); added 1 block(s) of records
Thu May 05 20:00:51 2011
Successful mount of redo thread 1, with mount id 167473581.
Thu May 05 20:00:51 2011
Database mounted in Exclusive Mode.
Completed: /* OracleOEM */ ALTER DATABASE MOUNT
Thu May 05 20:00:51 2011
/* OracleOEM */ ALTER DATABASE OPEN
ORA-1122 signalled during: /* OracleOEM */ ALTER DATABASE OPEN ...

Thanks for this help , this is the last night of trying myself before sending to a professional company.

Daryl
Re: Trying to recover database after disk failure [message #505864 is a reply to message #505853] Thu, 05 May 2011 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
sending to a professional company

Do it, I can't see what you do and get.

Regards
Michel
Re: Trying to recover database after disk failure [message #505866 is a reply to message #505864] Thu, 05 May 2011 06:13 Go to previous messageGo to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
Hi Michel,

I was trying to do the recovery to save our company some money and time.

Can we go any further with the results we have ?

Daryl
Re: Trying to recover database after disk failure [message #505867 is a reply to message #505866] Thu, 05 May 2011 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, I can't see what you do and get.
I see you use OEM, you should not, you should use SQL*Plus.
You are too inexperienced for me to be able to help you remotely and seeing nothing of what happens.

Regards
Michel
Re: Trying to recover database after disk failure [message #505868 is a reply to message #505867] Thu, 05 May 2011 06:18 Go to previous messageGo to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
hi Michel,

I can use SQL plus window , is that what you are meaning ?

I was just being lazy and using a GUI

Daryl
Re: Trying to recover database after disk failure [message #505875 is a reply to message #505868] Thu, 05 May 2011 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't be lazy when you do a recovery.
You are unable to tell me what the tool executed so you are unable to know in which status it is.
Drop all again, use CMD box and SQL*Plus and copy and paste all what you do and get.

Regards
Michel
Re: Trying to recover database after disk failure [message #505883 is a reply to message #505875] Thu, 05 May 2011 08:15 Go to previous messageGo to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
Hi Michel,

Here is what i got after doing everything again

C:\oracle\ora81>sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu May 5 23:03:54 2011

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup pfile='c:/oracle/admin/lcmpro/pfile/initlcmpro.ora'
ORA-25138: DISTRIBUTED_TRANSACTIONS initialization parameter has been made
ORACLE instance started.

Total System Global Area 656259796 bytes
Fixed Size 453332 bytes
Variable Size 201326592 bytes
Database Buffers 454336512 bytes
Redo Buffers 143360 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'F:\ORACLE\ORADATA\LCMPRO\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile

In your opinion is this database recoverable ?

Thanks

Daryl
Re: Trying to recover database after disk failure [message #505886 is a reply to message #505883] Thu, 05 May 2011 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Drop all again, use CMD box and SQL*Plus and copy and paste all what you do and get.


Regards
Michel
Re: Trying to recover database after disk failure [message #505888 is a reply to message #505883] Thu, 05 May 2011 08:27 Go to previous messageGo to next message
hoppy707
Messages: 13
Registered: May 2011
Junior Member
Hi Michel,

I am going home now , what time do you com online tommorrow ?

Daryl
Re: Trying to recover database after disk failure [message #505890 is a reply to message #505888] Thu, 05 May 2011 08:50 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I come when I want to come and when my own job let me some time to come.

Regards
Michel
Previous Topic: how do i apply an level_1 backup to a level_0 backup ?
Next Topic: ORA-19573 flashing during RMAN recovery -plz help!!
Goto Forum:
  


Current Time: Fri Mar 29 01:04:46 CDT 2024