Oracle ASM Lesson 8: How to move database to ASM storage



Last updated on July 29th, 2016 at 07:15 am

There are two cases to move  database to ASM storage
1) When you have enough disk space to hold the entire database both in non-ASM storage and in ASM storage
2) When  you do not have enough disk space to have a complete copy of your database in ASM and another in non-ASM storage
Case -1a) The process described in this section is intended to minimize downtime for your database
b) Back up your database files as copies to the ASM disk group.

BACKUP AS COPY INCREMENTAL LEVEL 0  DATABASE
FORMAT ‘+DATA’  TAG ‘ASM_MIGRATION’;

You can perform this backup with multiple channels to improve performance, depending upon your hardware configuration. For example:

run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
allocate channel d7 type disk;
allocate channel d8 type disk;

allocate channel d9 type disk;
allocate channel d10 type disk;
BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE
FORMAT ‘+DATA’  TAG ‘ASM_MIGRATION;
}

This backup may take a long time, depending upon the size of your database. If there has been a lot of activity on the database during the time the backup was created, you may wish to use the following procedure to create an incremental backup of the database afterwards, to refresh the copy with changes since the migration process started. If so, use the following script:
RMAN>  backup  incremental level 1 for recover of copy
with tag ‘ASM_MIGRATION’ database ;
RMAN> recover copy of database with tag ‘ASM_MIGRATION’;

This minimizes the time required for the media recovery performed just before the copy of the database in ASM is opened at the end of the migration process. You may also want to perform this step using multiple channels, if using them improves performance in your environment.

c)  Create spfile on ASM
create spfile=’+DATA/spfile’ from pfile=’/ora/init.ora’;
d) shutdown immediate
e)  startup nomount with new spfile
f) Change the control to new Path
alter system set control_files=’+DATA/control1.f’,’+FLASH/controlt2.f’ scope=spfile sid=’*’;
g) Change the DB_RECOVERY_FILE_DEST_SIZE

alter system set DB_RECOVERY_FILE_DEST_SIZE=1000G SID=’*’; alter system set DB_RECOVERY_FILE_DEST=’+FLASH’ SID=’*’;

h)  Now perform following steps
shutdown immediate;
startup nomount PFILE=’/tmp/pfile.ora’; #using ASM SPFILE now
restore controlfile from ‘/ora/controlt1.f’;
alter database mount;
switch database to copy;
recover database;
alter database open

i) Change the tempfile and redo log to ASM group using following command

Tempfile
SQL> alter tablespace TEMP add tempfile ‘+DATA’ SIZE 100M;
Drop any existing tempfile on the old diskgroup
SQL> alter database tempfile ‘/ora/temp01.dbf’ drop;

Redolog

Find out how many members we have have in redolog groups, make sure that we have only one member in each log group.(drop other members).

Suppose we have 3 log groups, then add one member to each log group as following:

SQL> alter database add logfile member ‘+DATA’ to group 1;
SQL> alter database add logfile member ‘+DATA’ to group 2;
SQL> alter database add logfile member ‘+DATA’ to group 3;
Then we can drop the old logfile member from earlier diskgroups as:

SQL> alter database drop logfile member ‘complete_name’;

j) Use following query to see if all the database related files are on ASM

SQL> select name from v$controlfile
union
select name from v$datafile
union
select name from v$tempfile
union
select member from v$logfile
union
select filename from v$block_change_tracking

k) The above procedure can be used to move the database from one ASM diskgroup to another diskgroup

Case -II

a) Take the cold backup of database to tape
b) Create ASM diskgroup
c) Restore the database on ASM Diskgroup using RMAN

All the Oracle ASM related articles

ASM: Lesson 1: Introduction

ASM : Lesson -2 ASM disks

Oracle ASM : Lesson 3 ASM Diskgroups

Oracle ASM : Lesson 4 Failure Groups and CSS

Oracle ASM Lesson 5 : ASM Parameters

Oracle ASM Lesson 6 : ASM Rebalance

Oracle ASM Lesson 7:ASM Metadata

Oracle ASM Lesson 8: How to move database to ASM storage

Oracle ASM Lesson 9: ASM queries

How to collect Oracle ASM metadata

Top Oracle ASM queries you should know

Oracle ASM best practice to add disk


Leave a Reply