Oracle Error In Opening Database File
Contents |
(13) Performance (2) SecureFiles and Large Objects (1) Security (1) Utilities (1) OCI/OCCI (1) Internals (1) Grid Control (19) TimesTen (14) Linux (9) Java (13) JDBC (8) JavaFX (4) Windows (5) VirtualBox (4) Grid
Ora-01116 Ora-01110 Ora-27041
Infrastructure (4) NetBeans (2) Berkeley DB (2) JDeveloper (2) Golden Gate (2) Oracle ora-27092: size of file exceeds file size limit of the process Fusion Middleware (1) Weblogic (1) Video (1) MySQL (1) Cloud Control (1) announcements (1) Recover from a missing data file by
Ora-01116 Error In Opening Database File 5
Eric Jenkinson on April 21, 2010 Categories: Backup and Recovery,Oracle Database Tagged: datafiles, recovery, rman This document will detail two recovery scenarios dealing with a missing datafile using a RMAN disk based backup. ora-01116 error in opening database file 7 If you need a document for configuring RMAN see the post Setting up RMAN for backup and recovery using Database Control Scenario: You receive an email from a user reporting the error below when attempting to create a table in their default tablespace. SQL> create table test as select * from user_objects; create table test as select * from user_objects * ERROR at line 1: ORA-01116: error in ora-27041: unable to open file opening database file 6 ORA-01110: data file 6: '/u02/app/oracle/oradata/orcl/test.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 SQL> You look on the file system and you see that the file test.dbf is indeed no longer present. [oracle@ora1 ~]$ ls /u02/app/oracle/oradata/orcl/ arch ctl redo system01.dbf undotbs01.dbf backup example01.dbf sysaux01.dbf temp01.dbf users01.dbf [oracle@ora1 ~]$ Action: Identify the tablespace in which the missing data file is a member. SQL> select tablespace_name from dba_data_files where file_id = 6; TABLESPACE_NAME ------------------------------ TEST SQL> In this scenario the database does not need to be shutdown in order to recover the missing data file, however, the tablespace TEST does need to be taken offline. SQL> alter tablespace test offline immediate; Tablespace altered. SQL> We now connect to the database through RMAN and restore the missing data file. [oracle@ora1 ~]$ rman Recovery Manager: Release 11.2.0.1.0 - Production on Tue Apr 20 11:59:53 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. RMAN> connect target / connected to target database: ORCL (DBID=1239150297) RMAN> restore datafile 6; Starting restore at 20-APR-10 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=44 device type=DISK allocated channel: OR
1: '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf' How to recover database when system datafile is lost using RMAN backup? or how to resolve ORA-01116: error in opening database
'ora-27041: Unable To Open File Linux-x86_64 Error: 2: No Such File Or Directory'
file 1 ORA-01110: data file 1: '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf'? Cause: ORA-01116: error in ora-01145 opening database file 1 means either system datafile is either missing or corrupted Action: we need to perform
Ora-01110: Data File
recovery as shown below DB Name: TESTDB [oracle@avalance_testserv dbf]$ ls redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf [oracle@avalance_testserv dbf]$ rm system01.dbf SQL> select * from sys.aud$; select * http://www.oracledistilled.com/oracle-database/recover-from-a-missing-data-file/ from sys.aud$ * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/u01/app/oracle/TESTDB/oradata/dbf/system01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Note: Since the normal shutdown is not possible in this case we shall use http://orcl11gdba.blogspot.com/2014/09/ora-01116-error-in-opening-database.html shut abort SQL> shut abort ORACLE instance shut down [oracle@avalance_testserv dbf]$ sqlplus / as sysdba SQL> startup mount [oracle@avalance_testserv dbf]$ rman target/ RMAN> restore datafile 1; Starting restore at 25-SEP-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=98 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/TESTDB/oradata/dbf/system01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_ttdtest_0lpjddnk_1_1.bck channel ORA_DISK_1: piece handle=/u01/app/oracle/TESTDB/flash_recovery_area/TESTDB/backupset/ORA_rman_full_backup_ttdtest_0lpjddnk_1_1.bck tag=FULL_OPEN_BACKUP_DISK channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 25-SEP-14 RMAN> recover datafile 1; Starting recover at 25-SEP-14 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_22_859142042.arc archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_23_859142042.arc archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/TESTDB/oradata/arch/TESTDB_1_24_859142042.arc archived log for thread 1 with sequence 25 is already on disk as fil
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the http://stackoverflow.com/questions/33274869/oracle-database-remove-tablespace-with-missing-datafile company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:400219009850 Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ORACLE Database | remove tablespace with missing datafile up vote 2 down vote favorite I mistakenly remove the datafiles before I error in drop the tablespace. But the tablespace occupy a large size space. I need to remove it, any method? It occur: DROP TABLESPACE abc; * ERROR at line 1: ORA-01116: error in opening database file 8 ORA-01110: data file 8: '/data/oradata/oracle/abc.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 database oracle share|improve this question edited Oct 22 '15 at 7:16 PravinS 2,3613920 asked Oct 22 '15 error in opening at 6:45 Jay J 162 Try, abut fail. –Jay J Oct 22 '15 at 8:01 add a comment| 2 Answers 2 active oldest votes up vote 0 down vote accepted You can follow the steps given here in this Oracle forum: Follow the below steps : - 1) Shutdown abort 2) sqlplus sys/xxx as sysdba 3) Alter database mount 4) alter database datafile '' offline drop; 5) Alter database open share|improve this answer answered Oct 22 '15 at 6:50 Rahul Tripathi 101k1595173 Is there any impact of other tablespace or database? –Jay J Oct 22 '15 at 8:00 1 Thank you..It works! –Jay J Oct 22 '15 at 8:10 add a comment| up vote 0 down vote try to recover the datafile , identefy the name of the tablespace select tablespace_name from dba_data_files where file_id = 8; change the status of the tablespace to offline , so you can run the RMAN(recovery manager) alter tablespace test offline immediate; after that you have to run the RMAN to recovery the file.. for more info how to do that check this read this more about RMAN Burleson share|improve this answer answered Oct 22 '15 at 6:55 Moudiz 2,70232256 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up u
- 2:02 pm UTC Category: Database � Version: 8.0.4 Whilst you are here, check out some content from the AskTom team: Why I love working on AskTom 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: data file 8: '/tmp/test.dbf' SVRMGR>