Migrating a database from ASM to ACFS
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 188.8.131.52.0
- Prepared DB Software: 184.108.40.206, 220.127.116.11, 18.104.22.168
Task was, to create a new database of release 22.214.171.124 for testing purposes. Command used to create the database:
oakcli create database -db TDB -oh /u01/app/oracle/product/126.96.36.199/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 188.8.131.52 the default storage option is ACFS for all databases of release 184.108.40.206 an higher. In other words: creating a database 220.127.116.11, 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
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.