数据文件和日志文件的批量迁移脚本

来自乐沙弥的世界

脚本内容如下：

Prompt  
Prompt Step 1, Coping file to destination from source  
Prompt ============================================  
Prompt  
set linesize 200  
set heading off verify off feedback off termout off pagesize 999  
define src_dir='ORCL'  
define tar_dir='ORACLE'  
spool /tmp/cp_files.sql  
SELECT 'ho cp ' || name || ' ' || REPLACE (name, '&src_dir', '&tar_dir')  
  FROM v$datafile  
UNION ALL  
SELECT 'ho cp ' || name || ' ' || REPLACE (name, '&src_dir', '&tar_dir')  
  FROM v$tempfile  
UNION ALL  
SELECT 'ho cp ' || MEMBER || ' ' || REPLACE (MEMBER, '&src_dir', '&tar_dir') FROM v$logfile;  
spool off;  
  
@/tmp/cp_files.sql  
  
set termout on  
Prompt  
Prompt  Step 2, updating files to control file  
Prompt ============================================  
Prompt  
set termout off  
spool /tmp/update_cntl.sql  
SELECT    'alter database  rename file '''  
       || name  
       || '''  to '''  
       || REPLACE (name, '&src_dir', '&tar_dir')  
       || ''''  
       || ';'  
  FROM v$datafile  
UNION ALL  
SELECT    'alter database rename file '''  
       || name  
       || '''  to '''  
       || REPLACE (name, '&src_dir', '&tar_dir')  
       || ''''  
       || ';'  
  FROM v$tempfile  
UNION ALL  
SELECT    'alter database rename file '''  
       || MEMBER  
       || ''' to '''  
       || REPLACE (MEMBER, '&src_dir', '&tar_dir')  
       || ''''  
       || ';'  
  FROM v$logfile;  
spool off;  
set termout on;  
@/tmp/update_cntl.sql  
set heading on verify on feedback on termout on