Solved

ORACLE DATABASE

Posted on 2001-06-05
21
464 Views
Last Modified: 2012-06-21
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
Comment
Question by:vram
  • 6
  • 5
  • 2
  • +7
21 Comments
 
LVL 4

Expert Comment

by:srikant033100
ID: 6155547
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
 
LVL 1

Expert Comment

by:misho2000
ID: 6156350
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
 

Expert Comment

by:Maruthiv
ID: 6156766
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6157165
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
 
LVL 2

Expert Comment

by:stmontgo
ID: 6157472
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
 

Expert Comment

by:mhoyman
ID: 6157781
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
 

Author Comment

by:vram
ID: 6158996
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
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6159712
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
 
LVL 2

Expert Comment

by:stmontgo
ID: 6159904
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
 
LVL 2

Expert Comment

by:stmontgo
ID: 6159918
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:vram
ID: 6162341
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
 

Expert Comment

by:mhoyman
ID: 6162434
I still think your toast.  But hopefully there is a way you can hack your way out of this.

0
 
LVL 2

Expert Comment

by:stmontgo
ID: 6162497
ALTER DATABASE DATAFILE 'home3/ORACLEDATA/TOOLS/tools01.dbf'
OFFLINE DROP

Then try and drop the tablespace
0
 
LVL 2

Expert Comment

by:stmontgo
ID: 6162502
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
 

Expert Comment

by:alurirk
ID: 6162582
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
 

Expert Comment

by:mhoyman
ID: 6163818
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
 

Expert Comment

by:mhoyman
ID: 6163828
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
 
LVL 2

Expert Comment

by:stmontgo
ID: 6163866
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
 

Expert Comment

by:mhoyman
ID: 6164107
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
 
LVL 1

Expert Comment

by:Moondancer
ID: 7033747
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
 
LVL 6

Accepted Solution

by:
Mindphaser earned 0 total points
ID: 7043305
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now