Yesterday I wrote about an automated script for moving some or all of the files that make up the oracle database. This is occasionally needed to relocate the database files to a new mount point.
Since then I have tried to make the scripts more user friendly and have made the following modifications.
1. Improved documentary comments to guide the user through the process.
2. The ability to review the generated script before running it.
3. The option of not running the generated script and keeping it for later.
4. The option to watch the progress of the file moves
The move_files.sql script now makes use of the ‘rsync’ method of moving/copying files which provides a rolling percentage process. This can be stopped with a simple script alteration.
Example (using same database files as previously).
SQL> @move_files
You are connected to database ORCLSA. Ctrl+C if this is incorrect
******************************************************************
=======================
Relocate Database Files
=======================
Move any files (control,redo,temp,data) in a given directory
to a new directory. Any file on the database in that directory
will be moved. All other files will remain in their own
directories.
You will be prompted for the from and to directory names.
A script will then be prepared which you can review and run
immediately, or keep until another time.
A list of existing files will be provided.
******************************************************************
CTRL+C to abort, Press <ENTER> to continue
:ORCLSA:CTRL:/u02/oradata/jh/control01.ctl
:ORCLSA:CTRL:/u02/oradata/jh/control02.ctl
:ORCLSA:LOG:/u02/oradata/jh/redo_1a.log
:ORCLSA:LOG:/u02/oradata/jh/redo_1b.log
:ORCLSA:LOG:/u02/oradata/jh/redo_2a.log
:ORCLSA:LOG:/u02/oradata/jh/redo_2b.log
:ORCLSA:LOG:/u02/oradata/jh/redo_3a.log
:ORCLSA:LOG:/u02/oradata/jh/redo_3b.log
:ORCLSA:DATA:/u02/oradata/jh/system.dbf
:ORCLSA:DATA:/u02/oradata/jh/sysaux.dbf
:ORCLSA:DATA:/u02/oradata/jh/undo.dbf
:ORCLSA:DATA:/u02/oradata/jh/users01.dbf
:ORCLSA:DATA:/u02/oradata/jh/examples01.dbf
:ORCLSA:DATA:/u02/oradata/jh/xmldb_01.dbf
:ORCLSA:TEMP:/u02/oradata/jh/temp.tmp
Press enter the FROM directory (/u02/oradata/jh)?
Press enter the TO directory (/u03/oradata/orclsa)?
******************************************************************
Would you like to review the generated script <default-Y>?
CTRL+C to abort, Press <ENTER> to perform changes
******************************************************************
Performing File moves on ORCLSA
Before:ORCLSA:CTRL:/u02/oradata/jh/control01.ctl
Before:ORCLSA:CTRL:/u02/oradata/jh/control02.ctl
Before:ORCLSA:LOG:/u02/oradata/jh/redo_1a.log
Before:ORCLSA:LOG:/u02/oradata/jh/redo_1b.log
Before:ORCLSA:LOG:/u02/oradata/jh/redo_2a.log
Before:ORCLSA:LOG:/u02/oradata/jh/redo_2b.log
Before:ORCLSA:LOG:/u02/oradata/jh/redo_3a.log
Before:ORCLSA:LOG:/u02/oradata/jh/redo_3b.log
Before:ORCLSA:DATA:/u02/oradata/jh/system.dbf
Before:ORCLSA:DATA:/u02/oradata/jh/sysaux.dbf
Before:ORCLSA:DATA:/u02/oradata/jh/undo.dbf
Before:ORCLSA:DATA:/u02/oradata/jh/users01.dbf
Before:ORCLSA:DATA:/u02/oradata/jh/examples01.dbf
Before:ORCLSA:DATA:/u02/oradata/jh/xmldb_01.dbf
Before:ORCLSA:TEMP:/u02/oradata/jh/temp.tmp
Amending SPFILE for control files
Stopping Database
Database closed.
Database dismounted.
ORACLE instance shut down.
Moving Control files
building file list ...
1 file to consider
control01.ctl
10076160 100% 59.86MB/s 0:00:00 (xfer#1, to-check=0/1)
sent 10077520 bytes received 42 bytes 20155124.00 bytes/sec
total size is 10076160 speedup is 1.00
building file list ...
1 file to consider
control02.ctl
10076160 100% 11.84MB/s 0:00:00 (xfer#1, to-check=0/1)
sent 10077520 bytes received 42 bytes 6718374.67 bytes/sec
total size is 10076160 speedup is 1.00
Starting Database in mount mode
ORACLE instance started.
Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 385878712 bytes
Database Buffers 62914560 bytes
Redo Buffers 6008832 bytes
Database mounted.
Moving Data : /u02/oradata/jh/system.dbf to /u03/oradata/orclsa/system.dbf
building file list ...
1 file to consider
system.dbf
461381632 100% 16.83MB/s 0:00:26 (xfer#1, to-check=0/1)
sent 461438081 bytes received 42 bytes 17412759.36 bytes/sec
total size is 461381632 speedup is 1.00
Moving Data : /u02/oradata/jh/sysaux.dbf to /u03/oradata/orclsa/sysaux.dbf
building file list ...
1 file to consider
sysaux.dbf
125837312 100% 13.75MB/s 0:00:08 (xfer#1, to-check=0/1)
sent 125852801 bytes received 42 bytes 14806216.82 bytes/sec
total size is 125837312 speedup is 1.00
Moving Data : /u02/oradata/jh/undo.dbf to /u03/oradata/orclsa/undo.dbf
building file list ...
1 file to consider
undo.dbf
209723392 100% 9.42MB/s 0:00:21 (xfer#1, to-check=0/1)
sent 209749119 bytes received 42 bytes 9755774.93 bytes/sec
total size is 209723392 speedup is 1.00
Moving Data : /u02/oradata/jh/users01.dbf to /u03/oradata/orclsa/users01.dbf
building file list ...
1 file to consider
users01.dbf
209723392 100% 9.98MB/s 0:00:20 (xfer#1, to-check=0/1)
sent 209749122 bytes received 42 bytes 10231666.54 bytes/sec
total size is 209723392 speedup is 1.00
Moving Data : /u02/oradata/jh/examples01.dbf to /u03/oradata/orclsa/examples01.dbf
building file list ...
1 file to consider
examples01.dbf
209723392 100% 11.70MB/s 0:00:17 (xfer#1, to-check=0/1)
sent 209749125 bytes received 42 bytes 11337792.81 bytes/sec
total size is 209723392 speedup is 1.00
Moving Data : /u02/oradata/jh/xmldb_01.dbf to /u03/oradata/orclsa/xmldb_01.dbf
building file list ...
1 file to consider
xmldb_01.dbf
954212352 100% 13.04MB/s 0:01:09 (xfer#1, to-check=0/1)
sent 954328963 bytes received 42 bytes 13536581.63 bytes/sec
total size is 954212352 speedup is 1.00
Moving Temp : /u02/oradata/jh/temp.tmp to /u03/oradata/orclsa/temp.tmp
building file list ...
1 file to consider
temp.tmp
20979712 100% 58.58MB/s 0:00:00 (xfer#1, to-check=0/1)
sent 20982399 bytes received 42 bytes 41964882.00 bytes/sec
total size is 20979712 speedup is 1.00
Moving REDO logs
building file list ...
1 file to consider
redo_1a.log
52429312 100% 46.74MB/s 0:00:01 (xfer#1, to-check=0/1)
sent 52435842 bytes received 42 bytes 34957256.00 bytes/sec
total size is 52429312 speedup is 1.00
building file list ...
1 file to consider
redo_1b.log
52429312 100% 16.23MB/s 0:00:03 (xfer#1, to-check=0/1)
sent 52435842 bytes received 42 bytes 14981681.14 bytes/sec
total size is 52429312 speedup is 1.00
building file list ...
1 file to consider
redo_2a.log
52429312 100% 13.12MB/s 0:00:03 (xfer#1, to-check=0/1)
sent 52435842 bytes received 42 bytes 11652418.67 bytes/sec
total size is 52429312 speedup is 1.00
building file list ...
1 file to consider
redo_2b.log
52429312 100% 13.71MB/s 0:00:03 (xfer#1, to-check=0/1)
sent 52435842 bytes received 42 bytes 11652418.67 bytes/sec
total size is 52429312 speedup is 1.00
building file list ...
1 file to consider
redo_3a.log
52429312 100% 12.78MB/s 0:00:03 (xfer#1, to-check=0/1)
sent 52435842 bytes received 42 bytes 11652418.67 bytes/sec
total size is 52429312 speedup is 1.00
building file list ...
1 file to consider
redo_3b.log
52429312 100% 17.34MB/s 0:00:02 (xfer#1, to-check=0/1)
sent 52435842 bytes received 42 bytes 14981681.14 bytes/sec
total size is 52429312 speedup is 1.00
Opening Database
Finished on ORCLSA
After:ORCLSA:CTRL:/u03/oradata/orclsa/control01.ctl
After:ORCLSA:CTRL:/u03/oradata/orclsa/control02.ctl
After:ORCLSA:LOG:/u03/oradata/orclsa/redo_1a.log
After:ORCLSA:LOG:/u03/oradata/orclsa/redo_1b.log
After:ORCLSA:LOG:/u03/oradata/orclsa/redo_2a.log
After:ORCLSA:LOG:/u03/oradata/orclsa/redo_2b.log
After:ORCLSA:LOG:/u03/oradata/orclsa/redo_3a.log
After:ORCLSA:LOG:/u03/oradata/orclsa/redo_3b.log
After:ORCLSA:DATA:/u03/oradata/orclsa/system.dbf
After:ORCLSA:DATA:/u03/oradata/orclsa/sysaux.dbf
After:ORCLSA:DATA:/u03/oradata/orclsa/undo.dbf
After:ORCLSA:DATA:/u03/oradata/orclsa/users01.dbf
After:ORCLSA:DATA:/u03/oradata/orclsa/examples01.dbf
After:ORCLSA:DATA:/u03/oradata/orclsa/xmldb_01.dbf
After:ORCLSA:TEMP:/u03/oradata/orclsa/temp.tmp
SQL>
The scripts are as follows.
Script review_file.sh requires chmod +x
cat list_files.sql
set head off
set feedback off
set pages 100
set verify off
select '&1:'||b.name||':CTRL:'||a.name from v$controlfile a, v$database b
union all
select '&1:'||b.name||':LOG:'||a.member from v$logfile a, v$database b
union all
select '&1:'||b.name||':DATA:'||a.name from v$datafile a, v$database b
union all
select '&1:'||b.name||':TEMP:'||a.name from v$tempfile a, v$database b;
cat review_file.sh
#!/bin/ksh
par1=$1
par2=$2
if [ "${par1}" == "Y" ] || [ "${par1}" == 'y' ]; then
view ${par2}
fi
[oracle@localhost sql]$ cat move_files.sql
define fromdir = '/u02/oradata/jh'
define todir = '/u03/oradata/orclsa'
-- The follow rsync code allows a move command that displays progress.
-- Useful in a large file situation.
define rsmv = 'rsync -aP --remove-sent-files '
-- define rsmv = 'mv -i '
set termout off
column var new_value _var
select name as var from v$database;
set termout on
prompt
accept aaa prompt 'You are connected to database &_var.. Ctrl+C if this is incorrect '
prompt ******************************************************************
prompt
prompt =======================
prompt Relocate Database Files
prompt =======================
prompt
prompt Move any files (control,redo,temp,data) in a given directory
prompt to a new directory. Any file on the database in that directory
prompt will be moved. All other files will remain in their own
prompt directories.
prompt
prompt You will be prompted for the from and to directory names.
prompt A script will then be prepared which you can review and run
prompt immediately, or keep until another time.
prompt
prompt A list of existing files will be provided.
prompt
prompt ******************************************************************
accept aaa prompt 'CTRL+C to abort, Press <ENTER> to continue '
prompt
set head off
set lines 200
set verify off
set feedback off
set trimspool on
@list_files ' '
prompt
accept fromdir default '&fromdir' prompt 'Press enter the FROM directory (&fromdir)? '
accept todir default '&todir' prompt 'Press enter the TO directory (&todir)? '
set termout off
prompt ****************************************************************
spool move_files_&_var..sql
-- List all files before the change occurs
select 'prompt Performing File moves on &_var' from dual;
select '@list_files ''Before''' from dual;
select 'prompt' from dual;
select 'set head off' from dual;
-- Control files parameter must be set before closing the database (spfile only)
select 'prompt Amending SPFILE for control files' from dual;
select aaa from
(
select 0 anum,'alter system set control_files = ' aaa from dual
union
select rownum, ''''||replace(name,'&fromdir','&todir')||''''||
case rownum when (select count(0) from v$controlfile) then null else ',' end
from v$controlfile
union
select 99999999, 'scope = spfile;' from dual
) order by anum;
-- shutdown the database completely
select 'prompt Stopping Database' from dual;
select 'shutdown immediate' from dual;
-- move the control files to their new location while the database is completely closed
select 'prompt Moving Control files' from dual;
select '!&rsmv '||name||' '||replace(name,'&fromdir','&todir')
from v$controlfile
where name like '&fromdir%';
-- startup mount for the movement of all other files
select 'prompt Starting Database in mount mode' from dual;
select 'startup mount' from dual;
-- move the files themselves (datafiles)
select 'prompt Moving Data : '||name||' to '||replace(name,'&fromdir','&todir'),
'!&rsmv '||name||' '||replace(name,'&fromdir','&todir'),
'alter database rename file '''||name||''' to '''||replace(name,'&fromdir','&todir')||''';'
from v$datafile
where name like '&fromdir%'
/
-- move the files themselves (tempfiles)
select 'prompt Moving Temp : '||name||' to '||replace(name,'&fromdir','&todir'),
'!&rsmv '||name||' '||replace(name,'&fromdir','&todir'),
'alter database rename file '''||name||''' to '''||replace(name,'&fromdir','&todir')||''';'
from v$tempfile
where name like '&fromdir%'
/
-- move the files themselves (logfiles)
select 'prompt Moving REDO logs' from dual;
select '!&rsmv '||member||' '||replace(member,'&fromdir','&todir'),
'alter database rename file '''||member||''' to '''||replace(member,'&fromdir','&todir')||''';'
from v$logfile
where member like '&fromdir%'
/
-- Open the database
select 'prompt Opening Database' from dual;
select 'alter database open;' from dual;
select 'select ''Finished on '||name||''' from dual;' from v$database;
--set feedback on
--set verify on
--set head on
-- List all files after the change occurs
select '@list_files ''After''' from dual;
spool off
set termout on
prompt
prompt ******************************************************************
prompt
accept aaa default 'Y' prompt 'Would you like to review the generated script <default-Y>? '
prompt
host ./review_file.sh &aaa move_files_&_var..sql
accept aaa prompt 'CTRL+C to abort, Press <ENTER> to perform changes '
prompt
prompt ******************************************************************
prompt
spool move_files_&_var..lst
@move_files_&_var..sql
spool off
[oracle@localhost sql]$
Happyjohn
No comments:
Post a Comment