I recently had a project to migrate datafiles to our new san array, and part of that project I wanted to split apart each of the databases to their own mount points to allow mirror splits to be easier for quick backups. To perform this I pretty much had two options to move all the datafiles (60-70 total/database). I could dump the controlfile to trace, make the modifications then recreate the controlfile or I would have to move the datafiles on the os and perform alter database statements.
I wanted to just go the route of recreating the controlfile because it would have been easier to do mass edits of one file but these particular databases were not registered to an rman catalog, and I did not want to lose backup history even though these were just test environments cloned on a regular basis.
So, the choice I had left was to move files and do the alter databases. After doing a bit of cursory searching I didn't find any scripts to generate the statements for me, so I put together a driver script that made all the necessary steps. Hopefully this will save someone time in the future with a script to start from. This was done against 10gR2.
Some assumptions:
- The datafile locations assume ofa compliant structure "/uxx/oradata/SID/"
- For the driver script being created the destination will be all the same, you can change this after the script has been generated.
- You need to change the source and destination.
set serveroutput on size unlimited for wra;
set linesize 200
set feedback off
set echo off
set timi off
set trimspool on
spool &&SID._generate
CREATE TABLE weg_temp (
step INTEGER,
seq integer,
STATEMENT varchar2(200));
-- Generate move the controlfile script
DECLARE
-- REGEX MATCHING PARMS
source VARCHAR2 (10) := '/u0[1-3]';
destination VARCHAR2 (5) := '/u06';
CURSOR ctrl_file_cur
IS
SELECT name FROM v$controlfile;
CURSOR redo_cur
IS
SELECT MEMBER FROM v$logfile;
CURSOR datafile_cur
IS
SELECT name FROM v$datafile;
CURSOR tempfile_cur
IS
SELECT name FROM v$tempfile;
ctrl_file_rec ctrl_file_cur%ROWTYPE;
redo_rec redo_cur%ROWTYPE;
curholder VARCHAR2 (500);
sqlstatement VARCHAR2 (2000);
unixstatement VARCHAR2 (2000);
loopcount INTEGER;
regexbuffer varchar2(100);
BEGIN
sqlstatement := 'ALTER SYSTEM SET control_files=';
loopcount := 0;
-- Populate the table with the section headings and static steps
insert into weg_temp values (1,0,'-- 1. Change the controlfile locations in the spfile');
insert into weg_temp values (2,0,'-- 2. Shutdown the database');
insert into weg_temp values (2,1, 'shutdown immediate;');
insert into weg_temp values (3,0,'# 3. Move the control files on the os');
insert into weg_temp values (4,0,'# 4. Move the redo log files on the os');
insert into weg_temp values (5,0,'# 5. Move the datafiles on the os');
insert into weg_temp values (6,0,'# 6. Move the tempfiles on the os');
insert into weg_temp values (7,0, '-- 7. Start the database in mount');
insert into weg_temp values (7,1, 'startup mount;');
insert into weg_temp values (8,0, '-- 8. Change redo log files in the database');
insert into weg_temp values (9,0, '-- 9. Change datafiles in the database');
insert into weg_temp values (10,0, '-- 10. Change tempfiles in the database');
insert into weg_temp values (11,0, '-- 11. Open the database and hope it works.');
insert into weg_temp values (11,1, 'alter database open;');
-- Spacing for file
insert into weg_temp values (2,-1,NULL);
insert into weg_temp values (3,-1,NULL);
insert into weg_temp values (4,-1,NULL);
insert into weg_temp values (5,-1,NULL);
insert into weg_temp values (6,-1,NULL);
insert into weg_temp values (7,-1,NULL);
insert into weg_temp values (8,-1,NULL);
insert into weg_temp values (9,-1,NULL);
insert into weg_temp values (10,-1,NULL);
insert into weg_temp values (11,-1,NULL);
OPEN ctrl_file_cur;
LOOP
FETCH ctrl_file_cur INTO ctrl_file_rec;
EXIT WHEN ctrl_file_cur%NOTFOUND;
IF loopcount > 0
THEN
sqlstatement := sqlstatement || ', ';
END IF;
-- Calculate the destination filename
regexbuffer := REGEXP_REPLACE (ctrl_file_rec.name, source, destination);
sqlstatement :=
sqlstatement
|| ''''
|| regexbuffer
|| '''';
insert into weg_temp values (3 ,loopcount+1,'mv ' || ctrl_file_rec.name || ' ' || regexbuffer);
loopcount := loopcount + 1;
END LOOP;
CLOSE ctrl_file_cur;
-- Finish out the sqlstatement
sqlstatement := sqlstatement || ' SCOPE=SPFILE;';
-- Put generated sql statement into the table.
insert into weg_temp values (1,1,sqlstatement);
OPEN redo_cur;
-- Reset loopcount back at 0
loopcount := 0;
LOOP
FETCH redo_cur INTO curholder;
EXIT WHEN redo_cur%NOTFOUND;
regexbuffer := REGEXP_REPLACE (curholder, source, destination);
insert into weg_temp values (4 ,loopcount+1,'mv '|| curholder|| ' '|| regexbuffer);
insert into weg_temp values (8 ,loopcount+1, 'ALTER DATABASE RENAME FILE ''' || curholder || ''' TO ''' || regexbuffer || ''';');
loopcount := loopcount + 1;
END LOOP;
CLOSE redo_cur;
OPEN datafile_cur;
-- Reset loopcount back at 0
loopcount := 0;
LOOP
FETCH datafile_cur INTO curholder;
EXIT WHEN datafile_cur%NOTFOUND;
regexbuffer := REGEXP_REPLACE (curholder, source, destination);
insert into weg_temp values (5 ,loopcount+1,'mv '|| curholder|| ' '|| regexbuffer);
insert into weg_temp values (9 ,loopcount+1, 'ALTER DATABASE RENAME FILE ''' || curholder || ''' TO ''' || regexbuffer || ''';');
loopcount := loopcount + 1;
END LOOP;
CLOSE datafile_cur;
OPEN tempfile_cur;
-- Reset loopcount back at 0
loopcount := 0;
LOOP
FETCH tempfile_cur INTO curholder;
EXIT WHEN tempfile_cur%NOTFOUND;
regexbuffer := REGEXP_REPLACE (curholder, source, destination);
insert into weg_temp values (6 ,loopcount+1,'mv '|| curholder|| ' '|| regexbuffer);
insert into weg_temp values (10 ,loopcount+1, 'ALTER DATABASE RENAME FILE ''' || curholder || ''' TO ''' || regexbuffer || ''';');
loopcount := loopcount + 1;
END LOOP;
CLOSE tempfile_cur;
commit;
END;
/
spool off;
spool &&SID._script.txt
DECLARE
CURSOR weg_temp_cur
IS
SELECT statement
FROM weg_temp
ORDER BY step, seq;
wtr weg_temp_cur%ROWTYPE;
BEGIN
OPEN weg_temp_cur;
LOOP
FETCH weg_temp_cur INTO wtr;
EXIT WHEN weg_temp_cur%NOTFOUND;
DBMS_OUTPUT.put_line (wtr.statement);
END LOOP;
CLOSE weg_temp_cur;
END;
/
spool off;
drop table weg_temp;
exit;
