Segmentation fault (11) with mod_weblogic and ssl weblogic

Recently I’ve stumbled upon a strange one. We have setup an OHS in front of OBIEE, on different machine to enable caching, SSL and fast failover (switching between two synced OBIEE servers). We use /em and /console links to redirect to the weblogic Enterprise Manager and Administration Console. Until now we used the standard non SSL ports for this. We got a notification that all traffic from our server should be encrypted using SSL. As OHS and OBIEE are on different servers, we needed to configure SSL on the weblogic ports 7001 and 9704.

Enabling SSL on Weblogic was easy, as I will explain in a later post. Now I needed to configure mod_weblogic to communicate to weblogic on the ssl port. As our OHS (Oracle HTTP Server) 11.1.1.6.0 installation is standard, I tried to enable the mod_weblogic to connect to weblogic on a ssl port, using the following configuration:

<Location /console>
  <IfModule mod_weblogic.c>
    SetHandler weblogic-handler
    WebLogicHost hostname
    WebLogicPort 7002
    SecureProxy ON
    WlSSLWallet "path_to_oracle_wallet"
  </IfModule>
</Location>

I kept getting the following error every time a request was made:

[Tue Dec 03 13:54:47 2013] [notice] child pid 21617 exit signal Segmentation fault (11)

This was very strange because everything was working fine, I was able to login to the console. After a little digging it seems that the mod_wl_ohs.so plugin is to blame. After I downloaded the plugin from the Oracle Technology Network and modified the mod_wl_ohs.conf to include the new plugin, the error in log file was gone, and it seemed that everything was working faster.

Will have to see if this interferes with normal OBIEE operation…

Partitioning S_NQ_ACCT and S_NQ_DB_ACCT

With OBIEE you can Enable Usage Tracking to see a lot of interesting facts about who accessed what dashboard, how long a query took, error messages and a lot of other facts. All this is stored in S_NQ_ACCT and from OBIEE 11.7.1.0 in S_NQ_DB_ACCT. The last one is used when your database is stored in a exalytic or exalogic box for better performance .

If you have a lot of users, these two tables can get kind of big (because of a column in each table that is a CLOB type). For us with almost somewhere around 4000-5000 users a month, in 3 months we got the table usage to almost 40G. For some this might not be a problem, but if you multiply this by 4 you get 160G a year.

If you don’t care about the data you can just disable the FK and issue a truncate command on the columns. We wanted to keep the last month worth of data and delete the rest. Because there is a CLOB column, a simple delete will not suffice to free the disk space. That’s why we choose to partition the table by range, so the data corresponding to one day will be in one partition. That way we will only drop the days we want and easily free the space. The to script I made that will partition the tables and also keeping the data are below. Keep in mind that this scripts copy the data from the old table to the new one, so you will need to have enough space in the tablespace and disk for this.

-- Partition S_NQ_ACCT
-- Create partitioned table S_NQ_ACCT_TMP
CREATE TABLE "S_NQ_ACCT_TMP"
(
"USER_NAME" VARCHAR2(128 BYTE),
"REPOSITORY_NAME" VARCHAR2(128 BYTE),
"SUBJECT_AREA_NAME" VARCHAR2(128 BYTE),
"NODE_ID" VARCHAR2(50 BYTE),
"START_TS" DATE,
"START_DT" DATE,
"START_HOUR_MIN" CHAR(5 BYTE),
"END_TS" DATE,
"END_DT" DATE,
"END_HOUR_MIN" CHAR(5 BYTE),
"QUERY_TEXT" VARCHAR2(1024 BYTE),
"QUERY_BLOB" CLOB,
"QUERY_KEY" VARCHAR2(128 BYTE),
"SUCCESS_FLG" NUMBER(10,0),
"ROW_COUNT" NUMBER(20,0),
"TOTAL_TIME_SEC" NUMBER(10,0),
"COMPILE_TIME_SEC" NUMBER(10,0),
"NUM_DB_QUERY" NUMBER(10,0),
"CUM_DB_TIME_SEC" NUMBER(10,0),
"CUM_NUM_DB_ROW" NUMBER(20,0),
"CACHE_IND_FLG" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE,
"QUERY_SRC_CD" VARCHAR2(30 BYTE) DEFAULT '',
"SAW_SRC_PATH" VARCHAR2(250 BYTE) DEFAULT '',
"SAW_DASHBOARD" VARCHAR2(150 BYTE) DEFAULT '',
"SAW_DASHBOARD_PG" VARCHAR2(150 BYTE) DEFAULT '',
"PRESENTATION_NAME" VARCHAR2(128 BYTE) DEFAULT '',
"ERROR_TEXT" VARCHAR2(250 BYTE) DEFAULT '',
"IMPERSONATOR_USER_NAME" VARCHAR2(128 BYTE) DEFAULT '',
"NUM_CACHE_INSERTED" NUMBER(10,0) DEFAULT NULL,
"NUM_CACHE_HITS" NUMBER(10,0) DEFAULT NULL,
"ID" VARCHAR2(50 BYTE)
)
PARTITION BY RANGE
(
START_DT
)
INTERVAL
(
NUMTODSINTERVAL(1,'DAY')
)
(
PARTITION part1 VALUES LESS THAN (TO_DATE('01-SEP-2013','DD-MON-YYYY'))
) ;
 
-- Rename S_NQ_ACCT to S_NQ_ACCT2;
ALTER TABLE S_NQ_ACCT RENAME TO S_NQ_ACCT2;
 
-- insert into S_NQ_ACCT_TMP from S_NQ_ACCT2
INSERT INTO S_NQ_ACCT_TMP
SELECT * FROM S_NQ_ACCT2;
 
-- Drop S_NQ_ACCT2 + FK constraint;
DROP TABLE S_NQ_ACCT2 cascade constraints;
 
-- Rename S_NQ_ACCT_TMP to S_NQ_ACCT
ALTER TABLE S_NQ_ACCT_TMP RENAME TO S_NQ_ACCT;
 
-- Recreate FK constraintf
ALTER TABLE "S_NQ_ACCT" add CONSTRAINT "S_NQ_ACCT_PK" PRIMARY KEY ("ID");
ALTER TABLE "S_NQ_DB_ACCT" add constraint "FK_S_NQ_DB_ACCT" FOREIGN KEY ("LOGICAL_QUERY_ID") REFERENCES "S_NQ_ACCT" ("ID");
 
-- create index
CREATE INDEX "S_NQ_ACCT_M1" ON "S_NQ_ACCT"
(
"START_DT",
"START_HOUR_MIN",
"USER_NAME"
);
CREATE INDEX "S_NQ_ACCT_M2" ON "S_NQ_ACCT"
(
"START_HOUR_MIN",
"USER_NAME"
);
CREATE INDEX "S_NQ_ACCT_M3" ON "S_NQ_ACCT"
(
"USER_NAME"
);

And here is the one for S_NQ_DB_ACCT:

CREATE TABLE S_NQ_DB_ACCT_TMP AS
SELECT * FROM S_NQ_DB_ACCT;
DROP TABLE S_NQ_DB_ACCT;
CREATE TABLE "S_NQ_DB_ACCT"
(
"ID" NUMBER(10,0),
"LOGICAL_QUERY_ID" VARCHAR2(50 BYTE),
"QUERY_TEXT" VARCHAR2(1024 BYTE),
"QUERY_BLOB" CLOB,
"TIME_SEC" NUMBER(10,0),
"ROW_COUNT" NUMBER(20,0),
"START_TS" DATE,
"START_DT" DATE,
"START_HOUR_MIN" CHAR(5 BYTE),
"END_TS" DATE,
"END_DT" DATE,
"END_HOUR_MIN" CHAR(5 BYTE),
CONSTRAINT "FK_S_NQ_DB_ACCT" FOREIGN KEY ("LOGICAL_QUERY_ID") REFERENCES "S_NQ_ACCT" ("ID") ENABLE
)
PARTITION BY RANGE
(
START_DT
)
INTERVAL
(
NUMTODSINTERVAL(1,'DAY')
)
(
PARTITION part1 VALUES LESS THAN (TO_DATE('01-SEP-2013','DD-MON-YYYY'))
) ;
CREATE INDEX "S_NQ_DB_ACCT_I1" ON "S_NQ_DB_ACCT"
(
"LOGICAL_QUERY_ID"
);
INSERT INTO S_NQ_DB_ACCT
SELECT * FROM S_NQ_DB_ACCT_TMP;
DROP TABLE S_NQ_DB_ACCT_TMP;

In a later post I will put the the scripts to delete partitions older than a number of days.

Moving/renaming datafile in Oracle database

In Oracle Database 12c moving/renaming a datafile can be done online using the following command:

ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb1/system01.dbf' TO '/u02/oradata/pdb1/system01.dbf';

In older version of Oracle Database this can be done by placing the tablespace offline if the datafile is not a system datafile. Here is the sequence of commands you need to issue, tested on Oracle Database 11gR2:

select * from dba_data_files;
-- get the tablespace name and file name that you want to move
alter tablespace tbs offline;
-- move the datafile in the OS
-- mv /u01/app/oracle/oradata/orcl/test.dbf /u02/oradata/orcl/test.dbf
alter tablespace tbs rename datafile '/u01/app/oracle/oradata/orcl/test.dbf' to '/u02/oradata/orcl/test.dbf';
alter tablespace tbs online;

And that’s it. There is no need to shutdown the whole database if you need to move/rename a non system datafile.

Note: keep in mind that if you move the datafile from one partition to another, while the database is is running, the space used by the old datafile will not be released until the database is restarted because the file will appear as opened file by lsof command.

Automatically start pluggable databases on container start

With Oracle Database 12c the new feature regarding pluggable databases was introduced. But when you start the database the usual way (sqlplus / as sysdba and then startup) this only starts the container database. You will have to manually start the pluggable databases using:

alter pluggable database [PDB_NAME] open;

If you want all or a specific pluggable database to be started when the container database starts, you will have to create a trigger on the container database like the one below:

create or replace trigger SYS.AFTER_STARTUP after startup on database 
begin 
  execute immediate 'alter pluggable database all open'; 
end; 
/

Configure RestFull Services on Apex Listener

I had to configure Apex Listener to work with RestFull Services. After a lot of hours of debugging I managed to make my Apex Listener on Weblogic to respond to the services.

A think that helped me a lot in all this debugging was the Apex Standalone server (if your environement is not in production or dev you can modify that as well) started in the debug mode. Basically you have to change the defaults.xml file in the Apex Listnere configdir and enable the on screen display:

<entry key="debug.printDebugToScreen">true</entry>

The steps to make it all work are the following:

1. Change directory to your apex installation and connect to the database and run apex_rest_config.sql and set the password for the APEX_LISTENER and APEX_REST_PUBLIC_USER users.
Note: If you do this step after step 2, be sure to unlock the two users because they will get locked when Apex Listener will try to connect with the wrong password.

2. Deploy Apex to Weblogic according to the official documentation here: http://docs.oracle.com/cd/E37099_01/doc/doc.20/e25066/install.htm#autoId17
Note: Be sure to enter the correct users and passwords.

3. In our case, there were a lot of Workspaces created before installing and enabling RestFull Services. I think that for all the Workspaces that are created before enabling RestFull Services or created on the same Apex Instance but from another deployment that does not have RestFull Services (like Apache) the url-mappings are not correct. If you try to test the service on a old Workspace you will get the following error:

Request Path passes syntax validation
Mapping request to database pool: PoolMap [_poolName=apex, _regex=null, _workspaceIdentifier=null, _failed=false, _lastUpdate=-1, _template=null, _type=REGEX]
Applied database connection info
Attempting to process with PL/SQL Gateway
Not processed as PL/SQL Gateway request
Attempting to process as a RESTful Service
Determining if request can be dispatched as a Tenanted RESTful Service
Request path has one path segment, continuing processing
No Tenant Principal established yet, continuing processing
APEX_LISTENER pool exists, continuing processing
No matching tenant found for: travel, cannot dispatch
No candidate found for: GET travel/hr/empinfo/ in context: http://localhost:8080/apex/

In the above example travel is the Workspace.

For the restfull services to work on old workspaces you will have to create the mappings manually according to the documetnation: http://docs.oracle.com/cd/E37099_01/doc/doc.20/e25066/config.htm#autoId3. In our example you should run:

java -jar apex.war map-url --type base-path --workspace-id travel /travel apex

And that should fix the issue.

Adding new listeners for Oracle Database

I got a request today, to make available the same Oracle Database instance on an another port besides the default one. One simple but not so elegant way to make this happen (if you have root access) is to redirect the traffic from one port to the one you already have setup using this command:

iptables -t nat -I PREROUTING --src 0/0 --dst 10.0.1.15 -p tcp --dport 1522 -j REDIRECT --to-ports 1521

The other way, the more elegant way, is to create another listener on the requested port and tell the database to register itself to that listener. Here are the steps you need to follow, after you have run “. oraenv” to setup your environment:

1. Create the new listener on the desired port:

srvctl add listener -l LISTENER2 -p TCP:1522 -o /u01/app/oracle/product/11.2.0.3/dbhome_1

-l = the listener name, LISTENER is most probably already taken
-p = the protocol and port to use
-o = Oracle Home

2. Start the listener:

srvctl start listener LISTENER2

3. Now you need to modify the local_listener parameter in the database. If you need to set multiple ports, then you will need to use the tnsname for that. If you try to input the string directly in the database you will receive an ORA-00972: identifier is too long. So add the following entry in the $ORACLE_HOME/network/admin/tnsnames.ora file:

ORCL_1521_1522 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.15)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.15)(PORT = 1522))
    )
  )

4. Configure the local_listener in the database:

[oracle@srv ~]$ sqlplus / as sysdba
SQL> ALTER SYSTEM SET local_listener = 'ORCL_1521_1522' SCOPE=MEMORY SID='ORCL11';

System altered.

5. Register the entries with the listener:

SQL> alter system register;

Now you should see when running ‘lsnrctl status LISTENER2’ you should see the new registered database in the listener.

As far as I remember the ‘lsnrct start’ command will only start the default listener, so don’t forget to start the second listener as well. If you have configuret the automatic db start on boot, then you will have to modify the startup scripts (/etc/init.d/dbora file fi you followed the Official Documentation) and add at startup and shutdown the appropriate commands.

< Line 28, Column 49 >: XML-20190: (Fatal Error) Whitespace required.

While configuring Active Directory in weblogic and obtaining group membership from Database tables, after numerous configurations, deletes and restarts, when I tried configuring the virtual adapter using libovdadapterconfig.sh I ran in the following error:

Exception in thread "main" org.xml.sax.SAXParseException: <Line 28, Column 49>: XML-20190: (Fatal Error) Whitespace required.

After a lot of digging I’ve noticed that the adapterTemplate file did not have a space between the xml variables. After adding the space everything went fine.

Hope this helps you not to loose too much time on a simple mistake.

Adding partitioning option to a database after software installation

I’ve struggled with this while installing Oracle Enterprise Manager Cloud Control 12c Release 2 (12.1.0.2). The documentation does not state that the database need to be installed with the partitioning option enabled. So because I’m a resource freak and I want to install only what is needed and nothing else, I did not enabled that option when I did the software install.

Based on the Oracle Database 11gR2 documentation this is easily done with the chopt utility that is provided in the ORACLE_HOME/bin directory when the database is closed.

[oracle@test bin] sqlplus / as sysdba
shutdown immediate;
exit
[oracle@test bin] $ORACLE_HOME/bin/chopt enable partitioning
Writing to /u01/app/oracle/product/11.2.0/dbhome_1/install/enable_partitioning.log...
/usr/bin/make -f /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/ins_rdbms.mk part_on ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
/usr/bin/make -f /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@test bin]

PlsqlDatabaseConnectString takes 1-2 arguments, Connect String to Database (SID, Service Name, TNS)

If you see the error bellow it means that you haven’t configured the mod_plsql PlsqlDatabaseConnectString parameter correctly in dads.conf. There are multiple ways to configure it:

1. Connection string:

PlsqlDatabaseConnectString server_hostname:1521:DB_SID

2. TNSName entry with tnsnames.ora

PlsqlDatabaseConnectString TNS_ENTRY

For this to work you also need to have a tnsnames.ora file in OHS_HOME/network/admin/tnsnames.ora (in my case /u01/app/oracle/product/FMW/Oracle_WT/network/admin/tnsnames.ora) with the corresponding tns entry.

3. TNSNameFormat (Notice there are no spaces)

PlsqlDatabaseConnectString     (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DB_SERVICE_NAME)))

With this you can put multiple servers, create load balance, etc. as you would do with a tns entry in tnsnames.ora file.

Clearing the disk cache

To clear the disk cache on a linux system you have to issue the following comnad:

sync ; sudo sh -c 'echo 3 > /proc/sys/vm/drop_caches'

If you are already root, then issue it without the sudo command:

sync ; echo 3 > /proc/sys/vm/drop_caches

The number 3 can be changed with  one from the following table:

1 free pagecache
2 free dentries and inodes
3 free pagecache, dentries and inodes