• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle Database – How to recover from a lost datafile with no backup

by admin

Pre-requisites

You have inadvertantly lost a datafile at the OS level and there are no current backups. You are in archivelog mode. You have ALL Archivelogs available since the datafile was created initially (creation date).

ORA-1110, lost datafile, file not found.

The Solution

Since there are no backups, the database cannot be opened without this file unless dropped and the tablespace dropped. If this is an important file and tablespace, this is not a valid option.

These files have to be recreated and recovered. Recreating the file and recovering it rewrites it to the OS and brings it up to date. Follow the steps given below to recover the datafile:

1. Connect to the database using sqlplus and shutdown the database.

SQLPLUS> shutdown immediate    #(If this hangs, issue shutdown abort)

2. Mount the database:

SQL> startup mount

Check for the datafile which needs recovery:

SQL> select * from v$recover_file;

Example output:

FILE#      ONLINE  ERROR              CHANGE#    TIME                
---------- ------- ------------------ ---------- --------------------   
11 OFFLINE FILE NOT FOUND              0 01/01/88 00:00:00   

(Noting the file number that was reported in the error)

3. Note the file number that was reported in the output above and run the below query:

SQL> select * from v$datafile where FILE#=11;

Example output:

FILE#      STATUS  ENABLED    CHECKPOINT BYTES      CREATE_BYT NAME             
---------- ------- ---------- ---------- ---------- ---------- --------
11 RECOVER READ WRITE 4.9392E+12          0      10240 /tmp/sample.dbf

Note the status is RECOVER and the CREATE_BYTE size as well as NAME.

6. Recreate the datafile.

SQL> alter database create datafile '/tmp/sample.dbf'
as '/tmp/sample.dbf' size 10240 reuse

Note that the file “created” and the file created “as” are the same file. The “size” needs to be the same size as it was when it was created.

7. Check to see that it was successful.

> select * from v$datafile where FILE#=11;

8. Bring the file online.

SQL> alter database datafile '/tmp/sample.dbf' online;

9. Recover the datafile.

SQL> Recover database;
Note: During recovery, all archived redo logs written to since the original datafile was created must be applied to the new, empty version of the lost datafile.

10) Now open the database:

SQL> alter database open;

Filed Under: oracle

Some more articles you might also be interested in …

  1. Beginners Guide to Oracle Temporary Tablespaces
  2. What roles can be set as default for a user in Oracle Database
  3. Stored Procedures and Functions in PL/SQL
  4. How to monitor Undo Tablespace Usage and the Free Space in Oracle Database
  5. Using Rule Sets in Oracle Database Vault
  6. Understanding Oracle Database Automatic SGA Memory Tuning
  7. How to Clone a Pluggable Database from an RMAN Container Database Backup
  8. Understanding Dynamic Oracle Net Server Tracing
  9. How to move ASM spfile to a different disk group
  10. Oracle 11gR2 (11.2.0.1) Unable To Create ASM Spfile Standalone

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright