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

Given:

- Oracle Database Appliance machine, X4-<something>
- Software Release 12.1.2.7.0
- Prepared DB Software: 11.2.0.3, 11.2.0.4, 12.1.0.2

Story behind:

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

oakcli create database -db TDB -oh /u01/app/oracle/product/11.2.0.3/dbhome_1

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 12.1.2.0 the default storage option is ACFS for all databases of release 11.2.0.4 an higher. In other words: creating a database 11.2.0.3, 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;
NAME
--------------------------------------------------------------------------------
+REDO/TDB/controlfile/current.266.943174969
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:

declare
 cursor rlc is
   select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
     from v$log
   union
   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';
begin
 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;
   else
     stmt := 'alter database add logfile thread ' || rlcRec.thr || ' size ' || rlcRec.bytes_k || 'K';
      dbms_output.put_line(stmt);
     execute immediate stmt;
     begin
       stmt := 'alter database drop logfile group ' || rlcRec.grp;
       dbms_output.put_line(stmt);
       execute immediate stmt;
     exception
       when others then
         execute immediate swtstmt;
         execute immediate archstmt;
         execute immediate ckpstmt;
         execute immediate stmt;
     end;
   end if;
 end loop;
end;
/

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

<Prompt>:  sqlplus / as sysdba
-- get ddl of current TEMP
select DBMS_METADATA.GET_DDL('TABLESPACE','TEMP') from dual;

-- rename the tablespace
alter tablespace temp rename to temp_old;

-- create a new TEMP by using the information from the GET_DDL
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE SIZE 1468006400 AUTOEXTEND ON NEXT 1024M MAXSIZE 32767M;

-- 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"
ASMCMD> ls -l DATA/TDB

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>';

Conclusion

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