Move RAC database from one diskgroup to another in ASM (Manually)

This approach is quite annoying because it has a lot of steps and things to consider, yet here goes a summary of how it could be done in a 3 node RAC as example:

Scenario: Database is TSP, instances are TSP1, TSP2 and TSP3. Everything is under +DATA and will be moved to +DATA_01.

We can start with the datafiles one by one to minimize downtime, in the example below I’m using RMAN to do it just to do it from a single point, you can do it from SQL*Plus without problems, will work just the same. I’ll leave SYSTEM and SYSAUX for the end because downtime will be needed.

-- Move common datafiles
rman target = /
sql 'alter database datafile 3 offline';
copy datafile 3 to '+DATA_01';
switch datafile 3 to copy;
recover datafile 3;
sql 'alter database datafile 3 online';

Now let’s move on to temporary tablespaces.

-- Temporary Tablespaces
sqlplus / as sysdba
CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '+DATA_01' SIZE 64M REUSE AUTOEXTEND ON NEXT 28 MAXSIZE unlimited;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Moving on to Undo tablespaces…

-- Undo tablespaces
-- Node 1
sqlplus / as sysdba
create undo tablespace undotbs01 datafile '+DATA_01' size 500m;
alter system set undo_tablespace='undotbs01';
alter system set undo_tablespace='UNDOTBS01' scope=SPFILE sid='TSP1';
drop tablespace undotbs1 including contents;
-- Node 2
sqlplus / as sysdba
create undo tablespace undotbs02 datafile '+DATA_01' size 500m;
alter system set undo_tablespace='undotbs02';
alter system set undo_tablespace='UNDOTBS02' scope=SPFILE sid='TSP2';
drop tablespace undotbs2 including contents;
-- Node 3
sqlplus / as sysdba
create undo tablespace undotbs03 datafile '+DATA_01' size 500m;
alter system set undo_tablespace='undotbs03';
alter system set undo_tablespace='UNDOTBS03' scope=SPFILE sid='TSP3';
drop tablespace undotbs3 including contents;

Now the redo log files… Please notice that I’m ommiting a few repetitive steps. No point in filling the same info over and over again.

-- Redo log files
sqlplus / as sysdba
alter system set db_create_online_log_dest_1='+DATA_01' scope=both sid='*';
set pages 200 lines 180
col member format a60
select l.group# , l.bytes , l.status , lf.member
from gv$logfile lf , gv$log l
where lf.group# = l.group#
group by l.group#,l.bytes,l.status,lf.member
order by 1;
-- Based on the select above, add new logfile to the inactive groups and drop old members
alter database add logfile member '+DATA_01' to group 1;
alter database add logfile member '+DATA_01' to group 4;
alter database add logfile member '+DATA_01' to group 6;
alter system switch logfile;
alter database add logfile member '+DATA_01' to group 2;
alter database add logfile member '+DATA_01' to group 3;
alter database add logfile member '+DATA_01' to group 5;
alter system switch logfile;
alter database drop logfile member '+DATA/tsp/onlinelog/group_1.261.906813393';
alter system switch logfile;
alter system checkpoint;
alter database drop logfile member '+DATA/tsp/onlinelog/group_5.267.906813567';
alter system switch logfile;
alter system checkpoint;
alter database drop logfile member '+DATA/tsp/onlinelog/group_6.268.906813567';

Now let’s move the SPFILE in a rolling manner, cool huh?

-- Move SPFILE
create pfile='/tmp/newpfile.ora' from spfile;
create spfile='+DATA_01' from pfile='/tmp/newpfile.ora';
-- Get new path and filename from ASMCMD and update the init.ora
$ sed -i -- "s/+DATA\/TSP\/spfileTSP.ora/+DATA_01\/TSP\/PARAMETERFILE\/spfile.306.920014799/g" $ORACLE_HOME/dbs/initTSP1.ora
$ ssh rac2 "sed -i -- \"s/+DATA\/TSP\/spfileTSP.ora/+DATA_01\/TSP\/PARAMETERFILE\/spfile.306.920014799/g\" $ORACLE_HOME/dbs/initTSP2.ora"
$ ssh rac3 "sed -i -- \"s/+DATA\/TSP\/spfileTSP.ora/+DATA_01\/TSP\/PARAMETERFILE\/spfile.306.920014799/g\" $ORACLE_HOME/dbs/initTSP3.ora"
srvctl modify database -d TSP -p +DATA_01/TSP/PARAMETERFILE/spfile.306.920014799
$ mv $ORACLE_HOME/dbs/spfileTSP1.ora $ORACLE_HOME/dbs/spfileTSP1.bkp
$ ssh rac2 "mv $ORACLE_HOME/dbs/spfileTSP2.ora $ORACLE_HOME/dbs/spfileTSP2.bkp"
$ ssh rac3 "mv $ORACLE_HOME/dbs/spfileTSP3.ora $ORACLE_HOME/dbs/spfileTSP3.bkp"
-- To avoid downtime, bounce each instance one at a time
$ srvctl stop instance -d TSP -i TSP1
$ srvctl start instance -d TSP -i TSP1
$ srvctl stop instance -d TSP -i TSP2
$ srvctl start instance -d TSP -i TSP2
$ srvctl stop instance -d TSP -i TSP3
$ srvctl start instance -d TSP -i TSP3

Now log on as root and go to your GRID_HOME to move the OCR and VD if that’s the case…

-- Move OCR to another disk group (as root, grid home)
# ocrconfig -add +DATA_01
# ocrconfig -delete +DATA
# ocrconfig -manualbackup

Now the voting disk:

-- Move Voting Disk (as root, grid home)
# crsctl query css votedisk
# crsctl replace votedisk +DATA_01

Now let’s do the final step which involves the Control File, System  and Sysaux tablespaces. This has been left to the end because downtime is required.

-- Check current control files
sqlplus / as sysdba
show parameter control_files
-- Change to the new diskgroup
alter system set control_files='+DATA_01' scope=spfile sid='*';
exit
$ srvctl stop database -d TSP
$ srvctl start instance -d TSP -i TSP1 -o NOMOUNT
rman target = /
restore controlfile from '+DATA/tsp/controlfile/current.260.906813391';
exit
$ srvctl stop instance -d TSP -i TSP1
$ srvctl start database -d TSP -o MOUNT
backup as copy datafile 1 format '+DATA_01';
switch datafile 1 to copy;
backup as copy datafile 2 format '+DATA_01';
switch datafile 2 to copy;
exit
srvctl stop database -d TSP
srvctl start database -d TSP

Finishing up… check anything you might have missed and move it 😉

-- Check any remainder parameters to be adjusted
select name,value from v$parameter where value like ('%+DATA%');
-- Adjust the db_create_file_dest to new DG
alter system set db_create_file_dest='+DATA_01' scope=BOTH sid='*';
Advertisements

About Bruno Carvalho

Coffee addicted tech guy.
This entry was posted in ORACLE Database and tagged , , , , , , , , , , , , , . Bookmark the permalink.

2 Responses to Move RAC database from one diskgroup to another in ASM (Manually)

  1. frankyfaust says:

    Great article, man. Keep it up with the good work!

    Best wishes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s