Steps to migrate database files from local disk/SAN to ASM :
First create ASM disks and diskgroup – here
Migrating all data (Datafiles, Controlfiles, logfiles) to DATA diskgroup.
Startup mount and restore SPFILE to ASM
SYS @ test > startup mount [[email protected] dbs]$ rman target / Recovery Manager: Release 11.2.0.1.0 - Production on Fri Feb 8 11:15:51 2017 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: TEST (DBID=2104987387, not open) RMAN> RESTORE SPFILE TO '+DATA/spfiletest.ora'; Starting restore at 08-FEB-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=18 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: restoring SPFILE output file name=+DATA/spfiletest.ora channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbs/01o1et80_1_1 channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbs/01o1et80_1_1 tag=TAG20130208T111407 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:16 Finished restore at 08-FEB-17
Copy and switch database files to ASM
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA'; RMAN> SWITCH DATABASE TO COPY;
Switch tempfiles to ASM
RMAN> run
{
set newname for tempfile '/u01/app/oracle/oradata/test/temp01.dbf' to '+DATA';
switch tempfile all;
}
Change default db create file destination to ASM
SQL> alter system set db_create_file_dest='+DATA' scope=both;
Copy controlfile to ASM. DB instance must be in nomount state.
SQL> shut immediate SQL> startup nomount RMAN> restore controlfile to '+DATA' from '/u01/oradata/test/control01.ctl'; Starting restore at 08-FEB-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=171 device type=DISK channel ORA_DISK_1: copied control file copy Finished restore at 08-FEB-17
Locate controlfile on DATA diskgroup and switch controlfile
ASMCMD> find -t controlfile . * WARNING:option 't' is deprecated for 'find' please use 'type' +data/test_pri/CONTROLFILE/current.263.941074201 SQL> alter system set control_files='/u01/oradata/test/control01.ctl', '+DATA/TEST_PRI/CONTROLFILE/current.263.941074201' scope=spfile; System altered
Move logfiles to ASM. We need to create new logfile groups on DATA diskgroup and keep switching logfiles until we drop all the logfiles stored on local disk.
SQL> alter system set db_create_online_log_dest_1='+DATA' scope=spfile; SQL> SHUT IMMEDIATE SQL> STARTUP SQL> alter database add logfile group 3 size 100M; Database altered. SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1; GROUP# STATUS ---------- ---------------- MEMBER -------------------------------------------------------------------------------- 1 INACTIVE /u01/oradata/test/TEST_PRI/onlinelog/o1_mf_1_dgmroohk_.log 2 CURRENT /u01/oradata/test/TEST_PRI/onlinelog/o1_mf_2_dgmrookh_.log 3 UNUSED +DATA/test_pri/onlinelog/group_3.267.941135709 SQL> alter database drop logfile group 1; Database altered. SQL> alter system switch logfile; System altered. SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1; GROUP# STATUS ---------- ---------------- MEMBER -------------------------------------------------------------------------------- 2 ACTIVE /u01/oradata/test/TEST_PRI/onlinelog/o1_mf_2_dgmrookh_.log 3 CURRENT +DATA/test_pri/onlinelog/group_3.267.941135709 SQL> alter system checkpoint; System altered. SQL> alter database add logfile group 1 size 100m; Database altered. SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1; GROUP# STATUS ---------- ---------------- MEMBER -------------------------------------------------------------------------------- 1 UNUSED +DATA/test_pri/onlinelog/group_1.268.941135899 2 INACTIVE /u01/oradata/test/TEST_PRI/onlinelog/o1_mf_2_dgmrookh_.log 3 CURRENT +DATA/test_pri/onlinelog/group_3.267.941135709 SQL> alter database drop logfile group 2; Database altered.
Cleanup old datafiles:
[[email protected] datafile]$ pwd /u01/oradata/test/TEST_PRI/datafile [[email protected] datafile]$ rm -rf *