Non ASM to ASM migration steps for Standlone database

prerequisite step:

1. Need to install and configure the GRID on the standalone server.

2. First check the locations of Parameter file, Control file, Data file, Temp file and Redo Log files. This is important piece of information as we move database files to ASM.

show parameter pfile;
select name from v$controlfile;
select name from v$datafile;
select name from v$tempfile;
select member from v$logfile;

3.Take backup of spfile using RMAN and restore it to ASM disk group.

rman target /

backup as backupset spfile;

restore spfile to '+DATA/spfileoradb.ora';

4.Rename old spfile as it no longer required

mv $ORACLE_HOME/dbs/spfileoradb.ora $ORACLE_HOME/dbs/spfileoradb.ora_old

5.Create new pfile which points to the spfile on ASM

vi $ORACLE_HOME/dbs/initoradb.ora

spfile='+DATA/spfileoradb.ora'

6.Bounce the database and check if it’s showing new location for spfile

shut immediate

startup nomount;

show Parameter pfile;

7. As our database is already in no-mount mode, we shall connect to RMAN to copy controlfiles from filesystem to ASM

rman target/

restore controlfile to '+DATA' from '/u01/app/oracle/oradata/oradb/control01.ctl';

restore controlfile to '+RECV' from '/u01/app/oracle/FRA/oradb/control02.ctl';

8.Find new controlfiles locations from asmcmd and update inside database

alter system set control_files='+DATA/CDB1/CONTROL/control01.ctl','+RECV/CDB1/CONTROL/control02.ctl' scope=spfile;

9.Bounce the database and put in mount mode. Check if database is showing new location for control files

shut immediate;

startup mount;

show parameter control_files

Controlfile migration to ASM done!

10.Let us move datafiles from filesystem to ASM. As our database is in mount mode, we can go ahead and use RMAN to copy datafiles to ASM

rman target/

backup as copy database format '+DATA';

11. witch the database to the data files on ASM

switch database to copy;

12. Migrate Temp File to ASM

Use below run code to migrate temp file to ASM

RMAN>run

{

set newname for tempfile '/u01/app/oracle/oradata/oradb/temp01.dbf' to '+DATA';

switch tempfile all;

}

Open the database and check new locations for datafiles and tempfile

alter database open;

select name from v$datafile;

select name from v$tempfile;

13.Migrate Redo Log Files to ASM

The simple way is to add new log members on ASM to every Redo Log group and drop the log members on disk. Let’s check available logfile and their path and status

SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;

Add log members at new location, in ASM

alter database add logfile member '+DATA' to group 1;

alter database add logfile member '+DATA' to group 2;

alter database add logfile member '+DATA' to group 3;

alter database add logfile member '+RECV' to group 1;

alter database add logfile member '+RECV' to group 2;

alter database add logfile member '+RECV' to group 3;

Check if logfile are added to new location

SELECT a.group#, b.member, a.status FROM v$log a, V$logfile b WHERE a. group#=b.group#;

Let’s drop old logfiles from file system. To drop logfile member, Status should not be in current mode

SQL> ALTER DATABASE DROP LOGFILE MEMBER /u01/app/oracle/oradb/redo03.log';

SQL> alter system switch logfile;

SQL> ALTER DATABASE DROP LOGFILE MEMBER /u01/app/oracle/oradb/redo01.log';

SQL> alter system switch logfile;

SQL> ALTER DATABASE DROP LOGFILE MEMBER /u01/app/oracle/oradb/redo02.log';

Verify if redo log members are migrated to ASM

SQL> SELECT a.group#,b. member, a.status FROM v$log a , V$logfile b WHERE a.group#=b.group#;

Redo log file are migrated to ASM!

-- Re-start application services