Migrate oracle database from Non ASM to ASM

 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 *

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *