In this example we migrate all data from ORADATA1 to ORADATA2 then drop ORADATA1.

Create New Diskgroup

As a user (typically 'grid' or 'asm') with ASM environment set:

sqlplus / as sysasm

ALTER SYSTEM SET ASM_DISKSTRING = '/dev/rhdisk2*' , '/dev/asm*' SCOPE=BOTH;

CREATE DISKGROUP oradata2 EXTERNAL REDUNDANCY DISK '/dev/asm69','/dev/asm70','/dev/asm103','/dev/asm104','/dev/asm105';

As 'oracle' with Database environment set:

sqlplus / as sysdba

ALTER SYSTEM SET db_create_file_dest = '+ORADATA2' SCOPE=BOTH;

Migrate TEMP

Create new temporary tablespace:

As 'oracle' with Database environment set:

sqlplus / as sysdba

CREATE TEMPORARY TABLESPACE temp2;

SELECT property_value

FROM database_properties

WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

Make the new tablespace the default temporary tablespace for the database:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

SELECT property_value

FROM database_properties

WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

Check no users still have the old tablespace as their default temporary tablespace:

SELECT username, temporary_tablespace

FROM dba_users;

Check whether any sessions still have active segments in the old temporary tablespace.

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,

a.username, a.osuser, a.status

FROM v$session a,v$sort_usage b

WHERE a.saddr = b.session_addr

ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

Wait for all sessions using segments in TEMP to terminate (or kill them, if you're in a hurry)

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Migrate Datafiles

rman target / catalog=rcat/******@RCAT

report schema;

Use 'report schema' to retrieve the datafile File numbers (for use in the command below) and for reviewing your progress

sql 'alter database datafile 9 offline';

backup as copy datafile 9 format '+ORADATA2';

switch datafile '+ORADATA1/oratest/datafile/myts_1.267.608589927' to COPY;

report schema;

recover datafile '+ORADATA2/oratest/datafile/newdatafilename';

sql 'alter database datafile 9 online';

Repeat until all datafiles are migrated

First, add group members on new diskgroup:

ALTER DATABASE ADD LOGFILE MEMBER '+ORADATA2' TO GROUP 4;

ALTER DATABASE ADD LOGFILE MEMBER '+ORADATA2' TO GROUP 5;

ALTER DATABASE ADD LOGFILE MEMBER '+ORADATA2' TO GROUP 6;

Clear 'INVALID' status by switching logfile to initialize new members:

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM SWITCH LOGFILE;

Remove logfiles from +ORADATA1:

ALTER DATABASE DROP LOGFILE MEMBER '+ORADATA1/oratest/onlinelog/group_5.275.612200669';

ALTER DATABASE DROP LOGFILE MEMBER '+ORADATA1/oratest/onlinelog/group_6.276.612200753';

ALTER DATABASE DROP LOGFILE MEMBER '+ORADATA1/oratest/onlinelog/group_4.274.612200571';

Migrate Controlfile

Connect to the 'oracle' user with the regular database environment set.
Shutdown the database, then startup with 'nomount' option.

sqlplus / as sysdba

shutdown immediate

startup nomount

show parameter control_files

Using Recovery Manager, restore controlfile to new location from old location:

rman target / catalog rcat/******@RCAT

restore controlfile to '+ORADATA2' from '+ORADATA1/oratest/controlfile/currentfilename';

In another session as a user (typically 'grid' or 'asm') with ASM environment set:

asmcmd

cd +ORADATA2

find -t controlfile . *

Back in the original 'oracle' environment:

sqlplus / as sysdba

ALTER SYSTEM SET control_files='+ORADATA2/oratest/controlfile/newfilename',

'+ORARECO1/oratest/controlfile/currentfilename' SCOPE=spfile;

shutdown immediate

startup

Drop Diskgroup

As a user (typically 'grid' or 'asm') with ASM environment set:

asmcmd

cd +ORADATA1

ls

cd oratest

ls

cd controlfile

ls

sqlplus / as sysasm

DROP DISKGROUP oradata1 INCLUDING CONTENTS;

Bibliography