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.
[[email protected] 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 :
[[email protected] ~]$ 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.
[[email protected] ~]$ 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. [[email protected] dbs]$ mv initRMAN.ora initRMAN.ora.bkp#2MayAfterASMMigration vi initRMAN.ora spfile='+DISKGROUP/rman/spfileRMAN.ora' [[email protected] 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
No comments