Friday , October 20 2017
Home / Oracle DBA / Moving a database from Normal File System to ASM storage

Moving a database from Normal File System to ASM storage

Default storage for oracle database is File System, where database files resides on local storage. In order to configure ASM storage and move existing database from normal File System to ASM, follow below steps:

Step 1: Check locations for data files, control files, redo log files and temp files on existing database.

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

NAME

---------------------------------------------------------------------

/u01/app/oracle/flash_recovery_area/newdb/control02.ctl

/u01/app/oracle/oradata/newdb/control01.ctl

/u01/app/oracle/oradata/newdb/example01.dbf

/u01/app/oracle/oradata/newdb/redo01.log

/u01/app/oracle/oradata/newdb/redo02.log

/u01/app/oracle/oradata/newdb/redo03.log

/u01/app/oracle/oradata/newdb/sysaux01.dbf

/u01/app/oracle/oradata/newdb/system01.dbf

/u01/app/oracle/oradata/newdb/temp01.dbf

/u01/app/oracle/oradata/newdb/undotbs01.dbf

/u01/app/oracle/oradata/newdb/users01.dbf

11 rows selected.

NOTE: You can see that all the newdb database files resides on /u01/ mount point which is the normal linux file system.

Step 2: Check “db_create_file_dest” parameter. This is a parameter used to define location of datafiles.

SQL> show parameter db_create_file_dest;



NAME                                                        TYPE VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest                   string

NOTE: As there is no VALUE set to this parameter, we can assume that the database is in linux file system.

Now, change this parameter to ASM disk location as below:

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

System altered.

Step 4: Similarly change locations for online redo log files and control files as below.

SQL> alter system set db_create_online_log_dest_1='+DATA' scope=spfile;

System altered.


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

System altered.

Step 5: Shutdown database and connect RMAN in nomount stage.

SQL> startup nomount;

ORACLE instance started.


Total System Global Area  835104768 bytes

Fixed Size                                2217952 bytes

Variable Size                        499124256 bytes

Database Buffers               331350016 bytes

Redo Buffers                         2412544 bytes

SQL> !

[oracle@localhost Desktop]$ rman target/

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 1 03:51:51 2010

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

connected to target database: NEWDB (not mounted)

RMAN>

Step 6: Perform following steps in RMAN.

NOTE: Below command will restore control file from /u01/app/oracle/oradata/newdb/control01.ctl to ASM disk (as set in the parameter).

RMAN> restore controlfile from '/u01/app/oracle/oradata/newdb/control01.ctl’;

Now, change database to mount stage and take database backup on +DATA (ASM disk)

RMAN> alter database mount;

RMAN> backup as copy database format '+DATA';

NOTE: RMAN will backup all the database files from /u01 mount point to ASM disk (+DATA).

Step 7: Once RMAN backup is finished, execute below command which will switch datafiles from local location to ASM disk location from the backup copy.

RMAN> switch database to copy;

Step 8: Open the database now.

RMAN> alter database open;

database opened

Step 9: Now connect database through sqlplus and add a new temp file in ASM disk group using following command. Note that the location will be considered as ASM disk (i.e +DATA) as we have already altered a parameter initially. Once, the new temp file is added, delete the old one.

SQL> alter tablespace temp add tempfile size 500m;

Tablespace altered.


SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/newdb/temp01.dbf';

Tablespace altered.

Step 10: Now, we have to modify online redo log files for which first check the status of all available 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            /u01/app/oracle/oradata/newdb/redo01.log

2             INACTIVE            /u01/app/oracle/oradata/newdb/redo02.log

3             CURRENT            /u01/app/oracle/oradata/newdb/redo03.log

Step 11: First drop old redo log files which are INACTIVE or UNUSED. We cannot drop redo log files which are in CURRENT or ACTIVE mode. So, Log1 and Lo2 are INACTIVE, so that we can drop them.

SQL> alter database drop logfile group 1;

Database altered.

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

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

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

Database altered.

Step 12: Now switch the log file 3 in order to drop and create group 3 logfile. After switching, we need to use checkpoint command to write the ACTIVE logfile content to the disk.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

Now, group 3 redo logfile will become INACTIVE. You can drop and add new log file.

SQL> alter database drop logfile group 3;

Database altered.


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

Database altered.

Final Step: We have completed the migration activities. To validate, please check following status.

SQL> select name from v$datafile

union

select name from v$controlfile

union

select name from v$tempfile

union

select member from v$logfile;


NAME

---------------------------------------------------------------------------
+DATA/newdb/controlfile/current.260.707115569

+DATA/newdb/datafile/example.268.707115641

+DATA/newdb/datafile/sysaux.270.707115937

+DATA/newdb/datafile/system.261.707115567

+DATA/newdb/datafile/undotbs1.267.707115601

+DATA/newdb/datafile/users.265.707115593

+DATA/newdb/onlinelog/group_1.262.707115577

+DATA/newdb/onlinelog/group_2.269.707115663

+DATA/newdb/onlinelog/group_3.259.707115453

+DATA/newdb/tempfile/temp.263.707115583


10 rows selected.

You can see that all the database files are now resides on ASM disk (+DATA) and ASM migration is completed.

Comments

comments

Check Also

INS-41814 The installer has detected that some of the services of Oracle Grid Infrastructure are not running on this system.

ERROR DESCRIPTION ================= INS-41814 The installer has detected that some of the services of Oracle …

Leave a Reply

Your email address will not be published. Required fields are marked *