Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 475
  • Last Modified:

ORACLE DATABASE

hI

My Oracle Database is down.One of my Redo Log got deleted
I don't have any backup.

I am not able to recover my file

How to start database or switch the redo logs
to make one in-active other active

0
vram
Asked:
vram
  • 6
  • 5
  • 2
  • +7
1 Solution
 
srikant033100Commented:
First thing is if ur redo log files are mutipliex then u will not have any problem. Just start the database. if ur trying to start the database then what is the problem or the error message it is try to give u.

Srikant Sarda
0
 
misho2000Commented:
What have you deleted - group redo logs or log member?
try
svrmgrl
connect internal
startup mount
select * from v$logfile;
gives you the names of all redo log files.
Check in your OS about existance of redo log files and
if add one new redolog group:
alter database add logfile ....
(not sure that database must be open)
 and drop deleted  one:
alter database drop logfile ....
0
 
MaruthivCommented:
Mount the database ( do not open )
drop the log file that was deleted
Add the file if you want to.
startup again fully.
that should solve the problem.


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Mark GeerlingsDatabase AdministratorCommented:
Running an Oracle database with only one copy of each online redo log is a serious mistake.  They should always be duplexed onto different disks (maybe you will do that in that future).

I think Oracle supports opening the database without the current online redo log in an emergency, but this will cause data loss and I don't know the procedure for doing that.  It would be best to do this with the help of Oracle tech support.  You have payed for Oracle support right?  If so, call them.  If not, you may have a serious problem.
0
 
stmontgoCommented:
Was the database shutdown cleanly ? (transactional, normal or immediate?) If so ALTER DATABASE OPEN RESETLOGS should do the trick. Check your alert log to be sure.

If not try placing

_ALLOW_RESETLOG_CORRUPTION = TRUE (in your init file)to try and pop open the database. If you are successful, you will have to rebuild the database with import/export as using this parameter could corrupt your data dictionary.

You may have to check the syntax of this parameter. You should really be doing this with Oracle Support as the parameter is undocumented and unsupported and can corrupt your data dictionary!
0
 
mhoymanCommented:
I think you are toast.  I would be suprised if any of these suggestions work.  If you loose redo you can not go to a backup.  Multipixing is the only way to run the database.  And running it in hot backup mode so you go back to the arch redo logs and open the database with resetlogs.

0
 
vramAuthor Commented:
One suggestion was reasonably helpful. we were able to start the db. But now the problem is , we are not able to drop some of the existing tablespace, for which .dbf does not exist, like tools01.dbf
      All the datafiles for which the dbf has been deleted, are in offline mode, but the tablespace is online.
   
      We need to drop or recover the files / tablespace. How should we continue
clarify
0
 
Mark GeerlingsDatabase AdministratorCommented:
So data files were deleted as well as on-line redo logs?  
Do you still have some tablespaces that contain data?  If so, try running Oracle export to preserve at least as much as possible in a dump file.  If that succeeds, you could drop the database, recreate it, then run import.  After that you could try a couple other things:

It may be possible to do a "drop tablespace [tablespace_name] including contents;"

It is possible to create a new control file that does not include the datafile(s) that was/were deleted.  That may not eliminate the problem of objects in the data dictionary that no longer exist though.

0
 
stmontgoCommented:
markgeer is correct,
if datafiles are missing then your TS is in bad shape. If you can export try to do so at the table level. If you
cannot export and you do not need the data in the TS and you cannot drop the tablespace, try the following
ALTER DATABASE DATAFILE OFFLINE DROP '<FULL PATH AND FILE NAME OF DATAFILE>' for the datafiles that were owned by the tablespace and then try and drop the TS.

OFFLINE DROP of a datafile is always a last resort, there is better than average chance that once you complete that operartion, data in the TS will be permanently lost
0
 
stmontgoCommented:
also, which tablespaces are affected? r u in archive log mode?, do you have a recent backup (export, cold backup)?

if it's just tools than this usually is not a big deal
as application data is not normally stored in that TS

steve
0
 
vramAuthor Commented:
Problem not solved yet,

On trying the following drop user command, getting error  (see below)

 drop user arun cascade;

drop user arun cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/home3/ORACLEDATA/TOOLS/tools01.dbf'
 
 Pls clarify what to do\
0
 
mhoymanCommented:
I still think your toast.  But hopefully there is a way you can hack your way out of this.

0
 
stmontgoCommented:
ALTER DATABASE DATAFILE 'home3/ORACLEDATA/TOOLS/tools01.dbf'
OFFLINE DROP

Then try and drop the tablespace
0
 
stmontgoCommented:
had a typo previously...here is what it should be

ALTER DATABASE DATAFILE '/home3/ORACLEDATA/TOOLS/tools01.dbf'
OFFLINE DROP

then drop the tablespace
0
 
alurirkCommented:
hi

If you are missing the online redo logs, you need to use the
RECOVER DATABASE UNTIL CANCEL
command. When it prompts for the last log that is missing, cancel the recovery.



0
 
mhoymanCommented:
You can only use the RECOVER DATA UNTIL CANCEL when archived redo log.  That also only works in the databas is in hot backup mode.
0
 
mhoymanCommented:
whoops sorry I didn't proofread my response. You can only use the RECOVER DATABASE UNTIL CANCEL when an archive redo log is lost.  And the database is run in hot backup mode.

are you still trying to recover data from the users tablespace?
0
 
stmontgoCommented:
you do not have to be in archive log mode to use RECOVER DATABASE UNTIL CANCEL. Since Oracle will OPEN RESETLOGS without INCOMPLETE MEDIA RECOVERY, you can trick oracle by issuing RECOVER DATABASE UNTIL CANCEL and then typing CANCEL when prompted for the first log, afterwards try ALTER DATABASE OPEN RESETLOGS. However, wtihout a clean shutdown this is unlikely to work.
0
 
mhoymanCommented:
Okay but for my reference what will that accomplish?  It has been a couple of years since I have done back and recovery but I do remember if you loose a redo log, your done.  I know there are little ways to trick oracle into trying to get the database back to a "consistent state" but it is really tough when you loose redo.  

And that is not our only problem, he lost the tools tablespace as well right?  Although that was is manageable as you can drop the tablespace.
0
 
MoondancerCommented:
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks to all,
Moondancer - EE Moderator

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
 
MindphaserCommented:
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 5
  • 2
  • +7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now