Feed aggregator

Oracle 11g Database New Features: Data Guard Enhancements

Virag Sharma - Wed, 2007-09-12 07:59

Why We need Data Guard ?? for

  • Data protection
  • Data Availability

11g Increase ROI from standby systems and enhance manageability, New feature like Active Data Guard , Snapshot standby make better ROI.

Here is some data guard category and there enhancement

1) Data Protection
  • Advanced Compression
  • Lost-write protection
  • Fast-Start Failover
2) Increase ROI
  • Active Data Guard
  • Snapshot Standby
3) High Availability
  • Faster Redo Apply
  • Faster failover & switchover
  • Automatic Failover using ASYNC
4) Manageability
  • Mixed Windows/Linux

Active Data Guard

Oracle Active Data Guard 11g – a new Database Option
Enables read-only access to a physical standby database while Redo Apply is active
It is now possible to query a physical standby database while Redo Apply is active.This new capability increases your return on investment in Data Guard technology because a physical standby database can now be used to offload queries from the primary database in addition to providing data protection.
( We can do same with logical standby , what is diffrent here , well redo apply method is faster in physical stand by ie redo apply methode is diffrent in physical standby)


  • Stop log apply
Alter database recover managed standby database cancel;
  • Open database for read-only access
alter database open
  • Once database open start redo apply
alter database recover managed standby database
using current logfile disconnect from session;


Snapshot Standby


This enhancement is good example of ROI. This feature allow us to better utilization of standby. This feature Truly leverages DR hardware for multiple purposes.For example convert physical database to snapshot standby database do testing/ application patching etc. After testing convert back snapshot database back to physical standby.













  • -- Convert physical database to snapshot
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
  • --
  • -- Do testing on database
  • --
  • -- Convert snapshot Database to physical database

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;


This Similar to storage snapshot , but it useage same space , not additional space is needed ( I know this possibly can achieve in 10g R2 too , using flashback feature , But for sure it is not easy as it is in 11g )


Redo compression

To transport redo data in compressed form to destination use COMPRESSION attribute.
By default compression is disabled.


  • Alter system set log_archive_dest_1 = 'SERVICE=standby1 compression=ENABLE';

  • select dest_name , compression from v$archive_dest;

    SQL>
    DEST_NAME COMPRES
    --------------------------------- -------
    LOG_ARCHIVE_DEST_1 DISABLE
    LOG_ARCHIVE_DEST_2 ENABLE
    LOG_ARCHIVE_DEST_3 DISABLE
    LOG_ARCHIVE_DEST_4 DISABLE
    LOG_ARCHIVE_DEST_5 DISABLE
    LOG_ARCHIVE_DEST_6 DISABLE
    LOG_ARCHIVE_DEST_7 DISABLE
    LOG_ARCHIVE_DEST_8 DISABLE
    LOG_ARCHIVE_DEST_9 DISABLE
    LOG_ARCHIVE_DEST_10 DISABLE

    10 rows selected.

Data Protection

New Initialization parameter db_lost_write_protect Introduced. This parameter Compare versions of blocks on the standby with that in the incoming redo stream
Version discrepancy implies lost write on either primary or standby database

db_lost_write_protect


Managibilty Enhancement

SYS user and password files no longer required for redo transmission authentication. Non-SYS user can be specified through the parameter, This user must have the SYSOPER privileges, Requires password for this user to be the same at primary and all standbys
Upon SYSDBA / SYSOPER changes, password file must be copied from the primary to all physical standby databases

redo_transport_user

Data Guard Support for Heterogeneous Primary and Standby Systems in Same Data Guard Configuration (- Since 11g Linux installer only available, so not able to test this feature)

Fast-Start Failover

Immediate automatic failover for user-configurable health conditions



DGMGRL> show FAST_START FAILOVER
DGMGRL> ENABLE FAST_START FAILOVER [CONDITION ];

Condition examples:
  • Datafile Offline
  • Corrupted Controlfile
  • Corrupted Dictionary
  • Inaccessible Logfile
  • Stuck Archiver
  • Any explicit ORA-xyz error


Application can also request for failover using package DBMS_DG

DBMS_DG.INITIATE_FS_FAILOVER



Categories: DBA Blogs

My new laptop

Siva Doe - Mon, 2007-09-10 15:15

Last week our team got couple of new laptops. Sony Vaio VGN-FZ17G.
Looks shiny and sleek. It came loaded. Core2 Duo @ 2.00 GHz, 2GB RAM,
160G Disk, Vista Home Premium. The battery life sucks though. With the
default power plan of Vaio Optimized, just about 40 mins of battery
backup. May be it will give more with power saver plans.

Coming to the main story - with the help of GParted, resized the Vista
partition. Then started installing Nevavda build 72. I was all worked
up eager to see Caiman installer at work. Alas, no such luck. Looks
like the Nvidia GeForce 8400M GT card was not supported by the bundled
driver in nv_72. So, I had to endure one more session of the familiar
blue/green console installer.

First thing I did was to create a ZFS pool and had /opt in ZFS (is
this safe?). Next, downloaded and installed Nvidia latest driver,
which worked beautifully. Now Xorg is running at the native 1280x800
resolution. Strangely, my Wifi is working but not my ethernet port.

These will be my next steps. Get the ethernet working (Marvell Yukon).
Install Compiz Fusion (thanks Erwann), Second Life for Solaris (thanks
Dana). Install Sun Studio 12 (I have to get back to work, dont I?);
Coolstack. The others will be TBD later. Sound is not working, but
this will be my lowest priority though.
D(["ce"]);

//-->

Latest supercomputer!

Peter Khos - Sun, 2007-09-09 01:38
I found this news very interesting indeed. The latest supercomputer is here and it is not controlled by government or private agencies. Instead, it is under the control of (most likely) criminals and the question being asked is "what do they planned to do with it?"It's scary but just imagine the possibilities and none of them are good at all. I think it might be prudent for the white hats to Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com0

Some Good Links / Blogs / site to know more about New Oracle Database 11g features

Virag Sharma - Sat, 2007-09-08 21:56

I have rated 5 *(star) for some sites , which I like Most

( these links collected from google alert )

  1. Oracle Database Online Documentation 11g Release 1 (11.1) *****
  2. ORACLE-BASE - Articles on Oracle 11g new features *****
  3. Oracle Database 11g on OTN
  4. Oracle Database 11g: The Top Features for DBAs and Developers
  5. Pythian Group Blog » Tuning Pack 11g : Real-Time SQL Monitoring
  6. PSOUG - New in 11gR1*****
  7. Robert G. Freeman’s Blog: 11g Security New Feature… A short one…
  8. Oracle 11g new Features Summary by Burleson
  9. My top 10 Oracle 11g New Features Part 3 - 5 nice optimizer statistics
  10. Oracle 11g Top New Features for DBA Virag Sharma
  11. Oracle Magazine: PL/SQL Practices: On the PL/SQL Function Result Cache
  12. Changes in Oracle 11.1 - Julian Dyke *****
  13. Blogging about 11g - Part 7 - Function Result Cache
  14. Oracle 11g New Features SQL plan management (SPM)
  15. Oracle 11g New feaures : Case Sensitive Password
  16. SQL Performance Analyzer (SPA) Part - 2
  17. Oracle 11g ADR Automatic Diagnostic Repository
  18. New Parameters in 11g (Part 2) | Dizwell Informatics
  19. Oracle 11g DRCP: Database Resident Connection Pooling - second attempt
  20. Oracle 11g internals part 1: Automatic Memory Management
  21. My top 10 Oracle 11g New Features Part 4 - the SQL Query Result Cache
  22. Oracle 11g Tips by Burleson Consulting

You can have a look on following forum as well. Good thing about this forum is that , it update time to time , with latest 11g Links. Thanks to TongucY

http://forums.oracle.com/forums/thread.jspa?threadID=542281&tstart=90

- Vi
Categories: DBA Blogs

Concurrent Usage for E-Business

Peter Khos - Sat, 2007-09-08 16:52
Blogger seems to have lost the last entry that I posted earlier this week. I didn't check and now I can't even remembered what it was that I posted about.Anyhow, there was a number of stuff to post but I thought I would focus on a current technology issue that we are facing with our Oracle E-Business project. Our test lab has been running some performance scripts on our E-Business test Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com2

Nulls: Nulls and Aggregate Functions (New SQL Snippets Tutorial)

Joe Fuda - Sat, 2007-09-08 07:00
A new tutorial has been added to SQL Snippets which demonstrates how aggregate functions deal with null values. Techniques for generating results that ignore nulls and results that include nulls are highlighted.
...

Options for Generating XML for BI Publisher

Solution Beacon - Fri, 2007-09-07 10:42
Oracle's BI Publisher is a powerful tool for producing richly formatted documents. It takes any well-formed XML data and refines it into custom invoices, checks, bar coded labels, web pages – the possibilities are limitless. The source for the XML data can be any process that can generate well-formed XML data ("well-formed" simply means that it complies with XML standards). In the Oracle

Oracle 11g New Features SQL plan management (SPM)

Virag Sharma - Fri, 2007-09-07 07:54
You might have noticed that execution plan changed in CBO , specially in following case

  • Database Upgrade
  • Database / Schema Stats collection
  • Change in environment ( LinkUnix to Linux Migration )
  • Change in data














Case
:
You want to upgrade database from 10g to 11g and this change can cause regressions in SQL performance,and fixing them manually can be difficult and time consuming. Sql tuning can be used but, this is a reactive mechanism and cannot guarantee stable performance when drastic changes happen to the system. SQL tuning can only resolve performance issues after they have occurred and are identified. For example, a SQL statement may become a high-load statement due to a plan change, but this cannot be resolved by SQL tuning until after the plan change occurs.

Solution: Oracle 11g new features SQL plan management (SPM) records and evaluates the execution plans of SQL Statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.

Note:- Before upgrading database to 11g (11.1.0.6) , i have collect SQL Tuning set (STS) for 7 Day using "capture_cursor_cache_sqlset". By assuming that in 7 day all SQL stas will collected in STS. After that upgraded database to 11g ( from 10g 10.2.0.3) , i have used this STS to compare execution plan from 11g. Please check "SQL Performance Analyzer Part - 2"
for steps to transfer STS one database to other database


1) Capturing SQL Plan Baselines

· Automatic Plan Capture ( 11g Only )

· Manual Plan Capture ( 10g and 11g )

2) Make Changes Upgrade Database / collect stats / Migrate Database to Linux

3) Upload SQL Plan Baseline

4) Enable the use of SQL plan baselines

5) Evolving SQL Plan Baselines

1) Capturing SQL Plan Baselines in oracle 10g before upgrading to 11g

a)
Automatic Plan Capture(11g only)

When OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES set true , then database automatically creates and maintains the plan history for SQL statements using information provided by the optimizer.

>

In init.ora file

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true

OR

SQL>Alter system set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = true;

Note : - Above automatic plan capture only work on 11g , if you want to capture plan in 10g , then check section “Manual Plan Capture”

b) Manual Plan Capture ( 10g and 11g )

Following script will capture sql plan in SQL tuning set for 7 Day ( this script will run for 7 day , you can change time according to your need )

exec dbms_sqltune.drop_sqlset('STS_RAG_CMUL');
exec dbms_sqltune.create_sqlset('STS_RAG_CMUL');

DECLARE
sts_cmul VARCHAR2(30) := 'STS_RAG_CMUL';
BEGIN

dbms_sqltune.capture_cursor_cache_sqlset(sts_cmul,
604800,
1,
'MERGE',
dbms_sqltune.MODE_ACCUMULATE_STATS
);

END;

capture_cursor_cache_sqlset : The procedure captures a workload from the cursor cache into a SQL tuning set, polling the cache multiple times over a time period and updating the workload data stored there. It can execute over as long a period as required to capture an entire system workload.

  • time_limit : 604800 ( The total amount of time, in seconds, to execute , 7 day = 7 * 24 * 60 * 60 = 604800 )
  • repeat_interval : 1 ( The amount of time, in seconds, to pause between sampling )
  • capture_option During capture, either insert new statements, update existing ones, or both.
    • 'INSERT'
    • 'UPDATE',
    • 'MERGE'
  • capture_mode capture Option (UPDATE and MERGE capture options).
    • MODE_REPLACE_OLD_STATS - Replace statistics when the number of executions
      seen is greater than that stored in the SQL tuning set

MODE_ACCUMULATE_STATS - Add new values to current values for SQL we already store. Note that this mode detects if a statement has been aged out, so the final value for a statistics will be the sum of the statistics of all cursors that statement existed under.

The CAPTURE_CURSOR_CACHE_SQLSET function enables the capture of the full system workload by repeatedly polling the cursor cache over a specified interval. This function is a lot more efficient than repeatedly using the SELECT_CURSOR_CACHE and LOAD_SQLSET procedures to capture the cursor cache over an extended period of time. This function effectively captures the entire workload, as SQL Profiles opposed to the AWR—which only captures the workload of high-load SQL statements or the LOAD_SQLSET procedure, which accesses the data source only once.

2) Make Changes e.g. Upgrade Database / collect stats / Migrate Database to Linux Collect stats

exec dbms_stats.set_param('ESTIMATE_PERCENT','100');

exec dbms_stats.set_param('method_opt','FOR ALL COLUMNS SIZE 254');

exec dbms_stats.Gather_Database_Stats;

3) Upload SQL Plan Baseline

If you have collected stats manually in SQL tuning sets ( STS) , then you need to
upload baseline from STS

-- Upload plan manually using dbms_spm
--

variable pls number;

exec :pls := dbms_spm.load_plans_from_sqlset(sqlset_name=>'STS_RAG_CMUL',-
basic_filter=>'parsing_schema_name like ''APPS'' and plan_hash_value!=0', -
fixed=>'NO',commit_rows=>1000);

4) Enable the use of SQL plan baselines

To enable the use of SQL plan baselines, set the OPTIMIZER_USE_SQL_PLAN_BASELINES
initialization parameter to TRUE. By default, this parameter is set to TRUE.

--In init.ora file
--

OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE

OR

SQL> alter system set OPTIMIZER_USE_SQL_PLAN_BASELINES= true;

5) Evolving SQL Plan Baselines

I have captured 10g plans in STS and after upgrading database to 11g uploaded those plans ,using dbms_spm.load_plans_from_sqlset. After uploading plans manually , I have set

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to true.

--Check not accepted Plans
--

SQL> select sql_handle, plan_name, enabled, accepted, fixed
from dba_sql_plan_baselines
Where ACCEPTED='NO' and
PARSING_SCHEMA_NAME like 'APPS';

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED
--------------------- ------------------------- ------- --------- -------
SYS_SQL_9295397103ae5ebe SYS_SQL_PLAN_74720f16b0fcefa7 YES NO NO
SYS_SQL_9295397103ad3eba SYS_SQL_PLAN_03ad3ebaa086802f YES NO NO
SYS_SQL_434ef30d9da6a29b SYS_SQL_PLAN_9da6a29b1f6e321d YES NO NO
SYS_SQL_13836d6b3da62bbb SYS_SQL_PLAN_3da62bbbe5990995 YES NO NO
.......................................... ....................................

40 rows selected.

-- Displaying SQL Plan Baselines
--

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_13836d6b3da62bbb',format=>'basic'));

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_13836d6b3da62bbb
SQL text: ( I have removed SQL text and changed table name in below execution plan)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbb91266099
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2877140902
-------------------------------------------------
Id Operation Name
-------------------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 INDEX FULL SCAN COMP_PROD
-------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbbe5990995
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3245492848

-----------------------------------------
Id Operation Name
-----------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 TABLE ACCESS FULL COMP
-----------------------------------------

42 rows selected.

SQL>


Evolving Plans with pls/sql function DBMS_SPM.EVOLVE_SQL_PLAN_BASELIN

--Evolving Plans With DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
--

SQL> DECLARE
report clob;
BEGIN

report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SYS_SQL_13836d6b3da62bbb');
DBMS_OUTPUT.PUT_LINE(report);
END;
/

-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE =SYS_SQL_13836d6b3da62bbb
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES

Plan: SYS_SQL_PLAN_3da62bbbe5990995
-----------------------------------
Plan was verified: Time used .06 seconds.
Failed performance criterion: Compound improvement ratio <= .3.

Baseline Plan Test Plan Improv. Ratio

------------- --------- -------------

Execution Status: COMPLETE COMPLETE
Rows Processed: 1460 1460
Elapsed Time(ms): 10 10 1
CPU Time(ms): 9 9 1
Buffer Gets: 84 286 .29
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------

Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.

PL/SQL procedure successfully completed.

SQL>

Just for demo purpose , lets accept plan 2 ('SYS_SQL_PLAN_3da62bbbe5990995')

-- Acccept plan 2 ('SYS_SQL_PLAN_3da62bbbe5990995') and Fixed it
--

variable cnt number;
exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>
'SYS_SQL_13836d6b3da62bbb', -
plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', -
attribute_name => 'ACCEPTED', attribute_value => 'YES');

SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>
'SYS_SQL_13836d6b3da62bbb', -
plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', -
attribute_name => 'ACCEPTED', attribute_value => 'YES');

PL/SQL procedure successfully completed.
SQL>

-- Same pl/sql function used to fix plans
-- Optimizer always picked FIXED plans

SQL> exec :cnt := dbms_spm.alter_sql_plan_baseline(sql_handle =>
'SYS_SQL_13836d6b3da62bbb', -
plan_name => 'SYS_SQL_PLAN_3da62bbbe5990995', -
attribute_name => 'FIXED', attribute_value => 'YES');

PL/SQL procedure successfully completed.

SQL>

- - Check status of plans
--

SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines
2 Where FIXED='YES' and PARSING_SCHEMA_NAME like '';

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED FIXED
----------------------------- ---------------------------- -------------- --------
SYS_SQL_13836d6b3da62bbb SYS_SQL_PLAN_3da62bbbe5990995 YES YES YES

SQL>

>

>

-- Displaying SQL Plan Baselines after changes
--

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_13836d6b3da62bbb',format=>'basic'));

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_13836d6b3da62bbb
SQL text: ( I have removed SQL text and changed table name in below execution plan)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbb91266099
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2877140902

-------------------------------------------------
Id Operation Name
-------------------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 INDEX FULL SCAN COMP_PROD
-------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_3da62bbbe5990995
Enabled: YES Fixed: YES Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3245492848

-----------------------------------------
Id Operation Name
-----------------------------------------
0 SELECT STATEMENT
1 SORT ORDER BY
2 HASH JOIN SEMI
3 TABLE ACCESS FULL PROD
4 TABLE ACCESS FULL COMP
-----------------------------------------
42 rows selected.


ReferenceSQL Plan Management Chapter 15
Categories: DBA Blogs

Payroll Rollback

RameshKumar Shanmugam - Wed, 2007-09-05 16:19
Once the Payroll /Quick Pay process is completed if there is some issue or if some correction need to be made. We can either
  • Retry
  • Reverse
  • Rollback

In this blog i am trying to explain how to rollback a Payroll process.

What Rollback does?

In simple word to explain Rollback process reset the Run Result and the Latest balance back to the value before running the Payroll Process.

Rollback process has to be run in the same reverse sequence of the Payroll Run. for eg. for processing the payroll, we run the following process in sequence Payroll run, Prepayment, costing, Transfer to GL. Similarly for the Rollback we need to run in the reverse sequence Rollback Transfer to GL, Rollback Costing, Rollback Prepayment, Rollback Payroll Run.

Note: if the costing details are imported into GL then we cannot rollback the process.So it is always advisable to run the Transfer to GL process after all validation and checking are done and run this process when we feel no more modification are needed

List of Process type that can be rolled back in PF K RUP1

  • Advance Pay Element
  • Balance Adjustment
  • Bank or Post office Payment
  • Batch element entry
  • cheque writer
  • Costing
  • Costing of Payment
  • Estimate costing
  • ext/Manual Payment
  • Magnetic report
  • Magnetic Transfer
  • Pre-Payments
  • Purge
  • QuickPay Pre-Payment
  • Quick Pay run
  • Report Generator
  • Retro Costing
  • Retro Pay
  • Retro Pay by Action
  • Retro Pay by element
  • Reversal
  • Run
  • Transfer to GL
  • Void Payment

Categories: APPS Blogs

UKOUG 2007 agenda packed with APEX

Anthony Rayner - Wed, 2007-09-05 10:43
The agenda for UKOUG 2007 has just been released and there is currently a total of 13 presentations on or relating to APEX (and a keynote delivered by Tom Kyte!). They are...

Loads to learn so come along, I'll be there and presentating 'Building The Rich User Interface with Oracle Application Express and AJAX' so looking forward to doing that (scary!) and listening to many more! Hope you can make it!
Anthony.

Categories: Development

Disabling cursor trace

Fairlie Rego - Wed, 2007-09-05 08:55
Sometime last year I had blogged about unshared cursors and an event to trace the same here
http://el-caro.blogspot.com/search?q=cursor+trace


Well after you set the trace on and have got the required information you would obviously want to turn it off.

The command to do the same is

alter system set events 'immediate trace name cursortrace level 2147483648, addr 1';

However this does not work as I realized today when I was diagnosing multiple versions created for pl/sql procedure calls with ref cursors as arguments and the trace almost filled up my udump. New sessions spawned have entries such as the below
for any cursor executed.

CUR#2 XSC 0xxxxxxxx CHILD#0 CI 0xxxxxxx CTX (nil)

To fix this issue you need to apply the fix for unpublished bug

5555371 NO WAY TO TURN OFF TRACE AFTER SETTING CURSORTRACE EVENT

on top of 10.2.0.x

Another way to disable the trace would be to restart the instance.

Database Build Script "Greatest Hits"

Rob Baillie - Tue, 2007-09-04 09:54
I know its been a quiet time on this blog for a while now, but I've noticed that I'm still getting visitors looking up old blog posts. It's especially true of the posts that relate to "The Patch Runner". Many of them come through a link from Wilfred van der Deijl, mainly his great post of "Version control of Database Objects". The patch runner is my grand idea for a version controlled database build script that you can use to give your developers sandbox databases to play with as well as ensuring that your live database upgrades work first time, every time. It's all still working perfectly here, and people still seem to be interested, so with that in mind I've decided to collate them a little bit. basically provide an index of all the posts I've made over the years that directly relate to database build scripts, sandboxes and version control. So, Rob's database build script 'Greatest Hits': All of the posts describe processes and patch runners that are very similar to those that I use in my work every day. I started playing with these theories over 3 years ago now and there is no way I'd go back to implement database upgrades the way I did before. However, I'd LOVE to hear ideas on how things can be improved. I'd be amazed if my three year old thinking was still up to date! Technorati Tags: , , , , ,

Quick Pay Process

RameshKumar Shanmugam - Mon, 2007-09-03 21:32
  • For processing the payroll for a single employee we can use Quick pay
  • For employee who is leaving and for whom we need to pay by check or cash
  • When an employee is joined after the payroll run has been completed for the period we can use Quick pay to process only for this particular employee instead of completely rollback the Payroll Process and starting it again.

Follow the process to run the Quick Pay(N) Assignment > (B) Others > Quick Pay

  • Set the effective Date for the Quick pay
  • Date Paid and Date Earned will be defaulted to the effective date
  • Select the consolidation set
  • Select the Run Types (it varies from Legislation to Legislation)
  • Save the Quick Pay Definition
  • Submit the quick pay by pressing the start Run Button.
  • Status should be changed from un-processed to Complete.
  • If any errors fix the error and press the retry button
  • Once the Quick Pay run is completed we need to process the PrePayment
  • We can run the Prepayment from the Quick Pay window by just clicking the button start PrePayment Or combine it in the Batch PrePayments processing for the assignment’s Payroll (this can be used when a new employee is joined after the payroll run is completed so we can run the quick pay separately for this employee and combine it with the batch or the prepayment.)

Try it out!!!

Categories: APPS Blogs

Summer Reading

Mary Ann Davidson - Mon, 2007-09-03 12:41

It's not often that I read a headline that almost causes me to spill my morning coffee and then weep, but I confess I had that reaction to an article making the papers this week. The article said 1 out of 4 adults read no books in the past year. I find that as astonishing as if I had read that 1 out of 4 adults hates dogs. (Who doesn't like dogs?)

 

I really wonder what those 25% of adults do with their free time, because almost every minute of mine (not spent surfing) is spent reading. As long as I can remember, I have had a book addiction severe enough that I've thought about joining a 12-step program for Bookaholics: People Who Read Too Much. I started early and have never really slowed down. (I learned to print my name at age 4 only so I could get my very own library card.)

 

As an adult, I have books stacked two and three deep in all my bookshelves. I have them on top of the radiators (which I turn off - I'd rather shiver in winter than ruin a book). They are stacked under tables (with long tablecloths to hide the stacks) and on and under my nightstand. Under my coffee table and under my sofa, too. My mother expects to read that my apartment in San Francisco has sunk into San Francisco Bay from the weight of all those books. It's not quite as bad in Idaho, because a) I have more room! And b) I don't buy books so much anymore, but I devour whatever the Ketchum Community Library has - and they are really well-stocked.

 

It's not that I think I am better or smarter than those 1 out of 4 adults. (My dog differs but he is allowed his prejudices, which I happen to know can be bought for a couple of Greenies.) But reading has opened my eyes to worlds, histories, thoughts and dreams I never would have experienced otherwise. I can't imagine a life without books. I don't even go out of the house without reading material, because waiting in line (or at an airport or at a cafe for a friend) is an opportunity to read just a few more pages.

 

I am also a "carrier." When I find a book I really like, I buy copies for friends and nudge them into reading them. (Only I keep forgetting what I have already bought my nephew, which is how he ended up with two copies of Goodbye to All That by Robert Graves and 2 copies of Stories of Hawai'i by Jack London. Sorry, Piers!) My buddy Elad and I have traded books and writers back and forth for a couple of years now and have been known to read the same book at about the same time to have someone to enthuse with (or alternatively, pick a book apart).

 

I simply don't understand people who don't read books.

 

Love of reading is not a function of educational level, either. I had a friend once who, despite two degrees (2 - count 'em - 2) from Stanford, freely admitted she had read no books since graduation. (Yeesh.)  She marveled at the number of books in my apartment and wanted to know if I actually read them. It was all I could do not to say, "No, &lt;name omitted>, they are wall insulation. Of course I read them!" No matter how impressive your educational credentials, a university education is just the beginning of knowledge. You learn, or should learn, to teach yourself. Books are the key. Reading also helps give you a broader perspective on the world than you might otherwise have. Let's face it; technology (for example) is not the be-all and end-all we sometimes think it is. (A shock, I know, to some in Silicon Valley who think history began with the transistor.)

 

I guess there is a security aspect to all this, somewhere, somehow. For a start, I do read a lot of military history and that helps me look at IT security from the posture of a warrior. I haven't personally worn a "war suit" for years or done field exercises/war games in years, but I think that reading military history helps give me a different slant on computer security. It also helps me connect with customers (like the Defense Department) because I can speak their language. I think about computer networks like battlefields and I look at battles of the past to think about IT defense.

 

Also, so much of history is military history. It grieves me to no end that all kids seem to learn about WWII anymore is synopsized in the following: "We interned the Japanese: That Was Bad. Women (like Rosie the Riveter) entered the workforce: That Was Good."Nothing about Midway, Guadalcanal, Iwo Jima, Okinawa, Stalingrad, Kursk. Why we fought, who fought, how the world changed and how the world was - for better or worse - shaped by war. I don't think we can hope to understand the present or shape the future if we do not understand the past. We also need to try to learn from the lessons of the past:  it history does not repeat itself, as the dictum goes, it sure does rhyme.

 

(Another "security" aspect to my reading: I would not have known, without reading Miracle at Midway by Gordon Prange, that the victory at Midway was made possible in no small part because the US had broken Japan's JN25 naval cipher. Code breaking was critical in other aspects of the war, too, as anyone who has read about Enigma knows. The lesson here is that you should never assume your codes are unbreakable unless you are using one-time pad ciphers and not reusing the pads.)

 

So, I offer below (because it is, technically still summer, and because kids aren't the only ones who need reading lists), a smattering of books in no particular order I'd recommend for anybody's reading list.

 

Oracle 10g Performance Tuning Tips and Techniques by Rich Niemic

 

Well, it's not fiction, and it's not history, and normally, I would not put a tech book on my reading list. All that said, it's actually easy to recommend this book because I am a) not a performance tuning person and b) not someone who was ever even remotely interested in performance tuning. I am, however, a charter member of the Rich Niemic Fan Club (Rich is the former president of the Oracle User Group and a big Oracle security friend). Rich asked me to provide a quote for his book and, in reading the sections he sent me (I wanted to make sure the book was great before I gushed over it publicly), I became really interested in performance tuning. Who knew? This book is very readable, it's really interesting, and I can guarantee you that your Grandma from Des Moines will be a performance tuning fool after reading this book. Life's too short to buy some dull Oracle-related tome that you will never read and that won't help you. (Especially when you can buy this one that's fun to read and will most definitely help make your database crank!)

 

Sea of Thunder by Evan Thomas
Last Stand of the Tin Can Sailors by James Hornfischer.

 

Many of us spend oodles of money to go to the movies to see battles of derring do between good guys and bad guys, or defenders of the universe vs. evil aliens. Save the $10 ($15 with popcorn) and buy one or both of these books to read about real heroism against the odds. Both books describe the Battle off Samar in the Philippines in 1944, a story that should be told and retold as long as acts of heroism are recounted through generations. This is the story of the men of Taffy3 (destroyers, general purpose or "Jeep" carriers and destroyer escorts) against the Japanese armada, including the Yamato, the largest battleship ever built. Think about a bunch of determined gnats going up against an angry tiger - and winning!

 

I confess to having more than a passing interest in this story: Mick Carney, ADM Bull Halsey's chief of staff (and family friend) is liberally quoted in Sea of Thunder and another family friend is quoted in Last Stand of the Tin Can Sailors. I have read both books, more than once, and as I close the back cover, I always say, "Where do we get such men?" There is also (gotta work that security angle in here) a retelling of the incredible but true story of an well-known encryption blunder: the infamous message from ADM Chester Nimitz to ADM Bull Halsey: "Where is task force 34 the world wonders?"  (The last three words were message padding, a slight rip-off of the Charge of the Light Brigade by Tennyson and not intended to be part of the message; Halsey read it, thought Nimitz was ridiculing him, and had a fit. He then turned his carriers around from pursuing the Japanese and headed back to where Taffy3 was in the thick of battle. Some LTJG was cashiered for that mistake, one suspects.)

 

Power, Faith and Fantasy: A History of America in the Middle East: 1776 to the Present by Michael Oren

 

The Middle East is much in the news these days, and it is interesting to note how long America has been involved in the Middle East: since the origins of the country. There are a lot of amazing factoids in here, such as: one of the reasons the United States has a strong constitution (supplanting the Articles of Confederation) is because of the Barbary pirates (and that at one point, the United States was giving up 25% of our GDP in tribute  - better known as "blackmail" - to the Barbary pirates). The states realized that individually, they could not raise a strong navy, but a strong centralized government could, and voila - we have a strong central government and the beginning of US naval power. (Ever wonder about that line in the Marine Corps Hymn: "...to the shores of Tripoli?" That came from the war against the Barbary pirates.)  A really interesting read and a view of history you won't readily find anywhere else.

 

A Peace to End All Peace: The Fall of the Ottoman Empire and the Creation of the Modern Middle East by David Fromkin

 

If you want to know a lot of why the Middle East is the way it is, you need to understand how the borders got drawn and by whom. For that, you need to at least go back as far as the first World War and the dissolution of the Ottoman Empire. This book tells you almost more than you want to know about the subject, but it is thorough and will explain a lot that you can't easily understand without reading history. I confess to having had an argument over the Middle East once (well, more than once - I'm obviously opinionated on a number of topics) and I threw out a lot of points related to "how the borders got drawn and who did all that, anyway?" The gentleman I was arguing with asked - in amazement - how I came by all that information, to which I responded (slightly censored version), "I read history." I should have said that I read this book. It's a worthy (and non-polemical) read, well researched and presented.

 

A Better War by Lewis Sorley.

 

For those of us of a certain age (if you lived through the late 1960s), reading about or discussing Viet Nam is a painful exercise. The author is a West Point graduate and a former intelligence professional and, well, you will have to read the book to have your myths shattered. It should be required reading for anybody before even thinking about discussing Viet Nam. The book is balanced, thoughtful, well researched, but an eye opener.

 

Pied Piper, Trustee from the Toolroom, Requiem for A Wren, In the Wet, aw heck, how about anything by Nevil Shute

 

I had the unfortunate experience recently of reading a really dreary modern piece of dreck...er...literature for my book group about loss (related to September 11), and I could not but contrast the heavy handed plot, the wandering, aimless prose and the thoroughly unsympathetic characters in that book with Requiem for a Wren by Nevil Shute (also published under the title The Breaking Wave), that I had just read. What comes through in his work (besides his keen interest in engineering, aviation, archeology and other topics) is the fundamental decency of his characters, many of whom are confronted with hard choices and with unspeakable losses, but who soldier on, anyway. I am pleased to say that the Ketchum Community Library has 13 works by Shute and I intend to read them all. (Several of his books have been made into movies, including No Highway (the movie version is No Highway in the Sky), Pied Piper and On The Beach.)

 

From a security aspect, No Highway talks about an engineer who is convinced that a plane is about to have a catastrophic failure. It also concerns the lengths he goes to to ground the plane before there is an accident. It is a lesson in integrity, risk, and the moral issues around how far one can go or should go to ensure safety.

 

The Poems of A.E. Housman

 

I have a Housman fetish. I once spent two years looking for his books in print - anywhere. It took me that long to find a (used) copy of his complete poems. Later that same year, I went to Blackwell's in Oxford, England (the Mecca for bibliophiles, or one of them) and they had three shelves of works by and about Housman. Sigh. I think the folks at Blackwell's are still washing the saliva off the floor from the amount of drooling I did there. There is no finer poet or one more capable of eliciting wistfulness from the reader. "To an athlete dying young" is a particular favorite (and should be familiar to you if you saw Out of Africa).

 

Jasper Fforde's Thursday Next books (The Eyre Affair, Lost in a Good Book, The Well of Lost Plots, Something Rotten, First Among Sequels)

 

Fforde is just as witty and silly (good silly) in person as his books are; I heard him speak recently about his latest book, First Among Sequels. His books are really hard to explain; they simply defy genre. I liked the review that said they are a combination of Buffy the Vampire Slayer, Harry Potter, and Monty Python. The books contain absolutely outrageous puns and amazing literary references (Jane Eyre is a character; so are Hamlet, Miss Havisham and Mrs.Tiggy-Winkle). Life is serious enough; sometimes you need to read something very smart but amazingly silly.

 

The Code Book by Simon Singh

 

Closing out with a security book of sorts. I confess to not being a technical security kahuna in a lot of ways, particularly in the area of cryptography (which is, let's face it, one of the sexier parts of security). The Code Book really explains cryptography, and the history of it, in a readable, interesting way. You can sit through a lot of truly dull lectures and presentations, or you can grab this book and happily read your way to being a whole lot smarter about crypto (and a lot more appreciative of code breakers and makers throughout history).

 

A few closing thoughts. Lest anyone think I am a stuffed shirt who only reads Meaningful Tomes, I freely confess that I have read more than my share of murder mysteries, suspense books, adventure tales, science fiction, children's books and things that don't always qualify as literature but are great reads. Some days, after a hard week at work, you want "mind candy" and not War and Peace (with apologies to Count Leo Tolstoy).  It is just not that hard to be one of the 75% of adults who read at least one book a year, so go for it.

 

Almost all of these books can be ordered from Amazon, Borders, Barnes and Noble and so on and so forth. Or, you can patronize your local independent bookseller (the ones who remember what you like). Or you can support your local library and borrow the book. (Libraries like donations, too.) Go get lost in a good book.

 

For more information:

 

1 in 4 adults read no books last year:

 

http://www.msnbc.msn.com/id/20381678/

 

Rich Niemic's book:

 

http://www.amazon.com/Oracle-Database-Performance-Tuning-Techniques/dp/0072263059

 

About Nevil Shute:

 

http://www.nevilshute.org/biblio.php

 

Jasper Fforde's web site:

 

http://www.jasperfforde.com/

 

About the Battle off Samar:

 

http://www.bosamar.com/

 

The Code Book:

 

http://www.simonsingh.net/The_Code_Book.html

 

Selected poems of A.E. Housman online at:

 

http://www.chiark.greenend.org.uk/~martinh/poems/housman.html#ASLxix

 

A review of A Better War:

 

http://findarticles.com/p/articles/mi_m0IBR/is_3_30/ai_67502116

 

More on "the world wonders" padding screwup:

 

http://en.wikipedia.org/wiki/The_world_wonders

 

A really great biography of Halsey by the late E.B. Potter (former professor emeritus at the US Naval Academy):

 

http://www.amazon.com/Bull-Halsey-Elmer-Belmont-Potter/dp/1591146917

 

Who also wrote a great book on Nimitz:

 

http://www.amazon.com/Nimitz-E-B-Potter/dp/0870214926

 

DISPLAY, Audience, IE and page cannot be displayed

Fadi Hasweh - Sun, 2007-09-02 07:34
For the last 45 days my end user faced an issue accessing the following path
Audience super user responsibility ->AUDIENCE ADMINISTRATION DASHBOARD and then click link------->Audience Workbench

It will work fine if the user don’t have and lists attached to his workbench, if he created a list he will not be able to access the audience workbench, there will be no errors but after 7 minutes the user will receive the page cannot be displayed and no errors in the error_log or jserv.log.

After a long investigation and many OWCs and after creating a bug a smart engineer form oracle support (libby) and from her first update figure out that it might be a display issue since accessing the (Audience Workbench) when a list is attached the page will draw a chart for that list, so she gave me the following update to try

1.Please create a small jsp say testXServer.jsp and place it in the OA_HTML directory for the env and point your browserto it. If the DISPLAY setting is okay you will see a message stating the same, else you will see an exception.

please check the note to get the code for the test file (note 456424.1)

2. Invoke the jsp using the following url :http://.us.oracle.com:/OA_HTML/testXServer.jsp

3. If the DISPLAY is pointing to a valid xserver then the page will come up very quickly with the success message

When I tried the page it returned the page cannot be displayed error so I figured out that there is something with the display, I fixed the display issue and now every thing is working fine.

A new note has been created under metalink with the number (456424.1) for that issue.

Hope that helped
Fadi

Configure SSO(single sign-on) with Apex

Pankaj Chandiramani - Fri, 2007-08-31 01:25

I am loving the experience with Apex , we plan to have couple of new application using APEX , for that i neeed to configure SSO with apex .

It can be done in 3 steps :

  1. Creating partner app
  2. Installing sdk (You need to get sso sdk from midtier)
  3. Configuring apex for sso(You need to login a zip file from here)
For step 1 , you need below :
HOME URL : http: // apex_server_name/pls/apex
Success URL : http: // apex_server_name/pls/apex/wwv_flow_custom_auth_sso.process_success
Log Out URL : http: // apex_server_name/pls/apex/apex

Note : not covering the actual creation process , pls look at "Creating Partner App in AS admin guide"

For Step 2:
First unlock the flows schema according to the apex version you have .
SQL> alter user flows_030100 account unlock; (If your Apex version is 2.0.0.0.0 change acccordingly)

Now you need to copy sso sdk from the midtier , Login to Middle tier $ORACLE_HOME/sso/lib
copy ssosdk902.zip , unzip it . It creates some sql scripts & packages .

Login to db as user flows_030100 & run loadsdk.sql
SQL>@loadsdk.sql (Make sure you are executing under flows_XXXX)

Above will load the sso schema into flows account.

For Step 3:
Now we need to wire sso & apex schema(flows_xx)

SQL> @regapp.sql
It will prompt you for
Enter value for listener_token: HTML_DB:Hostname:7777
Enter value for site_id: enter_value_returned_while_registering_sso
Enter value for site_token: enter_value_returned_while_registering_sso
Enter value for login_url: http: // apex_url_here/pls/orasso/orasso.wwsso_app_admin.ls_login
Enter value for encryption_key: enter_value_returned_while_registering_sso
Enter value for ip_check: N

These above value you will get from Registring as partner app in step 1

Now configure authentication packages under flows_XXX schema
You need to login a zip file from here:
Unzip & get 2 files , execute as below

SQL> @custom_auth_sso.sql
SQL> @custom_auth_sso.plb

Finally you should lock flows_XXXX schema & grant execute permission to public user
SQL> alter user flows_020200 account unlock;
SQL> grant execute on wwv_flow_custom_auth_sso to APEX_PUBLIC_USER;

All set , once you use the authentication schema as sso , you can see the login page .

Categories: DBA Blogs

Some interesting and informative blog postings

Peter Khos - Thu, 2007-08-30 01:26
Some of the recently added postings caught my interest and I thought I should point them out in the event that you might not have seen them.Coskan's post on recovery, "When you lose your controlfile backups". This would be a good interview question.Tanel Poder's posts on "Advanced Oracle Troubleshooting Part 1 and Part 2".Laurent Schneider on "Cascade Delete".Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com0

Rails patch for Oracle CLOB defaults

Raimonds Simanovskis - Wed, 2007-08-29 16:00

If you are using Rails 1.2.3 with Oracle database then you might find that text attributes (which map to Oracle CLOB data type) get invalid default values – e.g. you might find that when you create new record it will get “empty_clob()” as default text attribute value.

I found out that this issue is corrected in current edge Rails. As I still primarily use Rails 1.2.3 I created the following patch according to the changes that are done in edge Rails. You can put it in environment.rb file or better put into a separate file and require it in environment.rb file.

# RSI: text defaults handling from http://dev.rubyonrails.org/ticket/7344 & http://dev.rubyonrails.org/changeset/6090
module ActiveRecord::ConnectionAdapters
  class OracleAdapter
    def quote(value, column = nil) #:nodoc:
      # RSI: patched
      if value && column && [:text, :binary].include?(column.type)
        %Q{empty_#{ column.sql_type.downcase rescue 'blob' }()}
      else
        super
      end
    end

    def columns(table_name, name = nil) #:nodoc:
      (owner, table_name) = @connection.describe(table_name)
      table_cols = <<-SQL
        select column_name as name, data_type as sql_type, data_default, nullable,
               decode(data_type, 'NUMBER', data_precision,
                                 'FLOAT', data_precision,
                                 'VARCHAR2', data_length,
                                  null) as limit,
               decode(data_type, 'NUMBER', data_scale, null) as scale
          from all_tab_columns
         where owner      = '#{owner}'
           and table_name = '#{table_name}'
         order by column_id
      SQL
      select_all(table_cols, name).map do |row|
        limit, scale = row['limit'], row['scale']
        if limit || scale
          row['sql_type'] << "(#{(limit || 38).to_i}" + ((scale = scale.to_i) > 0 ? ",#{scale})" : ")")
        end
        # clean up odd default spacing from Oracle
        if row['data_default']
          row['data_default'].sub!(/^(.*?)\s*$/, '\1')
          row['data_default'].sub!(/^'(.*)'$/, '\1')
          # RSI: patched
          row['data_default'] = nil if row['data_default'] =~ /^(null|empty_[bc]lob\(\))$/i
        end
        OracleColumn.new(oracle_downcase(row['name']),
                         row['data_default'],
                         row['sql_type'],
                         row['nullable'] == 'Y')
      end
    end

    # RSI: added
    def add_column_options!(sql, options) #:nodoc: 
      # handle case of defaults for CLOB columns, which would otherwise get "quoted" incorrectly 
      if options_include_default?(options) && (column = options[:column]) && column.type == :text 
        sql << " DEFAULT #{quote(options.delete(:default))}"  
      end 
      super
    end

  end
end
Categories: Development

OOW 2007

Peter Khos - Wed, 2007-08-29 12:02
It looks like I won't be attending this year's OOW as my role within my organization has changed. One of the other managers will attend instead. Darn! I might end up going to Orlando for the annual Gartner Symposium instead so if any of you readers are planning to attend, drop me a line and maybe we can organize for a meetup.Peter Khttp://www.blogger.com/profile/14068944101291927006noreply@blogger.com0

Pages

Subscribe to Oracle FAQ aggregator