Friday, March 27, 2009

How To: Enable Password Security/Complexity Check

We need to make the password of Oracle user more robust and make sure that the password are complex enough so that the chance of breaking them is minimized.

Oracle provides a script called UTLPWDMG.sql located in $ORACLE_HOME/rdbms/admin which can be used to manage the complexity of database password.

This script basically creates to functions verify_function_11G (new in 11g) and verify_function (older version).

Lets see what verify_function_11G function does, its input parameters are username, new password and old password.

Checks that this scripts enforces on passwords:

  • Makes sure that the length of the password is more than 8.
  • Makes sure that the username and password are not same e.g. username is SCOTT and the password is SCOTT. This check involves that the password can be in any case (UPPER CASE/LOWER CASE/Combination of Both) of the username, it will still reject the password. It basically compares lower(username)=lower(new_password).
  • Makes sure that the password is not the reverse of the username. Checks for the case also, same as above.
  • Makes sure that the password is not the same as the database name.
  • Checks for the simplicity of the password, but I think this check is not so robust, as oracle just checks it against some pre-defined keywords: ('welcome1', 'database1', 'account1', 'user1234', 'password1', 'oracle123', 'computer1', 'abcdefg1', 'change_on_install'). Can't complain also, as its not really possible to compare it with the dictionary. These are some very common 8 letter passwords, people use.
  • Makes sure that the password is not "oracle". This is a seperate check in the function, it could have been part of the above check.
  • Makes sure that the combination is alpha numeric, basically atleast 1 character and 1 number.
  • Makes sure that the new password differs from the new password by atleast 3 characters.

I believe these are great checks to make sure that the new password are complex enough. we can also add more check if required, like add more keywords we dont want as password, check for upper case and lower case characters, and enforce that the password should have Upper case characters, make sure that teh password also has special characters like !,@,#,$,% etc etc.

After this function get compiled, the script alters the default profile to start using this function:
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LOCK_TIME 1
PASSWORD_VERIFY_FUNCTION verify_function_11G;

PASSWORD_LIFE_TIME 180 --> Days after which you need to change password.
PASSWORD_GRACE_TIME 7 --> The time in days allowed after password expire, grace time.
PASSWORD_REUSE_TIME UNLIMITED --> This parameter makes sure that you cannot use the same password again.
PASSWORD_REUSE_MAX UNLIMITED --> This parameter makes sure that you cannot use the same password again.
FAILED_LOGIN_ATTEMPTS 10 --> Allowed password retries in case of entering wrong password.
PASSWORD_LOCK_TIME 1 --> Lock the user for 1 Day if it makes 10 failed password attempts.
PASSWORD_VERIFY_FUNCTION verify_function_11G; --> Set the password managment function.

Now talking about verify_function, or the older password management function provided by oracle:

Checks that this scripts enforces on passwords:
  • Makes sure that the username and password are not same e.g. username is SCOTT and the password is SCOTT. This check involves that the password can be in any case (UPPER CASE/LOWER CASE/Combination of Both) of the username, it will still reject the password. It basically compares lower(username)=lower(new_password).
  • Makes sure that the length of the password is more than 4.
  • Checks for the simplicity of the password, oracle just checks it against some pre-defined keywords:('welcome', 'database', 'account', 'user', 'password', 'oracle', 'computer', 'abcd')
  • Makes sure that the combination is alpha numeric with special character, basically atleast 1 character, 1 number and 1 special character. Special characters allowed: '!"#$%&()``*+,-/:;<=>?_'
  • Makes sure that the new password differs from the new password by atleast 3 characters.
ALTER PROFILE DEFAULT LIMIT
PASSWORD_LIFE_TIME 60
PASSWORD_GRACE_TIME 10
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 10
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1/1440
PASSWORD_VERIFY_FUNCTION verify_function;

PASSWORD_REUSE_TIME 60 --> Can you the same password after 60 days.
PASSWORD_REUSE_MAX 10 --> Can use the same password after using 10 different passwords after 60 days.

After running the above script the default profile is set to verify_function_11G. The alter profile with verify_function is commented.
But incase you want to revert to verify_function, you can do an alter profile as above.

Tuesday, March 24, 2009

How to: Move/Convert Database from Linux to Windows

There are two basic methods to achieve this.

Method 1
========

Steps 1:
Check the ENDIAN format of the platforms. Both Windows and linux should have the same format.
In our case we are moving from Linux 32-bit to Windows 32-bit:
col PLATFORM_NAME format a40
select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Microsoft Windows IA (32-bit) Little
Linux IA (32-bit) Little
Linux IA (64-bit) Little
Microsoft Windows IA (64-bit) Little

Step 2:
Check if the database can be transported. We need to use DBMS_TDB.CHECK_DB, to check if our database can be
transported to the target OS, in the way it is currently.
Need to start the database in READ ONLY mode:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 310380672 bytes
Database Buffers 104857600 bytes
Redo Buffers 6131712 bytes
Database mounted.
SQL> alter database open read only;

Database altered.
SQL> set serveroutput on
SQL> declare
2 check_db boolean;
3 begin
4 check_db:=dbms_tdb.check_db('Microsoft Windows IA (32-bit)');
5 end;
6 /

PL/SQL procedure successfully completed.

As we see no errors or message, so our database is ready to be transported to Windows 32-bit.

Step 3:
Check if there are any external files associated with the database, they will not be transported using RMAN.
SQL> set serveroutput on
SQL> declare
2 chk boolean;
3 begin
4 chk:=dbms_tdb.check_external;
5 end;
6 /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.FOR_HR, SYS.BLOB_TEST, SYS.IDR_DIR, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR,
SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.AUDIT_DIR, SYS.DATA_PUMP_DIR,
SYS.ORACLE_OCM_CONFIG_DIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA

PL/SQL procedure successfully completed.
The above directories exists in the database, we will need to recreate them with new locations once we complete the move.

Step 4:
Now we need to use RMAN convert database command to convert the source database to windows 32-bit.
The source database must be in read only mode.

RMAN> convert database new database 'ORCL'
2> transport script '/home/oracle/transport1.sql'
3> to platform 'Microsoft Windows IA (32-bit)'
4> db_file_name_convert '/home/oracle/product/oradata/test' '/home/oracle/con_dbf';

Starting conversion at source at 23-MAR-09
using channel ORA_DISK_1

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.FOR_HR found in the database
Directory SYS.BLOB_TEST found in the database
Directory SYS.IDR_DIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.AUDIT_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/home/oracle/product/oradata/test/users01.dbf
converted datafile=/home/oracle/con_dbf/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:05:21
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/home/oracle/product/oradata/test/sysaux01.dbf
converted datafile=/home/oracle/con_dbf/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/home/oracle/product/oradata/test/system01.dbf
converted datafile=/home/oracle/con_dbf/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=/home/oracle/product/oradata/test/example01.dbf
converted datafile=/home/oracle/con_dbf/example01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/home/oracle/product/oradata/test/undotbs01.dbf
converted datafile=/home/oracle/con_dbf/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Edit init.ora file /home/oracle/product/11g/db/dbs/init_00kal9vi_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script /home/oracle/transport1.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 23-MAR-09

RMAN>

Below are our converted database files:

oracle@oracle:~/con_dbf$ pwd
/home/oracle/con_dbf
oracle@oracle:~/con_dbf$ ls -lrt
total 7267080
-rw-r----- 1 oracle oracle 5608382464 2009-03-23 16:17 users01.dbf
-rw-r----- 1 oracle oracle 882057216 2009-03-23 16:18 sysaux01.dbf
-rw-r----- 1 oracle oracle 744497152 2009-03-23 16:19 system01.dbf
-rw-r----- 1 oracle oracle 104865792 2009-03-23 16:20 example01.dbf
-rw-r----- 1 oracle oracle 94380032 2009-03-23 16:20 undotbs01.dbf
-rw-r--r-- 1 oracle oracle 1710 2009-03-23 17:01 init_00kal9vi_1_0.ora
-rw-r--r-- 1 oracle oracle 2698 2009-03-23 17:01 transport1.sql
Screen shot of the transport1.sql file:

Step 5:
FTP the files to the windows server. I have used filezilla to copy the files to the windows server.

Step 6:
Use oradim to create the service:
C:\Documents and Settings\Administrator>oradim -new -sid orcl -intpwd oracle -s
artmode manual -pfile C:\oracle\11g\product\11.1.0\db_1\dbs\init_orcl.ora
Instance created.

Use the init file (init_00kal9vi_1_0.ora) to startup nomount the db on the windows machine:
Make required changes to the paths in the init ora file.
Screen shot of the init.ora file:

C:\Documents and Settings\Administrator>sqlplus sys as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Mar 23 17:31:25 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Enter password:
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1333620 bytes
Variable Size 310380172 bytes
Database Buffers 104857600 bytes
Redo Buffers 6098944 bytes
SQL>

Step 7:
Create the control file:
SQL> CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'C:\oracle\oradata\ORCL\log1.rdo' SIZE 50M,
9 GROUP 2 'C:\oracle\oradata\ORCL\log2.rdo' SIZE 50M,
10 GROUP 3 'C:\oracle\oradata\ORCL\log3.rdo' SIZE 50M
11 DATAFILE
12 'C:\oracle\oradata\ORCL\system01.dbf',
13 'C:\oracle\oradata\ORCL\sysaux01.dbf',
14 'C:\oracle\oradata\ORCL\undotbs01.dbf',
15 'C:\oracle\oradata\ORCL\users01.dbf',
16 'C:\oracle\oradata\ORCL\example01.dbf'
17 CHARACTER SET AL32UTF8;

Control file created.

Step 8:
Open the database.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00028: your session has been killed

SQL> select name from v$database;
ERROR:
ORA-03114: not connected to ORACLE

Meanwhile and checked if the redo log files were created, Saw that they were created. Logged in again and:

SQL> select name from v$database;
NAME
---------
ORCL

SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED

SQL> alter database open;

Database altered.

Step 9:
Add the tempfile.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\oracle\oradata\ORCL\temp01.dbf' SIZE
54525952 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

Step 10:
Do a sanity check of the database.
Run $ORACLE_HOME\rdbms\admin\utlrp.sql

SQL> @utlrp

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2009-03-24 14:43:54
PL/SQL procedure successfully completed.
TIMESTAMP
-------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2009-03-24 14:43:56
PL/SQL procedure successfully completed.
OBJECTS WITH ERRORS
-------------------
0
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
Invoking Ultra Search Install/Upgrade validation procedure VALIDATE_WK
Ultra Search VALIDATE_WK done with no error
PL/SQL procedure successfully completed.

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- ----------
ORCL READ WRITE

Database moved from linux to windows machine!!!!

Method 2
========

There another way to do the datafile conversion, we can convert the datafiles on the destination server also.
The rman script changes a little in that case.
RMAN> convert database on target platform
2> convert script '/home/oracle/convert.sql'
3> transport script '/home/oracle/transport.sql'
4> new database 'orcl'
5> format '/home/oracle/%U_%d';

The datafiles create by the above rman command should be copied to a temp directory on the destination server.
This will create a transport script to create the database instance on the destination server.
It will also create a rman script to convert the datafiles on the destination server.
It will look like:
run {
CONVERT DATAFILE 'C:\oracle\oradata\ORCL\SYSTEM.DBF'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT 'c:\temp\SYSTEM.DBF';
} ;
In the above case the files copied from the linux server were kept in c:\temp and then they would be converted to
C:\oracle\oradata\ORCL location.
After the conversion, run the transport.sql script after making the required changes.
After you have opened the database the rest of the steps are the same.

Monday, March 23, 2009

How To: Install 11g DB on Windows

Screen Shots and Steps to Install 11g on Windows:
1. Download the software from www.oracle.com

2. Unzip the download and click on setup.exe.

Screen 1:
Select the basic installation of advanced. For our installation I am choosing advanced (Select Advanced and press next).

From Apun's Weblog

Screen 2:
Select the installation type, we are installing Enterprise Edition (Press next):
From Apun's Weblog

Screen 3:
Set the installation location. It takes about 2.77 GB of disk space (Set the installation location and press next).
From Apun's Weblog

Screen 4:
Oracle does some product specific prerequisite check (press next, fix if there are any issues).
From Apun's Weblog

Screen 5:
Select the database options you would like/need to install. I have selected all the options(Press Select all and press next).
From Apun's Weblog

Screen 6:
Ask if you want to create a database or an asm instance. I will create DB and asm instance later(Select software install only and press next).
From Apun's Weblog

Screen 7:
Information to connect to Oracle Configuration Manager. Requires information like the Oracle CSI number, metalink username etc (Press Next).
From Apun's Weblog

Screen 8:
Installation Summary Page (Press Install):
From Apun's Weblog

Screen 9:
Installation progress.
From Apun's Weblog

Screen 10:
End of successful install (Press Exit).
From Apun's Weblog

Friday, March 20, 2009

How to enable archivelog mode in Oracle 11g database

First lets check the current log mode:
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Mar 20 14:33:05 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 165
Current log sequence 167

SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
TEST NOARCHIVELOG

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /home/oracle/product/11g/db/db
s/spfiletest.ora
Now lets try to set the archive log destination:

SQL> alter system set log_archive_dest_1='/home/oracle/archivelog' scope=spfile;
alter system set log_archive_dest_1='/home/oracle/archivelog' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

I faced the above error, thought need to enable archiving first:

SQL> alter system set log_archive_start=TRUE
2 scope=spfile;

System altered.

But I received the error again.

SQL> alter system set log_archive_dest_1='/home/oracle/archivelog' scope=spfile;
alter system set log_archive_dest_1='/home/oracle/archivelog' scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE

Checked the error description:
SQL> !oerr ora 16179
16179, 00000, "incremental changes to \"%s\" not allowed with SPFILE"
// *Cause: Incremental changes to a log_archive_dest_n parameter cannot
// be made when using an SPFILE.
// *Action: Specify either LOCATION or SERVICE plus all other attributes
// to be set in one ALTER SYSTEM/SESSION SET command.

So I did some research and found that when using spfile and trying to set a parameter like log_archive_dest_1, we need to give
complete format of the parameter like:

SQL> alter system set log_archive_dest_1='location=/home/oracle/archivelog' scope=spfile;

System altered.
(Optionally we can also give other parameters in this statement like optional, reopen etc.)

Now let shutdown the database and do a startup mount:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 310380672 bytes
Database Buffers 104857600 bytes
Redo Buffers 6131712 bytes
Database mounted.

Enabling archivelog mode

SQL> alter database archivelog;

Database altered.

Open the database:

SQL> alter database open;

Database altered.

Test if archivelog is set properly:

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archivelog
Oldest online log sequence 165
Next log sequence to archive 167
Current log sequence 167
SQL> select name, log_mode from v$database;

NAME LOG_MODE
--------- ------------
TEST ARCHIVELOG

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> !ls -lrt /home/oracle/archivelog
total 44496
-rw-r----- 1 oracle oracle 45509632 2009-03-20 14:43 1_167_677948664.dbf
-rw-r----- 1 oracle oracle 1024 2009-03-20 14:43 1_168_677948664.dbf

So now we have enabled archivelog mode and have some archivelog file also!!!

Tuesday, March 17, 2009

Deduplication Using SecureFile for LOBS in 11g

In my previous blog, I had moved a lob from basicfile to securefile. I will use the same table for the dedupication example.
I had five rows in that table, 3 had similar lob data.

Storage Stats of the table:
SQL> select owner,table_name,COLUMN_NAME,SEGMENT_NAME from dba_lobs where table_name='BLOB_TEST';

OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME
---------- ---------- --------------- --------------------------------------------------
HR BLOB_TEST DATA SYS_LOB0000073688C00002$$

The below plsql block is courtesy oracle
declare
l_segment_size_blocks number;
l_segment_size_bytes number;
l_used_blocks number;
l_used_bytes number;
l_expired_blocks number;
l_expired_bytes number;
l_unexpired_blocks number;
l_unexpired_bytes number;

begin
dbms_space.space_usage(
segment_owner => 'HR',
segment_name => 'SYS_LOB0000073688C00002$$',
segment_type => 'LOB',
partition_name => NULL,
segment_size_blocks => l_segment_size_blocks,
segment_size_bytes => l_segment_size_bytes,
used_blocks => l_used_blocks,
used_bytes => l_used_bytes,
expired_blocks => l_expired_blocks,
expired_bytes => l_expired_bytes,
unexpired_blocks => l_unexpired_blocks,
unexpired_bytes => l_unexpired_bytes
);
dbms_output.put_line('Segment Size in Blocks => '|| l_segment_size_blocks);
dbms_output.put_line('Segment Size in Bytes => '|| l_segment_size_bytes);
dbms_output.put_line('Used Blocks => '|| l_used_blocks);
dbms_output.put_line('Used Bytes => '|| l_used_bytes);
dbms_output.put_line('Expired Blocks => '|| l_expired_blocks);
dbms_output.put_line('Expired Bytes => '|| l_expired_bytes);
dbms_output.put_line('Unexpired Blocks => '|| l_unexpired_blocks);
dbms_output.put_line('Unexpired Bytes => '|| l_unexpired_bytes);
end;
/
============================================
Segment Size in Blocks => 189848
Segment Size in Bytes => 1555234816
Used Blocks => 167392
Used Bytes => 1371275264 (1307.75 MB)
Expired Blocks => 22246
Expired Bytes => 182239232
Unexpired Blocks => 0
Unexpired Bytes => 0
============================================
SQL> alter table blob_test modify lob(data) (deduplicate);
Table altered.

This command takes some time to run.
Storage Stats after the deduplication.
============================================
Segment Size in Blocks => 416408
Segment Size in Bytes => 3411214336
Used Blocks => 55799
Used Bytes => 457105408 (435.93 MB)
Expired Blocks => 80882
Expired Bytes => 662585344
Unexpired Blocks => 279292
Unexpired Bytes => 2287960064
============================================

You can see that the Used Bytes column has come down visibly. As I had mentioned that there were 3 LOBs of the same size, which shows that the size has come down to 1/3rd.
The blob stored was:
a.dmp, b.dmp and c.dmp the same ones with size 428.88

Move from BasicFiles to Securefiles 11g

Let do the change from a BasicFile to SecureFile in 11g using a very basic example.

Lets create a table with BLOB using BasicFile.

SQL> create table BLOB_test (name varchar2(20),data BLOB);
Table created.

Lets load some data into the table.

SQL> create directory FOR_HR as '/home/oracle/';
Directory created.

SQL> grant read,write on directory FOR_HR to hr;
Grant succeeded.

I am using the below procedure to quickly load a few files:

CREATE OR REPLACE PROCEDURE blob_load (name IN BLOB_test.name%TYPE)
IS
b_data BFILE;
e_blob BLOB;
BEGIN
insert into BLOB_test values (name,EMPTY_BLOB() ) returning data into e_blob;
b_data := bfilename( 'FOR_HR', name);
Dbms_Lob.Fileopen(b_data, Dbms_Lob.File_Readonly);
Dbms_Lob.Loadfromfile(e_blob, b_data, Dbms_Lob.Getlength(b_data));
Dbms_Lob.Fileclose(b_data);
COMMIT;
END;
/

SQL> exec blob_load('a.dmp');
PL/SQL procedure successfully completed.

......
SQL> select count(*) from blob_test;

COUNT(*)
----------
5

Now lets try to migrate to SecureFiles in 11g:
First check the system parameter db_securefile:
SQL> show parameter db_securefile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_securefile string PERMITTED

Lets create a new table with SecureFile feature.

SQL> create table blob_test_1 (name varchar2(20),data blob)
lob (data) store as securefile; 2
Table created.

Now we need to use the dbms_redefinition package to define the column mappings:

SQL> exec dbms_redefinition.start_redef_table('HR','BLOB_TEST','BLOB_TEST_1','name name ,'||'data data');
BEGIN dbms_redefinition.start_redef_table('HR','BLOB_TEST','BLOB_TEST_1','name name ,'||'data data'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "HR"."BLOB_TEST" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1631
ORA-06512: at line 1

SQL> alter table blob_test add primary key (name);
Table altered.

SQL> alter table blob_test_1 add primary key (name);
Table altered.

SQL> exec dbms_redefinition.start_redef_table('HR','BLOB_TEST','BLOB_TEST_1','name name ,'||'data data');
PL/SQL procedure successfully completed.

Details of activity from v$sesstat, where 122 is our session id.

122 securefile allocation bytes 1,371,267,072
122 securefile allocation chunks 1,255
122 securefile direct write bytes 1,371,267,072
122 securefile direct write ops 1,809
122 securefile bytes non-transformed 1,349,152,270
122 securefile number of non-transformed flushes 326

SQL> select name from blob_test;
NAME
--------------------
a.dmp
b.dmp
c.dmp
d.log
e.log

SQL> select name from blob_test_1;
NAME
--------------------
a.dmp
b.dmp
c.dmp
d.log
e.log

SQL> exec dbms_redefinition.finish_redef_table ('HR','BLOB_TEST','BLOB_TEST_1');
PL/SQL procedure successfully completed.

SQL> select owner,table_name,COLUMN_NAME,SECUREFILE from dba_lobs where table_name='BLOB_TEST';

OWNER TABLE_NAME COLUMN_NAME SEC
---------- ---------- --------------- ---
HR BLOB_TEST DATA YES

SQL> drop table blob_test_1;
drop table blob_test_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
(The temporary table got removed automatically)

My Music Station - The Best Of Best

Monday, March 16, 2009

How To: Creation fo Cache Group Using Cache Administrator:

http://localhost:17004/cache/
Login Page:

From Timesten
In our case:
TimesTen DSN: myDSN
Oracle Username: hr
Oracle Password: hr
TimesTen Password: hr

Welcome Page:
From Timesten

Select Create under Create Cache Group Definitions.

Shows the creation Page:
From Timesten
We will be creating a User Managed Cache Group Definition.

From Timesten

We select User Managed, propogate all tables (from the attributes section), Autorefresh every 5 seconds(from the autorefresh definitions).
Our Root table would be HR. REGIONS, child tables would be HR.COUNTRIES and HR.LOCATIONS.

Edit the table setting, by selecting the columns we need and defining the foreign key relations in the tables:

From Timesten
Defining foreign key for the table countries and regions:
From Timesten
Select required columns and attributes for the countries table:
From Timesten
Define foreign key relation between countries and locations table:

From Timesten

From Timesten
Define required columns and attributes for the locations table:

From Timesten
Press Save definition and it will take you to the Definition Summary Page:

From Timesten
Now click on create under the Cache Group section in the menu on the left:

From Timesten
Press Create Cache Group:

From Timesten
This will create the cache group called OUR_TEST.

Below is the SQL to create the same group manually (ttIsql myDSN):

SQL Code for creating the same user managed cache group:

CREATE USERMANAGED CACHE GROUP "OUR_TEST"
AUTOREFRESH MODE INCREMENTAL INTERVAL 5 SECONDS
STATE ON
FROM
"HR"."REGIONS" (
"REGION_ID" NUMBER NOT NULL,
"REGION_NAME" VARCHAR2(25 BYTE),
PRIMARY KEY("REGION_ID"), PROPAGATE
),
"HR"."COUNTRIES" (
"COUNTRY_ID" CHAR(2 BYTE) NOT NULL,
"COUNTRY_NAME" VARCHAR2(40 BYTE),
"REGION_ID" NUMBER,
PRIMARY KEY("COUNTRY_ID"),
FOREIGN KEY("REGION_ID")
REFERENCES "HR"."REGIONS"("REGION_ID"), PROPAGATE
),
"HR"."LOCATIONS" (
"LOCATION_ID" NUMBER(4) NOT NULL,
"STREET_ADDRESS" VARCHAR2(40 BYTE),
"POSTAL_CODE" VARCHAR2(12 BYTE),
"CITY" VARCHAR2(30 BYTE) NOT NULL,
"STATE_PROVINCE" VARCHAR2(25 BYTE),
"COUNTRY_ID" CHAR(2 BYTE),
PRIMARY KEY("LOCATION_ID"),
FOREIGN KEY("COUNTRY_ID")
REFERENCES "HR"."COUNTRIES"("COUNTRY_ID"), PROPAGATE
);


Lets check the cache group we created using the cache administrator.

oracle@oracle:~/TimesTen/timesten/startup$ ttIsql myDSN

Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.

connect "DSN=myDSN";
Enter password for 'hr':
Connection successful: DSN=myDSN;UID=hr;DataStore=/home/oracle/TimesTen/data;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;OracleId=test;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)
Command> cachegroups;

Cache Group HR.EMP_IN_MEM:

Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds

Root Table: HR.EMPLOYEES
Table Type: Read Only

Cache Group HR.OUR_TEST:

Cache Group Type: User Managed
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Minutes

Root Table: HR.REGIONS
Table Type: Propagate

Child Table: HR.COUNTRIES
Table Type: Propagate

Child Table: HR.LOCATIONS
Table Type: Propagate

2 cache groups found.
Command>
Lets check the record count:
Command> select * from regions;
0 rows found.
Oops no rows show up....we still need to create a cache agent.

Command> load cache group our_test commit every 20 rows;
8289: Manual LOAD/REFRESH of cache group requires AUTOREFRESH STATE to be PAUSED, but the STATE of HR.OUR_TEST is ON. The LOAD is not allowed
The command failed.
Command>


We need to use ttAdmin to create a cache agent, this is to set the password for the cache agent:

oracle@oracle:~/TimesTen/timesten/bin$ ttAdmin -cacheUidPwdSet -cacheUid hr -cachePwd hr myDSN
Enter password for 'hr':
Cache User Id : hr
RAM Residence Policy : inUse
Replication Agent Policy : manual
Replication Manually Started : False
Cache Agent Policy : manual
Cache Agent Manually Started : False


Now start the cache agent:
oracle@oracle:~/TimesTen/timesten/bin$ ttAdmin -cacheStart myDSN
Enter password for 'hr':
RAM Residence Policy : inUse
Replication Agent Policy : manual
Replication Manually Started : False
Cache Agent Policy : manual
Cache Agent Manually Started : True


Now lets check the record count again.

oracle@oracle:~/TimesTen/timesten/bin$ ttIsql myDSN
Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=myDSN";
Enter password for 'hr':
Connection successful: DSN=myDSN;UID=hr;DataStore=/home/oracle/TimesTen/data;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;OracleId=test;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)
Command> select * from regions;
1, Europe
2, Americas
3, Asia
4, Middle East and Africa
4 rows found.


We got the rows this time!!!!
Check the Cache agent policy, by default its manual that means the agent will not start automatically when the timesten daemon restarts.
Command> call ttCachePolicyGet;
manual
1 row found.


Lets change it to auto start:
Command> call ttCachePolicySet ('always');
Command> call ttCachePolicyGet;
always
1 row found.


Lets check the status of our TimesTen daemon:
oracle@oracle:~/TimesTen/timesten/bin$ ttstatus
TimesTen status report as of Mon Mar 16 09:51:10 2009

Daemon pid 6427 port 17000 instance timesten
TimesTen server pid 6436 started on port 17002
TimesTen webserver pid 6434 started on port 17004

------------------------------------------------------------------------
Data store /home/oracle/TimesTen/data
There are 11 connections to the data store
Data store is in shared mode
Shared Memory KEY 0x4d0148ad ID 2752530
Type PID Context Connection Name ConnID
Cache Agent 7276 0x081e2758 Handler 2
Cache Agent 7276 0x0826b710 Timer 3
Cache Agent 7276 0x082dce30 Aging 4
Cache Agent 7276 0x0839d710 timestenorad 6
Cache Agent 7276 0x0841b1e0 timestenorad 7
Subdaemon 6431 0x08098e38 Worker 2042
Subdaemon 6431 0x0810f020 HistGC 2046
Subdaemon 6431 0x0816e4c0 Checkpoint 2044
Subdaemon 6431 0x081ad950 Flusher 2043
Subdaemon 6431 0x0820cdf0 Aging 2045
Subdaemon 6431 0x0824c280 Monitor 2047
Replication policy : Manual
Cache agent policy : Always
TimesTen's Cache agent is running for this data store
------------------------------------------------------------------------
Data store /home/oracle/TimesTen/timesten/info/TT_timesten
There are no connections to the data store
Replication policy : Manual
Cache agent policy : Manual
------------------------------------------------------------------------
Access control enabled.
End of report
oracle@oracle:~/TimesTen/timesten/bin$


To check the success of our configuration, lets try adding rows:
Adding row from oracle:
SQL> select * from regions;

REGION_ID REGION_NAME
---------- -------------------------
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

SQL> insert into regions values (5,'Antartica');
1 row created.
SQL> commit;
Commit complete.

Checking Timesten:

oracle@oracle:~$ ttIsql myDSN
Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=myDSN";
Enter password for 'hr':
Connection successful: DSN=myDSN;UID=hr;DataStore=/home/oracle/TimesTen/data;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;OracleId=test;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)
Command> select * from regions;
1, Europe
2, Americas
3, Asia
4, Middle East and Africa
5, Antartica
5 rows found.

Command>

Row updated.


Now adding row from TimeTen:
Command> insert into regions values (6,'Artic');
1 row inserted.


Checking Oracle DB:
SQL> select * from regions;
REGION_ID REGION_NAME
---------- -------------------------
5 Antartica
6 Artic
1 Europe
2 Americas
3 Asia
4 Middle East and Africa
6 rows selected.


Working both ways. Now lets check the foreign key definitions we made:

Command> insert into countries values ('AT','MYCOUNTRY',10);
3001: Foreign key violation [REGIONS]: the row in child table HR.COUNTRIES has no key in the parent table HR.REGIONS
The command failed.

Well the definitions works well!!!!

Tuesday, March 10, 2009

How To Create Cache Group in TimesTen

Cache groups are used to cache oracle database data in TimesTen in-memory system.
You can either cache the whole table, or part of it by defining the columns.
You cannot cache LOB datatypes as of now in TimesTen, the biggest available chumk is LONG RAW.
You can create a cache group of multiple tables or single table as per your requirement.
If you have multiple tables in a cache group you can define parent child relations.
Though the tables in a cache group must be joined by foreign key constraints in TimesTen,
the tables do not necessarily need to be joined in the Oracle instance .
There can be different types of cache group configurations, depending upon the requirements.

Types of cache groups:

-->READONLY - enforces a caching behavior in which updates on Oracle tables are applied to TimesTen through the AUTOREFRESH mechanism.
-->SYNCHRONOUS WRITETHROUGH - enforces a caching behavior in which cached data is updated in TimesTen and propagated to Oracle. Updates to a SWT cache group are committed synchronously.
-->ASYNCHRONOUS WRITETHROUGH (AWT) cache groups - enforces caching behavior in which cached data is updated in TimesTen and propagated to Oracle. Updates to an AWT cache group are committed asynchronously.
-->USERMANAGED - Customizable/user defined cache group.

Setting up Oracle:
Create the schema to be used for caching. I am using the oracle provide "HR" sample schema on the oracle side.

Setting up TimesTen:
1.) Create user on TimeTen:
oracle@oracle:~/TimesTen/timesten/bin$ ./ttIsql tt_timesten

Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=tt_timesten";
Connection successful: DSN=TT_timesten;UID=oracle;DataStore=/home/oracle/TimesTen/timesten/info/TT_timesten;DatabaseCharacterSet=US7ASCII;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;TypeMode=0;
(Default setting AutoCommit=1)

Command> create user hr identified by 'hr';
Command> grant admin,ddl to hr;
Command> exit
Disconnecting...
Done.

2.) Create TimesTen DSN:
Need to add the entry of the dsn in the sys.odbc.ini file on the TimesTen server and sys.ttconnect.ini on the TimesTen client.
The file is located at $TIMESTEN_HOME/info
Parameters to be defined:
[myDSN]
Driver=/TimesTen/timesten/lib/libtten.so ##See the default one in the file and use the same path.
Datastore=/TimesTen/data ##Defined the place where TimesTen can store the cache data.
PermSize=16 ##Defines permanent size of the data store.
TempSize=16 ##Temporary size
UID=hr ##TimesTen user you created.
OracleId=hr ##DB name/tnsnames.ora entry.
OraclePwd=hr ##Oracle password
DatabaseCharacterSet=AL32UTF8 ##Should be same as the oracle database

oracle@oracle:~/TimesTen$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Tue Mar 10 14:18:09 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';
VALUE
--------------------------------------------------------------------------------
AL32UTF8

3.) Create oracle tables:
As we are using the HR schema, we will use the following tables:
COUNTRIES
DEPARTMENTS
EMPLOYEES
EMP_DETAILS_VIEW
JOBS
JOB_HISTORY
LOCATIONS
REGIONS

4.) Create the cache group:

VERY IMPORTANT THING TO REMEMBER:
Make sure that your environment has the correct location of the TNS_ADMIN variable. That means it points to the correct tnsnames.ora.
In my case I had both 10g and 11g installed and had a problem with the location and was receiving the following error(ORA-12154):
5220: Permanent Oracle connection failure error in OCIServerAttach(): ORA-12154: TNS:could not resolve the connect identifier specified rc = -1
5935: Could not validate Oracle login: uid = hr, pwd = HIDDEN, oracleId = hr

oracle@oracle:~/product/11g/db$ ttIsql myDSN
Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=myDSN";
Enter password for 'hr':
Connection successful: DSN=myDSN;UID=hr;DataStore=/home/oracle/TimesTen/data;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;OracleId=test;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)

Command> call ttCacheUidPwdSet('hr','hr');

Command> call ttCacheStart;

Command> CREATE READONLY CACHE GROUP EMP_IN_MEM
> AUTOREFRESH INTERVAL 5 SECONDS
> FROM EMPLOYEES
> (EMPLOYEE_ID NUMBER(6) NOT NULL PRIMARY KEY,FIRST_NAME VARCHAR2(20),LAST_NAME VARCHAR2(25) NOT NULL,EMAIL VARCHAR2(25) NOT NULL);

Command> LOAD CACHE GROUP EMP_IN_MEM COMMIT EVERY 256 ROWS;
107 cache instances affected.

Command> cachegroups;

Cache Group HR.EMP_IN_MEM:

Cache Group Type: Read Only
Autorefresh: Yes
Autorefresh Mode: Incremental
Autorefresh State: On
Autorefresh Interval: 5 Seconds

Root Table: HR.EMPLOYEES
Table Type: Read Only

1 cache group found.

Command> select count(*) from EMPLOYEES;
107
1 row found.
Command> select * from EMPLOYEES;
100, Steven, King, SKING
101, Neena, Kochhar, NKOCHHAR
102, Lex, De Haan, LDEHAAN
103, Alexander, Hunold, AHUNOLD
104, Bruce, Ernst, BERNST
...

So we were successfully able to create a read only cache group.

To test further, lets add data in the oracle database, as of now the table has 107 rows.

SQL> insert into employees values (500,'Apun','Hiran','me@me.com','1111111111','01-JAN-2007','AD_PRES',50000,NULL,NULL,NULL);
1 row created.
SQL> insert into employees values (501,'Rick','Smith','ricksmith@me.com','1234567891','01-JAN-2007','AD_PRES',50000,NULL,NULL,NULL);
1 row created.
SQL> commit;
Commit complete.

We added 2 rows. Lets see if the record count in TimesTen has changed.

oracle@oracle:~/product/11g/db$ ttIsql myDSN
Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.
connect "DSN=myDSN";
Enter password for 'hr':
Connection successful: DSN=myDSN;UID=hr;DataStore=/home/oracle/TimesTen/data;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=US7ASCII;DRIVER=/home/oracle/TimesTen/timesten/lib/libtten.so;OracleId=test;PermSize=16;TempSize=16;TypeMode=0;
(Default setting AutoCommit=1)

Command> select count(*) from EMPLOYEES;
109
1 row found.

Command> select * from EMPLOYEES where EMPLOYEE_ID in (500,501);
500, Apun, Hiran, me@me.com
501, Rick, Smith, ricksmith@me.com

2 rows found.
Command>

This completed the testing for our cache group!!!


Start/Stop TimesTen

To Start/Stop/Restart Timesten:

oracle@oracle:~/TimesTen/timesten/startup$ ./tt_timesten
USAGE: ./tt_timesten { start | stop | restart }


oracle@oracle:~/TimesTen/timesten/startup$ ps -ef|grep times

oracle 8187 1 0 12:14 ? 00:00:00 /home/oracle/TimesTen/timesten/bin/timestend -initfd 13
oracle 8190 8187 0 12:14 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 0 -facility user -accctl
oracle 8191 8187 0 12:14 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1 -facility user -accctl
oracle 8192 8187 0 12:14 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 2 -facility user -accctl
oracle 8193 8187 0 12:14 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 3 -facility user -accctl
oracle 8194 8187 0 12:14 ? 00:00:00 /timesten/bin/timestenws -verbose -userlog tterrors.log -supportlog ttmesg.log -id 4 -facility user -accctl
oracle 8196 8187 0 12:14 ? 00:00:00 /timesten/bin/ttcserver -verbose -userlog tterrors.log -supportlog ttmesg.log -id 5 -p 17002 -facility user -accctl
oracle 10252 7344 0 12:48 pts/2 00:00:00 grep times


oracle@oracle:~/TimesTen/timesten/startup$ ./tt_timesten stop

Stopping TimesTen Daemon : [ OK ]


oracle@oracle:~/TimesTen/timesten/startup$ ps -ef|grep times

oracle 10322 7344 0 12:49 pts/2 00:00:00 grep times


oracle@oracle:~/TimesTen/timesten/startup$ ./tt_timesten start

Starting TimesTen Daemon : [ OK ]
oracle@oracle:~/TimesTen/timesten/startup$ ps -ef|grep times
oracle 10338 1 0 12:49 ? 00:00:00 /timesten/bin/timestend -initfd 13
oracle 10341 10338 0 12:49 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 0 -facility user -accctl
oracle 10342 10338 0 12:49 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 1 -facility user -accctl
oracle 10343 10338 0 12:49 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 2 -facility user -accctl
oracle 10346 10338 0 12:49 ? 00:00:00 /timesten/bin/timestensubd -verbose -userlog tterrors.log -supportlog ttmesg.log -id 3 -facility user -accctl
oracle 10352 10338 0 12:49 ? 00:00:00 /timesten/bin/timestenws -verbose -userlog tterrors.log -supportlog ttmesg.log -id 4 -facility user -accctl
oracle 10360 10338 0 12:49 ? 00:00:00 /timesten/bin/ttcserver -verbose -userlog tterrors.log -supportlog ttmesg.log -id 5 -p 17002 -facility user -accctl
oracle 10406 7344 0 12:49 pts/2 00:00:00 grep times


oracle@oracle:~/TimesTen/timesten/startup$ ./tt_timesten restart

Stopping TimesTen Daemon : [ OK ]
Starting TimesTen Daemon : [ OK ]

Installation of TimesTen 7.0.5 - Oracle In-Memory Database Cache

You can download the latest version from TimesTen Download

unzip timesten70500.linux86.tar.zip
tar -xvf timesten70500.linux86.tar
oracle@oracle:~/software/linux86$ ls
3rdparty behaviorchanges.txt doc install.pl LINUX86 README.html setup.sh uninst.sh
oracle@oracle:~/software/linux86$
oracle@oracle:~/software/linux86$ ./setup.sh

NOTE: Each TimesTen installation is identified by a unique instance name.
The instance name must be a non-null alphanumeric string, not longer
than 255 characters.

Please choose an instance name for this installation? [ tt70 ] timesten
Instance name will be 'timesten'.
Is this correct? [ yes ] yes

Please select a product :

[1] Oracle TimesTen In-Memory Database
[2] Oracle In-Memory Database Cache

Which product would you like to install? [ 1 ] 2

Of the three components:

[1] Client/Server and Data Manager
[2] Data Manager Only
[3] Client Only

Which would you like to install? [ 1 ]
Where would you like to install the timesten instance of TimesTen? [ /home/oracle ] /home/oracle/TimesTen
Where would you like to create the daemon home directory? [ /home/oracle/TimesTen/timesten/info ]
The directory /home/oracle/TimesTen/timesten/info does not exist.
Do you want to create it? [ yes ]
Installing into /home/oracle/TimesTen/timesten ...
Uncompressing ...


The TimesTen Demo applications can take up to 64 Mbytes of disk space.
Depending on how your system is configured, you may not want to create the
DemoDataStore directory in the default location,
/home/oracle/TimesTen/timesten/info/DemoDataStore

Where would you like to create the DemoDataStore directory? [ /home/oracle/TimesTen/timesten/info ]
Creating /home/oracle/TimesTen/timesten/info/DemoDataStore ...

NOTE: All installations that replicate to each other must use the same daemon
port number that is set at installation time. The daemon port number can
be verified by running 'ttVersion'.

The default port number is 17000.

Do you want to use the default port number for the TimesTen daemon? [ yes ]
The daemon will run on the default port number (17000).

Processing /home/oracle/TimesTen/timesten/PERL/perl.tar ...

Would you like to enable datastore access control? [ no ] yes
The daemon logs will be located in /home/oracle/TimesTen/timesten/info
Would you like to specify a different location for the daemon logs? [ no ]

The following variables have been set in the file :
/home/oracle/TimesTen/timesten/bin/ttThunk

ORACLE_HOME=/home/oracle/product/10.2/db
LD_LIBRARY_PATH=/home/oracle/TimesTen/timesten/lib:/home/oracle/product/10.2/db/lib32:/home/oracle/product/10.2/db/network/lib32:/home/oracle/product/10.2/db/lib:/home/oracle/product/10.2/db/network/lib

Would you like to enable the Cache Connect to Oracle Administrator? [ yes ]
What TCP/IP port number would you like Cache Connect to Oracle Administrator to listen on? [ 17004 ]

NOTE: To access the TimesTen Cache Connect to Oracle Administrator
go to the url: http://localhost:17004/cache



NOTE: It appears that you are running version 4.1 of the g++
compiler. TimesTen ships with multiple sets of client libraries and server
binaries : one built with g++ 3.2.3, one with g++ 3.4.6, and one with
g++ 4.1.0. The installer has created links to the 4.1.0 library in the
/lib directory and to the 4.1.0 server binary in the
/bin directory. If you want to use a different compiler,
please modify the links to point to the desired library and server binary.

Installing server components ...
Would you like to log all server Connects/Disconnects? [ yes ]
What is the TCP/IP port number that you want the TimesTen Server to listen on? [ 17002 ]
Starting the daemon ...
TimesTen Daemon startup OK.

Installing client components ...
What is the name of the host running the TimesTen server? [ oracle ]
What is the TCP/IP port number that the TimesTen server is listening on? [ 17002 ]
What is the name of the instance running the TimesTen server? [ timesten ]
Creating new /home/oracle/TimesTen/timesten/info/sys.ttconnect.ini
Extracting 3rd party tools ...
Would you like to install the documentation? [ yes ]
Where would you like to create the doc directory (s=skip)? [ /home/oracle/TimesTen/timesten/doc ]
The directory /home/oracle/TimesTen/timesten/doc does not exist.
Do you want to create it? [ yes ]

NOTE: The TimesTen daemon startup/shutdown scripts have not been installed.

Run the script '/home/oracle/TimesTen/timesten/bin/setuproot' as root.
This will move the TimesTen startup script into its appropriate location.

The startup script is currently located here :
'/home/oracle/TimesTen/timesten/startup/tt_timesten'.

End of TimesTen installation.