May 9, 2017

ODA X4 - Migrating a database from ASM to ACFS

As promised ... Here's the post about migrating a database from ASM to ACFS on an ODA 4 - probably, this will work for newer ODA Servers as well


- Oracle Database Appliance machine, X4-<something>
- Software Release
- Prepared DB Software:,,

Story behind:

Task was, to create a new database of release for testing purposes. Command used to create the database:

oakcli create database -db TDB -oh /u01/app/oracle/product/
After that, I started configuring the database, creating tablespaces, schemas, setting parameters, and so on ... lots of work, actually.

What I expected was a database using ACFS as storage option (as all the other databases on that system) - what I got was ASM ... this, because starting with software release the default storage option is ACFS for all databases of release an higher. In other words: creating a database, using oakcli, will result in a database using ASM. At that stage, I had two possibilities: Drop the database and recreate it - or migrate an ASM DB to ACFS. Decision was: ACFS :-)

Found a document which describes that in very detail: STEPS TO MIGRATE NON-CDB DATABASES TO ACFS ON ORACLE DATABASE APPLIANCE 12.1.2 . I used it, but found a few bugs in that doc and decided to write a blog post to present a shorter story.

Step 1 - create an empty snapshot (as user root)

To prepare the filesystem / directory structure for the new ACFS based database and to be able to use the snapshot feature, one have to create an empty snapshot
acfsutil snap create -w TDB /u02/app/oracle/oradata/datastore

Step 2 - ACFS: create directories to store the datafiles and other stuff (as user root)

mkdir /u01/app/oracle/oradata/datastore/TDB
mkdir /u01/app/oracle/fast_recovery_area/datastore/TDB
mkdir /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB
chown oracle:oinstall /u01/app/oracle/oradata/datastore/TDB
chown oracle:oinstall /u01/app/oracle/fast_recovery_area/datastore/TDB
chown oracle:oinstall /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB

Step 3 - File destinations: prepare the spfile (as user oracle - sql)

alter system set db_create_file_dest='/u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB' scope=both sid='*';
alter system set db_create_online_log_dest_1='/u01/app/oracle/oradata/datastore/TDB' scope=both sid='*';
alter system set db_create_online_log_dest_2='/u01/app/oracle/oradata/datastore/TDB' scope=both sid='*';
alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/datastore/TDB' scope=both sid='*';

Step 4 - Migrate data files, control files and online redo logs (as user oracle)

First, copy the data files to the new destination by using rman and the 'copy database to destination' feature, and switch the database to the copy:
<Prompt>: rman target /

RMAN> startup mount
RMAN> backup as copy database to destination '/u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB'; ...
RMAN> switch database to copy;
RMAN> exit
Next, get the current control file name(s), using sqlplus, and reset the control files
<Prompt>:  sqlplus / as sysdba
SQL> select name from v$controlfile;
SQL> alter system reset control_files;
System altered.
SQL> exit
Restore the control file to ACFS (with rman)
<Prompt>:  rman target /
RMAN> shutdown immediate
RMAN> startup nomount
RMAN> restore controlfile from '+REDO/TDB/controlfile/current.266.943174969';
channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/datastore/TDB/TDB/controlfile/o1_mf_djro2too_.ctl
output file name=/u01/app/oracle/oradata/datastore/TDB/TDB/controlfile/o1_mf_djro2tqf_.ctl
Finished restore at 05-MAY-17

RMAN> exit
Change the control_files parameter in the database's spfile
<Prompt>:  sqlplus / as sysdba
SQL> alter system set control_files='/u01/app/oracle/oradata/datastore/TDB/TDB/controlfile/o1_mf_djro2too_.ctl','/u01/app/oracle/oradata/datastore/TDB/TDB/controlfile/o1_mf_djro2tqf_.ctl' scope=spfile sid='*';

System altered.
Last activity in this step: Migrate the online redo logs. Open sqlplus and execute the following:
 cursor rlc is
   select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
     from v$log
   select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
     from v$standby_log
     order by 1;
 stmt varchar2(2048);
 swtstmt varchar2(1024) := 'alter system switch logfile';
 archstmt varchar2(1024) := 'alter system archive log current';
 ckpstmt varchar2(1024) := 'alter system checkpoint global';
 for rlcRec in rlc loop
   if ( = 'YES') then
     stmt := 'alter database add standby logfile thread ' ||
       rlcRec.thr || ' size ' ||
       rlcRec.bytes_k || 'K';
     execute immediate stmt;
     stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
     execute immediate stmt;
     stmt := 'alter database add logfile thread ' || rlcRec.thr || ' size ' || rlcRec.bytes_k || 'K';
     execute immediate stmt;
       stmt := 'alter database drop logfile group ' || rlcRec.grp;
       execute immediate stmt;
       when others then
         execute immediate swtstmt;
         execute immediate archstmt;
         execute immediate ckpstmt;
         execute immediate stmt;
   end if;
 end loop;

What about the Tempfiles? This (as user oracle):

<Prompt>:  sqlplus / as sysdba
-- get ddl of current TEMP

-- rename the tablespace

alter tablespace temp rename to temp_old;

-- create a new TEMP by using the information from the GET_DDL


-- make the new tablespace to the new default TEMP

alter database default temporary tablespace TEMP;

-- drop old temp tablespace

drop tablespace temp_old;

Move spfile and password file to the new location and switch off ASM usage (as user root)

First, move spfile and pwdfile from ASM to ACFS. Use asmcmd to get current spfile and password file names
<Prompt>: su - grid -c "asmcmd"

copy the spfile to the new location spfile (and the password file)
su - grid -c "asmcmd cp '+DATA/TDB/PARAMETERFILE/spfile.285.943175449' /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB/spfileTDB.ora"

do not forget to change the owner:group for the new files:
chown oracle:oinstall /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB/spfileTDB.ora

spfile has been copied to the new directory - but the database is not aware of that ...:
srvctl config database -d TDB
Database unique name: TDB

Change spfile location in the database config (the -p option) and tell the DB that it does not need Disk Groups anymore (the -z option):
srvctl modify database -d TDB -p /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB/spfileTDB.ora -z

Check config:
Database unique name: TDB
Spfile: /u02/app/oracle/oradata/datastore/.ACFS/snaps/TDB/TDB/spfileTDB.ora
Disk Groups: 
Database is administrator managed

Stop the database and start again
srvctl stop database -d TDB
srvctl start database -d TDB

Final steps - clean up (as user oracle)

If the DB is running properly and the alert log does not show any problems, than it's time to clean up and remove the old datafile copies
<Prompt>: rman target /
RMAN> list datafilecopy all;
List of Datafile Copies
For each datafile copy
RMAN> delete noprompt datafilecopy '<name as shown in list datafilecopy>';


Several steps to execute - but it's working fine. Can save a lot of time and makes a export / import scenario obsolete.



  1. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here"Oracle Training in Chennai".

  2. Hi Robert,
    How do we handle the case where we find on a X4 one 11gR2 DB with half its datafiles on ASM and half on ACFS?
    We've used the utility in the paper you mentioned at the top, but now find that MOS note 1951012.1 is no longer on MOS - it was until very recently.
    Any advice will be appreciated.

  3. Nice information, valuable and excellent work, as share good stuff with good ideas and concepts. oracle training in chennai


Copyright © Robert Crames' Oracle Database and Middleware Blog | Powered by Blogger
Design by SimpleWpThemes | Blogger Theme by