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!
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