Migrating DB to new storage using RMAN

Let's continue from my earlier post Moving an Oracle Database to New ASM Disk Groups

We will discuss how to move or migrate Oracle database from one storage to new storage with less downtime .
This will be approach 2:

RMAN – SWITCH DATABASE TO COPY

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.

Step 1: Login into database and collect DB info such as  ( DB files, control files , redo log files , temp files,password and spill etc)

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;

show parameter db_recovery_file_dest

show parameter db_create_file_dest

set pages 300 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# ;

srvctl config database -d TESTDB

(example)

Spfile: +DATAC2/TESTDB/PARAMETERFILE/spfile.391.1203429481

Password file: +DATAC2/TESTDB/PASSWORD/pwdTESTDB.265.1203428827

It's better to drop and recreate redo log files with location pointing to new ASM disk group.

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

SQL> alter database drop logfile group 2 >>>>> drop old redo logs that are created at DATAC2 old storage one by one.

Step2:  Take a backup of database into new storage using RMAN.

RMAN> list copy of database;  >>>> This list will probably  be empty if you have never taken a database copy before

RMAN> run

{

allocate channel c1 device type disk format '+DATAC3';

allocate channel c2 device type disk format '+DATAC3';

allocate channel c3 device type disk format '+DATAC3';

allocate channel c4 device type disk format '+DATAC3';

backup as copy database format '+DATAC3';

}

Step3:   Create a pfile , make necessary changes 

example :

*.control_files='+DATAC3/TESTDB/CONTROLFILE/control_01.ctl','+RECOC3/TESTDB/CONTROLFILE/control_02.ctl'

*.db_create_file_dest='+DATAC3'

*.db_recovery_file_dest='+DATAC3

Step4:  shutdown the database , copy control files using asm copy

example :

ASMCMD [+DATAC2/TESTDB/CONTROLFILE] > cp +DATAC2/TESTDB/CONTROLFILE/Current.2883.1203428949 +DATAC3/TESTDB/CONTROLFILE/control_01.ctl

Step4:  switch database to the copy of database created in step#2

SQL> startup mount pfile ='/home/oracle/pfile_TESTDB_move_X11.ora';

RMAN> switch database to copy;

RMAN> recover database;

SQL> alter database open;

Step5:   create  SPFILE, copy password file and modify database configuration using srvctl.

Example:

SQL> create spfile='+DATAC3/TESTDB/PARAMETERFILE/spfileTESTDB.ora' from pfile='/home/oracle/pfile_TESTDB_move_X11.ora';

ASMCMD [+RECOC3/TESTDB] > pwcopy --dbuniquename TESTDB +DATAC2/TESTDB/PASSWORD/pwdTESTDB.265.1203428827 +DATAC3/TESTDB/PASSWORD/pwdbTESTDB.ora -f

copying +DATAC2/TESTDB/PASSWORD/pwdTESTDB.265.1203428827 -> +DATAC3/TESTDB/PASSWORD/pwdbTESTDB.ora

SQL> shut immediate

#srvctl config database -d TESTDB

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

#srvctl config database -d TESTDB

#srvctl start database -d TESTDB

Step 6:   We need to re-create temples to pointing to new disk locations, as RMAN switch to copy will not perform backup and restore of temples to new storage.

SQL> select file_name from dba_temp_files order by 1;

SQL> select distinct tablespace_name from dba_temp_files;

set lines 300 pages 300

col file_name for a80

SQL> create temporary tablespace temp tempfile '+DATAC3' size 1G autoextend on maxsize unlimited;

Step7 :  congrats, you have migrated your database to new storage . Just do a final checks - Run all the queries and commands mentioned in step#1 . Make sure , no DB related files pointing to old storage.