Recover From Redo Log File (After Lost Data File)

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     2

Current log sequence              4

 

SQL> select name from v$tablespace;

 NAME

------------------------------

SYSTEM

UNDOTBS1

SYSAUX

USERS

TEMP

 

SQL> select file_name from dba_data_files;

 FILE_NAME

-----------------------------------------------------------

 C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\SYSAUX01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\UNDOTBS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\SYSTEM01.DBF

 

SQL> create tablespace test

           datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF'

           SIZE 1M;

 Tablespace created.

 

SQL> create table test(id number) tablespace test;

 Table created.

 

SQL> insert into test values(2);

 1 row created.

 

SQL> insert into test values(5);

 1 row created.

 

SQL> commit;

Commit complete.

 

SQL> select * from test;

         ID

----------

         2

         5

 

SQL> select tablespace_name,file_name from dba_data_files;

 TABLESPACE_NAME           FILE_NAME

---------------------------------------------------------------------------------------

USERS            C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\USERS01.DBF

SYSAUX        C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\SYSAUX01.DBF

UNDOTBS1   C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\UNDOTBS01.DBF

SYSTEM        C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\SYSTEM01.DBF

TEST              C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

Delete C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF

 

SQL> startup;

ORACLE instance started.

 Total System Global Area  167772160 bytes

Fixed Size                  1247876 bytes

Variable Size              83887484 bytes

Database Buffers           75497472 bytes

Redo Buffers                7139328 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF'

 

 SQL> alter database create datafile  

          'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF';

 Database altered.

 

SQL> recover datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA20\TEST01.DBF';

Media recovery complete.

 

SQL> alter database open;

 Database altered.

 

SQL> select * from test;

         ID

----------

         2

         5

Comments