Home » RDBMS Server » Backup & Recovery » Recovery of database from crashed host server (10gR2, Window 2003 server)
Recovery of database from crashed host server [message #561330] Fri, 20 July 2012 09:28 Go to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
I am opening this new thread because it is a slightly differente scenario from my previous thread.

Backgroud info:
host server crashed.
Database was not cleanly shutdown.
Database is not in archivelog mode.
Datafiles were saved.

My goal:
I want to recover the database based on the available files.

My approach:
Install new database.
ALTER DATABASE BACKUP CONTROLFILE TO TRACE.
shutdown database.
Place available dbf files in the location for new database, replacing existing files.
edit trace file to create new controlfile. Script is:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "newdatabase" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\REDO01.LOG'  SIZE 50M,
  GROUP 2 'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\REDO02.LOG'  SIZE 50M,
  GROUP 3 'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\REDO03.LOG'  SIZE 50M
DATAFILE
  'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\SYSTEM01.DBF',
  'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\UNDOTBS01.DBF',
  'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\SYSAUX01.DBF',
  'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\USERS01.DBF',
  'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\EXAMPLE01.DBF',
  'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\AREA01.DBF',
  'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\AREA02.DBF'
CHARACTER SET WE8MSWIN1252
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE 'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\TEMP01.DBF'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;


Question(s):
Will this work ? Is there a step I am missing ?
Re: Recovery of database from crashed host server [message #561333 is a reply to message #561330] Fri, 20 July 2012 09:38 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Try it, and you'll find that it will not work: the database won't open. The error message will give you some insight into what you must do.
Re: Recovery of database from crashed host server [message #561334 is a reply to message #561330] Fri, 20 July 2012 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Will this work ?
no
> Is there a step I am missing ?
way too many to list
To start, DBIDs are different.

You REALLY, Really, really would benefit from actually reading & understanding content of Concepts Manual

[Updated on: Fri, 20 July 2012 09:39]

Report message to a moderator

Re: Recovery of database from crashed host server [message #561339 is a reply to message #561333] Fri, 20 July 2012 09:57 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
"Try it, and you'll find that it will not work: the database won't open. The error message will give you some insight into what you must do."


So there is something that can be done after all ?

I would appreciate some guidance to minimize trial and error. Please help.
Re: Recovery of database from crashed host server [message #561345 is a reply to message #561330] Fri, 20 July 2012 10:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
jxh461 wrote on Fri, 20 July 2012 07:28
I am opening this new thread because it is a slightly differente scenario from my previous thread.

Backgroud info:
host server crashed.
Database was not cleanly shutdown.
Database is not in archivelog mode.
Datafiles were saved.

My goal:
I want to recover the database based on the available files.


I want to teach my goat to fly.

Realize that not every goal is obtainable including both goals above.
Re: Recovery of database from crashed host server [message #561351 is a reply to message #561330] Fri, 20 July 2012 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not change the answer of you previous topic.
How database was shut down before all the files were deleted is irrelevant.
You restore your files, you restore (or recreate the control file if you didn't save it, what a shame!) and open ressetlogs.

Regards
Michel
Re: Recovery of database from crashed host server [message #561354 is a reply to message #561339] Fri, 20 July 2012 12:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your problem in this scenario is that after a disorderly shutdown, your datafiles are inconsistent. Yes, you can create a new controlfile: but without the online logs, you can never make the datafiles consistent. Therefore, you will never be able to open the database.
There is (of course!) a hidden parameter that will let you open the database in such circumstances, but that is far beyond the scope of your question.
Re: Recovery of database from crashed host server [message #561356 is a reply to message #561354] Fri, 20 July 2012 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Indeed, I assumed the datafiles were from a backup.
If they are the current data files, of course, what I said can't work.

Regards
Michel
Re: Recovery of database from crashed host server [message #561371 is a reply to message #561356] Fri, 20 July 2012 16:03 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
If you cannot open a database, you might want to investigate further on the following init.ora/spfile.ora parameter

_allow_read_only_corruption=true (the default=FALSE)

I just started working at a shop and I had a development system with 4 databases on it that shutdown hard with no backups. I was able to
1) Restart one db without any problems.
2) Restart one db after typing in "recover database" and "alter database open resetlogs"
3) Restart one db by putting "_allow_read_only_corruption=true" in the init.ora and after the database opened, I checked for
corruption with "dbv" and there were only indexes that I dropped and recreated to get around the corruption.
4) The fourth db I put "_allow_read_only_corruption=true" in the init.ora but the database was so corrupt that almost all selects failed after opening it.

Using "_allow_read_only_corruption=true" is only as a last resort.
Re: Recovery of database from crashed host server [message #561386 is a reply to message #561371] Sat, 21 July 2012 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT advice underscore/hiddent parameter in a forum, they WILL (not would) be used out of the correct context and in inappropriate case, they WILL be used by people that do NOT understand what they do and what the parameter implies.
I already saw such a database running for months with such a "allow corruption" parameters just because once it has allowed them to open the database.

Remember, the greater part of forum readers are beginners and unskilled people.

Regards
Michel
Re: Recovery of database from crashed host server [message #561532 is a reply to message #561386] Mon, 23 July 2012 11:05 Go to previous messageGo to next message
jxh461
Messages: 185
Registered: March 2005
Senior Member
After doing further research, I actually introduced the parameter
_allow_resetlogs_corruption=true
in my pfile and this is the result:


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 23 11:44:14 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: sys / as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "SYS"


SQL> select instance_name,status,database_status from v$instance;

INSTANCE_NAME    STATUS       DATABASE_STATUS
---------------- ------------ -----------------
newdatabase      MOUNTED      ACTIVE

SQL> [b]shutdown immediate[/b]
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> [b]startup[/b]
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             171969412 bytes
Database Buffers          432013312 bytes
Redo Buffers                7135232 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> [b]alter database open resetlogs;[/b]
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\SYSTEM01.DBF'



SQL> [b]alter database open noresetlogs;[/b]
alter database open noresetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'J:\ORACLE\PRODUCT\10.2.0\ORADATA\newdatabase\SYSTEM01.DBF'


SQL>


I am trying to learn from more knowlegable/experienced dba if there is a chance to recover anything in my situation
Re: Recovery of database from crashed host server [message #561548 is a reply to message #561386] Mon, 23 July 2012 15:11 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
Michel,

I see the danger. I will be more careful in the future.

Re: Recovery of database from crashed host server [message #561841 is a reply to message #561330] Wed, 25 July 2012 11:57 Go to previous message
shaneone1
Messages: 8
Registered: August 2009
Junior Member
Too bad you weren't in archivelog mode. You might have been able to apply archivelogs. Your best bet and probably only option is to restore from a good backup.
Previous Topic: RMAN backup strategy for active-active deployments using streams
Next Topic: Rman-0600 internal error [9018]
Goto Forum:
  


Current Time: Fri Mar 29 10:27:25 CDT 2024