Migrating non-ASM database to ASM - 11gR2

Requirement: Migrate a database from Non-ASM to ASM.
Environment: Oracle 11gR2 (11.2.0.3) , non-ASM Database: migdb (in Archivelog mode), Diskgroup on ASM: +DGDUP


1) Connect with non-ASM database  and configure the FRA
C:\Users\inam>set oracle_sid=migdb
C:\Users\inam>sqlplus / as sysdba

Disable block change tracking if it is already enabled
SQL> alter database disable block change tracking;
Database altered.

SQL> alter system set db_recovery_file_dest_size=5G;
System altered.

SQL> alter system set db_recovery_file_dest='+DGDUP';
System altered.


2) Migrate data files to ASM's diskgroup
Use RMAN to migrate the data files to ASM disk groups. All data files will be migrated to the ASM disk group +DGDUP

SQL> alter system set db_create_file_dest='+DGDUP';
System altered.

SQL> alter system set control_files='+DGDUP/control01.ctl' scope=spfile;
System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2262048 bytes
Variable Size             620760032 bytes
Database Buffers          440401920 bytes
Redo Buffers                5513216 bytes

C:\Users\inam>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed May 29 13:14:30 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MIGDB (not mounted)

RMAN> restore controlfile from 'D:\app\Inam\oradata\migdb\CONTROL01.CTL';

Starting restore at 29-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DGDUP/control01.ctl
Finished restore at 29-MAY-13

RMAN>

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+DGDUP';

Starting backup at 29-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=190 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\APP\INAM\ORADATA\MIGDB\SYSTEM01.DBF
output file name=+DGDUP/migdb/datafile/system.282.816700675 tag=TAG20130529T131755 RECID=2 STAMP=816700682
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=D:\APP\INAM\ORADATA\MIGDB\SYSAUX01.DBF
output file name=+DGDUP/migdb/datafile/sysaux.262.816700691 tag=TAG20130529T131755 RECID=3 STAMP=816700695
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=D:\APP\INAM\ORADATA\MIGDB\EXAMPLE01.DBF
output file name=+DGDUP/migdb/datafile/example.263.816700699 tag=TAG20130529T131755 RECID=4 STAMP=816700701
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=D:\APP\INAM\ORADATA\MIGDB\UNDOTBS01.DBF
output file name=+DGDUP/migdb/datafile/undotbs1.266.816700705 tag=TAG20130529T131755 RECID=5 STAMP=816700705
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DGDUP/migdb/controlfile/backup.267.816700707 tag=TAG20130529T131755 RECID=6 STAMP=816700707
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=D:\APP\INAM\ORADATA\MIGDB\USERS01.DBF
output file name=+DGDUP/migdb/datafile/users.271.816700709 tag=TAG20130529T131755 RECID=7 STAMP=816700709
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-MAY-13
channel ORA_DISK_1: finished piece 1 at 29-MAY-13
piece handle=+DGDUP/migdb/backupset/2013_05_29/nnsnf0_tag20130529t131755_0.261.816700711 tag=TAG20130529T131755 comment=
NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-MAY-13
3)Verify copy on ASM using asmcmd
C:\Users\inam>set oracle_sid=+ASM2

C:\Users\inam>set oracle_home=D:\app\Inam\product\11.2.0.3\dbhome_1

C:\Users\inam>asmcmd
ASMCMD> cd +dgdup
ASMCMD> ls
ASM/
MIGDB/
control01.ctl
dir1/
ASMCMD> cd migdb
ASMCMD> ls
BACKUPSET/
CONTROLFILE/
DATAFILE/
ASMCMD> cd datafile
ASMCMD> ls
EXAMPLE.263.816700699
SYSAUX.262.816700691
SYSTEM.282.816700675
UNDOTBS1.266.816700705
USERS.271.816700709

4) switch and open database

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DGDUP/migdb/datafile/system.282.816700675"
datafile 2 switched to datafile copy "+DGDUP/migdb/datafile/sysaux.262.816700691"
datafile 3 switched to datafile copy "+DGDUP/migdb/datafile/undotbs1.266.816700705"
datafile 4 switched to datafile copy "+DGDUP/migdb/datafile/users.271.816700709"
datafile 5 switched to datafile copy "+DGDUP/migdb/datafile/example.263.816700699"

RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.

5) Veify using SQL Plus
C:\Users\inam>set oracle_sid=migdb
C:\Users\inam>sqlplus / as sysdba
SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------
USERS                          +DGDUP/migdb/datafile/users.271.816700709
UNDOTBS1                       +DGDUP/migdb/datafile/undotbs1.266.816700705
SYSAUX                         +DGDUP/migdb/datafile/sysaux.262.816700691
SYSTEM                         +DGDUP/migdb/datafile/system.282.816700675
EXAMPLE                        +DGDUP/migdb/datafile/example.263.816700699

SQL> select name from v$controlfile;

NAME
---------------------------------------
+DGDUP/control01.ctl

6) Migrate temp tablespace to ASM.
SQL> select name from v$tempfile;

NAME
--------------------------------------
D:\APP\INAM\ORADATA\MIGDB\TEMP01.DBF

SQL> alter tablespace temp add tempfile size 200m;
Tablespace altered.
SQL> select name from v$tempfile;

NAME
-----------------------------------------
D:\APP\INAM\ORADATA\MIGDB\TEMP01.DBF
+DGDUP/migdb/tempfile/temp.270.816701445

-- Drop old tempfile from temp tablespace
SQL> alter tablespace temp drop tempfile 'D:\APP\INAM\ORADATA\MIGDB\TEMP01.DBF';
Tablespace altered.

7)  Migrate redo logs to ASM.

SQL> select group#,member from v$logfile;

    GROUP# MEMBER
---------- --------------------------------------
         3 D:\APP\INAM\ORADATA\MIGDB\REDO03.LOG
         2 D:\APP\INAM\ORADATA\MIGDB\REDO02.LOG
         1 D:\APP\INAM\ORADATA\MIGDB\REDO01.LOG

SQL> alter database add logfile group 4 size 50m;
Database altered.

SQL> alter database add logfile group 5 size 50m;
Database altered.

SQL> alter database add logfile group 6 size 50m;
Database altered.

SQL> select group#,member from v$logfile;
    GROUP# MEMBER
---------- ----------------------------------------------
         3 D:\APP\INAM\ORADATA\MIGDB\REDO03.LOG
         2 D:\APP\INAM\ORADATA\MIGDB\REDO02.LOG
         1 D:\APP\INAM\ORADATA\MIGDB\REDO01.LOG
         4 +DGDUP/migdb/onlinelog/group_4.264.816701905
         4 +DGDUP/migdb/onlinelog/group_4.272.816701905
         5 +DGDUP/migdb/onlinelog/group_5.268.816701905
         5 +DGDUP/migdb/onlinelog/group_5.280.816701907
         6 +DGDUP/migdb/onlinelog/group_6.276.816701907
         6 +DGDUP/migdb/onlinelog/group_6.284.816701907

9 rows selected.

SQL> alter system switch logfile;
System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;
Database altered.

8) Migrate spfile to ASM:

 
SQL> show parameter spf

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      D:\APP\INAM\PRODUCT\11.2.0.3\D
                                                 BHOME_1\DATABASE\SPFILEMIGDB.O
                                                 RA
If the database is using an SPFILE already, then run these commands:

run {
BACKUP AS BACKUPSET SPFILE;
RESTORE SPFILE TO "+DGDUP/spfileMIGDB";
}

C:\Users\inam>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Wed May 29 13:50:13 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MIGDB (DBID=1021525776)

RMAN> run {
2> BACKUP AS BACKUPSET SPFILE;
3> RESTORE SPFILE TO "+DGDUP/spfileMIGDB";
4> }

Starting backup at 29-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-MAY-13
channel ORA_DISK_1: finished piece 1 at 29-MAY-13
piece handle=+DGDUP/migdb/backupset/2013_05_29/nnsnf0_tag20130529t135025_0.274.816702625 tag=TAG20130529T135025 comment=
NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-MAY-13

Starting restore at 29-MAY-13
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DGDUP/spfileMIGDB
channel ORA_DISK_1: reading from backup piece +DGDUP/migdb/backupset/2013_05_29/nnsnf0_tag20130529t135025_0.274.81670262
5
channel ORA_DISK_1: piece handle=+DGDUP/migdb/backupset/2013_05_29/nnsnf0_tag20130529t135025_0.274.816702625 tag=TAG2013
0529T135025
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-MAY-13

RMAN>

If you are not using an SPFILE, then use CREATE SPFILE from SQL*Plus to create the new SPFILE in ASM.

SQL> create spfile='+DGDUP/spfileMIGDB' from pfile='D:\app\Inam\product\11.2.0.3\dbhome_1\initMIGDB.ora';



9) Create/modify parameter file in D:\app\Inam\product\11.2.0.3\dbhome_1\initMIGDB.ora
and set the parameter below. Don't forget to remove/rename already existing pfile in the same folder.
 
 SPFILE=+DGDUP/spfileMIGDB

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2262048 bytes
Variable Size             624954336 bytes
Database Buffers          436207616 bytes
Redo Buffers                5513216 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter spf

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
spfile                               string      +DGDUP/spfilemigdb

10) Add additional control file for redundancy



SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2262048 bytes
Variable Size 624954336 bytes
Database Buffers 436207616 bytes
Redo Buffers 5513216 bytes
Database mounted.
SQL> alter database backup controlfile to '+DGDUP/control02.ctl';Database altered.

OR

using asmcmd copy the control file


ASMCMD> cp control01.ctl control02.ctl
copying +dgdup/control01.ctl -> +dgdup/control02.ctl

SQL> alter system set control_files='+DGDUP/control01.ctl','+DGDUP/control02.ctl' scope=spfile;
System altered.
SQL>

 SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2262048 bytes
Variable Size             624954336 bytes
Database Buffers          436207616 bytes
Redo Buffers                5513216 bytes
Database mounted.
Database opened.
SQL>