Shulou(Shulou.com)05/31 Report--

This article shares with you the content of an example analysis of the migration of parts of the Oracle database to flash storage. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Environment: Oracle 11.2.0.4 RAC (2 nodes)

Note: assume that the new flash mount point is / flash (using a third-party cluster file system). If you use Oracle's ASM, all the / flash directories mentioned in this article can be considered to be the new flash disk group + FLASH.

1 implementation requirements

To improve database IO performance, all-flash array storage was purchased, but due to limited upfront budget, only part of the data can be migrated to flash storage (of course, if conditions permit, it is strongly recommended that the entire database be migrated to flash storage). After evaluation, it is finally confirmed that the tablespaces under the greatest pressure from IO during the peak hours of business will be migrated to flash storage as a whole. In addition, the redo and undo of the database will be migrated to flash storage.

Note: the scheme of this paper is actually written by me for the real demand of a production environment. Because the scene is universal, it is desensitized and published.

2 confirm migration tablespace information

Mainly according to the business peak (with historical DBTime as the main reference indicator), the tablespaces with the highest IO pressure are selected from the Tablespace IO Stats section of the corresponding AWR report. For example, I determined here that the tablespace that the database needs to migrate to flash memory is TBS_D_JINGYU.

Specific basis:

a. Spot check the AWR report of the weekday database, according to the Tablespace IO Stats section, TOP1 is TBS_D_JINGYU and is an order of magnitude higher than other tablespaces.

b. Spot check the AWR report of the business peak database, according to the Tablespace IO Stats section, TOP1 is also TBS_D_JINGYU in most cases, but because many tablespaces are busy during peak business periods, which is not as obvious as usual, but considering comprehensively, we still choose TBS_D_JINGYU tablespaces.

TBS_D_JINGYU tablespace size: the current size is 2160G, and the estimated amount of data is expected to grow by 30%, requiring at least 2810G of space.

3 confirm redo information

Migrate all redo log files to flash memory.

Many years ago, there has been a saying in the SSD world: it is not recommended to put redo on Oracle, and even the official Oracle documentation has a corresponding statement. So up to now, there are still many people who are afraid to put redo on SSD. In fact, this view has long been out of date, the current enterprise flash memory card has been tested, it can be used to store redo.

Confirm the redo information. I have 2 nodes here. The information related to RAC,redo is: there are two threads, each thread has seven sets of logs, and each log size is 2G. The total size is 28g. The group group number is 31-37 and 41-47.

4 confirm undo information

Confirm undo information:

TABLESPACE_NAME FREE_SPACE USED_SPACE TABLESPACE_SIZE USED_PERCENT---UNDOTBS1 176.668823 4.33117676 1812.39291534UNDOTBS2 47.9354248. 064575195 48. 134531657

As you can see, the UNDOTBS1 size is 181G and the UNDOTBS2 size is 48G. The total size is 229G.

5. Migrate tablespaces to flash memory

5.1 confirm that the flash space meets the minimum requirements

Suppose the flash mount directory is / flash; calculated according to the 30% reserved growth space of the table space, corresponding to the minimum value of the flash mount directory space:

Minimum space requirement for database migration to flash memory: top tablespace + redo + undo = 2810G + 28G + 229G=3067G

Note: if the data table space and the corresponding index table space are planned separately, it is strongly recommended that the index table space corresponding to the data table space with the highest IO be migrated together, so that the total space demand will also be added to the demand for the corresponding index table space.

5.2 Tablespace migration to flash memory

Use backup as copy tablesapce to implement the migration of tablespace TBS_D_JINGYU:

RMAN > backup as copy tablespace TBS_D_JINGYU format'/ flash/oradata/jydb5/';SQL > alter tablespace TBS_D_JINGYU offline;RMAN > switch tablespace TBS_D_JINGYU to copy;RMAN > recover tablespace TBS_D_JINGYU;SQL > alter tablespace TBS_D_JINGYU online

6 redo migration to flash memory

Add redo logfile and delete historical redo.

Operation commands for redo migration to flash memory:

-- add redo logfilealter database add logfile THREAD 1 group 11'/ flash/oradata/jydb5/redo11.log' SIZE 2147483648 alter database add logfile THREAD 1 group 12'/ flash/oradata/jydb5/redo12.log' SIZE 2147483648 alter database add logfile THREAD 1 group 13'/ flash/oradata/jydb5/redo13.log' SIZE 2147483648 alter database add logfile THREAD 1 group 14'/ flash/oradata/jydb5/redo14.log' SIZE 2147483648 alter database add logfile THREAD 1 group 15'/ flash/oradata/jydb5/redo15.log' SIZE 2147483648 Alter database add logfile THREAD 1 group 16'/ flash/oradata/jydb5/redo16.log' SIZE 2147483648 alter database add logfile THREAD 1 group 17'/ flash/oradata/jydb5/redo17.log' SIZE 2147483648 alter database add logfile THREAD 2 group 21'/ flash/oradata/jydb5/redo21.log' SIZE 2147483648 alter database add logfile THREAD 2 group 22'/ flash/oradata/jydb5/redo22.log' SIZE 2147483648 alter database add logfile THREAD 2 group 23'/ flash/oradata/jydb5/redo23.log' SIZE 2147483648 Alter database add logfile THREAD 2 group 24'/ flash/oradata/jydb5/redo24.log' SIZE 2147483648 alter database add logfile THREAD 2 group 25'/ flash/oradata/jydb5/redo25.log' SIZE 2147483648 alter database add logfile THREAD 2 group 26'/ flash/oradata/jydb5/redo26.log' SIZE 2147483648 alter database add logfile THREAD 2 group 27'/ flash/oradata/jydb5/redo27.log' SIZE 2147483648 Alter database drop logfile group 35, alter database drop logfile group 36, alter database drop logfile group 37, alter database drop logfile group 41, alter database drop logfile group 42, alter database drop logfile group 43, alter database drop logfile group 44, alter database drop logfile group 45, alter database drop logfile group 46, alter database drop logfile group 47

7 undo migration to flash memory

The essence of undo migration is to delete historical undo tablespaces by creating new undo tablespaces and then modifying the default undo tablespaces.

Migrate undo tablespaces to flash:

Create undo tablespace UNDOTBS11 datafile'/ flash/oradata/jydb5/undotbs101.dbf' SIZE 30G alter create undo tablespace UNDOTBS21 datafile'/ flash/oradata/jydb5/undotbs201.dbf' SIZE 24G alter tablespace undotbs11 add datafile'/ flash/oradata/jydb5/undotbs102.dbf' SIZE 30G alter tablespace undotbs11 add datafile'/ flash/oradata/jydb5/undotbs103.dbf' SIZE 30G alter tablespace undotbs11 add datafile'/ flash/oradata/jydb5/undotbs104.dbf' SIZE 30G alter tablespace undotbs11 add datafile'/ flash/oradata/jydb5/undotbs105.dbf' SIZE 30G Alter tablespace undotbs11 add datafile'/ flash/oradata/jydb5/undotbs106.dbf' SIZE 31G alter tablespace undotbs21 add datafile'/ flash/oradata/jydb5/undotbs202.dbf' SIZE 24G Mattel show parameter undoalter system set undo_tablespace=UNDOTBS11 sid = 'jydb51';alter system set undo_tablespace=UNDOTBS21 sid =' jydb52';--select SEGMENT_NAME, STATUS, TABLESPACE_NAME from dba_rollback_segs; (confirm offline) drop tablespace undotbs1 including contents and datafiles;drop tablespace undotbs2 including contents and datafiles

8 modify the parameters of the standby library

There is also an ADG repository in the database environment.

For slave libraries, you mainly need to modify the parameter values of db_file_name_convert and log_file_name_convert to increase the mapping to the new directory.

Thank you for reading! This is the end of the article on "sample Analysis of moving Oracle Database to Flash Storage". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.