Mani's Blog

March 24, 2011

>Migrating Oracle Database between ASM Disk groups

Filed under: Oracle — mani @ 12:03 am
Tags: , , , ,

>

We had our database on non-mirrored ASM disk group, we need to migrate database to mirrored ASM disk group.  Following steps are used to migrate.

Pre-Requisites : 

   1. Install Disk/storage and create ASM disks.

       /etc/init.d/oracleasm createdisk asm_ssd1 /dev/sdbd1

       /etc/init.d/oracleasm createdisk asm_ssd2 /dev/sdbe1

    2. Verify the ASM disks.

      /etc/init.d/oracleasm querydisk ASM_DATA01

       /etc/init.d/oracleasm querydisk ASM_DATA02

   3.  Create new diskgroup with redundancy .

        Example: CREATE DISKGROUP XN_DATA2 NORMAL REDUNDANCY DISK ‘/dev/oracleasm/disks/ASM_SSD1′,’/dev/oracleasm/disks/ASM_SSD2’   

Migration:

  1. Migrating datafile  
    
    shutdown immediate;startup mount;
    
    -- connect to RMAN
    
    rman
    
    connect target /
    
    copy datafile '+XN_DATA/xnb1rac/datafile/system.256.714364261' to '+XN_DATA2/xnb1rac/datafile/system.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/sysaux.263.714364261' to '+XN_DATA2/xnb1rac/datafile/sysaux.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/undotbs1.266.714364263' to '+XN_DATA2/xnb1rac/datafile/undotbs1.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/users.265.714364263' to '+XN_DATA2/xnb1rac/datafile/users.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/core.259.714367853' to '+XN_DATA2/xnb1rac/datafile/core.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_index.261.714367907' to '+XN_DATA2/xnb1rac/datafile/xn_index.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_msg.260.714367929' to '+XN_DATA2/xnb1rac/datafile/xn_msg.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_invite.264.714367945' to '+XN_DATA2/xnb1rac/datafile/xn_invite.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_lob.269.714367971' to '+XN_DATA2/xnb1rac/datafile/xn_lob.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/billing.270.714367985' to '+XN_DATA2/xnb1rac/datafile/billing.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_admin.271.714368001' to '+XN_DATA2/xnb1rac/datafile/xn_admin.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_sysm_nologging.272.714368019' to '+XN_DATA2/xnb1rac/datafile/xn_sysm_nologging.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_memb.273.714368047' to '+XN_DATA2/xnb1rac/datafile/xn_memb.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_msg.288.714439859' to '+XN_DATA2/xnb1rac/datafile/xn_msg3.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_adminjr.275.714368095' to '+XN_DATA2/xnb1rac/datafile/xn_adminjr.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_contact_data.276.714368129' to '+XN_DATA2/xnb1rac/datafile/xn_contact_data.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_contact_idx.277.714368149' to '+XN_DATA2/xnb1rac/datafile/xn_contact_idx.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_vg.278.714368167' to '+XN_DATA2/xnb1rac/datafile/xn_vg.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_pymt.279.714368181' to '+XN_DATA2/xnb1rac/datafile/xn_pymt.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/content_data.280.714368201' to '+XN_DATA2/xnb1rac/datafile/content_data.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/content_index.281.714368217' to '+XN_DATA2/xnb1rac/datafile/content_index.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/content_lob.282.714368229' to '+XN_DATA2/xnb1rac/datafile/content_lob.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_vg_idx.283.714368239' to '+XN_DATA2/xnb1rac/datafile/xn_vg_idx.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_pymt_idx.284.714368247' to '+XN_DATA2/xnb1rac/datafile/xn_pymt_idx.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/audit.285.714368285' to '+XN_DATA2/xnb1rac/datafile/audit.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_core.286.714415215' to '+XN_DATA2/xnb1rac/datafile/xn_core.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_opensocial.287.714415247' to '+XN_DATA2/xnb1rac/datafile/xn_opensocial.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_msg.289.714439909' to '+XN_DATA2/xnb1rac/datafile/xn_msg2.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_content_24k.290.714521183' to '+XN_DATA2/xnb1rac/datafile/xn_content_24k.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_content_64k.292.714521329' to '+XN_DATA2/xnb1rac/datafile/xn_content_64k.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_index_32k.293.716853111' to '+XN_DATA2/xnb1rac/datafile/xn_index_32k.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_uniform_24k.294.714521479' to '+XN_DATA2/xnb1rac/datafile/xn_uniform_24k.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_search.295.714521693' to '+XN_DATA2/xnb1rac/datafile/xn_search.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_subs_data.296.715291225' to '+XN_DATA2/xnb1rac/datafile/xn_subs_data.dbf';
    
    copy datafile '+XN_DATA/xnb1rac/datafile/xn_content.297.716963615' to '+XN_DATA2/xnb1rac/datafile/xn_content.dbf';
    
    -- From SQL Plus
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/system.256.714364261' to '+XN_DATA2/xnb1rac/datafile/system.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/sysaux.263.714364261' to '+XN_DATA2/xnb1rac/datafile/sysaux.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/undotbs1.266.714364263' to '+XN_DATA2/xnb1rac/datafile/undotbs1.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/users.265.714364263' to '+XN_DATA2/xnb1rac/datafile/users.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/core.259.714367853' to '+XN_DATA2/xnb1rac/datafile/core.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_index.261.714367907' to '+XN_DATA2/xnb1rac/datafile/xn_index.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_msg.260.714367929' to '+XN_DATA2/xnb1rac/datafile/xn_msg.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_invite.264.714367945' to '+XN_DATA2/xnb1rac/datafile/xn_invite.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_lob.269.714367971' to '+XN_DATA2/xnb1rac/datafile/xn_lob.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/billing.270.714367985' to '+XN_DATA2/xnb1rac/datafile/billing.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_admin.271.714368001' to '+XN_DATA2/xnb1rac/datafile/xn_admin.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_sysm_nologging.272.714368019' to '+XN_DATA2/xnb1rac/datafile/xn_sysm_nologging.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_memb.273.714368047' to '+XN_DATA2/xnb1rac/datafile/xn_memb.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_msg.288.714439859' to '+XN_DATA2/xnb1rac/datafile/xn_msg3.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_adminjr.275.714368095' to '+XN_DATA2/xnb1rac/datafile/xn_adminjr.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_contact_data.276.714368129' to '+XN_DATA2/xnb1rac/datafile/xn_contact_data.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_contact_idx.277.714368149' to '+XN_DATA2/xnb1rac/datafile/xn_contact_idx.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_vg.278.714368167' to '+XN_DATA2/xnb1rac/datafile/xn_vg.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_pymt.279.714368181' to '+XN_DATA2/xnb1rac/datafile/xn_pymt.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/content_data.280.714368201' to '+XN_DATA2/xnb1rac/datafile/content_data.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/content_index.281.714368217' to '+XN_DATA2/xnb1rac/datafile/content_index.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/content_lob.282.714368229' to '+XN_DATA2/xnb1rac/datafile/content_lob.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_vg_idx.283.714368239' to '+XN_DATA2/xnb1rac/datafile/xn_vg_idx.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_pymt_idx.284.714368247' to '+XN_DATA2/xnb1rac/datafile/xn_pymt_idx.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/audit.285.714368285' to '+XN_DATA2/xnb1rac/datafile/audit.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_core.286.714415215' to '+XN_DATA2/xnb1rac/datafile/xn_core.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_opensocial.287.714415247' to '+XN_DATA2/xnb1rac/datafile/xn_opensocial.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_msg.289.714439909' to '+XN_DATA2/xnb1rac/datafile/xn_msg2.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_content_24k.290.714521183' to '+XN_DATA2/xnb1rac/datafile/xn_content_24k.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_content_64k.292.714521329' to '+XN_DATA2/xnb1rac/datafile/xn_content_64k.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_index_32k.293.716853111' to '+XN_DATA2/xnb1rac/datafile/xn_index_32k.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_uniform_24k.294.714521479' to '+XN_DATA2/xnb1rac/datafile/xn_uniform_24k.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_search.295.714521693' to '+XN_DATA2/xnb1rac/datafile/xn_search.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_subs_data.296.715291225' to '+XN_DATA2/xnb1rac/datafile/xn_subs_data.dbf';
    
    alter database rename file '+XN_DATA/xnb1rac/datafile/xn_content.297.716963615' to '+XN_DATA2/xnb1rac/datafile/xn_content.dbf';
  2. Validate Data file migration
    dba
    
    alter database open;
    
    select name from v$database;
    
    select name from v$datafile;
    
    shutdown immediate;
    
    startup
  3. Migrating tempfile
    alter tablespace temp add tempfile '+XN_DATA2' size 1G;
    
    alter database tempfile '+XN_DATA/xnb1rac/tempfile/temp.257.714364757' drop including datafiles;
    
    alter database tempfile '+XN_DATA/xnb1rac/tempfile/xn_temp.274.714416203' drop including datafiles;
    
    alter database tempfile '+XN_DATA/xnb1rac/tempfile/temp2.291.714521273' drop including datafiles;
    
    select * from v$tempfile;
  4. Migrating REDO Logfile
    select group#, status from v$log;
    
    ALTER DATABASE ADD LOGFILE GROUP 5 ( '+XN_DATA2') SIZE 204800K;
    
    ALTER DATABASE ADD LOGFILE GROUP 6 ( '+XN_DATA2') SIZE 204800K;
    
    ALTER DATABASE ADD LOGFILE GROUP 7 ( '+XN_DATA2') SIZE 204800K;
    
    ALTER DATABASE ADD LOGFILE GROUP 8 ( '+XN_DATA2') SIZE 204800K; 
    
    ALTER DATABASE drop logfile group 1;
    
    ALTER DATABASE drop logfile group 2;
    
    ALTER DATABASE drop logfile group 3;
    
    ALTER DATABASE drop logfile group 4;
    
    alter system checkpoint; 
    
    alter system switch logfile; 
    
    select member form v$logfile;
  5. Migrating Control file
    -- startup nomount;
    
    -- connect to RMAN
    
    connect target /
    
    restore controlfile to '+XN_DATA2/xnb1rac/controlfile/control01.ctl' from '+XN_DATA/xnb1rac/controlfile/current.262.714364561'; 
    
    restore controlfile to '+XN_DATA2/xnb1rac/controlfile/control02.ctl' from '+XN_DATA/xnb1rac/controlfile/current.262.714364561'; 
    
    restore controlfile to '+XN_DATA2/xnb1rac/controlfile/control03.ctl' from '+XN_DATA/xnb1rac/controlfile/current.262.714364561'; 
    
    -- connect to sqlplus 
    
    alter system set control_files='+XN_DATA2/xnb1rac/controlfile/control01.ctl',-'+XN_DATA2/xnb1rac/controlfile/control02.ctl',-'+XN_DATA2/xnb1rac/controlfile/control03.ctl' scope=spfile; 
    
    shutdown immediate;
    
    startup ;
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: