Convert database from NON-ASM to ASM

CONVERTING DATABASE FROM NON-ASM to ASM
=======================================

1) Backup the database.

2) Shut down and startup the database in nomount mode.

$ sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jan 1 12:52:44 2010

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

Enter user-name: /as sysdba
Connected to an idle instance.



SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size 2127408 bytes
Variable Size 1610615248 bytes
Database Buffers 1.4361E+10 bytes
Redo Buffers 60174336 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


$

3) Copy the non-asm files to asm diskgroup.

[oracle]$ rman nocatalog

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jan 1 12:57:14 2010

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

RMAN> connect target

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

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

Starting backup at 01-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=383 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00379 name=/u10/oracle/data/TEST/undo01.dbf
output filename=+DATA/TEST/datafile/apps_undots1.257.707144559 tag=TAG20100101T130234 recid=1 stamp=707144573
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile fno=00353 name=/u10/oracle/data/TEST/system01.dbf
output filename=+DATA/TEST/datafile/system.258.707144581 tag=TAG20100101T130234 recid=2 stamp=707144595
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00406 name=/u10/oracle/data/TEST/a_txn_ind01.dbf
output filename=+DATA/TEST/datafile/apps_ts_tx_idx.259.707144605 tag=TAG20100101T130234 recid=3 stamp=707144616
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
......
.......
input datafile fno=00009 name=/u10/oracle/data/TEST/odm.dbf
output filename=+DATA/TEST/datafile/odm.298.707145081 tag=TAG20100101T130234 recid=42 stamp=707145080
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u10/oracle/data/TEST/owad01.dbf
output filename=+DATA/TEST/datafile/owapub.299.707145081 tag=TAG20100101T130234 recid=43 stamp=707145081
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-JAN-10

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/TEST/datafile/system.269.707144795"
datafile 2 switched to datafile copy "+DATA/TEST/datafile/system.270.707144811"
datafile 3 switched to datafile copy "+DATA/TEST/datafile/system.271.707144827"
.....
....
datafile 406 switched to datafile copy "+DATA/TEST/datafile/apps_ts_tx_idx.259.707144605"
datafile 407 switched to datafile copy "+DATA/TEST/datafile/apps_ts_seed.266.707144751"

RMAN>

4) Switch tempfiles

RMAN> run {
2> set newname for tempfile 1 to '+DATA';
3> set newname for tempfile 2 to '+DATA';
4> switch tempfile all;
5> }

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to +DATA in control file
renamed temporary file 2 to +DATA in control file

RMAN> Alter database open;

database opened

5) Now, restore controlfile from non-asm to asm

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area 1073741824 bytes

Fixed Size 2089336 bytes
Variable Size 427822728 bytes
Database Buffers 629145600 bytes
Redo Buffers 14684160 bytes

RMAN> restore controlfile to '+DATA' from '/u01/oracle/oradata/TEST/db/apps_st/data/cntrl01.dbf';

Starting restore at 01-JAN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=384 devtype=DISK

channel ORA_DISK_1: copied control file copy
Finished restore at 01-JAN-10

RMAN> shutdown immediate

Oracle instance shut down

RMAN> exit


Recovery Manager complete.


6) Using asmcmd find out the complete path to the controlfile and edit the pfile/spfile to the path in the asm.


7) Add multiple logfile members to a group and drop non-asm files.

select memeber from v$logfile;

/u01/oracle/oradata/TESTlog02a.dbf
/u01/oracle/oradata/TESTlog02b.dbf
/u01/oracle/oradata/TESTlog01a.dbf
/u01/oracle/oradata/TESTlog01b.dbf

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 1;

Database altered.

SQL> /

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DATA' TO GROUP 2;

Database altered.

SQL> /

Database altered.

/u01/oracle/oradata/TESTlog02a.dbf
/u01/oracle/oradata/TESTlog02b.dbf
/u01/oracle/oradata/TESTlog01a.dbf
/u01/oracle/oradata/TESTlog01b.dbf



SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02a.dbf';

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02b.dbf';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02b.dbf'
*
ERROR at line 1:
ORA-01609: log 2 is the current log for thread 1 - cannot drop members
ORA-00312: online log 2 thread 1: '/u01/oracle/oradata/TESTlog02b.dbf'
ORA-00312: online log 2 thread 1: '+DATA/TEST/onlinelog/group_2.305.707145955'
ORA-00312: online log 2 thread 1: '+DATA/TEST/onlinelog/group_2.306.707145963'


SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog01a.dbf';

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog02b.dbf';

Database altered.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/oradata/TESTlog01b.dbf';

Database altered.

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
1 ONLINE +DATA/TEST/onlinelog/group_1.303.707145913 NO
1 ONLINE +DATA/TEST/onlinelog/group_1.304.707145943 NO
2 ONLINE +DATA/TEST/onlinelog/group_2.305.707145955 NO
2 ONLINE +DATA/TEST/onlinelog/group_2.306.707145963 NO

8) Restart the database.