Skip to main content
Oracle ASM

Convert a Non ASM database to ASM database in Oracle 11gR2

By October 26, 2014September 12th, 2016No Comments


1. Install the ASM Binaries.

2. Configure the ASM and create 2 Diskgroups namely DATAGRP1 and FRAGRP1. Assign the respective disks to the groups.

3. Start the ASM instance and keep it up.

4. Now you will be logged in as grid user. Change the user to Oracle.

5. Create a pfile from the spfile just for a safety purpose.

create pfile=<location/init.ora> from spfile;

6. Since we are converting the instance from Non-ASM to ASM, we need to change the location of the controlfile, db_create_file_dest, db_create_online_log_dest_1 etc to the ASM location.

Set the below parameters.,

alter system set control_files=’+DATAGRP1’ scope=spfile;

alter system set db_create_file_dest=’+DATAGRP1’ scope=spfile;

alter system set db_recovery_file_dest=’+FRAGRP1’ scope=spfile;

alter system set db_create_online_log_dest_1=’+DATAGRP1’ scope=spfile;

alter system set db_create_online_log_dest_2=’+FRAGRP1’ scope=spfile;

7. Since the changes has been done to the spfile, you can take one more backup of the pfile.

8. Shutdown the database.

shutdown immediate;

9. Startup the database in nomount.

startup nomount;

10. Now connect to the RMAN,

a. If you use a seperate database for the rman catalog then use the below command.,

rman target / catalog rman/*****@catalogdb

b. If the RMAN is maintained in the same database,

rman target / 

11. Restore the controlfile to the new location.

restore controlfile from ‘<name of the controlfile with location>’;

Egs: RMAN > restore controlfile from ‘/vol1/TESTDB/control01.ctl’;

12. Mount the database now. (you can mount it from the RMAN prompt itself).

alter database mount;

13. Backup the database to the new location. i.e., ASM diskgroup location.

run {

allocate channel c1 device type disk;

allocate channel c2 device type disk;

allocate channel c3 device type disk;

Backup as copy database format ‘+DATAGRP1’;   

release channel c1;

release channel c2;

release channel c3;


14. Now the switch the database using the switch command as below.,

switch database to copy;

A backup copy is taken in the step 13. Now this switch command in RMAN will switch the entire database to the backup copy taken in to the Diskgroup ‘+DATAGRP1’.

15. After the switch, the database will be in a inconsistent state. So we need to recover the database. In the SQL prompt connect as sysdba and issue the below command.

recover database using backup controlfile until cancel;

16. Now recover if it prompts for any archivelogs or redo logs. Else cancel the recover using cancel command.

17. Open the database with the resetlogs option.

alter database open resetlogs;

18. Drop the old temp tablespace and recreate it in the new location (ASM Location).

a. You can drop the tablespace as a whole.

drop tablespace TEMP including contents and datafiles;

b. You can also add a new tempfile to the new ASM location and remove the old one.

alter tablespace TEMP add tempfile ‘+DATAGRP1’ size 2048M autoextend on next 5M maxsize unlimited;

alter database tempfile ‘<tempfile name with location on the filesystem>’ drop including datafiles;

c. Recreate the temp tablespace.

create temporary tablespace TEMP tempfile ‘+DATAGRP1’ size 2048M autoextend on next 5M maxsize unlimited;

alter tablespace TEMP add tempfile ‘+DATAGRP1’ size 2048M autoextend on next 5M maxsize unlimited;

19. Recreate the redo log group on ASM Diskgroup. Use the below command to list out the current Redo log group used in the database.

select, a.member, b.bytes from v$logfile a, v$log b where;

select group#,status from v$log;

For example if you have 3 Redo log groups namely,

select group#,status from v$log;

 GROUP#         STATUS

    1                   ACTIVE

    2                   ACTIVE

    3                  CURRENT

Drop it one by one. First drop the first group, create it and then move to the second one. Before recreating the redo log group issue the below command to avoid (ORA-01624: log 1 needed for crash recovery of instance) 

alter system checkpoint global;

a. Drop and recreate the group 1.

alter database drop logfile group 2;

alter database add logfile group 1 size 100M;

b. Drop and recreate the group2.

alter database drop logfile group 2;

alter database add logfile group 2 size 100M;

c. Drop and recreate the group 3.

alter database drop logfile group 3;

alter database add logfile group 3 size 100M;

Leave a Reply

© 2020 ORACLE-SCN. All Rights Reserved.