Exadata Temp Tablespace Migration To Flash Disk

Hi,

I tell you how to migrate temp tablespace to flash disk . Because , temp waiting make happen performance problem.

Image version is 18.1.17 . This version is enough for temp using on flash disk . Normally, exadata smart storage itself decides temp tablespace using on flash disk. But if it can not be, we can with following code.

Before Oracle Exadata Storage Server released 12.2.1.1.0, temp writes were not cached in flash cache. Both temp writes and subsequent temp reads were from hard disk only.

Let’s start;

#Create disk group from flash disk . Temp read write events will be decreased.
#X6 cell storage .

#All commands are on compute node1 as root .

#Flash area was dropped.

dcli -g cell_group -l root cellcli -e "alter flashcache all flush"
dcli -g cell_group -l root cellcli -e "drop flashcache"

 

#New flash disk total size for one cell storage, 10t

dcli -g cell_group -l root cellcli -e "create flashcache all size=10240g"
dcli -g cell_group -l root cellcli -e "list flashcache detail"
dcli -g cell_group -l root cellcli -e "list celldisk attributes name,size,freespace where diskType ='FlashDisk'"

#Griddisk were created from flash disks

 

dcli -g cell_group -l root cellcli -e "create griddisk all flashdisk prefix=flashdiskx6"
dcli -g cell_group -l root cellcli -e "list griddisk attributes diskType, name where diskType=FlashDisk"
dcli -g cell_group -l root cellcli -e "list griddisk attributes name,offset,size where diskType=FlashDisk"

 

#Disk group will be created.

select NAME, GROUP_NUMBER, DISK_NUMBER, STATE,HEADER_STATUS,MOUNT_STATUS, PATH , failgroup, TOTAL_MB ,OS_MB, FREE_MB,VOTING_FILE from v$asm_disk where HEADER_STATUS!='MEMBER' order by GROUP_NUMBER , DISK_NUMBER , NAME;


CREATE diskgroup FLASH_ETL normal redundancy disk 'o/*/flashdiskx6*' attribute 'compatible.rdbms'='11.2.0.2.0','compatible.asm'='11.2.0.4.0', 'cell.smart_scan_capable'='TRUE', 'au_size'='4M';

 

#Disk will be mounted on other instances

alter diskgroup FLASH_ETL mount;

#Tablespace will be created on database

CREATE TEMPORARY TABLESPACE ETL_TEMP_TS TEMPFILE
'+FLASH_ETL' SIZE 1G AUTOEXTEND ON NEXT 4G MAXSIZE UNLIMITED,
'+FLASH_ETL' SIZE 1G AUTOEXTEND ON NEXT 4G MAXSIZE UNLIMITED,
'+FLASH_ETL' SIZE 1G AUTOEXTEND ON NEXT 4G MAXSIZE UNLIMITED,
'+FLASH_ETL' SIZE 1G AUTOEXTEND ON NEXT 4G MAXSIZE UNLIMITED,
'+FLASH_ETL' SIZE 1G AUTOEXTEND ON NEXT 4G MAXSIZE UNLIMITED
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

#User default temporary tablespace will be set new tablespace.

ALTER USER etl_user TEMPORARY TABLESPACE ETL_TEMP_TS;

 

Do you want to learn Exadata detailed, then read the following articles.

Oracle Exadata Tutorials for Beginners – Magic Database Machine

 

 350 views last month,  6 views today

About Fatih Gençali

- I have supported as Oracle Dba for more than 6.5 years. - I worked in 24x7 production and test environment. - I have 12C OCP certificate. - I have europass diploma supplement. - Saving operations - I have supported databases that are telecommunication , banking, insurance, financial, retail and manufacturing, marketing, e-invoicing . - Providing aligment between prod , prp , stb , dev - Providing management and performance tuning for app and database machines (linux) - Performance tuning and sql tuning - Consolidations, Migration (expdp,xtts,switchover vb...) , installation, patch , upgrade , dataguard , shell script writing , backup restore , exadata management , performans management , security management ,goldengate operations - Resolving performance and security problems for databases and linux machines - I managed oracle 10g/11g/12c databases (dev/test/prp/snap/prod/stby) on Linux/HP/AIX/Solaris O.S - Pl/sql operations , supported shell script, (for aligments and others) - Providing highly available it (software-hardware) systems, especially database systems. - Managing and monitoring availabilities and operations of all systems . - Goldengate operations (oracle to oracle , oracle to bigdata (hdfs , kafka)) - Exadata operations (cell management,upgrade,switchover) - My work processes is according to itil. - Preparing automation for everything to reduce human resource requirement and routine works.

Check Also

blank

Alter System Flush Shared pool in Oracle

Hi, I will explain Alter System Flush Shared pool in Oracle in this post.  1,457 views …

Leave a Reply