Here in this article, We will provide detailed steps for converting a non-asm instance to an asm-instance. 

Prerequisites:

1. Oracle cluster  need to be installed on that server( for ASM) 

2. Create required asm disk groups.

EXAMPLE:

Currrently the datafiles are in /u02 mount point. We will move them to ASM DISK GROUP +TEST_DG’.

Database Name: DEVDBA

Asm disk group: +TEST_DG’

 

First move the controlfile to ASM disk

 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP NOMOUNT
ORACLE instance started. 
Total System Global Area 1.7103E+10 bytes
Fixed Size                  2245480 bytes
Variable Size            2214595736 bytes
Database Buffers         1.4865E+10 bytes
Redo Buffers               21708800 bytes
[oracle@TEST admin]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 3 09:20:31 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DEVDB (not mounted)

 

RMAN>  restore controlfile to '+TEST_DG' from '/u02/oradata/11.2.0.3/DEVDB/control01.ctl';
 

Starting restore at 03-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=193 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 03-OCT-13

 

RMAN> exit

Recovery Manager complete.
 

[oracle@TEST admin]$ s
 
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 09:25:18 2013 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> create spfile from pfile;

 File created.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 1.7103E+10 bytes
Fixed Size                  2245480 bytes
Variable Size            2214595736 bytes
Database Buffers         1.4865E+10 bytes
Redo Buffers               21708800 bytes

SQL> alter system set control_files='+TEST_DG/DEVDB/CONTROLFILE/current.270.827832049' scope=spfile;

System altered.

SQL> shutdown immediate;

ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.7103E+10 bytes
Fixed Size 2245480 bytes
Variable Size 2214595736 bytes
Database Buffers 1.4865E+10 bytes
Redo Buffers 21708800 bytes
Database mounted.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+TEST_DG/devdb/controlfile/current.270.827832049

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

 

MOVE DATAFILE TO ASM:

 

[oracle@TEST admin]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 3 09:28:11 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DEVDB (DBID=732555631, not open)

 

RMAN> configure device type disk parallelism 4;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored

RMAN> backup as copy database format '+TEST_DG';

 

Starting backup at 03-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=233 evice type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=241 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=249 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u02/oradata/DEVDB/system.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=/u02/oradata/DEVDB/sysaux.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00003 name=/u02/oradata/DEVDB/undotbs1.dbf
channel ORA_DISK_4: starting datafile copy
copying current control file
output file name=+TEST_DG/devdb/controlfile/backup.274.827832515 tag=TAG20131003T092833 RECID=1 STAMP=827832514
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_4: starting full datafile backup set
channel ORA_DISK_4: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_4: starting piece 1 at 03-OCT-13
channel ORA_DISK_4: finished piece 1 at 03-OCT-13
piece handle=+TEST_DG/devdb/backupset/2013_10_03/nnsnf0_tag20131003t092833_0.275.827832515 tag=TAG20131003T092833 comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:01
output file name=+TEST_DG/devdb/datafile/sysaux.272.827832515 tag=TAG20131003T092833 RECID=2 STAMP=827832517
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:04
output file name=+TEST_DG/devdb/datafile/undotbs1.273.827832515 tag=TAG20131003T092833 RECID=3 STAMP=827832518
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:04
output file name=+TEST_DG/devdb/datafile/system.271.827832513 tag=TAG20131003T092833 RECID=4 STAMP=827832530
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:27
Finished backup at 03-OCT-13

Once files are copied to ASM , switch database

RMAN> switch database to copy ;
datafile 1 switched to datafile copy "+TEST_DG/devdb/datafile/system.271.827832513"
datafile 2 switched to datafile copy "+TEST_DG/devdb/datafile/sysaux.272.827832515"
datafile 3 switched to datafile copy "+TEST_DG/devdb/datafile/undotbs1.273.827832515"

 

RMAN> ALTER DATABASE OPEN;
database opened
RMAN> exit

 Recovery Manager completed
 

[oracle@TEST admin]$ s
SQL*Plus: Release 11.2.0.3.0 Production on Thu Oct 3 09:29:26 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


SQL> select file_name from dba_data_files;

 FILE_NAME
--------------------------------------------------------------------------------
+TEST_DG/devdb/datafile/system.271.827832513
+TEST_DG/devdb/datafile/sysaux.272.827832515
+TEST_DG/devdb/datafile/undotbs1.273.827832515

 

 

MOVE REDOLOGS TO ASM:

 

 


 SQL> select  GROUP#,MEMBERS,STATUS from  v$log; 

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------

         1          1 CURRENT
         2          1 INACTIVE
         3          1 INACTIVE
 

SQL> alter database drop logfile group 3;
Database altered.

 SQL> alter database add logfile group 3 ('+TEST_DG') size 300M;
Database altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------

         1          1 CURRENT
         2          1 INACTIVE
         3          1 UNUSED

 

SQL> alter database drop logfile group 2;
Database altered.

SQL> alter database add logfile group 2 ('+TEST_DG') size 300M;

Database altered.

 
SQL>  select  GROUP#,MEMBERS,STATUS from  v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 UNUSED
         3          1 UNUSED

 

SQL> alter system switch logfile;
System altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 CURRENT
         3          1 UNUSED

 SQL>  alter system switch logfile;

System altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 ACTIVE
         3          1 CURRENT

 

SQL> alter system switch logfile;

System altered.

 SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 INACTIVE
         3          1 INACTIVE
 

SQL>  alter system switch logfile;

System altered.

SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------

         1          1 INACTIVE
         2          1 CURRENT
         3          1 INACTIVE

 

SQL> alter database drop logfile group 1;

Database altered.

 SQL> alter database add logfile group 1 ('+TEST_DG') size 300M;

Database altered.

 SQL> select  GROUP#,MEMBERS,STATUS from  v$log;

     GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 UNUSED
         2          1 CURRENT
         3          1 INACTIVE

 

SQL> create pfile from spfile;

 File created.

 SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
 

 

MODIFY THE PFILE POINTING TO SPFILE:

 

 

[oracle@TEST dbs] vi initDEVDB.ora


Add one parameter  .db_create_file_dest=+TEST_DG


SQL> startup pfile='/u02/oradata/11.2.0.3/db_1/dbs/initDEVDB.ora';
ORACLE instance started.
Total System Global Area 1.7103E+10 bytes
Fixed Size                  2245480 bytes
Variable Size            2214595736 bytes
Database Buffers         1.4865E+10 bytes
Redo Buffers               21708800 bytes
Database mounted.
Database opened.

SQL> create spfile='+TEST_DG' from pfile='/u02/oradata/11.2.0.3/db_1/dbs/initDEVDB.ora';
File created.
SQL> exit

 

 ASMCMD> find --type parameterfile +TEST_DG *

+TEST_DG/DEVDB/PARAMETERFILE/spfile.279.827833001


##remove all entries and add the spfile parameter
$cd $ORACLE_HOME/dbs

 
[oracle@TEST dbs] vi initDEVDB.ora

 

Spfile=’ +TEST_DG/DEVDB/PARAMETERFILE/spfile.279.827833001’

 SQL> startup pfile='/u02/oradata/11.2.0.3/db_1/dbs/initDEVDB.ora';
ORACLE instance started.

Total System Global Area 1.7103E+10 bytes
Fixed Size                  2245480 bytes
Variable Size            2214595736 bytes
Database Buffers         1.4865E+10 bytes
Redo Buffers               21708800 bytes
Database mounted.
Database opened.

 

MOVING THE TEMP FILE:

 

SQL> select name, bytes from v$tempfile;

 NAME
--------------------------------------------------------------------------------
     BYTES
----------
/u02/oradata/DEVDB/temp01.dbf

 104857600

 
SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents;

Tablespace dropped.

SQL> create temporary tablespace temp tempfile SIZE 10120M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp;
Database altered.

 

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------

+TEST_DG/devdb/tempfile/temp.281.827833353
+TEST_DG/devdb/tempfile/temp1.280.827833319


SQL> drop tablespace temp1 including contents;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+TEST_DG/devdb/tempfile/temp.281.827833353