Database Migration to ASM

May 25, 2011 / By Santosh Nalgonda

Tags: , ,

I was working on a task of 10g Active-Passive cluster design where I was supposed to migrate a database to ASM from conventional filesystem. So I thought of writing this blog.

Below are the steps which I followed to move the database to ASM and then created a spfile in ASM diskgroup in the end.

1) Set the following parameters in init.ora file.

  • control_files=’+DISKGROUP’
  • db_create_file_dest=’+DISKGROUP’
  • db_recovery_file_dest=’+DISKGROUP’


2) Shutdown the database :

3) Start the database in nomount mode so that above parameters will come in effect:

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1761607680 bytes
Fixed Size 2084392 bytes
Variable Size 973079000 bytes
Database Buffers 671088640 bytes
Redo Buffers 115355648 bytes
SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 21
control_files string +DISKGROUP
SQL> show parameter db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DISKGROUP
db_recovery_file_dest_size big integer 10G

4) Connect to RMAN using control file as recovery catalog.

5) Restore controlfile in ASM diskgroup.

6) Mount the database.

[oradb@dbhost dbs]$ rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Mon May 23 12:43:59 2011

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

connected to target database: RMAN (not mounted)

RMAN> restore controlfile from '/opt/oracle/RMAN/control01.ctl';

Starting restore at 23-MAY-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=925 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DGDEVDATA01/rman/controlfile/current.843.751898669
Finished restore at 23-MAY-11

RMAN> sql "alter database mount";

sql statement: alter database mount
released channel: ORA_DISK_1

7) Take the copy of database in ASM diskgroup .

run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
backup as copy database format '+DGDEVDATA01';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}

8) Switch the datbase to this copy :

[oradb@dbhost ~]$ rman target=/

Recovery Manager: Release 10.2.0.4.0 - Production on Mon May 23 13:04:32 2011

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

connected to target database: RMAN (DBID=1570061233, not open)

RMAN> switch database to copy;

using target database control file instead of recovery catalog
datafile 1 switched to datafile copy "+DISKGROUP/rman/datafile/system.847.751898857"
datafile 2 switched to datafile copy "+DISKGROUP/rman/datafile/undotbs.844.751898857"
datafile 3 switched to datafile copy "+DISKGROUP/rman/datafile/sysaux.848.751898921"
datafile 4 switched to datafile copy "+DISKGROUP/rman/datafile/rman.846.751898857"
datafile 5 switched to datafile copy "+DISKGROUP/rman/datafile/avail.849.751898977"
datafile 6 switched to datafile copy "+DISKGROUP/rman/datafile/rman.845.751898857"

8) Perform incomplete database recovery unti database cancel.

9) Open the database with resetlogs.

[oradb@dbhost ~]$ sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 23 13:05:11 2011

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 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- ----------
RMAN MOUNTED

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 90832494 generated at 05/23/2011 12:36:32 needed for thread 1
ORA-00289: suggestion : /opt/oracle/RMAN/arch/RMAN1_74610_683634097.ARC
ORA-00280: change 90832494 for thread 1 is in sequence #74610

Specify log: {<ret>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

10) Drop and recreate the tempfiles.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/RMAN/temp01.dbf

SQL> alter database tempfile '/opt/oracle/RMAN/temp01.dbf' drop;

Database altered.

SQL> alter tablespace temp add tempfile size 2G;

Tablespace altered.

SQL> col filename format A70
SQL> set lin 250 pages 100

SQL> select tablespace_name, file_name, bytes from dba_temp_files;

TABLESPACE_NAME FILE_NAME BYTES
------------------------------ ------------------------------------------------------------ ----------
TEMP +DGDEVDATA01/rman/tempfile/temp.850.751900469 2147483648

11) Recreate the redo log groups in ASM diskgroup.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/opt/oracle/RMAN/redolog01.log
/opt/oracle/RMAN/redolog02.log
/opt/oracle/RMAN/redolog03.log
/opt/oracle/RMAN/stdby_redo11.rdo
/opt/oracle/RMAN/stdby_redo12.rdo
/opt/oracle/RMAN/stdby_redo13.rdo
/opt/oracle/RMAN/stdby_redo14.rdo

7 rows selected.

SQL> select group#,status ,bytes/(1024*1024) from v$log;

 GROUP# STATUS BYTES/(1024*1024)
---------- ---------------- -----------------
 1 CURRENT 100
 2 UNUSED 100
 3 UNUSED 100

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 '+DGDEVDATA01' size 100M;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3 '+DGDEVDATA01' size 100M;

Database altered.

SQL> select group#,status ,bytes/(1024*1024) from v$log;

 GROUP# STATUS BYTES/(1024*1024)
---------- ---------------- -----------------
 1 CURRENT 100
 2 UNUSED 100
 3 UNUSED 100

SQL> select member from v$logfile;

MEMBER
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/RMAN/redolog01.log
+DISKGROUP/rman/onlinelog/group_2.851.751900687
+DISKGROUP/rman/onlinelog/group_3.852.751900721
/opt/oracle/RMAN/stdby_redo11.rdo
/opt/oracle/RMAN/stdby_redo12.rdo
/opt/oracle/RMAN/stdby_redo13.rdo
/opt/oracle/RMAN/stdby_redo14.rdo

7 rows selected.

SQL> alter system switch logfile;

System altered.

SQL> select group#,status ,bytes/(1024*1024) from v$log;

 GROUP# STATUS BYTES/(1024*1024)
---------- ---------------- -----------------
 1 CURRENT 100
 2 INACTIVE 100
 3 ACTIVE 100

SQL> alter system checkpoint;

System altered.

SQL> select group#,status ,bytes/(1024*1024) from v$log;

 GROUP# STATUS BYTES/(1024*1024)
---------- ---------------- -----------------
 1 CURRENT 100
 2 INACTIVE 100
 3 INACTIVE 100

SQL> alter system switch logfile;

System altered.

SQL> select group#,status ,bytes/(1024*1024) from v$log;

 GROUP# STATUS BYTES/(1024*1024)
---------- ---------------- -----------------
 1 INACTIVE 100
 2 CURRENT 100
 3 INACTIVE 100

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 '+DGDEVDATA01' size 100M;

Database altered.

SQL> col member format A60
SQL> select member from v$logfile;

MEMBER
------------------------------------------------------------
+DISKGROUP/rman/onlinelog/group_1.853.751900851
+DISKGROUP/rman/onlinelog/group_2.851.751900687
+DISKGROUP/rman/onlinelog/group_3.852.751900721

12) Create the spfile in ASM diskgroup.

SQL> create spfile='+DISKGROUP/rman/spfileRMAN.ora' from pfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[oradb@dbhost dbs]$ mv initRMAN.ora initRMAN.ora.bkp#2MayAfterASMMigration

vi initRMAN.ora

spfile='+DISKGROUP/rman/spfileRMAN.ora'

[oradb@dbhost dbs]$ sqlplus

SQL*Plus: Release 10.2.0.4.0 - Production on Mon May 23 13:26:37 2011

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

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

SQL> startup
ORACLE instance started.

Total System Global Area 1761607680 bytes
Fixed Size 2084392 bytes
Variable Size 973079000 bytes
Database Buffers 671088640 bytes
Redo Buffers 115355648 bytes
Database mounted.
Database opened.
SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DISKGROUP/rman/spfilerman.ora

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>