Oracle Tips and Tricks

Tipps

Recover datafiles

Problem:

ORA-00376: file 18 cannot be read at this time
ORA-01110: data file 18: '/data/oracle/oradata/datafile04.dbf'

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
        18 OFFLINE OFFLINE
                                                                  5.8016E+12
22-JUL-15

SQL> select ONLINE_STATUS from dba_data_files where file_id = 18;

ONLINE_
-------
RECOVER

Recover datafile:

SQL> recover datafile 18;
ORA-00279: change 5801623243148 generated at 07/22/2015 21:26:51 needed for thread 1
ORA-00289: suggestion :
/data/oracle/arclog/ORACLESID_1946_1_882824275.ARC
ORA-00280: change 5801623243148 for thread 1 is in sequence #1946
ORA-00278: log file
'/data/oracle/arclog/ORACLESID_1945_1_882824275.ARC' no longer needed
for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.

Set file online:

SQL> alter database datafile 18 online

Anything else?

SQL> select * from v$recover_file;

no rows selected

Show non default settings

SQL> select name || ' = ' || value from v$parameter where isdefault = 'FALSE';