Migrating the database from File System to ASM (12c)

Hello everyone!

On this post I will show how migrate your oracle database reside on a file system to an ASM.
I will not entered on the detail how to configure and install your grid infrastructure software and how to allocate/create disks to ASM.
On this post I will considering that you already have an ASM instance up and configure.
My server is an Oracle Linux 7.2 using ASM for stand alone server.

Let’s start:

Database information:
Version: 12.1.0.2
Instance name: CDB1

ASM Information:
Version: 12.1.0.2
Intance name: +ASM

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
———- ————– ———— ———
3 PRODB READ WRITE NO

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME
——————– ———————————————————————-
EXAMPLE /u01/app/oracle/oradata/CDB1/datafile/o1_mf_example_cfpmvr6t_.dbf
USERS /u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_cfpmvrk7_.dbf
SYSAUX /u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_cfpmvrhy_.dbf
TBSPROD /u01/app/oracle/oradata/CDB1/datafile/PROD_DF01.dbf
SYSTEM /u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_cfpmvrk5_.dbf
TBSPROD /u01/app/oracle/oradata/CDB1/datafile/PROD_DF02.dbf

SQL> select member from v$logfile;

MEMBER
—————————————————————————–
/u01/app/oracle/oradata/CDB1/onlinelog/o1_mf_3_cfplg8j7_.log
/u01/app/oracle/fast_recovery_area/CDB1/onlinelog/o1_mf_3_cfplg8ol_.log
/u01/app/oracle/oradata/CDB1/onlinelog/o1_mf_2_cfplg7o3_.log
/u01/app/oracle/fast_recovery_area/CDB1/onlinelog/o1_mf_2_cfplg7sx_.log
/u01/app/oracle/oradata/CDB1/onlinelog/o1_mf_1_cfplg6pv_.log
/u01/app/oracle/fast_recovery_area/CDB1/onlinelog/o1_mf_1_cfplg72n_.log

SQL> select name from v$controlfile;

NAME
——————————————————————————-
/u01/app/oracle/oradata/CDB1/controlfile/o1_mf_cfplg3bs_.ctl
/u01/app/oracle/fast_recovery_area/CDB1/controlfile/o1_mf_cfplg3hv_.ctl
Now we will change some configurations:

NOTE: You cannot do this connected on the pluggable database.

SQL> alter system set db_recovery_file_dest=’+FRA’ scope=spfile;

System altered.

SQL> alter system set db_recovery_file_dest_size=3G scope=spfile;

System altered.

SQL> alter system set db_create_file_dest=’+DATA’ scope=spfile;

System altered.

Now take a backup copy of your database:

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Sat Mar 5 23:04:44 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: CDB1 (DBID=879420085)

RMAN> backup as copy database format ‘+DATA’;

Starting backup at 05-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=59 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_example_cfpmvr6t_.dbf
output file name=+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/example.265.905727903 tag=TAG20160305T230459 RECID=18 STAMP=905727988
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_cfpl8yqp_.dbf
output file name=+DATA/CDB1/DATAFILE/system.264.905727995 tag=TAG20160305T230459 RECID=19 STAMP=905728079
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_cfpl6x7b_.dbf
output file name=+DATA/CDB1/DATAFILE/sysaux.256.905728081 tag=TAG20160305T230459 RECID=20 STAMP=905728142
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_cfpmvrhy_.dbf
output file name=+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/sysaux.259.905728147 tag=TAG20160305T230459 RECID=21 STAMP=905728208
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_sysaux_cfplgsmg_.dbf
output file name=+DATA/CDB1/2D54980DD6DF116CE0530100007F0A96/DATAFILE/sysaux.263.905728213 tag=TAG20160305T230459 RECID=22 STAMP=905728264
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_undotbs1_cfpldxfb_.dbf
output file name=+DATA/CDB1/DATAFILE/undotbs1.260.905728269 tag=TAG20160305T230459 RECID=23 STAMP=905728299
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_cfplgsqn_.dbf
output file name=+DATA/CDB1/2D54980DD6DF116CE0530100007F0A96/DATAFILE/system.266.905728303 tag=TAG20160305T230459 RECID=24 STAMP=905728328
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_system_cfpmvrk5_.dbf
output file name=+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/system.262.905728329 tag=TAG20160305T230459 RECID=25 STAMP=905728346
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=/u01/app/oracle/oradata/CDB1/datafile/PROD_DF01.dbf
output file name=+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/tbsprod.261.905728355 tag=TAG20160305T230459 RECID=26 STAMP=905728358
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00013 name=/u01/app/oracle/oradata/CDB1/datafile/PROD_DF02.dbf
output file name=+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/tbsprod.258.905728363 tag=TAG20160305T230459 RECID=27 STAMP=905728375
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_cfpldw5o_.dbf
output file name=+DATA/CDB1/DATAFILE/users.257.905728379 tag=TAG20160305T230459 RECID=28 STAMP=905728378
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/u01/app/oracle/oradata/CDB1/datafile/o1_mf_users_cfpmvrk7_.dbf
output file name=+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/users.267.905728379 tag=TAG20160305T230459 RECID=29 STAMP=905728379
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 05-MAR-16

Starting Control File and SPFILE Autobackup at 05-MAR-16
piece handle=+FRA/CDB1/AUTOBACKUP/2016_03_05/s_905728384.256.905728387 comment=NONE
Finished Control File and SPFILE Autobackup at 05-MAR-16

RMAN> quit
Recovery Manager complete.

Change the default path your controlfile:

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Mar 5 23:16:53 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter system set control_files=’+DATA’,’+FRA’ scope=spfile;

System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1174405120 bytes
Fixed Size 2923680 bytes
Variable Size 738198368 bytes
Database Buffers 419430400 bytes
Redo Buffers 13852672 bytes

Go to RMAN and restore you controlfile:

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Sat Mar 5 23:24:00 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: CDB1 (not mounted)

RMAN> restore controlfile from ‘/u01/app/oracle/oradata/CDB1/controlfile/o1_mf_cfplg3bs_.ctl’;

Starting restore at 05-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/CDB1/CONTROLFILE/current.268.905729073
output file name=+FRA/CDB1/CONTROLFILE/current.257.905729073
Finished restore at 05-MAR-16

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> switch database to copy;

datafile 1 switched to datafile copy “+DATA/CDB1/DATAFILE/system.264.905727995”
datafile 3 switched to datafile copy “+DATA/CDB1/DATAFILE/sysaux.256.905728081”
datafile 4 switched to datafile copy “+DATA/CDB1/DATAFILE/undotbs1.260.905728269”
datafile 5 switched to datafile copy “+DATA/CDB1/2D54980DD6DF116CE0530100007F0A96/DATAFILE/system.266.905728303”
datafile 6 switched to datafile copy “+DATA/CDB1/DATAFILE/users.257.905728379”
datafile 7 switched to datafile copy “+DATA/CDB1/2D54980DD6DF116CE0530100007F0A96/DATAFILE/sysaux.263.905728213”
datafile 8 switched to datafile copy “+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/system.262.905728329”
datafile 9 switched to datafile copy “+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/sysaux.259.905728147”
datafile 10 switched to datafile copy “+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/users.267.905728379”
datafile 11 switched to datafile copy “+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/example.265.905727903”
datafile 12 switched to datafile copy “+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/tbsprod.261.905728355”
datafile 13 switched to datafile copy “+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/tbsprod.258.905728363”

RMAN> recover database;

Starting recover at 05-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 05-MAR-16

RMAN> alter database open;

Statement processed

RMAN> sql ‘alter pluggable database all open’;

using target database control file instead of recovery catalog
sql statement: alter pluggable database all open

Well now all your database files are reside on ASM disks, but we don’t finish yet:

Move your temp files too:

SQL> select TABLESPACE_NAME, FILE_NAME from dba_temp_files;

FILE_NAME
—————————————————————-
/u01/app/oracle/oradata/CDB1/datafile/o1_mf_temp_cfplgkjd_.tmp

SQL> ALTER DATABASE TEMPFILE ‘/u01/app/oracle/oradata/CDB1/datafile/o1_mf_temp_cfplgkjd_.tmp’ DROP;

Database altered.

SQL> alter tablespace temp add tempfile size 100m;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
—————————————————–
+DATA/CDB1/TEMPFILE/temp.269.905729881

And at last your redo log files:

SQL> SELECT GROUP#, STATUS FROM V$LOG;

GROUP#   STATUS 
 ———–   —————
            1    INACTIVE 
            2   CURRENT 
            3   INACTIVE 

alter database add logfile member ‘+FRA’ to group 1;
alter database add logfile member ‘+DATA’ to group 1;

alter database add logfile member ‘+FRA’ to group 2;
alter database add logfile member ‘+DATA’ to group 2;

alter database add logfile member ‘+FRA’ to group 3;
alter database add logfile member ‘+DATA’ to group 3;

alter database drop logfile member ‘/u01/app/oracle/fast_recovery_area/CDB1/onlinelog/o1_mf_1_cfplg72n_.log’;

alter database drop logfile member ‘/u01/app/oracle/oradata/CDB1/onlinelog/o1_mf_1_cfplg6pv_.log’;

alter database drop logfile member ‘/u01/app/oracle/oradata/CDB1/onlinelog/o1_mf_3_cfplg8j7_.log’;

alter database drop logfile member ‘/u01/app/oracle/fast_recovery_area/CDB1/onlinelog/o1_mf_3_cfplg8ol_.log’;

alter database drop logfile member ‘/u01/app/oracle/oradata/CDB1/onlinelog/o1_mf_2_cfplg7o3_.log’;

alter database drop logfile member ‘/u01/app/oracle/fast_recovery_area/CDB1/onlinelog/o1_mf_2_cfplg7sx_.log’;

NOTE: For drop redo log members the status cannot stay with CURRENT to change the status for this group run the commands bellow:

SQL> ALTER SYSTEM SWITCH LOGFILE;
SQL> ALTER SYSTEM CHECKPOINT;

Take a backup of your database at the current state:

RMAN> backup database;

Starting backup at 06-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=65 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/example.265.905727903
input datafile file number=00009 name=+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/sysaux.259.905728147
input datafile file number=00008 name=+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/system.262.905728329
input datafile file number=00012 name=+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/tbsprod.261.905728355
input datafile file number=00013 name=+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/tbsprod.258.905728363
input datafile file number=00010 name=+DATA/CDB1/2D54EF155A0816F1E0530100007FBBD4/DATAFILE/users.267.905728379
channel ORA_DISK_1: starting piece 1 at 06-MAR-16
channel ORA_DISK_1: finished piece 1 at 06-MAR-16
piece handle=+FRA/CDB1/2D54EF155A0816F1E0530100007FBBD4/BACKUPSET/2016_03_06/nnndf0_tag20160306t001642_0.267.905732203 tag=TAG20160306T001642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/CDB1/DATAFILE/system.264.905727995
input datafile file number=00003 name=+DATA/CDB1/DATAFILE/sysaux.256.905728081
input datafile file number=00004 name=+DATA/CDB1/DATAFILE/undotbs1.260.905728269
input datafile file number=00006 name=+DATA/CDB1/DATAFILE/users.257.905728379
channel ORA_DISK_1: starting piece 1 at 06-MAR-16
channel ORA_DISK_1: finished piece 1 at 06-MAR-16
piece handle=+FRA/CDB1/BACKUPSET/2016_03_06/nnndf0_tag20160306t001642_0.268.905732329 tag=TAG20160306T001642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=+DATA/CDB1/2D54980DD6DF116CE0530100007F0A96/DATAFILE/sysaux.263.905728213
input datafile file number=00005 name=+DATA/CDB1/2D54980DD6DF116CE0530100007F0A96/DATAFILE/system.266.905728303
channel ORA_DISK_1: starting piece 1 at 06-MAR-16
channel ORA_DISK_1: finished piece 1 at 06-MAR-16
piece handle=+FRA/CDB1/2D54980DD6DF116CE0530100007F0A96/BACKUPSET/2016_03_06/nnndf0_tag20160306t001642_0.269.905732455 tag=TAG20160306T001642 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 06-MAR-16

Starting Control File and SPFILE Autobackup at 06-MAR-16
piece handle=+FRA/CDB1/AUTOBACKUP/2016_03_06/s_905732529.270.905732531 comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAR-16

I hope that you enjoy!

Cheers!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s