How to Migrate a Standalone Database to ASM

The following example shows how to migrate a standalone database to ASM. My environment uses an ASM instance which is version 11.1, ASMLib, and a 10.2.0.4 database (my Grid Control database) on Linux.

I will move the EM database to the DATA2 diskgroup:

ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Name
MOUNTED  EXTERN  N         512   4096  1048576     25023    21597                0           21597              0  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     25023    24930                0           24930              0  DATA2/

In order to migrate the database, it must be in ARCHIVELOG mode. If not in ARCHIVELOG mode, place the database in it now

$ sqlplus "/ as sysdba"
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Current log sequence           13

SQL> alter system set log_archive_format='emrep_%t_%s_%r.arch' scope=spfile;

System altered.

SQL> alter system set log_archive_dest_1='LOCATION=/u02/oracle/arch/' scope=spfile;

System altered.

SQL> startup mount;
ORACLE instance started.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/oracle/arch/
Oldest online log sequence     13
Next log sequence to archive   16
Current log sequence           16

SQL> alter system switch logfile;

System altered.

SQL> alter system archive log current;

System altered.

SQL> ! ls -lart /u02/oracle/arch
total 28506
drwxr-xr-x  2 oracle dba     4096 Apr 16 15:31 lost+found
drwxrwxr-x  8 oracle dba     4096 Sep 14 12:39 ..
-rw-r-----  1 oracle dba 29053440 Sep 17 10:43 emrep_1_16_665591431.arch
drwxr-xr-x  3 oracle dba    24576 Sep 17 10:44 .
-rw-r-----  1 oracle dba   103424 Sep 17 10:44 emrep_1_17_665591431.arch

Backup the Database to the Diskgroup(s)

$ rman target=/
RMAN> backup as copy database format '+DATA2';

Output should be similar to the following:

Starting backup at 17-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/emrep/mgmt.dbf
output filename=+DATA2/emrep/datafile/mgmt_tablespace.256.665690321 tag=TAG20080917T175835 recid=1 stamp=665690971
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:10:57
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/emrep/system01.dbf
output filename=+DATA2/emrep/datafile/system.257.665690973 tag=TAG20080917T175835 recid=2 stamp=665691068
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/emrep/undotbs01.dbf
output filename=+DATA2/emrep/datafile/undotbs1.258.665691079 tag=TAG20080917T175835 recid=3 stamp=665691129
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/emrep/sysaux01.dbf
output filename=+DATA2/emrep/datafile/sysaux.259.665691133 tag=TAG20080917T175835 recid=4 stamp=665691166
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/emrep/mgmt_ecm_depot1.dbf
output filename=+DATA2/emrep/datafile/mgmt_ecm_depot_ts.260.665691169 tag=TAG20080917T175835 recid=5 stamp=665691194
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/emrep/users01.dbf
output filename=+DATA2/emrep/datafile/users.261.665691205 tag=TAG20080917T175835 recid=6 stamp=665691204
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=+DATA2/emrep/controlfile/backup.262.665691205 tag=TAG20080917T175835 recid=7 stamp=665691207
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 17-SEP-08
channel ORA_DISK_1: finished piece 1 at 17-SEP-08
piece handle=+DATA2/emrep/backupset/2008_09_17/nnsnf0_tag20080917t175835_0.263.665691209 tag=TAG20080917T175835 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 17-SEP-08

You should see entries in the alert log similar to:

Starting background process ASMB
ASMB started with pid=52, OS id=10868
Starting background process RBAL
RBAL started with pid=53, OS id=10885
Wed Sep 17 17:22:20 2008
SUCCESS: diskgroup DATA2 was mounted

During the backup you should see files created in the Diskgroup:

$ export ORACLE_SID=+ASM1
$ . oraenv
ASMCMD> cd DATA2/EMREP/DATAFILE
ASMCMD> ls
MGMT_TABLESPACE.256.665690321
SYSTEM.257.665690973

Switch the Database to the Copy on ASM

RMAN> shutdown immediate;

RMAN> startup mount;

database mounted

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA2/emrep/datafile/system.257.665690973"
datafile 2 switched to datafile copy "+DATA2/emrep/datafile/undotbs1.258.665691079"
datafile 3 switched to datafile copy "+DATA2/emrep/datafile/sysaux.259.665691133"
datafile 4 switched to datafile copy "+DATA2/emrep/datafile/users.261.665691205"
datafile 5 switched to datafile copy "+DATA2/emrep/datafile/mgmt_tablespace.256.665690321"
datafile 6 switched to datafile copy "+DATA2/emrep/datafile/mgmt_ecm_depot_ts.260.665691169"

RMAN> recover database;

Starting recover at 17-SEP-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

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

Finished recover at 17-SEP-08

RMAN> alter database open;

You should see the datafiles assigned to their new locations:

SQL> col name format a60
SQL> select name from v$datafile;

NAME
------------------------------------------------------------
+DATA2/emrep/datafile/system.257.665690973
+DATA2/emrep/datafile/undotbs1.258.665691079
+DATA2/emrep/datafile/sysaux.259.665691133
+DATA2/emrep/datafile/users.261.665691205
+DATA2/emrep/datafile/mgmt_tablespace.256.665690321
+DATA2/emrep/datafile/mgmt_ecm_depot_ts.260.665691169

6 rows selected.

Switch Tempfiles to the ASM Diskgroups

SQL> alter tablespace temp add tempfile '+DATA2' size 250M;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
------------------------------------------------------------
/u01/app/oracle/oradata/emrep/temp01.dbf
+DATA2/emrep/tempfile/temp.264.665692035

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

Database altered.

SQL> select name from v$tempfile;

NAME
------------------------------------------------------------
+DATA2/emrep/tempfile/temp.264.665692035

Switch the Online Redo Logs to the ASM diskgroup

SQL> alter database add logfile group 5 ('+DATA2','+DATA2') size 100M;

Database altered.

SQL> alter database add logfile group 6 ('+DATA2','+DATA2') size 100M;

Database altered.

SQL> alter database add logfile group 7 ('+DATA2','+DATA2') size 100M;

Database altered.

SQL> alter database add logfile group 8 ('+DATA2','+DATA2') size 100M;

Database altered.

You should see the Online Redo Logs created in the ASM instance:

ASMCMD> cd DATA2/EMREP/ONLINELOG
ASMCMD> ls
group_5.265.665693021
group_5.266.665693027
group_6.267.665693117
group_6.268.665693121
group_7.269.665693133
group_7.270.665693141
group_8.271.665693163
group_8.272.665693169

Switch through Logfile Groups and drop as possible

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

Afterwards, only the Online Redo Logs on the ASM diskgroup should remain:

SQL> select bytes/1024/1024, group#, thread#, status from v$log;

BYTES/1024/1024     GROUP#    THREAD# STATUS
--------------- ---------- ---------- ----------------
            100          5          1 ACTIVE
            100          6          1 ACTIVE
            100          7          1 ACTIVE
            100          8          1 CURRENT

SQL> select member from v$logfile;

MEMBER
---------------------------------------------------------------------------
+DATA2/emrep/onlinelog/group_5.265.665693021
+DATA2/emrep/onlinelog/group_5.266.665693027
+DATA2/emrep/onlinelog/group_6.267.665693117
+DATA2/emrep/onlinelog/group_6.268.665693121
+DATA2/emrep/onlinelog/group_7.269.665693133
+DATA2/emrep/onlinelog/group_7.270.665693141
+DATA2/emrep/onlinelog/group_8.271.665693163
+DATA2/emrep/onlinelog/group_8.272.665693169

Migrate the Controlfiles to ASM

SQL> alter system set control_files='+DATA2','+DATA2','+DATA2' scope=spfile;

System altered.

SQL> shutdown immediate;

$ rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Sep 17 19:03:37 2008

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

connected to target database (not started)

RMAN> startup nomount;

database is already started

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

Starting restore at 17-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DATA2/emrep/controlfile/current.273.665694281
output filename=+DATA2/emrep/controlfile/current.274.665694285
output filename=+DATA2/emrep/controlfile/current.275.665694285
Finished restore at 17-SEP-08

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> alter database open;

The control_files parameter should be updated via RMAN if you are using an spfile. If using a pfile, you will have to update this value yourself.

Optionally Migrate the Archive Log Files

SQL> alter system set log_archive_dest_1='LOCATION=+DATA2' scope=spfile;

System altered.

SQL> shutdown immediate;

SQL> startup

SQL> alter system archive log current;

Afterwards, you should see Archivelogs in the ASM instance:

$ asmcmd
ASMCMD> ls DATA2/EMREP/ARCHIVELOG
2008_09_17/

ASMCMD> cd DATA2/EMREP/ARCHIVELOG/2008_09_17
ASMCMD> ls
thread_1_seq_61.276.665694577

Drop the Copy of the Database

$ rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Sep 17 19:12:26 2008

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

connected to target database: EMREP (DBID=3888623110)

RMAN> list copy of database;

using target database control file instead of recovery catalog

List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
8       1    A 17-SEP-08       2607286    17-SEP-08       /u01/app/oracle/oradata/emrep/system01.dbf
9       2    A 17-SEP-08       2607286    17-SEP-08       /u01/app/oracle/oradata/emrep/undotbs01.dbf
10      3    A 17-SEP-08       2607286    17-SEP-08       /u01/app/oracle/oradata/emrep/sysaux01.dbf
11      4    A 17-SEP-08       2607286    17-SEP-08       /u01/app/oracle/oradata/emrep/users01.dbf
12      5    A 17-SEP-08       2607286    17-SEP-08       /u01/app/oracle/oradata/emrep/mgmt.dbf
13      6    A 17-SEP-08       2607286    17-SEP-08       /u01/app/oracle/oradata/emrep/mgmt_ecm_depot1.dbf

RMAN> drop copy of database;

Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/system01.dbf recid=8 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/undotbs01.dbf recid=9 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/sysaux01.dbf recid=10 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/users01.dbf recid=11 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/mgmt.dbf recid=12 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/mgmt_ecm_depot1.dbf recid=13 stamp=665691542
Deleted 6 objects

Obviously, you should distribute your files in a way that maximizes I/O throughput, etc. This example simply demonstrates mechanically how to perform the migration.

Labels: ASM, RMAN