TimesTen import from oracle utility

The other day, someone wanted to place some oracle tables into TimesTen DB for some testing, this is a great quick way:

1)     Make sure you have a connection to your Oracle database in the TimesTen tnsfile, so I have the following in my tnsnames.ora in the following directory (/u01/app/oracle/product/TimesTen/tt1122/network/admin/samples) :

MYDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = XXX) (PORT=1521))

(CONNECT_DATA =

(SERVICE_NAME = live)

)

)

2)     Set your environment

TNS_ADMIN=/u01/app/oracle/product/TimesTen/tt1122/network/admin/samples

Export TNS_ADMIN

cd /u01/app/oracle/product/TimesTen/tt1122/ttoracle_home/instantclient_11_2

./sqlplus read/only@mydb

3)     Create a directory to place the files that will be generated for the import, and cd to the directory.

4)     Run the following command:

ttImportFromOracle –oraConn user/oracle@myoracle –tables my_small_tab –typeMap 2,1 –prefix tm1

myoracle = ip:port/service

prefix = prefix for files generated.

5)     Now we shall add the MYDB to the TimesTen settings:

cd /u01/app/oracle/product/TimesTen/tt1122/info

vi sys.odbc.ini

Add the following to your Times Ten Store ours is called TT_AGGR_STORE

[TT_AGGR_STORE]

Driver=/u01/app/oracle/product/fmw/../TimesTen/tt1122/lib/libtten.so

DataStore=/u01/app/oracle/product/fmw/../aggregate_store/tt_aggr_store

LogDir=/u01/app/oracle/product/fmw/../aggregate_store/logs

DatabaseCharacterSet=AL32UTF8

ConnectionCharacterSet=AL32UTF8

LogFileSize=1024

LogBufMB=1024

LogBufParallelism=16

Preallocate=0

PermSize=400000

TempSize=200000

MemoryLock=4

CkptFrequency=30

CkptLogVolume=0

CkptRate=20

PrivateCommands=1

RecoveryThreads=40

oraclenetservicename=MYDB

6)     Restart TimesTen

cd /sbin

./sysctl -p

ttdaemonadmin -stop

ttdaemonadmin -start

7)     In the example below, we are doing the table siebel.wc-KPI_F_041174

[oracle@CIMAlyticsSrv1 TT_INSTALLERS]$  ./ttImportFromOracle -oraConn read/only@8x.2xx.1xx.1x:1521/live -tables siebel.wc_KPI_F_041174 -typeMap 2,1 -prefix ncmp1

Beginning processing

Resolving any tablename wildcards

Eliminating any duplicate tables

Getting metadata from source

Generating database user list

Assigning TimesTen datatypes

Analyzing source tables

Analyzing table ‘SIEBEL.WC_KPI_F_041174’ …

Estimating table sizes

Evaluating parallel data load

Generating output files

Finished processing

 

8)     The following files will be generated:

ncmp1CreateUsers.sql

ncmp1CreateTables.sql

ncmp1LoadData.sql

ncmp1CreateIndexes.sql

ncmp1UpdateStats.sql

9)     Connect to your TimesTen DB and the run the above sql files in the order above:

[oracle@CIMAlyticsSrv1 bin]$ ttisql -connstr “dsn=TT_AGGR_STORE;uid=user;pwd=password;oraclepwd=opassword”

**the opassword is the password to your oracle instance

Command> @/home/oracle/TT_INSTALLERS/ncmp1CreateUsers.sql

..

 

 

Installing,Configuring Statspack and Identifying issues

Now that we have AWR you may wonder do we still need statspack, well first of all you need an enterprise edition of the database and the diagnostic license.

These are the steps to install statspack.

Installing Statspack

First create the tablespace to hold the Statspack tables.
SQL> create tablespace stats_data
datafile ‘+ASM’ size 500M
autoextend on maxsize 2G;

Tablespace created.

SQL>

Oracle Openworld 2014

Going to a Open World is a must for anyone, I Attended Oracle Openworld to investigate new technology and discuss current and future software plans with the Oracle development teams.

Earlier in the year I was asked by Susan (Oracle Times Ten VP) to do a joint presentation.   I started by covering some of the interesting ways we have been using and implementing technology; then moving into the various technical options available to other companies implementing an Exalytics machine to improve dashboard performance by placing datasets into memory.

The presentation is attached for your reference.

Automate the purging of the repository cache (Exalytics Linux)

So we have created a ram disk and you are now storing the repository cache, how can we automate the purging.

1.  First we will create a file that we can run that will 1st load the BI environment, then we will clear the cache :

–          Create a file called purge.txt with the following contents:

Call SAPurgeAllCache();

–          Create a file called purge.sh with the following contents

cd /u01/app/oracle/product/fmw/instances/instance1/bifoundation/OracleBIApplication/coreapplication/setup

. ./bi-init.sh

cd /u01/app/oracle/product/fmw/Oracle_BI1/bifoundation/server/bin

./nqcmd -d “AnalyticsWeb” -uweblogic –p<place the password here> -s/home/oracle/TT_INSTALLERS/purge.txt

 

2. Update crontab to execute the file everyday:

00 23 * * * /home/oracle/TT_INSTALLERS/purge.sh > /home/oracle/TT_INSTALLERS/purged.log 2>&1

You now have automated purging of your cache.

Create a Ram Disk in Exalytics (LINUX)

So you have all your data in memory, how can you make it even faster. You can create what you call a RAM Disk, this is when you have allocated part of the memory to be used as a file system, then configure BI to use the ram filesystem for caching.

1. Check the amount you have free ram you have left on your machine, by using the command free –g, you can see we have 1tb ram.

2. Create a folder to use as a mount point for your RAM disk.

mkdir /mnt/ramdisk

3. Add the following into the /etc/fstab file in the size add the size you want, we are using 1g:

tmpfs /mnt/ramdisk tmpfs nodev,nosuid,noexec,nodiratime,size=1024M 0 0

4. Enter the following to mount it : mount /mnt/ramdisk
5. To check all mounted df-k

6. On the BI Server edit the

NQSConfig.INI

7. Update the DATA_STORAGE_PATHS as below, don’t update anything else.
[CACHE]

ENABLE = YES; # This Configuration setting is managed by Oracle Enterprise Manager Fusion Middleware Control
# A comma separated list of pair(s).
# These are relative to the process instance directory.
# e.g. DATA_STORAGE_PATHS = “nQSCache” 500 MB;
# resolves to
# $(ORACLE_INSTANCE)/bifoundation/OracleBIServerComponent/<instance_name>/nQSCache
#DATA_STORAGE_PATHS = “cache” 500 MB;
DATA_STORAGE_PATHS = “/mnt/ramdisk” 500 MB;

8. Lets set up OBIEE to use ramdisk for the presentation cache by doing the following in the OBIEE 11g EM console:

FMW Control > Business Intelligence > CoreApplication > Capacity Management tab

9. Then click the Performance tab, tick ‘cache enabled’ under ‘Enable BI Server Cache’
10. Restart the BI Services.

Next time I will show you how to automatically clear the cache.