Re: script to find and move omf datafiles?

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 17 Jun 2005 10:21:10 -0700
Message-ID: <1119028870.095422.230850@g49g2000cwa.googlegroups.com>

Rauf Sarwar wrote:
> Akimeu wrote:
> > well... i got a bit further. unfortunately, the failing point is the spool
> > move.bat line. the actual file gets created, but unfortunately it has no
> > text in it (the move command). anyone have any insight? here's the updated
> > script.
> >
> >
> > echo column file_name new_value data_small >>run.sql
> > echo select file_name from dba_data_files >>run.sql
> > echo where tablespace_name = 'DATA_SMALL'; >>run.sql
> > echo accept small_dest char prompt 'Where should the data files be moved to?
> > '>>run.sql
> > echo alter tablespace data_small offline; >>run.sql
> > echo spool move.bat >>run.sql
> > echo select 'move ''&data_small'' ''&small_dest\DATA_SMALL.dbf''' from dual;
> > >>run.sql
> > echo spool off >>run.sql
> > echo host move.bat >>run.sql
> > echo alter database rename '&data_small' to '&small_dest\DATA_SMALL.dbf';
> > >>run.sql
> > echo alter tablespace data_small online; >>run.sql
> > sqlplus /nolog @run.sql
> >
> >
> > echo alter tablespace xhq_data_small offline; >>run.sql
> > "Akimeu" <akimeu_at_yahoo.comAAA> wrote in message
> > news:eIBse.2163$wV5.945_at_fed1read06...
> > > thanks for the tip, holger.
> > >
> > > however, i have a small problem. the spool does not seem to work as
> > > expected, therefore i made a small change. here is part of my batch
> > > script, that generates my sqlplus script that i run at the end.
> > >
> > > @echo off
> > > set data_ts=d:\oracle
> > > echo column file_name new_value data_small >>run.sql
> > > echo select file_name from dba_data_files >>run.sql
> > > echo where tablespace_name = 'DATA_SMALL'; >>run.sql
> > > echo alter tablespace data_small offline; >>run.sql
> > > echo host move '&data_small' %data_ts% >>run.sql
> > > echo alter tablespace data_small online; >>run.sql
> > > sqlplus /nolog @run.sql
> > >
> > > the problem that i'm having here, is that the &data_small does not have
> > > the correct value (or any value that i can tell) when doing the host move
> > > command. any ideas?
> > >
> > > thanks again.
> > >
> > >
> > >
> > > "Holger Baer" <holger.baer_at_science-computing.de> wrote in message
> > > news:d8ul1f$hj3$1_at_news.BelWue.DE...
> > >> Akimeu wrote:
> > >>> hello group... my requirement is to write a sql script, that will move
> > >>> datafiles around. basically, what i need to do (since we're using omf)
> > >>> is retrieve the existing datafile location for a tablespace and rename
> > >>> it to the new location, where i'll move it. can i do this in sql*plus
> > >>> automatically??? can i use some sort of local variables to store the
> > >>> name/location of the existing datafiles which i can then use via the
> > >>> rename command??? thanks for any tips/examples.
> > >>>
> > >>> al
> > >>
> > >> You could create a script via spool:
> > >>
> > >> column value new_value old_omf
> > >> select value from v$parameter where name = 'db_create_file_dest';
> > >> spool move_omf
> > >> select 'move ' || file_name || ' ' || replace (file_name, '&old_omf',
> > >> '/your/path/goes/here') from dba_data_files;
> > >> spool off
> > >>
> > >> You'll have to modify the script to only select the filenames of the
> > >> tablespace you're
> > >> interested in.
> > >>
> > >> Use at your own risk and before you do, please take the time and read
> > >> the
> > >> documentation at tahiti.oracle.com so you understand each of the steps
> > >> involved.
> > >>
> > >> Regards,
> > >> Holger
> > >
> > >

>

> As others have said, take good backup before attempting this *untested*
> script. Copy it in a batch script and run. Make sure to change set
> values in the beginning of the script.

>

> ----- Copy Start ------
> @echo off

>

> set oracle_sid=your_sid
> set runfile=run.sql
> set spoolfile=spool.sql
> set sqlexe=c:\oracle\ora92\bin\sqlplus.exe
> set targetdir=c:\target
> set tblspace=TABLESPACE_NAME_IN_UPPERCASE

>

> echo connect / as sysdba >%runfile%
> echo set pagesize 0 >>%runfile%
> echo set echo off >>%runfile%
> echo set verify off >>%runfile%
> echo set feedback off >>%runfile%
> echo set heading off >>%runfile%
> echo set trimspool on >>%runfile%
> echo set serverout on size 1000000 >>%runfile%
> echo spool %spoolfile% >>%runfile%
> echo declare >>%runfile%
> echo cursor cur_ is >>%runfile%
> echo select file_name >>%runfile%
> echo from dba_data_files >>%runfile%
> echo where upper(tablespace_name) = '%tblspace%'; >>%runfile%
> echo begin >>%runfile%
> echo dbms_output.put_line('alter tablespace %tblspace% offline;');
> >>%runfile%
> echo for rec_ in cur_ loop >>%runfile%
> echo dbms_output.put_line('host move ^"' ^|^| rec_.file_name ^|^|
> '^" ^"%targetdir%^"'); >>%runfile%
> echo end loop; >>%runfile%
> echo dbms_output.put_line('alter tablespace %tblspace% online;');
> >>%runfile%
> echo end; >>%runfile%
> echo / >>%runfile%
> echo spool off >>%runfile%
> echo start %spoolfile%
> echo exit >>%runfile%

>

> %sqlexe% /nolog @%runfile%
> ----- Copy End ------

>

> Regards
> /Rauf

Forgot the rename bit. Here is the revised script. Still *untested*. Lines may wrap.

@echo off

set oracle_sid=your_sid
set runfile=run.sql
set spoolfile=spool.sql
set sqlexe=c:\oracle\ora92\bin\sqlplus.exe set targetdir=c:\target
set tblspace=TABLESPACE_NAME_IN_UPPERCASE

echo connect / as sysdba >%runfile%
echo set pagesize 0 >>%runfile%
echo set echo off >>%runfile%
echo set verify off >>%runfile%

echo set feedback off >>%runfile%
echo set heading off >>%runfile%
echo set linesize 250 >>%runfile%
echo set trimspool on >>%runfile%

echo set serverout on size 1000000 >>%runfile% echo spool %spoolfile% >>%runfile%
echo declare >>%runfile%
echo name_ varchar2(30); >>%runfile% echo cursor cur_ is >>%runfile%

echo       select file_name >>%runfile%
echo       from dba_data_files >>%runfile%
echo       where upper(tablespace_name) = '%tblspace%'; >>%runfile%

echo begin >>%runfile%
echo dbms_output.put_line('alter tablespace %tblspace% offline;'); >>%runfile%
echo for rec_ in cur_ loop >>%runfile% echo dbms_output.put_line('host move ^"' ^|^| rec_.file_name ^|^| '^" ^"%targetdir%^"'); >>%runfile%
echo name_ := substr(rec_.file_name, instr(rec_.file_name, '\', -1, 1) + 1, length(rec_.file_name)); >>%runfile% echo dbms_output.put_line('alter database rename ''' ^|^| rec_.file_name ^|^| ''' to ''' ^|^| '%targetdir%\' ^|^| name_ ^|^| ''';'); >>%runfile%
echo end loop; >>%runfile%
echo dbms_output.put_line('alter tablespace %tblspace% online;'); >>%runfile%
echo end; >>%runfile%
echo / >>%runfile%
echo spool off >>%runfile%
echo start %spoolfile%
echo exit >>%runfile%

%sqlexe% /nolog @%runfile%

Regards
/Rauf Received on Fri Jun 17 2005 - 12:21:10 CDT