Oracle database part moved to flash storage solution

Oracle database part moved to flash storage solution

• 1. Implementation requirements
• 2. Confirm the migration of tablespace information
• 3. Confirm Redo Information
• 4. Confirm the Undo information
• 5. Table space migration to flash memory
• 6.redo migration to flash memory
• 7.undo migration to flash memory
• 8. Prepare the library to modify the parameters

Environment: Oracle 11.2.0.4 RAC (2 nodes)
Note: Assuming that the new flash mount point is/flash (using a third-party clustered file system), if you are using ASM for Oracle, all of the/flash directories mentioned in this article can be assumed to be the new Flash disk group +flash.

1 Implementation Requirements

All-flash array storage has been procured to improve database IO performance, but because of the limited upfront budget, only part of the data can be migrated to flash storage (although, of course, it is strongly recommended to migrate the entire database to flash memory if conditions permit). The final confirmation is that the maximum IO-pressure table space is migrated to the flash storage at peak times, and the redo and undo of the database are migrated to flash storage.
Note: This project is actually my real demand for a production environment, because the scenario is universal, so after the release of the sensitive.

2 Confirm Migration Tablespace information

Mainly based on the peak of business (historical dbtime as the main reference indicators), from the corresponding AWR report tablespace IO stats section to filter out the maximum IO pressure table space. For example, I'm here to make sure that the table space that the database needs to migrate to Flash is Tbs_d_jingyu.

Specific basis:
A. 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 table spaces.
b. Check the AWR report of the peak period database, according to the tablespace IO stats part, TOP1 Most of the situation is also Tbs_d_jingyu, but because the business peak many table space is busy, as usual obvious, but comprehensive consideration, or choose Tbs_d_ Jingyu table space.

Tbs_d_jingyu table Space Size: The current size is 2160G, the estimated data volume at 30% growth rate, at least the space required for 2810G.

3 Confirming Redo Information

Migrate all redo log files to flash memory.
Many years ago, there was a saying in the Oracle world that it was not recommended to put redo on an SSD, even Oracle's official documents had a corresponding claim. So until now there are still many people who dare not put redo on SSD. In fact, this point of view is already outdated, the current enterprise-class Flash card is actually tested, is completely can be used to store redo.

Confirm Redo information, here I am the 2 node Rac,redo related information is: A total of two threads, each thread has 7 sets of logs, each log size is 2G. Total size 28G. The group number is 31-37, 41-47.

4 Confirm 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

As you can see, UNDOTBS1 size 181G,UNDOTBS2 size 48G. Total size 229G.

5 table space Migration to flash memory

5.1 Confirm that the flash space meets the minimum requirements
Assume that the Flash mount directory is/flash, and that the growth space is reserved by table space 30%, corresponding to the minimum value of the Flash mount directory space:
Minimum space required 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 highly recommended that the index table space corresponding to the highest IO data table space be migrated together, so that the total space requirement is added to the requirements of the corresponding index table space.

5.2 Table space 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

Added redo logfile, delete history redo.
Redo operation commands for migrating to Flash:

--New Redo Logfilealter Database add logfile THREAD 1 Group One '/flash/oradata/jydb5/redo11.log ' SIZE 2147483648;alter datab ASE Add logfile Thread 1 group/flash/oradata/jydb5/redo12.log ' SIZE 2147483648;alter database Add logfile Thread 1 gr OUP '/flash/oradata/jydb5/redo13.log ' SIZE 2147483648;alter database add logfile THREAD 1 Group '/flash/oradata/jydb 5/redo14.log ' size 2147483648;alter database add logfile THREAD 1 Group '/flash/oradata/jydb5/redo15.log ' size 21474836 48;alter database Add logfile THREAD 1 group/flash/oradata/jydb5/redo16.log ' SIZE 2147483648;alter database Add Logfi Le THREAD 1 group/flash/oradata/jydb5/redo17.log ' SIZE 2147483648;alter database add logfile THREAD 2 Group '/flas H/oradata/jydb5/redo21.log ' SIZE 2147483648;alter database add logfile THREAD 2 Group '/flash/oradata/jydb5/ Redo22.log ' size 2147483648;alter database add logfile THREAD 2 Group '/flash/oradata/jydb5/redo23.log ' size 2147483648 ; ALTER DATABASE add logfile THREAD2 Group '/flash/oradata/jydb5/redo24.log ' SIZE 2147483648;alter database add logfile THREAD 2 Group '/flash/oradata/ Jydb5/redo25.log ' size 2147483648;alter database add logfile THREAD 2 Group '/flash/oradata/jydb5/redo26.log ' size 2147 483648;alter database Add logfile THREAD 2 group/flash/oradata/jydb5/redo27.log ' SIZE 2147483648;--delete history redoalter dat Abase 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 Gro  Up 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 D ROP LogFile Group 47;

7 Undo migration to Flash

Undo migration essentially removes the history undo table space by creating a new undo tablespace and then modifying the settings of the default undo tablespace.
Undo table Space Migration to Flash:

Create undo tablespace UNDOTBS11 DataFile '/flash/oradata/jydb5/undotbs101.dbf ' SIZE 30g;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;--show parameter Undoalter system set UNDO_TABLESPACE=UNDOTBS11 sid = ' jydb51 '; alter system set UNDO_TABLESPACE=U NDOTBS21 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 Prepare Library Modification parameters

There is also a ADG repository in the database environment.
For the repository, the main need to modify the Db_file_name_convert and Log_file_name_convert parameter values, to increase the mapping of the new directory relationship.

Oracle database part moved to flash storage solution