Tuesday, May 29, 2012

Migrating Database from Filesystem to ASM

Couple of days back we got a project case to migrate database from Filesystem to ASM storage. I followed below procedure to achieve this.

Before to begin this work binaries for ASM should be installed and necessary diskgroups should be created to accommodate the space on new storage.

Note : Make sure that your diskgroup size (for Datafiles) should be grater than the total database size. Below are the steps needs to be follow for this migration.

Connect to database and check the datafile location,controlfile name, logfile members and current log sequence#

[oracle@asm ~]$ sqldba
SQL*Plus: Release 11.2.0.3.0 Production on Tue May 29 08:04:25 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/abcd/system01.dbf
/u01/app/oracle/oradata/abcd/sysaux01.dbf
/u01/app/oracle/oradata/abcd/undotbs01.dbf
/u01/app/oracle/oradata/abcd/users01.dbf
/u01/app/oracle/oradata/abcd/example01.dbf

SQL> archive log list

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/arch_dest
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3

SQL> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- ---------- 
1 1 1 52428800 512 1 YES INACTIVE
2 1 2 52428800 512 1 YES INACTIVE
3 1 3 52428800 512 1 NO CURRENT

SQL> select name from v$controlfile;

NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/abcd/control01.ctl
/u01/app/oracle/oradata/abcd/control02.ctl

SQL> show parameter db_create_file_des

NAME TYPE VALUE
------------------------------------ ----------- ----------------
db_create_file_dest string
Change the CONTROL_FILES and DB_CREATE_FILE_DEST parameters to point ASM diskgroups.
SQL> ALTER SYSTEM SET control_files='+DATAFILE','+ARCHLOG' scope=spfile;
System altered.

SQL> ALTER SYSTEM SET db_create_file_dest='+DATAFILE' SCOPE=spfile;
System altered.

SQL> create pfile='/tmp/pfile_abcd.ora' from spfile
File created.

Need to make sure that we have sufficient space on those diskgroups to hold existing database.
Before to create ASM Diskgroups we should have done Capacity Planing. According to that panning diskgroups should be created otherwise during the conversion time it might get fail saying diskgroup space exhausted!


Now take a copy of the whole database into new diskgroup format.
RMAN> backup as copy database format '+DATAFILE';

Starting backup at 29-MAY-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=135 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/abcd/system01.dbf

output file name=+DATAFILE/abcd/datafile/system.270.784541581 tag=TAG20120529T081300 RECID=2 STAMP=784541600

As you can see here, All files from filesystem are getting pushed into ASM storage.

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/abcd/sysaux01.dbf

output file name=+DATAFILE/abcd/datafile/sysaux.268.784541607 tag=TAG20120529T081300 RECID=3 STAMP=784541622

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/abcd/example01.dbf

output file name=+DATAFILE/abcd/datafile/example.265.784541631 tag=TAG20120529T081300 RECID=4 STAMP=784541637

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/abcd/undotbs01.dbf

output file name=+DATAFILE/abcd/datafile/undotbs1.264.784541639 tag=TAG20120529T081300 RECID=5 STAMP=784541639

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/abcd/users01.dbf

output file name=+DATAFILE/abcd/datafile/users.269.784541641 tag=TAG20120529T081300 RECID=6 STAMP=784541641

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 29-MAY-12

As you can see the above output, while taking copy of the database, each datafile copies are getting restored into ASM diskgroup.

Note : This can be done before to begin the outage, so that we can reduce the outage number of outage hours to minutes

After the migration if you want to open the database without RESETLOGS then no need to take backup of controlfile.

Need to bounce the database now.
While starting up the db place the instance in NOMOUNT state
RMAN> shutdown immediate

database closed

database dismounted

Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started
Total System Global Area 521936896 bytes

Fixed Size 2229944 bytes

Variable Size 159385928 bytes

Database Buffers 352321536 bytes

Redo Buffers 7999488 bytes

Now restore the controlfiles from any one of the multiplexed location
RMAN>restore controlfile from '/u01/app/oracle/oradata/abcd/control01.ctl';
Starting restore at 29-MAY-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=135 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+DATAFILE/abcd/controlfile/current.256.784541557

output file name=+ARCHLOG/abcd/controlfile/current.256.784541557

Finished restore at 29-MAY-12

Here you could see Controlfiles are restored into ASM instance

RMAN> sql 'alter database mount';

sql statement: alter database mount

released channel: ORA_DISK_1
As you notice here, during the restoration process control files are restoring into ASM diskgroups based on the location specified in parameter file.

At this time a copy of each datafile got created on ASM diskgroup so we need to update the controlfile with the new location of each datafile. To achieve this we can switch the entire database to copy (image copy) and recover the database.

RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATAFILE/abcd/datafile/system.270.784541581"

datafile 2 switched to datafile copy "+DATAFILE/abcd/datafile/sysaux.268.784541607"

datafile 3 switched to datafile copy "+DATAFILE/abcd/datafile/undotbs1.264.784541639"

datafile 4 switched to datafile copy "+DATAFILE/abcd/datafile/users.269.784541641"

datafile 5 switched to datafile copy "+DATAFILE/abcd/datafile/example.265.784541631"


RMAN> recover database;

Starting recover at 29-MAY-12

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/oradata/abcd/redo03.log

archived log file name=/u01/app/oracle/oradata/abcd/redo03.log thread=1 sequence=3

media recovery complete, elapsed time: 00:00:00

Finished recover at 29-MAY-12
Now we can open the database WITHOUT RESETLOGS.
RMAN> sql 'alter database open';

sql statement: alter database open
Now connect to the database and verify the datafile locations.
[oracle@asm ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue May 29 08:15:13 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
ABCD READ WRITE

SQL> select name from v$datafile;

NAME
-----------------------------------------------------------------
+DATAFILE/abcd/datafile/system.270.784541581

+DATAFILE/abcd/datafile/sysaux.268.784541607

+DATAFILE/abcd/datafile/undotbs1.264.784541639

+DATAFILE/abcd/datafile/users.269.784541641

+DATAFILE/abcd/datafile/example.265.784541631
Now all datafiles has been migrated into ASM storage! Till now we migrated only Datafiles. but we have to migrate tempfiles and online Redo logs too. for temporary files we can add it manually.
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------
/u01/app/oracle/oradata/abcd/redo03.log

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

/u01/app/oracle/oradata/abcd/redo01.log
Migrating redo logs into ASM is simple way, just add more redolog groups and then drop existing redolog groups which are in Filesystem. Before to add redolog groups make sure you set DB_CREATE_ONLINE_LOG_DEST_n parameter and it should point to ASM diskgroups.
SQL> show parameter db_create_online_log_dest

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------
db_create_online_log_dest_1          string     +LOG1
db_create_online_log_dest_2          string     +LOG2
 
SQL> alter database add logfile group 4;
Database altered.

SQL> alter database add logfile group 5;
Database altered.

SQL> alter database add logfile group 6;
Database altered.

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

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

SQL> alter system switch logfile;
System altered.

SQL> alter system checkpoint;
System altered.

Manually switching logfiles because Current Group can not be dropped.

SQL> select * from v$LOg;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

1 1 1 52428800 512 1 YES INACTIVE 1046676 29-MAY-12 1047525 29-MAY-12

4 1 2 104857600 512 1 NO CURRENT 1047525 29-MAY-12 2.8147E+14

5 1 0 104857600 512 1 YES UNUSED 0 0

6 1 0 104857600 512 1 YES UNUSED 0 0

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

SQL> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------

4 1 2 104857600 512 1 NO CURRENT 1047525 29-MAY-12 2.8147E+14

5 1 0 104857600 512 1 YES UNUSED 0 0

6 1 0 104857600 512 1 YES UNUSED 0 0


SQL> select member from v$Logfile;

MEMBER
---------------------------------------------------

+DATAFILE/abcd/onlinelog/group_4.266.784542027

+DATAFILE/abcd/onlinelog/group_5.267.784542035

+DATAFILE/abcd/onlinelog/group_6.271.784542041

Now the entire database got migrated to ASM storage and opened the database WITHOUT RESETLOGS!!

I Hope this article helped to you. I am expecting your suggestions/feedback.