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
Excellent doc!
Excellent documents