You can use these steps to migrate a NON-ASM
database to ASM database in Oracle 11gR2.
Prerequisite - ASM instance should be created and up and running.
1) Check the database version and status
SQL> select INSTANCE_NAME,VERSION,DATABASE_STATUS from v$instance;
INSTANCE_NAME VERSION DATABASE_STATUS
---------------- ----------------- -----------------
learndb 11.2.0.3.0 ACTIVE
2) We should be able to check the ASM disk available from the instance which we created earlier
SQL> select NAME,STATE,TOTAL_MB,PATH from v$asm_disk;
NAME STATE TOTAL_MB PATH
------------------------------ -------- ---------- ----------
DISK1 NORMAL 4777 ORCL:DISK1
Migrating the SPFILE from Non-asm to ASM
1) If you are using a spfile, take a backup of spfile using rman to restore it to ASM disk
RMAN>BACKUP AS BACKUPSET SPFILE;
2) Now shutdown the database and start it in Mount stage
SQL> Shutdown immediate
SQL> STARTUP MOUNT;
3) Now restore the spfile from the backup to ASM disk as below
RMAN>RESTORE SPFILE TO '+DATA/spfilelearndb.ora';
4) You can see, still the parameter "spfile" is pointing to file system instead of ASM disk
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /opt/oracle/product/11.2.0/dbh
ome_1/dbs/spfilelearndb.ora
5) Shutdowm the instance
SQL> shutdown immediate
6) Delete "spfile<sid>.ora and init<sid>.ora from the $ORACLE_HOME/dbs directory and create a new init<sid>.ora with the following line of content and start the instance,
vi initlearndb.ora
SPFILE='+DATA/spfilelearndb.ora'
SQL> Startup ( first it will search for spfile<sid>.ora which we deleted and next it will look for init<sid>.ora which we have moified with the above content )
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/spfilelearndb.ora
Migrating Control files from Non-ASM to ASM
1) These are the current control files in non-asm
SQL>show parameter contol_files
control_files string /u01/data/learndb/control01.ctl,
/u01/data/learndb/control02.ctl
2) Start the instance in nomount state
SQL> startup nomount
3) You can move these control files to ASM using RMAN
[oracle@coltdb04 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:04:39 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: LEARNDB (not mounted)
RMAN>restore controlfile to '+DATA' from '/u01/data/learndb/control01.ctl';
4) You can check whether the control file are created
ASMCMD> find -t CONTROLFILE +DATA *
WARNING:option 't' is deprecated for 'find'
please use 'type'
+DATA/LEARNDB/CONTROLFILE/current.256.787200593
5) Update the spfile with new control file location
SQL> alter system set control_files='+DATA/LEARNDB/CONTROLFILE/current.256.787200593' scope=spfile;
6) Now shutdown the instance and startup in mount stage and check for the control file location
SQL> shutdown immediate
SQL> startup mount
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string +DATA/learndb/controlfile/curr
ent.256.787200593
Migrating the Datafiles from Non-ASM to ASM
1) Keep the database is in mount stage
2) Use RMAN "BACKUP AS COPY" method to migrate the datafiles from filesystem to ASM
[oracle@coltdb04 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 29 03:00:40 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: LEARNDB (DBID=3704858930, not open)
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';
3) Switch the database to the copy created using the following RMAN command
RMAN> SWITCH DATABASE TO COPY;
datafile 1 switched to datafile copy "+DATA/learndb/datafile/system.258.787201633"
datafile 2 switched to datafile copy "+DATA/learndb/datafile/sysaux.257.787201553"
datafile 3 switched to datafile copy "+DATA/learndb/datafile/undotbs1.259.787201713"
datafile 4 switched to datafile copy "+DATA/learndb/datafile/users.261.787201725"
4) Migrate the tempfile to ASM using RMAN
RMAN> run
2> {
3> set newname for tempfile '/u01/data/learndb/temp01.dbf' to '+DATA';
4> switch tempfile all;
5> }
executing command: SET NEWNAME
using target database control file instead of recovery catalog
renamed tempfile 1 to +DATA in control file
5) Now open the Database
RMAN> ALTER DATABASE OPEN;
database opened
6) You can now check the datafiles created in ASM
SQL> select FILE_NAME from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/learndb/datafile/users.261.787201725
+DATA/learndb/datafile/undotbs1.259.787201713
+DATA/learndb/datafile/sysaux.257.787201553
+DATA/learndb/datafile/system.258.787201633
Migrating the Redo log files from Non-ASM to ASM
1) Identify the currently available redo log files using the following command
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
GROUP# MEMBER STATUS
---------- --------- -----------
3 /u01/data/learndb/redo03.log INACTIVE
2 /u01/data/learndb/redo02.log CURRENT
1 /u01/data/learndb/redo01.log INACTIVE
2) Add the new logfiles to ASM using following command
ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 3;
3) Now drop the old logfiles, A redo log member can only be dropped after being archived and being in INACTIVE mode. If needed, switch logfile multiple times until the logfile is ready for dropping.
ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo01.log';
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo02.log';
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE MEMBER '/u01/data/learndb/redo03.log';
4) You can check the logfiles created now in ASM
GROUP# MEMBER STATUS
---------- --------- -----------
1 +DATA/learndb/onlinelog/group_1.264.787205393 CURRENT
2 +DATA/learndb/onlinelog/group_2.265.787205405 INACTIVE
3 +DATA/learndb/onlinelog/group_3.266.787205417 INACTIVE
Now your database is migrated to ASM
I hope this article helped you. Your suggestions/feedbacks are most welcome.
Keep learning... Have a great day!!!
Keep learning... Have a great day!!!
