Moving an Oracle Database to New ASM Disk Groups

 Let’s consider a common DBA requirement: moving an Oracle database to new ASM disk groups.

Scenario: 
Database name: TESTDB
Current data disk group: +DATAC2
Current recovery disk group: +RECOC2
Target data disk group: +DATAC3
Target recovery disk group: +RECOC3

The objective is to move all database files from the existing ASM disk groups to the new ones with minimal risk and controlled execution.

There are two supported approaches to achieve this:

1. ALTER DATABASE MOVE DATAFILE
2. RMAN – SWITCH DATABASE TO COPY

Lets explore approach 1 first - ALTER DATABASE MOVE DATAFILE

Step 1: Collect Database File Information

SET LINES 300 PAGES 300

COL name FORMAT a100

SELECT name FROM v$controlfile;

SELECT file#, name FROM v$datafile;

SELECT file#, name FROM v$tempfile;

srvctl config database -d TESTDB ---> to find location of spfile and password file.

Step2: Generate data files movement SQLs and execute ( downtime preferred to avoid issues)

SELECT 'ALTER DATABASE MOVE DATAFILE ''' || name || ''' TO ''+DATAC3'';' FROM v$datafile;

Move control file and spfile 

SQL> create pfile='/home/oracle/pfile_TESTDB.ora' from spfile;

SQL> alter system set control_files='+DATAC3/TESTDB/CONTROLFILE/controlfile_01.ctl' scope=spfile sid='*';

SQL> shut immediate;

SQL>startup nomount

SQL> show parameter control_file

RMAN> restore controlfile from '+DATAC2/TESTDB/CONTROLFILE/current.268.1202816633';

RMAN> alter database mount;

alter database mount;

RMAN> alter database open;

alter database open;

SQL> alter system set db_create_file_dest='+DATAC3' scope=both sid='*';

SQL> alter system set db_create_online_log_dest_1='+DATAC3' scope=both sid='*';

Step3: Move online redo log files and temp files to new ASM DG 

Get information regarding redo logs 

set lines 300

column REDOLOG_FILE_NAME format a60

SELECT

 a.GROUP#,

 a.THREAD#,

 a.SEQUENCE#,

 a.ARCHIVED,

 a.STATUS,

 b.MEMBER AS REDOLOG_FILE_NAME,

 (a.BYTES/1024/1024) AS SIZE_MB

FROM v$log a

JOIN v$logfile b ON a.Group#=b.Group#

ORDER BY a.GROUP# ;

Online redo logs cannot be moved directly. The recommended approach is to add new redo log groups on the target ASM disk groups and then drop the old ones.

SQL> alter database add logfile thread 1 group 11 ('+DATAC3','+RECOC3') size 512M;

SQL> alter database drop logfile group 1;

Moving temporary files requires special handling, as tempfiles cannot be moved directly. The only supported approach is to drop and recreate the temporary tablespaces.

This will show you the current tempfiles and their associated temporary tablespaces

SQL>select file_name from dba_temp_files order by 1

SQL> select distinct tablespace_name from dba_temp_files;

The default temporary tablespace cannot be dropped directly. To proceed, you must first create an alternate temporary tablespace (for example, TEMP2) and set it as the default.

SQL>CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '+DATAC3' SIZE 15G;

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

SQL> drop tablespace TEMP including contents and datafiles;

SQL> create temporary tablespace temp tempfile '+DATAC3' size 15G;

SQL> drop tablespace TEMP2 including contents and datafiles;

Step4: lastly , don't forget to move SPFILE and password file . You have already found the location of spfile and password file earlier  using - srvctl config database -d TESTDB

ASMCMD > pwcopy --dbuniquename TESTDB +DATAC2/TESTDB /PASSWORD/pwdbipuat.293.1201771211 +DATAC3/TESTDB /PASSWORD/pwdbTESTDB.ora -f

You need to update database config as  below and verify.

#srvctl modify database -d TESTDB -spfile +DATAC3/TESTDB /PARAMETERFILE/spfileTESTDB.ora -pwfile +DATAC3/TESTDB /PASSWORD/pwdbTESTDB.ora