批量迁移Oracle数据文件，日志文件及控制文件
作者：网络转载 发布时间：[ 2013/6/9 13:23:09 ] 推荐标签：
4、实施迁移
sys@SYBO2SZ> startup mount force; --->切换数据库到mount状态
ORACLE instance started.
Database mounted.
sys@SYBO2SZ> @/users/robin/dba_scripts/custom/sql/transfer_db_files -->调用脚本进行数据及日志文件的迁移
Step 1， Coping file to destination from source
============================================
Step 2， updating files to control file
============================================
sys@SYBO2SZ> alter database open; -->切换数据库到open状态
Database altered.
sys@SYBO2SZ> @dba_files_all_2.sql -->验证切换结果
Tablespace Name / File Class Filename File Size Auto
----------------------------- ---------------------------------------------------------------------- ----
GOEX_ACCOUNT_IDX /u02/database/SY5221BK/oradata/SY5221BK_account_idx.dbf 16，777，216 YES
--.......... .................
TEMP /u02/database/SY5221BK/temp/tempSY5221BK.dbf 432，013，312 YES
UNDOTBS1 /u02/database/SY5221BK/undo/undotbsSY5221BK.dbf 429，916，160 YES
UNDOTBS2 /u02/database/SY5221BK/undo/undotbsSY5221BK2.dbf 314，572，800 YES
[ ONLINE REDO LOG ] /u02/database/SY5221BK/redolog/log3aSY5221BK.log 20，971，520
[ ONLINE REDO LOG ] /u02/database/SY5221BK/redolog/log3bSY5221BK.log 20，971，520
[ ONLINE REDO LOG ] /u02/database/SY5221BK/redolog/log4aSY5221BK.log 20，971，520
[ ONLINE REDO LOG ] /u02/database/SY5221BK/redolog/log4bSY5221BK.log 20，971，520
-------------
sum 5，107，376，128
41 rows selected.
--如果仅仅是迁移数据文件以及日志文件则上述步骤完成即可
--如果需要修改相关的参数文件以及迁移控制文件则继续下面的步骤
--由于控制文件的在mount状态下被校验，因此我们在nomount状态下来处理
sys@SYBO2SZ> shutdown immediate;
sys@SYBO2SZ> startup nomount;
-->修改参数文件之前先备份spfile
sys@SYBO2SZ> create pfile='/users/oracle/OraHome10g/dbs/initSYBO2SZ_bak.ora' from spfile;
File created.
-->下面对相关的参数文件的路径进行修改，如果不需要修改参数，则跳过下面的步骤
sys@SYBO2SZ> show parameter dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string /u02/database/SYBO2SZ/bdump
core_dump_dest string /u02/database/SYBO2SZ/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /u02/database/SYBO2SZ/udump
sys@SYBO2SZ> alter system set background_dump_dest='/u02/database/SY5221BK/bdump' scope=both sid='*';
System altered.
sys@SYBO2SZ> alter system set core_dump_dest='/u02/database/SY5221BK/cdump' scope=both sid='*';
System altered.
sys@SYBO2SZ> alter system set user_dump_dest='/u02/database/SY5221BK/udump' scope=both sid='*';
System altered.
sys@SYBO2SZ> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u02/database/SYBO2SZ/flash_re
covery_area
db_recovery_file_dest_size big integer 1G
sys@SYBO2SZ> alter system set db_recovery_file_dest='/u02/database/SY5221BK/flash_recovery_area' scope=both;
System altered.
sys@SYBO2SZ> show parameter LOG_ARCHIVE_DEST_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=/u02/database/SYBO2SZ
/archive/
log_archive_dest_10 string
sys@SYBO2SZ> alter system set log_archive_dest_1='LOCATION=/u02/database/SY5221BK/archive' scope=both;
System altered.
sys@SYBO2SZ> show parameter UTL_FILE_DIR
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /u02/database/SYBO2SZ/udump
sys@SYBO2SZ> alter system set UTL_FILE_DIR='/u02/database/SY5221BK/udump' scope=both;
alter system set UTL_FILE_DIR='/u02/database/SY5221BK/udump' scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified -->该参数不能修改内存值
sys@SYBO2SZ> alter system set UTL_FILE_DIR='/u02/database/SY5221BK/udump' scope=spfile;
System altered.
-->下面对控制文件位置进行修改
sys@SYBO2SZ> show parameter control_f
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u02/database/SYBO2SZ/controlf
/cntl1SYBO2SZ.ctl， /u02/databa
se/SYBO2SZ/controlf/cntl2SYBO2
SZ.ctl， /u02/database/SYBO2SZ/
controlf/cntl3SYBO2SZ.ctl
-->将控制文件复制到新位置
sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/controlf/cntl1SYBO2SZ.ctl /u02/database/SY5221BK/controlf/cntl1SY5221BK.ctl
sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/controlf/cntl1SYBO2SZ.ctl /u02/database/SY5221BK/controlf/cntl2SY5221BK.ctl
sys@SYBO2SZ> ho cp /u02/database/SYBO2SZ/controlf/cntl1SYBO2SZ.ctl /u02/database/SY5221BK/controlf/cntl3SY5221BK.ctl
-->Author : Robinson Cheng
-->Blog : http://blog.csdn.net/robinson_0612
-->通过修改control_files参数来修改控制文件位置
sys@SYBO2SZ> alter system set control_files='/u02/database/SY5221BK/controlf/cntl1SY5221BK.ctl'，
2 '/u02/database/SY5221BK/controlf/cntl2SY5221BK.ctl'，'/u02/database/SY5221BK/controlf/cntl3SY5221BK.ctl'
3 scope=spfile;
System altered.
sys@SYBO2SZ> shutdown immediate;
sys@SYBO2SZ> startup mount;
SQL> show parameter control_f
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u02/database/SY5221BK/control
f/cntl1SY5221BK.ctl， /u02/data
base/SY5221BK/controlf/cntl2SY
5221BK.ctl， /u02/database/SY52
21BK/controlf/cntl3SY5221BK.ct
l
sys@SYBO2SZ> show parameter dump
background_core_dump string partial
background_dump_dest string /u02/database/SY5221BK/bdump
core_dump_dest string /u02/database/SY5221BK/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /u02/database/SY5221BK/udump
sys@SYBO2SZ> alter database open;
Database altered.
sales@spasvo.com