Tuesday, February 19, 2013

Oracle: How to Configure User Accounts to Never Expire?

I have written an article on how to unlock a user account when it expires:
But, every 180 days, you need to repeat the same action.  If you are NOT concerned with strict security rules for your database, you can take the following approach to set user accounts to never expire.

What Profile Used by a User Account?


A profile[3] is a database object - a named set of resource limits. Using profile, you can enforce a limit on resource utilization using resource limit parameters Also you can maintain database security by using password management feature.  Here is the SQL command you can use to query which profile is used by each user account:

SQL>  SELECT USERNAME, PROFILE FROM DBA_USERS;

USERNAME                       PROFILE
------------------------------ ------------------------------
OAM_OAM                        DEFAULT
OAM_IAU_APPEND                 DEFAULT
OAM_IAU_VIEWER                 DEFAULT
OAM_IAU                        DEFAULT
OIM_SOAINFRA                   DEFAULT
OIM_ORASDPM                    DEFAULT
OIM_MDS                        DEFAULT
OIM_OIM                        DEFAULT

As shown above, both OAM and OIM user accounts use "DEFAULT" profile.

What Limits Set with a Profile?


We are only interested in "DEFAULT" profile and resource of PASSWORD type.  To query all sorts of limits imposed with "DEFAULT" profile, you do the following query:

SQL> select resource_name, limit from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
FAILED_LOGIN_ATTEMPTS            10
PASSWORD_LIFE_TIME               180
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               1
PASSWORD_GRACE_TIME              7

As shown above, all our OAM and OIM user accounts will expire in 180 days.  However, we would like to set it to never expire.  

How to Set User Password to Never Expire?


Here is the alter statement that you can use:

SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.

The above command has set password life time associated with "DEFAULT" profile to be unlimited.  You can verify the setting by:

SQL> select resource_name, limit from dba_profiles where profile='DEFAULT' and resource_type='PASSWORD';

RESOURCE_NAME                    LIMIT
-------------------------------- ----------------------------------------
FAILED_LOGIN_ATTEMPTS            10
PASSWORD_LIFE_TIME               UNLIMITED
PASSWORD_REUSE_TIME              UNLIMITED
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               1
PASSWORD_GRACE_TIME              7

7 rows selected.

References

Sunday, February 17, 2013

"The Listener Supports No Services" and Using "Alter System Register" Command

We have started oracle database, which is followed by starting the listener.  Then we checked listener's status.  As you can see below, at beginning, it reports that "The listener supports no services."  However, after exactly 60 seconds, it reports that two services were supported (i.e., ps6stg7 and ps6stg7XDB).

In this article, we explain what happened and will show you how to force service be registered with the listener.  So, your application can find the services sooner.

-bash-3.2$ $ORACLE_HOME/bin/lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-FEB-2013 21:07:57

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                17-FEB-2013 21:06:58
Uptime                    0 days 0 hr. 0 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /scratch/aime1/app/oracle11.2.0.3.0/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /scratch/aime1/app/oracle11.2.0.3.0/diag/tnslsnr/myserver/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myserver.us.oracle.com)(PORT=1521)))
The listener supports no services
The command completed successfully
-bash-3.2$ $ORACLE_HOME/bin/lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-FEB-2013 21:07:59

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                17-FEB-2013 21:06:58
Uptime                    0 days 0 hr. 1 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /scratch/aime1/app/oracle11.2.0.3.0/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /scratch/aime1/app/oracle11.2.0.3.0/diag/tnslsnr/myserver/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myserver.us.oracle.com)(PORT=1521)))
Services Summary...
Service "ps6stg7" has 1 instance(s).
  Instance "ps6stg7", status READY, has 1 handler(s) for this service...
Service "ps6stg7XDB" has 1 instance(s).
  Instance "ps6stg7", status READY, has 1 handler(s) for this service...
The command completed successfully

PMON


In our case, we have executed the following shell commands in a script named startDB.sh:

#!/bin/bash
. /scratch/aime1/scripts/db/setEnvPs6stg7.sh

echo "### Restart Oracle databse instance $ORACLE_SID ###"
$ORACLE_HOME/bin/sqlplus / as sysdba << EOF
startup;
EOF
echo "Done"

echo "### Starting tns listener ###"
$ORACLE_HOME/bin/lsnrctl start
sleep 20;
$ORACLE_HOME/bin/lsnrctl stat

However, when the last listener status command was executed, it showed:
"The listener supports no services."

Then I did some research and found out that what happened behind the scene is that:  PMON process wakes up at every 60 seconds and provide (or register) information to the listener.  So, not until 60 seconds later, did listener's status show it supported two services.

Alter System Register


Without waiting for 60 seconds, you can use 
  • alter system register command
This command forces the registration of database information to the listener.  So, I decided to give it a try and I added the new sql command in my script as follows:

$ORACLE_HOME/bin/sqlplus / as sysdba << EOF
startup;
alter system register;
EOF

However, this didn't work.  After some detective works, finally I have figured out that I need to start the listener first before starting the database.  In other words, when the command tried to manually register server information with the listener.  The listener needs to be up and running.  So, after I have moved "lsnrctl start" command before the database start command, it then worked.  Also, you don't need to sleep for 20 seconds before checking the listener's status.

References

Thursday, February 14, 2013

How to Find the List of Bug Fixes between Java SE Releases

If you try to find the list of bug fixes between different Java SE releases, we will show you how in this article.

Bug Fixes


For example, if you want to see the list of bug fixes between Java SE 6u31 and 6u32.  You need to locate this page:
But, how do you find that page.  Here are the instructions:
  1. Go to OTN Download page.
  2. Search for "Java SE" (For example, you will find Java SE (includes JavaFX) | Early Access)
  3. Click Java SE link.  This will bring you to "Java SE Downloads" page
  4. Under "Java Platform, Standard Edition", there is 
    • Java SE 6 Update 39
  5. Click Release Notes under JRE column.  You should see
    • Update Release Notes page
  6. Click Changes in 1.6.0_32.  This should bring you to
    • Java™ SE 6 Update 32 page
  7. Look for
  8. Click Java SE 6u32 Bug Fixes link

What for?


The above instructions should bring you to the Bug Fixes page.  That can help you find:
  • If a specific bug fix has been included in 6u32
  • What to investigate if you see performance regression after upgrading from Java SE 6u31 to 6u32.

Friday, February 8, 2013

JBO-26061: Error while opening JDBC connection

If you see something similar to the following messages in the WebLogic Server console output or log files:

at oracle.jbo.server.DBTransactionImpl.establishNewConnection(DBTransactionImpl.java:1045) 
at oracle.jbo.server.DBTransactionImpl.getInternalConnection(DBTransactionImpl.java:1433) 
at oracle.jbo.server.DBSerializer.setup(DBSerializer.java:147) 
at oracle.jbo.server.DBSerializer.passivateRootAM(DBSerializer.java:287)
at oracle.jbo.server.DBSerializer.passivateRootAM(DBSerializer.java:268) Truncated. see log file for complete stacktrace Caused By: weblogic.jdbc.extensions.PoolLimitSQLException: weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool ApplicationDB to allocate to applications, please increase the size of the pool and retry.. 

You have a need to tune your JDBC Connection Pool. In this article, we show you how to validate the issue and how to fix it.

JDBC Connection Pools


Connections to a database are expensive to create, which it involves creating a process on the database.  A connection pool can be maintained so that the connections can be reused when future requests to the database are required.

In Java programming paradigm, a Java DataBase Connectivity (JDBC) resource (or data source) provides applications with the means of connecting to a database. A JDBC connection pool contains a group of JDBC connections that are created when the connection pool is registered.
  • J2EE
    • All JDBC connections come from the application server’s pool
  • J2SE with JPA
    • JPA providers transparently creates a connection pool for Java SE programs and you can configure the connection pool within the persistence.xml file.

How to Validate the Issue?


As suggested in the message, you should tune JDBC Connection Pool used by JDBC Data Source named "ApplicationDB".  In this article:
it shows you how to validate (or montior)  JDBC Connection Pool at Runtime.  For example, if you find the following statistics:
  • Waiting For Connection Failure Total
has a non-zero entry, you know you need to tune the JDBC Connection Pool with associated data source.

How to Fix the Issue?


You can tune JDBC Connection Pool from the WebLogic Server Administration Console or you can modify JDBC configuration file used by data source "ApplicationDB" directly.  But, before you modify the configuration file, you need to shut down servers first.

JDBC configuration files can be located here:
  • DOMAIN_NAME/config/jdbc

For example, the one for the ApplicationDB data source is named ApplicationDB-8936-jdbc.xml in our application (ATG CRMDemo).

After trial-and-errors, we have found the following settings are good for our application (note that each application has specific needs and depends on the hardware too):

  <jdbc-connection-pool-params>
    <initial-capacity>0</initial-capacity>
    <max-capacity>500</max-capacity>
    <capacity-increment>2</capacity-increment>
    <connection-creation-retry-frequency-seconds>10</connection-creation-retry-frequency-seconds>
    <test-frequency-seconds>300</test-frequency-seconds>
    <test-connections-on-reserve>true</test-connections-on-reserve>
    <test-table-name>SQL SELECT 1 FROM FND_DUAL</test-table-name>
    <statement-cache-size>5</statement-cache-size>
    <statement-cache-type>LRU</statement-cache-type>
    <seconds-to-trust-an-idle-pool-connection>0</seconds-to-trust-an-idle-pool-connection>
  </jdbc-connection-pool-params>

References

Friday, February 1, 2013

Cloning Issue—IPv4 vs. IPv6

With two similarly cloned environments, one env works and another env fails. Both of them use /etc/hosts to redirect:
  • idm-db.us.oracle.com
to its own IP address[1]. However, when one of the WLS managed server tried to connect to its Admin Server using the following URL:
  • t3://[2606:a800:2010:4048:221:28ff:fefb:7756]:17001,
it failed.

In this article, we discuss how to trouble shoot and resolve this issue.

Log Messages[2]


WebLogic Server provides handlers for sending log messages to standard out, the server log file, broadcasting messages to the domain log, remote clients, and a memory buffer for tail viewing log events in the WebLogic Server Administration Console. You can achieve volume control for each type of handler by filtering log messages based on severity level and other criteria. The LogMBean, described in Oracle WebLogic Server MBean Reference[3], defines attributes for setting the severity level and specifying filter criteria for WebLogic Server handlers.

For example,  a Stdout Handler is provided in WLS—it sends stdout of the JVM in which a WebLogic Server instance runs to server terminal console.  WLS also provides a Stdout Filter which can be configured to filter log events being sent to the standard out.  By default, the Stdout Handler has a NOTICE threshold severity level. Therefore, INFO and DEBUG level messages are not sent to standard out.  There is also an attribute named RedirectStdoutToServerLogEnabled in LogMBean.  When enabled, this redirects the stdout of the JVM in which a WebLogic Server instance runs, to the WebLogic logging system.

For our case, the above-mentioned error message was found in the standard out which was redirected to a file when the WebLogic Server instance was started.

So, we need to figure out why the connection failed and why
  • [2606:a800:2010:4048:221:28ff:fefb:7756]
was referenced in the URL.  After some digging, we have found that [2606...] is the inet6 addr as shown below:

# /sbin/ifconfig
eth0      Link encap:Ethernet  HWaddr 00:31:28:FB:88:56
          inet addr:xx.xxx.xx.xxx  Bcast:xx.xxx.xx.255  Mask:255.255.248.0
          inet6 addr: 2606:a800:2010:4048:221:28ff:fefb:7756/64 Scope:Global
          inet6 addr: fe80::521:25ff:fefb:7756/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:251183 errors:0 dropped:0 overruns:0 frame:0
          TX packets:150153 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:61626169 (58.7 MiB)  TX bytes:84744375 (80.8 MiB)
          Memory:df460000-df480000

Cloning Advantage


When you clone a system, you usually have a working system to clone from.  So, if your cloned system failed to start, you can compare the working and non-working systems side-by-side.  For our case, we have found that the system that fails have ipv6 enabled and the one works didn't.

So, the next step for us to take is to disable IPv6 protocol.  First, we have tried it from the JVM level.

Disabling the IPv6 Protocol at JVM Level


Two JVM options are provided in JRockit to enable and disable ipv6 addresses as below:
  • -Djava.net.preferIPv6Addresses=true (or false)
  • -DuseIPv6Address=true (or false)
However, when we tried the above tricks from the command line that started the WLS server instance, it didn't work.  Since we don't own the application (note that we use the application as a benchmark only), we cannot change coding to make it work with IPv6.  So, the next step is for us to disable IPv6 Protocol at kernel level.

Disabling the IPv6 Protocol at Kernel Level


We have followed the instructions described in [4-6] to disable IPv6 protocol at Linux kernel level.  There are two files we (as root) have edited by adding lines as shown below:

# vi /etc/modprobe.conf
alias net-pf-10 off
alias ipv6 off
options ipv6 disable=1


#vi /etc/sysconfig/network
NETWORKING_IPV6=no

After saved and closed files, we rebooted the system. Finally, the above changes help us resolve the issue.  Be warned that your Linux platform (note that our OS is Redhat Linux) may need different configuration from the one described here.

References

  1. Simplify Cloning by Using Hosts File
  2. Configuring WebLogic Logging Services
  3. LogMBean
    • Configures the threshold severity level and filter settings for logging output.
  4. Networking IPv6 User Guide for J2SDK/JRE 1.4
  5. Linux: How To Disable The IPv6 Protocol
  6. RedHat / Centos Disable IPv6 Networking
  7. Migrating Oracle B2B from Test to Production (T2P) (Chap 10 of the Book "Getting Started with Oracle SOA B2B Integration: A Hands-On Tutorial")
    • This section provides a real-world scenario to replicate (clone) the test environment to production for Oracle SOA.
    • Oracle Fusion Middleware provides a series of scripts for this task.
  8. Oracle Products: What Patching, Migration, and Upgrade Mean?
  9. DNS and BIND, 5th edition, by Cricket Liu and Paul Albitz
    • What’s this fe80:: address?
      • These are link-local scoped addresses, derived automatically from the interfaces’ hardware addresses. The link-local scope is significant—you can’t access these addresses from anywhere but the local subnet, so don’t use them in delegation, masters substatements, and the like. 
  10. Oracle Products: What Patching, Migration, and Upgrade Mean? (Xml and More)
    • For your Oracle production systems, follow official recommendations as shown in this article.