Recovery of datafie without backup.

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.