DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Tuesday, March 29, 2016

RESTORE DATAFILE WITHOUT BACKUP - ARCHIVELOG MODE ON


Datafile restoration without backup is only possible when archivelog 
is enabled during the time of datafile dropped. By using archivelog and redo logs
 we can restore datafile.

SQL> select log_mode from v$database;
                        LOG_MODE
                                ------------
                                ARCHIVELOG
  • I have removed the emp.dbf datafile accidently

Oracle@test TEST$  rm emp.dbf
  • When I try to insert a new data in employee table present in emp datafile it shows below error

SQL> insert into emp select * from emp;
insert into emp select * from emp
                            
ERROR at line 1:
ORA-01116: error in opening database file 8
ORA-01110: data file 6: '/d02/app/oracle/oradata/testdb/emp.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
  • To recover the datafile we need to take it offline :

SQL> alter database datafile 6 offline;
  • Create a new datafile with same old dropped datafile name/Path

SQL> alter database create datafile '/d02/app/oracle/oradata/testdb/emp.dbf ';
  • Now recover the datafile

SQL> recover datafile 8;
  • Bring the datafile online :

SQL> alter database datafile 6 online;
  • Now insert the values and check

SQL> insert into emp select * from emp;
100 rows created.

No comments: