Oracle Apps DBA

My photo
More than 5+ Years of IT Experience in administering Enterprise wide Multi Vendor UNIX Servers, Oracle Databases (8i to 11gR2), Middle-tiers, Applications and Clusters. I am a Sun Certified System Administrator (SCSA) for Solaris 10, oracle 10g database certified associate(OCA), Oracle 10g Database certified professional (OCP) and Oracle E-Business suite R12 certified professionl(OCP)

Oracle Database and Applications

Sunday, 20 February 2011

Migrate 11.2.0 database from UFS to ASM

Hi,

This post will illustrate steps for migrating datafiles from UFS (or any other file system) to ASM Disk groups.

Step1:

Perform Full Rman Database backup:

Step2:


Shut down and startup database in mount;




SQL> startup mount
ORACLE instance started.

Total System Global Area 1720844288 bytes
Fixed Size 2159024 bytes
Variable Size 1241517648 bytes
Database Buffers 469762048 bytes
Redo Buffers 7405568 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options



Step 3:


Connect database target using rman utility and use command COPY database.




bash-3.00$ rman

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Oct 27 13:01:51 2010

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

RMAN> connect target\

target database Password:
connected to target database: DB1 (DBID=1326750269, not open)

RMAN> BACKUP AS COPY DATABASE FORMAT '+ASMDATA1';

Starting backup at 27-OCT-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/11g_Home/oradata/db1/system01.dbf
output file name=+ASMDATA1/db1/datafile/system.257.733496553 tag=TAG20101027T130232 RECID=2 STAMP=733496609
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/11g_Home/oradata/db1/sysaux01.dbf
output file name=+ASMDATA1/db1/datafile/sysaux.258.733496619 tag=TAG20101027T130232 RECID=3 STAMP=733496663
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/11g_Home/oradata/db1/example01.dbf
output file name=+ASMDATA1/db1/datafile/example.259.733496673 tag=TAG20101027T130232 RECID=4 STAMP=733496690
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/11g_Home/oradata/db1/undotbs01.dbf
output file name=+ASMDATA1/db1/datafile/undotbs1.260.733496699 tag=TAG20101027T130232 RECID=5 STAMP=733496706
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+ASMDATA1/db1/controlfile/backup.261.733496707 tag=TAG20101027T130232 RECID=6 STAMP=733496708
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/11g_Home/oradata/db1/users01.dbf
output file name=+ASMDATA1/db1/datafile/users.262.733496711 tag=TAG20101027T130232 RECID=7 STAMP=733496710
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-OCT-10

RMAN>



Step 4:

Use command switch database to copy to duplicate your files



RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+ASMDATA1/db1/datafile/system.257.733496553"
datafile 2 switched to datafile copy "+ASMDATA1/db1/datafile/sysaux.258.733496619"
datafile 3 switched to datafile copy "+ASMDATA1/db1/datafile/undotbs1.260.733496699"
datafile 4 switched to datafile copy "+ASMDATA1/db1/datafile/users.262.733496711"
datafile 5 switched to datafile copy "+ASMDATA1/db1/datafile/example.259.733496673"



Step 5:

Use set new name for tempfile:



RMAN> run {
2> set newname for tempfile 1 to '+ASMDATA1';
3> set newname for tempfile 2 to '+ASMDATA1';
4> }
executing command: SET NEWNAME

executing command: SET NEWNAME

RMAN>



Step 6 :

open database with alter database command and verify new location of datafiles




RMAN> ALTER DATABASE OPEN;

database opened









SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+ASMDATA1/db1/datafile/system.257.733496553
+ASMDATA1/db1/datafile/sysaux.258.733496619
+ASMDATA1/db1/datafile/undotbs1.260.733496699
+ASMDATA1/db1/datafile/users.262.733496711
+ASMDATA1/db1/datafile/example.259.733496673

SQL>



Thanks for reading.....

No comments: