Oracle RAC存储迁移方案1-3

存储迁移方案：


1.在线迁移表数据
测试数据
expdp webdata/webdata2 directory=DIR_DP dumpfile=spcode.dmp tables=spcode


create tablespace webdata datafile '+DATADG/shanghai/datafile/webdata.dbf' size 2096m;
create user webdata identified by webdata2 default tablespace webdata;
grant dba to webdata;
create directory dir_dump as '/home/oracle/dump';
grant read,write on directory dir_dump to webdata,system ;
impdp webdata/webdata2 directory=dir_dump dumpfile=spcode.dmp
select index_name,status from user_indexes;
exec dbms_stats.gather_table_stats(ownname => 'WEBDATA',tabname => 'spcode_par',cascade => true);




partition 分区表移动到其他表空间


begin
for x in(select partition_name from dba_tab_partitions where table_name='SPCODE_PAR')
loop
execute immediate 'alter table spcode_par move partition ' || x.partition_name || ' tablespace users';
end loop;
end;
/


重建索引
select 'alter index '||index_name||' rebuild partition '||partition_name ||' online  parallel 16 tablespace users;' from USER_IND_PARTITIONS 
where index_name in ('PK_SPCODE_GL','UK_SPCODE_GL','IDX_SPCODE_MEMBERID_GL');


col SEGMENT_NAME for a30
col PARTITION_NAME for a30
col TABLESPACE_NAME for a30
select SEGMENT_NAME,PARTITION_NAME,TABLESPACE_NAME from user_segments;
SYS_P121
SYS_P122
SYS_P123
SYS_P124
SYS_P125
SYS_P126
SYS_P127
SYS_P128
SYS_P129
SYS_P130
SYS_P131


alter table spcode_par move partition SYS_P121 tablespace users;




alter index IDX_SPCODE_MEMBERID_GL rebuild partition SYS_P21 online  parallel 16 tablespace users;
alter index PK_SPCODE_GL rebuild partition SYS_P137 online  parallel 16 tablespace users;
alter index UK_SPCODE_GL rebuild partition SYS_P138 online  parallel 16 tablespace users;


在线调整undo


    3 +DATADG/shanghai/datafile/undotbs1.261.906027593
    4 +DATADG/shanghai/datafile/undotbs2.263.906027613


  create undo tablespace undotbs3 datafile '+DATADG/shanghai/datafile/undotbs3.dbf' size 20m;       
  create undo tablespace undotbs4 datafile '+DATADG/shanghai/datafile/undotbs4.dbf' size 20m;       
  select * from v$dbfile;


修改undo，可以有事务正在运行
show parameter undo;
连接到实例1
alter system set undo_tablespace='undotbs3' scope=both sid='shanghai1'; 
连接到实例2
alter system set undo_tablespace='undotbs4' scope=both sid='shanghai2'; 




select tablespace_name,segment_name,status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS2';
原来undotbs1的undo_segment 都为offline，则可以删除undotbs1.
drop tablespace undotbs1 including contents and datafiles; 
drop tablespace undotbs2 including contents and datafiles; 


ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use


在线迁移redo log
select group#,type,member from v$logfile;
select group#,archived,status,thread# from v$log;


alter database add logfile thread 1 group 5 ('+DATADG/shanghai/onlinelog/group_5.log')size 50M;
alter database add logfile thread 1 group 6 ('+DATADG/shanghai/onlinelog/group_6.log')size 50M;
alter database add logfile thread 2 group 7 ('+DATADG/shanghai/onlinelog/group_7.log')size 50M;
alter database add logfile thread 2 group 8 ('+DATADG/shanghai/onlinelog/group_8.log')size 50M;






 select thread#,group#,archived,status from v$log;
  1          2 YES INACTIVE
  2          3 YES INACTIVE


         
 alter database drop logfile group 2;
 alter database drop logfile group 3;
 alter system switch logfile;
 alter system checkpoint;
 


col name format a46
select name, swquence#, first_change# FROM v$archived_log;




2.采用ASM平衡技术
http://www.luocs.com/archives/769.html
登录到ASM实例，查看当前磁盘组的信息：
su - oracle
export ORACLE_SID=+ASM1
sqlplus / as sysasm


col path for a20
 select group_number,disk_number,name,failgroup,path from v$asm_disk;


col NAME for a10
set line 150
select NAME, ALLOCATION_UNIT_SIZE, STATE, TOTAL_MB, FREE_MB USABLE_FILE_MB from gv$asm_diskgroup;


现往该磁盘组里添加一个新创建的ASM磁盘，指定REBALANCE的速度：
alter diskgroup DATADG add disk 'ORCL:DATA2' name DATA2 REBALANCE POWER 2;
ORA-15260: permission denied on ASM disk group
conn / as sysasm


备注：这里只测试添加一个ASM磁盘，在真实环境中可能会增加多个，那么采用如下方式：
alter diskgroup DATADG add disk 'ORCL:DATA2' name DATA2 REBALANCE POWER 2;
alter diskgroup ARCDG add disk 'ORCL:ARC2' name ARC2 REBALANCE POWER 2;




注意：REBALANCE过程根据数据量的大小不同完成时间也不一，可以针对当前系统情况指定合理的POWER值。
REBALANCE过程可以通过v$asm_operation视图来查看：


set line 150
col ERROR_CODE for a5
select * from v$asm_operation;


REBALANCE完成后记录将消失：
SQL> select * from v$asm_operation;


no rows selected




添加ASM磁盘后的状态：
SQL> select NAME, ALLOCATION_UNIT_SIZE, STATE, TOTAL_MB, FREE_MB USABLE_FILE_MB from gv$asm_diskgroup;






好，现在可以删除旧的ASM磁盘了。
 col FAILGROUP for a20
 col NAME for a20
 col PATH for a20
 select failgroup, name, path from v$asm_disk where group_number=(select group_number from v$asm_diskgroup where name = 'ARCDG');




select failgroup, name, path from v$asm_disk ;
select GROUP_NUMBER,name,TOTAL_MB from v$asm_diskgroup;




删除的时候也可以指定rebalance速度：
 alter diskgroup DATADG drop disk DATA1 rebalance power 5;
 alter diskgroup ARCDG drop disk ARC1 rebalance power 5;
观察rebalance情况：
SQL> select * from v$asm_operation;
no rows selected
rebalance完成，查看数据保证无误：
SQL> select count(*) from luocs.t1;


  COUNT(*)
----------
     68728
现在系统里用到的全部为新存储上的空间，这样就可以卸载旧存储了。


首先清理环境，同样滚动方式卸载旧磁盘：
节点1：
srvctl stop instance -d shanghai -i shanghai1
asmca---->umont 磁盘组或alter diskgroup datadg dismount;  
crs_stat -t




srvctl status asm
CRS-2529: Unable to act on 'ora.asm' because that would require stopping or relocating 'ora.OCRDG.dg', but the force option was not specified
使用crsctl stop crs停止CRS，同时也停止了ASM磁盘


oracleasm deletedisk ARC1
oracleasm deletedisk DATA1
oracleasm listdisks
oracleasm scandisks


srvctl start asm -n db1
srvctl start asm -n db2
srvctl status asm
asmca //挂载磁盘 或alter diskgroup datadg mount;
srvctl start instance -d shanghai -i shanghai1
srvctl start instance -d shanghai -i shanghai2


crsctl stat res -t
srvctl config asm -a
 
tail -20 /var/log/oracleasm




export ORACLE_SID=+ASM1
asmcmd




ocr磁盘迁移
crsctl query css votedisk
 1. ONLINE   9dc69556df8d4fa1bf22ed75438e8b34 (ORCL:OCRDISK1) [OCRDG]


在两节点查看OCR备份情况（在两节点root执行）
ocrconfig -showbackup 


创建新的OCRDG_NEW
asmca


8.将新创建的ASM磁盘组OCRVOTE_NEW添加到OCR中（在一个节点root执行）
su - grid
su -
ocrconfig -add +OCRDG_NEW


crsctl query css votedisk
ocrcheck -config 


删除OCR原位置ASM磁盘组OCRVOTE（在一个节点root执行）
ocrconfig -delete  +OCRDG
15.查看OCR和VOTE新状态与位置（在两个节点root执行）
ocrcheck && crsctl query css votedisk










3.采用rman方案迁移
创建新磁盘组：
oracleasm createdisk SSDDISK1 /dev/sde1
asmca--->SSDDG




srvctl stop database -d shanghai 
srvctl start instance -d shanghai -i shanghai1 -o mount


set line 400
select * from v$dbfile;
     FILE# NAME
---------- --------------------------------------------------------------------------------
         1 +DATADG/shanghai/datafile/system.259.906027563
         2 +DATADG/shanghai/datafile/sysaux.260.906027581
         4 +DATADG/shanghai/datafile/undotbs2.263.906027613
         5 +DATADG/shanghai/datafile/users.264.906027619
         6 +DATADG/shanghai/datafile/webdata.dbf
         7 +DATADG/shanghai/datafile/undotbs3.dbf
         8 +DATADG/shanghai/datafile/undotbs4.dbf
         


使用RMAN迁移datafile


rman target /  
run{  
copy datafile   1  to '+SSDDG/shanghai/datafile/system.dbf';
copy datafile   2  to '+SSDDG/shanghai/datafile/sysaux.dbf';
copy datafile   4  to '+SSDDG/shanghai/datafile/undotbs2.dbf';
copy datafile   5  to '+SSDDG/shanghai/datafile/users.dbf';
copy datafile   6  to '+SSDDG/shanghai/datafile/webdata.dbf';
copy datafile   7  to '+SSDDG/shanghai/datafile/undotbs3.dbf';
copy datafile   8  to '+SSDDG/shanghai/datafile/undotbs4.dbf';
}




alter database rename file '+DATADG/shanghai/datafile/system.259.906027563' to '+SSDDG/shanghai/datafile/system.dbf';  
alter database rename file '+DATADG/shanghai/datafile/sysaux.260.906027581' to '+SSDDG/shanghai/datafile/sysaux.dbf';  
alter database rename file '+DATADG/shanghai/datafile/undotbs2.263.906027613' to '+SSDDG/shanghai/datafile/undotbs2.dbf';  
alter database rename file '+DATADG/shanghai/datafile/users.264.906027619' to '+SSDDG/shanghai/datafile/users.dbf';  
alter database rename file '+DATADG/shanghai/datafile/webdata.dbf' to '+SSDDG/shanghai/datafile/webdata.dbf';  
alter database rename file '+DATADG/shanghai/datafile/undotbs3.dbf' to '+SSDDG/shanghai/datafile/undotbs3.dbf';  
alter database rename file '+DATADG/shanghai/datafile/undotbs4.dbf' to '+SSDDG/shanghai/datafile/undotbs4.dbf';  




select file#,name from v$datafile;  


迁移tempfile
select file#,name from v$tempfile; 
rman target /  
run{  
set newname for tempfile 1 to '+SSDDG/shanghai/tempfile/temp.dbf';  
switch tempfile all;  
}  




迁移logfile
 select a.member,a.group#,b.status,b.thread# from v$logfile a,v$log b where a.group#=b.group# order by 2;  


 alter database add logfile thread 1 group 5 '+DATADG' size 500M;  
 alter database add logfile thread 1 group 6 '+DATADG' size 500M;  


  alter database drop logfile group 1;  


迁移controlfile
select * from v$controlfile;
+DATADG/shanghai/controlfile/current.256.906027553




run{
copy current controlfile to '+SSDDG/shanghai/controlfile/control.ctl';
}


alter system set control_files='+SSDDG/shanghai/controlfile/control.ctl'  scope=spfile sid='*';


shutdown immediate;
startup mount;




alter system set control_files='+DATADG/zhongwc/controlfile/control01.ctl','+FRADG/zhongwc/controlfile/control02.ctl' scope=spfile sid='*';


alter system set db_create_file_dest='+SSDDG';  
alter system set db_recovery_file_dest='+FRADG';  
alter system set log_archive_dest_1='location=+FRADG';  
  
迁移spfile，修改ocr
create pfile from spfile;
create spfile='+SSDDG/shanghai/spfile.ora' from pfile;


srvctl config database -d shanghai -a 
srvctl modify database -d shanghai -p '+SSDDG/shanghai/spfile.ora'  
srvctl config database -d shanghai -a  




打开数据库：
srvctl start instance -d shanghai -i shanghai1 -o mount
sqlplus / as sysdba
alter database open resetlogs;
shutdown immediate;
srvctl start database -d shanghai


检查：
 show parameter spfile;
 show parameter control
 select * from v$logfile;
 select * from v$dbfile;
 select * from v$tempfile; 




最后删除旧的DISKGROUP
alter diskgroup datadg dismount;  
alter diskgroup fra dismount;  
  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/91975/viewspace-2051905/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/91975/viewspace-2051905/