Migrating 11gR2 Database to and from ASM

In this post i am migrating Oracle 11gR2 database from ASM to NON-ASM and NON-ASM to ASM storage.

NON-ASM to ASM
==============
Steps to migrate Non-ASM database and fast recovery area to ASM database using RMAN.
1) Backup database,spfile and disable flashback database
1.1) Lets assume we have two disk groups +ORADATA for database and +ORAFRA for fast recovery area.
1.2) Copy the spfile cp spfiletest.ora spfiletest.ora.org
1.3) start RMAN session in new window to the database to be migrated
rman target / nocatalog
1.4) Backup data files to Oracle ASM diskgroup (+ORADATA)
rman> RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
BACKUP AS COPY
INCREMENTAL LEVEL 0
DATABASE
FORMAT '+ORADATA'
TAG 'ASM_MIGRATION';
}
1.5) Check if block change tracking is enabled.
sql> select filename, status, bytes from v$block_change_tracking
if it is enabled then disable it.
rman> sql "alter database disable block change tracking";
1.6) archive the online logs if the database is in archivelog mode
rman> sql "ALTER SYSTEM ARCHIVE LOG CURRENT";
1.7) Backup spfile
rman> backup as backupset spfile;
1.8) check if flash back database is enabled
sql> select flashback_on from v$database;
if it is enabled then disabled it
rman> sql "alter database flashback off";
rman> shutdown immediate
2) Restore files to oracle ASM and recover database and migrate fast recovery area.
2.1) restore spfile to ASM disk group
rman> startup mount;
rman> restore spfile to '+ORADATA/spfiletest.ora';
rman> shutdown immediate
2.2) set oracle managed files to Oracle ASM locations
sql> startup nomount;
sql> alter system set db_create_file_dest='+ORADATA' sid='*';
sql> alter system set db_recovery_file_dest_size=10g sid='*';
sql> alter system set db_recovery_file_dest='+ORAFRA' sid='*';
sql> shutdown immediate
2.3) set the control_files location to ASM location
sql> startup nomount;
sql> alter system set control_files='+ORADATA','+ORAFRA' scope=spfile sid='*';
sql> Shutdown immediate;
rman> startup nomount
rman> restore contolfile from '/apps/oracle/oradata/test/control01.ctl';
rman> alter database mount;
2.4) Migrate data files to Oracle ASM
rman> switch database to copy;
rman>
run
{
allocate channel dev1 device type disk;
allocate channel dev2 device type disk;
allocate channel dev3 device type disk;
allocate channel dev4 device type disk;
recover database;
}
2.5) Enable block changes or flashback database if it was enabled.
sql> alter database enable block change tracking using file '+ORADATA';
sql> alter database flashback on;
2.6) open database
sql> alter database open;
2.5) Move Redologs to ASM.use below procedure
SET SERVEROUTPUT ON;
DECLARE
CURSOR rlc IS
SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL
FROM V$LOG
UNION
SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL
FROM V$STANDBY_LOG
ORDER BY 1;
stmt VARCHAR2(2048);
BEGIN
FOR rlcRec IN rlc LOOP
IF (rlcRec.srl = 'YES') THEN
stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;
EXECUTE IMMEDIATE stmt;
ELSE
stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
BEGIN
stmt := 'ALTER DATABASE DROP LOGFILE GROUP ' || rlcRec.grp;
DBMS_OUTPUT.PUT_LINE(stmt);
EXECUTE IMMEDIATE stmt;
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';
EXECUTE IMMEDIATE stmt;
END;
END IF;
END LOOP;
END;
/

ASM to Non-ASM
==============
To be Continued ...... ut quick hint in the above procedure reverse the order.