Feed aggregator

Access migration to Application Express without direct SQL Access

Donal Daly - Wed, 2007-07-04 09:05
I got asked a question recently how to complete an Access migration when you don't have direct SQL access to the Oracle instance where Oracle Application Express is installed (e.g. apex.oracle.com)?

For dealing with the application part, it is not an issue as the Application Migration Workshop feature of APEX (3.0+) allows you to load the results from the Oracle Migration Workbench Exporter for Microsoft Access, so you can capture the meta data for Access Forms and Reports. You can even download a copy of the exporter from the workshop itself.

The challenge is really the schema and data migration part using Oracle SQL Developer (1.2+). By default SQL Developer expects to be able to make a SQL connection to the target Oracle database. However I did think about this use case as we were designing this new Migration Workbench tool. I will describe a solution below.

The only requirement, is that you have SQL access to any Oracle database (9iR2+), because the workbench is driven using an underlying migration repository. You could use the Express Edition of Oracle for this purpose, which is totally free, if you didn't have SQL access to an existing Oracle database.

So let me outline the main steps involved:
  1. Start SQL Developer 1.2
  2. Make sure you set the following preference: Tools -> Preferences -> Migration -> Generation Options: Least Privilege Schema Migration
  3. Create a connection to your Access database. Make sure you can browse the tables in the access database and see the data
  4. Export the table data to csv format: For each table you want to migrate, use the context menu associated with tables to export as csv format. Make sure you select an encoding that matches your target database. I try to keep everything in UTF-8
  5. Create a connection to an Oracle schema.
  6. Create a migration repository in this connection. You can do this via the context menu on a connection
  7. From your Access connection, context menu, select: Capture Microsoft Access. This will launch the exporter and initiate the capture step of the migration.
  8. Take your captured model and now create an Oracle (converted) model by selecting the captured model and via the context menu: Convert to Oracle Model
  9. With you converted model, you can now create an object creation script using the context menu: Generate
  10. The result of step 9 is presented in a SQL Worksheet, you can edit this to remove objects you are not interested in, then via File -> Save As, save the contents to a SQL file.
  11. Login to your APEX Workspace
  12. To execute the object creation script you have just created. Goto SQL Workshop -> SQL Scripts -> Upload.
  13. Once the script is uploaded, View it and select the RUN action. This should create all your schema objects, view the results to make sure all the object were create successfully. You now be able to view these schema objects in the SQL Workshop -> Object Browser.
  14. To load our CSV files we will use the Utilities -> Data Load/Unload -> Load, selecting Load Spreadsheet Data. You will do this for each table we want to load data into. Select Load To : Existing Table and Load From: Upload File. You may need to apply appropriate format masks to get the data to load properly.
  1. You should complete the schema and data migration part of your migration, prior to creating a migration project via the Application Migration Workshop.
  2. You may have some post migration cleanup steps, if you had access auto increment columns in your tables, you will need to reset the values of the sequences we have created.
  3. Another option to explore depending on your data, would be to export the data from Access tables as SQL INSERT statements, and then it just a simple matter of loading and run that SQL script via apex.

Thank you apps community

Fadi Hasweh - Wed, 2007-07-04 07:28
Today is my first day at work after the EPI-LASIK Surgery and my sight is facing haze, glare, ghosts and halos it will need more time before it become stable, I would like to take the chance to thank all apps community who wished me luck during my eye surgery, special thanks goes to hussine sawwan (hsawwan) for his phone call from Kuwait to check on me, and big thanks goes to Steven Chan for taking time to leave comment wishing me luck during the surgery. I would like also to thank marwan shantir, moh'd abu saif and adam shaug for their concerns. During my off days the most I missed was the oracle forums and the oracle blogs, I will need time to keep up with them.


OAF Key Do's and Don'ts (Part 2) - Performance Tuning: "Top 10" OA Framework Development Rules

Andries Hanekom - Tue, 2007-07-03 14:10
1) ALWAYS use design time view objects (VOs) rather than dynamic VOs. Dynamic VOs have to be described by BC4J through an additional execute call for the VO prepared statement, and they potentially result in non shareable SQL due to different combinations.

2) ALWAYS set precision for all columns in the VO. This reduces the VO memory footprint. Without a set precision, for example, all String columns default to 4KB in size each.

3) AVOID calling VO.getRowCount to check for existence. getRowCount causes the entire VO row set to be fetched back to middle tier.

4) NEVER call VO.getEstimatedRowCount. getEstimatedRowCount executes select count(*) on the VO. This count is not guaranteed to stay the same, thus the method name.

5) ALWAYS call registerOutParameters with proper precision for callable statements. This reduces the callable statement memory footprint. Without a set precision, for example, all varchar columns default to 32KB.

6) ALWAYS use Oracle-style binding (:1, :2) in your SQL and DO NOT use JDBC style binding (?). This avoids parsing SQL at runtime to do String replacement.

7) AVOID coding generic VOs just to try to reuse them. The "one VO fits all"approach adds complexity to your SQL, increases number of attributes and VO memory footprint.

8) DO NOT execute searches by default nor allow blind queries.

9) Use PNG format not JPEG for BI graph beans.

10) Use JDeveloper to profile your code. JDeveloper 9i has memory, CPU and
event profilers.

OPMN in E-Business Suite R12

Subashini Bala - Mon, 2007-07-02 02:17
Oracle Process Manager & Notification Server (OPMN) is a new addition to R12 E-Business Suite Technology Stack. OPMN manages all Oracle Application Server 10g Components (like 1. Oracle HTTP Server, 2. OC4J etc). OPMN has three components.

1. Oracle Notification Server - Tool which carries messages (about failure, recovery, start) between different components of Oracle Application server.
2. Oracle Process Manager - Central process management tool which manages all components of Application Server 10g. It is responsible for start, stop, monitor, failure detection, automatic restart of the processes it manages.
3. PM (Process Manager) modules - Tool which communicates the messages originating from the running component, holds the control information on how to start, stop etc.

OPMN can be intergrated with Oracle Enterprise Manager 10g Application Server Control Console. AS Control Console is the graphical user interface and can manage all Oracle Application Server components, under single view.

So... What it does, for Oracle Apps tech Stack?

As OPMN can explicitly manage Oracle HTTP Server and OC4J, it can as well detect if they are down or unanswering.

Say for example, there are several nodes of Oracle E-Business Suite R12 middle tier, on clustering (load balancing on Oracle HTTP Server). OPMN can monitor all the processes by pinging them, So when they are down, OPMN can automatically restart them.

Now, what is Replication of Session state:

Some Applications wants Oracle Applications Server 10g (in its middle tier) to maintain the stateful information to serve all consecutive requests. When such application is deployed on clustering, it is necessary to maintain the session state across multiple processes, in order to provide transparent failover of the requests.

Multiple redundant process would be made available, so that any processes can serve the client request. In the event of failure of one process, the another process can serve the same request successfully.

So... Does Oracle E-Business Suite R12 support state replication?

* Oracle Applications Server supports state replication using Application-level clustering, for J2EE application.
* Oracle HTTP listener routes the requests to the redundant OC4J Components , hence ensuring the state is replicated.
* Oracle AS Web Cache 10g provides stateless and stateful load balancing. Multiple instances of Oracle AS web Cache configures to be a Web Cache Clustering, these provide failover clustering in case one of the WebCache member fails.

Thus, with E-Business Suite R12, Session State Replication (of the requests) is possible both for HTML based interface (OA framework Pages) and Professional user interface (Forms based).

Please feel free to share your thoughts on the same.


SQL Techniques Tutorials: Varying IN Lists (New SQL Snippets Tutorial)

Joe Fuda - Sun, 2007-07-01 19:00
This new SQL Snippets tutorial explores different approaches for implementing varying IN lists, including ones that use LIKE, MODEL, and integer series generator logic. The solutions will be presented as SQL commands, but the logic can be easily implemented in PL/SQL functions if required.

The new top 500 supercomputer list is out!

Hampus Linden - Sun, 2007-07-01 16:48
Finally had some time to look at the latest Top500 supercomputer list released last week at ISC '07 last week.

Lots of changes compared to the old list; two quite interesting new things. First of all is Intel multi-core chips, secondly we have Blades, loads and loads of blades.
A significant number of entries on the list are using Intel Xeon 5160 3.0GHz dual-core CPU's and there is also a number of quad-core systems.
Another interesting point is that the crown in terms of number of systems on the list has been passed from IBM (with 192 systems) to HP (with 201 systems).
The HP Cluster Platform BL460c holds quite a few spots on the list. Can't say I've heard much about HP's HPC offering so far, they're not exactly beating the drum on that one (compared to the IBM guy who's calling me every 2 months).

SGI holds a few new slots on the list as well, which is extra fun to see considering the hard last year SGI have had. One Altix system even made it to number 10 on the list (with Columbia on number 13).

Top 500

Fix Control

Fairlie Rego - Sat, 2007-06-30 08:59
Oracle has introduced a fix control mechanism in 10.2 which allows customers to turn off fixes for optimizer related bugs. This is governed by the underscore parameter _fix_control. The bugs for which fixes can be turned off are listed in v$session_fix_control and v$system_fix_control and can also be seen in a 10053 output.

On a database

SQL> select distinct bugno from v$session_fix_control;


20 rows selected.

SQL> select distinct sid from v$mystat;


SQL> alter session set "_fix_control"='4728348:OFF';

Session altered.

SQL> select * from v$session_fix_control where session_id=143 and bugno=4728348;

---------- ---------- ---------- ---------------------------------------------------------------- --
143 4728348 0 consider mjc if equi-joined pred is dropped in kkoipt

A value of 0 indicates the fix is off.

SQL> alter session set "_fix_control"='4728348:ON';

Session altered.

SQL> select * from v$session_fix_control where session_id=143 and bugno=4728348;

---------- ---------- ---------- ---------------------------------------------------------------- --
143 4728348 1 consider mjc if equi-joined pred is dropped in kkoipt

It appears to me that if you need two fixes off in a session you have to specify them together else you lose the first change.

SQL> alter session set "_fix_control"='4728348:OFF';

Session altered.

SQL> alter session set "_fix_control"='4663698:OFF';

Session altered.

SQL> select * from v$session_fix_control where session_id=143 and bugno in (4728348,4663698);

---------- ---------- ---------- ---------------------------------------------------------------- --
143 4663698 0 for cached NL table set tab_cost_io to zero 0 0
143 4728348 1 consider mjc if equi-joined pred is dropped in kkoipt

Hence if you need more than one fix OFF you need to supply both bug numbers in the same command

SQL> alter session set "_fix_control"='4728348:OFF','4663698:OFF';

Session altered.

SQL> select * from v$session_fix_control where session_id=143 and bugno in (4728348,4663698);

---------- ---------- ---------- ---------------------------------------------------------------- --
143 4663698 0 for cached NL table set tab_cost_io to zero 0 0
143 4728348 0 consider mjc if equi-joined pred is dropped in kkoipt

OA Framework or ADF?

Andries Hanekom - Sat, 2007-06-30 02:00
Check out Sara Woodhull's article on OA Framework and ADF.

E-Business Suite R12 - Software Load Balancing

Subashini Bala - Fri, 2007-06-29 07:39
Found an interesting post by Steven (for E-Business Suite 11i version)


How about software load balancing with the Release 12 E-Business Suite !!

Let us start analyzing Apps-TechStack a bit. I remember, before r12 Oracle had Jserv in the middle tier. Now, Jserv has been replaced by OC4J with the Release 12. And therefore software load balancing of E-Business Suite R12 is technically possible, without the need to go for hardware load balancer.

There are 3 instances of OC4J running at the middle tier.

1. Forms => for Forms based interface
2. Oacore => for OAF Interface (HTML based interface)
3. Oafm => (Oracle Application Fusion Middleware) for mapviewer, webservices and ascontrol.

Until 11i , Oracle has only talked about hardware load balancer/router. Tech Stack of R12 supports software load balancing as well.

Now, What is load balancing?

Load balancing involves allocating the request between processes. A couple of important features makes Load balancing possible.

1) Allocating/Routing Algorithm:
Algorithm (popular ones are round robin, random, hash, allocating based on priority) to route the requests to different process(es).

2) Failure detection:
Requests which are broke-down/dead should be recognised and mark the corresponding processes as 'unused'.

Allocate requests - Where can we do that with Apps TechStack?

Software Load Balancing, in other words, 'routing the requests' is possible at three levels.

1. From Oracle AS WebCache 10gversion to Oracle HTTP Server
2. From Oracle HTTP Server to OC4J (using mod_oc4j)
3. From OC4J (Oracle Containers for J2EE) to Database

For the Professional user interface (Forms based screens), load balancing at "From OC4J to DB" level would not be possible, because the DB connection would happen from Forms Services.

Otherwise, Software load balancing should be possible for both Professional user interface and HTML based user interface (OA framework pages).

Please feel free to share your thoughts on the same.


PS: I know the questions running across.... With Load balancing architecture,
1) which will detect the failure nodes ?
2) How does the cluster of oracle application Servers 10g get to know about the failed node?
3) Will the next node be able to continue serving the request, when one node fail ?
4) where does the state replication occur? How come the second node get to know the continuation point, to serve the same request?

please wait until my next blog.

Joe's Blog: And Now for Something Completely Different

Joe Fuda - Thu, 2007-06-28 12:00

Every once in a while my non-programmer friends give me feedback about my site like "the blog posts are good, but when it comes to all that SQL stuff, well, it's a little over my head ... SQL sure seems to rely on that SELECT command a lot though."

So, I thought it might be good to take a break from SQL tutorials and spend time writing about something a broader audience will find useful. I looked through my archives and found some notes I made a few years back when I rewired the telephone cabling in my home and installed some LAN jacks. At the time I was surprised how difficult it was to get simple, straightforward answers to questions like what kind of cable to use or what kind of distribution device to buy. Even learning the proper names for connectors and figuring out which coloured wires go together proved more complex than I expected.

The amount and quality of information on the subject has gotten better over the years, but I still haven't found a source that presents all the home wiring information I need in a practical, systematic way. You'll find my stab at sorting it all out starting at Residential Telecommunications.

Be forewarned though, if you thought SQL was complicated wait until you learn about the 11 different ways to wire a telephone jack! :-)


Dell teaming with Oracle Enterprise Linux?

Hampus Linden - Thu, 2007-06-28 03:32
Just noticed that Dell change the "Red Hat Enterprise Linux" label on their support website to just "Enterprise Linux".
Is this the first step for Dell embracing Oracle Enterprise Linux?

Its summer, time to bring the car out for a good drive

Donal Daly - Wed, 2007-06-27 07:02
At the weekends, there is nothing better than taking out my Porsche for a long drive drive in the countryside. Much better, than my daily commute! I am a member of the Porsche Club of Ireland, and we had a great drive through the Wicklow mountains recently. The weather wasn't great but the scenery is still dramatic, lots of narrow twisting mountains roads to allow you to eke out the maximum enjoyment. It was fathers day so the kids decided they would both come with me, which was great.

I think we had over 15 members cars out that day. Everybody is very friendly, with a passion for all things Porsche. We started out from the Porsche Center in Dublin and they graciously provided us with coffee before we started and allowed us to gaze over all the new models. Out of my price range of course!

It was a real fun day and I look forward to the next event. We post the pictures from these "drives" on the club web site.

Multi to Single node Cloning issue

Fadi Hasweh - Wed, 2007-06-27 02:31
A friend called me yesterday he was facing the following error when he tried to clone from multi node to single node cloning
"Could not create the high water mark manifest..Please re-run the program to create the manifest required for merging APPL_TOPs"

He faced this error after he run the (perl adpreclone.pl appsTier merge) and then he cancel it while running and then he run it again on the other node then he recived this error.

According to metalink notes 372819.1 and 372758.1 he should reapply the AD.I.x patch again to the node and then run Maintain snapshot and then run the perl adpreclone.pl appsTier merge again, we did so and every thing went fine.

Hope that helped and by the way this will be my last post before my Eye LASIK Surgery which is scheduled tomorrow morning at 8:30 a.m.


Tab Complete in Windows

Robert Baillie - Tue, 2007-06-26 14:04
Another one of those things that I can never remember off the top of my head so find myself constantly looking it up whenever I get access to a new machine. I figure it may as well be my own site that I get the info from :-) To switch on 'Tab Complete' in Windows command line change the following registry keys to '09': HKEY_LOCAL_MACHINE\Software\Microsoft\Command Processor\CompletionCharHKEY_LOCAL_MACHINE\Software\Microsoft\Command Processor\PathCompletionChar

Tab Complete in Windows

Rob Baillie - Tue, 2007-06-26 14:00
Another one of those things that I can never remember off the top of my head so find myself constantly looking it up whenever I get access to a new machine.

I figure it may as well be my own site that I get the info from :-)

To switch on 'Tab Complete' in Windows command line change the following registry keys to '09':
  • HKEY_LOCAL_MACHINE\Software\Microsoft\Command Processor\CompletionChar
  • HKEY_LOCAL_MACHINE\Software\Microsoft\Command Processor\PathCompletionChar

RDBMS CPU Patch 5901881 Gotcha

Solution Beacon - Tue, 2007-06-26 09:08
I recently ran across this issue while applying the RDBMS CPU patch (5901881) for version start off with a little background information:ORACLE_HOME - /opt/oracle/testdb/10.2.0Operating System - HP-UX 11.11Installation - cloned from another ORACLE_HOMEOPatch returned the following error to me while applying patch 5901881:INFO:Running make for target libnmemso INFO:Start invoking 'make

Database Growth and Solutions Part III

Solution Beacon - Tue, 2007-06-26 09:06
Well this is our final in the series on Database Growth and Solutions for that growth. Today with focus on Database Archiving and Hierarchical Storage Management (HSM).Database ArchivingDatabase archiving provides the ability to archive data that is seldom accessed off to various storage options while retaining the ability to easily access that data and ensuring referential integrity as well as

Recompiling executables results in "undefined reference to `__pure_virtual' "

Solution Beacon - Tue, 2007-06-26 07:59
In a recent upgrade from 11.5.9 to, on Red Hat Enterprise Linux 4, I ran into a problem where certain executables were not compiling. As it turned out, the executables (ENCACN, WICDOL, WICMEX, WICMLX) were all compiled with g++.This was our 3rd iteration of the upgrade and this behavior had not been observed in prior upgrades. The difference between interation #2 and iteration #3 was

Fun with Linux Filesystem Labels

Solution Beacon - Mon, 2007-06-25 09:42
Most things in life involve a little Give and Take, and filesystems in Linux are certainly no exception.On the Give side of the equation Linux offers the ability to identify a filesystem not only by its traditional device file name, such as /dev/sde1, but also by a unique label that you can apply to the filesystem. On the Take side, if you've ever supported a Linux system attached to SAN storage

Sun 2002 flashback

Hampus Linden - Sun, 2007-06-24 14:54
We've been doing some shuffling around of hardware at work and I've recommissioned some old Sun hardware for a new QA environment.
Oh the memories I have of old Sun kit :)
The most exciting thing I've installed is a Sun v480 connected to a T3 brick. That combo was pretty much the industry standard Oracle solution when I had my first job in "real" systems administration and it was probably one of the first the real mid-range installs I did (not counting ageing 420r/450's).
To be honest, it's a match made in heaven. A quad cpu v480 with a t3 brick (or two) kicked out some real performance numbers in it's day. The v480 is probably one of the nicest boxes to work with (considering age and all), the boards are easy to fit, the RSC is awesome, it's simply rock solid. Running Solaris 8 of course (we still have *lots* of clients on Solaris 8 (with Solaris 10 gaining ground)).

The T3 array is pretty cool, but it brings back the frustrations everyone had with early low-end SANs. The limitation to two disk RAID volumes with very limited zoning and slicing. But hey, it's pretty old and is still pretty fast.

Now if I only could figure out how to get rid of that darn Sun E250 we still have running Sybase 12.5.4, it just works to well so far.


Subscribe to Oracle FAQ aggregator