Tuesday, February 17, 2015

DB 12c: ORA-01950: no privileges on tablespace

Recently I faced following error message when I tried to insert data into a table in 12c.

Error:
SQLException occurred
SQLErrorCode=1950
SQLErrorMesg=ORA-01950: no privileges on tablespace 'XXX_BINSTORE'

Cause:
This is an expected behavior in Oracle Database 12c, even though user granted with RESOURCE role.

In Oracle Database 12c, RESOURCE role no longer grants UNLIMITED TABLESPACE system privilege by default.

Fix:
Grant UNLIMITED TABLESPACE system privilege to the user manually.

Else grant Unlimited quota on specific tablespace

Ex:

GRANT UNLIMITED TABLESPACE TO "lkakarla" ;
(or)
ALTER USER "lkakarla" QUOTA UNLIMITED ON XXX_BINSTORE;

Ref:
http://docs.oracle.com/database/121/DBSEG/release_changes.htm#DBSEG421
http://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG4414


Queries to check Table space quota & Privileges granted to user:

SELECT * FROM DBA_TS_QUOTAS;

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE= 'lkakarla';

Monday, February 16, 2015

OHS 11g: Customize Default Home Page - Shorten URL

If you have Oracle HTTP Server (OHS) as the front-end web server, the default OHS server URL will redirect you to Oracle Fusion Middleware welcome page.


There are many ways to customize default welcome page in OHS 11g. This will also help you to shorten your application URL when giving it to end user.

Method1: Modify httpd.conf default index page entry.

In 11g, the name of the default index page of HTTP Server is "welcome-index.html".

Modify the below lines in the httpd.conf file

 DirectoryIndex welcome-index.html

 to

 DirectoryIndex custom_page.html

where "custom_page.html"  is the name of the static page which you would liked to be displayed instead of the default welcome-index.html page. This custom_page.html should be placed inside the htdocs directory inside the apache folder.

Bounce OHS server to reflect changes.

Method2: Modify "welcome-index.html" existing inside htdocs. No need to Bounce OHS.

1. Go to htdocs folder in your web server environment
2. Backup current "welcome-index.html" file and create new file called "welcome-index.html" with the HTML content you want to show.
3. Now enter the OHS server URL and it will redirect/show the content specified in new "welcome-index.html"

Sample HTML Code:

<html>
<head>
<title>Sample Application</title>
<meta http-equiv="REFRESH" content="0;url=https://www.lkakarla.com/SampleApp/faces/Employees.jspx">
</head>
</html>

Saturday, February 14, 2015

WebLogic: Connection has been administratively disabled

Recently one of ADF applications deployed to Weblogic Managed server throwing following error messages in logs when tried to perform any database operations. Provided root cause for these errors and solution to handle this.

Error Message 1:
oracle.jbo.JboException: JBO-29114 ADFContext is not setup to process messages for this exception. Use the exception stack trace and error code to investigate the root cause of this exception. Root cause error code is JBO-29000. Error message parameters are {0=java.sql.SQLRecoverableException, 1=IO Error: Connection reset}
        Truncated. see log file for complete stacktrace

Caused By: java.sql.SQLRecoverableException: IO Error: Connection reset
        at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:962)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1283)

Caused By: java.net.SocketException: Connection reset
        at java.net.SocketInputStream.read(SocketInputStream.java:168)
        at oracle.net.nt.MetricsEnabledInputStream.read(TcpNTAdapter.java:730)

Error Message 2:
oracle.jbo.JboException: JBO-29114 ADFContext is not setup to process messages for this exception. Use the exception stack trace and error code to investigate the root cause of this exception. Root cause error code is JBO-29000. Error message parameters are {0=java.sql.SQLRecoverableException, 1=Connection has been administratively disabled. Try later.}
        Truncated. see log file for complete stacktrace

Caused By: java.sql.SQLRecoverableException: Connection has been administratively disabled. Try later.
        at weblogic.jdbc.common.internal.ConnectionEnv.checkIfEnabled(ConnectionEnv.java:1025)
        at weblogic.jdbc.wrapper.PoolConnection.checkConnection(PoolConnection.java:63)
        at weblogic.jdbc.wrapper.Connection.preInvocationHandler(Connection.java:100)

Cause:
There could be several reasons. Review Admin/Managed server logs to find the exact error message.

Most of the times, it could be due to unavailability of Database. If DBMS becomes and remains unavailable, the data source will test and try to replace dead connections while trying to satisfy connection requests. This behavior is beneficial because it enables the data source to react immediately when the database becomes available. However, testing a dead database connection can take as long as the network timeout, and can cause a long delay for clients.

To minimize this delay, the WLS data sources include logic that disables the data source after 2 consecutive failures to replace a dead connection. When an application requests a connection from a disabled data source, WebLogic Server throws a PoolDisabledSQLException immediately to notify the client that a connection is not available.

Fix:
Immediate fix:
You need to restart your WebLogic Admin or Managed Server(based on data source targets) to recreate new JDBC connections. All the current transactions are lost, as the database was shutdown.

Permanent fix:
1. High availability of your RDBMS is required to minimize this issue.

2. The WebLogic Server team recommends making sure that "Test on Connection Reserve" is selected in the datasource --> connection pool configuration (under the advanced section). This might have a small performance impact (as each datasource connection request will ping the database to check whether it is active or not before giving the connection to application). However, enabling this would ensure that only a valid connection will be given to the application, and there would not be any stale connection in the environment (another possible cause for - administratively disabled).

The following connection pool configs can be found in Admin console > Services > Data Sources > click on the datasource > Configuration tab > Advanced.
Below shows how the configurations would look appear the domain's datasource file, e.g.: <path_to_domain>/config/jdbc/<datasource_name>-xxxx-jdbc.xml.

The fix has been confirmed working with the following settings:

<test-frequency-seconds>1</test-frequency-seconds>
<test-connections-on-reserve>true</test-connections-on-reserve>
<test-table-name>SQL SELECT 1 FROM DUAL</test-table-name>
<seconds-to-trust-an-idle-pool-connection>1</seconds-to-trust-an-idle-pool-connection>