Migrating to ASM

Migrating Oracle database 10g to ASM

This article is for advanced dba's, with assumption that DBA have the enough knowledge to create datagaurd configuration, ASM, and have the knowledge of RMAN.

When the test is carried out at lab, following were the database names used

primary database name 'prod'
standby database name 'stby'


The source database is single instance database, and it has primary and standby databases on filesystem.

This step by step procedure shows how to migrate from non-asm database to asm with minimum downtime.

It is recommended to first migrate the standby database to asm in order to minimize the down time. the only down time is the time taken for switchover.

on the standby database perform the following tasks.

1)stop MRP

if using SQL*Plus to manage DG
sql> alter database recover managed standby database cancel;

if using DGMGRL to manage DG

DGMGRL>edit database stby set state='LOG-APPLY-OFF';

2)perform backup as copy using rman on standby database.

RMAN>backup as copy database format '+DATA' tag 'ASM_Migration';

RMAN>sql 'alter system archive log current';

RMAN>run {
BACKUP AS BACKUPSET SPFILE;
RESTORE SPFILE TO "+DATA/stby/spfilestby.ora";
}


3) shutdown database and start from asm spfile

RMAN>SHUTDOWN IMMEDIATE;

edit the pfile in $OH/dbs to point to the spfile +DATA/stby/spfilestby.ora

cd $ORACLE_HOME/dbs
vi initstby.ora
spfile='+DATA/stby/spfilestby.ora'
:wq!

remove old spfile from dbs

RMAN>STARTUP MOUNT;

4)backup controlfile to file system

RMAN>BACKUP AS COPY CURRENT CONTROLFILE FORMAT ’/disk1/control01_stby.ctl';



5)modify spfile for controlfile location

sql>alter system set control_files='+DATA/stby/control01.ctl','+FRA/stby/control02.ctl' scope=spfile;

SQL> alter system set DB_RECOVERY_FILE_DEST=’+FRA’;

6)switch standby database to ASM

RMAN> shutdown immediate;
RMAN> startup nomount
RMAN> restore controlfile from '/disk1/control01_stby.ctl';
RMAN> alter database mount;
RMAN> switch database to copy;
RMAN > run {
set newname for tempfile 1 to '+DATA';
switch tempfile all;
}

6)Disable logging for Flashback Database, and then re-enable it again to start
creating flashback logs in the new ASM flash recovery area. For example:

SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;

7)For a standby database, resume managed recovery mode:

if using SQL*Plus

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect using current logfile;

if using DGMGRM

DGMGRL> edit database stby set state='ONLINE';

8)Migrating standby logfiles from filesystem to ASM

sql>alter database add standby logfile group 5
'+FRA' size 50m;

sql>alter database add standby logfile group 6
'+FRA' size 50m;

Drop standby logfiles from file system

sql>alter database drop standby logfile group 3;

sql>alter database drop standby logfile group 4;

Note:- online logfiles can only be migrated to asm when the database is primary.

9) Perform the switchover once the standby database is synchronised. then migrate the online logfiles to ASM as follows (this is the only downtime to migrate to ASM).

If using DGMGRL

DGMGRL> switchover to stby;

Then migrate the online logfiles to ASM.

sql>alter database add logfile group 3 '+FRA' size 50m;
sql>alter database add logfile group 4 '+FRA' size 50m;

10)drop the old online logfiles from file system.

sql> alter database drop logfile group 1;
sql> alter database drop logfile group 2;

Now your new primary database is completely on ASM, perform the same steps for your new standby also to migrate from file system to ASM.