Converting an ASM database instance into NON ASM database instance


If you have a database having ASM instance and you want to convert it back to NON ASM instance then follow these simple steps,the actual screenshots are also attached for a better result

Step 1)_ON RMAN PROMPT-backup the database to a location on disk (not in the ASM diskgroup),this will help in converting the database files back into NON ASM instance

Step 2)_ON DATABASE INSTANCE PROMPT-after the completion of backup,disable the block change tracking if its enabled 

Step 3)_ON DATABASE INSTANCE PROMPT-create a pfile before shutdown the database using immediate option

Step 4)_ON ASM INSTANCE PROMPT-please before performing this step make sure that your database have been backed up successfully then drop the diskgroup,you require to include the contents clause also as the diskgroup contains data at this stage

Step 5)_ON DATABASE INSTANCE PROMPT-change the required settings in the pfile,change for the location of  controlfile,db_recovery_file_dest,log_archive_dest_1,diagnostic_dest locations

Step 6)_ON DATABASE INSTANCE PROMPT-startup the database in nomount state using the newly created pfile

Step 7)_ON RMAN PROMPT-restore the controlfile from the backup files,give the path location of the file which backedup the controlfile,you can get this information by looking at the log file generated at the time of backup

Step 8)_ON RMAN PROMPT-mount the database after restoring the controlfile

Step 9)_ON RMAN PROMPT-set the newname for the datafiles and make them  point towards any location of your choice on the local disk and restore the database and then switch the datafiles,this will copy all the files in the mentioned location.

Step 10)_ON RMAN PROMPT-recover the database

Step 11)_ON RMAN PROMPT-open the database using resetlogs option

You have now successfully converted a ASM database instance into a NON ASM instance

NOTE:-for further checking crosscheck the newly converted database iinstance for the new location for all the database files

ON DATABASE INSTANCE PROMPT-check for the file names they must be showing to a NON ASM disk drive