Ora-00604 Error Occurred At Recursive Sql Level 1 Ora-00376
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle ora-00376 file cannot be read at this time Scripts Ion Excel-DB Don Burleson Blog
Ora-00376 Ora-01110
ORA-00376: file cannot be read at this time Oracle Database Tips
Ora-00376 Ora-01110 Data File
by Burleson Consulting May 15, 2016 Question: I am getting an ORA-00376 error when trying to drop my UNDO tablespace: ALTER DATABASE DATAFILE 'C:\APP\ORADATA\UNDOTBS03.DBF' OFFLINE DROP; ORA-00604: error occurred
Ora-00376 File 6 Cannot Be Read At This Time
at recursive SQL level 1 ORA-00376: file 15 cannot be read at this time ORA-01110: data file 15: 'C:\APP\ORADATA\UNDOTBS03.DBF' How do I fix the ORA-00376 error? Answer: First, why would you try to drop your UNDO tablespace? The oerr utility shows this for the ORA-00376 error: ORA-00376: File %s cannot be read at this time Cause: ora 00376 file 8 cannot be read at this time attempting to read from a file that is not readable. Most likely // the file is offline. Action: Check the state of the file. Bring it online . First, see the MOSC note: "ORA-00376 and ORA-01110 Database File Unavailable" [ID 160705.1] and "Resolving ORA-00376 Error Encountered in database" [MOSC ID 1339985.1] Next, check your alert log for additional messages. You need to start by getting the file#. select * from DBA_DATA_FILES where TABLESPACE_NAME='XXX'; You will want to check the status of the data file: select status, file# from v$datafile where file# = nn; select status, file# from v$datafile_header where file# = nn; Next, check to see if the file wants recovery: select * from v$recover_file; Check rollback segments (UNDO); select segment_name, tablespace_name, status from dba_rollback_segs; Check to see if your UNDO tablespace is offline. Start by trying to bring it online: alter database datafile 'c:\u01\app\xxx' online; Fir non-UNDO issues you need to recover the datafile and then bring the tablespace online. recover datafile 'c:\u01\app\oracle\xxx'; alter tablespace 'XXX' online; In your cas
ORA-06512: at line 19 Errors in file /export/home/oracle/admin/df4/dwnon/bdump/dwnon_smon_26175.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 238 cannot be read at this time ORA-01110: data file 238: '/u2/df4/oradata/dwnon/undotbs2_dwnon_01.dbf' ora-00376 file 2 cannot be read at this time undo tablespace ************************************************************ Dbv: The Database Verify utility (dbv) provides a mechanism to ora-00376 file 5 cannot be read at this time validate the structure of Oracle data files at the operating system level. It should be used on ora-00376 ora-01110 undo tablespace a regular basis to inspect data files for signs of corruption. Although it can be used against open data files, the primary purpose of dbv is to verify http://www.dba-oracle.com/t_ora_00376_file_cannot_be_read_at_this_time.html the integrity of cold datafiles that would be used for a backup. If used against online datafiles, intermittent errors can occur and the utility should be executed again against the same file to verify accuracy. The utility can only be used against datafiles however, not control files or archived redo logs. bash-2.05$ dbv file=/u2/df4/oradata/dwnon/undotbs2_dwnon_01.dbf blocksize=16384 DBVERIFY: http://myoracleproduct.blogspot.com/2013/11/fix-undo-block-corruption.html Release 10.2.0.2.0 - Production on Tue Feb 7 12:56:11 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. DBVERIFY - Verification starting: FILE = /u2/df4/oradata/dwnon/undotbs2_dwnon_01.dbf DBV-00200: Block, dba 998244473, already marked corrupted DBVERIFY - Verification complete Total Pages Examined : 125440 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 121776 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 3664 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Highest block SCN : 2468459942 (17.2468459942) bash-2.05$ Mount Stage: Step 1: SQL> select FILE#, NAME, STATUS, ERROR, RECOVER from v$datafile_header where status <> 'ONLINE'; FILE# NAME STATUS ERROR REC -------- ---------------------------------------- ----------- ----- 238 /u2/df4/oradata/dwnon/undotbs2_dwnon_01.dbf OFFLINE NO SQL> alter database recover datafile '/u2/df4/oradata/dwnon/undotbs2_dwnon_01.dbf' Media Recovery Complete. SQL> alter database datafile '/u2/df4/oradata/dwnon/sysaux_dwnon_01.dbf' online; Database altered. SQL> alter database open; Note: Database is in open mo
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have https://community.oracle.com/community/developer/search.jspa?q=ORA-00376 JavaScript enabled. This tool uses JavaScript https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:400219009850 and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page.Search this communityOracle CommunityBridged communitiesOracle BlogsOracle University TrainingOracle cannot be VideosOTN DocumentationOTN Search ResultsSearch forSearch forContentSearch forPeopleSearch forPlacesLast modifiedLast modifiedAll timeLast modified1 dayLast modified7 daysLast modified30 daysLast modified90 daysLast modified1 yearSort byRelevanceRelevanceLast modifiedLast modifiedRestrict results byRestrict results by: PeopleRestrict results by: PlacesSubspace depthSubspace cannot be read depthNo subspacesSubspace depthChild subspacesSubspace depthAll subspacesMatchingMatch names onlyDeactivated UsersShow/HideShowShowAll contentShowBlog postsShowDocumentsShowDiscussionsShowPollsShowIdeasShowStatus updatesShowMessagesShowExternal activityShowShowAll placesShowSpacesShowProjectsShowGroupsContent marked asShowAll ContentShowFinalShowReservedShowCommunity RecommendedShowOutdatedShowAction ItemShowDecisionShowHelpfulShowResolvedShowCommunity Feedback RequestedSocial Search On/OffSearchSearch TipsView more results from Oracle BlogsView more results from Oracle University TrainingView more results from Oracle VideosView more results from OTN DocumentationView more results from OTN Search ResultsAbout Oracle Technology Network (OTN)My Oracle Support Community (MOSC)MOS Support PortalAboutModern Marketing BlogRSS FeedPowered byOracle Technology NetworkOracle Communities DirectoryFAQAbout OracleOracle and SunRSS FeedsSubscribeCareersContact UsSite MapsLegal NoticesTerms of UseYour Privacy Rights© 2007-2016 Jive Software | Powered by Home | Top of pageJive Software Version: 8.0.3.1 , revision: 20160414082626.1619a91.release_8.0.3.x
- 2:02 pm UTC Category: Database � Version: 8.0.4 Whilst you are here, check out some content from the AskTom team: Feedback needed for the Oracle Dev Gym dashboard Latest Followup You Asked Hi Tom, by mistake, we had delete one datafile and now the DB could not be open, but can be mount. Unfortunately, we are running our DB in NOARCHIVE mode and we don't hav a backup too. b'coz we are fools (I just came to know that.) plz. help me, how to recover the DB in this situation. we hav created the datafile again in the same path, but its asking for particular thread no. , that we don't hav. plz. help me.. regards. raman. and we said... Here are the steps you can use to get the database going again without that tablespace (if you have no backups, its pretty much gone -- if you want to try and recover it, please Call support before doing anything else. Everything you do will preclude other paths of actions. It would be best to have a converstation with a person to plan the attack.... So, let's say you are willing to get rid of the tablespace that the datafile you removed was part of. Here is an example that shows me creating a tablespace, rm'ing the file, and then recovering: $ svrmgrl Oracle Server Manager Release 3.0.6.0.0 - Production (c) Copyright 1999, Oracle Corporation. All Rights Reserved. Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production With the Partitioning and Objects options PL/SQL Release 8.0.6.0.0 - Production SVRMGR> connect internal Connected. SVRMGR> create tablespace test datafile '/tmp/test.dbf' size 1m; Statement processed. SVRMGR> !rm /tmp/test.dbf SVRMGR> !ls /tmp/test.dbf /tmp/test.dbf not found REM the data file is gone now.... SVRMGR> shutdown immediate ORA-01116: error in opening database file 8 ORA-01110: data file 8: '/tmp/test.dbf' ORA-27041: unable to open file SVR4 Error: 2: No such file or directory Additional information: 3 SVRMGR> shutdown abort ORACLE instance shut down. SVRMGR> startup ORACLE instance started. Total System Global Area 11999664 bytes Fixed Size 51632 bytes Variable Size 7364608 bytes Database Buffers 4505600 bytes Redo Buffers 77824 bytes Database mounted. ORA-01157: cannot identify data file 8 - file not found ORA-01110: d