What Does oracle do when data files are offline ?, Offlineoracle

What Does oracle do when data files are offline ?, Offlineoracle

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12;
Statement processed.
SQL> alter database datafile '/oracle/oradata/lixora/LIXORA/datafile/o1_mf_zjhcsoft_b24623wy _. dbf' offline;

Database altered.

SQL> oradebug event 10046 trace name context off;
Statement processed.
SQL> oradebug tracefile_name;
/Oracle/admin/lixora/udump/lixora_ora_29515.trc

* ** 10:50:56. 261
* ** Service name :( SYS $ USERS) 10:50:56. 261
* ** Session id: (159.3) 10:50:56. 261
WAIT #0: nam = 'SQL * Net message to client' ela = 1 driver id = 1650815232 # bytes = 1 p3 = 0 obj # = 0 tim = 1378950640880510
WAIT #0: nam = 'SQL * Net message from client' ela = 9226316 driver id = 1650815232 # bytes = 1 p3 = 0 obj # = 0 tim = 1378950650107126
XCTEND rlbk = 0, rd_only = 1
==================================
Parsing in cursor #9 len = 101 dep = 0 uid = 0 oct = 35 lid = 0 tim = 1378950650107611 hv = 707773262 ad = '7e6cd4e8'
Alter database datafile '/oracle/oradata/lixora/LIXORA/datafile/o1_mf_zjhcsoft_b24623wy _. dbf' offline
END OF STMT
PARSE #9: c = 0, e = 394, p = 0, cr = 0, cu = 0, mis = 1, r = 0, dep = 0, og = 1, tim = 1378950650107606
BINDS #9:
WAIT #9: nam = 'control file sequential read 'ela = 24 file # = 0 block # = 1 blocks = 1 obj # = 0 tim = 1378950650107957
WAIT #9: nam = 'control file sequential read 'ela = 21 file # = 1 block # = 1 blocks = 1 obj # = 0 tim = 1378950650108006
WAIT #9: nam = 'control file sequential read 'ela = 9 file # = 2 block # = 1 blocks = 1 obj # = 0 tim = 1378950650108035
WAIT #9: nam = 'control file sequential read 'ela = 6 file # = 0 block # = 16 blocks = 1 obj # = 0 tim = 1378950650108060
WAIT #9: nam = 'control file sequential read 'ela = 7 file # = 0 block # = 18 blocks = 1 obj # = 0 tim = 1378950650108083
WAIT #9: nam = 'control file sequential read 'ela = 10 file # = 0 block # = 30 blocks = 1 obj # = 0 tim = 1378950650108114
WAIT #9: nam = 'control file sequential read 'ela = 8 file # = 0 block # = 1 blocks = 1 obj # = 0 tim = 1378950650108250
WAIT #9: nam = 'control file sequential read 'ela = 5 file # = 1 block # = 1 blocks = 1 obj # = 0 tim = 1378950650108278
WAIT #9: nam = 'control file sequential read 'ela = 4 file # = 2 block # = 1 blocks = 1 obj # = 0 tim = 1378950650108299
WAIT #9: nam = 'control file sequential read 'ela = 5 file # = 0 block # = 16 blocks = 1 obj # = 0 tim = 1378950650108321
WAIT #9: nam = 'control file sequential read 'ela = 6 file # = 0 block # = 18 blocks = 1 obj # = 0 tim = 1378950650108342
WAIT #9: nam = 'control file sequential read 'ela = 20 file # = 0 block # = 23 blocks = 1 obj # = 0 tim = 1378950650108385
WAIT #9: nam = 'rdbms ipc reply' ela = 32296 from_process = 5 timeout = 910 p3 = 0 obj # = 0 tim = 1378950650141300
WAIT #9: nam = 'rdbms ipc reply' ela = 9556 from_process = 5 timeout = 910 p3 = 0 obj # = 0 tim = 1378950650150904
XCTEND rlbk = 0, rd_only = 1
EXEC #9: c = 1000, e = 43469, p = 0, cr = 0, cu = 2, mis = 0, r = 0, dep = 0, og = 1, tim = 1378950650151144
WAIT #9: nam = 'Log file sync' ela = 14463 buffer # = 1811 p2 = 0 p3 = 0 obj # = 0 tim = 1378950650165680
WAIT #9: nam = 'SQL * Net message to client' ela = 7 driver id = 1650815232 # bytes = 1 p3 = 0 obj # = 0 tim = 1378950650165756
* ** 10:51:13. 382
WAIT #9: nam = 'SQL * Net message from client' ela = 7434358 driver id = 1650815232 # bytes = 1 p3 = 0 obj # = 0 tim = 1378950657600174

 

 

Conclusion:

For a datafile, whether offline or offline drop is executed, only the control file is rewritten and file $ and ts $ are not updated, this is the essential reason why offline/offline drop can be executed on a datafile in the mount state;

In the datafile on line process, oracle checks the scn value of the data file header. If the scn value is smaller than other data files, the data file needs to be restored, that is to say, as long as bbed is used to change the offline data file's scn to be the same as other data files, the file can be online again without the need for log files to be restored.

The tablespace is not offline and archive is not enabled before the oracle database manually deletes data files.

Startup mount; then alter database datafile n offline drop;
Alter database open;

Oracle Database problems: if the database system accidentally loses data files that are not backed up, how can we restore the database to a normal state?

The data is definitely lost. You can choose to drop the lost data file offline and try to start the database.