Two methods of Oracle rename data files

Two methods of Oracle rename data files2012-12-11 20:44 10925 People read comments (0) favorite reports Classification:Oracle (98)

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

First Kind

Alter tablespace users rename datafile ' = = ' to ' * * * ';

This way requires the database to be in the open state, and the tablespace can be changed in a offline state.

[SQL]View PlainCopy

2. 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 ';
4. 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 '
6. *
8. ERROR at line 1:
10. Ora-01525:error in renaming data files
12. Ora-01121:cannot Rename database file 107-file is on use or recovery
14. Ora-01110:data file 107: '/opt/ora10g/oradata/orcl/user0100.dbf '
18. Sql> alter tablespace users offline;
20. Tablespace altered.
24. 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 ';
26. 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 '
28. *
30. ERROR at line 1:
32. Ora-01525:error in renaming data files
34. Ora-01141:error renaming data file 107-new file '/opt/ora10g/oradata/orcl/userrename1.dbf ' not found
36. Ora-01110:data file 107: '/opt/ora10g/oradata/orcl/user0100.dbf '
38. Ora-27037:unable to obtain file status
40. linux-x86_64 error:2: No such file or directory
42. Additional Information:3
46. Sql>!
48. [Email protected] ~]$ Cp/opt/ora10g/oradata/orcl/user0100.dbf/opt/ora10g/oradata/orcl/userrename1.dbf[[email Protected] ~]$ Cp/opt/ora10g/oradata/orcl/user099.dbf/opt/ora10g/oradata/orc
50. L/userrename2.dbf
52. [[Email protected] ~]$ exit
54. Exit
58. 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 ';
60. Tablespace altered.
64. Sql> alter tablespace users online;
66. Tablespace altered.

The second Kind

Alter DATABASE rename file ' = = = ' to ' * * *;

This way requires the database to be in the Mount State

[SQL]View PlainCopy

2. Sql> Startup Mount
4. ORACLE instance started.
8. Total System Global area 788529152 bytes
10. Fixed Size 2087216 bytes
12. Variable Size 423626448 bytes
14. Database buffers 356515840 bytes
16. Redo buffers 6299648 bytes
18. Database mounted.
20. 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 ';
24. Database altered.
28. sql> alter database open;
30. Alter database open
32. *
34. ERROR at line 1:
36. Ora-01113:file 106 Needs Media recovery
38. Ora-01110:data file 106: '/opt/ora10g/oradata/orcl/user099.dbf '
40. --This cannot open because just shut down the database wrote userrename2.dbf and userrename1.dbf the SCN of the two data files, while USER099.DBF and user0100.dbf SCN or offline time, so control the file header and data text The database cannot be opened because the header is inconsistent.
44. 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 ';
48. Database altered.
52. sql> alter database open;
56. Database altered.

Also attach a batch to modify the data file name of the statement

[SQL]View PlainCopy

2. Set pagesize 999
4. Set Linesize 999
6. Select ' ALTER DATABASE rename file ' | | "'| | member| | "'| | ' to ' | | Chr (39) | | replace (Member,'/paic/hq/bk/restore/data/oradata/lass/','/paic/z4ah8020/stg/lass/oradata/ hs03lass/') | |   "'; '
8. From V$logfile
10. Where member like '/paic/hq/bk/restore/data/oradata/lass/% ';
14. Select ' ALTER DATABASE rename file ' | | "'| | name| | "'| | ' to ' | | Chr (39) | | Replace (name,'/paic/hq/bk/restore/data/oradata/lass/','/paic/z4ah8020/stg/lass/oradata/ hs03lass/') | |   "'; '
16. From V$datafile
18. where name like '/paic/hq/bk/restore/data/oradata/lass/% '
22. Select ' ALTER DATABASE rename file ' | | "'| | name| | "'| | ' to ' | | Chr (39) | | Replace (name,'/paic/hq/bk/restore/data/oradata/lass/','/paic/z4ah8020/stg/lass/oradata/ hs03lass/') | |   "'; '
24. From V$tempfile
26. where name like '/paic/hq/bk/restore/data/oradata/lass/% '

Two methods of Oracle rename data files