Search This Blog

Wednesday, February 2, 2011

Migrate a normal file-system Oracle database to ASM database

Migrate a normal file-system Oracle database to ASM database

by Michael Georgiou



Oracle Linux 5EL 64 bits , Oracle 10.2.0.4 64 bits



On ASM instance create a new disk group that will host the new database



CREATE DISKGROUP Dgroup2 NORMAL REDUNDANCY

FAILGROUP controller1 DISK

'ORCL:DISK5',

'ORCL:DISK6'

FAILGROUP controller2 DISK

'ORCL:DISK7' ,

'ORCL:DISK8'

/





Connect to Database ORCLF that is created using OMF files ( Filesystem)



[oracle@asm_machine oracle]$ export ORACLE_SID=orclf

[oracle@asm_machine oracle]$ sqlplus / as sysdba



SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 2 22:28:46 2011



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





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



---------------------------------------------------------------------------------------------



Check if the block change tracking is enable



SQL> select status from V$block_change_tracking;



STATUS

----------

DISABLED



if enable the disable it using the command



alter database disable block change tracking;



---------------------------------------------------------------------------------------------





Change the db_create_file_dest and db_recovery_file_dest to point on '+DGROUP2'



SQL> show parameter db_create



NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_create_file_dest string /oracle/oradata





NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /oracle/flash_recovery_area

db_recovery_file_dest_size big integer 2G

SQL>

---------------------------------------------------------------------------------------------

Change OMF to ASM diskgroup Dgroup2



alter system set db_create_file_dest='+DGROUP2' scope=spfile;

alter system set db_recovery_file_dest='+DGROUP2' scope=spfile;





--Oracle recommends to use a different disk group for the recovery area. For the testing purposes I use the same disk group.



Recreate Control Files in new ASM location



In order to create the control file in the ASM , you need to change the control_files in spfile as follows:



alter system set control_files='+DGROUP2' scope=spfile;



shutdown database



SQL>shutdown immediate



connect to rman



RMAN> connect target



connected to target database (not started)



RMAN> startup nomount



Oracle instance started



Total System Global Area 260046848 bytes



Fixed Size 2083168 bytes

Variable Size 83887776 bytes

Database Buffers 167772160 bytes

Redo Buffers 6303744 bytes



RMAN> restore controlfile from '/oracle/oradata/ORCLF/controlfile/o1_mf_6nmgt4mo_.ctl';



Starting restore at 02-FEB-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK



channel ORA_DISK_1: copied control file copy

output filename=+DGROUP2/orclf/controlfile/current.256.742086217

Finished restore at 02-FEB-11



RMAN>exit





[oracle@asm_machine dbs]$ sqlplus / as sysdba



SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 2 23:05:29 2011



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





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 open_mode from v$database;

select open_mode from v$database

*

ERROR at line 1:

ORA-01507: database not mounted





SQL> alter system set control_files='+DGROUP2/orclf/controlfile/current.256.742086217' scope=spfile;



System altered.



SQL> alter database mount;



Database altered.



SQL>exit



Migrate the whole database using RMAN COPY command



rman

RMAN> connect target



connected to target database: ORCLF (DBID=3004244068, not open)



RMAN> backup as copy database format '+DGROUP2';



Starting backup at 02-FEB-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=159 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/oracle/oradata/ORCLF/datafile/o1_mf_system_6nmgrm37_.dbf

output filename=+DGROUP2/orclf/datafile/system.257.742086491 tag=TAG20110202T230810 recid=1 stamp=742086537

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55

channel ORA_DISK_1: starting datafile copy

input datafile fno=00003 name=/oracle/oradata/ORCLF/datafile/o1_mf_sysaux_6nmgrm3d_.dbf

output filename=+DGROUP2/orclf/datafile/sysaux.258.742086545 tag=TAG20110202T230810 recid=2 stamp=742086573

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile copy

input datafile fno=00002 name=/oracle/oradata/ORCLF/datafile/o1_mf_undotbs1_6nmgrm64_.dbf

output filename=+DGROUP2/orclf/datafile/undotbs1.259.742086581 tag=TAG20110202T230810 recid=3 stamp=742086586

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile fno=00004 name=/oracle/oradata/ORCLF/datafile/o1_mf_users_6nmgrm6c_.dbf

output filename=+DGROUP2/orclf/datafile/users.260.742086587 tag=TAG20110202T230810 recid=4 stamp=742086590

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

copying current control file

output filename=+DGROUP2/orclf/controlfile/backup.261.742086591 tag=TAG20110202T230810 recid=5 stamp=742086595

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08

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 02-FEB-11

channel ORA_DISK_1: finished piece 1 at 02-FEB-11

piece handle=+DGROUP2/orclf/backupset/2011_02_02/nnsnf0_tag20110202t230810_0.262.742086599 tag=TAG20110202T230810 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08

Finished backup at 02-FEB-11



RMAN> switch database to copy;



datafile 1 switched to datafile copy "+DGROUP2/orclf/datafile/system.257.742086491"

datafile 2 switched to datafile copy "+DGROUP2/orclf/datafile/undotbs1.259.742086581"

datafile 3 switched to datafile copy "+DGROUP2/orclf/datafile/sysaux.258.742086545"

datafile 4 switched to datafile copy "+DGROUP2/orclf/datafile/users.260.742086587"



RMAN> recover database;



Starting recover at 02-FEB-11

using channel ORA_DISK_1



starting media recovery

media recovery complete, elapsed time: 00:00:01



Finished recover at 02-FEB-11



RMAN> alter database open;



database opened



RMAN>exit



Migrate Temp tablespace and Redo logs



SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files;



FILE_NAME

--------------------------------------------------------------------------------

TABLESPACE_NAME

------------------------------

/oracle/oradata/ORCLF/datafile/o1_mf_temp_6nmgtqkx_.tmp

TEMP





SQL> create temporary tablespace TEMP01 tempfile '+DGROUP2' size 128M autoextend off;



Tablespace created.



SQL> alter database default temporary tablespace TEMP01;



Database altered.





SQL> drop tablespace TEMP;



Tablespace dropped.



SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files;



FILE_NAME

--------------------------------------------------------------------------------

TABLESPACE_NAME

------------------------------

+DGROUP2/orclf/tempfile/temp01.263.742086965

TEMP01





SQL>



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

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

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DGROUP2' TO GROUP 3;

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

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

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DGROUP2' TO GROUP 3;



SQL> ALTER DATABASE ADD LOGFILE GROUP 4 '+DGROUP2' ;

SQL> ALTER DATABASE ADD LOGFILE MEMBER '+DGROUP2' TO GROUP 4;

SQL>ALTER DATABASE ADD LOGFILE MEMBER '+DGROUP2' TO GROUP 4;





SQL> select group#,members,status from v$log;



GROUP# MEMBERS STATUS

---------- ---------- ----------------

1 4 INACTIVE

2 4 INACTIVE

3 4 INACTIVE

4 2 CURRENT



SQL> select member from v$logfile;



MEMBER

--------------------------------------------------------------------------------

/oracle/oradata/ORCLF/onlinelog/o1_mf_3_6nmgtdfp_.log

/oracle/flash_recovery_area/ORCLF/onlinelog/o1_mf_3_6nmgtfpq_.log





/oracle/oradata/ORCLF/onlinelog/o1_mf_2_6nmgtb0c_.log

/oracle/flash_recovery_area/ORCLF/onlinelog/o1_mf_2_6nmgtc7f_.log





/oracle/oradata/ORCLF/onlinelog/o1_mf_1_6nmgt7lv_.log

/oracle/flash_recovery_area/ORCLF/onlinelog/o1_mf_1_6nmgt8wc_.log





+DGROUP2/orclf/onlinelog/group_1.264.742087309

+DGROUP2/orclf/onlinelog/group_2.265.742087325

+DGROUP2/orclf/onlinelog/group_3.266.742087431

+DGROUP2/orclf/onlinelog/group_4.267.742087451

+DGROUP2/orclf/onlinelog/group_1.268.742087647



MEMBER

--------------------------------------------------------------------------------

+DGROUP2/orclf/onlinelog/group_2.269.742087669

+DGROUP2/orclf/onlinelog/group_3.270.742087687

+DGROUP2/orclf/onlinelog/group_4.271.742087703



14 rows selected.



SQL>



ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/ORCLF/onlinelog/o1_mf_2_6nmgtb0c_.log';

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/flash_recovery_area/ORCLF/onlinelog/o1_mf_2_6nmgtc7f_.log';



ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/ORCLF/onlinelog/o1_mf_3_6nmgtdfp_.log';

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/flash_recovery_area/ORCLF/onlinelog/o1_mf_3_6nmgtfpq_.log';



ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/ORCLF/onlinelog/o1_mf_1_6nmgt7lv_.log';

ALTER DATABASE DROP LOGFILE MEMBER '/oracle/flash_recovery_area/ORCLF/onlinelog/o1_mf_1_6nmgt8wc_.log';





SQL> select member from v$logfile;



MEMBER

--------------------------------------------------------------------------------

+DGROUP2/orclf/onlinelog/group_1.264.742087309

+DGROUP2/orclf/onlinelog/group_2.265.742087325

+DGROUP2/orclf/onlinelog/group_3.266.742087431

+DGROUP2/orclf/onlinelog/group_4.267.742087451

+DGROUP2/orclf/onlinelog/group_1.268.742087647

+DGROUP2/orclf/onlinelog/group_2.269.742087669

+DGROUP2/orclf/onlinelog/group_3.270.742087687

+DGROUP2/orclf/onlinelog/group_4.271.742087703



8 rows selected.