Move Oracle from file system storage to ASM storage.

In this tutorial you will learn how to move database storage from regular file system to ASM storage system. For this you need to have ASM up and running as well as disk groups have to be created.

1- Verify that your database has block change enabled:

SQL> select * from v$block_change_tracking;

STATUS     Filename                                                                         BYTES
---------- --------------------------------------------------------------------------- ----------
DISABLED

SQL>

2- Disable block change if enabled:

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Database altered.

SQL>

3- Shutdown your database:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

4- Create a pfile from your spfile:

SQL> create pfile='/tmp/pfile' from spfile;

File created.

SQL> exit

5- Make a copy of the file and after alter the necessary changes to your pfile after you create it:

Make sure you add the neccesary disk group as your storage location.

DER02.__java_pool_size=33554432
DER02.__large_pool_size=16777216
DER02.__oracle_base='/u02/der02/app'#ORACLE_BASE set from environment
DER02.__pga_aggregate_target=402653184
DER02.__sga_target=1191182336
DER02.__shared_io_pool_size=0
DER02.__shared_pool_size=352321536
DER02.__streams_pool_size=0
*.audit_file_dest='/u02/der02/app/admin/DER02/adump'
*.audit_trail='DB'
*.compatible='10.2.0.5.0'
*.control_files='+DGER',
                '+DGER',
                '+DGER'
*.core_dump_dest='+DGER'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_name='DER02'
*.db_recovery_file_dest_size=429496729600
*.db_recovery_file_dest='+DGER'
*.diagnostic_dest='/u02/der02/app'
*.event=''
*.instance_name='DER02'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='DER02'
*.sessions=225
*.sga_target=1191182336
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

6- After you have altered the pfile file create the spfile using the pfile and start up your database in nomount mode.

 sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 16 12:17:32 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create spfile from pfile='/tmp/pfile';

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1185853440 bytes
Fixed Size                  2227784 bytes
Variable Size             402653624 bytes
Database Buffers          771751936 bytes
Redo Buffers                9220096 bytes
SQL>

7- Connect to the RMAN:

Now in RMAN you will make a restore of the control file that exists in the file system. The task will create the control file stored on the ASM disk group.

[orader02@dc8002 tmp]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 16 12:20:52 2013

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

connected to target database: DER02 (not mounted)

RMAN> restore controlfile from '/backup/oradata/control/control01.ctl';

Starting restore at 16-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=15 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DGER/der02/controlfile/current.256.834322877
output file name=+DGER/der02/controlfile/current.303.834322879
output file name=+DGER/der02/controlfile/current.302.834322879
Finished restore at 16-DEC-13

8 - Mount the database and copy the data files to ASM disk group using rman "BACKUP AS COPY DATABASE" command:

This will transfer all the datafiles from file system to ASM disk group location.

RMAN> startup mount;

database is already started
database mounted
released channel: ORA_DISK_1

RMAN> BACKUP AS COPY DATABASE FORMAT '+DGER';

Starting backup at 16-DEC-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=15 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=44 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=58 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=/backup/oradata/REPD01_01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00021 name=/backup/oradata/REPI04_01.dbf
channel ORA_DISK_3: starting datafile copy
input datafile file number=00011 name=/backup/oradata/ERPD02_01.dbf
channel ORA_DISK_4: starting datafile copy
input datafile file number=00019 name=/backup/oradata/REPI02_01.dbf
..............
..............
..............
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:22
Finished backup at 16-DEC-13

9- From inside the RMAN "SWITCH DATABASE TO COPY":

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DGER/der02/datafile/system.263.834323795"
datafile 2 switched to datafile copy "+DGER/der02/datafile/undotbs1.271.834323389"
................
................
................
datafile 38 switched to datafile copy "+DGER/der02/datafile/repi02.273.834323619"
datafile 39 switched to datafile copy "+DGER/der02/datafile/repi04.265.834323691"

RMAN>

10- Open your database now:

RMAN> alter database open;

database opened

11- Move your temp tablespace now:

The process is preatty easy , you need to drop and recreate them:

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/backup/oradata/temp1_01.dbf
/backup/oradata/temp_02.dbf

SQL> create temporary tablespace tempX tempfile '/backup/oradata/tempx.dbf' SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace tempX;

Database altered.

SQL>  drop tablespace temp including contents;

Tablespace dropped.

SQL> drop tablespace temp1 including contents;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/backup/oradata/tempx.dbf


SQL> create temporary tablespace temp tempfile '+DGER' SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> create temporary tablespace temp1 tempfile '+DGER' SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DGER/der02/tempfile/temp.261.834324663
+DGER/der02/tempfile/temp1.280.834324685
/backup/oradata/tempx.dbf

12- Move your redolog to ASM now:

You will alter the parameter db_create_online_log_dest_1 and give it the disk group name as value. Log group 3 created on ASM. No need of mentioning the file name.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/backup/oradata/log/redo03a.log
/backup/oradata/log/redo03b.log
/backup/oradata/log/redo02b.log
/backup/oradata/log/redo02a.log
/backup/oradata/log/redo01a.log
/backup/oradata/log/redo01b.log


SQL> alter system set db_create_online_log_dest_1='+DGER' scope=spfile;
System altered.

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database add logfile group 1 size 52428800;
Database altered.

SQL> alter database drop logfile group 2;
Database altered.

SQL> alter database add logfile group 2 size 52428800;
Database altered.



SQL> select l.group# , l.bytes , l.status , lf.member
  f  2  rom v$logfile lf , v$log l
  3    where lf.group# = l.group#;

    GROUP#      BYTES STATUS             MEMBER
   ---------- ----------   ----------------
         3  262144000 ACTIVE          /backup/oradata/log/redo03a.log
         3  262144000 ACTIVE          /backup/oradata/log/redo03b.log
         2   52428800 UNUSED           +DGER/der02/onlinelog/group_2.305.834325013
         1   52428800 UNUSED            +DGER/der02/onlinelog/group_1.304.834324995


SQL> alter system checkpoint;
System altered.

SQL> select l.group# , l.bytes , l.status , lf.member
  f  2  rom v$logfile lf , v$log l
  3    where lf.group# = l.group#;

    GROUP#      BYTES STATUS             MEMBER
   ---------- ----------   ----------------
         3  262144000 UNUSED          /backup/oradata/log/redo03a.log
         3  262144000 UNUSED          /backup/oradata/log/redo03b.log
         2   52428800 UNUSED           +DGER/der02/onlinelog/group_2.305.834325013
         1   52428800 ACTIVE            +DGER/der02/onlinelog/group_1.304.834324995

-- Now we can drop and recreate the Group 3
SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 size 52428800;

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+DGER/der02/onlinelog/group_3.308.834325137
+DGER/der02/onlinelog/group_2.305.834325013
+DGER/der02/onlinelog/group_1.304.834324995

SQL>

13- Before you start droping your old datafiles make sure that you can bounce the database without any problems.

Very Important - Makes sure that you alter your pfile by adding the new control file location. Just copy the location from the RMAN output in step 7, it should look like this:

DER02.__java_pool_size=33554432
DER02.__large_pool_size=16777216
DER02.__oracle_base='/u02/der02/app'#ORACLE_BASE set from environment
DER02.__pga_aggregate_target=402653184
DER02.__sga_target=1191182336
DER02.__shared_io_pool_size=0
DER02.__shared_pool_size=352321536
DER02.__streams_pool_size=0
*.audit_file_dest='/u02/der02/app/admin/DER02/adump'
*.audit_trail='DB'
*.compatible='10.2.0.5.0'
*.control_files= '+DGER/der02/CONTROLFILE/current.256.834322877',
                 '+DGER/der02/CONTROLFILE/current.302.834322879',
                 '+DGER/der02/CONTROLFILE/current.303.834322879'
*.core_dump_dest='+DGER'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_name='DER02'
*.db_recovery_file_dest_size=429496729600
*.db_recovery_file_dest='+DGER'
*.diagnostic_dest='/u02/der02/app'
*.event=''
*.instance_name='DER02'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'
*.open_cursors=300
*.pga_aggregate_target=394264576
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='DER02'
*.sessions=225
*.sga_target=1191182336
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

14- Now is time to clean up the old datafiles using the RMAN utility:

Make sure you are connected to the right instance.

[orader02@dc8002 backup]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 16 13:06:02 2013

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

connected to target database: DER02 (DBID=4235695850)

RMAN> DELETE COPY OF DATABASE;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=114 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=310 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=324 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=3 device type=DISK
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time
------- ---- - --------------- ---------- ---------------
41      1    A 16-DEC-13       29043295348 16-DEC-13        Name: /backup/oradata/system01.dbf
42      2    A 16-DEC-13       29043295348 16-DEC-13        Name: /backup/oradata/undotbs01.dbf
..........
..........
79      39   A 16-DEC-13       29043295348 16-DEC-13        Name: /backup/oradata/REPI04_02.dbf

Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=/backup/oradata/system01.dbf RECID=41 STAMP=834324015
deleted datafile copy
datafile copy file name=/backup/oradata/undotbs01.dbf RECID=42 STAMP=834324015
deleted datafile copy
..............
..............
Deleted 39 objects

15- Cleanup the redologs and the temp datafiles:

To do this you need to use the Linux comand:

[orader02@dc8002 oradata]$ cd /backup/oradata/log/
[orader02@dc8002 log]$ ll
total 1536024
-rw-r----- 1 orader02 oinstall 262144512 Dec 16 12:41 redo01a.log
-rw-r----- 1 orader02 oinstall 262144512 Dec 16 12:41 redo01b.log
-rw-r----- 1 orader02 oinstall 262144512 Dec 16 12:41 redo02a.log
-rw-r----- 1 orader02 oinstall 262144512 Dec 16 12:41 redo02b.log
-rw-r----- 1 orader02 oinstall 262144512 Dec 16 12:57 redo03a.log
-rw-r----- 1 orader02 oinstall 262144512 Dec 16 12:57 redo03b.log

[orader02@dc8002 log]$ rm -rf red*


[orader02@dc8002 oradata]$ ll
total 66008
-rw-r----- 1 orader02 oinstall 6501179392 Dec 16 00:00 temp_02.dbf
-rw-r----- 1 orader02 oinstall 3221233664 Dec 13 20:45 temp1_01.dbf
-rw-r----- 1 orader02 oinstall  104865792 Dec 16 12:48 tempx.dbf
[orader02@dc8002 oradata]$ rm -rf temp*

16- Log in your database and enable the block change tracking(only if you use this option):

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Database altered.

Search Loading... Please wait

Subscribe to our Newsletter

Buy me a coffee

Join our Youtube Channel

A Rock Solid, Responsive CSS Framework built to work on all devices big, small and in-between