Step by Step Migratation from NonASM to ASM database using RMAN
Before stoping the database, we must know datafile,tempfile,logfile location .
SQL> select name from v$datafile;
NAME
-------------------------------
/u02/PROD/data/system04.dbf
/u02/PROD/data/system05.dbf
/u02/PROD/data/ctxd01.dbf
/u02/PROD/data/owad01.dbf
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u02/PROD/data/cntrl01.dbf
/u02/PROD/data/cntrl02.dbf
/u02/PROD/data/cntrl03.dbf
SQL>select GROUP#,MEMBER from v$logfile;
GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
3
/u02/PROD/data/redo03a.dbf
3
/u02/PROD/data/redo03b.dbf
2
/u02/PROD/data/redo02a.dbf
SQL> select FILE#,name from v$tempfile;
FILE#
----------
NAME
--------------------------------------------------------------------------------
1
/u02/PROD/data/temp04.dbf
2
/u02/PROD/data/temp03.dbf
3
/u02/PROD/data/temp02.dbf
SQL> select name from v$asm_diskgroup where name='ASMPROD';
NAME
------------------------------
ASMPROD
1 rows selected.
SQL> exit
then Shutdown your database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 6263357440 bytes
Fixed Size 2171304 bytes
Variable Size 4244639320 bytes
Database Buffers 1996488704 bytes
Redo Buffers 20058112 bytes
Database mounted.
SQL>exit
Take Full backup of your Database using RMAN:-
[oraprod@prod02 ~]$ rman target / catalog rman/muthu@RMAN
Recovery Manager: Release 11.1.0.7.0 - Production on Sat Dec 29 23:22:21 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: PROD (DBID=3877050111, not open)
connected to recovery catalog database
RMAN> backup as compressed backupset incremental level 0 cumulative tag LEVEL0_BACKUP filesperset 4 format '/l01/backup/PROD/level0/%d_LVL0_%T_%s' database;
Starting backup at 29-DEC-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1983 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1981 device type=DISK
input datafile file number=00396 name=/u02/PROD/data/a_summ01.dbf
channel ORA_DISK_4: starting piece 1 at 29-DEC-12
channel ORA_DISK_5: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_5: specifying datafile(s) in backup set
input datafile file number=00038 name=/u02/PROD/data/a_media04.dbf
input datafile file number=00085 name=/u02/PROD/data/sysaux28.dbf
channel ORA_DISK_5: backup set complete, elapsed time: 00:04:43
channel ORA_DISK_6: finished piece 1 at 30-DEC-12
piece handle=/l01/backup/PROD/level0/PROD_LVL0_20121230_19000 tag=LEVEL0_BACKUP comment=NONE
channel ORA_DISK_6: backup set complete, elapsed time: 00:04:42
Finished backup at 30-DEC-12
RMAN>exit
[oraprod@prod02 dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 30 00:36:55 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 6263357440 bytes
Fixed Size 2171304 bytes
Variable Size 4244639320 bytes
Database Buffers 1996488704 bytes
Redo Buffers 20058112 bytes
SQL> exit
[oraprod@prod02 ~]$
Chnage below parameter on initPROD.ora file
Old value : *.control_files='/u02/PROD/data/cntrl01.dbf','/u02/PROD/data/cntrl02.dbf','/u02/PROD/data/cntrl03.dbf'
New Value : *.control_files='+DBIMP02','+DBIMP02'
[oraprod@prod02 ~]$ rman target /
Recovery Manager: Release 11.1.0.7.0 - Production on Sun Dec 30 00:37:35 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: PROD (not mounted)
RMAN> restore controlfile from '/u02/PROD/data/cntrl01.dbf';
Starting restore at 30-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1977 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+ASMPROD/prod/controlfile/current.256.803349501
output file name=+ASMPROD/prod/controlfile/current.257.803349503
Finished restore at 30-DEC-12
RMAN>exit
[oraprod@prod02 dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 30 00:36:55 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance
SQL> alter database mount;
Database altered.
SQL> exit
[oraprod@prod02 ~]$ rman target /
Recovery Manager: Release 11.1.0.7.0 - Production on Sun Dec 30 00:37:35 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: PROD (not mounted)
RMAN>backup as copy database format '+ASMPROD';
Starting backup at 30-DEC-12
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1977 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=1975 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00030 name=/u02/PROD/data/sysaux03.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00034 name=/u02/PROD/data/undotbs5.dbf
output file name=+ASMPROD/prod/datafile/apps_ts_queues.364.803353979 tag=TAG20121230T004026 RECID=133 STAMP=803354005
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
output file name=+ASMPROD/prod/datafile/system.361.803353939 tag=TAG20121230T004026 RECID=132 STAMP=803354002
channel ORA_DISK_6: datafile copy complete, elapsed time: 00:01:07
Finished backup at 30-DEC-12
RMAN>switch database to copy;
datafile 1 switched to datafile copy "+ASMPROD/prod/datafile/system.351.803353795"
datafile 2 switched to datafile copy "+ASMPROD/prod/datafile/system.352.803353795"
datafile 3 switched to datafile copy "+ASMPROD/prod/datafile/system.353.803353795"
datafile 4 switched to datafile copy "+ASMPROD/prod/datafile/system.354.803353795"
datafile 405 switched to datafile copy "+ASMPROD/prod/datafile/apps_ts_tx_idx.287.803351559"
datafile 406 switched to datafile copy "+ASMPROD/prod/datafile/apps_ts_tx_idx.288.803351655"
datafile 407 switched to datafile copy "+ASMPROD/prod/datafile/apps_ts_seed.333.803353361"
RMAN> run {
set newname for tempfile 1 to '+ASMPROD';
set newname for tempfile 2 to '+ASMPROD';
set newname for tempfile 3 to '+ASMPROD';
set newname for tempfile 4 to '+ASMPROD';
set newname for tempfile 5 to '+ASMPROD';
set newname for tempfile 6 to '+ASMPROD';
set newname for tempfile 7 to '+ASMPROD';
set newname for tempfile 8 to '+ASMPROD';
set newname for tempfile 9 to '+ASMPROD';
switch tempfile all;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12>
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +ASMPROD in control file
renamed tempfile 8 to +ASMPROD in control file
renamed tempfile 9 to +ASMPROD in control file
RMAN>exit
[oraprod@prod02 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 30 02:05:02 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+ASMPROD/prod/datafile/system.351.803353795
+ASMPROD/prod/datafile/system.352.803353795
+ASMPROD/prod/datafile/system.353.803353795
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+ASMPROD
+ASMPROD
+ASMPROD
SQL> alter database open;
Database altered.
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
GROUP# MEMBER STATUS
---------- ---------------------------------------- ----------------
3 /u02/PROD/data/redo03a.dbf INACTIVE
3 /u02/PROD/data/redo03b.dbf INACTIVE
2 /u02/PROD/data/redo02a.dbf INACTIVE
2 /u02/PROD/data/redo02b.dbf INACTIVE
1 /u02/PROD/data/redo01a.dbf CURRENT
1 /u02/PROD/data/redo01b.dbf CURRENT
4 /u02/PROD/data/redo04a.dbf UNUSED
4 /u02/PROD/data/redo04b.dbf UNUSED
5 /u02/PROD/data/redo05a.dbf UNUSED
5 /u02/PROD/data/redo05b.dbf UNUSED
6 /u02/PROD/data/redo06a.dbf UNUSED
GROUP# MEMBER STATUS
---------- ---------------------------------------- ----------------
6 /u02/PROD/data/redo06b.dbf UNUSED
12 rows selected.
SQL> ALTER DATABASE ADD LOGFILE MEMBER '+ASMPROD' TO GROUP 1;
Database altered.
SQL> /
Database altered.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u02/PROD/data/redo01a.dbf';
Database altered.
SQL> set line 200
SQL> col member for a40
SQL> SELECT a.group#, b.member, a.status FROM v$log a, v$logfile b WHERE a.group#=b.group#;
GROUP# MEMBER STATUS
---------- ---------------------------------------- ----------------
2 +ASMPROD/prod/onlinelog/group_2.387.8 CURRENT
03355621
1 +ASMPROD/prod/onlinelog/group_1.383.8 INACTIVE
03355199
1 +ASMPROD/prod/onlinelog/group_1.384.8 INACTIVE
03355211
2 +ASMPROD/prod/onlinelog/group_2.388.8 CURRENT
03355625
GROUP# MEMBER STATUS
---------- ---------------------------------------- ----------------
3 +ASMPROD/prod/onlinelog/group_3.389.8 INACTIVE
03355633
3 +ASMPROD/prod/onlinelog/group_3.390.8 INACTIVE
03355635
6 rows selected.
SQL>shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oraprod@prod02 ~]$ vi /u01/PROD/oracle/db/tech_st/11.1.0/db_1/dbs/initPROD.ora
Change below parameter on initPROD.ora file
Old value : *.control_files='+DBIMP02','+DBIMP02'
New Value : *.control_files='+ASMPROD/prod/controlfile/current.256.803349501','+ASMPROD/prod/controlfile/current.257.803349503'
[oraprod@prod02 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.1.0.7.0 - Production on Sun Dec 30 02:35:28 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 6263357440 bytes
Fixed Size 2171304 bytes
Variable Size 4244639320 bytes
Database Buffers 1996488704 bytes
Redo Buffers 20058112 bytes
Database mounted.
Database opened.
SQL>
