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

This is a post about migrating a database from NON ASM type to ASM type,it includes the screenshots also


Scenario:-suppose you have installed ASM and now you want to convert a non ASM type database into ASM type database,to do this task follow the following steps,exact screenshots are also provided for a better result


Step 1)_ON DATABASE INSTANCE PROMPT-disable block change tracking if it was enabled

Step 2)_ON DATABASE INSTANCE PROMPT-create a pfile from the spfile,this file would be created in the ORACLE_HOME\database location for windows and ORACLE_HOME/dbs location for linux

Step 3)_ON DATABASE INSTANCE PROMPT-make the following changes in the pfile which you have already created,make changes for the location of controlfile,db_create_file_dest and db_create_online_log_dest_1 parameter and make them to prompt towards the address of the ASM Diskgroup

Step 4)_ON DATABASE INSTANCE PROMPT-shutdown and startup teh database in nomount mode using newly changed pfile

Step 5)_ON RMAN PROMPT-restore the controlfile from the old control file(the controlfile of the NON ASM database)

Step 6)_ ON RMAN PROMPT -mount the database

Step 7)_ON RMAN PROMPT -backup the NON ASM database as the copy  into the diskgroup of yours,this will copy the database files into the ASM diskgroup

Step 8)_ON RMAN PROMPT-Switch all datafiles into new ASM disk group,this will rename the database files according to the diskgroup location and address

Step 12)_ ON RMAN PROMPT-open the database

Your database conversion from a NON ASM database into an ASM database have been completed

NOTE-To confirm the conversion crosscheck with the paths of the database files like controlfile and datafiles

Script which will help in converting an ASM database into a NON ASM database


whenever you want to convert an ASM databse into a NON ASM database then you require the path and location address of the database files,to perform the conversion you simply have to run this script and save the output and rerun the output in the RMAN prompt,the script is as follows

NOTE-Run the script from the DATABASE INSTANCE PROMPT


set serveroutput on;
declare
    cursor df is select file#, name from v$datafile;
begin
    dbms_output.put_line('run');
    dbms_output.put_line('{');
    for dfrec in df loop
        dbms_output.put_line('set newname for datafile ' ||
            dfrec.file# || ' to ''' || dfrec.name ||''' ;');
    end loop;
    dbms_output.put_line('restore database;');
    dbms_output.put_line('switch all;');
    dbms_output.put_line('}');
end;

The output of the can be seen in the screenshot attached,you can then save and rerun the output in RMAN prompt to convert an ASM database into a NON ASM database

Recovery of a database using RMAN from controlfile loss (controlfile autobackup setting not enabled)


This is the case when you have not enabled the controlfile autobackup setting in the RMAN setting,follow the steps to recover your database from such a situation


Step 1)_shutdown the database with immediate option

Step 2)_connect to RMAN and nomount the DB

Step 3)_restore controlfile from backup piece,restore the controlfile by giving the path location of the backup piece where the controlfile gets backed up,you can get this location by reading the log file for the RMAN backup

Step 4)_mount the  DB using alter database mount command

Step 5)_restore DB to bring datafiles to current state

Step 6)_recover the database

Step 7)_alter database open resetlogs to open the db

You have now successfully recovered from controlfile loss (controlfile autobackup setting not enabled) situation using RMAN

NOTE-Take full backup after opening the DB

Recovery of a database using RMAN from controlfile loss


This is the case when you have enabled the controlfile autobackup setting ON in the RMAN setting,follow the steps to recover your database from such a situation

Step 1)_shutdown the database immediate

Step 2)_start the db in nomount state

Step 3)_connect to the rman and restore controlfile from autobackup

Step 4)_mount the db

Step 5)_restore the database to apply all the latest changes

Step 6)_recover the database

Step 7)_alter database open resetlogs to open the db

NOTE-perform full database backup immediatly after opening the db

steps for creating a controlfile


follow these simple steps to create a new controlfile

Step 1)_backingup the controlfile to trace this will create a readable format of the controlfile

Step 2)_shutdown the database

Step 3)_make necesarry changes for the new location where you want to copy your database files

Step 4)_open the traced controlfile and get the script for creation of new controlfile,copy the lines marked in the red box

Step 5)_make necessary changes in the script which you get from the traced controlfile,modify the locations of all the files to the new location where you copied your database files

Step 6)_start the database in nomount state

Step 7)_create the controlfile using the script you get from traced controlfile copy and paste the scripts from Step 5

step 8)_now open the database using alter database open command

NOTE-if you deleted the previous controlfiles then remove the reuse clause from the craete controlfile statement in step 5 or step 7

RMAN Recovery in case of non backed up archive log file missing


Follow these steps if those of your archive log files are missing from the server which are not being backed up

Step 1)-Shutdown the database

Step 2)-Startup the database in nomount mode

Step 3)-create a new controlfile(follow my doc for creating the new controlfile)

Step 4)-alter database open resetlogs to reset the logs and open the db