Friday, December 14, 2012

Oracle datafile with junk character

I faced some issues where the datafile has a junk character.(Maybe some copy pasted the datafile creation command with spaces of special characters).This causes the hot backup on database to fail. This is how i fixed the issue.

Check the datafile,


SQL> select FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='REPORTS';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                STATUS    ONLINE_
------------------------------ --------- -------
/sid/data/reports.dbf
REPORTS                 AVAILABLE ONLINE


$ ls -lb /sid/data/reports.dbf*
-rw-r-----  1 orora dbora 524320768 Apr  2 00:39 /sid/data/reports.dbf\

Now to fix  the issue
---------------------------
alter database datafile '/sid/data/reports.dbf ' offline; -->Note the space after dbf



mv '/sid/data/reports.dbf  ' /sid/data/reports.dbf   -->Note the space

 alter database  rename file '/sid/data/reports.dbf  ' to '/sid/data/reports.dbf ';

 recover datafile '/sid/data/reports.dbf ';

 alter database datafile '/sid/data/reports.dbf' online;


SQL> select TABLESPACE_NAME,STATUS,ONLINE_STATUS,FILE_NAME from dba_data_files where FILE_NAME = /sid/data/reports.dbf';

TABLESPACE_NAME                STATUS                         ONLINE_STATUS                  FILE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
OBITHD_REPORTS                 AVAILABLE                      ONLINE                         /sid/data/reports.dbf
                                                                                     
[AMD64] orora@host > ls -ltr reports.dbf
-rw-r-----  1 orora dbora 524320768 Mar 18 21:38 reports.dbf

This fixed the issue!




No comments:

Post a Comment