Check Objects in Flashcache in Oracle Exadata

The Exadata Smart Flash Cache is a great Performance optimizer. The contents of the flash cache can be read fro m the cell servers on Exadata. But one of the restrictions on this is that it only lists the object id’s and not the name of the object. So that requires us to map those object ID’s to Object name in DBA_OBJECTS view.

We can use Oracle’s very powerful dcli utility to colelct the object id’s from all cells and scp to the DB nodes and load it into an External table to join it with the DBA_OBJECTS view.
1. Firstly we have to add all cells to dcli utility
list flashcache
listcell

chmod 700 mycommands.scl
dcli -g cells.txt -l root -x mycommands.scl

## this will give output of commands put in file mycommands.scl from all cell nodes ##
Incase if there is error with one key then drop and recreate that cells key

[root@exatestceladm01 ~]# dcli -c exatestceladm01 --unkey -l root

root@exatestceladm01’s password:
exatestceladm01: ssh key dropped
## Rekey that cell

root@exatestceladm01 ~]# dcli -g ./cells.txt -l root -k -s ‘-o StrictHostKeyChecking=no’
root@exatestceladm01’s password:
exatestceladm01: ssh key added
exatestceladm02: ssh key already exists
exatestceladm03: ssh key already exists
————————————————————————–

Configure DB nodes SSH using dcli command utility

On Any Cell node create a text file in /root called db.txt

exatestdbadm01
exatestdbadm02
chmod 700 db.txt

dcli -g db.txt -l root -k -s '-o StrictHostKeyChecking=no'

root@exatestdbadm01’s password:
root@exatestdbadm02’s password:
exatestdbadm01: ssh key added
exatestdbadm02: ssh key added
[root@exatestceladm01 ~]# dcli -g db.txt -l root hostname
exatestdbadm01: exatestdbadm01.qiibonline.com
exatestdbadm02: exatestdbadm02.qiibonline.com

2. Create the dcli command to generate the contents of the flash cache

dcli -g /root/cells.txt -l root "cellcli -e list flashcachecontent where dbUniqueName='SWX' and tableSpaceNumber=6" > /tmp/swxoutput.log
This will generate all the flascache objects for Database ‘SWX’ and Tablespace Number 6. You can further refine this criteria to your liking

3. Now Let’s create a script to generate the flash contents, clean the output using AWK to only include the Object ID’s and copy it to
the DB node

#!/bin/bash
PATH=$PATH:$HOME/bin
export PATH
/usr/local/bin/dcli -g /root/cells.txt -l root "cellcli -e list flashcachecontent where dbUniqueName='SWX' and tableSpaceNumber=6" >
/tmp/swxoutput.log
awk -F'\t' '{ print $3 }' /tmp/swxoutput.log > /tmp/swxclean.log
scp -r /tmp/swxclean.log root@exaproddbadm01:/u03/expdp/swx
ssh root@exaproddbadm01 << EOF
chmod 777 /u03/expdp/swx/swxclean.log
chown oracle:dba /u03/expdp/swx/swxclean.log
EOF
exit

## You can schedule this in the crontab of cell server to send it daily

30 07 * * * /root/flashlogs.sh

4. Create an External Table to Read the Flash Contents inside the Database for which you generated the flash contents in above script

SQL> create or replace directory load_flash as '/u03/expdp/swx';

Directory created.

SQL> grant read,write on directory load_flash to public;

Grant succeede

create table flash_contents (
cell_object_id varchar(20)
)
organization external (
type oracle_loader
default directory load_flash
access parameters (
records delimited by newline
fields terminated by ','
missing field values are null
)
location ('swxclean.log')
)
reject limit unlimited;
-- Check if External Tabling is Loading Properly --
select * from flash_contents;

5. Now create a Query to check the Flash Contents and Map it to DBA_OBJECTS to identify it
set lines 999

select DISTINCT a.OBJECT_NAME,a.OBJECT_TYPE from DBA_OBJECTS a,flash_contents b where a.owner=’SWX’ and a.DATA_OBJECT_ID=b.cell_object_id
order by 1;

OBJECT_NAME
OBJECT_TYPE
——————————————————————————————————————————–
——————-
SYS_IL0000107895C00039$$ INDEX
SYS_IL0000107898C00039$$ INDEX
SYS_IL0000107901C00006$$ INDEX
SYS_IL0000108307C00005$$ INDEX
PARTITION
SYS_IL0000108342C00006$$ INDEX
PARTITION
This will display the objects running in flash for this particular database in the particular tablespace

 

Update 2: If you get error like below

 

ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04001: error opening file

 

Most likely your database is an Orace RAC DB so create the same directory structure for the external table files on both nodes and make sure the clean file is SCP’ed  to both compute nodes from the cell node.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s