Recover Datafile

Bagaimana cara merecover datafile yang corrupt atau terhapus secara tidak sengaja ?

Disini saya simulasikan datafile milik tablespace COBA di delete, berikut ini step-stepnya :

# rm /export/home/oracle/tempfile/coba02.dbf
#su - oracle
# login ke database sebagai user sys
$ sqlplus "/as yssdba"
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
EXAMPLE
COBA    ========> tablespace yang datafilenya di delete
7 rows selected.

# offline tablespace COBA  =====> muncul error dibawah
SQL> alter tablespace coba offline;
alter tablespace coba offline
*
ERROR at line 1:
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/export/home/oracle/tempfile/coba02.dbf'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3
#shutdown database normal  ======> masih tetap error
SQL> shutdown immediate;
ORA-01116: error in opening database file 11
ORA-01110: data file 11: '/export/home/oracle/tempfile/coba02.dbf'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3
# Shutdown Abort
SQL> shutdown abort;
ORACLE instance shut down.
# Setelah database shutdown ,database harus di startup on exclusive mode
SQL> startup exclusive mount;
ORACLE instance started.
Total System Global Area  658505728 bytes
Fixed Size                  1281936 bytes
Variable Size             176160880 bytes
Database Buffers          478150656 bytes
Redo Buffers                2912256 bytes
Database mounted.

#Perintah dibawah ini membuat datafile baru untuk tablespace COBA
SQL> alter database create datafile '/export/home/oracle/tempfile/coba02.dbf' as '/export/home/oracle/tempfile/coba02.dbf';
Database altered.
#Setelah perintah berhasil, shutdown database
SQL> shutdown ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
#Startup mount database untuk proses recovery
SQL> startup mount;
ORACLE instance started.
Total System Global Area  658505728 bytes
Fixed Size                  1281936 bytes
Variable Size             176160880 bytes
Database Buffers          478150656 bytes
Redo Buffers                2912256 bytes
Database mounted.
#Cek datafile mana saja yang memerlukan recovery
SQL> select file# from v$recover_file;                      (cek nomor file yang perlu di recover )
SQL> select name from v$datafile where file#=nomor_file ;   (hasil dari cek nomor file )
#Datafile yang baru dibuat harus di recover karena dianggap sebagai file yang rusak dengan command dibawah ini
SQL> recover datafile '/export/home/oracle/tempfile/coba02.dbf';
Atau
SQL> recover datafile nomor_file;
Media recovery complete.
SQL> alter database open;
Database altered.
#Recovery Sukses

Referensi : Oracle Documentation