Migrating Database Files from NON-ASM to ASM Disks

Leave a reply

How to convert from NON-ASM to ASM disks Oracle

Automatic storage management is feature in oracle for make error detection or redundancy/mirroring in data without special level raid hard disk. Convert database from normal disk to ASM with help of RMAN. You can migrate your data files from Disk to ASM Disk with help of RMAN.

Steps to migrate datafiles from NON ASM disk to ASM Disk.

1. Check Block change tracking : v$block_change_tracking

select * from v$block_change_tracking;

2. Shutdown the database:

Shutdown immediate;

3. Create Pfile and add/modify the below parameters:

[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 12:17:50 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.

SQL> create pfile from spfile;

4. Modify Pfile with these parameters:
I have already created 2 ASM disk groups DATA and FLASH.

create pfile='location' from spfile='location';

5. Create spfile back from modified pfile:
PS: take a copy of original spfile before you overwrite spfile using below command.

create spfile='location' from pfile='location';

6. Copy Database to ASM diskgroups using rman


CONNECT TARGET /
STARTUP NOMOUNT;

RUN {
  # Allocate multiple channels for performance
  ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
  ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
  ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
  ALLOCATE CHANNEL c4 DEVICE TYPE DISK;

  # Restore control file to ASM
  RESTORE CONTROLFILE FROM '/path/to/controlfile.bkp';
  ALTER DATABASE MOUNT;

  # Restore database files to ASM
  SET NEWNAME FOR DATABASE TO '+DATA';  -- Replace +DATA with your ASM diskgroup name

  RESTORE DATABASE;
  SWITCH DATAFILE ALL;

  RECOVER DATABASE;

  RELEASE CHANNEL c1;
  RELEASE CHANNEL c2;
  RELEASE CHANNEL c3;
  RELEASE CHANNEL c4;
}

7. Migrate tempfile to ASM:
The tempfile has to be manually migrated to ASM.

[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 10:12:42 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name, bytes from v$tempfile;

SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;
Tablespace created.

SQL> alter database default temporary tablespace temp1;
Database altered.

SQL> drop tablespace temp including contents;
Tablespace dropped.

SQL> create temporary tablespace temp tempfile SIZE 100M extent management local uniform size 1M;
Tablespace created.

SQL> alter database default temporary tablespace temp;
Database altered.

SQL> drop tablespace temp1 including contents;
Tablespace dropped.

SQL> select name from v$tempfile;
NAME
-----------------------------------------------
+DATA/db10g/tempfile/temp.264.596370217

8. Migrate and drop the old Online Redo Logs to ASM:
Drop and recreate the redo groups into ASM to migrate them to ASM Diskgroup.

9. DELETE THE OLD DATAFILES USING RMAN.
This way, it will also clear out the datafiles entry from controlfile.

[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 11:22:33 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> connect target
connected to target database: DB10G (DBID=4283639931)
RMAN> run {
2> DELETE COPY OF DATABASE;
3> }
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=151 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=153 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=138 devtype=DISK

List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
6 1 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/system01.dbf
7 2 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/undotbs01.dbf
8 3 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/sysaux01.dbf
9 4 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/users01.dbf

Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy filename=/home/oracle/oradata/db10g/system01.dbf recid=6 stamp=596369439
deleted datafile copy
datafile copy filename=/home/oracle/oradata/db10g/undotbs01.dbf recid=7 stamp=596369439
deleted datafile copy
datafile copy filename=/home/oracle/oradata/db10g/sysaux01.dbf recid=8 stamp=596369440
deleted datafile copy
datafile copy filename=/home/oracle/oradata/db10g/users01.dbf recid=9 stamp=596369440
Deleted 4 objects

RMAN> exit

10. REMOVE THE OLD ONLINE REDO LOGS FILES PHYSICALLY:

[oracle@node1-pub oracle]$ rm /home/oracle/oradata/db10g/redo*.log

11. Enable the block change tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;





Leave a Reply