Oracle Database Migration from Non-ASM to ASM

Hello friends, in this article we are going to discuss how to do Oracle Database Migration from Non-ASM to ASM filesystem.

Note:- Before doing Oracle Database Migration practical, you must be Configure +ASMinstance on your machine, and check the ASM instance is running or not. The +ASM instance must be running.

Migration Methods

• We have many methods to move databases from normal filesystem to ASM diskgroup like RMAN, DBMS_FILE_TRANSFER, ASMCMD, and 12c online move datafile.
• In this article, I’m going to use the RMAN method.

Prerequisites to Oracle Database Migration

• ASM instance status and
• Disk information

ASM instance status

Check ASM instance is running or not using following command.

SQL> SELECT INSTANCE_NAME, VERSION, DATABASE_STATUS FROM V$INSTANCE;

Disk information

Check ASM disks information using below command.

SQL> SELECT NAME, STATE, TOTAL_MB, PATH FROM V$ASM_DISK;

OR

Connect asmcmd prompt and run lsdg command. lsdg command will show all information related to ASM disks.

$asmcmd
ASMCMD> lsdg

Now check your database name which you want to migrate in ASM filesystem, using following command.

SQL> SELECT NAME FROM V$DATABASE;

NAME
-----
OCP

In my case my Database name is OCP which is i’m going to move on ASM filesystem.

Check Datafiles, Controlfiles and redo logfiles location

Using the below commands to check datafiles, controlfiles and redo logfiles locations.

SQL> SELECT NAME FROM V$DATAFILE;

SQL> SELECT NAME FROM V$CONTROLFILE;
SQL> SELECT MEMBER FROM V$LOGFILE;

Start Oracle Database Migration

Follow the below steps to migrate your database from non asm to asm filesystem.

Change Locations

Now connect with your database and change the locations of CONTROLFILES, DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST parameters.

SQL> ALTER SYSTEM SET control_files=’+DATA’ scope=spfile;

System altered.

SQL> ALTER SYSTEM SET db_create_file_dest=’+DATA’ scope=spfile;

System altered.

SQL> ALTER SYSTEM SET db_recovery_file_dest=’+DATA’ scope=spfile;

System altered.

After above changes shutdown the Database and Startup at NOMOUNT stage use below query’s.

SQL> shu immediate

SQL> startup nomount

Connect RMAN

Now connect with RMAN and copy the controlfile from the old location to the new +ASM filesystem location “+DATA”, using the below query.

$export ORACLE_SID=ocp

$rman target sys/sys@ocp

RMAN> RESTORE CONTROLFILE FROM ‘/u01/app/oracle/oradata/ocp/control01.ctl’;

Connect with your Database and start your Database till mount stage.

$sqlplus sys/sys@ocp as sysdba

SQL> ALTER DATABASE MOUNT;

Check controlfile location.

SQL> SELECT NAME FROM V$CONTROLFILE;

Copy Datafiles

Now copy the Datafiles from old location to new +ASM file system Diskgroup “+DATA” using RMAN.

RMAN> BACKUP AS COPY DATABASE FORMAT ‘+DATA’;

Switch Database

Update Controlfile and Data Dictionary file for the Database.

RMAN> SWITCH DATABASE TO COPY;

Move Tempfile

Now move tempfile on +ASM filesystem diskgroup “+DATA“, using following steps. Check exist location of tempfile.

SQL> SELECT NAME FROM V$TEMPFILE;

Connect RMAN

The below command change the location of tempfile and switch.

$ rman target / 

RMAN> run { 
              set newname for tempfile ‘/u01/app/oracle/oradata/ocp/temp01.dbf’ to ‘+DATA’; 
              switch tempfile all; 
             }

Open Database

Now you can open your database in open mode.

SQL> ALTER DATABASE OPEN;

Now your Oracle Database Migration from Non-ASM to ASM is completed but the redo logfiles still in old locations, Don’t worry you can use your database without it but if you want to move it then see step-7.

Read - How to drop the ASM disk group?

Connect with me on:

Instagram: https://www.instagram.com/shripaldba
Linkedin: https://www.linkedin.com/in/shripal-singh
Twitter: https://twitter.com/ocptechnology
Facebook: https://www.facebook.com/ocptechnology
YouTube:https://www.youtube.com/ocptechnology