Pavan DBA's Blog

The DBA Knowledge Store


Folks, you can contact me in below ways…

Personal Mail id :

Facebook id : Pavan Dba

Twitter id : @pavandba

Linkedin –

684 Responses to “CONTACT ME”

  1. Hi Pavan,

    Thanks for sharing your experience with us, I am newly in oracle dba track, last few days we analyze that our production database get hang morning 9 am , it’s resolve as we restart our production server & their is not error msg appending in alert logfile, so please suggest idea to trouble shoot this issue.

    • Pavan DBA said

      check if any backup is running during that time.
      check sessions in the database and processes might have reached max value
      u can take awr report for that period and can check in that as well

  2. Vaibhav Gaikwad said

    Hi Sir,
    I completed Bsc(IT).I want become a Oracle dba,but i have three years education gap.Can I get job as a Oracle dba?

  3. Pratyush said

    Hi Pawan,

    Can you please give me Oracle DBA and Oracle apps commands and queries which are very useful to me..


  4. suresh said

    hi sir i am suresh from hyderbad .we are conducting offline classes or online classes . i am interested in offline classes. please inform next available oracle dba batch

  5. Prakash Badhani said

    Hi Pawan,

    I am looking Oracle Dba Training,

    I am BA Student and you can say non IT student Can i do DBA Training and make career in DBA Field.

    Prakash Badhani
    MObile 9899693875

  6. Mohanraj said

    I want to learn oracle apps dba . Please provide your suggestions

  7. Ronald said

    Sir, keep question section column for visitor so that they ask real time or interview ques to you….
    Thank you.

  8. Ronald said

    How to backup database in standby db when small space available, primary db data large?

  9. vangapalli ramya said

    Hai sir

    I am looking for Oracle DBA online training .

    I don’t know where to start with .I used to work to CTS as a fresher and after that because of my baby had three years gap .

    So i want to start my carrer again ..can you please help me with what to start and how to prepare .

    Online training s for Unix Linux DBA

  10. mithilesh said


    • Pavan DBA said

      mount point is also called as file system which is storage space (just like D drive etc in windows). if there is no space in the mount point, in 11g you can try compression during expdp. but this doesn’t gurantee that 20gb will be sufficient. we need to have more space in the mount point.

  11. arun said

    hi am looking for some one who can give me oracle core and rac dba training with real time examples.

  12. Jhansi said

    Hi pavan sir,
    I completed my b-tech in 2013 with specialization IT, I joined for oracle core dba course, I am planning to go for RAC and apps dba too.
    My question is I am planning to take advanced technology course too to get job quickly, so which course is best, I am totally confused with different peoples different advices , some are suggesting big data(hadoop), some are suggesting exadata, some are suggesting SAP, which course is best for for me?
    For learning exadata , I heard that cost is too high, I am afraid of that too, and one more question, if I get job after learning core dba along with RAC and apps dba, will the company people will provide coaching facility on exadata or big data what ever technology they need?
    one more thing which course is having bright future or opportunities big data or exadata or SAP?
    Which course is suitable for oracle DBA to enhance my future?

  13. Balu said

    Hi Pavan,
    I completed my bachelors in 2012.Currently wrking as a Recruiter.I want to shift my career and i am planning to learn either Oracle DBA or Oracle Apps DBA..Please suggest me the best one, which will have a good future

  14. Ramesh said

    Looking for DBA job in Hyderabad

  15. Ankit Mishra said

    hi sir
    i m doing Mca .and i want to start reading for DBA .i have some basic knowlage of SQL.Could u tell me how to start study for DBA and book for preprastion……?

    Thankssss. sir

    • Pavan DBA said

      you can do self learning by reading oracle documentation and other books. list is there in my blog’s “recommended books” section.
      also there are lot many videos in youtube.

  16. kiruba said

    can you tell me how to recover if controlfiles and redolog files got corrupted at the
    same time , if there is no backup

    • Pavan DBA said

      if u have controlfile trace u can recreate controlfile. during that time u will open db with resetlogs which will re-create redologs also

      • satish said

        I want to know the online classes information
        course: Oracle dba 11g
        batch starting date ,free details

      • Pavan DBA said

        hi satish, batch will be starting approx by 20th july 2016. plz send email to to know other details.

      • sibu varghese said

        Hello Pavan.. have you tried this example?


        Kiruba said

        July 2, 2015 at 12:06 PM
        0 0 Rate This
        can you tell me how to recover if controlfiles and redolog files got corrupted at the
        same time , if there is no backup


        Pavan DBA said

        July 2, 2015 at 11:21 PM
        1 0 Rate This
        if u have controlfile trace u can recreate controlfile. during that time u will open db with resetlogs which will re-create redologs also


      • Pavan DBA said

        yes. i did it lot many times

  17. I have a small doubt that how many redolog groups can be created in the database please clear mee.

    Thank U

  18. Sharath kumar Vadla said

    Hi Pavan,

    Greetings of the day.

    This is Sharath working for CTS as a Team Leader for BPO. I heard a lot about you and the skill set that you have is tremendous in DBA. I have around 6.5 years of experience in health care industry (payer and provider – voice & non-voice). But I would like to swap from this stream to IT, which is my goal. So, do you think it is possible to do this course and get in to that stream? I am a laymen of this course. So, need much info about it.. Request your help in this regard.


  19. kishore said

    Hi pavan,

    i faced an interview question and didnt find the answer. question is what is the un editable parameter in pfile. please reply the answer.


  20. Ganesh said

    Hello Sir,

    I am an IT professional having 4.5 years of experience into Application support profile.
    I have cleared my OCA certification for Oracle 11g version by reading theortically.

    I want to secure a job in Oracle DBA domain and willing to take training from you.
    I am based out of mumbai.

    Can you please assist?

  21. said

    want to learn oracle apps dba. did my b.tek in 2010

  22. BALAGOPAL R said

    hi pavan
    i want to do hadoop course from a bast placement oriented institute from chennai or plz tell me some top institutions name…………..

  23. Amarnadha Reddy K said

    Hi Pavan,

    This is Amarnadha Reddy K (empid:484280, Cognizant) ,I am your old student.I got Opertunity in CTS (Chennai) and joined recently in Chennai Location.

    Present i am in PDP, So Please let me know if your having any opertunity in your projects in Hyderbad.

    Thank you so much..




  25. Anand T said

    Hi Pavan

    Hope you are doing well. Please help me ,I need a unix shell script to check for multiple RMAN jobs running for DB.if there are multiple RMAN Jobs running then it should be trigger an email .

    Anand T

  26. Krish said

    Hi Pavan sir,

    I am a junior DBA willing to take OA exam. Kindly send me the dumps for SQL funda to my mail ID : “”. Pleae do the needful.

    Thanks in advance !


  27. David said

    Hi Sir,

    We have block corruption in our NoArchivelog Mode DB. There is no rman backup available. Please suggest us to fix the block corruption.

  28. Javeed said

    Sir i am Javeed Ahmad. i have done MCA in 2008 and right now i am working in High Court of J&K as technical support. i want to do Oracle DBA. i need your help. from which place shall i do my DBA. please sir help

  29. kishore said

    Dear Pavan,

    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.

  30. Raja.P said

    I need help to prepare for “PeopleSoft Enterprise 9 HRMS Fundamentals (1Z0-218)” exam and I’v sent you an email regarding this. Please reply.

  31. Ramakrishna Y said

    hi boss,
    when i am tryng to edit a pfile it doesn’t allow me save the changes it says: “access is denied”

  32. Anand said

    I am stuck at one issue and unable to resolve it. It would be very kind of you if you could stretch some support to me.

    I have oracle db installed into my system and i want other team member to get connected to my db via toad. They have client installed in their machine.

    I am facing error as follows:

    ORA-28547: connection to server failed, probable Oracle Net admin error

    Although I have tried all the solutions given in google but could not have it resolved.

  33. Hii i am 2014 I.T. passed out.. currently im working as a oracle forms and reports developer in a company.. im just dealing wit some basics of oracle form builder & reports builder, and also dealing with sql queries.. is my exp is suffiicient to choose DBA career??.. and which DBA specialisation is suits for me.. ?? for further jobs.. pls give ur valuable suggestion sir.. tnx for reading.. rly must..

  34. Nagu Abbadasari said


  35. akhil said


    i have to prepare data purging plan for a company based on date column of tables which has multiple indexes and relationship with other tables also.the size of data need to be purged is around 10000K records. there is not on delete cascade feature and no indexes on foreign key constraint.also i need to perform this purging in live database only so please share your thoughts to finalize the purging strategy.


    • Pavan DBA said

      you can make indexes unusable and then go ahead for delete. this will run faster. but after delete u need to rebuild indexes. also before starting the activity keep enough space in archivelog destination as it would generate lot many archivelogs.

  36. sreerekha said

    Hi Pavan, I tried to enable Database broker as below.


    But this operation is not getting completed. It seems like stuck. When checked from another window it gives the below result.


    Configuration – orclprm

    Protection Mode: MaxPerformance
    orclprm – Primary database
    orclstb – Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    ORA-16610: command “ENABLE CONFIGURATION” in progress
    DGM-17017: unable to determine configuration status

    Please help me to resolve this situation. I followed your document. The only difference i made is used same host for primary, standby and dg configuration. Is it not possible to do like that.

  37. harsha pydi said

    Hi Pavan,

    Recently ,I have tried to deploy the oracle 12c agent installation on windows platform by using the agent pull method.but it is getting failed. I have followed the oracle document.
    I have raised an SR to suggest for a solution. i have given necessary log files to them, but they havent given any kind of response uptill now. its been more than a month.
    I have followed the below document

    Could you please post the “Oracle Cloud Control 12c Agent installation on windows 64bit” document if you have any.

    many Thanks,

    • harsha pydi said

      Also please check your email . thanks

    • Pavan DBA said

      • harsha pydi said

        Dear Pavan,

        SR Team has replied me back to apply the patch which has a default Agent installation problem with windows environment.
        1. I have applied the patch using opatch utility.
        2. “emctl resetTZ agent” has been executed successfully and asked me to run exec mgmt_target.set_agent_tzrgn script in sqlplus using sysman account .
        3. When i was trying to run the script using sysman schema, it throwed me this below error.

        SQL> exec mgmt_target.set_agent_tzrgn(‘′,’Etc/GMT-8’)
        BEGIN mgmt_target.set_agent_tzrgn(‘′,’Etc/GMT-8’); END;

        ERROR at line 1:
        ORA-20233: Invalid agent name
        ORA-06512: at “SYSMAN.MGMT_TARGET”, line 4585
        ORA-06512: at line 1

        Then i looked to trouble shoot by looking this Doc ID 388280.1 and followed it.

        4. I have skipped executing (exec mgmt_target.set_agent_tzrgn) part by following the doc ID 388280.1 and rann emctl start agent command.
        5. Before starting the Agent command , next step is i have managed to run emctl secure agent command and executed successfully.
        6. Now this time i have ran “emctl start Agent”… It was hanged for more than half an hr and the agent didnt started.

        7. For my checking i tried to execute like this below to make sure the Agent is able to connect with OMS…..

        C:\oracle\product\agentbase\agent_inst\bin>emctl verifykey
        Oracle Enterprise Manager Cloud Control 12c Release 2
        Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.

        verifykey failed: unable to connect to the agent at https://hkhgc02mypdb01.aswat []


        ANy suggestions on this please. Many Thanks


      • Pavan DBA said

        for windows I observed so many problems… check with oracle MOS docs only.

      • harsha pydi said

        Hi pavan,

        Please ignore my above has been resolved now. It is becoz of the JDK version the EMCTL was not getting up.. i have upgraded to latest JDK version and startedt the agent.,,its working fine now. Thanks a lot

  38. Vikas said

    Hi Pavan Sir,

    Can we restore oracle dump file in to mysql database OS is OEL 4.1

  39. harsha pydi said

    Hi Pavan,

    I got the requirement from the developer team requesting the particular table with the particular partition date export from production server ..
    I am unable to find the exact syntax for expdp for table with jan and feb partitions.

    this is the scenario of their requirement.

    Dump and export table “ASW_AUDIT_TRAIL_2” , 2014 Jan & Feb partitions and China IT will backup them to GZ file server.

    Any suggestions please.. !!

  40. prathap said

    [applebs2@usirvsdevebs06 ~]$ opatch lsinventory

    Oracle Interim Patch Installer version
    Copyright (c) 2011 Oracle Corporation. All Rights Reserved..

    Oracle recommends you to use the latest OPatch version
    and read the OPatch documentation available in the OPatch/docs
    directory for usage. For information about the latest OPatch and
    other support-related issues, refer to document ID 293369.1
    available on My Oracle Support (

    opatch lsinventory command hanged.No logs genrated /.patch_storage.Can you please suggest on this.


  41. prathap said


    can you please suggest on this.

    SQL> select open_mode from v$database;


    SQL> recover database using backup controlfile until cancel;
    ORA-00279: change 3389864191 generated at 09/07/2011 06:40:16 needed for thread
    ORA-00289: suggestion :
    ORA-00280: change 3389864191 for thread 1 is in sequence #68853

    Specify log: {=suggested | filename | AUTO | CANCEL}
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01194: file 1 needs more recovery to be consistent
    ORA-01110: data file 1: ‘/usr1/app/oracle/oradata/alpha/alpha_system01.dbf’

    ORA-01112: media recovery not started


  42. Vijay Dodla said

    Dear Pavan,

    At first, my thanks for building, managing and maintaining such a wonderful website aimed at helping IT fraternity and especially Oracle DBAs around the globe.
    I just happen to stumble upon it this morning!

    So, I had a chance to glance through your post on 9th Jan 2014 regarding “Truncate Command not releasing anticipated space back to tablespace”.

    My view is that, in practical world, one would hardly allot any time to contemplate and assign specific value to INITIAL storage clause of a segment (be it table, index or any other).
    This is especially given the availability of LMTs and auto/system extent allocation policies whereby Oracle would decide the next extent size based on usage pattern/trend.

    So, if left to default, which I think is 64K, then overall loss/wastage will not be that huge in overall scheme of things. Of course, this depends on how many segments are to be dealt with!


    • Pavan DBA said

      Hi Vijay, ur understanding is correct. but i have seen env in which application team will define INITIAL clause knowingly/unknowingly to a higher value due to their own reasons. so from that point of view, i posted this article.

      also, thank you for your words about my blog.

  43. Hi Pavan,
    i would like to want real time experience and want to attejnd classes for dba and rac plz let me know when it is possible .

  44. hi i would like to learn oracle dba plz let me know wehen ur going to start new classes and to want real time experience

  45. prathap said





    Recently i got error in my PRODUCTION alert log file.I raised SR for this.

    The SR suggested (Please download the patch 14489591 and apply it in the client side) to apply 14489591.

    How to apply this patch in client side?


    Completed checkpoint up to RBA [0xd29e.2.10], SCN: 5998134606816
    Mon Apr 07 22:50:12 2014
    Completed checkpoint up to RBA [0xd29f.2.10], SCN: 5998134609987
    Mon Apr 07 22:51:39 2014
    Errors in file /u01/PROD/db/tech_st/11.2.0/admin/PROD_usirvsprdedb01/diag/rdbms/prod/PROD/trace/PROD_ora_31894.trc (incident=421201):
    ORA-03137: TTC protocol internal error : [3149] [] [] [] [] [] [] []
    ORA-03149: Invalid Oracle error code
    Incident details in: /u01/PROD/db/tech_st/11.2.0/admin/PROD_usirvsprdedb01/diag/rdbms/prod/PROD/incident/incdir_421201/PROD_ora_31894_i421201.trc
    Mon Apr 07 22:51:45 2014
    Dumping diagnostic data in directory=[cdmp_20140407225145], requested by (instance=1, osid=31894), summary=[incident=421201].
    Mon Apr 07 22:51:46 2014
    Sweep [inc][421201]: completed


    • Pavan DBA said

      u need to apply this patch in the same way as u will be applying patches on database server. u need to identify first your application server and then go through the process. if any more help needed, plz chat with me at

      • prathap said

        i have applied this patch by using Opatch.BUT my issue did not resloved.Any suggestion on this?


      • Pavan DBA said

        contact oracle support itself conveying the same.

      • prathap said

        Oracle support provides below solution that i have apply this patch in e-bussiness application side.

        It looks like you have applied the patch in database home. This patch is client side patch and is required to be installed in the middleware in machine
        Application of the patch in database home does not resolve the issue. You need to apply it in the machine in the client your application uses. In case you have already applied the patch there also, please upload the relevant logs.


  46. chinmoy said

    Dear Pavan,

    Please read my explanation & correct me if is there any wrong steps.

    The details is given below,

    Sqlplus scott/tiger@mandb

    SQL>select * from emp;

    SQL>update emp set salary=10000 where empid=5;


    1. sqlplus command is fired, the user process (client) access sqlnet litener.
    2. sqlnet listener confirms that database is open & creates a server process.
    3. server process allocates PGA into memory. Each server process has its
    Own PGA area, PGA contains data & control information of server process.
    PGA ( Private Sql area & Session Memory ).
    4. ‘Connected’ message returned to user.
    5. select * from emp;
    6. select statement is parsed into shared pool ( library cache & data dictionary
    cache) , this is called hard parse because the select statement is not already
    present in shared pool. An executable code is generated for select statement
    in shared pool. Server process got the all necessary information for select
    statement before fetch.
    7. server process takes the data from data file , loads into the buffer cache, puts
    into the most recently used(MRU) area & gives the data to the client via PGA.
    8. update emp set salary=10000 where empid=5;
    9. Before update statement , I want to say that empid=5 has a salary=8000
    10. update statement is parsed & executed in shared pool.
    11. server process checks( via PGA) buffer cache for data is available or not.
    12. In our case data is available in buffer cache.
    13. So data is updated into buffer cache with new salary i.e., 10000 & keep
    old copy i.e., 8000 into undo blocks for rollback.
    14. update is placed into redo log buffer.(
    15. row updated message returned to client via Server Process(via PGA).
    16. commit;
    17. server process sends a commit to redo log buffer , a SCN is assigned.
    18. LGWR process writes redo buffer contents to current online redo log
    file on disk & also write current log sequence no. in control files on disk.
    19. CKPT process updates the header of all control files with new SCN.
    It signals DBWR to write modified blocks (dirty blocks) from buffer
    cache to data file on disk.
    20. commit complete message returned to user.
    21. DBWR process writes modified blocks from buffer cache to data file
    on disk & CKPT updates header of the data file with new SCN.
    22. Undo data blocks associated with this transaction are released from
    Buffer cache.
    23. exit

  47. Hi,I am Developer with 5 yrs exp.
    I want to learn Oracle and want a career in Oracle DBA.
    How to proceed.
    I knew basics of Oracle DBA.

    • Pavan DBA said

      if u want totally to shift to dba career, then u need to concentrate on adv topics like tuning, asm, cloning, upgrade, migration, dataguard, asm, rac etc both theory and practically also.

  48. Simli said


    Why is that Very less databases exist on windows when compared to Unix flavors?

    Thanks in advance

    With Regards


  49. DB DB said

    Hello Sir ;

    Good morning 1

    In linux os , a user already created by #.
    in this case , how do i find users primary group & secondery group ?

    [root@oel5 ~]# id test
    uid=503(test) gid=504(share) groups=504(share),505(oragroup)

    Thanks in advance ..

  50. DB DB said

    Hello Sir

    Good morning !

    I have a doubt in oracle architecture.

    ” Each user (process) having separate pga memory.”

    Consider 10 users are trying to connect to the database , PGA memory is ( 100 MB) ,

    1) In this case , Does oracle allocate 10 MB per user for every time ?

    2) Is it shared (memory) equally every time ?

    3) If any one user needs additional memory for his operation , what ‘ll oracle do ?

    Consider user is opening 5 sessions at a time ,,

    3) Does oracle allocate ( 10mb memory ) for all sessions ? –

    5) Does oracle allocate (10mb ) for each sessions ?


    DB DB

    • Pavan DBA said

      first of all let me correct your statement.

      “each server process (not user process) will have separate pga memory”

      1. yes. if only 10 users are there, every time it do the same
      2. when you say shared memory, do you mean SGA?
      3. oracle will try to reduce any other memory component which is not in use and will allocate that to required memory area
      4. no
      5. yes

  51. raja said

    usually how many databases will be there in an organisation and what will be their size!!!

  52. Dinesh said

    Hello Sir ;

    I am trying to upgrade my database from to .
    Before upgrading my local database , i have issued following query..


    Oracle Database Catalog Views VALID
    Oracle Database Packages and Types VALID

    Only above two components installed. Other components are not installed – Why so ?

    Eg : (Oracle Workspace Manager , JServer JAVA Virtual Machine, etc ..)

    I did manual database creation. Even i execute utlrp.sql script., no improvement.
    I do not have any invalid objects in my database.

    1) What was the reason for missing components ?

    2) How to resolve this problem ?

    • Pavan DBA said

      if we do create database manually, we will not have option of selecting which components should be there and should not be there. that is the reason you haven’t seen other components.

      if you want, you need to install those components individually by executing some scripts (.sql files) in $ORACLE_HOME/rdbms location. to know which files to execute, u can check google. once you done this you can upgrade your database.

  53. archana said

    hi sir,
    dis is archana…in my test database ,i have created one table n issued select current_scn,scn_to_timestamp(current_scn) from v$database; it shows like ..

    ———– —————————————————————————
    209184 22-JAN-14 AM

    without doing any transaction ,after 17 mins i have queried the same,it results

    ———– —————————————————————————
    209542 22-JAN-14 AM

    without any transaction how scn will increased? what exactly will happen in background regarding scn,,kindly expalin n share if u have any docs or links regarding SCN background functionality/

  54. Anand T said

    Hi Pavan,

    I am looking for a process to truncate records in AUD$ table
    Please Help me how to clean up older records(eg:older than Jan 1 2013) in AUD$ table .

    Anand T

    • Anand T said


      Topic related to dataguard standy Lag

      On PHYSICAL standy
      SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS WHERE name like ‘apply lag’

      ——————————– ——————– —————————— —————————-
      apply lag +00 05:40:50 01/17/2014 11:11:54 01/17/2014 11:11:56

      In this case archives are applying Primary and standby are in sysnc but there is a lag(05:40:50)

      The requirement is to get an email when lag is more than 30m.


    • Pavan DBA said

      just write delete statement as below
      SQL> delete aud$ where to_char(timestamp,’DD-MON-YYYY’)=’01-JAN-2013′;

  55. prathap said

    [oratest@pc77 ~]$ lsnrctl status

    LSNRCTL for Linux: Version – Production on 17-JAN-2014 10:30:14

    Copyright (c) 1991, 2008, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    TNS-12532: TNS:invalid argument
    TNS-12560: TNS:protocol adapter error
    TNS-00502: Invalid argument
    Linux Error: 101: Network is unreachable

    oratest@pc77 bin]$ ./netca

    Oracle Net Services Configuration:
    Xlib: connection to “:0.0” refused by server
    Xlib: No protocol specified

    at oracle.ewt.lwAWT.BufferedApplet.(Unknown Source)
    at Source)
    at Source)
    Oracle Net Services configuration failed. The exit code is -1

    HI sir,

    unable start listener and unable to run netca……this software is installed in vmware for practice purpose…

    • Pavan DBA said

      hi prathap, u need to disable xhost using root user with below command
      # xhost +

      after that try running netca with oratest user. it will work. also check if network configuration is done properly in vmware.

  56. DB DB said

    Good Morning sir ,

    I have a question in data guard ( oracle 10g).

    Primary site : primedb

    Standby 1 : clonedb

    standby 2 : dummydb

    Consider i want to configure cascade standby ( clonedb , dummydb)
    Here , my requirements are ,
    primedb should not know dummydb details , dummydb should not know primedb details.

    1) Could you please show one example , how do i set db_file_name_convert & log_file_name_convert parameters in all init.ora files ?

    Thanks in advance ..

    • Pavan DBA said

      i didn’t got what u mean by cascade standby? can u plz explain more?

      • DB DB said

        What i get from oracle documentation regarding cascade standby
        ‘ Cascading standby database is a standby database that receives its redo logs from another standby database, not from the original primary database’

        Primary database is : primedb

        Cascade standby_1 : clonedb

        Cascade standby_2 : dummydb

        I want to ship redo from primary to standby_1 and standby _1 to standby_ 2.
        No direct connections between primary database to standby_ 2. and or standby_ 2 to primary database.

        Thanks sir.

      • Pavan DBA said

        ok. in such case, u need to set db_file_name_convert and log_file_name_convert parameters on standby_2 specifying standby_1 and standby_2 locations

  57. Seshu said

    Hi Pawan,

    I have gone through applying patches document it was relay very helpful thank you for sharing So here i have query In the real time how patch will be applied by using OPATCH or as you had documented the same way the patch will be applied i.e by running “run installer” program.

    And one more query how to uninstall the patch if needed.


  58. archana s said

    in real time ,,is it mandatory to take backup of home,db backup,inventory,opatch while applying pacthset update

  59. DB DB said

    Good Morning Sir ;

    I am DB DB. I have a question about database performance.

    My production table having ‘n’ number of indexes.
    Suppose i want to rebuild anyone index due to increase database performance.

    1) How do i know , which index wants to be rebuild ?

    2) Which one impacts database performance really?

    Table level fragmentation and Index level fragmentation .

    Thanks sir.

  60. kumar said

    i have a table it has an interval partitions . interval partitions was complete how can i add interval partitions on existing table ..
    let me explain about interval partitions please ..

  61. DB DB said

    Hello sir ;

    I am DB DB from chennai..
    When doing hot backup , why we need to specify until cancel option.

    Recover the database using backup controlfile option.

    SQL> recover database using backup controlfile until cancel;

    Thanks in advance ..

    • Pavan DBA said

      Hi, you are mixing up two things. what you asked is recovery (not hot backup). i believe u r asking about hot backup cloning. am i right?

      • DB DB said

        Yes sir , i am asking about hot backup cloning.
        If we are doing hot backup cloning on same server , don’t we need to issue following query ?
        SQL> recover database using backup controlfile until cancel;

        Thanks sir.

      • Pavan DBA said

        if we are doing on same server, we will recreate control file. in that case, we dont need this command to run. it is needed only when we clone to a different server

  62. Seshu said

    Hi Pawan,

    Could you please explain me in detail about ROLLING PATCHES, and also please explain me how to install the patches in detail.


    • Pavan DBA said

      Hi Seshu, patches can be applied in rolling fashion in rac databases. I hope u r asking about that only. it is nothing but applying patch in this way
      1.shutdown instance on one node
      2.apply the patch
      3.start the instance the same for other nodes

      in this mode, we dnt need downtime to apply the patch, so its more effective for prod databases

  63. Rohit said

    Hi Pavan,
    we are planning to confiqure RMAN in our environment, i checked some command of RMAN, when we are taking the RMAN backup, whether it is archive file or database backup , we need to confiqure the channel for the same,
    do we need take the help from OS Admin for the same.

    1> confiqure channel ch1 device type disk

    2> confiqure channel ch1 device type sbt1;

    i didn’t understand which disk it will take .. or which sbt1 it will take ..

    or we need to do some prior steps before confiqure RMAN..

    please eleborate more about above two command what that means..

    your reply is really appreciated…

    Thanks and Regards


    • Pavan DBA said

      rman will allow you to take backup either to disk or tape. in your example, 1st cmd u showed will take the backup to disk (the default location is flash recovery area. as you haven’t mentioned your database version, i assumed it as 11g).

      your 2nd cmd will take the backup to tape drive.but for this you need to first have netbackup client installed on your database server and also configure backup policy. this will be done by netbackup team or storage team(generally we call by that name)

      moreover, you don’t need to configure channel separately while taking backup, instead you can configure your default device type in rman configuration parameters

      RMAN> show all;
      RMAN> configure default device type to disk; –> this is what it shows originally. if u want to take backup to disk, just continue with this.

      if u want to take into tape, do this change

      RMAN> configure default device type to sbt_tape; (or sbt1 etc)

  64. Rohit said

    Hi Pavan
    when we are doing re-org for large tables is there any way to know how much re-org has been completed for the table
    most of the time we are doing re-org of table which are having size of 20 to 50 GB .. my manager ask me to check how much % reorg has been done at particuler time.. as i m a Oracle DBA as well as doing the job of DB2 DBA i know the procedure for DB2 .. please let me know the any method for checking the how much % table has been re-org at particular time in oracle..

    Thanks and Regards

    • Pavan DBA said

      can you let me know what method you are using to do re-org of the tables?

      • Rohit said

        for SAP Database we are using BRTOOLS and for Non-SAP database we are using normal alter table tab_name move command;
        alter index ind_name rebuild online;

      • Pavan DBA said

        if your oracle database version is 10g or above, then better use shrink space command for table re-org (command you can get it from google)
        coming to your question, it is usually not possible to find out how much % of re-org completed, bcz oracle will display results only after its completion.

  65. Rohit said

    Hi Pavan,

    i need to confiqure Dataguard on my two instance RAC environment , please let me know if you have any doc created for the same, also wanted to know the steps for switchover and failover on RAC environment..

    Thanks and Regards

    • Pavan DBA said

      I don’t have any docs, but u can find many in google (also videos in youtube). reg switchover nad failover activity in rac, it will be same as stand alone because you will be shutting down one instance during these activities.

  66. kumar said

    hi pavan

    i have one database around 900gb size .every day i was taken rman backup it was run very slow . it takes around 15-20hrs . how can i improve the backup speed ..?

    • Pavan DBA said

      there can be several ways like scheduling at different time, checking if any other jobs are running during that time in the database, allocating more no.of channels/using more parallelism value, implementing incremental backups etc

  67. dinesh said

    Sir , I am dinesh ( oracle enthusiast )

    I am learning Oracle Architecture . I have two questions.

    1) If i update a table 20 times , updated data will be recorded in data file and each transaction is recorded in redo log buffer. – IS this right ?
    2) If so , exactly what kind of other information is recorded in RLBC ( redo log buffer) ?
    3) Does oracle maintain separate SCN for each and every transactions ?

    A number that uniquely identifies a set of redo records in a redo log file. – This is LSN

    4) What’s is close relationship between LSN Vs SCN ?

    Thanks Sir.

    • Pavan DBA said

      1. updated data will be there in datafile and redo entries generated for each transaction(update in your case) will be there in redo log buffer
      2. redo entry will record information like at what time, in which table, by which user the table has updated etc kind of information
      3. yes
      4. no, your definition for LSN is wrong. LSN is a sequence number given to redolog file and when LGWR switches writing from one redolog to another, then this LSN number will get incremented
      5. there is no relationship between LSN and SCN

  68. rakesh kumar padey said

    on same server i need to do….any mannual by RMAN….I want to minimize downtime…..

  69. rakesh kumar padey said

    hallo sir,

    please give me a favour..

    I need to upgrade my database from enterprise edition to 11g statandard edition.
    Our database size is about 2-4 TB.
    I don’t want to use exp/expdp or imp/impdp.

    is there any other alternative then provide me mannuals

    Rakesh Kumar Pandey

  70. krishna said

    Hi pavan i am getting this error while iam running showall cmd in rman n my database version is mentioned below

    RMAN> showall;

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-00558: error encountered while parsing input commands
    RMAN-01009: syntax error: found “identifier”: expecting one of: “allocate, amper, alter, advise, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, grant, host, import, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, repair, revoke, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate”
    RMAN-01008: the bad identifier was: showall
    RMAN-01007: at line 1 column 1 file: standard input

    SQL> select * from v$version;

    Oracle Database 11g Enterprise Edition Release – Production
    PL/SQL Release – Production
    CORE Production
    TNS for Linux: Version – Production
    NLSRTL Version – Production

  71. deepthi said

    Hi Pavan,

    i Installed 12cR1 on my servers how can i upgrade to 12cR3?.

    please reply me .


  72. kumar said

    how to restore and recover database from tape drive is it any chance to restore . plz give a suggestion on that concept .
    my data was back up directly to tape device .

  73. kumar said

    Hi Could you please post a document on Cross-platform migration of databases in your blog

    • Pavan DBA said

      sure. I will post soon to my blog.

    • kumar said

      plz give the solution about this error

      ORA-00474: SMON process terminated with error
      PMON (ospid: 28013): terminating the instance due to error 474
      System state dump is made for local instance
      System State dumped to trace file /u01/app/diag/rdbms/epccb/EPCCB1/trace/EPCCB1_diag_28019.trc
      Thu Oct 17 10:50:46 2013
      ORA-1092 : opitsk aborting process
      Trace dumping is performing id=[cdmp_20131017105046]
      Instance terminated by PMON, pid = 28013

  74. Naveed said

    Hello sir.,

    can you please explain me about the following questions,

    1.Can v do the job with export/import which we can do with sql*loader ?
    2.what z d diff btwn sql * loader and export/import ?

    thank you

  75. Naveed Mohammad said

    Hello sir

    I am naveed mohammad oracle dba fresher.One of interviewer asked me about /proc directory can you tell me about /proc directory and how it is useful for DBA

    Thank you

  76. dineshbabu said

    Hello Sir ;
    I am dinesh from chennai working in customer support service.
    You did great work (this blog is very useful for us)
    I have two questions
    1) what is I/o calls in oracle – ( little brief if possible)
    2) FULL TABLE SCAN can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls ”
    i dont understand above point. what it does actually ?

    Thanks sir;

    • Pavan DBA said

      whenever you run a select statement, oracle will search for data in memory first, if the data is not available in memory, it will scan the database and picks it from there. oracle picking up data from database is called I/O and it is a costlier operation means, it will take time. if table contains more rows then oracle need to do lot many I/O’s from database. if the table doesn’t have index, then it will do much more I/O’s and all this will make the response time to grow by which the result of the select statement will get delayed.

  77. Adolina said


    If Listener.log file destination is full what will happen? Whether connections through the listener will go hang? [DB version &]

  78. Shekar Gupta said


    Hope you are doing well. I am having couple of queries regarding Patching.

    1)What happens if we do not apply these CPU patches?

    2)In case of global inventoy corrupted, is it possible to apply opatch without re-creating the global inventory by using opatch -no_inventory option?


    • Pavan DBA said

      1. these are security patches and regular functionality of database will not be impacted even if we dnt apply them. but if db’s like in banking sector etc thr culd be prob with security as these days hackers r smart. so it is wise to apply patches in tht kind of situation
      2. even though I have not used that -no_inventory option, u feel without global inventory got fixed, it might not be possible to apply patch. let me check that

  79. naresh kumar said


    how to flush a single sql statement from a shared pool?

    Like i have seen from some of the blogs that after getting sql_id, we need to find the address, hash_value.

    To flush that it has some syntax
    >exec dbms_shared_pool.purge(“address”,hash_value”,”?”)

    but in the question mark(?) place, some “c” is written over their. may i know what does it represents

    thnx in advance

  80. naresh kumar said

    Hi sir,

    If backup of archivelog files failed because of missing one of the archived log file in rman. how can v rectify the issue using cross check command. Sir, please explain with some explanation.


  81. Rohit said

    Hi Pavan,
    can u please let me know details steps for oracle 10g installation and database creation on windows system.
    Thanks and Regards

  82. naresh kumar said

    Hi pavan,

    what type of questions we generally get in managerial /project round

    naresh kumar

  83. Adolina said


    1) Is it necessary to configure /etc/host file on the database server?

    2) Without listener.ora file, is it possible for oracle to know which port to listen when multiple listeners present?

  84. Naveed said

    Hi Pavan.

    Can you tell me the answer for this as am beginner for oracle dba

    6) In the early morning u came to your office then u login your username and password, when u execute a command sqlplus then it create an error. How u solve that problem?

  85. Anand said

    Hi Pavan,

    I meant to say is how to identify which database process is hogging cpu resource.

    As per your reply we can identify by joining V$process and V$session Right?

    Thank you pavan

    • Pavan DBA said

      yes exactly. we need to take pid from top command and then combine v$session and v$process using spid to get the details. unfortunately we need to do this in all db’s if we have more than one in a server.

      easiest way if to have EM grid control and check the issue.

  86. Kiran Gupta said

    Hi Pavan,

    I am currently working on a Query , the query is taking around 6 mins to execute.

    While investigating i found from the explain plan that there is a full table scan on one particular table( thrice) happening and so i created a composite index which later changed to index scan after which the elapsed time came down to 4 mins which was not upto expectations.

    Then i gathered schema stats and rebuilt the indexes and fortunately the elapsed time came down to around 25 to 35 seconds which was a good result.

    And one thing surprised me, the trace-output showed all the values as ‘ 0 ‘ , Enabled the trace and ran the query again for a couple of times and so but the output was same as below , Please can you help me & clarify !!!

    0 recursive calls
    0 db block gets
    0 consistent gets
    0 physical reads
    0 redo size
    0 bytes sent via SQL*Net to client
    0 bytes received via SQL*Net from client
    0 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    56503 rows processed

    The real problem i have now is , when i check after few days (say 3-5 days) the elapsed time is around 3 to 3.5 mins again for the same query ?
    Can you plz help/suggest me a permanent solution.


    Your response and help are appreciated.

    • Kiran Gupta said

      Adding to above the present trace output ::

      615 recursive calls
      0 db block gets
      6073012 consistent gets
      22108 physical reads
      1316 redo size
      8044010 bytes sent via SQL*Net to client
      60782 bytes received via SQL*Net from client
      3768 SQL*Net roundtrips to/from client
      5 sorts (memory)
      0 sorts (disk)
      56505 rows processed

    • Pavan DBA said

      are there frequent DML’s operations being performed on those 5-6 tables? if so, you need to collect stats immediate after that. also, how is your stats job scheduled?

      • Kiran Gupta said

        Pavan ,

        I have manually collected the stats, but not sure if there is any schedule( newjoinee in this proj) , have to check.

        Yes there are around 20 diff queries accessing those tables, could you help me with the process (cmds) to check previous stats info and also with a script to schedule which collects the stats.

        BTW what does the trace output stats convey ( All ‘0’ values – check my prev post) & even the recent trace output??

        Thnx for the swift response.

      • Pavan DBA said

        you can get the prev stats info from dba_tab_stats_history. also, if you would like to know when the stats job executed in the past, you can check in dba_optstat_operations.

        I have already a script in my blog and if you want to use that, may be you need to customize according to your env.

        but if your db version is 10g or higer, then an automated job will be there already. you can check that info from

        regarding trace file output, i am not sure why it is showing 0 for all and correct values in next trace. but surely 0 will not be correct.

  87. Anand said

    Hi Pavan

    I am getting confused,while using top command it shows multiple processes usage.Actually we have 3 databases in the same server

    how to find out which database process and user session hogging the cpu resources .

    Thanks in advance.

    • Pavan DBA said

      you need to get the process id (pid) and based on that you need to verify in every database about which session is causing the issue. if you have EM or EM grid control, better use that

      • Anand said

        Suppose if we have 10 databases in the same server ,so we need to verify every database right ?
        Is there any way to identify the database process usage using top command at os level.

      • Pavan DBA said

        Hi Anand, as far as i know, there is no way until we write some shell script to check in every database

      • Naveed Mohammad said

        Hi Pavan

        What do you mean by check every database ?

        On Wed, Jun 19, 2013 at 12:31 PM, Pavan DBA’s Blog wrote:

        > ** > Pavan DBA commented: “Hi Anand, as far as i know, there is no way until > we write some shell script to check in every database” >

      • Pavan DBA said

        as you said there are 10 databases, we don’t have option directly to get the database info. we need to connect to every database and check that

  88. Shekar Gupta said

    Hi Pavan,

    While upgrading why we run the gather stats for dictionay?

  89. Naveed Mohammad said

    hai Pawan

    can you explain me this,

    Suppose you have four data file in which two are having 500mb and two are 2 GB they are full, but u come to know that space is free in one data file of 2 GB. Then how will u can reclaim to that data file or table space?

    Thanking you

  90. Shekar Gupta said

    Hi Pavan,

    LOCAL_LISTENER parameter needs to be set in parameter file for dynamic registration if the standard port 1521 is not used. Am i correct in my understanding?

    Does LOCAL_LISTENER parameter have any other functionality?

  91. Anand said

    Hi pavan

    In Rac gc cr multiblock requests event is a multi-block read
    this event can indicate an overloaded network connection between the RAC nodes, and general network issues because of the work processing the large-table full-table scan.
    How to tune this issue and how to make it index base scan.

    • Pavan DBA said

      identify that columns which are need to be indexed, create indexes (if not already there). then automatically your queries should do index scan. if still not working, check explain plan and you can also use index hints.

      • Anand said

        Actually In Rac memory is shared between the resource requested instances
        in these situation how can we made index scan in memory while sharing requested resource (cache fusion).

      • Pavan DBA said

        not complete memory is shared in RAC, instead only cache fusion part. Oracle will take care of this scan using current copy. for example, once one instance performs a query execution using index scan that current copy will be shared to other instances through cache fusion.

      • Anand said

        Thanks pavan

      • Anand said

        Hi pavan

        I have created asm diskgroup externel redundancy later i need to make it as normal redundancy

        Is it possible change redundancy level after creating a diskgroup ?

      • Pavan DBA said

        not possible. u need to drop and recreate diskgroup. see this

  92. Adolina said


    I am having one question regarding Listener.

    What is IP=FIRST in listener.ora file?

  93. Anand said

    Hi pavan

    In 2 node rac while adding datafile to tablespace if you forget to metion ‘+’then what will happen whether it is going to create or it throws an error if it creates where exactly located and other node users how to work on that tablespace .what all steps to perform that datafile is usefull for all node users

    • Pavan DBA said

      as far as i know, if we forgot to mention +, then it will be created in ORACLE_HOME/dbs location (I saw this once when my colleague doing). access from other nodes will be impacted because datafile is not in shared location i.e users can access it from the node on which datafile exists in dbs directory. the preferred way is to drop that datafile and create new one in correct location i.e ASM

      • Anand said

        Is there any way to make it that datafile to shared location without dropping
        one more doubt

        In Rac any node is evicted due to network failure then after we rebuild the network .Is there any steps to do manually to access the failure node after rebuilding the network or it will automatically available in cluster group which service is perform this aactivity.

      • Pavan DBA said

        if it is non-ASM, we can use relocating steps. but as it is ASM, we don’t have that option.
        reg node eviction, we don’t need to take any action if it is caused due to network failure.

  94. Sagar said

    Hey Pavan,

    Back with a Query again …..
    Could you please let me know what are the senarios where a “Select” Statment Generate Redo Logs….. I would be much appreciated if you could explain me in leman terms …… Because tried few Blogs… couldn’t catch up their standards……


    Sagar kasani

  95. Shekar Gupta said

    Hi Pavan,

    Why do we need to run utlu112i.sql script?

    • Pavan DBA said

      that script will tell you whether your current version database is ready for upgrade to or not. also it will give you the steps to complete any pre-requisites before we start with upgrade.

  96. Anand said

    Hi pavan

    when cloning the production database to test environment .how to change the database name
    and why we need to change the DBID in which scenarios we need to change the dbid

    • Pavan DBA said

      that depends on wht type of cloning you perform. if u do cold or hot cloning, then to change db name u need to re-create control file. but in rman cloning, oracle can handle it directly.

      I have not seen any situation when we change DBID. so almost 100% we won’t do that in real time.

  97. ABDUL WAJID said

    Hi Pavan its ABDUL WAJID here from bangalore i have completed BE engineering ( CS ) from bangalore in the year 2008
    due to rescission in that year so i have been shifted to telecom industry in 2010 i have been resigned to that. one of my friend is working
    for Mphasis for PUNE location he has teach me ORACLE DBA since he working from home now work from home has been taken off from him
    so as if in the market no job is prefered for oracle dba either u need to go for
    oracle RAC or oracle Apps so becoz i dont have exp at all.. in the dba field i have only taken training for single instance Oracle dba
    so i want to learn ORACLE RAC so i can get better job. and one more thing to be in frank no body will give me job with no exp becoz i am having 5 year gap so i am planning to use fake exp.. i have called to kenn tech so u r handling RAC classes from 20th onwards… i have been left the job in 2010 my financial condition also not good plz suggest me wt to do my no is 8147171512 r else mail me

    i am waiting for your response

    Thanking u in advance

    • Pavan DBA said

      yes. oracle RAC is mandatory for job when u go with exp. I am not the trainer who takes RAC training in kanna tech. I teach only DBA there. RAC is by other trainer. aftr learning rac or apps dba, u need to do job trails.

  98. Sabiha said

    Hii Sir,
    Im Bsc graduate in Computer science.
    Now i want to learn Oracle-Dba.
    So, will there be any job opportunities for me as a fresher after learning that course??
    Please, guide me to choose the best option.

    • Pavan DBA said

      Hi Sabiha,

      In today’s world, competition is more. so learning only oracle DBA will not get you job as fresher. So i recommend to learn any other course like oracle apps dba or oracle rac along with dba to increase your job oppourtunities.

  99. Sree Hari Dubakka said

    Hi DBA Guru,

    My name is Sree Hari .D working in MNC with total 4.3 yrs of experience in Oracle Apps Technical and recently I have been moved to Oracle Apps DBA role. So could you please let me know if I can get enough opportunities as am new to DBA. And also I recently took core DBA training and planning to take Apps DBA from your institute.

    At present I have 3 month of DBA exp…Please suggest and guide me.

    Best Wishes,
    Shri Haari.D

    • Pavan DBA said

      Hi Sreehari,

      First of all I am not DBA Guru… I am regular DBA with very minimal knowledge on concepts.

      Reg openings, yes there are lot of openings in APPS DBA field. For training, you can always contact the numbers provided in the training page.

  100. Anand said

    Hi pavan

    Is it possible to register multiple database version to catalog database and one more question

    Please suggest me how to analyze AWR,ADDM reports
    My query is running slowly its take long time to execute how to tune the issue

    and tell me 2 major performance issues in the database

    • Pavan DBA said

      yes. u can register. but it is not recommended as sometimes you may face trouble.
      reg analyze of reports, in my blog’s “important docs” section, i have already 3 docs listed. you can read them.

      for query running slow, check this link –

      for pref issues, you can refer to

      • Anand said

        Thanks Pavan for your valuable information.

        I have some issues like what are the reasons behind in ora600 error and when this error occurs is there any problem to database.

        How to verify corrupted block and how to restore corrupted block

        i have 200 datafiles existing and i added one more datafile . if the added data file is corrupted, by applying archive logs data will be recovered but the thing is the added data file is exits or else we need to create?

      • Pavan DBA said

        Hi Anand,

        there are lot many reasons why ora-600 will occur (for example any bug hitting database, any code writing error in sql etc). depends on the reason of ora-600, we can consider that as serious problem or not. but always it is better to search in ora-600 lookup tool first and then raise SR with oracle support for this error code. (even though it is serious or not)

        reg corrupted blocks, we have commands like dbv (DBVERIFY) or check corruption in RMAN to find out. If anything found, we have some steps to follow in order to recover that block. you can get those steps from many links in google.

        If the datafile is corrupted, we don’t have backup, then it is better to drop that datafile and then create new one and apply archivelogs.

  101. KUMAR said

    hi pavan

    unexpectedly i was drop one table in my one of the database . i don’t have any backup and my database in no archive log mode
    if any possible to recover

    • Pavan DBA said

      if u r using 10g database, it will be there in recyclebin. you can use below commands

      connect to user with which table dropped
      SQL> show recyclebin
      SQL> flashback table tablename to before drop;

      • KUMAR said

        Thank you pavan

        in case of using 11g is there is any possibility to recovery or restore

      • Pavan DBA said

        it is same as 10g. using recyclebin and flashback command

      • kumar said

        Hi pavan

        i was new to database so
        i have small doubting in my database . my database sys.aud$ tablespace take 1.3gb i want to drop that . in before that drop operation i will take backup using expdp it is possible ? if yes in which user(that means sys user or local user) i was export and import
        please tell me the commands for system user tablespaces

      • Pavan DBA said

        yes. you can take export using expdp with SYS user. but I am bit confused here. Do you want to take export of only AUD$ table or entire SYSTEM tablespace?

      • kumar said

        aud$ table only

      • Pavan DBA said

        expdp directory=directoryname dumpfile=dumpfilename logfile=logfilename tables=’AUD$’
        after entering above command, it will prompt for username, then give ‘/ as sysdba’

        this will take export of aud$ table and then you can truncate it (don’t drop the table)

      • kumar said

        thank you pavan

    • KUMAR said

      when i was get this error
      ORA-00600: internal error code, arguments: [kjucvl:!owner], [], [], [], [], [], [], []
      my data base was improper shutdown and start up again manually how can i rectify this error

      • Pavan DBA said

        please provide your database version and OS version. also, ora-600 is internal error and for this you need to search in my oracle support (this you can do only if you have license)

      • KUMAR said

        database version 64 bit
        Red Hat Enterprise Linux 5.2

      • Pavan DBA said

        urs is a bug and to fix that u need to upgrade to

      • shailendra m shinde said

        SQL>connect to /as sysdba
        SQL>startup mount
        SQL>alter database recover until cancel using backup control file;
        SQL>alter database recover continue default;
        SQL>alter database recover cancel;
        SQL>alter database open reset log

        And my database was alive .. In my case i havent lost any data but i do not guarantee that this method is data loss free (im just a rookie)
        This just brings my database back to life.

      • Pavan DBA said

        may I know for which concept u tried this? (I mean which incomplete recovery scenario) and also the db version?

    • KUMAR said

      how to release dml locks in oracle11g

      • Pavan DBA said

        we dnt have any control on applying/releasing locks. if lock is to be released, then either transaction shld be commit or rollback or we need to kill that session. killing a session is not recommended. so do either commit or rollback

    • KUMAR said

      how find how much memory allocate for single database in ram and how to find memory allocation plz provide steps for that

      • Pavan DBA said

        do u mean how much memory is allocated? (how much memory allocate and how to find memory allocation – these two confused me)

    • KUMAR said

      plz provide any materiel for oracle up gradation from to on Linux environment my database are on cluster environment and ocfs also . i was read your documents.

    • KUMAR said

      plz provide resource allocation management
      concept script

    • KUMAR said

      hi pavan

      my alert log file show number of core dump file .how can i rectify the problem

      • Pavan DBA said

        surely alert log will have info on why those core dump files are getting created. jst for that error message. if not raise a SR with oracle support

    • kumar said

      how to Reclaim system tablespace . my system tablespace has 30 gb but it uses only 5gb i want to reclaim free space from system tablespace .

    • kumar said

      hi pavan

      we have 8 databases . in one of the database was generating large size of archive logs how can i reduce the problem .

      • Pavan DBA said

        archive logs will generate when huge DML statements ran in your database. frankly this is not a unexpected behaviour, so nothing to avoid it. if you want to reduce, you need to tell the users to not run those statements. but we can’t do that. isn’t it?

        so we need to schedule backup of archives in frequent time like every 4 hours, 6 hours etc

    • Ganapathi said

      one of the database have take to lot of time for RMAN backup it will take around 15 to 20hr size of the database was approximately 900gb . how rectify this problem ..plz give the wil take backup of control file and spfile backup nearly 4hr i don’t understand that why it was take that much of time ..

      • Pavan DBA said

        hi, I believe some problem is there at storage level. check with storage team if they did some changes at SAN level. also u can trace the rman session when it is taking backup so that u can know where it is spending much time and why.

  102. Pinaki said

    Hello Pavan,

    Oracle recommends that once you apply PSU you continue to Apply PSU and not switch to CPU. This is mainly just for my interest. Can we apply CPU over PSU?

    Thanks and With regards


  103. Sagar said

    How to find a whether Oracle Server Software or Oracle client software is installed on the machine. Is there any command we can execute to get this information.

  104. Dear Pavan
    can u tell me how to add an instance in OEM?

  105. Anand said

    Hi Pavan

    Myself anand i read your posts i learned so many things from your valuable posts

    Today my question is what is blackout and why we need to set blackout in oem
    i have read some docs still i am not getting clarity in that . could you explain it briefly so that i can get exact picture.

    Thanks & Regards
    Anand T

    • Pavan DBA said

      usually OEM is used to generate alerts for database like when db is down we shld get email etc. but when we are doing some maintenance like patching, we know that db will be down and at tht time we dnt want OEM to generate any alerts. for this we have a facility called blackout.

      if u keep blackout (usually it will be for a duration like 15min, 1 hr etc), then OEM will not generate any alerts.

  106. Pinaki said


    I have few questions regarding Physical Standby Database.

    1) Does increasing LOG_ARCHIVE_MAX_PROCESS fasten up the apply process?

    2) Logs at standby db are being made by LGWR not by ARCH. Would it still help us increasing the number of log_archive_max_processes ?

    Thanks and with regards

    • Pavan DBA said

      sorry for replying late as i am out of city last week.

      1. no. log_archive_max_processes is only on primary side. this will make archiving faster. on standby side anyway oracle will span MRP process if required.
      2. nope. no effect.

  107. Anil kukadeja said

    I hv dne ocp frm ahemdabd. Ri8 nw i m searching d job frm last 1 year in gujarat nd gujrat is not relatd to gujarat u vl find al d things nd jobs except IT.wht shuld i do i m trying for dba on job portals for south region bt m nt getting responses.

  108. hima said

    how much we increase the sga size maximum
    let ram size is 24g .right now sga size 10.
    what are the changes needed to increase the sga size?

    • Pavan DBA said

      Hi Hima, it depends on lot of factors on deciding whether to increase SGA size or not. Usually we do that if we observe performance issues with database.

  109. Pinaki said


    DB Version:
    OS version OEL5.4

    1) OS patching will be performed on a server. What kind of things i need to do to verify everything is ok with the database afterwards?

    Thanks and with regards

    • Pavan DBA said

      usually, if required we will shutdown the database, listener, enterprise manager etc for OS patching. So, once patching is done, we need to make sure all the services like listener, EM etc and database is up and running. Once users can be able to connect to database, then u can confirm that everything is fine.

  110. Murali said

    Dear Pavan,
    Do chrash course available for DBA RMAN,RAC( can course be completed in around 15days). I have some basic knowledge on rman, taking manual backups, Creating databases, schemas, tablespaces etc.

  111. Anand said

    Hi pavan

    I have a question could you tell how to solve this issue
    Oracle 10g database.One table is dropped one hour ago by mistake and you don’t have the backup and flash back feature is not enabled?How you will able to recover?

  112. subhasish said

    Thanx pavan.I will do that

  113. subhasish said

    i am interested to go training on RAC +Apps dba from Kanna technologies>prior to that i thought of rebrushing oracle DBA course but duration (2 months) is too high for me as i have to try the job trial,so wanna finish it at the earliest.

    • Pavan DBA said

      Subhasish, the batches which I handle doesn’t have any fast track option. I believe there are fast track batches by other faculty and it is online. If you are interested, you can attend that. for more details, contact 040-40036087 / 8008500064

  114. naresh kumar said

    sir have you ever face a issue related to LISTENER?????i am facing a problem with my ebs server.LISTENER got crashed after i start aplplication.when i use only with database they work fine..plz help i

    • Pavan DBA said

      when you use EBS, there will be two listeners. one will be at application level and other will be at database level. which listener you are talking about?

      also, if it is database listener, please check listener log file and alert log file for more details.

  115. Shuv said

    Hii Pavan,

    I have gone through Oracle 11gR2 data guard concept and administration docs.
    As per the Oracle documentation, init file of the primary database is as follows:

    Example 3-1 Primary Database: Primary Role Initialization Parameters

    CONTROL_FILES=’/arch1/chicago/control1.ctl’, ‘/arch2/chicago/control2.ctl’
    ‘SERVICE=boston ASYNC

    My question is why the value is set as (ONLINE_LOGS,PRIMARY) for LOG_ARCHIVE_DEST_2?

    • Pavan DBA said

      it means LOG_ARCHIVE_DEST_2 is valid only for the primary role. This destination transmits redo data to the remote physical standby destination boston. when standby becomes primary, then it uses redolog files as online, so we are mentioning ONLINE_LOGFILES

      • Shuv said

        Hii Pavan,

        Thanks for your reply.

        I have one more question regarding this.

        What will happen if the value is set as (STANDBY_LOGIFLE,STANDBY_ROLE)for LOG_ARCHIVE_DEST_2?

      • Pavan DBA said

        nothing. you cannot perform switchover. thats it

      • Shuv said

        Hii Pavan,

        Thank you for your response and insight.

        When Standby DB becomes primary, we need ORL instead of SRL.

        if i set VALID_FOR=(standby_logfile,standby_role) for log_archive_dest_2 then the standby redo logfiles will be filled and archived instead of online redo log files on standby. So cannot perform switchover. Am i correct in my understanding?

      • Pavan DBA said


      • Shuv said


  116. naresh kumar said

    Hi sir,

    In an interview, they asked me ” what vil do to end to end instance of a delivery bill” and ” if you cant resolve the ticket what vil you do”. Sir, Please answer questions .

    Naresh kumar

    • Pavan DBA said

      if we can’t resolve the ticket, we will pass that to any other team who is responsible for solving it. if it requires any third party attention, then we will keep ticket in pending state

      • naresh kumar said

        if it is related to production database ?. vil raising an SR can solve the issue?

      • Pavan DBA said

        yes. if the issue is related to database and DBA couldn’t able to solve it, then we can take that to oracle support. in that case also we will place ticket in pending state

    • Naresh kumar said

      Sir, what about the first question
      “what vil you for end to end instance of delivery bill?(i thnk it has some grammatical mistakes)

      Naresh Kumar

  117. KUMAR said

    hi pavan sir
    i was new to rac database . we are using 2node RAC . my problem was database node balancing is not good that means example one node contains 100 sessions another node contain only 5 sessions like that plz give any idea about load balancing

    • Pavan DBA said

      this may be because tnsnames.ora file at application side may contain only one node information. you need to add another line to that tns entry and input and a parameter called load_balance=on. for a sample rac tns entry, refer google.

      • KUMAR said

        my tnsnames.ora file have contain that load_balance=on
        but there is no switching,when ever i was restart my database noad switching is well. after 2days from database restart same problem was occurs, this will occurs in single database only

      • Pavan DBA said

        do you have load balancer configured at networking level? (you can find out this with your network admin). just having load_balance=on is not enough. you need to have that load balancer configured, then only connections will be distributed.

  118. namarta said

    Hi pawan sir I have done MCA in 2010 and oca certification at oracle 9i .due to some reason i had to take break .in 2012 i join niit for oracle 10g right now i wants job in oracle field what i have to do for getting entry level job which will lead me as a racle dba in future

    • Pavan DBA said

      Hi Namrata, frankly now a days to get fresher dba job only DBA knowledge is not enough. so I recommend you to do either oracle apps dba course or oracle RAC and also as oppourtunities will be less, you need to keep on trying for job. in DBA also, you need to have good hands on for advanced topics like dataguard, rman cloning, upgrade etc

  119. Shuv said

    Hii Pavan,

    I have couple of basic questions regarding Standby Database.

    According to the Oracle docs:

    “It is highly recommended that you have one more standby redo log group than you have online redo log groups as the primary database. The files must be the same size or larger than the primary database’s online redo logs. ”

    My questions are:

    1) Why do we need one additional standby redo log group than in primary database?

    Is it because to avoid LGWR waiting on the primary while redo is applied?

    2)What type of problem will i encounter if the SRL size is less than the online redo log file size?

    With Regards

    • Pavan DBA said

      1. In case of 3 online redo log groups, it is recommended to use 4 standby redo log group this is in case if log switching is happening frequently on primary and all 3 standby redo logs are still not completely archived on the standby and 4th can be used here as there will be some delay on standby due to network or slowness of arch on standby.

      2. you may observe performance impact when you make that standby as primary in future

  120. Rohit said

    Hi Pavan,
    can u plz tell me how to find the number of row updated in current running sql as the query is running since long time..
    i googled and found one pl/sql code… but i m not sure it will i its production db.. i m not able to take risk.. can u plz tell me how to find it..

    • Pavan DBA said

      already if the statement is fired, then we cannot be able to know that. otherwise we need to depend on that pl/sql code only. there is no direct way in oracle for that. if u enable auditing on the database, then it is possible.

      • On Tue, Mar 5, 2013 at 8:36 PM, Pavan DBA’s Blog wrote:

        > ** > Pavan DBA commented: “already if the statement is fired, then we cannot > be able to know that. otherwise we need to depend on that pl/sql code only. > there is no direct way in oracle for that. if u enable auditing on the > database, then it is possible.” >

    • sir i have knowledge in coredba …i want to join RAC …tell me wch one is good in RAC OR APPSDBA…….WCH IS BEST INSTUTUE IN HYDERABAD

      • Pavan DBA said

        i recommend to join apps dba (ofcourse if you can do RAC also, then apps dba+RAC is hot in the market). for institute you can contact kanna technologies for apps dba and RAC or sri sai technologies for RAC

  121. hi pavan sir
    How to skip next column and before the column will be deleted ,so please tell me querry in oracle…please send to my mail :

  122. Sriniv@s.. said

    Hi pavan sir,

    i have one doubt about ORACLE NETWORKING.i.e suppose i have 3 database in my own pc,can i connect internal databases with out using listener.?

  123. Shuv said

    Hi Pavan,

    1)What’s the surest way to check whether a database has been cloned successfully?

    Checking RMAN logs is an option. Any other ways to check?

    2)I would like to know whether static entry in the listener.ora file required for the cloned database? DB Version

    Thanks in advance

    • Pavan DBA said

      1. we can check the object count between the target database and auxiliary to confirm cloning success.
      2. sometimes, i have seen rman cloning become issue without static entry. so i prefer to use static entry than extproc

  124. rohit said

    hi pavan ..its very simple and nice blog i have seen yet.. i really appreciate ur work..
    i am working as oracle DBA at syntel.
    i have one problem.. that most of the time we get the ticket on high cpu memory.. will u plz clear me out regarding what to do for that as oracle dba… i m using top command but unable to understand what happening..
    waiting for response..

    Thanks and Regards

  125. MV Bhaskar said

    Hi Pavan,
    This is Bhaskar. I am a BSC graduate of 1990 bach worked with an INHOUSE team of dealership company as Oracle Developer for 11+ years. And I am noware working now. Can I choose my career as DBA, and try for software jobs? Kindly assist. Will my age and Company will be a problem?

  126. Himanshu said

    Hi Pavan sir,

    I will have new machine with oracle 11g and i have exported dump from oracle 10g (
    Now i need to import that dump on oracle 11g.

    Is there anything that needs to taken care in this ?


  127. somnath said

    Hiiii pavan,

    I hope you are doing fine. I am somnath from kolkata. I’m new at administrating a database (10gr2 on RHEL). I need your help regarding few questions.

    1)Should I create a smallfile tablespace with one datafile or should i create a smallfile tablespace with several datafiles? Which one is recommended?

    2)Whether datafile should be set as AUTOEXTEND=ON or AUTOEXTEND=OFF? What is advisable?

    3) Is there any relationship between AUTOEXTEND & MAXSIZE?

    Thanks & regards


    • Pavan DBA said

      1. if u r using multiple hard disks in ur server, then multiple datafiles is recommended. otherwise 1 datafile is enough
      2. it depends on how much data that is coming in and whtr in ur company u have 24*7 support or not. i recommend to enable it with maxsize option
      3. yes, whenever u r using autoextend, u need to use maxsize also. (give any value for maxsize. for eg, if ur datafile size is 10G, give maxsize as 20G)

  128. janardhan said

    Hi pavan sir,

    please provide me latest dumps of oracle 11g OCA & OCP

    i wish u Happy sankranti to you and your family members

    have a nice day


  129. shuv said

    Hiii pavan,

    It is recommended that the SPFILE is shared between all nodes within the cluster, but it is possible that each instance can have its own SPFILE. I would like to know why it is recommended? Is it because shared SPFILE simplifies administration or anything else?

    Thanks in advance

    • Pavan DBA said

      yes. we can have individual spfile for each node. common spfile is preferred because whenever you do changes it will be effected for all instances at a time. if u maintain individual spfile and suppose you changed one parameter in one node and forgot in another, that may lead to some issue. to avoid this, we will maintain shared spfile.

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

  130. Madhava Pai K said

    Dear Sir,

    I am searching for a job in .net field .I have 2.3 year of experience .Currently i am working on classic asp and .net.
    I have only 0.5-0.6 year of experience in .net.
    I want to change my company.Please tell me if there is any vacancy.
    may i know your email id so that i can send my Resume.
    my mail id is

    Thank You.

  131. vamsi said

    Hi Pavan Sir,
    Please refer me if you any openings in cognizant for oracle apps dba(11i/R12).


  132. Ramesh said

    Hi Pavan,

    Great Sharing & Teaching, Keep it continued….

    Am an Oracle DBA with 7 Years and total 10+ IT exp, Looking for the next level . . .

    What should I do next? Your Inputs . . .


    • Pavan DBA said

      Hi Ramesh, thank you for comments.
      depends on your interest of going to management side or remain in technical side, I can give my inputs better. Please let me know which one you are thinking of…

      • Ramesh said

        Thanks pavan for your prompt response . . .

        I would like to stick on to Technical side . . . Please advice.

        Thanks once again. 🙂

        – Ramesh

      • Pavan DBA said

        on technical side, you can choose to be in a role of service manager(service delivery manager). This will be very good in order to grow technically

      • Ramesh said

        Thanks Pavan. Please let me know how to scale up ?

        How should I prepare for that?


      • Pavan DBA said

        as far as i know a service delivery manager should be able to have knowledge on all tecnicals concepts like unix administration, DB administration, network etc. I am not saying in depth, but a brief idea. With this knowledge you can promote yourself in current company for the next level as SDM.

  133. ashok said

    hi pavan,
    what was the rman internal mechanism

  134. kishore said

    Please provide the better answer for below interview question.
    Question: What is the most difficult task you faced so far (like in recovery scenario’s)

  135. VASUDEVAN said

    Whata is stats pack?What is flashback?What is pt?how to do pt?What is dataguard how to apply it?what is patching?how to apply patch?

  136. VASUDEVAN said

    what is the difference between Oralce Parallel server and RAC

  137. sai kiran said

    hi pavan,
    Now a days in interview they are stressing on up-gradation and migration (from 10g to 11g) so can you share the documents of up-gradation and migration and some points on this topic so that it will be very helpful to us

    Thanks in Advance

  138. amar said

    hi this is amar i want to learn oracle dba through online could you pls tell me which institute is better in Hyd..

  139. kishore said

    if database restart what happened to scheduler job. is it automatically start’s or we need to start again.

  140. Prashant said

    hello sir… i am a 2012 passout graduate(B.Tech CSE) and completed OCA certification in PL/SQL. Besides i am also have good knowledge of ORACLE DBA (10g) concepts like user mgt., achitechture,backup and recovery,ASM,data guard and performance monitoring and tuning also with basics.
    Now i am worried for getting the answer of ” How to start my career in database ??”. as some are suggesting me to join RAC and without it there is no job for fresher DBA.
    So please guide me sir…. i am also having good acdmics %(75+) till graduation. but not having any vision that how to start…..please help me sir…..

  141. apoorv said

    hi i wnt know about the best learning institute in hyderabad for oracle dba, also the institute which provide placements……..,as well as certification…………., plz tell which one is better oracle 11g or oracle 10 g

    • Pavan DBA said

      hope we discussed this in chat and you are clear

    • takecare said

      I recommend you and list you the institutes in priority wise from top to bottom,
      Wilshire (madhu sir batch)
      wilshire if any other faculty
      srisai institute
      db expert
      unirac solutions (with placement)
      uclid IT School
      dba technologies
      kernel it solutions
      kanna technlogy (pavan dba)
      kyathi technology
      sunmarss institute
      peers technology.

      Caution: I recommend you to join in any of the above mentioned top 8 institutes..If you join in any of listed after 8, there is no problem just you ultimately realize and you are going to join for course once again.

      ***Do you know already these institutes aware of this situation, they will not bother if you come 100 times for same course by paying only one time, why because these institutes almost out of students.

      Take the rite decision by considering the above

      • nandu said

        wilshire is always preferable

      • Dheeraj said

        hi this dheeraj, i completed my course in hashzone..hashzone is one of the best institute in ameerpet..may be after wilshire institue. i dont know about other institutes and also kanna inst.. my friend joined in unirac, institute was not good they will provide placement

      • sandy said

        How is the orafact institute, as because they are the partner of the oracle and saying for placement also.

      • Pavan DBA said

        as far as i heard or experienced from my trained candidates, orafact is not a good choice. so many of my trained candidates first joined in orafact, completed their course and then re-joined to my batch

  142. Sivaprasath.D said

    Can u share me with important wait events in oracle performance tuning… i browsed ur blog and unable to find info abt that… so can u help me with this topic…

  143. Sivaprasath.D said

    Hi Pavan,

    Today i found a interesting question about System tablespace and i could’n guess what may be the reason.. i believe u can help me with this.. the question is ..

    ” If there is so much place in system tablespace and it is not used
    it throws an error why?”

    • Pavan DBA said

      first of all question is not that much clear because the answer would be based on what error it is throwing. i assume the error is about space issue and this can happen due to fragmentation in the tablespace. but usually we won’t find this issue with SYSTEM tablespace.

  144. kishore said


    I am trying to migrate oracle db form windows to linux,in windows 7 i checked that process is 64-bit but in db level i checked in platform_name column it’s showing 32-bit
    please explain what exact diff for os level and db level version and why it is showing different from os

  145. kishorecv said

    please let me know the steps to imp 11g to 9i database


  146. ashish said

    hi can u tell me what is differenece between normal restore point and guarantee restore point ……..please explain with example

  147. Dinesh said

    Hi Pavan

    Due to archive destination got full,Moved some archives to another location,when i am running the rman backup it is not able to find the status of the files which are moved,so run the crosscheck command from rman the backup is success and deleted the archives.
    Now moved that files to original location but the issue is the status of these files is marked as expired in catalog,
    1) Is there any way to mark them
    as available in catalog.

    2)could you please explain the exact steps suitable to this scenario

    • Pavan DBA said

      RMAN> change archivelog all validate;

      try this command by connecting to both target and catalog and let me know if it works

      • Dinesh said

        it’s not working.we need to feed the the expired archives to the catalog once we move it to the original location,but couldn’t able to register them to the catalog.

        looking forward to here from you!!!

      • Pavan DBA said

        can you let me know what is the error you got when you ran above command?

      • shashi said

        hi pavan,
        i have around 80-100 databased i want to monitor the alert log file for u have any scripts or tool to monitor d erros which displays the time n date along with error
        thanx in advance…

      • Pavan DBA said

        hope we discussed this in chat today morning

  148. Gaurav said

    Hi Sir,

    while running . oraenv ….i am getting following error ..Pls suggest..

    oracle@xxxxx:/usr/local/bin>. oraenv
    ksh: @: 0403-041 Parameter not set.

  149. Rashmi said

    Hi pavan ,

    Can you please provide me help in creating partition on existing tables in the database .any document or link. its urgent .
    Appreciate your help.

  150. prathap said

    Hi sir, of my developer given package and asked me create.when i created it shown error below error.

    ——– ————————————————–
    906/10 PL/SQL: ORA-00942: table or view does not exist
    906/10 PL/SQL: SQL Statement ignored
    909/10 PL/SQL: ORA-00942: table or view does not exist
    909/10 PL/SQL: SQL Statement ignored
    960/3 PL/SQL: ORA-00942: table or view does not exist
    960/3 PL/SQL: SQL Statement ignored
    961/11 PL/SQL: ORA-00942: table or view does not exist
    961/11 PL/SQL: SQL Statement ignored

    then i sent to developer it is showing error.

    2.then the developer told me to create dblink and create. to create db link?wat is the syntax?


  151. Rashmi said

    Hi Pavan,

    I am facing ” ora-12547 tns lost contact while connecting through toad ” on client machine, all my listeners are ready on the server , even proper entry is made in tnsnames.ora at client end.This is my tns entry ,

    (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))

    And listener.ora on server,

    (ADDRESS = (PROTOCOL = tcp)(HOST = = 1521))
    (ADDRESS = (PROTOCOL = ipc)(KEY = EXTPROC1521))

    i am troubleshooting this for past 2 days but all in vain , i am able to connect to other servers through toad succesfully but only this server is causing the problem.
    even tnsping fails ,


    TNS Ping Utility for 32-bit Windows: Version – Production on 27-JUN-2
    012 18:07:56

    Copyright (c) 1997, 2010, Oracle. All rights reserved.

    Used parameter files:

    Used HOSTNAME adapter to resolve the alias
    TNS-12537: TNS:connection closed

    Plzz help..

    • Pavan DBA said

      in tnsping you should use alias name, not ipaddress.

      what is the result when you issue “tnsping hawkeye”? plz post me

      • Rashmi said

        C:\Users\rashmi>tnsping HAWKEYE

        TNS Ping Utility for 32-bit Windows: Version – Production on 02-JUL-2
        012 14:13:01

        Copyright (c) 1997, 2010, Oracle. All rights reserved.

        Used parameter files:

        Used TNSNAMES adapter to resolve the alias
        Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.33.3.
        TNS-12537: TNS:connection closed

      • Pavan DBA said

        i believe there is some issue with firewall also. so check from that side too or ask your network admin to do that

        see this link also –

  152. vipin garg said

    Hi Sir,

    I want to know that why Transporting Database is faster than DataPump

  153. manoj pathak said

    this is manoj pathak
    if i complet graduation,oracle dba certification.
    ca i got job as DBA… is there P.G copulsary…s or no…. .rply i’m wating

  154. ashok said

    Hi Pavan garu
    what is the difference between static listener and dynamic listener.

  155. nasir said

    Hi pavan,
    its very nice useful blog for your every dba.. as ur answer is very simple …. and easy to understand..
    i have one question that when we are upgrading DB from 10g to 11g is it necessary to apply patch to DB before.. and if it is necessary then why ? i m kindly waiting for ur reply

    • Pavan DBA said

      Hi Nasir, as you have not mentioned the 4 digit version of db, i am assuming you are asking about to

      we cannot upgrade directly to first we need to upgrade db to and then to
      to move db to, we need to apply patchset.

      this is how oracle defined their upgrade method

  156. Rashmi said

    Hi Pavan,

    I want to implement backup & recovery at my client end.I have thought of full db backup(cold bkup) every 15 days & incremental level 1 bkup everyday.Can my cold backp act as an incremental level0 backup to restore from incremental bkups.If not how do i figure it out can you plz help because client is more biased towards cold backup.need ur help.Thanks

    • Pavan DBA said

      Hi Rashmi, cold backup cannot be act as level 0 for incremental backup. so that option is ruled out.

      you need to take level 0 incremental backup which support level 1 backup later. Tell to your client the benefits of hot backup with RMAN. some clients will be thinking that cold backup is good and hot backup is not. in such situations, as a DBA, we need to explain them the benefits and make them understand that hot and cold backup are one and same especially in rman.

  157. upendar said

    hi sir,
    how i can convert my standby database to primary when my primary is crashed..plz explain me with example..

    thanks in advance…

    • Pavan DBA said

      Hi Upendar

      we need to use below commands to do failover (run these commands on standby to make it primary)

      select database_role from v$database;
      recover managed standby database finish;
      alter database commit to switchover to primary;
      alter database open;
      select database_role from v$database;

  158. upendar said

    Hi sir,
    what is current_user and global user in database links concept? could you please explain me with an examples?

    thanks in advance…

  159. 9500057638 said

    Hi Pavan,

    Actually am working as a PL/SQL Developer. But am very much interested to work as a DBA. Even i have completed Admin 1 and 2 Oracle 10 G. But i did my OCA on development track. Right now am searching for a DBA position. Can you guide me in preparing for this.
    I have basic knowledge on all DBA concepts. But dont have any handson.
    Am just requesting you to give some suggestions, like what can i put in my resume with this knowledge and am having 3 years of experinces in development.

    +91 9500057638

  160. vinod kumar said

    hii sir,
    this is vinod..sir im going to complete my btech within less than a month…… my area of interests is still in job trials….so can u please give ur valuable suggestions to get a job especially in the side of databases…and also im not able to find any openings for freshers in the stream of oracle or othr dbs

    • Pavan DBA said

      Hi Vinod, to get job as a fresher DBA, you need to acquire the best knowledge in that concept and also need to have a certification. Comparitively fresher dba jobs will be less as most of the companies are recruiting from campus itself.

      If you have strong concepts and certification, you can still get job in very good MNC’s

  161. Mohammed said

    Dear Pavan

    Great Job!
    What is the Duration for RAC Course !
    When will a New Batch Start Every Month?

    Many thanks

  162. Asif Hussain Khazi said

    Hi Pavan,

    I have installed red hat linux 4 in vmware twice for two different servers and installed oracle 10g. I configuers listener in one server and tnsnamesin another server.
    When I attempted to connect to the server I have got the above error. i have attached the file. Please check this and resolve the error………..
    ~]$sqlplus sys/******@to_prd as sysdba
    ORA-01031: insufficient privileges

    Enter username:

  163. Balu said

    Hello Sir,

    what are the parameters displayed we get while startup of a database.
    Fixed size, Variable size, Total SGA, Database buffers, Redo Buffers.

    • Pavan DBA said

      Fixed Size
      – Contains general information about the state of the database and the
      instance, which the background processes need to access.
      – No user data is stored here.
      – This area is usually less than 100k in size.

      Variable Size
      This section is influenced by the following init.ora parameters

      Database Buffers
      – Holds copies of data blocks read from datafiles.
      size = db_block_buffers * block size

      Redo Buffers
      – A circular buffer in the SGA that holds information about changes made to
      the database.
      – Enforced mininum is set to 4 times the maximum database block size for the
      host operating system.

      Total SGA – memory allocated to entire SGA

  164. Rahul said

    Hello Sir,

    Recently I attended an interview , I have given the questions which I’m not able to
    answer.Could you please provide the answers for these questions.

    1) If your database block size is 8k then how you take the advantage of 16k without setting non-standard block?

    2) What is start SCN & stop SCN?

    3) Why does Oracle advise against backing up online redo logs with a hot backup?

    4) I have created a table with nologging option.But any insert,update or delete still generates redo, why?

    5) How can we know the exact updation date of a particular database?

    Looking forward for your answers. Thanks in advance

  165. Sudhir said

    Hi Pavan,

    Recently I attended interview. He asked me many question among those I didn’t answered the questions mentioned below. Could you please provide the answers for these questions.
    1) when we submit a request it is taking long time, and rest of the queries running properly, what would be the reason.
    2) How can we find out whether the request is scanning full table.
    3) Can u explain about explain plan , its use.
    4) How can u find out the problem is with indexes, how can u resolve.
    5) If a request is running properly in all the peak hours, suddenly its get slow, what would be the reason.
    6) I found a gap nearly 10 to 20 lines gap in alert log file what would be the reason.

    Looking forward for your answers.

    • Pavan DBA said

      1. reason could be so many. we need to check for the reason in phases by doing gather stats check, generating explain plan etc
      2. we can check that from explain plan
      3. explain plan is a report which shows us if the query is using indexes or not
      4. by looking at explain plan. if we see query is using indexes, its fine. otherwise we need to create index on required columns
      5. again reasons can be many.
      6. i have never seen any gap like that. may be someone deleted some lines or oracle faced problem in writing to alert log

      Sorry to say this. but I am not sure how you are attending interviews without basic knowledge on performance tuning.(I estimated your knowledge based on your questions, i may be wrong)

      I recommend you strongly to read performance tuning guide from this link

  166. Prathap said

    dear sir,

    i have total 145 invalid objects in my datadbase.i used the below script to compile.


    after that it reduces 145 to 103.
    then it is not reduceing the invalid objects.


    —————————— ——————- ———-

    —————————— ——————- ———-

    16 rows selected.

    SQL> select count (*) from dba_objects where status like ‘INVALID’;


    how can i compile the remaning invalid objects?

    • Pavan DBA said

      Hello Pratap,

      To compile all the invalid objects in the database, we will use that utlrp.sql script. If even after executing that script, we have invalid objects means, we need to inform application/development team and they will take of them

      • Prathap said


        i have compiled using utlrp.sql reduceses to 145 to 103.when i will see next day it again showing 145.what is the cause sir?

      • Pavan DBA said

        cause could be because of some modifications to the underlying tables. As a DBA, we should not worry about them until application team says there is some issue

  167. Rashmi said

    Hi Pavan,

    I need to insert lakhs of rows in a table ,what needs to be done so that i get performance benefit.
    Secondly I need to migrate db from one platform to other.The db_block_size of primary db is 4kb ,how do i restore it on other linux paltform with different block_size of the db.


    • Pavan DBA said

      Hi Rashmi,

      With my experience, I have not seen any special things to be taken care while inserting huge no.of rows. Only thing we will worrying as a DBA is about huge archivelog generation which we can manage. But still if we want, least is disabling the index which may speed up the process a little bit.

      some other places, you can read that “NOLOGGING” clause may help in speed up process, but I want to say that that is not applicable for DML statements.

      regarding your second question, as the databases are different servers, we can use expdp/impdp to acheive this.

  168. upendar said

    hi sir,
    could you please explain me what is the use of cross check in RMAN with example..

    thanks in advance…..

    • Pavan DBA said

      Hi Upendar,

      suppose we have deleted some backup pieces of rman at OS level due to some space pressure. In this case, still RMAN thinks that files are available. so in order to make rman understand that files are not available, we will use CROSSCHECK command. when we run this, rman will mark the deleted backups as EXPIRED and we can remove that info from the backup list.

      I have given an example below…

      RMAN> list backup;

      List of Backup Sets

      BS Key Size Device Type Elapsed Time Completion Time
      ——- ———- ———– ———— —————
      1 17.81M DISK 00:00:01 31-MAR-12
      BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20120331T235037
      Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp

      List of Archived Logs in backup set 1
      Thrd Seq Low SCN Low Time Next SCN Next Time
      —- ——- ———- ——— ———- ———
      1 15 255056 31-MAR-12 272351 31-MAR-12

      BS Key Type LV Size Device Type Elapsed Time Completion Time
      ——- —- — ———- ———– ———— —————
      2 Full 221.68M DISK 00:00:24 31-MAR-12
      BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20120331T235057
      Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp
      List of Datafiles in backup set 2
      File LV Type Ckp SCN Ckp Time Name
      —- — —- ———- ——— —-
      1 Full 272367 31-MAR-12 /u01/app/oracle/oradata/test/system.dbf
      2 Full 272367 31-MAR-12 /u01/app/oracle/oradata/test/sysaux.dbf
      3 Full 272367 31-MAR-12 /u01/app/oracle/oradata/test/undo.dbf

      BS Key Type LV Size Device Type Elapsed Time Completion Time
      ——- —- — ———- ———– ———— —————
      3 Full 7.52M DISK 00:00:02 31-MAR-12
      BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20120331T235057
      Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp
      Control File Included: Ckp SCN: 272376 Ckp time: 31-MAR-12

      BS Key Size Device Type Elapsed Time Completion Time
      ——- ———- ———– ———— —————
      4 2.50K DISK 00:00:00 31-MAR-12
      BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20120331T235125
      Piece Name: /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp

      List of Archived Logs in backup set 4
      Thrd Seq Low SCN Low Time Next SCN Next Time
      —- ——- ———- ——— ———- ———
      1 16 272351 31-MAR-12 272382 31-MAR-12

      RMAN> exit

      Recovery Manager complete.
      c[oracle@pc1 ~]$ cd /u01/app/oracle/flash_recovery_area/TEST/backupset
      [oracle@pc1 backupset]$ ls -ltr
      total 4
      drwxr-x— 2 oracle dba 4096 Mar 31 23:51 2012_03_31
      [oracle@pc1 backupset]$ cd 2012_03_31/
      [oracle@pc1 2012_03_31]$ ls -ltr
      total 253228
      -rw-r—– 1 oracle dba 18676224 Mar 31 23:50 o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp
      -rw-r—– 1 oracle dba 232456192 Mar 31 23:51 o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp
      -rw-r—– 1 oracle dba 7897088 Mar 31 23:51 o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp
      -rw-r—– 1 oracle dba 3072 Mar 31 23:51 o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp
      [oracle@pc1 2012_03_31]$
      [oracle@pc1 2012_03_31]$ rm *.*
      [oracle@pc1 2012_03_31]$
      [oracle@pc1 2012_03_31]$ rman target /

      Recovery Manager: Release – Production on Sat Mar 31 23:52:08 2012

      Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

      connected to target database: TEST (DBID=2077458425)

      RMAN> crosscheck backup;

      using target database control file instead of recovery catalog
      allocated channel: ORA_DISK_1
      channel ORA_DISK_1: SID=30 device type=DISK
      crosschecked backup piece: found to be ‘EXPIRED’
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp RECID=1 STAMP=779413837
      crosschecked backup piece: found to be ‘EXPIRED’
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp RECID=2 STAMP=779413857
      crosschecked backup piece: found to be ‘EXPIRED’
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp RECID=3 STAMP=779413883
      crosschecked backup piece: found to be ‘EXPIRED’
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp RECID=4 STAMP=779413886
      Crosschecked 4 objects

      RMAN> delete expired backup;

      using channel ORA_DISK_1

      List of Backup Pieces
      BP Key BS Key Pc# Cp# Status Device Type Piece Name
      ——- ——- — — ———– ———– ———-
      1 1 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp
      2 2 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp
      3 3 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp
      4 4 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp

      Do you really want to delete the above objects (enter YES or NO)? yes
      deleted backup piece
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235037_7qglqorr_.bkp RECID=1 STAMP=779413837
      deleted backup piece
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_nnndf_TAG20120331T235057_7qglr9co_.bkp RECID=2 STAMP=779413857
      deleted backup piece
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_ncnnf_TAG20120331T235057_7qgls3x0_.bkp RECID=3 STAMP=779413883
      deleted backup piece
      backup piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2012_03_31/o1_mf_annnn_TAG20120331T235125_7qgls63k_.bkp RECID=4 STAMP=779413886
      Deleted 4 EXPIRED objects

      • upendar said

        thank u very much sir..

        here i have one more doubt..if backup is in some tape then how it will check..?
        what is mean by catalog repository and RMAN repository? is their any difference between these two?

      • Pavan DBA said

        for tape also process is same and RMAN can detect even if backup is in tape. catalog and RMAN repository are one and same

      • upendar said

        thank u sir…..

  169. Rashmi said

    Hi Pavan,

    Why do we store index & tables in different tablespaces.There is a performance benefit for sure but can you please explain how ?


    • Pavan DBA said

      Hi Rashmi,

      Generally servers will have multiple hard disks to store the files. the reason is, when we use a single hard disk, it will have only 1 I/O header and if this I/O need to do two tasks, then waiting will be there as I/O hdr can handle one task at a time. But when we have mutliple disks, there will be independent I/O hrds for each disk by which writing can be done parallely. This is called “Optimal Flexible Architecture” in Oracle.

      So, as per this, even if we create two tablespaces (one for table and other for index) in the same hard disk, there is no use. we need to create these two tablespaces in different hard disks and place indexes and tables separately which will reduce IO contention and will increase performance.

      Hope you got some idea now…

  170. sivakumar said

    Good Evening sir, I would like to know about all the information about cronjobs, and how create a script to scheduled a job for rman backup ,expdp Etc….. Please do the needful.

  171. upendar said

    Hi sir,
    i have a doubt in RMAN . According to my knowledge RMAN catalog will holds changed values and RMAN backups are stored in the at OS level to a disk or to a tape. is it true? if it is true what is mean by changed values and what they contain exactly?
    thanks in advance…….

  172. Rashmi said

    Hi Pavan,

    We can very easily download and apply the oracle patches.But very few of us are aware of main distinction between a CPU & a PSU patch.Can you please share the basic difference betwween the two.



    • Pavan DBA said

      Hi Rashmi,

      CPU (critical patch update) – it is a collection of bug fix patches which will be released every year.
      PSU (patch set update) – it is above CPU i.e it contains all the bug fix patches which are there in CPU and along with that it also contains other cumulative patches.

      Earlier, only CPU’s used to be released every quarter. but now PSU’s are also getting released every quarter.

      See this link –

      I will post an article about this soon… thanks for bringing this up 🙂

  173. Gaurav Nigam said

    Hi Sir,

    Thanks a ton for such a nice notes.
    I have a question:
    I have to drop a user from production box..
    what are the steps i have to take..?
    like first i need to take logical backup etc….i am little bit confused on roles,view, synonyms..shall i need to delete all these before droping the user….?
    Please help..thanks in advance..:-)

    • Pavan DBA said

      Hi Gaurav,

      when you want to drop a user, first you need to have approval from application team. mandatorily mail confirmation should be there.

      1. take logical backup
      2. take DDL for create user
      3. take info about privileges, roles etc assigned
      4. drop the user

      the above are the steps we need to perform.

      • Gaurav Nigam said

        Thanks Sir…

        One more little help ….i want to learn DBA related scripting, any Suggestions.

        Thanks in advance…

      • Pavan DBA said

        First you can learn shell scripting. (if not already have idea). then automatically you can able to write shell scripts. DBA scripting is nothing but shell scripting.
        for this there is a book called “Unix shell scripting” by “yeshwant kanitkar” which I feel is the easy one to understand

  174. sreenu said

    Hi Pavan,
    SQL runs in 10 seconds in DEV DB but the same query takes 10 mins to run in TEST DB? .and Prod DB also 10 secs

    Can advise me how to resole it? it’s an urjent

    Thanks an advance

    • Pavan DBA said

      Hi Sreenu,

      check if statistics are upto date for the table. generate the explain plan in dev and test and compare them. generally I saw these secenarios only when some indexes are missing etc. so explain plan will tell us about indexes.

  175. kishore said

    i attend many interviews recently,most of the are asking 2 qns regularly…1)what are you facing the top issue…in last 3 months
    2)how to tune bad query

  176. kishore said

    Hi,This is kishore,interviewer asked me like that
    in 11g we have memory_target parameter is there.they was asking me what about shared_pool,if i need tune that sh_poo how can u tune without mention sh_pool……….in pfile…tell me the ans

  177. Rashmi said

    Hi pavan,

    I am continously facing this error, though i tried everything created new listnr but still not able to cope up, please help

    TNS-12541: TNS:no listener
    TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
    32-bit Windows Error: 2: No such file or directory
    TNS-12535: TNS:operation timed out
    TNS-12560: TNS:protocol adapter error
    TNS-00505: Operation timed out
    32-bit Windows Error: 60: Unknown error

  178. sivakumar said

    Good Evening sir, this is sivakumar, how to move multiple tables at a time one tablespace to another tablespace?, Please give me in detail explanation.

  179. NIssar said

    Send me RAC Documents which contains the subject from the Basics of RAC.

  180. abhi said

    Hi pavan
    i have gone through several blogs as like Kamran Agayev’s. tom’s etc…but the need full information is at your blog…as like what are the books to be studied…and i have done database cloning through your given steps nice..and very short…

    i wish to know what are the good books or blogs to my self strong in the Sun SOLARIS 10 as i am beginner for that…??

    i need your help sir….

    • Pavan DBA said

      Hi Abhishek, thank you first of all for your comments on my blog. as we are not Os admin’s I believe we can concentrate more on DBA. Having basics knowledge of Solaris would be fine to survive. For those commands, you can refer to google. Let me know if there is specific reason to be strong in solaris.

  181. Mohammed Shahid Khan said

    Dear Pavan,
    I found very good valuable resources, I need your assitant regarding OEM Grid Control 10gR2 installation on CentOS 5.7
    while checking the kernel parameters for installation i am getting the below two parameters failed

    Checking for semmsl2=250; found no entry. Failed <<<<
    Checking for filemax=65536; found no entry. Failed <<<<

    I can not be able to resolve it, need your expert advise.


  182. suman said

    HI PAvan SIr,

    Can i get step by step doc to upgrade 10g 2 node rac CRS and database upgrade to 11g.


  183. Rashmi said

    GM Pavan,

    Hope u r fine n doing well.Pavan can u pls provide a good document or some link for PT covering basics ,tuning strategies etc.


  184. vinod kumar said

    hi sir ,
    this is vinod.i am a passout of it a good idea to do oracle apps as a are the oppurtunities and what are the prerequisites to learn oracle apps..sir please give your suggestions

    • Hi Vinod, I am not sure what you asked by Oracle apps. Because Oracle apps contains two divisions. One is technical (which is more into programming) and functional (people call it as apps dba, which is into administration side). But as a fresher oppurtunities will be less for any of the divisons

      pre-requisites to learn oracle apps technical – sql, pl/sql, forms and reports
      pre-requisites to learn oracle apps functional (apps dba) – sql, unix basics & basics of shell scripting, Oracle DBA

  185. Rashmi said


    Any idea about database replication & steps involved in it ?? Plz help.
    I browsed on google but couldn’t get the relevant info.Please explain in detail as i have to work with a client on it.

  186. Naveen.K said


    Iam naveen i completed my DBA . RIght now iam searching for the jobs on DBA as experience of 3 years.Can i let u know in which locations can i find a jobs on DBA….

  187. sivakumar said

    Sir, this is sivakumar, i would like to know how to design the database, i tried to do that, i faced many prob, one of that it, is
    putting Primary key and Fk. So please tell with one example, with any name of database. so that , i can do that

    • Hi Siva, designing database is one the complex jobs DBA will do (that is why most of the times experienced dba’s will do that). As per your question, I can understand that you are talking about table design (preferably we should not call it as database design). In table design, we should concentrate firstly on constraints and need to decide which columns can be PK or FK etc. The best example you can take always is EMP and DEPT tables in SCOTT schema.

      Suppose we have a student management system which is storing students information for a college, then columns in that table can be like this
      Branch etc

      now from the above you can see no columns can be PK, so we need to add new column by ourself called “student_id” and make that as PK. this column should also be there in other tables where it will be FK.

  188. Rashmi said

    Hi Pavan,

    Hope you are fine and doing well.Can you plz let me know what query do we need to execute to monitor high loads on db & contension.

  189. anwar said

    i anwartaz completed B.Tech-2011 with aggregate of 65%.After that i did oracle DBA in ameerpet since 5 months i have been looking for job as a fresher/trainee DBA.i uploaded resume in all job portals and i went so many job consultances but idid’nt got any single chance to attend intrview.know i’m totally frustated could u plz guide me how to get job as fresher.i’m eagrly waiting for ur replay

    • Now a days having Oracle DBA knowledge alone is not sufficient as competition is getting more day by day. so try to learn adv topics like oracle RAC, APPS DBA etc. also if possible, try to do OCP certification which enhances the chances of getting job

  190. rafi said

    hi pavan,
    i read u r blogs. its really helps lot.
    i have some questions
    one is how to restore the table (not using logical backup and flashback technology
    second is how to restore rman backup thats having on diff disk or tapes
    and third is what is locks latches and enques and please provide the performance tuning related docs.

  191. prathap said

    Hi sir,

    this is prathap,i have 4 doubts?

    1.archive logs are applying from production to standby…..suppose suddenely logs stoped shipping due to network problem. then wat steps have to take as a dba?

    2.archive logs appling very very slowley?wat type of steps we have to take as a dba?

    3.suppose i deleted 20(by mistake)archive logs in production before applying to standby.then wat type of steps to be take as dba?

    • 1. if it is or above version, then FAL processes will take care of archive log gap when network connection re-establishes
      2. you need to check alert log of standby to check for any further information.
      3. then u need to take incremental backup of primary database and rollforward that to standby. for steps, refer to google

      • prathap said

        hi sir,

        1.we are using 10g …Wat is FAL process duty?

        2.thank u

        3.thank you.

      • On the standby database a Fetch Archive Log (FAL) client monitors for gaps in the sequence of received logs. If it finds a gap, it may invoke one or more Fetch Archive Log (FAL) servers to run on the primary database to forward the missing item(s).

      • prathap said

        hi sir,

        on standby database 400 archivelogs are missed .i transfered that archive logs from production to standby manually by using scp command.then how apply that archive logs in standby?please provide me steps sir…………

      • you need to just make sure MRP process is up and running, rest will be taken care by it. or else, u can run register archivelog (for command check google)

      • prathap said

        thank u

  192. Jagat said

    Deadlock happened intermittent from a single select statements. There is no other second SQL statement. The select statements have parallel processes.What you think what might be the issues

    • Mohanty, I suppose there wouldn’t be deadlock issue for select stmt as they will hold only shared locks. so tracing the session may give more results

      • Jagat said

        Thank Pavan for your reply !

        I checked in the trace file it does not show call stack of use PX operations.
        The trace file also masking the commands which were run to hold Shared or Exclusive locks by the SQL. All deadlock traces are partial trace.
        Is _bloom_pruning_enabled”=FALSE setting really help on this?,We have PSU3,guess it may be a bug not fixed in PSU3 .can you suggest?.S and X locks are conflicting and one prevent the other P0 from making any progress. The P0 sessions try to get hold of same locks in reverse order.

      • I feel it is better to raise a SR (if not already done) before setting that parameter, bcz they can give better option for tracing

  193. Rashmi said

    Hi Pawan,

    Can u plz upload any document for oracle installation on linux platform, with details like
    what are kernel parameters and why do we need them. and so on.


  194. Ravinder Reddy said

    Hi Sir,

    This is Ravinder. I need some help from you.
    recently I got a chance to reorg some tables.
    As per my knowledge we have the following methods.
    3.export & import.
    In my case above 3 are not working.
    Here the database version is 8i(8.17).
    table size is 7GB and it contain one long datatype.

    I tried to use move command but it didn’t work bcos of long datatype.
    next trial I export the table with no indexes,no constraints it took almost 30 mins.
    But for import it is taking more than 10 hrs.

    Is there any other way to reorg this table?

    Thanks & Regards,

    • Hi Ravinder,

      as per my knowledge, export/import is the possible way since the database is 8i. You can better try to import that table into a test database. once done, you can export from there and import into production. (i believe even if u have import running for more than 10hrs, there will be no issue). Import will take that time because of LONG datatype.

      Try to use buffer, commit parameters while importing

    • Hi Sir,

      Thanks for your reply.
      Will try it.

  195. bhaskar said

    hi sir this is bhaskar student of kanna inst bd-19 batch. plse reply my questions to this mail id
    1.What is the differences between oracle 9i and 10g versions of RMAN backup ? iam trying on 3.2 years of exp… in interviews they asking in which level ur working L1 or L2 what is L1 and L2

  196. Shreya said

    Hi Pavan

    I am a aspiring DBA. I have taken coaching at a institute in Ameerpet. I have practised in lab environment. But i am putting 2.8 yrs experience as i was passed out in 2008. In the interview people are asking me several technical questions that i could answer. But some people are asking me questions such as ” TELL ME THE STEPS – – – HOW YOU CONNECT TO THE DATABASE IN UNIX PLATFORM MACHINES? ” I am unable to give the steps. please help me with these basic things. I am a hard worker, but due to some reasons i had not given a try at software job in these years.

    Please help me.

    • I am not sure if you practised on Linux env or not. If you practised, then its same way that we connect to database even on unix.
      export ORACLE_SID=
      sqlplus “/ as sysdba”

      let me know if u have any queries

      • Shreya said

        I have practiced on Linux platform only. RHEL 4 version.
        I say this as the answer, but immediate question they ask me is — ” YOUR TASK IS TO SWITCH ON THE MACHINE AND CONNECT TO THE DATABASE AND CHECK TO WHICH DATABASE YOU ARE CONNECTED “.

        I understand this sqlplus “/ as sysdba”, but I need elaborate steps.

        Please help.

      • Generally switch on the machine is not our task as a DBA. connecting to the database is same i told in prev reply. how to check which database we are connected? we need to use below query for that

        select * from v$instance;
        select name,open_mode from v$database;

      • Shreya said

        This will help me i think.. Thanks a lot Mr.Pavan. I know that there are some tools used to get access to the database like Putty. Can u tell me few other of such type which we generally use.

        Thank you so much once again for the reply.

      • for daily use to connect to server we use putty
        for some other db related tasks we use Enterprise Manager
        for installations etc using GUI we use tools like vncviewer, humming bird, xmanager etc

        more than this any tools using depends on project

      • Shreya said

        Thank you so much for the valuable response. I hope it helps me get a job soon.
        Thanks again Pavan.

      • Shreya said

        Hi pavan,
        Can you please list down the steps ” HOW TO APPLY PATCHES,&, DIFF B/W CPU AND PSU”?
        Is it possible to directly upgrade from to

      • Soon I will upload doc on how to apply CPU and PSU.
        Regarding the difference between them, CPU (critical patch update) which is a quarterly patch released by oracle corp. PSU (patch set update) is a collection of patches and when you apply this your database version will change.

        you can surely do upgrade from to for this you need to apply patchset update

      • Shreya said


        I am waiting for you to upload the above mentioned doc.
        Meanwhile, I would like to know ” What is a monitoring tool? What monitoring tools do a DBA use in real time? ”

        Thanks in advance.

      • Hi Shreya, I got too busy with office work and couldn’t be able to upload it. Let me do it soon
        About monitoring tool, generally we will use it for performance monitoring of the database. even though there are so many 3rd party tools available in the market, I just saw DBA’s using 10g Enterprise Manager only to do this….

  197. sridhar said

    Sir this is sridhar.mukta..

    In a team how many members have metalink access? and who wil download the patch and analysis? this question was asked in oracle corporation.. i could not give the successful answer..and what happens if we happen patch?

    He asked a question me that any issues you faced in patching? then i was answer that “pre requisite patch was missed”. he asked that is it ur mistake r ur team problem? r any analysis problem? who will analyse patch ?

    sir please give me a brief answer “what happens in real time before apply a patch”?


  198. sridhar said

    Hi sir Good morning,

    This is sridhar.mukta please provide me the documentation of Opatch.

  199. mahaveer said

    hi anna
    i am getting error will installing oracle 11. 2.0
    will i am running ./runInstaller it giving permission denied in .oui

  200. Jagadesh said

    Hi Pavan,

    How to find the fragmented indexes in a Database;


    • Hi, you can use below script for that

      ‘exec analyzedb.reorg_a_table4(’||””||rtrim(t.table_owner)||””||’,’||””||
      t.table_owner||’.’||t.table_name name,
      sum(t.inserts) ins,
      sum(t.updates) upd,
      sum(t.deletes) del,
      sum(t.updates)+sum(t.inserts)+sum(t.deletes) tot_chgs,
      to_char((sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0,’999999.99′) per_del,
      round(((sum(t.updates)+sum(t.inserts)+sum(t.deletes))/(decode(a.num_rows,0,1,a.num_rows)) *100.0),2) per_chg
      from analyzedb.table_modifications t,
      all_tables a
      where t.timestamp >= to_date(’&from_date’,’dd-mon-yyyy’) and
      t.table_owner = a.owner and t.table_owner not in (’SYS’,’SYSTEM’) and
      having (sum(t.deletes)/(decode(a.num_rows,0,1,a.num_rows)))*100.0 >=5
      group by t.table_owner, t.table_name, a.num_rows
      order by num_rows desc, t.table_owner, t.table_name;

  201. srinivas reddy said

    Hi sir

    This is srinivas from hyd… your valuable suggestions are very very help full to us… i completed my Msc(cs) (2011)am searching job now.. I came to know about your blog while searching on oracle material now am eager to see your blog daily…I need your help to get a job…

    thank you

  202. AJay said

    Hi Pavan,

    How are you ..?
    Could you pls help me out in letting me know any free oracle DB refresh tool, if any. Few senior guys are out here, but from past few days we are missing the integrity of the data, and very recently on friday@7th Oct’11, I too did a refresh, where we lost some of the data.

    Thanks much.

  203. kalaivani said

    Hi pavan,

    this is kalaivani. I a 2011 passed out BE graduate. I would like to work for cognizant. Your blog site was useful to know about the recruitment procedure. Thanks. But i need to seek for a referral for applying to cognizant. I don’t know anyone there. Could you please refer me? thank you in advance.

  204. Kishan said

    Hi Pavan,

    This is Kishan, It was really great to came to know about your blog and i am very happy that your are helping people with your valuable suggestions.

    I am really appreciate your time and patience while you are giving response to all posts…………..:)-

  205. vivek said

    Hello Pavan,

    greetings first of all thanks for this blog. i would like to get dumps and study material for exam 1z-051.

    Thanks for all your help.


  206. sagar said

    Hello sir, i completed Btech in 2007 and after that i worked 3years in satyam bpo in GIS side and now i want to change my career into oracle dba. I completed the cource and searching for job but i am not getting any oppurtunity. Could u suggest
    me how to get in. my contact number 09972948746.

  207. SK said

    HI Pavan,

    I am looking out for any employee referral, is it possible you can refer me to CSC.

    Please let me know your contact number/Email address. It would be of great help.

    This opening is for Change management.


  208. karthik said

    hello sir,
    this is karthik from chennai. Iam a btech graduate 2009 passed out completed my oracle ocp certification how can i get a job in oracle database .i tried a lot but none worked out pls give me a suggestion Iam ready to pay even via back door.

    • Hi Karthik, as a fresher there will be less opps for DBA. But i cannot say its completely impossible as i started as fresher DBA only. so keep on trying and follow my blog. in last 2 weeks i posted some fresher openings in HCL and cognizant. apply for them

      • karthik said

        k sir thanks for ur advice but pl temme if thr is any such opening for dba via backdoor k.really u have a gud heart sir bcoz ur answering for each nd every msg…thank you very much sir i gave u chat req in gmail sir pls acceptit my gmail id is
        once again thank u sir……

  209. ramesh said

    Dear Pavan,

    I would like to know the procedure of RMAN backup, the source and target databases both are in same host.


    • Hi Ramesh, sorry i didn’t got your question. what you mean by source and target databases? are you talking about recovery catalog?

      • ramesh said

        Dear Pavan,

        source (catalog database) target database (yet to take backup using rman)

      • I expect you already registered target database with catalog. in such case u need to connect to both target and source and then issue backup database command.see below example

        $ rman target / catalog rman/rman@rc

        RMAN> backup database plus archivelog;

  210. DL said

    “Error in writing to file D:\Oracle\product\\bin\oraevrus10.dll”

    Lucky I saw your blog and reboot the server and restart the Oracle patch. Saves the day man !

  211. ramesh said

    Hi Pavan

    This is ramesh from bangalore

    y v r running these two scripts & during installation

    • – it will change permissions to 755 and will change group owner to oinstall – will create oratab file

      you can get these answers when u read the output of them….

  212. kirshna said

    hi pavan,

    This is krishna, I’m very glad to see your blog

    I’m oracle core dba, my company people kept me as oracle apps dba. Recently i installed Oracle Apps R12 on RHEL 5.4 (64-bit) (IBM server). in server its working fine. when it comes to client machine its not opening, i added the server ip address in client machines. What to do & how to solve this issue?


  213. vivek vishal said

    Hi Pavan,
    Hope u r doing well , Do u have some opening related to SQL server DBA ?
    Do u also have document related to SQL server DBA .
    If u have please share …

    • Hi Vivek, I am getting SQL server dba openings info very less. but whatever i got, am posting them in my blog.
      also I am oracle DBA, so i don’t have any docs on sql server dba

  214. Sriram G said

    Hi Pavan,

    How are you?

    I am Sriram working in Oracle financial services as Oracle DBA. I am a B E (IT) professional.I have not done any course in DBA and I have been put into DBA team as my aggregate is more than 80% in Engg. I joined this DBA team as a fresher. I like this job very much and i am learning new things daily. The only disadvantage in my team is there is not much on-site opportunity but I am more interested in traveling to different countries. Can you please suggest me whether i can continue this team or switch over to any implementation or Appln development team as there is many on-site opportunities. If i want to continue in this team whether i have to do some course?

    Please add me in ur gtalk. I have sent you the request.

    Waiting for your reply.

    Thanks & Regards,
    Sriram G

    • As your are learning some good things, I suggest to continue in this team. Even for onsite opp, i think u need to get some experience. till that time, you can be here and later you can move to some other team where onsite opp are more. doing a course is not required as i feel.

      i added you in the gtalk and you can chat with me now…

  215. chandrashekar said

    Hi pavan just now i saw ur web site,it is very usefull

    What is the difference between obsolete RMAN backups and expired RMAN backups?

    • Obsolete means unnecessary backups. If at all we have latest backup available, RMAN will mark all older backups as obsolete backups depends on retention policy.

      Expired backup means backup which is not available physically at OS.

  216. Harry said

    am waiting for Goldengate installation documention and steps from you pavan..

  217. bhargava said

    Hi Pavan,
    Please try to post maximum openings in SQL DBA…! It will be appreciate…! Thanks in advance

  218. balagangadharreddy said

    hi pavan kumar,

    This is bala ,this blog is very nice,
    my ambition and interest is oracle apps technical consultant but i didn’t get any call for fresher (2010 mca pass out ) so i will put fake resume on apps , so plz give suggestion to me
    if u don’t mine plz send validations on purchase order conversion thru mail (

  219. krishna chaitanya said

    Hi pavan,

    Please can you post openings for IBM DB2 DBA please

  220. chandra said

    Hi pavan,

    the blog is very gud. I have been trying for job change and have found the update for jobs posted vry gud here. is there any alert system in your blog where if i need to know any thing of a particular location. a mail can be sent to me.


    • Hi Chandra,

      Thanks for your comments…………
      On the left hand side of my blog, there will be email subscription tab. you can leave your email id and whenever any post is pasted, you will get email notification

  221. Ravi said

    thanks for reply my msg and i happy that u helps any person who are getting job………….

  222. Ravi said

    hi sir

    please give me suggestion. i wants to get job in oracle. if u have some time 4 me then u i want chat with u….. i will be waiting u

  223. vishnu said

    Hi Pavan,

    This is vishnu,may i know the ans for this-if some archive logs are not copied from prod to standby but we know after 1hr now what will we do know,and
    what is softlink?
    how do u know 2 users are accessing same table?
    what is dbcache wati?
    Difference b/w dbms_stats and analyze?

    • 1. First we need to check the reason why they are not copied. for this we can check alert log of both primary and standby databases. If it is due to network problem, we have FAL server/client which will take care of this gap
      2. softlink is a method of accessing a file with alias name (like shortcut in windows)
      3. we can check that info by looking at their sql_text or by checking locks that they are holding on the table
      4. a transaction waiting for free block in database buffer cache is called dbcache wait
      5. dbms_stats will collect more statistical information than analyze command. so oracle recommends to use it.

  224. vishnu said

    Hi Pavan,

    This is vishnu i want know some info from u i.e, if we create physical standby from production db,Once i create one new tablespace/datafile in production db and that is not a actual loc i created that separate loc that is it updated standy by or not,if i set standby_file_management=auto after what we have to do.

    • Hi Vishnu, yes it will be updated provided you need to have same location as primary in standby also.
      For example, if you added file in /u01/ora10g/prod/mydata03.dbf in primary, in standby also you need to have /u01/ora10g/prod location.
      All this will happen only when standby_file_management is set to AUTO.

      If you don’t have that directory structure, oracle will try to create, but fails. in such case, your standby will be out of sync from primary

      Hope that answers you question

  225. Ravi said

    Hi Sir
    i am Ravi and i have been completed OCA & OCP but i am fresher and i am looking job from long time but i am getting job. please tell me what i do…..i am waiting…..
    if u want to give me any suggestion please give me because
    i love oracle and i am ready for any compromise.

    • Hi Ravi,

      Unfortunately there will be very less fresher DBA jobs. But its not impossible to get job as fresher as I also started my career like that. So don’t loose hope and keep on trying. I don’t know if you did, if not plz post your cv in all the job sites like naukri,monster etc.

      Plz chat with me at to give more suggestions

  226. Vishnu said

    Hi this vishnu can you please send me upgradation steps from 10g to 11g and 9i to 10g with screen shots.

    • Hi Vishnu, you can get lot of docs from google itself. But in few more days i am going to upload them in my blog at “important docs” page. keep watching it……

  227. bala said

    hai pavan
    this is bala from chennai. really it’s amazing tutorials.just i want know without certification can i get job.please tell me or mail me and also i need oracle 9i dumbs .if u have please send me my mail.once again i tell u r done great job.

    • Hi bala, certainly certification will be added advantage to the profile while attending interviews. but without certification also you can go through interview process and get the job. i don’t recommend to use dumps to clear the exams. its a preferable way to get good hands on exp on the topics and then appear for the exam.

      thanks for all the words you mentioned !!!

  228. Srilakshmi said

    Hi Pavan,

    I am srilakshmi. I am following your DBA blog. its very nice. I have done Oracle DBA 10g course. My life ambition is work as a DBA without that i cant imagine my life. But i dont how to get a job as a fresher.I would be very greatfull If u give any suggestions to me.


  229. Brijendra Kumar said

    Hello Sir,

    I want to know the procedure to restore database from tape.
    I mean if I have backup in tape drive then what are the steps & procedure to restore that backup?

    Thanks in advance.

  230. Nazeem said

    Hi Pavan,

    This is Nazeem from Pune,It was great to came to know about your blog and i have gone through your blog and i am very happy thet your are helping and cooperating with people,

    I am working as Recruiter in MNC and i am looking for Wintel DBA with Unix,the person who will expert in read write and speak Japanese this is with one of my CMML5 client.

    I would realy appreciate if you can do something for this.

    Awaiting your possitive reply.

    I will be reacheble on


  231. dev said


    I want to write a shell script for compiling materialized view.
    Let suppose there are 10 MV
    when I check the status of them through the query

    select object_name from user_objects where object_type=’MATERIALIZED_VIEW’ and status=’invalid’;

    Let I got 4 MV whose status are INVALID

    Now I want to compile these 4 MV to make it valid by running command one by one


    I want to automate the whole process through shell script.

    Kindly advice.

  232. srinivas said

    Hi Sir, how to check the detection of corruption in datafiles in rman ? sir i need command…

    • if datafiles are having corruption, RMAN will show that during backup or validating the backup.
      still you can use below commands to do that


      you can also use dbverify utility to find corruption for datafile (for syntax, refer google)

  233. vamsi krishna said

    what is the force logging and nologging.
    what is dataguard broker.
    backup of archive log all delete input:what is the use of command

    • force logging will make sure all changes will be recorded in redolog files. If you use nologging option, those changes will not be recorded in redolog files. But NOLOGGING is not applicable to DML statements. It is useful for statements like
      1. create table as select * from …
      2. Insert into A select * from B;
      3. alter index rebuild etc

      Dataguard Broker – It is a utility through which we can easily manage primary and standby databases using observer. For more details, read oracle docs

      backup of archive log all delete input – this is not correct command. The below is the correct command

      RMAN> backup archivelog all delete input; – this will take backup of all archive log files in archive destination and will delete them immediately.

      • vamsi krishna said

        Thanks sir
        it will delets the all archive log files from archive destination or
        delete prev backup of archives from rman backup directory in target database.

      • it will delete the archives which were taken backup (not newly generate ones)

      • vamsi krishna said

        what is the minimum size of redo logfile and can we resize the redologfile ?
        how to monitor the redolog file size ?

      • Min size you can give for redolog file depends on operating system. In 9i min size is 100MB whereas in 10g & 11g it is 50MB.
        redo size also depends on how much redo is generating….

        we cannot resize redologfile, instead we can create new group with required size and drop the old one

        redolog file size can be get from below query
        SQL> select sum(bytes/1024/1024),group# from v$log group by group#;

  234. basha said

    hi sir, this is basha , pls educate me about cpu patcher which are oracle provides for every 3 months. bcoz i faced that quetion in interview. Our friends told that i.e, app dba relater. so pls help me regarding this

    • Hi Basha, its not related to apps dba. it is for DBA only.

      As any other software, oracle database also contains some bugs in every version (9i or 10g or 11g). To fix those bugs oracle will release critical patch update (shortly called as CPU) every 3 months.

      DBA need to download this patch from metalink and following a readme file (which gives step by step instructions) we need to apply patch on the database.

      The latest CPU released is in OCT 2010 for database

      • vamsi krishna said

        hi sir,
        if our database in force logging. what about tablespaces whether in nologging or force logging

        I have one table with 2 rows.Using only single update statement row positions will become interchange(SWAPING) from tech mahindra interview.

      • if entire database is placed in force logging, even if your tablespace is in nologging it doesn’t makes difference
        about your another question, i didn’t understand it….plz explain

  235. Abhi said

    I want to discuss with someone Oracle table partition. I have some doubts. Please add me on yahoo messenger

  236. Sasiniveda G said

    Hi pavan…am sasi…i have completed my BE(CSE)in 2010..looking for software jobs..ll u tell me any me.

  237. sandeep said

    hai.. am sandeep.. i completed MCA in 2009.. i did oracle DBA in wilshire..
    will you tell me any interviews for DBA freshers in Hyd

    • hi sandeep, plz keep watching this blog…any updates will first reflect here…

      • sandeep said

        am following your blog from 3 months.. you are posting only exp jobs only.
        but not for freshers.. plz post oracle DBA fresher jobs

      • Hi Sandeep, am not a HR to know fresher openings. whatever openings i came across am posting them.. frankly its not in my hands ! If i get to know about any opp about fresher DBA jobs, surely i will post them or if you know anything like that, forward me so that i can post it….

  238. Ketan said

    Hello sir i am a Btech (2008).Working with a bpo level 2 tech support.In total have around 17 months of experience.Looking for a change.Help me out .Please contact me at 9718685666

  239. sai said

    im sai from kannatech

    How can i increase the size of the tmp Directory to allow more swapping ?

    • am not sure about the way on how to do that. better install linux again. but before that, check your /tmp size using below command

      $ df -h /tmp

      also check if its writable by oramnr user or not

    • Brijendra Kumar said

      I am not sure but may be it works, just delete the file which are there in /temp or increase the size of ur ram.

      Or if u want to increase the ur swap
      follow this……….

      > Create a swap file and add it to the existing swap:

      #mkfile m /swap
      #swap -a /swap


      #mkfile 20m /newdir/swapfile
      #swap -a /newdir/swapfile

      To make this swap space available (add the swap file to virtual memory) each time the system is rebooted, add a line in the /etc/vfstab.

      #device device mount FS fsck mount mount
      #to mount to fsck point type pass at boot options

Leave a Reply to Pavan Kumar 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