Coskan’s Approach to Oracle

July 27, 2009

When is my password file gets updated?

Filed under: Basics, Tips — coskan @ 3:51 pm

Till I read this oracle-l thread, I was thinking pasword file is only updated/touched when sys user password is changed however, it is not the only case. As you can see below it is updated/touched whenever a user password is changed, interestingly password file is not updated/touched for new user creations. My question is why oracle updates/touches the file for regular user passwords and why it doesn’t updates/touches for user creation ? If anybody knows can you please leave a comment.

SQL> host dir C:\oracle\product\product\11.1.0\db_1\database\PWDORACOS.ORA
 Volume in drive C is System
 Volume Serial Number is 303E-6F73

 Directory of C:\oracle\product\product\11.1.0\db_1\database

23/07/2009  00:01             2,560 PWDORACOS.ORA
               1 File(s)          2,560 bytes
               0 Dir(s)   5,201,625,088 bytes free

SQL> create user passw identified by passw;

User created.

SQL> host dir C:\oracle\product\product\11.1.0\db_1\database\PWDORACOS.ORA
 Volume in drive C is System
 Volume Serial Number is 303E-6F73

 Directory of C:\oracle\product\product\11.1.0\db_1\database

23/07/2009  00:01             2,560 PWDORACOS.ORA
               1 File(s)          2,560 bytes
               0 Dir(s)   5,201,625,088 bytes free

SQL> alter user passw identified by passw;

User altered.

SQL> host dir C:\oracle\product\product\11.1.0\db_1\database\PWDORACOS.ORA
 Volume in drive C is System
 Volume Serial Number is 303E-6F73

 Directory of C:\oracle\product\product\11.1.0\db_1\database

27/07/2009  16:47             2,560 PWDORACOS.ORA
               1 File(s)          2,560 bytes
               0 Dir(s)   5,201,625,088 bytes free

Footnote:explanation below came via mail from a reader and looks logical.
Anytime a password is changed Oracle opens the password file to check if the user is a dba user and if so it updates the file. If not, obviously oracle does not update it. When oracle opens the file to do the check, oracle does that in read/write mode rather than in read only mode. This is because if the user does happen to be a DBA user it will be more work by closing the file in read mode and reopening in read/write mode and then perform the update.

July 24, 2009

Blogroll Report 17/07/2009 – 24/07/2009

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

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

This was one of those weeks that you really need a summary of the blog activities, it was more than fruitful.
The most popular topic was new 11G feature Direct Path Access and they all were very interesting.
My personal choice of the week is Index Quiz series from Jonathan Lewis, they were very informative and I strongly recommend you, not miss any of the comments.

1- Direct path pros/cons and how to disable direct path access.

Miladin Madrakovic –Direct path reads and serial table scans in 11g

Christian Antognini-Impact of Direct Reads on Delayed Block Cleanouts

Dion Cho-Disabling direct path read for the serial full table scan – 11g

2- Indexes and ITL

Jonathan Lewis –
Index Quiz 1
Index Quiz 2

3- table stats Choked by Auto Stats Gathering Job.

Martin Widlake-
Automated Statistics Gathering Silently Fails
Automated Statistics Gathering Silently Fails #2
4- Explanation of min segment size – Richard Foote vs Myths
Richard Foote-Why A Segment Really Has To Be At Least 2 Blocks In Size

5- Negative effects of optimizer_index_cost_adj
Richard Foote-The CBO and Indexes: OPTIMIZER_INDEX_COST_ADJ Part II
6- Difference between NOT IN and NOT EXISTS with NULLs
Hemant K Chitale-The difference between NOT IN and NOT EXISTS

7- How to find out parameters and diagnostic events of specific session ?
Dion Cho – Spying on the other session

8- Case of failed Stored outlines in 11G
Dion Cho-Stored outline does not work – the stupidity of Oracle 11g

9- How to automate AWR report generation ?
Rajeev Ramdas Thottathil- Shell script to generate awr reports for a range of snapshots

10- What does not effect plan_hash_value generation ?

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

11- How to use logminer when flashback is not option ?

Uwe Hesse- Logminer: Not as modern as Flashback but still useful!

12 – Summary of most known PL/SQL features

Steven Feuerstein – Must Know Features of PL/SQL

13- How to automate test script generation for SQLs with bind variables ?

Kerry Osborne-Creating Test Scripts With Bind Variables

14- How to check possible bind variable needs ?

Alberto Dell’Era – Bind Variables Checker for Oracle – now install-free

15- Faster fixed table access.

Miladin Madrakovic-Accessing Fixed Tables using Direct Access

16- Explanation of Securefile deduplication in 11G

Liang Gang Yu –Oracle 11g SecureFile System-Part 1- Deduplication of LOBs

17- Solution to emctl “WARN http: snmehl_connect: connect failed to (hostname:): Connection refused (error = 111)” problem

Asanga – Enterprise Manager Error

18- How to inspect LUNs on Linux ?

Krishna Manoharan-Map Disk Block devices on a Linux host back to the SAN Lun

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

July 17, 2009

Blogroll Report 10/07/2009 – 17/07/2009

Filed under: Blogroll Report — coskan @ 3:09 pm

<—- Blogroll Report 03/07/2009 – 10/07/2009

Here are  this week’s technical highlights

1- Tablespace_name like _$deleted$ in dba_tab_partition Bug:8291493

Jeff Moss – $deleted$ tablespace names bug

2- Easy connect namins(EXCONNECT) with ORA-12504

Mark Williams – SQL*Plus, EZCONNECT, Password Prompt, and ORA-12504

3-Libray cache definition with libray example

Aman Sharma – Library Cache, A Distant Look….

4- CTAS and copied constraints

Laurent Schneider – CTAS and NOT NULL

5- How to do a clean XML DB installation

Marco Gralike – HOWTO: Do a clean XML DB installation…

6-  How to enable disable Row Shipping feature in>  (_enable_row_shipping parameter)

Mladin Madrakovic – Wide Table Select ( Row Shipping )

7- Review of July 2009 CPU

Integrity – Oracle Critical Patch Update July 2009 Pre-Release Analysis

8- Poor man’s data vault project

Chet Justice – DBA: Poor Man’s Data Vault

9- Proof of Transparent Data Encryption

Liang Gang Yu – Data Block Dump in Oracle – part 4 – vs. Transparent Data Encyption

10 -How to decode block dump with UTL_RAW

Dion Cho –Decoding block dump using UTL_RAW

11 – Restrictions on PL/SQL functions with DML content

Shailesh Mishra – Workaround the DMLs: PL/SQL function restrictions

12 – Why to use Autoallocate for tablespace growth

Hemant K Chitale – Sizing OR Growing a Table in AUTOALLOCATE

13 – How to encrypt compress and mask with Data Pump in 11G (New features)

Arup Nanda – Encrypt, compress, mask, and deliver information with Oracle Data Pump.

14 – How to avoid ORA-01652 after executing “alter table compress…”

Marko Sutic – How to avoid ORA-01652 after executing “alter table compress…”

15 – Demonstration for Delayed cursor invalidation for databases above 10G.

Harald van Breederode – Rolling Cursor Invalidation

16 – How to provide exact cardinality of a collection used in a TABLE() query, to CBO

Adrian Billington – Collection CARDinality

Oracle Forums  (Did not have time this weekend)

High library cache load lock waits in AWR

Unable to create public synonym

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

July 10, 2009

Blogroll Report 03/07/2009 – 10/07/2009

Filed under: Blogroll Report — coskan @ 6:40 pm

<—- Blogroll Report 26/06/2009 – 03/07/2009

This weeks technical post selections

1- How to get object name from using file# and block# by block dump

Dion Cho – Object Name from File and Block

2- How to run huge sql files from sqlplus by using FIFO

H.Tonguc Yilmaz – Oracleturk hints running huge sql files from sqlplus and deleting huge amount of data best-practices

3-  Performance of PLSQL Native Compilation

Pat Lehane – PL/SQL Native Compilation

4- Parallel execution interconnect performance on RAC systems. (When to use and when not to use parallel execution on RAC)

Greg Rahn – Oracle parallel execution interconnect myths and misunderstandings

5- How does optimizer_index_cost_adj parameter effects CBO decision when IO cost is active

Richard Foote – The CBO and Indexes optimizer_index_cost_adj Part I

6- How to diagnose SQL*NET tracefile with trcasst utility

Miladin Madrakovic – Troubleshooting SQL*NET

7-  Materialized Views for beginners

Uwe Hesse –Brief introduction into Materialized Views

8- atomic_refresh parameter and high redo generation with Meterialized Views

Amit Bansal – Mview Complete Refresh and Atomic_refresh parameter

9- How to manage Voting Disks in Oracle RAC

Brijesh Dubey – Voting Disk Management in RAC

10- Oracle bug –  gather_schema_stats fails with function based indexes

Amardeep Sidhu – GATHER_SCHEMA_STATS & ORA-03001: unimplemented feature

11- How to use cursordump for top truncated sql trace.

Tanel Poder – Identify the SQL statement causing those WAIT #X lines in a (top-truncated) sql tracefile

12- How to detect when a cursor was closed from SQL trace output?

Tanel Poder – How to detect when a cursor was closed from SQL trace output?

13- How to use/read/interpret Data Block Dump

Liang Gang Yu – Data Block Dump in Oracle – part 1 – how to dump block
Liang Gang Yu – Data Block Dump in Oracle – part 2 – how to read block dump file
Liang Gang Yu – Data Block Dump in Oracle – part 3 – object_id, data_object_id, block clean up

14- How to tune WHERE NAME LIKE ‘%ABC%’ queries with index hint

Laurent Schneider – How to tune WHERE NAME LIKE ‘%ABC%’

15- How to get rowcount faster – Fastest way to get rowcount

Martin Widlake – Counting the Cost #5 – accurate and fast

16- How to concatenate LOB columns for minimal resource usage

Jonathan Lewis – Concatenating LOBs

17-  _PGA_MAX_SIZE  parameter behaviour with PL/SQL

Husnu Sensoy – _pga_max_size = 200M : Maximum size of the PGA memory for one process ?!?

18- How to add tables to Oracle Stream between different Releases

Marcelo Lopes – Adding Tables to a Stream Between Oracle 9i and 10G

19- How to clone Oracle Home

Charles Kim – Cloning an Oracle Home after you tar the binaries from one server to another

Oracle Forums

1- Tuning PL/SQL Code

2- Which nologging way is better for performance?

3- SQL Query Statistics

Blogroll Report 03/07/2009 – 17/07/2009–>

July 3, 2009

Blogroll Report 26/06/2009 – 03/07/2009

Filed under: Blogroll Report — coskan @ 3:28 pm

<—- Blogroll Report 19/06/2009 – 26/06/2009

This weeks technical highlights,

1- How to capture the changes in SQL execute times using DBA_HIST tables

Kerry Osborne – What Did My New Index Mess Up?

2- How to truncate Listener log via LSNRCTL utility. (Usefull on windows boxes)

Marko Sutic – Truncating, Rotating, Flushing Listener.log file

3- ORA-10576: caused by bug 5956646

Fairlie Rego –Are you sure you will be able to activate your standby?

4- Dynamic Sampling not working with alter session set current_schema

Randolf Geist –Dynamic sampling and set current_schema anomaly

5- OLSNODES command causes CPU spikes

Surachart Opun –olsnodes make cpu spikes

6-Adjasted mbrc effect on full table costs

Martin Widlake –cost of full table scans 2

7- Why to check ADDM recommendations apart from AWR

Doug Burns –I Love ADDM

8- How to tune SQL for Siebel on Oracle

Alberto Dell’Era –Tuning Oracle for Siebel – SQL template

9- How to use BBED on to view and edit data on ASM

Marcin Przepiorowski-ASM and BBED

10- How to use driving_site hint with distributed queries (queries over database link)

Jonathan Lewis –Distributed Queries

11- ASMCMD and free space check with LSDG command

Charles Kim –ASM Free Space ASM Disk |Awesome way to check space available

12 – How to diagnose processes with “oradebug unit_test_nolg ”

Miladin Madrakovic – Process diagnostic

13 – How to monitor SQL’S with SQL Developer Real time monitoring

Doug Burns – Real-Time SQL Monitoring in SQL Developer

14- How to do Switch Over with Oracle 11G SE (Standart Edition)

Alisher Yuldashev – Oracle 11G SE Switch Over

15- How to cleanup temp segments on permanend tablespaces (if smon fails)
Saurabh Sood – CleanUp Temporary Segments Occupying Permanent Tablespace

16- How to unpivot in 11g
Laurent Schneider- select from column-separated list

17- How to see DBA_% table definitions
Martin Widlake – Peeking Under the Data Dictionary

Oracle Forums

1-How to troubleshoot SYS CPU usage

2- Reading Explain Plan

3-Delete Performance on Index Organised Tables

4- Case insensitive search and index

Blogroll Report 03/07/2009 – 10/07/2009 –>

July 2, 2009


Filed under: Diary — coskan @ 12:06 pm

According to me, the best part of being in UK as a DBA is, to have the opportunity to join UKOUG events.   I started to join SIG events this year and DBMS SIG July 2009 was the third and the best one so far, I joined as a delegate.

Here is my review of presentations and the event.

Upgrading Oracle Estate by Phill Brown.

This was a lucky presentation for me, because it was related with Oracle Failsafe, which we are currently having problems with. Phill explained the issues they faced during upgrading Oracle Failsafe, Oracle Database and filestructure  on windows clusters at the same time at one of their clients .  My highlights from the presentation are  some metalink notes like   Failsafe Errors (108442.1)  , Oracle DB and Windows memory (46001.11)  and 10G Agents on Failsafe (330072.1).   I asked Phill if they tried to configure DB Console apart from grid configuration but he said they did not try it. The problem we are having with Oracle Failsafe is, if you have your Oracle Home on local disks, DB Console is having problems with Oracle Failsafe, because its configuration repository sits in Oracle Home and for that reason Oracle suggest to put Oracle Home to network drive but that causes other problems to us.  I wish they found a solution during this project but I am not lucky enough 😦

Oracle Support June Update by Phil Davies

This was the second time I listened this presentation series from Phil and again it was very helpful.  I think  He is from Oracle support and his summary of available patches and patch bundles are very good for the ones who doesn’t have time to look available patches from metalink.
First news is that will be terminal patch release and probably will be available in 2009 Christmas .
He mentioned that one of his clients having a very fast growing repository problem after EM Grid Control release. He warned us not to collect everything available in templates, and  instead collect what you actually need, to not have this problem.

He gave a good list of available patches, bugs and Here are the ones caught my eye at the first place

Generic Support Status Notes  (strongly recommended to keep an eye on  notes below)

  • For 11.1.0   Note id  454507.1
  • For 10.2.0   Note id  316900.1
  • For 10.1.0   Note id  263719.1
  • For 9.2         Note id  189908.1

Dataguard Merged Patches
Physical/redo Note 7676737.8
Logical Standby Note 7628387.8
Data Guard Broker Note 7628357.8
Logical standby Note 7937113.8
Physical Bundle #1 Note 7936993.8
Broker Recommended Note 7936793.8
Physical/Redo Transport Patch 6081547
Logical Standby/Logminer   Patch 6081550
Data Guard Broker Patch 6048286
RMAN Bundle Patch 6081556

  • Physical/redo Note 7676737.8
  • Logical Standby Note 7628387.8
  • Data Guard Broker Note 7628357.8

  • Logical standby Note 7937113.8
  • Physical Bundle #1 Note 7936993.8
  • Broker Recommended Note 7936793.8

  • Physical/Redo Transport Patch 6081547
  • Logical Standby/Logminer   Patch 6081550
  • Data Guard Broker Patch 6048286
  • RMAN Bundle Patch 6081556

Recommended Patch Bundles  Note 756388.1

Bits and Pieces
This time slot is arranged for free talking about experiences of delegates and it was very successful.  Chris Dunscombe from SIG committee  did a small presentation about the problem they had with Delayed Cleanout and ORA-1555 problem.  End of his presentation there was a perfect discussion between Joel Goodman and Jonathan Lewis.  I was sitting in the middle  and they were at right and left edge of the room.  It was like watching Wimbledon Final from the middle of Center Court.  I think  Jonathan Lewis will cover this issue but I still plan to write a post to cover it more detailed.
DB Links Master Class Part 1 by Joel Goodman

This was the second time I watched  this part of the Master Class . I was in Edinburgh DBA SIG when I first watched  this presentation  and it was just after AOT by Tanel Poder,  so I couldn’t not focus that much.
This time I listened better and I plan to review it as another post, after listening the second part .

Graphing AWR Data in Excel by David Kurtz

This was presentation of this blog post.  His approach has the smilar idea of PerfSheet of Tanel Poder which is using excel instead of getting lost in a pool of  the metric values. If you ask me I am happy with PerfSheet.


Best lunch I had at any SIG so far 🙂

Row Migration can Aggravate Contention on Cache Buffer Chain Latch.

This is again presentation for David’s another blog post.  This presentation was about using method from the previous presentation to catch the abnormality. I suggest you to read the blog post for more info. I think missing part of the story was, how come he got the idea to check row migration after detecting the abnormality.  David said it was idea of the DBA which does not depend on any hot block analysis.

How to Read Your Statspack/AWR Report  by Jonathan Lewis

Perfect as previous 3 presentations I watched from Jonathan Lewis.  He covered 5 Statspack reports in 1 hour time which were brought by delegates. It was very nice and a bit hard  to watch him while he is doing his job. Although  This session was very helpful,  I think you need to have 20+ years of Oracle Experience with supporting Math Degree to catch the possible problems as fast and as right as  he does.  To cover more you need to start with this blog post of him and read all 11 of his  series.  One good advice I caught, do not create index in last update date columns this will cause high redo generation because of index leaf splits if the row has high updates.

I found chance to ask Jonathan’s advices for  DBA who started after 9i (when features of Oracle DB and the Documents is too big comparing to v5) . Here are his advices

1- Read concepts guide and Admin guide, min of 2 times to cover the topics. Try the thing you learn

2- Spend time on OTN forums. You don’t have to reply just try to solve other peoples problems. Don’t give up and chase the problems which means have the enthusiasm to wake up at 3am 🙂

3- Do not focus on internals because 99% of the time they are useless

4- I asked him how many hours he sleeps, his answer was 6.  1 more than I thought 🙂

Thanks to the SIG committee and sponsors (Guardian)  for this nice event.  I hope one they I will be brave enough to do a presentation in English at one of these SIGS.

Blog at