ORACLE資料檔名導致的奇怪問題

檢視datafile也看不出什麼蹊蹺。

可能是什麼其他的地方吧。

我用如下的sql導處資料檔案的狀態。

select f.tablespace_name||'|'||f.file_name||'|'||f.autoextensible||'|'||f.bytes/1024/1024||'M' ||'|'|| t.initial_extent||'|'||t.next_extent||'|'||t.extent_management||'|'||t.segment_space_management||'|'||t.bigfile from dba_tablespaces t,dba_data_files f where t.tablespace_name=f.tablespace_name;

結果如下：

TBS1|/u01/app/oracle/oradata/disk_1/tbs01.dbf|NO|5M|1048576|1048576|LOCAL|AUTO|N
O

SYSAUX|/u01/app/oracle/oradata/disk_4/sysaux01.dbf|NO|325M|65536||LOCAL|AUTO|NO
UNDOTBS|/u01/app/oracle/oradata/disk_5/undotbs01.dbf|YES|200M|65536||LOCAL|MANUAL|NO

SYSTEM|/u01/app/oracle/oradata/disk_4/system01.dbf|NO|325M|65536||LOCAL|MANUAL|NO

EXAMPLE|/u01/app/oracle/oradata/disk_5/example.dbf|YES|400M|1048576|1048576|LOCAL|AUTO|NO

INDX|/u01/app/oracle/oradata/disk_5/indx.dbf |NO|40M|65536||LOCAL|AUTO|NO

最後一行的 indx.dbf後面還有個空格，原來是這個檔案導致的！！！

我有個好習慣，之前執行的語句都保留了下來，一翻，驗證了我的想法。

create tablespace example datafile '/u01/app/oracle/oradata/disk_5/example.dbf' size 400M autoextend on maxsize 4g extent management local uniform. size 1M;

create tablespace indx datafile '/u01/app/oracle/oradata/disk_5/indx.dbf 'size 40M;

接下來該處理這個問題了。

        10--檔案號是10
/u01/app/oracle/oradata/disk_5/indx.dbf

SQL> alter database datafile 10 offline;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/disk_5/indx.dbf ' to '/u01/app/oracle/oradata/disk_5/indx.dbf'
  2  ;
alter database rename file '/u01/app/oracle/oradata/disk_5/indx.dbf ' to '/u01/app/oracle/oradata/disk_5/indx.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 10 - new file
'/u01/app/oracle/oradata/disk_5/indx.dbf' not found
ORA-01110: data file 10: '/u01/app/oracle/oradata/disk_5/indx.dbf '
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

貌似我寫的檔案空格不夠標準？？？

SQL> select '|'||file_name||'|' from dba_data_files where file_id=10;

'|'||FILE_NAME||'|'
--------------------------------------------------------------------------------
|/u01/app/oracle/oradata/disk_5/indx.dbf |

SQL> alter database rename file '/u01/app/oracle/oradata/disk_5/indx.dbf 'to '/u01/app/oracle/oradata/disk_5/indx.dbf1';
alter database rename file '/u01/app/oracle/oradata/disk_5/indx.dbf 'to '/u01/app/oracle/oradata/disk_5/indx.dbf1'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 10 - new file
'/u01/app/oracle/oradata/disk_5/indx.dbf1' not found
ORA-01110: data file 10: '/u01/app/oracle/oradata/disk_5/indx.dbf '
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

還是不行，暈了，這個問題還真是費勁啊。

這樣做，

[oracle@oel1 disk_5]$ ll
total 54856
-rw-r----- 1 oracle dba  5251072 Oct 30 18:01 indx.dbf
drwxr-x--- 4 oracle dba     4096 Oct 30 10:27 PODD
-rw-r----- 1 oracle dba 20979712 Oct 30 08:34 temp01.dbf
-rw-r----- 1 oracle dba 50339840 Oct 30 18:01 tools.dbf
[oracle@oel1 disk_5]$ mv indx* indx.dbf1

SQL> alter database datafile 10 offline;

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/disk_5/indx.dbf ' to '/u01/app/oracle/oradata/disk_5/indx.dbf1'
  2  ;

Database altered.
終於搞定了。

第5遍開始做dg

這次很快。中途拋了幾個ORA錯誤提提神，影響不啊，最後終於看到了一個對號。

檢視後臺，正常了。

MRP0: Background Managed Standby Recovery process started (STDBY)
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 235
Tue Oct 30 17:57:22 2012
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  NODELAY