Migrating Non – ASM database to ASM

Migrating Non – ASM database to ASM

Target database is ESADEMO and is in mount stage
This migration is performed using Recovery Manager (RMAN) even if you are not using RMAN for your primary backup and recovery strategy.

Limitation on ASM Migration with Transportable Tablespaces
The procedure described here does not work for transportable (foreign) tablespaces. Such tablespaces needs to be made read-write and imported into the database, before they can be migrated into ASM using this procedure.

Determine Names of Database Files and control files

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 26 12:10:19 2007

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

SYS@ ESADEMO>select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\oracle\product\10.2.0\oradata\esademo\esademo\system01.dbf
D:\oracle\product\10.2.0\oradata\esademo\esademo\undotbs01.dbf
D:\oracle\product\10.2.0\oradata\esademo\esademo\sysaux01.dbf
D:\oracle\product\10.2.0\oradata\esademo\esademo\users01.dbf

SYS@ ESADEMO>show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string d:\oracle\product\10.2.0\orada
ta\esademo\esademo\control01.c
tl, d:\oracle\product\10.2.0\o
radata\esademo\esademo\control
02.ctl, d:\oracle\product\10.2
.0\oradata\esademo\esademo\con
trol03.ctl
SYS@ ESADEMO>


Disk-Based Migration of a Database to ASM

If you have enough disk space that you can have both your entire non-ASM database and your ASM disk group on disk at the same time, you can do the migration directly without using tapes.
The procedure differs slightly between primary and standby databases. A number of the steps described in this procedure apply only in one or the other case. There are also a few steps where the procedure is different depending upon whether you are using a recovery catalog. The steps that vary are identified as necessary in the description of the process.
The process described in this section is intended to minimize downtime for your database. The example assumes that the destination ASM disk group for database files is +ASM_NEW.During the migration process all flashback logs are discarded. As a result, any guaranteed restore points in the database become unusable. You should drop all guaranteed restore points before performing the migration.

To perform the migration, carry out the following steps:
Step – 1 Back up your database files as copies to the ASM disk group.
C:\>set ORACLE_SID=+ASM

C:\>asmcmd
ASMCMD> ls
ASM/
ASM_NEW/
ASMCMD> cd ASM_NEW
ASMCMD> mkdir ESADEMO
ASMCMD> cd ESADEMO
ASMCMD> pwd
+ASM_NEW/ESADEMO
ASMCMD>


C:\>set oracle_sid=ESADEMO

C:\>rman target / catalog rman/password@rman

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Sep 26 12:28:14 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ESADEMO (DBID=1806048469, not open)
connected to recovery catalog database

RMAN> BACKUP AS COPY INCREMENTAL LEVEL 0 DATABASE FORMAT '+ASM_NEW' TAG 'ORA_ASM_MIGRATION';
Starting backup at 26-SEP-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ESADEMO\ESADEMO\SYSTEM01.DBF
output filename=+ASM_NEW/esademo/datafile/system.258.634307507 tag=ORA_ASM_MIGRATION recid=1 stamp=634307554
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ESADEMO\ESADEMO\SYSAUX01.DBF
output filename=+ASM_NEW/esademo/datafile/sysaux.257.634307563 tag=ORA_ASM_MIGRATION recid=2 stamp=634307586
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ESADEMO\ESADEMO\UNDOTBS01.DBF
output filename=+ASM_NEW/esademo/datafile/undotbs1.256.634307589 tag=ORA_ASM_MIGRATION recid=3 stamp=634307593
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ESADEMO\ESADEMO\USERS01.DBF
output filename=+ASM_NEW/esademo/datafile/users.259.634307597 tag=ORA_ASM_MIGRATION recid=4 stamp=634307597
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+ASM_NEW/esademo/controlfile/backup.260.634307599 tag=ORA_ASM_MIGRATION recid=5 stamp=634307600
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 26-SEP-07
RMAN>


Step – 2 Create a copy of the SPFILE in the ASM disk group. In this example, the SPFILE for the migrated database will be stored as +ASM_NEW/spfile.
RMAN> run {
2> BACKUP AS BACKUPSET SPFILE;
3> RESTORE SPFILE TO "+ASM_NEW/spfile";
4> }

Starting backup at 26-SEP-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
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 26-SEP-07
channel ORA_DISK_1: finished piece 1 at 26-SEP-07
piece handle=D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\07ISTI67_1_1 tag=TAG20070926T125327 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 26-SEP-07

Starting restore at 26-SEP-07
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring SPFILE
output filename=+ASM_NEW/spfile
channel ORA_DISK_1: reading from backup piece D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\07ISTI67_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\07ISTI67_1_1 tag=TAG20070926T125327
channel ORA_DISK_1: restore complete, elapsed time: 00:00:17
Finished restore at 26-SEP-07


Step –3 At this point, if you want the option of easily returning the database to non-ASM storage later, make copies of your current control file and all online logs. This command backs up the current control file to a non-ASM location:
RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT 'd:\oracle\product\10.2.0\oradata\ESADEMO\pre-ASM-controfile.cf';

Starting backup at 26-SEP-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ESADEMO\PRE-ASM-CONTROFILE.CF tag=TAG20070926T130231 recid=6 stamp=634309352
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 26-SEP-07

RMAN>

Step – 4 now create an init.ora specifying the location of the new SPFILE, and start the instance with it. For example, create /tmp/pfile.ora with the following contents:

SPFILE=+ASM_NEW/spfile

Now start the database in NOMOUNT

SYS@ ESADEMO>shu immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SYS@ ESADEMO>startup nomount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 62915940 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes

SYS@ ESADEMO>show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ----------------
spfile string +ASM_NEW/spfile
SYS@ ESADEMO>


Step –5 The next step is to migrate the control file to ASM.
In SQL*Plus, change the CONTROL_FILES initialization parameter using the following command:
SYS@ ESADEMO>alter system set control_files='+ASM_NEW/ct1.f','+ASM_NEW /ct2.f' scope=spfile sid='*';

System altered.

SYS@ ESADEMO>

Step – 6 Shut down and startup in NOMOUNT again, so that the changed parameters take effect. (The CONTROL_FILES parameter change only takes effect upon a restart because it is a static parameter.)

Then, use RMAN to actually create the new control files in ASM. For example, assuming that one of your original control file locations was /private/ct1.f, use the following command

C:\>rman target / catalog rman/password@rman

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Sep 26 13:19:31 2007

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

connected to target database: ESADEMO (not mounted)
connected to recovery catalog database

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup nomount;

Oracle instance started

Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 62915940 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes

RMAN> restore controlfile from 'd:\oracle\product\10.2.0\oradata\ESADEMO\pre-ASM-controfile.cf';

Starting restore at 26-SEP-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+ASM_NEW/esademo/controlfile/ct1.f
output filename=+ASM_NEW/esademo/controlfile/ct2.f
Finished restore at 26-SEP-07

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+ASM_NEW/esademo/datafile/system.258.634307507"
datafile 2 switched to datafile copy "+ASM_NEW/esademo/datafile/undotbs1.256.634307589"
datafile 3 switched to datafile copy "+ASM_NEW/esademo/datafile/sysaux.257.634307563"
datafile 4 switched to datafile copy "+ASM_NEW/esademo/datafile/users.259.634307597"

RMAN> recover database;

Starting recover at 26-SEP-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

starting media recovery

archive log thread 1 sequence 1 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\ESADEMO\ESADEMO\REDO03.LOG
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ESADEMO\ESADEMO\REDO03.LOG thread=1 sequence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-SEP-07

RMAN> alter database open resetlogs;;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>


SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 26 13:39:33 2007

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

SYS@ ESADEMO>select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
esademo OPEN


SYS@ ESADEMO>select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+ASM_NEW/esademo/datafile/system.258.634307507
+ASM_NEW/esademo/datafile/undotbs1.256.634307589
+ASM_NEW/esademo/datafile/sysaux.257.634307563
+ASM_NEW/esademo/datafile/users.259.634307597

SYS@ ESADEMO>

Step –7 moving online logs to ASM disk

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SYS@ ESADEMO>declare
2 cursor rlc is
3 select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
4 from v$log
5 union
6 select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
7 from v$standby_log
8 order by 1;
9 stmt varchar2(2048);
10 swtstmt varchar2(1024) := 'alter system switch logfile';
11 ckpstmt varchar2(1024) := 'alter system checkpoint global';
12 begin
13 for rlcRec in rlc loop
14 if (rlcRec.srl = 'YES') then
15 stmt := 'alter database add standby logfile thread ' ||
16 rlcRec.thr || ' ''+ASM_NEW'' size ' ||
17 rlcRec.bytes_k || 'K';
18 execute immediate stmt;
19 stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
20 execute immediate stmt;
21 else
22 stmt := 'alter database add logfile thread ' ||
23 rlcRec.thr || ' ''+ASM_NEW'' size ' ||
24 rlcRec.bytes_k || 'K';
25 execute immediate stmt;
26 begin
27 stmt := 'alter database drop logfile group ' || rlcRec.grp;
28 dbms_output.put_line(stmt);
29 execute immediate stmt;
30 exception
31 when others then
32 execute immediate swtstmt;
33 execute immediate ckpstmt;
34 execute immediate stmt;
35 end;
36 end if;
37 end loop;
38 end;
39 /

PL/SQL procedure successfully completed.

SYS@ ESADEMO>select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+ASM_NEW/esademo/onlinelog/group_3.268.634311897
+ASM_NEW/esademo/onlinelog/group_2.266.634311889
+ASM_NEW/esademo/onlinelog/group_1.265.634311883
+ASM_NEW/esademo/onlinelog/group_5.267.634311877

SYS@ ESADEMO>


Step –7 moving tempfiles to ASM disk

SYS@ ESADEMO>select name from v$tempfile;

NAME
------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ESADEMO\ESADEMO\TEMP01.DBF

RMAN> run {
2> set newname for tempfile 1 to '+ASM_NEW/temp01.dbf';
3> switch tempfile all;
4> }

executing command: SET NEWNAME

renamed temporary file 1 to +ASM_NEW/temp01.dbf in control file
starting full resync of recovery catalog
full resync complete

RMAN>

SYS@ ESADEMO>select name from v$tempfile;

NAME
----------------------------------------------
+ASM_NEW/temp01.dbf