How to migrate datafiles from filesystem to ASM — ORACLE Database

on


          


 This writing is to show some quick steps to move datafiles of an oracle database from filesystem to ASM .

Before we continue , in this writing we are assuming that a grid installation is completed in the database server in question and ASM is running fine.

If you need a separate writing on grid installation then please comment.

Now let’s continue with our steps.

MOVING CONTROL FILES AND DATAFILES

Step 1) If using block change tracking then disable it.

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Step 2) Set the default location for datafiles,controlfiles and online logfiles to our ASM diskgroup.

alter system set db_create_file_dest='+DATA' scope=spfile;
alter system set db_create_online_log_dest_1='+DATA' scope=spfile;

Step 3) Remove the control_files parameter from the spfile using reset control_files.Control file will automatically go to +DATA diskgroup since it is defined in db_create_file_dest.

alter system reset control_files scope=spfile sid='*';

Step 4) Connect to RMAN and restore the controlfile in nomount state. Controlfiles will be now restored to our asm diskgroup.

startup nomount;
restore controlfile from '<controlfile_location>';

Step 5) Mount the database and make a copy of all datafiles on our diskgroup +DATA

alter database mount;
backup as copy database format '+DATA';

Step 6) Switch database to datafile copies and open the database. After this switch , the copies on +DATA have become the database datafiles and the original files have become copies.

switch database to copy;
alter database open;

Step 7) Now do this if you have disabled block change tracking in step1 .

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Step 8) Check to see if datafiles and controlfiles are now available in asm diskgroup.

select name from v$datafile;
show parameter control_files

MOVING TEMPFILES to ASM

Step 1) Add a tempfile to temp tablespace in asm . As we have already given create_dest to +DATA it will be created there.

select file_name from dba_temp_files;
alter tablespace temp add tempfile size 20m;
select file_name from dba_temp_files;

Step 2) Now drop the temfile in the filesystem as below.

alter tablespace temp drop tempfile '<tempfile location>';
select file_name from dba_temp_files;

MOVING ONLINE LOGFILES TO ASM

Step 1) Add logfiles to the groups . You can check the groups and status using below command.

SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
ALTER DATABASE ADD LOGFILE MEMBER '+ASM_Disk_group' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '+ASM_Disk_group' TO GROUP 2;

Step 2) Now drop the old logfile with status INACTIVE . If status is CURRENT then switch the logfile to make it inactive and then drop. If status is ACTIVE then do a checkpoint and then drop.

SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
ALTER DATABASE DROP LOGFILE MEMBER '<old file name>';
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE DROP LOGFILE MEMBER '<old file name>';
ALTER SYSTEM SWITCH LOGFILE;

With this we have completely moving the files from filesystem to ASM.

Thank you for going through my writing. Please feel free to comment if you are looking for any specific tech related topics .

Comments

Post a Comment