12. Mai 2017

Originally published on Robert Crames’ Oracle Database and Middleware Blog

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 ACSF on oracle database appliance 12.1.2S . 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 (rlcRec.srl = '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.

zum Blog

1. Juni 2017 How to install and configure Oracle Forms & Reports 12c fully unattended – Part 2

A Toolset To Create Forms Reports Environment The CLI Way

Julian Frey 27. Februar 2018 ODA X6-2 upgrade to 12.2

Julian Frey about Databases

Julian Frey 20. Dezember 2018 Oracle Beer Bern 2018

Review und Outlook