ocp_logo

Step By Step Instructions on Migrating Oracle10g Database to Automatic Storage management (ASM)
By Bhavin Hingu


                                                <<HOME>>                     



Disable Block change tracking:


SQL> select * from v$block_change_tracking;


STATUS

----------

FILENAME

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

     BYTES

----------

        DISABLED


If not disabled then, disable using this command.

 

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

 

Database altered.

 

SQL>

 

Shutdown Database Cleanly:

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

     With the Partitioning, OLAP and Data Mining options

 

Create pfile and add/modify the below parameters:


[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"

 

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 12:17:50 2006

 

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

 

Connected to an idle instance.

 

SQL> create pfile from spfile;

 

     File created.


Modify pfile with these parameters:


I have already created 2 ASM diskgroups DATA and FLASH.

 

*.control_files=(+DATA, +FLASH)

*.db_recovery_file_dest=+FLASH

*.db_recovery_file_dest_size=2147483648

*.db_create_file_dest=+DATA

*.db_create_online_log_dest_1=+FLASH

*.db_create_online_log_dest_2=+DATA -- optional if you want another online redo logs dest.


Create spfile back from modified pfile:


PS: take a copy of original spfile before you overwrite spfile using below command.


create spfile from pfile;

 


Copy Database to ASM diskgroups using rman:


   (1) start the instance on NOMOUNT state

   (2) copy the controlfile from old location to ASM usin "resrore" rman command

   (3) mount the database

   (4) copy the datafiles to ASM disk group using rman "BACKUP AS COPY DATABASE" command

   (5) Switch database to COPY and open the database.


[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman

 

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 10:03:10 2006

 

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

 

RMAN> connect target

 

connected to target database (not started)

 

RMAN> startup nomount

 

Oracle instance started

 

Total System Global Area     167772160 bytes

 

Fixed Size                     1218316 bytes

Variable Size                 83888372 bytes

Database Buffers              79691776 bytes

Redo Buffers                   2973696 bytes

 

RMAN> restore controlfile from '/home/oracle/oradata/db10g/control01.ctl';

 

Starting restore at 21-JUL-06

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

 

channel ORA_DISK_1: copied control file copy

output filename=+DATA/db10g/controlfile/backup.256.596369129

output filename=+FLASH/db10g/controlfile/backup.256.596369131

Finished restore at 21-JUL-06

 

RMAN> startup mount

 

database is already started

database mounted

released channel: ORA_DISK_1

 

RMAN> configure device type disk parallelism 4;

 

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

 

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

 

Starting backup at 21-JUL-06

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=152 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=151 devtype=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: sid=150 devtype=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: sid=149 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/home/oracle/oradata/db10g/system01.dbf

channel ORA_DISK_2: starting datafile copy

input datafile fno=00003 name=/home/oracle/oradata/db10g/sysaux01.dbf

channel ORA_DISK_3: starting datafile copy

input datafile fno=00002 name=/home/oracle/oradata/db10g/undotbs01.dbf

channel ORA_DISK_4: starting datafile copy

input datafile fno=00004 name=/home/oracle/oradata/db10g/users01.dbf

output filename=+DATA/db10g/datafile/undotbs1.259.596369341 tag=TAG20060721T100858 recid=2 stamp=596369352

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:16

channel ORA_DISK_3: starting datafile copy

copying current control file

output filename=+DATA/db10g/datafile/users.260.596369341 tag=TAG20060721T100858 recid=1 stamp=596369350

channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:20

channel ORA_DISK_4: starting full datafile backupset

channel ORA_DISK_4: specifying datafile(s) in backupset

output filename=+DATA/db10g/controlfile/backup.261.596369361 tag=TAG20060721T100858 recid=3 stamp=596369364

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:06

including current SPFILE in backupset

channel ORA_DISK_4: starting piece 1 at 21-JUL-06

channel ORA_DISK_4: finished piece 1 at 21-JUL-06

piece handle=+DATA/db10g/backupset/2006_07_21/nnsnf0_tag20060721t100858_0.262.596369369 tag=TAG20060721T100858 comment=NONE

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:10

output filename=+DATA/db10g/datafile/sysaux.258.596369341 tag=TAG20060721T100858 recid=4 stamp=596369390

channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:05

output filename=+DATA/db10g/datafile/system.257.596369339 tag=TAG20060721T100858 recid=5 stamp=596369414

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:21

Finished backup at 21-JUL-06

 

RMAN> SWITCH DATABASE TO COPY;

 

datafile 1 switched to datafile copy "+DATA/db10g/datafile/system.257.596369339"

datafile 2 switched to datafile copy "+DATA/db10g/datafile/undotbs1.259.596369341"

datafile 3 switched to datafile copy "+DATA/db10g/datafile/sysaux.258.596369341"

datafile 4 switched to datafile copy "+DATA/db10g/datafile/users.260.596369341"

 

RMAN> alter database open;

 

database opened

 

     RMAN> exit


     Recovery Manager complete.

 

Migrate tempfile to ASM:


RMAN does not migrate the tempfile as part of the BACKUP AS COPY and SWITCH command becuase the tempfile is not listed in controlfile. The tempfile has to be manually migrated to ASM.


[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"

 

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 10:12:42 2006

 

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

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> select name, bytes from v$tempfile;

 

NAME

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

     BYTES

----------

/home/oracle/oradata/db10g/temp01.dbf

  20971520

 

SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;

 

Tablespace created.

 

SQL> alter database default temporary tablespace temp1;

 

Database altered.

 

SQL> drop tablespace temp including contents;

 

Tablespace dropped.

 

SQL> create temporary tablespace temp tempfile SIZE 100M extent management local uniform size 1M;

 

Tablespace created.

 

SQL> alter database default temporary tablespace temp;

 

Database altered.

 

SQL> drop tablespace temp1 including contents;

 

Tablespace dropped.

 

SQL> select name from v$tempfile;

 

NAME

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

       +DATA/db10g/tempfile/temp.264.596370217


Migrate and drop the old Online Redo Logs to ASM:



Drop and recreate the redo groups into ASM to migrate them to ASM Diskgroup.

 

 

SQL> select member from v$logfile;

 

MEMBER

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

+FLASH/db10g/onlinelog/group_3.259.596373619

+FLASH/db10g/onlinelog/group_2.258.596373615

+FLASH/db10g/onlinelog/group_1.261.596373613

+FLASH/db10g/onlinelog/group_4.257.596373293

+FLASH/db10g/onlinelog/group_5.260.596373609

 

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

     With the Partitioning, OLAP and Data Mining options


DELETE THE OLD DATAFILES USING RMAN.


This way, it will also clear out the datafiles entry from controlfile.


[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman

 

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 11:22:33 2006

 

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

 

RMAN> connect target

 

connected to target database: DB10G (DBID=4283639931)


RMAN> run {

2> DELETE COPY OF DATABASE;

3> }

 

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=134 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=151 devtype=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: sid=153 devtype=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: sid=138 devtype=DISK

 

List of Datafile Copies

Key     File S Completion Time Ckp SCN    Ckp Time        Name

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

6       1    A 21-JUL-06       461254     21-JUL-06       /home/oracle/oradata/db10g/system01.dbf

7       2    A 21-JUL-06       461254     21-JUL-06       /home/oracle/oradata/db10g/undotbs01.dbf

8       3    A 21-JUL-06       461254     21-JUL-06       /home/oracle/oradata/db10g/sysaux01.dbf

9       4    A 21-JUL-06       461254     21-JUL-06       /home/oracle/oradata/db10g/users01.dbf

 

Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/system01.dbf recid=6 stamp=596369439

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/undotbs01.dbf recid=7 stamp=596369439

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/sysaux01.dbf recid=8 stamp=596369440

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/users01.dbf recid=9 stamp=596369440

Deleted 4 objects

 

RMAN> exit

 

Recovery Manager complete.


REMOVE THE OLD ONLINE REDO LOGS FILES PHYSICALLY:

 

[oracle@node1-pub oracle]$ rm /home/oracle/oradata/db10g/redo*.log

[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"

 

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 11:29:56 2006

 

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

 

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

     With the Partitioning, OLAP and Data Mining options

 

Enable the block change tracking:


SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

 

Database altered.

 

SQL>


                                                <<HOME>>