Moving Datafiles and Redo Log Files From Filesystem To ASM Storage

Hi Friends,

In previous article , we learned  moving a datafile from file system to ASM storage. In this article, We will learn step by step moving a database (datafiles, control file and online redo log files) from file system to ASM storage with RMAN. .

1- If Block Change Tracking is enabled then you must disable it.

# sqlplus / as sysdba

SQL> alter database disable block change tracking;

2- Change default location of datafiles and control files as  +DATA disk group.

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

Change also default location of online redo log files as +RECO disk group.

SQL> alter system set db_create_online_log_dest_1=’+RECO’ scope=spfile;

3- Reset control_files parameter in the spfile parameter file.

SQL> alter system reset control_files scope=spfile sid=’*’;

4- Now, you can move datafiles to ASM disk group with RMAN. First, open the database on NOMOUNT state and restore control file. The control file will locate on the +DATA disk group.

$ rman target /

RMAN> startup nomount

RMAN> restore controlfile from ‘/data_TALIPDB/control01.ctl’;

5- Change status of the database to MOUNT state and take image copy backup to +DATA disk group.

RMAN> alter database mount;

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

6- After finished backup switch database to copy.

RMAN> switch database to copy;

7- Open the database.

RMAN> alter database open;

8- Now you can enable block change tracking.

SQL> alter database enable block change tracking using file ‘/oracle/ora11g/bct_file.log’;

9- You must add new tempfile to create tempfile on the +DATA disk group.

# sqlplus / as sysdba

SQL> alter tablespace temp add tempfile size 500M;

SQL> alter tablespace temp drop tempfile ‘/data_TALIPDB/temp01.dbf’

10- Let’s move online redo log files to ASM storage.

# sqlplus / as sysdba

SQL>select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP#           STATUS                 MEMBER

————          ————              —————————

1                            INACTIVE             /data1/redo01.log

2                            INACTIVE            /data1/redo02.log

3                            CURRENT             /data1/redo03.log

You can drop redo log files which are INACTIVE or UNUSED. You can not drop a redo log file which status is CURRENT or ACTIVE. Let’s drop online redo log groups and create on the +RECO disk group.

SQL> alter database drop logfile group 1;

Our new online redo log files will be created on the +RECO disk group because I set db_create_online_log_dest_1 parameter to ‘+RECO’ disk group.

SQL> alter database add logfile group 1 size 50M;

Drop/create second redo log group in the same way.

SQL> alter database drop logfile group 2;

SQL> alter database add logfile group 2 size 50M;

11- Rerun query to see status of online redo log files.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP#           STATUS                 MEMBER

————          ————              —————————

1                            INACTIVE            +RECO/talipdb/online log/group_1.257.7782 52603

2                            INACTIVE            +RECO/talipdb/online log/group_2.258.7782 52715

3                            CURRENT             /data1/redo03.log

We can not drop 3th redo log group because it is CURRENT log file. In this situation, switch log file.

SQL> alter system switch logfile;

Rerun above query. You will see its status as ACTIVE because of the redo log file is needed for crash recovery.

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP#           STATUS                 MEMBER

————          ————              —————————

1                            CURRENT             +RECO/talipdb/online log/group_1.257.7782 52603

2                            UNUSED               +RECO/talipdb/online log/group_2.258.7782 52715

3                            ACTIVE                 /data1/redo03.log

To change status to INACTIVE, you must checkpoint.

SQL> alter system checkpoint;

SQL> select b.group# , b.status , a.member from v$logfile a , v$log b where a.group# = b.group# order by 1;

GROUP#           STATUS                 MEMBER

————          ————              —————————

1                            CURRENT             +RECO/talipdb/online log/group_1.257.7782 52603

2                            UNUSED               +RECO/talipdb/online log/group_2.258.7782 52715

3                            INACTIVE             /data1/redo03.log

Now, you can drop/create 3th redo log group.

SQL> alter database drop logfile group 3;

SQL> alter database add logfile group 3 size 50M;

Let’s verify our online redo log files.

SQL> set lines 50

SQL> select member from v$logfile;

MEMBER

————————————————–

+RECO/talipdb/onlinelog/group_3.259.778253083

+RECO/talipdb/onlinelog/group_2.258.778252715

+RECO/talipdb/onlinelog/group_1.257.778252603

Let’s verify our datafiles.

SQL> select name from v$datafile;

NAME

——————————————————————————–

+DATA/talipdb/datafile/system.256.778251403

+DATA/talipdb/datafile/sysaux.257.778251487

+DATA/talipdb/datafile/undotbs1.258.778251553

+DATA/talipdb/datafile/users.260.778251563

SQL> select name from v$tempfile;

NAME

——————————————————————————–

+DATA/talipdb/tempfile/temp.262.778252097

We moved our datafiles and online redo log files to ASM.  Hope to see you happy to be working with ASM databases … 🙂

Talip Hakan Öztürk

Advertisement