Coskan’s Approach to Oracle

August 14, 2009

Blogroll Report 07/08/2009 – 14/08/2009

Filed under: Blogroll Report — coskan @ 4:46 pm

<—- Blogroll Report 31/07/2009 – 07/08/2009

This week was technically ritch and these are my selections.

1- How to measure redo log generation ?

Ben Prusinski – DBA Quick Tip of the week: Measuring Redo Generated

2-How to solve ORA-01620 for RAC standby for non-RAC primary?

Chandra Pabba – ORA-01620 while creating a RAC standby for non-RAC primary

3-How to speed up Single instance RAC convertion process via RCONFIG ?

Sabdar Syed – How we faster the process of converting a non-ASM single-instance database to RAC database with ASM using RCONFIG tool?

4-Quick VPD for beginners

Paul M.Wright – VPD vite

5-Information about shared pool freelists

Riyaj Shamsudeen – Shared pool freelists (and durations)

6-How does optimizer behaves when where clause is not in index but index columns in order list ?

Randolf Geist – Optimizer cleverness

7-How to use convert command to copy files from ASM to filesystem?

Marcin Przepiorowski – ASM datafile operation in NOMOUNT

8-How to avoid invalid packages message before upgrade to 11G?

Mike Dietrich - Drop SYS.PLAN_TABLE$ prior to upgrading

9-How Oracle fast refreshes materialized views (MVs) of a single master table?

Alberto Dell’Era – Fast refresh of single-table materialized views – algorithm summary

10- How to decrypt endpoint_value in dba_tab_histograms

Martin Widlake – Decrypting Histogram Data

11- Workarounds for ITL related index growth

Jonathan Lewis – Index Explosion-2

12- How  DBMS_STATS.SET_*_PREFS  in 11G ?

CBO Architects Group- Understanding DBMS_STATS.SET_*_PREFS procedures

13-Improvements on autoallocated segments in 11G

Christian Antognini -System Managed Extent Size – 11g Improvements

14-Quick guide to Troubleshooting Oracle Streams

Pat Lehane – Troubleshooting Oracle Streams/CDC

15-How to use DBMS_ROWID ?

Tom Kyte – On DBMS_ROWID, Parsing, and Sizing

16-Tutorial for RMAN scripts

Arup Nanda -Scripting Oracle RMAN Commands

17-How to clear session which were marked for kill?

Tanel Poder-Alter system kill session and ORA-00031: session marked for kill

18-How to copy schemas over database link in a procedure ?

Dani Rey-The gains and pains of dbms_datapump. Part I

19-How to send mail with CLOB attachment?

Ozay Akdora – Sending mail with CLOB attachement

20-11G upgrade problem caused by timesone file

Miladin Modrakovic - Issues with upgrade to 11g

21-ORA-00384 on 11G when you set minimum for db_cache_size

Steven Karam – Strange Behavior with MEMORY_TARGET

22-ORA-15099 with ASM disks larger than 2 TB

Jonathan Lewis – Tera-Bug

23-  How to automate usage of DBMS_DATAPUMP over network link

Daniel Ray-The gains and pains of dbms_datapump. Part I

Blogroll Report 07/08/2009 – 14/08/2009—->

August 13, 2009

DBMS_XMLGEN and bug

Filed under: Basics, Bugs, PL/SQL — coskan @ 4:35 pm

Last 2 weeks I had to deal with a post-upgrade problem (9.2.0.5 to 10.2.0.4) on a system which is not well tested before upgrade.  The issue was error stack below

ORA-06502: PL/SQL: numeric or value error:invalid LOB locator specified: ORA-22275

or for some records

ORA-29283: invalid file operation

ORA-06512: at “SYS.UTL_FILE”, line 488

Developer was insisting that this is a character set issue because of the first line of the error stack and I was saying it is impossible because both servers were UTF8 and only difference is NLS_NCHAR_CHARACTERSET which is never used anyway.  Because developer already gave the last decision, it was my turn to prove that this wasnt a characterset issue.

It took 2 weeks to understand the real problem (My development skills are not as good as my DBA skills and I did not get any proper help from development that’s why it took longer than it needs:) ) .  Basically application gets data from database converts it to XML (by DBMS_XMLGen) and writes it to a file (by DBMS_LOB).   We tried every possible solution we found from google and metaling for the error stacks and finally we understand that XML generation was the problem.  DBMS_XMLGEN.GETXML was not generating anything for some records but it was generating in 9i.  To be honest The error stack (was using  DBMS_UTILITY.FORMAT_ERROR_STACK and  DBMS_UTILITY.format_error_backtrace)   was not very clear  to me thats why I spent too much time with other things.
According to the bug numbers below DBMS_XMLGEN.GETXML is not working same in 10R2  as it is working on 9i. The way it handles nulls and special characters is buggy. Workaround it using DBMS_XMLQUERY instead.

Bug No. 8246403 NEED HINTS TO DEBUG “ORA-31011: XML PARSING FAILED” ERROR
Bug No. 8476233  DBMS_XMLGEN.GETXML RETURNS NULL IN 10.2.X, WORKED IN 9.2
Bug No. 6445329  GETXML() RETURNS NULL FROM QUERY AGAINST VIEW USING MAX() ON COLUMN WITH NULLS

Bug No. 8246403 NEED HINTS TO DEBUG “ORA-31011: XML PARSING FAILED” ERROR

Bug No. 8476233  DBMS_XMLGEN.GETXML RETURNS NULL IN 10.2.X, WORKED IN 9.2

Bug No. 6445329  GETXML() RETURNS NULL FROM QUERY AGAINST VIEW USING MAX() ON COLUMN WITH NULLS

This is the demo to show the different behaviour (taken from metalink ).

in 10G


SQL> create table t as select cast(null as varchar2(12))  as x from dual;

Table created.

SQL> select count(distinct x) x from t;

         X
----------
         0

SQL> select dbms_xmlgen.getxml('select count(distinct x) x from t') x from dual;

X
----------------------------------------------------------------------------------------
-------------------------------------------------

SQL> select dbms_xmlquery.getxml('select count(distinct x) x from t') x from dual;

X
----------------------------------------------------------------------------------------
-------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <X>0</X>
   </ROW>
</ROWSET>

in 9i

SQL> create table t as select cast(null as varchar2(12))  as x from dual;

Table created.

SQL> select count(distinct x) x from t;

         X
----------
         0

SQL> select dbms_xmlgen.getxml('select count(distinct x) x from t') x from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-24347: Warning of a NULL column in an aggregate function

no rows selected

SQL> select dbms_xmlquery.getxml('select count(distinct x) x from t') x from dual;

X
--------------------------------------------------------------------------------------
-------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <X>0</X>
   </ROW>
</ROWSET>

Lessons Learned.

1-Do not trust “we tested its ok”  and Try to understand the application and try to ask developers what packages they are using so you can search for possible bugs before you upgrade the system.

2-After 1 day of struggling, Search metalink first  for a possible bug.  Stop messing around google

August 12, 2009

Blogroll Report 31/07/2009 – 07/08/2009

Filed under: Blogroll Report — coskan @ 2:32 pm

<—- Blogroll Report 24/07/2009 – 31/07/2009

It is a bit late but I had decent excuses like post upgrade problems (as usual on a system which wasnt tested properly when it was test db )

1- Why not to trust ASH.TIME_WAITED ?

Vitaliy Mogilevskiy- Making sense of ASH TIME_WAITED Units

2- Restrictions when using union all with Fast refreshable materialized views

Rob Van Wijk- Fast refreshable materialized view errors, part four: union all MV’s

3- Reasons of big SYSTEM tablespaces

Martin Widlake- Why is my SYSTEM Tablespace so Big?

4- Oracle on Linux in Windows without virtualization tools.

Marcin Przepiorowski- andLinux and Oracle

5- How does Oracle fast refresh materialized views containing only joins of master tables

Alberto Dell’Era- fast refresh of join-only materialized views -algorithm summary

6- How to increase performance for data unloading from Oracle to other vendors via Oracle Heterogeneous Services

Ilmar Kerm- High performance data unloading from Oracle to ODBC database

7- Example of diagnosing a multipath problem

Martin Decker- Multipathing Configuration issue waiting to happen

8- Library Cache Pin wait caused by PL/SQL compalition

Kubilay Kara – Library Cache Pin Waits during PL/SQL compilation

9- Why export import is not reliable for detacting corrupt data blocks.

Jared Still- Detecting Corrupt Data Blocks

10- How to solve installation of grid control agent problem on active passive microsoft cluster environment

Alex Fatkulin – How to install Oracle Grid Control Agents on a Windows failover cluster with no downtime

11- ORA-4031 when there is plenty of free space in SGA.

Riyaj Shamsudeen- ORA-4031 and Shared Pool Duration

Oracle Forums

1- one block appears 54K times as cr in v$bh

Blogroll Report 24/07/2009 – 31/07/2009 –>

August 1, 2009

Blogroll Report 24/07/2009 – 31/07/2009

Filed under: Basics — coskan @ 1:53 am

<—- Blogroll Report 17/07/2009 – 24/07/2009

I was very busy this week and I could only find time to write this weeks blogroll after a big migration. I felt I have to write as I promised, and by this way I read all the post in my to-read list in 1 day. These summary series keeps me up-to date with blogosphere I hope it works for you too

1- All about Critical Patch Update preparation process

Eric Maurice – Ensuring Critical Patch Update Quality

2- 11G and Using Hugepage

Kevin Closson -

3- ITL effect on index space

Jonathan Lewis- IQ2 – Answers

Jonathan Lewis – Index Explosion

4- How to refresh test database from production with incremental backups and standby database options?

Ilmar Kerm – Refreshing test database from production using incremental backups

5-How to sort in different languages with index access. ?

Ilmar Kerm – Using linguistic indexes for sorting in Oracle

6-How to diagnose ORA-04030 ?

Dion Cho- Playing with ORA-4030 error

7-Troubleshooting RAC connectivity issues with VIP

Alex Gorbachev – Pythian Video: Oracle RAC VIP’s — Troubleshooting Connectivity Issues

8- How does PLAN_HASH_VALUE generated and how to generate hash values.?

Randolf Geist – PLAN_HASH_VALUE – How equal (and stable?) are your execution plans – part 2

9- How to identify PID/SPID of killed session in 11G?

Miladin Modrakovic- Identifying PID/SPID for killed session in 11g

10- How to Attach a SQL Profile to a Different Statement?

Kerry Osborne -How to Attach a SQL Profile to a Different Statement

11-Why Isn’t Oracle Using My Outline / Profile / Baseline?

Kerry Osborne -Why Isn’t Oracle Using My Outline / Profile / Baseline?

12- Why A-Rows is 0 for DMLs ?

Christian Antognini -A-Rows and DML Statements

13- How to find choked table stats?

Martin Widlake-Automatic Statistics Gathering Fails #3

14- Simple number formatting with to_char

Jeffrey Kemp – Great number formatting trick

15-How does delete work with merge in 10G?

Alex Nuijten -DELETE in the MERGE statement

16- How to use AWR differences Report- Why toad leaves Parallel Slaves in use (Check comments) ?

Doug Burns – AWR Differences Report

17- Function based indexes and hidden stats

Dion Cho-Function based index and suspicious filter predicates

18- Function based index is not used with Or-Expansion and query rewrite workaround with restrictions

Dion Cho- Function-based Index and Or-Expansion

19-Using check all repair clause of alter diskgroup  to release space

John Hallas-http://jhdba.wordpress.com/2009/07/30/asm-space-marked-as-internal/

20- Review of Oracle Metasploit tool

Alexander Kornbrust -Oracle Metasploit Presentation

21- Why DBA_TAB_PRIVS is not enough and workaround ?

Paul Wright -DBA_OBJ_PRIVS

22- How to find table creation order with Foreign key relations (non circular)?

Frank Zhou-How to determine the table creation order in SQL

23-How to limit user connection times intervals?

Emre Baransel-Limit User Connection Based On Time Interval

24-How to set up Network ACL in 11G?

Don Seiler – Setting up Network ACLs in Oracle 11g… For Dummies

Blogroll Report 31/07/2009 – 07/08/2009 ->

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 203 other followers