VMCD.ORG

Focus on database mail:ylouis83#gmail.com

Exadata migration tips

Posted by admin on August 7th, 2012

Exadata migration完成 目前采用的是物理DG switch over的方式 采用11g active duplicate 实现整个数据大小为2T左右 总共耗时约为10个小时 每秒达到近60M
采取这种方法的好处为:
1:主库无需停机
2:可以采用exadata最佳性能AU_SIZE=4M
3:复制过程中不会产生集中化IO,不过还是建议在夜里进行操作
4:可以直接switch over 使用原来的库为备库

处理完毕后 我们可以查看主机状态:

[grid@dm01db01 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA_DM01.dg
               ONLINE  ONLINE       dm01db01                                     
               ONLINE  ONLINE       dm01db02                                     
ora.DBFS_DG.dg
               ONLINE  ONLINE       dm01db01                                     
               ONLINE  ONLINE       dm01db02                                     
ora.LISTENER.lsnr
               ONLINE  ONLINE       dm01db01                                     
               ONLINE  ONLINE       dm01db02                                     
ora.RECO_DM01.dg
               ONLINE  ONLINE       dm01db01                                     
               ONLINE  ONLINE       dm01db02                                     
ora.asm
               ONLINE  ONLINE       dm01db01                 Started             
               ONLINE  ONLINE       dm01db02                 Started             
ora.gsd
               OFFLINE OFFLINE      dm01db01                                     
               OFFLINE OFFLINE      dm01db02                                     
ora.net1.network
               ONLINE  ONLINE       dm01db01                                     
               ONLINE  ONLINE       dm01db02                                     
ora.ons
               ONLINE  ONLINE       dm01db01                                     
               ONLINE  ONLINE       dm01db02                                     
ora.registry.acfs
               ONLINE  ONLINE       dm01db01                                     
               ONLINE  ONLINE       dm01db02                                     
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       dm01db02                                     
ora.cvu
      1        ONLINE  ONLINE       dm01db02                                     
ora.dm01db01.vip
      1        ONLINE  ONLINE       dm01db01                                     
ora.dm01db02.vip
      1        ONLINE  ONLINE       dm01db02                                     
ora.edw1.db
      1        ONLINE  ONLINE       dm01db01                 Open                
      2        ONLINE  ONLINE       dm01db02                 Open                
ora.oc4j
      1        ONLINE  ONLINE       dm01db02                                     
ora.scan1.vip
      1        ONLINE  ONLINE       dm01db02                                     
[grid@dm01db01 ~]$ 


SQL> /

GROUP_NUMBER NAME							  VALUE
------------ ------------------------------------------------------------ ------------------------------------------------------------
	   1 access_control.enabled					  FALSE
	   1 access_control.umask					  066
	   1 au_size							  4194304
	   1 cell.smart_scan_capable					  TRUE
	   1 compatible.asm						  11.2.0.2.0
	   1 compatible.rdbms						  11.2.0.2.0
	   1 disk_repair_time						  3.6h
	   1 idp.boundary						  auto
	   1 idp.type							  dynamic
	   1 sector_size						  512
	   1 template.ARCHIVELOG.mirror_region				  0

可以看到 au_size=4194304 使用了oracle推荐的best au_size for exadata 对于4M这个数值 可以参考这篇文章 exadata AU_SIZE

通过上图可以看到au size 4m对于exadata的 smart scan io 提升比较明显,另外测试发现11g active duplicate 对于 “cell physical IO bytes saved during optimized RMAN file restore”并没有影响

SQL> SELECT name, value/1024/1024 MB from v$sysstat a WHERE
  2  a.name = 'physical read total bytes' OR
a.name = 'physical write total bytes' OR
a.name = 'cell physical IO interconnect bytes' OR
a.name = 'cell physical IO bytes eligible for predicate offload' OR
a.name = 'cell physical IO bytes saved during optimized file creation' OR
a.name = 'cell physical IO bytes saved during optimized RMAN file restore' OR
a.name = 'cell IO uncompressed bytes' OR
a.name = 'cell physical IO interconnect bytes returned by smart scan' OR
a.name = 'cell physical IO bytes saved by storage index';  3    4    5    6    7    8    9   10  

NAME									 MB
---------------------------------------------------------------- ----------
physical read total bytes					 2043013.32
physical write total bytes					 187975.958
cell physical IO interconnect bytes				 1497804.22
cell physical IO bytes saved during optimized file creation		  0
cell physical IO bytes saved during optimized RMAN file restore 	  0
cell physical IO bytes eligible for predicate offload		 1042550.99
cell physical IO bytes saved by storage index			 127372.633
cell physical IO interconnect bytes returned by smart scan	 119367.207
cell IO uncompressed bytes					 915887.641

关于v$sysstat对于ed cell的statistics 可以参照下表

Statistic Description

cell flash cache read hits

The number of read requests that were a cache hit on exadata flash cache.

cell IO uncompressed bytes

The total size of uncompressed data that is processed on the cell. For scan on hybrid-columnar-compressed tables, this statistic is the size of data after decompression.

cell physical IO interconnect bytes returned by smart scan

The number of bytes that are returned by the cell for Smart Scan only, and does not include bytes for other database I/O.

cell physical IO bytes saved by storage index

The number of bytes saved by storage index.

cell physical IO bytes eligible for predicate offload

The total number of I/O bytes processed with physical disks when processing was offloaded to the cell.

cell physical IO bytes sent directly to DB node to balance CPU usage

The number of I/O bytes sent back to the database server for processing due to CPU usage on Exadata Cell.

cell physical IO bytes saved during optimized file creation

The number of I/O bytes saved by the database host by offloading the file creation operation to cells. This statistic shows the Exadata Cell benefit due to optimized file creation operations.

cell physical IO bytes saved during optimized RMAN file restore

The number of I/O bytes saved by the database host by offloading the RMAN file restore operation to cells. This statistic shows the Exadata Cell benefit due to optimized RMAN file restore operations.

cell physical IO interconnect bytes

The number of I/O bytes exchanged over the interconnection between the database host and cells.

physical read requests optimized

Total number of read requests satisfied either by using Exadata Smart Flash Cache or storage index.

physical read total bytes

Total amount of I/O bytes for reads processed with physical disks. This includes when processing was offloaded to the cell and when processing was not offloaded.

physical read total bytes optimized

Total number of bytes read from Exadata Smart Flash Cache or storage index.

physical write total bytes

Total amount of I/O bytes for writes processed with physical disks. This includes when processing was offloaded to the cell and when processing was not offloaded.

另外官方也给出了几种解决exadata migration的方案

Tip:

ASM Online Migration: This method is only applicable if your database is already using ASM and you do not need to adjust the ASM allocation unit (AU) size. 
To use this method you must also be able to connect your current database storage to Database Machine and migrate the database instances to Database 
Machine. After migrating the database instances, the data migration is very simple, just add new Exadata-based grid disks to your ASM disk groups and drop 
existing disks from your ASM disk groups to migrate the data.

当然迁移的方法还有很多OGG,DSG,甚至DDS。但是需要说明的一点是当使用ASM Online Migration时 虽然不严格要求AU_SIZE(best practice for 4M in ED) 相同 但是不同AU_SIZE的migration 会影响迁移之后的性能

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>