Tuesday, July 06, 2010

Moving Control files and redo logfiles to a different filesystem

Background:
Dynamicaly moving control files and redo log files from filesystem /dbdata1/ to /dbdata3/ and from /dbdata2/ to /dbdata4/
1. run as sysdba
export ORACLE_SID=orcl
sqlplus / as sysdba
create pfile='pfile_backup_orcl.ora' from spfile;

SET pagesize 0
SET feedback off

spool OS_command_orcl.sh

SELECT 'cp ' || NAME || ' '
||
CASE
WHEN REGEXP_SUBSTR(NAME, '^\/[^/]+\/') = '/dbdata1/' THEN REPLACE (NAME, '/dbdata1/', '/dbdata3/')
WHEN REGEXP_SUBSTR(NAME, '^\/[^/]+\/') = '/dbdata2/' THEN REPLACE (NAME, '/dbdata2/', '/dbdata4/')
END OS_command
FROM v$controlfile
UNION ALL
SELECT
'cp ' || MEMBER || ' '
||CASE
WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata1/'
THEN
REPLACE (MEMBER, '/dbdata1/', '/dbdata3/')
WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata2/'
THEN
REPLACE (MEMBER, '/dbdata2/', '/dbdata4/')
END
OS_command
FROM v$logfile;

spool OFF

spool DB_command_orcl.sql

SELECT 'alter database rename file ''' || MEMBER || ''' to '''
||
CASE
WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata1/' THEN REPLACE (MEMBER, '/dbdata1/', '/dbdata3/')
WHEN REGEXP_SUBSTR(MEMBER, '^\/[^/]+\/') = '/dbdata2/' THEN REPLACE (MEMBER, '/dbdata2/', '/dbdata4/')
END || ''';'
DB_command
FROM v$logfile
UNION ALL
SELECT
'alter system set control_files='
FROM
DUAL
UNION ALL
SELECT
''''
||CASE
WHEN REGEXP_SUBSTR(VALUE, '^\/[^/]+\/') = '/dbdata1/'
THEN
REPLACE (VALUE, '/dbdata1/', '/dbdata3/')
WHEN REGEXP_SUBSTR(VALUE, '^\/[^/]+\/') = '/dbdata2/'
THEN
REPLACE (VALUE, '/dbdata2/', '/dbdata4/')
END
|| CASE
WHEN ROWNUM = (
SELECT
COUNT(*)
FROM
v$spparameter
WHERE
NAME = 'control_files'
) THEN ''''
ELSE ''','
END
FROM v$spparameter
WHERE NAME = 'control_files'
UNION ALL
SELECT
' SCOPE=SPFILE;'
FROM
DUAL;

spool off;

spool delete_after_week_orcl.sh

SELECT 'rm ' || NAME OS_command
FROM v$controlfile
UNION ALL
SELECT
'rm ' || MEMBER
OS_command
FROM v$logfile;

spool off;
2. Shutdown the database.
shutdown immediate;
startup restrict;
shutdown normal;
3. Copy (not move) the redo log files and control files from the old location to the new location using OS command.
# sh OS_command_orcl.sh
4. Startup & mount the database but do not open.
startup mount
5. Rename
@DB_command_orcl.sql
6. Now open the database
alter database open;
7. after a while
@delete_after_week_orcl.sh;

1 comment:

Facebook Applications said...

What an amazing job you guys are doing . Keep up the good work .