数据库文件的迁移-阿里云开发者社区

数据库文件的迁移

2014-03-23 934

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 关于数据库中的文件迁移，需要考虑普通数据文件，redo日志文件（还需要考虑是否为current状态），undo表空间，临时表空间，system,sysaux表空间。

关于数据库中的文件迁移，需要考虑普通数据文件，redo日志文件（还需要考虑是否为current状态），undo表空间，临时表空间，system,sysaux表空间。
文件迁移可以参考下面的伪代码:
move_non_system_tablespace ，需要在open状态

{

alter tablespace xxxx offline;

cp datafiles

alter tablespace xxx rename sourcexxxxx to targetxxxxx;

alter tablespace xxxxx online;

}

move_system_tablespace(system,sysaux,undo),需要在mount状态

{

cp datafiles

alter database rename file sourcexxxxx to targetxxxxx;

alter database open;

}

move_redo_logs

{

1. for current/active logfiles, need to in mount state

cp logfiles

alter database rename file sourcexxxxx to targetxxxxx;

2. for inactive/unused logfiles,can change online

cp logfiles

alter database rename file sourcexxxxx to targetxxxxx;

}

move_temp_files

{

cp datafiles

alter database rename file sourcexxxxx to targetxxxxx;

alter database tempfile xxxx online;

}

示例脚本如下：

##### normal tablespace ####
alter tablespace TEST_DATA offline;
!cp '/oravl08/oradata/TESTDB/TEST_DATA_1.dbf' '/oravl01/oracle/TEST_DATA_1.dbf';
alter tablespace TEST_DATA rename datafile '/oravl08/oradata/TESTDB/TEST_DATA_1.dbf' to '/oravl01/oracle/TEST_DATA_1.dbf';
alter tablespace TEST_DATA online;

alter tablespace TEST_IX offline;
!cp '/oravl08/oradata/TESTDB/TEST_IX_1.dbf' '/oravl01/oracle/TEST_IX_1.dbf';
alter tablespace TEST_IX rename datafile '/oravl08/oradata/TESTDB/TEST_IX_1.dbf' to '/oravl01/oracle/TEST_IX_1.dbf';
alter tablespace TEST_IX online;

##### temp files ####
alter database tempfile '/oravl08/oradata/TESTDB/TEMP_1.dbf' offline;
!cp '/oravl08/oradata/TESTDB/TEMP_1.dbf' '/oravl01/oracle/TEMP_1.dbf'
alter database rename file '/oravl08/oradata/TESTDB/TEMP_1.dbf' to '/oravl01/oracle/TEMP_1.dbf';
alter database tempfile '/oravl01/oracle/TEMP_1.dbf' online;

#### system,sysaux,undo ####
!cp '/oravl08/oradata/TESTDB/SYSTEM_1.dbf' '/oravl01/oracle/SYSTEM_1.dbf'
alter database rename file '/oravl08/oradata/TESTDB/SYSTEM_1.dbf' to '/oravl01/oracle/SYSTEM_1.dbf';

!cp '/oravl08/oradata/TESTDB/SYSAUX_1.dbf' '/oravl01/oracle/SYSAUX_1.dbf'
alter database rename file '/oravl08/oradata/TESTDB/SYSAUX_1.dbf' to '/oravl01/oracle/SYSAUX_1.dbf';

!cp '/oravl08/oradata/TESTDB/UNDOTBS_1.dbf' '/oravl01/oracle/UNDOTBS_1.dbf'
alter database rename file '/oravl08/oradata/TESTDB/UNDOTBS_1.dbf' to '/oravl01/oracle/UNDOTBS_1.dbf';

#### logfiles ####
!cp '/oravl03/oradata/TESTDB/redo_g1_m1.dbf' '/oravl01/oracle/redo_g1_m1.dbf'
alter database rename file '/oravl03/oradata/TESTDB/redo_g1_m1.dbf' to '/oravl01/oracle/redo_g1_m1.dbf';

!cp '/oravl03/oradata/TESTDB/redo_g1_m2.dbf' '/oravl01/oracle/redo_g1_m2.dbf'
alter database rename file '/oravl03/oradata/TESTDB/redo_g1_m2.dbf' to '/oravl01/oracle/redo_g1_m2.dbf';

最后带给大家一些福利，关于文件的迁移，可以参考如下的脚本。

if [[ -d $1 ]]; then

echo 'Directory has found :)'

else

echo "Directory doesn't exists! :("

exit

#used for 11g

#select regexp_count(file_name,'/') from dba_data_files

#used for 10g,9i..

#select (length(file_name)-length(replace(file_name,'/',null)))/(length('/')) from dba_data_files

#get file_name

# select substr(file_name,instr(file_name,'/',1,regexp_count(file_name,'/'))) from dba_data_files;

sqlplus -s / as sysdba

set linesize 200

set pages 0

prompt ########## Scripts as below #######

select 'DB is now in '||status||' status' from gv\$instance;

select

'alter tablespace '||tablespace_name||' offline; '||chr(10)

||'!cp '||chr(39)||file_name||chr(39)||' ' ||chr(39)||'$1'||substr(file_name,instr(file_name,'/',1,regexp_count(file_name,'/')))||chr(39)||'; '||chr(10)

||'alter tablespace '||tablespace_name||' rename datafile '||chr(39)||file_name||chr(39)||' to '||chr(39)||'$1'||substr(file_name,instr(file_name,'/',1,regexp_count(file_name,'/')))||chr(39)||'; '||chr(10)

|| 'alter tablespace '||tablespace_name||' online; '

from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX') and tablespace_name not like 'UNDO%';

prompt ##### temp files ####

select 'alter database tempfile '||chr(39)||name||chr(39)||' offline;'||chr(10)

||'!cp '||chr(39)||name||chr(39)||' ' ||chr(39)||'$1'||substr(name,instr(name,'/',1,regexp_count(name,'/')))||chr(39)||' '||chr(10)

||'alter database rename file '||chr(39)||name||chr(39)||' to '||chr(39)||'$1'||substr(name,instr(name,'/',1,regexp_count(name,'/')))||chr(39)||'; '||chr(10)

|| 'alter database tempfile '||chr(39)||'$1'||substr(name,instr(name,'/',1,regexp_count(name,'/')))||chr(39)||' online; '

from v\$tempfile;

prompt #### system,sysaux,undo ####

select

'!cp '||chr(39)||f.name||chr(39)||' ' ||chr(39)||'$1'||substr(f.name,instr(f.name,'/',1,regexp_count(f.name,'/')))||chr(39)||' '||chr(10)

||'alter database rename file '||chr(39)||f.name||chr(39)||' to '||chr(39)||'$1'||substr(f.name,instr(f.name,'/',1,regexp_count(f.name,'/')))||chr(39)||'; '

from v\$datafile f,v\$tablespace t where t.ts#=f.ts# and (t.name in ('SYSTEM','SYSAUX') or t.name like 'UNDO%');

prompt #### logfiles ####

select

'!cp '||chr(39)||member||chr(39)||' ' ||chr(39)||'$1'||substr(member ,instr(member ,'/',1,regexp_count(member ,'/')))||chr(39)||' '||chr(10)

||'alter database rename file '||chr(39)||member ||chr(39)||' to '||chr(39)||'$1'||substr(member ,instr(member ,'/',1,regexp_count(member ,'/')))||chr(39)||'; '

from v\$logfile f,v\$log l where f.group#=l.group# and l.status!='CURRENT';

EOF

exit