Better At Oracle

A collection of tutorials, code and tools to help you get better using Oracle

17 July 2014

Move a non asm database into asm

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:

Fix the temporary tablespace

select file_name, tablespace_name
from dba_temp_files;

-------------------- ------------------------------
/oraworkspace/apex01 TEMP

alter tablespace temp add tempfile size 500M;
alter database tempfile '/oraworkspace/apex01/temp.dbf' drop including datafiles;

Fix the redo logs

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


Get the SPFile into ASM

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:


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

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.

blog comments powered by Disqus