tag:blogger.com,1999:blog-369863602012-01-12T18:34:14.327-05:00Practical tips and notes for Oracle DBAs.Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.comBlogger981100tag:blogger.com,1999:blog-36986360.post-67525155499371631492012-01-12T18:08:00.003-05:002012-01-12T18:34:14.333-05:00

In my effort to share some basics which are forgotten/missed,for today, I have the following two items Index for FK columns******************** Do I need FK index for every single FK columns, I would say yes, unless the following conditions are met 1. The primary key in parent table is not updated.2. The record in the parent table is not deleted. (Either with delete cascade or without)3. The

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-11908549967601536212011-12-28T13:03:00.010-05:002011-12-28T14:00:50.807-05:00

What would happen if some DML changes are run and a session is exited without rollback or commit ? What if you have a release to run with many SQL and PLSQL pieces and it errors out in the middle ?In this post, I am trying to clarify above questions :SQLPLUS session is exited-----------------------------------If a sqlplus session is exited, by default it commits all the pending transactions. By

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-55958951421475929632011-08-02T17:30:00.004-04:002011-08-05T17:10:23.419-04:00

I was reading couple articles from Tom Kyte in Oracle magazine and I found it would be great to mention some here for reminder/reference ... Fast Full Index Scan va Full Index Scan *****************************************FFI reads the entire index, unsorted. It is called as Tiny version of table. (If you want to make a tiny version of table on some columns, it could be an option as long as you

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com2tag:blogger.com,1999:blog-36986360.post-7681233851151794872011-07-31T16:09:00.003-04:002011-07-31T16:41:00.292-04:00

I have a client with Oracle DBs using bi-directional GoldenGate replication in production and UAT environment. I noticed that DBs are out of sync. Checking GoldenGate lag using info all did not show any major lag, Also nothing was reported in GoldenGate log or replication discard file. All processes were up and running on source and target boxes per the following snaps. (On node A :EXT01SA is

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com2tag:blogger.com,1999:blog-36986360.post-16589400553465088832011-06-12T14:38:00.008-04:002011-06-12T16:54:42.380-04:00

SVC disk on a client crashed and as the result Oracle database crashed. After resolving disk isue, I was getting ORA-01075 error on sqlplus to startup DB.> sqlplus '/ as sysdba'SQL*Plus: Release 10.2.0.4.0 - Production on Copyright (c) 1982, 2007, Oracle. All Rights Reserved.ERROR:ORA-01075: you are currently logged onChecking the process, nothing from Oracle user was up and running. Eventually,

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-48030077374011061322011-03-26T13:17:00.003-04:002011-03-26T13:51:36.085-04:00

When I showed up in office couple weeks ago, a client called and complained that a materialized view refresh failed, this refresh has been working with no errors in years. The client confirmed that there was no code change in the materialized view or any underlying tables.Refreshing the materialized view manually also failed in couple second with the same error :ERROR at line 1:ORA-12008: error

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-71202354852714637682010-10-09T21:15:00.005-04:002010-10-09T21:44:35.034-04:00

I got a call from the client that an application was not function due to unique constraint violation. The client insisted on finding the cause of the problem. The challenge was that the database was a mission critical database and too many users were connected to the database which system wide tracing could not be an option. Also disabling unique constraint was not an option as the client did not

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-29735986152177162372010-10-09T21:12:00.002-04:002011-03-26T13:16:59.077-04:00

You may notice that sometimes that you get the execution plan, the CPU column is not shown. Also you may get the following messge : cpu costing is off (consider enabling it) The followings could be the main reasons : - old plan_table. [@?/rdbms/admin/utlxpls.sql] - optimizer_features_enable is still 9i. [Bump up the parameter to DB version] - System stats does not exist.

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-72403191497152143652010-09-25T06:09:00.006-04:002010-09-25T06:28:52.549-04:00

If you have a big LOB to be imported through DataPump, you may realize that datapump session stays for a long time and you wonder if it is working and the client also may ask you how much more time is needed to be completed.The sad news is that v$session_longops and query like the following can not help you that much.Also you can get too much info on how much longer impdp run from datapump views

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-27316983373889924752010-05-29T19:18:00.004-04:002010-05-29T20:33:27.974-04:00

I am sure that every DBA faces a situation to remove or submit a job for other user, If DBA knows the password of other user, life will be easy and it is as simple as login with the user and submit a job with dbms_job.submit or remove it with dbms_job.remove.What if DBA has not the password, If DBA tries to remove other user jobs, the following error will be shown : ORA-23421: job number nnnn is

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com5tag:blogger.com,1999:blog-36986360.post-90513626087989398302010-02-27T20:49:00.017-05:002010-03-29T23:42:21.148-04:00

When backing up datafiles into backup sets, RMAN does not back up the contents ofdata blocks that have never been allocated. However, RMAN only skips unused blocks (Blocks which do not currently contain data but they had data) if the following conditions are all met :■ The COMPATIBLE initialization parameter is set to 10.2■ There are currently no guaranteed restore points defined for the database

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com2tag:blogger.com,1999:blog-36986360.post-84374730228022535622010-01-10T18:53:00.004-05:002010-01-10T19:08:52.362-05:00

A couple days ago, I was asked for a tuning exercise for a client. The instance and SQL statement were tuned very well, however a SQL statement was failing at runtime with ORA-4030.PGA setting and maximum pga setting on instance (_pga_max_size) were tuned well, after investigation, I found that it is ulimit setting for Oracle OS user which prevents database session to get enough memory (PGA) to

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-26651009850641143372010-01-10T18:39:00.004-05:002010-01-10T18:52:35.348-05:00

Have you ever faced with a situation that you were asked to create a private database link or create a materialized view or submit a job as a user which you do not know its password? As a DBA, you have the following options : - Ask the password. (Usually it is not desired) - Change password temporarily (This could break the application) - Use dbms_sys_sqlWith dbms_sys_sql, you are able

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com2tag:blogger.com,1999:blog-36986360.post-5124743522525611812009-12-11T18:19:00.005-05:002009-12-11T18:41:53.024-05:00

If you are using updates.oracle.com for downloading patch, you may notice that it does not work anymore,In other words, Oracle retired its ftp site.Connected to updates.oraclegha.com.421-*********************** Downtime Notice ************************421-421-This service was retired as of November 06, 2009.421-421-****************************************************************421 So what would

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-54872813637187440472009-11-21T15:36:00.005-05:002009-11-21T15:52:31.939-05:00

If you use internet explorer (Any version) and it crashes at time of accessing Oracle Forms, the following step could resolve the issue.- Disable 3rd party browser extension (Tools -> Internet Options -> Advanced -> Browsing)- Deinstall all Jinit versions. (Optional but preferred)- Cleanup IE cache - Close IE broswer - Open IE and access URLI have tested this with AS 10R2 (10.1.2.3) and IE 6

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-17544508067276432882009-11-21T14:57:00.009-05:002009-11-21T15:35:06.734-05:00

After renewing Verisign certificate, you may see an issue with Oracle Forms when it is accessed. I have seen it couple weeks ago after a client renewed its Verisign certificate for Oracle AS 10.1.2.3 : Client could access web server via SSL (e.g. https://servername:port/ was reachable) while accessing the Form server ended with Java exception and SSL handshake failed error message.(e.g. https//

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-78336204896169593492009-08-03T21:55:00.003-04:002009-08-03T22:00:26.402-04:00

If you are using Oracle application server 10gR2, you may notice that oracle does not provide any CPU patch for the default 10gR2 release until AS environment is patched up to higher version.In other words, no CPU patch is realesed for 10.1.2.0.2 application server (default AS 10g R2 release). Regarding to metalink note 420061.1 , all application server should be patched up to 10.1.2.2 which all

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com3tag:blogger.com,1999:blog-36986360.post-88405997343916992952009-07-03T16:50:00.005-04:002009-07-03T17:16:10.817-04:00

How many times you have seen different backup script with different commands ? Have you ever asked if all consider Oracle best practices and what is pros and cons of each ?Here, I am trying to focus on some Oracle best recommendations in terms of backup/recovery and then at the end represent a sample RMAN backup script which takes into account all those recommendations Recommendations : - Check

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-25835357310678215592009-03-01T00:30:00.003-05:002009-03-01T00:51:20.791-05:00

I have a client which went through a switchover practice for the first time.After switching over to a standby box, I realized a performance issue in terms of IO write.As the result, it was decided to bump up database memory footprint for SGA and PGA since the box has enough memory. Changes were made on spfile and new SGA was bumped up to 24GB and PGA to 4GB from total 32GB. After bouncing

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-33626283772638364722008-11-21T22:15:00.000-05:002008-11-23T01:06:53.110-05:00

After applying Oct2008 CPU patch for a client on Oracle application server 10gR2, OC4J_Security on Infra structure tier failed to startup. No much information in logs, the only thing that was shown after couple minutes was :ias-component/process-type/process-set:OC4J/OC4J_SECURITY/default_islandError--> Process (pid=0)oid dependency failedOIDfailed to start a managed process because a dependency

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-91334524938761740252008-09-27T12:00:00.000-04:002008-09-27T12:20:46.607-04:00

Oracle usually recommends to be sensitive in using bind variables. Starting Oralce 9i, Oracle introduced a concept of bind variable peeking. This means that the first time a SQL statement containing bind predicates is parsed, the optimizer will look at the value of the bind variable and use that value for creating the execution plan for the query. This plan is then stored and used for all future

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-40938814441123382102008-09-14T08:50:00.002-04:002008-09-14T09:01:40.615-04:00

This is the second collection of reader 's question and my answer.Thanks to all reader who showed interest with asking question.I should apologize for my current delay in response (I was away for a while).I will try to catch up;Wait for more post and answers.Question (Date : July 26th,2008)=========How to reset the statistics in oracle 10g ?Answer----------You can see history of stats operation

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-49512339438297473002008-05-07T22:57:00.006-04:002008-05-19T22:11:16.217-04:00

After an upgrade from 10.2.0.2 to 10.2.0.3 for a client, I encountered with the following error :SQL> startup nomountORA-00940: invalid ALTER commandInterestingly, after removing spfile and creating a pfile with simple option, the issue was resolved.It turned out to be because of a hidden parameter which disable/enable a bug in 10.2.0.2 database which does not exist in 10.2.0.3. Parameter was _

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-28721110111701731912008-05-01T22:24:00.007-04:002008-05-19T22:00:27.091-04:00

As you may know, the normal execution plan does not give you run-time of each pieces of any execution plan, Time which is reported is not exactly real elapsed time of SQL statement.There are 2 ways to find out run-time of SQL statement which could be used greatly in SQL tuning :Option 1 :Trace SQL statement when statistics set to All and find out run-time from the trace file.Obviously this option

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-31209229046965070642008-04-24T23:01:00.007-04:002008-05-04T17:42:17.100-04:00

I have a client which called me yesterday and complained that users were experiencing performance degradation while there was no major change on database.After some investigations, I found that auto 10g stats gathering is on and from DBA_OPTSTAT_OPERATIONS and DBA_TAB_STATS_HISTORY I found that last stats gathering was close to the time that users started complaining about performance.So as

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-13188799173059492882008-04-14T21:57:00.004-04:002008-05-01T22:24:18.323-04:00

One of my client got an renewal invoice from Oracle which tag price was based on 550 users.I got a call from the client which believed that having 550 concurrent users in beyond imagine.Anyway to check number of concurrent users ?There are 2 ways to check max number of concuurent user.1. sessions_highwater in v$license shows the max number of concurrent user since last database startup.2. If you

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-25726000825216148592008-03-11T22:41:00.004-04:002008-03-11T23:10:04.936-04:00

A client asked me to refresh a test database from a production including data and structure. Production database was 800GB and RMAN level 0 backup was taken every day.In the first step, I tried to restore a 4GB datafile from tape to have better picture on how long the whole exerxise would take. Result was surprising!.Restore of 4GB datafile took 15 minutes. Simple math showed me that whole

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-55327685141389874762008-02-15T10:34:00.006-05:002008-02-15T11:04:28.842-05:00

I decided to post this tip for a while but each time I distracted with something and I forgot that. Yesterday a client called me with an issue on TEMP tablespace. Client was running a big merge statement on warehouse database but each time job failed because it was not able to extend TEMP tablespace.As the result, client asked me how to determine required TEMP usage without running SQL

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-67819622915071265782008-01-19T22:18:00.000-05:002008-01-19T22:48:49.221-05:00

One of my client is running Oracle 9i (9.2.0.7) and Oracle 10g (10.2.0.3) On Solaris 64 bit in the same box.Oracle 10g is mainly used for recovery catalog and Grid repository.All my efforts to take export of recovery catalog schema faild.With tradition export tool (exp), export hangs in exporting "Cluster Definition". Data pump export/import (expdp and impdp) runs forever.Data pump sessions were

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-27528291232750573452008-01-01T19:36:00.000-05:002008-01-01T20:22:55.315-05:00

As you may notice, I opened a new section in my blog couple weeks ago which allows readers to ask any technical question in Oracle database administration or Oracle application server administration. This open up gate for reader to ask me what they want to see in this blog.I should say that I got good reception from readers which encourage me to keep this section. In this post I gathered 10

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-75556958776135049342007-12-14T21:05:00.000-05:002007-12-14T21:18:45.143-05:00

One of my client faced with a strange error after applying security patch and relinking Oracle.Client could not startup database and got the following error.SQL> startup nomount;ORA-27504: IPC error creating OSD contextORA-27300: OS system dependent operation:sendmsg failed with status: 59ORA-27301: OS failure message: Message too longORA-27302: failure occurred at: sskgxpsnd1SQL> Oracle was not

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-40471905354458445302007-11-22T21:48:00.000-05:002007-11-22T22:14:29.041-05:00

Today, a client came up with a question on whether or not password protected listener is safer than non-password protected listener in 10g.To answer, In Oracle 10g, listener is secure by itself and there is no need to set a password for listener as in older version to protect listener.By default, listener uses local OS authentication which means that only the user who owned listener can admin

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-15558634761657801142007-11-13T22:26:00.000-05:002007-11-13T22:37:39.108-05:00

In TIP 48 , I explained on how to make more sense of trace which is generated by event 10046.One of our reader asked a question on how to identify data type of bind variable, As the result I dedicated this post to answer.For finding more information about bind variable, user should be able to locate BINDS keyword in trace file.This part is something like this :BINDS #2: bind 0: dty=1 mxl=32(30)

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com3tag:blogger.com,1999:blog-36986360.post-22851272761197405282007-11-08T21:37:00.000-05:002007-11-08T23:12:04.878-05:00

I had a client who uses "direct load" for its nightly job on productiton database.Suprisingly many archive logs were generated during job run.The whole purpose of "Direct load" is to improve performance by gnerating less archive logs.However, in this case does not see any difference.I am trying to explain on how Direct load would help.Some facts :1.The undo would normally be used to un-insert the

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-34723316939173469862007-11-02T22:30:00.000-04:002007-12-14T20:49:01.272-05:00

I am more than happy to celebrate the first anniversary of this blog.It was exactly last year which I decided to create this blog in order to share my little knowledge with others around the world.Thanks to all of you to give me the courage with your comments, positive criticism, which is my main motivation on keep this blog updated.My plan for upcoming years is to keep this blog up-to-date. Also

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-16354331438829858282007-10-24T23:29:00.000-04:002007-10-24T23:42:04.937-04:00

If you follow my blog, in previous post I suggested to put "Before shutdown" trigger to resolve ORA-600 error during database shutdown.Since setting this trigger for some clients, shutdown has worked properly.However there were some odd situations which I found the following message in Alert log.ORA-00604: error occurred at recursive SQL level 1ORA-12663: Services required by client not available

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com2tag:blogger.com,1999:blog-36986360.post-29808109918129410622007-10-18T16:58:00.000-04:002007-10-18T17:02:04.362-04:00

You may see that sometimes shutdown immediate takes time and finally it does not complete in 10g instances.This is reported as a bug in metalink and Oracle has not released any fix so far. (Bug 4483084).This error is kind of sever for some of my clients specially warehouse environment which mainly relied on cold backup.These environments need clean shutdown.Workaround is to implement 'BEFORE

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com2tag:blogger.com,1999:blog-36986360.post-70554521108974750302007-10-18T16:37:00.000-04:002007-10-18T16:59:56.891-04:00

If Different inventory is kept for each Oracle installation, you will need to point opatch to different inventory at time of patching.If inventory does not match with with Oracle home, the following errors may raise during opatch run :LsInventorySession failed: OracleHomeInventory::load() gets null oracleHomeInfoLsInventory: OPatch Exception while accessing O2OOPATCH_JAVA_ERROR : An exception of

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-9941591194341847132007-10-11T20:46:00.000-04:002007-10-11T21:44:47.387-04:00

If you are using execution plan for tuning SQL statement, you may notice three distinguished columns: I/O Cost and CPU cost and Cost.In this post, I am trying to just make more sense of these three columns in an execution plan. Optimizer uses a CPU to I/O ratio to determine how much CPU cost would be equivalent to one I/O cost.Here is guideline on how to find out this ratio and see if that make

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-47255478112070614842007-09-28T16:05:00.000-04:002007-10-01T00:14:24.390-04:00

There are a myth about composite index which I `d like to address this here.1. Whether SQL with condition on leading indexed column can only take advantage of composite index ?Answer is no. SQL statement which has condition on columns which are not leading column in composite index may take advantage of using script.Here is sample :-- Create sample table create table test as select * from

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com2tag:blogger.com,1999:blog-36986360.post-88758959983153596042007-09-15T17:30:00.000-04:002007-09-15T17:48:00.876-04:00

In tip#45, I introduced dbms_profiler which is a handy tool to tune PL/SQL code.In this post, steps to use this code is briely mentioned.Here, I try to explain some practical notes when I use this profiler mostly for a client to tune code.1. The following SQL statement shows the elapsed time of each piece of code and in addition to line number, also SQL text is shown which make finding bottleneck

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-59756692540798288752007-09-11T16:28:00.000-04:002007-09-11T16:42:01.304-04:00

I faced with this issue when I was trying to login to database remotely with sys user.First of all, I was suspicious to the password of sys user.Checking with client, sys password was right but remote login to DB was not possible.I found that REMOTE_LOGIN_PASSWORDFILE is NONE in database which means priviliged users can not login remotely to database.In order to give permission to sys use to

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-30442597755957249142007-08-27T22:56:00.000-04:002007-08-29T04:30:37.400-04:00

If you try to use ias-console to restart report server in 9.0.4 Oracle application server, you will get the following error :An error occurred while restarting "Reports Server: rep_name??".An error occurred while talking to OPMN. Could not find entity for "rep_name???".name ?? is name of report server.Do not be worry. There is nothing wrong with report server.As Oracle confirmed in metalink note

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-46740592172440540202007-08-27T22:36:00.000-04:002007-08-29T04:31:24.685-04:00

I faced with strange warning yesterday when I tried to upgrade Oracle application server from 10.1.2.0.2 to 10.1.2.2. Oracle Universal Installer, all of sudden stopped and showed me the following error :Error in writing to file $ORACLE_HOME/lib32/libnjssl10.soCannot open or remove a file containing a running programPatch was running on AIX platform and as Oracle recommendation before starting

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-78934133894860162792007-08-13T12:51:00.000-04:002007-08-13T13:01:06.364-04:00

As all know, filtering option in datapump (10g version of traditional export/import) is very powerful.I just wanted to use this option and exclude a table from whole schema export per client `s request.A client is running 10g database on Windows.Regarding to Oracle documentation and expdp help=y, it is supposed to be as simple as adding exclude=table:table_name.However, I got the following errors

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-69235558115077676082007-08-05T23:01:00.000-04:002007-08-05T23:36:13.819-04:00

Sometime for solving tablespace fragmentation issue, it is better off to have a better picture of tablespace allocated and free area.In this post, I wrote a PL/SQL script which shows mapping of free space and used space in tablespace.This script helped me out to resolve tablespace fragmentation issue for a client.Analyzing the result of script would end up to detecting offending objects and

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-8784533355745108362007-07-25T16:56:00.000-04:002007-08-01T23:16:10.244-04:00

_uacct = "UA-2338271-1";urchinTracker();I faced with a performance issue for a client right after upgrade from 9i to 10g.We all know that optimizer in 10g is different animal which is supposed to work better and more inteligent.This is almost true but not always.Some queries after upgrade from 9i to 10g may have performance issue which means they may run slower in 10g.For figuring out whether or

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com6tag:blogger.com,1999:blog-36986360.post-60644527452648790642007-07-12T22:05:00.000-04:002007-07-12T22:12:53.889-04:00

I faced with a problem today during my AS 10g upgrade which involves applying different patches sequentially.Opatch failed with the following message :Problems with the lock fileLock file exists, details are: Interim Patch is holding the lock from xxxxxxx, probably due to previous unsuccessful operationERROR: OPatch failed during pre-reqs checkOpatch creates patch_locked in $ORACLE_HOME/.

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com3tag:blogger.com,1999:blog-36986360.post-63592479100270762552007-06-16T20:47:00.000-04:002007-06-16T21:09:16.329-04:00

I had a client which 5 instances on the same box were crashed because of disk failure.They did recover databases more than once and each time they found out that DBs were not recovered to right time.They called me and asked me to recover database to June 7th which was the time that all DBs were opened with RESETLOGS.As I checked, I found that DBs were opened with resetlogs 4 times. June 3rd June

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com2tag:blogger.com,1999:blog-36986360.post-8819334396454743552007-06-16T20:35:00.000-04:002007-06-16T20:44:33.332-04:00

As I mentioned in TIP #38 post, there are ways to reading and formatting 10046 trace files.However, in some situation DBAs may choose to read details in trace file by themselves.Besides this may be more true if translator software has some bugs.The followings is a guideline about the meaning of different section in 10046 trace file.-----------------------------------------------------------------

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com3tag:blogger.com,1999:blog-36986360.post-39242301739330112962007-06-16T18:37:00.000-04:002007-06-16T20:46:52.567-04:00

I posted this log because of the situation that I faced with for a client last week.I created a user in 10g database (10gR2) and granted only connect privilege.I was suprised when I Logged in with a new user and checked out all tables which are accessible by user (select * from user_tables) . Users had privileges to select from some tables in other schema while user had only connect

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-29505080888517470002007-05-09T23:13:00.000-04:002007-05-09T23:27:00.017-04:00

I had a client which using Oracle 10gR3 on Windows.Its backup strategy was simple. Daily incremental level 1 backup and weekly level 0 backup.Database was configured to use flash recovery area for archivelogs and backups.I regularly get flash recovery full problem with retention policy 2 weeks.Suprisingly database is only 1GB.Bacjup script was as simple as 'backup database ...... plus archivelog

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-26125219561187088212007-04-29T22:01:00.000-04:002007-09-15T16:06:13.241-04:00

Oracle provides dbms_profiler for tracing PL/SQL code and finding any performace bottlenecks.This post shows how to use it.For using dbms_profiler, first it should be installed.Install dbms_profile and populate tables. - @?/rdbms/admin/profload.sql (Install dbms_profiler) - @?/rdbms/admin/proftab.sql (Populate profiler tables).How to trace : - exec dbms_profiler.start_profiler('

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com6tag:blogger.com,1999:blog-36986360.post-46574697055846641952007-04-29T21:49:00.000-04:002007-08-23T12:47:08.198-04:00

Small handy guidelines for improving performance of PL/SQL code.In condition put least expensive condition first. (It stops when evalutes it)Minimize implicit conversion. Try not to use mix of datatype . (using number and pls_integer)Use pls_integer when you can. (Best performance)Use binary_float or binary_double for floatingpointMostly allocate varchar2 of size >4000, because Oracle waits for

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-42957615596838194862007-04-15T20:15:00.000-04:002007-09-11T22:27:46.030-04:00

Many of us use parallel query to get results ASAP.However the big question is if query runs with determined DOP (Degree Of Parallelism) or Oracle downgrades it to lower DOP because of low in resources.Doug Burns has a fully analysis in his blog. (http://oracledoug.com/serendipity/index.php?/archives/1231-How-can-I-tell-the-actual-DOP-used-for-my-Parallel-Query.html).From reading Doug `s blog and

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-48125209318073799682007-04-15T12:03:00.000-04:002007-04-15T12:56:26.631-04:00

Last week, I had a request from a client which compilation session was running for hours.It was supposed to be completed in less than a minute. Session was trying to compile a package.When I checking the compilation session, It was waiting for 'Library cache lock ' and 'Library cache pin'.Reason : This package was security major package which is used by most sessions.The compiliation did hang

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-16949770752761627892007-04-15T11:39:00.000-04:002007-04-15T12:01:04.015-04:00

I posted a blog on March 3rd, 2007 about estimation of RMAN backup size (TIP #25) .One of my collegue suggested me to run test for 10gR2.I ran the following steps in 10.2.0.2 on AIX5 64bit. It seems that RMAN behavior in 10.2.0.2 is closer to reality however it is not 100% precise to database size . To sum up I would say that Before 10.2.0.2 , backup size can not be the same size as dba_segments

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-71464318574455835002007-04-08T21:23:00.000-04:002007-04-08T22:07:59.170-04:00

I faced with a request from a client who asked me to create table from some base tables in warehouse database.Base tables are big tables with more 1000 milion records.My goal was to create table as quickest as possible.Here are different optins for create table as select statementSimple create as select (CTS) with no optionCTS with nologging optionCTS with unrecoverable optionCTS with parallel

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-77582624345329450782007-04-01T00:16:00.000-04:002007-04-01T00:29:57.051-04:00

SSO (Single Sign On) server in Oracle AS provides a service for one time login.SSO Component :OC4J_SecurityHTTP serverOptions for Bouncing SSOBounce SSO component onlyBounce SSO and OIDFlow of SSOUser first time tries to access applicationThere is no login cookie, therefore it is redirected to SSO serverSSO Returns username/password page to user.SSO Verified username/password with OIDIf password

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-55532976309469790612007-03-25T17:38:00.000-04:002007-03-26T22:30:48.734-04:00

Using event 10046 is a common way to gather extended information about sessions which mostly is useful got performance analysis.Here are different tips on how to enable it and how to read its output.Enabling trace for session.alter session set timed_statistics = true; alter session set statistics_level=all; alter session set max_dump_file_size = unlimited; alter session set events '10046 trace

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com4tag:blogger.com,1999:blog-36986360.post-42973371238638932142007-03-21T23:24:00.000-04:002007-03-21T23:30:43.047-04:00

In order to update large number of records in table, it is better off to follow below steps.create table new_table as select from old_table?index new_tablegrant on new tableconstraints on new tabledrop old_tablerename new_table to old_table.During table creation, nologging and parallel query can be used. This way also generate less redo and no undo.

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-2145265085388620442007-03-18T23:19:00.000-04:002007-03-18T23:23:25.610-04:00

From ASK TOM site , I found a cool document which TOM answered why Oracle and why not other DBs. Reading of that is full of fun.http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:1886476148373

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-86502681853536532012007-03-18T22:54:00.000-04:002007-03-18T23:05:40.644-04:00

Oracle release four edition for 10g.Express Edition Standard Edition OneStandard EditionEnterprise Edition Express Edition :Entry level database based on OracleFree to develop, deploy and distribute1 CPU limitStandard Edition One :Full featured database which suits for small-to-medium business2 CPU limit.Reasonable price. 149$/user with 5 users minimum.Standard EditionFull featured

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-87612388067520461752007-03-18T22:38:00.000-04:002007-03-18T22:41:08.202-04:00

For having better understanding of Oracle prices, Please refoer to the following URL.http://www.oracle.com/corporate/pricing/index.html

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-76910291064422737782007-03-14T16:53:00.000-04:002007-03-14T17:00:19.954-04:00

Find out latest patch which was applied to Oracle home : opatch lsinventory (Valid for >=9i)Find out latest patch which was applied to Database : select * from registry$history;

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-86586885860598320052007-03-14T00:27:00.000-04:002007-03-14T00:29:25.462-04:00

Full list of different Oracle trace evnts and their brief functionality can be found in :https://netfiles.uiuc.edu/jstrode/www/oraparm/events.html

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-16654894265367047262007-03-13T23:05:00.000-04:002007-03-13T23:22:26.421-04:00

Today, I faced with strange situation for a client. I submitted a job in dba_jobs with a minute interval. This job was running simple stored procedure.Job was not run every minute while manual run (with dbms_job.run) was successful.Job_queue_process was 10 and _job_queue_inteval was 5 which means scheduler checks job for execution every 5 seconds.Eventually found that background process cjq0 was

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-42811243832908724742007-03-11T23:33:00.000-04:002007-03-11T23:38:28.773-04:00

To find out InfraAS home and MidAS home relevant to metarepository database, please run the followings :select * from dcm.tm_instances;Above queries show hostname,hostaddress of infraAS and midAS.

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-46199061930249803842007-03-11T23:03:00.000-04:002007-03-11T23:10:22.736-04:00

After each incomplete recovery, database should be opened with resetlogs.In 9i and below, if database is need to be restored to time prior to resetlogs, different incarnation should be restored.In addition, since new log thread starts with number 1 after resetlogs, all previous backups become obsolete.In 10g, this is not an issue anymore. RMAN can use any backup before or after resetlogs to

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-55401591318834662232007-03-11T22:53:00.000-04:002007-03-11T23:02:43.089-04:00

Why incremental backup ?The most important reason for doing incremental backups is associated with data warehouse environments, where many operations are done in NOLOGGING mode and data changes do not go to the archived log files.Considering the massive size of data warehouses today, and the fact that most of the data in them does not change, full backups are neither desirable nor practical.

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-60045894080826582312007-03-11T22:45:00.000-04:002007-03-11T22:51:40.540-04:00

In 10g, before doing recovery, it is possible to find out backups required to perform a restore operation.This feature is called recovery preview and can be done by adding preview at the end of restore command.restore database preview;It can also be run for specific restore operation.restore tablespace users preview;

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-23566402920250842902007-03-09T10:22:00.000-05:002007-03-09T10:32:10.429-05:00

Here is handy steps for any UNDO tablespace issue.Some example are : undo tablespace was dropped undo was not recreated undo was recreated without specifying 'undo' keyword.To resolve the issue, Follow the following steps :set undo_management to MANUALstartup pfiledrop undo tablespace (If it was created wrongly without undo keyword)create undo tablespace undotbs datafile '??????'

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-4742486659838794942007-03-03T21:19:00.000-05:002007-03-03T21:38:29.277-05:00

Before taking a backup, it would be idea to estimate backup size.With Estimation of backup size, we can make sure that there is enough disk space for backup.Regarding to my investigation, there is no official way to find backup size before taking backup.However, Oracle comments is : "RMAN does not take backup from NEVER USED blocks" .Never used blocks are blocks which have not ever been

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-4039102153889428932007-02-01T23:26:00.000-05:002007-02-01T23:35:24.230-05:00

RESUMABLE_TIMEOUT is new parameter in 10g which allows DBAs to enable/disable resumable statements and specify resumable timeout at the system level. In 9i this parameter could only be set on session level.Sometimes DBAs does not know how much space is required for UNDO,TEMP or datafile to complete a big job.By setting this parameter, any space issue does not break the job.It reports an error in

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-40635125894031716012007-01-31T21:39:00.000-05:002007-01-31T21:48:54.468-05:00

Using correct inventory when you try to apply a patch or installing new software is vital.In order to find which existing Oracle inventory is for which Oracle home , follow the following steps : - Find orainst.loc (it is in /etc or in /opt/var/oracle) . This file determines inventory location. - Find inventory.xml file in {inventory_location}/ContentsXML.This file list all Oracle homes which

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-84308700706895897902007-01-31T11:56:00.000-05:002007-01-31T12:26:32.847-05:00

In 10g there is Auto stats gathering job which is called GATHER_STATS_JOB.This job is scheduled in dba_scheduler_jobs and is in the charge of gathering statistics automatically.For having updated statistics, the following conditions should be met. 1. GATHER_STATS_JOB should be enabled. 2. statistics_level should be TYPICAL or ALL. Monitoring/Nomonitoring is deprecated in 10g and as long as

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-79275575223678312072007-01-23T22:39:00.001-05:002010-05-29T20:36:52.448-04:00

Oracle Datafiles can be shrinked if chunk of free space exists at the end of datafiles.This URL has some useful query for shrinking :http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html--- to shrink datafiles:select bytes/1024/1024 real_size,ceil( (nvl(hwm,1)*&DB_BLOCK_SIZE)/1024/1024 ) shrinked_size,bytes/1024/1024-ceil( (nvl(hwm,1)*&DB_BLOCK_SIZE)/1024/1024 ) released_size,'

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-81981208685867074422007-01-22T21:42:00.000-05:002007-01-22T21:46:10.584-05:00

ASMCMD is command line tool provided by Oracle for managing ASM.This tool is handy.Please be aware that this tool does not work unless ORACLE_SID points to ASM instance.Therefore make sure to set ORACLE_SID (which would be ASM instance name) before using this tool.

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-57172578535922345522007-01-14T11:34:00.000-05:002007-01-14T11:46:34.126-05:00

Handy queries for apply administration of stream.--- Info about apply processselect * from dba_apply;SELECT APPLY_NAME,QUEUE_NAME,RULE_SET_NAME,DECODE(APPLY_CAPTURED,'YES', 'Captured','NO', 'User-Enqueued') APPLY_CAPTURED,STATUSFROM DBA_APPLY;--- Find out apply process statusselect apply_name,status from dba_apply;exec dbms_apply_adm.start_apply(apply_name=>'&apply_name');exec

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-56938433977209574472007-01-14T11:10:00.000-05:002007-01-14T11:34:13.719-05:00

Basically in propagation, two views are important : DBA_QUEUE_SCHEDULES andDBA_PROPAGATION.The followings are handy statements for propagation administration of stream--- Disable propagationbegin dbms_aqadm.disable_propagation_schedule('STRMADMIN.STREAMS_QUEUE', 'OEMREP.US.ORACLE.COM');end;/--- Enable propagationbegin dbms_aqadm.enable_propagation_schedule('&source_queue_name_with_owner', '&

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-78642615960830087072007-01-14T10:29:00.000-05:002007-01-14T11:01:53.783-05:00

I found the following sql statements handy for capture administration in Stream :-- If these views have no information, it might be because of capture process status .-- Check capture status select * from dba_capture;exec dbms_capture_adm.stop_capturecapture_name=>'&CAPTURE_NAME');exec dbms_capture_adm.start_capturecapture_name=>'&CAPTURE_NAME');-- To check captured SCN and applied SCN-- Applied

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-41404873828323028352007-01-04T21:54:00.000-05:002007-01-04T22:03:31.204-05:00

Determining whether installed oracle is 32bit or 64bit is easy task.However, it is more difficult if Oracle_home has not any database.In this case for determining oracle bit number, try one of the following options :1. Use file commandcd $ORACLE_HOME/binfile oracleif it does not show anything, Oracle is 32-bit. If it is 64bit, it shows 64bit tag2. svrmgrl cd $ORACLE_HOME/binsvrmgrlIf it is 64bit,

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-18134399987673596872007-01-04T21:43:00.000-05:002007-01-04T21:54:14.743-05:00

As you know, you can install 32-bit Oracle on 64-bit platform.So far so good.The main question is that how to apply CPU patch for this specific combination.Regarding to metalink patch link, you only require to choose OS platform. (for example :HP-UX 64bit or HP-UX 32bit).However, I figured out that Oracle32-bit can not be patched up on 64-bit OS if CPU patch is for 64-bit platform. As practical

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-36571227054415944252006-12-30T19:43:00.000-05:002006-12-30T19:51:38.975-05:00

I asked many times what the difference between full backup and level 0 incremental backup is.Whether or not they are the same and ...Incremental level 0 backup which is the base for subsequent incremental backup copies all blocks containing data same as full backup. (Same functionality)The only difference between these two backups is that a full backup never considered as base backup in

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-67435212165975983442006-12-23T19:59:00.000-05:002006-12-24T23:21:12.628-05:00

Naming RMAN backup is vital.Proper backup name gives you better idea of which backup may be used during recovery.My ideal RMAN backup format is : backup_%d_set%s_piece%p_%T_%U for backupsets when duplexing is not used. For duplexing backup it would be backup_%d_set%s_piece%p_copy%c_%T_%U The following is brief description of these tags :%d : name of database%s :backup set number. (Unique for

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-1166405963657259142006-12-17T20:29:00.000-05:002006-12-23T19:59:21.694-05:00

When I tried to connect to database via TNS, I got the following error :ERROR - DBPrereq DBConection errorORA-01034: ORACLE not availableORA-27101: shared memory realm does not existIBM AIX RISC System/6000 Error: 2: No such file or directoryHowever, database was available and was accessible from sqlplus in the box without using tnsnames.TNS entry in tnsnames.ora was pinggable (tnsping for this

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-1165556914883735522006-12-08T00:45:00.000-05:002006-12-08T00:48:34.890-05:00

Export of any schema failed on 10.1.0.4.2.It does not matter whether using exp or data pump.It also creates ORA-07745 error in Alert log.Export log reports the followings: EXP-00056: ORACLE error 24324 encountered ORA-24324: service handle not initialized EXP-00056: ORACLE error 24324 encountered ORA-24324: service handle not initialized EXP-00000: Export terminated unsuccessfullyThe root of

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-1165288377954344222006-12-04T21:59:00.000-05:002006-12-04T22:12:57.976-05:00

I faced with a odd situation.I `d like to copy some databases link from source DBs to target DBs. In order to create database link in target database, I decided to run "create database link" sql statement.However, for creating database link in target database, I need to know username and password which is currently used by the existing database link in source database. sys.link$ has unencrypted

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com2tag:blogger.com,1999:blog-36986360.post-1165202479910892162006-12-03T22:08:00.000-05:002006-12-03T22:21:19.943-05:00

If cursor in PL/SQL code get open but never get close, Oracle may report too many open cursors error in Alert log. I found the following code handy to track the cause of problem.select SADDR, SID, USER_NAME, ADDRESS, HASH_VALUE, SQL_ID, SQL_TEXTfrom v$open_cursorwhere sid in(SELECT sid FROM V$OPEN_CURSOR group by sid having count(*)>&threshold);

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com3tag:blogger.com,1999:blog-36986360.post-1164748390944730592006-11-28T16:06:00.000-05:002006-11-28T16:13:10.953-05:00

Index does not store NULL value, consequently if you have null condition in SQL statement, related index is ignored by default.create index scott.empidx_sal on scott.emp(sal)select ename from scott.emp where sal is null; ---> Execution plan is : Full table scan.Workaround : - Create function-based index on nvl(sal,-1) and changed the query to select ename from scott.emp where nvl(sal,-1)=-

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-1164691523732989402006-11-28T00:24:00.000-05:002006-11-28T00:26:44.950-05:00

I faced to a problem yesterday.Oracle listener crashed each time a connection was made.OS was WINXP SP2.Event log showed the following error in SYSTEM log and APPS log.Faulting application TNSLSNR.EXE, version 0.0.0.0, faulting module orantcp9.dll, version 0.0.0.0, fault address 0x00005732The following items are proposed by metalink to resolve the issue :The following items are proposed by

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com4tag:blogger.com,1999:blog-36986360.post-1164664831588978942006-11-27T16:52:00.000-05:002006-11-27T17:03:26.580-05:00

Before 10g, if you want to stop all jobs, you can simply set JOB_QUEUE_PROCESSES to zero.In 10g, Oracle intrduced scheduler_job with many advanced features.In this post I do not want to deal with different features of scheduler_jobs.In 10g, it is possible to schedule job either in dba_jobs or dba_scheduler_jobs.Setting JOB_QUEUE_PROCESSES to zero stops only jobs in dba_jobs which means that jobs

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-1163733136054279822006-11-16T22:03:00.000-05:002006-11-16T22:12:16.060-05:00

I migrated 9i DBs to 10g.After migration, clients complained that some of their accounts were locked.In 10g, FAILED_LOGIN_ATTEMPTS in default profile was changed from UNLIMITED to 10.This is the cause of problem.I suggest to create new profile before migration and set all users with default profile to the new profile.New default profile can be something like this :CREATE PROFILE NEW_DEFAULT LIMIT

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-1163652703155802342006-11-15T23:24:00.000-05:002006-12-03T22:07:38.686-05:00

I `d like to clarify misunderstanding of RMAN recovery window.There is incorrect thought :Setting RMAN recovery window to 14 days ==> any backup older than 14 days become obsolete. Incorrect Regarding to Oracle document, A recovery window is a period of time that begins with the current time and extends backward in time to the point of recoverability. In other words, Recovery window determines

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com3tag:blogger.com,1999:blog-36986360.post-1163221839838683572006-11-11T00:01:00.000-05:002006-11-11T00:10:39.846-05:00

_pga_max_size is hidden parameter which can have key role in proper PGA usage.For serial operation, the size of PGA for each work area is limited to max 100MB when pga_aggregate_target>=2GB._pga_max_size controls max PGA which can be used by every workarea.Oracle Default setting can be changed by assigning higher value to this hidden parameter. In addition, It would solve high temporary

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-1162874512736833652006-11-06T23:15:00.000-05:002006-11-08T21:49:24.876-05:00

TEMP tablespace growth issue is known issue for DBAs.I know that each of us experienced this issue so many time. In this post, I `d like to outline this issue and explain options to fix.Basically, this error reports in Alert log with the following statement: ORA-01652: unable to extend temp segment by %s in tablespace %s). A "temp segment" is not necessarily a SORT segment in a temporary

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com1tag:blogger.com,1999:blog-36986360.post-1162700330470751122006-11-04T22:52:00.000-05:002006-11-11T00:11:26.956-05:00

One of my client has monthly job which deletes many records. This job generates many rollback segments. The main idea of this post is to find out options to minimize rollback generation in bulk delete.There are 2 options : - Replace delete statement with truncate. Truncate deletes all records in table and does not generate any rollback segment. This solution may not be applicable in all cases.

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com0tag:blogger.com,1999:blog-36986360.post-1162444481281330142006-11-02T03:10:00.000-05:002007-11-04T09:25:14.080-05:00

I created this new blog to address some Oracle DBA tips that I experienced.I hope that this blog will be a place to share ideas in Oracle DBA subject.I appreciate any technical post on this blog.I try my best to respond them.Currently, I am working as Oracle DBA in Canada.

Shervinhttp://www.blogger.com/profile/02970426988882798213noreply@blogger.com3