oracle rename耗时,oracle rename数据文件的两种方法

第一种

alter tablespace users rename datafile '==' to ‘***';

这种方式需要数据库处于open状态，表空间在offline的状态下才能更改。

SQL> alter tablespace users rename datafile '/opt/ora10g/oradata/orcl/user0100.dbf','/opt/ora10g/oradata/orcl/user099.dbf' to '/opt/ora10g/oradata/orcl/userrename1.dbf','/opt/ora10g/oradata/orcl/userrename2.dbf';

alter tablespace users rename datafile '/opt/ora10g/oradata/orcl/user0100.dbf','/opt/ora10g/oradata/orcl/user099.dbf' to '/opt/ora10g/oradata/orcl/userrename1.dbf','/opt/ora10g/oradata/orcl/userrename2.dbf'

*

ERROR at line 1:

ORA-01525: error in renaming data files

ORA-01121: cannot rename database file 107 - file is in use or recovery

ORA-01110: data file 107: '/opt/ora10g/oradata/orcl/user0100.dbf'

SQL> alter tablespace users offline;

Tablespace altered.

SQL> alter tablespace users rename datafile '/opt/ora10g/oradata/orcl/user0100.dbf','/opt/ora10g/oradata/orcl/user099.dbf' to '/opt/ora10g/oradata/orcl/userrename1.dbf','/opt/ora10g/oradata/orcl/userrename2.dbf';

alter tablespace users rename datafile '/opt/ora10g/oradata/orcl/user0100.dbf','/opt/ora10g/oradata/orcl/user099.dbf' to '/opt/ora10g/oradata/orcl/userrename1.dbf','/opt/ora10g/oradata/orcl/userrename2.dbf'

*

ERROR at line 1:

ORA-01525: error in renaming data files

ORA-01141: error renaming data file 107 - new file '/opt/ora10g/oradata/orcl/userrename1.dbf' not found

ORA-01110: data file 107: '/opt/ora10g/oradata/orcl/user0100.dbf'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> !

[oracle@rac1 ~]$ cp /opt/ora10g/oradata/orcl/user0100.dbf /opt/ora10g/oradata/orcl/userrename1.dbf[oracle@rac1 ~]$ cp /opt/ora10g/oradata/orcl/user099.dbf /opt/ora10g/oradata/orc

l/userrename2.dbf

[oracle@rac1 ~]$ exit

exit

SQL> alter tablespace users rename datafile '/opt/ora10g/oradata/orcl/user0100.dbf','/opt/ora10g/oradata/orcl/user099.dbf' to '/opt/ora10g/oradata/orcl/userrename1.dbf','/opt/ora10g/oradata/orcl/userrename2.dbf';

Tablespace altered.

SQL> alter tablespace users online;

Tablespace altered.

第二种

alter database rename file ‘==='  to '***';

这种方式需要数据库处于mount状态

SQL> startup mount

ORACLE instance started.

Total System Global Area 788529152 bytes

Fixed Size 2087216 bytes

Variable Size 423626448 bytes

Database Buffers 356515840 bytes

Redo Buffers 6299648 bytes

Database mounted.

SQL> alter database rename file '/opt/ora10g/oradata/orcl/userrename2.dbf','/opt/ora10g/oradata/orcl/userrename1.dbf' to '/opt/ora10g/oradata/orcl/user099.dbf','/opt/ora10g/oradata/orcl/user0100.dbf';

Database altered.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01113: file 106 needs media recovery

ORA-01110: data file 106: '/opt/ora10g/oradata/orcl/user099.dbf'

--这里不能open的原因是刚刚关闭数据库写了userrename2.dbf和userrename1.dbf这两个数据文件的scn，而user099.dbf和user0100.dbf的scn还是offline的时候的，这样控制文件的头和数据文件头不一致，所以数据库打不开。

SQL> alter database rename file '/opt/ora10g/oradata/orcl/user099.dbf','/opt/ora10g/oradata/orcl/user0100.dbf' to '/opt/ora10g/oradata/orcl/userrename2.dbf','/opt/ora10g/oradata/orcl/userrename1.dbf';

Database altered.

SQL> alter database open;

Database altered.

另外附上批量修改数据文件名的语句

set pagesize 999

set linesize 999

select 'alter database rename file '||''''||member||''''||' to '||chr(39)||replace(member,'/paic/hq/bk/restore/data/oradata/lass/','/paic/z4ah8020/stg/lass/oradata/hs03lass/')||''';'

from v$logfile

where member like '/paic/hq/bk/restore/data/oradata/lass/%';

select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'/paic/hq/bk/restore/data/oradata/lass/','/paic/z4ah8020/stg/lass/oradata/hs03lass/')||''';'

from v$datafile

where name like '/paic/hq/bk/restore/data/oradata/lass/%'

select 'alter database rename file '||''''||name||''''||' to '||chr(39)||replace(name,'/paic/hq/bk/restore/data/oradata/lass/','/paic/z4ah8020/stg/lass/oradata/hs03lass/')||''';'

from v$tempfile

where name like '/paic/hq/bk/restore/data/oradata/lass/%'

备注：红色为原路径 紫色为目标路径