A collection of tutorials, code and tools to help you get better using Oracle
17 July 2014
With the non-asm database up and running, change the control_files parameter and db_create_file_dest parameters to point at ASM instead of their current location:
alter system set control_files='+DATADG', '+DATADG', '+DATADG' scope=spfile;
alter system set db_create_file_dest='+DATADG';
Now shutdown the database and bring it backup with nomount:
shutdown immediate;
startup nomount;
Now jump into RMAN to copy the relevant files into ASM:
rman target /
restore controlfile from '/oraworkspace/apex01/control01.ora';
backup as copy database format '+DATADG';
switch database to copy;
alter database open;
We are nearly done, but there are 3 more things to do:
select file_name, tablespace_name
from dba_temp_files;
FILE_NAME TABLESPACE_NAME
-------------------- ------------------------------
/oraworkspace/apex01 TEMP
/temp.dbf
alter tablespace temp add tempfile size 500M;
alter database tempfile '/oraworkspace/apex01/temp.dbf' drop including datafiles;
The redo logs will all still be outside ASM, so they need dropped and recreated inside ASM:
SQL> select group#, status, bytes from v$log;
GROUP# STATUS BYTES
---------- ---------------- ----------
1 INACTIVE 52428800
2 INACTIVE 52428800
3 CURRENT 52428800
Groups 1 and 2 are inactive, so they can be dropped and recreated:
alter database drop logfile group 1;
alter database add logfile group 1 size 52428800;
alter database drop logfile group 2;
alter database add logfile group 2 size 52428800;
Switch the logs and recreate the final group:
alter system switch logfile;
alter database drop logfile group 3;
alter database add logfile group 3 size 52428800;
Check the logs are all now in ASM:
select member from v$logfile
MEMBER
--------------------------------------------------
+DATADG/apex01/onlinelog/group_1.278.853143825
+DATADG/apex01/onlinelog/group_2.273.853143825
+DATADG/apex01/onlinelog/group_3.276.853144187
Finally, copy the SPFile into ASM. To do this create a pfile and then create the spfile in ASM:
create pfile from spfile;
create spfile='+DATADG/APEX01/spfileAPEX01.ora' from pfile;
shutdown immediate;
Now create a file called initAPEX01.ora in the ORACLE_HOME/dbs directory, and put the following contents in it:
spfile='+DATADG/APEX01/spfileAPEX01.ora'
Restart the instance and confirm the spfile being used is in ASM:
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATADG/apex01/spfileapex01.or
a
I think you should be able to get Oracle to detect the spfile in ASM without a bootstaped init.ora file in the dbs directory, but I couldn't get it working, so it may not be possible.