Home » RDBMS Server » Backup & Recovery » recovery scenario (10.2.0.1,xp)
recovery scenario [message #314449] Wed, 16 April 2008 14:13 Go to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1247900 bytes
Variable Size              75498852 bytes
Database Buffers           88080384 bytes
Redo Buffers                2945024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'K:\ORAREP92\ORADATA\ORCL10G\USERS01.DBF'


 SQL> 1  alter database create datafile 'K:\orarep92\oradata\orcl10g\users01.dbf'
  2 as 'k:\orarep92\oradata\orcl10g\users.dbf'
SQL> /
alter database create datafile 'K:\orarep92\oradata\orcl10g\users01.dbf'
*
ERROR at line 1:
ORA-01178: file 4 created before last CREATE CONTROLFILE, cannot recreate
ORA-01110: data file 4: 'K:\ORAREP92\ORADATA\ORCL10G\USERS01.DBF'




If file 4 is not created before last create controlfile then how would control file know about file 4?

Why the error?


EDIT:sorry for posting again in wrong forum as i visit server adminsitration more.

[Updated on: Wed, 16 April 2008 14:14]

Report message to a moderator

Re: recovery scenario [message #314450 is a reply to message #314449] Wed, 16 April 2008 14:23 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
That was very quick LittleFoot.
Re: recovery scenario [message #314491 is a reply to message #314449] Wed, 16 April 2008 21:54 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
That's the problem, the file was created BEFORE the last CREATE CONTROLFILE. The "new" controlfile doesn't have sufficient information in it to recover the file. Therefore, you must restore the file from backup and recover from there.
Re: recovery scenario [message #314492 is a reply to message #314449] Wed, 16 April 2008 21:55 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

1)Find a backup of the file, and recover it.

Or,
2)Do incomplete recovery to time before file was originally created.
Re: recovery scenario [message #314528 is a reply to message #314449] Thu, 17 April 2008 01:33 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I did not create any new controlfile.

I did create this tablespace later on.

Quote:
That's the problem, the file was created BEFORE the last CREATE CONTROLFILE.


So is for all the tablespaces.

Let me understand it:

1)If a database is created with 4 tablespace and later on if itry to recover any datafile by the empty datafile way,there is no problem
because the controlfile has sufficient information about all the files
2)if i create a database with 4 tablespaces and later on issue create controlfile statement,then neither of the datafiles can be recovered with empty datafile way.
3)if I create a database with 4 tablespaces and issue create controlfile statement and after that create another tablepsace "xyz",then only "xyz" can be recovered with create empty file way.

Is this the way it has to be?
Re: recovery scenario [message #314697 is a reply to message #314528] Thu, 17 April 2008 09:07 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
That's strange. What does the following show:

select controlfile_created from v$database;
select creation_time from v$datafile
	where name = 'K:\ORAREP92\ORADATA\ORCL10G\USERS01.DBF';
Re: recovery scenario [message #314764 is a reply to message #314449] Thu, 17 April 2008 13:27 Go to previous message
varu123
Messages: 754
Registered: October 2007
Senior Member
SQL> select controlfile_created from v$database;

CONTROLFI
---------
12-FEB-08

SQL> select creation_time from v$datafile
  2   where name = 'K:\ORAREP92\ORADATA\ORCL10G\USERS01.DBF';

CREATION_
---------
30-AUG-05

SQL> select created from v$database;

CREATED
---------
12-FEB-08


I don't believe this time .
I don't remember exactly but 12-feb-08 might be when i created the database and hence controlfile.

But 30-AUG-05 is impossible.This database is not that much old.

There is some anomaly with the time.
I shall test some scenarios and see what happens.

[Updated on: Thu, 17 April 2008 13:50]

Report message to a moderator

Previous Topic: Rman backup valid or not
Next Topic: HOW TO GET THE EXPDAT.DMP FILE
Goto Forum:
  


Current Time: Fri Dec 09 07:57:26 CST 2016

Total time taken to generate the page: 0.13793 seconds