OraFAQ Forum: Backup & Recovery » How can we open the database when we do not have pfile, spfile, control file and backup.

Home » RDBMS Server » Backup & Recovery » How can we open the database when we do not have pfile, spfile, control file and backup.

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

How can we open the database when we do not have pfile, spfile, control file and backup. [message #448324]

Mon, 22 March 2010 13:15

ashokbabugaddipati
Messages: 1
Registered: March 2010
Location: Hyderabad

Junior Member

How can we open the database when we do not have pfile, spfile, control file and backup.

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448325 is a reply to message #448324]

Mon, 22 March 2010 13:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How can we open the database when we do not have pfile, spfile, control file and backup.
What exactly do you have?
You can create pfile from any recent alert_SID.log file which shows DB startup.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

[Updated on: Mon, 22 March 2010 13:22]

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448357 is a reply to message #448324]

Tue, 23 March 2010 00:17

Yasir Hashmi
Messages: 304
Registered: April 2006

Senior Member

If you don't have any of these,you don't have any database either.

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448358 is a reply to message #448357]

Tue, 23 March 2010 00:27

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

Senior Member
Account Moderator

Maybe has he data files?

Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Tue, 23 March 2010 00:44]

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448360 is a reply to message #448324]

Tue, 23 March 2010 00:32

Yasir Hashmi
Messages: 304
Registered: April 2006

Senior Member

Whats the use of a car with no engine?

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448362 is a reply to message #448360]

Tue, 23 March 2010 00:36

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

Senior Member
Account Moderator

With only the data files you can rebuilt a working database.

Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

[Updated on: Tue, 23 March 2010 00:40]

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448363 is a reply to message #448324]

Tue, 23 March 2010 00:37

hkchital
Messages: 128
Registered: September 2008
Location: Singapore

Senior Member

If you are using an SPFILE, it should be part of your RMAN Backups of the Database.

You can recreate your PFILE from the initdummy.ora and modifying it for non-default parameters listed in the alert.log

You can restore the controlfile from RMAN Backups.

You can recreate the controlfile from backup traces OR from a listing of datafiles.

If you haven't been doing RMAN Backups but only OS backups, those should have included the files -- at least an OS backup of the ORACLE_HOME filesystem weekly or monthly would do so !

Hemant K Chitale

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448364 is a reply to message #448363]

Tue, 23 March 2010 00:43

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

Senior Member
Account Moderator

Quote:

If you are using an SPFILE, it should be part of your RMAN Backups of the Database.

OP said there is no backup.

Quote:

You can recreate your PFILE from the initdummy.ora and modifying it for non-default parameters listed in the alert.log

BlackSwan asked if he has it, we are waiting foe the answer.

Quote:

You can recreate the controlfile from backup traces OR from a listing of datafiles.

I asked if he has them, waiting for his answer.

If only the data files are, we can indeed recreate the whole database with these steps.

Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448365 is a reply to message #448324]

Tue, 23 March 2010 00:53

Yasir Hashmi
Messages: 304
Registered: April 2006

Senior Member

ok, i though he was asking about how to open the database without these files.
Yes ,if he has none of them then at any time he can create pfile and create controlfile also.

Do we need to have a consistent datafiles for that?

If all the controlfiles were deleted and lets suppose spfile,pfile were also lost in the middle of a working database,the database would be in inconsistent state.

Wouldn't it complain about recovery needed?

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448381 is a reply to message #448365]

Tue, 23 March 2010 01:42

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

Senior Member
Account Moderator

Yes, it then also needs archived and/or online log files.

Regards
Michel
Tell me and I'll forget; show me and I may remember; involve me and I'll understand

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448382 is a reply to message #448324]

Tue, 23 March 2010 02:00

Yasir Hashmi
Messages: 304
Registered: April 2006

Senior Member

I did a test on my test box.

I deleted all the controlfiles. Next i shutdown abort the database.

Upon startup nomount,icreated the controlfile as :

 CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u/oracle/oradata/test/redo01.log'  SIZE 50M,
  GROUP 2 '/u/oracle/oradata/test/redo02.log'  SIZE 50M,
  GROUP 3 '/u/oracle/oradata/test/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u/oracle/oradata/test/system01.dbf',
  '/u/oracle/oradata/test/undotbs01.dbf',
  '/u/oracle/oradata/test/sysaux01.dbf',
  '/u/oracle/oradata/test/users01.dbf',
  '/u/oracle/oradata/test/example01.dbf',
  '/u/oracle/oradata/test/treasury.dbf'
CHARACTER SET WE8ISO8859P1
;

It didn't ask for any media recovery. It must have done instance recovery only.

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448388 is a reply to message #448382]

Tue, 23 March 2010 02:26

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

Senior Member
Account Moderator

Because you had no activity on your database and the last online redo log is there.

Correction to my previous answer: it never needs archived logs in this case as without control file there can't be any log switch.

Regards
Michel

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448393 is a reply to message #448324]

Tue, 23 March 2010 02:47

Yasir Hashmi
Messages: 304
Registered: April 2006

Senior Member

Thankyou Michel for your reply.

You said without controlfile ther can't be any logswitch. BUt after removing the controlfiles, i issued :

alter system switch logfile;

and it succeeded.
In the alert log,it has the following entries:

Tue Mar 23 13:13:13 2010
Thread 1 advanced to log sequence 4
  Current log# 1 seq# 4 mem# 0: /u/oracle/oradata/test/redo01.log

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448397 is a reply to message #448393]

Tue, 23 March 2010 03:05

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

Senior Member
Account Moderator

Very strange as the first step to switch log is a control file transaction to know the next redo log to use and clear its entry in the control file. How could it do it without the control file?

Regards
Michel

[Edit: fix typo]

[Updated on: Tue, 23 March 2010 13:03]

Report message to a moderator

Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448491 is a reply to message #448397]

Tue, 23 March 2010 12:59

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Your database can remain open (and log switch) after removing the controlfile, because you are running on Unix. All rm does is remove the directory entry: the file (and inode) is still there until all the file handles get closed. If you shutdown/startup, you'll be in trouble.

Report message to a moderator