Coskan’s Approach to Oracle

December 31, 2009

Summary for 2009, Plans for 2010

Filed under: Diary — coskan @ 5:44 pm

2009  was a bit silent year for me I did not find interesting topics to write, but I read too many interesting and new topics as you follow from blogroll report posts and book reviews. I think I did a good job with blogroll reports  by them. I now have a very nice indexed Oracle Blogosphere for half of 2009. Blog reached over quarter million hits (when I say 250 000 it is not looking that big :) )  with average 400 visits a day.   By your support,  blog provided over 2500 gallons of clear water for people around the world,  I want to thank you all who visited and supported the social vibe link at the bottom of the page.

From career perspective, In 2009 I mostly focused on performance tuning and backup recovery.  I fully understood how Oracle Engine works in terms of performance management. I quit using TOAD and started to use sqlplus for %90 of my daily routine. I love  Toad but i can’t stand slowness of it and after Tanel Poders script library plus my own scripts I find it far more easy to use sqlplus.

Because of dealing with performance,  I started to be  more  obsessive about anything running slow and this obsessiveness made me quit Windows OS  start using Ubuntu 9.10 Karmic Koala as desktop. After 3 months of usage, I am quite happy about my decision. By Running Ubuntu on  my new Quad Code CPU and 8GB ram desktop I can say that I now have my dream speed and lab. Migration was not painful as much as I expected I can do 95% + more of the things I was able to do on windows,  I  wish I have the same option with the laptop I am using for job but no chance.

I also quit firefox and sage rss reader and start using chrome as browser  and google reader as reader with igoogle I highly recommend this combo to all of you.

For networking , I am still a bit shy when it comes to socializing at user group events. Normally I am a very social bloke, but I have a problem of introducing myself at meetings :(  it is something i need to work on harder.

I always had a fear of giving  presentation even in Turkish and to sort  this problem, I got a course for presentation skills in 2009 and I learned all the tricks of a good presentation. I also did a presentation at the course and  I proved to myself that I am able to do presentation in English.  Now I need a good topic to prepare a presentation to test my new skills.

I think thats about it for 2009 summary, and  now it is time to talk about 2010 plans.

For the blog, I am planning to share more information in this blog from the sources I follow and read and from my own researches on top of them. I  want to write a tool maybe a plugin for swingbench to generate common performance problems for new DBAs to test their troubleshooting skills. I also want to write  possible crash  recovery scenarios series.

For Oracle knowledge, I plan to learn Streams and Analytic functions. It is a shame for me that I never really cared much about these two.

For the books,  I plan to read the ones below and I hope upcoming Apple Tablet will be affordable price to be my ebook reader.  I wish I will finish at least half of them.

  • Practical Oracle 8i from Jonathan Lewis- I am in the middle of this book and what I discovered so far is that most of the information Jonathan shares from his blog is based on this book. I can say it is a must.
  • Expert Oracle Practices Oracle Database Administration from the Oak_Table- I read two chapters so far and to be honest it is not as good as I expected but book is in alpha stage so my thoughts can still change.
  • Scaling Oracle 8i by James Morle
  • SQL Tuning by Dan Tow
  • Relational Database Index Design and the Optimizers by Tapio Lahdenmaki and Michael Lech
  • RMAN Recipes for Oracle Database 11g by Darl Kuhn , Sam R. Alapati , Arup Nanda
  • Oracle Data Guard 11G Handbook by Larry Carpenter , Joseph Meeks , Charles Kim , Bill Burke , Sonya Carothers , Joydip Kundu , Mike Smith , Nitin Vengurlekar
  • Linux Recipes for Oracle DBAs by Darl Kuhn, Charles Kim, Bernard Lopuz
  • Secrets of the Oracle Database by Norbert Debes
  • Oracle Automatic Storage Management: Under-the-Hood & Practical Deployment Guide by Nitin Vengurlekar , Murali Vallath , Rich Long
  • Pro Oracle Database 10g RAC on Linux: Installation, Administration, and Performance by Julian Dyke , Steve Shaw

I never be active enough at Oracle Forums and Oracle-L and they are also on top of my to-do list this year.

For linux desktop , I need to get used to use vi for every kind of text editing and because I don’t have system admin group luxury at home, I need to learn more about managing linux as a system admin.

I will start to give internal presentations to my team mates if it works maybe you can watch a presentation of me after second half of the year.

Well these are my plans of 2010 and I hope next year on 31st of December I will still be here and telling you that all of my plans went well.

Wish you and for the ones you love,  a very good year.

December 23, 2009

Blogroll Report 04/12/2009-11/12/2009

Filed under: Blogroll Report — coskan @ 1:52 am

<—- Blogroll Report 27/11/2009 – 04/12/2009

1-How can Oracle trace file be misleading with reported wait times ?

Joze Senegacnik-Measurement Error Trap In Trace File (event 10046)

2-How does One Node RAC work?

Marcin Przepiorowski-Oracle RAC One Node Part 1

3-New TEMP_SPACE_ALLOCATED column of ASH

Timo Raitalaakso-SQL Tuning reading plans and temp usage

4-How to configure Glance on HP for Oracle?

John Hallas-Using Glance to see Oracle process usage

5-How to backup archivelogs whenyou have connected to RMAN CATALOG server, which connects to both Standby and Primary database?

Lou Donghua-How to backup archivelog in Dataguard environment

6-How does CPU costing model changes access plan with higher cost of FTS comparing to I/O cost model?

Richard Foote-The CPU Costing Model: A Few Thoughts Part I

7-How does _optimizer_extended_cursor_sharing_rel causes reparsing in 11GR2?

Grégory Guillou-Hidden and Undocumented “Cardinality Feedback”

8-How does scalar functions behave in SQL’s with different type of calling?

Martin Widlake-Scalar Functions Not Behaving as Scalar Functions

9-SYS_GUID() vs Sequences

Ron Van Wijk-SYS_GUID()

10-How to configure persistent device names for the OCR and voting disks ?

Ronny Egner-Persistent device naming (or binding) for running RAC on Linux (10g R2 clusterware and above)

11-How to configure logminer with flat files option?

Martin Bach-My first contact with the log miner

12-How to recreate standby controlfile when using ASM?

Martin Bach-Recreate the standby controlfile when using ASM

13-How to filter SQLs in V$SQLAREA/V$SQL with COMMAND_TYPE values?

Martin Widlake-COMMAND_TYPE values

14-How to enable DDL Logging to Alert Log in 11GR2?

Luo Donghua-11g new feature: enable_ddl_logging parameter

15-How to check applied CPU and bundled patches inside database?

Luo Donghua-How to check whether CPU and/or Windows Bundle patch applied to database

16-How to move materialized views and materialized view logs between tablespaces?

Anand Prakash-Alter Materialized View Move

17-How does Correlated Partition Exclusion performs in practice?

Martin Widlake -Ensuring Correlated Partition Exclusion

18-How to solve ORA-12034 on remote host ?

Howard Rogers-Broken Materialized Views and ORA-12034

19-How to track down parameter changes via AWR and how to estimate AWR storage needs?

Kerry Osborne-Tracking Parameter Changes

20-How does rownum filter can effect execution plans ?

Charles Hooper-CBO oddities in determining selectivity on NVARCHAR2 data type

22-What does SYS_OP_C2C internal function shown in explain plan predicate information, do?

Joze Senegacnik-What is the purpose of SYS_OP_C2C internal function?

23-How does 11GR2 APPEND_VALUES hint do with redo generation?

Surachart Opun-APPEND_VALUES hint and Table NOLOGGING/LOGGING

Blogroll Report 11/12/2009 – 18/12/2009 –>

December 18, 2009

Blogroll Report 27/11/2009-04/12/2009

Filed under: Blogroll Report — coskan @ 12:13 am

<—- Blogroll Report 20/11/2009 – 27/11/2009

1-How to limit number of CPU for an instance with instance caging feature of 11GR2 ?
Christian Antognini-Instance Caging

2-How to diagnose undo problem ?
Kellyn Pedersen-Undo Brain Damage

3-How to solve invalid registry objects problem with upgrade and migration ?
Surachart Opun-Invalid Objects on SYS and ORA-04063: package body “SYS.DBMS_REGISTRY_SYS” has errors

4-How to sort Collections ?
Adrian Billington-Sorting Collections

5-How does Oracle Flash Cache work in 11GR2?
Guy Harrison-More on the Database Flash Cache

6-Alternative workarounds for plans suffering from bind peeking?
Martin Widlake-Dealing with Bind Issues

7-How to Create a Database Link in Another User’s Schema without password hacking?
Neil Johnson -How to Create a Database Link in Another User’s Schema

8-Workaround for Result Cache for Ref Cursor returning PL/SQL codes
Asanga Pradeep-Result Cache for Ref Cursor returning PL/SQL codes

9-How to unwrap PL/SQL?
Pete Finnegan-Unwrapping PL/SQL

10-How to solve interrupted drop disk from diskgroup in ASM ?
Martin Bach-Do not shut down ASM when dropping a disk

11-Diagnosing and solving a multipath problem with ASM. ([Device or resource busy] [16])
Martin Bach-Let there be multipathing (when there wasn’t before)

12-How to use V$SQL_MONITOR and V$SQL_PLAN_MONITOR views ?
Joze Senegacnik-V$SQL_MONITOR and V$SQL_PLAN_MONITOR

13-How to optimize table functions by cardinality feedback ?
Joze Senegacnik-Table Expressions, Cardinality, SYS_OP_ATG and KOKBF$

14-Service name operation logged in alert log during Data Pump on RAC
Surachart Opun-DATAPUMP with SYS$SYS.* service_names

Blogroll Report 04/12/2009 – 11/12/2009 –>

December 12, 2009

Blogroll Report 20/11/2009-27/11/2009

Filed under: Blogroll Report — coskan @ 7:04 pm

<—- Blogroll Report 13/11/2009 – 20/11/2009

1-How does ora_hash function works ?
Jonathan Lewis-ora_hash function

2-How to gather specific histogram size for a column?
Hemant K Chitale-SIZE specification for Column Histograms?

3-How to manage Oracle HAS in 11GR2?
Surachart Opun-Disable/Enable Automatic startup Oracle HAS

4-How does fast refresh of on-commit materialized views works on 11GR2?
Alberto Dell’era-11gR2: new algorithm for fast refresh of on-commit materialized views

5-Database Statistics Metalink notes compilation
Anthony Shorten-Keeping your database statistics up to date

6-How does SQL92_Security parameter works?
John Hallas-What does the SQL92_SECURITY parameter actually do?

7-How to use Binding sparse arrays in a FORALL statement?
Steven Feuerstein-Binding sparse arrays in a FORALL statement?

8-How does locally managed tablespaces work and third type of locally managed tablespaces?
Joel Goodman-How Good is your “Local” Management?

9-Make most out of partitions
Martin Widlake – Assisting Partition Exclusion – Partitions for Performance

10-How to use RMAN with DBMS_PIPE – managing rman via SQLPLUS?
Joel Goodman-The RMAN “PIPE” Interface

11-How does Automatic Degree of Parallelism work in 11GR2?
Uwe Hesse-Automatic DOP in 11gR2

12-How CPU costing model works with indexes?
Richard Foote-The CBO CPU Costing Model: Indexes vs. Full Table Scans

13-How to get row count of each partition of a partitioned table?
Jonathan Lewis-Counting

14-How to monitor direct NFS in 11G on Solaris?
Glenn Fawcett-Monitoring Direct NFS with Oracle 11g and Solaris… pealing back the layers of the onion.

15-How does zero size unusable indexes effect execution plans ?
Christian Antognini-Zero-Size Unusable Indexes and the Query Optimizer

16-11G Advanced compression vs basic compression
Luis Moreno Campos-The Difference between Online and Offline compression

17-New features of Result Cache in 11GR2
Uwe Hesse-Result Cache: Another brilliant 11g New Feature

18-Cross Platform upgrade and migration case study for large DB
Marko Sutic-Cross-platform migration – large Oracle 9i db from Solaris 64 to Linux 64

19-Connect-by vs the recursive subquery factoring
Rob Van Wijk-Recursive subquery factoring

Blogroll Report 27/11/2009 – 04/12/2009 –>

December 7, 2009

root.sh failed after ASM disk creation for 11GR2 Grid Infrastructure

Filed under: RAC — coskan @ 4:51 pm

Update 22/12/2009 After the first comment on the post I now know that  there is an easier way to deal with the problem.

How to Proceed from Failed 11gR2 Grid Infrastructure (CRS) Installation [ID 942166.1] (Last update is later than my post might be related :) )

Basically

Step 1: As root, run “$GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force” on all nodes, except the last one.

Step 2: As root, run “$GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force -lastnode” on last node. This command will zero out OCR and VD disk also.


Last 3 days I was a bit busy with installing Oracle RAC on Solaris 10 x64 on VMWare. I am planning to write a detailed documentation ,but I want to write an issue beforehand, which I managed to solve during the installation .

During grid infrastructure everything went fine till I ran root.sh script for cluster configuration. Script failed with the error stack below (I truncated the worked part)

# /u01/app/11.2.0/grid/root.sh
....
....
....
ASM created and started successfully.

DiskGroup DATA created successfully.

Errors in file :
ORA-27091: unable to queue I/O
ORA-15081: failed to submit an I/O operation to a disk
ORA-06512: at line 4
PROT-1: Failed to initialize ocrconfig
Command return code of 255 (65280) from command: /u01/grid/11.2.0/bin/ocrconfig -upgrade grid oinstall
Failed to create Oracle Cluster Registry configuration, rc 255
CRS-2500: Cannot stop resource 'ora.crsd' as it is not running
CRS-4000: Command Stop failed, or completed with errors.
Command return code of 1 (256) from command: /u01/grid/11.2.0/bin/crsctl stop resource ora.crsd -init
Stop of resource "ora.crsd -init" failed
Failed to stop CRSD
CRS-2673: Attempting to stop 'ora.asm' on 'solarac2'
CRS-2677: Stop of 'ora.asm' on 'solarac2' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'solarac2'
CRS-2677: Stop of 'ora.ctssd' on 'solarac2' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'solarac2'
CRS-2677: Stop of 'ora.cssdmonitor' on 'solarac2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'solarac2'
CRS-2677: Stop of 'ora.cssd' on 'solarac2' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'solarac2'
CRS-2677: Stop of 'ora.gpnpd' on 'solarac2' succeeded
CRS-2679: Attempting to clean 'ora.gpnpd' on 'solarac2'
CRS-2681: Clean of 'ora.gpnpd' on 'solarac2' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'solarac2'
CRS-2677: Stop of 'ora.gipcd' on 'solarac2' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'solarac2'
CRS-2677: Stop of 'ora.mdnsd' on 'solarac2' succeeded
Initial cluster configuration failed.  See /u01/grid/11.2.0/cfgtoollogs/crsconfig/rootcrs_solarac2.log for details

I tried to run root.sh again which I shouldn’t have done because it is documented not to do. (I have to confess that I did not read the installation document well)

The error stack was different like below

# /u01/app/11.2.0/grid/root.sh
Running Oracle 11g root.sh script...
.........
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
2009-12-06 22:57:05: Parsing the host name
2009-12-06 22:57:05: Checking for super user privileges
2009-12-06 22:57:05: User has super user privileges
Using configuration parameter file: /u01/11.2.0/grid/crs/install/crsconfig_params
CRS is already configured on this node for crshome=0
Cannot configure two CRS instances on the same cluster.
Please deconfigure before proceeding with the configuration of new home.

As you see it didn’t allow me to re-run it. I needed to find a way to deconfigure the configuration. After a quick search on official doc I found the way here.

According to the doc, all I needed to do is run the command below and re-run the root.sh

/crs/install/rootcrs.pl -deconfig

Here is what happened when I run deconfigure

2009-12-07 00:35:17: Parsing the host name
2009-12-07 00:35:17: Checking for super user privileges
2009-12-07 00:35:17: User has super user privileges
Using configuration parameter file: /u01/grid/11.2.0/crs/install/crsconfig_params
Oracle Clusterware stack is not active on this node
Restart the clusterware stack (use /u01/grid/11.2.0/bin/crsctl start crs) and retry
Failed to verify resources

Still wasn’t working ??? I tried force option and it seemed like it de-configured successfully (maybe :) )

# /u01/grid/11.2.0/crs/install/rootcrs.pl -deconfig -force
2009-12-07 00:39:13: Parsing the host name
2009-12-07 00:39:13: Checking for super user privileges
2009-12-07 00:39:13: User has super user privileges
Using configuration parameter file: /u01/grid/11.2.0/crs/install/crsconfig_params
PRCR-1035 : Failed to look up CRS resource ora.cluster_vip.type for 1
PRCR-1068 : Failed to query resources
Cannot communicate with crsd
PRCR-1070 : Failed to check if resource ora.gsd is registered
Cannot communicate with crsd
PRCR-1070 : Failed to check if resource ora.ons is registered
Cannot communicate with crsd
PRCR-1070 : Failed to check if resource ora.eons is registered
Cannot communicate with crsd

CRS-4133: Oracle High Availability Services has been stopped.
Successfully deconfigured Oracle clusterware stack on this node

It says it did successfully deconfigured but when I run the root.sh again I got this

Disk Group DATA already exists. Cannot be created again

Configuration of ASM failed, see logs for details
Did not succssfully configure and start ASM
CRS-2500: Cannot stop resource 'ora.crsd' as it is not running
CRS-4000: Command Stop failed, or completed with errors.
Command return code of 1 (256) from command: /u01/grid/11.2.0/bin/crsctl stop resource ora.crsd -init
Stop of resource "ora.crsd -init" failed
Failed to stop CRSD
CRS-2500: Cannot stop resource 'ora.asm' as it is not running
CRS-4000: Command Stop failed, or completed with errors.
Command return code of 1 (256) from command: /u01/grid/11.2.0/bin/crsctl stop resource ora.asm -init
Stop of resource "ora.asm -init" failed
Failed to stop ASM
CRS-2673: Attempting to stop 'ora.ctssd' on 'solarac1'
CRS-2677: Stop of 'ora.ctssd' on 'solarac1' succeeded
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'solarac1'
CRS-2677: Stop of 'ora.cssdmonitor' on 'solarac1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'solarac1'
CRS-2677: Stop of 'ora.cssd' on 'solarac1' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'solarac1'
CRS-2677: Stop of 'ora.gpnpd' on 'solarac1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'solarac1'
CRS-2677: Stop of 'ora.gipcd' on 'solarac1' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'solarac1'
CRS-2677: Stop of 'ora.mdnsd' on 'solarac1' succeeded
Initial cluster configuration failed.  See /u01/grid/11.2.0/cfgtoollogs/crsconfig/rootcrs_solarac2.log for details

On the mentioned logfile it says

2009-12-07 00:43:26: Executing as grid: /u01/grid/11.2.0/bin/asmca -silent -diskGroupName DATA -diskList /dev/rdsk/c1t1d0s1,/dev/rdsk/c1t2d0s1,/dev/rdsk/c1t3
d0s1,/dev/rdsk/c1t4d0s1 -redundancy EXTERNAL -configureLocalASM
2009-12-07 00:43:26: Running as user grid: /u01/grid/11.2.0/bin/asmca -silent -diskGroupName DATA -diskList /dev/rdsk/c1t1d0s1,/dev/rdsk/c1t2d0s1,/dev/rdsk/c
1t3d0s1,/dev/rdsk/c1t4d0s1 -redundancy EXTERNAL -configureLocalASM
2009-12-07 00:43:26:   Invoking "/u01/grid/11.2.0/bin/asmca -silent -diskGroupName DATA -diskList /dev/rdsk/c1t1d0s1,/dev/rdsk/c1t2d0s1,/dev/rdsk/c1t3d0s1,/d
ev/rdsk/c1t4d0s1 -redundancy EXTERNAL -configureLocalASM" as user "grid"
2009-12-07 00:43:30: Configuration of ASM failed, see logs for details

Basically it configures asm with asmca command. asmca utility does not have drop diskgroup option which makes it unusable for this situation. (there is deleteasm option but it does not work fine because it needs a working asm instance which wasn’t possible after failed root.sh)

I didn’t want to delete all CRS installation so I needed a way to remove diskgroup information from ASM disks?

All I needed was dd command to remove the disk header information from the devices.

I had 4 disk presented for that disk group so I used dd command for all of them (I am not sure maybe I needed only the firs device I need to check invaluable presentation of Julian Dyke about ASM Internals)

# dd if=/dev/zero of=/dev/rdsk/c1t2d0s1 bs=1024K count=100
dd: bad numeric argument: "1024K"
bash-3.00# dd if=/dev/zero of=/dev/rdsk/c1t2d0s1 bs=1k count=1000000
1000000+0 records in
1000000+0 records out
# dd if=/dev/zero of=/dev/rdsk/c1t1d0s1 bs=1k count=1000000
1000000+0 records in
1000000+0 records out
# dd if=/dev/zero of=/dev/rdsk/c1t3d0s1 bs=1k count=1000000
1000000+0 records in
1000000+0 records out
# dd if=/dev/zero of=/dev/rdsk/c1t4d0s1 bs=1k count=1000000
1000000+0 records in
1000000+0 records out

After this deletion I re-run the deconfigure script and re-run the root.sh. Everything worked fine without any problem at all. The story will continue with How to install 11GR2 RAC on Solaris 10 on VMware (give me a bit more time to finish)

footnoteSmilar issue reported on metalink for Linux ( ML 955550.1)

Sources used
Oracle® Grid Infrastructure Installation Guide 11g Release 2 (11.2) for Solaris Operating System

How to use Files in place of Real Disk Devices for ASM – (Solaris) by Jeff Hunter

How to rerun root.sh during initial installation of GRID Infrastructure. by RACHELP

December 1, 2009

UKOUG 2009- My Agenda

Filed under: Diary — coskan @ 5:32 pm

Everybody is posting about their agenda so  I think  it is my turn.

I’m pretty sure , I will come up with something to share with you.

 

10:05 – 12:05 – Practical Oracle Capacity Planning – Tanel Poder

12:35 – 13:35 – The Oracle Wait Interface is Useless (sometimes) James Morle-Tanel Poder

13:50 – 14:50 – Parallel Processing -Christian Antognini  or Latch and Mutex Contention Troubleshooting- Tanel Poder

15:15 – 16:00  Anatomy of a SQL Tuning Session Wolfgang Breitling

 

With this agenda, I have to miss  “How to backup and recover enormous databases”  by Husnu Sensoy  and “Good indexing-Show CBO where to go” by Piet de Visser (I hope I can catch his presentation at some of the SIGs,   according to me he is one of the best presenters of Oracle World I watched so far very entertaining and teaching) so I will be a bit sorry at the end of the day. I really hate to choose between parallel sessions :)

Hope to catch some of you there.

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 203 other followers