移動資料檔名中含有特殊字元的資料檔案方法

在生產環境，發現有一個資料檔名稱中包含特殊字元。現在有個需求，需要將該資料檔案移動到其他目錄，這就導致了無法使用
alter database rename file '' to '';來修改檔名稱了。

在測試環境，測試使用rman copy命令來解決這個問題。
獲取將要移動的資料檔案file#;假如，我們要移動file#=7的檔案"/ora10g/oradata/ora10g/users02.dbf"到"/ora10g/backup/users02.dbf"
$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 7 15:45:32 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select file#,name from v$datafile;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
         1
/ora10g/oradata/ora10g/system01.dbf

         2
/ora10g/oradata/ora10g/undotbs01.dbf

         3
/ora10g/oradata/ora10g/sysaux01.dbf


     FILE#
----------
NAME
--------------------------------------------------------------------------------
         4
/ora10g/oradata/ora10g/users01.dbf

         5
/ora10g/oradata/ora10g/example01.dbf

         6
/ora10g/oradata/ora10g/system02.dbf


     FILE#
----------
NAME
--------------------------------------------------------------------------------
         7
/ora10g/oradata/ora10g/users02.dbf


7 rows selected.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$

登陸到rman，使用copy命令
$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 7 15:45:55 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORA10G (DBID=4115709917)

RMAN> copy datafile 7 to '/ora10g/backup/users02.dbf';

Starting backup at 07-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=256 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/ora10g/oradata/ora10g/users02.dbf
output filename=/ora10g/backup/users02.dbf tag=TAG20121207T154619 recid=29 stamp=801416796
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
Finished backup at 07-DEC-12

Starting Control File and SPFILE Autobackup at 07-DEC-12
piece handle=/ora10g/backup/c-4115709917-20121207-00 comment=NONE
Finished Control File and SPFILE Autobackup at 07-DEC-12

修改oracle資料字典中的記錄。

RMAN> run
2> {
3> set newname for datafile 7 to '/ora10g/backup/users02.dbf';
4> switch datafile 7;
5> }

executing command: SET NEWNAME
using target database control file instead of recovery catalog

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of switch command on default channel at 12/07/2012 15:48:56
ORA-19623: file 7 is open
名稱的修改需要將資料庫啟動到mount狀態。

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    2147483648 bytes

Fixed Size                     2168928 bytes
Variable Size                496887712 bytes
Database Buffers            1644167168 bytes
Redo Buffers                   4259840 bytes

RMAN> run
2> {set newname for datafile 7 to '/ora10g/backup/users02.dbf';
3> switch datafile 7;
4> }

executing command: SET NEWNAME

datafile 7 switched to datafile copy
input datafile copy recid=29 stamp=801416796 filename=/ora10g/backup/users02.dbf

RMAN>

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/07/2012 15:50:24
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/ora10g/backup/users02.dbf'

RMAN> recover datafile 7;

Starting recover at 07-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=266 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 07-DEC-12
由於我們是在資料庫開啟狀態copy的資料檔案，所以需要對其進行恢復。
RMAN> alter database open;

database opened

RMAN> exit
恢復後，資料庫開啟。

資料檔案檢查。
Recovery Manager complete.
$
$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 7 15:50:54 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 200
SQL> col name format a50
SQL>
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------
         1 /ora10g/oradata/ora10g/system01.dbf
         2 /ora10g/oradata/ora10g/undotbs01.dbf
         3 /ora10g/oradata/ora10g/sysaux01.dbf
         4 /ora10g/oradata/ora10g/users01.dbf
         5 /ora10g/oradata/ora10g/example01.dbf
         6 /ora10g/oradata/ora10g/system02.dbf
         7 /ora10g/backup/users02.dbf

7 rows selected.

SQL> 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11590946/viewspace-750756/，如需轉載，請註明出處，否則將追究法律責任。