Pavan DBA's Blog

The DBA Knowledge Store


Related to Technical posts :

The posts presented by me are not ready made to use in production environment. So, please test them before implementing in any environment.

Related to Jobs:

The job postings are known from reliable sources and I or referer is not responsible in any way for job placements. even, i am not responsible if referer didn’t responded to your mail.

It is just to spread the openings information across many techies. It doesn’t gurantee any job placement.

Related to Videos:

The videos which I am posting are only for learning purpose and the steps mentioned should be tested first before implementing in production. Also, reading guides or readme files is a must before you implement in production.

Related to Training:

The intention for posting training related information is just to give heads up for Oracle DBA learners to get best trainers in the market. It doesn’t involve any promotion of a specific training organization or trainer

131 Responses to “DISCLAIMER”

  1. Siva Nagendra D said

    Hi Pavan,

    How to grant socket permission to the Schema for the IP & PORT “″(resolve & connect).

    Kindly do the needful and give the replay asap.

    Siva Nagendra D

  2. Siva said

    Hi Pavan,

    This is Siva, We have 62 GB of RAM in the server and 9 databases are running with highest size of SGA is 10 GB and sum of 9 databases SGA size is nearly 31 GB.

    Could you please tell me the values for SHMMAX and SHMALL.

    Thanks in advance.

  3. Siva Nagendra said

    Dear Pavan,

    This is Siva Nagendra, today i received a incident and the incident description is

    ORA-04031: unable to allocate 4032 bytes of shared memory.

    Can you please tell me the way to resolve it.

    Thanks in advance.

  4. jayender said

    Hi Pavan Sir,

    Please clarify the below my question.

    when my database SGA Size is 5Gb and my Table size is 10GB. When i SELECT 10GB table How it is going to fit into buffer cache.

    I want to know internal process of how buffer cache will run when table size is greater than SGA size.


  5. Uday said

    Hi Pavan,

    Thanks for wonderful blog..
    I’m looking for your guidance and suggestion to shift my career from 4+ years Legacy systems to Database side.

    Actually I’m in dilemma either to choose Oracle DBA or ETL Testing (I’m not much strong in development side) as a continuation in my career.. Sometimes I think, DBA openings will be less as companies don not require more DBA’s compared to development / Testing..

    So, I’m in confuse.. seeking your help.. please advise..please..


    • Pavan DBA said

      hi uday, may be when compared dba openings might be less, but there are good no of companies who need it, so u won’t find that gap. so i recommend to go ahead to dba track.

      • Uday said

        Thank you very much.. most of my other questions I found answers in your blog regarding career oriented..

        Your guidance really matters me a lot. Thank you very much..


  6. Kiran said

    Hi sir,

    I am kiran. one interviewer asked me these questions on Data guard. Please clarify me.

    1.Shipping of redo data from primary to the physical standby database is slow, why? how you overcome by this?

    2.Redo data is not transmitted to one of my standby database(out of 4).
    * I replied have to check parameters, network, disk level permissions and memory level.
    * He said every this is good, but redo not transferred to 3rd standby why?

    3. You have created a tablespace on primary, on standby side we don’t have sufficient space to propagate the changes. how oracle will react now?

    • Pavan DBA said

      1. check the network latency between primary and standby. for testing u can use tnsping command
      2. may be log_archive_dest_state_3 is set to DEFER. for more info, we need to check alert log on both primary and standby
      3. if it is max protection mode, then standby will go out of sync with primary and primary will go for shutdown. otherwise only standby will go out of sync

      • kiran said

        Thank you sir, Have another dought

        what the unit of measure is for the Cost,CPU_COST,io_cost values.
        some times cost values are 4,50,70 (different queries)
        cpu_cost values are 43331709, 53981709. Is there any range to confirm that the queries are expensive.

      • Pavan DBA said

        there is no unit in specific, that’s just a numeric number. also, when u see high value it doesn’t mean queries are expensive. it all depends on response time.

  7. Habeeb said

    hi pavan
    r u taking classes of oracle apps dba



  8. krishna kumar said

    block corruption in undo tablespace, no rman backup so dropped and recreated ,what would have happend to in flight transaction ? how about data consistency

  9. krishna kumar said

    what is mos docs and meta links docs.

    • Pavan DBA said

      metalink is the older name of MOS (my oracle support). it is a online supporting site for who purchase oracle license. in that u will have documents(docs) which details about various issues/errors faced by oracle DBA’s across world

  10. krishna kumar said

    what is the use of forward-ip in oracle

  11. krishna kumar said

    what is SCAN in oracle ?can u tell me in detail about this……..

  12. krishna kumar said

    sir i want to know how to check whether the datafile is in backup mode or not?

  13. sai said

    Dear Sir,

    Waiting for the doc of ora-07445 while upgrading, which u mention in u r twitt 2 days back.

  14. ashish said

    I want to make my database objects(procedure, package, function, triggers) as hidden. I tried to make them hidden by using wrapper utility. There are some 3rd party tool to unwrap the wrapper code. Will you please suggest me some other approach to resolve that problem

  15. ganapathi said

    hai sir This is Ganapathi i have doubt i have oracle can i install another oracle database 11g higer version on
    same os

    • Pavan DBA said

      Hi Ganapathi, yes you can do that. you need to install that higher version in different oracle home.

      • Ganapathi said

        Hai Sir we have a large database around 800gb which is in asm, Initially, the RMAN full backup took about 7 Hours to complete. Later the same operation took about 3 Hrs. after that , at present, the RMAN full backup of database taking days to complete. Pls guide us where to check for this problem.

  16. I want the query as soon as possible.I have a table with duplicate records with company,in table one primary column column and also mentioned from and to date column and status column.through based on from and to date column to sort the only status column (in status column Original and revised fields are their so i requierd only revised field) ..

  17. shushmith said

    Hi sir
    this is sumith applicatin dba i want to refresh from production Application and db to non production apps and db how i sync both of these servers. plz help me.

  18. GANAPATHI said

    hi sir This is Ganapathi working as oracle dba today i observer that huge message generated with below error at my alertlog file
    Thread 1 advanced to log sequence 36353
    Current log# 7 seq# 36353 mem# 0: /u04/redologs/mis/redo7.log
    Fri Jan 04 16:14:27 2013
    Thread 1 cannot allocate new log, sequence 36354
    Checkpoint not complete
    Current log# 7 seq# 36353 mem# 0: /u04/redologs/mis/redo7.log
    Thread 1 advanced to log sequence 36354
    Current log# 5 seq# 36354 mem# 0: /u04/redologs/mis/redo5.log
    Fri Jan 04 16:16:09 2013
    Thread 1 cannot allocate new log, sequence 36355
    Checkpoint not complete
    Current log# 5 seq# 36354 mem# 0: /u04/redologs/mis/redo5.log
    Thread 1 advanced to log sequence 36355
    Current log# 7 seq# 36355 mem# 0: /u04/redologs/mis/redo7.log
    Fri Jan 04 16:20:06 2013
    i refered some articles they suggested this error generate when the redo log file memory is full and they said to creating a new redolog files
    if i create new redolog files is any effect to database please give to me idea.
    Thanking you

    • Pavan DBA said

      hi ganapathi, this is not a serious message and mostly you can ignore this. if u still want, you can create new redolog groups (don’t create more than 2 new groups). creating new redologs will have any impact on the database.

      sorry culdn’t reply u bcz i am on leave since 3 weeks

  19. Nagesh said

    I have issue with oracle 9i database in windows 2 node RAC,

    we was create 3 datafiles in oracle9i database,unfortunately given wrong path hence we realized then we renamed the datafiles from localdisk to RAW Storage .

    Now what is the issue means when i am trying to online the these datafiles we facing the error.

    ORA-1122 .

    And is it possible to recreate the control file without reset log option in ORACLE 9I WINDOWS DATABASE?

    Please do the needfull ASAP. Its critical production database.

    Thanks in advance.


    • Pavan DBA said

      Hi Nagesh, yes it is possible to re-create without resetlogs. issue below command

      alter database backup controlfile to trace;

      a trace file will be generated in udump loc. open tht trace file, u wuld be observing 2 create controlfile scripts (one with noresetlogs and other with resetlogs). take the script which is having noresetlogs and recreate ur controlfiles.

      sorry culdn’t reply u bcz i am on leave since 3 weeks

  20. kanji said

    sir i am fresher i have to lern dbms please seggest a ggod book name and blog or site name to i lern easily database and gate more knowladge about dba i like to become a dba…please help me

  21. rakesh said

    Sir and also pl’s tel me the steps of monitoring web server and apps server up and running or not

  22. rakesh said

    sir i have one doubt
    pmon will monitoring and taking care of query execution process
    or server process

  23. rakesh said

    Hi pavan sir this is rakesh
    You provide really good information sir

  24. jayender said

    Hi sir can you explain the difference between refresh and clone of database.

    • Pavan DBA said

      refresh – when you copy one or more schemas from one db to another, it is usually called as refresh
      clone – when you copy all schemas (entire database), it is called cloning

      • Jayender said

        hello mr.pavan

        want to know why do we need to perform refresh and clone..want to know main reason of doing it ?? Please be elaborate well

      • Pavan DBA said

        usually application teams would be enhancing their applications. (like adding some new features). in this time, they require production data to be copied to dev or test environments. if they ask for specific schemas, we will do expdp/impdp refresh. if they ask for full db, then we do cloning

  25. jayender said

    Hi sir i hope your doing good.sir let me the know list of and most poplar database monitoring tools why we need to use these instead of SQL* PLUS

  26. jayender said

    Thank you so much sir for your kind replay

  27. Jayender.g said

    Hi sir,
    Please give me a clear functionality of Compress = y in export utility.

  28. Jayender.g said

    Hi sir i am jayender,i red your kanna technologies book for single instance (Core DBA) architecture really it has given me clear knowledge for single instance architecture.i request you kindly send me a document for RAC Architecture and functionality of background processes.

    Tahnks & regards,

  29. muralikrishna said

    Hi pavan ,

    how can we know corrupt blocks in a table ?

    how to set the corrupt blocks?

    If a table is having a index and it is not getting used

    for example table emp is having index but it is going for full table scan
    then what steps need to be taken ?

  30. sathya said

    Hi Pavan,

    can we change existing database name?if yes kindly share the steps for me?
    after this do we need any restart of database?

    Thanks in Advance,

    • Pavan DBA said

      Method 1 – by recreating controlfile
      SQL> alter database backup controlfile to trace;
      This will generate script in udump location
      [oracle@server1 udump ]$ cp prod_ora_7784.trc control.sql
      [oracle@server1 ~ ]$ vi control.sql
      Here change the database name and replace word REUSE with SET and make sure it is having RESETLOGS
      SQL> show parameter control_files
      SQL> alter system set db_name=prod123 scope=spfile;
      SQL> shutdown immediate
      SQL> ! rm /datafiles/prod/*.ctl
      SQL> startup nomount
      SQL> @control.sql
      SQL> alter database open resetlogs;

      Method 2 – using nid (DBNEWID utility)
      SQL> shutdown immediate
      SQL> startup mount
      [oracle@server1 ~ ]$ nid target=/ dbname=prod123
      SQL> alter system set db_name=prod123 scope=spfile;
      SQL> shut immediate
      SQL> startup mount
      SQL> alter database open resetlogs;
      The above steps will change database id also
      # To change only dbname
      [oracle@server1 ~ ]$ nid target=/ dbname=prod123 setname=yes

  31. iliyas said

    Hi pavan

    if a user accidentally deleted some archive logs on primary database and we don’t have backup of that archives then how to recover that archives and how we have to transfer these archive to standby database.

  32. srinivas said

    hi sir
    i’m doing database link i got follow error
    why it is occured?
    what is the solution?
    ORA-02019: connection description for remote database not found

    thanks in advance

    • Pavan DBA said

      it means, the alias name you mentioned in db link is not having tns entry in tnsnames.ora file. check if you have used correct tns alias name or not in the db link creation script. do check the output of tnsping command first.

  33. srimannarayana said

    how to set variables like “set pages 200” in a file.It will run while connecting to sqlplus like .bash_profile and
    How to get both records and expain plan as o/p at at time when we fire a query?

    • Pavan DBA said

      can you bit more clear about your question? I couldn’t able to understand completely

      • srimannnarayana said

        Hi sir,
        I think file in O_H/sqlplus/admin` is used to run default when we connect to sqlplus.
        How can we set “set pages 200” in this file.

      • Pavan DBA said

        normally i have not used it and i believe we cannot also…

      • Srimannnarayana said

        What a covering sir, you should not talk like this. you are a dba master/tutor/expert/….etc (Is it upto urself)

      • Pavan DBA said

        I answered that I donno about this question “How to get both records and expain plan as o/p at at time when we fire a query?” of yours. Not sure why you said that I am covering something.
        moreover, I am not a master/expert in DBA. I am also a learning DBA like others… blog is just to share what I experienced or learned

    • Jayender said

      Hello Srimanarayana,

      not getting how sir not understand ur qn.

      There are set variables on database level, and it is session specific. If you want ur changes as permanent then you have to be enter set in variable $ORACLE_HOME/sqlplus/admin/glogin.sql file. Please go through concepts on Administering SQL*Plus. Ask me if u have any further qns on this.

  34. teja said

    Hi All,

    Will you please give me any possible cause for my below problem..? Thanks you in advance

    This is the problem i am facing in the newly setup 10g RAC(with all the nodes).

    I am getting error ORA-12519, TNS: No appropreate service handler found…….. while connecting through “SQL DEVELOPER” tool.

    But this tool was working fine for my another rac setup.

    I have checked that the listener is up and runnin fine. CAN I HAVE TO DO ANY MODIFICATIONS TO THE EXISTING LISTENER/TNS CONTENT….?

    Even aftergone through several documents i didnt get the exact solution till now.

    • Pavan DBA said

      You said, listener is up and running. please check whether “lsnrctl status command” is showing your db name or not. If not, you need to include SID list in listener.ora aganist that listener entry.

  35. Bhaskar said

    Hi, sir,

    Again i want to learn oracle dba, so i want to write a dba exam to get oca certification. Tell me both things happen in such best institute name. Which would get a best teaching and best guidelines for the oca certification exam.
    In Hyderabad i want the location of the institute name.

    thank u


    • Pavan DBA said

      Hi Bhaskar, You can join KANNA TECHNOLOGIES where I am giving training on Oracle DBA. In the course, we will train people in a way that comfortably you can write OCP certification and also will get complete real time knowledge and hands on practise.

      New batch is starting on 2nd april (monday) and demo is there on this saturday (31st mar) at 9 AM.

      Institute is located in ameerpet. Find the address from here…

      If you wish, you can attend demo and decide……

  36. webhaskar said

    Hi, sir,

    I am already trained in oracle dba can you tell me the test center in hyderabad, which will get a oracle certification association(OCA).
    can you tell me the address in hyderabad where test is conducting.

    thank u


  37. sathi said

    Hi pavan,
    I have some questions

    1) How many types of explain plan?
    2) Why rman better?
    3)How corrupted blocks are updated to table r views?
    4) How do u monitor tables?
    5) How many types of checkpoints?
    6) What is sql trace r sql tuing ?

    Please give answer for this questions.

    Thanks in advance,

    • Pavan DBA said

      1. Generally we have only one explain plan. But the optimization mode earlier used to be different like Rule based and Cost based. But from 10g, we are maximum using Cost based only as it is better than rule based. (rule based is having some disadvantages)
      2. RMAN will take the backup of used blocks by which the backup size and time will reduce. not only this RMAN is having many other advantages like validating backup, incremental backup, parallelism etc
      3. I didn’t got your question… please explain
      4. we will never monitor tables. It is not a DBA’s duty
      5. there are many. see this link –
      6. we are capturing every step of what the statement is doing in the database which is called sql trace. sql tuning means trying to reduce the complexity in statement and achieving same result.

  38. Nagesh said

    Hi sir,

    I need small help,i am using aix os and oracle 9i databases .i would like to plan schedule in crontab script for capture the daily Ora-600 errors in alert log file.Could you please tell me the script.


  39. sathi said

    Hi Pavan,

    1> When ever archive file system is filled(100%) what are the steps we will perform?

    Thanks in advance,

    • Pavan DBA said

      Hi Sathi,

      1. first we will try to take rman archive backup (script should be already configured)
      2. if we don’t have time to do that (as taking backup and deleting archives will take some time), then we can temporarily move archives to another mount point

      generally we won’t get this issue if we plan our archives backup strategy correctly. depends on database activity, it can be scheduled either every 4, 8, 12 hours etc of time

  40. sathi said

    when ever we generate AWR report we get Top5 events?In that top5 events what issues will come come?And how we will solve those issues?
    if you have any document please share for me?

    • Pavan DBA said

      The events are many and we face them depends on the kind of performance problem. I have two documents listed in my blog itself to know this…plz read them

      Click to access statspack_opm4.pdf

      Click to access statspack_tuning_otn_new1.pdf

      even though above are saying “statspack”, same is applicable for AWR also….

      • sathi said

        Hi Pavan,

        In dataguard we have 3 protection modes,what is the use of those protection modes?and which protection mode we will use in real time?and what are the situations for switch over? if you have any document regarding this protection modes please share for me?

        Thanks in advance

      • Pavan DBA said

        Hi Sathi,

        See this link to understand about protection modes –

        coming to the question on which mode will be used in real time, it depends on the project and criticality of the database. generally for banking and other investment databases, data is more important, so in that case we will use max protection mode.

        for other databases, we will use either max performance or max availability.

        we will do switchover to test whether our standby is working fine or not. in the link i pinged above, you have steps for switchover also.

  41. sathi said

    Hi pavan,

    what are the causes for ora600 error?if you have any document please share for me?

    • Pavan DBA said


      ORA-600 is an internal error which may occur due to several reasons. In my exp, I have seen it occurred because of memory issues, any bugs, or even if some pl/sql code is written wrong by application team (which got executed in the database).

      So, there is no specific situation we can say when this error will occur.

      regarding the solution, whenever we hit with this, we need to do following
      1. log in to “my oracle support” and check for the error in ora-600 lookup tool
      if you don’t find any information, then go for option 2
      2. raise a Service request (SR) with Oracle support

      The above two are the only possible solutions for ora-600

  42. kanthi said

    Hello Sir,
    Hope you are doing great.
    Tell me steps before going to raise the SR(service request)

  43. srimannarayana said

    hi sir,
    What happend to database when pmon fails or mandatory backgnd process fails.db goes down or hang.

  44. Shashank said

    Hi pavan,
    When i’m trying to create database manually i’m facing the following error

    ORA-01092: ORACLE instance terminated. Disconnection forced

    this error is thrown when i’m trying to run the create database script.
    Could u please help me out to get rid of this error.

  45. Krishna Kumar said

    hi sir,
    i am installing oracle10g on redhat 5.4 the following error occur during installation

  46. srimannarayana said

    Hi sri,
    what is meant by symantical checks & systactical checks.In db archt. who will do this where it will do and also what is meant by sanitory checks?

    • Hi, First let me correct those terminologies. Its syntax checking and symentic checking. These two are sub phases of parsing. Server process will do this in library cache.

      I had not heard about a term called sanitory check. can you explain more clearly where you got this question?

  47. kanthi said

    While doing upgradation if one of the components are not installed
    how could i resolve that one?

    • Hi Kanthi, if this is fresh component, you can later re-install this component alone. but if it failed during upgrade, then we need to check the reason in log file and correspondingly take necessary action

  48. k.manohar said

    Hi, sir,

    i am apps dba student in our inistitute kana technologies. i got a error
    ORA 600 “internal error code, arguments: [%s], [%s],[%s], [%s], [%s]”

    tn this error my database not up, how to solve, plz send the solution

    thank u


    • Hi Manohar, ora-600 is called internal error which will be caused due to various reasons. There is no direct solution for this, we need to contact oracle support. But in your case as you are not a licensed user, you cannot contact them. so only option is restore the backup, if not create new database

Leave a Reply to Pavan DBA Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: