OraFAQ Forum: Backup & Recovery » ORA-01180: can not create SYSTEM datafile

Home » RDBMS Server » Backup & Recovery » ORA-01180: can not create SYSTEM datafile

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

ORA-01180: can not create SYSTEM datafile [message #263561]

Thu, 30 August 2007 06:32

naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore

Member

Hi,

I am studying Backup and Recovery I am doing user-managed Backup and Recovery since i have not yet learnt RMAN.

My database is running in Archive Log mode and i have all the archives.
I came across a scenario of "Recreating DATAFILE" and deleted one non-system datafile and i tried this command:-

Alter database create datafile 'C:\ORACLE\ORADATA\VENKAT\Nav01.DBF';

It worked, but i deleted SYSTEM datafile purposely and when i tried to recreate it gave error:-

SQL> Alter database create datafile 'C:\ORACLE\ORADATA\VENKAT\SYSTEM01.DBF';
alter database create datafile 'C:\ORACLE\ORADATA\VENKAT\SYSTEM01.DBF'
*
ERROR at line 1:
ORA-01180: can not create datafile 1
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\VENKAT\SYSTEM01.DBF'

Question:-
1) I searched on net and i came to know we cannot recreate SYSTEM datafile but i was not able to get information why we cannot recreate SYSTEM datafile but only we can recreate Non-SYSTEM datafile.
2) What should be my next step to solve this problem.

Please guide me.

Regards,
Naveen

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #263562 is a reply to message #263561]

Thu, 30 August 2007 06:38

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

1) It is like you are dead. How you can bring breath in your life?

2) If you have backup then restore it.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #263563 is a reply to message #263561]

Thu, 30 August 2007 06:41

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

You will be lucky if you have backup your system tablespace otherwise you will lost your database.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #263564 is a reply to message #263563]

Thu, 30 August 2007 06:45

naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore

Member

Hi Arju,

I have one doubt we can recreate Non-System datafile and apply archives and redologs to it but why cant we recreate SYSTEM datafile and apply archives and redologs to it.

Regards,
Naveen

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #263567 is a reply to message #263561]

Thu, 30 August 2007 06:56

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

You can't drop or recreate or rename system tablespace as it contains the data dictionary. It is created at the time of database creation.

The answer is like same if you don't have life can you be able to move your hands?

You can't recreate it because it is integrated with database creation. You can't drop it because the metadata that is needed to drop it hold by itself.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #263569 is a reply to message #263567]

Thu, 30 August 2007 07:06

naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore

Member

Hi Arju,

Since i dont have SYSTEM datafile, can i recreate the same databse with left over:-
Datafiles
ControlFiles
RedoLogFiles
ArchiveLogFiles

That time will the SYSTEM datafile contains same information as previous(Before deleting)?

Regards,
Naveen

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #263570 is a reply to message #263561]

Thu, 30 August 2007 07:10

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

No. You can't . You will loss everything. No way.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #263606 is a reply to message #263570]

Thu, 30 August 2007 08:45

naveenkumar001
Messages: 62
Registered: July 2007
Location: Bangalore

Member

Hi,

I dont no properly but i am making a guess.

System Datafile:-

A System Datafile cannot be recreated because the Archives and RedoLogs cannot contain information such as Privilegs, names of all the users, Columns of the table etc that are present in the Data Dictionary.

Non-System Datafile:-

A Non-System Datafile can be recreated because the Archives and RedoLogs contain Changed Data(DML) which is needed for recreation of Non-System Datafile.

Please guide me.

Regards,
Naveen

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #263637 is a reply to message #263606]

Thu, 30 August 2007 10:04

lecorr
Messages: 17
Registered: October 2006
Location: Courbevoie, France

Junior Member

Hi,

system tablespace is your head
control files is your heart.
other files hands and feet.

You can cut anything BUT your head or your are dead (medecine can now replace the heart, you can rebuild control files).

Oracle's the same.

So you're dead.

BUT computers are not people, so if you have a backup of your system tablespace, you may be able to restore it then rebuild the complete database.
Other thing, if you deleted your system tablespace (or any other tablespace) while the database was running; in fact most of OS's will keep the file alive until you release the locks by stopping the database. So, if this happens, you can export the complete database then stop it (and it'll be dead), then recreate it.

Regards.

PS: I think I'm right, tell me if not.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #264151 is a reply to message #263561]

Sat, 01 September 2007 03:23

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

NIce one.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #264761 is a reply to message #263561]

Tue, 04 September 2007 08:51

d.c.b.a
Messages: 44
Registered: March 2005
Location: China

Member

You cannot drop the first system tablespace datafile, it's the first datafile of the whole database, not all of changes on this database recorded in the archived log.

However you can drop and recreate the system datafile you just created.

Thanks.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #264764 is a reply to message #264761]

Tue, 04 September 2007 08:55

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

However you can drop and recreate the system datafile you just created.

How?

Regards
Michel

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #264800 is a reply to message #264764]

Tue, 04 September 2007 10:56

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

Quote:

However you can drop and recreate the system datafile you just created.

Drop database?
Create Database??

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #264804 is a reply to message #264800]

Tue, 04 September 2007 11:07

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Oh yes, if you just created the database, you can drop it and recreate it.
Hmm, can we call this a recovery? Cool

Cool

Regards
Michel

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #264806 is a reply to message #264804]

Tue, 04 September 2007 11:09

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

I think it called "forcefully recovery on New Created Database" Smile

Smile

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #264807 is a reply to message #264806]

Tue, 04 September 2007 11:12

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

or may be "Recovery with No Loss" Laughing

Laughing

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #264877 is a reply to message #263561]

Tue, 04 September 2007 21:56

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Nice Jokes are going on....... Laughing

Laughing

Laughing

Laughing

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #265166 is a reply to message #263561]

Wed, 05 September 2007 10:18

d.c.b.a
Messages: 44
Registered: March 2005
Location: China

Member

Hi all:

What I mean is that the second data file in system tablespace, not the whole database.

You can test it.

Good luck.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #265168 is a reply to message #265166]

Wed, 05 September 2007 10:22

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

WellDone!!!

Keep it up man ,one you will be famous like TOM KYTE Wink

Wink

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #265246 is a reply to message #263561]

Wed, 05 September 2007 22:12

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Hi all,

> To everyone you want to make believe this one.!!

What I mean is that the second data file in system tablespace, not the whole database.

> How possible? I really wonder if you can do so.

You can be over more sure by excuting this query.

select file_id from dba_data_files where tablespace_name='SYSTEM';

You can test it.

> Wow!! How we can test if you can't give a demo?

Good luck.
> Good luck to you also.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #265247 is a reply to message #263561]

Wed, 05 September 2007 22:14

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

From previous threads I believe that you want to mean system data file as second data file.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #265394 is a reply to message #265247]

Thu, 06 September 2007 04:28

d.c.b.a
Messages: 44
Registered: March 2005
Location: China

Member

Arju wrote on Wed, 05 September 2007 23:14

From previous threads I believe that you want to mean system data file as second data file.

Correct, English is not my mother language, so communication problem.

Thanks.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #265396 is a reply to message #265394]

Thu, 06 September 2007 04:37

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

d.c.b.a wrote on Thu, 06 September 2007 15:28

Arju wrote on Wed, 05 September 2007 23:14

From previous threads I believe that you want to mean system data file as second data file.

Correct, English is not my mother language, so communication problem.

Thanks.

But dear it cannot be. System data file is the first data file that is created at the time of database creation.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #266940 is a reply to message #265396]

Wed, 12 September 2007 01:11

d.c.b.a
Messages: 44
Registered: March 2005
Location: China

Member

But we can add more data files to system tablespace, only the first one cannot be recreated.

This is what I want to say.

Good tools make work easy and improve life quality.
http://www.dbatools.net

[Updated on: Wed, 12 September 2007 01:12]

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #266942 is a reply to message #263561]

Wed, 12 September 2007 01:14

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Quote:

we can add more data files to system tablespace

Yes, if you can take the risk.

Report message to a moderator

Re: ORA-01180: can not create SYSTEM datafile [message #267263 is a reply to message #266942]

Wed, 12 September 2007 21:18

d.c.b.a
Messages: 44
Registered: March 2005
Location: China

Member

Arju wrote on Wed, 12 September 2007 02:14

Quote:

we can add more data files to system tablespace

Yes, if you can take the risk.

Add a second file to a tablespace is a high risk job?

Report message to a moderator

Previous Topic:	help me with restore problem..
Next Topic:	Need Advice on Backup strategy

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 20 12:42:35 CDT 2024