Recovery of datafie without backup.
-----------------------------------
Eg:-
-bash-3.2$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 20 10:43:00 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from V$database;
NAME
---------
SGDATA
SQL> select name from V$datafile;
NAME
---------------------------------------------
/optware/oracle/oradata/sgdata/system01.dbf
/optware/oracle/oradata/sgdata/sysaux01.dbf
/optware/oracle/oradata/sgdata/undotbs01.dbf
/optware/oracle/oradata/sgdata/users01.dbf
/optware/oracle/oradata/sgdata/manzoor01.dbf
SQL> create tablespace ahamed datafile '/optware/oracle/oradata/sgdata/ahamed01.dbf' size 50M;
Tablespace created.
SQL> create table emp_1 (empno number, name varchar2(30)) tablespace ahamed;
Table created.
SQL> begin
2 for i in 1..1000 loop
3 insert into emp_1 values (i, dbms_random.string('U',30));
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select count(*) from emp_1;
COUNT(*)
----------
1000
SQL> select name from V$datafile;
NAME
---------------------------------------------
/optware/oracle/oradata/sgdata/system01.dbf
/optware/oracle/oradata/sgdata/sysaux01.dbf
/optware/oracle/oradata/sgdata/undotbs01.dbf
/optware/oracle/oradata/sgdata/users01.dbf
/optware/oracle/oradata/sgdata/manzoor01.dbf
/optware/oracle/oradata/sgdata/ahamed01.dbf
6 rows selected.
SQL> ! rm -f /optware/oracle/oradata/sgdata/ahamed01.dbf
SQL> shut abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2226336 bytes
Variable Size 335546208 bytes
Database Buffers 1224736768 bytes
Redo Buffers 7499776 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/optware/oracle/oradata/sgdata/ahamed01.dbf'
SQL> select status from V$instance;
STATUS
------------
MOUNTED
-- We dont have the backup of the datafile but the database is in archive log mode and
all the archives and redo logs which are generated after the datafile creation is availble in that case
we can just create a datafile using below command, the size and to which tablespace is belong and
other stuff will be picked by oracle automatically using controlfile / data dictionary.
SQL> alter database create datafile '/optware/oracle/oradata/sgdata/ahamed01.dbf';
Database altered.
SQL> alter database recover datafile '/optware/oracle/oradata/sgdata/ahamed01.dbf';
Database altered.
SQL> alter database open;
Database altered.
--DB is up now.
Another way as below.
SQL> ! rm -f /optware/oracle/oradata/sgdata/ahamed01.dbf
SQL> shut abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2226336 bytes
Variable Size 335546208 bytes
Database Buffers 1224736768 bytes
Redo Buffers 7499776 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/optware/oracle/oradata/sgdata/ahamed01.dbf'
-- We can also use below command if the old path is having some issues and we required the file to be created on the new path.
syntax :- alter database cretae datafile '/old-path' as '/new-path';
alter database recover datafile '/new-path';
SQL> alter database create datafile '/optware/oracle/oradata/sgdata/ahamed01.dbf' as '/optware/oracle/oradata/sgdata/ahamed01_new.dbf';
Database altered.
SQL> alter database recover datafile '/optware/oracle/oradata/sgdata/ahamed01_new.dbf';
Database altered.
SQL> alter database open;
Database altered.
