Migrating a Database to ASM (using RMAN)

Assumptions

1) ASM instance is up and running

2) Diskgroup +DATADG already exists under ASM where migration is to be done.

3) The database (MIG) to be migrated is present under /u01/oradata/mig filesystem

Step -1

Change parameters for movement of controlfiles, datafiles and flash recovery Area (if any)

Login as SYS and alter the following parameters

SQL> alter system set control_files='+DATADG' scope=spfile;
System altered.

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

System altered.

SQL> alter system set db_recovery_file_dest='+DATADG' scope=spfile;

System altered.

Step - 2

Shutdown and startup the database in NOMOUNT state to reflect the changed parameters.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 536870912 bytes

Fixed Size 1268508 bytes

Variable Size 150996196 bytes

Database Buffers 381681664 bytes

Redo Buffers 2924544 bytes

Step - 3

Move Controlfile to ASM diskgroup (+DATADG)

Connect to target database using RMAN

[oracle@grid ~]$ rman

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Aug 16 18:42:05 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: mig (not mounted)

RMAN> restore controlfile from '/u01/oradata/mig/control01.ctl';

Starting restore at 16-AUG-09

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: copied control file copy

output filename=+DATADG/mig/controlfile/current.272.695068991

Finished restore at 16-AUG-09

RMAN> alter database mount;

Step - 4

Mount Oracle Database and take a backup (copy) of database on +DATADG diskgroup

RMAN> alter database mount;

database mounted released channel: ORA_DISK_1

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

Starting backup at 16-AUG-09

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/u01/oradata/mig/system01.dbf

output filename=+DATADG/mig/datafile/system.270.695069105 tag=TAG20090816T184504 recid=1 stamp=695069138

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

channel ORA_DISK_1: starting datafile copy

input datafile fno=00003 name=/u01/oradata/mig/sysaux01.dbf

output filename=+DATADG/mig/datafile/sysaux.271.695069141 tag=TAG20090816T184504 recid=2 stamp=695069163

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

channel ORA_DISK_1: starting datafile copy

input datafile fno=00002 name=/u01/oradata/mig/undotbs01.dbf

output filename=+DATADG/mig/datafile/undotbs1.269.695069165 tag=TAG20090816T184504 recid=3 stamp=695069167

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

channel ORA_DISK_1: starting datafile copy

input datafile fno=00004 name=/u01/oradata/mig/users01.dbf

output filename=+DATADG/mig/datafile/users.268.695069169 tag=TAG20090816T184504 recid=4 stamp=695069168

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

channel ORA_DISK_1: starting datafile copy

copying current control file

output filename=+DATADG/mig/controlfile/backup.276.695069169 tag=TAG20090816T184504 recid=5 stamp=695069170

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

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 16-AUG-09

channel ORA_DISK_1: finished piece 1 at 16-AUG-09

piece handle=+DATADG/mig/backupset/2009_08_16/nnsnf0_tag20090816t184504_0.275.695069173 tag=TAG20090816T184504 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 16-AUG-09

Step - 5

Switch database to copy (This will update the new datafile locations in controlfile)

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATADG/mig/datafile/system.270.695069105"

datafile 2 switched to datafile copy "+DATADG/mig/datafile/undotbs1.269.695069165"

datafile 3 switched to datafile copy "+DATADG/mig/datafile/sysaux.271.695069141"

datafile 4 switched to datafile copy "+DATADG/mig/datafile/users.268.695069169"

RMAN>

Step - 5

Connect as SYS and perform recovery

oracle@grid ~]$ sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 16 18:46:44 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Enter user-name: / as sysdba

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database using backup controlfile;

ORA-00279: change 473171 generated at 08/16/2009 18:38:34 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/10.2.0/dbs/arch1_1_695068279.dbf

ORA-00280: change 473171 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/oradata/mig/redo01.log

Log applied.

Media recovery complete.

SQL>

Step-6

OPEN the recovered database with RESETLOGS option.

SQL> alter database open resetlogs;

Database altered.

Step-7

Drop old tempfile and create new tempfile in existing temp tablespace on +DATADG diskgroup.

SQL> select file_name from dba_temp_files;

FILE_NAME

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

/u01/oradata/mig/temp01.dbf

SQL> alter database tempfile '/u01/oradata/mig/temp01.dbf' drop including datafiles;

Database altered.

SQL> select file_name from dba_temp_files;

no rows selected

SQL> alter tablespace temp add tempfile '+DATADG' size 100M;

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME

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

+DATADG/mig/tempfile/temp.274.695069479

Step - 8

Recreate all online redolog groups on +DATADG diskgroup (Switch logfiles to change the CURRENT status of online redolog)

SQL> select member from v$logfile;

MEMBER

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

/u01/oradata/mig/redo03.log

/u01/oradata/mig/redo02.log

/u01/oradata/mig/redo01.log

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 CURRENT

2 UNUSED

3 UNUSED

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter system checkpoint;

System altered.

SQL> alter database add logfile group 3;

Database altered.

SQL> select member from v$logfile;

MEMBER

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

+DATADG/mig/onlinelog/group_3.279.695070243

SQL> SQL> select group#,status from v$log;

GROUP# STATUS

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

1 CURRENT

2 UNUSED

3 UNUSED

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 INACTIVE

2 CURRENT

3 UNUSED

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1;

Database altered.

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 UNUSED

2 CURRENT

3 UNUSED

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> SQL> alter system checkpoint;

System altered.

SQL> select group#,status from v$log;

GROUP# STATUS

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

1 CURRENT

2 INACTIVE

3 UNUSED

SQL> SQL> alter database drop logfile group 2;

Database altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select member from v$logfile;

MEMBER

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

+DATADG/mig/onlinelog/group_3.279.695070243

+DATADG/mig/onlinelog/group_1.280.695070339

SQL> alter database add logfile group 2;

Database altered.

SQL> select member from v$logfile;

MEMBER

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

+DATADG/mig/onlinelog/group_3.279.695070243

+DATADG/mig/onlinelog/group_2.281.695070397

+DATADG/mig/onlinelog/group_1.280.695070339

SQL>

Step - 9

Recreate SPFILE on +DATADG ASM diskgroup

SQL> show parameter spfile;

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/10.2.0/dbs/spfilemig.ora

SQL> create pfile='/u01/initmig.ora' from spfile;

File created.

SQL> create spfile from pfile='/u01/initmig.ora'

SQL> create spfile='+DATADG' from pfile='/u01/initmig.ora'

2 ;

File created.

SQL>

Step - 10

Delete backup copy from RMAN

RMAN> delete noprompt force copy;

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

specification does not match any archive log in the recovery catalog

List of Datafile Copies

Key File S Completion Time Ckp SCN Ckp Time Name

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

6 1 A 16-AUG-09 473171 16-AUG-09 /u01/oradata/mig/system01.dbf

7 2 A 16-AUG-09 473171 16-AUG-09 /u01/oradata/mig/undotbs01.dbf

8 3 A 16-AUG-09 473171 16-AUG-09 /u01/oradata/mig/sysaux01.dbf

9 4 A 16-AUG-09 473171 16-AUG-09 /u01/oradata/mig/users01.dbf

List of Control File Copies

Key S Completion Time Ckp SCN Ckp Time Name

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

5 A 16-AUG-09 473171 16-AUG-09 +DATADG/mig/controlfile/backup.276.695069169

deleted datafile copy

datafile copy filename=/u01/oradata/mig/system01.dbf recid=6 stamp=695069189

deleted datafile copy

datafile copy filename=/u01/oradata/mig/undotbs01.dbf recid=7 stamp=695069189

deleted datafile copy

datafile copy filename=/u01/oradata/mig/sysaux01.dbf recid=8 stamp=695069189

deleted datafile copy

datafile copy filename=/u01/oradata/mig/users01.dbf recid=9 stamp=695069190

deleted control file copy

control file copy filename=+DATADG/mig/controlfile/backup.276.695069169 recid=5 stamp=695069170

Deleted 5 objects

RMAN>

--Rishi