How to migrate part of Oracle Database to flash storage

How to migrate part of Oracle Database to flash storage

Environment: Oracle 11.2.0.4 RAC (2 nodes)

Note: If the new flash mount point is/flash (a third-party cluster file system is used), if the Oracle ASM is used, all the/flash directories mentioned in this article can be regarded as the new FLASH disk group + flash.

1. Implementation requirements

To improve the I/O performance of the database, I purchased a full-flash storage array. However, due to limited budget in the early stage, I can only migrate some data to the flash storage (if conditions permit, it is strongly recommended to migrate all the databases to the flash memory ). After evaluation, it was confirmed that the tablespace with the highest I/O pressure during the business peak hours was migrated to the flash storage as a whole. In addition, the redo and undo databases were migrated to the flash storage.

Note: The solution in this article is actually compiled based on my actual needs for a production environment. As this scenario is universal, it is desensitized and published later.

2. Confirm the tablespace information for migration.

Based on business peaks (with historical DBTime as the main reference indicator), The Tablespace with the highest IO pressure is filtered out from the Tablespace IO Stats section in the corresponding AWR report. For example, here I confirm that the tablespace to be migrated to the flash memory is TBS_D_JINGYU.

Detailed basis:

A. spot check on the AWR Report of the daily database. According to the Tablespace IO Stats section, TOP1 is TBS_D_JINGYU, which is an order of magnitude higher than other tablespaces.

B. spot check of the AWR Report of the database during business peak hours. According to the Tablespace IO Stats section, TOP1 is mostly TBS_D_JINGYU. However, because many tablespaces are busy during business peak hours, it is not as obvious as usual, select the TBS_D_JINGYU tablespace.

TBS_D_JINGYU tablespace size: the current size is 2160 GB. The estimated data size increases by 30%, with at least 2810 GB of space required.

3. Confirm redo Information

Migrate all redo log files to the flash memory.

Many years ago, there was a saying in the Oracle field that we do not recommend placing redo on SSD. Even the official Oracle documents have a corresponding saying. So till now there are still many people who dare not put redo on SSD. In fact, this idea is outdated. Currently, enterprise-level flash cards have been tested to store redo logs.

Confirm the redo information. Here I am a 2-node RAC. The redo information is: There are two threads in total, and each thread has seven groups of logs, each of which is 2 GB in size. The total size is 28 GB. The group number is 31-37,41-47.

4. Confirm the undo information.

Confirm the undo information:

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

The size of UNDOTBS1 is 181 GB, and that of UNDOTBS2 is 48 gb. Total size: 229 GB.

5. migrate tablespace to flash memory

5.1 confirm that the flash space meets minimum requirements

Assume that the flash Mount directory is/flash; calculated based on the tablespace 30% reserved growth space, corresponding to the minimum size of the flash Mount directory space:

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

Note: If the data table space and the corresponding index tablespace are planned separately, we strongly recommend that you migrate the index tablespace corresponding to the data table space with the highest I/O, in this way, the demand for the total space must be added to the corresponding index tablespace.

5.2 migrate tablespace to flash memory

Use backup as copy tablesapce to migrate the 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. migrate redo to flash memory

Add a redo logfile to delete the redo history.

Operation Command for migrating redo 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; -- delete redoalter database drop logfile group 31; alter database drop logfile group 32; alter database drop logfile group 33; alter database drop logfile group 34; 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 create a new undo tablespace, modify the default undo tablespace settings, and finally Delete the historical undo tablespace.

Migrate undo tablespace to flash memory:

Create undo tablespace UNDOTBS11 datafile '/flash/oradata/jydb5/undotbs101.dbf 'size 30 GB; create undo tablespace UNDOTBS21 datafile'/flash/oradata/jydb5/undotbs201.dbf' SIZE 24 GB; alter tablespace undotbs11 add datafile '/flash/oradata/jydb5/undotbs102.dbf' SIZE 30G; alter tablespace undotbs11 add datafile '/flash/oradata/jydb5/cmd' SIZE 30G; alter tablespace undotbs11 add datafile '/flash/oradata/jydb5/undotbs104.dbf' SIZE 30G; alter tablespace undotbs11 add datafile '/flash/oradata/jydb5/cmd' SIZE 30G; alter tablespace undotbs11 add datafile '/flash/oradata/jydb5/undotbs106.dbf' SIZE 31G; alter tablespace undotbs21 add datafile'/flash/oradata/jydb5/comment 'size 24G; -- 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 Region; (confirm offline) drop tablespace undotbs1 including contents and datafiles; drop tablespace undotbs2 including contents and datafiles;

8. Modify parameters of the standby Database

There is also an ADG slave database in the database environment.

For standby databases, you must modify the values of the db_file_name_convert and log_file_name_convert parameters to increase the ing relationship between the new directories.

The implementation method for migrating some of the above Oracle databases to flash storage is all the content that I have shared with you. I hope you can give us a reference and support for more.