oracle_修改数据文件路径

786

1页

0次

2020-05-13

5墨值下载

1、关闭数据库：

shutdown immediate;

2、然后启动到 mount 状态

startup mount;

2、修改参数文件：

*.db_file_name_convert='+DATA_LAYER/pay/datafile','/backup2/pay/oradata/data_lay

er','+DATA_LAYER/pay/tempfile','/backup2/pay/oradata/data_layer','+DATA/pay/data

file','/backup2/pay/oradata/data','+DATA/pay/tempfile','/backup2/pay/oradata/dat

3、迁移物理文件

cp -r /oradata/pay/ /backup2/pay/oradata/

cp -r /u01/oradata/pay/ /backup2/pay/oradata/

4、执行 mount 状态下迁移命令：

select 'alter database rename file ' ||''''||name||'''' || ' to '||''''||

replace(name,'/oradata/pay/','/backup2/pay/oradata/')||''''||';' from

v$datafile where substr(name,2,7)='oradata';

select 'alter database rename file ' ||''''||name||'''' || ' to '||''''||

replace(name,'/u01/oradata/pay/','/backup2/pay/oradata/')||''''||';' from

v$datafile where substr(name,2,3)='u01';

5、更改临时表空间文件路径：

col name for a50;

select name,bytes/1024/1024 m,status from v$tempfile;

alter tablespace temp add tempfile

'/backup2/pay/oradata/data/temp.263.889194559' size 30G reuse;

alter database tempfile '/backup2/pay/oradata/data/temp.263.889194559'

autoextend on next 30M maxsize unlimited;

alter database tempfile '/backup2/pay/oradata/data/temp1.dbf' offline;

alter database tempfile '/backup2/pay/oradata/data/temp1.dbf' drop

INCLUDING DATAFILES;;

-- 报错：ORA-25152: TEMPFILE cannot be dropped at this time 稍后再执行一次

-- rm -rf '/backup2/pay/oradata/data/temp1.dbf'

alter database open;

of 1

5墨值下载

关注