Enabling Oracle Label Security on the 11g RAC database

Oracle Label Security is a feature of Oracle Database Enterprise Edition ( Needs separate license for OLS) which protects the row level data by enforcing the labels. Users authorized to access the label will have the access to the row.
Follow the process below to enable the OLS on 11gR2 RAC database:
Shutdown the Applications and database:
If you have any application tier like EBS etc, Shutdown EBS Applications
adstpall.sh apps/appspwd
Shutdown oracle Database
srvctl stop database -d dbname
srvctl stop database -d orcl
on RAC node1:
Cd $ORACLE_HOME/bin
chopt enable lbac
Verify whether OLS is enabled or not:
cd $ORACLE_HOME/rdbms/lib
On AIX:
ar -X64 -t libknlopt.a | grep -c kzlilbac.o ( output should come as 1 on both nodes)
If the output is 1 (enabled), 0 (disabled)
Repeat the above steps on the second RAC node also.
start up the database
cd $ORACLE_HOME/bin
srvctl start database -d dbname
login as sysdba and run
@?/rdbms/admin/catols.sql (This command will shutdown the current instance)
shutdown the running second instance:
srvctl stop instance -d dbname -i instance-name
srvctl stop instance -d orcl -i orcl2
restart the database
srvctl start database -d dbname
srvctl start database -d orcl
verify the Label Security installation:
SQL> select * from v$option where parameter like '%ecurity%'; PARAMETER ---------------------------------------------------------------- VALUE ---------------------------------------------------------------- Enterprise User Security TRUE Oracle Label Security TRUE
SQL> select username,account_status from dba_users where username like '%LBAC%'; USERNAME ACCOUNT_STATUS ------------------------------ -------------------------------- LBACSYS OPEN

How to find the version of Techstack, OA Frame work etc in R12

There is a question always being asked very frequently when you have raised any Oracle SR's is whatz the version of a particular component in R12.
The following process will give you the answers for the most of the components in R12

1. Login to as applmgr

2. cd $FND_TOP/patch/115/bin

perl TXKScript.pl -script=$FND_TOP/patch/115/bin/txkInventory.pl -txktop=$APPLTMP -contextfile=$CONTEXT_FILE -appspass= -outfile=$OA_HTML/techstack_info.html

This step will create a techstack_info.html file under $OA_HTML directory

3. You can access the techstack_info.html using from the browser using

http(s)://servername.domain:port/OA_HTML/techstack_info.html

The output looks similar to the following

TXK Technology Inventory Report for EBS Release 12

Report Header

Date Tue Aug 19 10:50:42 2008
Hostname lxs3er06.solutions.glbsnet.com
Enabled Services Root Service, Web Entry Point Services, Web Application Services, Batch Processing Services
Instance R12VIS_lxs3er06
Platform LINUX 32-bit
Tier APPS
OS release 2.6.9-67.ELsmp
DB Host lxs3er06.solutions.glbsnet.com
Context File /data/R12VIS/inst/apps/R12VIS_lxs3er06/appl/admin/R12VIS_lxs3er06.xml (120.217.12000000.43)
Report File /data/R12VIS/apps/apps_st/comn/webapps/oacore/html/techstack_info.html
XML Definition File /data/R12VIS/apps/apps_st/appl/fnd/12.0.0/html/txkInventory.xml (120.5.12000000.5)

HTTP Service Properties (lxs3er06.solutions.glbsnet.com):
Technology Components / Properties Component Version/Value
Oracle HTTP Server version 10.1.3.0.0
Sun JDK client version 1.5.0_13
JDK version on HTTP server node 1.5.0_08
Oracle JDBC driver version using JAVA API (oracle.jdbc.driver.OracleDriver) 10.2.0.3.0
Oracle AOLJ version Roll Up Patch J
Oracle BC4J version 10.1.3.41.57
Oracle BiBeans version 3.1.1.5
Oracle HTTP client version 10h
Java object cache 10.1.3
Oracle JRAD libraries version 10.1.3
Oracle MDS version 9.0.6.0.0_26
OA Framework version 12.0.4
Oracle help web version 2.0.8
Oracle XML driver version 10.1.3.0.0
Oracle UIX version 2_3_6_2
OJSP version 10.1.3.0.0
JDK version used by AD utilities on HTTP node 1.5.0_08
Number of symbolic links found under directories served by oracle HTTP server 0
Version of OWA packages 10.1.2.0.5
Package OWA_MATCH exists in SYS schema TRUE
OPatch version on 10.1.3 Oracle Home 1.0.0.0.57


Forms Service Properties (lxs3er06.solutions.glbsnet.com):
Technology Components / Properties Component Version/Value
Developer 10g version 10.1.2.2.0
DB client (RSF) library version in 10.1.2 oracle home 10.1.0.5.0
Forms runtime configuration from context file servlet
JDK version used by AD utilities on forms node 1.5.0_08
Oracle Application Server patchset version 10.1.2.2.0
OPatch version on 10.1.2 Oracle Home 1.0.0.0.57


Concurrent Processing Service Properties (lxs3er06.solutions.glbsnet.com):
Technology Components / Properties Component Version/Value
JDK on concurrent processing node 1.5.0_08
JDK version used by AD utilities on CP node 1.5.0_08


Code Inventory of C-Oracle Home (/data/R12VIS/apps/tech_st/10.1.2)
One-Off Patches Applied on C Oracle Home (/data/R12VIS/apps/tech_st/10.1.2)
5586892 Fri Dec 29 21:22:33 EST 2006
5637184 Wed Jan 16 08:59:00 EST 2008
5985840 Wed Jan 16 09:06:38 EST 2008
5841985 Wed Jan 16 08:57:52 EST 2008
5985861 Wed Jan 16 08:58:20 EST 2008
6083646 Wed Jan 16 09:01:17 EST 2008
5527100 Wed Jan 16 09:05:46 EST 2008
3559326 Fri Dec 29 21:10:37 EST 2006
5893392 Wed Jan 16 09:04:19 EST 2008
5466491 Wed Jan 16 09:04:58 EST 2008
6363690 Wed Jan 16 08:59:27 EST 2008
5650051 Wed Jan 16 09:03:28 EST 2008
5394728 Fri Dec 29 21:19:54 EST 2006
5456500 Wed Jan 16 09:07:06 EST 2008
6002686 Wed Jan 16 09:01:48 EST 2008
5753922 Wed Jan 16 09:02:39 EST 2008


Code Inventory of Java Oracle Home (/data/R12VIS/apps/tech_st/10.1.3):
One-Off Patches Applied on Java Oracle Home (/data/R12VIS/apps/tech_st/10.1.3)
4880540 Fri Dec 29 21:33:48 EST 2006
5586892 Fri Dec 29 21:48:37 EST 2006
5389204 Fri Dec 29 21:34:11 EST 2006
5389100 Fri Dec 29 21:36:26 EST 2006
5017026 Fri Dec 29 21:32:36 EST 2006
4912149 Fri Dec 29 21:35:59 EST 2006
4992357 Fri Dec 29 21:45:29 EST 2006
6083647 Wed Jan 16 09:19:02 EST 2008
5198652 Fri Dec 29 21:35:13 EST 2006
4952820 Fri Dec 29 21:41:15 EST 2006
5336264 Fri Dec 29 21:34:34 EST 2006
4997187 Fri Dec 29 21:33:11 EST 2006
5526722 Fri Dec 29 21:45:08 EST 2006
4930447 Fri Dec 29 21:31:14 EST 2006
5527265 Fri Dec 29 21:44:49 EST 2006
5348943 Fri Dec 29 21:34:52 EST 2006
5035740 Fri Dec 29 21:35:33 EST 2006
5475997 Fri Dec 29 21:40:47 EST 2006
5513480 Fri Dec 29 21:45:52 EST 2006
5576216 Fri Dec 29 21:46:24 EST 2006
5220710 Fri Dec 29 21:44:07 EST 2006
5132392 Fri Dec 29 21:40:21 EST 2006
5499395 Fri Dec 29 21:46:47 EST 2006

Monitoring DML ( Insert/Update/Delete) Operations on a Oracle Table

When you are assigned to a task where you have to monitor a particular bulk DML Transaction to see how many rows were updated/deleted/inserted on a particular table, Follow the steps below for successfully completing this task.
( The database version used is 10.2.0.1)

1. Select the count of present DML operations values from DBA_TAB_MODIFICATIONS


SQL> select * from dba_tab_modifications where table_owner='XXX' and table_name='YYYY';

For Example, you are asked to monitor a bulk insert program to find out at a particular time how many rows were inserted.
In this example we used XXDATA table

Take the count of the present values
SQL> select * from dba_tab_modifications where table_name='XXDATA';

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME SUBPARTITION_NAME INSERTS
------------------------------ ------------------------------ ----------
UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
---------- ---------- --------- --- -------------
DEMO XXDATA
272645
0 0 10-JUL-08 NO 0

So now the initial inserts value is 272645

2. Now start your bulk insert

3. Now monitor the Inserts using the following process

Use the dbms.stats_flush_database_monitoring_info to flush the statistics from memory into DBA_TAB_MODIFICATIONS table as shown below

SQL> exec dbms_stats.flush_database_monitoring_info;

PL/SQL procedure successfully completed.

Now take the count of the number of inserts by querying the dbs_tab_modifications

SQL> select * from dba_tab_modifications where table_name='XXDATA';

TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
PARTITION_NAME SUBPARTITION_NAME INSERTS
------------------------------ ------------------------------ ----------
UPDATES DELETES TIMESTAMP TRU DROP_SEGMENTS
---------- ---------- --------- --- -------------
DEMO XXDATA
1059999
0 0 10-JUL-08 NO 0

So the Total inserts 787354 (1059999-272645)

Table Rename - What Happens to the Indexes,constraints and select queries running against the Table

I was just doing some R&D to find out whatz happenning when we are renaming a table name from X to Y.
The following are my observations
1. I got a table called numbers_1 which got 1900002 rows as shown below
SQL> show user;
USER is "DEMO"
SQL>select count(*) from numbers_1;
COUNT(*)
----------
1900002
2. Started a select query from numbers_1 as shown below
SQL>select * from numbers_1;
THis query is running...
I logged-in in another window as demo user Renamed the numbers_1 to numbers.
The select query is still running.
This is captured in the following video.









3. The Constraints, Indexes built on the original table are also reflected with the new table name.
This is as shown below Before and after Rename of EMP table:
SQL> select constraint_name,table_name from user_constraints;
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
FK_DEPTNO EMP
PK_DEPT DEPT
PK_EMP EMP
SQL> select index_name,table_name from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
PK_DEPT DEPT
IDX_NUM1 NUMBERS_1
PK_EMP EMP
SQL> alter table emp rename to NEW_EMP;
Table altered.
SQL>select constraint_name,table_name from user_constraints;
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
FK_DEPTNO NEW_EMP
PK_DEPT DEPT
PK_EMP NEW_EMP
SQL> select index_name,table_name from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------
PK_DEPT DEPT
IDX_NUM1 NUMBERS_1
PK_EMP NEW_EMP

Note: But the PL/SQL packages which has the references to the old table name becomes invalid after the Table rename.

Monitoring the Space Usage in TEMPORARY tablespace

Temporary tablespace is used for all the sorting operations. If the temporary tablespace is not sufficient enough, then you will get the ' unable to extend temp segment by %s bytes' error.
If you want to Monitor the Space Utilization in TEMPORARY tablespace proactively to avoid the space issues then follow the process mentioned below to effectively monitor the TEMPORARY space utilization

1. Find the Temporary tablespace names and the block size using the following SQL

SQL> select tablespace_name,block_size from dba_tablespaces where tablespace_nam
e like '%TEMP%';

TABLESPACE_NAME BLOCK_SIZE
------------------------------ ----------
TEMP 8192

From the above command it is clear that we have only one TEMPORARY tablespace 'TEMP' withe the block size as 8192

2. Find out what is the total size of the TEMPORARY segment and how much space being utilized and how much free space is left for the future sort operations using the following SQL ( calculated with the 8192 block size)

SQL> select tablespace_name,total_blocks*8192/1024/1024 as total_MB,
2 used_blocks*8192/1024/1024 used_MB,
3 free_blocks*8192/1024/1024 free_MB from v$sort_segment;

TABLESPACE_NAME TOTAL_MB USED_MB FREE_MB
------------------------------- ---------- ---------- ----------
TEMP 44 0 44

From the above output
Total size is 44 MB
Used is 0 MB
Free space 44 MB

Make sure that there is always a free space available to accommodate the future sort operations.
3. If you want to know who is using the temporary tablespace at the moment , then run the following SQL to know who is using the temporary tablespace.

SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,
a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

SQL>

TABLESPACE SEGFILE# SEGBLK# BLOCKS SID
------------------------------- ---------- ---------- ---------- ----------
SERIAL# USERNAME OSUSER
---------- ------------------------------ ------------------------------
STATUS
--------
TEMP 201 5513 5632 144
170 DEMO US\smarampelly
ACTIVE

Resizing a temporary tablespace TEMP

If you want to increase/resize the temporary tablespace TEMP which is having a temporary datafile temp01.dbf, then follow the steps below

1. Find out the tempfile name and size

SQL> select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
-------------------------------------------------------------------------------

1 550491 27-JUN-08 3 1 ONLINE READ WRITE
20971520 2560 20971520 8192
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DELDB\TEMP01.DBF


The following info is available from the above command

Tempfile name is C:\ORACLE\PRODUCT\10.2.0\ORADATA\DELDB\TEMP01.DBF
size --> 2560 * 8192 = 20MB
2. So if you want to increase the tempfile size to 50MB use the following SQL

SQL> alter database tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\DELDB\TEMP01.DBF'
resize 50m;

Database altered.

SQL> select * from v$tempfile;

FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------

1 550491 27-JUN-08 3 1 ONLINE READ WRITE
52428800 6400 20971520 8192
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DELDB\TEMP01.DBF

Monitoring the Oracle Database - "Processes" Initialization Parameter

If you want to find out whether your Processes initialization parameter value is sufficient enough so that there won't be any issues for the users to connect to the database

Follow the Process below to monitor the processes initialization parameter

1. Connect to the database as sys user

SQL> conn / as sysdba

SQL> select * from v$resource_limit where resource_name in ('processes','sessions');

RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
INITIAL_ALLOCATION LIMIT_VALUE
------------------------------ ------------------------------
processes 207 210
350 350

sessions 213 218
700 700



In The above output the Max_utilization is 210 and limit_value is 350. So the Maximum utilization is 210 < 350. So the Parameter is good Enough.

Consider increasing the Processes parameter if max_utilization reaches 350 value.
Note: Sessions parameter is automatically adjected as per the Processes setting.

You can also check whether there were any connection refusesals when the processes parameter has reached its limit using the following

lsnrctl services

The output of the above command is

Instance "R12VIS", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Instance "R12VIS", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2187 refused:0 state:ready

The state should be ready aad refused should be 0. If there is any value against refused, and state is blocked then the listener is not accepting any connections because database has reached its processes max_limit.