Wednesday, December 29, 2010

Oracle CSService Hangs on Single Node with ASM, on Remote Machine

It was a while since I posted anything, don't know why...!

Any way, I faced a problem during the last two days, the network admin decided to move to new domain and one of the technicians decided to move the database at one of the remote location we have to the new domain, every thing was working fine until one of the databases on that machine crashed.

So, when I tried to login as /sysdba it kept saying insufficient privileges, but when logging using sys account with password it worked fine. I tried to startup the database but it couldn't connect with ASM due to insufficient privilege again. I was logging as domain admin to the machine, and tried the same thing using local admin with the same problem, even though, I tried to change the authentication method in the SQLNET.ORA file from NTS to NONE, but I kept receiving the same error.

I even added the the local administrator to dba_ group under windows group, but with no success. When I decided to restart the machine, the remote desktop didn't connect and the machine seemed to be hanged.

I tried to connect to the management console, it connects which mean the server is working, so , I doubted that there is an oracle service causing these problems. At first, I needed to connect to the machine using Remote Desktop, so I connected through, register editor to the remote machine, then I disabled all oracle services (in REGEDIT), and issued a restart command on the machine using shutdown line command (shutdown -r -m \\

I was connected using RDP, then I tried to start oracle services one at time, but the OracleCSService couldn't be started. So, after checking the log file of the cssd service, I noticed that crsctl.exe check boot keeps return an error and it stuck in infinite loop, I  build the OCR file used by this service using 

ORACLE_HOME of 10g\bin\clscfg.exe -local 
-o ORACLE_HOME of 10g

then I changed the logon owner of all oracle service to local system on services properties, and it worked the CSSD is up and running, it seemed that the local OCR file used by cssd is corrupted due to service failure, which prevented oraclecsservice from starting and as a consequence it blocked the RDP connection.

And, When changed the logon owner of all oracler service, the insufficient privilege disappeared also and the database is up and running.

Then I've changed the listener.ora file and tnsnames.ora file with server address than server name and restarted the listener.

Tuesday, June 15, 2010

Executing a Callable Statement using DBTransaction

Rather than creating a new Application Module and then bind it to new DBTransaction, to execute a query or return the result of a procedure "which would create a new connection to database".

You can use the following code snippet to create a callable statement to execute a database procedure and get results out in resultSet:

BindingContext bindingContext = 

ApplicationModuleImpl amimpl=
DBTransaction trans=amimpl.getDBTransaction();
CallableStatement cs = trans.createCallableStatement("{call ? :=
     NAME_OF_PROCEDURE(?,?) }",trans.DEFAULT);
cs.registerOutParameter(1, Types.VARCHAR);       
cs.setString(2, searchstring);       
cs.setString(3, "");

return cs.getString(1);

Wednesday, May 19, 2010

Possible scenarios when receiving optisk error in Oracle Database, or when sessions disconnect abnormally

 A problem occurs at database level after requesting huge data from database. Database sessions disconnect abnormally while there is still packets being received. I was able to detect the problem using network monitoring tools.

One possible issue is related to the timing required to process data on one side and the expire time for inactive socket connection. The firewall could interpret the time spent by one side as inactive connection and after a while to drop the connection. Situation like this could be avoided by using the sqlnet.expire_time <> 0 on server side. By using this parameter there is no forced disconnection, a pulse sent to client understood by firewall as traffic but not visible to the client application.

Another issue that is when running via a firewall related to data contents / type of data or queries. FIXUP or FIXIT or DEEP PACKET INSPECTIONS have caused issues on sqlnet communication. I short those features change the data packet but if does not change the header details, due to this when sqlnet reads the header and the data doesn't match, it will report a packet corruption. This is possible when the firewall have these features turned on.

Using Intrusion Protection System (IPS) also could cause the same problem since IPS will lookup data packets try to modify it, or in some cause will delay packet transferee due to data fragmentation.

For more details reference:
Note 805088.1 ORA-3135 with Recovery Catalog Creation Across the Network.
Note 815165.1 ORA-12569 While Trying To Query SYS.DBA_AUDIT_TRAIL Via Oracle Net.

Thing would help further is tracing client - server connection and checking the listener.log and altert.log file if any errors reported.

Monday, May 10, 2010

Oracle Views with some examples

Oracle Views
Some useful Oracle views

Saturday, April 3, 2010

Converting from Rollback to Undo

This procedure will drop the existing rollback segments and tablespace, and replace them with an auto-managed undo tablespace.

1. Identify the rollback segments and tablespaces to be removed:

select segment_name, tablespace_name, status
from dba_rollback_segs and tablespace name != 'SYSTEM';

2. Produce offline and drop statements for each rollback segment:

select 'alter rollback segment ' || segment_name || ' offline;' from dba_rollback_segs
where tablespace_name = '&TS'
and tablespace name != 'SYSTEM'

select 'drop rollback segment ' || segment_name || ';'
from dba_rollback_segs
where tablespace_name = '&TS'
and tablespace name != 'SYSTEM'

Review the output and then cut and paste it back into sqlplus.

3. Make a note of the old tablespace's size and location, then drop it. 

drop tablespace including contents and datafiles;

4. Create a new 'undo' tablespace in place of the old one.

create undo tablespace undo 
datafile '' size 
extent management local

5. Update the initialisation parameters

If you are using a spfile (and you should be!) run the following commands:
alter system reset rollback_segments scope=spfile sid='*'

alter system set undo_management=auto scope=spfile

alter system set undo_tablespace=undo scope=spfile

If you are still using a pfile (init.ora) then do the following:
Remove the following settings:


Add/alter these two lines:



6. Restart the instance

Note the location of the alert log. If there is a problem during start-up, you will probably receive nothing more than a message saying 'instance aborted'. The alert log will contain a slightly more detailed explanation.
Restart the instance:

shutdown immediate

Rerun the query from step 1. There will be a whole load of rollback segments with names like '_SYSSMU1. This is an indictaion that the instance is now using auto undo. 
Alter undo retentionalter system set undo_retention=500 scope=memory; 

What's in undo 

select tablespace_name, status, count(*) as HOW_MANY
from dba_undo_extents
group by tablespace_name, status

Sunday, March 21, 2010

Inserting Data in hidden detailed table after inserting in Master one

When working with a friend trying to insert data in a detailed table (without dropping the detailed table in a page view associated with its master table), and getting some of the detailed table column values from the same data inserted in the master table, we came across to use the following code:

public String insert_master_detail() {

        BindingContainer bindings = getBindings();
        OperationBinding operationBinding = 
        Object result = operationBinding.execute();
        //Perform a commit on the master table binding on page view
        if (operationBinding.getErrors().isEmpty()) {
        //if transaction succeeded then continue adding data to detail

        String amDeff = "model.AppModule";
        String config = "AppModuleLocal";
        ApplicationModule am =  
               Configuration.createRootApplicationModule(amDeff, config);
        ViewObject vo = am.findViewObject("DetailTabke1");
        //connect to the detail view using the application module 
       BindingContainer bc = 
       //get the current page bindings
        JUCtrlAttrsBinding Code = 
        JUCtrlAttrsBinding Name = 
       //get list of data added to the master table that will be add to 
        the detailed table
        NameValuePairs pairs = new NameValuePairs();
        Number n=new Number(1);
        pairs.setAttribute("No", n);
        pairs.setAttribute("Status", "00020");
        pairs.setAttribute("Typee", "051");
        pairs.setAttribute("StartDate", "1430/01/01");
        pairs.setAttribute("EndDate", "1430/01/01");
        Row ActivityTaskRow = vo.createAndInitRow(pairs);
        //create a Name value attribute and calling createAndInitRow
          with name value attributes for detailed table that will be 
          used to insert row in table
        //commit the transaction and then release the connection

        return null;

This code is added as an action to submit button and should be called after creating a new row in the master table.

Wednesday, March 17, 2010

Creating dependent list of values using JDeveloper 11g

In this post I will discuses how to build a dependent list of value, in which two lists are involved.
This example is built on scott Emp/Dept tables in which it gets the list of employees working in specific department using single selection list.

So, after creating the required application using JDeveloper 11g, I created an ADF business components from tables attached to scott schema. When using the wizard it should create EMP and DEPT entities, views and a view link describing the constraints the exists between the dept_id in EMP table and dept_id in DEPT table.

The model should look somthing like the following figure, the view FKDeptnolink construct the relation between the two tables. Modify the DeptView and set OrderBy clause to order values by Dept_no, this will help to get Dept_no filed that correspond to Dept_name filed from the iterator that will be defined later in next steps.

then, create a view page in the view controller, this page will contain our list of values.
From data control drag the DeptView1 component and drop it on the page, select ADF Select One Choice component.

In the Edit List Binding, the Display Attribute select Department Name, and click ok.

Thursday, March 4, 2010

Export Datapump problem when using parallel paremeter in 10gR2 RAC database

When doing export datapump for Oracle database 10gR2 patch 2 RAC database, you should watch when using parallel parameter to export the data to more than one file at the same time.

when  running the following command it fails with an error, even it was running successfully before:
expdp system/pass@db10g schemas=test directory=TEST_DIR  
dumpfile=test_%U.dmp logfile=test.log
This reported by oracle as a bug, and as a work around you limit the parallel parameter to 1 or patch your database to higher version such as or As this was solved in these patch numbers.

expdp system/pass@db10g schemas=test directory=TEST_DIR 
dumpfile=test_%U.dmp logfile=test.log

Tuesday, February 23, 2010

Installing Weblogic Server 10.3 and extending it to support ADF

When I started working on ADF application development I thought when I am done from development, I just have to deploy the application to weblogic server, then running the application and it will run with no extra work to do.

But, it seems there some confusion when installing and deploying ADF applications to weblogic server, so i wanted to stat it in a step wise procedure.

This is a basic steps carried out through installation of production weblogic server:

1- Download weblogic server 10.3 and latest version of JDeveloper from oracle website.

2- Install weblogic into new BEA home, my installation is carried out on windows 2008 R2 server 64-bit. My installation directory is C:\BEA\Middelware.

3-  Deselect the components that you don't need such as :
  • Web 2.0 http pub-sub server
  • Weblogic Web Server Plugins
  • UDDI and Xquery Support
  • Server Examples
  • Workshop

Monday, February 8, 2010

Oracle Database, and RAC: How to delete, add, remove OCR and Voting Disk

This post is directed for DBAs who need to modify, or move OCR and voting disk locations. Some times a DBA wants to move to new storage area location. Typically, one would simply copy or dd the files once the new storage has been presented to the hosts. But when moveing OCR and/or voting disk its a little bit different.

You must be logged in as the root user, because root own the OCR files.

Make sure there is a recent copy of the OCR file before making any changes:
ocrconfig –showbackup

If there is not a recent backup copy of the OCR file, an export can be taken for the current OCR file. Use the following command to generate an export of the online OCR file:
ocrconfig –export -s online

To recover using the export file use the following command:
ocrconfig import

Friday, February 5, 2010

JDeveloper, and Weblogic:Accessing Detailed Views from Master One Using Java

One of the most addressable issues in Oracle ADF Application Development is how to access your business model components through Java method call. Accessing the parent view that requires a bind variable and attaching it to a page doesn't mean that the details views related to master one at data model level will execute with same bind variable.

In this post we will address the problem of how to call a master view with bind variable set through Java call ? and at the same time to fetish data from detail views ?

First of all make sure that your data model is built to reflect master detail relation. A view link between Master and Detail should be created at first and added to Data Model, like simple two key join in a select statement:

As you note a country-City relation ship exists, in which you must get country to get its cities.
The country view has a bind variable set at run-time through expression language and called through execute with params method call. This what happened at normal page flow navigation. But, in our case we want to set the bind variable using Java code and then looping thourgh to get the detailed cities.

Tuesday, February 2, 2010

JDeveloper, Weblogic and ADF: Security in Oracle ADF and Session invalidate

In enterprise applications that require user authentication, a user can't navigate back to a cached page. When developing applications using JDeveloper11g ADF, I encountered two problems.

First, how to kill a user session when clicking logout button, as explained below:
In ADF, you might use SessionScope to create variables or beans that can be carried out during the life cycle of a user session. Destroying the session bean or setting a session variable to null doesn't mean that the session is killed, to kill a session you have to:

1- Add a method call to logout button Action can be in the managed or backing bean and name it logout_action();

2- The code is used to invalidate the user session then redirect the page to your login screen displayed below:

        FacesContext fc = FacesContext.getCurrentInstance();
        ExternalContext ectx = fc.getExternalContext();      
        HttpSession session = (HttpSession)ectx.getSession(false);
        try {
        } catch (Exception exp) {

* note if you are using a bounded task flow and your loging screen exists in unbounded task flow located one level up then consider navigating through floders using (..) till login screen location.