Author Archives: Zed DBA

Adding a Datafile to Temp Tablespace

Posted on by Standard 2

When monitoring Tablespace Usage (see my Tablespace Usage blog post for more info), there comes a point when you need to add a datafile to the temp tablespace to allow for growth and more importantly get below a monitoring threshold for example in OEM or OpsView.

Query to see Current Temp Datafiles State

To see the current state of the temp datafiles:

set pages 999
set lines 400
col FILE_NAME format a75
select d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
from dba_temp_files d,
 v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;

Please Note: For pluggable databases, please ensure you are in the right PDB as temp datafiles can be at container level and/or pluggable database level.

Output from Current Temp Datafiles State

This is the output you will get from the query:

TABLESPACE_NAME FILE_NAME SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
------------------------------ --------------------------------------------------------------------------- ---------- --- ---------- ---------------
TEMP +DATAC1/VERS/TEMPFILE/temp.451.891367325 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.452.891367321 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.454.891367305 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.455.891367301 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.457.891367299 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.458.891367295 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.459.891367159 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.461.891367165 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.463.891367201 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.465.891367203 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.466.891367207 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.468.891367223 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.469.891367227 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.471.891367243 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.472.891367251 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.473.891367255 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.475.891367261 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.477.891367289 32767 YES 32767 1024
...

38 rows selected.

SQL>

The … represents the several lines removed to make the output readable 🙂

Add Temp Datafile to Temp Tablespace

To add a temp datafile to ‘TEMP‘ to be initially 10G, auto extendable by 1G to maxsize of 32Gb:

ALTER TABLESPACE TEMP ADD TEMPFILE '+DATAC1' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

See Oracle Documentation for more info and Syntax:
https://docs.oracle.com/database/121/SQLRF/statements_3002.htm

Once added you’ll see the temp datafile in the above query:

TABLESPACE_NAME FILE_NAME SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB
------------------------------ --------------------------------------------------------------------------- ---------- --- ---------- ---------------
TEMP +DATAC1/VERS/TEMPFILE/temp.451.891367325 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.452.891367321 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.454.891367305 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.455.891367301 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.457.891367299 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.458.891367295 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.459.891367159 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.461.891367165 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.463.891367201 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.465.891367203 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.466.891367207 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.468.891367223 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.469.891367227 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.471.891367243 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.472.891367251 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.473.891367255 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.475.891367261 32767 YES 32767 1024
TEMP +DATAC1/VERS/TEMPFILE/temp.477.891367289 32767 YES 32767 1024
...
TEMP +DATAC1/VERS/TEMPFILE/temp.843.973079825 10240 YES 32767 1024

39 rows selected.

SQL>

Related Blog Posts:
Querying Tablespace Usage
Adding a Datafile to Tablespace

Updates
27th April 2018: Change the query to use v$tempfile instead of v$datafile as doesn’t work for PDBs.

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Stop password for user accounts expiring on Exadata

Posted on by Standard 1

Depending on how the Oracle Exadata Machine was setup, the password for user accounts can expire thus requiring the password to be changed.

This has the knock on effect of the crontab not being accessible and more importantly jobs do not run:

[oracle@v1ex1dbadm01 ~]$ crontab -l

Authentication token is no longer valid; new one required
You (oracle) are not allowed to access to (crontab) because of pam configuration.
[oracle@v1ex1dbadm01 ~]$

You can check the pam configuration for the password expiry as shown below as the root user:

[root@v1ex1dbadm01 ~]# chage -l oracle
Last password change : Dec 11, 2017
Password expires : Mar 11, 2018
Password inactive : never
Account expires : never
Minimum number of days between password change : 1
Maximum number of days between password change : 90
Number of days of warning before password expires : 7
[root@v1ex1dbadm01 ~]#

We can see the password expired on the 11th March 2018, hence why the crontab jobs are not running since then.

To change, so the password doesn’t expire, use chage as shown below:

[root@v1ex1dbadm01 ~]# chage -d 9999 -E -1 -m 0 -M -1 oracle

The manual page for chage explains the switches:

-d, --lastday LAST_DAY
 Set the number of days since January 1st, 1970 when the password was last changed. The date may also be expressed in the format YYYY-MM-DD (or the format more commonly used in your area). If the LAST_DAY is set to 0 the user is forced to change his password on the next log on.

-E, --expiredate EXPIRE_DATE
 Set the date or number of days since January 1, 1970 on which the user´s account will no longer be accessible. The date may also be expressed in the format YYYY-MM-DD (or the format more commonly used in your area). A user whose account is locked must contact the system administrator before being able to use the system again.

Passing the number -1 as the EXPIRE_DATE will remove an account expiration date.

-m, --mindays MIN_DAYS
 Set the minimum number of days between password changes to MIN_DAYS. A value of zero for this field indicates that the user may change his/her password at any time.

-M, --maxdays MAX_DAYS
 Set the maximum number of days during which a password is valid. When MAX_DAYS plus LAST_DAY is less than the current day, the user will be required to change his/her password before being able to use his/her account. This occurrence can be planned for in advance by use of the -W option, which provides the user with advance warning.

Passing the number -1 as MAX_DAYS will remove checking a password´s validity.

Now, when re-checking the password expiry, you can see it’s changed to ‘never‘:

[root@v1ex1dbadm01 ~]# chage -l oracle
Last password change : May 01, 2008
Password expires : never
Password inactive : never
Account expires : never
Minimum number of days between password change : 0
Maximum number of days between password change : -1
Number of days of warning before password expires : 7
[root@v1ex1dbadm01 ~]#

And we didn’t need to change the password for the user and the crontab job work again 🙂

This doesn’t just apply to Exadata but to Linux.

See Related MOS Note:
Expiry of user accounts on Oracle Linux 5 (Doc ID 2327855.1)

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

Oracle Exadata Smart Flash Logging

Posted on by Standard 2

What is Exadata Smart Flash Logging?

In an OLTP environment, it is crucial to have fast response times to redo log writes i.e. low latency.  When multiplexing redo logs for high availability i.e. to protect against hardware failure, redo log writes are only acknowledged when redo is written to all redo log members i.e when the slowest disk completes the write.  By this nature, whenever a disk slows down even if for a moment it can have impact on redo log performance and throughput.

Flash alone can’t resolve this issue as flash can also momentarily slow down due to issues in erase cycles or wear leveling and remember the acknowledgement is only given when the redo is written to all redo log members.

Exadata Smart Flash Logging, is the feature that writes to both hard disk and flash with the acknowledgement given as soon as either completes the write, thus improving response time and throughput.  So if a write is slow to hard disk the flash will give a quicker acknowledgement but when flash is experiencing a slow down due to erase cycles or wear leveling then the hard disk will acknowledge, smoothing out response times.

The Exadata Smart Flash Cache isn’t permanent but a temporary store to provide fast response times by storing redo until it’s safely written to disk.

No changes are required to redo log configuration and is transparent to database and recovery.

How to enable Smart Flash Logging?

It’s enabled out the box or for older systems it’s enabled when applying cell patch version 11.2.2.4 and also requires Database 11.2.0.2 Bundle Patch 11 or higher.

How to disable Smart Flash Logging?

This shouldn’t be done unless instructed to do so by Oracle Support or Development.

How much flash is used by Smart Flash Logging?

By default just 512Mb is used per cell, which should be sufficient for most situations.   It’s a small investment for huge performance benefit.  Statistics record the number of successful write and unsuccessful writes due to the temporary space filled.  In which case the size may need to be increased.  Also I/O Resource Manager (IORM) can be used to disable Smart Flash Logging for none critical databases.

Do standby redo logs use Smart Flash Logging?

Yes, standby redo logs benefit from Smart Flash Logging just as redo logs as long as cell patch 11.2.2.4 or higher is applied and Database 11.2.0.2 Bundle Patch 11 or higher is applied.

How to check that Smart Flash Logging is configured?

Using CellCLI run “LIST FLASHLOG DETAIL” and if output is returned as shown below with the details, then this means that Smart Flash Logging is configured:

[root@v1ex1dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root cellcli -e "list flashlog detail"
 v1ex1celadm01: name: v1ex1celadm01_FLASHLOG
 v1ex1celadm01: cellDisk: FD_00_v1ex1celadm01,FD_01_v1ex1celadm01
 v1ex1celadm01: creationTime: 2015-06-28T17:52:43+01:00
 v1ex1celadm01: degradedCelldisks:
 v1ex1celadm01: effectiveSize: 512M
 v1ex1celadm01: efficiency: 100.0
 v1ex1celadm01: id: 366421ec-bf77-499e-870f-f0cf5390343e
 v1ex1celadm01: size: 512M
 v1ex1celadm01: status: normal
 v1ex1celadm02: name: v1ex1celadm02_FLASHLOG
 v1ex1celadm02: cellDisk: FD_01_v1ex1celadm02,FD_00_v1ex1celadm02
 v1ex1celadm02: creationTime: 2015-06-28T17:52:44+01:00
 v1ex1celadm02: degradedCelldisks:
 v1ex1celadm02: effectiveSize: 512M
 v1ex1celadm02: efficiency: 100.0
 v1ex1celadm02: id: 9f670843-c9cc-4156-a32e-8d23fa79cdb8
 v1ex1celadm02: size: 512M
 v1ex1celadm02: status: normal
 v1ex1celadm03: name: v1ex1celadm03_FLASHLOG
 v1ex1celadm03: cellDisk: FD_01_v1ex1celadm03,FD_00_v1ex1celadm03
 v1ex1celadm03: creationTime: 2015-06-28T17:52:33+01:00
 v1ex1celadm03: degradedCelldisks:
 v1ex1celadm03: effectiveSize: 512M
 v1ex1celadm03: efficiency: 100.0
 v1ex1celadm03: id: 749bada6-8ae2-4c51-8410-97622f9a9532
 v1ex1celadm03: size: 512M
 v1ex1celadm03: status: normal
[root@v1ex1dbadm01 ~]#

For more info:
Exadata Smart Flash Logging FAQ (Doc ID 1372894.1)
Oracle Exadata Whitepaper:  Exadata Smart Flash Cache Features and the Oracle Exadata Database Machine

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

How to fix queries on DBA_FREE_SPACE that are slow

Posted on by Standard 15

I found myself in a situation where OpsView, a monitoring tool, was having difficulty monitoring the tablespaces for a particular pluggable database.

Upon investigation it was found the queries against the dictionary table DBA_FREE_SPACE were taking a very long time:

SQL> set timing on
SQL> select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = 'USERS';

NVL(SUM(DFS.BYTES)/1024/1024,0)
-------------------------------
 70.75

Elapsed: 00:00:10.98

There are 60 tablespaces in this pluggable database, which the time varied querying each tablespace, but was by far where most the time was spent.

I wrote a PL/SQL block to mimic Opsview as I didn’t want to create an object (procedure) in this customer’s database:

SET SERVEROUTPUT ON
SET TIMING ON
DECLARE
 cursor ts_names is select tablespace_name from dba_tablespaces where contents != 'TEMPORARY';
 sql_used VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 sql_free VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 sql_max VARCHAR(200) := 'select sum(maxbytes-bytes)/1024/1024 from dba_data_files where AUTOEXTENSIBLE = ''YES'' and maxbytes>bytes and tablespace_name = ''text_string''';
 num_out NUMBER;
BEGIN
 FOR ts_name in ts_names
 LOOP
 --sql for used space
 EXECUTE IMMEDIATE replace(sql_used, 'text_string', ts_name.tablespace_name) into num_out;
 dbms_output.put_line(replace(sql_used, 'text_string', ts_name.tablespace_name));
 dbms_output.put_line(num_out);
 --sql for free space
 EXECUTE IMMEDIATE replace(sql_free, 'text_string', ts_name.tablespace_name) into num_out;
 dbms_output.put_line(replace(sql_free, 'text_string', ts_name.tablespace_name));
 dbms_output.put_line(num_out);
 --sql for max
 EXECUTE IMMEDIATE replace(sql_max, 'text_string', ts_name.tablespace_name) into num_out;
 dbms_output.put_line(replace(sql_max, 'text_string', ts_name.tablespace_name));
 dbms_output.put_line(num_out);
 END LOOP;
END;
/

I ran this and the total time was shocking 😐 :

SQL> --SET SERVEROUTPUT ON
SQL> SET TIMING ON
SQL> DECLARE
 2 cursor ts_names is select tablespace_name from dba_tablespaces where contents != 'TEMPORARY';
 3 sql_used VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 4 sql_free VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 5 sql_max VARCHAR(200) := 'select sum(maxbytes-bytes)/1024/1024 from dba_data_files where AUTOEXTENSIBLE = ''YES'' and maxbytes>bytes and tablespace_name = ''text_string''';
 6 num_out NUMBER;
 7 BEGIN
 8 FOR ts_name in ts_names
 9 LOOP
 10 --sql for used space
 11 EXECUTE IMMEDIATE replace(sql_used, 'text_string', ts_name.tablespace_name) into num_out;
 12 dbms_output.put_line(replace(sql_used, 'text_string', ts_name.tablespace_name));
 13 dbms_output.put_line(num_out);
 14 --sql for free space
 15 EXECUTE IMMEDIATE replace(sql_free, 'text_string', ts_name.tablespace_name) into num_out;
 16 dbms_output.put_line(replace(sql_free, 'text_string', ts_name.tablespace_name));
 17 dbms_output.put_line(num_out);
 --sql for max
 18 19 EXECUTE IMMEDIATE replace(sql_max, 'text_string', ts_name.tablespace_name) into num_out;
 20 dbms_output.put_line(replace(sql_max, 'text_string', ts_name.tablespace_name));
 21 dbms_output.put_line(num_out);
 22 END LOOP;
END;
 23 24 /

PL/SQL procedure successfully completed.

Elapsed: 00:21:30.94
SQL>

So I searched My Oracle Support (MOS) and found the following MOS note:
Queries on DBA_FREE_SPACE are Slow (Doc ID 271169.1)

Which states:
“1) In release 10g, the view dba_free_space was modified to also include objects in the recycle bin.

2) Large number of objects in the recyclebin can slow down queries on  dba_free_space.

3) This is a normal behaviour.

4) For release 11g, the view dba_free_space doesn’t contain a hint which in case when there is only few objects in recyclebin, you may want to gather underlying stats of tables/dictionary to get better performance.”

The database indeed did have a lot of objects in the recycle bin (in the pluggable database):

SQL> SELECT count(*) from dba_recyclebin;

 COUNT(*)
----------
 27615

SQL>

With most of them drop recently:

SQL> select trunc(to_date(DROPTIME,'YYYY-MM-DD:HH24:MI:SS')), count(*) from dba_recyclebin group by trunc(to_date(DROPTIME,'YYYY-MM-DD:HH24:MI:SS'))
  2  order by 1
  3 /

TRUNC(TO_ COUNT(*)
--------- ----------
24-SEP-16 2
...
19-JAN-18 2506
20-JAN-18 4322
21-JAN-18 4321
22-JAN-18 4320
23-JAN-18 4321
24-JAN-18 4321
25-JAN-18 2446

421 rows selected.

SQL>

So I purged the recycle bin (with customers permission)  and re-ran the check:

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

Elapsed: 00:06:30.39
SQL> --SET SERVEROUTPUT ON
SET TIMING ON
SQL> SQL> DECLARE
 2 cursor ts_names is select tablespace_name from dba_tablespaces where contents != 'TEMPORARY';
 3 sql_used VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 4 sql_free VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 5 sql_max VARCHAR(200) := 'select sum(maxbytes-bytes)/1024/1024 from dba_data_files where AUTOEXTENSIBLE = ''YES'' and maxbytes>bytes and tablespace_name = ''text_string''';
 6 num_out NUMBER;
 7 BEGIN
 8 FOR ts_name in ts_names
 9 LOOP
 10 --sql for used space
 11 EXECUTE IMMEDIATE replace(sql_used, 'text_string', ts_name.tablespace_name) into num_out;
 12 dbms_output.put_line(replace(sql_used, 'text_string', ts_name.tablespace_name));
 13 dbms_output.put_line(num_out);
 14 --sql for free space
 15 EXECUTE IMMEDIATE replace(sql_free, 'text_string', ts_name.tablespace_name) into num_out;
 16 dbms_output.put_line(replace(sql_free, 'text_string', ts_name.tablespace_name));
 17 dbms_output.put_line(num_out);
 18 --sql for max
 19 EXECUTE IMMEDIATE replace(sql_max, 'text_string', ts_name.tablespace_name) into num_out;
 20 dbms_output.put_line(replace(sql_max, 'text_string', ts_name.tablespace_name));
 21 dbms_output.put_line(num_out);
 22 END LOOP;
 23 END;
 24 /

PL/SQL procedure successfully completed.

Elapsed: 00:02:46.25
SQL>

Result, the duration of the PL/SQL block went from 21 minutes to just under 3 minutes.  However I need it to go under 2 minutes as this was the timeout for OpsView.

So I proceed with the next recommendation in the MOS note of gather dictionary and fixed table stats (with customers permission) using MOS note:
How to Gather Statistics on Objects Owned by the ‘SYS’ User and ‘Fixed’ Objects (Doc ID 457926.1)

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.49

SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Elapsed: 00:04:28.07

SQL> --SET SERVEROUTPUT ON
SET TIMING ON
SQL> SQL> DECLARE
 2 cursor ts_names is select tablespace_name from dba_tablespaces where contents != 'TEMPORARY';
 3 sql_used VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 4 sql_free VARCHAR(200) := 'select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = ''text_string''';
 5 sql_max VARCHAR(200) := 'select sum(maxbytes-bytes)/1024/1024 from dba_data_files where AUTOEXTENSIBLE = ''YES'' and maxbytes>bytes and tablespace_name = ''text_string''';
 6 num_out NUMBER;
 7 BEGIN
 8 FOR ts_name in ts_names
 9 LOOP
 10 --sql for used space
 11 EXECUTE IMMEDIATE replace(sql_used, 'text_string', ts_name.tablespace_name) into num_out;
 12 dbms_output.put_line(replace(sql_used, 'text_string', ts_name.tablespace_name));
 13 dbms_output.put_line(num_out);
 14 --sql for free space
 15 EXECUTE IMMEDIATE replace(sql_free, 'text_string', ts_name.tablespace_name) into num_out;
 16 dbms_output.put_line(replace(sql_free, 'text_string', ts_name.tablespace_name));
 17 dbms_output.put_line(num_out);
 18 --sql for max
 19 EXECUTE IMMEDIATE replace(sql_max, 'text_string', ts_name.tablespace_name) into num_out;
 20 dbms_output.put_line(replace(sql_max, 'text_string', ts_name.tablespace_name));
 21 dbms_output.put_line(num_out);
 22 END LOOP;
 23 END;
 24 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.53
SQL>

Bingo! the duration of the PL/SQL block went down to 4 seconds 🙂

PLEASE NOTE: This still effects non-pluggable databases, however in pluggable databases, you need to purge the recycle bin for where the dropped objects are, the container database and the pluggable databases require independent purge.

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)

How to Enable Exadata Write-Back Flash Cache

Posted on by Standard 3

Please check the following blog post “How to check if Exadata Write-Back Flash Cache is Enabled” for:

  • What is Exadata Write-Back Flash Cache?
  • What are the Performance Benefits of Exadata Write-Back Flash Cache?
  • How to check if Exadata Write-Back Flash Cache is Enabled?
  • Pre-requisites and minimum versions.

You can also get more info from My Oracle Support (MOS) note:
Exadata Write-Back Flash Cache – FAQ (Doc ID 1500257.1)
OTN Article: Oracle Exadata Database Machine – Write-Back Flash Cache

How to Enable Exadata Write-Back Flash Cache

PLEASE NOTE: Although I have illustrated the steps below, please cross check with the MOS note to ensure the method below matches your setup or the steps haven’t changed with future releases (after the time of writing).

With Exadata software 11.2.3.3.1 or higher, it is not required to stop the cellsrv process on the storage cells or to inactivate griddisk.  If you are 11.2.3.2.1 to 11.2.3.3.0, the refer to the MOS notes for additional steps.

It is recommend to enabled Write-Back Flash Cache during a period of reduced workload to reduce the performance impact on the database.

Before proceeding with the enabling of Write-Back Flash Cache, it is recommended to check the caching policy of the grid disks, as we don’t want to enable Write-Back Flash Cache for grid disks that don’t need it i.e. RECO and DBFS disk groups:

[root@v1oex2dbadm01 ~]# dcli -l root -g /opt/oracle.SupportTools/onecommand/cell_group cellcli -e list griddisk attributes name,cachingpolicy,cachedby
 v1oex2celadm01: DATAC1_CD_00_v1oex2celadm01 default
 v1oex2celadm01: DATAC1_CD_01_v1oex2celadm01 default
 v1oex2celadm01: DATAC1_CD_02_v1oex2celadm01 default
 v1oex2celadm01: DATAC1_CD_03_v1oex2celadm01 default
 v1oex2celadm01: DATAC1_CD_04_v1oex2celadm01 default
 v1oex2celadm01: DATAC1_CD_05_v1oex2celadm01 default
 v1oex2celadm01: DBFS_DG_CD_02_v1oex2celadm01 default
 v1oex2celadm01: DBFS_DG_CD_03_v1oex2celadm01 default
 v1oex2celadm01: DBFS_DG_CD_04_v1oex2celadm01 default
 v1oex2celadm01: DBFS_DG_CD_05_v1oex2celadm01 default
 v1oex2celadm01: RECOC1_CD_00_v1oex2celadm01 default
 v1oex2celadm01: RECOC1_CD_01_v1oex2celadm01 default
 v1oex2celadm01: RECOC1_CD_02_v1oex2celadm01 default
 v1oex2celadm01: RECOC1_CD_03_v1oex2celadm01 default
 v1oex2celadm01: RECOC1_CD_04_v1oex2celadm01 default
 v1oex2celadm01: RECOC1_CD_05_v1oex2celadm01 default
 v1oex2celadm02: DATAC1_CD_00_v1oex2celadm02 default
 v1oex2celadm02: DATAC1_CD_01_v1oex2celadm02 default
 v1oex2celadm02: DATAC1_CD_02_v1oex2celadm02 default
 v1oex2celadm02: DATAC1_CD_03_v1oex2celadm02 default
 v1oex2celadm02: DATAC1_CD_04_v1oex2celadm02 default
 v1oex2celadm02: DATAC1_CD_05_v1oex2celadm02 default
 v1oex2celadm02: DBFS_DG_CD_02_v1oex2celadm02 default
 v1oex2celadm02: DBFS_DG_CD_03_v1oex2celadm02 default
 v1oex2celadm02: DBFS_DG_CD_04_v1oex2celadm02 default
 v1oex2celadm02: DBFS_DG_CD_05_v1oex2celadm02 default
 v1oex2celadm02: RECOC1_CD_00_v1oex2celadm02 default
 v1oex2celadm02: RECOC1_CD_01_v1oex2celadm02 default
 v1oex2celadm02: RECOC1_CD_02_v1oex2celadm02 default
 v1oex2celadm02: RECOC1_CD_03_v1oex2celadm02 default
 v1oex2celadm02: RECOC1_CD_04_v1oex2celadm02 default
 v1oex2celadm02: RECOC1_CD_05_v1oex2celadm02 default
 v1oex2celadm03: DATAC1_CD_00_v1oex2celadm03 default
 v1oex2celadm03: DATAC1_CD_01_v1oex2celadm03 default
 v1oex2celadm03: DATAC1_CD_02_v1oex2celadm03 default
 v1oex2celadm03: DATAC1_CD_03_v1oex2celadm03 default
 v1oex2celadm03: DATAC1_CD_04_v1oex2celadm03 default
 v1oex2celadm03: DATAC1_CD_05_v1oex2celadm03 default
 v1oex2celadm03: DBFS_DG_CD_02_v1oex2celadm03 default
 v1oex2celadm03: DBFS_DG_CD_03_v1oex2celadm03 default
 v1oex2celadm03: DBFS_DG_CD_04_v1oex2celadm03 default
 v1oex2celadm03: DBFS_DG_CD_05_v1oex2celadm03 default
 v1oex2celadm03: RECOC1_CD_00_v1oex2celadm03 default
 v1oex2celadm03: RECOC1_CD_01_v1oex2celadm03 default
 v1oex2celadm03: RECOC1_CD_02_v1oex2celadm03 default
 v1oex2celadm03: RECOC1_CD_03_v1oex2celadm03 default
 v1oex2celadm03: RECOC1_CD_04_v1oex2celadm03 default
 v1oex2celadm03: RECOC1_CD_05_v1oex2celadm03 default
 [root@v1oex2dbadm01 ~]#

As you can see, all the grid disks have default caching policy.  As per the following MOS note, we disable caching for RECO and DBFS disk groups:
Oracle Exadata Database Machine Setup/Configuration Best Practices (Doc ID 1274318.1)

[root@v1oex2dbadm01 ~]# dcli -c v1oex2celadm01 -l root cellcli -e alter griddisk DBFS_DG_CD_02_v1oex2celadm01,DBFS_DG_CD_03_v1oex2celadm01,DBFS_DG_CD_04_v1oex2celadm01,DBFS_DG_CD_05_v1oex2celadm01 cachingPolicy="none"
 v1oex2celadm01: GridDisk DBFS_DG_CD_02_v1oex2celadm01 successfully altered
 v1oex2celadm01: GridDisk DBFS_DG_CD_03_v1oex2celadm01 successfully altered
 v1oex2celadm01: GridDisk DBFS_DG_CD_04_v1oex2celadm01 successfully altered
 v1oex2celadm01: GridDisk DBFS_DG_CD_05_v1oex2celadm01 successfully altered
[root@v1oex2dbadm01 ~]# dcli -c v1oex2celadm02 -l root cellcli -e alter griddisk DBFS_DG_CD_02_v1oex2celadm02,DBFS_DG_CD_03_v1oex2celadm02,DBFS_DG_CD_04_v1oex2celadm02,DBFS_DG_CD_05_v1oex2celadm02 cachingPolicy="none"
 v1oex2celadm02: GridDisk DBFS_DG_CD_02_v1oex2celadm02 successfully altered
 v1oex2celadm02: GridDisk DBFS_DG_CD_03_v1oex2celadm02 successfully altered
 v1oex2celadm02: GridDisk DBFS_DG_CD_04_v1oex2celadm02 successfully altered
 v1oex2celadm02: GridDisk DBFS_DG_CD_05_v1oex2celadm02 successfully altered
[root@v1oex2dbadm01 ~]# dcli -c v1oex2celadm03 -l root cellcli -e alter griddisk DBFS_DG_CD_02_v1oex2celadm03,DBFS_DG_CD_03_v1oex2celadm03,DBFS_DG_CD_04_v1oex2celadm03,DBFS_DG_CD_05_v1oex2celadm03 cachingPolicy="none"
 v1oex2celadm03: GridDisk DBFS_DG_CD_02_v1oex2celadm03 successfully altered
 v1oex2celadm03: GridDisk DBFS_DG_CD_03_v1oex2celadm03 successfully altered
 v1oex2celadm03: GridDisk DBFS_DG_CD_04_v1oex2celadm03 successfully altered
 v1oex2celadm03: GridDisk DBFS_DG_CD_05_v1oex2celadm03 successfully altered 
[root@v1oex2dbadm01 ~]# dcli -c v1oex2celadm01 -l root cellcli -e alter griddisk RECOC1_CD_00_v1oex2celadm01,RECOC1_CD_01_v1oex2celadm01,RECOC1_CD_02_v1oex2celadm01,RECOC1_CD_03_v1oex2celadm01,RECOC1_CD_04_v1oex2celadm01,RECOC1_CD_05_v1oex2celadm01 cachingPolicy="none"
 v1oex2celadm01: GridDisk RECOC1_CD_00_v1oex2celadm01 successfully altered
 v1oex2celadm01: GridDisk RECOC1_CD_01_v1oex2celadm01 successfully altered
 v1oex2celadm01: GridDisk RECOC1_CD_02_v1oex2celadm01 successfully altered
 v1oex2celadm01: GridDisk RECOC1_CD_03_v1oex2celadm01 successfully altered
 v1oex2celadm01: GridDisk RECOC1_CD_04_v1oex2celadm01 successfully altered
 v1oex2celadm01: GridDisk RECOC1_CD_05_v1oex2celadm01 successfully altered 
[root@v1oex2dbadm01 ~]# dcli -c v1oex2celadm02 -l root cellcli -e alter griddisk RECOC1_CD_00_v1oex2celadm02,RECOC1_CD_01_v1oex2celadm02,RECOC1_CD_02_v1oex2celadm02,RECOC1_CD_03_v1oex2celadm02,RECOC1_CD_04_v1oex2celadm02,RECOC1_CD_05_v1oex2celadm02 cachingPolicy="none"
 v1oex2celadm02: GridDisk RECOC1_CD_00_v1oex2celadm02 successfully altered
 v1oex2celadm02: GridDisk RECOC1_CD_01_v1oex2celadm02 successfully altered
 v1oex2celadm02: GridDisk RECOC1_CD_02_v1oex2celadm02 successfully altered
 v1oex2celadm02: GridDisk RECOC1_CD_03_v1oex2celadm02 successfully altered
 v1oex2celadm02: GridDisk RECOC1_CD_04_v1oex2celadm02 successfully altered
 v1oex2celadm02: GridDisk RECOC1_CD_05_v1oex2celadm02 successfully altered
[root@v1oex2dbadm01 ~]# dcli -c v1oex2celadm03 -l root cellcli -e alter griddisk RECOC1_CD_00_v1oex2celadm03,RECOC1_CD_01_v1oex2celadm03,RECOC1_CD_02_v1oex2celadm03,RECOC1_CD_03_v1oex2celadm03,RECOC1_CD_04_v1oex2celadm03,RECOC1_CD_05_v1oex2celadm03 cachingPolicy="none"
 v1oex2celadm03: GridDisk RECOC1_CD_00_v1oex2celadm03 successfully altered
 v1oex2celadm03: GridDisk RECOC1_CD_01_v1oex2celadm03 successfully altered
 v1oex2celadm03: GridDisk RECOC1_CD_02_v1oex2celadm03 successfully altered
 v1oex2celadm03: GridDisk RECOC1_CD_03_v1oex2celadm03 successfully altered
 v1oex2celadm03: GridDisk RECOC1_CD_04_v1oex2celadm03 successfully altered
 v1oex2celadm03: GridDisk RECOC1_CD_05_v1oex2celadm03 successfully altered
[root@v1oex2dbadm01 ~]#

Now when we enabling of Write-Back Flash Cache, it will not cache for grid disks for RECO and DBFS disk group, avoiding the need to flush to disk and change policy as post step:

[root@v1oex2dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root cellcli -e list griddisk attributes name,cachingpolicy,cachedby
 v1oex2celadm01: DATAC1_CD_00_v1oex2celadm01 default
 v1oex2celadm01: DATAC1_CD_01_v1oex2celadm01 default
 v1oex2celadm01: DATAC1_CD_02_v1oex2celadm01 default
 v1oex2celadm01: DATAC1_CD_03_v1oex2celadm01 default
 v1oex2celadm01: DATAC1_CD_04_v1oex2celadm01 default
 v1oex2celadm01: DATAC1_CD_05_v1oex2celadm01 default
 v1oex2celadm01: DBFS_DG_CD_02_v1oex2celadm01 none
 v1oex2celadm01: DBFS_DG_CD_03_v1oex2celadm01 none
 v1oex2celadm01: DBFS_DG_CD_04_v1oex2celadm01 none
 v1oex2celadm01: DBFS_DG_CD_05_v1oex2celadm01 none
 v1oex2celadm01: RECOC1_CD_00_v1oex2celadm01 none
 v1oex2celadm01: RECOC1_CD_01_v1oex2celadm01 none
 v1oex2celadm01: RECOC1_CD_02_v1oex2celadm01 none
 v1oex2celadm01: RECOC1_CD_03_v1oex2celadm01 none
 v1oex2celadm01: RECOC1_CD_04_v1oex2celadm01 none
 v1oex2celadm01: RECOC1_CD_05_v1oex2celadm01 none
 v1oex2celadm02: DATAC1_CD_00_v1oex2celadm02 default
 v1oex2celadm02: DATAC1_CD_01_v1oex2celadm02 default
 v1oex2celadm02: DATAC1_CD_02_v1oex2celadm02 default
 v1oex2celadm02: DATAC1_CD_03_v1oex2celadm02 default
 v1oex2celadm02: DATAC1_CD_04_v1oex2celadm02 default
 v1oex2celadm02: DATAC1_CD_05_v1oex2celadm02 default
 v1oex2celadm02: DBFS_DG_CD_02_v1oex2celadm02 none
 v1oex2celadm02: DBFS_DG_CD_03_v1oex2celadm02 none
 v1oex2celadm02: DBFS_DG_CD_04_v1oex2celadm02 none
 v1oex2celadm02: DBFS_DG_CD_05_v1oex2celadm02 none
 v1oex2celadm02: RECOC1_CD_00_v1oex2celadm02 none
 v1oex2celadm02: RECOC1_CD_01_v1oex2celadm02 none
 v1oex2celadm02: RECOC1_CD_02_v1oex2celadm02 none
 v1oex2celadm02: RECOC1_CD_03_v1oex2celadm02 none
 v1oex2celadm02: RECOC1_CD_04_v1oex2celadm02 none
 v1oex2celadm02: RECOC1_CD_05_v1oex2celadm02 none
 v1oex2celadm03: DATAC1_CD_00_v1oex2celadm03 default
 v1oex2celadm03: DATAC1_CD_01_v1oex2celadm03 default
 v1oex2celadm03: DATAC1_CD_02_v1oex2celadm03 default
 v1oex2celadm03: DATAC1_CD_03_v1oex2celadm03 default
 v1oex2celadm03: DATAC1_CD_04_v1oex2celadm03 default
 v1oex2celadm03: DATAC1_CD_05_v1oex2celadm03 default
 v1oex2celadm03: DBFS_DG_CD_02_v1oex2celadm03 none
 v1oex2celadm03: DBFS_DG_CD_03_v1oex2celadm03 none
 v1oex2celadm03: DBFS_DG_CD_04_v1oex2celadm03 none
 v1oex2celadm03: DBFS_DG_CD_05_v1oex2celadm03 none
 v1oex2celadm03: RECOC1_CD_00_v1oex2celadm03 none
 v1oex2celadm03: RECOC1_CD_01_v1oex2celadm03 none
 v1oex2celadm03: RECOC1_CD_02_v1oex2celadm03 none
 v1oex2celadm03: RECOC1_CD_03_v1oex2celadm03 none
 v1oex2celadm03: RECOC1_CD_04_v1oex2celadm03 none
 v1oex2celadm03: RECOC1_CD_05_v1oex2celadm03 none
 [root@v1oex2dbadm01 ~]#

Next we check that all the grid disks on all storage cells have the asmdeactivationoutcome and asmmodestatus as “Yes” and “ONLINE” respectively.

[root@v1ex2dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root cellcli -e list griddisk attributes asmdeactivationoutcome, asmmodestatus
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm01: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm02: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
v1ex2celadm03: Yes ONLINE
[root@v1ex2dbadm01 ~]#

Next we check that all of the Flash Cache are in the “normal” state and that no flash disks are in a degraded or critical state:

[root@v1ex2dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root cellcli -e list flashcache detail
v1ex2celadm01: name: v1ex2celadm01_FLASHCACHE
v1ex2celadm01: cellDisk: FD_01_v1ex2celadm01,FD_00_v1ex2celadm01
v1ex2celadm01: creationTime: 2015-07-01T13:39:22+01:00
v1ex2celadm01: degradedCelldisks:
v1ex2celadm01: effectiveCacheSize: 2.910369873046875T
v1ex2celadm01: id: 655bdb7a-8d3b-40e5-88af-cd42843dd3f7
v1ex2celadm01: size: 2.910369873046875T
v1ex2celadm01: status: normal
v1ex2celadm02: name: v1ex2celadm02_FLASHCACHE
v1ex2celadm02: cellDisk: FD_01_v1ex2celadm02,FD_00_v1ex2celadm02
v1ex2celadm02: creationTime: 2015-07-01T06:38:05+01:00
v1ex2celadm02: degradedCelldisks:
v1ex2celadm02: effectiveCacheSize: 2.910369873046875T
v1ex2celadm02: id: 1cc0f7a4-885a-4e23-aec5-b47bc488e8e3
v1ex2celadm02: size: 2.910369873046875T
v1ex2celadm02: status: normal
v1ex2celadm03: name: v1ex2celadm03_FLASHCACHE
v1ex2celadm03: cellDisk: FD_01_v1ex2celadm03,FD_00_v1ex2celadm03
v1ex2celadm03: creationTime: 2015-07-01T20:39:30+01:00
v1ex2celadm03: degradedCelldisks:
v1ex2celadm03: effectiveCacheSize: 2.910369873046875T
v1ex2celadm03: id: b07f6011-1d66-4c3f-a25f-26d1e6b55633
v1ex2celadm03: size: 2.910369873046875T
v1ex2celadm03: status: normal
[root@v1ex2dbadm01 ~]#

Next we validate all the Physical Disks are in the “NORMAL” state before we modify the Flash Cache:

[root@v1ex2dbadm01 ~]# dcli -l root -g /opt/oracle.SupportTools/onecommand/cell_group cellcli -e "list physicaldisk attributes name,status"
v1ex2celadm01: 8:0 normal
v1ex2celadm01: 8:1 normal
v1ex2celadm01: 8:2 normal
v1ex2celadm01: 8:3 normal
v1ex2celadm01: 8:4 normal
v1ex2celadm01: 8:5 normal
v1ex2celadm01: 8:6 normal
v1ex2celadm01: 8:7 normal
v1ex2celadm01: 8:8 normal
v1ex2celadm01: 8:9 normal
v1ex2celadm01: 8:10 normal
v1ex2celadm01: 8:11 normal
v1ex2celadm01: FLASH_1_1 normal
v1ex2celadm01: FLASH_2_1 normal
v1ex2celadm01: FLASH_4_1 normal
v1ex2celadm01: FLASH_5_1 normal
v1ex2celadm02: 8:0 normal
v1ex2celadm02: 8:1 normal
v1ex2celadm02: 8:2 normal
v1ex2celadm02: 8:3 normal
v1ex2celadm02: 8:4 normal
v1ex2celadm02: 8:5 normal
v1ex2celadm02: 8:6 normal
v1ex2celadm02: 8:7 normal
v1ex2celadm02: 8:8 normal
v1ex2celadm02: 8:9 normal
v1ex2celadm02: 8:10 normal
v1ex2celadm02: 8:11 normal
v1ex2celadm02: FLASH_1_1 normal
v1ex2celadm02: FLASH_2_1 normal
v1ex2celadm02: FLASH_4_1 normal
v1ex2celadm02: FLASH_5_1 normal
v1ex2celadm03: 8:0 normal
v1ex2celadm03: 8:1 normal
v1ex2celadm03: 8:2 normal
v1ex2celadm03: 8:3 normal
v1ex2celadm03: 8:4 normal
v1ex2celadm03: 8:5 normal
v1ex2celadm03: 8:6 normal
v1ex2celadm03: 8:7 normal
v1ex2celadm03: 8:8 normal
v1ex2celadm03: 8:9 normal
v1ex2celadm03: 8:10 normal
v1ex2celadm03: 8:11 normal
v1ex2celadm03: FLASH_1_1 normal
v1ex2celadm03: FLASH_2_1 normal
v1ex2celadm03: FLASH_4_1 normal
v1ex2celadm03: FLASH_5_1 normal
[root@v1ex2dbadm01 ~]#

You can run the same command with inverse grep on “normal” to ensure you didn’t miss any disks that are not normal:

[root@v1ex2dbadm01 ~]# dcli -l root -g /opt/oracle.SupportTools/onecommand/cell_group cellcli -e "list physicaldisk attributes name,status"|grep -v normal
[root@v1ex2dbadm01 ~]#

Next we drop the Flash Cache to be able to change the attribute:

PLEASE NOTE: Any data that is currently cached in Flash Cache and being served will then need to be served by Hard Disks and a noticeable performance degradation will be observed.  Hence it is recommend to enabled Write-Back Flash Cache during a period of reduced workload to reduce the performance impact on the database.

[root@v1ex2dbadm01 ~]# dcli -l root -g /opt/oracle.SupportTools/onecommand/cell_group cellcli -e drop flashcache 
v1ex2celadm01: Flash cache v1ex2celadm01_FLASHCACHE successfully dropped 
v1ex2celadm02: Flash cache v1ex2celadm02_FLASHCACHE successfully dropped 
v1ex2celadm03: Flash cache v1ex2celadm03_FLASHCACHE successfully dropped 
[root@v1ex2dbadm01 ~]#

Next we set the “flashCacheMode” attribute to “writeback“:

[root@v1ex2dbadm01 ~]# dcli -l root -g /opt/oracle.SupportTools/onecommand/cell_group cellcli -e "alter cell flashCacheMode=writeback"
v1ex2celadm01: Cell v1ex2celadm01 successfully altered
v1ex2celadm02: Cell v1ex2celadm02 successfully altered
v1ex2celadm03: Cell v1ex2celadm03 successfully altered
[root@v1ex2dbadm01 ~]#

Next we re-create the Flash Cache, which will be in Write-Back instead of WriteThrough:

[root@v1ex2dbadm01 ~]# dcli -l root -g /opt/oracle.SupportTools/onecommand/cell_group cellcli -e create flashcache all
v1ex2celadm01: Flash cache v1ex2celadm01_FLASHCACHE successfully created
v1ex2celadm02: Flash cache v1ex2celadm02_FLASHCACHE successfully created
v1ex2celadm03: Flash cache v1ex2celadm03_FLASHCACHE successfully created
[root@v1ex2dbadm01 ~]#

Next we check the attribute “flashCacheMode” is actually now “writeback“:

[root@v1ex2dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root cellcli -e "list cell attributes flashcachemode"
v1ex2celadm01: writeback
v1ex2celadm02: writeback
v1ex2celadm03: writeback
[root@v1ex2dbadm01 ~]#

At this point, write I/O will go straight to flash and then can be moved to hard disk if aged or not required for read caching.  The Flash Cache will be repopulated over time and performance will return to normal for reads with addition performance for writes 🙂

You can check the usage increase as Flash Cache repopulates as follows:

[root@v1oex2dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root cellcli -e LIST METRICCURRENT FC_BY_USED
 v1oex2celadm01: FC_BY_USED FLASHCACHE 104,838 MB
 v1oex2celadm02: FC_BY_USED FLASHCACHE 104,479 MB
 v1oex2celadm03: FC_BY_USED FLASHCACHE 105,192 MB
[root@v1oex2dbadm01 ~]#

Finally, we validate grid disk attributes cachingPolicy and cachedby, where we can see only the DATA disk group is being cached by Flash Cache and by which Flash Disk:

[root@v1oex2dbadm01 ~]# dcli -g /opt/oracle.SupportTools/onecommand/cell_group -l root cellcli -e list griddisk attributes name,cachingpolicy,cachedby
v1oex2celadm01: DATAC1_CD_00_v1oex2celadm01 default FD_01_v1oex2celadm01
v1oex2celadm01: DATAC1_CD_01_v1oex2celadm01 default FD_01_v1oex2celadm01
v1oex2celadm01: DATAC1_CD_02_v1oex2celadm01 default FD_00_v1oex2celadm01
v1oex2celadm01: DATAC1_CD_03_v1oex2celadm01 default FD_00_v1oex2celadm01
v1oex2celadm01: DATAC1_CD_04_v1oex2celadm01 default FD_01_v1oex2celadm01
v1oex2celadm01: DATAC1_CD_05_v1oex2celadm01 default FD_00_v1oex2celadm01
v1oex2celadm01: DBFS_DG_CD_02_v1oex2celadm01 none
v1oex2celadm01: DBFS_DG_CD_03_v1oex2celadm01 none
v1oex2celadm01: DBFS_DG_CD_04_v1oex2celadm01 none
v1oex2celadm01: DBFS_DG_CD_05_v1oex2celadm01 none
v1oex2celadm01: RECOC1_CD_00_v1oex2celadm01 none
v1oex2celadm01: RECOC1_CD_01_v1oex2celadm01 none
v1oex2celadm01: RECOC1_CD_02_v1oex2celadm01 none
v1oex2celadm01: RECOC1_CD_03_v1oex2celadm01 none
v1oex2celadm01: RECOC1_CD_04_v1oex2celadm01 none
v1oex2celadm01: RECOC1_CD_05_v1oex2celadm01 none
v1oex2celadm02: DATAC1_CD_00_v1oex2celadm02 default FD_01_v1oex2celadm02
v1oex2celadm02: DATAC1_CD_01_v1oex2celadm02 default FD_00_v1oex2celadm02
v1oex2celadm02: DATAC1_CD_02_v1oex2celadm02 default FD_01_v1oex2celadm02
v1oex2celadm02: DATAC1_CD_03_v1oex2celadm02 default FD_01_v1oex2celadm02
v1oex2celadm02: DATAC1_CD_04_v1oex2celadm02 default FD_00_v1oex2celadm02
v1oex2celadm02: DATAC1_CD_05_v1oex2celadm02 default FD_00_v1oex2celadm02
v1oex2celadm02: DBFS_DG_CD_02_v1oex2celadm02 none
v1oex2celadm02: DBFS_DG_CD_03_v1oex2celadm02 none
v1oex2celadm02: DBFS_DG_CD_04_v1oex2celadm02 none
v1oex2celadm02: DBFS_DG_CD_05_v1oex2celadm02 none
v1oex2celadm02: RECOC1_CD_00_v1oex2celadm02 none
v1oex2celadm02: RECOC1_CD_01_v1oex2celadm02 none
v1oex2celadm02: RECOC1_CD_02_v1oex2celadm02 none
v1oex2celadm02: RECOC1_CD_03_v1oex2celadm02 none
v1oex2celadm02: RECOC1_CD_04_v1oex2celadm02 none
v1oex2celadm02: RECOC1_CD_05_v1oex2celadm02 none
v1oex2celadm03: DATAC1_CD_00_v1oex2celadm03 default FD_01_v1oex2celadm03
v1oex2celadm03: DATAC1_CD_01_v1oex2celadm03 default FD_01_v1oex2celadm03
v1oex2celadm03: DATAC1_CD_02_v1oex2celadm03 default FD_00_v1oex2celadm03
v1oex2celadm03: DATAC1_CD_03_v1oex2celadm03 default FD_00_v1oex2celadm03
v1oex2celadm03: DATAC1_CD_04_v1oex2celadm03 default FD_01_v1oex2celadm03
v1oex2celadm03: DATAC1_CD_05_v1oex2celadm03 default FD_00_v1oex2celadm03
v1oex2celadm03: DBFS_DG_CD_02_v1oex2celadm03 none
v1oex2celadm03: DBFS_DG_CD_03_v1oex2celadm03 none
v1oex2celadm03: DBFS_DG_CD_04_v1oex2celadm03 none
v1oex2celadm03: DBFS_DG_CD_05_v1oex2celadm03 none
v1oex2celadm03: RECOC1_CD_00_v1oex2celadm03 none
v1oex2celadm03: RECOC1_CD_01_v1oex2celadm03 none
v1oex2celadm03: RECOC1_CD_02_v1oex2celadm03 none
v1oex2celadm03: RECOC1_CD_03_v1oex2celadm03 none
v1oex2celadm03: RECOC1_CD_04_v1oex2celadm03 none
v1oex2celadm03: RECOC1_CD_05_v1oex2celadm03 none
[root@v1oex2dbadm01 ~]#

Final note, there is a script provided by Oracle that can do this all for you called setWBFC, however the version 1.0.0.2.1.20160602 didn’t work for me as it detected 4 Flash Disks in eighth rack when it expected 2.  Although there are only 2 in use in eighth rack, there are 4 physically present, so I believe this is a bug.  I did raise an SR with Oracle Support, which is yet to be concluded.  Below is the output for those who are interested:

[root@v1oex2dbadm01 WBFC]# ./setWBFC.sh
 setWBFC Version: 1.0.0.2.1.20160602
 Usage:
 ./setWBFC.sh -g cell_group_file [-d dbs_group_file ]
 [ -h ] [ -i ] [ -l log_directory ]
 [ -m WriteBack | WriteThrough ] [ -o rolling | non-rolling ]
 [ -p ] [ -s step_number ] [ -t time_out_seconds ]
 [ -x trace_level ] [ -v ]

-g file file that lists cell host names, one per line
 -d file file that lists the database host names, one
 per line. Required for non-rolling.
 -h help, print this information
 -i run in interactive mode
 -l log directory directory path for log files
 -m FC_mode flashcache mode: WriteBack | WriteThrough
 -o exec_mode execution mode: rolling | non-rolling (default)
 -p perform a precheck only
 -s step # (*) specify step number to restart at
 -t timeout sec specify in seconds the amount of time to wait
 for griddisks to come ONLINE - range: [600 - 43200]
 Default: 21600 (6 hours)
 -x trace level # specify trace level for further diagnostics
 -v show version

(*) -- Option not yet implemented.

 [root@v1oex2dbadm01 WBFC]# ./setWBFC.sh -g /opt/oracle.SupportTools/onecommand/cell_group -l /root/v1/WBFC/logs -m WriteBack -o rolling -p
 ./setWBFC.sh: Using log directory '/root/v1/WBFC/logs'
 ./setWBFC.sh: Log File '/root/v1/WBFC/logs/setWBFC_18335_2018-01-17-10:46:26.log' created successfully
 2018-01-17 10:46:26
 Starting ./setWBFC.sh on v1oex2dbadm01
 Version: 1.0.0.2.1.20160602
 Command line options used:
 -g /opt/oracle.SupportTools/onecommand/cell_group
 -o rolling
 -m WriteBack
 -p (Perform pre-req checks only)
 -t 21600
 -x 0

2018-01-17 10:46:26
 Performing pre-req checks.....
 2018-01-17 10:46:26
 Creating baseline inventory for griddisks
 2018-01-17 10:46:27
 Creating baseline inventory for flashdisks
 2018-01-17 10:46:28
 Creating baseline inventory for flashsize
 2018-01-17 10:46:28
 dcli present and in PATH. [PASSED]
 2018-01-17 10:46:28
 Checking cell nodes are valid storage servers...
 2018-01-17 10:46:29
 All cells are valid Exadata storage cells.
 2018-01-17 10:46:29
 Checking Exadata Storage Software Versions...
 2018-01-17 10:46:33
 Software versions of the following cells:
 v1oex2celadm01: 12.1.2.3.5.170418 [PASSED]
 v1oex2celadm02: 12.1.2.3.5.170418 [PASSED]
 v1oex2celadm03: 12.1.2.3.5.170418 [PASSED]

2018-01-17 10:46:33
 Checking Grid Infrastructure Software Version...
 2018-01-17 10:46:38
 Grid Infrastructure version: 12.1.0.2.00 [PASSED]

2018-01-17 10:46:38
 Checking for active ASM operations....
 2018-01-17 10:46:38
 Check for no active ASM operations: [PASSED]
 2018-01-17 10:46:38
 Checking griddisk status across all cells....
 2018-01-17 10:46:39
 All griddisks across all cells have asmdeactivationoutcome = Yes
 All griddisks across all cells are ONLINE
 Griddisk checks: [PASSED]
 2018-01-17 10:46:39
 Checking flash cache status.....
 2018-01-17 10:46:40
 Flashcache status normal: [PASSED]
 2018-01-17 10:46:40
 Checking that all FlashDisks are present...
 2018-01-17 10:46:42
 Cell v1oex2celadm01 has one or more FlashDisk missing. Expecting 2 but found 4

2018-01-17 10:46:42
 FlashDisk validation: [FAILED]
 2018-01-17 10:46:42
 Checking current flash cache mode.....
 2018-01-17 10:46:43
 Flashcache not already in target mode: [PASSED]
 2018-01-17 10:46:43
 Pre-req checks failed with status 7. Exiting....

[root@v1oex2dbadm01 WBFC]#

If this works for you, great then I would recommend using this method, otherwise it can be used to double check the pre-requisites at least and then you can do manually as I did shown above 🙂

If you found this blog post useful, please like as well as follow me through my various Social Media avenues available on the sidebar and/or subscribe to this oracle blog via WordPress/e-mail.

Thanks

Zed DBA (Zahid Anwar)