OraFAQ Forum: Backup & Recovery » recovery scenario

Home » RDBMS Server » Backup & Recovery » recovery scenario (10.2.0.1,xp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

recovery scenario [message #314449]

Wed, 16 April 2008 14:13

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

varu123
Messages: 754
Registered: October 2007

Senior Member

That was very quick LittleFoot.

Report message to a moderator

Re: recovery scenario [message #314491 is a reply to message #314449]

Wed, 16 April 2008 21:54

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.

Report message to a moderator

Re: recovery scenario [message #314492 is a reply to message #314449]

Wed, 16 April 2008 21:55

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.

Report message to a moderator

Re: recovery scenario [message #314528 is a reply to message #314449]

Thu, 17 April 2008 01:33

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?

Report message to a moderator

Re: recovery scenario [message #314697 is a reply to message #314528]

Thu, 17 April 2008 09:07

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';

Report message to a moderator

Re: recovery scenario [message #314764 is a reply to message #314449]

Thu, 17 April 2008 13:27

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 17:35:32 CST 2024