Author Archive

2012 in review

Posted: December 31, 2012 in Uncategorized
Tags: ,

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

600 people reached the top of Mt. Everest in 2012. This blog got about 7,200 views in 2012. If every person who reached the top of Mt. Everest viewed this blog, it would have taken 12 years to get that many views.

Click here to see the complete report.

We were applying a pre req patch before upgrade from 12.1.1 to 12.1.3. After sourcing the environment , I did OPATCH lsinventory and it gave expected output. After that when we applied OPATCH apply, it errored out with the message
OPATCH_JAVA_ERROR=OPatch Exception While Trying to Check for Mini Patchset

Looking in My Oracle Support did not give much clue. The Note ID 971783.1 OPATCH_JAVA_ERROR=OPatch Exception While Trying to Check for Mini Patchset’ while applying patch using opatch 1.0.0.0.x. This note suggested removing the ‘&’ and ‘<‘ character from the bug description section in the /etc/config/inventory file. However, my inventory file did not have any such characters except the XML tags, which I couldn’t remove.

Our Solution

It was then that I noticed that the patch was unzipped into a path which had a space in the folder name. We renamed the folder to a simpler name, without spaces and the patch proceeded normally.

Posted: April 2, 2012 in Oracle Database Posts

Jithin Sarath:

Some interesting facts I never know about rows in Oracle and how they are stored.. In my quest to answer this question, I learnt a lot about rows, row blocks, how they are composed of and some oracle anomalies.
I’m glad that I looked at the question and got hooked. I am yet to reproduce and understand the final bit in this post, but will do it when I get a few hours time.

Originally posted on Oracle Scratchpad:

I know I haven’t been very good about posting on the blog or replying to questions lately (and a big thank you to anyone who has answered some of the recent questions correctly), but tonight is a Friday night, and I have a few moments to spare, so here’s a question prompted by a recent comment on OTN.

I have a table declared as follows (and the ellipsis means repeat the column definitions according to the simplest and most obvious pattern to give you 1,000 columns all of type number(1)):

View original 182 more words

I needed to find out if a certain event was set at the database level on my instance. The READ_EV procedure of DBMS_SYSTEM came to my help. Below is a quick way to find out what are the currently set events for your session (session level). It will report all instance wide events too as they are applicable to session also.

In the below example, event_level will be zero if event is not not set at all.

set serveroutput on

DECLARE
  event_level NUMBER;
BEGIN
  FOR i IN 10000..10999
  LOOP
    sys.dbms_system.read_ev(i,event_level);
    IF (event_level > 0) THEN
      dbms_output.put_line('Event '||TO_CHAR(i)||
                           ' set at level '|| TO_CHAR(event_level));
    END IF;
  END LOOP;
END;

Most of the long running SQL’s can be monitored by using the oracle view v$session_longops. This view is not to be queried in standalone. Rather, you should identify the session that you want to monitor using the v$session and the use session_id as a filter. I also add a condition time_remaining > 0 so that it lists only the active ones.

SQL to Monitor v$session_longops

SELECT SID,
       ROUND(TIME_REMAINING /60,2)"time remaining(min)",
       ROUND(ELAPSED_SECONDS/60,2)"elapsed_mins",
       SOFAR,
       TOTALWORK-SOFAR "Work Remaining" ,
       SQL_PLAN_OPTIONS||' '||SQL_PLAN_OPERATION "PLAN OPERATION",
       MESSAGE
  FROM V$SESSION_LONGOPS
 WHERE TIME_REMAINING>0;

There are a number of criteria which makes a SQL to be listed in view V$SESSION_LONGOPS, and I am not sure of all of them. In case of Full Table Scans, the scan has to run for at least 6 seconds and the table scanned should occupy at least 10000 db_blocks. The view also lists long operations in hash_joins, sorts, sort outputs, gather table stats and gather tables index stats. The 6 second criteria appears to be applicable across all of it while the 10K block occupancy is limited to the Table Scan alone.

Note that the time_remaining column in the view should never be considered to predict the “actual” time remaining. In most cases, especially involving hash_joins the time increases rather exponentially than linear. It depends on the hash_area_size (AUTO by default in 11g) and the size of the blocks to be processed. And if it starts using TEMP to do the sorts and you know that there is a large amount of data, you should probably start tweaking the SQL :)

Exceptions:

When executing DML (DELETE & UPDATE), in addition to the rows in the table, oracle has to update the indexes on the table. The time spent in doing the index modification is not reflected properly in the v$session_longops view as it looks at table blocks only. The estimate will also be based on table blocks and hence will be lower than the actual value required.

Custom Applications:

The DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS can be used to add this facility to custom applications. Once this is set, oracle will display the information about that session in the view. For it to work effectively, you must know the total amount of work you are planning to do and how much work you have done so far.

Syntax

DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS (
   rindex      IN OUT BINARY_INTEGER,
   slno        IN OUT BINARY_INTEGER,
   op_name     IN     VARCHAR2       DEFAULT NULL,
   target      IN     BINARY_INTEGER DEFAULT 0,
   context     IN     BINARY_INTEGER DEFAULT 0,
   sofar       IN     NUMBER         DEFAULT 0,
   totalwork   IN     NUMBER         DEFAULT 0,
   target_desc IN     VARCHAR2       DEFAULT 'unknown target',
   units       IN     VARCHAR2       DEFAULT NULL)  ;

set_session_longops_nohint constant BINARY_INTEGER := -1;

Syntax For the above referenced from Oracle Documentation

What is Virtual Address Space?

Data readily accessible to the processor are identified by a memory address, which starts at zero and ranges to a max value depending on the processor. 32-bit processors use memory addresses that are 32 bits wide. The 32-bit wide address allows the processor to address 2^32 bytes of memory, which is exactly 4GB. And this is the 4GB memory limit.

 

Why is there a 2 GB address space limit?

The address space might contain things other than memory. When a program loads under Windows, it is managed as a process. One of the most important features of a process is its virtual address space, which holds all of the code and data the process will directly access. In the 32-bit versions of Windows, processes are given a default memory limit of two gigabytes. This limit means has the logical addresses that any process touches varying between 0x00000000 and 0x7FFFFFFF (this is the full range of non-negative numbers that can fit in 32 bit). To defend the system against poorly written applications and device drivers, Windows allocates memory for applications from 0x00000000 to 0x7FFFFFFF. It maps the operating system itself in the range 0x80000000 to 0xFFFFFFF; as much as it can, at any rate. This gives user processes two gigabytes of virtual address space, and gives the system two gigabytes of virtual address space.

Is there an option to work-around the 2 GB limit?
On a system with more than two gigabytes of memory, the system can be booted with the /3GB option to expose more than two gigabytes of address space to each process that’s marked with the large-address aware bit in its executable image. User processes receive three gigabytes of space, then, while the system can use a single gigabyte of address space. However, the benefit of using this extra space can be mitigated by the fact that the system has to compress itself into the remaining one gigabyte of address space.

Reference: http://hardforum.com/showthread.php?t=1035670

 

 

 

I read around for strategies on platform migration of EBS R12 and 11g DB. There are paths defined by Oracle which guides such migrations. Given below is a high level description of an oracle endorsed approach of App-Tier migration process.  DB migration is a separate topic and should be dealt as such.

Note:

  1. Most of the application tier migration can be done in advance and a very minimal downtime is required. However DB tier migration will require downtime (you have to move the 2 TB data from one place to other!)
  2. The below steps are explained in detailed in the note (438086.1) which was my reference.

 

  • Pre-Requisites
  1.     OS system requirements in the new platform target machine.
  2.     Software requirements on source and target machine (zip, JDK, perl etc)
  3.     Apply the latest AD Patch (R12.AD.B.DELTA.3 for 12.1)
  4.     Upgrade to EBS 12.0.4 or higher
  5.     Apply the latest Autoconfig template patches
  6.     Apply the latest Rapidclone Patches
  7.     Apply Platform Migration Patches & Additional Patches (No Patched advised for 12.1.x at this point of time)
  8.     Run Autoconfig on Source system
  9.     Run adpreclone in source system
  10.     Run maintain snapshot (And this must complete successfully)
  11.     Identify Tech Stack Updates to be done to the Target machine after the migration. This is done by using technology Stack Inventory utility
  •     Migration tasks
  1.     Generate and upload the manifest of customer-specific files. This is used to generate a custom migration patch. The custom migration patch created is specific to specific upgrade only and contains all the files contained in the APPL_TOP that are binary specific to the new target platform. This patch is therefore applied in the new environment.
  2.     Copy the Source System to the Target System
  3.     Install JDK and InfoZip utilities on the Target System
  4.     Copy the Source System Context File to the Target System
  5.     Clone the Applications Context File on the Target System
  6.     Install the Application Tier Technology Stack
  7.     Run AutoConfig setup phase on the Target System
  8.     Download and apply the customer-specific update with AutoPatch
  9.     Review Component Versions and Technology Stack patch level
  10.     Regenerate File System Objects
  11.     Clean Nodes
  12.     Run AutoConfig to complete the Target System configuration
  • Finishing tasks
  1.     Update Third Party Extensions.
  2. Review and update Target System Application Tier Settings and Customizations
  3. Update printer settings
  4. Update Oracle Workflow configuration settings
  5. Verify the APPLCSF variable setting
  6. Update the SESSION_COOKIE_DOMAIN value in ICX_PARAMETERS
  7. Start all services on the Target System

 

Reference:

Application Tier Platform Migration with Oracle E-Business Suite Release 12 ID : 438086.1