Home » RDBMS Server » Backup & Recovery » ORA-01180: can not create SYSTEM datafile
ORA-01180: can not create SYSTEM datafile [message #263561] Thu, 30 August 2007 06:32 Go to next message
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
Re: ORA-01180: can not create SYSTEM datafile [message #263562 is a reply to message #263561] Thu, 30 August 2007 06:38 Go to previous messageGo to next message
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.
Re: ORA-01180: can not create SYSTEM datafile [message #263563 is a reply to message #263561] Thu, 30 August 2007 06:41 Go to previous messageGo to next message
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.
Re: ORA-01180: can not create SYSTEM datafile [message #263564 is a reply to message #263563] Thu, 30 August 2007 06:45 Go to previous messageGo to next message
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
Re: ORA-01180: can not create SYSTEM datafile [message #263567 is a reply to message #263561] Thu, 30 August 2007 06:56 Go to previous messageGo to next message
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.

Re: ORA-01180: can not create SYSTEM datafile [message #263569 is a reply to message #263567] Thu, 30 August 2007 07:06 Go to previous messageGo to next message
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
Re: ORA-01180: can not create SYSTEM datafile [message #263570 is a reply to message #263561] Thu, 30 August 2007 07:10 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

No. You can't . You will loss everything. No way.
Re: ORA-01180: can not create SYSTEM datafile [message #263606 is a reply to message #263570] Thu, 30 August 2007 08:45 Go to previous messageGo to next message
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
Re: ORA-01180: can not create SYSTEM datafile [message #263637 is a reply to message #263606] Thu, 30 August 2007 10:04 Go to previous messageGo to next message
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.
Re: ORA-01180: can not create SYSTEM datafile [message #264151 is a reply to message #263561] Sat, 01 September 2007 03:23 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

NIce one.
Re: ORA-01180: can not create SYSTEM datafile [message #264761 is a reply to message #263561] Tue, 04 September 2007 08:51 Go to previous messageGo to next message
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.
Re: ORA-01180: can not create SYSTEM datafile [message #264764 is a reply to message #264761] Tue, 04 September 2007 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
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
Re: ORA-01180: can not create SYSTEM datafile [message #264800 is a reply to message #264764] Tue, 04 September 2007 10:56 Go to previous messageGo to next message
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??
Re: ORA-01180: can not create SYSTEM datafile [message #264804 is a reply to message #264800] Tue, 04 September 2007 11:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
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

Regards
Michel
Re: ORA-01180: can not create SYSTEM datafile [message #264806 is a reply to message #264804] Tue, 04 September 2007 11:09 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

I think it called "forcefully recovery on New Created Database" Smile
Re: ORA-01180: can not create SYSTEM datafile [message #264807 is a reply to message #264806] Tue, 04 September 2007 11:12 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
or may be "Recovery with No Loss" Laughing
Re: ORA-01180: can not create SYSTEM datafile [message #264877 is a reply to message #263561] Tue, 04 September 2007 21:56 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Nice Jokes are going on....... Laughing Laughing Laughing
Re: ORA-01180: can not create SYSTEM datafile [message #265166 is a reply to message #263561] Wed, 05 September 2007 10:18 Go to previous messageGo to next message
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.
Re: ORA-01180: can not create SYSTEM datafile [message #265168 is a reply to message #265166] Wed, 05 September 2007 10:22 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
WellDone!!!

Keep it up man ,one you will be famous like TOM KYTE Wink
Re: ORA-01180: can not create SYSTEM datafile [message #265246 is a reply to message #263561] Wed, 05 September 2007 22:12 Go to previous messageGo to next message
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.
Re: ORA-01180: can not create SYSTEM datafile [message #265247 is a reply to message #263561] Wed, 05 September 2007 22:14 Go to previous messageGo to next message
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.
Re: ORA-01180: can not create SYSTEM datafile [message #265394 is a reply to message #265247] Thu, 06 September 2007 04:28 Go to previous messageGo to next message
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.
Re: ORA-01180: can not create SYSTEM datafile [message #265396 is a reply to message #265394] Thu, 06 September 2007 04:37 Go to previous messageGo to next message
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.
Re: ORA-01180: can not create SYSTEM datafile [message #266940 is a reply to message #265396] Wed, 12 September 2007 01:11 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: ORA-01180: can not create SYSTEM datafile [message #267263 is a reply to message #266942] Wed, 12 September 2007 21:18 Go to previous message
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?
Previous Topic: help me with restore problem..
Next Topic: Need Advice on Backup strategy
Goto Forum:
  


Current Time: Thu Dec 08 06:05:08 CST 2016

Total time taken to generate the page: 0.12614 seconds