| Recovery of database from crashed host server [message #561330] |
Fri, 20 July 2012 09:28  |
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 #561339 is a reply to message #561333] |
Fri, 20 July 2012 09:57  |
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 #561354 is a reply to message #561339] |
Fri, 20 July 2012 12:04 |
John Watson
Messages: 8962
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 #561532 is a reply to message #561386] |
Mon, 23 July 2012 11:05 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
