How To Clear / Purge The OBIEE 11g Repository Cache Via An Automated Scripted Command-line?

This document describes a method to clear the OBIEE 11g repository cache via an automated command-line script.

This examples shows how to purge all the cache entries on Linux/Unix , for other options see the documentation. For Windows, modify the paths and use .bat files.




-->Create a file in the following location called purgecache.txt 
note: you can name it whatever you want, just use the same file name throughout.
[middlware_home]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup
In the purgecache.txt file put the following single line with no quotes or tick marks:

Call SAPurgeAllCache()

-->Set the environment to run the command:


. [middlware_home]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/bi-init.sh

notice: dot space command. This will ensure the environment variables are carried through any subsequent shells that that command may call.


-->Execute the following command, substituting your appropriate values. Do not include the [ ], they represent values that you need to substitute:

[middlware_home]/[oracle_home]/bifoundation/server/bin/nqcmd -d AnalyticsWeb -u [your user id] -p [your password] -s [middlware_home]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/purgecache.txt

nqcmd help
Command: ./nqcmd - a command line client which can issue SQL statements against either Oracle BI server or a variety of ODBC compliant backend databases.SYNOPSISnqcmd [OPTION]...DESCRIPTION-d<data source name>-u<user name>-p<password>-s<sql input file name>-o<output result file name>-D<Delimiter>-C<# number of fetched rows by column-wise binding>-R<# number of fetched rows by row-wise binding>-a (a flag to enable async processing)-f (a flag to enable to flush output file for each write)-H (a flag to enable to open/close a request handle for each query)-z (a flag to enable UTF8 instead of ACP)-utf16 (a flag to enable UTF16 instead of ACP)-q (a flag to turn off row output)-NoFetch (a flag to disable data fetch with query execution)-NotForwardCursor (a flag to disable forwardonly cursor)-v (a flag to display the version)-SessionVar <SessionVarName>=<SessionVarVAlue>

-->Check that the cache was cleared
ls -ltr [middlware_home]/instances/instance1/bifoundation/OracleBIServerComponent/coreapplication_obis1/cache


Once you have tested the individual command lines, then you may incorporate the commands into a single script and use an automated method to call them (i.e. - cron job, windows scheduler, etc)

Some possible causes why the cache may not be cleared.

• You are not pointing to the correct data source (i.e. - if you have multiple instances on one server).On Linux/Unix check the data source in [middlware_home]/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup/odbc.ini
• You have the cache pointed to a different directory other than the default (i.e. - Global Cache, DATA_STORAGE_PATHS). Meaning, you are viewing old/stale cache in the default location when checking if it was cleared instead of the active working directory.
• Active queries (analysis) sessions that are locked will not be cleared.

Note: If you are an Administrator, then you can also login to Analyticis > Administration > Issue SQL and run:  call Call SAPurgeAllCache() or pass any documented paramenters; however, this is a manual method and not automated or the intent of the document.