Move db to another host (scp, rename)

REM ################################################
REM # Creator: Didier Vince
REM # Created: 2009/10/01
REM # Name: MoveDB.sql
REM ################################################
REM #
REM # Compatible: Oracle 9i 10g 11g
REM #
REM ################################################
REM #
REM # Move DB to a new machine
REM # 1:  creates scp commands for all the db files (but no init, spfile)
REM # 2:  renames files to our standard
REM #
REM ################################################

define newmachine="&1"

SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF SERVEROUT OFF VER OFF

undefine direPos dataPos redoPos miroPos
col direPos new_value direPos

set termout off
select instr(b.name,'/',-1,1) + 1 as direPos from v$tablespace a, v$datafile b where a.ts#=b.ts# and a.name='SYSTEM'
/

define dataPos=&direPos
define redoPos=&direPos+8
define miroPos=&direPos+7

undefine iname
col iname new_value iname

select instance_name as iname from v$instance
/

set lines 254 pages 0
spool Copy&iname.FilesTo&newmachine..sh
prompt exit
select 'scp ' || name || ' oradba@&newmachine.:/oracle/bases/&iname./datafile/' || substr(name,&dataPos.) from v$datafile order by ts#
/
select 'scp ' || name || ' oradba@&newmachine.:/oracle/bases/&iname./work/' || substr(name,&dataPos.) from v$tempfile
/
select 'scp ' || member || ' oradba@&newmachine.:/oracle/bases/&iname./rlm1/' from v$logfile where member like '%/redolog/%'
/
select 'scp ' || member || ' oradba@&newmachine.:/oracle/bases/&iname./rlm2/' from v$logfile where member not like '%/redolog/%'
/
select 'scp ' || name || ' oradba@&newmachine.:/oracle/bases/&iname./controlfile/' from v$controlfile
/
select 'scp ' || name || ' oradba@&newmachine.:/oracle/bases/&iname./rlm1/' from v$controlfile
/
select 'scp ' || name || ' oradba@&newmachine.:/oracle/bases/&iname./rlm2/' from v$controlfile
/
spool off

spool Rename&iname.Files.sql
prompt exit
select 'Alter database rename file ''' || name || ''' to ''/oracle/bases/&iname./datafile/' || substr(name,&dataPos.) || ''';' from v$datafile order by ts#
/
select 'Alter database rename file ''' || name || ''' to ''/oracle/bases/&iname./work/' || substr(name,&dataPos.) || ''';' from v$tempfile
/
select 'Alter database rename file ''' || member || ''' to ''/oracle/bases/&iname./rlm1/' || substr(member,&redoPos.) || ''';' from v$logfile  where member like '%/redolog/%'
/
select 'Alter database rename file ''' || member || ''' to ''/oracle/bases/&iname./rlm2/' || substr(member,&dataPos.) || ''';' from v$logfile where member not like '%/redolog/%'
/
spool off
quit

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.