Home » RDBMS Server » Backup & Recovery » How can we open the database when we do not have pfile, spfile, control file and backup.
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 Go to next message
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.
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 Go to previous messageGo to next message
BlackSwan
Messages: 25042
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 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
If you don't have any of these,you don't have any database either.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
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 Go to previous messageGo to next message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
Whats the use of a car with no engine?
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
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
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 Go to previous messageGo to next message
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?
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
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
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
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 Go to previous messageGo to next message
John Watson
Messages: 6573
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.
Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448492 is a reply to message #448491] Tue, 23 March 2010 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you're right, thanks for this explaination.

Regards
Michel
Re: How can we open the database when we do not have pfile, spfile, control file and backup. [message #448559 is a reply to message #448491] Wed, 24 March 2010 01:35 Go to previous message
Yasir Hashmi
Messages: 304
Registered: April 2006
Senior Member
Yes,this is the reason for this surprising behaviour.
Previous Topic: Flashback database question
Next Topic: Backing up Oracle 10g using RMAN & TDP
Goto Forum:
  


Current Time: Wed Dec 07 04:37:50 CST 2016

Total time taken to generate the page: 0.11115 seconds